× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Alexei makes a good point. Maybe 'where exists' would be better, if it
works as I hope it does, viz., that it stops as soon as it finds a match.

At 02:47 PM 3/15/02 -0600, you wrote:

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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.