JDBC Connection error

[Former Member]

Hi Gurus,

I am using File-XI-JDBC interface connecting to Oracle database. The first message (after a gap of more than 8 hours) to JDBC is failing with the following error message. After the first message, all the other messages are inserting successfully. The JDBC connection session has problem and it seems that the session is crashing for the first message and after that the connection is establishing successfully. How to resolve this issue? It looks like this is bug using Oracle database. The Oracle database version is 10g. Any help is appreciated in this regard.

com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'xxxx.xxxx' (structure 'Insert'): java.sql.SQLException: No more data to read from socket

Thanks

Kalyan

[bhavesh_kantilal]

Known issue.

In the receiver JDBC adapter, select option --> Disconnect from Database after each message processing and you will no longer face this issue.

Regards

Bhavesh

[Former Member]

Bhavesh,

Thanks for the reply. That advanced option works better. If I am using this option, the JDBC session stays for 2 minutes (?) and inserting the records with same first field at a time into the table. I am using EOIO for the sender messages. For some other reason I cannot use it. Is there any other solution than this?

Thanks

Kalyan

[bhavesh_kantilal]

Apart from That option no other option I am aware of.

Take a look at this blog to deal with EOIO message stuck in the AF,

/people/stefan.grube/blog/2006/04/27/how-to-deal-with-stuck-eoio-messages-in-the-xi-30-adapter-framework

Regards

Bhavesh

[Former Member]

Bhavesh,

Thanks again. The EOIO is for the sender file adapter. I have seen this blog before.

As per the theory, with out that advanced option, the JDBC should establish the connection. I think this is a bug in oracle database crashing the connection if it stays for longtime with out an activity. The session should be disconnected after certain time and reestablish when there is a message.

Kalyan

[bhavesh_kantilal]

Kalyan,

Because the QOS is EOIO in the Sender File Adapter the QOS will continue to be the same for the Receiver JDBC adapter as well.

Your diagnosis of the problem is correct. Oracle has turned of the connection due to connectivity issues / session timed out.

Regards

Bhavesh

[prateek]

I think ur problem is discussed here

SAP Note 791284

Regards,

Prateek

[Former Member]

Prateek,

Really appreciated your reply. But this note is specific to XI 3.0. We are having PI 7.0 SP09. I didn't see any note for this version. Is it the same for 2004S also.

Thanks

Kalyan

[Former Member]

Yes Kalyan.

---Satish

[bhavesh_kantilal]

Kalyan,

Dont think this note is going to be relevant to Pi SP 9. This should have been fixed in PI SP 9.

You would need to conider using the Disconnect from DB option in the JDBC adapter. Reason is that if the Database is actively closing the connection, the first message afer that will always error out in XI. You would need to manually activate the adapter once again for the connection to be re-established.

If this is a repeated issue, use this option. If no , then you can ask the DB team to let you be informed of Shut Downs etc in Advance so that manual restart of adapters can be done.

Regards

Bhavesh

[Former Member]

Bhavesh,

I agree with your solution. Is there a way that we start and stop the communication channels automatically (kind of scheduling)? That would resolve the issue also. The reason for my issue is that the target DB restarts everyday at 4.32 am as part of cold back up which is causing the connection on DB lost. If I have a way of start/stop the communication channels, I would schedule a job around 5 am.

Anyways, thanks much for your participation and help.

Kalyan

[Former Member]

yes use adapter sceduling from SP19 xi 3.0 . otherwise use setting in visualadmin to reconnect automatically after connection is failed before trying for frist time.

By setting this property to false we can force Adapter to check the connection for every message being sent to the end system

[Former Member]

Hi Kalyana,

There is a option in Communication Channel Monitoring where you can specify the time when the adapter should start and stop.

Inyour case filter out the relevant adapter and do the necessary configurations.

Not sure from which SP this feature is available but itz available in PI 7.0 SP10

Regards,

SUmit

[prateek]

Check whether the sequence of the parameters and there names in XI is similar to that of the Stored procedure.

Also check the field types

Have a look at the connection settings of your JDBC adapter and also at the note 831162

Regards,

Prateek