Db2 8.1 maintenance thru nov

Diana Nagel

Db2 8.1 maintenance thru nov
We recently applied maintenance to our db2 v 8.1 on our z\os mainframe - ptfs were thru nov 27, 2007.

According to the user this sql we always runs fine.Generally it is run thru cognos reporting tool....can't get cube rebuilt and seems to have stopped here. I have checked this in visual explain - added all stats it requests, reorged, rebuilt and it still grinds away.
Up until we applied maintenance - apparently this worked fine ...Omegamon just shows that it is sitting on ts polin and index poli4..... .

Also I did not write this and it is going to be rewritten but user wasn't quiet ready to do that...yet.....just wondering why it all stopped now. This apparently was built in qmf for windows,copy - pasted to cognos repting. Whatever maintenance was applied -does not like this now.

My question is has anyone had trouble with db2 v 8.1 and current maintenance application in regards to 3rd party tools running sql or maybe sql/functions relative to views, indexes etc.

SELECT
TRANSACTION_DATE,
VENDOR_CODE,
SUPPLIER,
FACILITY,
AGENT,
EDI_IND,
MPA_IND,
COUNT(DISTINCT PO_KEY),
COUNT(*),
SUM(PO_LINE_COST)
FROM (
SELECT CASE WHEN B.PO_REVISION_NBR = ' ' THEN B.PO_ISSUE_DATE
ELSE PO_ORIG_ISSUE_DATE END AS TRANSACTION_DATE,
B.VENDOR_CODE,
C.VENDOR_SUPPLY_NAME AS SUPPLIER,
A.FACILITY,
B.AGENT,
CASE WHEN EDI_STATUS='Y' AND EDI_OBJECT_MODE = 'E' THEN 'EDI'
ELSE 'NON-EDI' END AS EDI_IND,
B.PURCHASE_ORDER_NBR || B.PO_RELEASE_NBR AS PO_KEY,
CASE WHEN CROSS_REF LIKE '9%' OR CROSS_REF LIKE 'MPA%' OR
CROSS_REF LIKE 'ALL%'
THEN 'MPA' ELSE 'NON-MPA' END AS MPA_IND,
A.PO_LINE_COST
FROM PP.TIDPOLIN A
, PP.TIDPOMST B
, PP.TIDVNMST C
WHERE ((B.PO_REVISION_NBR ¬= ' '
AND PO_ORIG_ISSUE_DATE BETWEEN
STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)|| sUBSTR(CHAR(CURRENT DATE),9,2) )
OR (B.PO_REVISION_NBR = ' '
AND PO_ISSUE_DATE BETWEEN
STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)|| sUBSTR(CHAR(CURRENT DATE),9,2)))
AND B.VENDOR_CODE = C.VENDOR_CODE
AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
AND B.PO_RELEASE_NBR ¬= '00000'
AND B.PO_STATUS IN ('OPEN', 'COMPLETE', 'CLOSED', 'HISTORY'))
X GROUP BY TRANSACTION_DATE,
VENDOR_CODE,
SUPPLIER,
FACILITY,
AGENT,
EDI_IND,
PO_KEY,
MPA_IND


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Shery hepp

Re: Db2 8.1 maintenance thru nov
(in response to Diana Nagel)
Diane, answer to your question- yes we have a case open with IBM
regarding a query in a 3rd party tool that doesn't run in production. At
first we suspected it had to do with maintenance in db2- but then we
found it was actually due to something being different between prod
stats and test stats. For some reason it will only run using the test
stats transferred to prod.



You don't specify if this is production or test, but I'm assuming this
is production.



What I would do is

1. Explain the query and compare the access path between prod (if
that's where it's broke) and test.
2. Verify the access paths are the same.

a. If not you may need to transfer your stats down to
ensure you have the same access path in test as prod.

3. I would then run the query in test to verify if you have the
same behavior as production.
4. I would back out the maintenance from test and run another
explain to compare access paths and run the query again. Verify and
confirm the access path, performance and result set.



If it performs well then you've isolated this to something in the
maintenance. If not then I suggest moving the maintenance back in and
that you check IBMLink to see if there is a problem reported and fix
available. Hope this helps. Good luck- Shery

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Diana Nagel
Sent: Wednesday, January 09, 2008 3:55 PM
To: [login to unmask email]
Subject: [DB2-L] Db2 8.1 maintenance thru nov



We recently applied maintenance to our db2 v 8.1 on our z\os mainframe -
ptfs were thru nov 27, 2007.

According to the user this sql we always runs fine.Generally it is run
thru cognos reporting tool....can't get cube rebuilt and seems to have
stopped here. I have checked this in visual explain - added all stats it
requests, reorged, rebuilt and it still grinds away.

Up until we applied maintenance - apparently this worked fine
...Omegamon just shows that it is sitting on ts polin and index
poli4..... .

Also I did not write this and it is going to be rewritten but user
wasn't quiet ready to do that...yet.....just wondering why it all
stopped now. This apparently was built in qmf for windows,copy - pasted
to cognos repting. Whatever maintenance was applied -does not like this
now.

My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or
maybe sql/functions relative to views, indexes etc.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Carol Anne Sutfin

Re: Db2 8.1 maintenance thru nov
(in response to Shery hepp)
Diane,

I have had some similar problems to this, with 3rd party software.
We use 3rd party LOAD, REORG, etc from those guys that bought Flip's
company. (yep, CA)

There were problems if you replied on the statistics from the LOAD or REORG
to provide enough info for dynamic SQL to run.

Lots of CA PTF's later I have overcome the problem.

My work around was to make sure that a standard IBM RUNSTAT had been done.

Hope this helps

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Diana Nagel
<[login to unmask email]
> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] Db2 8.1 maintenance thru
nov

01/09/2008 04:55
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






We recently applied maintenance to our db2 v 8.1 on our z\os mainframe -
ptfs were thru nov 27, 2007.


According to the user this sql we always runs fine.Generally it is run thru
cognos reporting tool….can't get cube rebuilt and seems to have stopped
here. I have checked this in visual explain - added all stats it requests,
reorged, rebuilt and it still grinds away.


Up until we applied maintenance - apparently this worked fine …Omegamon
just shows that it is sitting on ts polin and index poli4….. .


Also I did not write this and it is going to be rewritten but user wasn't
quiet ready to do that…yet…..just wondering why it all stopped now. This
apparently was built in qmf for windows,copy - pasted to cognos repting.
Whatever maintenance was applied -does not like this now.


My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or maybe
sql/functions relative to views, indexes etc.


SELECT
TRANSACTION_DATE,
VENDOR_CODE,
SUPPLIER,
FACILITY,
AGENT,
EDI_IND,
MPA_IND,
COUNT(DISTINCT PO_KEY),
COUNT(*),
SUM(PO_LINE_COST)
FROM (
SELECT CASE WHEN B.PO_REVISION_NBR = ' ' THEN B.PO_ISSUE_DATE
ELSE PO_ORIG_ISSUE_DATE END AS TRANSACTION_DATE,
B.VENDOR_CODE,
C.VENDOR_SUPPLY_NAME AS SUPPLIER,
A.FACILITY,
B.AGENT,
CASE WHEN EDI_STATUS='Y' AND EDI_OBJECT_MODE = 'E' THEN 'EDI'
ELSE 'NON-EDI' END AS EDI_IND,
B.PURCHASE_ORDER_NBR || B.PO_RELEASE_NBR AS PO_KEY,
CASE WHEN CROSS_REF LIKE '9%' OR CROSS_REF LIKE 'MPA%' OR
CROSS_REF LIKE 'ALL%'
THEN 'MPA' ELSE 'NON-MPA' END AS MPA_IND,
A.PO_LINE_COST
FROM PP.TIDPOLIN A
, PP.TIDPOMST B
, PP.TIDVNMST C
WHERE ((B.PO_REVISION_NBR ¬= ' '
AND PO_ORIG_ISSUE_DATE BETWEEN
STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2) )
OR (B.PO_REVISION_NBR = ' '
AND PO_ISSUE_DATE BETWEEN
STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2)))
AND B.VENDOR_CODE = C.VENDOR_CODE
AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
AND B.PO_RELEASE_NBR ¬= '00000'
AND B.PO_STATUS IN ('OPEN', 'COMPLETE', 'CLOSED', 'HISTORY'))
X GROUP BY TRANSACTION_DATE,
VENDOR_CODE,
SUPPLIER,
FACILITY,
AGENT,
EDI_IND,
PO_KEY,
MPA_IND



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at www.idug.org under the
Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Diana Nagel

Re: Db2 8.1 maintenance thru nov
(in response to Carol Anne Sutfin)
Sherry, thanks for suggestions. This is a dynamic sql and runs the same
in prod or test. Just doesn't matter - just grinds away. Went thru all
stats using visual explain for suggestions all seems pretty reasonable
but the schematic seems quite different then what I have seen in the
past. I have sorts, hybrid joins, merges, semijoin, partition degree of
3, more merges, lots of work files and parallel tasks - its quite the
graphic. In the plan table I have a method 4 - hybrid join which where
it continues to run.

Just not sure what to do ....I am concerned with the maintenance its
caused me more problems then I need right now.

diana

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Wednesday, January 09, 2008 5:59 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Db2 8.1 maintenance thru nov



Diane, answer to your question- yes we have a case open with IBM
regarding a query in a 3rd party tool that doesn't run in production. At
first we suspected it had to do with maintenance in db2- but then we
found it was actually due to something being different between prod
stats and test stats. For some reason it will only run using the test
stats transferred to prod.



You don't specify if this is production or test, but I'm assuming this
is production.



What I would do is

1. Explain the query and compare the access path between prod (if
that's where it's broke) and test.
2. Verify the access paths are the same.

a. If not you may need to transfer your stats down to
ensure you have the same access path in test as prod.

3. I would then run the query in test to verify if you have the
same behavior as production.
4. I would back out the maintenance from test and run another
explain to compare access paths and run the query again. Verify and
confirm the access path, performance and result set.



If it performs well then you've isolated this to something in the
maintenance. If not then I suggest moving the maintenance back in and
that you check IBMLink to see if there is a problem reported and fix
available. Hope this helps. Good luck- Shery

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Diana Nagel
Sent: Wednesday, January 09, 2008 3:55 PM
To: [login to unmask email]
Subject: [DB2-L] Db2 8.1 maintenance thru nov



We recently applied maintenance to our db2 v 8.1 on our z\os mainframe -
ptfs were thru nov 27, 2007.

According to the user this sql we always runs fine.Generally it is run
thru cognos reporting tool....can't get cube rebuilt and seems to have
stopped here. I have checked this in visual explain - added all stats it
requests, reorged, rebuilt and it still grinds away.

Up until we applied maintenance - apparently this worked fine
...Omegamon just shows that it is sitting on ts polin and index
poli4..... .

Also I did not write this and it is going to be rewritten but user
wasn't quiet ready to do that...yet.....just wondering why it all
stopped now. This apparently was built in qmf for windows,copy - pasted
to cognos repting. Whatever maintenance was applied -does not like this
now.

My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or
maybe sql/functions relative to views, indexes etc.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Gannon

Re: Db2 8.1 maintenance thru nov
(in response to Diana Nagel)
Diana,



I also work in an environment like the one you have describe - Cognos
with DB2 V8 & Omegamon - I was beginning to feel we were the only site
using this combination .... Feel free to contact me off-list, maybe we
can set up some kind of Mental Support group ;-)



In a lot of the cases where I have see the Hybrid join used in our
queries is in places where you are generating a join of Many to Many -
In the area where this join type is being used has your data changed ???





William B. Gannon

- IBM Certified Solutions Expert

- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Diana Nagel
Sent: Wednesday, January 09, 2008 9:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Db2 8.1 maintenance thru nov



Sherry, thanks for suggestions. This is a dynamic sql and runs the same
in prod or test. Just doesn't matter - just grinds away. Went thru all
stats using visual explain for suggestions all seems pretty reasonable
but the schematic seems quite different then what I have seen in the
past. I have sorts, hybrid joins, merges, semijoin, partition degree of
3, more merges, lots of work files and parallel tasks - its quite the
graphic. In the plan table I have a method 4 - hybrid join which where
it continues to run.



Just not sure what to do ....I am concerned with the maintenance its
caused me more problems then I need right now.



diana



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Wednesday, January 09, 2008 5:59 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Db2 8.1 maintenance thru nov

Diane, answer to your question- yes we have a case open with IBM
regarding a query in a 3rd party tool that doesn't run in production. At
first we suspected it had to do with maintenance in db2- but then we
found it was actually due to something being different between prod
stats and test stats. For some reason it will only run using the test
stats transferred to prod.



You don't specify if this is production or test, but I'm assuming this
is production.



What I would do is

1. Explain the query and compare the access path between prod (if
that's where it's broke) and test.
2. Verify the access paths are the same.

a. If not you may need to transfer your stats down to
ensure you have the same access path in test as prod.

3. I would then run the query in test to verify if you have the
same behavior as production.
4. I would back out the maintenance from test and run another
explain to compare access paths and run the query again. Verify and
confirm the access path, performance and result set.



If it performs well then you've isolated this to something in the
maintenance. If not then I suggest moving the maintenance back in and
that you check IBMLink to see if there is a problem reported and fix
available. Hope this helps. Good luck- Shery

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Diana Nagel
Sent: Wednesday, January 09, 2008 3:55 PM
To: [login to unmask email]
Subject: [DB2-L] Db2 8.1 maintenance thru nov



We recently applied maintenance to our db2 v 8.1 on our z\os mainframe -
ptfs were thru nov 27, 2007.

According to the user this sql we always runs fine.Generally it is run
thru cognos reporting tool....can't get cube rebuilt and seems to have
stopped here. I have checked this in visual explain - added all stats it
requests, reorged, rebuilt and it still grinds away.

Up until we applied maintenance - apparently this worked fine
....Omegamon just shows that it is sitting on ts polin and index
poli4..... .

Also I did not write this and it is going to be rewritten but user
wasn't quiet ready to do that...yet.....just wondering why it all
stopped now. This apparently was built in qmf for windows,copy - pasted
to cognos repting. Whatever maintenance was applied -does not like this
now.

My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or
maybe sql/functions relative to views, indexes etc.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services



-----------------------------------------
Under Florida law, e-mail addresses are public records. If you do
not want your e-mail address released in response to a public
records request, do not send electronic mail to this entity.
Instead, contact this office by phone or in writing.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: Db2 8.1 maintenance thru nov
(in response to William Gannon)
A few comments from my view point as a Systems Programmer but by no
means a SQL coding expert

">According to the user this sql we always runs fine"
Statements of this type, the classic being, "It use to work" are generally
speaking not considered relyable.

COUNT(DISTINCT PO_KEY),
This requires an additional sort for each unique string of values in the Group
By that contains 6 column names from two diffrent tables.
There is also and additional COUNT and an additional SUM in the resulting
columns that will have to be resolved for each value string in the GROUP BY.

FROM PP.TIDPOLIN A
> , PP.TIDPOMST B
> , PP.TIDVNMST C
Three tables participating in the join and posibly many indexs as we will see
later, some times not an indicator of good performance

>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
>SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2) )
Strip and SUBSTR tend not to perform well in WHERE clauses, As I recall the
term is Stage II predicates.

>AND B.VENDOR_CODE = C.VENDOR_CODE
>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
The B <->C join criteria
> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
> AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
> AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
The A<->B Joint criteria
Are both of these sets supported by a single index in each table
Note in this best of possible worlds 4 indexs participating
2 from B and one each from A and C
There is an old recomendation that this sort of clause should be at the
begining of the WHERE clause.

Is every column in the form col = value referenced in the where clause
supported by an index where the column in question is the high level column in
the index? Lots of columns here means lots of indexs and or lots of scans


My gut tells me
1) no reason to belive that this ever ran fast
2) some key data missing from the report like
Why is this SQL on the block to be rewritten even before the latest service
perhaps it was not performing very well
3) Bad performance in just this querry coupled with fears that the bad
performance might spread is justified but hardly a reson to conclude it is
service related
4) What are the columns in POLIL4
5) A serch on IBM Link using keywords "PERFM DB2" and last updated after
12/01/07 returned 77 hits, several new under development APARs that seem
to talk about problems with second columns in a index statistics group
6) "ptfs were thru nov 27, 2007" is sort of a weird statement. As I recall
monthly service comes out the first business day of the month so the DEC
tape comes out on or shortly after DEC 1. Are you saying you applied servce
to the Nov tape plus selected hipers minus unresolved PEs or what? Is it your
normal practice to be one month behind bleeding edge in production. Some
shops would consider this to be VERY agressive.

Best Wishes
Avram Friedman








On Wed, 9 Jan 2008 16:55:15 -0600, Diana Nagel <[login to unmask email]>
wrote:

