2017-06 APPLCOMPAT in Db2 12: a little bit more agile?

How to set Collection Ids for a reopt 2, 3 or 4 (Runtime optimization) and avoid the default collection NULLID ?

Following on from my last newsletter, I have had to do some research about Collection Ids and how to set them.


Last newsletter:
2017-05 Db2 version 12: A little bit too agile?
How to handle APPLCOMPAT when it comes to Dynamic SQL. A support to manage Db2 12 “agile” release.

Driven to distraction?

The first thing you learn, is that the cli.ini file is now gone…all of the data is now found in the db2dsdriver.cfg file. OK, how does this all hang together?

First Contact

The very first thing that happens, is it looks up the name and the address of the desired Db2 on z/OS. This is just the TCP/IP data to initiate “first contact”. Once the first contact is done, the User Id and Password are required to validate the connection. All well and good.

If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes

Reoptimize this!

Then it gets ugly… If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes (Now please re-read my last newsletter to see why that is of major interest!).

But, what happens if you want to use reopt 2, 3 or 4 ?


Quick aside:

The reopt level determines what style of run time optimization to enable on the host. You can set:

  • Reopt 2 – No optimization (and this is the default),
  • Reopt 3 – To get REOPT(ONCE) behavior – so the first time that SQL comes in to execute, it gets optimized with the literal values in it, and then not again, or
  • Reopt 4 – To get REOPT(ALWAYS) behavior, so it drives a re-optimization every time that SQL comes in.

I have NULL idea what you are talking about…

So back to my question…

What happens if you wish to use reopt 2, 3 or 4? Well, you have a problem, as these must use the reserved collection ids NULLID, NULLIDR1 and NULLIDRA respectively, which (remember my last newsletter), all die horribly with APPLCOMPAT getting involved…

It gets worse

Naturally, there is a dark lining to this cloud! The use of reopt actually disallows the use of CurrentPackageSet, which was the only way of managing APPLCOMPAT. What on Earth can you do…I have no idea…

Help is on the way…

We are conducting a Design Council in Germany  (September 4-5th, 2017) about IBMs Continuous Delivery for Db2 in September, where we will discuss all of this with customers and Db2 users, but I would love to get some feedback from you all too!

 

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

2017-05 Db2 version 12: A little bit too agile?

 

How to handle APPLCOMPAT when it comes to Dynamic SQL?

I have been involved in testing in Db2 version 12 for a while now, and I think we need a discussion about a few features that come in with this “agile” release.

APPLCOMPAT to the rescue?

First up, is the use of APPLCOMPAT – This is now available throughout the SQL, but comes with a few problems. If you have static SQL then all is fine and dandy, as the APPLCOMPAT used is stored in the Db2 Catalog. You can easily refer to it and check which package is using what APPLCOMPAT. The real trouble starts with Dynamic SQL.

Dynamic SQL intro

A quick discourse about Dynamic SQL is now required…

All Dynamic SQL that runs, must run “under the control” of a Package.

This means that we all have loads of empty packages that are just used to run Dynamic SQL. You all have the SYSLHxxx, SYSLNxxx style Packages and probably loads more. These Packages are used for authentication, tracing, and validation of requests from remote users.

Package is the Boss

This is all good, apart from when a new Db2 12 Function Level (from now on FL) is activated. Why? Because the “package is boss” – If the package was bound at FL500 and you activate FL501, then any SQL that executes in that package that attempts to use an FL501 statement fails. If the SQL attempts to SET CURRENT APPLCOMPAT = ‘V12R1M501’ it will, of course, also fail.

REBIND the world?

To “fix” this, you simply have to REBIND the package to the new FL level.

Sounds simple, huh?

Well, what that means is that *all* SQL that uses that package will get the ability to go to FL501 straightaway, unless they are coded with SET CURRENT APPLCOMPAT = ‘V12R1M500’ or which level you would prefer…

We all have that coded in our JAVA programs don’t we?

Dynamic Packages always allocated

So the problem now is – You have hundreds of Packages that you must rebind, but you dare not rebind them! Even worse, is that you probably *cannot* rebind them anyway, as they are permanently allocated and in use!

. Imagine how many Dynamic SQLs are running in your shop?
. Can you flush the DSC and rebind all of your “empty” Packages?
. When can you plan such an outage?

COLLECTIONs can help

One work-around is to have a new COLLECTION, which the empty packages are bound to again. This works great, apart from one tiny little problem… The COLLID must then be set/changed in all of the CLI.INI or API places where it is currently set, or just defaulted to, today! At one of my customer sites that would be over 8,000 files to update!

So now my questions to the readers out there


How do you plan to manage this?

How do you plan to roll-out FL levels?

If using a new collection, how many CLI.INIs etc. must be changed?


 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

2017-03 Db2 11 RBA/LRSN Migration 6 to 10 bytes

Db2 11 RBA/LRSN Migration 6 to 10 bytes: How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s?
Are you thinking days, months, years??

This month, I want to discuss the pretty big changes that appeared in Db2 11, as these have been made even more important with Db2 12 coming around the corner.

End-of-Time

In Db2 11, the so-called end-of-time or end-of-log problem finally got addressed. The old “it will never run out” six byte RBA (2 to the power of 48 or 256TB) was extended on the left with four bytes of zeroes up to 10 bytes, and the “valid until 17th September 2042 and incremented every 16 microseconds” six byte LRSN was also extended on the left with one byte, and on the right with three bytes. This means the RBA can address 2 to the power of 80 or one Yotta Byte and the LRSN goes up to about the year 36,000. More importantly it goes down to nearly the picosecond!

2042! I will be retired by then…

Well, hold your horses! The end-of-time problem can occur way earlier than that, due to an idiosyncrasy of upgrading to datasharing from non-datasharing. To do this, Db2 must transform your current 6 Byte RBA into a 6 byte LRSN. Naturally an LRSN is a date/time and the RBA just a byte address, so Db2 basically rounds the RBA up to be an LRSN and adds a so-called “DELTA” value to the BSDS. This delta value can surprise you, badly! One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018… Whoops!

This delta value can surprise you, badly!
One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018…

 

Check your delta!

Just run a DSNJU004 on your BSDS and check for the STCK TO LRSN DELTA line:

DSNJCNVT CONVERSION PROGRAM HAS RUN   DDNAME=SYSUT1                  
   LOG MAP OF BSDS DATA SET COPY 1, DSN=SB10.BSDS01
   LTIME INDICATES LOCAL TIME, ALL OTHER TIMES ARE GMT.
         DATA SHARING MODE IS ON
         SYSTEM TIMESTAMP   - DATE=2017.079  LTIME=19:29:46.01
         UTILITY TIMESTAMP  - DATE=2016.071  LTIME=18:19:43.66 
         VSAM CATALOG NAME=SB10
         HIGHEST RBA WRITTEN       000000000000FCD54000 2017.079 20:29:46.0
         HIGHEST RBA OFFLOADED     000000000000FBF0AFFF
         RBA WHEN CONVERTED TO V4  00000000000000000000
         MAX RBA FOR TORBA         00000000000000000000
         MIN RBA FOR TORBA         00000000000000000000
         STCK TO LRSN DELTA        00000000000000000000

Here in one of our baby datasharing systems there is no delta, so I can retire!

Roadworks ahead! DB2 11 RBA- LRSN Migration 6 to 10 Bytes - Db2 zOS

I like to think of the RBA/LRSN like a three lane German highway (so six lanes in total) that is getting widened to five lanes (so ten in total) You just *know* that the throughput will go up and the traffic jams will go down!

Will it really help?

Well, the out-of-the-box benefits are threefold:

 

  1. No LRSN “spin” – In datasharing a member must wait or “spin”, for some styles of inserts/updates, until it gets a unique LRSN. Now, with faster and faster machines, CPU is being wasted doing nothing but spinning its heels! The IBM Labs state that the percentage overhead ranges between 0% – 6%, and that heavy batch can be much more – even as much as 20%+
  2.  All of the conversion from and to is then gone. Externally always 10, internally a mix. The puffing up and the shrinking down also takes CPU cycles
  3. Converting Logs and tables “may yield a few percent” performance improvement – This again from the IBM Labs.

Road map required!

How to get there? Well first, in NFM, migrate the BSDS from each member one at a time and when all members are done, analyze your workload and pick the biggest usage of UPDATE/DELETE. REORG these objects at the TP/TS level. This gives the biggest improvement earliest.

Rolling on the REORGs

Then trickle through the REORGS on *all* user objects. Here *all* means *all* ! Well actually not all … clone tables cannot be migrated to 10 bytes so you must drop the clone table, REORG the base tablespace and then recreate the clone table.

Remember here to make sure your ZPARMS (OBJECT_CREATE_FORMAT EXTENDED and UTILITY_OBJECT_CONVERSION NOBASIC) are correct and that the Reordered Row Format (RRF) is enabled, since Basic Row Format (BRF) is deprecated!

Forward thinking!

Other things to plan, and think about, at the same time are:

Migrate all INDEX based Partitioned Objects to TABLE based Partitioned Objects and then migrate these to be partitioned by Range PBR (UTS). First an ALTER INDEX xxx.yyy NOT CLUSTER to make it table based instead of index based. Then an ALTER TABLESPACE xx.yy SEGSIZE nn to make it into a PBR (UTS)
Migrate any single table simple/segmented objects to be Partitioned by Growth PBG (UTS). Just an ALTER TABLESPACE xx.yy MAXPARTITIONS 1 is enough
Note that these simple ALTERS cause PACKAGE Invalidation and so must be timed correctly.

Why all the fuss?

Why do this? Well, remember that the UTS, as the underlying tablespace, is the *only* space where Db2 is adding new functionality and performance. Think about inline LOBs, especially with COMPRESS YES, FTB in Db2 12, HASH method, Fast Insert etc. It is clearly the aim of IBM to go to a purely UTS world at some point.

Relative Page Numbering – Should I wait?

In Db2 12, the Relative Page Numbering (RPN) system enables varying sizes of DSSIZE, also at the Index Level. This is seriously good news, *but* it requires a TS level REORG to get there, and you must be in Db2 12. So if you have *vast* history style partitioned tables, you would wait until you are in Db2 12 before you actually do the first big REORG, including going to 10 byte RBA/LRSN if space and time are a big worry.

Are you alone?

No, of course not! Our product RealTimeDatabaseExpert (RTDX) can do all this for you. We also guarantee that no object will be left “hanging”, due to using our BatchControl technology. We also use look-ahead features with time windows to check that we are *not* going to encroach into productive timeframes. If you also have our WorkLoadExpert (WLX) software installed, we can even pick the right moment to fire off the REORGs that you need when you can actually run them! This permanent trickle of reorgs means that within a few years you are done and ready to take advantage of that ten-lane Autobahn!

 

This newsletter is interrupted by our marketing department who are calling out

By the way, this tailored solution for this special requirement is also available for rent!”


DB2 11 RBA- LRSN Migration 6 to 10 Bytes - Db2 zOS

See our RTDX Flyer and RealTime DBAExpert page


Now back to our usual programming…

How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s? Are you thinking days, months, years?? I would love to know! To help you in this phase here are some little SQLs that will give you an idea of how far you have to go…based on the assumption that anything REORGed in the last six months will probably get REORGed again.

