Getting error in Informatica SQL30081N in a Source qualifier after fetching 11424 rows

Question

We are getting below error in an Informatica workflow. The same is running seamlessly in pilot environment, issue is in production.

After 11424 rows are selected this error is thrown from z/Os DB2. This source qualifier query if run separately fetches 2.6 million rows. The buffer is set to Auto in the Informatica session properties.

SQL Error [
[IBM][CLI Driver] SQL30081N  A communication error has been detected. 
Communication protocol being used: "TCP/IP".  
Communication API being used: "SOCKETS".  
Location where the error was detected: "<server-ip 11:22:22:22 format>".  
Communication function detecting the error: "send".  
Protocol specific error code(s): "32", "*", "0".  
SQLSTATE=08001
sqlstate = 40003 

Database driver  error...
Function name  Fetch
Native error  code = -30081

This is expected to be run successfully as we have not made any changes to this workflow. This started failing a few weeks back.

I did some reading on this error and what I could find said this is due to connection blocked by DB2 firewall. If this is the case why only one select statement is failing while other source qualifiers are running successfully?

EDIT your question to add missing information (1) what is in the Db2-server diagnostics file for seconds leading up to the timestamp of the error (2) what is the Db2-server operating-system (z/os, i-series, linux/unix/windows) . Your question is not about programming, but about troubleshooting, and only your site has the diagnostic data. — mao
I do agree that this is more of a trouble shooting. My idea was to get some insights from someone who might have already faced a similar issue and could give me some pointers on how to get around this issue. — Peter Gerald
Please also include information whether rows were fetched on by-one or e.g. there could have been a long pause in fetches. If the latter, then we shall look also into different TCP/IP parameters. — kkuduk
@PeterGerald 3 possible causes: - TCP/IP timeout - WLM threshold that forces the connection automatically - some Db2 problem (there was a recent APAR with Db2 MPP and columnar tables that could cause it) this is why we do need more details about the database. — kkuduk
@kkuduk DB2 Server is running z/os . I have added more information to the query. — Peter Gerald

hidehy hidehy · Accepted Answer · 2019-11-11T08:57:10

According to the Db2 manual below, it it typically outside the Db2 software:

Protocol-specific error codes for the SQL30081N message
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.messages.doc/doc/r0052008.html

The cause of the SQL30081N message is typically outside the Db2 software and actual error codes are protocol-specific.

And here is a documented link for error number 32 :

TCP/IP errors
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.messages.doc/doc/r0058740.html

Table 1. UNIX TCP/IP error numbers

errnos  AIX error number    Linux error number  Description

   :

EPIPE   32  32  Broken pipe.

So generally speaking, it is suggested to check TCP/IP level.

Hope this helps.

Getting error in Informatica SQL30081N in a Source qualifier after fetching 11424 rows

1 Answers