How to Handle SQLCODE: -904 Error (JDBC Connector to DB2 for z/OS)

[Chris]

I have a long-running AL that includes a connector to a z/OS instance of DB2. One of the tables I'm connecting to is refreshed in the wee hours of the morning, so it disappears for a short time. Because of the time it takes the AL to run, I can't simply schedule it to run during a time period that avoids the temporary unavailability of the table.

When the table goes away, the AL is failing with:
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -904, SQLSTATE: 57011, SQLERRMC: 00C90086;00000200;DDVSEC2D.GDVSEC59

I already tried selecting options on the "Connection Errors" tab to no avail. I selected to Retry on Initial Connection Failure, Auto Reconnect on Connection Loss. And added to retry up to 30 times with 10 seconds between each retry. These options aren't even being exercise--he script is humming along, and then it fails immediately--in a way that makes me believe the options on the Connection Errors tab aren't even being considered.

Can anyone offer any coaching that could help? Do I have to do something special to recognize this particular error, perhaps?

Cheers,
Chris

[Eddie Hartman]

Post by Chris
I have a long-running AL that includes a connector to a z/OS instance of DB2. One of the tables I'm connecting to is refreshed in the wee hours of the morning, so it disappears for a short time. Because of the time it takes the AL to run, I can't simply schedule it to run during a time period that avoids the temporary unavailability of the table.
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -904, SQLSTATE: 57011, SQLERRMC: 00C90086;00000200;DDVSEC2D.GDVSEC59
I already tried selecting options on the "Connection Errors" tab to no avail. I selected to Retry on Initial Connection Failure, Auto Reconnect on Connection Loss. And added to retry up to 30 times with 10 seconds between each retry. These options aren't even being exercise--he script is humming along, and then it fails immediately--in a way that makes me believe the options on the Connection Errors tab aren't even being considered.
Can anyone offer any coaching that could help? Do I have to do something special to recognize this particular error, perhaps?
Cheers,
Chris

Well, if this a transient error you have a couple of options:

1. Either define this error as a 'connection loss' by configuring custom reconnect rules (https://ibm.biz/BdjiMY) and then enabling Re-connect on Connection Lost with enough delay and retries to get past the problem.
2. Or, code the Error Hook to sleep a bit and then try to re-initialize the Connector and retry the current Work Entry.
--- Default On Error Hook
// Lazy initialization of retryCount
if (typeof retryCount == "undefined") errorCount = 0;

// 100 retries
if (retryCount++ < 100) {
task.sleep(1000*60*10); // 10 min in msecs
// system.sleep(600) // does the same thing

try { thisConnector.connector.terminate() // terminate
} catch (excptn) { // swallow exception }

try { thisConnector.connector.initialize(null) // init
} catch (excptn) { // swallow exception }

// If the Connector is in Iterator mode, you also have to select entries.
// However, if the Iterator re-selects it will start reading from the start again.
// Connection Error tab has an option for a skip count on re-connect.
// If the data is returned in the same order on each selection then this should work fairly well.

system.retryEntry(); // Have this Connector try again with the current Work Entry
}
---
I have not tested this myself, but it should work. In theory :)
Please share any discoveries!
-Eddie

[Chris]

I altered a little bit--mostly to add some logging output, but, added to the Default On Error hook, this works. Thanks for your help, as always, Eddie!

theconn = thisConnector.getName();
task.logmsg("Error for "+theconn+" Connector. Will attempt reconnection");

// Lazy initialization of retryCount
if ( typeof retryCount == "undefined" ) {
task.logmsg("Initializing retryCount");
retryCount=0;
}

retryLimit=30; // Number of times to retry connection
retryDelay=1; // Delay between retries--in minutes
task.logmsg("Retry count is "+retryCount);
if (retryCount++ < retryLimit){
task.logmsg("Sleeping for "+retryDelay+" minute(s)")
task.sleep(1000*60*retryDelay); // converts minutes to milliseconds
// system.sleep(600) // does the same thing?
try { thisConnector.connector.terminate() // terminate
} catch (excptn) {
// swallow exception
}
task.logmsg("Attempting connector re-initialization");
try { thisConnector.connector.initialize(null) // init
} catch (excptn) {
task.logmsg( "Re-initialization of "+theconn+" Connector failed: " + excptn )
}

// If the Connector is in Iterator mode, you also have to select entries.
// However, if the Iterator re-selects it will start reading from the start again.
// Connection Error tab has an option for a skip count on re-connect.
// If the data is returned in the same order on each selection then this should work fairly well.

system.retryEntry(); // Try again with the current Work Entry
}