2020-02 Db2 UPDATE column: UPDATEs for nothing and CPU ain’t free!

A bad misquote of a great Dire Straits song, but it is one great thing I saw last year!

What is an Update?

We all know what an update is, right? You have a column containing some value and you wish to update it to a new – different – value. You code an UPDATE with SET and all is done.

But, what happens “under the covers” when the column value you are updating is exactly the same as the new value?

Suspected real-time abuse

The problem surfaced gradually… as all good problems do… The DBAs were wondering why an SQL that was executed half a million times per day was waiting for other threads for so long. (This was discovered using our WorkLoadExpert (WLX)). The wait times were frighteningly high, and so it was decided that this SQL should be the target of some sort of tuning effort.

It then came out, while gathering basic tablespace statistical data, that :

the related tablespace had not been REORGed for over six months when, according to SEGs WorkLoadExpert, there were half a million updates every day against it!

RTS or SEG Bug?

Naturally the first idea is it must be a bug.

  • Either the Real-time Statistics (Not incrementing the REORGUPDATES counter – It could even be NULL for example) or,
  • heaven forbid, a bug in our WorkLoadExpert.

I took a closer look at the SQL:

UPDATE aaa.bbb
 SET COL1 = ?
 WHERE COL1 = ?
 ;

and just sort of wondered out loud,

“They are not using the same value in both parameter markers are they?”

The “they” in this case was the developer of course…

Oh My Word!

After a quick e-mail discussion, it then came out that, that was indeed the case! Db2 is clever but sometimes not that brilliant! The developer had had the idea of executing this SQL to “see” if the value existed or not… He did not think about what Db2 then actually does…

Under the Covers

Db2 does not “know” what the current value of COL1 is. It used, in this case, Index access to get and obtain an X lock on the target page – (this was then the reason for the very large wait times on the other threads!). Once the lock was held, it could then discover that there was *nothing* to do, and so it did nothing! Then it happily released the lock(s) after doing nothing and returned SQLCODE 0.

No Log data was written as nothing was done, and REORGUPDATES was not incremented as nothing was done, but the CPU/Elapsed overhead was enormous!

The right way

The head DBA has said the SQL should look like:

SELECT ‘A’ FROM aaa.bbb
WHERE COL1 = ?
FETCH FIRST 1 ROW ONLY
WITH UR
;

This is now on its way through change management! Naturally, it is the way the developer should have coded it from the get go!

What can you do?

Now this, of course, caused alarm bells to ring as “cut-and-paste” is your friend. If there is bad code in one place it is probably being copied further, even as you read this! Using SEG’s WorkLoadExpert and the Real-time Statistics, you can easily pull out and analyze any “bad guys”.


Put simply, use the UPDATE count from WLX and correlate it to the REORGUPDATES counter. If they are wildly different, taking into account REORGLASTTIME and WLX_TIMESTAMP, then you have a candidate to further track down!


Now, where are those refrigerators we have to move?

As always, I would be pleased to hear from you and any war stories you have!

TTFN,
Roy Boxwell
Senior Architect


More about WLX

2018-03 RTS RUNSTATS

„Breaking the rules is often fun, although we cannot condone it. But breaking the rules of Real Time Statistics (RTS) in Db2 12 can really land you in hot water.

We provide two queries that may give you a Get Out of Jail Free Card – at least as far as RTS is concerned…”

In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.
In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.

The Arrival of RTS

The Real Time Statistics were basically created by Dr Jim Teng in Db2 V7 and have always obeyed a few quite simple rules.


Real Time Statistics (RTS) rules:


  1. Rule Number one
    If in doubt set to NULL.
    This might read a bit odd but the idea from Jim was that if any value is not 100% known then the column must be set to the NULL value.


  2. Rule Number two
    Externalize when asked, or by timer.


  3. Rule Number three
    No DEFINE NO data.


  4. Rule Number four
    Utilities will always update the RTS unless it cannot – see Rule Number one.


  5. Rule Number five
    SQL updates the counters unless they cannot – Think Mass Delete in a multi-table tablespace. Totalrows cannot be updated.

Rules are made to be broken

A couple of years into RTS usage and the clamor for changing various insert values got too big, so IBM enhanced the RTS so that on creation the REORGLASTTIME got set to the created timestamp and all the counter columns got set to zero instead of NULL. Now this was a good change as adding 1,000,000 to NULL you get NULL, whereas adding 1,000,000 to zero means you get 1,000,000 which is obviously better for working out whether or not you need a utility to run. The REORGLASTTIME was also accepted as when you create a nice empty object or you insert one row into an object, then by definition, it is in a perfectly reorganized state!

RTS rule one broken

Mass delete always caused problems, as mentioned earlier, so IBM then made a “half way” fix for the INDEX statistics in Db2 9 by zeroing the TOTALENTRIES when there is a Mass Delete as Db2 knows that the index is now empty.

(PM34730: RTS SYSINDEXSPACESTATS TOTALENTRIES INCORRECT AFTER MASS DELETE.)

Of course it did not update the TOTALROWS, as it didn’t “know” how many rows were mass deleted or truncated. This causes “drift” between the TS and IX statistics, but is only a minor annoyance.

Db2 12 breaks rule one

Now in Db2 12 Rule one has been broken again. Not that much of a break, but still not brilliant! What they have done, is change the STATSLASTTIME to now also be, nearly, the created TS – as if creating an object sets the runstats columns to valid data!

Naturally, the Db2 Catalog is still all -1’s. This makes generating utilities based on the RTS a little bit tricky, as you cannot trust the STATSLASTTIME to now ever actually be the time a RUNSTATS utility really ran – which was the *whole*, and only, purpose of this column! If only they had set the STATSLASTTIME to ‘0001-01-01-00.00.00.00.000000’ then all would be well…

Who woke the dogs up? (Or: Who let the dogs out? I.e. American jargon)

PI79234: SYSIBM.SYSTABLESPACESTATS.STATSINSERTS IS NOT UPDATED BY RTS EXTERNALIZATION SINCE OBJECT IS CREATED.
Is the APAR (PTF UI48494) that caused me all the trouble.

Where’s the beef?

Well, the problem is, if you are generating RUNSTATS based on the RTS – and who isn’t these days? – Then you are probably using this column. Now it *looks* like a RUNSTATS has been run at least once.

This is false and can lead to the not running of RUNSTATS when it most definitely should have been run. Check all your home-grown RUNSTATS checkers. Remember DSNACCOX is also a little bit broken, as it uses these predicates:

