Re: Retrieving 1, and only 1, row in SQL

[rob@xxxxxxxxx]

Modification:
Performance is fast on the
field check and the file check.  Performance plummets on the *LIBRARY*
check.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin



                    rob@dekko.com
                    Sent by:                  To:     midrange-l@midrange.com
                    midrange-l-admin@mi       cc:
                    drange.com                Fax to:
                                              Subject:     Retrieving 1, and 
only 1, row in SQL

                    03/15/2002 02:27 PM
                    Please respond to
                    midrange-l







The file being queried is QSYS2/SYSCOLUMNS.  This file has 6.6million
records on our development box.  There are no keys on the file and I am not
going to create an index on an IBM supplied file.

The following subprocedure is used to access the file.  The point of this
subprocedure is threefold depending on how it is called.  One, determine
the existence of the library.  Two determine the existence of the file.
Three determine the existence of the field.  Performance is fast on the
field check and the file check.  Performance plummets on the file check.
For example a check on one of our BPCS data libraries returned a count of
81,390 and took about a minute.

I thought I'd play around with STRSQL.  First I tried
select dbname
from qsys2/syscolumns
where dbname='DATDIVF'
fetch first 1 row only
But I got "ALWCPYDTA(*NO) specified but temporary result required for
SELECT IN".  I sure don't want to wait for it to copy a 6.6 million record
file.  Would it copy the logical (QSYS2/SYSCOLUMNS, 6,590,211 records,
ODOBSZ=53,248) or the physical (QSYS/QADBIFLD, 6,590,209 records,
ODOBSZ=2,212,638,720)?

Any suggestions?

     P checkdb         B
     D checkdb         PI            10I 0
     D library                       10A   CONST
     D File                          10A   CONST OPTIONS(*NOPASS)
     D field                         10A   CONST OPTIONS(*NOPASS)

     D* Local fields
     D retField        S             10I 0
     D sqlstring       s            200a
     D loFile          s                   like(File)
     D hiFile          s                   like(File)
     D loField         s                   like(Field)
     D hiField         s                   like(Field)

      /free
       sqlstring = 'Select count(*) from qsys2/syscolumns +
                    where DBNAME = ? +
                      and TBNAME between ? and ? +
                      and NAME   between ? and ?';
       if %parms>1;
        loFile=File;
        hiFile=File;
       else;
        loFile=*loval;
        hiFile=*hival;
       endif;
       if %parms>2;
        loField=Field;
        hiField=Field;
       else;
        loField=*loval;
        hiField=*hival;
       endif;
       retField=*zeros;
      /end-free

     C/EXEC SQL
     C+ Prepare stmt from :sqlString
     C/END-EXEC


     C/EXEC SQL
     C+ declare C1 cursor for stmt
     C/END-EXEC

     C/EXEC SQL
     C+ Open C1 using :library, :loFile, :hiFile, :loField, :hiField
     C/END-EXEC

     C/EXEC SQL
     C+ Fetch C1 into :retField
     C/END-EXEC

     C/EXEC SQL
     C+ Close C1
     C/END-EXEC

     C                   RETURN    retField
     P checkdb         E


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.