Sender JDBC on database DB2/400

[Former Member]

Hi Gurus,

I got a scenario in which i select form 2 tables with join select statement

where flag = 0.

now my problem before the selected datasets will be update flag = 1 my database is added with a couple of new records with flag = 0.

is it a right statement for Query :

update table <db> set flag = 1 where flag = 0

and then the join select statement?

or is here another solution better?

Thanks for help!!

[VijayKonam · Accepted solution]

In this case you are going to miss the two newly added records since they were added after your your select query and before your update query.

A tri-state flag would be good in this case. Flag = 0 means just added. Not picked up by XI

flag =1 means picked up by XI

flag = 2 means picked up by XI and updated.

You select query should select all records with flag = 0 and update the flag to 1. (you might have to use SP here)

You update quey only update the records from flag = 1 to 2.

VJ

[Former Member]

Thanks for your help!!

best regards

Michael

[Former Member]

Hi folks,

thanks for your help but i have just only one question.

my select statement does look like so:

update BISVK000TB set AU_STATUS = 'INV' where AU_STATUS = 'ANG' select k.VERTR_NL, k.MITGLIEDNR, k.REFERENZ, k.HERKUNFT, k.SOTYPE, k.AU_STATUS, p.POS_NR, p.ARTIKELNR, p.BMENGE, p.EINZPREIS, p.CURRENCY, k.ID_AUFTRAG, k.TSTADD from BISVK000TB k, BISVP000TB p where k.ID_AUFTRAG = p.ID_AUFTRAG and k.AU_STATUS = 'INV'

in Channel Monitoring i get this:

'java.sql.SQLException: [SQL0199] keyword SELECT nicht erwartet

what is teh mistake here?

[Former Member]

Well... in your SQL statement you write "...where AU_STATUS = 'ANG' select k.VERTR_NL, ..." without giving any order how this "select" had to be involved in the "where"-clause.

[Former Member]

Hi,

for few days ago i read that it's possible to concat a select statement.

if it's true how does the statement look like and support the DB2/400 this function?

[Former Member]

Michael,

According to SAP, you have to use the Transaction Isolation level setting "Serializable" or "Repeatable Read" to avoid this issue. You can select the concurrency in your sender JDBC communication channel in the following way. Select Advanced Setting --> Select Serializable.

P.S: Increasing the transaction isolation level may have an adversary effect on the performance.

Regards,

Jai Shankar