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

Optimizer recommended a logical over QADBIFLD with these key fields:

DBIREL, DBILB2, DBIATR, DBILFI, DBILFL

The first is Relational? Y/N, the third is the attribute (<> 'IX'). These
are in the where clause for SYSCOLUMNS.

The other 3 are the fields you are using for selection.

Cheers

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

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