0

The following SQL Query:

CREATE TABLE "SomeTable" ("dateEnd" DATE)

Creates a table SomeTable with a column dateEnd. However, the database-type is Timestamp, not Date. It used to work, but after reimporting a whole database dump, all the Date data-types are replaced by Timestamp data-types. Even If I create a very simple table, like the one above, the data-type jumps to Timestamp. I am using DB2 express c version 11.1.0.

J. Doe
  • 12,159
  • 9
  • 60
  • 114

1 Answers1

1

If your Db2 database was created in Oracle Compatibility mode, then DATE columns are implemented as TIMESTAMP(0) columns to match what Oracle does.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0053667.html

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0054912.html

BTW you may want to use either Db2 Developer-C or Db2 Developer Community Edition. Those are effectively replacing the old Express-C edition

https://www.ibm.com/uk-en/marketplace/ibm-db2-direct-and-developer-editions

Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
  • Hey Vernon, thank you for your answer. How would I know if the Db2 database was created in Oracle Compatibility mode? If someone else reads out the dump it works, but not on my computer on the exact same dump. The registery value DB2_COMPATIBILITY_VECTOR can not be changed I read based on the links you provided... – J. Doe Sep 21 '18 at 09:16
  • https://stackoverflow.com/questions/51945979/db2-warehouse-on-cloud-default-plsql-compatibility – Paul Vernon Sep 21 '18 at 09:27
  • You can change the DB2_COMPATIBILITY_VECTOR, but you can't change the way the data is stored on disk. I.e. the `date_compat`, `number_compat` and `varchar2_compat` config params can't be changed on an existing database, so you can't change how e.g. `DATE` is treated on an existing database. You would have to create a new database (with DB2_COMPATIBILITY_VECTOR unset) and drop the old one – Paul Vernon Sep 21 '18 at 09:55
  • Hmm I see, I changed indeed the DB2_COMPATIBILITY_VECTOR, but that didn't worked out as you stated. In my .dmp file, there is no such attribute DB2_COMPATIBILITY_VECTOR, which makes it very weird. You are correct that it is in compability mode. I am importing create the database again (takes a very long time), but I haven't changed anything in the .dmp file though... Could there anything else causing the database jump to a combability mode? – J. Doe Sep 21 '18 at 10:56
  • I'm not sure what you really mean by your .dmp file. Those files are " Dump files are in binary format and are intended for IBM Software Support representatives." https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/c0008937.html – Paul Vernon Sep 21 '18 at 11:19
  • DB2_COMPATIBILITY_VECTOR is a an environment variable. If it is set you can see it's value with `db2set`. If not set, you can see it in the listed of all documented env variables like this `db2set -lr | grep DB2_COMPATIBILITY_VECTOR` – Paul Vernon Sep 21 '18 at 11:22
  • We got a .dmp file which creates the whole database, but i got it working right now! :) Thank you very much for your information and links, it was really helpful. – J. Doe Sep 21 '18 at 11:44