IBM Db2 Administration Tool: generate DDL for parent tablespace and database (GEN/MIG)

 by Jørn Thyssen and Tom Ulveman Jensen

Introduction

When you use IBM Db2 Administration Tool’s (AOC) GEN function to reverse engineer DDL from the Db2 Catalog it will normally generate the DDL for the selected objects and any child objects. For example, if you generate DDL for a table you will get the DDL for the table, all indexes, views, synonyms, aliases, etc. with option of turning it on and off for specific object types. However, you would not get the DDL for the parent table space.

Similarly, if you use GEN for a table space you would get the DDL for the table space, all tables, all indexes, views, synonyms, aliases etc. But you would not get the DDL for the parent database.

There are certain scenarios where you also need the DDL for the parent objects. For example, you want to create a table in a different subsystem where the tablespace and database does not already exist. Previously you would have to navigate to the parent table space and issue the GEN command for the table space instead of the table. This will work fine for table spaces with a single table, but could lead to issues if the table space is a multi-table tablespace as you would now get the DDL for the tablespace and ALL of the tables in that tablespace, rather than the specific table of interest.

This issue is more apparent for the parent database which may contain 100s of tablespaces and tables, so navigating to the parent database and GEN the database would generally give you the DDL for too many objects.

The work around in the past would be to manually GEN the database only (de-selecting all child objects) and GEN the tablespace only (de-selecting all child objects), and the concatenate the DDL for the database; tablespace; and the table with all child objects.

With APAR PH30132 AOC introduces an option for generating DDL for the parent tablespace and database for GEN and MIG. With this option you avoid having to manually generate the DDL for parent objects.

GEN support for DDL for parent tablespace and database

If you use GEN from the ISPF panels there are now new options for table-like objects (tables, views, aliases, etc.) where you can turn DDL generation for parent tablespace and database on or off:

CREATE parent DATABASE . N (Y,N)
CREATE parent TABLESPACE N (Y,N)


To ensure backwards compatibility the default options are off.

Similarly, there is a new option for GEN on tablespaces to turn DDL generation for the parent database on or off:

CREATE parent DATABASE . N (Y,N)

If you use GEN in batch there is a new sub-option “INCLUDE” for the object request or quick-scope

Examples for tables:

OWN='TS5941',TB=’GLWTEMP’,INCLUDE='DB,TS';                      
TYPE='TB',QUAL='TS5941',NAME='GLWTEMP',INCLUDE='DB,TS';   
TYPE='TB',QUAL='TS5941',NAME='GLWTEMP',INCLUDE='TS';  
TYPE='TB',QUAL='TS5941',NAME='GLWTEMP',INCLUDE='DB';  
TYPE='TB',QUAL='TS5941',NAME='GLWTEMP',INCLUDE=’’;  

Examples for tablespaces:

DB='TS5941',   TS='GLWSEMP'   ,INCLUDE='DB';    
TYPE='TS',QUAL='TS5941',NAME='GLWSEMP',INCLUDE='DB';  


Tip: ADBTEP2 has a directive to ignore certain SQLCODEs. If you add
--#SET ACCEPT_RC ON -601

to SYSIN DD of ADBTEP2 then it will ignore SQLCODE -601 for existing objects and continue processing. If you are unsure if the database or tablespace already exist on the target, then you can use this option with ADBTEP2.

MIG support for DDL for parent tablespace and database

AOC’s migrate function (MIG) may also generate DDL if the scope of migrate includes DDL:

Scope of migrate:                        
  DDL . . . . . . . . . . . . . . . Y    
  Data  . . . . . . . . . . . . . . Y    
  Catalog statistics  . . . . . . . Y    

On the “Migrate Parameters” panel (ADB28M) you can use the primary command “GEN” to modify GEN options, such as which object types to include; specify masking; SQLID; and much more.

With APAR PH30132 there is now also support for generating DDL for parent tablespaces and databases:

CREATE parent DATABASE  . . . . . . Y (Y,N)   
CREATE parent TABLESPACE  . . . . . N (Y,N)   

For example, if you want to migrate 10 tables including data from one schema or subsystem to another where the objects do not exist you would previously have to generate the DDL for any parent objects manually. Now you can just turn on DDL generation for parent database and tables.

If you’re unsure if the parent objects exist you can use the “Tip” from the previous section and manually add the ACCEPT_RC directive to ignore any existing objects (SQLCODE -601).