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!