JDBC sender adapter: Update before mapping

[former_member296836]

Dear all,

following scenario:

in a database we have a field called "statpi". We pick all messages that have statpi = 0 and

update the data record to statpi = 1 via UPDATE function in JDBC sender adapter.

Every select statement is limited to 100 data records for each message in PI.

But if one data record is wrong and the mapping failed, of course the complete mapping failed.

My problem: The update function of the JDBC sender adapter has already updated the

data records, although they didn't have been send to target system.

What I need is following thing: The adapter should send the messages and after PI has mapped

the message and send it to target system, the UPDATE command should be executed.

In my opinion, this is only possible with BPM, but I am not familiar with Advanced mode in JDBC

sender adapter and "Transaction isolation level". Is there any workaround for this?

Thanks

Regards

Chris

[Former Member]

We handled a similiar situation without a BPM. On the comm channel we changed the update to update the status to a "2", and changed the select to pick up 0 & 2. Then in the receiver determination we have two communication components, one to where you are sending the message and the other for the data source. Of course you will also need the interface determination, and receiver agreement for the source system. We then created another mapping to update the records to a status of 1, you could add a check in here for the validity of the data before doing the update. Hope that helps.

[Former Member]

Hi Tracy,

I have a similar situation - but my database is Oracle

What am noticing is that the UPDATE statement incorrectly updates the 'newer' rows inserted AFTER the select was performed !

SQL Select = select * from <VIEW_XYZ> where transfer_status = 'R'

UPDATE statement = UPDATE <TABLE> set status = 'S' WHERE status = 'R'

Did you experience this behavior?

What's your isolation level set as ?

Thank you

[stefan_grube]

> But if one data record is wrong and the mapping failed, of course the complete mapping failed.

> My problem: The update function of the JDBC sender adapter has already updated the

> data records, although they didn't have been send to target system.

Fix the mapping and resend the message again from PI.

[former_member296836]

Thanks for your replies.

@Shabarish

I use the command "rownum" in select and update statement.

As I tested and even as it is written in this thread:

The result should be identically.

@ Stefan

The problem is, that the mapping is all right, but the source data is wrong.

So I have to inform the database team and they have to fix the data.

In ideal case the data records are still in statpi=0 and get picked up after

the database team has fixed the problem and the adapter polls again.

But now, I can only stop the message and resend the message (except

the wrong one) via test message tab in RWB, because I am not able to

rollback the changes on statpi.

[Shabarish_Nair]

ideally in such a case either;

1. manual resend via PI (you can use the message editor from RWB)

2. make required correction in the source DB. Reset field statpi to default and let the processing continue

[stefan_grube]

> I use the command "rownum" in select and update statement.

> The result should be identically.

What happens, when new values have entered db meanwhile?

> In ideal case the data records are still in statpi=0 and get picked up after

> the database team has fixed the problem and the adapter polls again.

If the flag is not set, the JDBC adapter would poll the same data again and again.

> But now, I can only stop the message and resend the message (except

> the wrong one) via test message tab in RWB, because I am not able to

> rollback the changes on statpi.

- You could do following: pass the values with an error flag, so you can handle this in receiver application,

- Do a split mapping and create a file where you drop any wrong lines

- Use XML validation in sender adpter, so the messages are not processed.

- use a stored procedure in DB whee you check data quality to send out.

In our company we decided to pass all values, even if they are unvalid, and repair them in receiving system.

[former_member296836]

> 1. manual resend via PI (you can use the message editor from RWB)

Resend messages manually is something I would like to avoid, because in my opinion the middleware

should not change content of messages. But this is the workaround we are using at the moment.

> 2. make required correction in the source DB. Reset field statpi to default and let the processing continue

That will be no problem if we have only 10 (or less) messages. But later we will select up to 5000

data records for each message. We will not be able to reset the statpi field manually for each record.

[former_member296836]

>

> What happens, when new values have entered db meanwhile?

The job that change the DB will be fixed on a certain time. We need to configure

Availability Time Planning to be sure, that we start our select at another time.

>

> > In ideal case the data records are still in statpi=0 and get picked up after

> > the database team has fixed the problem and the adapter polls again.

>

> If the flag is not set, the JDBC adapter would poll the same data again and again.

The adapter should poll every hour. Of course we will have some errors before the

support is able to fix this problem, but because of one message each hour this should

not be the main problem.

> - You could do following: pass the values with an error flag, so you can handle this in receiver application,

I am using the function formatNumber for the field that could raised the error. But to chekc if I am able

to do a workaround here and let the target system fix the error sounds like an interessting possibility.

> - Do a split mapping and create a file where you drop any wrong lines

Also interessting. You mean that all "good" record should be mapped to struc1 and the bad one to struc2.

I am thinking of how to determine which record is wrong. Perhaps with an UDF that checks for this special

error.

> - Use XML validation in sender adpter, so the messages are not processed.

You are full of good ideas. I am not using validation yet and the mentioned field is define as string, but that

is changeable. A good possibility to check the new capability of 7.1.

> - use a stored procedure in DB whee you check data quality to send out.

not sure what this is, but I will discuss with DB team.

[rodrigoalejandro_pertierr]

hi,

- Use XML validation in sender adpter, so the messages are not processed.

as i can see, that is not an option because your error is related to date, not the structure.

use a stored procedure in DB whee you check data quality to send out.

I am agree with this option, but before implement it, you should analize with that data comes wrong and if you can resolve that in the mapping or if the data could be corrected in the source message at the moment the table is filling. i sugguest that first analize and later drop ideas.

I am using the function formatNumber for the field that could raised the error. But to chekc if I am able to do a workaround here and let the target system fix the error sounds like an interessting possibility 

I think if the target message could solved the issue so you, i do not recommend you to do this acording what you arre saying.

You can split the message and store the Key of all bad lines, so later call a interface that mark those lines as bad but keep the first flag marked.

also in the other mapping send just the right records and discard the bad.

Thanks

Rodrigo P.

Edited by: Rodrigo Alejandro Pertierra on Jun 24, 2010 12:11 PM

[Shabarish_Nair]

the problem you will have is the transaction handling interms of the record set.

how will you ensure that the same records that were picked is the same that are being updated?