View Issue Details

IDProjectCategoryView StatusLast Update
0002955SymmetricDSBugpublic2020-12-15 00:05
Reportermalachi83 Assigned To 
Prioritynormal 
Status newResolutionopen 
Summary0002955: DB2 9 fails on start up (meta-data is missing)
DescriptionI am installing SymmetricsDS for the first time with the aim of syncing between IBM db2 9.7 FP 9 to postgresql 9.5.
I have followed the quick start demo guide using the data in the samples directory.
When i start the service it attempts to create the SYM tables and because I have already created the tables as part of one of the previous steps (../bin/symadmin --engine corp-000 create-sym-tables ) it fails.
If I drop the SYM tables and start the service again it starts up ok and creates the SYM tables. However when i stop and start the service it trys to create the tables again and fails.
The user which is configured in the properties file has access to the SYM tables. If logon to the database using db visualiser with the same user I can see all the SYM tables.
log output below any help or advice would be appreciated.
[startup] - SymmetricWebServer - About to start SymmetricDS web server on host:port default:31415
[startup] - / - Initializing Spring root WebApplicationContext
[corp-000] - AbstractSymmetricEngine - Initializing connection to database
[store-001] - AbstractSymmetricEngine - Initializing connection to database
[corp-000] - JdbcDatabasePlatformFactory - Detected database 'db2', version '9', protocol 'db2'
[corp-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.db2.Db2DatabasePlatform
[store-001] - JdbcDatabasePlatformFactory - Detected database 'PostgreSQL', version '9', protocol 'postgresql'
[store-001] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.postgresql.PostgreSqlDatabasePlatform
[corp-000] - Db2v9SymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.db2.Db2v9SymmetricDialect
[corp-000] - StagingManager - The staging directory was initialized at the following location: /etc/symmetrics/symmetric-server-3.8.11/tmp/corp-000
[corp-000] - ClusterService - This node picked a server id of symmetricds-4-poc
[store-001] - PostgreSqlSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.postgresql.PostgreSqlSymmetricDialect
[store-001] - ExtensionService - Found 0 extension points from the database that will be registered
[store-001] - StagingManager - The staging directory was initialized at the following location: /etc/symmetrics/symmetric-server-3.8.11/tmp/store-001
[store-001] - ClusterService - This node picked a server id of symmetricds-4-poc
[startup] - / - Initializing Spring FrameworkServlet 'rest'
[startup] - SymmetricWebServer - Starting JMX HTTP console on port 31416
[startup] - SymmetricWebServer - Joining the web server main thread
HttpAdaptor version 3.0.1 started on port 31416
[store-001] - ExtensionService - Found 0 extension points from the database that will be registered
[store-001] - ClientExtensionService - Found 7 extension points from spring that will be registered
[store-001] - ExtensionService - Registering extension named 'databaseUpgradeListener' of type 'IDatabaseUpgradeListener'
[store-001] - ExtensionService - Registering extension named 'softwareUpgradeListener' of type 'ISoftwareUpgradeListener'
[store-001] - AbstractSymmetricEngine - Initializing SymmetricDS database
[store-001] - PostgreSqlSymmetricDialect - Checking if SymmetricDS tables need created or altered
[corp-000] - ClientExtensionService - Found 7 extension points from spring that will be registered
[corp-000] - ExtensionService - Registering extension named 'databaseUpgradeListener' of type 'IDatabaseUpgradeListener'
[corp-000] - ExtensionService - Registering extension named 'softwareUpgradeListener' of type 'ISoftwareUpgradeListener'
[corp-000] - AbstractSymmetricEngine - Initializing SymmetricDS database
[corp-000] - Db2v9SymmetricDialect - Checking if SymmetricDS tables need created or altered
[corp-000] - Db2v9SymmetricDialect - There are SymmetricDS tables that needed altered
[corp-000] - Db2v9SymmetricDialect - DDL failed: CREATE TABLE "SYM_CHANNEL"(
"CHANNEL_ID" VARCHAR(128) NOT NULL,
"PROCESSING_ORDER" INTEGER DEFAULT 1 NOT NULL,
"MAX_BATCH_SIZE" INTEGER DEFAULT 1000 NOT NULL,
"MAX_BATCH_TO_SEND" INTEGER DEFAULT 60 NOT NULL,
"MAX_DATA_TO_ROUTE" INTEGER DEFAULT 100000 NOT NULL,
"EXTRACT_PERIOD_MILLIS" INTEGER DEFAULT 0 NOT NULL,
"ENABLED" SMALLINT DEFAULT 1 NOT NULL,
"USE_OLD_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"USE_ROW_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"USE_PK_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"RELOAD_FLAG" SMALLINT DEFAULT 0 NOT NULL,
"FILE_SYNC_FLAG" SMALLINT DEFAULT 0 NOT NULL,
"CONTAINS_BIG_LOB" SMALLINT DEFAULT 0 NOT NULL,
"BATCH_ALGORITHM" VARCHAR(50) DEFAULT 'default' NOT NULL,
"DATA_LOADER_TYPE" VARCHAR(50) DEFAULT 'default' NOT NULL,
"DESCRIPTION" VARCHAR(255),
"QUEUE" VARCHAR(25) DEFAULT 'default' NOT NULL,
"MAX_NETWORK_KBPS" DECIMAL(10,3) DEFAULT 0.000 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR(50),
"LAST_UPDATE_TIME" TIMESTAMP,
PRIMARY KEY ("CHANNEL_ID")
)
[corp-000] - JdbcSqlTemplate - DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=CAPEST.SYM_CHANNEL;TABLE, DRIVER=4.14.137. Failed to execute: CREATE TABLE "SYM_CHANNEL"(
"CHANNEL_ID" VARCHAR(128) NOT NULL,
"PROCESSING_ORDER" INTEGER DEFAULT 1 NOT NULL,
"MAX_BATCH_SIZE" INTEGER DEFAULT 1000 NOT NULL,
"MAX_BATCH_TO_SEND" INTEGER DEFAULT 60 NOT NULL,
"MAX_DATA_TO_ROUTE" INTEGER DEFAULT 100000 NOT NULL,
"EXTRACT_PERIOD_MILLIS" INTEGER DEFAULT 0 NOT NULL,
"ENABLED" SMALLINT DEFAULT 1 NOT NULL,
"USE_OLD_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"USE_ROW_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"USE_PK_DATA_TO_ROUTE" SMALLINT DEFAULT 1 NOT NULL,
"RELOAD_FLAG" SMALLINT DEFAULT 0 NOT NULL,
"FILE_SYNC_FLAG" SMALLINT DEFAULT 0 NOT NULL,
"CONTAINS_BIG_LOB" SMALLINT DEFAULT 0 NOT NULL,
"BATCH_ALGORITHM" VARCHAR(50) DEFAULT 'default' NOT NULL,
"DATA_LOADER_TYPE" VARCHAR(50) DEFAULT 'default' NOT NULL,
"DESCRIPTION" VARCHAR(255),
"QUEUE" VARCHAR(25) DEFAULT 'default' NOT NULL,
"MAX_NETWORK_KBPS" DECIMAL(10,3) DEFAULT 0.000 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR(50),
"LAST_UPDATE_TIME" TIMESTAMP,
PRIMARY KEY ("CHANNEL_ID")
)
[corp-000] - AbstractSymmetricEngine - An error occurred while starting SymmetricDS
org.jumpmind.db.sql.SqlException: DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=CAPEST.SYM_CHANNEL;TABLE, DRIVER=4.14.137
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:303)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:294)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:493)
at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:474)
at org.jumpmind.symmetric.db.db2.Db2SymmetricDialect.createOrAlterTablesIfNecessary(Db2SymmetricDialect.java:48)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:155)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:449)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:423)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:580)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:571)
at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:244)
at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:478)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=CAPEST.SYM_CHANNEL;TABLE, DRIVER=4.14.137
at com.ibm.db2.jcc.am.ed.a(ed.java:677)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.oo.c(oo.java:2715)
at com.ibm.db2.jcc.am.oo.d(oo.java:2703)
at com.ibm.db2.jcc.am.oo.b(oo.java:2070)
at com.ibm.db2.jcc.t4.ab.i(ab.java:226)
at com.ibm.db2.jcc.t4.ab.c(ab.java:48)
at com.ibm.db2.jcc.t4.p.b(p.java:38)
at com.ibm.db2.jcc.t4.qb.h(qb.java:114)
at com.ibm.db2.jcc.am.oo.hb(oo.java:2065)
at com.ibm.db2.jcc.am.oo.a(oo.java:3237)
at com.ibm.db2.jcc.am.oo.e(oo.java:1073)
at com.ibm.db2.jcc.am.oo.execute(oo.java:1056)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:416)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:491)
... 11 more
[corp-000] - AbstractSymmetricEngine - Stopping SymmetricDS externalId=000 version=3.8.11 database=DB2/LINUXX8664
[corp-000] - AbstractSymmetricEngine - SymmetricDS: type=server, name=corp-000, version=3.8.11, groupId=corp, externalId=000, databaseName=DB2/LINUXX8664, databaseVersion=9.7, driverName=IBM Data Server Driver for JDBC and SQLJ, driverVersion=4.14.137
Steps To ReproduceInstall db2 9.7.9
Configure symmetrics using quick start demo guide loading sample data.
Start service
start up fails because SYM tables already exists.
Drop SYM tables
Start up is successful
stop then start service. Start up fails because SYM tables exist.
Tagsdialect: db2, install

Activities

malachi83

2017-01-18 14:52

reporter   ~0000956

looking at the code It looks to be an issue with the meta data being returned.
adding logging on the meta values shows the below is be returned.
meta data values {TABLE_CAT=null, SELF_REF_COL_NAME=null, TABLE_NAME=null, TABLE_SCHEM=DB2INST1, TYPE_SC
HEM=null, TYPE_CAT=null, TABLE_TYPE=TABLE, REMARKS=null, REF_GENERATION=null, TYPE_NAME=null, NAME=SYM_TRIGGER_ROUTER_GROUPLET}
the table name is returned in the meta values under NAME instead of TABLE_NAME which is null
It then misses out a bit of code which create a table object.
The start up thinks no tables are created and then tries to create them.
Any help on this would be appreciated.

Issue History

Date Modified Username Field Change
2017-01-09 13:09 malachi83 New Issue
2017-01-18 14:52 malachi83 Note Added: 0000956
2019-04-24 14:26 elong Tag Attached: install
2019-04-24 14:26 elong Tag Attached: dialect: db2
2019-05-13 20:34 elong Summary SymmetricsDS Installation - Fails on start up. => SymmetricsDS Installation - Fails on start up (meta-data is missing)
2019-05-13 20:34 elong Summary SymmetricsDS Installation - Fails on start up (meta-data is missing) => DB2 9 fails on start up (meta-data is missing)