Skip to main content
Delphix

Masking DB2 Connector Errors (KBA1021)

 

This document describes common ERRORCODE, SQLCODE, and troubleshooting techniques when Masking IBM DB2 databases. 

The Troubleshooting sections are:

Troubleshooting DB2 Connector

The first level of the error message is detailed in the logs. They are accessed from two different places:

  • Application Logs - Main Screen
  • Job Logs - Monitor Page

For additional information please set the diagnostics on DB2 and check db2diag.log.

  • set DIAGLEVEL to 4
  • Check the db2diag.log

DB2 Error Code Formats

In this document, there are two different error message formats, ERRORCODE and SQLCODE. These two formats are structured in two or three components:

  1. ERRORCODE=error-code or SQLCODE=sql-code
  2. SQLSTATE=state-code,
  3. (optional) SQLERRMC=sql-error-message-code

Documentation:

Troubleshooting Steps 

The first step in troubleshooting is to look at the logs:

  1. Open the masking logs
    • Get the ERRORCODE or SQLCODE
    • Get the SQLSTATE
    • Get the Step returning the error
    • Check additional information such as executed SQL  and additional errors
  2. Check if the ERRORCODE or SQLCODE is detailed below.
  3. If additional information is needed or the error is not detailed below, search IBM DB2 documentation on the Web.
  4. If further information is needed, set the diagnostics level to 4, rerun and check db2diag.log.

DB2 ERRORCODE Summary 

This section describes the reason and actions for the most common errors when masking DB2 databases. The errors below indicates that the Masking Engine can communicate with the DB2 database,

If an error is not in the list below, please follow the steps 3 and 4 above in Troubleshooting to find more information. 

ERRORCODE=-4214, JDBC failed to connect to the database 

The JDBC failed to connect due to authentication method not correctly set. 

DB2 Error Details and Format 

The error message says "Connection authorization failure occurred.  Reason: Local security service non-retryable error.".

There are two components to this error:

  1. ERRORCODE=-4214,
  2. SQLSTATE=28000  "Authorization name is invalid."
Troubleshooting 

The database authentication method needs to be checked.

From IBM the suggestion is to check the database authentication configuration using: 

db2 get dbm cfg | grep AUTHENTICATION 
Masking Example 

This error is not related to any specific masking step or specific SQL statement. The example below is an error in an Update step after the connection was closed in the Table input step.

Exception
[jcc][t4][2010][11246][4.18.60] Connection authorization failure occurred.  Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000

ERRORCODE=-4229, The executed SQL statement had an error 

The message usually does not give you any hint of what went wrong. To get more details please, set DIAGLEVEL to 4 and check db2diag.log.

Description 

This error is related to INSERT/UPDATE/DELETE, which encountered an error. 

Troubleshooting 

The reason for this error is that the executed SQL statement had an error and could not complete.

Reasons could be:

  • Duplicated key violation.
  • NULL violation.
  • Log File has reached its saturation point.

Check the constraints and table properties, including indexes. Check also db2diag.log for errors, i.e. log files reached saturation point.

Masking Example 

This error is mainly related to Update and Insert steps when these fail to execute the statement due to some database table or column related issue. The issue is either that the value directly violates a constraint or column property or the COMMIT fails due to some violation or error. 

The two examples below are from two different types of Update errors:

Example 1 - Update Failure 

The update failed due to a violation.

ERROR 25-01 11:46:20,638 - Update - Error in step, asking everyone to stop because of:
Error updating batch
[jcc][t4][102][10040][4.18.60] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
Example 1 -Update (Commit) Failure 

Committing the updates to the table failed with a violation or error.

ERROR 01-02 10:59:23,481 - Update - Unable to commit Update connection [ISAP - TSO35_ISAP - DSNB - ST35 CONNECTOR] :org
Error updating batch
[jcc][t4][102][10040][4.18.60] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null

ERRORCODE=-4470, Connection already closed 

Request to close the connection to the DB2 database failed since it was already closed. 

This error can usually be ignored and any earlier error should be investigated. 

DB2 Error Details and Format 

The error message says it all here: "Invalid operation: Connection is closed."

There are two components to this error:

  1. ERRORCODE=-4470,
  2. SQLSTATE=08003 or NULL
Reason 

The SQLSTATE code 08003 indicates that the "connection does not exist".

Masking Example 

This error is not related to any specific masking step or specific SQL statement. The example below is an error in an Update step after the connection was closed in the Table input step.

Error performing rollback on connection
[jcc][t4][10335][10605][4.18.60] Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003

ERRORCODE=-4499, Connection refused 

The connection to the DB2 database was refused. 

DB2 Error Details and Format 

There are two components to this error and there is the socket defined in the error message:

  1. Error message
    • Optional; socket: IP address and Port number for the failed connection 
  2. DB2 error code
    • ERRORCODE=-4499,
    • SQLSTATE=08001
Reason 

The reason, according to IBM Support, is that TCP/IP is not properly enabled on your DB2 database server. This can be a configuration issue on the DB2 system or a Firewall issue. 

Masking Example 

This error is not related to any specific masking step or specific SQL statement. 

Example 1 - Execute_Create_Index 
Error occured while trying to connect to the databas
Error connecting to database: (using class com.ibm.db2.jcc.DB2Driver)
[jcc][t4][2043][11550][4.18.60] Exception java.net.ConnectException: Error opening socket to server /10.41.48.165 on port 5,026 with message: Connection refused. ERRORCODE=-4499, SQLSTATE=08001
Example 2 - Table Input 
Couldn't get row from result set
[jcc][t4][2030][11211][4.18.60] A communication error occurred during operations on the connection's underlying socket, socket input stream,
or socket output stream.  Error location: Command timeout check.  Message: Command timed out. ERRORCODE=-4499, SQLSTATE=08001

DB2 SQLCODE Summary 

SQLCODE is the return code from the execution. A negative value indicates an error. 

Below are common masking errors.

SQLCODE=-104, Illegal symbol/token   

The [token] is illegal in the SQL statement and a potential alternative keyword(s) (token-list) is provided. This list of alternatives are only suggestions. 

DB2 Error Details and Format 

Illegal symbol TOKEN. Some Symbols that might be legal are: TOKEN-LIST

There are usually three components to this error:

  1. SQLCODE=-104,
  2. SQLSTATE=42601,
  3. SQLERRMC=[token];[token-list]
Masking Example 

On the masking engine, this error has been seen in the TableOutput step. The error usually presents the SQL which is failing and the SQL statement can be tested on the database. 

The SQLERRMC details the TOKEN. Here: "ISAPDBX" 

INFO  01-02 15:06:51,651 - TableOutput - Connected to database [ISAP - TSO35_ISAP - DSNB - ST35 CONNECTOR] (commit=10000)
Couldn't execute SQL: TRUNCATE TABLE ISAPDBX."PA_RECOR" DROP STORAGE IGNORE DELETE TRIGGERS IMMEDIATE
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=ISAPDBX;IS, DRIVER=4.18.60

SQLCODE=-204 - Object is unspecified  

This error indicates that an object referenced in the statement is not available on the DB2 system. Usually, this error is seen in the SELECT statement.

DB2 Error Details and Format 

The object, OBJECT-NAME, is unspecified and not available.

There are three components to this error:

  • SQLCODE=-204,
  • SQLSTATE=42704 or 46002,
  • SQLERRMC=[Object Name]
Reason 

The reason for this error is likely that the object name (or required specifiers) was incorrectly specified, the object does not exist on the DB2 system. 

Masking Example 

On the masking engine, we will usually see SQLCODE=-204 after a Table input step. It can also be after other steps. The error usually presents the SQL which is failing and this SQL can be tested on the database. 

The SQLERRMC details the Object. Here: "SELECT * FROM DBX."TABLE" "

In this case, it is likely that the schema or the table name is incorrect. 

ERROR 19-01 13:19:28,834 - Table input - Unexpected error
An error occurred executing SQL:
SELECT * FROM DBX."TABLE"
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DBX."TABLE"

SQLCODE=-206, Object is not valid in this context 

This error indicates that an object referenced in the statement is missing.

DB2 Error Details and Format 

object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED

There are three components to this error:

  • SQLCODE=-206,
  • SQLSTATE=42703,
  • SQLERRMC=[Object Name]
Reason 

The reason for this error is that the executed SQL statement had an error and could not complete. Reasons could be:

  • Duplicated key violation 
  • NULL violation 
  • Log File has reached its saturation point
Masking Example 

On the masking engine, we will see SQLCODE=-206 at a Table input step. The error usually presents the SQL which is failing and this SQL can be tested on the database. 

The SQLERRMC details the Object: "DMS_ROWID"

In this case, the manually added column is "DMS_ROW_ID" and it has been specified as "DMS_ROWID" in the  Rule Set and Logical Key, which is incorrect and cannot be found.

ERROR 19-01 13:19:28,834 - Table input - Unexpected error
An error occurred executing SQL:
SELECT "DMS_ROWID" , "DATA_1" , "DATA_2" , "DATA_3" FROM DBX."TABLE"
SQLCODE=-206, SQLSTATE=42703, SQLERRMC=DMS_ROWID

SQLCODE=-302, Input too large or invalid for target column  

The value of input parameter number POSITION-NUMBER is invalid or too large for the target column. 

Description 

The masked data returned to was invalid or too large to fit in the corresponding column on DB2. 

DB2 Error Details and Format 

THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE

There are three components to this error:

  • SQLCODE=-302,
  • SQLSTATE= 22003 (too large), 22001 (otherwise),
  • SQLERRMC=[parameter position number]
Troubleshooting 

The masked data in either invalid or too large to fit in the target column. 

The best way to analyze this is to look at the position and the DDL (table description). 

  • Are there any fields that are too small?
  • Are there any constraints?
  • Check return values (nulls and size) from algorithms - is the algorithm fit for the data?
Masking Example 

This error happens in the Update or Insert step when the data is written to the database.

In the example below the data is invalid (SQLSTATE=22001).

ERROR 01-02 15:11:07,355 - Update - Error in step, asking everyone to stop because of:
Error inserting/updating row
DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=9, DRIVER=4.18.60

SQLCODE=-471, The SQL Code failed   

This error happens during the creation of the Rule Set when the table and column descriptions are collected.

DB2 Error Details and Format 

INVOCATION OF FUNCTION OR PROCEDURE name FAILED DUE TO REASON reason-code

There are usually three components to this error:

  1. SQLCODE=-471,
  2. SQLSTATE= 55023,
  3. SQLERRMC=[name];[reason-code]
Troubleshooting 

This happens when the Universal Driver has not been properly configured in a DB2 system. 

Check reason-code and DB2 logs. 

Masking Example 

In this specific case, the masking engine tries to execute a DB2 API(function) called "SYSIBM.SQLTABLES".  

The error example below is thrown in the Rule Set creation process.

FATAL com.dmsuite.manager.collection.CollectionManagerImpl - Error retrieving tables:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-471, SQLSTATE=55023, SQLERRMC=SYSIBM.SQLTABLES;00E7900C, DRIVER=4.18.60

SQLCODE=-551 - No privilege to perform operation  

Privileges are lacking to perform the operation. The error message details which operation and object.

DB2 Error Details and Format 

auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name

There are usually three components to this error:

  1. SQLCODE=-551,
  2. SQLSTATE= 42501,
  3. SQLERRMC=[auth-id];[operation];[object-name]
Reason 

Privileges not correctly configured on DB2.

Masking Example 

This error is usually returned early in the masking process, even during Connector and Rule Set creation. 

The error example below is thrown at the transformation job, at the Table input step.

ERROR 09-11 14:42:03,619 - Table input - Unexpected error
An error occurred executing SQL:
SELECT "CODE_FSL_CLI" , "NOME_CGN_CLI" , "NOME_NOME_CLI" , "CODE_CLI" FROM C8DBA16."DELPHIX_TABLE"
DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=C8DBAR2;SELECT;C8DBA16.DELPHIX_TABLE, DRIVER=4.18.60

SQLCODE=-805, DB2 DBRM or package not found   

This error happens during the creation of the Rule Set when the table and column descriptions are collected.

DB2 Error Details and Format 

DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code

There are three components to this error:

  • SQLCODE=-805,
  • SQLSTATE= 51002,
  • SQLERRMC=[location-name.collection-id.dbrm-name.consistency-token];[plan-name];[reason-code]
Troubleshooting 

See IBM KC below and use the information that is provided for the reason-code to resolve the problem.

Masking Example 

The error example below is thrown in the Rule Set creation process.

FATAL com.dmsuite.manager.collection.CollectionManagerImpl - Error retrieving tables:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=DSNB.NULLID.SYSSTAT.5359534C564C3031;DISTSERV;04, DRIVER=4.18.60

SQLCODE=-913, Execution failed due to Deadlock or Timeout 

The application was the victim in a deadlock or experienced a timeout. The reason code indicates whether a deadlock or timeout occurred.

DB2 Error Details and Format 

UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name

There are three components to this error:

  • SQLCODE=-913,
  • SQLSTATE= 57033,
  • SQLERRMC=[reason-code];[resource-type/resource-name];[system-action]
Troubleshooting 

This error can be generated due to lock situation from the Masking Engine or resource timeout/deadlock on DB2.

The reason for this error is detailed in the reason-code. Then get the resource. Please see IBM documentation for code details.

  • reason-code, the most common are
    • 00C90088 - deadlock

    • 00C9008E - timeout

Masking Example 

On the masking engine, we will see SQLCODE=-913 at a Table input step. The error indicates that DB2 could not read the row. 

The SQLERRMC shows the error reason: here 00C9008E = Timeout

ERROR 19-01 13:19:28,834 - Table input - Unexpected error
Couldn't get row from result set
DB2 SQL Error: SQLCODE=-913, SQLSTATE=57033, SQLERRMC=00C9008E;...