jdbc ,bpm

[Former Member]

Hello

i have 2 database tables on same database, i want to insert records in first table and after last record is inserted in the first table , i want to insert records in the second table, is this possible via bpm ? if so how? or is there any other way it could be done more easily ?

this is a very high volume interface involving 40,000 records approx inserts.

Thanks

[Former Member]

Hi,

The queues will come into the picture in case of multiple messages.

Suppose you have 100 separate messages, in that case, depending upon the worker threads availability, the messages will be inserted.

But in your case, the message you will be inserting is single.

No matter it has lakhs of records, but while getting inserted in databse, it will be inserted as a single message and hence a single queue(or thread) will be used in this case.

I hope it clarifies your doubt.

-Supriya.

[Former Member]

Getting your point but for performance optimization reasons ,i was thinking on abap side : if there are 50000 records ,to split the number of messages in chunks of 100 or 1000. In that case case we would have 500 messages in moni. splitting in chunks would be better for debugging purposes and even for performance optimization reasons, since the number i am referring as 50,000 might increase to 3 million records.

Thanks

Edited by: sap_logic on Sep 16, 2010 4:24 PM

[Former Member]

You can not see this functionality readily available on Advanced Tab page of Communication channel.

For this, you have to configure this parameter in the table of 'Advanced Mode'.

Refer the above blog given by Amit.

-Supriya.

[Former Member]

Ok it is working in prototype as you all have mentioned , but what about multiple queues we have when we register queues , suppose first table has 50000 records to be inserted and second table has 1 record, can a particular queue not go out of sequence of the m apping sequence and insert in second table first and disturb the logic ? its just a thought..( when do queues come into picture and why we have so many queues(

Thanks

[Former Member]

Hi,

If you want to see how parsing is done at the receiver JDBC end and what SQL query would be formed in order to insert the data, you can use 'logSQLStatement' parameter on the advanced tab page of receiver JDBC channel.

Refer the SAP Note 801367 for the details of this parameter.

Once this parameter is configured in channel, then run the scenario.

In runtime workbench, in message monitoring, on the details tab of the message you can see the actual SQL query formed while inserting the data.

-Supriya.

[Former Member]

Hi Sap Logic,

It possible to BPM,What you are using the BPM to insert the one table, Same BPM should also insert the second table also.

But it changes only in JDBC structure not in BPM

Generally you insert the data into the one table using the structure below: Here i am giving the example for Employee data

StatementName1

--dbTableName

-

---

action (Map to constant 'INSERT')

-

---

table (Map to constant 'TableName1')

-

---

access

-

---

Empno

-

---

Empname

-

---

Address

If you want to insert second table immediatly after insert the first table, You can add one more Statement structure under StatementName1.See below

StatementName1

--dbTableName

-

---

action (Map to constant 'INSERT')

-

---

table (Map to constant 'Tablename1')

-

---

access

-

---

Empno

-

---

Empname

-

---

Address

StatementName2

--dbTableName

-

---

action (Map to constant 'INSERT')

-

---

table (Map to constant 'TableName2)

-

---

access

-

---

Desgination

-

---

Gender

-

---

Blood Group

Regards,

Sateesh

[rodrigoalejandro_pertierr]

hi,

you could do it in two sync interfaces but i need to know which is the sender system (FILE;RFC;SOAP;ETC)

[Former Member]

Hi,

No, it doesn't happen in that way.

The sequence you have mentioned in the structure, the same sequence will be followed for inserting the data.

As once the Insertion for both the tables are done, database will execute commit command, which ensures the successful insertion of data.

Unless, first table records are inserted, second table records will not be inserted.

-Supriya.

[Former Member]

Hi Sap_logic,

Supriya is right, if you let your mapping programm buid the message that way, the jdbc adapter will handle it exact

in that order (first fill up table 1, then table 2).

Don't worry about your mapping programm messing up anything. It is totally executed before the inserts starts.

The processing in order in PI is always:

Mapping gets exectued and produces an XML Message (e.g. like the one Supriya described above).

The XML message in whole gets sent to the Adapter (here JDBC receiver).

The JDBC Adapter works through the message like a regular XML parser (from top to the end).

So the first statement will be extecuted first, and the last last

Best wishes

Sebastian

[Former Member]

Hi,

what you are mentioning might be true ,but someone from SAP or having deep knowledge of internal parsing mechanism could tell the answer if there are no parallel processors which split the work if the first pipeline is consuming lot of time for inserts.

Thanks

[Former Member]

Hello sap_logic

Well what i wrote is not a guess, it's actually how it works and no for sure not there's such thing as parrallel processors for different parts of one message .....

Refer to the note Supriya posted for official statements of SAP

Best wishes

Sebastian

[Former Member]

Hello

I do not se logSQL statement in adavance tab. We have PI 7.1. Is this functuionality not available in PI 7.1?

[Former Member]

Hi,

U have to specify that parameter under "Advanced Mode".

Plz have a look:

/people/francesco.bersani/blog/2009/11/27/preparedstatement-with-jdbc-receiver-adapter

Thanks

Amit

Edited by: AmitSri on Sep 13, 2010 7:27 PM

[Former Member]

Hi,

Go thru this...

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

But the best practice is to use stored procedure in jdbc sceanrio as you can implement complex logics in stored procedue.. you can extend procedure as you want without disturbing XI code..

Regds,

Pinangshuk.

[Former Member]

Hi Supriya

The way your jdbc statement has ben created would work if SAP uses sequential logic internally for parsing. In that case it would be fine , but since we are not sure internally how SAP mapping logic works , we cannot be sure that creating 2 jdbc statements in that sequence would lead to first table which has a huge number of records to be fed first and then come to second table. If there are a huge number of records for first table, it would take time for inserts in first table and some optimization mechanism on SAP mapping side could start mapping second node to second table before first table everything has been inserted.... not sure though.. does anyone have any suggestions?

Thanks

[Former Member]

Hi,

Hi Laxmi,

Create the Insert structure for JDBC in PI as below:

Suppose Insert_Structure is the structure you are creating.

Then hierarchy would be:

<Insert_Structure>

-

---

<statement1>

-

---

<row1>

-

---

<Action>

-

---

<table>

-

---

<access>

-

---

Field1

-

---

Field2

-

-

-

---

FieldN

-

---

</access>

-

---

</row1>

-

---

</statement1>

-

---

<statement2>

-

---

<row2>

-

---

<Action>

-

---

<table>

-

---

<access>

-

---

Field1

-

---

Field2

-

-

-

---

FieldN

-

---

</access>

-

---

</row2>

-

---

</statement2>

For Statement1, the action would be Update_Insert and table will have the first table name.

Similarly, for Statement2, the action would be Update_Insert and table will have the second table name.

These values will be hardcoded in mapping.

In this case, tables will be updated in the sequence you mentioned.

-Supriya.

[Former Member]

instead of BPM , use Stored procedure .

Your flow will be PI to DB Stored procedure and then rest thing will be handled by SP.