>We recently applied maintenance to our db2 v 8.1 on our z\os mainframe -
ptfs were thru nov 27, 2007.
>
>According to the user this sql we always runs fine.Generally it is run thru
cognos reporting tool....can't get cube rebuilt and seems to have stopped
here. I have checked this in visual explain - added all stats it requests,
reorged, rebuilt and it still grinds away.
>Up until we applied maintenance - apparently this worked fine ...Omegamon
just shows that it is sitting on ts polin and index poli4..... .
>
>Also I did not write this and it is going to be rewritten but user wasn't quiet
ready to do that...yet.....just wondering why it all stopped now. This
apparently was built in qmf for windows,copy - pasted to cognos repting.
Whatever maintenance was applied -does not like this now.
>
>My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or maybe
sql/functions relative to views, indexes etc.
>
>SELECT
> TRANSACTION_DATE,
> VENDOR_CODE,
> SUPPLIER,
> FACILITY,
> AGENT,
> EDI_IND,
> MPA_IND,
> COUNT(DISTINCT PO_KEY),
> COUNT(*),
> SUM(PO_LINE_COST)
>FROM (
> SELECT CASE WHEN B.PO_REVISION_NBR = ' ' THEN B.PO_ISSUE_DATE
> ELSE PO_ORIG_ISSUE_DATE END AS TRANSACTION_DATE,
> B.VENDOR_CODE,
> C.VENDOR_SUPPLY_NAME AS SUPPLIER,
> A.FACILITY,
> B.AGENT,
> CASE WHEN EDI_STATUS='Y' AND EDI_OBJECT_MODE = 'E' THEN 'EDI'
> ELSE 'NON-EDI' END AS EDI_IND,
> B.PURCHASE_ORDER_NBR || B.PO_RELEASE_NBR AS PO_KEY,
> CASE WHEN CROSS_REF LIKE '9%' OR CROSS_REF LIKE 'MPA%' OR
> CROSS_REF LIKE 'ALL%'
> THEN 'MPA' ELSE 'NON-MPA' END AS MPA_IND,
> A.PO_LINE_COST
> FROM PP.TIDPOLIN A
> , PP.TIDPOMST B
> , PP.TIDVNMST C
>WHERE ((B.PO_REVISION_NBR ?= ' '
>AND PO_ORIG_ISSUE_DATE BETWEEN
>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
>SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2) )
> OR (B.PO_REVISION_NBR = ' '
>AND PO_ISSUE_DATE BETWEEN
>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and
>SUBSTR(CHAR(CURRENT DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2)))
>AND B.VENDOR_CODE = C.VENDOR_CODE
>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
> AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
> AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
> AND B.PO_RELEASE_NBR ?= '00000'
> AND B.PO_STATUS IN ('OPEN', 'COMPLETE', 'CLOSED', 'HISTORY'))
> X GROUP BY TRANSACTION_DATE,
> VENDOR_CODE,
> SUPPLIER,
> FACILITY,
> AGENT,
> EDI_IND,
> PO_KEY,
> MPA_IND

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Diana Nagel

Re: Db2 8.1 maintenance thru nov
(in response to Avram Friedman)
Thanks for input, I will review asap...but yes we are aggressive in
maintenance as our initial intend was to start on db2 9 but that has
been delayed.

Here is our index poli4
PURCHASE_ORDER_NBR 1 A
PO_LINE_STATUS 2 A
PO_RELEASE_NBR 3 A

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
Sent: Thursday, January 10, 2008 8:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Db2 8.1 maintenance thru nov

A few comments from my view point as a Systems Programmer but by no
means a SQL coding expert

">According to the user this sql we always runs fine"
Statements of this type, the classic being, "It use to work" are
generally speaking not considered relyable.

COUNT(DISTINCT PO_KEY),
This requires an additional sort for each unique string of values in the
Group By that contains 6 column names from two diffrent tables.
There is also and additional COUNT and an additional SUM in the
resulting columns that will have to be resolved for each value string in
the GROUP BY.

FROM PP.TIDPOLIN A
> , PP.TIDPOMST B
> , PP.TIDVNMST C
Three tables participating in the join and posibly many indexs as we
will see later, some times not an indicator of good performance

>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR(CURRENT
>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2) )
Strip and SUBSTR tend not to perform well in WHERE clauses, As I recall
the term is Stage II predicates.

>AND B.VENDOR_CODE = C.VENDOR_CODE
>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
The B <->C join criteria
> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR AND
> A.PO_REVISION_NBR = B.PO_REVISION_NBR AND A.PO_RELEASE_NBR =
> B.PO_RELEASE_NBR
The A<->B Joint criteria
Are both of these sets supported by a single index in each table Note in
this best of possible worlds 4 indexs participating
2 from B and one each from A and C
There is an old recomendation that this sort of clause should be at the
begining of the WHERE clause.

Is every column in the form col = value referenced in the where clause
supported by an index where the column in question is the high level
column in the index? Lots of columns here means lots of indexs and or
lots of scans


My gut tells me
1) no reason to belive that this ever ran fast
2) some key data missing from the report like
Why is this SQL on the block to be rewritten even before the latest
service
perhaps it was not performing very well
3) Bad performance in just this querry coupled with fears that the bad
performance might spread is justified but hardly a reson to conclude it
is service related
4) What are the columns in POLIL4
5) A serch on IBM Link using keywords "PERFM DB2" and last updated after
12/01/07 returned 77 hits, several new under development APARs that
seem to talk about problems with second columns in a index statistics
group
6) "ptfs were thru nov 27, 2007" is sort of a weird statement. As I
recall monthly service comes out the first business day of the month so
the DEC tape comes out on or shortly after DEC 1. Are you saying you
applied servce to the Nov tape plus selected hipers minus unresolved PEs
or what? Is it your normal practice to be one month behind bleeding
edge in production. Some shops would consider this to be VERY agressive.

Best Wishes
Avram Friedman








On Wed, 9 Jan 2008 16:55:15 -0600, Diana Nagel <[login to unmask email]>
wrote:

>We recently applied maintenance to our db2 v 8.1 on our z\os mainframe
>-
ptfs were thru nov 27, 2007.
>
>According to the user this sql we always runs fine.Generally it is run
>thru
cognos reporting tool....can't get cube rebuilt and seems to have
stopped here. I have checked this in visual explain - added all stats it
requests, reorged, rebuilt and it still grinds away.
>Up until we applied maintenance - apparently this worked fine
>...Omegamon
just shows that it is sitting on ts polin and index poli4..... .
>
>Also I did not write this and it is going to be rewritten but user
>wasn't quiet
ready to do that...yet.....just wondering why it all stopped now. This
apparently was built in qmf for windows,copy - pasted to cognos repting.

