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.

Views: 170

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Comment by Datajoe on September 10, 2019 at 16:25

Multiline command output can also be generated by using DB2 Common Table Expressions and recursive SQL. In the example below the DB2 Catalog query result is joined with a 4 row sequence table. The sequence numbers are subesequently used to control multiline command output Generation. Because the query output is casted to CHAR(79) there is no need to remove hex(00) or other unreadable binary characters. In our DB2 environment the query output generated by DSNTIAUL is directly processed by DB2 DSN processor.

with
Q1 (SEQNO) AS
( select 1 from sysibm.sysdummy1
UNION ALL
SELECT SEQNO + 1 FROM Q1
WHERE SEQNO < 4
),
Q2 (SEQNO,COLLID,NAME,VERSION,TYPE) as
( SELECT DISTINCT
Q1.SEQNO
,COLLID
,NAME
,VERSION
,TYPE
FROM SYSIBM.SYSPACKDEP, SYSIBM.SYSPACKAGE, Q1
WHERE
(( BTYPE = 'I'
AND BQUALIFIER = 'SYSIBM'
AND BNAME IN ( 'DSNOTX01', 'DSNATX02' )
)
OR
( BTYPE = 'T'
AND BQUALIFIER = 'SYSIBM'
AND BNAME IN( 'SYSCONTROLS', 'SYSENVIRONMENT'
,'SYSINDEXES', 'SYSKEYS'
,'SYSPACKAGE', 'SYSPACKCOPY'
,'SYSROUTINES', 'SYSTABLES'
,'SYSTRIGGERS'
)
)
OR
(BNAME IN
('SYSTABLEPART' ,'SYSTABLESPACE' ,'DSNDOB02')
AND LOCATION = ' '
AND BQUALIFIER = 'SYSIBM'
AND BTYPE IN ('I','T')
)
)
AND COLLID = DCOLLID
AND NAME = DNAME
AND CONTOKEN = DCONTOKEN
AND RELBOUND <> 'Q'
ORDER BY COLLID,NAME,VERSION,SEQNO
)
select char
(CASE
WHEN Q2.SEQNO = 1 THEN
' REBIND ' CONCAT
CASE
WHEN TYPE = 'T' THEN
'TRIGGER PACKAGE -'
ELSE
'PACKAGE -'
END
WHEN Q2.SEQNO = 2 THEN
' (' CONCAT
STRIP(COLLID) CONCAT
'.' CONCAT
STRIP(NAME) CONCAT
'.(' CONCAT
STRIP(VERSION) CONCAT
')) -'
WHEN Q2.SEQNO = 3 THEN
' '
ELSE ' '
END,79)
FROM Q2
;

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Db2 for z/OS Master class with John Campbell and the SWAT team returns!

Started by Surekha Parekh in What's hot ?. Last reply by carol Goldberg Nov 7. 1 Reply

Db2 for z/OS Master class with John Campbell and the SWAT team returns! June 24-28, 2019 at IBM Hursley near Winchester, UKSeptember 23-27, 2019 at IBM Silicon Valley Lab in San Jose, California…Continue

Tags: Events, MasterClass, JohnCampbell

RBS Shares their Db2 Utilities Experiences

Started by Calene Janacek in What's hot ? Oct 22. 0 Replies

Join Mark Turner, Lead Mainframe Architect and Strategist from RBS and Haakon Roberts, IBM DE as they share Royal Bank of…Continue

Tags: #Db2

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service