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: 124

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

NEW Announcement! Db2 Analytics Accelerator Version 7.5

Started by Patricia Zakhar in What's hot ? Oct 15. 0 Replies

Today, IBM announced the Db2 Analytics Accelerator for z/OS Version 7.5, which delivers enterprise-grade transactional and analytic processing. It introduces Integrated Synchronization, a transformative capability that provides an integrated,…Continue

Export Data from DB2 5.0 Database from a Laptop

Started by Rakesh in Application Development and DB2 Oct 8. 0 Replies

Dear All,   I've to Export Data out from DB2 5.0 Database from a Laptop. I've installed Toad for DB2 on that Laptop. Now going to Install DB2OLEDBV5_x64 on that Laptop. After this how to proceed?ThanksRakeshContinue

Tags: Export, 5, DB2

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service