Saint Louis Db2 Users Group – March 2020

STLDUG – Saint Louis Db2 Users Group, MO, USA – March 2020

SEGUS & SOFTWARE ENGINEERING present:

Db2 for z/OS Know your Limits!

In this presentation I will show and explain the current number of limits within Db2 on z/OS.

Starting with relatively basic simple things like DSSIZE or PIECESIZE. Moving on to Linear Dataset limits (32 for segmented spaces, 254 for LOB). We then will have a quick look at how the COPYPOOL is looking before diving off to the world of PBGs, where you must be aware of MAXPARTITONS among other things. Then we will review SEQUENCES and Numeric Primary Key usage before finally delving into the abstract limit of Db2 within the Db2 engine including DBATs etc.

1. DSSIZE or PIECESIZE among other basic simple things
2. Linear Dataset limits (32 for segmented spaces, 254 for LOB)
3. COPYPOOL
4. PBGs & MAXPARTITIONS
5. SEQUENCES and Numeric Primary Key usage
6. Abstract limit of Db2 (DBATs etc.)


Our Space AssuranceExpert for Db2 z/OS monitors (critical) Db2 limitations in real-time


Speaker biography

Roy Boxwell has more than 34 years of experience in MVS, OS/390, and z/OS environments – 31 of those in Db2. He specializes in installation, migration, and performance monitoring and tuning. Roy leads the SEG development team responsible for the real time database maintenance solutions. He is also an active participant, speaker and contributor on the IDUG Db2 Listserv and sends out a monthly Db2 z/OS Newsletter.

New England DB2 Users Group December 2019

NEDB2UG – Old Sturbridge Village – MA, USA

SEGUS & SOFTWARE ENGINEERING present:

Db2 for z/OS Know your Limits!


In this presentation I will show and explain the current number of limits within Db2 on z/OS.

Starting with relatively basic simple things like DSSIZE or PIECESIZE. Moving on to Linear Dataset limits (32 for segmented spaces, 254 for LOB). We then will have a quick look at how the COPYPOOL is looking before diving off to the world of PBGs, where you must be aware of MAXPARTITIONS among other things. Then we will review SEQUENCES and Numeric Primary Key usage before finally delving into the abstract limit of Db2 within the Db2 engine including DBATs etc.

1. DSSIZE or PIECESIZE among other basic simple things
2. Linear Dataset limits (32 for segmented spaces, 254 for LOB)
3. COPYPOOL
4. PBGs & MAXPARTITIONS
5. SEQUENCES and Numeric Primary Key usage
6. Abstract limit of Db2 (DBATs etc.)


Our Space AssuranceExpert for Db2 z/OS monitors (critical) Db2 limitations in real-time


Speaker biography

Roy Boxwell has more than 33 years of experience in MVS, OS/390, and z/OS environments – 30 of those in Db2. He specializes in installation, migration, and performance monitoring and tuning. Roy leads the SEG development team responsible for the real time database maintenance solutions. He is also an active participant, speaker and contributor on the IDUG Db2 Listserv and sends out a monthly Db2 z/OS Newsletter.

2019-12 Fun with Db2 12 PBR RPN

I have recently enhanced our SpaceAssuranceExpert (SAX) product to automatically help out if partitions, or partitioned indexes, are getting too big for their boots in a productive system.

SAX – What is it?

The idea behind SAX, is to stop any and all chances of getting a dreaded SQLCODE -904 in production, especially “out of the blue”.

Our idea was, in Db2 12, with the new PBR RPN to do an on-the-fly ALTER to the DSSIZE, just like SAX does now with the SECQTY to avoid running out of extents.

RTFM Time

A quick look in the manual tells you that :


it is an immediate ALTER (as long as you make the new DSSIZE larger than the old one!) and there are no package invalidations or REORGS required.


This is fantastic!

So I created a nice little PBR and then ran a horrible Cartesian join SPUFI to flood the first partition with data. This join had a TIME card of (,1) to limit it to one second of CPU before getting an Abend S322.

SAX Monitor

The SAX monitor reacted perfectly and did the TP alter to 513 GB DSSIZE (I had an increment size of 512 as a test), and got an SQLCODE -666 (I just love that SQLCODE…), as the INSERT was still running.