SELECT  COUNT(*)                          AS INDEXPARTS_TOTAL
FROM SYSIBM.SYSINDEXSPACESTATS;
SELECT  COUNT(*)                          AS INDEXPARTS
      , SUM(COALESCE(NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE (REORGLASTTIME IS NULL
  OR (REORGLASTTIME IS NOT NULL
  AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR
       (REORGLASTTIME IS NOT NULL
   AND (LOADRLASTTIME IS NULL
    OR (LOADRLASTTIME IS NOT NULL
    AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))
;                                                          
SELECT  COUNT(*)                          AS TABLEPARTS_TOTAL
FROM SYSIBM.SYSTABLESPACESTATS
; 
SELECT  COUNT(*)                          AS TABLEPARTS
      , SUM(COALESCE(NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(REORGUPDATES, 0))    AS REORGUPDATES
      , SUM(COALESCE(REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS
WHERE (REORGLASTTIME IS NULL
  OR (REORGLASTTIME IS NOT NULL
  AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR (REORGLASTTIME IS NOT NULL
   AND (LOADRLASTTIME IS NULL
    OR (LOADRLASTTIME IS NOT NULL
    AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))    
;                                                           

Naturally if you are *in* Db2 11 you can easily extend these queries to give you proper feedback like:

SELECT  COUNT(*)                          AS INDEXPARTS 
      , SUM(CASE WHEN B.RBA_FORMAT = 'E' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_EXTENDED
      , SUM(CASE WHEN B.RBA_FORMAT = 'U'  
                       THEN 1 ELSE 0 END) AS INDEXPARTS_DEFINE_NO 
      , SUM(CASE WHEN B.RBA_FORMAT = 'B' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_BASIC
      , SUM(CASE WHEN B.RBA_FORMAT = ' ' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_MIGRATED
FROM SYSIBM.SYSINDEXSPACESTATS A
    ,SYSIBM.SYSINDEXPART       B
WHERE A.CREATOR   = B.IXCREATOR
  AND A.NAME      = B.IXNAME
  AND A.PARTITION = B.PARTITION 
;                                                                    
SELECT  B.RBA_FORMAT                               AS INDEXPART_FORMAT
      , COUNT(*)                                   AS INDEXPARTS 
      , SUM(1E00 * COALESCE(A.NACTIVE, 0))         AS NACTIVE
      , SUM(1E00 * COALESCE(A.REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(1E00 * COALESCE(A.REORGDELETES, 0))    AS REORGDELETES
      , SUM(1E00 * COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS A                                    
    ,SYSIBM.SYSINDEXPART       B
WHERE A.CREATOR   = B.IXCREATOR 
  AND A.NAME      = B.IXNAME 
  AND A.PARTITION = B.PARTITION
  AND B.RBA_FORMAT IN ('B' , ' ')
  AND ((A.REORGLASTTIME IS NULL 
  OR (A.REORGLASTTIME IS NOT NULL
  AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR                                                                 
    (A.REORGLASTTIME IS NOT NULL
    AND (A.LOADRLASTTIME IS NULL
    OR (A.LOADRLASTTIME IS NOT NULL 
    AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))
  GROUP BY B.RBA_FORMAT
;                                                                    

SELECT  COUNT(*)                          AS TABLEPARTS 
      , SUM(CASE WHEN B.RBA_FORMAT = 'E' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_EXTENDED 
      , SUM(CASE WHEN B.RBA_FORMAT = 'U' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_DEFINE_NO 
      , SUM(CASE WHEN B.RBA_FORMAT = 'B'
                       THEN 1 ELSE 0 END) AS TABLEPARTS_BASIC 
      , SUM(CASE WHEN B.RBA_FORMAT = ' ' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_MIGRATED
      FROM SYSIBM.SYSTABLESPACESTATS A   
          ,SYSIBM.SYSTABLEPART       B 
WHERE A.DBNAME    = B.DBNAME 
  AND A.NAME      = B.TSNAME 
  AND A.PARTITION = B.PARTITION 
;                                                             
SELECT  B.RBA_FORMAT                        AS TABLEPART_FORMAT
      , COUNT(*)                            AS TABLEPARTS
      , SUM(COALESCE(A.NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(A.REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(A.REORGUPDATES, 0))    AS REORGUPDATES
      , SUM(COALESCE(A.REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS A
    ,SYSIBM.SYSTABLEPART       B
WHERE A.DBNAME    = B.DBNAME
  AND A.NAME      = B.TSNAME
  AND A.PARTITION = B.PARTITION
  AND B.RBA_FORMAT IN ('B' , ' ')
  AND ((A.REORGLASTTIME IS NULL
  OR (A.REORGLASTTIME IS NOT NULL
  AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))     
  OR                                                          
     (A.REORGLASTTIME IS NOT NULL                             
   AND (A.LOADRLASTTIME IS NULL                               
    OR (A.LOADRLASTTIME IS NOT NULL                           
    AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))    
GROUP BY B.RBA_FORMAT                                         
;

Here the RBA_FORMAT value “E” is extended (10 Bytes), “U” is DEFINE NO but if you have the ZPARM OBJECT_CREATE_FORMAT set to EXTENDED (which you should!) then when an insert happens it will get created as extended. The problem children are “B” and Blank.

One late bit of info that came from a reader that some of you might find interesting, or perhaps worrying, is this:

There is a potentially time-consuming pre-requisite to the migration and that is the changes made to InfoSphere Information Replication 10.2.1 which expand two columns COMMITSEQ and INTENTSEQ from 10 to 16 bytes to accommodate 10-bytes RBA/LRSN. This modification has a big impact on application programs that make use of these two columns, especially COBOL programs that must be changed and recompiled and regression tested; as well as all SORT statement on the data files extract that include these columns, etc.

 

As usual any comments or criticisms are greatly appreciated

TTFN, Roy Boxwell

 

Share it!

2017-02 Why SIZE still matters in Db2 12

What has changed for space management in Db2 12?

How to avoid SIZE limits in Db2 12 like in previous Db2 Releases?

How to monitor the maximum possible SIZE of table and index spaces and table and index partitions?

SIZE in Db2 12: Now that Db2 12 has gone GA, I thought it would be nice to do a quick re-recap of Space management and its problems over the releases. My “old” newsletter 2014-05: Why SIZE matters for Db2 still receives a lot of hits on our website, so I know that this is a big topic of interest for many of you. Some nifty things have been introduced in Db2 12 to make space a lot easier to use and manage.

In the beginning…secondary allocation for tablespaces and indexes since Db2 V7, Db2 V8…

Since Db2 V8, the DBAs of this world have all had the ability to forget about PQTY and SQTY in the DDL for Tablespaces and Indexes. At first, nearly no-one trusted the sliding scale algorithm, and SOFTWARE ENGINEERING’s product Space AssuranceExpert (aka SAX) monitored and reacted instantly to secondary allocations.

However, we now have Db2 12, and I thought it would be interesting to review what was done in Db2 V7 (when our SAX was launched), and the difference nowadays in the Db2 12 world.

IFCID issuing for space extents

Every time a secondary allocation is done in Db2, it can be made to spit out an IFCID. SAX runs as a started task, active 24×7, from Db2 start up until just before Db2 shut down. It catches all of these IFCIDs thrown by Db2, and performs an analysis with six basic questions:

1Can this dataset reach its maximum physical size *before* running out of physical extents? (The actual size is dependent on the “geometry” of the object of course!)
2Will this object run out of datasets? (The number of datasets an object can have is, once again, dependent on the “geometry” of the object)
3Is this partition nearing its maximum size?
4Did Db2 ask for one extent but got more back?
5Are any of my SMS disk storage pools running out of space?
6Are there any SEQUENCES that are about to hit the wall?

(Numbers five and six are actually triggered by a timer, naturally.)

Can this dataset reach its maximum size before running out of extents?

Remember, back in those old days of Db2 V7? We only had 255 extents and 254 partitions, but datasets could still get pretty big pretty fast.

The problem lots of shops had, was that an important dataset would “hit the buffers” of maximum number of extents *way* before it ever ran out of physical space. Thus causing grief, wailing and gnashing of teeth! SAX stopped all this by giving WTO “heads-up” style messages in two flavors. First, a warning message, and then a critical message. This gave DBAs and space managers much needed time to plan for the outage and the, inevitably, long running REORG to actually action the required ALTER, or perhaps even any DROP/RECREATE that had to be done.

IBM also noticed this problem and so introduced in Db2 V8 the “sliding scale” of secondary allocations, as long as the ZPARM OPTIMIZE EXTENT SIZING field (MGEXTSZ) was set to YES (this is the default from Db2 9, by the way). Of course, to really use this, you then had to ALTER all of the existing spaces PQTY and SQTY to be -1, and then remember to delete all PRIQTY and SECQTY lines in your DDL and also rely on the TSQTY and IXQTY ZPARMs giving a big enough “first default”. (By the way, defaults for these two ZPARMS are 0, which is actually translated to be 720k or one cylinder for normal spaces and 7200k or 10 cylinders for LOB spaces). This all probably explains why the take up of this great feature has not been that spectacular and, in fact, Listserv *still* gets questions about “How good is this feature?” This also explains why the primary reason for having SAX is still valid at most shops today!

However, most shops these days tend to ignore the extents problem and only REORG when over 1000 extents have been allocated. This is no problem for SAX, as it knows the SECQTY and the MGEXTSZ ZPARM settings and can decide to “ignore” an IFCID for extent and ALTER SECQTY processing if the SECQTY is -1 and the MGEXTSZ is YES.

Will this object run out of datasets?

Now the problem of running out of datasets is very, very evil indeed… For a non-partitioned space, you can have up to 32 datasets.  Db2 will happily allocate away and you will never know, or even be informed, if, and when, the last possible dataset has just been allocated and, of course, you will not know that the 33rd one cannot be allocated until you get a -904 unavailable resource error! By definition this is “not good”, as you must do a HUGE REORG with a bunch of managers breathing down your neck and *not* make any mistakes with the new allocations. (Again, this is a very good reason to have SAX doing all the monitoring and triggering early warning “heads-up” style messages!)

Is this partition nearing its maximum size?

A partition running out of space is rare, but when it does happen it is, of course, a disaster! The idea in SAX, is to warn when “the end is near” for any of the partitions in use and thus, as before, allow time for the ALTER etc.

Did Db2 ask for one extent but got more back?

Degenerated extents are annoying as well. You have only 255 or 7,257 extents, Db2 requests one but gets up to five back! This is “wasting” your precious supply of extents and so SAX can also warn you if this starts happening. Remedial action can again be planned to correct the problem, (normally a volume defrag in this case). Now in z/OS 1.7 “Extent Constraint Removal” was introduced for the DATACLAS which, if set to “Y”, allows 7,257 extents but still limits you to 123 extents per volume and 59 volumes. So watch out if you are using huge “virtual” disks (E.g. MOD 54 or EAV), as you can end up wasting space because you still cannot exceed 123 extents per volume.

SAX also takes care of duplicate recording – This is where an Extent is registered but SMS “consolidates it into the primary/existing extent – This would normally get logged as an extent but SAX sees this and does not report it as an extent.

Are any of my SMS disk storage pools running out of space?

When an SMS Pool runs out of space, either for sort/work or image copy, it is *not* good! The idea here, is to also give a “heads-up” style alert. The DBA can trigger the space management people to have a look at the state and size of the SMS storage groups this time alerted by percentage used or GBs of space free.

Are there any SEQUENCES that are about to hit the wall?

The usage of SEQUENCES has taken off. Nowadays shops can run into the problem of SEQUENCES hitting the maximum/minimum number for a NOCYCLE defined sequence. SAX tests sequences at the same time as the SMS groups to warn about any encroaching problem with WTO/MSG and reporting.


What was new in Db2 V8?

Db2 V8 introduced a big change – Partitions went up to a maximum of 4,096, and the calculation about how many pieces your NPI can have got “a little bit complex” (see also my previous newsletter: “2014-04 Are you going to pieces”).


What was new in Db2 9?  PBG and UTS spaces

In Db2 9 the next major advance came with UTS spaces. The one that caused the most grief was, of course, PBG. Why? Well, the first problem was that some people went mad and used MAXPARTITIONS 4096 right from the get-go. They then found out this could not simply be changed and ended up being a huge problem. IBM came out with a bunch of fixes for these people, but the recommendation is still true today: “Use the number you expect to use!”

PBGs, however, came with a new set of space management problems:

1By definition every partition is full, and so a TP REORG is “dangerous” -especially if you have VARCHAR, and even more so if compressed.
2ALTER at TP level is not supported for PBG.
3Getting rid of empty partitions was not supported
4Adding partitions dynamically (by command) was not supported.
5What to do if the partition that is “in use” is growing and is

a – The last allowed Partition
b – MAXPARTITIONS is set to one?

Now these are “non trivial” because the Db2 catalog is so defined and you would not want an alert every time someone created a table or index!

The trick here, is to treat these conditions as if it was a normal space and so, instead of warning that you are using the last part, it waits until you are using, e.g. 80% of that part. Then, e.g. at 90% comes the critical threshold warning.

Big changes happened here in Db2 12.


What was new in Db2 10?

With Db2 10 came the ability to ALTER PBGs to add parts which made using DSN1COPY to clone data around a lot better!


What was new in Db2 11?

In Db2 11 the REORG utility can be used to remove any empty parts in PBGs by the use of the ZPARM REORG_DROP_PBG_PARTS being set to ENABLE (DISABLE is the default).


What is new in Db2 12?

Now in Db2 12 there is partition independence for DSSIZE. Before, all partitions had to have the same maximum size (DSSIZE). Now you can have different sizes for different parts. This requires either making a new tablespace (UTS Relative Page Numbering), or an ALTER and TS level reorg of an existing UTS space. The tablespace goes relative page numbering and the RID increases in size to seven bytes hence the need for a TS level REORG. The Partitioning indexes also get DSSIZE so they can vary in size as well. Once you are there, all of the Partitions can then be ALTERed up in size with no outage! This is really, really good!

REORG of a PBG can “spill” into a new partition. This is also really good, as it was the major problem with PBG TP level reorgs. The chance of LOB data going into COPYP during the log apply phase has been stopped – Thankfully! Finally, delete of empty partitions is controlled with a utility DROP PART syntax.


The SAX way for Space monitoring

The SAX tool way of processing all this info is neatly summarised in the help panel of the tool itself:

SUPERVISE LPS

Supervise linear pagesets. If specified, a warning is issued
in case of high allocated reaches this percentage of the
maximum data set size for partitioned objects.

For non-partitioned objects, a warning is issued for every
newly allocated data set as soon as the data set number
reaches this percentage of the maximum number of data sets:

Two different values may be entered for warning and critical
values with different message ids. This may be useful for
automation reasons (see below).

Object type: TABLESPACE      ! Maximum number of data sets
-----------------------------+----------------------------
LOB tablespaces              ! 254
-----------------------------+----------------------------
Non-partitioned tablespaces  ! 32
-----------------------------+----------------------------
Partitioned tablespaces      ! 1 (Percent used check)
-----------------------------+----------------------------
Partitioned By Growth        ! MAXPARTITIONS. LPS check if
tablespaces                  ! more than one. If on last
                             ! partition then percent used.
-----------------------------+----------------------------
Object type: INDEX           ! Maximum number of data sets
-----------------------------+----------------------------
Non-partitioned indexes on   ! MIN ( 4096 , 2 power 32 /
tablespace with LARGE,       !      ( DSSIZE / TS PGSIZE))
DSSIZE, or more than 64      ! Eg: 128 GB DSSIZE with
Partitions                   !       8 KB Tablespace Page
                             ! gives 256 Pieces (datasets)
                             ! Or    4 GB DSSIZE with
                             !       4 KB Tablespace Page
                             ! gives 4096 Pieces (datasets)
-----------------------------+----------------------------
Non-partitioned indexes      ! 32
otherwise                    !
-----------------------------+----------------------------
Partitioned indexes          ! 1 (Percent used check)
-----------------------------+----------------------------
To support automation based on WTO ids two different
thresholds may be specified:
Field (1) specifies a warning threshold using WTO ids
O2RTSU04 - 12W  (non-partitioned spaces)
O2RTSU04 - 14W  (partitioned spaces)
O2RTSU04 - 16W  (partition by growth spaces)
Field (2) specifies a critical threshold using WTO ids
O2RTSU04 - 13W  (non-partitioned spaces)
O2RTSU04 - 15W  (partitioned spaces)
O2RTSU04 - 17W  (partition by growth spaces)

AUDIT DEGENERATED XTS
Audit secondary quantity for de-generated extents. If
specified, a warning is issued in case of the last extent
does not reach this percentage of the SECQTY specified
in the Db2 catalog. If this field is left blank, no
auditing is performed.

AUDIT SMS STOGROUPS
Should the Space AssuranceExpert audit SMS stogroups. Y/N
If Y is entered, a pop-up window will allow you to enter up
to 24 SMS storage groups which will be audited.
If WARN IF % ALLOC > or WARN IF GB FREE < is specified and
exceeded, a warning (WTO) will be issued.

CHECK SYSSEQUENCES
Should the Monitor also check for SYSIBM.SYSSEQUENCES that
are running out of room every PING minutes?

N  - do nothing.  This is the default.
I  - check Identity Columns and Doc Ids for XML.
S  - check User Defined Sequences.
B  - do both.

PERCENT USED
If checking of SEQUENCES is desired then a threshold
percentage must be given from 1 to 99. If this percentage of
the available sequences is exceeded then an action is
triggered.

EXCEEDED ACTION
When a percentage is exceeded this specifies what type and
and which style of message should be externalized.

N  - do nothing.  This is the default.
W  - to write out a WTO.
M  - to write a message to the job log.
B  - do both.

To support automation based on WTO ids the following
messages are output:

O2RTS000 - 20W  (SEQUENCES MAXVALUE)
O2RTS000 - 21W  (SEQUENCES MINVALUE)
O2RTS000 - 22W  (IDENTITY MAXVALUE)
O2RTS000 - 23W  (IDENTITY MINVALUE)

So now you know why size still matters for Db2 12! The big question now is: “Are your space management and monitoring tools up-to-date, or are they still Db2 V7?”

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

2017-01 Db2 12 technical overview: Roy’s first features review

This Db2 12 technical overview presents in an “easy to read” table list a review of new Db2 12 features

Have you encountered any other Db2 12 changes you’d like to discuss?

 

Now that Db2 12 has gone GA I can finally talk about it. So here’s another new Features “first look” at what I think is cool, great, or odd ! This is my personal list for a Db2 12 technical overview – in no particular order :

  •  Db2 12 SQL Optimizer, triggers, Arrays, Merge, UNICODE Columns. Temporal, SQL pagination, SQL Stability, Log, Partitions,…
  •  Data Sharing
  •  Utilities DSN1COPY, Alternate Copy Tools, Audit, REORG, PBG reorgs, COMPRESSRATIO, RELOAD, RO tablespaces, LOAD, BACKUP and Recovery, PiT, RUNSTATS…

 

AGILE This release of Db2 will be ”the last” release, as Db2 Development has gone all agile on us and will be doing Continuous Delivery (CD) from now on. CD promises Easier, Cheaper, Faster and Simpler Db2 maintenance and the quick realization of new functionality.

 


Db2 12 – SQL


Optimizer


MQT or Table expression columns are “trimmed” if they are not used in the outer query.

In LEFT OUTER JOIN, if columns are not used, they can be Pruned.

UNION ALL gets major work when pushing down join predicates as well as pushing down ORDER BY and FETCH FIRST

Outer table joins can get reordered to avoid unnecessary materializations

User-defined functions get two improvements with merge and the introduction of indexes on the join or correlation predicates that are passed in as parameters

Adaptive Index is designed for Multi Index and single index list prefetch to determine at execute time the filtering of each index. This ensures the optimal execution sequence of indexes or, perhaps, a quicker fallback to Tablespace scan if no filtering index exists.


TRIGGERS

The new “advanced” triggers enable SQL and Global variable usage and SQL PL.


ARRAYS

Get a couple of nice new features, specifically the use of a global variable as an array type and the ability to use the ARRAY_AGG without forcing an ORDER BY.


GLOBAL VARIABLES

Get LOB support and in a SET they can be the target.


PureXML

The XMLMODIFY can do multiple document updates in a single invocation. Various XML performance boosts are also included, e.g. XMLTable and the XSLTRANSFORM allows transformations to different formats.


JSON

When using the JSON_VAL function the first argument must not now always be a BLOB. It can be a view, CASE, table expression, trigger transition variable or SQL PL variable or parameter.


MERGE

Is now a full MERGE with the ability to use table references with multiple MATCHED clauses, including DELETE operations.


SQL PAGINATION

The ability of Db2 to “understand” typical paging has been greatly boosted. Typically it was always coded like:

SELECT blah blah blah
FROM mytable
WHERE (SURNAME = ‘BOXWELL’ AND FORENAME > ‘ROY’)
        OR (SURNAME > ‘BOXWELL’)

This is pretty horrible for the Db2 optimizer but we *all* know what we really mean! Now in Db2 12, so does the optimizer! Sadly you must rewrite your queries a little so this example becomes:

SELECT blah blah blah
FROM mytable
WHERE (SURNAME, FORENAME) > (‘BOXWELL’, ‘ROY’)

Also with this comes a nice little feature called OFFSET ROWS. Typically, this is for when the connection to the server is a bit shaky and so after some paging, when the cursor is reopened, the code “knows” it can miss the first 60 rows, so the cursor changes to be:

SELECT * FROM mytable OFFSET 60 ROWS

Nice feature, but beware of polluting the DSC! It is much better to use a parameter marker for these Offsets!


UNICODE Columns

In DB2 11, we got a “fix” for UNICODE columns that was really a “crutch”. This has now been fixed with real UNICODE columns in DB2 12. You must migrate your existing data though!


Piece-wise DELETE

This is a feature I have wanted for decades! Simply add the FETCH FIRST nnnn ROWS ONLY within a DELETE and then programmatically loop around until you are done. Much easier than the method we have today of DECLAREing a CURSOR with an UPDATE of a dummy column and the DELETE WHERE CURRENT OF and after 5000 or so issue a COMMIT.


TEMPORAL RI

You can now add RI as normal and not be forced to use a trigger or stored procedure.


TEMPORAL TABLES

Get the ability to not just be inclusive-exclusive but also inclusive-inclusive.


TEMPORAL Logical Transactions

Another new feature with temporal tables, is the ability to support logical units of work for SYSTEM_TIME. These logical units of work are not determined by COMMIT or ROLLBACK but by using a built-in Global Variable.


PERCENTILE functions

Two new functions PERCENTILE_CONT and PERCENTIL_DISC are new BIFs.


DRDA Fast Load

Is the ability to load data into z/OS DB2 from files sitting on distributed clients.


ODBC

Gets a new INI keyword KEEPDYNAMIC and the connection attribute of SQL_ATTR_KEEP_DYNAMIC.


Obfuscated Code

Mainly of interest to Vendors is the ability to hide your stored procedure, TRIGGER or UDF coding from prying eyes.


RLF for Static SQL

This is a big one! The Resource Limit Facility has always only been available for dynamic SQL. Now you can also use it to cap Static SQL.


TRANSFER OWNERSHIP

This is a very handy way of clearing out all the old owners from a DB2 system.


SQL Stability

Dynamic Plan Stability is nearly the same as BIND QUERY, but the hope is that it will be easier and better to use! But beware of saving all of your dynamic SQL away!

Static Plan Stability gets a good enhancement that allows FREE on the original or previous. What is really good, is that the current version can be in use so there is no application outage anymore.


Insert

New Insert algorythm can be used for faster unclustered insert processing in some cases. Only for UTS MEMBER CLUSTER (This is actually the default for these spaces).


CONCENTARTE LITERALS

Now supported at the Package Level.


FTB

Fast Index Traversal – Especially good for randomly accessed indexes. If the index is unique, and 64 bytes or less, it is eligible. Index is controlled with the new Catalog table SYSIBM.SYSINDEXCONTROL and the -DISPLAY STATS(INDEXMEMORYUSAGE) command.


Log

Active log size can go from 4GB now up to 768GB ! Be careful here!


In-Memory bufferpools

by using PAGESTEAL(NONE) keyword.


PARTITIONS

Finally we get the chance to give each partition its own DSSIZE as well as the Partitioning indexes! This is great, but sadly is only available to an existing space once you have reorged the whole tablespace…However, once you are there, you can then have data and index parts up to 1TB in size, plus, when you do an ALTER of the DSSIZE, it does not cause an outage (as long as you make it bigger!). A side effect of this is that the RID is now seven bytes (see REORG mapping table for other changes). You can now also add partitions in the middle of an existing PBR table.


 

 


Data Sharing


Recovery

of retained locks from a failed member can be handled automatically


LPL and GRECP recovery

LPL and GRECP recovery auto retries three times after waiting three minutes


 


Db2 12 Utilities


DSN1COPY

In DB2 11 this utility got a few sanity checks and now the REPAIR CATALOG utility can fix some of these. The REPAIR CATALOG TEST also looks for some problems caused by misuse/abuse of DSN1COPY.


ALTERNATE COPY POOLS

The usage of BACKUP SYTEM is growing. So is the amount of storage required! The idea here, is to define a set of copy pools, but only one for many DB2 subsystems. The alternate copy pool uses as many volumes as it needs and leaves the other volume free for a different subsystem backup. This reduces the amount of space that must be allocated.


Audit

A new Authorization arrived: UNLOADAUTH to “replace” the “Does the user have SELECT auth on the table?” check that has run up to now. UNLOAD is special and should be controlled over this auth and no longer over just SELECT.


REORG

PBG tablespaces get the best news here!

PBG reorgs can now spill over into a new PBG if the row(s) do not fit back into the original partition. Classic case here, is compressed data that no longer fits back. This forced people to use a TS level reorg or not use compression.

If the PBG contains LOB data and it extended to a new partition in the log apply phase, then the LOB space was left in COPY Pending… pretty horrible and that no longer happens in DB2 12.

Another PBG bonus, is the delete of “emptied” Partitions after a REORG has completed.

Improved FlashCopy support – You can now decide to stop the REORG if the flash copy fails.

New Catalog column COMPRESSRATIO for use by utilities that records the compression savings at the record instead of at the page level.

RELOAD phase can now be offloaded to zIIP.

RO tablespaces can now be REORGed at any SHRLEVEL.

The mapping table gets changed again due to the relative page numbering in the new PARTITION support (seven byte RID).


LOAD


PART REPLACE with dummy input against an empty (PBR) partition could be quicker.

LOAD SHRLEVEL CHANGE PARALLEL support for PBG for SHRLEVEL CHANGE.

Additional zIIP offload, like in REORG, in the RELOAD phase, including the data conversion and loading of the record into the page set.

LOAD RESUME BACKOUT YES to avoid RECP on failure. Adds a new option on LOAD RESUME SHRLEVEL NONE to allow LOAD to back out the rows already loaded upon encountering an error (such as conversion, LOB/XML, duplicate key, referential integrity violation) without leaving the page set in RECP.

PREFORMAT support for auxiliary tables. Support is extended to LOB table spaces and auxiliary indexes.

Maintain MAXASSIGNEDVAL for identity columns. LOAD now maintains the MAXASSIGNEDVAL for user-provided input and resets the value if a LOAD REPLACE is run on the table space.

LOAD REPLACE support for the COMPRESSRATIO column for use by utilities that records the compression savings at the record instead of at the page level column.


BACKUP and RECOVERY

Point-in-Time support for PBGs, Flashcopy FLASHCOPY_PPRCP keyword. As mentioned the default is changed to not recover unchanged objects. MODIFY RECOVERY gets two new options: DELETEDS to delete the datasets and NOCOPYPEND to not set COPY pending after doing the MODIFY.


PiT

Has been improved with the ability to skip unnecessary recoveries. SCOPE UPDATE only processes objects that have been updated up to the TOLOGPOINT or TORBA.


RUNSTATS

New CLUSTERRATIO formula which should better reflect dynamic prefetch. Terry Purcell has stated that it is not a huge change and does not require a RUNSTATS of all tablespaces!


FREQVAL COUNT nn

The COUNT nn is now optional and, if not used, then RUNSTATS will work out the best number for you. This is really, really nice and I would recommend this in an instant! It has also been retro fitted to DB2 11.


Autonomic Statistics with PROFILEs

I am no fan of this, as I believe it makes for a pretty nasty feedback loop where anyone’s “dumb” QMF/SAS/DSNTEP2/SPUFI will get inserted as a PROFILE COLGROUP, and then these PROFILEs will get bigger and bigger until no-one knows which are really useful and which are just fluff! I would recommend setting the ZPARMs STATFDBK_SCOPE to ALL (Default) STATFDBK_PROFILE to NO (Default is YES). When YES is used DB2 12 will create and/or maintain a PROFILE for you. Finally, validate that the SYSTABLES column STATS_FEEDBACK is set to “N” (Default is “Y”) for any and all tables where you do *not* want SYSSTATSFEEDBACK data. E.g. All the DSNDB01 tables where a RUNSTATS is not even allowed!


DSC

DSC Invalidation got switched off by default. In the past *any* RUNSTATS flushed the cache. Now you must add the key word INVALIDATECACHE YES to get this to occur. (Unless you use the REPORT NO UPDATE NONE syntax this still just flushes the DSC)


Inline Stats

Inline Stats got a huge boost with PROFILE support, MOST/BOTH/LEAST and LOAD PARALLEL got inline stats.


 

Have you encountered any other Db2 12 changes you’d like to discuss?

As usual, feel free to email me with questions or comments.

TTFN

Roy Boxwell

 

 

2016-09 Solving SEQUENCE or space problems in Db2

What do you do if your critical production tablespace reaches its maximum of 32 datasets on a Saturday?

Could you actually get the REORG through before prime time starts on Monday morning?

I have recently been involved with trialing and testing our space monitor software (SpaceAssuranceExpert or SAX) on Db2 z/OS after some enhancements had been added. It was originally designed—many moons ago—to monitor the size of the secondary extents that Db2 was using and to dynamically issue an ALTER SECQTY to guarantee that the maximum size of the object was reached *before* users ran out of physical extents.

MGEXTSZ to the Rescue?

Now you might be wondering “What’s that got to do with the price of beef?” because, as we all know, Db2 V8 introduced a “sliding scale” to the size of the secondary extents so that it could also guarantee that a dataset hit its maximum size *before* you ran out of extents.

Extents are not everything

The “problem” is that extents are not everything. In fact, one major area of concern is the number of datasets. If it is 01:00 on a Saturday morning and your critical production tablespace has reached its maximum of 32 datasets – what are you going to do? Could you actually get the REORG through before prime time starts on Monday morning? Or what happens when Partition 26 completely fills up?

SAX to the rescue!

This is where our SAX tool saves the day. It is an STC that runs 24×7 catching the IFCIDs that Db2 throws whenever it issues an extent request for a dataset. Using the Db2 Catalog, SAX then determines the exact make-up (geometry) of the object being extended and can use two levels of warning percentages to start triggering alarm bells way, way before it all goes pear-shaped!

Here is my little “ready-reckoner” for Linear Dataset Allocations:
 Space and sequence problems in DB2 z/OS; degenerated extent

Object type: TABLESPACE        !   Maximum number of data sets 
-------------------------------+------------------------------- 
LOB tablespaces                !   254                         
-------------------------------+------------------------------- 
Non-partitioned tablespaces    !   32                          
-------------------------------+------------------------------- 
Partitioned tablespaces        !    1 (Percent used check)      
-------------------------------+------------------------------- 
Partitioned By Growth          !   MAXPARTITIONS. LPS check if 
tablespaces                    !   more than one. If on last   
                               !   partition then percent used.
-------------------------------+------------------------------- 
                                                                  
Object type: INDEX             !   Maximum number of data sets 
-------------------------------+------------------------------- 
Non-partitioned indexes on     !   MIN ( 4096 , 2 power 32 /   
tablespace with LARGE,         !       ( DSSIZE / TS PGSIZE)) 
DSSIZE, or more than 64        !   Eg: 128 GB DSSIZE with      
Partitions                     !         8 KB Tablespace Page  
                               !   gives 256 Pieces (datasets) 
                               !   Or    4 GB DSSIZE with      
                               !         4 KB Tablespace Page  
                               !   gives 4096 Pieces (datasets)
-------------------------------+------------------------------ 
Non-partitioned indexes        !   32                          
otherwise                      !                             
-------------------------------+------------------------------ 
Partitioned indexes            !   1 (Percent used check)      
-------------------------------+------------------------------

Here you can see that it is not as easy to calculate how many datasets are allowed as it used to be. You must also make sure you understand PBG space definitions. SAX allows two percentages and uses them in two different ways:

  1. The number of datasets that have been allocated
  2. The used space within a linear dataset

The second is also used if it is a PBG with MAXPARTITIONS 1, (e.g. The Db2 Catalog), or if the Partition being extended is the last allowable Partition.

How big can my PARTITION get?

There is a full description in the SQL guide all about the maximum size of a partition. Here is a little summary of this info:

Use the DSSIZE parameter to control how big a partition is (or for LOB spaces how big the LOB space can get):

1G1 Gigabyte
2G2 Gigabytes
4G4 Gigabytes
8G8 Gigabytes
16G16 Gigabytes
32G32 Gigabytes
64G64 Gigabytes
128G128 Gigabytes
256G256 Gigabytes

To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.

How does the number of partitions affect my size?

If NUMPARTS is used along with DSSIZE then the maximum size of each partition depends on the value of NUMPARTS, as shown in the following list. Otherwise, the maximum size of each partition defaults to 4G.

Value of NUMPARTS  Maximum partition size (default for DSSIZE)

1 to 164GB (4G)
17 to 322GB (2G)
33 to 641GB (1G)
65 to 254 4GB (4G)

How does my size affect the number of partitions?

If NUMPARTS is greater than 254, the maximum partition size (and the default for DSSIZE) then depends on the actual page size of the table space.

Page sizeMaximum partition size (default for DSSIZE)
4K4GB (4G)
8K8GB (8G)
16K16GB (16G)
32K32GB (32G)

If DSSIZE is explicitly specified, the maximum number of partitions that can be specified, or is the default, is limited by the maximum table space size. For example:

  • For a partitioned table space with a 4K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 256.
  • For a partitioned table space with an 8K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 512.
  • For a partitioned table space with a 32K page size, if DSSIZE 128GB is specified, the maximum NUMPARTS value is 1024.

Special rules for LOBs

For LOB table spaces, if DSSIZE is not specified, the default for the maximum size of each data set is 4GB. The maximum number of data sets is 254.

What about PBGs?

To use these UTS types you must specify the MAXPARTITIONS clause. It specifies that the table space is a partition-by-growth table space. The data set for the first partition is allocated unless the DEFINE NO clause is specified for the partition. The data sets for additional partitions are not allocated until they are needed. (Unless you use the NUMPARTS clause)

You specify the maximum number of partitions to which the table space can grow, which must be in the range of 1 to 4096, also depending on the corresponding values of the DSSIZE and page size clauses.

How does my MAXPARTITIONS affect my size?

The maximum value for MAXPARTITIONS is a function of DSSIZE and table space page size:

DSSIZE value4K page8K Page16K page32K page
1G – 4G4096409640964096
8G2048409640964096
16G1024204840964096
32G512102420484096
64G25451210242048
128G1282565121024
256G64128256512

WTO to Job Ticket

These warnings are issued as WTOs and can easily be picked up by system automation tools to open job tickets or send e-mails to alert DBAs—days or weeks before the system stops working.

For a warning SAX issues WTO ids:

O2RTSU04 - 12W (non-partitioned spaces)

O2RTSU04 - 14W (partitioned spaces)

O2RTSU04 - 16W (partition by growth spaces)

For a critical SAX issues WTO ids:

O2RTSU04 - 13W (non-partitioned spaces)

O2RTSU04 - 15W (partitioned spaces)

O2RTSU04 - 17W (partition by growth spaces)

This is not all that SAX does, in fact, it covers all of these problems:

  1. Can this data set reach its maximum physical size *before* running out of physical extents? (The actual size is dependent on the “geometry” of the object of course!)
  2. Will this dataset run out of datasets? (Again, how many datasets an object can actually have is dependent on the “geometry” of the object)
  3. Is this partition nearing its maximum size?
  4. Do I have a SEQUENCE/IDENTITY problem coming up?
  5. Did Db2 ask for one extent but got more back?
  6. Are any of my SMS disk storage pools running out of space?

New in SAX – SEQUENCE Support

Number four on that list is brand new. We have a customer who got bitten by a nasty problem. They had a SEQUENCE defined with the NO CYCLE parameter so it could not loop around and then finally they hit the last available number. Not good! They asked if SAX could be modified to also take care of this hidden nasty and we readily agreed so that all customers can benefit. The parameters panel got this set of new Parameters:

 Space and sequence problems in DB2 z/OS; degenerated extent

  CHECK SYSSEQUENCES                                                           

                Should the Monitor also check for SYSIBM.SYSSEQUENCES that  
                are running out of room every PING minutes?                  
                               
                         N  - do nothing.  This is the default.              
                         I  - check Identity Columns and Doc Ids for XML.    
                         S  - check User Defined Sequences.                  
                         B  - do both.                                       
                                                          
   PERCENT USED                                                           

                If checking of SEQUENCES is desired then a threshold         
                percentage must be given from 1 to 99. If this percentage of 
                the available sequences is exceeded then an action is        
                triggered.                                                   
                                                                        
   EXCEEDED ACTION                                                        

                When a percentage is exceeded this specifies what type and   
                and which style of message should be externalized.
                         N  - do nothing.  This is the default.              
                         W  - to write out a WTO.          
                         M  - to write a message to the job log.            
                         B  - do both.                                       
                                                         
                To support automation based on WTO ids the following         
                messages are output:                                         
                                                            
                O2RTS000 - 20W  (SEQUENCES MAXVALUE)                         
                O2RTS000 - 21W  (SEQUENCES MINVALUE)                         
                O2RTS000 - 22W  (IDENTITY MAXVALUE)                          
                O2RTS000 - 23W  (IDENTITY MINVALUE)                          
                                                            
                Note that the action is only done if the WARN SUPP INTVL has 
                been exceeded.

As mentioned in the screen shot above from our online help panel, it will check the SYSSEQUENCES every PING minutes which, by default, is 30. When the WARN SUPP INTVL is set, you can reduce the number of warnings issued so as not to overload your problem ticket System!

Degenerated Extent support

There is another WTO that can be issued by degenerated extents (Number five in the list)

O2RTSU04 – 10W (Audit SECQTY)

These occur when Db2 requests one extent but gets back, say, five. This is ok, but it eats through the number of extents quite quickly and it implies the need for a disk defragmentation to be scheduled.

Let’s talk about Extents

While talking about extents, what I have also seen, is that the number of extents is sometimes getting very large indeed. At one customer site they had numerous datasets with over 4,000 extents! Now we all know that no-one knows where data is really stored on the modern disk sub-systems, but still… I would schedule a reorg at say 1,000 extents. The number of extents changed a *long* time ago in z/OS 1.7 to raise it from 255 to 7,257, spread over 59 volumes, *but* still limited to 123 extents per volume. This little nugget of information is *very* important if you are thinking of going down the “one huge EAV volume for all my data” road, (these disks can have up to 262,668 cylinders or about 223GB), as the extents per volume limit is still there.

In comparison, the good old MOD-3s had 3,339 cylinders and 3GB of space.

SMS Storage Group Checks

Finally, SAX can also check and alert if your SMS storage groups start getting full. This is especially handy for your Db2 Catalog, Copy Pools and Work Pool SMS Storage groups. Depending on the thresholds you define you can get either

O2RTSU05 - 05W SMS STOGROUP XXXXXXXX: % ALLOC = XXXX

Or

O2RTSU05 - 05W SMS STOGROUP XXXXXXXX: GB FREE = XXXXXXXXXXXX

WTOs being issued. Not really normal DBA work, but very handy nevertheless!

The Future?

SAX will continue to be updated and enhanced for the new features and functionality that Db2 brings in future releases. For example: Relative Page Numbers in Db2 12, where all partitions can get their own DSSIZE with seven byte RIDs.

Of course, you could do all of this on your own too—but then you’d have to maintain it! And that’s enough of me being a sales guy. Back to what I really love the most: solving Db2 problems.

As usual any questions or comments are welcome,

TTFN Roy Boxwell