First cut at a dbschema replacement for OnLine 5.0
Here's a first cut at replacement for dbschema that will retrieve
extent information, locking mode, and dbspace location.
It needs some work, and I'm looking for some help...
* money dimensions - I don't know how to translate a money field
* indexes - It needs to spit out indexes
* dbspace placement - How do you determine the name of the dbspace
in which a table is located?
If anyone wants to help, please send email, along with code suggestions.
Here's what I have so far:
----- cut here -----
#############################################################################
#
# $Header$
#
#############################################################################
#
# Title:
#
# Description:
#
# Name: dump_ddl
#
# Input: None
#
# Author: bossert
database mpc_001
define
table_name char(20),
db_name char(20)
main
define
argcount smallint,
cmd char(20),
i smallint,
j smallint,
cc integer
let cmd = NULL
let db_name = NULL
let table_name = NULL
call fgl_getenv( 'DBNAME' )
returning db_name
let argcount = num_args()
if ( argcount > 5 )
then
error "Maximum of 5 arguments"
exit program
end if
let j = 1
for i = 1 to argcount
if ( j > i )
then
continue for
end if
let cmd = arg_val(i)
if (cmd[1] = "-") then
case cmd[2]
when "d"
let j = i + 1
let db_name = arg_val(j)
-- when "t"
-- let j = i + 1
-- let table_name = upshift(arg_val(j))
otherwise
display "Usage foo [-d database]"
exit program
end case
end if
end for
if ( db_name is not NULL )
then
database db_name
else
display "No database defined"
display "Usage dump_ddl [-d database]"
exit program
end if
call prep_messages() -- Prepare SQL statements
call run_messages() -- Run report
exit program ( 0 )
end main
##############################################################################
function prep_messages()
##############################################################################
#
# Description:
# Select tables
#
define
sql_text char(2000)
let sql_text =
"select ",
"t.tabname, ",
"t.owner, ",
"t.locklevel, ",
"t.fextsize, ",
"t.nextsize, ",
"c.colname, ",
"c.colno, ",
"c.coltype, ",
"c.collength ",
"from ",
"systables t, syscolumns c ",
"where ",
"t.tabid > 100 and ",
"t.locklevel not in ('B') and ",
"t.tabname not matches 'sys*' and ",
"c.tabid = t.tabid ",
"order by ",
"t.tabname, ",
"c.colno "
prepare messages_s1 from sql_text
declare messages_c1 cursor for messages_s1
end function
# prep_messages
##############################################################################
function run_messages()
##############################################################################
#
# Description:
#
#
define
r record
tabname like systables.tabname,
owner like systables.owner,
locklevel like systables.locklevel,
fextsize like systables.fextsize,
nextsize like systables.nextsize,
colname like syscolumns.colname,
colno like syscolumns.colno,
coltype like syscolumns.coltype,
collength like syscolumns.collength
end record,
rpt_start smallint
let rpt_start = FALSE
open messages_c1
if ( sqlca.sqlcode != 0 )
then
return
end if
while ( TRUE )
fetch messages_c1 into r.*
case
when ( sqlca.sqlcode = 0 )
when ( sqlca.sqlcode = NOTFOUND )
exit while
otherwise
exit while
end case
if ( rpt_start = FALSE )
then
start report messages_rpt1
let rpt_start = TRUE
end if
output to report messages_rpt1( r.* )
end while
close messages_c1
if ( rpt_start = TRUE )
then
finish report messages_rpt1
end if
end function
# run_messages
##############################################################################
report messages_rpt1( r )
##############################################################################
#
# Description:
#
#
define
r record
tabname like systables.tabname,
owner like systables.owner,
locklevel like systables.locklevel,
fextsize like systables.fextsize,
nextsize like systables.nextsize,
colname like syscolumns.colname,
colno like syscolumns.colno,
coltype like syscolumns.coltype,
collength like syscolumns.collength
end record,
first smallint,
i char(1),
l char(1)
output
top margin 0
bottom margin 0
left margin 0
order external by
r.tabname,
r.colno
format
-- first page header
before group of r.tabname
print 'create table "', r.owner clipped, '".', r.tabname clipped
print '('
let first = 0
on every row
if ( first = 1 )
then
print ','
end if
let first = 1
print column 5, r.colname clipped, ' ';
case
when ( r.coltype = 0 )
print 'char(', r.collength using '<<<<<<', ')';
when ( r.coltype = 1 )
print 'smallint';
when ( r.coltype = 2 )
print 'integer';
when ( r.coltype = 7 )
print 'date';
when ( r.coltype = 8 )
print 'money(', r.collength using '<<<<<<', ')';
when ( r.coltype = 256 )
print 'char(', r.collength using '<<<<<<', ') not null';
when ( r.coltype = 257 )
print 'smallint not null';
when ( r.coltype = 258 )
print 'integer not null';
when ( r.coltype = 262 )
print 'serial';
when ( r.coltype = 263 )
print 'date not null';
when ( r.coltype = 264 )
print 'money(', r.collength using '<<<<<<', ') not null';
end case
after group of r.tabname
skip 1 line
print ')'
print
'extent size ', r.fextsize using '<<<<<<<<', ' ',
'next size ', r.nextsize using '<<<<<<<<'
print 'lock mode (';
case
when ( r.locklevel = 'P' )
print 'PAGE';
when ( r.locklevel = 'R' )
print 'ROW';
end case
print ');'
skip 1 line
end report
############################################################################
function messages_rcsid()
############################################################################
#
# Description:
# This function