1;M;Tables/Views within Database;Select distinct tabschema from syscat.tables;Schema;1500;
2;M;Tables (only) within Database;Select distinct tabschema from syscat.tables where type = 'T';Schema;1200;
3;M;Packages within Database;Select distinct pkgschema from syscat.packages;Schema;1200;
4;M;Triggers within Database;Select distinct trigschema from syscat.triggers;Schema;1200;
5;M;Functions within;Select DISTINCT FUNCSCHEMA from SYSCAT.FUNCTIONS;Schema;1500;
6;M;Indexes within Database;Select distinct indschema from SYSCAT.INDEXES;Schema;1200;
7;M;Users within Database;Select distinct grantee from (select distinct grantee from SYSCAT.TABAUTH where GRANTEETYPE='U' union select distinct grantee from syscat.packageauth where GRANTEETYPE='U' union select distinct grantee from syscat.dbauth where GRANTEETYPE='U') as a;Userid;1275;
8;M;PK Columns for;SELECT kc.COLNAME, c.TYPENAME, c.LENGTH, c.SCALE FROM SYSCAT.KEYCOLUSE kc, SYSCAT.TABCONST tc, SYSCAT.COLUMNS c WHERE kc.CONSTNAME=tc.CONSTNAME AND kc.TABSCHEMA=tc.TABSCHEMA AND kc.TABNAME=tc.TABNAME AND kc.TABSCHEMA=c.TABSCHEMA AND kc.TABNAME=c.TABNAME AND kc.COLNAME=c.COLNAME AND kc.TABSCHEMA = ? AND kc.TABNAME = ? AND tc.TYPE = 'P' ORDER BY kc.COLSEQ;Column;630;Type;465;Length;650;Scale;500;
9;M;Views within Database;Select distinct creator from SYSIBM.SYSTABLES where type = 'V';Schema;1200;
10;M;Tables/Views within Schema;Select tabname, type, case status when 'N' then ''else status end, substr(remarks,1,100) from syscat.tables where tabschema = ? order by 1;Table;2625;Type;500;Status;550;Desc;495;
11;S;Full details of;Select * from syscat.tables where tabschema = ? and tabname = ?;Column;1800;Value;4035;
12;M;Indexes on;Select indschema,indname,uniquerule,colnames from syscat.indexes where tabschema = ? and tabname=? order by indname;Schema;705;Index;2070;Type;500;Columns;3810;
13;M;Objects comprising View;Select BSCHEMA, BNAME, BTYPE,BNAME from SYSCAT.VIEWDEP where viewschema=? and viewname=? order by 1,2;Schema;1275;Name;2625;Type;500;Id;1;
14;M;Packages using;Select a.pkgschema,a.pkgname, b.remarks,a.tabauth from SYSCAT.PACKAGEDEP a, syscat.packages b where a.pkgschema=b.pkgschema and a.pkgname=b.pkgname and bschema = ? and bname=? order by 1,2;Pkg Schema;1095;Pkg Name;900;Desc;495;
15;M;Access list for;Select distinct grantee, granteetype, selectauth, updateauth, insertauth, deleteauth, alterauth, refauth, grantor from sysibm.systabauth where tcreator = ? and ttname=? order by grantee;Userid;960;Type;500;Select;585;Update;630;Insert;510;Del;390;Alter;405;Ref;390;Granted By;960;
16;M;Views on;Select VIEWSCHEMA, VIEWNAME from SYSCAT.VIEWDEP where bschema=? and bname=? order by 1,2;View Schema;1170;View Name;975;
17;W;Definition of view;Select TEXT from SYSCAT.VIEWS WHERE VIEWSCHEMA=? AND VIEWNAME=? order by seqno;View Text;10380;
18;M;Functions within schema;Select FUNCNAME,REMARKS, specificname from SYSCAT.FUNCTIONS WHERE FUNCSCHEMA = ? order by 1;Name;1365;Desc;3600;Internal Name;1;
19;M1;Parameters for function;select TYPENAME, ORDINAL, LENGTH, SCALE, ROWTYPE, CAST_FUNCID, PARMNAME From SYSCAT.FUNCPARMS Where specificname = ? order by ordinal;Type;930;Seq;360;Length;645;Scale;500;Row Type;900;Cast as;660;Desc;465;
20;C;Column list for;SELECT COLNAME, TYPENAME, length, SCALE, NULLS,remarks,COLNO,default FROM SYSCAT.COLUMNS WHERE TABSCHEMA=? and TABNAME=? order by colno;Name;1200;Type;1185;Length;645;Scale;500;Nulls;500;Desc;495;Default;615;
21;S;Details of package;Select * from syscat.packages where pkgschema = ? and pkgname = ?;Column;2000;Value;3300;
22;M;Objects used by Package;Select bschema,bname, btype,tabauth,bname from SYSCAT.PACKAGEDEP where pkgschema=? and pkgname=? order by 1,2;Schema;1500;Name;2000;Type;1000;Tabauth;800;Id;1;
23;M;Statement in package;Select SECTNO,STMTNO, SEQNO, TEXT from SYSCAT.STATEMENTS where pkgschema=? and pkgname=? order by 1,2,3;Section;700;Stmt;600;Seq;500;Text;8000;
24;M;Access list for package;Select distinct GRANTEE, GRANTEETYPE, executeauth, BINDAUTH, CONTROLAUTH from SYSCAT.PACKAGEAUTH where pkgschema=? and pkgname=? order by grantee;Id;1500;User/Group;1000;Execute;700;Bind;700;Control;700;
25;T;Details of trigger;Select * from SYSCAT.TRIGGERS WHERE trigschema = ? and TRIGNAME =?;Column;2000;Value;3000;
26;M;Triggers on table;Select TRIGSCHEMA,TRIGNAME,VALID,Trigevent,TRIGTIME from SYSCAT.TRIGGERS WHERE TABSCHEMA=? AND TABNAME=?;Schema;705;Name;540;Valid;450;Event;540;Time;450;
27;M;Triggers involving table;Select a.TRIGSCHEMA, a.TRIGNAME, b.tabschema,b.tabname,b.valid from SYSCAT.TRIGDEP a, syscat.triggers b where a.trigschema=b.trigschema and a.trigname=b.trigname and a.bschema=? AND BNAME=?;Trg Schema;1500;Trg Name;1000;Tab Schema;1200;Tab Name;1800;Valid;600;
28;M;Packages using;Select a.pkgschema,a.pkgname, b.remarks,a.tabauth from SYSCAT.PACKAGEDEP a, syscat.packages b where a.pkgschema=b.pkgschema and a.pkgname=b.pkgname and bschema = ? and bname=? order by 1,2;Pkg Schema;1500;Pkg Name;2000;Desc;5000;
30;M;Check Constraints on;Select CONSTNAME, TEXT from SYSCAT.CHECKS where TABSCHEMA=? and TABNAME=?;Constraint Name;1410;Text;405;
31;M;Tables (only) within Schema;Select tabname, 'T', case status when 'N' then ''else status end, parents, children, substr(remarks,1,50) from syscat.tables where tabschema = ? and type='T' order by 1;Table;2500;Type;1;Status;550;Ref Tabs (parents);1500;Dep Tabs (children);1500;Desc;4000;
32;M;Packages within Schema;Select PKGNAME, valid, remarks, LAST_BIND_TIME,TOTAL_SECT from SYSCAT.PACKAGES where pkgschema=? order by 1;Name;1335;Valid;500;Desc;495;
33;M;Triggers within Schema;Select TRIGNAME,VALID, REMARKS from SYSCAT.TRIGGERS where trigschema=? order by 1;Name;840;Valid;500;Desc;495;
34;D;Contents of;Select TRIGNAME,VALID, REMARKS from SYSCAT.TRIGGERS where trigschema=? order by 1;Name;1500;Valid;500;Desc;2500;
35;M;Referenced Tables for;Select REFTABSCHEMA, REFTABNAME, FK_COLNAMES, PK_COLNAMES, deleterule from SYSCAT.REFERENCES WHERE TABSCHEMA =? and TABNAME=?;Schema;720;Ref Table;840;Cols (FK);750;PK in Ref Tab;1200;Delete Rule;1000;
36;M;Dependent Tables for;Select TABSCHEMA, TABNAME, PK_COLNAMES, FK_COLNAMES,deleterule from SYSCAT.REFERENCES WHERE REFTABSCHEMA =? and REFTABNAME=?;Schema;705;Dep Table;900;Cols (PK);795;FK in Dep Tab;1245;Delete Rule;1000;
37;M;Dependent objects for trigger;Select BSCHEMA, BNAME, BTYPE, bname WHERE from SYSCAT.TRIGDEP WHERE TRIGSCHEMA=? AND TRIGNAME=?;Schema;1500;Name;2000;Type;600;Id;1;
38;M;Tables within Schema;Select TABNAME,type,CARD,NPAGES,(case when card=-1 then 0 else card end)/(case when npages=0 then 1 else npages end),t.tbspace,ts.pagesize,stats_time from SYSCAT.TABLES t, SYSCAT.TABLESPACES ts WHERE t.tbspaceid=ts.tbspaceid and t.TABSCHEMA=? and t.type='T' order by npages desc;Table;3600;Type;1;Rows;645;Pages;585;Rows/Pg;800;Tablespace;1515;Pg Size;645;Stats Time;1785;
39;M;Column sizing for;Select COLNAME,COLCARD,LENGTH,AVGCOLLEN,LOW2KEY,HIGH2KEY, colno from SYSCAT.COLUMNS WHERE TABSCHEMA =? and TABNAME=? order by colno;Name;2400;Distinct;645;Length;645;Avg Len;720;2nd Lowest Value;2400;2nd Highest Value;2340;
40;M;Tablespaces within database;Select distinct tbspace,tbspacetype,datatype,remarks from SYSCAT.TABLESPACES order by 1;Tablespace;1650;SMS/DMS;930;Datatype;800;Remarks;3570;
41;S;Details of tablespaces;Select * from SYSCAT.TABLESPACES where tbspace=?;Column;1725;Value;2490;
42;M;Indexes within Schema;Select iNDNAME,uniquerule,TABSCHEMA,TABNAME from SYSCAT.INDEXES WHERE INDSCHEMA=? order by 1;Name;2265;Type;450;Tab Schema;1095;Tab Name;3060;
43;S;Details of Index;Select * from SYSCAT.INDEXES WHERE INDSCHEMA=? AND INDNAME=?;Column;2000;Value;6405;
44;M;Tables within Tablespace;Select TABSCHEMA,TABNAME,NPAGES,CARD from SYSCAT.TABLES WHERE TBSPACE=? order by 1,2;Tab Schema;1095;Tab Name;900;Pages;600;Rows;525;
45;M;Table authorities for;Select distinct TABSCHEMA,TABNAME,SELECTAUTH, UPDATEAUTH,INSERTAUTH, DELETEAUTH, ALTERAUTH, CONTROLAUTH, REFAUTH, INDEXAUTH from SYSCAT.TABAUTH WHERE GRANTEETYPE='U' and GRANTEE=? ORDER BY 1,2;Schema;1200;Name;2000;Select;600;Update;600;Insert;510;Delete;600;Alter;405;Control;600;References;990;Index;510;
46;M;Package authorities for;Select distinct PKGSCHEMA,PKGNAME,EXECUTEAUTH,BINDAUTH, CONTROLAUTH from SYSCAT.PACKAGEAUTH WHERE GRANTEETYPE='U' and GRANTEE=?;Schema;975;Package;1050;Execute;705;Bind;405;Control;645;
47;S;Database authorities for;Select distinct CONNECTAUTH,CREATETABAUTH,BINDADDAUTH,DBADMAUTH,NOFENCEAUTH, IMPLSCHEMAAUTH from SYSCAT.DBAUTH WHERE GRANTEETYPE='U' and GRANTEE=?;Authority;1800;Grant Auth?;1020;
48;V;Definition of view;Select TEXT from SYSCAT.VIEWS WHERE VIEWSCHEMA=? AND VIEWNAME=? order by seqno;a;3150;
49;M;Views within Schema;Select name, type, case status when 'N' then ''else status end, substr(remarks,1,100) from sysibm.systables where creator = ? and type = 'V' order by 1;View;2000;Type;1;Status;800;Desc;4000;
50;B;Table authorities for;Select TABSCHEMA,TABNAME,GRANTOR,SELECTAUTH, UPDATEAUTH,INSERTAUTH, DELETEAUTH, ALTERAUTH, CONTROLAUTH, REFAUTH, INDEXAUTH from SYSCAT.TABAUTH WHERE GRANTEETYPE='U'and GRANTEE=? ORDER BY 1,2;Schema;1155;Name;3060;Grantor;720;Select;600;Update;600;Insert;510;Delete;600;Alter;405;Control;600;References;990;Index;510;
51;X;Text of trigger;Select TEXT from SYSCAT.TRIGGERS WHERE trigschema = ? and TRIGNAME =?;Text;6825;
52;X1;Text of function;Select BODY from SYSCAT.FUNCTIONS where specificname = ?;Text;5895;
53;O;Title;D;Schema;800;Table;1800;Cols (PK);2000;FK in Dep Tab;2000;
54;O;Title;F;Schema;800;Table;1800;Cols (FK);2000;PK in Ref Tab;2000;
55;F;Column List for Alias;SELECT col.COLNAME, col.TYPENAME, col.LENGTH, col.SCALE, col.NULLS, col.REMARKS, col.COLNO, col.DEFAULT FROM SYSCAT.COLUMNS col, SYSCAT.TABLES tab WHERE col.TABSCHEMA=tab.BASE_TABSCHEMA AND col.TABNAME=tab.BASE_TABNAME and tab.tabschema=? and tab.tabname = ? order by col.COLNO;Schema;1500;Name;2000;Type;1000;Tabauth;800;
56;M;Table/Index Sizing;sELECT ts.tbspace,coalesce(tab.np,-1), coalesce(ind.np,-1), coalesce(tab.n,0), coalesce(tab.tyes,0), coalesce(ind.n,0), coalesce(ind.iyes,0) FROM syscat.tablespaces ts left outer join (select coalesce(tab.INDEX_TBSPACE, tab.TBSPACE) as ts, count(*) as n, sum(case when ind.nleaf = -1 then 0 else 1 end) as iyes, sum(case when ind.NLEAF = -1 then null else ind.nleaf end) as np from SYSCAT.INDEXES ind, SYSCAT.TABLES tab WHERE ind.TABSCHEMA=tab.TABSCHEMA AND ind.TABNAME=tab.TABNAME group by coalesce(tab.INDEX_TBSPACE, tab.TBSPACE)) as ind on ts.tbspace=ind.ts left outer join ( SELECT TBSPACE as ts, count(*) as n, sum(case when Npages=-1 then 0 else 1 end) as tyes, sum(case when NPAGES=-1 then null else npages end) as np FROM SYSCAT.TABLES where tbspace is not null GROUP BY TBSPACE ) as tab on ts.tbspace = tab.ts ;Tablespace;1785;Tot Tab Pages;1275;Tot Idx Pages;1185;# Tables;750;# Tabs w/Stats;1335;# Indexes;840;# Idxs w/Stats;1200;
57;M;Tablespaces within database;SELECT distinct tab.TBSPACE, tab.TBSPACEID, tab.PAGESIZE, buf.BPNAME, tab.NGNAME FROM SYSCAT.TABLESPACES tab, SYSCAT.BUFFERPOOLS buf WHERE tab.BUFFERPOOLID=buf.BUFFERPOOLID order by 2;Tablespace;1650;Id;390;Page Size;900;Buffer Pool;1500;Node Group;1920;
58;M;Tables within Tablespace;SELECT a.ts, count(*), sum(np) FROM (select coalesce(tab.INDEX_TBSPACE, tab.TBSPACE) as ts, case when ind.NLEAF = -1 then 0 else ind.nleaf end as np from SYSCAT.INDEXES ind, SYSCAT.TABLES tab WHERE ind.TABSCHEMA=tab.TABSCHEMA AND ind.TABNAME=tab.TABNAME) as a group by a.ts;Tablespace;1650;Num Indexes;1110;Total Index Pages;1545;
59;M;Indexes in Tablespace;select INDSCHEMA ,INDNAME, FULLKEYCARD, nleaf, tab.TABSCHEMA ,tab.TABNAME from SYSCAT.INDEXES ind, SYSCAT.TABLES tab WHERE ind.TABSCHEMA=tab.TABSCHEMA AND ind.TABNAME=tab.TABNAME and coalesce(tab.INDEX_TBSPACE, tab.TBSPACE) = ? order by 1 ,2;Index Schema;1200;Index Name;1020;Num Key Vals;1185;Num Pages;1000;Tab Schema;1095;Tab Name;2625;
60;M;Bufferpools within database;Select Bpname, Bufferpoolid, Npages, Pagesize, Estore, Ngname, Bufferpoolid From Syscat.Bufferpools;BP Name;1740;Id;390;# Pages;720;Pg Size;645;Extended Storage?;1500;Node Group;1000;Id;1;
61;M1;Tables in Bufferpool;SELECT t.TABSCHEMA, t.TABNAME, t.NPAGES, t.TBSPACE FROM SYSCAT.TABLES t, SYSCAT.TABLESPACES ts WHERE t.TBSPACEID=ts.TBSPACEID AND ts.BUFFERPOOLID = ? AND t.TBSPACEID <> 0 ORDER BY 1, 2;Tab Schema;1095;Tab Name;2280;Pages;585;Tablespace;1245;
62;M1;Indexes in Bufferpool;SELECT i.INDSCHEMA, i.INDNAME, i.NLEAF, t.TABSCHEMA, t.TABNAME, coalesce(t.index_tbspace,t.TBSPACE) FROM SYSCAT.INDEXES i, SYSCAT.TABLES t, SYSCAT.TABLESPACES ts WHERE i.TABSCHEMA=t.TABSCHEMA AND i.TABNAME=t.TABNAME AND t.TBSPACEID=ts.TBSPACEID AND ts.BUFFERPOOLID = ? ORDER BY 1, 2;Idx Schema;1000;Idx Name;885;Leaf Pgs;750;Tab Schema;1095;Tab Name;2610;Tablespace;1005;
63;M;Tblspc User Auths;Select distinct grantee,granteetype,useauth,grantor from SYSCAT.TBSPACEAUTH where tbspace=?;Id;1200;User/Group;1000;Authority;800;Granted By;1000;
64;M;RI for table;select schema,name,tabschema,tabname,fkcol,reftabschema, reftabname, pkcol,deleterule,updaterule, constname from (select c.tabschema SCHEMA,c.tabname NAME, c.tabschema, c.tabname, substr(c.fk_colnames,1,255) fkcol, c.reftabschema, c.reftabname, substr(c.pk_colnames,1,255) pkcol, c.deleterule, c.updaterule, c.constname from syscat.references c union select p.reftabschema SCHEMA,p.reftabname NAME, p.tabschema, p.tabname, substr(p.fk_colnames,1,255) fkcol, p.reftabschema, p.reftabname, substr(p.pk_colnames,1,255) pkcol, p.deleterule, p.updaterule, p.constname from syscat.references p) a where a.schema=? and a.name=?;Schema;1;Table;1;Dep Schema;1110;Dep Table (child);1995;Dep Cols (FK);3600;Ref Schema;1065;Ref Table (parent);2100;Ref Cols (PK);3600;Del Rule;700;Upd Rule;810;Constraint Name;1995;
65;M;Index Access List;select grantee, controlauth, grantor from syscat.indexauth where indschema=? and indname=? order by 1;Userid;1080;Control;645;Granted By;960;
66;M;Index Columns;SELECT ic.COLNAME, ic.COLORDER, c.TYPENAME, c.LENGTH, c.AVGCOLLEN, c.COLCARD,ic.COLSEQ FROM SYSCAT.INDEXCOLUSE ic, SYSCAT.INDEXES i, SYSCAT.COLUMNS c WHERE ic.INDSCHEMA=i.INDSCHEMA AND ic.INDNAME=i.INDNAME AND i.TABNAME=c.TABNAME AND ic.COLNAME=c.COLNAME AND i.INDSCHEMA = ? AND i.INDNAME = ? ORDER BY ic.COLSEQ;Column Name;2500;Asc/Desc;500;Type;1200;Length;800;Avg Len;800;Distinct;1000;
67;M;Column List;select colname, count(*), colname from syscat.columns where tabschema=? group by colname;Column Name;3405;Num Tables/Views;1620;Cname;1;
68;M;Procedures within database;Select distinct procschema from syscat.procedures;Schema;1500;
69;M;Procedures within Schema;Select PROCNAME, PARM_COUNT, REMARKS,SPECIFICNAME from SYSCAT.PROCEDURES where procschema=? order by 1;Name;2200;Num Parms;975;Desc;495;Internal name;1;
70;S1;Details of procedure;Select * from syscat.procedures where SPECIFICNAME = ?;Column;2000;Value;3000;
71;P1;Parameters for procedure;select PARMNAME, PARM_MODE, TYPENAME, LENGTH, SCALE, AS_LOCATOR From SYSCAT.PROCPARMS Where SPECIFICNAME=? Order By ORDINAL;Parm;1500;Mode;800;Type;1000;Length;800;Scale;800;AS Locator;800;
72;X1;Details of procedure;Select text from syscat.procedures where SPECIFICNAME = ?;Text;11325;
73;Q;View as Query;Select TEXT from SYSCAT.VIEWS WHERE VIEWSCHEMA=? AND VIEWNAME=? order by seqno;View Text;12000;
74;M1;Tables with column;SELECT c.TABSCHEMA, c.TABNAME, tab.TYPE, c.TYPENAME, c.LENGTH, c.SCALE, c.NULLS, c.DEFAULT FROM SYSCAT.COLUMNS c, SYSCAT.TABLES tab WHERE c.TABSCHEMA=tab.TABSCHEMA AND c.TABNAME=tab.TABNAME AND c.COLNAME = ? order by 1, 2;Schema;1200;Table;1800;Tab Type;800;Col Type;930;Length;650;Scale;500;Nulls;500;Default;615;
75;M;Indexes with column;select ic.indschema, ic.indname, i.tabname, ic.colseq,ic.colorder from syscat.indexcoluse ic, syscat.indexes i where ic.indschema=? and ic.colname=? and i.indschema=ic.indschema and i.indname = ic.indname order by 1,2;Ind Schema;1000;Ind Name;825;Tab Name;900;Col Nbr;630;Order;510;
76;M;Orphan Aliases;select tabschema,tabname, base_tabschema, base_tabname from syscat.tables where type='A' and (base_tabschema,base_tabname) not in (select tabschema,tabname from syscat.tables) and tabschema = ?;Alias Schema;1;Alias Name;960;Base Schema;1185;Base Name;990;
77;M;Indexes on;select INDSCHEMA,INDNAME, COALESCE(R1,R2) AS GROUP, FULLKEYCARD, sum(case when nleaf=-1 then 0 else nleaf end), nlevels, clusterratio,density,stats_time, colnames from SYSCAT.INDEXES, (VALUES(' ','TOTAL')) AS X(R1,R2) WHERE tabschema=? and tabname=? group by grouping sets ((r1,indschema,indname,fullkeycard,nlevels,clusterratio,density,colnames,stats_time), (r2) ) order by 4;Ind Schema;1020;Ind Name;1830;;600;# Key Vals;900;Leaf Pgs;750;Level;510;Clust %;600;Density %;800;Stats Time;1785;Columns;12000;
78;M;Schema authorities for;Select distinct SCHEMANAME,CREATEINAUTH,ALTERINAUTH,DROPINAUTH from SYSCAT.SCHEMAAUTH WHERE GRANTEETYPE='U' and GRANTEE=?;Schema;1005;Create;600;Alter;405;Drop;450;
79;M;Index Sizing;select INDNAME, FULLKEYCARD, nleaf, nlevels, clusterratio, tabschema, tabname,stats_time from SYSCAT.INDEXES WHERE tabschema=? order by nleaf desc;Index;2500;# Key Vals;900;Leaf Pgs;750;Levels;600;Clust %;600;Tab Schema;1095;Tab Name;2715;Stats Time;1785;
80;M1;Sizing of column in;SELECT col.TABSCHEMA, col.TABNAME, col.COLCARD, col.LENGTH, col.AVGCOLLEN, col.LOW2KEY, col.HIGH2KEY FROM SYSCAT.COLUMNS col, SYSCAT.TABLES tab WHERE col.TABSCHEMA=tab.TABSCHEMA AND col.TABNAME=tab.TABNAME AND col.COLNAME = ? AND tab.TYPE = 'T';Schema;1200;Table;2000;Distinct;645;Length;720;Avg Len;720;2nd Lowest Value;1500;2nd Highest Value;1500;
81;M;Multi-Schema Columns in;SELECT colname, count(distinct TABSCHEMA),colname FROM SYSCAT.COLUMNS where colname in (select distinct colname from syscat.columns where tabschema=?) group by colname having count(distinct TABSCHEMA)>1;Column;3000;Num Schemas;1000;Col;1;
82;M;Columns within Database;Select distinct tabschema from syscat.tables;Schema;1500;
83;M1;Objects used by Function;Select BSCHEMA ,BNAME ,BTYPE ,TABAUTH, bname from SYSCAT.FUNCDEP WHERE FUNCNAME=? order by 1,2;Schema;705;Name;540;Type;465;Tabauth;720;Id;1;
84;M;Title;select colname, count(*), colname from syscat.columns where tabschema=? group by colname having count(*) >1;Column Name;2640;Num Tables/Views;1000;Cname;1;
85;M;Tablespace authorities for;Select distinct TBSPACE, USEAUTH, grantor from SYSCAT.TBSPACEAUTH WHERE GRANTEETYPE='U' and GRANTEE=? order by 1;Tablespace;1600;Authority;1000;Grantor;1200;
86;M;Nodegroups within database;Select ngname, pmap_id, rebalance_pmap_id,remarks from SYSCAT.NODEGROUPS;NG Name;2190;Pmap Id;700;Rebalance Pmap Id;1695;Description;960;
87;M;Nodes in Nodegroup;SELECT nodenum, in_use FROM SYSCAT.NODEGROUPDEF WHERE ngname = ? ORDER BY 1;Node Num;915;In Use;600;
88;M;Tablespaces in Nodegroup;SELECT tab.TBSPACE, buf.BPNAME FROM SYSCAT.TABLESPACES tab, SYSCAT.BUFFERPOOLS buf WHERE tab.BUFFERPOOLID=buf.BUFFERPOOLID AND tab.NGNAME = ?;Tablespace;1500;Bufferpool;1500;
89;M;Nodegroup-specific Bufferpools;Select BPNAME, NPAGES from SYSCAT.BUFFERPOOLS WHERE NGNAME= ?;Bufferpool;1500;Pages;1000;
102;M;Functions within schema;Select FUNCNAME, specificname from SYSCAT.FUNCTIONS WHERE FUNCSCHEMA = ? order by 1;Name;2460;Internal Name;2190;
103;M1;Views Dependent on;Select viewschema, viewname, dtype from SYSCAT.VIEWDEP where bname=? order by 1,2;Schema;705;Name;540;Type;500;Id;1;
104;M1;Packages Dependent on;Select PKGSCHEMA , PKGNAME tabauth from SYSCAT.PACKAGEDEP where BNAME=? order by 1,2;Schema;705;Package;780;Tabauth;720;
105;R;Groups within Database;Select distinct grantee from (select distinct grantee from SYSCAT.TABAUTH where GRANTEETYPE='G' union select distinct grantee from syscat.packageauth where GRANTEETYPE='G' union select distinct grantee from syscat.dbauth where GRANTEETYPE='G') as a;Group;1275;
106;M;Table authorities for;Select distinct TABSCHEMA,TABNAME,SELECTAUTH, UPDATEAUTH,INSERTAUTH, DELETEAUTH, ALTERAUTH, CONTROLAUTH, REFAUTH, INDEXAUTH from SYSCAT.TABAUTH WHERE GRANTEETYPE='G' and GRANTEE=? ORDER BY 1,2;Schema;1200;Name;2000;Select;600;Update;600;Insert;510;Delete;600;Alter;405;Control;600;References;990;Index;510;
107;B;Table authorities for;Select TABSCHEMA,TABNAME,GRANTOR,SELECTAUTH, UPDATEAUTH,INSERTAUTH, DELETEAUTH, ALTERAUTH, CONTROLAUTH, REFAUTH, INDEXAUTH from SYSCAT.TABAUTH WHERE GRANTEETYPE='G'and GRANTEE=? ORDER BY 1,2;Schema;1155;Name;3060;Grantor;720;Select;600;Update;600;Insert;510;Delete;600;Alter;405;Control;600;References;990;Index;510;
108;M;Package authorities for;Select distinct PKGSCHEMA,PKGNAME,EXECUTEAUTH,BINDAUTH, CONTROLAUTH from SYSCAT.PACKAGEAUTH WHERE GRANTEETYPE='G' and GRANTEE=?;Schema;975;Package;1050;Execute;705;Bind;405;Control;645;
109;M;Schema authorities for;Select distinct SCHEMANAME,CREATEINAUTH,ALTERINAUTH,DROPINAUTH from SYSCAT.SCHEMAAUTH WHERE GRANTEETYPE='G' and GRANTEE=?;Schema;1005;Create;600;Alter;405;Drop;450;
110;M;Tablespace authorities for;Select distinct TBSPACE, USEAUTH, grantor from SYSCAT.TBSPACEAUTH WHERE GRANTEETYPE='G' and GRANTEE=? order by 1;Tablespace;1600;Authority;1000;Grantor;1200;
111;S;Database authorities for;Select distinct CONNECTAUTH,CREATETABAUTH,BINDADDAUTH,DBADMAUTH,NOFENCEAUTH, IMPLSCHEMAAUTH from SYSCAT.DBAUTH WHERE GRANTEETYPE='G' and GRANTEE=?;Authority;1800;Grant Auth?;1020;
112;U;Ids within Database;Select distinct grantee from (select distinct grantee from SYSCAT.TABAUTH union select distinct grantee from syscat.packageauth union select distinct grantee from syscat.dbauth) as a;Id;1275;
113;A;Table authorities for;Select TABSCHEMA,TABNAME, SELECTAUTH, UPDATEAUTH,INSERTAUTH, DELETEAUTH, ALTERAUTH, CONTROLAUTH, REFAUTH, INDEXAUTH from SYSCAT.TABAUTH WHERE GRANTEE=? ORDER BY 1,2;Schema;800;Name;1500;Select;600;Update;600;Insert;510;Delete;600;Alter;405;Control;600;References;990;Index;510;