XCOM23000E DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704

Products

• XCOM Data Transport
• XCOM - SUPPORT

Issue/Introduction

Although the installation of XCOM Gateway was successful. The following errors were displayed:

XCOM23000E Cannot insert policy data 
[09-15-2020/14:19:21.982/GMT-03:00][ERROR][com.ca.xcom.gateway.gcs.ws.rest.GatewayAdminServersWS] Exception occurred in the GatewayAdminServersWS->getServers->
com.ca.xcom.gateway.daoservice.support.exception.SystemDAOException: MSG23402
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=GATEWAY.XCOM_GATEWAY_SERVER, DRIVER=4.25.13
Error Code: -204
Call: SELECT SERVER_ROWID, AVAILABLE_IN_BRIDGE, AVAILABLE_IN_UI, CERT_ID, ENC_KEY, HOST_NAME, PASSWORD, PROXY_HOST_NAME, PROXY_PASSWORD, PROXY_SERVER_PORT, PROXY_TYPE, PROXY_USERNAME, SERVER_NAME, SERVER_PORT, SERVER_TYPE, USERNAME FROM XCOM_GATEWAY_SERVER
Query: ReadAllQuery(name="XcomGatewayServer.findServers" referenceClass=XCOMGatewayServer sql="SELECT SERVER_ROWID, AVAILABLE_IN_BRIDGE, AVAILABLE_IN_UI, CERT_ID, ENC_KEY, HOST_NAME, PASSWORD, PROXY_HOST_NAME, PROXY_PASSWORD, PROXY_SERVER_PORT, PROXY_TYPE, PROXY_USERNAME, SERVER_NAME, SERVER_PORT, SERVER_TYPE, USERNAME FROM XCOM_GATEWAY_SERVER")
 at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, 

When reviewing the DB install log file, it ends as successful. but the tables were not created.

Environment

Release : 12.0

Component : CA XCOM DATA TRANSPORT GATEWAY FOR WINDOWS

Cause

SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704

The database was not created with the proper privileges. Also the userid that was specified for after the installation, did not have enough privileges. 

Resolution

The userid used to create the database must have these permissions: create tables, create triggers, create index, select, update, insert, and delete within the database.

For an existing database, the user ID that is passed must have privileges to alter tables, create trigger, create index, create table, Insert, update table, select table, and drop tables. 

Refer to the Gateway 12.0 online manual: Define the Database

Additional Information

"SQLCODE=-204, SQLSTATE=42704" is a DB2 error code indicating that an undefined object was detected. In this case, either the table is not present, or that DB2ADMIN does not have access to read this.