IBM Db2 Automation Tool simplifies the process of generating Db2 utility JCL by using job profiles. Job profiles incorporate utility, exception, and object profiles, and can be easily customized to trigger Db2 utilities for selected objects. For example, from the object profile, you can choose to include (or exclude) objects based on criteria such as:
- Wildcard criteria
- The type of objects (such as indexes, table spaces, or entire volumes)
- The objects’ RI relationship(s) and auxiliary spaces
- And more
As rich as these features are, Db2 Automation Tool has other methods to filter objects and target them for utilities with even more flexibility. This blog will focus on powering up your Db2 automation by using advanced SQL and job profile groups. These two options alone provide you with the ability to easily customize Db2 Automation Tool to handle virtually any task. Let’s walk through how to solve a real-world difficulty with Db2 Automation Tool by creating a job profile with groups and with the advanced SQL feature.
Example: Relative page numbering, partitions, and REORGS
It’s not uncommon today for a Db2 table to contain billions of rows. Previously, a REORG on the entire table space was required. This can become very costly considering how large these tables can become over time. To solve this problem, Db2 12 for z/OS introduced relative page numbering for range-partitioned table spaces. In some cases, this allows a partition-level REORG. However, when an ALTER occurs (such as converting an existing range-partitioned table space to relative page numbering), a REORG of the entire space may be recommended as the ALTER will place the object in REORG-pending advisory (AREOR) status.
Now, consider the situation where 1,000 PBR RPN objects require a REORG. In a Db2 Automation Tool job profile, because of the pending definition changes, some objects need to be included at the PART level and others at the ALL level. These objects also need to be processed in parallel. This can be difficult to accomplish with a single Db2 Automation Tool job – but it can be done by leveraging advanced SQL and job groups.
Introducing advanced SQL and job groups
The object profile in a job profile might have a wildcard specified using the DB, TS, and USER names. But how do we add criteria to filter on PBR RPN objects?
Answer: Update your wildcard object in the object profile, but further refine the selection of the specific PBR RPN object by using advanced SQL.
We’ll need two job groups in one job profile to accomplish this task.
First, create a job profile and add the desired object and utility profiles.
Next, update the object profile to enter Y in the Advanced SQL field and Y in the Update SQL field. After you press Enter, on the Object Selection Advanced SQL panel, add 6 blank lines and include the following SQL statement:
SELECT TS.DBNAME, TS.NAME
FROM SYSIBM.SYSTABLESPACE TS,
SYSIBM.SYSPENDINGDDL PD
WHERE TS.PAGENUM = ‘R’
AND TS.DBNAME = PD.DBNAME
AND TS.NAME = PD.TSNAME
Here is what the SQL looks like on the panel:
You now have an object profile that selects PBR RPNs with pending changes. Press PF3, and select the partition method at the ALL level.
Now that the first job group is complete, you might want to rename this group so that it’s easily identifiable in the Update Jobs Profile Display. A suggested name might be something like “ALL PART RPN REORG PENDING,” as shown below:
Now it’s time to create another job group within this job profile. Since we will be using the same utility profile, enter a line command R in the GRP line command area to repeat the current job group. You can rename the new job group to something like “RPN PART LVL REORG NO PENDING.”
Now, replace the object profile in the new group with a different object profile that selects objects at the part level.
Update that object profile to include the following query in the advanced SQL options:
SELECT TS.DBNAME, TS.NAME
FROM SYSIBM.SYSTABLESPACE TS
WHERE PAGENUM = 'R' -- RPN
AND NOT EXISTS (SELECT DBNAME, TSNAME
FROM SYSIBM.SYSPENDINGDDL
WHERE TS.DBNAME = DBNAME
AND TS.NAME = TSNAME)
You now have two job groups that can filter on not only PBR RPNs, but those at the PART or ALL level, and with or without pending definition changes.
Here is what the job profile looks like with the two job groups:
Notes to consider
There are several considerations to keep in mind about job groups:
- Each job group is treated as a separate job. The build will result in separate jobs.
- Each job group shares the same job options.
- The number of job groups is a multiplier of the number of jobs specified in the “Maximum nbr of jobs” field in the job breakdown options. For example, if you have two job groups, and enter a value of 5 for “Maximum nbr of jobs,” you will generate a total of 10 jobs at build time. The beauty of using job groups is that they can be scheduled to run at the same time to process multiple objects and utilities in parallel.
Summary
IBM Db2 for z/OS Automation Tool is a wonderful tool – one that DBAs will not want to be without. With options like advanced SQL and job groups, Db2 Automation Tool can very specifically identify the objects that you want to process, and create jobs that target those objects in one job profile with several job groups. You can also use these job profiles along with Db2 Autonomics Director to prioritize how and when your objects are processed – but that’s an entirely different blog.