0

We have a huge system which use DOS batch and VBScript to access to IBM DB2. In the system, we use ADODB.Command.CommandTimeout property.

Our client request that they want to know whether it is because of timeout or not when they have an error in the batch process. We assumed that there must be an error code(Err.Number) for timeout, researched log files of timeout error and found that there are at least two pattern of error code.

Pattern 1: Err.number = 3712
Pattern 2: Err.number = -2147467259

Not sure is there any other patterns of timeout error code. It seems that system simply cancels the connection when it hits time limit and returns error caused by the canceling, not caused by timeout itself. When we have an error in the batch process, we would like to detect whether it is caused by timeout or not. Is there any way to detect?

(Edit)Here's a code where we execute SQL and handle errors

'timeout setting
oAdoCommand.CommandTimeout = ENV_DB_TIMEOUT

On Error Resume Next
    'Execute
    Set oRs = oAdoCommand.Execute
    If Err.Number <> 0 Then
        'Error handling (We'd like to detect timeout around here)
        Call FN05MakeWriteLogMsg("FN01E0103", "E", MSG_FN01E0103, Empty, 1, sLogMsg) 'Function to edit and log error message
        Set oRs = Nothing
        Set oAdoCommand = Nothing
        Exit Function
    End If
On Error Goto 0

Regards,

miurat
  • 1
  • 1
  • 2
    https://stackoverflow.com/q/2977913/11683? – GSerg Jul 16 '20 at 08:50
  • @GSerg does SQL Server timeout error codes apply to DB2? – user692942 Jul 16 '20 at 10:09
  • 1
    Can you edit your question to show the typical code fragment for error handling in Vbscript, and how that code returns exit-codes to the calling batch files. – mao Jul 16 '20 at 11:47
  • @Lankymart No. I looked over DB2 in the question, and there was no tag. The principle should be the same though. – GSerg Jul 16 '20 at 12:24
  • Thanks for comments, I added code where we handle error. We confirmed that DB2 errors were logged, when err.number is 3712, DB2 error was FN01E0102 (failed to close connection), when err.number is -2147467259, DB2 error was SQL0723N (an error occurred in a triggered sql). – miurat Jul 17 '20 at 05:01
  • 1
    Db2 will return specific and unique error codes for different kinds of timeouts. Additionally Db2 can associate 'reason codes' with specific 'sqlcodes' to further clarify the context of the error. For example, a lock timeout may have sqlcode -911 (with reason code 2 for deadlock or 68 for lock timeouts) or -912 or -913 (depending on the target Db2-server platform and other details) The provider should expose those real error codes (sqlcodes) to the application somehow. The application code should be able to find the true underlying sqlcode and react appropriately. – mao Jul 17 '20 at 08:24
  • 1
    Does this answer your question? [How to retrieve all errors and messages from a query using ADO](https://stackoverflow.com/questions/3013447/how-to-retrieve-all-errors-and-messages-from-a-query-using-ado) – user692942 Jul 20 '20 at 11:20
  • Hi ,thanks for your comment. It seems that I couldn't retrieve true sql error code regarding errors message that seems not to relate to timeout. I'll try to use code shown in linked post from Lankymart. – miurat Jul 21 '20 at 01:19

0 Answers0