JDBC to Idoc scenario

[Former Member]

Hi Experts,

     I am very New to DB2 scenario's...

     I have a scenario like, i need to pick data from 4 Database tables in a single shot and post to SAP in the form of Idoc. I am done with design part and i am strucked on the configuring part.

--->1.  As i can see  "Query Sql statement" & "Update Sql statement" on the sender Comchannel, what we need to give there?

--->2.  If stored procedure was not provided by DB people, how we can pick data from tables in a single shot to PI and post the Idoc?

As i created my sender_DT is like this...

row

  Table1

      ---

      ---

      ---

  Table2

      ---

      ---

      ---

Table3

      ---

      ---

      ---

am i correct or do i need to change anything here.

could you please help me (Screen shots) on your valuable suggestions.

Thanks in Advance.

Kumar

[naveen_chichili · Accepted solution]

Hi Dileep,

You can do the query to join the tables and fetch the data from the database. I would be better if you get a stored procedure from your DB guys.

Once you fetch the data from your DB table then you need to update the table in order to avoid duplicates in the data.

Select Statement--> used to fetch the data from DB Table

Update Statement --> to Update the table.

Thanks,

Naveen

[manoj_khavatkopp]

Hi,

You can write a inner join SQl query to retrieve data from all 4 tables at one shot.

And note for Update SQL query ot updating the flag , if its oracle DB inner join with Update query dosen't support so you need to go for sub-query.

Br,

Manoj

[Former Member]

Hi Dileep,

If you can't get a stored procedure, ask your Db people to create a view that contains the data from your 4 tables. Remember you will also need a flag field that you have to update. so next time your pulling data you can select only the records that are not read before.

Your XML will look like this (so you need to adjust your Repository objects:

<resultset>

<row>

<columnname1>value<\columnname1>

<colmunname2>value<\columnname2>

<\row>

<row>

...

<\resultset>

Kind regards

[iaki_vila]

Hi Dileep,

--->1.  As i can see  "Query Sql statement" & "Update Sql statement" on the sender Comchannel, what we need to give there?

In Query SQL Stament the SELECT to get the value. Pay attention that you will need a field with a flag to know what registers are been taken and what registers are not taken

In Update SQL statement you will do an update of the flag field to avoid the same register will be taken.

--->2.  If stored procedure was not provided by DB people, how we can pick data from tables in a single shot to PI and post the Idoc?

You will need to do a join between the tables.

Regards.