sysmaster database schema
Everyone,
There have been many emails about where to find system information in the
various sys tables, I find having a hard-copy listing of the sysmaster
database schema helpful. Below is a primitive Unix ksh script to generate
such a schema. A lot of these tables are currently "unsupported" - besides
the usual Informix Guides does anyone know of a 'hidden delights of the
sysmaster database' document ?
Geoff Poole, Oxford GlycoSciences
#!/usr/bin/ksh
#--------------------------------------------------
# Description: primitive generate sysmaster database schema
# Author: Geoff Poole
#--------------------------------------------------
coltype()
{
case $1 in
0) echo SQLCHAR ;;
1) echo SQLSMINT ;;
2) echo SQLINT ;;
3) echo SQLFLOAT ;;
4) echo SQLSMFLOAT ;;
5) echo SQLDECIMAL ;;
6) echo SQLSERIAL ;;
7) echo SQLDATE ;;
8) echo SQLMONEY ;;
9) echo SQLNULL ;;
10) echo SQLDTIME ;;
11) echo SQLBYTES ;;
12) echo SQLTEXT ;;
13) echo SQLVCHAR ;;
14) echo SQLINTERVAL ;;
15) echo SQLNCHAR ;;
16) echo SQLNVCHAR ;;
17) echo SQLINT8 ;;
18) echo SQLSERIAL8 ;;
19) echo SQLSET ;;
20) echo SQLMULTISET ;;
21) echo SQLLIST ;;
22) echo SQLROW ;;
23) echo SQLCOLLECTION ;;
24) echo SQLROWREF ;;
40) echo SQLUDTVAR ;;
41) echo SQLUDTFIXED ;;
42) echo SQLREFSER8 ;;
43) echo SQLLVARCHAR ;;
44) echo SQLSENDRECV ;;
45) echo SQLBOOL ;;
46) echo SQLIMPEXP ;;
47) echo SQLIMPEXPBIN ;;
*) echo UNKNOWN ;;
esac
}
dbaccess sysmaster - <<!
unload to "sysmaster_cols.unl"
select t.tabname,c.colno,c.colname,c.coltype,c.collength
from systables t,syscolumns c
where t.tabid = c.tabid
order by 1,2;!
IFS="\\|" # define field seporator for read command
cat sysmaster_cols.unl|while read TABLE COLNO COLUMN COLTYPE COLLENGTH
do
[ "$TABLE" != "$PREVIOUS" ] && { printf "\\n%-20s\\n" $TABLE ; }
PREVIOUS=$TABLE
(( COLTYPE2=COLTYPE&255 ))
printf " %3d) %-20s %-s(%d)\\n" $COLNO $COLUMN $(coltype $COLTYPE2)
$COLLENGTH
done