Category: News from the Labs

Hi all! This month I wish to go though a few of the interesting, annoying and odd things that I bumped into last year. Some were new for me and some were just interesting for me!

COMPRESS THIS!

One of my customers is now starting down the road of compressing their very, very large NPSI’s as the RECOVER utility is actually way faster than a REBUILD. Nothing new here, is there? But wait! What if they are using FLASH COPY?

It gets very, very ugly very, very quickly is what happens!

Why?

Remember how FLASH COPY works? It is sooooo blindingly fast because it does all the actual copy stuff “in the DS8000, or equivalent, box” and *not* on your mainframe. This is really cool as you just shoot off a FLASH COPY and, as long as a few really basic rules are not broken, the copy is finished the moment it starts!

So, what about Indexes?

Firstly, if you want to do a FLASH COPY of a COMPRESS YES index you *cannot* also do a sequential copy. Further, remember that a FLASH COPY is a VSAM dataset and you cannot do a COPYTOCOPY of one of these either, meaning you have just one single VSAM dataset as a copy – This is, at least for me, a single point of failure and not good. But it gets much worse!

Really, how so?

Please now remember how index compression works… It is done purely “in memory” in the bufferpool. This means that when you have an insert, delete, or key update in memory and it has not yet been externalized to disk that when you now do a FLASH COPY you are copying garbage… This is, to coin a phrase, “not good” whereas a normal COPY index goes through the bufferpool and so a sequential copy is naturally ok!

Bottom Line

If using COMPRESS YES indexes in no way use FLASH COPY. Perhaps, at least for storage, dataset compression of the sequential copy datasets might save space…but then that defeats the purpose of COMPRESS YES on the index purely in the Db2 world which is primarily reducing I/O and secondarily reducing index page splits.

What about SYSTEM LEVEL BACKUPS?

Guess what? These are FLASH COPY as well! If you use SLBs and you have COMPRESS YES indexes you better be careful what you “replay” and make sure to always REBUILD, or at least CHECK, all indexes after a RECOVER has been run!

Docu?

All of the above is documented of course but it is like Douglas Adams wrote “in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door in saying ‘Beware of the Shark.'” – [The Shark is my idea geddit? Originally it was Leopard of course!]

Death By RUNSTATS

It is surprisingly easy to kill yourself with a simple RUNSTATS these days.

How?

Let’s say you have PROFILE on and you are using SYSSTATFEEDBACK for all your tables. I know you are as it is all on by default and who changes defaults?

And?

Now a third-party vendor sells you some software with ridiculously long VARCHAR fields containing possible NAMEs and ADDRESSes. In this case VARCHAR(2000) is being used. The SQL in question is using dynamic SQL with literals, not parameter markers, in the WHERE clause against these columns and SYSSTATFEEDBACK “sees” the requirement for column groups as these columns have, naturally, no index and a column group is a “poor man’s” index for frequencies and cardinalities, right?

So?

You end up with 23 column groups for a five partition table with over 120 million rows.

But what has that got to do with the Price of Beef?

So, dear, friends, what does our poor old RUNSTATS utility do now? It must farm out these column groups to DFSORT — and can you guess how it works out the allocation size??? You guessed it: 2000 + 8 for the maximum record size then multiplied by 23, for the number of column groups, then the result multiplied by the number of rows: 120,000,000. Do the math and you end up with a DFSORT storage requirement of over 5 PB (Yep that’s PETA bytes!), then the Storage Admin freaked out!

Easy fix: Delete all COLGROUP definitions for this table not backed by a real index. RUNSTATS DELETE PROFILE is a great help here! Then switch off SYSSTATFEEDBACK for this table and control *all* other column groups because as I like to say “where there is one, there are probably more”.

Bugblatter Beast of Traal

It is possible to wrap your head in a towel so the beast does not see you, but it is sometimes better to actually look for these things before they get really really bad. I hate to think how long this RUNSTATS was just “growing and growing and growing” with no-one noticing that it was quite simply insane!

SYSSTATFEEDBACK is good but not that good!

Remember, it is doing the best it can based on the SQL usage and what the Db2 Optimizer thinks is missing or might improve performance. In this case, the excessive number of column groups and the excessive size of the groups was actually way more of a problem than a help!

Parameter Markers…

It would also have been fine if the developers had coded good SQL with parameter markers so that SYSSTATFEEDBACK would not have started the whole problem in the first place! As a secondary bonus, moving to parameter markers stops any SQL Injection attack vectors as you could do a lot of damage with a VARCHAR(2000) text field!!! Just using “A’ OR ‘A’ = ‘A” would be nice and evil, wouldn’t it!!! Returns every single row because in the code it is stringed into a delimited string. So, if the customer gives A as input it builds this string:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' ;

Now add my injection code:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' OR 'A' = 'A' ;

What does that OR do? Yep – It is always true so every row is always returned…Not what you would want with 120 million rows…

Stale Stats?

A last bit about SYSSTATFEEDBACK is that it recommends STALE quite a lot and some of these bogus entries are, in fact, just created by a STALE recommendation so one other way of clearing them all out is to run a little SQL like the following:

