** JDBC Receiver - Oracle Stored Procedure - Large Records - Performance

[jegathees_waran]

Hi friends,

In my File to JDBC scenario, I use Oracle SP. I designed my target structure as mentioned in help.sap.com. In this scenario, the Sender file sends large no. of records, we have to update those records in the Oracle table. As per this requirement, I did mapping. I tested one file with 4 records. In SXMB_MONI, mapping works fine. I have given below the target payload. The message is processed successfully. (Still I have not created SP in database I am unable to check for the updating of records in the table).

My doubt is

1) Whether the target payload is correct ?

2) For each <STATMENT> tag, Will XI establish connectivity to JDBC and update the record ? If it is, in real time if we send large no. of records, ex: 50 thousand records, performance isssu will come or not?

3) How to solve the problem as said in point 2. (LookUp procedure etc)

Kindly reply friends. (If you have faced this problem ... kindly reply how to solve this issue)

Target Payload:

<?xml version="1.0" encoding="utf-8"?>

<ns1:PSABCL_Mumbai xmlns:eds="http://sdn.sap.com/sapxsl" xmlns:ns0="http://abc.xyz.com" xmlns:ns1="http://abc.xyz.com/ABCL/Finance">

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

<IF_ROW_STAT>FOR_IMPORT</IF_ROW_STAT><CON_FST_NAME>John</CON_FST_NAME><CON_LAST_NAME>Test001915</CON_LAST_NAME><CON_MID_NAME>W</CON_MID_NAME>

</SP_ABCL>

</STATEMENT>

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

<IF_ROW_STAT>FOR_IMPORT</IF_ROW_STAT><CON_FST_NAME>Josephine</CON_FST_NAME><CON_LAST_NAME>Walker</CON_LAST_NAME><CON_MID_NAME>Rose</CON_MID_NAME>

</SP_ABCL>

</STATEMENT>

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

</SP_ABCL>

</STATEMENT>

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

</SP_ABCL>

</STATEMENT>

</ns1:PSABCL_Mumbai>

Thanking You.

Kind Regards,

Jegathees P.

[prabhu_s2 · Accepted solution]

moni shows that the message is sucessfuly processed by the IE. But the error in CC are not tracked in moni. so goto RWB and check communication chanel monitoring. u might find error log if the records are not updated in the DB table

[jegathees_waran]

Thanks VJ. I will try the same.

[jegathees_waran]

Hi VJ,

Could you give the example for the target payload (XML format) for one DB call for large records like you said? Beacuse, I am not able to imagine how the target payload should come to solve this ?

Kind Regards

Jeg P.

[VijayKonam]

There would be only one parameter which is input type and string data type in the target structure. Write a UDF and have all the parameters you want to send as the inputs for this UDF.

Now write the UDF. Lets say, we are going with XML.

<Target>

<Set> (repeats)

<Param1>Value</Param1>

<ParamN>Value</ParamN>

</Set>

<Target>

Then in the SP, strip this xml th way you want and use in your business logic. Thus, each was actually supposed to be a separate DB call, but now.. all are combined in to one SP call.

VJ

[Former Member]

Hi

>>Will XI establish connectivity to JDBC and update the record ?

This actually depends on the configuration.

If you select this

<b>Disconnect from Database After Each Message Processing</b>

It causes the database connection to be released and reestablished before every poll interval.

If there will be more records definitely the responses from the DB will be slow.

Thanks

[VijayKonam]

Hi,

The structure should be -

<MsgType Name>

<StatementName>

<storedProcedureName action = "EXECUTE">

<table>

<List of Parameters isInput = "true" type = "STRING">

Map the table node to the stored procedure name.

Also,

For each statement, XI would make a database call. For better performance, do not check the button in CC - Open a new connection to database for each message.

Also another solution would be, collect all the data in the mapping to a comma or a pipe separated string in the mapping and have the statement node created only once. This way, though you have 5000 records, they all will be given to the SP in one DB call. you can also manage the mapping so that you will not send more the the X number of records to database in a single call. We are using an XML created in code in an UDF for this. The SP has to take care of stripping the comma or pipe separated values or the XML sent as a string input parameter.

VJ

[prateek]

Ur structure looks fine. But what are these for

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

</SP_ABCL>

</STATEMENT>

<STATEMENT>

<SP_ABCL ACTION="EXECUTE">

</SP_ABCL>

</STATEMENT>

They are not performing any task.

Regards,

Prateek

[jegathees_waran]

Hi Prabhu,

It does not show any error in CC. But, in message monitoring, the message is in 'Waiting' state, reason is still I did not create SP in Oracle.

But, My doubt is 1) correctness of target payload for Multiple records 2) Performance issue when we send large amount of records.

[prabhu_s2]

check cc monitoring to see if any error are encountered