Db2 Administration Tool: improved schema support
by Jørn Thyssen and Tom Ulveman Jensen
For many years the Db2 Administration Tool (AOC) had the following search criteria on the “Db2 System Catalog” panel (option 1 from AOC main menu):
Enter standard selection criteria.
Name . . . .
Owner . . . . >
In D/L/H . .
Here “Name” is the name of an object; “Owner” is the owner/creator; and “In D/L/H” was “in database”, “in collection” or “in schema” depending on the type of object being searched for.
This could be a bit confusing for some object types. For example, when selecting tables: searching for “Owner” = “XYZ” would search the column “CREATOR” from SYSIBM.SYSTABLES. “CREATOR” for tables is considered being the schema. However, if you wanted to search for stored procedures in a schema you should enter the schema in the “In D/L/H” field.
With APAR PH30132 this confusion has been eliminated as schema is now a separate field.
Use the “Owner” field to search for ownership of Db2 objects: typically the OWNER column from the relevant catalog table, but in some cases CREATOR (e.g., databases and tablespaces).
Use the “Schema” field to search for the schema of Db2 objects: typically the SCHEMA column from the relevant catalog table, but in some cases CREATOR (e.g., tables, views, and indexes).
Enter standard selection criteria:
Name . . . .
Schema . . .
Owner . . . .
In DB/Coll .
In addition, the four search fields above are now 25 chars instead of 18 chars. The label “In D/L/H” was changed to “In DB/Coll”.
With the new layout it is very clear what your searching for and searches across different types of objects are consistent.
We’ve also delivered another schema related enhancement:
Option H on the “Db2 System Catalog” panel searches for schemas and would previously show:
DB2 Admin PDS1 Schemas Row 1 to 1 of 1
Line commands:
E - Data type F - Function J - Trigger O - Stored procedure A - Auth
GR - Grant GEN - Generate DDL REP - Report Q - Sequence GV - Gbl. Variable
CP - Copy privileges
Number of Number of Number of Number of Number of Number of
S Schema Data Types Functions Procedures Triggers Sequences Variables
* * * * * * *
--- -------- ---------- ---------- ---------- ---------- ---------- ----------
TS5941 0 0 2 0 0 0
******************************* END OF DB2 DATA *******************************
Several object types that also has a schema were absent: tables, indexes, views and aliases.
These missing types have been added:
DB2 Admin PDS1 Schemas Row 1 to 15 of 15
Command ===> Scroll ===> CSR
Type line commands in the Select column, then press Enter.
Line commands:
T - Tables X - Indexes V - Views AL - Aliases
E - Types F - Functions J - Triggers O - Procedures A - Auth
GR - Grant GEN - Generate DDL REP - Report Q - Sequences GV - Variables
? - Show all line commands
No. No. No. No. No. No. No. No. No. No.
S Schema Tables Indexs Views Aliass Types Funcs Procs Trig Seqs Vars
* * * * * * * * * * *
--- -------- ------ ------ ------ ------ ----- ----- ----- ----- ----- -----
TS5941 101 104 21 0 1 2 47 5 20 1
******************************* END OF DB2 DATA *******************************
In addition, the “GEN” line command and primary command will now generate the DDL for all schema objects. This gives you a very easy way to retrieve the DDL for all objects in one or more schemas.
For backwards compatibility we have introduced a new GEN request type ‘XS’ for the new extended schema. Use TYPE=’XS’,QUAL=’schema’ to get the DDL for all schema objects or use SCH=’schema’ for the old behavior, i.e., no DDL generated for tables, indexes, views and aliases.
It is also possible to navigate to the "Schemas" panel from the individual object lists using the "H" line command, e.g., from "Tables" to "Schemas" or from "Stored Procedures" to "Schemas".
#Db2Toolsforz/OS