ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE

William Proctor

ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE
Thanks for all replies in advance. We had to do an emergency compile in
production to fix a problem but the recompile and bind cause one of the
sql statements to choose a table space scan. The old access path was
thru an index. No changes were made to the sql statement at all.
Statistics are up to date and look good for choosing an index. We have
a hundred percent test system that the same sql statement is choosing an
index on. I cannot see any reason for the tablespace scan. Can someone
give me suggestions on what to look for? How can I get it to go back to
the index access?



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847






---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE
(in response to William Proctor)
The most likely cause of the problem is a change in the host variable that
is used for the SQL. V7 won't use an index if the data type doesn't match
exactly.

If the data type is correct then you can use a hint to get back to the old
access path. Find or build a plan_table row that has the correct access
path, change the queryno to match the current compile/DBRM and set column
opthint to a value and then bind with that hintid. This does require that
you have DSNZPARM specified to allow hints. Not all shops consider hints to
be safe. They are not a long term solution.
I would suggest a separate plan_table to keep the hint in, and do your
editing and then insert into prod.plan_table select * from hint.plan_table.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Proctor, William
Sent: Saturday, December 10, 2005 3:39 AM
To: [login to unmask email]
Subject: [DB2-L] ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE

Thanks for all replies in advance. We had to do an emergency compile in
production to fix a problem but the recompile and bind cause one of the sql
statements to choose a table space scan. The old access path was thru an
index. No changes were made to the sql statement at all. Statistics are up
to date and look good for choosing an index. We have a hundred percent test
system that the same sql statement is choosing an index on. I cannot see
any reason for the tablespace scan. Can someone give me suggestions on what
to look for? How can I get it to go back to the index access?



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847





----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE
(in response to Mike Bell)
This looks like a good example to use V8 Visual Explain (VE) if it's not
the host variable mismatch. Yes, V8 VE works on V7 subsystems.
http://www.ibm.com/software/data/db2/zos/osc/ve/

ftp://ftp.software.ibm.com/software/data/db2zos/Z32.pdf

Roger Miller

On Sat, 10 Dec 2005 09:38:59 -0600, Mike Bell <[login to unmask email]>
wrote:

>The most likely cause of the problem is a change in the host variable that
>is used for the SQL. V7 won't use an index if the data type doesn't match
>exactly.
>
>If the data type is correct then you can use a hint to get back to the old
>access path. Find or build a plan_table row that has the correct access
>path, change the queryno to match the current compile/DBRM and set column
>opthint to a value and then bind with that hintid. This does require that
>you have DSNZPARM specified to allow hints. Not all shops consider hints
to
>be safe. They are not a long term solution.
>I would suggest a separate plan_table to keep the hint in, and do your
>editing and then insert into prod.plan_table select * from
hint.plan_table.
>
>Mike Bell
>HLS Technologies
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
>Of Proctor, William
>Sent: Saturday, December 10, 2005 3:39 AM
>To: [login to unmask email]
>Subject: [DB2-L] ZOS 1.6 & DB2 V7.1 ACCESS PATH CHANGE
>
>Thanks for all replies in advance. We had to do an emergency compile in
>production to fix a problem but the recompile and bind cause one of the
sql
>statements to choose a table space scan. The old access path was thru an
>index. No changes were made to the sql statement at all. Statistics are
up
>to date and look good for choosing an index. We have a hundred percent
test
>system that the same sql statement is choosing an index on. I cannot see
>any reason for the tablespace scan. Can someone give me suggestions on
what
>to look for? How can I get it to go back to the index access?
>
>
>
>Bill Proctor
>
>Database Administrator (Adabas/DB2)
>
>DB2 UDB V7.1 Database Administration for OS/390
>
>Texas Guaranteed Student Loan Corp.
>
>Austin, Texas
>
>Phone: 512-219-4847
>
>
>--------------------------------------------------------------------------
--

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm