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

["Alexei Pytel" <pytel@xxxxxxxxxx>]

QSYS2/SYSCOLUMNS is a logical file (SQL view). Apparently query over this
view requires internal copy of data.
You have ALWCPYDTA(*NO). Try changing it to ALWCPYDTA(*YES) or ALWCPYDTA
(*OPTIMIZE).

Why do you think it's going to copy entire file? It needs to create
"temporary result" for whatever purpose - it can be quite small.

To analyze SQL performance - run your program in debug mode and look for
optimizer message. It should give a clue about performance differences
between the runs.

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

your query specifies SELECT COUNT(*). DB will probably scan entire file for
this query to count all occurences.
6 million records in a minute - not too bad.
SELECT COUNT(*) is not the best formulation if you only want to check for
existence.

    Alexei Pytel
speaking for myself only





                      rob@dekko.com
                      Sent by:                  To:       
midrange-l@midrange.com
                      midrange-l-admin@m        cc:
                      idrange.com               Subject:  Retrieving 1, and 
only 1, row in SQL


                      03/15/2002 01: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.