-- THIS SQL WILL CORRECT THE PROBLEM OF BOGUS COLUMN COLGROUP CAUSING
-- EXCESSIVE SORT ALLOCATION AND FAILING RUNSTATS.
--
-- WHAT IT DOES IS:
--
--    1) STOP SYSSTATFEEDBACK GENERATION FOR A GIVEN TABLE
--    2) CLEAN UP SYSCOLDIST AND SYSCOLDISTSTATS "F" ENTRIES WHICH
--       ARE LISTED IN SYSSTATFEEDBACK WITH A "F" AND "STALE" ENTRY
--    3) DELETE ALL "F" AND "STALE" ENTRIES FROM SYSSTATFEEDBACK
--
-- TWO VARIABLES WILL BE CREATED AND USE THE DEFAULT FOR NAME AND
-- CREATOR:
--
CREATE VARIABLE TAB_NAME    VARCHAR(128)
   DEFAULT 'MY_BAD_TABLE'
;
CREATE VARIABLE TAB_CREATOR VARCHAR(128)
   DEFAULT 'MY_BAD_CREATOR'
;
--
-- STOP SYSSTATFEEDBACK PROCESSING FOR THIS TABLE
--
UPDATE SYSIBM.SYSTABLES
SET STATS_FEEDBACK = 'N' 
WHERE CREATOR      = TAB_CREATOR
  AND NAME         = TAB_NAME
  AND TYPE         = 'T'
;
COMMIT ;
--
-- DELETE ANY STALE COLDIST FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDIST A
WHERE A.TBOWNER    = TAB_CREATOR 
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F'
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE COLDISTSTATS FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDISTSTATS A
WHERE A.TBOWNER    = TAB_CREATOR
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F' 
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE SYSSTATFEEDBACK FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSSTATFEEDBACK
WHERE TBCREATOR    = TAB_CREATOR
  AND TBNAME       = TAB_NAME
  AND TYPE         = 'F'
  AND REASON       = 'STALE'
;
COMMIT ;
--
-- DROP THE CREATED VARS FOR NEXT RUN
--
DROP VARIABLE TAB_NAME    ;
DROP VARIABLE TAB_CREATOR ;
COMMIT ;

Take care out there!

Caveat Emptor!

Remember to always review DELETEs like this *before* you do them in production. Blindly deleting stuff is sometimes dangerous and hazardous to your career path!

I hope you found this info interesting on a cold and dark January day, at least here in Germany!

TTFN,

Roy Boxwell

This month I wish to run through a bunch of, sadly, pretty common SQL coding mistakes that lot of beginners fall into. Not just humans either! AI is increasingly generating code snippets which developers simply cut-and-paste in the hope that it is:

1. Correct SQL
2. Does what they want it to do
3. Runs acceptably fast!

In the Beginning was the SELECT

So, let us begin with the number 1 mistake all coders make at some time or other:

SELECT * from mytable ;

Yep, SELECT * FROM mytable. We have all done this, and in SPUFI it is the absolute norm! But if you are coding SQL that must run fast it is deadly… Why?

You are *never* alone in this world and that is doubly true of SQL running on big iron. Think buffer pools, think work space, think FTB etc. RAM is cheap these days, but if it is all being used it is no good to anyone else! The reason I mention space here, is when you code a SELECT * you are, pretty obviously, selecting every single column in the Table, View, MQT etc. This is pretty clearly going to cost you in CPU and I/O as Db2 must access every column in every row, format it, and return the value.

It gets worse, of course, as we live in a buffered world. All of this data is read and written into your precious little buffer pools and also is externalized into your darling little sort work spaces… You get the idea! Every column extra you add to the SELECT adds to the colossal amount of storage and cpu you are using. It gets even worse: If the optimizer sees this, it will sometimes, and pretty often, switch off using an index as it thinks “Well, I gotta get back every column value so an index might not really save cpu here!” Whereas a SELECT with just the three columns you really want, which also happen to be in an index, will then tell the Optimizer: Just scan this small, in comparison, index dataset! The savings can be vast.

It gets still even worse, of course… Coding a SELECT * in static or dynamic SQL will require a code change whenever you add or remove a column, as the cursor and/or program *is* aware of the columns and types of data being returned. Failure to do so is very bad news indeed!

Interesting little fact: Removing a column and then running with SELECT * will not actually cause a run time error (A negative SQLCODE), but it will cause you to possibly select garbage into fields. It is one of my pet bug bears that you can always have more columns on a FETCH than on a SELECT and Db2 does not warn or tell you!

Bad Index Usage

Using functions on columns tends to kill index access outright. If you can code around it – super! If you cannot then an IOE (Index On Expression) might be your only hope. No one likes IOEs though… The expression used must match 100% to the expression in the SQL and so for UPPER or LOWER it is not a problem but for SUBSTR(COL3 , 1 , 5) and SUBSTR(COL3 , 1 , 4) it will fail – and not tell you that the IOE was there but was not a 100% Match. EXPLAIN is naturally your friend here!

Missing Index Columns

Another absolute favorite of programmers is forgetting a join column… We have all done it, and so I am not going to throw the first stone here, but if you have two tables, both with four column indexes all with the same column names and/or functional content, then when you EXPLAIN and it joins with just one or two columns – Alarm bells should start ringing. Sometimes it must be like this but most times a JOIN / WHERE predicate has simply fallen under the table – These can be evil little problems, as sometimes the cardinality of the missing column is one so the returned data is all perfect… Nasty! Here our SQL PerformanceExpert for Db2 z/OS (SPX) software can really help out with the hunt!

Cartesian Join

