Get Database DDLs

A SQL query, and BTEQ, to build the DDL for Database and User objects

English Italian  
Generates all DDL for "User" and "Database" objects from a defined db-branch.

-------------------------------------------------------------
-- NAME : CreateDatabasesDDL
-- DESCRIPTION: Generates all DDL for "User" and "Database"
-- objects.
-- PARAMETERS :
-- 1) root database
-- NOTE :
-- REMARKS : Some "User" parameters were omitted while
-- reverse engineering DDLs. The default password for
-- "User" objects in DDLs is set like the "User" name.
-------------------------------------------------------------
-- VERSION : 1.3, 18 Jan 2006
-- AUTHOR : Daniele Giabbai
-- CHANGES : Sorted the list for dbkind.
-------------------------------------------------------------
-- VERSION : 1.2, 11 Feb 2005
-- AUTHOR : Daniele Giabbai
-- CHANGES : Modified calculation of the "SpoolSpace" to
-- be assigned to each database: it must be as much as the
-- maximum spool space assigned to any of it's children.
-- Also added a "MODIFY" statement for the root database to
-- ensure that children spaces are assigned with no errors:
-- when running the generated SQL statements,

-- you should manually check that the statement works or
-- manually assign the correct amount of space (perm and
-- spool) to the root database.
-------------------------------------------------------------
-- VERSION : 1.1, 10 Feb 2005
-- AUTHOR : Daniele Giabbai
-- CHANGES : Added:
-- 1) "Comment" SQL statement generation.
-- 2) filter for a selected branch (must specify the root
-- database).
-------------------------------------------------------------
-- VERSION : 1.0, 28 Jan 2005
-- AUTHOR : Daniele Giabbai
-------------------------------------------------------------

Select
   'CREATE ' || (
    Case  DBKind
      When  'D' Then 'DATABASE'
      Else  'USER'
    end) || ' "' || DatabaseName || '" FROM "' || OwnerName || '" As ' sql01
  ,      'PERM = '  || trim(PermSpace(FORMAT 'ZZZZZZZZZZZZZZ9')) sql02
  , 'TEMPORARY = '  || trim(TempSpace(FORMAT 'ZZZZZZZZZZZZZZ9')) sql03
  ,     'SPOOL = ' || trim(SpoolSpace(FORMAT 'ZZZZZZZZZZZZZZ9')) sql04
  ,   'ACCOUNT = ''' || AccountName || '''' sql05
  , (
    Case  ProtectionType
      When  'N' Then 'NO '
      Else  ''
    end) || 'FALLBACK' sql06
  , (
    Case  Substr(JournalFlag,1,1)
      When  'S' Then ''
      When  'N' Then 'NO '
      Else  'DUAL '
    end) || 'BEFORE JOURNAL' sql07
  , (
    Case  Substr(JournalFlag,2,1)
      When  'S' Then ''
      When  'N' Then 'NO '
      When  'L' Then 'LOCAL '
      Else  'DUAL '
    end) || 'AFTER JOURNAL' sql08
  , Case  DBKind
      When  'D' Then ''
      Else  'PASSWORD = ' || trim(DatabaseName)
    end   sql09
  , Case  DBKind
      When  'D' Then ''
      Else  (
        Case
          When  DefaultDataBase is Null Then ''
          Else  'DEFAULT DATABASE = "' || trim(DefaultDataBase) || '"'
        end)
    end   sql10
  , Case
      When  CommentString is Null Then ''
      Else (Case When  position('''' In CommentString) >0 Then '--- ' Else '' End) ||
        'COMMENT ON DATABASE ' || DatabaseName || ' AS ''' || trim(CommentString) || ''';'
    End   sql11
  , ';'   sql12
From
  (
  select
    trim(A.DatabaseName) As DatabaseName
    , trim(OwnerName)    As OwnerName
    , trim(AccountName)  As AccountName
    , ProtectionType
    , level
    , JournalFlag
    , B.PermSpace
    , B.SpoolSpace
    , TempSpace
    , RowType (NAMED DBKind)
    , DefaultDatabase
    , CommentString
  From
    DBC.DBase A
  inner join
    (
    -------------------------------------------------------------
    --- Sum: perm space of each node + perm space of its children
    -------------------------------------------------------------
    Select
      trim(DatabaseName) As DatabaseName
      , sum(PermSpace) As PermSpace
      , max(SpoolSpace) As SpoolSpace
    from
      (
      -----------------------------------------------------------
      --- Get perm space for each node
      -----------------------------------------------------------
      select
        trim(DatabaseName) As DatabaseName
        , PermSpace
        , SpoolSpace
      From
        dbc.databases

      union

      -----------------------------------------------------------
      --- Sum child perm space, for each node that has children
      -----------------------------------------------------------
      select
        trim(Parent) As DatabaseName
        , sum(PermSpace) As PermSpace
        , max(SpoolSpace) As SpoolSpace
      From
        dbc.children A
        inner join dbc.databases B
      on Trim(DatabaseName) = Trim(child)
      group by 1
      ) A
    group by 1
    ) B
  on A.DatabaseName = B.DatabaseName

  inner join
    (
    -------------------------------------------------------------
    -- Calc the level of childness for each database
    -- and filter only databases of a defined branch
    -------------------------------------------------------------
    Select
      DatabaseName
      , rank(level ASC) as level
    from
      (
      Select
        trim(child) As DatabaseName
        , count(parent) level
      from dbc.children
      where DatabaseName in
        (select trim(child) from dbc.children
         where (trim(parent) = trim(database) or
                trim(child)  = trim(database))
           and trim(child) not in ('DBC')
         )
      group by 1
      ) A
    ) C
  on B.DatabaseName = C.DatabaseName
  ) As CreateDatabases_DBList
order by level,DBKind
;

Copyright © 2004-2018 Daniele Giabbai