ODBC peculiarity

[Buck Calabro/commsoft<mcalabro@xxxxxxxxxxxx>]

I'm relaying this question from our client-server group.  When the 
PC-centric folks come up with an oddity, they tend to ask me "why does the 
AS/400 do that?"  This one has me stumped...

If we run this SQL statement on an interactive SQL display, it runs fast 
and returns accurate results.

SELECT NHVNXT PAY_DESC, SUM(LOMBNB) TOT_AMT, 
  SUM(LODLVE) CURR_AMT, SUM(LOMCNB) PAST30_AMT, 
  SUM(LOMDNB) PAST60_AMT, SUM(LOMENB) PAST90_AMT, 
  SUM(LOM7NR) PAST120_AMT, SUM(LOM8NR) PAST150_AMT, 
  SUM(LOM9NR) PAST180_AMT 
FROM ACTPHY01 a, ASBPHY01, PCOPHY01 
WHERE B1LKNB = 16808 
AND B1HXCD = '  1' 
AND B1LWNB = LOLWNB 
AND LONUCG = NHNUCG 
AND LOADNS = (SELECT MAX(LOADNS) FROM ASBPHY01 
              WHERE LOLWNB = a.B1LWNB) 
group by NHVNXT

When using the IBM Client Access 95 ODBC driver over TCP/IP, this statement 
does a strange thing.
It runs properly the first time it executes.
It runs properly the second time it executes.
The third and subsequent executions return incorrect amounts for the 
SUM()s; rather than return the SUM(), they return the SUM() plus the value 
of the column in the first pass.

Here are the results for TOT_AMT, but all the other columns work the same 
way...
Pass 1   62.54
Pass 2   62.54
Pass 3  125.08
Pass 4  187.62 and so on.

If we remove the subselect and hard-wire a number it works properly every 
time:

SELECT NHVNXT PAY_DESC, SUM(LOMBNB) TOT_AMT, 
  SUM(LODLVE) CURR_AMT, SUM(LOMCNB) PAST30_AMT, 
  SUM(LOMDNB) PAST60_AMT, SUM(LOMENB) PAST90_AMT, 
  SUM(LOM7NR) PAST120_AMT, SUM(LOM8NR) PAST150_AMT, 
  SUM(LOM9NR) PAST180_AMT 
FROM ACTPHY01 a, ASBPHY01, PCOPHY01 
WHERE B1LKNB = 16808 
AND B1HXCD = '  1' 
AND B1LWNB = LOLWNB 
AND LONUCG = NHNUCG 
AND LOADNS = 2
group by NHVNXT

We are working around this by avoiding the subselect, but this can't be 
working as designed.

Looking at the QZDASOINIT job, I can see that there are open files in 
QTEMP:
*SUBQUERY  *SUBQUERY  *SUBQUERY  FORMAT0002 PHY      19  I   NO
ACTPHY01   COMDEV200  ACTPHY01   FORMAT0001 PHY      38  I   NO 17081 
*QUERY0003 QTEMP      *QUERY0003 *QUERY0001 PHY      76  O   NO 12

It almost appears that the DB manager on the 400 is caching the results for 
me (some sort of performance enhancement?)

Has anyone seen this before?  This is happening on all the PC's here.  My 
machine is Win95 OSR2, CA V3R2 Service level None
It is not limited to these particular DB files, either.  Any subselect 
causes SUM() to do this.

Ideas?

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---