2014-10 QUERYNO – Query what?

Can you Track your SQL Back in time? Do you have standards to catch QUERYNO abuse?

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!

– 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

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