Whatever maintenance was applied -does not like this now.
>
>My question is has anyone had trouble with db2 v 8.1 and current
maintenance application in regards to 3rd party tools running sql or
maybe sql/functions relative to views, indexes etc.
>
>SELECT
> TRANSACTION_DATE,
> VENDOR_CODE,
> SUPPLIER,
> FACILITY,
> AGENT,
> EDI_IND,
> MPA_IND,
> COUNT(DISTINCT PO_KEY),
> COUNT(*),
> SUM(PO_LINE_COST)
>FROM (
> SELECT CASE WHEN B.PO_REVISION_NBR = ' ' THEN B.PO_ISSUE_DATE
> ELSE PO_ORIG_ISSUE_DATE END AS TRANSACTION_DATE,
> B.VENDOR_CODE,
> C.VENDOR_SUPPLY_NAME AS SUPPLIER,
> A.FACILITY,
> B.AGENT,
> CASE WHEN EDI_STATUS='Y' AND EDI_OBJECT_MODE = 'E' THEN 'EDI'
> ELSE 'NON-EDI' END AS EDI_IND,
> B.PURCHASE_ORDER_NBR || B.PO_RELEASE_NBR AS PO_KEY,
> CASE WHEN CROSS_REF LIKE '9%' OR CROSS_REF LIKE 'MPA%' OR
> CROSS_REF LIKE 'ALL%'
> THEN 'MPA' ELSE 'NON-MPA' END AS MPA_IND,
> A.PO_LINE_COST
> FROM PP.TIDPOLIN A
> , PP.TIDPOMST B
> , PP.TIDVNMST C
>WHERE ((B.PO_REVISION_NBR ?= ' '
>AND PO_ORIG_ISSUE_DATE BETWEEN
>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR(CURRENT
>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2) )
> OR (B.PO_REVISION_NBR = ' '
>AND PO_ISSUE_DATE BETWEEN
>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR(CURRENT
>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
sUBSTR(CHAR(CURRENT DATE),9,2)))
>AND B.VENDOR_CODE = C.VENDOR_CODE
>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
> AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
> AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
> AND B.PO_RELEASE_NBR ?= '00000'
> AND B.PO_STATUS IN ('OPEN', 'COMPLETE', 'CLOSED', 'HISTORY'))
> X GROUP BY TRANSACTION_DATE,
> VENDOR_CODE,
> SUPPLIER,
> FACILITY,
> AGENT,
> EDI_IND,
> PO_KEY,
> MPA_IND

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: Db2 8.1 maintenance thru nov
(in response to Diana Nagel)
Hi Diane
From what you reported from Omegamon and the information you provided
about the index this is what I think is going on

The poli4 index is used for the Table a to Table b join

>> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR AND
>> A.PO_REVISION_NBR = B.PO_REVISION_NBR AND A.PO_RELEASE_NBR =
>> B.PO_RELEASE_NBR

The index contains 2 matching columns for the join (you should check the
explain to make sure it says 2 and not 1 matching columns ... 1 may mean a
RUNSTATS issue) These two columns are the first and third column in the
index.

So having a match on the first column PURCHASE_ORDER_NBR DB2 may scan
through the index looking for matches on the third column PO_RELEASE_NBR
This results of course in extra index pageset I/Os and/or buggerpool get pages.

The other join criteria PO_REVISION_NBR needs to be resolved by retriving
the actual data rows identified by the index. In addition you have a number
of conditions in the where clause that are not JOIN related that is they are
not a.col = b.col but a.col = value these would be resolved by looking at
data pages rather than index pages as well.

I am guessing that the poli4 index and the poli table space match the b. table
in your SQL as most of the ?.col = value references are for b.

The idea behind the way OMEGAMON displays delay information is to provide
some guidence on which issue to attack. What you are seing is use of the
poli4 index and table space. If it is worth adding an additional index I would
suggest one with the 3 columns used in the a-b table join. If the other table
has such an index (and I guess it does) the order of the columns in the index
should be the same as the other table. This would reduce the amount of
index access and to a lesser extent the amout of base table access.

A different choice would be to evaluate the current use of poli4 ... are you
getting any 'bang for the buck' by having PO_LINE_STATUS in the index?
No replace it with PO_REVISION_NBR
a little: the same as new by leave PO_LINE_STATUS to the end of the column
list
a Lot add PO_REVISION_NBR to the end of the list

Any index change would also require the appropate RUNSTATS and REBINDs to
be effective. If one takes the defaults in RUNSTATS in many cases the
optomizer only considers the first index column.

Best Wishes
Avram Friedman


On Thu, 10 Jan 2008 11:14:42 -0600, Diana Nagel <[login to unmask email]>
wrote:

>Thanks for input, I will review asap...but yes we are aggressive in
>maintenance as our initial intend was to start on db2 9 but that has
>been delayed.
>
>Here is our index poli4
>PURCHASE_ORDER_NBR 1 A
>PO_LINE_STATUS 2 A
>PO_RELEASE_NBR 3 A
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>Behalf Of Avram Friedman
>Sent: Thursday, January 10, 2008 8:36 AM
>To: [login to unmask email]
>Subject: Re: [DB2-L] Db2 8.1 maintenance thru nov
>
>A few comments from my view point as a Systems Programmer but by no
>means a SQL coding expert
>
> ">According to the user this sql we always runs fine"
>Statements of this type, the classic being, "It use to work" are
>generally speaking not considered relyable.
>
>COUNT(DISTINCT PO_KEY),
>This requires an additional sort for each unique string of values in the
>Group By that contains 6 column names from two diffrent tables.
>There is also and additional COUNT and an additional SUM in the
>resulting columns that will have to be resolved for each value string in
>the GROUP BY.
>
> FROM PP.TIDPOLIN A
>> , PP.TIDPOMST B
>> , PP.TIDVNMST C
>Three tables participating in the join and posibly many indexs as we
>will see later, some times not an indicator of good performance
>
>>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR
(CURRENT
>>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
>sUBSTR(CHAR(CURRENT DATE),9,2) )
>Strip and SUBSTR tend not to perform well in WHERE clauses, As I recall
>the term is Stage II predicates.
>
>>AND B.VENDOR_CODE = C.VENDOR_CODE
>>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
>The B <->C join criteria
>> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR AND
>> A.PO_REVISION_NBR = B.PO_REVISION_NBR AND A.PO_RELEASE_NBR =
>> B.PO_RELEASE_NBR
>The A<->B Joint criteria
>Are both of these sets supported by a single index in each table Note in
>this best of possible worlds 4 indexs participating
>2 from B and one each from A and C
>There is an old recomendation that this sort of clause should be at the
>begining of the WHERE clause.
>
>Is every column in the form col = value referenced in the where clause
>supported by an index where the column in question is the high level
>column in the index? Lots of columns here means lots of indexs and or
>lots of scans
>
>
>My gut tells me
>1) no reason to belive that this ever ran fast
>2) some key data missing from the report like
> Why is this SQL on the block to be rewritten even before the latest
>service
> perhaps it was not performing very well
>3) Bad performance in just this querry coupled with fears that the bad
>performance might spread is justified but hardly a reson to conclude it
>is service related
>4) What are the columns in POLIL4
>5) A serch on IBM Link using keywords "PERFM DB2" and last updated after
>12/01/07 returned 77 hits, several new under development APARs that
>seem to talk about problems with second columns in a index statistics
>group
>6) "ptfs were thru nov 27, 2007" is sort of a weird statement. As I
>recall monthly service comes out the first business day of the month so
>the DEC tape comes out on or shortly after DEC 1. Are you saying you
>applied servce to the Nov tape plus selected hipers minus unresolved PEs
>or what? Is it your normal practice to be one month behind bleeding
>edge in production. Some shops would consider this to be VERY agressive.
>
>Best Wishes
>Avram Friedman
>
>
>
>
>
>
>
>
>On Wed, 9 Jan 2008 16:55:15 -0600, Diana Nagel <[login to unmask email]>
>wrote:
>
>>We recently applied maintenance to our db2 v 8.1 on our z\os mainframe
>>-
>ptfs were thru nov 27, 2007.
>>
>>According to the user this sql we always runs fine.Generally it is run
>>thru
>cognos reporting tool....can't get cube rebuilt and seems to have
>stopped here. I have checked this in visual explain - added all stats it
>requests, reorged, rebuilt and it still grinds away.
>>Up until we applied maintenance - apparently this worked fine
>>...Omegamon
>just shows that it is sitting on ts polin and index poli4..... .
>>
>>Also I did not write this and it is going to be rewritten but user
>>wasn't quiet
>ready to do that...yet.....just wondering why it all stopped now. This
>apparently was built in qmf for windows,copy - pasted to cognos repting.
>
>Whatever maintenance was applied -does not like this now.
>>
>>My question is has anyone had trouble with db2 v 8.1 and current
>maintenance application in regards to 3rd party tools running sql or
>maybe sql/functions relative to views, indexes etc.
>>
>>SELECT
>> TRANSACTION_DATE,
>> VENDOR_CODE,
>> SUPPLIER,
>> FACILITY,
>> AGENT,
>> EDI_IND,
>> MPA_IND,
>> COUNT(DISTINCT PO_KEY),
>> COUNT(*),
>> SUM(PO_LINE_COST)
>>FROM (
>> SELECT CASE WHEN B.PO_REVISION_NBR = ' ' THEN B.PO_ISSUE_DATE
>> ELSE PO_ORIG_ISSUE_DATE END AS TRANSACTION_DATE,
>> B.VENDOR_CODE,
>> C.VENDOR_SUPPLY_NAME AS SUPPLIER,
>> A.FACILITY,
>> B.AGENT,
>> CASE WHEN EDI_STATUS='Y' AND EDI_OBJECT_MODE = 'E' THEN 'EDI'
>> ELSE 'NON-EDI' END AS EDI_IND,
>> B.PURCHASE_ORDER_NBR || B.PO_RELEASE_NBR AS PO_KEY,
>> CASE WHEN CROSS_REF LIKE '9%' OR CROSS_REF LIKE 'MPA%' OR
>> CROSS_REF LIKE 'ALL%'
>> THEN 'MPA' ELSE 'NON-MPA' END AS MPA_IND,
>> A.PO_LINE_COST
>> FROM PP.TIDPOLIN A
>> , PP.TIDPOMST B
>> , PP.TIDVNMST C
>>WHERE ((B.PO_REVISION_NBR ?= ' '
>>AND PO_ORIG_ISSUE_DATE BETWEEN
>>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR
(CURRENT
>>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
>sUBSTR(CHAR(CURRENT DATE),9,2) )
>> OR (B.PO_REVISION_NBR = ' '
>>AND PO_ISSUE_DATE BETWEEN
>>STRIP(CHAR(YEAR(CURRENT DATE)-4)) || '0101' and SUBSTR(CHAR
(CURRENT
>>DATE),1,4)|| SUBSTR(CHAR(CURRENT DATE),6,2)||
>sUBSTR(CHAR(CURRENT DATE),9,2)))
>>AND B.VENDOR_CODE = C.VENDOR_CODE
>>AND B.VENDOR_SUFFIX = C.VENDOR_SUFFIX
>> AND A.PURCHASE_ORDER_NBR = B.PURCHASE_ORDER_NBR
>> AND A.PO_REVISION_NBR = B.PO_REVISION_NBR
>> AND A.PO_RELEASE_NBR = B.PO_RELEASE_NBR
>> AND B.PO_RELEASE_NBR ?= '00000'
>> AND B.PO_STATUS IN ('OPEN', 'COMPLETE', 'CLOSED', 'HISTORY'))
>> X GROUP BY TRANSACTION_DATE,
>> VENDOR_CODE,
>> SUPPLIER,
>> FACILITY,
>> AGENT,
>> EDI_IND,
>> PO_KEY,
>> MPA_IND

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms