Somebody Please give me a Hint!

Edward Long

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

_____________________________________________________________________
* 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

Ford Wong

Re: Somebody Please give me a Hint!
(in response to Edward Long)
Edward,

Verify that table no is correct. Each table in the query is given a number and they have to match. Without seeing what you are doing, It seems simple enough what you are doing. I assume that the index that you want to use has usable columns related to the query.

Ford

----- Original Message -----
From: Ed Long <[login to unmask email]>
Date: Monday, February 7, 2011 3:13 pm
Subject: [DB2-L] Somebody Please give me a Hint!
To: [login to unmask email]

> 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
>
> _____________________________________________________________________
> * 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. *
** 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

Edward Long

Re: Somebody Please give me a Hint!
(in response to Ford Wong)
Thank you for the assist.
I eliminated all changes from the hint attempt, including the desired index
change, and still get the error. There are no missing tables.
The contents of the PLAN_TABLE, except for OPTHINT, are exactly the same as they
were after the first EXPLAIN.

I suspect if I don't update OPTHINT the second explain will work but not do
anything useful.
Edward Long




________________________________
From: Ford Wong <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, February 7, 2011 7:05:48 PM
Subject: Re: [DB2-L] Somebody Please give me a Hint!


Edward,

Verify that table no is correct. Each table in the query is given a number and
they have to match. Without seeing what you are doing, It seems simple
enough what you are doing. I assume that the index that you want to use has
usable columns related to the query.


Ford

----- Original Message -----
From: Ed Long <[login to unmask email]>
Date: Monday, February 7, 2011 3:13 pm
Subject: [DB2-L] Somebody Please give me a Hint!
To: [login to unmask email]

> 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
>
> _____________________________________________________________________
> * 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
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* 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

Mike Bell

Re: Somebody Please give me a Hint!
(in response to Edward Long)
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

Edward Long

Re: Somebody Please give me a Hint!
(in response to Mike Bell)
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. *
** 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