2015-01 BIFCIDS – Where’s the BIF?

 

How will you deal with loop-hole usage in production code?

 

 

The IFCIDs 366 and 376

Db2 provides many and varied IFCIDs. But for today, I’m most interested in the 366 and 376. The 366 is available in Db2 10 and the 376 in Db2 11. Now I like to call these “BIFCIDs” because they are triggered whenever a BIF is used that will behave differently than it is currently used when moving to the next release of Db2. (It’s also triggered when changing Application Compatibility settings in Db2 11 and higher).

 

So where’s the BIF?

BIF Usage Video (11min:)       Presentation

Well, a BIF is a Built-In Function such as CHAR, DECIMAL, etc. There are hundreds of them these days. In the last few Db2 releases, IBM has changed a few to make Db2 more compatible with SQL standards. They have actually closed a couple of loop-holes, where “bad” data could be accepted and processed.

 

Loop-hole user?

What happens is: someone somewhere found this loop-hole and used it in production code. Now when you upgrade your Db2, this code will either fail or give erroneous results – which is never good. Hence IBM created the IFCID 366. This is output every time an SQL statement is PREPARED, or executed, that contains a candidate BIF. There were so many of these, that IBM introduced a sort of condensed version so it only triggered one for the first execution, or prepare, but sadly that IFCID—376—is only for Db2 11.

Where can, or will, this really hurt?

 

Looking into the documentation for these IFCIDs you will see a long list of when they are written:

***********************************************************************
**  IFCID 0366 is a serviceability trace.                            **
**  It can be used to identify applications that are affected        **
**  by incompatible changes.                                         **
**  The QW0366FN field indicates the type of incompatible Change:    **
**                                                                   **                                                      
**  QW0366FN = 1                                                     **
**  Indicates that the pre Version 10 CHAR built-in function has     **
**  been invoked. There is an incompatible change to the output of   **
**  the CHAR function for some decimal data. The zparm               **
**  BIF_COMPATIBILITY and/or the SYSCOMPAT_V9 schema have been used  **
**  by this application to get the old behavior. Please make the     **
**  appropriate changes and rebind with the SYSCURRENT schema to     **
**  use the Version 10 CHAR(decimal) built-in function.              **
**  (PM29124 V10 only, usermod V8/V9)                                **
**                                                                   **
**  QW0366FN = 2                                                     **
**  Indicates that the pre Version 10 VARCHAR built-in function or   **
**  CAST(decimal AS CHAR or VARCHAR) has been invoked.               **
**                                                                   **
**  QW0366FN = 3                                                     ** 
**  Indicates that an unsupported character representation of a      ** 
**  timestamp string was used. PM48741 V10 only.                     **
**                                                                   ** 
**  QW0366FN = 4                                                     ** 
**  A QW0366FN 4 record indicates that the statement uses the        **     
**  word ARRAY_EXISTS as an unqualified user-defined function Name   **   
**  in a context that may be incompatible with Version 11.           **     
**                                                                   **
**  QW0366FN = 5                                                     ** 
**  A QW0366FN 5 record indicates that the statement uses the        **
**  word CUBE as an unqualified user-defined function Name           **
**  in a context that may be incompatible with Version 11.           **
**                                                                   **
**  QW0366FN = 6                                                     **
**  A QW0366FN 6 record indicates that the statement uses the        **
**  word ROLLUP as an unqualified user-defined function Name         **
**  in a context that may be incompatible with Version 11.           **
**                                                                   ** 
**  QW0366FN = 7                                                     **
**  A QW0366FN 7 record indicates that Db2 for z/OS server issued    **
**  a SQLCODE -301 for incompatible data type conversion from        **
**  string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC        **
**  etc.) to numeric data type in V10 CM mode when implicit          **
**  cast is not supported or V10 NFM mode when DDF_COMPATIBILITY     **
**  zparm is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to           **
**  disable implicit cast, and the client is CLI Driver              **
**  or v11 NFM mode & APPLCOMPAT = V10R1 when DDF_COMPATIBILITY      **
**  is set to SP_PARMS_NJV or DISABLE_IMPCAST_NJV to disable         **
**  implicit cast either from string data type to numeric or         ** 
**  from numeric data type to string data type.                      **
**                                                                   **
**  QW0366FN = 8                                                     **
**  A QW0366FN 8 record indicates that Db2 for z/OS server           **
**  returned output data match the data types of the                 **
**  corresponding CALL statement arguments when DDF_COMPATIBILITY    **
**  zparm is set to SP_PARMS_NJV.                                    **
**                                                                   **
**  QW0366FN = 9                                                     **
**  A QW0366FN 9 record indicates a data type conversion from        **
**  a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data             **
**  during input host variable bind-in process on server when        **
**  DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the        **
**  time zone information sent by Java IBM Data Server Driver.       **
**                                                                   **
**  QW0366FN = 10                                                    ** 
**  RTRIM, LTRIM or STRIP version 9 being used with mixed data       **
**                                                                   **
**  QW0366FN = 1101                                                  ** 
**  Indicates that the INSERT statement that inserts into an XML     **
**  column without XMLDOCUMENT function has been processed (which    **
**  should result in SQLCODE -20345 when run on Db2 release prior    **
**  to V11). Starting with V11, SQL error will no longer be issued.  **
**  Application will no longer recieve SQLCODE for this Statement.   **
**                                                                   ** 
**  QW0366FN = 1102                                                  **
**  Indicates that V10 XPath evaluation behavior was in effect which **
**  resulted in an error. For instance, a data type conversion error **
**  could have occured for a predicate that would otherwise be       **
**  evaluated to false. Starting from V11, such "irrelevant" Errors  **
**  might be suppressed so an application might no longer recieve    **
**  the SQLCODE for this Statement.                                  **
**                                                                   **
**  QW0366FN = 1103                                                  **
**  Indicates that a dynamic SQL uses the ASUTime limit that has     **
**  been set for the entire thread for RLF reactive governing.       **
**  For instance, when a dynamic SQL is processed from package A,    **
** if the ASUTime limit is already set during other dynamic SQL      ** 
** processing from package B in the same thread, the SQL from        **
** package A will use the ASUTime limit set during the SQL           **
** processing from package B. Stating with v11, dynamic SQLs from    **
** multiple packages will use the ASUTime limit that is set          **
** considering its own package information.                          **
**                                                                   **
** QW0366FN = 1104, 1105, 1106, 1107                                 **
** Indicates that CLIENT special register (CLIENT_USERID,            **
** CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set   **
** to a value that is longer than what is supported prior to V11.    **
** A shorter value has been used instead.                            **
**                                                                   **
** QW0366FN = 1108                                                   **
** Indicates that CLIENT special register (CLIENT_USERID,            **
** CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set   **
** to a value that is longer than what is supported prior to V11.    **
** Truncated values upto the supported lengths prior to v11 have     **
** been used for RLF table search instead.                           **
**                                                                   **
** QW0366FN = 1109                                                   **
** Indicates that CAST(string AS TIMESTAMP) was processed for the    **
** input string of length 8 and input was treated as a store clock   **
** value (or input string was of length 13 and was treated as a      **
** GENERATE_UNIQUE value). This behavior is incorrect for a CAST     **
** and is valid for TIMESTAMP built-in function only. This behavior  **
** is being corrected in Db2 11 so that input to CAST is not         **
** treated as a store clock value nor GENERATE_UNIQUE.               **
**                                                                   **
** QW0366FN = 1110                                                   **
** Indicates the integer argument of SPACE function is greater       **
** than 32764.                                                       **
**                                                                   **
** QW0366FN = 1111                                                   **
** Indicates the optional integer argument of VARCHAR function       **
** has a value greater than 32764. *                                 **
***********************************************************************

 

Useful stuff indeed!

Phew! Not a bad list, huh? Now you see why these IFCIDs are so useful. It could well be, that you have none of these “alive” in your system today. Or, of course, it could be that you get millions of the things! Somehow you will have to work out a way to save the data, analyse it to get to the root cause, and then, finally, fix the problem(s).

 

Saved by APPLCOMPAT?

You could argue that the new Db2 11 parameter Application Compatibility will save you, but this is really a false economy. All it enables is the guarantee that the code will still “run”. However, in two more Db2 releases the code will fail and, in two more releases – so about six years – who will even know *how* to change which piece of source code and, perhaps even, where is that source code?

 

Saved by BIFCIDs

Personally, what I would do, is : to run our SQL WorkloadExpert tool to trap all the required [B]IFCIDs for a few hours (at first!).Then I would analyse the results, fix the code where it needs fixing – and repeat! I would keep doing this until no IFCID records are coming out and I would be set!

BIF Usage Video (11min:)       Presentation

What is even better, is that our SQL WorkloadExpert will work correctly even when any new QW0366FN values appear – so when IBM decides to add another code (Like the new values 9 and 10 above for example) this BIF Usage still works correctly.

 

Of course, you may have another tool that you use at your site.

Can it see “Where’s the BIF?”

How will you deal with loop-hole usage in production code?

 

As usual, any question or comments gladly welcome!

 

TTFN

Roy Boxwell

 

 

2014-10 QUERYNO – Query what?

 

 

Can you Track your SQL Back in time?

Do you have standards to catch QUERYNO abuse?

QUERYNO Newsletter SOFTWARE ENGINEERING & SEGUS www.seg.de/en

 

This month I’d like to confront you all with a really nasty little bit of truth…

We have had the ability to specify a number to “mark” an SQL in DB2 for years and years now, using the QUERYNO keyword.

This number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement. But how many of you out there are actually using this? Even if you do use it, how many of you have got standards and practices in place to guarantee that there is no “misuse” of this feature? Yep, this month we are going to take a long, hard look in the mirror…

 

Definition of QUERYNO

The late great Richard A. Yevich summed it up neatly in 2001 as:

QUERYNO was introduced in V6 as a way to specify a particular QUERYNO for a SQL statement and match it to a HINT, which reference[s] the QUERYNO column in the plan table. It is used at BIND time by DB2, and nothing else.

Regards,
Richard

Of course these days, in fact as of DB2 V7, the QUERYNO has its own column in the SYSIBM.SYSPACKSTMT and can be used for “other things”. You can happily append QUERYNO to static (in fact also Dynamic SQL – but more about that later!) SQL like this:

 

How it Looks

EXEC SQL
    SELECT MAX(IBMREQD)
    INTO :O2TEST5-COL1:O2TEST5-NULL
    FROM SYSIBM.SYSDUMMY1
    WITH UR
    QUERYNO 777777
END-EXEC

All it does is “tie” the SQL to a user-defined arbitrary number, normally for HINT usage. Now the whole point of this was so that you, the DBA, could see that last week QUERYNO 77777 was working a treat, but this week it has suddenly gone all tablespace scanny on you. Your job, as always, is to fix the problem – so that DB2 can keep on chugging along!

 

Working without QUERYNO

Let us imagine that you do *not* use QUERYNO, so the SQL looks like:

EXEC SQL
    SELECT MAX(IBMREQD)
    INTO :O2TEST5-COL1:O2TEST5-NULL
    FROM SYSIBM.SYSDUMMY1
    WITH UR
END-EXEC

After long and hard analysis(!) you decide to add a predicate to speed it all up:

EXEC SQL
    SELECT MAX(A.IBMREQD)
    INTO :O2TEST5-COL1:O2TEST5-NULL
    FROM SYSIBM.SYSDUMMY1 A
    WHERE A.COL1 =
          (SELECT B.COL2 FROM ROY.TABLE B WHERE B.COL3 = A.COL2)
    WITH UR
END-EXEC

 

Can you track this SQL back in time?

Now, however, you trace this SQL you will find it quite hard to extremely impossible, to match this version to the “old” version in any sort of DB2 SQL Data Warehouse. Even our SQLWorkloadExpert will have trouble trying to do so because the SQL has completely changed.  Introducing new tables, changed SQL text etc. however the core competence of the SQL has not changed. All that has changed is the way we get the correct answer.

 

Now why you should all be using QUERYNO is obvious! The original query:

EXEC SQL
    SELECT MAX(IBMREQD)
    INTO :O2TEST5-COL1:O2TEST5-NULL
    FROM SYSIBM.SYSDUMMY1
    WITH UR
    QUERYNO 777777
END-EXEC

 

Brave new coding standards

Is now changed to be:

EXEC SQL
    SELECT MAX(A.IBMREQD)
    INTO :O2TEST5-COL1:O2TEST5-NULL
    FROM SYSIBM.SYSDUMMY1 A
    WHERE A.COL1 =
          (SELECT B.COL2 FROM ROY.TABLE B WHERE B.COL3 = A.COL2)
    WITH UR
    QUERYNO 777777
END-EXEC

 

And now you *can* match this SQL through time with no problem? Neat huh?

Problems problems problems…

Problems however are plentiful… first up is “cut-and-pasteitis”. This is a very nasty, and highly contagious, programmer disease, not just limited to green screen usage but also to GUI developers. It is *very* tempting to just grab a “neat” piece of SQL and drop it into the code and you are done! But if this contains an existing QUERYNO, or even no QUERYNO, then you are destroying your data warehouse.

You must enable QA code checking in order to stop any possible misuse of this Feature.

This entails checking all source code for the existence of a QUERYNO where relevant and needed (Remember that QUERYNO can only be used on DECLARE, SELECT, DELETE, INSERT, MERGE, REFRESH and UPDATE statements). The checking of the numbers used in order to make sure you do not have the number 1234546 55 different times. Also check for the allowance of “gaps”, for the really deranged among us who always want the numbers to go up sequentially in the code (I usually start at 1000 and go up in 1000 increments to start with, as I am indeed one of those poor deranged souls!).

 

Playing with time travel

Once all this is in place, and you are trapping and mapping your data,you can then do wonderful SQL time line Analysis  to really see what an SQL, or even a group of SQLs, did over time! QUERYNO Newsletter SOFTWARE ENGINEERING & SEGUS www.seg.de/en

– Did the change I do really help?

– By how much?

– Can you match the SQLs next to each other etc.

Not only that, but when you are trapping everything, why not use it to see if QUERYNO is actually being used correctly?

(We are currently building a “Use Case” for our SQL WorkloadExpert to do just that.)

 

No way for Dynamic?

I mentioned earlier that the Dynamic world is a bit different. Of course each Dynamic SQL gets its own Statement Id when it comes into the cache, (similar to the Statement Id static SQL gets in the BIND and also in the EDMPOOL cache), but you have no “fixed” point to compare to. Unless, that is, you have a nice SQL Data Warehouse where you store all your Dynamic SQL to enable time travel queries here too!

 

Please note one very important bit of information here

– None of the IFCIDs (316, 317, 318, 400 or 401) actually contains the QUERYNO!- For Static SQL you must fetch it yourself.- For Dynamic SQL its use is purely for documentation in the SQL Text itself.

 

Our question to you for Research purposes

How do you use or *not* use QUERYNO? Do you have standards to catch abuse?

For research purposes please send me an answer with Yes or No

I would dearly love to know!

 

 

Looking forward

As simple as it sounds the big problem has always been time…

Who has enough time to add QUERYNO to all their old legacy code? No-one of course!

But then SQL WorkloadExpert can help there too using our “Multi-Row Fetch” Use Case to show you the legacy code that gives the “biggest bang for the buck” and enables you to kill two birds with one stone!

As you change the heavy hitters for MRF, simply use an edit macro to add in QUERYNO to all of the embedded SQL

– Simple as that!

 

As usual any queries or criticism gladly accepted!

TTFN

Roy Boxwell

2014-09 Detecting invisible SQL since DB2 10

 

What you can actually see

is not everything that is really there…

   

 

The never-seen-before Static SQL Statements

I’ve been involved in reviewing some data provided by our new SQL WorkloadExpert tool (aka WLX). Over the past weeks, the one thing that I’ve seen time and time again, is the sheer number of Static SQLs that are running – and I mean running badly!

In the past, unless you always monitored 24×7, you never really had a chance to see all the static SQL that was running on your Plex. But now WLX makes for a real game changer!

It’s amazing what you can see – without the cost of a 24×7 Monitor.

I liken it to the phrase “Eyes Wide Shut”.

 

What you can do in WLX is a different way to find bad guy SQLs and that is by using an intensive view to find the SQLs that use a large amount of CPU per hour. I call these guys my “key-players” now some of these are obviously “old friends” but you will be surprised at how many “new contacts” you suddenly have!

Here are a couple of beautiful little examples that I found by using WLX to show me the most CPU intensive SQLs running on a Plex over a couple of days.

 

Viewing the static SQL

First query found now, and not seen before, looks like:

SELECT COL1
FROM TABLE1 T1
WHERE T1.IDENT = ?
AND T1.STATUS NOT IN ( 4, 6 )
WITH UR

Our systematic history viewer displays the following. The left hand scale is seconds and the right hand scale is logarithmic absolute numbers:

 

 

Shown here above are the CPU and the Elapsed Time, both per hour and in seconds. Then Getpages and Executions, both per hour and logarithmically graphed.

What you can see on the right side of the graph, is a dramatic drop in CPU and Elapsed and Getpages while the execution rate is constant. This was achieved by simply adding an appropriate index for that query.

 

 

Viewing the SQL from a different angle

Viewing the same data in a different way, (now on the per execution level instead of the per hour level) the results shown below look even better! (The left hand scale is seconds and the right hand scale is absolute numbers):

 

The Execution rate went *up*, but the resource usage dived down after the index was created – Great stuff indeed! This statement always flew under the radar and never raised a red flag before, but now? Slaps on the back all round!

Of course you could ask “Why was this not seen before?”

the answer is “It was never seen before!” Eyes wide shut – remember?

 

The same in tabular form

For those of you who may find the graphs hard to read, here’s the above data in a tabular form:

 

 

Never-seen-before DELETE Statement

Next up, is a nice Little never-seen-before DELETE Statement:

DELETE FROM TABLE1
WHERE COL1 = ?
AND   COL2_DATE = ?
AND   COL3 = ?
AND   COL4 = ?
AND   COL5 = ?
AND   COL6 = ?
AND   COL7 = ?

In the graph below, the left hand scale is seconds and the right hand scale is logarithmic absolute numbers:

4(9)

 

Again, looking at the per hour data, you can see a nice “dive” effect right after an index on all columns was created (Last three data points in this case). Good catch, eh?

 

Below is a new view of additional data for locks and waits. Note that the Global Lock wait count magically “disappears” due to it hitting zero. Again you can see the graph very nicely diving down at the end. Wonderful!

 

 

Again: here’s the above data in a tabular form:

 

 

 

 

 

What you can actually see is not everything that is really there…

These examples quickly show how Static SQL that was always thought to be well-tuned and running “OK” can, in fact, be hogging your machine without you even knowing it!

Remember that what you can actually see is not everything that is really there…

I wonder how many of these invisible hogs you have at your site? And have you*not* yet seen them?

 

As usual any queries or criticism gladly accepted!

TTFN

Roy Boxwell

2014-08: A Million ways to kill your DSC (Dynamic Statement Cache)

That’s No Way to Treat Your Best Friend!

 

The Dynamic Statement Cache (DSC) can, and should, be your best friend for helping SQL run nice and fast on your machine. If you are good, you have a latency (how long the statements stay in the DSC) of about two days, and everything looks hunky-dory at first glance.

However, it could be that your best friend is not all he’s cracked up to be!

The first inkling that something somewhere is not right, is when you see your DSC flush rate soaring upwards (i.e. how many statements are being flushed per hour). Statements are flushed all the time through RUNSTATS, or security changes, etc. But when you see thousands of statements grabbing their coats and heading for the door you know something is wrong – and it can’t just be the music!

 

1,000’s of INSERTS statements are taking a “slot” from the DSC for no purpose!

What I have seen is that lots of people concentrate on Dynamic SQLs that are SELECT or UPDATE or predicated DELETEs and MERGEs, but no-one bothers about simple INSERT statements… and guess what I had seen? Yep, 1,000’s are INSERTS using literals, each of which takes a “slot” from the DSC for no purpose whatsoever!!! These are really, really nasty indeed…

 

How to list and fix the „bad guys“ (the INSERTS Statements)

To find them,

– I use a snap of the DSC.

– Then I select only the INSERT Statements,

– and then simply exclude any that have parameter markers.

What you are left with, is the list of “bad guys” that, if excessive in number, must be fixed as soon as possible!

 

Here’s how I do this using our SQL PerformanceExpert (SPX):

1 First I snap the DSC and show only the INSERTS:

News from the labs Newsletter 2014-08 - A Million ways to kill your DSC - Screenshot1

Here you can see in our DB2 10 NF test system that there are 2,673 statements.

 

2 Identifying the „good guys“

After filtering on “INSERT”, we get the next Panel:

News from the labs Newsletter 2014-08 - A Million ways to kill your DSC - Screenshot2

Here I can see only 200 INSERTS are in the cache. Note that some have multiple executions.

These are the “good guys” – Multiply executed but singly prepared.

 

3 Now I use the primary command PR to just dump these statements in a file for ISPF usage:

"News from the labs" 2014-08 - A Million ways to kill your DSC - Screenshot3 - SQL Statements in a file for ISPF usage

Here all of the SQL text is shown, (it carries on over to the right hand side, of course!)

Now I can see some INSERTS with Parameter Markers, but I want to see the ones without.

 

4  INSERTS without Parameter Markers

Simply doing these ISPF commands enables me to see that view too:

X ALL

F ALL ?

F ALL INSERT 1

 

Then I simply page down looking for INSERTS with no matching question marks.

Of course you can also do this with a small REXX, etc

News from the labs Newsletter 2014-08 - A Million ways to kill your DSC - Screenshot4

Now I can quickly see that there appears to be a long set of INSERTS – all with literals.

All of these should be changed to be Parameter Markers to help boost overall system Performance!

 

Naturally I use the above SQL data for much more than just Parameter Marker usage. You can imagine the fun to be had just conducting text searches to see which SQL uses which Built-In-Function, for example. CHAR9 usage anyone?

The possibilities are endless!

Now I get to spend lots of time with my best friend.

 

Happy Tuning!

As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect

2014-07: APREUSE(WARN) – like getting married?


DB2 11 REBIND, something old, something new, something borrowed, something blue

Something olde,
Something new,
Something borrowed,
Something blue,
oh my goodness,
what’s going on,
within DB2?


Olde, New, Borrowed and Blue… in DB2 11 REBIND processing

Now once you stop laughing, the idea is that:

olde” is REBIND processing which changes old access paths,
new” is the new access path that comes from REBIND processing,
borrowed” is the use, and abuse, of OPT_HINT processing, and finally
blue” is, of course, IBM itself!!!

 

New in DB2 11… REBINDs with APREUSE(WARN)

Now in DB2 11 CM and above, you can issue REBINDs with the APREUSE(WARN) feature. This allows you to be nearly 100% sure that your access paths will *not* change, *but* it will regenerate the run time structures which allows fast xPROC processing. How does it do this magic? Quite simply: DB2 uses “internal” Opt Hints. They are not externalized in the PLAN_TABLE and they ignore the OPT_HINT ZPARM setting completely. In fact, they are generated from the “new”, in DB2 9 NF and above, directory element Explain Plan section in the Package, (this contains a sort of compressed version of the PLAN_TABLE access path data but nowhere near all of the access path data is saved away).

 

So what happens is, DB2 gets told to REBIND a package

– first it extracts the current access plan and stores it away,
– then it calls the optimizer for the new access path using the stored version as an OPT_HINT.
– If the hint is used, the access path is honoured and, if running with EXPLAIN(YES), you get PLAN_TABLE data with “APREUSE” in the column HINT_USED while OPTHINT stays empty.

 

Unknown access path “deviations“ if the hint failed

Now, if a hint failed, a new access path is created but you then get spaces in the HINT_USED column. I bet you see what I am aiming for??? Yep, all this does is create unknown access path “deviations” while also retaining possibly useless and obsolete access paths! The whole system makes nearly no sense to me whatsoever!

 

APREUSE(ERROR) to migrate to DB2 11

Now with APREUSE(ERROR) at least you either got new code for a complete package or you didn’t get *any* runnable code. It was still not taking advantage of any new optimizer functionality, but on the other hand was 100% stable.

The way I see it today, the only way to actually use this feature is purely in version migration and with some sort of explain tool to actually check out stuff before & after the event.

 

My way to migrate to DB2 11 is

1) Make sure RUNSTATS is 100% up to date and correct

2) Make sure plan management is active and possibly in EXTENDED mode

