GEN batch jobs

When you use the reverse engineering function (GEN) to generate SQL, you have the option to request batch as the execution mode. If you specify this option, Db2 Admin Tool generates a JCL batch job. You can optionally edit this job to use wildcard characters in the qualifiers and names of the objects. Using wildcard characters enables the DDL to be extracted for objects based on string patterns in the object name.

The following JCL is an example of one of these batch jobs:

Figure 1. Example JCL to generate SQL

//GENSQL   EXEC PGM=IKJEFT01,DYNAMNBR=100            
//STEPLIB  DD DISP=SHR,DSN=ADBD10.ISPLLIB            
//         DD DISP=SHR,DSN=DSN.DSNA.SDSNEXIT         
//         DD DISP=SHR,DSN=DSN.DSNA.SDSNLOAD         
//         DD DISP=SHR,DSN=AUTHSW.ISPLLIB     
//SYSTSPRT DD SYSOUT=*                               
//SYSTSIN  DD *                                      
  DSN SYSTEM(DSNA)                                   
  RUN PROG(ADB2GEN) PLAN(ADB) PARMS('/REBIND')    
  END
/*                                               
//SYSPRINT DD SYSOUT=*                           
//SQL      DD SYSOUT=*,DCB=(RECFM=FB,LRECL=80)   
//IN       DD *         
  DB2SYS   = 'DSNA',    
  DB2ALOC  = '',        
  DB2SERV  = 'DSNA',    
  DB2AUTH  = 'SINNOTT', 
  DB2REL   = '1013',    
  GENSG    = 'Y',       
  GENDB    = 'Y',       
  GENTS    = 'Y',       
  GENTABLE = 'Y',       
  GENVIEW  = 'Y',       
.
.
.
  NEWGRANTOR = '',       
  SPCALLOC = 'DEFINED',  
  TGTDB2   = '';   
  TYPE='DB',QUAL='',NAME='DSNDB04';

The in-stream data set identified by the IN DD statement contains both run parameters and request parameters:

Run parameters

Specify options for how the SQL is to be generated. They are listed first in the IN data set. In the preceding example, the semicolon (;) after the TGTDB2 parameter indicates the end of the list of run parameters. The next line contains request parameters.

Restriction: You cannot modify run parameters in the generated batch job.

Request parameters

Name the specific objects for which SQL is to be generated. The request parameters must follow the run parameters in the IN data set. The request parameters are:

TYPE
QUAL
NAME
VERSION
INCLUDE
XDTYPE
SQL

Start of change The TYPE, QUAL, and NAME parameters together identify an object or objects by name. If the object is a native stored procedure, user-defined function, or trigger, the optional VERSION parameter indicates a particular version. If the object is a table or table space, the optional INCLUDE parameter indicates whether to include parent objects. See Request parameters: TYPE, QUAL, and NAME. You can also optionally use the XDTYPE parameter to exclude objects. See Optional XDTYPE parameter. End of change

Start of change As an alternative to specifying an object name, the objects can be specified by using an SQL SELECT statement. In this case, the TYPE and SQL parameters are used. See Request parameters: TYPE and SQL. End of change

Wildcard characters in request parameters

You can modify the request parameters in the generated JCL with wildcard characters as needed. The values for the qualifier and name can contain one or more of the following wildcard characters:

-: A minus sign (-) represents any single character.
% or *: A percent sign (%) or asterisk (*) represents one or more characters.

Any other character represents a single occurrence of itself.

The rules for wildcard characters are the same as the rules for the LIKE predicate.

Request parameters: TYPE, QUAL, and NAME

The combination of TYPE, QUAL, and NAME parameters specify the request for an object. For example, the following request generates the DDL for database DSNDB04 and all of the objects that it contains:

TYPE='DB',QUAL='',NAME='DSNDB04';

The following table shows the values that can be specified for the request parameters TYPE, QUAL, and NAME for each type of object.

Table 1. Values of TYPE, QUAL, and NAME for each object type
Object Type	TYPE	QUAL	NAME
Database	DB	n/a	`dbanme`
Table space¹	TS	`dbname`	`tsname`
Table¹	TB	`creator`	`tbname`
Global Variable	GV	`schema`	`gvname`
View	VW	`creator`	`vwname`
Alias	AL	`creator`	`aliasname`
Index	IX	`creator`	`ixname`
User-defined data type	DT	`schema`	`udtname`
User-defined function	FU	`schema`	`udfname`
Stored procedure²	SP	`schema`	`stpname`
Sequence	SQ	`schema`	`seqname`
Schema	SC	`schema`	n/a
Extended Schema	XS	`schema`	n/a
Trigger	TG	`schema`	`tgname`
Storage group	SG	n/a	`sgname`
Synonym	SY	`creator`	`syname`
Trusted context	TC	n/a	`tcname`
Role	RO	n/a	`roname`

Notes:

For tables and table spaces, you can also specify an INCLUDE parameter to specify that parent objects are to be included. For details, see Optional INCLUDE parameter.
For native SQL procedures, user-defined functions, and triggers, you can also specify a VERSION parameter. For details, see Optional VERSION parameter.

Optional INCLUDE parameter

For tables and table spaces only, you can also specify an INCLUDE parameter as follows to include parent objects in the generated DDL:

For a table, you can generate the SQL for the parent table space (TS) and parent database (DB). Specify either TS or DB or both.
For a table space, you can generate the SQL for the parent database (DB). You can specify only DB for table spaces.

You cannot specify wildcard characters for the INCLUDE parameter. For syntax details, see the syntax for request-list.

Start of change For example, the following requests specify that the parent objects be included: End of change

TYPE='TS',QUAL='*',NAME='TS10*',INCLUDE='DB';

TYPE='TB',QUAL='TS3071',NAME='TB*',INCLUDE='DB,TS';

Optional XDTYPE parameter

You can specify the XDTYPE parameter to exclude objects. For example, the following request is used in the MIG process on the database level:

XDTYPE ='TS',QUAL='BFVDCH',NAME='BFVSCHK';

TYPE='DB',NAME='BFVDCH';

These two requests specify all table spaces in database BFVDCH, except for table space BFVSCHK.

Optional VERSION parameter

Start of change For native SQL procedures, user-defined functions, and triggers, you can also specify a VERSION parameter after TYPE, QUAL, and NAME to indicate the version or versions for which you want to extract the DDL. You can specify a specific version, the active version, or all versions. For example, the following request specifies version V1: End of change

TYPE='SP',QUAL='DEMBIN2',NAME='MYSTP',VERSION='V1';

Wildcard characters can also be used in the VERSION parameter. For example, the following specification extracts DDL for all versions:

VERSION='*'

If the version is omitted or set to blank, the active version is extracted. For example, the following specification extracts DDL for all active stored procedures within the TEST schema:

QUAL='TEST',NAME='*'

Request parameters: TYPE and SQL

The combination of TYPE and SQL parameters can be used instead of TYPE, QUAL, and NAME to specify a request for an object. In this case, the object or objects are identified by using an SQL SELECT statement. Using SELECT statements allows the objects to be filtered by clauses.

This request has the following format:

TYPE='SQL', SQL='statement'

where statement is a SELECT statement or a DD name that identifies a data set that contains the SELECT statement. If the SELECT statement contains single quotation marks (‘), use one of the following techniques:

Duplicate the single quotations marks in the statement. For example:
```
SQL='SELECT ''TB'' AS TYPE, ...'
```
Put the entire SQL parameter value in double quotation marks. For example:
```
SQL="SELECT 'TB' AS TYPE, ..."
```

The SELECT statement must return the following columns:

TYPE CHAR(2)
QUAL VARCHAR(128)
NAME VARCHAR(128)

The SELECT statement can optionally return the following additional columns:

VERSION VARCHAR(122)
INCLUDE CHAR(8)
XDTYPE CHAR(8) (exclude)

For example, the following request generates the DDL for table spaces created by TS3071:

TYPE='SQL', SQL="SELECT 'TS' AS TYPE, DBNAME AS QUAL, NAME
  FROM SYSIBM.SYSTABLESPACE
  WHERE CREATOR = 'TS3071'";

The SQL parameter can also specify a DD name, as shown in the following example:

TYPE='SQL', SQL='=SQLTRG';
//SQLTRG DD DSN=TARGET_DATASET_NAME,DISP=SHR

In this example, the data set TARGET_DATASET_NAME contains the SQL statement.

Note: If the SQL returns XDTYPE requests, make sure that the SQL returns those rows first. In most cases, XDTYPE requests do not need to be used, because SQL already allows for excluding objects.