If I had a Euro for every cartesian join I have seen in production I could retire! The major problem here, is sometimes you do not even see it, and sometimes the tables are so small the results are still OK. So these little problems fall under the radar until one day, that single row table you are mistakenly *not* joining to, grows up into 1,000,000 rows and then your little SQL just grinds to a halt, sucking the whole system down with it! These cartesian joins are sometimes caused by SQL changes where a programmer removes a JOIN to a table and accidentally deletes one row too many in the code. The syntax is still fine, the query might still run fine – especially in test – but then that little table grows… Boom! Here you need to use EXPLAIN, like in the index query, to see *exactly* how the Db2 Optimizer is actually joining all the tables. If you are using SYSIBM.SYSDUMMYx style tables, then you can sometimes want, and even expect, a cartesian join as you are 100% guaranteed to not ever have more than one row in that table! In all other cases, you had better make sure that there are correct JOIN … ON or WHERE criteria to avoid this pitfall.

Correlated Death

I read many years ago that “Correlated Queries are better and faster than Non-Correlated Queries unless they are not”. I always loved this advice as its right up there with “It Depends” in the list of non-helpful helpful advice! However, it has spawned an industry of correlated queries where programmers are 100% sure that writing SQL with correlated queries *all* the time is the absolute bee’s knees in performance. It isn’t! Here is a classic case:

SELECT C.CUSTOMER_ID, C.NAME
FROM CUSTOMER C
WHERE EXISTS (SELECT 1
              FROM ORDERS O
              WHERE O.CUSTOMER_ID = C.CUSTOMER_ID)
;

Please do not do this as the correlated query will be executed for *every* row in the CUSTOMER table, better could well be:

SELECT DISTINCT C.CUSTOMER_ID, C.NAME
FROM CUSTOMER C
   , ORDERS   O
WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
;

Sharp eyes will have noticed the additional DISTINCT on the second query. Why? Well, we (I) do not know whether the chance of multiple rows joining on orders is wanted or not, probably not, so you must then add the DISTINCT to remove all the duplicates. This is actually one case where I would run both queries with EXPLAIN and in Production to compare side by side the results and CPU, I/O etc. and check that the join result was/is the better choice. Remember: It Depends! There are so many possible good reasons for a correlated join that it is nearly impossible to simply say “always bad or always good”. You must always test what you think might help as you might actually make things worse.

DISTINCTly a Bad Idea

I have so often seen queries like:

SELECT DISTINCT blah blah from blah
UNION
SELECT DISTINCT blah blah from blah
ORDER BY blah blah
;

Why do people do this still? Db2 is pretty clever but if you tell it to sort everything and remove duplicates and then you tell it again to do basically the same it will do it! In this case just remove the DISTINCTs and try and get the column order into the ORDER BY sequence so that the last sort is a tick faster – it will save you a ton of I/O and CPU. Remember: Only remove duplicates or sort if you really want to, but also remember: If you do not have an ORDER BY, the sequence of returned data is basically random! It might come back in beautiful sequential order just because of an index, and then tomorrow a different index could be used and POUF! Random order. Golden rule here: Use the minimum number of DISTINCTs and UNIONs and at least one ORDER BY if the order of returned rows is important. If the sort order is not important then do not add it just for the humans!

Bonus point: Some coders add DISTINCT because they get “duplicate” rows back from their query. Adding a DISTINCT fixes their problem, but all it actually does is hide it under a heap of CPU and I/O!

The Bachelor Problem – (Borrowed from Craig Mullins)

This is the failure to COMMIT. It might seem trivial at first but after you have output about 10,000 log records and you are up to over 1,000 row updates, it might be time to think about COMMITing all that work and freeing up all the rows of data and memory in the buffer pool you are blocking at the moment. Remember that COMMIT will cause you to lose Cursor position unless you have WITH HOLD defined on them and always remember to commit at Transaction boundaries – Never “half way through” a transaction for example! I also never let a subroutine issue a COMMIT – it must be the “main” program that controls COMMIT frequency. My rule of thumb here, is to COMMIT at 500 “transactions” that cause many more than 500 updates to the database. I also always use a variable that can be set at run time to raise or lower this frequency. Commits after ever update will kill you – Never Committing will also kill you – Pick a good middle path!

Too Much Data can hurt!

Following on from not COMMITing when you should, is the other cardinal sin of fetching the world to see if one row exists… I have really seen this query in *production*:

SELECT 1
FROM SYSIBM.SYSTABLES
;

This was being used as a “ping” to see if the z/OS Db2 sub-system was up and accepting work! Utterly crazy and a grand example where adding LIMIT 1 would be a great help but really, why run this query at all? These days the z/OS Db2 is *always* there and so the query works 99.999% of the time and the 0.001% where it fails, well, the world is ending anyways! Please review all queries to make sure they are only returning the rows you wish to actually process!

Einstein was Right!

He is famously quoted as saying “Insanity is doing the same thing over and over and expecting different results, like rebooting Windows PCs” – This has at least two or three areas where it hits the SQL Db2 world!

1. Automation! If you do the same thing every morning every day – automate it!
2. If you are doing COMMIT, ROLLBACK, ROLLBACK after ever transaction – Stop!
3. If you are doing CLOSE CURSOR at CICS start of transaction – Stop!

The first point is a no-brainer. If you can write a little script, or even a program, that stops you wasting 30 minutes every day, it will add up very quickly! Remember the clever DBA gets the machine to do his/her work and not the other way around!

Number two is, sadly, a real life Hibernate problem that I discovered. The frame work, after a COMMIT, always issued two ROLLBACKs – I have mentioned before, that frameworks are all well and good but when you have *no* idea what is going on you have a problem brewing. In this particular case, the COMMIT and ROLLBACKs were not being traced, as they are not really SQL, so you had to look a bit deeper under the covers to see a crazy number of ROLLBACKs. Remember that Db2 is not the cleverest piece of silicon on the block … When it gets told ROLLBACK it dutifully saunters off and checks against the log to see if anything has been updated since the last COMMIT, and when you issue a ROLLBACK immediately after the last ROLLBACK Db2 goes off and checks again … This is a tremendous waste of CPU and I hope none of you out there “find” this little gem …