3) Make sure all packages are bound with EXPLAIN(YES)

4) Migrate to DB2 11 CM

5) Use a tool to analyse all current access paths and see which ones go *bad* with and without using APREUSE(WARN). Also, check out any CHANGED access paths (table order, different Index etc.)

6) Run with APREUSE(WARN) only those packages where no changes are found and also use APRETAINDUP(NO)

7) Run without APREUSE(WARN) on those packages with improvements to get the full functionality that you have paid good money for

8) Work first on any statements that go bad and then the changed ones

9) From this point on analyse every REBIND package and only do the REBIND if improvements in access path are visible

10) Get ready for DB2 vnext and repeat the above!

 

APREUSE(WARN)’s drawback

The real dangers with APREUSE(WARN) are that you will end up locking your code into access paths that are completely obsolete and could be dramatically improved with the creation of a new index etc. This new data is not and cannot be used due to the hint nature of this parameter. Worse, in my opinion, is the fact that there are a ton of reasons when the hint is not used and new access paths come charging over the hill like some irate mother-in-law!!!

 

Use APREUSE(WARN) *just* for release migration and *only* for the first rebind

Last word: As always with new parameters and options there are times to use them and times to *not* use them!  In this case *just* for release migration and *only* for the first rebind. Then — just like a wedding dress — never ever use again until the next one.

 

