|
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.
As an Amazon Associate we earn from qualifying purchases.
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.