Create table:
CREATE TABLE TEST_TABLE (
TEST_COLUMN INT,
UPDATETIME TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)
Rescript table is missing GENERATED BY properties
CREATE TABLE TEST_TABLE (
TEST_COLUMN INTEGER,
UPDATETIME TIMESTAMP NOT NULL)
We need to enhance extraction and scripting to support column property GENERATED BY
.-ALWAYS-----.
|--+-GENERATED--+------------+--+-| as-row-change-timestamp-clause
| '-BY DEFAULT-' '-
| .-ALWAYS-.
'-GENERATED--+--------+--+-| as-generated-expression-clause
+-| as-row-transaction-timestamp-clause
'-| as-row-transaction-start-id-clause
as-row-change-timestamp-clause |---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------| as-generated-expression-clause |--AS--(--generation-expression--)------------------------------| as-row-transaction-timestamp-clause |---------AS--ROW--+-BEGIN-+------------------------------------| '-END---' as-row-transaction-start-id-clause |---------AS--TRANSACTION START ID------------------------------|
Examples;
CREATE TABLE TEST_TABLE ( TEST_COLUMN INT, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO CREATE TABLE TEST_TABLE1 ( TEST_COLUMN INT, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO
Examples;
CREATE TABLE TEST_TABLE ( TEST_COLUMN INT, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO CREATE TABLE TEST_TABLE1 ( TEST_COLUMN INT, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO
Extraction and Scripting support is complete. In order to support these new features we also have to support extraction and scripting of the timestamp scale value. This value can be between 0 and 12.
This support has been added for versions of db2 10.1 and greater.
GUI support for this will require design work. Will fill a new issue for future version so it can properly be determined when to allocate resources to do the work.
Committed revision: 51142
Below are the final 2 table scripts I used to test extraction and DDL rescripting:
CREATE TABLE TEST_TABLE ( TEST_COLUMN INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, UPDATETIME6 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 YEAR), PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO CREATE TABLE TEST_TABLE1 ( TEST_COLUMN INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3, INCREMENT BY 7, CACHE 33) NOT NULL, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, UPDATETIME6 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 YEAR), PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4))
Extraction and Scripting support is complete. In order to support these new features we also have to support extraction and scripting of the timestamp scale value. This value can be between 0 and 12.
This support has been added for versions of db2 10.1 and greater.
GUI support for this will require design work. Will fill a new issue for future version so it can properly be determined when to allocate resources to do the work.
Committed revision: 51142
Below are the final 2 table scripts I used to test extraction and DDL rescripting:
CREATE TABLE TEST_TABLE ( TEST_COLUMN INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, UPDATETIME6 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 YEAR), PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4)) GO CREATE TABLE TEST_TABLE1 ( TEST_COLUMN INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3, INCREMENT BY 7, CACHE 33) NOT NULL, UPDATETIME TIMESTAMP NOT NULL, UPDATETIME1 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, UPDATETIME2 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 MONTH), UPDATETIME3 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, UPDATETIME4 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, UPDATETIME5 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, UPDATETIME6 TIMESTAMP NOT NULL GENERATED ALWAYS AS (UPDATETIME+1 YEAR), PERIOD SYSTEM_TIME (UPDATETIME3, UPDATETIME4))
Right click on Table and Select -> Tools > ER Diagram Generator is not generating the correct script
Right click on Table and Select -> Tools > ER Diagram Generator is not generating the correct script
This has been fixed in Commit Revision: 51157
A new dev build will be available soon
This has been fixed in Commit Revision: 51157
A new dev build will be available soon
Scripting in ER Diagram Generator is not fixed in 17.0.8-6. Can you double check if the fix was pushed in 17.0.8-6 ?
Scripting in ER Diagram Generator is not fixed in 17.0.8-6. Can you double check if the fix was pushed in 17.0.8-6 ?
ERM support for timestamp scale is added but we can't add ERM support for these properties to the TIMESTAMP until we add GUI support for them.
ERM support for timestamp scale is added but we can't add ERM support for these properties to the TIMESTAMP until we add GUI support for them.
Issue #14704 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build v17.0.8-5 |
No time estimate |
.-ALWAYS-----. |--+-GENERATED--+------------+--+-| as-row-change-timestamp-clause | '-BY DEFAULT-' '- | .-ALWAYS-. '-GENERATED--+--------+--+-| as-generated-expression-clause +-| as-row-transaction-timestamp-clause '-| as-row-transaction-start-id-clause