Receiver JDBC structure

[former_member189441]

Hi

Scenario: File-JDBC-File

  MT_sender                               Mapping                                      MT_Receiver
  Record (1..1)                     Field1 to statement                         Statement    1..Unbound
   Field1(1..unbound)                                                                    TableName
                                                                                Action   ( SELECT )
                                                                                Access
                                                                                Field2                                                                                
Key               1..1
                                              Field1 to Field1                                         Field1       1..1
 

in above case statement_response will be occurence 1..unbound..as we are executing multiple queries.in my case field1 in sender side are upto 400 values.then in that case is it better to do as option-1 or as below

  MT_sender                               Mapping                                      MT_Receiver
  Record (1..1)                            no mapping                             Statement    1..1
   Field1(1..unbound)                                                                    TableName
                                                                                Action   ( SELECT )
                                                                                Access
                                                                                Field2                                                                                
Key               1..1
                                              Field1 to Field1                                         Field1       1..unbound
 

in this case the single query executes like select field2 from table where (field1=1 or field1=2.........field1=400).

Which is better option

other option is write a query using DML operation using place holders.But can we give multiple values in place holders as input to the query using IN operator.. can we do like this??

Thanks

Edited by: murali krishna on Apr 9, 2010 3:19 PM

[former_member189441]

Hi Experts

Anybody agree with sushil.

I dont think it will generate 400 queries if we repeat Key tag...hi raj and ankesh confirm the same.

Thanks

[Former Member]

Hi,

Please enable logging & check. It would help others as well..!

Regards

Sushil

[Former Member]

Hi Murali,

To my understanding repeating the <Satement> tag should generate 400 queries.

For <Key> it will fire one query with 400 where conditions.

Also, for benefit sake could you describe your entire end-to-end scenario?

[former_member189441]

Hi Ankesh,

Yes, I mean to say repeating the key tag only.

Actually i am trying to retrieve data from 2 different table based on set values from input using 2 different <statement> in receiver strucure.

if i go for option-2 then i can get two statements with 400 rows each statement...i mean i can differentiate which response is for which request..

if i go for option-1 as response it self will repeat..i think it is difficult to differentiate 2 tables responses..

Raj

i checked with database guys for best query to execute this..they prefered query using IN operator..is there any way to design our receiver structure for IN operator ( i think we dont have that IN compartion oparator in out standard list)

or if repeating key will not lead to any performance issues for 600 records also..then we can for that..

Thanks

[Former Member]

Hi Murali,

Repeating Key statment or repeating statement would generate 400 select statements (assuming there are 400 values for key) at runtime (You can see if you enable seeting to see statement which will be fired by PI on DB).

As far as I know, there is no standard way by which IN operator can be supported however you can write a JAVA mapping which will generate the statement for you & it will be executed on DB (similar to DML).

Even SAP help doesnt support IN operator which you can check at bottom of the below link

http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Regards

Sushil

[justin_santhanam]

Murali,

I would suggest you to go ahead with Option 2. If you use Option 1- then you will get 400 statement_response, if you choose Option -2 then you will get one statement_response with 400 rows.

YES - you can use DML and it can has multiple values in the IN operator. If your volume size is too big I won't prefer using the DML. I personally faced lot of issues where I have to reframe the structure to either Option 1 or Option 2.. I'm not saying DML won't work- it will throw some error(like unparseable character ') and when you resend it will go through.

It's my opinion. Wait for other experts opinion too.

Raj.

[Former Member]

Hi Murali,

As I get it option 2 with Key 1..1

Field1 1..unbound

should be used.

However, I feel that Key should have multiple occurrences and not field1. Because,

<StatementName4>

<dbTableName action=u201DSELECTu201D>

<table>realDbTableName</table>

<access>

<col1/>

<col2/>

<col3/>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

this structure results in (col2 AND col4) OR col2

</dbTableName>

</StatementName4>