The third point was also found live in production. Way, way, way back when some bright CICS programmer had the brilliant idea to start every transaction with a CLOSE CURSOR in case the cursor had been left OPEN by the last transaction … We all know this cannot happen, right? Anyways, this genius got his/her way and *every* CICS transaction issued a CLOSE CURSOR and got an SQLCODE -501 “The cursor you are closing was not open” – surprise, surprise! Just think, if you had over 20,000,000 CICS transactions per day how much CPU just this tiny error handling path would cost you? Again, it is an SQLCODE that needs to be traced and they are not so easy to handle, but it is also doable. Here, check for how many weird or even “normal” SQLCODEs you get every day from all your running SQL – The contents can be quite scary!!!

No-one eats Stale Bread, do they?

But some people never care about Stale Statistics. If your STATSLASTTIME column in Real-Time Statistics (RTS) is over five years ago, and you have STATSINSERTS, STATSDELETES, STATSUPDATES or STATSMASSDELETES in the RTS, then you have to wonder, “Would a RUNSTATS possibly help me?”. Remember to always do a before/after check of the access paths – Here our Bind ImpactExpert for Db2 z/OS (BIX) can really help you.

Do you like Feedback?

Have you checked your SYSSTATFEEDBACK table to see what the Optimizer is crying out for? It is a gold mine of required data for your RUNSTATS, but beware, dear friends … It also holds the SQL query from Hell that the Management tried running once and got hopelessly wrong! In other words: review the contents, take a copy of the recommendations, delete them all and wait a while … If, after a couple of weeks, that row comes back into the SYSSTATFEEDBACK – BINGO! This is probably data that the Optimizer really needs and not a one-off query from hell! Here, our Real-Time DBAExpert for Db2 z/OS (RTDX) can really help you out!

Just the Facts, Ma’am

Well, I just wanted to run through a bunch of common errors and problems that I have seen around the world! I would love to hear from you if you have any other “Top Ten” style lists of evil things that have happened!

TTFN,

Roy Boxwell

Live from the Trenches

One of my readers just dropped me a note about two of things she has witnessed over her illustrious DBA life!

1. Selecting the current date from within a massive repeating loop like “select current date from sysibm.sysdummy1“. In 99.9999% of cases one single select is enough!
2. Incorrect RUNSTATS defintions E.g. TABEL(ALL) but not adding INDEX(ALL)

This month I thought I would let an AI do all the work… here is the result from the following prompt “write me a Db2 for z/OS newsletter about an interesting topic either to do with SQL or utility usage.”

Topic: Getting the Most from Temporal Tables in DB2 for z/OS

Why Temporal Tables Matter

In many industries—especially banking, insurance, and government—regulations require that you be able to reconstruct historical data exactly as it was at a given point in time. Prior to DB2 10 for z/OS, this was often done with custom history tables, triggers, and a lot of application code.

Today, temporal tables make this far simpler, with built-in DB2 features to maintain and query historical versions of data.

System-Period vs. Application-Period Temporal Tables

DB2 supports two main types of temporal tables:

1. System-period temporal tables – DB2 automatically maintains historical versions of rows when changes occur. Two timestamp columns (ROW BEGIN and ROW END) and a TRANSACTION START ID are used to track validity.
2. Application-period temporal tables – The application controls the validity period by providing its own start and end timestamps. Useful when the “effective” date of data is different from the transaction date.

You can also combine them into bi-temporal tables for the ultimate in time-based data tracking.

Basic Example: System-Period Temporal Table

CREATE TABLE CUSTOMER (
    CUST_ID         INTEGER NOT NULL,
    NAME            VARCHAR(100),
    STATUS          CHAR(1),
    ROW_BEGIN       TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
    ROW_END         TIMESTAMP(12) GENERATED ALWAYS AS ROW END   NOT NULL,
    TRAN_START_ID   TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID 
                                                                NOT NULL,
    PERIOD SYSTEM_TIME (ROW_BEGIN, ROW_END)
) WITH SYSTEM VERSIONING;

Once the table is system-versioned, DB2 automatically creates and manages a history table. You can query historical data using the FOR SYSTEM_TIME clause:

SELECT *
FROM CUSTOMER
FOR SYSTEM_TIME AS OF TIMESTAMP('2024-07-01-10.00.00')
WHERE CUST_ID = 1001;

Performance Considerations

• Indexing Matters: DB2 will maintain history indexes for you, but ensure they support your most common time-based predicates to avoid costly table scans.
• Partitioning History Tables: If your history tables grow large, consider partitioning by date to keep queries efficient.
• Utility Implications: REORG, COPY, and RUNSTATS operate on both the base and history tables. REORG with SHRLEVEL CHANGE can keep them online, but be mindful of increased elapsed time when history data is large.

Utility Tip: REORG and Temporal Tables

When running REORG on a temporal table, DB2 handles both the base and history table in a single execution if they are bound together. However, if you need to reorganize only the history table (for example, to improve access to older data without touching current production data), you can explicitly REORG just the history table.

Example:

REORG TABLESPACE HISTTS     <-- History table's tablespace
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL);

This can help reduce impact on current transactions while still maintaining query performance for historical reporting.

Best Practices

