2020-03 Db2 REORG SYSCOPY: DRAIN Delays are Despicable

I heard about an interesting problem the other day. Please remember that “interesting” to me is just that: “interesting”. ”Interesting” for the DBAs and employees of the firm where it happened is, naturally, a bit different.

A normal start

  • Monday morning and all is well until around 07:00, when delays start appearing in online transactions.
  • Soon the delays are gaining the advantage and customers are starting to complain.
  • At around about 07:20 nearly the whole machine just sat there…
  • About 15 minutes later everything started running normally.

Hmmm, interesting.

The stage is set

So, the lead DBAs are off and running, looking for bad SQL that could possibly have caused the disturbance in the force. They were checking whether the coupling facility was under stress, they were checking for parallel running REORG, MODIFY, or QUIESCE in the SYSIBM.SYSCOPY, they were using our WorkLoadExpert (WLX) to see what was happening in the time window involved.

Tuesday arrives

And so do I! As luck would have it, I am at this site to hold a presentation all about BindImpactExpert, which saves you from bad access paths, and RunstatsRescue, which rescues you from badly timed RUNSTATS. Now this site already has these products, but I must present to a new intake of DBA and developer employees.

Check everything

After my presentation we checked everything and found a few timeouts and deadlocks, but nothing serious. Then I got my Deer Stalker hat on, (now there’s an image!), and decided to see where delays were coming in. One of the developers had already done a quick WLX check and had seen very high Drain Lock values.

WLX outputs a summary of what workload has been processed which, here in the labs on my little test system, looks like this:

Wait times in microseconds because of …                                
 latch requests               :                 594                    0
 page latch                   :                   0                    0
 drain locks                  :                   0                    0
 drain lock claims            :                   0                    0
 log writer                   :               32230                    0
 synchronous I/O              :             6840389                 9623
 locks                        :                   0                    0
 synchronous execute          :                   0                    0
 global locks                 :                   0                    0
 other threads read  activity :            28429563                    0
 other threads write activity :               13166                    0 

At the actual customer site I could see a 1000 times increase in wait drain locks between two runs!

Utility versus SQL

Now, as I am sure you are all aware, a drain is *only* used by a command or a utility, so I started thinking:

“There must be a parallel running something somewhere!”

“There must be a parallel running something somewhere!”

So I used WLX to show me the SQLs that had the highest wait drain locks. I took the top two (over 30,000 seconds of delay!) and got their tablespace names from the Db2 Catalog using the first referenced table column.

Horrible Job to do

It is not a pleasant task to search master address space sysouts, but in this case it was the only way. Using the tablespace names from the Db2 Catalog. I just navigated to the date and time in question and did F commands in SDFS on the tablespace names.

BINGO!

After a few minutes I found a strange message (Correlation Id=010.TLPLKNC3) about a drain not being possible for an internal Db2 system task

– This happens to be used by REORG, and it gave me the info about where the drain came from. I looked at that system’s log output in the time range, and sure enough there was a REORG of that very table which kept failing due to not getting the drain!

A retry too far?

At this site they use a 3rd Party software tool to generate REORG, RUNSTATS and COPY and it had a default of RETRY 30. It kept trying 30 times before eventually failing.

This explains the missing SYSCOPY entry as the REORG had failed!

The other one?

So that was one bad boy found – What about the other? That tablespace did not appear in any of the sysouts. So I drilled down to get the full SQL text (Over 8000 bytes long!) and scrolled on down to the FROM lines – and there was the *first* table name! After the dust had settled, I went back and I saw that :

this one table was actually in every single SQL in the top 200 delay candidates! A pretty central table if you ask me!

Who? and Why?

The management level now wanted to know who did it? And why? I left that part up to the customer, of course, as I do not want to get involved in a finger pointing exercise! My feeling is: like most disasters, it was probably a chain of events something like:

  1. REORG generated on Sunday.
  2. Due to some unforeseen problem the JCL was shunted to Monday.
  3. On Monday at 07:00 it started and killed the machine.

Never again?

Best way is to generate jobs straight to the Job Scheduler for instantaneous execution (No waiting or shunting allowed) and guess what? We have the RealTimeDBAExpert (RTDX) that allows you to do just that! You can easily exclude objects from utilities based on days of the week, hours of day etc. If you have a bought-in or home-grown system would it also have caused this disaster?

Console Messages

If the customer had had our WLX Console Message Use Case licensed, it would have also made the detective work much easier, as then you have a central place to go where *all* console messages from *all* members are written and searchable! This would have saved a lot of time and trouble.

Bottom Line

(Removing my Deer Stalker hat and replacing it with a mortar board.)

Look everywhere, trust no-one and remember that a DRAIN is almost definitely nothing to do with SQL or a badly timed RUNSTATS.

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

TTFN,

Roy Boxwell

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

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

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:

1 Can 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!)
2 Will this object run out of datasets? (The number of datasets an object can have is, once again, dependent on the “geometry” of the object)
3 Is this partition nearing its maximum size?
4 Did Db2 ask for one extent but got more back?
5 Are any of my SMS disk storage pools running out of space?
6 Are 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:

1 By definition every partition is full, and so a TP REORG is “dangerous” -especially if you have VARCHAR, and even more so if compressed.
2 ALTER at TP level is not supported for PBG.
3 Getting rid of empty partitions was not supported
4 Adding partitions dynamically (by command) was not supported.
5 What 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

 

 

Rotten Results from RUNSTATS Require Rescue

Do you know the basic rules to ensure access path stability when using RUNSTATS?

Time for another of my “I noticed something strange at a customer site recently” newsletters. Enjoy!

 

RUNSTATS are good aren’t they?

At this particular site, the RUNSTATS methodology of RUNSTATS was, shall we say, “sub-optimal.” They use an ancient system to decide when to RUNSTATS, and they do tablespace’s and index’s *never* at the same time. Just to complicate matters even more, they never use inline RUNSTATS because “if the REORG abends, the statistics in the DB2 catalog are dead”. Now you are all probably well aware of the scale of the disaster at this site?

 

Daily fire fighting

Nearly every day, some access path somewhere goes horribly wrong… the under- manned and over-worked DBA group are tasked to find and fix ASAP. Cures range from a quick INDEX create or change, or perhaps even a really needed RUNSTATS or REORG.

 

Why do the Access Paths go “wrong”?

The real goal is to stop firefighting and to investigate the root cause. Why do so many access paths go wrong on such a regular basis? The answer is the systemic horribleness of RUNSTATS collection. Dynamic SQL is, obviously, very very sensitive to RUNSTATS. For one thing, the statements are kicked out of the cache! The very next time they come back, the DB2 Optimizer redrives the cost calculations and “Hey Presto!” you have a bad access path. Terry Purcell and Pat Bossmann have often said that about 90% of DB2 performance problems stem from bad RUNSTATS. The old adage “garbage in – garbage out” is still true!

 

Timing is everything

The timing of the RUNSTATS is critical for stable access paths.

Basic rules are:

 1  Only do a RUNSTATS if you really really need to!
a. RUNSTATS are not cheap!
b. The Dynamic Statement cache gets wiped
c. Locks on the Catalog can occur
2 Avoid doing RUNSTATS even if RTS says to run one!
a. Lots of people use the incorrect counters to trigger a RUNSTATS. Use the correct ones for the correct Object type
b. Never RUNSTATS LOB spaces – completely pointless work!
c. Even if a MASSDELETE has occurred do you really want to “reset” the DB2 catalog statistics?
d. VOLATILE tables must be handled with *extreme* care!
3 Choose your RUNSTATS parameters wisely!
a. Doing a blind “RUNSTATS the world” is just as bad as running an empty RUNSTATS!
b. HISTOGRAM should be used with caution
c. More than a hundred COLGROUPs should start alarm bells ringing

Quite a list here, and it really only shows some “Rules of Thumb”. I’ll bet you all have you own?

Is there a way back from the abyss?

But what happens if you have 1000’s of partitions with terabytes of data and the RUNSTATS was, shall we say, ill-advised or badly timed? Can you go back in time? Hands up those who wants to do a PiT recovery on the production catalog! No takers???

Yes! There is a way back from the abyss

I’ll bet you are all well ahead of me here, but the way to do this is pretty straightforward. You simply acquire our latest tool, RUNSTATS Rescue, to handle it all for you. Or, you could try and reset the data in the DB2 catalog from off-line backups that you happen to have taken before the RUNSTATS that is now killing you. …You did do that, right?

Why a tool?

Apart from the fact that this tool is from us, my firm, just trying to “roll your own” can be a real nightmare. Why?

  • Because you must first find out all of the objects that were touched by the badly performing SQL.
  • Then you must get all of the DB2 Optimizer relevant data back from a point in time before the RUNSTATS executed, and/or the last REBIND(s),
  • and then you must flush the dynamic statement cache and REBIND any static SQL.

Sounds like a lot of work.

What else must you do?

You also have to be transparent and so log what you do. You must allow for the ability to back-out your changes as perhaps you make another access path even worse. And it would be really cool if you could do “on the fly” explains to check that the RUNSTATS really *is* the root of all that evil. Remember that ZPARMS and BUFFERPOOLS also have a major influence on access paths. Even the speed of your machine! It is also a must to then be able to go even further back in time – perhaps as much as a year?

Hang on – What about PLAN STABILITY?

Doesn’t plan stability save you? I hear you all cry. Well, “No” is the short answer! If your package is invalidated by a Schema change (the classics are index drop and recreate or VIEW change), then plan stability does not work anymore. Further, in DB2 12, Dynamic Plan Stability has been announced. Sadly it *also* fails right here as there is no SWITCH PREVIOUS/ORIGINAL support!

It all works together

So, for the static SQL case where the package is not invalided, Plan Stability is good. If not: – RUNSTATS Rescue to the rescue. For Dynamic SQL – RUNSTATS Rescue is the answer.

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

TTFN,

Roy Boxwell

 

2016-03 DB2 z/OS Real Time Statistics (RTS) Revisited – Information missing (part 2)

DB2 z/OS Real Time Statistics (RTS) –  NULL initialization made easy

A second RTS query to set this time *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance before a REORG, RUNSTAT or a DB2 Migration for DB2 10 and DB2 11

Following on from last month’s Newsletter (first RTS query) where we inserted any missing data rows into the RTS tables-the next thing that I see on a, sadly, regular basis is NULL values in the RTS columns.

Now when RTS was introduced, way back in DB2 V7, the argument from DB2 Development was “If we do not know the exact count we will set the column to NULL.” I have always strongly disagreed with this approach as whenever you add +1 to NULL you get NULL, whenever you add +1000000 to NULL you get NULL. I said then, and I repeat it now, zero is orders of magnitude better than NULL because when you add +1 to 0 you get +1 and when you add +1000000 to 0 you get +1000000. This enables your DB2 Database Maintenance system to actually *work* and then, after a REORG, the counters are actually 100% correct. I am a firm believer in the “I don’t care if it is 99% inaccurate as long as it *counts*!” methodology, and please remember that a SHRLEVEL CHANGE RUNSTATS does *not* set the TOTALROWS or TOTALENTRIES!

 

RTS Database Maintenance

The following SQLs in this month’s newsletter will intelligently set *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance. It is written for DB2 10 with a couple of commented out lines for DB2 11. (In the RTS tables, there are only two actual new columns and these are the “info” only columns UPDATESIZE and LASTDATACHANGE in SYSTABLESPACESTATS. The commented out lines just set the UPDATESIZE to zero.)

 

RTS NULL initialization made easy

Now here are the UPDATE SQLs broken down to be one UPDATE SQL per KPI column and one “mass update” for all of the counter columns:

-- TOTALENTRIES
UPDATE SYSIBM.SYSINDEXSPACESTATS E                  
SET TOTALENTRIES =                                        
   (SELECT CASE A.CARDF                                   
           WHEN -1 THEN 0                                 
           ELSE A.CARDF                                   
           END                                            
    FROM SYSIBM.SYSINDEXPART  A                           
        ,SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE                     
      AND B.CREATOR    = A.IXCREATOR                      
      AND B.NAME       = A.IXNAME                         
      AND A.PARTITION  = E.PARTITION)                     
WHERE TOTALENTRIES IS NULL                                
;                                                         
-- NLEVELS
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET NLEVELS =                                             
   (SELECT CASE B.NLEVELS                                 
           WHEN -1 THEN 1                                 
           ELSE B.NLEVELS                                 
           END                                            
    FROM SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE )                   
WHERE NLEVELS IS NULL                                     
; 
-- NLEAF
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET NLEAF =                                               
   (SELECT CASE B.NLEAF                                   
           WHEN -1 THEN 1                                 
           ELSE B.NLEAF                                   
           END                                            
    FROM SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE )                   
WHERE NLEAF IS NULL                                       
;                                                         
-- SPACE: USE SPACE IF SPACEF -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET SPACE =                                               
   (SELECT CASE A.SPACEF                                  
           WHEN -1 THEN CASE A.SPACE                      
                        WHEN 0  THEN 0                    
                        ELSE A.SPACE                      
                        END                               
           ELSE MAX(MIN(2147483647 , A.SPACEF ) , A.SPACE)
           END                                            
    FROM SYSIBM.SYSINDEXPART  A                           
        ,SYSIBM.SYSINDEXES    B                                     
  WHERE B.DBNAME     = E.DBNAME                                   
    AND B.INDEXSPACE = E.INDEXSPACE                               
    AND B.CREATOR    = A.IXCREATOR                                
    AND B.NAME       = A.IXNAME                                   
    AND A.PARTITION  = E.PARTITION)                               
WHERE SPACE IS NULL                                                 
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET EXTENTS =                                                       
   (SELECT CASE A.EXTENTS                                           
           WHEN -1 THEN 1                                           
           ELSE A.EXTENTS                                           
           END                                                      
    FROM SYSIBM.SYSINDEXPART  A                                     
        ,SYSIBM.SYSINDEXES    B                                     
    WHERE B.DBNAME     = E.DBNAME                                   
      AND B.INDEXSPACE = E.INDEXSPACE                               
      AND B.CREATOR    = A.IXCREATOR                                
      AND B.NAME       = A.IXNAME                                   
      AND A.PARTITION  = E.PARTITION)                               
WHERE EXTENTS IS NULL                                               
;                                                                   
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET NPAGES             = COALESCE(NPAGES             , 0)           
WHERE NPAGES             IS NULL
;                                                                   
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET REORGINSERTS       = COALESCE(REORGINSERTS       , 0)           
  , REORGDELETES       = COALESCE(REORGDELETES       , 0)
  , REORGAPPENDINSERT  = COALESCE(REORGAPPENDINSERT  , 0)
  , REORGPSEUDODELETES = COALESCE(REORGPSEUDODELETES , 0)
  , REORGMASSDELETE    = COALESCE(REORGMASSDELETE    , 0)
  , REORGLEAFNEAR      = COALESCE(REORGLEAFNEAR      , 0)
  , REORGLEAFFAR       = COALESCE(REORGLEAFFAR       , 0)
  , REORGNUMLEVELS     = COALESCE(REORGNUMLEVELS     , 0)
  , REORGINDEXACCESS   = COALESCE(REORGINDEXACCESS   , 0)
  , STATSINSERTS       = COALESCE(STATSINSERTS       , 0)
  , STATSDELETES       = COALESCE(STATSDELETES       , 0)
  , STATSMASSDELETE    = COALESCE(STATSMASSDELETE    , 0)
  , COPYUPDATEDPAGES   = COALESCE(COPYUPDATEDPAGES   , 0)
  , COPYCHANGES        = COALESCE(COPYCHANGES        , 0)
WHERE REORGINSERTS       IS NULL                         
   OR REORGDELETES       IS NULL                         
   OR REORGAPPENDINSERT  IS NULL                         
   OR REORGPSEUDODELETES IS NULL                         
   OR REORGMASSDELETE    IS NULL                         
   OR REORGLEAFNEAR      IS NULL                         
   OR REORGLEAFFAR       IS NULL                         
   OR REORGNUMLEVELS     IS NULL                         
   OR REORGINDEXACCESS   IS NULL                         
   OR STATSINSERTS       IS NULL                         
   OR STATSDELETES       IS NULL                         
   OR STATSMASSDELETE    IS NULL                         
   OR COPYUPDATEDPAGES   IS NULL                         
   OR COPYCHANGES        IS NULL                         
;
-- NOTE THAT FOR TABLESPACES DSNDB01.SYSUTILX IS NOT
--      SET AND ANY WORK DATABASE IS ALSO IGNORED
--
-- TOTALROWS
UPDATE SYSIBM.SYSTABLESPACESTATS C                       
SET TOTALROWS =                                          
(SELECT CASE A.CARDF                                     
        WHEN -1 THEN 0                                   
        ELSE A.CARDF                                     
        END                                              
 FROM SYSIBM.SYSTABLEPART A                              
 WHERE A.DBNAME    = C.DBNAME                            
   AND A.TSNAME    = C.NAME                              
   AND A.PARTITION = C.PARTITION )                       
WHERE TOTALROWS IS NULL                                  
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX') 
  AND NOT EXISTS (SELECT 1                               
                  FROM SYSIBM.SYSDATABASE D              
                  WHERE C.DBNAME = D.NAME                
                    AND D.TYPE   = 'W')                  
;
-- SPACE: SPACE IF SPACEF IS -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C                       
SET SPACE =                                              
(SELECT CASE A.SPACEF                                                 
        WHEN -1 THEN A.SPACE                                          
        ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)             
        END                                                           
 FROM SYSIBM.SYSTABLEPART A                                           
 WHERE A.DBNAME    = C.DBNAME                                         
   AND A.TSNAME    = C.NAME                                           
   AND A.PARTITION = C.PARTITION )                                    
WHERE SPACE IS NULL                                                   
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')              
  AND NOT EXISTS (SELECT 1                                            
                  FROM SYSIBM.SYSDATABASE D                           
                  WHERE C.DBNAME = D.NAME                             
                    AND D.TYPE   = 'W')                               
;                                                                     
-- NACTIVE: SPACE IF SPACEF IS -1 / PGSIZE WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C                                    
SET NACTIVE =                                                         
(SELECT CASE A.SPACEF                                                 
       WHEN -1 THEN CASE A.SPACE                                      
               WHEN 0 THEN 0                                          
               ELSE A.SPACE / B.PGSIZE                                
               END                                                    
       ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
       END                                                            
FROM SYSIBM.SYSTABLEPART  A                                           
    ,SYSIBM.SYSTABLESPACE B                                           
WHERE A.DBNAME    = C.DBNAME                                          
  AND A.TSNAME    = C.NAME                                            
  AND A.PARTITION = C.PARTITION                                       
  AND A.DBNAME    = B.DBNAME                                          
  AND A.TSNAME    = B.NAME )                                          
WHERE NACTIVE IS NULL                                                 
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')              
  AND NOT EXISTS (SELECT 1                                            
                  FROM SYSIBM.SYSDATABASE D                           
                  WHERE C.DBNAME = D.NAME                             
                    AND D.TYPE   = 'W')                               
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSTABLESPACESTATS C                                    
SET EXTENTS =                                                         
(SELECT CASE A.EXTENTS                                                
        WHEN -1 THEN 1                                                
        ELSE A.EXTENTS                                                
        END                                                           
FROM SYSIBM.SYSTABLEPART  A                                           
WHERE A.DBNAME    = C.DBNAME                                          
  AND A.TSNAME    = C.NAME                                            
  AND A.PARTITION = C.PARTITION )                       
WHERE EXTENTS IS NULL                                   
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
  AND NOT EXISTS (SELECT 1                              
                  FROM SYSIBM.SYSDATABASE D             
                  WHERE C.DBNAME = D.NAME               
                    AND D.TYPE   = 'W')                 
;                                                       
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C                      
SET NPAGES           = COALESCE(NPAGES           , 0 )  
WHERE NPAGES IS NULL                                    
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
  AND NOT EXISTS (SELECT 1                              
                  FROM SYSIBM.SYSDATABASE D             
                  WHERE C.DBNAME = D.NAME               
                    AND D.TYPE   = 'W')                 
;                                                       
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C                      
 SET REORGINSERTS     = COALESCE(REORGINSERTS     , 0 )  
   , REORGDELETES     = COALESCE(REORGDELETES     , 0 )  
   , REORGUPDATES     = COALESCE(REORGUPDATES     , 0 )  
   , REORGUNCLUSTINS  = COALESCE(REORGUNCLUSTINS  , 0 )  
   , REORGDISORGLOB   = COALESCE(REORGDISORGLOB   , 0 )  
   , REORGMASSDELETE  = COALESCE(REORGMASSDELETE  , 0 )  
   , REORGNEARINDREF  = COALESCE(REORGNEARINDREF  , 0 )  
   , REORGFARINDREF   = COALESCE(REORGFARINDREF   , 0 )  
   , REORGCLUSTERSENS = COALESCE(REORGCLUSTERSENS , 0 )  
   , REORGSCANACCESS  = COALESCE(REORGSCANACCESS  , 0 )  
   , REORGHASHACCESS  = COALESCE(REORGHASHACCESS  , 0 )  
   , STATSINSERTS     = COALESCE(STATSINSERTS     , 0 )  
   , STATSDELETES     = COALESCE(STATSDELETES     , 0 )  
   , STATSUPDATES     = COALESCE(STATSUPDATES     , 0 )  
   , STATSMASSDELETE  = COALESCE(STATSMASSDELETE  , 0 )  
-- , UPDATESIZE       = COALESCE(UPDATESIZE       , 0 )  
   , COPYUPDATEDPAGES = COALESCE(COPYUPDATEDPAGES , 0 )  
   , COPYCHANGES      = COALESCE(COPYCHANGES      , 0 )  
 WHERE (REORGINSERTS     IS NULL                         
    OR  REORGDELETES     IS NULL                         
    OR  REORGUPDATES     IS NULL                         
    OR  REORGUNCLUSTINS  IS NULL                         
    OR  REORGDISORGLOB   IS NULL                         
    OR  REORGMASSDELETE  IS NULL                         
    OR  REORGNEARINDREF  IS NULL                         
    OR  REORGFARINDREF   IS NULL                         
    OR  REORGCLUSTERSENS IS NULL                         
    OR  REORGSCANACCESS  IS NULL                         
    OR  REORGHASHACCESS  IS NULL                         
    OR  STATSINSERTS     IS NULL                         
    OR  STATSDELETES     IS NULL                           
    OR  STATSUPDATES     IS NULL                           
    OR  STATSMASSDELETE  IS NULL                           
--  OR  UPDATESIZE       IS NULL                           
    OR  COPYUPDATEDPAGES IS NULL                           
    OR  COPYCHANGES      IS NULL)                          
   AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')  
   AND NOT EXISTS (SELECT 1                                
                   FROM SYSIBM.SYSDATABASE D               
                   WHERE C.DBNAME = D.NAME                 
                     AND D.TYPE   = 'W')                   
 ;

It is a very good idea to run all of these queries on a regular basis…just in case!

I would like to know how many rows this UPDATEd at your shops. Here in the Düsseldorf labs it updated hundreds in a DB2 11 NFM system.

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

TTFN,

Roy Boxwell

2016-2 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

2015-09 A real CLUSTER Buster

Are you using the “default” clustering INDEX or are you defining the correct INDEX with the CLUSTER Attribute?

This newsletter is dedicated to all DDL designers who do their best but then omit that last tiny bit. No-one really notices, or even cares, for years and years until…

Imagine a huge table up in the billions of rows. Imagine now that you have SQL that accesses this table and it must, as always, run fast. So what do you do? You create an index, RUNSTATS it and Hey Presto! Everything is sweet and dandy! Now imagine this happening again and again over time… What you finally end up with is a huge table with billions of rows now with ten indexes! Not too brilliant for insert and update but that is not the point of this newsletter.

 

An SQL, that had worked perfectly well, suddenly went pear shaped…

So now stop imagining, as this had already really happened at a customer site. We come to the crux: An SQL, that had worked perfectly well, suddenly went pear shaped (belly-up for the non-British English readers!) and started using a two column index with one matching column instead of a six column index with six matching columns! This change in access path caused death by random-IO to occur and it all went horribly wrong.

 

Now the question is why? What on earth happened for the DB2 Optimizer to make such a terrible decision?

1- RUNSTATS review

First idea was, of course, my favourite – Incomplete or not Full RUNSTATS data. In fact there were “bogus stats” from 2003 in the SYSCOLDIST, but even after all the bogus stats were deleted and a complete RUNSTATS with HISTOGRAM and FREQVAL performed, the access path remained stuck on the “bad” index.

 

2- DDL review

I then reviewed the DDL that created all of the objects and noticed that none of the indexes was defined with the CLUSTER attribute. The table itself was “as old as the hills,” but all of the indexes had been created and/or altered many times over the last ten years or so.

 

3- Redefine the “bad” Index as CLUSTER

 The dummy CLUSTER

Now, as we all know, if no index is defined as CLUSTER DB2 picks one to be a dummy CLUSTER when it does a REORG. So you can end up with 100% clustering non-clustered indexes. In this case that was exactly what was happening. The “bad” index was, purely by fluke after many years of index maintenance, the “default” clustering index, however it was a *terrible* choice for a clustering index. Worse still: because of the fact it was non-unique with two columns and therefore small (well small in this case was still 40,000 pages!) it looked positively “good” to the DB2 Optimizer—hence the decision to abandon a six column matching index in favour of a single column one…

“Cleansing” with ALTER, REORG and the DB2 10 INCLUDE syntax

A quick ALTER of the original “first” index to get the CLUSTER attribute, a REORG scheduled for the weekend to get the data into *proper* CLUSTERing sequence and – Bob’s your uncle! Access path swapped back to the “good” index.

Now there’s still work to be done here as ten indexes is about seven too many, if you ask me.With DB2 10 it is possible to use the INCLUDE syntax to weed out some of the extra indexes and thus speed up all usage of this mega-table. But, for the right here and now, the job is done!

So now I am at the end of this sad story of how a little design “error” of just forgetting one little attribute on an index create statement caused major mayhem many years down the line… remember to check *all* of your tables and see if you have any beauties like this in your shop (surely not!)

 

Here’s a little SQL that will do the job for you:

--                                                                     
-- QUERY TO LIST OUT ALL TABLES WITH TWO OR MORE INDEXES WHERE NO INDEX
-- IS DEFINED AS CLUSTER                                               
--                                                                     
SELECT A.CREATOR                                                       
      ,A.NAME                                                          
FROM SYSIBM.SYSTABLES  A                                               
WHERE NOT A.CREATOR = 'SYSIBM'                                         
  AND NOT EXISTS                                                       
          (SELECT 1                                                    
           FROM SYSIBM.SYSINDEXES B                                    
           WHERE A.NAME       = B.TBNAME                               
             AND A.CREATOR    = B.TBCREATOR                            
             AND B.CLUSTERING = 'Y')                                   
  AND 1 < (SELECT COALESCE(COUNT(*) , 0)                               
           FROM SYSIBM.SYSINDEXES B                                    
           WHERE A.NAME    = B.TBNAME                                  
             AND A.CREATOR = B.TBCREATOR)                              
ORDER BY 1 , 2                                                         
;

Note that this query excludes the SYSIBM indexes as IBM also forgot to CLUSTER them!

 

As usual, any comments or questions please mail me!

 

TTFN

Roy Boxwell

2015-03: DB2 z/OS object changes: Quiet Times for maintenance

Do you have an idea when tables are in use?

 

Ahhh! Wouldn’t it be great if we all had just quiet times? Sadly we never have time for anything these days, let alone for peace and quiet!

The quiet before the Storm?

What I mean by Quiet Times is, however, different: it is the time when a given table, or set of tables, is not in use. This is very interesting to find out, especially when you are doing data definition changes (DDL). For example: you are given the task of adding some columns to some tables – naturally these days you have no idea who or what is actually using the tables, and absolutely no idea *when* they are being used.

What do you do?

Well, all you can do is schedule the change for early one morning and then quickly push the ALTERs and the REORGs through – hoping not to collide with any users of the data.

 

Guessing when tables are in use can be dangerous

This is all a bit haphazard and dangerous! Wouldn’t it be better if you could look at a calendar and see that this table is only used Mo – Th from 09:00 – 16:00 thus giving you a really big hint that Friday morning is a better bet?

 

Capture your DB2 SQL Workload & project the results into a Calendar view

Using the new and enhanced IFCIDs in DB2 10 you can now do this! Capture your workload and analyze when table(s) are being used and project the results into a Calendar view:

News from the labs Newsletter 2015-03: Quiet Times

 

Gives this style Output:

News from the labs Newsletter 2015-03: Quiet Times

 

Handy huh?

Video (3 min.)  – Presentation

– You can drag the dates back and forth to validate the assumptions of a period of time, and then you can happily do your ALTERs and REORGs during the day.

– Apart from not having to get up early, the added bonus is that you get to learn more about who uses the tables!

Of course this system is *not* a crystal ball! It is just showing historical usage. Who knows what the future holds?

Would this style of output be useful for you? Could you imagine this helping you in your day-to-day tasks?

 

As usual any queries or criticism gladly accepted!

TTFN,

Roy Boxwell