My scenario is to export data from the real DB2 and then load it back for integration tests purpose. I am using liquibase (v3.5.3) to manage it.
I have found that TIMESTAMP values are changed during this cycle. When timestamp value is exported I can see it in changelog file as "2018-06-28 22:47:38.816343". After that when I load it back into DB2 it becomes "2018-06-28 23:01:14.343".
The reason is that the "816343" part is treated not as a part of a second but rather as milliseconds amount and that amount is added to the result timestamp.
In the tests a business decision criteria is made by comparing of those timestamps. I need them to be equal.
Any thoughts and proposals will be appreciated.
There are steps to reproduce:
1. Create a file "01_test_data_to_setup.xml" with content
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="OK" id="1">
<createTable tableName="TT">
<column name="TS_LOADED" type="TIMESTAMP"/>
<column name="TS_GENERATED" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="OK" id="2">
<insert tableName="TT">
<column name="TS_LOADED"/>
</insert>
</changeSet>
</databaseChangeLog>
2. Execute above changelog file with liquibase update command
liquibase.bat --driver=com.ibm.db2.jcc.DB2Driver --logLevel=info --classpath=~jdbc driver path here~ --changeLogFile=01_test_data_to_setup.xml --url=jdbc:db2:~jdbc url here~ --defaultSchemaName=~schema name here~ --username=~user name here~ --password=~password here~ update
3. Export data from DB
liquibase.bat --driver=com.ibm.db2.jcc.DB2Driver --logLevel=info --classpath=~jdbc driver path here~ --changeLogFile=db2_exported_test_data.xml --url=jdbc:db2:~jdbc url here~ --defaultSchemaName=~schema name here~ --username=~user name here~ --password=~password here~ --diffTypes="data" generateChangeLog --includeObjects="table:TT"
As result you will have "db2_exported_test_data.xml" file
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="OK (generated)" id="1530226079041-1">
<insert tableName="TT">
<column name="TS_LOADED"/>
<column name="TS_GENERATED" valueDate=~your generated timestamp value here. in my case it's "2018-06-28 22:47:38.816343"/>
</insert>
</changeSet>
</databaseChangeLog>
4. A file to load exported timestamp back to DB "02_test_data_to_load.xml"
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="OK" id="3">
<insert tableName="TT">
<column name="TS_LOADED" valueDate=~value in TS_GENERATED from previous step, i.e."2018-06-28 22:47:38.816343"~/>
</insert>
</changeSet>
</databaseChangeLog>
Liquibase command
liquibase.bat --driver=com.ibm.db2.jcc.DB2Driver --logLevel=info --classpath=~jdbc driver path here~ --changeLogFile=02_test_data_to_load.xml --url=jdbc:db2:~jdbc url here~ --defaultSchemaName=~schema name here~ --username=~user name here~ --password=~password here~ update
5. Check exported and loaded timestamp in DB or export TT table data one more time.
TS_LOADED timestamp value in second row will be different from TS_GENERATED value in first row
