JDBC sender: SQL query Issue

[Former Member]

Hello All,

Can you please let me know that the SAP PI Sender JDBC channel will able to call the below select Query. if not suggest me how to write the Query for my below requirement.

QUERY:

select country, orders_count from ORDERS where country='UK'

union

select country, orders_count from ORDERS where country='GE'

if i place the above query in my Sender JDBC channel, it returns only the output for first query. please let me know will the JDBC channel allow to call the UNION in select query.

My Actual Requirement:

Scenario: DB to DB through SAP PI (i need to use PI for Data upload...i know we can do without SAP PI. but my requirement is to Use SAP PI )

i have 3 DB tables at Source Database and i need to call few fields from each table and insert those records to target Database table.

please provide me some sameple Query's where SAP PI can allow to execute at runtime.

Thanks in advance....SARAN

[Former Member]

Hi Saran,

Try using Joins in SQL like select a field1, a.field2, b.field1, b.field2 from table1 a, table2 b where a.fieldnameX = "someValue" and b.filenameX="someValue"

Using multiple select statements from sender jdbc adapter would result in successful execution with multiple datasets and in PI it can read only the first dataset returned and not the subsequent. The same holds true if you call a Stored Proc with multiple statments.

Regards,

Suddhasatta

[former_member182503]

Hi Saran,

In your example scenario you can use JOIN to retrieve the data that you need.

Also, depending on your DB Vendor (Oracle, MSSQL, DB2, etc) you can handle several SELECT statements using STORED PROCEDURES and TEMPORARY TABLES, merging all datasets into one temporary table and in the end, selecting all data from the temp table.

Best regards,

JN

[Former Member]

Hi JN,

My observation with Stored Proc in the sender side is that, if we use multiple selects in a single transaction on DB the resultset returned from the first statement is only considered. Even if there is a Update statement before the Select statement in your Stored Proc, PI cannot see the return result set from Select, as the first returned value from DB processing is "n rows updated" output of Update Statement.

What's your opinion about this?

Using the Join would be the most feasible option in this scenario.

Regards,

Suddhasatta

[Former Member]

Thanks for the Quick reply.

it seems that the Query is the Problem. if i wrote simple select query, i am able to fetch the data but while using complex queries...i am getting above problem.

Now the problem is ....my JDBC Sender channel shows status as "Processing started" for longtime(more than 20min.) in runtimeWorkBench.

i did not understand why the channel is in such a status for long time even though i configured the Polling Interval as 600 seconds....

please suggest me how to debug teh above channel status issue.

Best Regards...SARAN

[baskar_gopalakrishnan2]

>>please suggest me how to debug teh above channel status issue

Manually stop and restart the channel one time. Also run the query one more time and see how that helps.

[Former Member]

Hi Baskar,

I have done that ....4 times....but channel shows same status..."Processing started". evenif i stop teh channel entry was not comming as " Polling interval started. Length: 600.0 seconds"

4/1/11 9:55:22 AM Processing started

4/1/11 9:50:28 AM Processing started

4/1/11 9:31:12 AM Processing started

4/1/11 9:09:37 AM Processing started

4/1/11 9:04:19 AM Processing started

please suggest how to proceed further. even though plloing interval given in channel as "600 sec"...the channel still shows the same status ..."Processing started" for more than 30 min...

Best Regards..>SARAN

[baskar_gopalakrishnan2]

Saran,

YOu might want to check SAP Notes regarding this issue. Can you do one more thing? Go to advance tab of the jdbc sender and check the option "Disconnect from database after processing each message". That should take care your problem.

Hope that helps

[Former Member]

Saran,

Did you check the receiver channel, my guess is there is a error i that and the sender is retrying it after some interval and hence the delay.

Make the sender channel inactive, cancel all the previous retrying message to avoid confusion and then make the sender channel active again.

Regards,

Kinshuk

[Former Member]

Thanks.....Baskar

One Quick Question for you...can you please let me know...suppose if i wrote a select qurey to fetch data of 5000000 records,

will it handled by our JDBC sender channel... for this how much poll interval i need to enter in channel parameters and also is there any other place we need to do settings(Visu.Admin/channel settings at admin level/) to handle such a huge select query in our channel.

or if any other possibility ...in jDBC sedner channel please let me know

Best Regards..>SARAN

[baskar_gopalakrishnan2]

Writing select query for the large volume of records return is not good practice. Try to do in smaller chunks.

Example: You have to select 1000 records from the table using sender jdbc. What I would do ten times as follows.

A) In Select statement... select * from table where rownum < 100 and read_record="N" ;

B) in Update Statement update a field in the table like boolean from false to true or yes to No.

Example: Before you configure this scenario, create a column like read_record of type varchar2 or char of size(1) and default value is N. in the update statement , update the column for those 100 records read in the A) step as "Y".

This way you can reread the same table for every 100 records sequentially and make your message size same all the times.

[udo_martens]

Hi Saran,

the statement should work from PI point of view. Test the statement before in your database. PI does not care about the statement, it is just forwarding the SQL.

Regards,

Udo

[stefan_grube]

Try the select statement with a tool on your DB directly.

The statements can be different depending on database

search for a db forum, you find tons of examples for select statements.