Broken pipe error in Sync Send JDBC Receiver: Extremely Serious issue

[Former Member]

Hi forum,

I have a serious error in a JDBC->XI--> JDBC scenario,

Step1: I use a sender JDBC channel to select some distinct values from a table say X, and update the same rows,

Query SQL Statement: select distinct Branch from dbo.X where status = 0

Update SQL statement: update dbo.X set Status = 2 where Status = 0

Step 2: enter BPM

Step 3: enter a loop

Step 4:make a synchronous call to the same table X, to fetch some records,

SQL query: select * from X where status = 0 and Branch = i; Update X set status = 9 where Branch = i

Step 5: close loop, this loop iterates for 5 or 6 times depending on i, which is no. of branches selected by the sender JDBC channel

so all the calls are to the same table, same databsse(SQL Server)

the problem is when this sync calls happens in the loop, it some times gives following error, which i se in RWB:

<b>Error Unable to execute statement for table or stored procedure. 'X' (Structure 'STATEMENT_X') due to com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.transmit operation. Exception:Broken pipe (errno:32). Context:(2217)</b>

i have no way to trace the cause of the error, the database is perfect and the error logs in the SQL server shows no trace of any error,

Note: It works sometimes

i dont know whom to approach, pls help

the issue is very very serious as its in production system,

[Former Member]

Sudeep

I used to get this " Broken Pipe " error when I was working with TIBCO components. The scenario was same like yours.

In our case we created the tkt to TIBCO & they suggested a solution to re-start the components like message broker & ADB ( JDBC adapter ) till the time they will come up with some permanent solution and after the version update in Tibco we never faced this issue again.

I suggest you to create a Tkt also to SAP .

- Lalit -

[Former Member]

Hi lalit,

could u please describe ur answer once again, i didnt get it properly,

pls

[Former Member]

Hi Sudeep,

SAP Note 846079 - XI 3.0 JDBC Receiver: # of Retries on SQL Error w/o Effect

Please check the following SAP note also - 831162

Some info which i found there and can be relevant in to your scenario.....

Q: I am using a JDBC Receiver Adapter in conjunction with the Lotus Domino Driver for JDBC perform an INSERT or UPDATE operation on a database. When sending a message to the receiver, the Adapter Monitoring shows the following error message:

"java.sql.SQLException: [Lotus][Domino Driver for JDBC]Invalid cursor state"

Is there a fix for this issue?

A: To work around this JDBC driver problem, activate the Advanced Mode for the respective JDBC Receiver channel and configure the setting "Number of Retries of Database Transaction on SQL Error" to a value > 0. Additionally, make sure that the setting "Database Auto-Commit Enabled" is also active as the Lotus Domino Driver for JDBC does not support transactions.

Apply note 846079 before configuring this scenario.

Q: The TCP/IP connection to my database host is running over an unreliable network connection, i.e. the connection is sometimes interrupted. Consequently, I sporadically receive an SQLException regarding a closed connection in the system trace or audit log or the connection as well as the JDBC Adapter channel are hanging.

How can I work around this connectivity issue?

A: Enable the "Advanced Mode" for the respective JDBC Adapter channel and select the option "Disconnect from Database After Processing each Message".

Note that this might put additional load on your DBMS due to the creation of a new database connection for each message.

I dont have access to a database right now, so i am not able to check this.

Also check the JDBC driver compatability as mentioned in the above note.

Cheer's

[Former Member]

it looks like that sql statements of XI system are not getting transferred properly sometime in this case. check the drivers installed for your database.. may be you replace the drivers and check the system...

[Former Member]

you can set the parameter LofSQLStatement to see the log and try to analyse the problem further.

But looks like your getting this problem because the connection to the database is broken. So, try by setting the parameter below to a value say 3 or higher, the default value is 1.

Number of Retries of Database Transaction On SQL

by increasing this parameter, the adapter will try to re-establish the connection and execute the query 3 times before raising an error.

Cheer's

[Former Member]

Hi dreamcatcher,

<<So, try by setting the parameter below to a value say 3 or higher, the default value is 1.>>

do u mean to say, i should go to the JDBC receiver channel-->Advanced Mode>Additional parameters--->Number of retries of database transaction on sql error

Note that this channel is used for synchronous communication, will retry by the channel happen in that case

[Former Member]

yes, it should execute it again irrespective of it being sych or asynch.

Cheer's

[Former Member]

can u pls tell me what is the default value of retries it takes if i dont specify this option

[Former Member]

default value is 1

[Former Member]

Dreamcatcher,

i tried increasing the rerty level to 5,

but it never happened, it happend only once, no retry,

Note: this is Sync send to databse

see i even tried changing the retry level of a JDBC receiver channel in case of a asynchronous case, there it did for 3 times irrespective of me giving 5 times