This ALTER was then internally queued to be attempted later, like in any other failure case.


All well and good.

Wham! Nasty errors !

Then I did the same for a DPSI on my PBR RPN… Oh dear!I got a nasty IO Error and then an even nastier ROLLBACK loop, meaning I had to cancel the IRLM to stop Db2… (There is an APAR for this problem PH18977.)

Rollback loop?

The ROLLBACK loop was caused by me choosing to use NOT LOGGED as a tablespace attribute. We have a finite amount of log space, and when the transaction was S322’d after one second of CPU,

the rollback could not find one of the archive logs and then we had to cold start Db2

– Not pretty!

Proper test!

I then wrote a couple of little test programs that actually COMMITted after 5000 inserts and then the ALTERs all worked as designed.


Do I worry too much about extended format and extended accessibility?

Next, I worried about the ominous “extended format and extended addressability“ attributes in the DATACLASS for a PBR RPN, and wondered what would happen if a customer has SAX running and it happily ALTERs a TP to say 6 GB when they can only address 4 GB…


IBM to the rescue!

Luckily for us, Db2 development had thought about this!

  • If you attempt to create a PBR RPN (even with a very small size) and your DATACLASS does not have the two attributes set, you get an error message 00D70008 telling you this detail.

  • If you ALTER an existing tablespace to be a PBR RPN and your DATACLASS does not have the two attributes set, then the ALTER works fine. But remember, this is a pending alter and you *must* do a REORG at the TS level with inline TP level copies. This REORG then fails – also with 00D70008.

So, in other words, SAX cannot hurt you here!


What about PBGs?

After all this we also considered PBGs. They have a limit as well – MAXPARTITIONS in their case. So we added an ability to also ALTER MAXPARTITIONS as well. Here you must be more careful though, as these ALTERs are still immediate *but* they invalidate any referring packages!

ABIND YES or NO?

If you work with ABIND set to YES all is good as the ALTER comes in, Db2 invalidates your packages and the auto rebind happens so fast that you do not even notice it happening, however, if you work with ABIND NO then any packages, even the package actually running and doing the inserts, will fail! If you are just using dynamic SQL then it is 100% OK otherwise – Buyer beware!

Aha!

I have opened an Analytics Idea (DB24ZOS-I-1057) to try and get this loophole closed, as I cannot see what access path change could be affected by going from 32 to, say, 36 MAXPARTITIONS.

Db2 keeps adding the parts dynamically and nothing happens then… Go figure… Anyway, if you would like it – Please vote for it!


There are already some nice comments attached to it:

  • DP commented

    this is just a limit in the catalog.  So how could access path be affected?  The actual growth of a partition doesn’t invalidate the package so how just changing the limit do so ?
  • BW commented

    I opened a Case on this asking why packages are being invalidated in this situation and it is still open waiting for a reply.
  • BD commented

    With only impact to catalog, not sure how Access Path would be impacted.  Seems wasteful and counter productive to invalidate packages.

Remember – You never stop learning!

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

2019-09 A DBA has got to know his limitations

Sorry, but I could not resist the Dirty Harry quote there…and yes I know it really should be “his/her” but that would not then be a quote, so please forgive me!

I have spoken/blogged about limitations in Db2 a few times over the years and we have a nice little tool called Space AssuranceExpert (SAX) that stops a whole bunch of nasty events (read -904’s) at sites around the world.

It grows and grows

SAX started out “just” monitoring the size and number of Extents/Linear Datasets that Db2 was allocating. It then either did a dynamic ALTER of the SECQTY to allow the dataset to get to its physical maximum size before running out of extents, or it warned people that the number of physical datasets was running out.


First enhancements


– SMS Storage groups Monitoring

We pretty quickly enhanced it to then also monitor SMS Storage groups as when your copypool runs out of space it can be a tad embarrassing!

At the same time, we changed the logic to work with the -1 -1 style allocations when IBM brought out their sliding scale logic.

Universal support

When PBG Tablespaces came out, we again enhanced the product due to the special processing limits of these Universal Tablespaces.