• Use temporal queries rather than joining base and history tables manually; it ensures optimizer support and cleaner code.
• Regularly RUNSTATS on both base and history tables to keep the optimizer informed about growth patterns.
• For reporting, consider materialized query tables (MQTs) on top of temporal queries for faster access to common time-based reports.

Final Thought:

Temporal tables take much of the heavy lifting out of managing historical data in DB2 for z/OS. When paired with smart indexing, partitioning, and utility usage, they can deliver both compliance and performance—without the spaghetti code of the old days.

Maintaining Large Temporal Table Environments in DB2 for z/OS

(Utility Scheduling & Best Practices)

When you start using temporal tables—especially system-period or bi-temporal—your history tables grow quickly. This can cause issues if utilities aren’t tuned for their unique workload patterns.

Here’s a real-world maintenance workflow you can adapt.

1. Understand the Data Growth Pattern

• Base table – Moderate growth, mainly current records.
• History table – Potentially huge growth, as every UPDATE or DELETE adds rows.
• Data skew – History tables are often append-heavy with few deletes.

Before setting schedules, run:

SELECT COUNT(*), MIN(ROW_BEGIN), MAX(ROW_END)
FROM HISTORY_TABLE;

This gives you volume and time-span, helping you plan partition-level REORGs.

2. Recommended Utility Workflow

Nightly (or per batch cycle):

• RUNSTATS on base table only (small, keeps optimizer fresh for OLTP).

RUNSTATS TABLESPACE BASETS TABLE(ALL) INDEX(ALL) UPDATE ALL;

Weekly:

• REORG history table partitions with recent activity.
  • Use PART to target only active partitions.
  • SHRLEVEL CHANGE to keep reporting jobs running.

REORG TABLESPACE HISTTS PART 15:17
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL);

Monthly:

• COPY both base and history tables for backup compliance.

COPY TABLESPACE BASETS FULL YES
COPY TABLESPACE HISTTS FULL YES

Quarterly:

• REORG full history table if query performance declines or RUNSTATS indicate high clustering ratio loss.
• RUNSTATS on full history table.
• Consider MERGECOPY to consolidate image copies.

3. Tips to Keep Utilities Efficient

• Use LISTDEF to dynamically select only affected partitions:

LISTDEF HISTPARTS INCLUDE TABLESPACE HISTTS
    PARTLEVEL
    PARTCOND (LASTUSED > CURRENT DATE - 7 DAYS);

• Parallelism – Large history REORG jobs benefit from PARALLEL keyword if CPU and I/O capacity allow.
• Inline Stats – For history tables, you can take inline stats during REORG to avoid a separate RUNSTATS step.
• Automation – Many shops integrate this with DSNACCOX to decide when REORG or RUNSTATS are needed.

4. Example End-to-End Utility JCL Snippet

