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

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

There are 3 logicals in QSYS over the physical that is the from file in
SYSCOLUMNS. They are QADBIATR (by attribute), QADBILFI (by long filename),
and QADBILLB (by long library name).  I suspect the long file and lib names
are not very useful to you.

Have you turned on DBG to see what indexes the optimizer is trying to use?

At 02:31 PM 3/15/02 -0500, you wrote:

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