Hi guys,
Here is the bug description I found using DBVis 8.0.1 and connected to DB2 schema:
1. Execute following statements in DB2 (create table and insert one new row):
CREATE TABLE TEST_TABLE
(
CUSTOMER_NAME VARCHAR(1000),
UP_CUSTOMER_NAME GENERATED ALWAYS AS (UPPER(CUSTOMER_NAME))
)
;
INSERT INTO TEST_TABLE( CUSTOMER_NAME ) VALUES ( 'Sample_Customer' ) ;
2. Now export the table to the SQL file, please select the options:
- Generate CREATE statements
- Generate DROP statements
- Include table data
3. Open the exported SQL file, it will contain:
DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE ( CUSTOMER_NAME VARCHAR(1000), UP_CUSTOMER_NAME VARCHAR(1000) GENERATED ALWAYS AS (UPPER(CUSTOMER_NAME)) );
insert into TEST_TABLE (CUSTOMER_NAME, UP_CUSTOMER_NAME) values ('Sample_Customer', 'SAMPLE_CUSTOMER');
4. Execute above statements, in the log you will get an ERROR:
WARNING: 10:42:29 [DROP - 0 row(s), 0.203 secs] Command processed. No rows were affected
WARNING: 10:42:30 [CREATE - 0 row(s), 0.500 secs] Command processed. No rows were affected
ERROR: 10:42:30 [INSERT - 0 row(s), 0.000 secs] [Error Code: -798, SQL State: 428C9] A value cannot be specified for column "UP_CUSTOMER_NAME" which is defined as GENERATED ALWAYS.. SQLCODE=-798, SQLSTATE=428C9, DRIVER=3.57.82
SUMMARY: ... 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.703/0.000 sec
[0 successful, 2 warnings, 1 errors]
The problem is that when the column is defined as GENERATED ALWAYS we cant insert values to that column, it is automatically set by GENERATED ALWAYS clause.
The insert statement should look like this:
insert into TEST_TABLE (CUSTOMER_NAME) values ('Sample_Customer');
Regards,
Chris