As usual, any comments or criticisms are greatly welcome!

How do you plan to use this parameter?

 

TTFN Roy Boxwell
Senior Software Architect

2014-06: SQL Metrics – Ripe for the Plucking

After a short absence, I am now back with my newsletters on a regular basis. During the summer, when you were (hopefully) relaxing and enjoying time with friends and family, my co-workers were busy here updating our websites. You may not see a big difference in „the look“, as most of the improvements were behind the scenes, but we have now migrated to a completely new platform and intend to be adding more features and enhancements to our sites as we move forward; all in the name of providing even better service for our customers!

As many of you know, August is the time of year us Europeans take our long summer vacations. Dreaming of Ice-cream in Italy, the balmy breezes of the Balearic Islands, or – in my case – the dense fog banks of Dover, (what white cliffs?).

So I’ll keep this newsletter short, while I unpack my swimsuit. For the past several months, I’ve been visiting various customer sites on DB2 roadshows. It’s always fascinating to re-acquaint myself with some real-life situations, but it never ceases to amaze me that so many sites still rely on the same traditional methods that have been used for the past 10 – 20 years.

Poorly designed SQL

Modern structures, languages, APIs etc. do nothing to stop bad, poorly-designed, SQL from landing in Production. It is, at the end of the day, the DBA who is left to pick up the pieces and try to figure out how to get a quart *out* of a pint pot, (or a large frame into a small swimsuit.) One of our newest product developments is called SQL WorkloadExpert (WLX), and it was designed to work for DB2 10 NFM and above only. Why, you ask? Because it needs the new and enhanced IFCIDs that came with that release – notably the enhanced 316, which gets written when a Dynamic Statement Cache is flushed, and the new IFCIDs 400 and 401, which give the same data but from the EDM Pool for static SQL. (Personally, I refer to this as the Static Statement Cache, or SSC.)

Build a Data Warehouse containing all the Dynamic SQL

This new and useful data is used to build a Data Warehouse containing all the SQL that is running in a plex. Also, using EXPLAIN data, DB2 Catalog data, and the RealTimeStatistics table, it builds up a complete picture of who is running what, when – and how often – on your machine. This is particularly interesting for some of those sites that I visited recently. It is truly amazing how much “low hanging fruit” is out there for really, really simple tuning that yields a huge bang for the buck! The aforementioned IFCIDs establish performance metrics that give you a handle on where CPU, IO, and Elapsed time is all going. These metrics cause the so-called “bad guys” to virtually leap off the page! The most quantitative metrics that are most useful for the “first” tuning aims, are the Executions per hour, CPU per hour and IO (Getpages) per hour.

 

2,500 seconds of CPU per hour reduced only by adding one little index…

For example, at one of those shops I saw the following: (This is a very large shop with 18 way data sharing and 10’s of CPUs per machine) One SQL was running 1.3 Million times per hour, using 2,500 seconds of CPU per hour and 240 Million getpages per hour. This is intense! The SQL was not particularly complex in this case either, and the per execution rates were not particularly significant. However, by simply adding one little index, the site reduced CPU consumption to 1,700 seconds per hour and 180 Million getpages per hour! Immediately, this provided a savings of 40% overall by providing such a huge performance boost. Another SQL’s CPU consumption was reduced from 17,111 seconds per hour down to just 16 seconds per hour! …Wow. Finally, a third SQL had an Elapsed time of 30,000 seconds per hour reduced down to 30 seconds per hour! The point I am trying to make is:   Don’t always trust blindly in your traditional Top Ten reports. By digging just a little deeper, you may be able to find some very easy (tuning) fruit, ripe for plucking. Now how do I tune my mind back in to „work“ mode…

Happy Tuning!

As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect

2012-07: Spatial Indexes – Do you know where your customers live?

Well I must admit that I did not know what a long and painful birth this “little” newsletter would be!

First I had to get a whole bunch of installation jobs run, then a few APARs from good old IBM, and then I had to run the “enable” Spatial support job (DSN5ENB) and finally the Spatial bind job (DSN5BND). This took a while and gave me a few extra grey hairs…however if you follow the documented approach it all works – now of course it assumes that everyone in the world lives in the USA or the UK as continental Europe uses the comma as a decimal separator but the Spatial support has hard coded decimal point – This also cost me LOTS of grey hairs as I had to manipulate the data using the ISPF Editor to get the format “right”.

 

So there I am with 20 rows of IBM test data and a newsletter to write…hmmm…need more data methinks.. So I decide to use GOOGLE to find some data and Voila! I find 43,191 ZIPCODEs from the USA with Latitude and Longitude…of course it is comma separated…load into excel change the format and save away to then be uploaded to the host and then INSERTED into a new spatial table…All goes horribly wrong because you cannot use a column during the insert of a function to get a point… GRRR!!

But let us do what I did step by step:

First I created a little table to hold the data from the excel:

CREATE DATABASE ZIPCODE;
COMMIT;
CREATE TABLE BOXWELL.ZIPCODES
(
ZIP           INTEGER     NOT NULL,
CITY          VARCHAR(64) NOT NULL,
STATE         CHAR(2)     NOT NULL,
LATITUDE      DECIMAL(9 , 6) NOT NULL,
LONGITUDE     DECIMAL(9 , 6) NOT NULL,
TIME_ZONE     SMALLINT    NOT NULL,
DST_FLAG      SMALLINT    NOT NULL
)
IN DATABASE ZIPCODE;
COMMIT;

Then I loaded it up with data