(STATSLASTTIME IS NULL OR
 STATSLASTTIME<LOADRLASTTIME OR
 STATSLASTTIME<REORGLASTTIME OR
 STATSLASTTIME< latest PROFILE_UPDATE for the table space1 OR

Make sure you do not use the STATSLASTTIME, but instead join across to the SYSINDEXPART or SYSTABLEPART and pull out the STATSTIME column. This data is still ok and not fake news!

The scope of the problem

To find out the scope of the problem at your site, or even if you have this problem, you can run these two queries which simply list out all the objects that have, according to the Db2 Catalog, never been RUNSTATted, but according to the RTS they have been RUNSTATted:

Query 1

-- SELECT LIST OF TABLESPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN.  
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE WORK DEFINED DATABASES AS NO RUNSTATS FOR THESE 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
-- DISTINCT IT FOR MULTI TABLE TABLESPACES 
SELECT DISTINCT A.DBNAME, A.NAME, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSTABLESPACESTATS A 
   ,SYSIBM.SYSTABLEPART        B 
   ,SYSIBM.SYSDATABASE         C 
   ,SYSIBM.SYSTABLES           D 
WHERE   B.STATSTIME       = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                           ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                          = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME        = 'DSNDB01' 
  AND A.DBNAME            = C.NAME 
  AND NOT C.TYPE          = 'W' 
  AND A.DBNAME            = B.DBNAME 
  AND A.NAME              = B.TSNAME 
  AND A.PARTITION         = B.PARTITION 
  AND A.DBNAME            = D.DBNAME 
  AND A.NAME              = D.TSNAME 
  AND D.TYPE            IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 
; 

Query 2

-- SELECT LIST OF INDEXSPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN. 
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE HASH INDEXES AS NO RUNSTATS ALLOWED 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
SELECT A.DBNAME, A.INDEXSPACE, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSINDEXSPACESTATS A 
    ,SYSIBM.SYSINDEXPART       B 
    ,SYSIBM.SYSINDEXES         C 
    ,SYSIBM.SYSTABLES          D 
WHERE   B.STATSTIME       = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                          ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                          = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME        = 'DSNDB01' 
  AND A.CREATOR           = B.IXCREATOR 
  AND A.NAME              = B.IXNAME 
  AND A.PARTITION         = B.PARTITION 
  AND A.CREATOR           = C.CREATOR 
  AND A.NAME              = C.NAME 
  AND NOT C.HASH          = 'Y' 
  AND C.TBCREATOR         = D.CREATOR 
  AND C.TBNAME            = D.NAME 
  AND D.TYPE            IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 -
;

You can run these in any version of Db2 you like, but you will only get results from a Db2 12 system with this APAR applied and you have created an index or a tablespace. When I run them here on my Db2 10 and 11 systems I get zero rows back and on my test Db2 12 FL501 system I get 172 rows.

The good news is…

Our product for helping you all get “agile” in Db2 12, CDDC – ContinuousDelivery DeploymentCheck, detects and reports these data constellations as does our Statistics Health Check, of course.

I would be very interested to hear your opinions about this “little change in behavior”. Have you come across this at your site?

 

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

TTFN,

Roy Boxwell

2017-11 Db2 APAR list: An APAR a day keeps the bugs at bay

Db2 12 APAR – time saving list

With all the talk about “agile” going on, and referring to one of my older 2017-09 newsletters* on APARs, I think it is time to tell you all about another little service that our company offers.

(completely free, simple and no marketing spam)


* Db2 12 Agile & APAR previous newsletter:
2017-09: Db2 12 SQL Access path: Death by APAR :  How many APARs really can affect access paths?

APAR Database

The APAR database can be accessed by anyone with an IBM Userid and you can merrily search to see if the problem that you have hit is already found and fixed, or a fix is in the works.

The problem is: What about the bugs that you do not *know* you have hit?

Get someone else to do the donkey work

The answer is to get someone else to do all the research for you, and deliver the answer by e-mail every month for all current releases of Db2 going back two years.

Who is that donkey? You will never guess…  😉

Three is the key

There are three Excel spread sheets here.


  1. SQL Performance
    The first is RTS. This lists any and all APARs to do with the Real-Time Statistics tables. If you rely on these tables to decide when to run REORG, COPY and RUNSTATS, then you want to make sure that they are being correctly updated, don’t you?


  2. SQL Access Path
    The second is RUNSTATS. RUNSTATS is a critical utility program and contains bugs like any other non-trivial program. I count RUNSTATS bugs as *always* a personal HIPER. I rely on statistics and so does the Db2 Optimizer. If there is bad data here, then your access paths have no chance!


  3. Performance PTFs
    Finally, and the biggest list, is those APARs that have anything to do with SQL Performance and SQL Access paths (If not already in the RUNSTATS list of course!) Performance PTFs are pretty important!

Going hyper over HIPER

All the APARs have their related PTF. A handy little HIPER column tells you whether or not this is a really important fix. As I mentioned, I treat the RUNSTATS ones as personal HIPERS. Also added is a PE indicator when a PTF goes bad on you (PTF in Error) so you can see if you introduced a problem by correcting another.

One Excel Example: The SQL Performance spread sheet

Db2 12 APAR list free for Db2 z/OS: SQL Performance (RTS) - Performance PTFs - SQL Access Path (RUNSTATS) -

This is from the SQL Performance spread sheet and you can see how it looks.

The same table in HTML:

APAR CLOSED STATUS Db2 10  Db2 11 Db2 12 HYPER Description
PI85305
2017-11-01
Closed
N/A
UI51606
UI51601
INEFFICIENT INDEX CHOSEN WHEN INDEX CAN DO INDEX SKIPPING…
 PI85418
 2017-19-25
Modified
UI50098
UI50099
UI50582
PREPARE TAKES LONG TIME AND HIGH CPU IF THE QUERY CONTAINS MA…
 PI85463
New & Closed
 N/A
 N/A
UI51342
TABLESPACE SCAN INSTEAD OF INDEX ACCESS – WITH INDEX HAVIN…

 …

 …

 …

 …

 …

 …

 …

 …

Interested in making your life a little bit easier and safer?

If you are interested in Db2 APARs and you have *no* time to go and scrabble around the internet trying to find out which APARs are needed, worthwhile, or dangerous, then

just email our technical support techsupport@seg.de and ask to be added to the APAR Mailing list.

It is completely free, you will not get marketing spam and it really is as simple as that!

 

Get AGILE now!

With these handy lists, you can quickly and easily review the state of your Db2, and react quickly and in a timely manner when you have to.

 

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

TTFN

 

Roy Boxwell

2016-10 Discovering hidden recovery problems in the SYSLGRNX – Update 2020-09

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

2016-02 Real Time Statistics (RTS) Revisited – Information missing (part 1)

Easy Real Time Statistics (RTS) data initialization from DB2 9 to DB2 11:

Special query to run before a REORG, RUNSTAT or a DB2 Migration. How many RTS rows did you find?

 

Hands up who knows nothing about the RTS? Good, all hands are down! I had an interesting experience the other day with one of my customers as they are in the process of doing the big bang “REORG the world” to get from a six byte RBA/LRSN to a 10 Byte RBA/LRSN due to problems with data cloning in a mixed DB2 release Environment.

 

RTS Database Maintenance

They use the RTS to drive the creation of REORG, RUNSTAT, and COPY utilities as this is the modern and correct way to go, right? Well, they ended up with a bunch of objects that refused to REORG. I looked high and low for *any* reason as to why they would be excluded from processing and found none. Well, actually, I lie – there was one, and that was the fact that the candidate list was based upon a SELECT from the RTS tables and then joining to the DB2 Catalog to refine the data and then finally generating the required REORG jobs.

 

RTS data missing

It was noticed that these tablespaces were either empty or very small and it was seen that they did not even *exist* in the RTS! Now cast your mind way way way back to DB2 V7 when the RTS were introduced as an “optional” feature. I wrote a little SQL INSERT to populate the RTS for any missing elements as the IBM way of populating the RTS was to “REORG the world” (remember those halcyon days?) Anyway these days, about 11 years later, it is *always* assumed that:

– The RTS data exists
– The RTS data is correct (mainly!)
– RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

------------------------------------------------------------------------
-- THESE TWO QUERIES WILL FILL THE RTS TABLES SYSIBM.SYSTABLESPACESTATS
-- AND SYSIBM.SYSINDEXSPACESTATS WITH DEFAULT AND, WHEN POSSIBLE,     --
-- WITH CATALOG DATA FOR MISSING ENTRIES                              --
-- (OBJECTS FOUND IN THE CATALOG BUT NOT IN RTS TABLES)               --
------------------------------------------------------------------------
-- LOCK TABLE SYSIBM.SYSTABLESPACESTATS IN EXCLUSIVE MODE ;         

INSERT INTO SYSIBM.SYSTABLESPACESTATS
 (UPDATESTATSTIME,NACTIVE,EXTENTS)
 ,LOADRLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGUPDATES,REORGUNCLUSTINS
 ,REORGDISORGLOB,REORGMASSDELETE,REORGNEARINDREF,REORGFARINDREF
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSUPDATES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,PSID,PARTITION,INSTANCE,SPACE,TOTALROWS 
 ,DBNAME,NAME)
 SELECT CURRENT TIMESTAMP 
 ,CASE A.SPACEF 
  WHEN -1 THEN CASE A.SPACE 
               WHEN 0 THEN NULL 
               ELSE A.SPACE / B.PGSIZE 
               END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.EXTENTS
  WHEN -1 THEN NULL
  ELSE A.EXTENTS
  END
  ,TIMESTAMP('0001-01-01-00.00.00.000000')
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0 , 0   
  , 0 , 0 , 0 , 0  
  ,CASE 
    WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
    THEN A.STATSTIME 
    WHEN A.STATSTIME < A.CREATEDTS THEN 
                       TIMESTAMP('0001-01-01-00.00.00.000000')
    ELSE A.STATSTIME
    END
  , 0 , 0 , 0 , 0 
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0   
  , 'N'   
  ,B.DBID,B.PSID,A.PARTITION,B.INSTANCE  
  ,CASE A.SPACEF 
    WHEN -1 THEN CASE A.SPACE 
                 WHEN 0  THEN NULL  
                 ELSE A.SPACE 
                 END 
    ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
    END 
   ,CASE A.CARDF 
    WHEN -1 THEN NULL  
    ELSE A.CARDF 
    END 
   ,A.DBNAME,A.TSNAME  
    FROM SYSIBM.SYSTABLEPART  A 
        ,SYSIBM.SYSTABLESPACE B 
    WHERE NOT EXISTS (SELECT C.*  
                     FROM SYSIBM.SYSTABLESPACESTATS C 
                     WHERE A.DBNAME = C.DBNAME 
                       AND A.TSNAME = C.NAME  
                       AND A.PARTITION = C.PARTITION)    
     AND NOT A.SPACE  = -1   
     AND A.DBNAME     = B.DBNAME   
     AND A.TSNAME     = B.NAME    
  ; 
COMMIT ;
-- LOCK TABLE SYSIBM.SYSINDEXSPACESTATS IN EXCLUSIVE MODE ;
INSERT INTO SYSIBM.SYSINDEXSPACESTATS
 (UPDATESTATSTIME
 ,NLEVELS,NLEAF,NACTIVE,SPACE,EXTENTS
 ,LOADRLASTTIME
 ,REBUILDLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGAPPENDINSERT
 ,REORGPSEUDODELETES,REORGMASSDELETE,REORGLEAFNEAR,REORGLEAFFAR
 ,REORGNUMLEVELS
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,ISOBID,PSID,PARTITION,INSTANCE
 ,TOTALENTRIES,DBNAME,NAME,CREATOR,INDEXSPACE)
  SELECT CURRENT TIMESTAMP
 ,CASE B.NLEVELS
   WHEN -1 THEN NULL
   ELSE B.NLEVELS
   END
  ,CASE B.NLEAF
   WHEN -1 THEN NULL
   ELSE B.NLEAF
   END
  ,CASE A.SPACEF
   WHEN -1 THEN CASE A.SPACE
                WHEN 0  THEN NULL
                ELSE A.SPACE / B.PGSIZE
                END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.SPACEF
  WHEN -1 THEN CASE A.SPACE
               WHEN 0  THEN NULL
               ELSE A.SPACE
               END
  ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
  END
 ,CASE A.EXTENTS
   WHEN -1 THEN NULL
   ELSE A.EXTENTS
   END
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0
 , 0 , 0 , 0 , 0 , 0
 ,CASE
  WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000')
  THEN A.STATSTIME
  WHEN A.STATSTIME < A.CREATEDTS THEN
                     TIMESTAMP('0001-01-01-00.00.00.000000')
  ELSE A.STATSTIME
  END
 , 0 , 0 , 0
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0
 , 'N'
 ,B.DBID,B.ISOBID, C.PSID
 ,A.PARTITION,C.INSTANCE
 ,CASE A.CARDF
  WHEN -1 THEN NULL
  ELSE A.CARDF
  END
 ,B.DBNAME,B.NAME,B.CREATOR,B.INDEXSPACE
  FROM SYSIBM.SYSINDEXPART  A
      ,SYSIBM.SYSINDEXES    B
      ,SYSIBM.SYSTABLESPACE C
      ,SYSIBM.SYSTABLES     D
 WHERE NOT EXISTS (SELECT E.*
                   FROM SYSIBM.SYSINDEXSPACESTATS E
                   WHERE B.DBNAME = E.DBNAME
                     AND B.INDEXSPACE = E.INDEXSPACE
                     AND A.PARTITION  = E.PARTITION)
   AND B.CREATOR    = A.IXCREATOR
   AND B.NAME       = A.IXNAME
   AND NOT A.SPACE  = -1
   AND B.TBCREATOR  = D.CREATOR
   AND B.TBNAME     = D.NAME
   AND D.DBNAME     = C.DBNAME
   AND D.TSNAME     = C.NAME
;
COMMIT ;

 

It may even be a good idea to run these two queries on a regular basis… just in case!

I would like to know how many rows these queries INSERTed at your shops – Here in Düsseldorf, in the labs, it found two TS’s and three IX’s in a DB2 11 NFM system.

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

TTFN,

Roy Boxwell