//STEP1  EXEC DSNUPROC,SYSTEM=DSN1,UID='HIST.REORG'
//SYSIN    DD *
  LISTDEF HIST_PARTS INCLUDE TABLESPACE HISTTS
    PARTLEVEL
    PARTCOND (LASTUSED > CURRENT DATE - 7 DAYS)
  REORG LISTDEF HIST_PARTS
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL) UPDATE ALL
/*
//STEP2  EXEC DSNUPROC,SYSTEM=DSN1,UID='HIST.COPY'
//SYSIN    DD *
  COPY TABLESPACE HISTTS FULL YES
/*
//STEP3  EXEC DSNUPROC,SYSTEM=DSN1,UID='BASE.RUNSTATS'
//SYSIN    DD *
  RUNSTATS TABLESPACE BASETS TABLE(ALL) INDEX(ALL) UPDATE ALL
/*

5. Key Takeaways

• Don’t treat history tables like “just another table”—their size and access patterns demand dedicated utility schedules.
• Use partition-level maintenance wherever possible to cut elapsed time.
• Integrate RUNSTATS and REORG with automation tools to keep the optimizer happy without manual intervention.

Whaddya Think?

Well, I do not think I will unemployed anytime soon based on this, although, at first glance, it is not that bad. Then you look at the details and it starts to all go horribly wrong…

• Table creation does not know of the keyword SYSTEM VERSIONING, this hints to the fact that the HIST table is not actually automatically created either!

The example SELECT SQL naturally dies with a:

DSNT408I SQLCODE = -20524, ERROR:  INVALID PERIOD SPECIFICATION OR PERIOD
         CLAUSE FOR PERIOD SYSTEM_TIME. REASON CODE = 02

until you manually create the history table and issue the required ALTER:

CREATE TABLE HIST_CUSTOMER (                       
 CUST_ID       INTEGER NOT NULL,                    
 NAME          VARCHAR(100),                       
 STATUS        CHAR(1),                            
 ROW_BEGIN     TIMESTAMP(12) NOT NULL,             
 ROW_END       TIMESTAMP(12) NOT NULL,             
 TRAN_START_ID TIMESTAMP(12) NOT NULL              
)
;                                                 
COMMIT 
;                                           
ALTER TABLE CUSTOMER                               
  ADD VERSIONING USE HISTORY TABLE HIST_CUSTOMER 
;

• LISTDEF does not support conditional partitions (PARTCOND) although the idea is pretty cool.

• Utility statements do not end in a semi-colon.

• Finally, and the very worst mistake of all: seven uses of the capital B! The audacity!!!

What About You?

Have any of you had “interesting” AI suggestions in the world of Db2 for z/OS? I would love to hear from you!

TTFN,

Roy Boxwell

This month I wish to spend a bit of time delving into the zIIP processor(s) that, hopefully, you all have available to use “for free”. Naturally, they cost a few thousand bucks a piece, but their usage is not in the rolling four-hour average and so basically free. Of course, if you have a different price model where CPU usage is all-inclusive then the use of these handy little beasts is moot!

What Is It?

They were first introduced in 2006 with the IBM System z9 processor complex. Their full name is System z Integrated Information Processor normally shortened to “zIIP”. They followed on and took over from the earlier zAAP, that was used for Java, and the IFL, which was for Linux and z/VM. Originally, they were just for Db2 workloads but nowadays quite a lot of non-Db2 work is zIIP eligible.

Eligible?

Yep, the wording is important! The fact that some function or code etc. is able to be run on a zIIP does not mean it *will* run on a zIIP. They are, after all, processors and when they are all busy, your workload will just trundle on by using the rest of the normal CPs (Central Processors) you have.

How Many?

It started out nice and easy… You could not have more zIIPs than you have CPs in your plex. So a 1:1 ratio. Then along came the zEC12 and it changed the ratio to be not more than 2:1 Nowadays, with the z16, IBM have thrown in the towel and announced there is no limit anymore!

When They Arrived

The first Db2 to exploit the zIIP was the “big change” version DB2 V8 when everything went UNICODE and long column on us all!

What have They Done for Us?

From the get go, any TCP/IP based remote accessing SQL was eligible for offload to zIIP. This was a very very good thing indeed and saved people mega-bucks. Parallel query child processes under a dependent enclave SRB, or independent enclave SRB if coming from TCP/IP, also got zIIP support and some utility processes, (Index build for LOAD, REORG and REBUILD INDEX, a portion of index build under a dependent enclave SRB and also a portion of sorting).

Sorry, SRB What?

You might have noticed a TLA (Three Letter Abbreviation) “SRB” occurring a lot in that text! So, what is an SRB and why is it so important? On mainframes, all work is run under two kinds of control blocks: Task and service request blocks. Normally user programs, and system programs, use a Task Control Block, (the TCB that we all know and love) and all run on normal CPs not zIIPs! The Service Request Block (SRB) however, is for system service routines. They are initiated by a TCB to do special stuff and to start them it is called “scheduling an SRB”. To do this, your program must be running in a higher authorized state called “supervisor state”. SRBs run parallel to the TCB task that scheduled them and they cannot own storage but can use the storage of the TCB. Only these SRBs are eligible to be offloaded to a zIIP.

And Then?

Well, when IBM brought out the z13 they merged the zAAP support onto the zIIP and since then, the general direction has been: If a task is an SRB then it *can* be made zIIP eligible. This has meant that there has been a gradual increase in Vendor take-on and IBM usage for these “helping hands”.

What about Db2 Usage?

In DB2 V9 they announced the actual, up until now hidden, limits of use. For TCP/IP remote SQL 60% offload, for Parallel queries 80% offload, Utilities up to 100% offload and, brand new, XML also up to 100% offload!

DB2 10

RUNSTATS were added, but *not* the distributed statistics and inline statistics parts, and Db2 buffer pools got 100% offload for prefetch and deferred write processing.

Db2 11

Not only did the B go lower case, but RUNSTATS got column group distribution statistic processing, and System Agent processing got up to 100% offload when running under enclave SRBs but not p-lock negotiation. This included page set castout, log read, log write, pseudo index-delete and XML multi version document cleanout.

It was also here, when they created the zIIP “needs help” function when a delay occurs. This is controlled by the z/OS parameter IIPHONORPRIORITY YES/NO setting. YES is the default and tells a stalled zIIP to shunt the work to a CP. That might, or might not, be a good idea depending on your cost or time SLAs.

Db2 12

This brought RESTful support at 60% offload, Parallel went up to 100% offload and RUNSTATS also went to 100% offload.

Db2 13

All Db2 SQL AI functions went straight to 100% offload and the COPY utility got a 100% offload but only in the COPYR phase.

COPY now in Db2 13 FL507

I recently kicked my little test Db2 up to Db2 13 FL507 and then waited a couple of days to see the zIIP usage that COPY just got. We were informed it was just in the COPYR subphase of the Utility. I use the SYSUTILITIES table to track everything, so I wrote a little SQL that lists out all the Utilities, Counts, CPU, zIIP and Elapsed.

Here’s the SQL splitting with/without zIIP usage:

SELECT SUBSTR(NAME , 1 , 18)                              AS UTILITY
      ,COUNT(*)                                           AS COUNT 
      ,(SUM(CPUTIME)     * 1.000) / 1000000               AS CPU   
      ,(SUM(ZIIPTIME)    * 1.000) / 1000000               AS ZIIP  
      ,((SUM(CPUTIME) + SUM(ZIIPTIME)) * 1.000) / 1000000 AS TOTAL 
      ,(SUM(ELAPSEDTIME) * 1.000) / 1000000               AS ELAPSED
FROM SYSIBM.SYSUTILITIES                                           
WHERE ZIIPTIME > 0                                                 
GROUP BY NAME                                                      
UNION ALL                                                          
SELECT SUBSTR(NAME , 1 , 18)                              AS UTILITY
      ,COUNT(*)                                           AS COUNT 
      ,(SUM(CPUTIME)     * 1.000) / 1000000               AS CPU   
      ,(SUM(ZIIPTIME)    * 1.000) / 1000000               AS ZIIP  
      ,((SUM(CPUTIME) + SUM(ZIIPTIME)) * 1.000) / 1000000 AS TOTAL 
      ,(SUM(ELAPSEDTIME) * 1.000) / 1000000               AS ELAPSED
FROM SYSIBM.SYSUTILITIES                                            
WHERE ZIIPTIME = 0                                                 
GROUP BY NAME                                                      
ORDER BY 1 , 2                                                     
FOR FETCH ONLY                                                      
WITH UR                                                            
;                                                                  

Here is my output:

---------+------------+----------+--------+----------+-----------+-
UTILITY            COUNT        CPU     ZIIP      TOTAL     ELAPSED
---------+------------+----------+--------+----------+-----------+-
CATMAINT               5       .282     .000       .282       3.477
COPY                 925     11.673    4.907     16.581     914.838
COPY               60471   1017.939     .000   1017.939   65126.853
LOAD                   2       .005     .000       .005        .012
LOAD                 802     17.453    3.852     21.306    1990.150
MODIFY RECOVERY    59128    391.163     .000    391.163   15461.098
MODIFY STATISTICS     47       .120     .000       .120       1.276
QUIESCE               10       .015     .000       .015        .156
REBUILD INDEX          3       .027     .000       .027        .797
REBUILD INDEX          9       .082     .002       .085       2.502
RECOVER                9       .047     .000       .047       1.009
REORG                  4       .022     .000       .022       1.942
REORG                 28      2.075     .427      2.503     178.284
REPORT RECOVERY        3       .059     .000       .059        .454
RUNSTATS              33       .096     .000       .096       4.695
RUNSTATS            3575     44.477   92.323    136.801    1182.851
UNLOAD              1688    129.379     .000    129.379     989.501
DSNE610I NUMBER OF ROWS DISPLAYED IS 17

Here, you can see which utilities are zIIP enabled and how much the zIIPs saves us. The new kid on the block, COPY, actually saves us about 30% which is *not* to be sneezed at!

Checking in Batch

I add the “hidden” parameter STATSLVL(SUBPROCESS) to all my Utilities so that it outputs more info as I am a nerd and love more data! The numbers never all add up and so you must be a little careful, but here’s an example Image Copy JCL with output showing the counters and details:

//ICU005   EXEC PGM=DSNUTILB,REGION=32M,                
//     PARM=(DD10,'DD1DBCO0ICU005',,STATSLVL(SUBPROCESS))
//STEPLIB  DD DISP=SHR,DSN=DSND1A.SDSNEXIT.DD10         
//         DD DISP=SHR,DSN=DSND1A.SDSNLOAD              
//DSSPRINT DD SYSOUT=*                                  
//* THRESHOLD REQUEST DB2CAT REQUEST                     
//SYSIN    DD *                                         
 COPY TABLESPACE DSNDB01.SYSSPUXA                       
    COPYDDN (SYSC1001)                                  
    FULL YES SHRLEVEL CHANGE                            
//SYSC1001 DD DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,       
//            SPACE=(1,(352,352),RLSE),AVGREC=M,        
//            DSN=COPY.DD10.DSNDB01.SYSSPUXA.P0000.D25195
//SYSPRINT DD SYSOUT=*

Normal output:

DSNU000I    195 07:45:39.89 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DD1DBCO0ICU005                          
DSNU1044I   195 07:45:39.90 DSNUGTIS - PROCESSING SYSIN AS EBCDIC          
DSNU050I    195 07:45:39.90 DSNUGUTC -  COPY TABLESPACE DSNDB01.SYSSPUXA COPYDDN(SYSC1001) FULL YES SHRLEVEL CHANGE
DSNU3031I -DD10 195 07:45:39.91 DSNUHUTL - UTILITY HISTORY COLLECTION IS ACTIVE. 
                      LEVEL: OBJECT, EVENTID: 238604 
DSNU3033I -DD10 195 07:45:39.92 DSNUHOBJ - SYSIBM.SYSOBJEVENTS ROWS INSERTED FOR OBJECT-LEVEL HISTORY             
DSNU400I    195 07:46:23.09 DSNUBBID - COPY PROCESSED FOR TABLESPACE DSNDB01.SYSSPUXA
                      NUMBER OF PAGES=222109
                      AVERAGE PERCENT FREE SPACE PER PAGE =  2.75
                      PERCENT OF CHANGED PAGES =  0.00
                      ELAPSED TIME=00:00:43   
DSNU428I    195 07:46:23.09 DSNUBBID - DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE DSNDB01.SYSSPUXA

Then the extra stuff, sorry about the formatting but WordPress is not good for batch output:

----------------------------------------------------------------------------------------------------------
                                          U T I L I T Y   S T A T I S T I C S                             
                                                                                                          
INTERVAL = UTILITY HISTORY        CPU (SEC)  = 0.000288          ZIIP = 0.000000              
   LEVEL = UTILINIT SUBPROCESS    ELAPSED TIME (SEC) = 0.000                                              
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                 6            2                                                                      
  BP32K               2            1                                                                      
                                                                                                          
  TOTAL               8            3                                                                      
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = UTILITY HISTORY        CPU (SEC)  = 0.000091          ZIIP = 0.000000              
   LEVEL = UTILINIT SUBPROCESS    ELAPSED TIME (SEC) = 0.000                                              
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                 3                                                                                   
  BP32K               1            1                                                                      
                                                                                                          
  TOTAL               4            1                                                                      
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = OBJECT-LEVEL HISTORY   CPU (SEC)  = 0.000147          ZIIP = 0.000000              
   LEVEL = UTILINIT SUBPROCESS    ELAPSED TIME (SEC) = 0.001                                              
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                 6            2             1                                                        
  BP32K               2            1                                                                      
                                                                                                          
  TOTAL               8            3             1                                                        
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = UTILINIT               CPU (SEC)  = 0.002101          ZIIP = 0.000000              
   LEVEL = PHASE                  ELAPSED TIME (SEC) = 0.021                                              
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                48            6             2                                                        
  BP32K               9            5                                                                     1
  BP32K               5            3                                                                      
  TOTAL              62           14             2                                                       1
                                                                                                          
  DDNAME        DS OPEN     DS CLOSE      READ I/O     WRITE I/O    I/O CHECKS      I/O WAIT    END OF VOL
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  SYSPRINT                                                     3             3                            
                                                                                                          
  TOTAL                                                        3             3                            
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = COPYRDN0001      "     CPU (SEC)  = 0.008764          ZIIP = 0.273090              
   LEVEL = SUBPHASE               ELAPSED TIME (SEC) = 43.033                                             
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0            223486            3          2713             1          4982                            
  BP32K              73           50                                                                     1
  BP32K               2            2                                                                      
                                                                                                          
  TOTAL          223561           55          2713             1          4982                           1
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = COPYWDN0001      "     CPU (SEC)  = 0.357434          ZIIP = 0.000000              
   LEVEL = SUBPHASE               ELAPSED TIME (SEC) = 43.032                                             
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = Pipe Statistics                                                                                
    TYPE = COPY Data Pipe000                                                                              
                                                                                                          
  Records in:                                     222,110                                                 
  Records out:                                    222,110                                                 
  Waits on full pipe:                                 360                                                 
  Waits on empty pipe:                                  0                                                 
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = COPY                   CPU (SEC)  = 0.004909          ZIIP = 0.000000              
   LEVEL = PHASE                  ELAPSED TIME (SEC) = 43.167                                             
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                22            7             1                                                        
  BP32K               9            8                                                                      
                                                                                                          
  TOTAL              31           15             1                                                        
                                                                                                          
  DDNAME        DS OPEN     DS CLOSE      READ I/O     WRITE I/O    I/O CHECKS      I/O WAIT    END OF VOL
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  SYSPRINT                                                     4             4                            
  SYSC1001            1            1                       37022         37022        38.793             1
                                                                                                          
  TOTAL               1            1                       37026         37026        38.793             1
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = UTILTERM               CPU (SEC)  = 0.000150          ZIIP = 0.000000              
   LEVEL = PHASE                  ELAPSED TIME (SEC) = 0.002                                              
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                 2                                                                                   
  BP32K               2            1                                                                      
                                                                                                          
  TOTAL               4            1                                                                      
                                                                                                          
----------------------------------------------------------------------------------------------------------
INTERVAL = COPY                   CPU (SEC)  = 0.373401          ZIIP = 0.273090              
   LEVEL = UTILITY                ELAPSED TIME (SEC) = 43.191                                             
                                                                                                          
  BUF POOL     GETPAGES     SYS SETW    SYNC READS    SYNC WRITE    SEQ PREFCH   LIST PREFCH    DYN PREFCH
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  BP0                72           13             3                                                        
  BP32K              20           14                                                                     1
  BP32K               5            3                                                                      
                                                                                                          
  TOTAL              97           30             3                                                       1
                                                                                                          
  DDNAME        DS OPEN     DS CLOSE      READ I/O     WRITE I/O    I/O CHECKS      I/O WAIT    END OF VOL
  --------   ----------   ----------    ----------    ----------    ----------   -----------    ----------
  SYSPRINT                                                     7             7                            
  SYSC1001            1            1                       37022         37022        38.793             1
                                                                                                          
  TOTAL               1            1                       37029         37029        38.793             1
                                                                                                          
----------------------------------------------------------------------------------------------------------

Here, you can easily see that the only phase with any zIIP values is the INTERVAL = COPYRDN0001 which is obviously the COPYR IBM mentioned, so that is 100% correct!

Bells and Whistles?

Here’s a complete list of all the z/OS parameters of interest in the IEAOPTxx dataset that are to do with zIIPs:

1. IIPHONORPRIORITY=xxx – Default is YES.
2. PROJECTCPU=xxx – Default is NO. Whether to report possible zIIP / zAAP offload data.
3. HIPERDISPATCH=xxx – Default is YES.
4. MT_ZIIP_MODE=n – Default is 1. This is the multithreading flag, changing it to 2 enables zIIP multithreading and, sort of, doubles your number of zIIPs.
5. CCCAWMT=nnnn – Default is 3200 (3.2 ms). This is the time z/OS waits before waking up idle CPs or zIIPs.
6. ZIIPAWMT=nnnn – Default is 3200 (3.2ms). This is the time to wait before a busy zIIP asks for help (IIPHONORPRIORITY=YES)

All of these parameters, with the possible exception of MT_ZIIP_MODE, should normally be left at their default values unless you really know what you are doing and what is running on your system! The multithreading parameter is naturally only supported on systems where the zIIP can go multithreading.

Anything Else?

Security encryption can run on zIIP and, as everything is encrypted these days, it can be a very good idea. XML usage in COBOL, PL/I, Java, CICS, IMS etc., and Machine learning with ONNX. Python AI and ML workloads are 70% eligible. System Recovery Boost z15 and above and for SVC dumps z16 and above. The Java workload within z/OSMF can go up to 95% offload according to IBM internal tests. With the ratio of zIIP to CP now gone the sky is basically the limit!

Saving the Day?

What are zIIPs saving your firm? I would love to get a screen shot of the output of that SQL!

TTFN

Roy Boxwell