OUTPUT START FOR UTILITY, UTILID = LOAD
PROCESSING SYSIN AS EBCDIC
LOAD DATA FORMAT DELIMITED COLDEL ';' CHARDEL '"' DECPT '.'
INTO TABLE BOXWELL.ZIPCODES
(ZIP INTEGER,
CITY CHAR,
STATE CHAR,
LATITUDE DECIMAL,
LONGITUDE DECIMAL,
TIME_ZONE SMALLINT,
DST_FLAG SMALLINT)
RECORD (1) WILL BE DISCARDED DUE TO 'ZIP'
CONVERSION ERROR FOR BOXWELL.ZIPCODES
ERROR CODE '02 - INPUT NUMERIC INVALID'
(RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=43191 FOR TABLE
BOXWELL.ZIPCODES
(RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED=43191 FOR
TABLESPACE ZIPCODE.ZIPCODES
(RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS NOT LOADED=1
(RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=43192
(RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:01
DISCARD PHASE STATISTICS - 1 INPUT DATA SET RECORDS DISCARDED
DISCARD PHASE COMPLETE, ELAPSED TIME=00:00:00
UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

The first record in the excel table was the headings record so it was OK to lose it by the way.

 

Then I created a new table with the LOCATION column and tried to INSERT like this:

CREATE DATABASE ZIPCODES;
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
COMMIT;
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
CREATE TABLE USA.ZIPCODES
(
LOCATION      DB2GSE.ST_POINT ,
LONGITUDE     DECIMAL(9 , 6) NOT NULL,
LATITUDE      DECIMAL(9 , 6) NOT NULL,
STATE         CHAR(2)     NOT NULL,
TIME_ZONE     SMALLINT    NOT NULL,
DST_FLAG      SMALLINT    NOT NULL,
ZIP           INTEGER     NOT NULL,
CITY          VARCHAR(64) NOT NULL
)
IN DATABASE ZIPCODES;
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
COMMIT;
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
INSERT INTO USA.ZIPCODES
SELECT DB2GSE.ST_POINT('POINT (LONGITUDE LATITUDE)' , 1003)
,LONGITUDE
,LATITUDE
,STATE
,TIME_ZONE
,DST_FLAG
,ZIP
,CITY
FROM BOXWELL.ZIPCODES
;
---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -443, ERROR:  ROUTINE GSEGEOMFROMTEXT (SPECIFIC NAME
STCO00002GFT) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT
GSE3049N  Invalid WKT format, expecting a number instead of
LONGITUDE  LATITUDE).
DSNT418I SQLSTATE   = 38SV8 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXRUFS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -101 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFF9B'  X'00000000'  X'00000000' X'FFFFFFFF'
X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

Oh dear… So now I wrote a select to generate a set of INSERTS like this:

SELECT 'INSERT INTO USA.ZIPCODES VALUES (' ||
'DB2GSE.ST_POINT(''POINT (' || LONGITUDE ||
' ' || LATITUDE || ')'' , 1003)' ||
', '  || LONGITUDE ||
' , ' || LATITUDE ||
' ,''' || STATE ||
''', '  || TIME_ZONE ||
' , '  || DST_FLAG  ||
' , ' ||  ZIP ||
' ,''' || CITY || ''') §'
FROM BOXWELL.ZIPCODES  ;

To create the geometry for the LOCATION column you can use the ST_POINT function and that takes as input a LONGITUDE and a LATITUDE and, all important, the SRS_ID which tells the system which Spatial Reference System you are using – I use 1003 in my examples because my data is “old” and used the WGS84 standard. The supplied Geometries have these names and, more importantly, the SRS_IDs that must be used!

SELECT * FROM DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS;
---------+---------+---------+-------------+-
SRS_NAME                               SRS_ID
---------+---------+---------+-------------+-
DEFAULT_SRS                                 0
NAD83_SRS_1                                 1
NAD27_SRS_1002                           1002
WGS84_SRS_1003                           1003
DE_HDN_SRS_1004                          1004

 

This SQL created output like this:

INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 210 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 211 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 212 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 213 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 214 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-71,013202 43,005895)' , 1003), -71,013202 , 43,005895 ,'NH', -5 , 1 , 215 ,'Portsmouth') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-72,637078 40,922326)' , 1003), -72,637078 , 40,922326 ,'NY', -5 , 1 , 501 ,'Holtsville') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-72,637078 40,922326)' , 1003), -72,637078 , 40,922326 ,'NY', -5 , 1 , 544 ,'Holtsville') §
INSERT INTO USA.ZIPCODES VALUES (DB2GSE.ST_POINT('POINT (-66,749470 18,180103)' , 1003), -66,749470 , 18,180103 ,'PR', -4 , 0 , 601 ,'Adjuntas') §
.
.
.

Now to get this to fly I had to change my SPUFI output row maximum, default column width, and output record sizes but finally I had a file with 43,191 INSERTS. As you can see the DECIMAL numbers have a comma in them so I had to do ISPF “change all” commands to change them to full stops and then I had to write a small dynamic SQL program to actually execute these SQLs as they were up to 177 bytes wide…I already had a high-speed version of DSNTIAD that I had written myself a while ago and a simple couple of changes and shazam! I had 43,191 rows of data – the INSERTS took 15 minutes of CPU by the way….

 

So now I am the proud owner of 43,191 zip codes *and* their spatial “locations” – what could I do? Well the first thing that sprung to mind was – How many cities with how many zip codes are there within a radius of 15 miles of Phoenix, AZ ?

SELECT A.CITY, COUNT(*)
FROM USA.ZIPCODES A
WHERE DB2GSE.ST_WITHIN(A.LOCATION,DB2GSE.ST_BUFFER (
(SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 85009)     -- phoenix
, 15 , 'STATUTE MILE')) = 1
GROUP BY A.CITY
;

 

There are about 73 different spatial functions by the way. In the above SQL I use just two

– ST_BUFFER has three parameters. The first is a “geometry” or LOCATION type field, then a distance, and finally the units. What it does is create a geometry space that is centered on the input geometry and is then the number of units around it (A radius in this case as we have a point as the input geometry). Thus we have a “space” 15 statute miles in radius centered on Phoenix AZ (Well actually on the location of zip code 85009 but that is near enough for me!).

– ST_WITHIN has two parameters which are both “geometries” and if one is within the other it returns a 1 else a 0 thus enabling the simple SQL I wrote.

This query returns:

---------+---------+---------+---------+---------+--------
CITY
---------+---------+---------+---------+---------+--------
Avondale                                             1
Carefree                                             1
Cashion                                              1
Cave Creek                                           1
Chandler                                             2
Fountain Hills                                       1
Gilbert                                              1
Glendale                                            12
Laveen                                               1
Mesa                                                 5
Palo Verde                                           1
Paradise Valley                                      1
Peoria                                               5
Phoenix                                             71
Scottsdale                                           8
Sun City                                             2
Sun City West                                        1
Surprise                                             2
Tempe                                                8
Tolleson                                             1
Tortilla Flat                                        1
Wickenburg                                           1
Youngtown                                            1
DSNE610I NUMBER OF ROWS DISPLAYED IS 23
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

 

Or the same for, say, Moline, IL

SELECT A.CITY, COUNT(*)
FROM USA.ZIPCODES A
WHERE DB2GSE.ST_WITHIN(A.LOCATION,DB2GSE.ST_BUFFER (
(SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 61265)     -- moline
, 15 , 'STATUTE MILE')) = 1
GROUP BY A.CITY
;
---------+---------+---------+---------+---------+---------+
CITY
---------+---------+---------+---------+---------+---------+
Andalusia                                               1
Barstow                                                 1
Bettendorf                                              1
Blue Grass                                              2
Buffalo                                                 1
Carbon Cliff                                            1
Coal Valley                                             1
Colona                                                  1
Davenport                                               9
East Moline                                             1
Eldridge                                                1
Hampton                                                 1
Le Claire                                               1
Milan                                                   1
Moline                                                  2
Orion                                                   1
Osco                                                    1
Pleasant Valley                                         1
Port Byron                                              1
Preemption                                              1
Princeton                                               1
Rapids City                                             1
Rock Island                                             5
Sherrard                                                1
Silvis                                                  1
Taylor Ridge                                            1
DSNE610I NUMBER OF ROWS DISPLAYED IS 26

 

Finally I wondered about the distance between two locations or geometries so I thought how many English Chains (I kid you not!) and Miles between these two cities??

SELECT INTEGER(DB2GSE.ST_DISTANCE(
(SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 61265)     -- moline
, (SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 85009)     -- phoenix
, 'STATUTE MILE')) AS MILES
,INTEGER(DB2GSE.ST_DISTANCE(
(SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 61265)     -- moline
, (SELECT B.LOCATION
FROM USA.ZIPCODES B
WHERE B.ZIP = 85009)     -- phoenix
, 'BRITISH CHAIN (SEARS 1922)')) AS CHAINS
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+-----
MILES       CHAINS
---------+---------+---------+---------+-----
1306       104520
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Isn’t that great! You can even specify which type of chain!!! The Function here ST_DISTANCE just accepts two geometries and the Unit you wish to have output. Just for your reference the number of units are in the GSE view DB2GSE.ST_UNITS_OF_MEASURE the first few rows look like

---------+---------+---------
UNIT_NAME
---------+---------+---------
METRE
FOOT
US SURVEY FOOT
CLARKE'S FOOT
FATHOM
NAUTICAL MILE
GERMAN LEGAL METRE
US SURVEY CHAIN
US SURVEY LINK
US SURVEY MILE
KILOMETRE
CLARKE'S YARD
CLARKE'S CHAIN
CLARKE'S LINK
BRITISH YARD (SEARS 1922)
BRITISH FOOT (SEARS 1922)
BRITISH CHAIN (SEARS 1922)
BRITISH LINK (SEARS 1922)
BRITISH YARD (BENOIT 1895 A)
BRITISH FOOT (BENOIT 1895 A)
BRITISH CHAIN (BENOIT 1895 A)
BRITISH LINK (BENOIT 1895 A)
BRITISH YARD (BENOIT 1895 B)
BRITISH FOOT (BENOIT 1895 B)
BRITISH CHAIN (BENOIT 1895 B)
BRITISH LINK (BENOIT 1895 B)
BRITISH FOOT (1865)
INDIAN FOOT

There is also a handy DESCRIPTION column that explains how and why.

 

So now I did an EXPLAIN of the three queries (1 is Phoenix, 2 is Moline, and 3 is Distance) and got this

---------+---------+---------+---------+---------+---------+----------+
LINE   QNO  PNO  SQ  M  TABLE_NAME A CS  INDEX IO  UJOG  UJOGC P CE
---------+---------+---------+---------+---------+---------+----------+
00001  01   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00001  01   02   00  3               00        N   ----  ---Y      S
00001  02   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00002  01   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00002  01   02   00  3               00        N   ----  ---Y      S
00002  02   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00003  01   01   00  0  SYSDUMMY1  R 00        N   ----  ----   S
00003  02   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00003  03   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00003  04   01   00  0  ZIPCODES   R 00        N   ----  ----   S
00003  05   01   00  0  ZIPCODES   R 00        N   ----  ----   S
-----+---------+---------+---------+---------+---------+-----------+
QUERYNO PROGNAME STMT_TYPE CC PROCMS PROCSU REASON
-----+---------+---------+---------+---------+---------+-----------+
      1 DSNESM68 SELECT    B   327   929    TABLE CARDINALITY UDF
      2 DSNESM68 SELECT    B   327   929    TABLE CARDINALITY UDF
      3 DSNESM68 SELECT    B   676  1918    TABLE CARDINALITY UDF

 

No big surprises as I have not run a RUNSTATS and have not created any indexes yet.

 

So now a full runstats and a re-explain

---------+---------+---------+---------+---------+---------+---------+-
LINE   QNO  PNO  SQ  M  TABLE_NAME A  CS  INDEX IO  UJOG  UJOGC  P  CE
---------+---------+---------+---------+---------+---------+---------+-
00001  01   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00001  01   02   00  3                 00        N   ----  ---Y      S
00001  02   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00002  01   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00002  01   02   00  3                 00        N   ----  ---Y      S
00002  02   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00003  01   01   00  0  SYSDUMMY1  R   00        N   ----  ----   S
00003  02   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00003  03   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00003  04   01   00  0  ZIPCODES   R   00        N   ----  ----   S
00003  05   01   00  0  ZIPCODES   R   00        N   ----  ----   S
-----+---------+---------+---------+---------+---------+---------+----
QUERYNO  PROGNAME  STMT_TYPE CC PROCMS PROCSU  REASON
-----+---------+---------+---------+---------+---------+---------+----
      1  DSNESM68  SELECT    B    2427   6893  UDF
      2  DSNESM68  SELECT    B    2427   6893  UDF
      3  DSNESM68  SELECT    B     676   1918  TABLE CARDINALITY UDF

The MS and SU numbers went up as now the optimizer realized the size of the table. So now I created a unique index on column ZIP

CREATE UNIQUE INDEX USA.ZIPCODES_IX ON USA.ZIPCODES (ZIP  ASC) USING STOGROUP SYSDEFLT PRIQTY 720 SECQTY 720 CLUSTER FREEPAGE  0 PCTFREE  10 CLOSE YES;

 

Run RUNSTATs and re-explained

 ---------+---------+---------+---------+---------+---------+---------+----
LINE   QNO  PNO  SQ  M  TABLE_NAME A CS INDEX        IO  UJOG  UJOGC  P  CE
---------+---------+---------+---------+---------+---------+---------+-----
00001  01   01   00  0  ZIPCODES   R 00               N  ----  ----   S
00001  01   02   00  3               00               N  ----  ---Y      S
00001  02   01   00  0  ZIPCODES   I 01 ZIPCODES_IX   N  ----  ----
00002  01   01   00  0  ZIPCODES   R 00               N  ----  ----   S
00002  01   02   00  3               00               N  ----  ---Y      S
00002  02   01   00  0  ZIPCODES   I 01  ZIPCODES_IX  N  ----  ----
00003  01   01   00  0  SYSDUMMY1  R 00               N  ----  ----   S
00003  02   01   00  0  ZIPCODES   I 01  ZIPCODES_IX  N  ----  ----
00003  03   01   00  0  ZIPCODES   I 01  ZIPCODES_IX  N  ----  ----
00003  04   01   00  0  ZIPCODES   I 01  ZIPCODES_IX  N  ----  ----
00003  05   01   00  0  ZIPCODES   I 01  ZIPCODES_IX  N  ----  ----
-----+---------+---------+---------+---------+---------+-------+--------
QUERYNO PROGNAME STMT_TYPE CC  PROCMS   PROCSU  REASON
-----+---------+---------+---------+---------+---------+-------+--------
     1  DSNESM68 SELECT    B     2427     6893  UDF
     2  DSNESM68 SELECT    B     2427     6893  UDF
     3  DSNESM68 SELECT    B      676     1918  TABLE CARDINALITY UDF

Aha! My index is being used but the cost estimates did not change a bit! So now is the time for a Spatial Index to rear its ugly head and get involved. Now a spatial index is a very, very special type of beast. It is *not* a standard B-Tree style index and it exists “outside” the scope of normal DB2 Indexes. Now to create a SPATIAL INDEX you must use a stored procedure with a bunch of parameters:

 

sysproc.ST_create_index ( table_schema/NULL, table_name , column_name , index_schema/NULL, index_name , other_index_options/NULL, grid_size1 , grid_size2 , grid_size3 , msg_code , msg_text )

 

I am actually using the command processor DSN5SCLP so my syntax looks like:

DSN5SCLP /create_idx ZA00QA1A +
-tableschema USA -tablename ZIPCODES -columnname LOCATION +
-indexschema USA -indexname LOC_IX +
-otherIdxOpts "FREEPAGE 0" +
-gridSize1 1.0 -gridSize2 2.0 -gridSize3 3.0

When it executes it just tells you this:

********************************* TOP OF DATA *****
GSE0000I  The operation was completed successfully.
******************************** BOTTOM OF DATA ***

 

Now do a re-explain

---------+---------+---------+---------+---------+---------+---------+--
LINE QNO PNO SQ  M TABLE_NAME A CS INDEX         IO  UJOG  UJOGC  P  CE
---------+---------+---------+---------+---------+---------+---------+--
00001 01 01  00  0 ZIPCODES   I 01 LOC_IX        N   ----  ----
00001 01 02  00  3              00               N   ----  ---Y      S
00001 02 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
00002 01 01  00  0 ZIPCODES   I 01 LOC_IX        N   ----  ----
00002 01 02  00  3              00               N   ----  ---Y      S
00002 02 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
00003 01 01  00  0 SYSDUMMY1  R 00               N   ----  ----   S
00003 02 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
00003 03 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
00003 04 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
00003 05 01  00  0 ZIPCODES   I 01 ZIPCODES_IX   N   ----  ----
-----+---------+---------+---------+---------+---------+---------+-----
QUERYNO  PROGNAME STMT_TYPE CC  PROCMS   PROCSU  REASON
-----+---------+---------+---------+---------+---------+---------+----
      1  DSNESM68 SELECT    B        2        5  UDF
      2  DSNESM68 SELECT    B        2        5  UDF
      3  DSNESM68 SELECT    B      676     1918  TABLE CARDINALITY UDF

BINGO! Looks like a winner to me!

Now a RUNSTATS and a re-explain

DSNU000I    200 09:00:34.20 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = RUNSTATS
DSNU1044I   200 09:00:34.35 DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNU050I    200 09:00:34.37 DSNUGUTC -  RUNSTATS TABLESPACE ZIPCODES.ZIPCODES TABLE(ALL) INDEX(ALL KEYCARD)
SHRLEVEL REFERENCE UPDATE ALL SORTDEVT SYSALLDA SORTNUM 4
DSNU186I  -QA1A 200 09:00:34.39 DSNUGSRI - A SPATIAL INDEX CANNOT BE PROCESSED BY THIS UTILITY
DSNU012I    200 09:00:34.41 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8

Argghhh! That was *not* in the documentation!!! So not just LOAD dies but also RUNSTATS!! The message itself is *very* helpful:

 

DSNU186I csect-name– A SPATIAL INDEX CANNOT BE PROCESSED BY THIS UTILITY

Explanation: The target objects specified for the utility include one or more spatial indexes, which are not supported by this utility. If the utility specifies a table space, then you can drop the spatial indexes, run the utility, and then recreate the indexes.
System action: The utility does not execute.
Severity: 8

 

I will try a REORG just for fun…nope it dies as well. Looks like you must remember to DROP all spatial indexes before any ”normal” DB2 Database Maintenance and then CREATE them back afterwards. Or if you have our RealTimeDatabaseExpert you can simply Exclude them from processing.

 

Finally this month a few CPU statistics from before and after index creation:

Query   Without Spatial Index   With Spatial Index
 Elapsed       CPU Elapsed CPU
Phoenix Query
Moline Query
Distance
202 seconds
186 seconds
2
186 seconds
172
1
25 seconds
13
2
23 seconds
9
1

Note that these figures are for 100 times execution in batch. As can be seen use of Spatial Indexes is very very good!

 

Well I learnt a lot this month and hope some of you did as well – As usual any comments or questions please feel free to mail me!

 

2011-01: APARS for RTS, RUNSTATS, and SQL PERFORMANCE for DB2 z/OS – Which are needed and why?

The Programmers‘ 1st Law of Bugdom; all non-trivial programs have bugs –  ergo   DB2 has bugs!

Of course, software being software means “keeping an eye” on the “APARs of interest”.  This is hard work because you must search the IBM technical problem database – lots of people do not like doing this, have no time to do this, or do not know how to do this!  At the end of this newsletter, I’ve attached lists of important APARs to save you the work.

First up: Real-Time Statistics (RTS):

Real-time statistics came in as an optional extra in DB2 V7.  They are now firmly embedded in the architecture of DB2 to the point of being used for space allocation, and even the Optimizer is starting to use this data. In this case, it would be very sensible to make sure that the data is as accurate as possible.  To do this, any bugs must be found and removed from the code.
Of course, you are already using RTS to drive your major DB2 utilities, aren’t you?  I hope so!  This makes 90% of all RUNSTATS irrelevant and stops 95% of REBINDs, which stops 99% of bad access paths in production from ever occurring.

Next up: RUNSTATS:

As you all know, the DB2 Optimizer makes its cost-based decisions on several columns in the DB2 catalog, and RUNSTATS updates some of these as well – just as RTS has bugs, so does RUNSTATS.  When you have bad catalog data, access paths and performance can suffer. SEGUS has a licensed FREEWARE product called “Statistics HealthCheck”, which checks the DB2 catalog for internal consistency and plausibility.  There is also a fully licensed version that will actually generate “corrective” RUNSTATS.  But, of course, if the bad catalog data comes from a bug in RUNSTATS, a new run of RUNSTATS will not help you and you will still suffer from bad performance.

Last up: SQLPERFORMANCE:

This is a keyword available in the IBM technical problem database search that I use because the Optimizer also has bugs.  (Of course, it also has more features than you can shake a stick at).  In other words, “it has lots of switches and buttons and dials” that can be tweaked, pushed, prodded, and flipped.  All of these have a direct effect on performance, so when Terry Purcell et al decides to change the composite filter factor for negative square-roots, it is nice if you can see this and apply the new feature where and when needed.

As an “aide de camp”, I maintain a most current APAR lists.  I track these and update my lists every month (I only keep about two years’ worth, because that old enough). Please contact us if you wish to receive this list. As soon as it becomes available, you will get an updated list containing the latest APARs for RTS, RUNSTATS and SQLPERFORMANCE.

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

2011-04: RTS – data for DBA daily usage

IBM brought in the RTS in DB2 V7 and it was a paradigm-shattering event! Well, at least for me…
What it did, was finally give us DBAs the ability to “see” what is happening in our DB2 sub-systems, without having to run an expensive RUNSTATS utility.

Up until this time, shops had to run 1000’s of RUNSTATS to see if a REORG was needed, and, of course, these RUNSTATS caused massive side effects:

  1. The DSC was invalidated for all RUNSTATSed objects
  2. Automatic REBIND detection software then scheduled a REBIND – which sometimes caused severe performance problems when the access path “went south” or “turned pear-shaped”
  3. Lots of CPU was wasted on non-changed data by RUNSTATS

 

The moment that RTS came into town it changed the whole game. Now you only need a RUNSTATS when you actually need a RUNSTATS and you only need a REORG when you actually need a REORG. (See my list of reasons why you do not actually need a REORG later in this newsletter). Just to complete the picture: You only need a COPY when you actually need a COPY! Lots of shops exploiting the RTS reported huge costs savings from using the real-time data for threshold based maintenance – if you haven’t yet considered the RTS then please do so, as it is a gold mine!

 

RTS tables from DB2 V8 up until DB2 10.

To start with, let us have a look at the RTS tables from DV8-10
As can readily be seen, IBM keeps adding columns to these tables!
Note that all the counter columns are NULLable. The way that the RTS data has been externalized, and which column(s) are updated  -and when – has changed a few times over the years. Now it seems to be very stable and the basic rule is:
When you first create an object (as long as it is not a DEFINE NO one of course!) it gets an entry in the RTS. The counters contain 0, but the relevant utility timestamp is NULL – which stops externalization of the counters (apart from REORGLASTTIME which is set to creation date).
When a RUNSTATS – or a COPY – has been done, then these counters start counting as well.

Now, just looking at the columns gives a great idea of the usefulness of this data: Upfront is a caveat, “The use of REORGLEAFNEAR is not recommended to decide upon a REORG or not”. The REORGLEAFNEAR is basically worthless as a data point. Recently, at a local DB2 user group meeting it was explained that the numbers in this column are really a waste of time and that, “IBM informed us that to use REORGLEAFNEAR in the REORG decision process was incorrect and could cause repetitive, needless, REORGs.” – You have been warned!! But apart from that, the data can help DBAs every day in their work.

 

REORG decisions:

Do not do a REORG
Simple huh? I basically see people all the time doing REORGs of TSs just because “The software told me to do it”. This is no excuse! The only real reason to REORG tablespaces is either to

a) reclaim space,

b) change attributes or

c) re-sort the data.

Now the last one really only makes sense if you do indeed have sequential access of the TS, otherwise, if the access is through an index and is effectively random, why bother with an expensive REORG???

 

RUNSTAT decisions:

Do not do a RUNSTATS
Equally simple! I see 1000’s of RUNSTATS conducted for absolutely no good reason. They kill your Dynamic Statement Cache (DSC), they can kill your access paths for REBINDs and BINDs, and what if you ran it when the table was empty but then along comes a LOAD of 2,000,000 rows??? Yup, a RUNSTATS run must be aware of the usage and environment and should be well timed. Basically, the rule is to run a RUNSTATS when the DB2 Optimizer benefits from the fresh statistics and generates a new, better access path. This may be a challenge if you don’t have the right database maintenance solution, but it’s another gold mine if you do!

 

COPY decision:

Do COPYs on-the-fly. Use SHRLEVEL CHANGE and run your ICs during the day. At 08:30 a.m. you have copied the world and your SLA’s for RECOVERY are great. What happens when you have a problem at 05:30 p.m.? I guess your SLA will *not* be met, right? What you must do, is learn to not be afraid of image copies during the day. They can save your life, but don’t do them on a fixed schedule. Do them when they’re needed!

At the INDEX level the RTS data can be used for the same set of Utilities as above.

 

IX REORG decisions:

The REORG of an index can bring real plus points! If REORGPSEUDODELETES is greater than 10% of TOTALENTRIES or REORGNUMLEVELS is greater than zero, then it is definitely worth doing an index REORG to clear out the junk and get the index back into tip-top condition. This should not change your access paths, if they were bound when the index was “fit”, but it will improve your elapsed time as the number of IOs will go down dramatically.

 

IX RUNSTAT decisions:

Do not do a RUNSTATS
Same as TS RUNSTATS, but with the added hint of never actually doing just an Index RUNSTATS on its own. If you do this, then the data stored in the DB2 catalog will start to “drift” apart and, eventually, even the DB2 Optimizer will “see” this and start to make bad decisions based on inaccurate data. If you can, always try to do a consistent full RUNSTATS at the TP or TS level. BTW, if you’d like to check your DB2 catalog for inconsistency, please try our Statistics HealthCheck Freeware. As the name says, it’s FREE! !
Also note that a RUNSTATS at the index level will only invalidate access paths in the DSC that actually currently use this index, so if the optimizer has picked a tablespace scan or another index, then just running a RUNSTATS index will not enable a new access path for existing statements – another reason to run at the TS or TP level!

 

IX COPY decision:

Do not do index COPYs on their own! Personally I am no fan of COPY YES indexes. The REBUILD INDEX utility is so fast these days, (I have examples of a 17,000,000 row table with 17 indexes and the rebuild took three minutes elapsed…). and in a data sharing system you can even bring the system crashing down around you if you do too many ALTERs at the same time, so be very careful! But, if you feel you must have COPY YES indexes to reduce CPU consumption during RECOVERY, then do the TS and the IX at the same time, in the same copy statement of course!

In the IBM manual “Performance Monitoring and Tuning Guide”, look at the chapter “Real-time Statistics” to see which thresholds IBM uses to determine when a utility should be run – and, of course, make sure you tailor them to suit your site, if you don’t have a set it and forget it solution like our RealTime DBAExpert! Solutions like that also manage the RTS. Orphans build up in the RTS and can cause problems, so every now and again make sure that only databases and tablespaces that actually exist are also in the RTS. Watch out for DSNDB01, Work Databases and the RTS itself, as the first two are *in* the RTS but the RTS is *not* in the RTS.

 

Finally, a quick run through some of my favourite columns in the RTS:

DBNAME & NAME – Remember that the DSNDB01 is in the RTS, but watch out for SYSLGRNX NACTIVE and do not forget that the RTS is not in the RTS, of course!
EXTENTS – This is the number of extents in the *last* linear dataset, not the total over all datasets.
INSTANCE – If using the CLONE system, then each CLONE has two entries in the RTS, so you must always also join to the INSTANCE column in the SYSIBM.SYSTABLESPACE to make sure you are looking at real data and not behind-the-scenes data – also for indexes, of course!
LASTUSED – A great place to begin the “unused index analysis saga”.

And, of course, the new DB2 10 fields, first the TABLESPACE ones:
REORGCLUSTERSENS – The number of times data has been read by SQL statements that are sensitive to the clustering sequence – in other words CLUSTER is important!
REORGSCANACCESS – The number of times data is accessed for SELECT, FETCH, searched UPDATE, or searched DELETE. Great for REORG decisions!
REORGHASHACCESS – The number of times data is accessed using hash access for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. Great for checking whether your usage of HASH Access is bringing dividends or not.
HASHLASTUSED – When it was last actually used. The docu states TIMESTAMP but I always see a DATE column here, just like normal index LASTUSED.
DRIVETYPE – Does this object live on a normal disk (HDD) or on a solid state drive (SSD)? Only for the first created object dataset.
LPFACILITY – Does the disk unit that this object lives on support the new High Performance List Prefetch facilty Y or N?
STATS01 – Some internally used data for IBM

 

And now the INDEX ones:

REORGINDEXACCESS – The number of times the index was used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. Great for REORG decisions as, if this number is high, then a REORG may not bring much in performance terms. Extra bonus data is that for hash overflow indexes, this is the number of times DB2 has used the hash overflow index – which is a *BAD* thing and should be controlled!
DRIVETYPE – Does this object live on a normal disk (HDD) or on a solid state drive (SSD)? Only for the first created object dataset.
STATS101 – Some internally used data for IBM. I just hope it is nothing to do with the Orwellian room 101!
Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

2011-02: XML Table Spaces – How they work and what you must take care of, or „LOBs the Next Generation“

 

XML has become more and more interesting for DB2 over the years.  Starting with DB2 9 it has also become much easier to use!

One of the weird things about XML is that the table spaces come in three (yes three!) different flavors depending on the base table space/table definition. Therefore, they are a bit „trickier“ than a „normal“ table space.

Our newsletter this month covers the relatively straight-forward rules for creating these.

 

Segmented table spaces:

If you create a segmented table space with an XML column type, DB2 will automatically:

  1. Add a column to the base table “DB2_GENERATED_DOC_ID_FOR_XML” BIGINT.
  2. Create an XML Partition by Growth (PBG) universal table space, (the name will be Xyyynnnn where yyy is the first three UTF-8 bytes of the base table space, and nnnn four numeric’s starting with 0000).
  3. Create an XML table in the above space. The name will be Xyyyyyyyyyyyyyyyyyynnn, where the y-y are the first 18 UTF-8 characters of the base table name, if it already exists.  nnn is used as three numeric’s starting at 000 until a unique name is found.
  4. Create a document ID index on the implicit column DB2_GENERATED_DOC_ID_FOR_XML in the base table. The name will be I_DOCIDyyyyyyyyyyyyyyyyyynnn, where the y-y are the first 18 UTF-8 characters of the base table name, if it already exists. nnn is used as three numeric’s starting at 000 until a unique name is found.
  5. Create a node ID index on each XML column in the base table. The name will be I_NODEIDyyyyyyyyyyyyyyyyyynnn, where the y-y are the first 18 UTF-8 characters of the XML table name, if it already exists. nnn is used as three numeric’s starting at 000 until a unique name is found.

 

PBG spaces:

If you create a Partitioned by Growth (PBG) table space with an XML column type, DB2 does the same as above, but with a default DSSIZE of 4GB – also for *all* of the indexes.

 

PBR spaces:

If you create a Partitioned by Range (PBR) table space with an XML column, DB2 will create as many PBR XML table spaces for you with their associated indexes but with a possibly larger DSSIZE.
This can all lead to a spectacular growth in storage space. Imagine a 4096 partition space, which is then altered to have two XML columns…the mind boggles!

 

What does this boil down to?

  • When using XML spaces make sure your back-up and recovery plans work.
  • Coordinate space management proactively.
  • Use COMPRESS wisely. XML table spaces inherit the COMPRESS attribute or can be ALTERed and then REORGed to enable compression. They compress very well.
  • Monitor your 16k Bufferpool usage because this is always used by XML table spaces.
  • Watch out if you copy data from subsystem to subsystem! XML data tends not to copy across too well, (e.g., DSN1COPY does not support it) because the XML descriptive data in the XSR system tables tends to be “forgotten”…

 

New from IBM:

Take a look at IBM’s RedBook „Extremely pureXML in DB2 10 for z/OS, SG24-7915-00“, which is full of great info about the use and maintenance of XML data.

 

Life of an XML column example:

  • Create segmented table space XMLCUSTO.
  • Create table XMLCUSTOMER (columns CID BIGINT and INFO XML).
  • DB2 adds the column DB2_GENERATED_DOC_ID_FOR_XML BIGINT, and it creates table space XXML0000.
  • DB2 creates the XML table XXMLCUSTOMER containing the three columns:
     DOCID         BIGINT
     MIN_NODE_ID   VARBIN(128)    NOT NULL
     XMLDATA       VARBIN(15850)  NOT NULL
  • DB2 creates the unique index I_DOCIDXMLCUSTOMER on the base table with the following column:
    DB2_GENERATED_DOC_ID_FOR_XML

 

  • Finally, DB2 creates the clustering, not padded, non-unique index I_NODEIDXXMLCUSTOMER on the XML table with columns:
     DOCID
     XMLDATA

 

A table INSERT:

INSERT INTO XMLCUSTOMER VALUES (06001, XMLPARSE(DOCUMENT '
 <customerinfo><name>John Smith</name>
 <addr country="United States"><street>327 Smith Ave</street>
 <city>Glendale</city><prov-state>California</prov-state>
 <pcode-zip>91208-06001</pcode-zip></addr>
 <phone type="work">888-111-0001</phone></customerinfo>'));

 

A SELECT:  

SELECT CID
FROM BOXWELL.XMLCUSTOMER
WHERE XMLEXISTS ('//addr[city="San Jose"]' passing INFO)

You will also find useful information about XML basics, XML integration in DB2 9, 10 and XML index design in our presentation entitled „XML Episode 9 – the return of the hierarchical Empire –„.

 

If you would like to sample SQL to create and populate some example XML spaces, please contact us.

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect