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".