× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: RE: ODBC peculiarity
  • From: Walden Leverich <walden@xxxxxxxxxxxxxxx>
  • Date: Thu, 25 Feb 1999 11:08:46 -0500

Buck,

Have you tried making the ODBC driver "dumb"? Try turning off the "Enable
Lazy close support" option on the Performance Tab and the "Enable extended
dynamic (package) support" option on the Package(s) tab of the ODBC Setup
for your data source. This shouldn't make a difference, but then again, you
"shouldn't" be getting the error you are getting.

-Walden 

-----Original Message-----
From: Buck Calabro/commsoft [mailto:mcalabro@commsoft.net]
Sent: Wednesday, February 24, 1999 2:10 PM
To: midrange-l@midrange.com
Subject: ODBC peculiarity


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
+---
+---
| 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
+---


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.