[DB2-L] Somebody Please give me a Hint!

Walter Janißen

[DB2-L] Somebody Please give me a Hint!
Hi Ed

I don't know, if your explain produced more than one query block and maybe all plan table rows belonging to a query must have the same opthint. Did you try that?

I tried, what you described. For 1 query (SELECT * FROM SYSIBM.SYSDUMMY1) it works and for one it doesn't meaning neither +394 nor +395, but SQLCODE 0. But always the OPTHINT-column in the new rows is blank.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Ed Long
Gesendet: Dienstag, 8. Februar 2011 01:53
An: [login to unmask email]
Betreff: Re: [DB2-L] Somebody Please give me a Hint!

Thank you for the assistance. I love the adverb eventually!
1: I am setting SQLID to the schema name which also contains the PLAN_TABLE which is UNICODE and validated by OSC. All objects are owned by the same ID.
2: This is dynamic SQL all done in one jcl stream. (see below for pseudocode). It is DSNTEP4.
3: Table 46 in Section 3.1.11.13 of DB2 9.1 z/os Performance Monitoring and Tuning Guide shows the complete list of edits that DB2 admits to applying. I've written a query that shows that my humble little configuration passes them all.
4: If I modify OPTHINT I get the error; if I don't the second explain runs fine.

Here is the pseudo code. I took as a guide an example in the same section of the tuning guide.
1: Delete ALL related rows from PLAN_TABLE.
2: COMMIT.
3: EXPLAIN. (Select contains queryno clause).
4: COMMIT.
5: Select Rows just created.
6: Implicit Commit from change of Job Step.
7: Update Plan_table where QUERYNO = 30001 and QBLOCKNO=1 and accessname = 'BULKPROCESSFROMBASKET' AND TNAME = 'PC_ASSIGN_WORKBASKET'. Only column updated is OPTHINT; Set to ALTIDX.
8:COMMIT.
9: Set current OPTIMIZATION HINT = 'ALTIDX'.
10:Rexplain (+395 RC=26).
11: Select from PLAN_TABLE (Shows OPTHINT SET TO ALTIDX).


________________________________
From: Mike Bell <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, February 7, 2011 7:40:04 PM
Subject: Re: [DB2-L] Somebody Please give me a Hint!

Start from the basics -
1. plan_table is the only one required.
2. the plan_table has to have the same creator as the owner value in the
BIND
3. if you don't specify the owner in the bind, then it defaults the userid
that submitted the bind or in TSO issued the bind.

In v9, the plan_table can now be an alias to another actual plan_table.
In V9, the plan_table can be either EBCDIC or unicode.
The spufi logic is actually a little more complicated and you usually have
to play with set current sqlid and set current schema to get it to work but
it does eventually.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]>] On Behalf Of Ed Long
Sent: Monday, February 07, 2011 4:13 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] Somebody Please give me a Hint!

So I'm trying to get a simple hint to work on DB2 z/OS v9. Simple, because
all I want to do is swap one index with another.
I've even tried setting up a dummy where all I do is issue the explain,
update the opthint column and re-explain.
BOOM.
+395 Reason Code 26 Table missing on the Explain.
The key point here is that there is nothing wrong with the content of the
PLAN_TABLE since DB2 put it all there.
Any suggestions. PMR already open.


Edward Long

________________________________


Introducing IBMR DB2R 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Edward Long

Re: Somebody Please give me a Hint!
(in response to Walter Janißen)
Two steps forward and one step back.
To activate a hint requires modifying the OPTHINT column in the PLAN_TABLE for all rows related to the query and then, in a separate update, make your actual changes. IBM had to read the DB2 code to determine that from the error message.
That is the good news; when I try to run the query with the hint active DSNTEP4 returns 0 rows and returns the +394 and then a -519.
More adventures to come.

Edward Long

--- On Mon, 2/7/11, Ed Long <[login to unmask email]> wrote:

From: Ed Long <[login to unmask email]>
Subject: Re: [DB2-L] Somebody Please give me a Hint!
To: "IDUG DB2-L" <[login to unmask email]>
Date: Monday, February 7, 2011, 7:52 PM

Thank you for the assistance. I love the adverb eventually!
1: I am setting SQLID to the schema name which also contains the PLAN_TABLE which is UNICODE and validated by OSC. All objects are owned by the same ID.
2: This is dynamic SQL all done in one jcl stream. (see below for pseudocode). It is DSNTEP4.
3: Table 46 in Section 3.1.11.13 of DB2 9.1 z/os Performance Monitoring and Tuning Guide shows the complete list of edits that DB2 admits to applying. I've written a query that shows that my humble little configuration passes them all.
4: If I modify OPTHINT I get the error; if I don't the second explain runs fine.

Here is the pseudo code. I took as a guide an example in the same section of the tuning guide.
1: Delete ALL related rows from PLAN_TABLE.
2: COMMIT.
3:
EXPLAIN. (Select contains queryno clause).
4: COMMIT.
5: Select Rows just created.
6: Implicit Commit from change of Job Step.
7: Update Plan_table where QUERYNO = 30001 and QBLOCKNO=1 and accessname
= 'BULKPROCESSFROMBASKET' AND TNAME = 'PC_ASSIGN_WORKBASKET'. Only
column updated is OPTHINT; Set to ALTIDX.
8:COMMIT.

9: Set current OPTIMIZATION HINT = 'ALTIDX'.
10:Rexplain (+395 RC=26).

11: Select from PLAN_TABLE (Shows OPTHINT SET TO ALTIDX).


From: Mike Bell <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, February 7, 2011 7:40:04 PM
Subject: Re: [DB2-L] Somebody Please give me a Hint!


Start from the basics -
1. plan_table is the only one required.
2. the plan_table has to have the same creator as the owner value in the
BIND
3. if you don't specify the owner in the bind, then it defaults the userid
that submitted the bind or in TSO issued the bind.

In v9, the plan_table can now be an alias to another actual plan_table.
In V9, the plan_table can be either EBCDIC or unicode. 
The spufi logic is actually a little more complicated and you usually have
to play with set current sqlid and set current schema to get it to work but
it does eventually.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Monday, February 07, 2011 4:13 PM
To: [login to unmask email]
Subject: [DB2-L] Somebody Please give me a Hint!

So I'm trying to get a simple hint to work on DB2 z/OS v9. Simple, because
all I want to do is swap one index with another.
I've even tried setting up a dummy where all I do is issue the explain,
update the opthint column and re-explain.
BOOM.
+395 Reason Code 26 Table missing on the Explain.
The key point here is that there is nothing wrong with the content of the
PLAN_TABLE since DB2 put it all there.
Any suggestions. PMR already open.


Edward Long

________________________________


Introducing IBMR DB2R 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member,
please register here.
< http://www.idug.org/register >  

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA *
*  Your only source for independent, unbiased, and trusted DB2 information.  *
**    DB2 certification -> no additional charge
**    Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv



_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv