-------------------------------------------------------------
-- 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
;
