/ --- Rexx --------------------------------------------------------- /
/ recherche d'une description de table DB2 /
/ Paramètres : - nom de la table à décrire /
/ Entrée : - néant /
/ Sortie : - néant /
/ ------------------------------------------------------------------ /

Address ISREDIT
"MACRO (param)"
if param = "PARAM" then parse arg param
parse upper value param with param

call initialisation
call traitement
call fin

exit

initialisation:
topca = 0
call mesvar("-L "param)
TABLE = mesvar("-g glChn")
SSID = mesvar("-g glSSID DO31")
CREATOR = mesvar("-g glOwner TOZ0")

TABLE6 = ""

select
when index(TABLE,"%") > 0 then TABLE = "like '"TABLE"'"
when index(TABLE,"_") > 0 then TABLE = "like '"TABLE"'"
otherwise do
TABLE6 = substr(TABLE, 2, 6)
TABLE = "= '"TABLE"'"
end
end

call init_db2

return

traitement:

call preparer_table
call preparer_colonnes
call preparer_index
call preparer_foreign_keys
call preparer_sequence
if TABLE6 ^= "" then call preparer_packages

return

fin:

call fin_db2

return

preparer_table:

req = "select name, creator, type, remarks, dbname, "
req = req!!"tsname, colcount, parents, children, keycolumns, "
req = req!!"status, tablestatus, label, checkflag "
req = req!!"from sysibm.systables "
req = req!!"where name "!!TABLE
req = req!!" and creator = '"!!CREATOR!!"' "

ligne_titre = "Table Creator Remarque"
ligne_valeur = 'tb" "creator""rmk'
fetch_into = ":tb, :creator, :type, :rmk, :dbname, :tsname, "
fetch_into = fetch_into!!":nbcol, :parent, :enfant, :colclef, "
fetch_into = fetch_into!!":status, :etattab, :label, :chkflag"
/csv_t = "Table;Creator;Type;Remarque;DBName;TSName;"
csv_t = csv_t!!"Nombre de colonnes;Nb colonnes dans Clef primaire;"
csv_t = csv_t!!"Parents;Enfants;Libelle;Statut;Etat;Check Flag"
detail.0 = 8
detail.1 = '"Table = "tb" - Dbname/TableSpace = "dbname"/"tsname'
detail.1 = detail.1!!'" / Creator = "creator'
detail.2 = '"Detail = "rmk'
detail.3 = '"Libelle = "label'
select
when type = 'A' then ltype = 'Alias'
when type = 'C' then ltype = 'Table clone'
when type = 'G' then ltype = 'Global temporary table'
when type = 'M' then ltype = 'Query table materialisee'
when type = 'P' then ltype = 'Table implicite pour colonnes XML'
when type = 'T' then ltype = 'Table'
when type = 'V' then ltype = 'Vue'
when type = 'X' then ltype = 'Table auxiliaire'
otherwise ltype = type!!' pas compris'
end
detail.4 = '"Type = "ltype" - Parent = "parent" - Enfant = "enfant'
detail.5 = '"Nb Col = "nbcol" - Clef primaire = "colclef" colonnes"'
select
when status = 'I' then lstatus = 'Definition incomplete'
when status = 'R' then lstatus = 'Erreur regeneration'
when status = 'X' then lstatus = 'OK'
when status = '' then lstatus = 'Ok mais pas de clef primaire'
otherwise lstatus = status!!' pas compris'
end
select
when etattab = 'L' then etattab = 'Manque table/index auxiliaire'
when etattab = 'P' then etattab = 'Manque clef primaire'
when etattab = 'R' then etattab = 'Manque index sur champ ID'
when etattab = 'U' then etattab = 'Manque index sur clef unique'
when etattab = 'V' then etattab = 'Erreur regeneration vue'
when etattab = '' then etattab = 'OK'
otherwise etattab = etattab!!' pas compris'
end
select
when chkflag = 'C' then do
chkflag = 'Attention CHEKPENDING ou '
chkflag = chkflag!!'donnees incoherentes'
end
when chkflag = '' then chkflag = 'OK'
otherwise chkflag = chkflag!!' pas compris'
end
detail.6 = '"Statut = "lstatus'
detail.7 = '"Etat table = " etattab'
detail.8 = '"Checl flag = " chkflag'/

call traiter_requete
/do i = 1 to detail.0
interpret say detail.i
end
say ""/

return

preparer_colonnes:

req = "select c.name, c.colno, c.coltype, c.length, c.nulls, "
req = req!!"c.remarks "
req = req!!"from sysibm.syscolumns c "
req = req!!"where c.tbname "!!TABLE
req = req!!" and c.tbcreator = '"!!CREATOR!!"' "
req = req!!"order by c.colno"

