[V8 CM z/os 1.7] Different SQL Results from V7 to V8

Michael Woodruff

[V8 CM z/os 1.7] Different SQL Results from V7 to V8
We recently upgraded to V8 and aside from performance issues, after a couple
of days our application development team ran across a problem with some SQL
that is run from a Peoplesoft Application. Some background, this is a 3
year proposal, so you will see that we are asking for the first year data,
but in V8 it seems we get the first year data repeated for the number of
years of the proposal. On a 5 year proposal, the first year data is
repeated 5 times, etc....

Here is the SQL:

---------------------------------------------------------------------------
SELECT PROPOSAL_ID, PROJECT_YEAR, BGT_PER_SEQ, DETAIL_LINE_SEQ_3,
AGY_SALARY_AMT, AGY_FICA_AMT, AGY_ANNUITY_AMT
FROM dbaprod.RSV_BU_PER_COMP A
WHERE EXISTS (SELECT 'X' FROM dbaprod.RSV_BU_PERSNL B
Where PROPOSAL_ID = '087847' and PROJECT_YEAR = '01'
AND
A.PROPOSAL_ID = B.PROPOSAL_ID AND
A.PROJECT_YEAR = B.PROJECT_YEAR AND
A.BGT_PER_SEQ = B.BGT_PER_SEQ )
AND PROPOSAL_ID = '087847' ORDER BY PROPOSAL_ID,
PROJECT_YEAR, BGT_PER_SEQ, DETAIL_LINE_SEQ_3;
----------------------------------------------------------------------------

Here are the results in V7

PROPOSAL_ID PROJECT_YEAR BGT_PER_SEQ DETAIL_LINE_SEQ_3 AGY_SALARY_AMT
AGY_FICA_AMT AGY_ANNUITY_AMT
----------- ------------ ----------- ----------------- --------------
------------ ---------------
087847 01 002 001 500 38
43
087847 01 002 002 500 38
58


When that SQL is executed in V8 we get these results

PROPOSAL_ID PROJECT_YEAR BGT_PER_SEQ DETAIL_LINE_SEQ_3 AGY_SALARY_AMT
AGY_FICA_AMT AGY_ANNUITY_AMT
----------- ------------ ----------- ----------------- --------------
------------ ---------------
087847 01 002 001 500 38
43
087847 01 002 001 500 38
43
087847 01 002 001 500 38
43
087847 01 002 002 500 38
58
087847 01 002 002 500 38
58
087847 01 002 002 500 38
58


Now aside from changing the SQL to fix it, which is going to be tough to
find all the SQL and change it, is there some reason that V8 is giving me
different results?

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, 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

Roger Miller

Re: [V8 CM z/os 1.7] Different SQL Results from V7 to V8
(in response to Michael Woodruff)
What is dbaprod.RSV_BU_PER_COMP? If that's not a view which joins a
couple of tables without all of the join conditions or unions or a declared
temp ..., then it's time to send this to service, where they will want to know a
lot about the data and query, DB2 service level, use of parallelism, explain
info, ...

Here are a few guesses if this involves service:
http://www-01.ibm.com/support/search.wss?
rs=64&lang=en&loc=en_US&r=10&cs=utf-
8&rankfile=0&cc=&coll=0&spc=&stc=&apar=include&q1=duplicate&q2=incorrout
&sort=desc&tc=SSEPEK&ibm-search.x=13&ibm-
search.y=8&dc=DB550+D100&dtm

Roger Miller, DB2 for z/OS

On Tue, 2 Dec 2008 20:19:46 +0000, Michael Woodruff
<[login to unmask email]> wrote:

>We recently upgraded to V8 and aside from performance issues, after a
couple
>of days our application development team ran across a problem with some
SQL
>that is run from a Peoplesoft Application. Some background, this is a 3
>year proposal, so you will see that we are asking for the first year data,
>but in V8 it seems we get the first year data repeated for the number of
>years of the proposal. On a 5 year proposal, the first year data is
>repeated 5 times, etc....
>
>Here is the SQL:
>
>--------------------------------------------------------------------------
-
>SELECT PROPOSAL_ID, PROJECT_YEAR, BGT_PER_SEQ, DETAIL_LINE_SEQ_3,
>AGY_SALARY_AMT, AGY_FICA_AMT, AGY_ANNUITY_AMT
> FROM dbaprod.RSV_BU_PER_COMP A
>WHERE EXISTS (SELECT 'X' FROM dbaprod.RSV_BU_PERSNL B
> Where PROPOSAL_ID = '087847' and PROJECT_YEAR = '01'
> AND
> A.PROPOSAL_ID = B.PROPOSAL_ID AND
> A.PROJECT_YEAR = B.PROJECT_YEAR AND
> A.BGT_PER_SEQ = B.BGT_PER_SEQ )
> AND PROPOSAL_ID = '087847' ORDER BY PROPOSAL_ID,
>PROJECT_YEAR, BGT_PER_SEQ, DETAIL_LINE_SEQ_3;
>--------------------------------------------------------------------------
--
>
>Here are the results in V7
>
>PROPOSAL_ID PROJECT_YEAR BGT_PER_SEQ DETAIL_LINE_SEQ_3
AGY_SALARY_AMT
>AGY_FICA_AMT AGY_ANNUITY_AMT
>----------- ------------ ----------- ----------------- --------------
>------------ ---------------
>087847 01 002 001 500 38
> 43
>087847 01 002 002 500 38
> 58
>
>
>When that SQL is executed in V8 we get these results
>
>PROPOSAL_ID PROJECT_YEAR BGT_PER_SEQ DETAIL_LINE_SEQ_3
AGY_SALARY_AMT
>AGY_FICA_AMT AGY_ANNUITY_AMT
>----------- ------------ ----------- ----------------- --------------
>------------ ---------------
>087847 01 002 001 500 38
> 43
>087847 01 002 001 500 38
> 43
>087847 01 002 001 500 38
> 43
>087847 01 002 002 500 38
> 58
>087847 01 002 002 500 38
> 58
>087847 01 002 002 500 38
> 58
>
>
>Now aside from changing the SQL to fix it, which is going to be tough to
>find all the SQL and change it, is there some reason that V8 is giving me
>different results?
>
>_________________________________________________________________
_____

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, 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

Michael Woodruff

Re: [V8 CM z/os 1.7] Different SQL Results from V7 to V8
(in response to Roger Miller)
That is a views but I broke down the views into the tables and the results
are the same. Here is the expanded SQL using just tables. It still gets
different results in V7 vs V8. I already mentioned to the programmer that
it is fairly convoluted, but a re-write isn't an acceptable fix - yet...

Anyway, here's the complete SQL with just tables.

SELECT C.PROPOSAL_ID,
C.PROJECT_YEAR,
C.BGT_PER_SEQ,
C.DETAIL_LINE_SEQ_3,
C.AGY_SALARY_AMT,
C.AGY_FICA_AMT,
C.AGY_ANNUITY_AMT
FROM RST_BU_PER_COMP C ,
RST_BU_PERSNL E ,
RST_SENIOR_KEY F
WHERE E.PROPOSAL_ID = F.PROPOSAL_ID
AND E.PROJECT_YEAR = F.PROJECT_YEAR
AND E.EMPL_SSN = F.KEY_EMPL_SSN
AND F.KEY_PERSON_CODE = 'K'
AND C.PROPOSAL_ID = E.PROPOSAL_ID
AND C.PROJECT_YEAR = E.PROJECT_YEAR
AND C.BGT_PER_SEQ = E.BGT_PER_SEQ
AND EXISTS (
SELECT 'X'
FROM RST_BU_PERSNL A ,
RST_SENIOR_KEY B
WHERE A.PROPOSAL_ID = B.PROPOSAL_ID
AND A.PROJECT_YEAR = B.PROJECT_YEAR
AND A.EMPL_SSN = B.KEY_EMPL_SSN
AND B.KEY_PERSON_CODE = 'K'
AND A.PROPOSAL_ID = '087847'
AND A.PROJECT_YEAR = '01'
AND C.PROPOSAL_ID = A.PROPOSAL_ID
AND C.PROJECT_YEAR = A.PROJECT_YEAR
AND C.BGT_PER_SEQ = A.BGT_PER_SEQ )
AND C.PROPOSAL_ID = '087847'
ORDER BY PROPOSAL_ID,
PROJECT_YEAR,
BGT_PER_SEQ,
DETAIL_LINE_SEQ_3;

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, 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