cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Single call for stored procedure from XI

Go to solution
Former Member

on ‎04-05-2010 2:57 PM

0 Kudos

Hi Friends,

I am using the stored procedure in target side, XI call the stored procedure each row to insert the record, Once successfully inserted log table is created,

so that thing i am using the stored procedure here.

We have lot of rows (that means lot of records)for ex 50000 records,presently in my scneario XI call the stored procedure for 50000 times to execute it.

So now i don't want like that, XI insert 50000 rows with in single call Stored Procedure is possible? can you please provide me your valubles?

Thank you very much.

Sateesh

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

madhusudana_reddy2
Contributor
‎04-27-2010 7:49 AM
0 Kudos

Hi Sateesh,

Even though you send 50000 records, stored procedure will execute single time only. It will not call 50000 times. Below is the structure for receiver jdbc,

<StatementName5>

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

In above structure your root node (<storedProcedureName action=u201D EXECUTEu201D>) occurance is 1..1so root node contains EXECUTE statement so that it will call stored procedure single time only.

Even though it will call single time for 50000 records, stored procedure will take so much time as this it needs to process 50000 records. Instead of this you process 40000 or 5000 records at a time by splitting in multimapping which will increase the performance.

thanks,

madhu

Show replies
Show replies
Former Member
‎04-27-2010 8:07 AM
0 Kudos

Hi Mudhusudhan,

Thank for your reply,

If i give the occurence of 1 to 1 for Execute statement, my structure is like this.

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:POCA0013_SCMDB_RESPONSE_MT xmlns:ns0="urn:pg.com:POCA0013:sample">
 <StatmentName>
  <XI_SP_KANLOGDATA action="EXECUTE">
    <PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-USA</PROJEKT_PROJKENNW> 
    <TV_KOLLO_PACKNR isInput="TRUE" type="CHAR"></TV_KOLLO_PACKNR> 
    <FM_NR isInput="TRUE" type="CHAR">SIK080280126498</FM_NR> 
    <ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-19-08 00:00:00.000</ERSTELL_FM> 
    <BEST_NR isInput="TRUE" type="CHAR">AH026456/203025962</BEST_NR> 
    <POSNR_KUNDE isInput="TRUE" type="CHAR">00005</POSNR_KUNDE> 
    <POSNR_LIEF isInput="TRUE" type="CHAR">000500</POSNR_LIEF> 
     ...........
     .......... 30 fields
  </XI_SP_KANLOGDATA>
 </StatmentName>
 <StatmentName>
  <XI_SP_KANLOGDATA action="EXECUTE">
    <PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-Dänemark</PROJEKT_PROJKENNW> 
    <TV_KOLLO_PACKNR isInput="TRUE" type="CHAR"></TV_KOLLO_PACKNR> 
    <FM_NR isInput="TRUE" type="CHAR">SIK080280120754</FM_NR> 
    <ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-16-04 00:00:00.000</ERSTELL_FM> 
    <BEST_NR isInput="TRUE" type="CHAR">AH026456/204318627</BEST_NR> 
    <POSNR_KUNDE isInput="TRUE" type="CHAR"></POSNR_KUNDE> 
    <POSNR_LIEF isInput="TRUE" type="CHAR">0193001740</POSNR_LIEF> 
     .........
     ...........
  </XI_SP_KANLOGDATA>
 </StatmentName>
 <StatmentName>
  <XI_SP_KANLOGDATA action="EXECUTE">
    <PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-Dänemark</PROJEKT_PROJKENNW> 
    <TV_KOLLO_PACKNR isInput="TRUE" type="CHAR"></TV_KOLLO_PACKNR> 
    <FM_NR isInput="TRUE" type="CHAR">SIK080280120754</FM_NR> 
    <ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-16-04 00:00:00.000</ERSTELL_FM> 
    <BEST_NR isInput="TRUE" type="CHAR">AH026456/204318627</BEST_NR> 
    <POSNR_KUNDE isInput="TRUE" type="CHAR"></POSNR_KUNDE> 
    <POSNR_LIEF isInput="TRUE" type="CHAR">0194001750</POSNR_LIEF> 
     ...........
     ...........
  </XI_SP_KANLOGDATA>
 </StatmentName>
</POCA0013_SCMDB_RESPONSE_MT >

This is correct format for call the stored procedure.Every statement have the one record and one execute statement,If i have 100 records,100 statement are going stored procedure.

Please suggest me how i can apporach here.

Thanks,

Sateesh

Edited by: sateesh kumar .N on Apr 27, 2010 9:07 AM

Edited by: sateesh kumar .N on Apr 27, 2010 9:08 AM

Former Member
‎04-27-2010 9:59 AM
0 Kudos

hmm, 50000 records as one XML ? Take care, that could blow up the system (memory). Does it make sense to use PI at all in that case ? Can you describe your scenario ? For such large data amounts, one should think about alternatives.

CSY

madhusudana_reddy2
Contributor
‎04-28-2010 11:03 AM
0 Kudos

Hi,

Can you try like below structure which contains execute statement only one. To see how many times the receiver stored procedure is executing use logSQLStatement = true in advanced parameters in receiver jdbc channel.

thanks,

madhu

Former Member
‎04-28-2010 11:41 AM
0 Kudos

Hi Madu,

Thank you for your message

I tried this format earlier itself,At that time i got error in RWB "Too many arguments" specified in stored procedure.

I don't get the idea how we can create the stored procedure if XI Send that format.

I tried to send below format also.I am getting same error like "too Many arguments" specified.

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

<ns0:POCA0013_SCMDB_RESPONSE_MT xmlns:ns0="urn:pg.com:POCA0013:sample">

<StatmentName>

<XI_SP_KANLOGDATA action="EXECUTE">

<table>XI_SP_KANLOGDATA</table>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-USA</PROJEKT_PROJKENNW>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-Denmark</PROJEKT_PROJKENNW>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-India</PROJEKT_PROJKENNW>

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<FM_NR isInput="TRUE" type="CHAR">SIK080280126498</FM_NR>

<FM_NR isInput="TRUE" type="CHAR">SIK080280126499</FM_NR>

<FM_NR isInput="TRUE" type="CHAR">SIK080280126500</FM_NR>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-19-08 00:00:00.000</ERSTELL_FM>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-20-08 00:00:00.000</ERSTELL_FM>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-21-08 00:00:00.000</ERSTELL_FM>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025962</BEST_NR>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025963</BEST_NR>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025964</BEST_NR>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00005</POSNR_KUNDE>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00006</POSNR_KUNDE>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00007</POSNR_KUNDE>

<POSNR_LIEF isInput="TRUE" type="CHAR">000500</POSNR_LIEF>

<POSNR_LIEF isInput="TRUE" type="CHAR">000501</POSNR_LIEF>

<POSNR_LIEF isInput="TRUE" type="CHAR">000502</POSNR_LIEF>

...........

......... 30 fields

</XI_SP_KANLOGDATA >

</StatmentName>

</POCA0013_SCMDB_RESPONSE_MT >

If i want to send the below format to database, How i can write the UDF in Mapping? Allready your know my source structure and target structure what i mentioned in first message of this thread.

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

<ns0:POCA0013_SCMDB_RESPONSE_MT xmlns:ns0="urn:pg.com:POCA0013:sample">

<StatmentName>

<XI_SP_KANLOGDATA action="EXECUTE">

<table>XI_SP_KANLOGDATA</table>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-USA;O-Denmark;O-India</PROJEKT_PROJKENNW>

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<FM_NR isInput="TRUE" type="CHAR">SIK080280126498;SIK080280126499;SIK080280126500</FM_NR>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-19-08 00:00:00.000;2009-20-08 00:00:00.000;2009-21-08

00:00:00.000</ERSTELL_FM>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025962;AH026456/203025963;AH026456/203025964</BEST_NR>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00005;00006;00007</POSNR_KUNDE>

<POSNR_LIEF isInput="TRUE" type="CHAR">000500;000501;000502</POSNR_LIEF>

...........

......... 30 fields

</XI_SP_KANLOGDATA >

</StatmentName>

</POCA0013_SCMDB_RESPONSE_MT >

If i passed above format to database, How much max char string acceptd for stored procedure parameter?

Please guide me how i can approach here.

Thank you so much.

Sateesh

Edited by: sateesh kumar .N on Apr 28, 2010 12:42 PM

Edited by: sateesh kumar .N on Apr 28, 2010 12:42 PM

Edited by: sateesh kumar .N on Apr 28, 2010 12:46 PM

Edited by: sateesh kumar .N on Apr 28, 2010 12:54 PM

Edited by: sateesh kumar .N on Apr 28, 2010 12:55 PM

Edited by: sateesh kumar .N on Apr 28, 2010 1:13 PM

Former Member
‎04-28-2010 11:57 AM
0 Kudos

Hi madhu,

Sorry for my previous reply,

Kindly check below

Hi Madu,

Thank you for your message

I tried this format earlier itself,At that time i got error in RWB "Too many arguments" specified in stored procedure.

I don't get the idea how we can create the stored procedure if XI Send that format.

I tried to send below format also.I am getting same error like "too Many arguments" specified.

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

