Stored Procedure

[Former Member]

Hi,

we have the Requirment like this

Business Requirment :

1. We have a Third party system (Seq Server) in that we have Distributers Data (Nearly 2,80,000) Records

2. XI Needs to Select Those Records and Send Those To ECC. (This is One time Job ,After That For Every Day 2000 Records Needs to Send to ECC)

Technical Info :

1.Seq Server having having a Table with 11 field of Distributers Data along with status Field ( Status Field 0 only XI needs to Select, than After Selecting XI Needs to Update Status as 1)

2.we have 11 Branches in a Table we Need to Select the tables Based on the Status (if status is' 0' ,XI Need to Select the Records and need to update status '1')

For that we need Stored Procedure

Regards

syamkumar

[former_member192343 · Accepted solution]

and what is the question?

sorry

[Former Member]

Hi syamkumar,

You are in the right direction.. i understood your need.. you need to write the <u>stored procedure</u> for that...exactly that is correct..

<b>

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

it will be helpful for your...

<b>/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

please go through this also..

You need to use T<b>OAD for MS SQL tool</b>...its was interesting concept ..

i am also doing the same concept..my scenario will be like.. IDOC-XI-JDBC...

Regards

kumar

[Former Member]

Hi,

You can use sender jdbc adapter for SQL server and receiver as RFC or proxy what ever you are comfortable with.

Thanks,

Prateek

[Former Member]

Prateek Shah ,

Thank you for your quick response

We have developed scenarion Using JDBC ->PROXY (Using BPM)

But

XI is selecting 2,80,000 it is comeing in to SXMB_MONI and we can Check into ECC SXMB_MONI

In ECC SXMB_MONI if we go and check it is Giving Green Flag and if we go and check in QueueID it is giving Information SYS FAIL

my assumtion is that

when Data(hole 280000 Records,size of XML Message 33MB) comeing in to ECC

it connect able to send hole data to proxy Program

inside proxy we calling one program it is taking time to insert the data(we can not change program) , that program can insert data 15000 Records.

wht XI need to do is we need to select 15000 Records Based on Branch Wise giving small time intervels.

for this is it possible send data with time intervels using Stored procedures.

is there any alternative way?

Regards

prasad

[Former Member]

Hi prasad

Why dont you set a limited pagesize while retrieving from the sender DB

Regards

krishna

[Former Member]

Prasad,

Instead of changing any thing on XI, why dont you pass only 15000 records at a time from your proxy to program(which you said can not changt but you can change the proxy). So in the proxy select 15000 records first & then call program & do this for all the data which you get from XI.

I mean is you process the data inside proxy in the batch of 15000 records.

Regards

Sushil

[Former Member]

Hi Krishnamoorthy

Than You For your Response

Why dont you set a limited pagesize while retrieving from the sender DB ?

How Can we do this

Regards

prasad

[bhavesh_kantilal]

Better option would be to filter records on the sender JDBC adapter itself. In the select and update query make sure that the number of rows selected are in chunks of records so that you do not have a perfromance issues.

Ask an SQL expert to make the change in the select query so that there are only chunks of rows selected every polling interval and the same rows are updated in the update query.

Regards

Bhavesh

[Former Member]

Hi,

Sushil

How we can do this is there any document.

Regards

prasad

[Former Member]

Prasad,

I dont have any document but it can be achieved simply using ABAP coding.

You might be geting data in some table which you take from Proxy interface. You can try something like:

data: count1 type i value 15000,

count2 type i.

itab[] = proxy_interface-table[].

describe itab lines line.

temp = (line / 15000) + 1.

do temp times.

count1 = count1 + 15000.

count2 = count1 + 15000

append lines of itab count1 count2.

enddo.

This code is not completely correct but just try this & change few lines & i feel you can do it bcoz I faces similar problem & using same approach i was able to achieve processing in lots.

Regards

Sushil

[Former Member]

Hi-

Check this thread and the links i have posted there,may be helpful to you.