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

I agree, it's something that'd at least need maintenance at upgrades, etc.
You may also need to deal with security - should, right? But, AFAIC,
those're the main issues. SYSCOLUMNS itself is just a normal SQL view over
this thing. You could have created it yourself.

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

>It just concerns me to build a logical over an IBM supplied file.
>
>Rob Berendt
>--
>"They that can give up essential liberty to obtain a little temporary
>safety deserve neither liberty nor safety."
>Benjamin Franklin
>
>
>
>                     Vernon Hamberg
>                     <vhamberg@attbi.com       To:     midrange-l@midrange.com
>                     >                         cc:
>                     Sent by:                  Fax to:
>                     midrange-l-admin@mi       Subject:     Re: Retrieving
> 1, and only 1, row in SQL
>                     drange.com
>
>
>                     03/15/2002 04:02 PM
>                     Please respond to
>                     midrange-l
>
>
>
>
>
>
>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.
>
>_______________________________________________
>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.