How a Mainframe COBOL program misused MQ queue

I came across a Mainframe COBOL program reading messages one by one from MQ eueue, and does some processing and writes the output data to another MQ queue.  It is a very simple program.

The read was destructive MQGET and MQPUT was done using MQPMO_NO_SYNCPOINT so messages were written to the output MQ queue immediately. 

When I checked with application SMEs Why this program was designed this way instead of using plain sequential files.

The answer: restartability.  If this program abends, you simply restart the program, no clean up is needed.

This kind of approach can significantly increase Mainframe CPU utilization and place additional load on the MQ subsystem.

Uncovering the Hidden Cause: A Db2 -911 Error That Silently Broke Data Consistency

Back in 2009, I was working as a Mainframe Consultant for a leading healthcare insurance company. The client’s system architecture was heavily based on CICS, with around 18 production regions handling healthcare claim adjudication. Each region was responsible for processing claims from specific parts of the United States and ran multiple background tasks simultaneously.

At that time, claim data was primarily stored in VSAM KSDS files, with each CICS region maintaining its own dataset. These records were quite large—around 18 KB each—and contained both the original claim data and the derived or calculated information generated during processing.

Around 2006–2007, the client has introduced Db2 into the claim processing flow. The monolithic VSAM structure was normalized into approximately 12 Db2 tables. These tables were split similarly into two logical groups—one for original claim details and another for derived data. Unlike VSAM, the Db2 tables were shared across all CICS regions, rather than being region-specific.

However, to minimize risk, the organization continued to treat VSAM as the primary data source, with Db2 acting as a shadow repository.

The claim processing flow remained largely unchanged: the system would read the VSAM record at the start, cache it in memory, process it across multiple programs, and finally write the updated data back to VSAM. With Db2 integration, additional SELECT, INSERT, and UPDATE statements were introduced throughout the processing steps to keep Db2 in sync with the cached data.

After deploying these changes to production, application SMEs observed inconsistencies—certain claims had missing or incomplete data in Db2 tables. Interestingly, the issue was not consistent; different tables were affected for different claims. Instead of investigating the root cause, a reconciliation batch job was implemented. This job compared VSAM records against Db2 and corrected discrepancies by inserting or updating missing data.

In 2009, I was assigned to debug a production issue related to claim processing. I set up an Xpediter session and carefully traced the execution of a claim through the system.

During debugging, I encountered a recurring Db2 SQL error: SQLCODE -911, which indicates a rollback due to a deadlock or timeout. I continued the session and noticed that, after processing, certain Db2 tables were missing data for the same claim.

This was a critical observation. The -911 error triggers a rollback, which means any previous INSERT or UPDATE operations in that logical unit of work are undone.

Further analysis revealed that the application had retry logic for handling SQLCODE -911. Whenever this error occurred, the program would retry the same SQL statement up to five times. If any retry succeeded, processing would continue as if nothing had happened.

The problem?

This retry logic was implemented for SELECT statements.

As a result:

A -911 error would roll back prior updates toDb2 tables.

A subsequent successful retry (on a SELECT) allowed processing to continue.

This led to partial or missing data in Db2 tables.

This flaw had gone unnoticed for nearly two years after Db2 integration.

When I presented my findings to the application SMEs, there was initial skepticism, partly because I was relatively new to the team. However, I substantiated the findings using official Db2 documentation explaining SQLCODE -911.

Eventually, the team acknowledged the root cause. The resolution involved significant changes to remove inappropriate retry logic and ensure proper handling of transactional failures.

This experience reinforced an important lesson:

Retry mechanisms must be carefully designed—especially in transactional systems—otherwise, they can silently introduce data inconsistencies.

Db2 for zOS 101 Db2 and WLM

The below notes are extracted from https://www.youtube.com/watch?v=Z8WdZ52NCbA

Terminology

Service definition

Consists of one or more service policies

Service policy

Contains several workloads
One service policy is active at a time in an LPAR or Parallel Sysplex

Workload
Arbitrary collection Consists of one or more service classes

Service class

Each service class has at least one period
- Each period has one goal
- If > 1 period, all but last have a duration

Goal (5 types)
System
Average response time
% response time
Execution velocity
Discretionary

Classification rules

WLM assigns address spaces and transactions to service classes by classification rules 

 Concepts: service class and classification

Classification

• Assignment of incoming work to a service class, and optional report class
• Based on a wide variety of filters, or qualifiers

 

