IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
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:
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.
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.
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.
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.
I hope I covered all imponderables, if not please bear with me and take the examples as a starter.
Add a Comment
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
;
March 16, 2021 from 11am to 11:30pm – Webcast
0 Comments 2 LikesBringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.
Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply 0 Likes
I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i be able to access this information?Continue
Tags: #DataAccess
Started by Kip in What's hot ? Nov 4, 2020. 0 Replies 0 Likes
This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue
© 2021 Created by Surekha Parekh.
Powered by
You need to be a member of The World of DB2 to add comments!
Join The World of DB2