Process Improvement using PI receiver JDBC adapter

[Former Member]

Hi Experts,

I am using PI 7.4. The reuirement is MDM to Teradata load using PI. I am using MDM adapter as sender & JDBC adapter as receiver. MDM is sending one record in one file & PI has to send each file in 8 tables in Teradata. Daily, 4,000 records (files) will be sent from MDM.

I have done the Configuration in PI as below:

Target Data type:

<StatementName1>

<db_tableName action="insert">

<table>table1</table>

<access>

<col1>value1</col1>

..

</access>

</db_tableName>

</StatementName1>

<StatementName2>

..

</StatementName2>

.

.

<StatementName8>

..

</StatementName8>

Receiver Channel Configuration:

maximum Concurrency:1

Auto Commit Enabled(No Transaction Level): Yes

Using the above configuration, PI is able to upload the data in Teradata. But the problem is: Teradata DBA is saying that this PI load seems to cause additional issue in the database because a very high count of session from PI system has been connected to teradata system. As a result, DBA.AccLogTable where access logging is saved, is getting full. They have shared the log on hourly basis. Please see below:

I am wondering, I am using only "INSERT" operation using "PI1LOAD" user why this "Select" statement is getting executed. Also, please advise how to improve this. Do I need to change any configuration in PI?

Thanks & Regards,

Moumita

[RaghuVamseedhar]

Moumita,

Before executing 'insert', DB is keeping locks on records ('select' statement).

Please try Transaction Isolation Level - 'read_uncommitted'.

Configuring the Receiver JDBC Adapter - Advanced Adapter Engine - SAP Library

[Former Member]

Hi Raghu, 

If I select "read_uncommitted", do I need to select "Database Auto commit Enabled" checkbox?

Also, as per my understanding, in the current scenario, JDBC adapter operates as follow:

1. MDM is sending the files one by one.

2. PI is picking one file and then opens one connection in the database.

3. PI writes the file in the DB.

4. Then PI closes the connection to Teradata.

5. PI again picks other file and starts processing in the same way.

Is there any option available where PI just creates one connection to the DB & send all the individual files to DB which are in "Delivering" queue in PI and close the connection to DB?

Please advise.

Thanks & Regards,

Moumita

[RaghuVamseedhar]

Moumita,

Default behavior of PI JDBC channel is to keep connection open.

"If you want the database connection to be released and reestablished before each Poll Interval , select Disconnect from Database After Processing Each Message ."

Configuring the Receiver JDBC Adapter - Advanced Adapter Engine - SAP Library

[iaki_vila]

Hi Moumita,

I am wondering, I am using only "INSERT" operation using "PI1LOAD" user why this "Select" statement is getting executed.

I think the problem could be in the DB itself. The tables can have triggers to check the integrity of the data received doing SELECTs in other tables.

Do I need to change any configuration in PI?

If the problem is the issue that i've commented you can't do anything in PI, even if the DB developers make a stored procedure the performance problem can persists.

I think you should talk with the DB administrator about the tables and if they have any special feature that raises extra SELECTs.

Regards.

[azharshaikh]

Hi Moumita,

Max Conc: 1 should limit the connection to 1 at any given time for that CC.

Please check if :

1. There is another Channel from PI doing Select operation (for some other Interface)

2. Is there any other / multiple channels connecting to this DB from PI

Regards,

Azhar

[Former Member]

Hi Azhar,

Currently, two communication channels from PI are connecting to the database. Both these two channels are doing only "INSERT" operation.

Is this "Select" operation is happening because of "Database Auto Commit Enabled (No transaction handling)"? I have selected this as "Yes".

Please advise.

Thanks,

Moumita

[pvishnuvardan_reddy]

Hi Moumita,

If the load on the database server is huge because of this, then can you try the option of using stored procedure instead of PI system acting directly on the database.

Also, refer the below blog about increasing the performance of receiver jdbc channel:

XI/PI File and JDBC Receiver Adapter performanc... | SCN

Regards