<ns0:POCA0013_SCMDB_RESPONSE_MT xmlns:ns0="urn:pg.com:POCA0013:sample">

<StatmentName>

<XI_SP_KANLOGDATA action="EXECUTE">

<table>XI_SP_KANLOGDATA</table>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-USA</PROJEKT_PROJKENNW>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-Denmark</PROJEKT_PROJKENNW>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-India</PROJEKT_PROJKENNW>

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<FM_NR isInput="TRUE" type="CHAR">SIK080280126498</FM_NR>

<FM_NR isInput="TRUE" type="CHAR">SIK080280126499</FM_NR>

<FM_NR isInput="TRUE" type="CHAR">SIK080280126500</FM_NR>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-19-08 00:00:00.000</ERSTELL_FM>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-20-08 00:00:00.000</ERSTELL_FM>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-21-08 00:00:00.000</ERSTELL_FM>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025962</BEST_NR>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025963</BEST_NR>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025964</BEST_NR>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00005</POSNR_KUNDE>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00006</POSNR_KUNDE>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00007</POSNR_KUNDE>

<POSNR_LIEF isInput="TRUE" type="CHAR">000500</POSNR_LIEF>

<POSNR_LIEF isInput="TRUE" type="CHAR">000501</POSNR_LIEF>

<POSNR_LIEF isInput="TRUE" type="CHAR">000502</POSNR_LIEF>

...........

......... 30 fields

</XI_SP_KANLOGDATA >

</StatmentName>

</POCA0013_SCMDB_RESPONSE_MT >

If i want to send the below format to database, How i can write the UDF in Mapping? Allready your know my source structure and target structure what i mentioned in first message of this thread.

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

<ns0:POCA0013_SCMDB_RESPONSE_MT xmlns:ns0="urn:pg.com:POCA0013:sample">

<StatmentName>

<XI_SP_KANLOGDATA action="EXECUTE">

<table>XI_SP_KANLOGDATA</table>

<PROJEKT_PROJKENNW isInput="TRUE" type="CHAR">O-USA;O-Denmark;O-India</PROJEKT_PROJKENNW>

<TV_KOLLO_PACKNR isInput="TRUE" type="CHAR" />

<FM_NR isInput="TRUE" type="CHAR">SIK080280126498;SIK080280126499;SIK080280126500</FM_NR>

<ERSTELL_FM isInput="TRUE" type="VARCHAR">2009-19-08 00:00:00.000;2009-20-08 00:00:00.000;2009-21-08

00:00:00.000</ERSTELL_FM>

<BEST_NR isInput="TRUE" type="CHAR">AH026456/203025962;AH026456/203025963;AH026456/203025964</BEST_NR>

<POSNR_KUNDE isInput="TRUE" type="CHAR">00005;00006;00007</POSNR_KUNDE>

<POSNR_LIEF isInput="TRUE" type="CHAR">000500;000501;000502</POSNR_LIEF>

...........

......... 30 fields

</XI_SP_KANLOGDATA >

</StatmentName>

</POCA0013_SCMDB_RESPONSE_MT >

If i passed above format to database, How much max char string acceptd for stored procedure parameter?

Please guide me how i can approach here.

Thank you so much.

Sateesh

Edited by: sateesh kumar .N on Apr 28, 2010 12:58 PM

Edited by: sateesh kumar .N on Apr 28, 2010 12:58 PM

Edited by: sateesh kumar .N on Apr 28, 2010 1:00 PM

Answers (2)

Answers (2)

Former Member
‎05-26-2010 7:00 AM
0 Kudos

There is other possible to do this scenario.

the possiblities are:

in Message Structure we took two Statements one statement for Insert and another statement for Stored Procedure.

In this my present scenario stored procedure is required for creating the Log table records.So i create the stored procedure structure in the same message type

Thank you

Sateesh

Show replies
Show replies
VijayKonam
Active Contributor
‎04-05-2010 10:58 PM
0 Kudos

You have to modify your stored procedure so that it can take an entire XML as input. Then with in the SP, use the XML as data source to retrieve records and insert them in to DB. In this case, you have to manually create any XML in the mapping and put it in the Stored Procedure input parameter. The XML structure needs to be decided between both PI and SQL developers.

This is one of the works around to achieve it.

VJ

Show replies
Show replies
Former Member
‎04-27-2010 7:27 AM
0 Kudos

Hi Vijay,

Thanks for your reply,

How i can create custom XML string for all the records?

Currently i am using message mapping here.how i can approach here.

Thank you.

Sateesh.

Edited by: sateesh kumar .N on Apr 27, 2010 8:27 AM

Ask a Question