Hi,




Further clarify, the reason we found some of the rows inserted was
missed was because the select start from the last sequence number for
50 rows, we suspect that some row of higher sequence number was seen
by the select statement before some with lower sequence number when
they are both committed within same unit of work by the inserting
program.
The question is: is it normal to have inserted rows partially
revealed
to another selected program running currently and anyway to avoid
this?

On Jun 1, 6:32*am, ovms <patrickclc... (AT) gmail (DOT) com> wrote:
So this sequence number is not unique?
Every 200 rows share the same sequence number?

Do you have an order by in your select Statement? It would help if you
provided the exact SQL syntax used and DDL for the table.

On Jun 2, 5:38*am, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Hi,
I update more information as below :

table creation DDL:
------------------------------------------------
-- DDL Statements for table "ABC "."TBL_ORDER_ACT"
------------------------------------------------


CREATE TABLE "ABC "."TBL_ORDER_ACT" (
"STREAM_ID" SMALLINT NOT NULL ,
"TLOG_SEQ_NUM" BIGINT NOT NULL ,
"ROW_TIME" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
..........
"TRADE_DATE" DATE )
IN "STOORDAC" INDEX IN "ITOORDAC" ;


-- DDL Statements for primary key on Table "ABC "."TBL_ORDER_ACT"

ALTER TABLE "ABC "."TBL_ORDER_ACT"
ADD PRIMARY KEY
("STREAM_ID",
"TLOG_SEQ_NUM");



-- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT"

CREATE INDEX "ABC "."IDX_ORDER_ACT_02" ON "ABC
"."TBL_ORDER_ACT"
("TRANS_TIME" ASC)

COMPRESS NO ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT"

CREATE INDEX "ABC "."IDX_TBLORDER_ACT_BROKER_NUM" ON "ABC
"."TBL_ORDER_ACT"
("BROKER_NUM" ASC,
"ORDER_SEQ_NUM" ASC)

COMPRESS NO ALLOW REVERSE SCANS;

-- DDL Statements for indexes on Table "ABC "."TBL_ORDER_ACT"

CREATE INDEX "ABC "."IDX_TBLORDER_ACT_STOCK_CODE" ON "ABC
"."TBL_ORDER_ACT"
("STOCK_CODE" ASC)

COMPRESS NO ALLOW REVERSE SCANS;

Two programs running in parallel with the following SQLs:

Insert SQL:
INSERT INTO ABC.TBL_ORDER_ACT VALUES (1,?,CURRENT TIMESTAMP,
6611,'00939',809,?,'2010-04-07 09:30:00.328077',212137392600328077,
'O',1,'OI','A',6500,'N',0,0,0,0,0,'B','A','Y',
43000,0,43000,0,0,'OG','OG01129001','','','N','N', '','1970-01-01',
0,75904,10000010,702,'',0,'2010-04-07','2010-04-07')

The TLOG_SEQ_NUM field are being filled with an increasing number for
each record by the program.


the read SQL:
select tlog_seq_num from abc.tbl_order_act where stream_id=1 and
tlog_seq_num>${LASTSEQ} order by tlog_seq_num asc fetch first 1000
rows only;"

the read SQL each time read from the Lastseq obtained in previous
read; we found that it missed a lot of records at the end.

We don't find similar problem with similar calls in mainframe z/OS
DB2; any light on this are appreciated.

PC.