RE: ODBC peculiarity

[Alan Schuetze <aschuetze@xxxxxxxxxxx>]

If it's worth anything...
In a previous life, I used to use the HIT Software ODBC driver with
NetSoft's Portfolio product for  performance reasons.


Alan Schuetze

Magellan Software
The Leader In Document Mining Technology*


        -----Original Message-----
        From:   Buck Calabro/commsoft [SMTP:mcalabro@commsoft.net]
        Sent:   Wednesday, February 24, 1999 11:10 AM
        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
+---