On Friday, August 18, 2023 at 8:56:11 p.m. UTC-4, Adam “Adamlivinlife” Kamal wrote:
> On Thursday, August 17, 2023 at 5:34:45 PM UTC-7, JShepherd wrote:
> > In article <3a8f4387-ed26-450b...@googlegroups.com>,
> > abol...@gmail.com says...
> > >
> > >On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:
> > >> In article <9c035799-1510-429d...@googlegroups.com>,=20
> > >> abol...@gmail.com says...=20
> > >> >=20
> > >> >On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =
> > >wrote:=20
> > >> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
> > >> >> abol...@gmail.com says...=3D20=20
> > >> >> >=3D20=20
> > >> >> >I have a SQL cursor that is used to retrieve records from parts table=
> > >. T=3D=20
> > >> >he =3D3D=3D20=20
> > >> >> >program is designed to return no more than 5000 records per read, the=
> > >n i=3D=20
> > >> >t c=3D3D=3D20=20
> > >> >> >losed the cursor.=3D3D20=3D20=20
> > >> >> >=3D20=20
> > >> >> >In case if there are more records than 5000, it saves off record numb=
> > >er =3D=20
> > >> >500=3D3D=20
> > >> >> >1 and use it to reopen the cursor and get the rest of the records.=3D=
> > >20=20
> > >> >> >=3D20=20
> > >> >> >The issue appears to be not all records is returned on the second rea=
> > >d.=3D=20
> > >> >=3D20=20
> > >> >> >=3D20=20
> > >> >> >The cursor look like this=3D20=20
> > >> >> >=3D20
> > >> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> > >> >> > SELECT COMPANY=3D3D20=20
> > >> >> > , WAREHOUSE=3D20=20
> > >> >> > , REPORT_NUMBER=3D20=20
> > >> >> > , SOURCE_OF_SUPPLY=3D20=20
> > >> >> > , PART_NUM=3D20=20
> > >> >> > , BIN_LOC=3D20=20
> > >> >> >=20
> > >> >> > FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
> > >> >> > WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
> > >> >> > WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
> > >> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
> > >> >> > SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
> > >> >> > PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
> > >> >> > BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
> > >> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=
> > >NUM=3D=20
> > >> >,=3D3D=3D20=20
> > >> >> > BIN_LOC, ALT_BIN ASC=3D20=20
> > >> >> > END-EXEC.=3D20=20
> > >> >> >=3D20
> > >> >> >On the second read/Open cursor, it returns the record which was saved=
> > > of=3D=20
> > >> >f =3D3D=3D20=20
> > >> >> >to use it to reopen the cursor. but then it skips a bunch of records.=
> > > I =3D=20
> > >> >was=3D3D=3D20=20
> > >> >> > hoping someone would be able to tell me what am i doing wrong in the=
> > > cu=3D=20
> > >> >rso=3D3D
> > >> >> >r. Thanks in advance and have a wonderful weekend !
> > >> >> The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=
> > >0=20
> > >> >>=3D20=20
> > >> >> What are the keycols for the table ?=3D20=20
> > >> >>=3D20
> > >> >> What does an explain plan for the statement show ?=20
> > >> >=20
> > >> >Happy Monday,=20
> > >> >
> > >> >Thanks for taking the time to reply back. The key is the selected column=
> > >s=3D=20
> > >> >=3D20=20
> > >> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
> > >> >T NULL=20
> > >> > , WARE CHAR(3) NO DEFAULT NOT NULL=20
> > >> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
> > >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
> > >> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
> > >> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
> > >> >=20
> > >> >
> > >> >Since you brought up the ORDER BY and explain plan, I noticed the key of=
> > > th=3D=20
> > >> >e table is in ascending order and exactly is my ORDER BY columns, so I r=
> > >emo=3D=20
> > >> >ved ORDER BY from the cursor but It still skipped records.=3D20
> > >> >=20
> > >> >Here is EXPLAIN plan:=20
> > >> >=20
> > >> > Operation 1.0 : Scan=20
> > >> > Table PARTS=20
> > >> > with correlation name PARTS=20
> > >> > Access type : No locks, browse access=20
> > >> > Lock mode : Chosen by the system=20
> > >> > Column processing : Requires retrieval of 19 out of 20 columns
> > >> >=3D20
> > >> > Access path 1 : Primary, sequential cache=20
> > >> > SBB for reads : Virtual, double buffer
> > >> > Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=
> > >=3D=20
> > >> >E ,=3D20=20
> > >> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
> > >> >ER=20
> > >> > End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
> > >> > :SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
> > >> >UMBER
> > >> > Index selectivity : Expect to examine 100% of rows from table=20
> > >> > Index pred. : None=20
> > >> > Base table pred. : Will be evaluated by the disk process
> > >> > ( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
> > >> >LOC >=3D3D :SEARCH-BIN-LOC )=20
> > >> > AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
> > >> > Pred. selectivity : Expect to select 1.2341% of rows from table
> > >> >=3D20
> > >> > Executor pred. : None=20
> > >> > Table selectivity : Expect to select 1.2341% of rows from table=20
> > >> > Expected row count: 5 rows after the scan=20
> > >> > Operation cost : 35
> > >> >=3D20
> > >> > Operation 1.1 : Sort=20
> > >> > Requested : Explicitly in the query=20
> > >> > Sort rows in the : Result of a Select=20
> > >> > Purpose : To order rows for an Order By=20
> > >> > Sort technique : FASTSORT=20
> > >> > Sort type : Plan to use User Process Sort=20
> > >> > UPS workspace : 34 Kbytes=20
> > >> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
> > >> > PARTS.REPORT_NUMBER asc,=20
> > >> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> > >> > PARTS.BIN_LOC asc,=3D20=20
> > >> > Sort cost : 1=20
> > >> >=3D20=20
> > >> > Total cost : 36
> > >> --------------------------------------------------
> > >> >>In case if there are more records than 5000, it saves off record number
> > >> >>and use it to reopen the cursor and get the rest of the records.
> > >> Where does record number come from and how does it become part=20
> > >> of the where clause on reopening the cursor?
> > >
> > >
> > >When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5=
> > >000 record limit will cause the program to save off the data for record 50=
> > >01 and close the cursor. The second read will use the saved off the data as=
> > > a starting point to read the next 5000 records. So record number is not a =
> > >part of the table and it's not used in the WHERE clause. It's an internal p=
> > >rogram counter to know when to stop reading as the page limit is set to 500=
> > >0 records. Thanks again for your help!
> > In the absence of any sample data,
> > I loaded a few rows with the variable part of the key as
> >
> > SS P000000000 BIN000000
> > SS P000000001 BIN000001
> > SS P000000002 BIN000002
> > SS P000000003 BIN000003
> > thru
> > SS P000000099 BIN000099
> >
> > ----------------------------
> > -- start the query
> >
> > set param ?company 12;
> > set param ?warehouse "W12";
> > set param ?report_number 1234;
> >
> > set param ?source_of_supply " ";
> > set param ?part_num " ";
> > set param ?bin_loc " ";
> >
> > prepare s1 from
> > +> SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
> > +> FROM =parts FOR BROWSE ACCESS
> > +> WHERE COMPANY = ?company and
> > +> WAREHOUSE = ?warehouse and
> > +> REPORT_NUMBER = ?report_number and
> > +> SOURCE_OF_SUPPLY >= ?source_of_supply and
> > +> PART_NUM >= ?part_num and
> > +> BIN_LOC >= ?bin_loc
> > +>;
> > --- SQL command prepared.
> >
> > execute s1;
> >
> > SOURCE_OF_SUPPLY PART_NUM BIN_LOC
> > ---------------- ---------- ---------
> >
> > SS P000000000 BIN000000
> > SS P000000001 BIN000001
> > SS P000000002 BIN000002
> > SS P000000003 BIN000003
> > SS P000000004 BIN000004
> > SS P000000005 BIN000005
> > SS P000000006 BIN000006
> > SS P000000007 BIN000007
> > SS P000000008 BIN000008
> > SS P000000009 BIN000009
> >
> > Assuming that 10 rows are fetched per open cursor
> > and the last row fetched was
> >
> > set param ?source_of_supply "SS";
> > set param ?part_num "P000000010";
> > set param ?bin_loc "zzzzzzzzz";
> >
> > The variable parts of the key is the problem
> > SOURCE_OF_SUPPLY >= ?source_of_supply and
> > PART_NUM >= ?part_num and
> > BIN_LOC >= ?bin_loc
> >
> > The high value of the bin_loc restart value prevents any more rows
> > from being fetched, at least in this test data,
> > because the three cols are AND'd. .
> >
> > With a more random bin_loc you would likely skip subsequent rows with
> > a bin_loc value less than the restart value
> >
> > execute s1;
> >
> > --- 0 row(s) selected.
> Thank you again for your help. I noticed if I remove the bin-_loc I get more records back.
> I am not sure what is the solution of this paging issue. In my mind I am giving the cursor a starting point on the second select. If both have the same order by to retrieve data, why the second select is not working. I still can not get it and that's preventing me from solving the issue. Would you please shed any light on how to fix this. Thanks in advance and have a wonderful weekend!

You should be aware that BROWSE ACCESS does not necessarily give you accurate results. READ COMMITTED is more reliable. I cannot say one way or another whether this will make a difference but no production code (in my opinion) should use BROWSE ACCESS as the results are questionable and not ever guaranteed to be consistent with what is in the TMF Audit Trail - assuming the tables are audited.