ligne_titre = "NCol Colonne Type Lgr Null"
ligne_titre = ligne_titre!!" Remarks"
ligne_valeur = 'right(ncol,4)" "left(col,20)'
ligne_valeur = ligne_valeur'" "left(type,8)'
ligne_valeur = ligne_valeur'" "right(lgr,5)" "null" " rmk'
fetch_into = ":col, :ncol, :type, :lgr, :null, :rmk"

call traiter_requete

return

preparer_index:

req = "select t.name, i.name, c.name, i.colcount, k.colseq, "
req = req!!"c.coltype, c.length, c.nulls, i.uniquerule, k.ordering, "
req = req!!"c.colno "
req = req!!"from sysibm.systables t "
req = req!!" inner join sysibm.syscolumns c "
req = req!!" on c.tbname = t.name "
req = req!!" and c.tbcreator = t.creator "
req = req!!" inner join sysibm.sysindexes i "
req = req!!" on i.tbname = t.name "
req = req!!" and i.tbcreator = t.creator "
req = req!!" inner join sysibm.syskeys k "
req = req!!" on k.colname = c.name "
req = req!!" and k.colno = c.colno "
req = req!!" and k.ixname = i.name "
req = req!!" and k.ixcreator = i.creator "
req = req!!"where t.name"!!TABLE
req = req!!"and t.creator = '"!!CREATOR!!"' "
req = req!!"order by t.name, i.name, k.colseq"

ligne_titre = "Table Index Colonne Ordre Type "
ligne_titre = ligne_titre!!" Lgr Null Uniq Tri Ncol"
ligne_valeur = 'tb" "idx" "left(col, 20)" "strip(nseq)"/"'
ligne_valeur = ligne_valeur'strip(nbcol)" "left(typ,8)" "right(lgr,4)'
ligne_valeur = ligne_valeur'" "nul" "unq" "right(nord,4)'
ligne_valeur = ligne_valeur'" "right(ncol,4)'
fetch_into = ":tb, :idx, :col, :nbcol, :nseq, :typ, :lgr, :nul, :unq"
fetch_into = fetch_into!!", :nord, :ncol"

call traiter_requete

return

preparer_foreign_keys:

req = "select substr(f.relname, 1, 8), r.colcount, "
req = req!!"f.colseq, substr(r.reftbname, 1, 8), "
req = req!!"substr(c.tbname, 1, 8), substr(c.name, 1, "
req = req!!"20), r.deleterule, r.checkexistingdata "
req = req!!"from sysibm.syscolumns c "
req = req!!" inner join sysibm.sysforeignkeys f "
req = req!!" on f.tbname = c.tbname "
req = req!!" and f.creator = c.tbcreator "
req = req!!" and f.colname = c.name "
req = req!!" inner join sysibm.sysrels r "
req = req!!" on r.tbname = f.tbname "
req = req!!" and r.creator = f.creator "
req = req!!" and r.relname = f.relname "
req = req!!"where (c.tbname"!!TABLE
req = req!!"and c.tbcreator = '"!!CREATOR!!"') "
req = req!!"union "
req = req!!"select substr(f.relname, 1, 8), r.colcount, "
req = req!!"f.colseq, substr(r.reftbname, 1, 8), "
req = req!!"substr(c.tbname, 1, 8), substr(c.name, 1, "
req = req!!"20), r.deleterule, r.checkexistingdata "
req = req!!"from sysibm.syscolumns c "
req = req!!" inner join sysibm.sysforeignkeys f "
req = req!!" on f.tbname = c.tbname "
req = req!!" and f.creator = c.tbcreator "
req = req!!" and f.colname = c.name "
req = req!!" inner join sysibm.sysrels r "
req = req!!" on r.tbname = f.tbname "
req = req!!" and r.creator = f.creator "
req = req!!" and r.relname = f.relname "
req = req!!"where (r.reftbname"!!TABLE
req = req!!"and r.reftbcreator = '"!!CREATOR!!"') "
req = req!!"order by 1 asc, 2 asc"

ligne_titre = "FKey NbCol Seq TbMaitre TbFille Colonne "
ligne_titre = ligne_titre!!" PropagDel VerfImmediat"
ligne_valeur = 'fkey" "right(nbcol,5)" "right(seq, 3)" "'
ligne_valeur = ligne_valeur'tb_maitre" "tb_fille'
ligne_valeur = ligne_valeur'" "left(col, 20)" "left(propagation, 10)'
ligne_valeur = ligne_valeur'" "verif_immediate'
fetch_into = ":fkey, :nbcol, :seq, :tb_maitre, :tb_fille, :col, "
fetch_into = fetch_into!!":propagation, :verif_immediate"

call traiter_requete

return

preparer_packages:

/*req = "select d.bname, d.btype, d.dname, p.creator, p.bindtime, "
req = req!!"p.pctimestamp, p.pdsname, p.dynamicrules "
req = req!!"from sysibm.syspackdep d inner join sysibm.syspackage p "
req = req!!"on p.name = d.dname and p.owner = d.bqualifier "
req = req!!"where substr(bname, 2, 6) = '"!!TABLE6!!"'"
req = req!!"and bqualifier = '"!!CREATOR!!"' "
req = req!!"order by d.bname asc, d.dname asc"

ligne_titre = "Objet Type Composant"
ligne_valeur = 'substr(obj, 1, 8, " ")" "type" "composant'
fetch_into = ":obj, :type, :composant, :creator, :bnd, :pcts, :pds, "
fetch_into = fetch_into!!":dynamrul"*/

req = "select distinct d.dname, hex(p.contoken), p.pctimestamp, "
req = req!!"p.pdsname "
req = req!!"from sysibm.syspackdep d inner join sysibm.syspackage p "
req = req!!"on p.name = d.dname and p.owner = d.bqualifier "
req = req!!"where substr(bname, 2, 6) = '"!!TABLE6!!"'"
req = req!!"and bqualifier = '"!!CREATOR!!"' "

ligne_titre = "Accesseurs ConToken DB2 ConToken Load "
ligne_titre = ligne_titre!!" PCTimeStamp"
ligne_valeur = 'obj" "ct" "right(ct, 8)left(ct, 8)" "pcts'
ligne_valeur = ligne_valeur!!'" "pdsn'
fetch_into = ":obj, :ct, :pcts, :pdsn"

call traiter_requete

return

preparer_sequence:

req = "select s.name, s.seqtype, s.sequenceid, s.increment, "
req = req!!"s.start, s.maxvalue, s.minvalue, s.cycle, "
req = req!!"s.maxassignedval, s.remarks, s.precision, s.restartwith "
req = req!!"from sysibm.syssequences s "
req = req!!"where substr(name, 2, 6) = '"!!TABLE6!!"'"
req = req!!"and schema = '"!!CREATOR!!"' "

ligne_titre = "Sequence Type Id Incr Start "
ligne_titre = ligne_titre!!"Cycle AssignMax Restart "
ligne_titre = ligne_titre!!"MinVal MaxVal"
ligne_valeur = 'name" "type" "seqid" "incr" "'
ligne_valeur = ligne_valeur'start" "cycle" "'
ligne_valeur = ligne_valeur'maxassignv" "restart" "'
ligne_valeur = ligne_valeur'minv" "maxv'
fetch_into = ":name, :type, :seqid, :incr, :start, :maxv, :minv, "
fetch_into = fetch_into!!":cycle, :maxassignv :maxassignvnull, "
fetch_into = fetch_into!!":remarks, :precis, :restart :restartnull"

call traiter_requete

return

traiter_requete:

say ligne_titre

call open_curseur
do until sqlcode = -100 ! sqlcode = 100
call fetch_curseur
if sqlcode = 0 then do
interpret say ligne_valeur
end
end
call close_curseur
say ""

return

init_db2:

call connect_db2

return

fin_db2:

call disconnect_db2

return

connect_db2:

address tso "SUBCOM DSNREXX"
if rc = 1 then s_rc = rxsubcom('ADD','DSNREXX','DSNREXX')

address DSNREXX "CONNECT" SSID
if sqlcode ^= 0 then call sqlca

return

disconnect_db2:

address DSNREXX "DISCONNECT"
s_rc = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
if sqlcode ^= 0 then call sqlca

return

exec_req:

address dsnrexx "execsql" req
if sqlcode <> 0 then call sqlca

return

open_curseur:

declare_cursor = "declare c1 cursor for s1"
address dsnrexx "execsql "!!declare_cursor
if sqlcode <> 0 then call sqlca
address dsnrexx "execsql prepare s1 from :req"
if sqlcode <> 0 then call sqlca
address dsnrexx "execsql open c1"
if sqlcode ^= 0 then call sqlca

return

fetch_curseur:

address dsnrexx "execsql fetch c1 into" fetch_into
if sqlcode = 0 then return
if sqlcode = +100 then return
if sqlcode = -100 then return

call sqlca

return

close_curseur:

address dsnrexx "execsql close c1"
if sqlcode <> 0 then call sqlca

return

sqlca:

address tso
say 'SQLCODE =' SQLCODE
say 'SQLSTATE =' SQLSTATE
say 'SQLERRP =' SQLERRP
say 'SQLERRMC =' SQLERRMC
do i=0 to 9
say 'SQLWARN'I' =' SQLWARN.i
end
say 'SQLWARN10=' SQLWARN.10
do i=1 to 6
say 'SQLERRD'I' =' SQLERRD.i
end

exit 12