In article <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>,
abolkini@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.