select and update rownum sender jdbc

[former_member229036]

Dear all

i am using PI 7.31 sender jdbc adapter.

table(TB_AA)  has 10000 row with pi_stat = 'R'   and then  sender jdbc adapter tried  10 row  in using rownum  every  120 second and update same rownum

but  result of select query and udpate query  were different.  not same record update for selecting data.

co_cd,  memid  is PK.

select co_cd , mem_id,  reg_n  FROM TB_AA  where PI_STAT = 'R' AND rownum <= 10

update  TB_AA  set  PI_STAT = 'S', where  PI_STAT = 'R' and rownum <= 10

query is wrong?  ,  what is correct update query with several PK ?

[iaki_vila]

Hi David,

I think the problem is really in the Oracle DB, check this blog for possible modifications in Oracle .

Have you tried to use the parameter maxMsgSize? (Configuring the Sender JDBC Adapter - Advanced Adapter Engine - SAP Library)

Regards.

[former_member229036]

i know that to limit row by select query , maxMsgSize can be controlled,  but when update query execute,  is it same row selected by select query?

umm..  i do not think so.

please is there a way to send row limited and update same row limited by select query ?

is it standard function  in sender jdbc adapter?

how do you make it with SAP PI ?   please help me

[former_member192851]

I do it like that:

SELECT <your fields> from <your table> where PI_STAT = 'R' FROM (select * from <your table> WHERE PI_STAT = 'R' ORDER BY <your unique id_field>) WHERE rownum <= 1000

UPDATE <your table> set PI_STAT = 'S' WHERE LINE_ID in  (SELECT LINE_ID FROM (select * from <your table> WHERE PI_STAT = 'R' ORDER BY <your unique id_field>) WHERE rownum <= 1000)

[former_member229036]

Hi Arten Solohin.

co_cd,  memid  is Primary Key.

select co_cd , mem_id,  reg_n  FROM TB_AA  where PI_STAT = 'R' AND rownum <= 10

update  TB_AA  set  PI_STAT = 'S', where  PI_STAT = 'R' and rownum <= 10

could you make your query with above my query ?  because i am confusing ORDER BY <your unique id_field>   AND LINE_ID ....

will be highly appreciated  in advance

thanks you very much.

[former_member192851]

Hi, i don't know sql so much, so advice you to ask sql guru guy.

Can you add one unique field to your table, or concat Primary Key from co_cd , mem_id to one string?

Maybe you can use construction like co_cd+mem_id as uniqueID... Can't check it know.

With one unique field your queries will be like that:

SELECT co_cd , mem_id,  reg_n from TB_AA where PI_STAT = 'R' FROM (select * from TB_AA WHERE PI_STAT = 'R' ORDER BY uniqueID) WHERE rownum <= 10

UPDATE TB_AA set PI_STAT = 'S' WHERE uniqueID in  (SELECT uniqueID FROM (select * from TB_AA WHERE PI_STAT = 'R' ORDER BY uniqueID) WHERE rownum <= 10)

[Former Member]

Hi,

A similar thread has been discussed already, it might help you.

http://scn.sap.com/thread/1991150

BR,

Anirban