View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003065SymmetricDSBugpublic2017-04-20 16:112017-04-20 19:00
Reporterrgiovanardi Assigned Tomaxwellpettit  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.20 
Target Version3.8.21Fixed in Version3.8.21 
Summary0003065: DB2 Error: SQLCODE=-574 during Initial Load when default datatype is CURRENT TIMESTAMP
DescriptionHi all,
  I have a problem with IBM DB2 as sync client with the initial load and the parameter initial.load.create.first=true:

When it tries to create tables with DATE data type and 'CURRENT DATE' as DEFAULT, it fails with DB2 SQL Error: SQLCODE=-574, SQLSTATE=42894

That error is caused by a syntax error: following the SQL that SymmetricDS tries to execute at initial load:

CREATE TABLE "SCHEMA"."TABLE"(
    "LAST_DATE" TIMESTAMP DEFAULT 'CURRENT TIMESTAMP' NOT NULL,
    "CREATION_DATE" DATE DEFAULT 'CURRENT DATE' NOT NULL,
    "CREATION_TIME" TIME DEFAULT 'CURRENT TIME' NOT NULL,
);

note that default values should not be quoted; following the right SQL syntax:

CREATE TABLE "SCHEMA"."TABLE"(
    "LAST_DATE" TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL,
    "CREATION_DATE" DATE DEFAULT CURRENT DATE NOT NULL,
    "CREATION_TIME" TIME DEFAULT CURRENT TIME NOT NULL,
);

I'm using:
Symmetric-server 3.8.20
IBM DB2 9.7 fp11

My current SYM_PARAMETER:

EXTERNAL_ID |NODE_GROUP_ID |PARAM_KEY |PARAM_VALUE |CREATE_TIME |LAST_UPDATE_BY |LAST_UPDATE_TIME |
------------|--------------|------------------------------------------|------------|---------------------------|---------------|---------------------------|
ALL |ALL |auto.registration |true |2017-04-20-12.20.32.091638 |admin |2017-04-20-12.20.32.091638 |
ALL |ALL |auto.reload |true |2017-04-20-12.23.37.771106 |admin |2017-04-20-12.23.37.771106 |
ALL |ALL |create.table.without.foreign.keys |true |2017-04-20-12.23.37.856032 |admin |2017-04-20-12.23.37.856032 |
ALL |ALL |initial.load.create.first |true |2017-04-20-12.23.37.947337 |admin |2017-04-20-12.23.37.947337 |
ALL |ALL |initial.load.delete.first |false |2017-04-20-12.23.38.025528 |admin |2017-04-20-12.23.38.025528 |
ALL |ALL |initial.load.use.extract.job.enabled |true |2017-04-20-12.23.38.109783 |admin |2017-04-20-12.23.38.109783 |
ALL |ALL |initial.load.extract.and.send.when.staged |true |2017-04-20-12.23.38.194178 |admin |2017-04-20-12.23.38.194178 |

Thanks in advance
Roberto
Steps To ReproduceUsing DB2 V9.7, create a table with datatype TIMESTAMP and default values CURRENT TIMESTAMP, as following:

CREATE TABLE "SCHEMA"."TABLE"(
    "LAST_DATE" TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL,
    "CREATION_DATE" DATE DEFAULT CURRENT DATE NOT NULL,
    "CREATION_TIME" TIME DEFAULT CURRENT TIME NOT NULL,
);

Then replicate that table to another DB2 V9.7 with initial.load.create.first=true
Additional InformationFollowing the symmetric.log:

2017-04-20 17:01:45,648 INFO [CLIENT] [DefaultDatabaseWriter] [CLIENT-data-loader-1] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor" schema="SCHEMA">
    <table name="TABLE">
        <column name="LAST_DATE" required="true" type="TIMESTAMP" size="26,6" default="CURRENT TIMESTAMP">
            <platform-column name="db2" type="TIMESTAMP" size="26" decimalDigits="6" default="CURRENT TIMESTAMP"/>
        </column>
        <column name="CREATION_DATE" required="true" type="DATE" size="10" default="CURRENT DATE">
            <platform-column name="db2" type="DATE" size="10" default="CURRENT DATE"/>
        </column>
        <column name="CREATION_TIME" required="true" type="TIME" size="8" default="CURRENT TIME">
            <platform-column name="db2" type="TIME" size="8" default="CURRENT TIME"/>
        </column>
    </table>
</database>
2017-04-20 17:01:45,648 INFO [CLIENT] [Db2DatabasePlatform] [CLIENT-data-loader-1] Running alter sql:
CREATE TABLE "SCHEMA"."TABLE"(
    "LAST_DATE" TIMESTAMP DEFAULT 'CURRENT TIMESTAMP' NOT NULL,
    "CREATION_DATE" DATE DEFAULT 'CURRENT DATE' NOT NULL,
    "CREATION_TIME" TIME DEFAULT 'CURRENT TIME' NOT NULL,
);

2017-04-20 17:01:45,648 WARN [CLIENT] [JdbcSqlTemplate] [CLIENT-data-loader-1] DB2 SQL Error: SQLCODE=-574, SQLSTATE=42894, SQLERRMC=LAST_DATE;SCHEMA.TABLE;1, DRIVER=3.72.24. Failed to execute: CREATE TABLE "SCHEMA"."TABLE"(
    "LAST_DATE" TIMESTAMP DEFAULT 'CURRENT TIMESTAMP' NOT NULL,
    "CREATION_DATE" DATE DEFAULT 'CURRENT DATE' NOT NULL,
    "CREATION_TIME" TIME DEFAULT 'CURRENT TIME' NOT NULL,
)
TagsNo tags attached.

Activities

maxwellpettit

2017-04-20 18:17

manager   ~0001023

Also added support for default value of CURRENT USER

Related Changesets

SymmetricDS: 3.8 2c9ff2ea

2017-04-20 14:16:53

maxwellpettit

Details Diff 		0003065: DB2 Error: SQLCODE=-574 during Initial Load when default
datatype is CURRENT TIMESTAMP		 Affected Issues
0003065
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2017-04-20 16:11 rgiovanardi New Issue
2017-04-20 17:17 chenson Assigned To => maxwellpettit
2017-04-20 17:17 chenson Status new => assigned
2017-04-20 18:17 maxwellpettit Note Added: 0001023
2017-04-20 18:17 maxwellpettit Status assigned => resolved
2017-04-20 18:17 maxwellpettit Fixed in Version => 3.8.21
2017-04-20 18:17 maxwellpettit Resolution open => fixed
2017-04-20 18:21 chenson Target Version => 3.8.21
2017-04-20 18:22 chenson Status resolved => closed
2017-04-20 19:00 maxwellpettit Changeset attached => SymmetricDS 3.8 2c9ff2ea