Think MAXPARTITIONS here!

Db2 12

Along came Db2 12 with its new RPN format and the ability to have a different DSSIZE for each partition, and each index got its own DSSIZE.

Sequences and Identities

Then we were asked if we could also monitor Sequences and Identities that are defined as non-cyclic. A lot of these beasts are defined so that they just “hit the buffers” at a certain number. So we added that to the system as well.

Numeric Primary Key support

Next came a request to monitor Numeric Primary Key columns. These were actually sequences before sequences were even invented! You have, typically, a SMALLINT field which keeps being incremented – Fine for the first 32,767 but after that?


No time to rest on our Laurels!

Are we now finished? No! There is *always* more to do!

  • What if you are nearing the column limit, or worse yet, the Index column limit?
  • What if the size of your index is getting dangerously close to the absolute physical maximum?
  • Talking about absolute physical maximums – What if you are running out of DBIDs?
  • Or, even worse, running out of OBIDs within a database – Do you even check these?
  • What happens when you try to ALTER it? Or create that new index?
  • Or even just a view with an INSTEAD of function?

BOOM! Is what happens!

SAX saves the day again!

We have enhanced SAX yet again, so that

you can now run a batch job to review all of these limits.

This gives you plenty of time to take corrective action before the bridges are burning…  


What are the limits?

In the Db2 SQL Reference Guide in the Appendix “Limits in Db2 for z/OS” it lists them out, but here are the important ones for you all:


750 Columns in a table.

Actually it can be less, depending on the complexity of any VIEWs, but to keep it simple this value is used as the upper limit.


64 Columns in an index. This includes INCLUDE columns.

For a partitioning index (That is an “old style” index that is actually used to partition the data) you get a maximum size for PADDED indexes of 255 – n bytes and, if NOT PADDED, then 255 – n – 2m – 2d bytes.

For any other indexes you get a maximum size for PADDED indexes of 2000 – n bytes and, if NOT PADDED, then 2000 – n – 2m – 2d bytes.

Where:

  • n is the number of columns which are NULLable
  • m is the number of varying length columns
  • d is the number of DECFLOAT columns

An FTB (News from the Labs 2019-07) is only possible for indexes that are 64 bytes or less. If you ALTER add a one byte column to a 64 byte index then you have broken this limit and that index is no longer eligible for FTB processing.

65,217 Databases may be defined. This includes any widow databases of course (Typically DSNnnnnn style empty databases where the implicitly created tablespace has been dropped when the table was dropped, but the database was “forgotten”).

32,767 OBIDs within a single database. Remember, that the number of objects (OBIDs) within a database is not a simple 1:1 relationship. Each tablespace, index or referential relationship takes two, whereas each table, check constraint, aux for LOB, XML for XML, trigger or view with INSTEAD OF takes one.


Stay up to date

If Db2 development bring any new ones out, think Agile here, then our SAX will be enhanced quickly to check for it!

What is the plan?

Simply schedule the SAX limit checker to run once a week or so.

  • It will report all its findings and, if required,
  • issue WTOs to alert you to any dangers before hitting any of these limits.

And you?

What do you do at your shop now? Do you monitor these hard limits? Does anyone care?

Go ahead, make my day and install a SAX trial!


For a SAX trial please email to : db2support@segus.com

Consult our Space AssuranceExpert (SAX) page


As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

2018-11 db2 Numeric Primary Keys: Space, the final frontier?

What do Db2 numeric Primary Keys have to do with space management ?

SMALLINT  –  INTEGER  –  DECIMAL  –  BIGINT…

How to find out which sequences are nearing their physical limit ?

Recently I was at a customer site and was showing them our SpaceAssuranceExpert (SAX) software. It basically does all sorts of space management and checking, and has recently been extended to now also check for any numeric Primary Keys that are running out of space.

Traffic is needed

The simple way to show that the started task is up and running, and working as designed, is to generate some traffic. To do this, I normally just do a really awful Cartesian join insert into a dummy table.

First I create my target like so:

CREATE TABLE ROY.SYSTABLESPACE LIKE SYSIBM.SYSTABLESPACE ;

