Introduction:

Sometimes it is useful to generate JCL executable code for //SYSIN via SQL mass operations, e.g. change specific tables from one option to another. If the result row fits into one JCL line with a length of 72, then all is fine. If the line must be split, then the question comes up: How to split?

Here is a solution for commands (like REBIND PACKAGE), SQL (like ALTER TRIGGER) or utilities (like RUNSTATS).

72 line split:

Recursive SQL with common table expression is the solution.

Step 1 - Generate the JCL lines:

With CTE T1 (and some more) you define what you want. The attached examples can be enhanced by more search conditions via predicates and/or joins. This is normal SQL business.

With CTE T2 you get a table with the 5 main rows:

1. The unique key for the row, e.g. the complete name.
2. The location counter for each row: This is important to get the rows in the correct order.
3. The generated text.
4. The index into the text.
5. The length of the next text portion.

With CTE T3 you get a table with the maximum location counter per each row. This is important because the last row might have special handling, e.g. no continuation character.

In the final SELECT you split up the row by command syntax with e.g. continuation character or not.

Continuation for DSN

Continuation for DSNTEP2

Continuation for utility

Step 2- Make it JCL-readable:

Unfortunately, your required generated text might be longer than 255, hence casting to CHAR is not a general solution. Unloading the data via DSNTIAUL (as in Step 1) generates VARCHAR columns, for which the x’00’ must be removed. This is done via ICE tools.

Step 3 – Incorporate the generated statements:

Generate the appropriate //SYSIN for the appropriate Db2 command processor (DSN command processor), SQL execution program (DSNTEP2) or Db2 utility statement program (DSNUTILB).

Examples:

REBIND PACKAGE:

The attached example selects all standard packages (TYPE = ‘ ‘), which are not valid (VALID=’N’) and generates the REBIND PACKAGE statements. Of course, other variations are possible, e.g. to select only the trigger packages and to generate REBIND TRIGGER PACKAGE etc.

DSNTIAUL_REBIND.TXT

ALTER TRIGGER:

The attached example generates for all advanced triggers (SQLPL=Y’) the SQL ALTER statement for a ALTER TRIGGER … REGENERATE ACTIVATE VERSION USING APPLICATION COMPATIBILITY V12R1M505.

DSNTIAUL_ALTER.TXT

RUNSTATS utility:

Generate RUNSTATS command for all user table spaces with explicit table name and with an explicit column list. This is an example of new BIF LISTAGG. Therefore some more CTEs are defined and used.

DSNTIAUL_RUNSTATS.TXT

I hope I covered all imponderables, if not please bear with me and take the examples as a starter.