Masking DB2 Error Summary (KBA1021)

Investigating the logs

You can use these grep or powershell commands to get the key details from the logs: 

• Change all '1234' to the execution ID for the job execution.

grep -E "JOB_ID_.*1234" info.log | grep -E "E=1|ERRORCODE|SQLCODE|SQLSTATE"

Select-String "JOB_ID_.*1234" info.log | Select-String "E=1|ERRORCODE|SQLCODE|SQLSTATE"

-4214 : JDBC failed to connect 

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

DB2 Error Details

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 

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
Connection authorization failure occurred.  Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000

-4229 : The SQL statement had an error 

DB2 is usually very brief in the error details and the message usually does not give you any hint of what went wrong. 

DB2 Error Details

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.

Usually, the log entry "Next Exception" will give more details.

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. To get more details, set DIAGLEVEL to 4 and check db2diag.log.

Examples

This error is mainly related to Update and Insert steps and 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 constraint violation (constraint not dropped).

ERROR - Update - Error in step, asking everyone to stop because of:
...
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
Next Exception: ...

Example 1 -Update (Commit) Failure 

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

ERRPR - Update - Unable to commit Update connection [...]
...
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
Next Exception: ...

-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

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".

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.

In the example below the error happens after another error and the Rollback is failing as the connection has been terminated.

Error performing rollback on connection
...
Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003

-4499 : Connection refused 

The connection to the DB2 database was refused. 

DB2 Error Details

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. 

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)
...
Error opening socket to server [IP/HOST] on port [Port] with message: Connection refused. ERRORCODE=-4499, SQLSTATE=08001

Example 2 - Table Input 

Couldn't get row from result set
...
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

-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

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]

Example 

On the Continuous Compliance 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: "OBJECT" 

TableOutput - Connected to database [...] (commit=10000)
Couldn't execute SQL: TRUNCATE TABLE ...
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=OBJECT;IS, DRIVER=4.18.60

-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 

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. 

Example 

On the Continuous Compliance Engine, you 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. 

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

-206 : Object is not valid in this context 

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

DB2 Error Details

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

Example 

On the Continuous Compliance Engine, you 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: "MY_ROWID"

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

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=MY_ROWID

-302 : Input too large or invalid  

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

DB2 Error Details

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?

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).

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

-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

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. 

Example 

In this specific case, the Continuous Compliance 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

-551 : No privilege to perform operation  

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

DB2 Error Details

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 are not correctly configured on DB2.

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.

Table input - Unexpected error
An error occurred executing SQL:
SELECT [COLUMNs] FROM [TABLE]
DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=[OBJECT];SELECT;[SCHEMA].[TABLE], DRIVER=4.18.60

-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

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.

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

-911 : Unit of work Deadlocked or Timeout

The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back.

This is a hard error to resolve. The reason can be many, including how the data is stored. 

DB2 Error Details 

THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name

There are three components to this error:

• SQLCODE=-911,
• SQLSTATE= 40001,
• SQLERRMC=[reason-code];[resource-type/resource-name];[system-action]

Troubleshooting 

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

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

The most common reason codes are:

• 00C90088 - deadlock
• 00C9008E - timeout

The changes associated with the unit of work must be entered again.

Diagnosis and Example

1. Are there any open transactions on the database?
   • What are they related to?
   • If the SELECT and UPDATE are locking - try to change the Commit Size or use OTF masking, 
2. Is anything else locking the database?
3. After a database restart, does the masking job still fail?
4. Confirm that nothing else is accessing the database whilst the masking job is running
5. Has there been a recent "disaster system power off"? eg: IBM Support doc - 444841

An error occurred executing SQL:
SELECT [COLUMNs] FROM [TABLE]
DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=4.25.1301

-913 : Execution Deadlocked 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

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 Continuous Compliance 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

Example

On the Continuous Compliance Engine, you 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;...