And then I simply do an INSERT from SELECT with no WHERE predicate in a batch SPUFI job:

INSERT INTO ROY.SYSTABLESPACE           
(SELECT A.* FROM SYSIBM.SYSTABLESPACE A 
                ,SYSIBM.SYSTABLESPACE B 
                ,SYSIBM.SYSTABLESPACE C 
                ,SYSIBM.SYSTABLESPACE D 
                ,SYSIBM.SYSTABLESPACE E 
                ,SYSIBM.SYSTABLESPACE F 
                ,SYSIBM.SYSTABLESPACE G 
                ,SYSIBM.SYSTABLESPACE H 
                ,SYSIBM.SYSTABLESPACE I 
                ,SYSIBM.SYSTABLESPACE J)
;

CCSID Problems

Now at this site, the first CREATE actually failed due to some odd sort of CCSID problem. The lead DBA said “No problem I have a little test table where we can do the same sort of thing.” This little table was used for the INSERT and we huddled over the keyboard waiting for the started task to start reporting on EXTENTS and other space data…

Nothing happened…

We waited and waited and waited, and still a big zilch!

Trust is everything

Now, I know my software and I know that this works fine, so I was a little perplexed to say the least!

I thought and thought and thought and then reviewed their SQL again:

INSERT INTO ROY.DSN_COLDIST_TABLE       
(SELECT A.* FROM ROY.DSN_COLDIST_TABLE A
                ,ROY.DSN_COLDIST_TABLE B
                ,ROY.DSN_COLDIST_TABLE C
                ,ROY.DSN_COLDIST_TABLE D
                ,ROY.DSN_COLDIST_TABLE E
                ,ROY.DSN_COLDIST_TABLE F
                ,ROY.DSN_COLDIST_TABLE G
                ,ROY.DSN_COLDIST_TABLE H
                ,ROY.DSN_COLDIST_TABLE I
                ,ROY.DSN_COLDIST_TABLE J
                ,ROY.DSN_COLDIST_TABLE K
                ,ROY.DSN_COLDIST_TABLE L
                ,ROY.DSN_COLDIST_TABLE M
                ,ROY.DSN_COLDIST_TABLE N
                ,ROY.DSN_COLDIST_TABLE O)
;

Ouch! That hurt!  😯

And then it hit me!

Look at the SQL and you can see that it is trying to INSERT into itself…

What does Db2 do in this situation?

It inserts into the work database until the INSERT is finished and then it actually moves all the data into the table…

*duh*

Waiting for Godot

If we had waited long enough we would have seen messages like:

O2RT-SU04-006I: Extent activity for
DC10.DSNDBD.DSNDB07.DSN32K00.I0001.A001
                Number of extents : 00032 - EXTS threshold is    1        
O2RT-SU04-011I: 08:11:35 - Datasets will be processed now                 
O2RT-SU04-006I: Extent activity for 
DC10.DSNDBD.DSNDB07.DSN32K00.I0001.A001
                Number of extents : 00034 - EXTS threshold is    1

With hindsight everything is clear!

But, of course, we didn’t see that until much later… then I noticed another little detail that I though was quite funny – the tablespace in question was in COPY Pending:

DSNT360I -DC10 *********************************** 
DSNT361I -DC10 * DISPLAY DATABASE SUMMARY        * 
               *       GLOBAL                    *
DSNT360I -DC10 *********************************** 
DSNT362I -DC10 DATABASE = DSN00201 STATUS = RW 
DBD LENGTH = 4028 
DSNT397I -DC10 
NAME     TYPE PART  STATUS       PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ------------ -------- -------- -------- -----
DSNRCOLD TS   0001  RW,COPY 
DSNRCOLD TS 
******* DISPLAY OF DATABASE DSN00201 ENDED **********************
DSN9022I -DC10 DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION 
***

It appears that Db2 does not actually even check if it can complete the INSERT at the beginning…

Get it right   😀

The moment we changed the INSERT to be on a different table (No work file usage) and it was not in COPY Pending status all worked as usual:

O2RT-SU04-006I: Extent activity for 
DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00002 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:40 - Datasets will be processed now                  
O2RT-SU04-006I: Extent activity for DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00003 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:41 - Datasets will be processed now                  
O2RT-SU04-006I: Extent activity for DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00004 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:42 - Datasets will be processed now

Much better data!

Why Primary Keys?

If you are wondering what numeric Primary Keys have to do with space management just think back to the good ol’ days before Sequences and Identity columns. You created keys with numeric types

  • SMALLINT,
  • INTEGER,
  • DECIMAL and
  • possibly even with the newer BIGINT.

These were, basically, “sequences”.


The challenge is to find out which of these is nearing its physical limit.
With SMALLINT it is not that big and DECIMAL can be very, very small indeed!


Catalog or Data?

Now this data is fetched from a couple of catalog tables and so is very dependent on the quality of your RUNSTATS.

so, in the next stage, it will be extended to actually read the User Data to see what the value currently really is.

Cool stuff, huh?

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

2016-10 Discovering hidden recovery problems in the SYSLGRNX

A query to read the SYSLGRNX

This month I want to have a look inside the SYSLGRNX – Not just for fun but actually to see if I, and you, have a serious RECOVER problem.

The Problem began with too many TP Image Copy steps

The problems all began when it was noticed by one of our customers that our DB2 Utility generation software (RTDX) was generating Image Copy jobs for “old” PBG partitions that were no longer being updated and they asked us why we were recommending an Image Copy.

RTDX and Image Copy

RTDX (RealTime DBAExpert for z/OS and SAX – Space AssuranceExpert) is threshold based and uses the Real-time Statistics (RTS) tables as the major driver of decisions about REORG, RUNSTATS, and IMAGE COPY. The normal rules for Image Copy are “If the RTS COPY counters have not been updated then there has not been an update and we do not need a Copy”. This is normally extended with a special copy rule “But generate a Full Image Copy every xx days even if no updates”. This is done to guarantee that you can perform a recovery even if your non-DB2 migration/back-up software automatically deletes datasets older than xx days.

Everything green?

So you think you are covered? Not so fast! When the RTS were first introduced back in DB2 V7 they were not that stable and, sometimes, the RTS DB was actually stopped. This meant that the numbers were sometimes “not that reliable” – Now for REORG and RUNSTATS it does not really matter because, at some point, the counters start to count again and all is OK. But for IMAGE COPY it would be a disaster to “miss” an update! RTDX handles this with an extra “belt and braces” or, for the Americans amongst us, “belt and suspenders” check: “CHECK SYSLGRNX? Y/N” setting. If set to “Y” then RTDX generates an extra job that extracts the SYSLGRNX data and loads it into a DB2 table. This can then be queried by RTDX to see when objects were opened for update (Just like DB2 does in RECOVER processing to get the required log ranges.)

Something Old Something New

So now you know how the system works I can explain what happened at the customer site. They use Partition By Growth tablespaces (PBGs) a lot as “containers” to just insert records into. They had PBGs with hundreds of partitions and they wondered why RTDX generated Image Copy steps for partitions 1 – 136 when they only held “old non-changed data”.

Check the code

First thing I did was to check the code for any silly mistakes. Everything was fine. I then got the SYSLGRNX extract file and saw that the Partitions were indeed in there… I then worked with the customer directly using log prints and tracing until we finally saw what was happening.

PBG doing the ripple!

When a new partition was added, all of the previous partitions header pages got an update “max no. of partitions”. Now this update counts as a system page and so is *not* in the RTS Counters as they only count data pages! RECOVER of course does not care! It would demand to look for the log ranges and what would happen if your logs have been deleted? Yep – Unrecoverable data!

APAR PI60104 fixes the problem

So you can apply the APAR and all is good? Not really. You still have to go and check if you have any potentially unrecoverable PBG datasets out there. How do you do that? Well – you ask me!

Reading SYSLGRNX is not so easy

The problem with SYSIBM.SYSLGRNX is that the data is nearly unusable when you want to join to the DB2 Catalog. E.g. Two byte character fields that actually contain SMALLINT values and, sometimes, with the high bit set…

Just creating a table and then a dumb INSERT INTO SELECT FROM dies because of the DSNDB01 Cursor rules. So first you’ll need to create your new version of SYSLGRNX:

-- CREATE NEW VERSION OF SYSLGRNX WITH SMALLINTS --
CREATE TABLE BOXWELL.SYSLGRNX2
   (LGRDBID          SMALLINT          NOT NULL
   ,LGRPSID          SMALLINT          NOT NULL
   ,LGRUCTS          TIMESTAMP         NOT NULL
   ,LGRSRBA          CHAR(10)          FOR BIT DATA NOT NULL
   ,LGRSPBA          CHAR(10)          FOR BIT DATA NOT NULL
   ,LGRPART          SMALLINT          NOT NULL
   ,LGRSLRSN         CHAR(10)          FOR BIT DATA NOT NULL
   ,LGRELRSN         CHAR(10)          FOR BIT DATA NOT NULL
   ,LGRMEMB          SMALLINT          NOT NULL
   ,LGRNEGPSID       CHAR(1)           NOT NULL)
;
COMMIT ;

Cross loader to the rescue!

After trying various tricks to get at the data I hit on the idea of abusing the cross loader to do it for me. Here is my JCL that you must tailor for your site to do the dirty deed:

SELECT COALESCE(                                              
       CASE WHEN ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRDBID, 2, 1)) > 32767 THEN  
                (ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRDBID, 2, 1))) - 32768      
            ELSE ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRDBID, 2, 1))               
       END , 0)         AS LGRDBID                            
      ,COALESCE(                                              
       CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN  
                (ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRPSID, 2, 1))) - 32768      
            ELSE ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRPSID, 2, 1))               
       END , 0)         AS LGRPSID                            
      ,TIMESTAMP(                                             
           CASE WHEN SUBSTR(A.LGRUCDT, 5 , 2 ) > '83' THEN '19'
                ELSE '20'                                     
           END CONCAT                                         
           SUBSTR(A.LGRUCDT, 5 , 2 ) CONCAT '-' CONCAT        
           SUBSTR(A.LGRUCDT, 1 , 2 ) CONCAT '-' CONCAT        
           SUBSTR(A.LGRUCDT, 3 , 2 ) CONCAT '-' CONCAT        
           SUBSTR(A.LGRUCTM, 1 , 2 ) CONCAT '.' CONCAT        
           SUBSTR(A.LGRUCTM, 3 , 2 ) CONCAT '.' CONCAT        
           SUBSTR(A.LGRUCTM, 5 , 2 ) CONCAT '.' CONCAT        
           SUBSTR(A.LGRUCTM, 6 , 2 ) CONCAT '0000'            
                 )      AS LGRUCTS                            
      ,A.LGRSRBA                                              
      ,A.LGRSPBA                                              
      ,A.LGRPART                                              
      ,A.LGRSLRSN                                             
      ,A.LGRELRSN                                             
      ,ASCII(SUBSTR(A.LGRMEMB, 2, 1)) AS LGRMEMB              
      ,CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256         
               + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN ‘Y’
            ELSE ‘N’                                          
       END              AS LGRNEGPSID                         
FROM SYSIBM.SYSLGRNX A                                        
ENDEXEC                                                               
LOAD DATA INCURSOR(C1) RESUME NO SHRLEVEL NONE REPLACE                    
  INTO TABLE BOXWELL.SYSLGRNX2                                        
