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

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
;

Latest Activity

William Shipley liked Jonathan Sloan's event IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update
4 hours ago
Carol Davis-Mann posted an event

Db2 for z/OS: The Evolution Engine Webcast at United Kingdom

March 16, 2021 from 11am to 11:30am
Join Julian Stuhler, IBM Gold Consultant and Solutions Delivery Director at Triton Consulting for his first IBM hosted webcast of 2021.Julian will be presenting Db2 for z/OS: The Evolution Engine on Tuesday, March 16th at 15:00 GMT (11:00 AM EDT).Presentation OverviewFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us.…See More
21 hours ago
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two

In DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two Gareth Copplestone-Jones provides guidance on the implementation of server-side configuration.Server-side configurationWhen considering how to manage managing Application Compatibility – APPLCOMPAT – for your distributed applications which use the NULLID packages, the main alternative to client-side configuration (discussed in the …See More
yesterday
An event by Jonathan Sloan was featured
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
yesterday
William Shipley liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
yesterday
Dragica Smintic liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
Wednesday
A blog post by Surekha Parekh was featured
Wednesday
An event by Surekha Parekh was featured

Db2 for z/OS: The Evolution Engine - Julian Stuhler at Webcast

March 16, 2021 from 11am to 12:30pm
Register NowDb2 for z/OS: The Evolution Engine - Julian StuhlerFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us. From internet banking transactions to airline reservations and insurance quotations, Db2 for z/OS continues to power a huge array of today’s high volume, mission-critical enterprise applications. But how has Db2 remained so relevant…See More
Wednesday
Tom Glaser posted events
Wednesday
Jonathan Sloan posted an event
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
Wednesday
Michal Bialecki liked Carol Davis-Mann's blog post DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One
Feb 19
A blog post by Carol Davis-Mann was featured

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 19
An event by Surekha Parekh was featured
Thumbnail

Business and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery at Virtual Event

March 23, 2021 from 11am to 12:15pm
REGISTER NOWBusiness and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery Abstract One of the most valuable resources for modern businesses is business critical data.  Data can be used to provide insight into every facet of the business from operations to market trends. Powerful data enables effective and efficient business decision making and your enterprise data is only as powerful as your ability to harness it. Storing,…See More
Feb 19
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 17
An event by Surekha Parekh was featured
Thumbnail

Db2 12 agile, open and secure - Your database of choice for business critical data at Webcast

March 16, 2021 from 11am to 12:30pm
Register Now    PART 2Maria Sueli from the IBM Development Lab Following Julian, Maria Sueli from the IBM Development Lab will share with you how you can remove barriers that are holding your Db2 enterprise data captive in outdated methods and processes.  She will demonstrate how Db2 for z/OS…See More
Feb 17
Annette Zawacki added a discussion to the group Data, Analytics and AI on IBM Z
Thumbnail

Don't miss the first Db2 Analytics Accelerator Webcast of 2021

IBM Db2 Analytics Accelerator Trends and DirectionsFebruary 16 / 11:00 AM ETRegister todayPlease join IBM for a webcast where we will share the latest Db2…See More
Feb 10

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

Forum

Replaying past database activities

Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply

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

Db2 is developer inclusive and preview of Db2 11.5.5

Started by Kip in What's hot ? Nov 4, 2020. 0 Replies

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

Badges  |  Report an Issue  |  Terms of Service