Execute the HDDL command on multiple objects of the same object type in batch mode.
search cancel

Execute the HDDL command on multiple objects of the same object type in batch mode.

book

Article ID: 280460

calendar_today

Updated On: 03-11-2024

Products

RC/Query for DB2 for z/OS

Issue/Introduction

Using batch mode with the HDDL command , JCL can be generated and saved, objects manually added if required, and then submitted later when it is more convenient to processing requirements.
The generated output is a single dataset that includes HDDL output for all included objects without duplicates.
In order to enable the command options for each object without repeating them, use the new SHOW/HIDE OPTIONS profile parameter to control whether the HDDL request options are displayed only once or for each command execution.

Environment

DB2 for Z/OS

Resolution

Example: Generated the DDL for six tables in one batch job

Enter HDDL next to the first object and an '=' sign next to subsequent ones(that repeats the previous command). 

 

RQTL          --------------- RC/Q Table List --------------- yyyy/mmm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N
Table Name ===> TBEMP%                > Creator ===> authid1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------authid1 -          LINE 1 OF 6    >
CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
HDDL____ TBEMP              authid1  DBCORP   TSEMP          8               9
=_______ TBEMP1             authid1  DBCORP   TSEMP3         5               0
=_______ TBEMPM             authid1  DBCORP   TSEMP2         4               8
=_______ TBEMP_R            authid1  DBCORP   TSREJ          7               0
=_______ TBEMP_TASK_ROLE    authid1  DBCORP   TSPROJ         3               9
=_______ TBEMP_TASK_ROLE_R  authid1  DBCORP   TSREJ          3               0
******************************* BOTTOM OF DATA ********************************

On the next screen choose Mode ===> B 

Set the correct JCL dataset(member)

If the DDL will be executed outside of Batch Processor, specify SPUFI Compatible   ===> Y

The new SHOW/HIDE OPTIONS profile parameter is used to control whether the HDDL request options are displayed only once or for each command execution. 

Alter any of the other options required. 

 

PTHDDLB   ------ RC/Q Hierarchical DDL Request Options ------ yyyy/mmm/dd hh:mm
COMMAND ===>

On Object: Type T  Name *

Execution Specifications:                  RI Chain Setting:
  Mode ===> B ( O - Online, B - Batch )      RI Chain ===> Y  (Y or N)

Batch Output Library:         SPUFI Options Setting:
  Project ===>                  SPUFI Compatible   ===> N  (Y or N)
  Group   ===>                Object Selection:
  Type    ===>                  SG DB TS T  I  V  S  A  TG R  SQ PM
  Member  ===>                  Y  Y  Y  Y  Y  Y  Y  Y  Y  Y  Y  Y

Other Partitioned or Sequential File:
Data Set Name ===> authid1.hlq.CNTL(myDDL)
  Volume Serial ===>        (If not cataloged)

SQL Terminator Setting:   SQL ID Setting:            Grants Setting:
  Terminator ===> ;         CreatedBy ===> N (Y or N)  Grants ===> N (Y or N)

 / Show (enter /) or hide (blank) options each time (see help to reset)

On the next screen choose "P" for preview....DESTINATION    ===> p

For this example, this is the JCL generated.

jobcard
//*
//HDDLSTEP EXEC PGM=PTLDRIVM,REGION=0M,
//             PARM='SUFFIX=00,EP=PTLHDDLB'
//STEPLIB  DD DISP=SHR,DSN=hlq.CDBALOAD
//         DD DISP=SHR,DSN=hlq.PRIVATE.SDSNEXIT
//         DD DISP=SHR,DSN=hlq.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=hlq.CDBALOAD
//         DD DISP=SHR,DSN=hlq.PRIVATE.SDSNEXIT
//         DD DISP=SHR,DSN=hlq.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=hlq.CDBAPARM
//HDDLOUT  DD DSN=authid1.hlq.CNTL(MYDDL),
//         DISP=SHR
//SYSPRINT DD SYSOUT=*
//MSGFILE  DD SYSOUT=*
//ABNLIGNR DD DUMMY                 SUPPRESS ABENDAID DUMPS
//PARMFILE DD *
SSID ssid
LOCATION LOCAL
SQLID authid1
OBJTYPE TB
NAME "TBEMP"
CREATOR "authid1"
OBJTYPE TB
NAME "TBEMP1"
CREATOR "authid1"
OBJTYPE TB
NAME "TBEMPM"
CREATOR "authid1"
OBJTYPE TB
NAME "TBEMP_R"
CREATOR "authid1"
OBJTYPE TB
NAME "TBEMP_TASK_ROLE"
CREATOR "authid1"
OBJTYPE TB
NAME "TBEMP_TASK_ROLE_R"
CREATOR "authid1"
SQLDLIM ;
SPUFIIND N
OBJECTS YYYYYYYYYYYY
OWNERIND N
RICHAIN  Y
GENGRANT N
/*
//

In this example the DDL is all written to authid1.hlq.CNTL(myDDL). The DDL is now able to be executed by Batch processor or by SPUFI or DSNTEP2.
It will contain the parent and dependent objects in its hierarchy as specified by the "Object Selection" screen fields above.

Additional Information