/*
The use of ASCII is to “translate” the character data into smallint and also to detect if we have negative numbers. This happens if you run out of DBIDs and also if you use CLONE objects. The requirement for the two COALESCEs is down to the cross loader syntax check on the CASE statements by the way…

This could load a lot of data of course!

Do not forget the INDEX

Now the Index that we need to speed up the processing (and the RUNSTATS of course!)

-- CREATE INDEX ON THE COLUMNS WE NEED --
CREATE INDEX BOXWELL.SYSLGRNX_IX
          ON BOXWELL.SYSLGRNX2
       (LGRDBID,LGRPSID,LGRPART,LGRMEMB)
        USING STOGROUP SYSDEFLT
             PRIQTY -1
             SECQTY -1
        CLUSTER
        CLOSE YES
;
COMMIT ;

And the RUNSTATS

RUNSTATS  TABLESPACE <yourdatabase>.<yourtablespace>
          TABLE (ALL)
          INDEX(ALL)
          FREQVAL NUMCOLS 1 COUNT 100 BOTH
          FREQVAL NUMCOLS 2 COUNT 100 BOTH
          FREQVAL NUMCOLS 3 COUNT 100 BOTH
          SHRLEVEL CHANGE REPORT NO UPDATE ALL HISTORY NONE

Now we can SQL So finally we have the SYSLGRNX data in a format that we can actually use! Here is my SQL that lists out any PBG spaces that have, according to the RTS, no updates but the last Image Copy timestamp is earlier than the SYSLGRNX record:

-- FINALLY THE QUERY TO SEE IF A PROBLEM EXISTS OR NOT --
--
-- SELECT ALL PBGS IN SYSTABLESPACE THAT HAVE DATA IN THE
-- SYSLGRNX WHICH ALSO HAVE AN ENTRY IN THE RTS WHERE THE
-- COUNTERS ARE NULL OR ZERO BUT THE COPYLASTTIME IS EARLIER
-- THAN THE SYSLGRNX TIMESTAMP.
--  THIS MEANS THAT THE RTS THINKS "NO REASON TO COPY"
--  BUT SYSLGRNX THINKS "SOMETHING WAS UPDATED"
--

SELECT A.DBNAME, A.NAME, B.LGRPART, A.DBID, A.OBID, A.PSID
      ,B.LGRUCTS, B.LGRSRBA, B.LGRSPBA, B.LGRSLRSN
      ,B.LGRELRSN, HEX(B.LGRMEMB) AS MEMBER
      ,C.COPYLASTTIME
      ,MAX(D.START_RBA) AS HIGHEST_IC_RBA
      ,MAX(D.TIMESTAMP) AS HIGHEST_IC_TS
FROM SYSIBM.SYSTABLESPACE A
INNER JOIN
    BOXWELL.SYSLGRNX2    B
 ON  A.DBID     = B.LGRDBID
 AND A.PSID     = B.LGRPSID
 AND A.INSTANCE = B.LGRINST
INNER JOIN
     SYSIBM.SYSTABLESPACESTATS C
 ON  C.DBID     = A.DBID
 AND C.PSID     = A.PSID
 AND C.PARTITION= B.LGRPART
 AND C.INSTANCE = A.INSTANCE
LEFT OUTER JOIN
     SYSIBM.SYSCOPY       D
  ON D.DBNAME    = A.DBNAME
 AND D.TSNAME    = A.NAME
 AND D.DSNUM   IN ( 0 , B.LGRPART)
WHERE A.TYPE   = 'G'
 AND (C.COPYUPDATEDPAGES = 0
  OR C.COPYUPDATEDPAGES IS NULL)
 AND (C.COPYCHANGES = 0
  OR C.COPYCHANGES IS NULL)
 AND (C.COPYLASTTIME < B.LGRUCTS
  OR C.COPYLASTTIME IS NULL)
 AND (D.ICTYPE = 'F'
  OR D.ICTYPE IS NULL)
GROUP BY A.DBNAME, A.NAME, B.LGRPART, A.DBID, A.OBID, A.PSID
     ,B.LGRUCTS, B.LGRSRBA, B.LGRSPBA, B.LGRSLRSN
     ,B.LGRELRSN, HEX(B.LGRMEMB)
     ,C.COPYLASTTIME
ORDER BY 1 , 2 , 3 , 7
WITH UR
;

And the winner is?

I hope you have zero records returned? Even if you do have some records, it is relatively easy to quickly get them image copied so that you can sleep at night. If zero, you still have a great copy of the SYSLGRNX that you can use for other purposes!

A customer replies

Here’s some real data back from a customer:

discovering hidden recovery problems in the SYSLGRNX; DB2 z/OS; PBG partition

The COUNT is the number of SYSLGRNX entries they have – So they have nine DB & TS groups and from a further analysis (removing the data sharing member duplicates) they have 1926 distinct DB, TS and Partitions!

They scheduled image copy processing for all of these and were then done.

I hope you liked this month’s topic.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell