Skip to main content
Delphix

KBA1021 DB2 Connector:Troubleshooting and Errors During Masking

 

Delphix Masking: This document provides an overview of common errors and troubleshooting techniques when Masking IBM DB2 databases. 

Overview 

This document describes troubleshooting and common ERRORCODE and SQLCODE errors during masking. 

Sections in this document are:

  • DB2 Connector Troubleshooting:
    • DB2 Error Code formats.
    • Troubleshooting Steps.
  • DB2 ERRORCODE Summary:
    • ERRORCODE=-4214 - JDBC failed to connect to the database.
    • ERRORCODE=-4229 - The executed SQL statement had an error.
    • ERRORCODE=-4470 - Connection already closed.
    • ERRORCODE=-4499 - Connection refused.
  • DB2 SQLCODE Summary:
    • SQLCODE=-104 - Illegal symbol/token.
    • SQLCODE=-204 - Object is unspecified.
    • SQLCODE=-206 - Object is not valid in this context.
    • SQLCODE=-302 - Input too large or invalid for target column.
    • SQLCODE=-471 - The SQL Code failed.
    • SQLCODE=-551 - No privilege to perform operation.
    • SQLCODE=-805 - DB2 DBRM or package not found.
    • SQLCODE=-913 - Execution failed due to Deadlock or Timeout.
  • Additional Information
  • External Links

DB2 Connector Troubleshooting

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

More information 

For more information:



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
More information 

For more information:


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
More information 

For more information:


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
More information 

For more information:


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
More Information 

For more information:


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 usually three components to this error:

  1. SQLCODE=-204,
  2. SQLSTATE=42704 or 46002,
  3. 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"
More Information

For more information:


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 usually three components to this error:

  1. SQLCODE=-206,
  2. SQLSTATE=42703,
  3. 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
More information 

For more information:



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 usually three components to this error:

  1. SQLCODE=-302,
  2. SQLSTATE= 22003 (too large), 22001 (otherwise),
  3. 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
More information 

For more information:



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
More information 

For more information:


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
More information 

For more information:


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:

  1. SQLCODE=-805,
  2. SQLSTATE= 51002,
  3. 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
More information 

For more information:


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 usually three components to this error:

  1. SQLCODE=-913,
  2. SQLSTATE= 57033,
  3. 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;...
More information 

For more information:



Additional Information