Service class

Set or group of related work

• Production CICS, IMS, and Db2 address spaced might be in the same service class: STCHI or PRODHI
• Separate report classes can report on CICS, IMS, Db2 separately
  

Service class can combine goals of different types in multiple periods

• Period is a combination of importance (IMP), goal, and duration
• A service class period is the target of WLM measurement and management actions
  

Subsystem types and classification

 

Transaction Type	Allowable Goal Types	Allowable # of Periods
Address space oriented	Response Time Execution Velocity Discretionary	Multiple
Enclave	Response Time Execution Velocity Discretionary	Multiple
CICS/IMS	Response Time	One

Goal Types

System goals
SYSTEM, SYSSTC
- These have fixed dispatch priorities above IMP 1

Response time goals
Average response time
- Includes queue time and execution time
- Better for homogeneous type transactions

Percentile response time
- Reduce effect of outliers 

- Better for heterogeneous type transactions

 

Execution velocity goals (velocity goals)
Intended for work where response time goals are not appropriate
- Address spaces, long running jobs

Discretionary

For low priority, long-running work
- Probably not appropriate for Db2 work

 

Goal types: more detail

Execution velocity goals (velocity goals)

• How fast work should run relative to other work requests when ready, without being delayed for CPU, storage or I/O
• Expressed as a number, e.g. 60 or 40
  Value of 60 means 'ready' work runs 60% of the time
• Differentiate velocity goals within an importance level by 10
• Appropriate velocity goals depend on the number of engines (CPs)

 Response time goals

• Average response time goal of 1 second - if one in 10 transactions takes 10 seconds, you will miss this goal
• Percentile response time goal of 90% complete in 1 second, one transaction in 10 taking 10 seconds will not miss this goal

Importance

For most work, importance 1 (IMP 1) is highest and importance 5 (IMP 5) is lowest

WLM applies resources to IMP 1 first

If IMP 1 work meets its goals, WLM will apply resources to IMP 2, then IMP 3, then IMP 4, etc.

Some service trickles down to DISCRETIONARY

SYSTEM and SYSSTC are internal service classes for system tasks and have the highest dispatching priorities

SYSOTHER is the default service class for unclassified work and runs at a DISCRETIONARY goal

Note: not all work is "most important"

Importance levels and Db2, example

SYSTEM	z/OS
SYSSTC	IRLMs
IMP 1 Highest	DB2PMSTR, DB2PDBM1, DB2PDIST, DB2PWLMx
IMP 2 High	Production DDF txns
IMP 3 Medium
IMP 4 Low	Low priority work
IMP 5 Lowest	Lowest priority work
DISCRETIONARY
SYSOTHER	Default service class

Importance 1 is highest priority after SYSSTC

Db2 address spaces should have velocity goals and a single period defined

Non-production Db2s could be IMP 2 or IMP 3 or IMP 4 if in same LPAR (or Parallel Sysplex) with production Db2

Discretionary work gets service after all other importance levels

• Not appropriate for Db2 address spaces
• Not recommended for Db2 work
• Very little service if CPU 100% busy

 WLM concepts and Db2 (notes)

Importance

• Production Db2 address spaces (MSTR, DBM1, DIST, WLM) should be defined with Importance 1 (IMP 1)
• Non-production Db2 address spaces in a production LPAR should be defined with lower importance: IMP > 1
  • Consider relative to other production work
• Production DDF transactions should generally be defined with IMP below that of production Db2 address spaces
• IRLMs should be defined in SYSSTC

 

Goals for Db2 work

• System - IRLM in SYSSTC
• Velocity goals are appropriate for started tasks or long-running work
  • Db2 address spaces should have velocity goals and only a single period in the service class (MSTR, DBM1, DIST, WLMx)
• Response time goals are appropriate for transactions, including most DDF work
  • Percentile response time-e.g. 90% complete in 0.5 seconds
  • Average response time - e.g. average response time is 0.5 seconds
• Discretionary: below IMP 5. Not appropriate for Db2 work, in general

 

Service class: assigning goal types (example only)

CICS, IMS or TSO transactions
E.g. average response time goal Transactions complete <0.7 seconds

Db2 Address Spaces
Velocity goal; IMP 1
Exec Vel = 70 , Single period

Production DDF Transactions
Percentile response time goal, single period
IMP 2; 90% complete < 0.5 seconds

Non-production DDF: response time goals in first period, response time or velocity in second period

Period 1: IMP 3, 90% complete <0.5 seconds
Period 2: IMP 4, 90% complete < 4 seconds
Period 3: MP 5, Vel = 40

Service class: period switch - example

PERIOD 1
DUR = 300
IMP = 3
R/T = 90% in 0.5 sec

PERIOD 2
DUR = 600
IMP = 4
R/T = 90% in 4 sec

PERIOD 3
IMP = 5
VEL = 40

All transactions assigned to this service class start in Period 1

• WLM manages the transactions in period 1 to the percentile response time goal of 90% completing in half a second, with importance of 3

 Transactions that accumulate 300 service units (DUR = 300) before completing migrate to Period 2

• New service class period; WLM manages the transactions in period 2 to the goal of 90% completing in 4 seconds, with importance of 4
• This means, 90% of those that did not complete in period 1

Transactions that accumulate 900 service units (DUR 300 + DUR 600) before completing migrate to Period 3

• A new service class period; WLM manages the transactions in period 3 to a velocity goal of 40, with importance of 5

"Service units" is a hardware independent measure of CPU consumption. If your transaction consumes 1000 service units on a z13, it should consume 1000 service units on a z14, z15 or z16 (and so on)

WLM managed delays

Processor
Dispatching priority

Non-paging DASD I/O
IOSQ, subchannel pending, control unit queue

Storage
Paging, swapping

Tasks
Multi-programming level, server address space creation
- e.g. WLM address spaces for external stored procedures

WLM cannot manage
-User delay
-Network delay
Any resources or delays not listed at right are shown as UNK (unknown)

Service class periods

WLM heuristic behavior is applied to service class periods

WLM can effectively manage 25-30 active service class periods

• If you have more than 30 active service class periods, WLM may not be able to adjust resources for all of them when the system is busy
• It is precisely when the system is busy that you want WLM to adjust resources to meet your business goals

"Loose" goals are performance goals that are too easily achieved

• Service class periods with loose goals are likely to have a PI < 1, so WLM will always perceive they are meeting their goals.
• Service class periods with loose goals may have a PI < 0.7, in which case they may become a donor
  • WLM may reduce resources for donor service class to apply to recipient service class (one with PI > 1)
    

 Defining Db2 address spaces to WLM

Db2 address spaces are started tasks

• To WLM, the Db2 address spaces have a subsystem type of "STC"

 IRLMs should be defined in service class SYSSTC

Remaining Db2 address spaces should be assigned to a service class with a single period, a velocity goal and appropriate importance. For example,

• Production: IMP 1
• QA, Development and/or Test in same LPAR/Sysplex:
  • IMP > 1 (i.e. lower importance)
  • Adjust based on other production work, such as production batch
• Db2 address spaces include ssnmMSTR, ssnmDBM1, ssnmDIST and ssnmWLMx for stored procedures

Three types of Db2 work

Local subsystem

Allied threads
-CICS
-IMS
-TSO
-WebSphere on zSystems
-MQ on zSystems

DDF threads

DDF requests use enclave SRBS
- zIIP-eligible 

Stored procedures

External stored procedures run in WLM application environments
Native SQL procedures run in DBM1 address space

First type: local attach

Db2 SQL activity runs under dispatchable unit of invoker

• IMS, CICS, TSO, Batch, etc.
• Inherited classification class of invoker
• Priority and management of home unit
• Service attributed back to invoker

 Second type: DDF and enclave SRBs

Why do we need enclaves?

-Manage DDF work separately from ssnmDIST

-Differentiate between high priority and low priority DDF work

Classifying DDF work

Define service classes and appropriate goals for DDF work

DDF Classification Defaults

• Defaults apply if you do not provide any classification rules for DDF work
• Enclaves default to the SYSOTHER service class (i.e. discretionary goal) unless they can be assigned to a service class

Managing DDF Work (Enclaves)

• All goal types are permitted
• Transactions may be subject to period switch
• WLM manages an enclave with its own dispatching priority, etc.

High performance DBAT DDF transactions, we must use velocity goal

WLM Notes

 
1. Goal Definition & Performance Index (PI)
 
Execution velocity goals

• Are very sensitive to configuration changes

 
Unachievable/Unrealistic velocity goals – ie. goal of 90
– Check velocities of SYSTEM and SYSSTC to determine highest achievable velocities
– Smaller n-way partitions will necessitate lower velocity goals
 
A velocity of 99 (conceptually 100) is not physically attainable. This means that the service class is always going to under achieve (PI > 1) so WLM is always going to try to give it more help unless or until the PI gets over 4. At that point it will give up trying to help and performance can get considerably worse.
 
Define the Goals
 
Base your definitions on measurements -

• Especially execution velocity goals can’t be defined without knowing the data

• Use periods of high utilization or contention as a base

 
If you want that WLM always keeps an eye on your service class
 

• Define a tight goal:

• 0.9 < PI < 1.2

• A little bit high temperature isn’t a bad thing

• Only if that doesn’t really help use CPU or Storage Critical

• Can be avoided in many cases

 
Always define goals based on measurement intervals of high system utilization or high system contention periods. This gives you the best indication of whether the goals can be achieved.
 
You should base goals on what can actually be achieved, not what you want to happen. The goals you set are how WLM knows whether your system is running OK, or whether it needs to make changes. If you set goals higher than necessary, WLM moves resources from lower importance work to higher importance work which might not actually need the resources.
 
Velocity goals should be set with a difference of at least 10 to be effective and meaningful – Any service classes with goals closer than 10 should be evaluated to be combined into one service class
 
 
2. Protecting Work & CPU Management
 
Protecting Work: CPU
 
To avoid that lower important work may get a higher dispatch priority than higher important work
 
= Define a tight goal!

• 0.9 < Performance Index < 1.2

• Ensures that a high important service class does not become donor

• But also requires a “constant” flow of work

= Or use CPU Critical as the last resort
 
CPU Critical only protects that work from lower importance work, no protection from work at same or higher importance, better to have the right goal
 
3. CPU Critical Usage Guidelines
 
When to use CPU Critical?
 
Use it seldom
 

• For 1 or 2 service classes

• At best only for importance 1

• Suited is work which doesn’t consume much CPU, which doesn’t show high consuming CPU spikes, and which needs fast access to CPU

• Examples:

• Critical server address spaces

 
Rule: use the similar rule of thumb for work which you would place in SYSSTC
 

• To SYSSTC

• Less than 20% of 1 logical processor

• No high consuming CPU spikes

• CPU Critical

• Less than 5 to 10% of total system consumption (depending on size of system)

• No high consuming CPU spikes

 
When running CICS/IMS with response time goals, and CPU critical is necessary, designate both regions and transactions as CPU critical
 
4. Discretionary Work & Capping
 
Discretionary work
 
In order to better help discretionary work get a chance to run, certain service class periods may become donors to discretionary work:
◦ Velocity goal of 30 or less or response time goal > 1 minute
◦ PI <= 0.81
◦ Not part of a resource group
 
In some cases, velocity goals of 31 (instead of 30) have been used to avoid becoming a donor
 
What to do if you don't like discretionary goal management (capping of work)
 

• Define a resource group without minimum and maximum

• Put service class with a goal eligible for capping into this resource group

• Capping will not take place !!

 
5. Service Class Design & Classification
 
Classify Work
 
Make sure that something really executes in your service classes

• Don’t define anything with less than 2% of CPU resource consumption

• BUT: classify distinct work to distinct service classes, for example:

• Don’t classify short living enclaves and STCs together

• Never classify CICS/IMS transactions together with anything else

• If you use multiple periods

• Make sure that some substantial amount of work is really ending in those periods

• More than 3 are very often counter productive

 
Not more than 25 (max 35) [actively running] service class periods with non discretionary goals
 
Service classes should only be defined for work when sufficient demand exists for them in the system. You can measure the demand either by the service consumption or by the amount of ending transactions during high utilization periods of this work. It probably does not make sense to define service classes with a demand of less than 1% of measured service units at any period or less than 1 transaction ending per minute. It is usually better to combine this work with other similar work
 
And as always, keep number of active service class periods to a range of 25 to 35!!!. Note that service classes with discretionary goals do not count, because they are not managed to goals.
 
6. Resource Groups
 
How to use Resource Groups
Use of minimum

• For medium and lower important work which need to make some progress

• But be very careful

• A resource group minimum supersedes all goals and all importance

• That means the resource group minimum comes first

 
To handle runaway jobs or transactions, consider creating a “sleeper” service class.
Associate the service class with a Resource Group and specify a maximum service unit capacity of 1. “Quiescing a batch job” on page 208 of “System Programmers Guide to Workload Manager” manual shows you how to use this Resource Group setting for batch and you can use this example in other cases.
 
7. SYSSTC / SYSOTHER & Special Classes
 
Unclassified work will default to one of two places
– Started Tasks default to SYSSTC
– All other work defaults to SYSOTHER
 
Recommendations for SYSSTC
– DB2IRLM and IMS IRLM – Lock manager needs high dispatching priority in order to let work flow properly through the system
– “Emergency” TSO ID – Only one TSO ID should be defined to SYSSTC
• All other TSO IDs should be grouped together, no special high priority service class for system programmers or management
 
Monitor the SYSSTC service class’s CPU usage. If high importance and online systems experience delay caused by SYSSTC, adjust SYSSTC by moving heavy CPU users to a different service class.
 
Consider creating a special high velocity, high importance TSO service class for emergencies. If the system hangs, you can use the RESET command to assign your TSO user ID to this class to allow you to investigate. Note that you might still need to wait for WLM to identify that this service class is delayed and adjust its priority. Assigning the TSO user ID to SYSSTC is an alternative.
 
8. CICS & IMS Management
 
Management: CICS and IMS
 
WLM Puts regions which process the same set of transactions to internal service classes
 
WLM creates internal service classes up to 2n-1
 
Only create different service classes if the work really executes in different regions otherwise the result is unpredictable
 
9. CICS & IMS – Goals (Response Time vs Velocity)
 
CICS and IMS – R.T. or Velocity Goal?
Which is the better way to manage online work?
Remember, WLM will set dispatching priority for the region
– Need to have the CICS and IMS Regions dispatched properly
– CICS and IMS have their own internal routines to decide which to run within their regions
– If transactions 0101 and PRD1 both run in AOR1, CICS will decide which to dispatch, NOT Workload Manager
 
Velocity Goals for CICS and IMS
Velocity goals are acceptable for environments with only one partition, or sysplexes with similar sized partitions
– A sysplex with a 4-way and a 20-way may not be a good candidate
 
Can be used when the nature of online transactions does not make classification of transactions goals reasonable
– Vastly different types of transactions would skew response time distribution data
– Two transactions service classes in same region will get same dispatching priority
 
Velocity goals do need to be monitored and may need to be adjusted during any processor changes
– Processor upgrades, LPAR definition changes, etc.
 
Response Time Goals for CICS and IMS
3 major advantages of response time goals
– Easier to understand and can be set to a business SLA
– Normally no need to change when environment is changed
– Can use same goal across entire parallel sysplex, regardless of individual partition size/speed
 
WLM needs to see at least three transactions complete within a 20-minute interval to get useful statistics for a response time goal
 
10. CICS/IMS Special Handling & Best Practices
 
Response Time Goals for CICS and IMS
You may want to exclude test work from being managed towards response time goals
à Too few transactions, no constant utilization and/or no need to manage it in a sophisticated way
 
Execution Velocity Goals for CICS and IMS
Make sense for test environments
à Without a constant flow of transactions
à If test and production runs on the same system or sysplex
à Use response time goals for production
à Use execution velocity goals for test
à Exempt test regions from response time management (Option: REGION)
 
Problem
à CICS TOR need fast access to CPU to get work in and out of the system
à CICS TOR have to wait behind AORs
      à Same dispatch priorities
 
Solution
à De-couple TORs from response time management but ensure that response time management remains in tact
à Option BOTH
 
 
CICS and IMS: Option BOTH
à What to do
      à Separate CICS TORs and AORs to different service classes
      à TOR service class
           à Set to importance 1, define a “high” execution velocity goal, CPU Critical may be an option too
      à Exempt the TORs from being managed towards response time goals by using option BOTH
           à This ensures that the end-to-end context for CICS transactions remains in tact
           à This ensures that CICS transactions (and the AORs) are still managed towards response time goals
           à This maintains all reporting features for CICS
 
à Why does it work
     à Typically TORs (as well as IMS CTL) only consumes 5 to 10% of the CPU of all CICS/IMS work
 
 
11. Transaction & Address Space Behavior
 
Address space classification in transaction management mode
You always need to classify the address spaces through the STC or JES subsystem classification rules. You only use the assigned service class goal during initialization and termination of the address space. The assigned service class goal is also used when there is no transaction during one minute.
 
The CICS address space receives resources and has a dispatching priority. A CICS transaction runs with the resources and dispatching priority of the CICS region. So, we recommend that you have more Application Owning Regions (AORs) than service classes on each z/OS image to make workload management via WLM easier. This decreases the chance of transactions with different goals executing in the same address space. If it happens, the transactions with less aggressive goals get a “free ride,” while WLM tries to honor the more aggressive and more important goals.
 
12. Multiple Periods & Monitoring
 
Notes on Multiple Periods
Workload Manager makes better decisions when there are more samples per service class period
Review RMF Workload Activity Report for service class utilization by period
 
If one period of a multi-period service class is always much smaller than the other periods, consider consolidation
 
For example, typical utilization pattern of three period service class
– SCLAS Period 1 – APPL% = 71.1
– SCLAS Period 2 – APPL% = 0.37
– SCLAS Period 3 – APPL% = 138.0
 
In this case, period 2 should either be combined with 1 or 3
 
13. Effect of diverse mix of Trasactions in CICS/IMS regions
 
If there are too many service classes for CICS or IMS with response time goals, the management of these transactions can become a little unpredictable, because WLM manages the regions based on the mix of transactions that runs in these regions.
 
If this mix is too diverse or even if there are just a few service classes with response time goals but some of them with low activity, the mix can end in unpredictable results from a management point of view.
 
This is one of the reasons why you might want to consider whether the response time goals for CICS and IMS are the best choice for your installation. Execution velocity goals might have their downsides, but they are much easier to correlate to what is running on the system
 

Running a job at a specific time every day without using a job scheduler

This post explains how to automate the repeated submission of a job using JCL, SORT, and a dataset/member where the job is stored.

Sample Job

/MYUSERRR JOB ,'REPEAT',MSGCLASS=F,COND=(4,LT),

//         CLASS=A,REGION=0M,NOTIFY=&SYSUID

//*

// SCHEDULE HOLDUNTL=('05:00','2026/141')

//*

//NEXTJOB  EXEC PGM=SORT

//SYSOUT   DD SYSOUT=*

//SORTIN   DD DISP=SHR,DSN=MYUSER.JCL(REPEAT)

//SORTOUT  DD SYSOUT=(,INTRDR)

//SYSIN    DD *

    OPTION COPY

    OUTFIL IFTHEN=(WHEN=(1,21,CH,EQ,C'// SCHEDULE HOLDUNTL='),

           BUILD=(1,23,C'05',26,6,DATE3(/)+1,40,41)),

           IFTHEN=(WHEN=NONE,BUILD=(1,80))

/*

//* PUT REST OF JOB AFTER THIS COMMENT

In the above job you have to change the job card to make it work at your installation. Whatever you want the job to do every hour or day or week or month or ... you will have to append in additional steps. The above job will run every day at 5am except for the first time.   

• If the scheduled time has already passed at submission, the job starts immediately.
• If the scheduled time is in the future, the job remains in HELD status until the specified time.

Please remember to save the JCL in MYUSER.JCL(REPEAT) before you submit it the first time.

 

So what is actually going on? SCHEDULE makes the job start at the specified time on the specified day. SORT reads the JCL from SORTIN and copies it to SORTOUT which submits it again. During the copy SORT edits the contents of the SCHEDULE card and adds one to the current date (DATE3(/)+1) which delays the execution of the job until next day at 5am. You can choose to add 7 instead if the job is supposed to run once a week or add some other value. The amazing thing is the ability to control the time of day, too. I have just chosen to let the job start at 5am. You can use some of the functionality in the BUILD function in SORT to make the job run every hour or every second hour or whatever you need.

 

Please be aware that when you make changes to the JCL in MYUSER.JCL(REPEAT) it will not take effect until after the next time the job is executed as the job waiting to be executed next time is sitting in the input queue in JES. If you want your change to have immediate effect you must cancel the job in JES and then submit your changed job with a SCHEDULE card holding the time and date for the next execution. The job will survive an IPL so you do not have to worry about that.

 

 

DB2 SQL to find tables used by a program

Below SQL to returns the list of tables used by a latest version of a program 

WITH TEMP(COLLID, NAME, CONTOKEN) AS(                    

     SELECT COLLID, NAME, CONTOKEN                       

     FROM SYSIBM.SYSPACKAGE                              

     WHERE (LOCATION,COLLID,NAME,BINDTIME) IN (          

           SELECT LOCATION,COLLID,NAME,MAX(BINDTIME)     

           FROM SYSIBM.SYSPACKAGE                        

           WHERE NAME = 'program_name'                       

          GROUP BY LOCATION,COLLID,NAME                  

       )                                                 

)                                                        

SELECT SUBSTR(T.NAME,1,8) AS PROGRAM,                    

SUBSTR(BQUALIFIER,1,8) AS QUALIFIER,                     

 SUBSTR(BNAME,1,30) AS TBL_NAME,                         

 BTYPE                                                   

 FROM SYSIBM.SYSPACKDEP,TEMP T                           

WHERE DCOLLID = T.COLLID                                 

 AND  DNAME   =  T.NAME                                  

 AND  DCONTOKEN = T.CONTOKEN                             

 AND BTYPE = 'T'                                         

WITH UR;                                                 

Defining GDGs with more than 255 generations

Generation Data Groups (GDGs) can be defined with more than the traditional 255 generations by using the LIMIT parameter in conjunction with the EXTENDED attribute. These parameters control the maximum number of Generation Data Sets (GDSs) that can be associated with a GDG.

Source : https://www.ibm.com/docs/en/zos/3.1.0?topic=dgp-required-parameters
LIMIT(limit)
Specifies the maximum number, from 1 to 255, of GDSs that can be associated with the GDG being defined. If EXTENDED is specified, 1 to 999 GDSs can be associated with the GDG being defined.

Source: https://www.ibm.com/docs/en/zos/3.1.0?topic=dgp-optional-parameters
EXTENDED|NOEXTENDED
Specifies whether the GDG is to be an extended GDGr or not.
     EXTENDED
        allow up to 999 generation data sets (GDSs) to be associated with the GDG.
        Abbreviation: EXT
 
     NOEXTENDED
        allow up to 255 generation data sets (GDSs) to be associated with the GDG. This is the default value.
        Abbreviation: NEXT
 
Sample JCL to define a GDG with 999 Generations
 
//STEP1    EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DEFINE GDG(NAME(USERID.GDG.LIMIT999) -
             EXTENDED                  -
                   LIMIT(999)                -
                   NOEMPTY                   -
                   SCRATCH)
/*
 
Converting a NOEXTENDED GDG to a EXTENDED GDG
 
Source:  https://www.ibm.com/docs/en/zos/3.1.0?topic=generationdatagroup-converting-noextended-extended
 
You can use the following procedure to convert a generation data group defined as NOEXTENDED to one that is EXTENDED. IBM advises that the original user catalog should be backed up before starting the procedure.

1. Define a temporary user catalog.
2. REPRO MERGECAT the GDG from the original user catalog to the temporary user catalog. This moves the GDG and all GDSes to the temporary user catalog and removes them from the original user catalog.
3. DEFINE a new GDG with the same name as the original GDG specifying the EXTENDED parameter and a new LIMIT parameter as desired.
4. REPRO MERGECAT only the GDSes using wild card notation from the GDG in the temporary user catalog into the new GDG now in the original user catalog.
5. Use DELETE RECOVERY to delete the temporary user catalog.

The process is complete. You can use LISTCAT to prove that the new GDG has the EXTENDED attribute and that a new LIMIT has been set.

Example JCL and IDCAMS statements:
 
//* GDG NOEXTENDED TO GDG EXTENDED CONVERSION
//*
//CONVERT EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
/* 1. DEFINE A TEMPORARY CATALOG */
 
DEFINE -
UCAT -
(NAME (TEMP.UCAT) STORCLAS(SMSSTORC) -
CYL (1 1))
 
/* 2. REPRO GDG AND GDSES TO THE TEMPORARY CATALOG */
 
REPRO -
INDATASET(ORIG.CAT) -
OUTDATASET(TEMP.UCAT) -
ENTRIES(SMSA.GDG) -
MERGECAT
 
/* 3. DEFINE GDG EXTENDED IN THE ORIGINAL CATALOG */
 
DEFINE GDG (NAME(SMSA.GDG) -
EXTENDED LIMIT(999) SCRATCH PURGE)
 
/* 4. REPRO GDSES BACK TO THE ORIGINAL CATALOG */
 
REPRO -
INDATASET(TEMP.UCAT) -
OUTDATASET(ORIG.CAT) -
ENTRIES(SMSA.GDG.*) -
MERGECAT
 
/* 5. DELETE TEMPORARY CATALOG */
 
DELETE TEMP.UCAT USERCATALOG RECOVERY
/* LISTCAT TO SHOW EVERYTHING BUILT CORRECTLY */
 
LISTCAT ENTRY(SMSA.GDG) ALL
/*