Create or replace for SQL Tables

In previous posts I have given examples of using the CREATE OR REPLACE for all kinds of SQL objects: Views, Indexes, Functions, Triggers, Sequences, etc. One situation I have not mentioned is using this option when creating a Table. I know it sounds a bit scary to replace an existing Table that contains data. If I am going to do it I want to have the option to preserve the data or to delete it.

Creating a new table is as easy as:

01 CREATE TABLE MYLIB.TESTING_TABLE 02 FOR SYSTEM NAME "TESTTABLE" 03 ( 04 FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL, 05 IDENTITY_COLUMN FOR "ID_COL" BIGINT 06 GENERATED ALWAYS AS IDENTITY 07 ) ; 08 INSERT INTO MYLIB.TESTTABLE (FIRST_COLUMN) 09 VALUES('FIRST'),('SECOND'),('THIRD') ;

This is a bit of a cheat as only lines 1-7 create the Table. Lines 8 and 9 insert data into the Table.

Lines 1 and 2: I always like to give my Tables, Views, and Indexes long names that are descriptive. I also like to give the short system name that this object will be known as.

Line 4: I am also going to define this column with a long and a short name.

Line 5: I like to have an identity column to have a unique value for every row/record.

Lines 8 – 9: After the Table has been created I am using a multiple row Insert to add three rows/records to the Table. I do not give the value for the identity column as this is generated by the database.

Now when I look at the table I can see the three rows:

FIRST_COLUMN IDENTITY_COLUMN ------------ --------------- FIRST 1 SECOND 2 THIRD 3

I believe the OR REPLACE was added in IBM i 7.3. In earlier releases I would drop, delete, the Table and then recreate it.

01 DROP TABLE MYLIB.TESTING_TABLE ; 02 CREATE TABLE MYLIB.TESTING_TABLE 03 FOR SYSTEM NAME "TESTTABLE" 04 ( 05 FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL, 06 IDENTITY_COLUMN FOR "ID_COL" BIGINT 07 GENERATED ALWAYS AS IDENTITY, 08 THIRD_COLUMN FOR "THIRD" INT 09 ) ;

Of course the resulting table is empty:

FIRST_COLUMN IDENTITY_COLUMN THIRD_COLUMN ------------ --------------- ------------

In this scenario I should use the Alter Table to add the column as the data is the Table is preserved.

ALTER TABLE MYLIB.TESTING_TABLE ADD THIRD_COLUMN FOR "THIRD" INT

Now, in this partition with IBM i 7.4, I can use the OR REPLACE to re-generate the file with the third column.

01 CREATE OR REPLACE TABLE MYLIB.TESTING_TABLE 02 FOR SYSTEM NAME "TESTTABLE" 03 ( 04 FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL, 05 IDENTITY_COLUMN FOR "ID_COL" BIGINT 06 GENERATED ALWAYS AS IDENTITY, 07 THIRD_COLUMN FOR "THIRD" INT 08 )

The values in the third column will be its default value, in this case null.

FIRST_COLUMN IDENTITY_COLUMN THIRD_COLUMN ------------ --------------- ------------ FIRST 1 - SECOND 2 - THIRD 3 -

There is an optional parameter in the CREATE TABLE called ON REPLACE. This is used to denote what should happen to the data in the Table.

01 CREATE OR REPLACE TABLE MYLIB.TESTING_TABLE 02 FOR SYSTEM NAME "TESTTABLE" 03 ( 04 FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL, 05 IDENTITY_COLUMN FOR "ID_COL" BIGINT 06 GENERATED ALWAYS AS IDENTITY, 07 THIRD_COLUMN FOR "THIRD" INT 08 ) 09 ON REPLACE PRESERVE ROWS

Fortunately, the default is to preserve the data. But I can give it as I have on line 9.

If I want to delete the data from the Table when I recreate it then I would use the following statement.

01 CREATE OR REPLACE TABLE MYLIB.TESTING_TABLE 02 FOR SYSTEM NAME "TESTTABLE" 03 ( 04 FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL, 05 IDENTITY_COLUMN FOR "ID_COL" BIGINT 06 GENERATED ALWAYS AS IDENTITY 07 THIRD_COLUMN FOR "THIRD" INT 08 ) 09 ON REPLACE DELETE ROWS

Line 9: Does what it says.

The other way I create tables is what I call "on the fly", creating a table as an outfile I can use for another process. For example:

01 CREATE TABLE QTEMP.WORKFILE AS 02 (SELECT CAST(USER_NAME AS CHAR(10)) AS USER, 03 STATUS,USRCLS, 04 CAST(TIMESTAMP AS DATE) AS CREATED 05 FROM QSYS2.USER_INFO 06 WHERE STATUS = '*ENABLED') 07 WITH DATA ;

I cannot use the OR REPLACE on a table like this as the ON REPLACE PRESERVE ROWS is not allowed:

01 CREATE OR REPLACE TABLE QTEMP.WORKFILE AS 02 (SELECT CAST(USER_NAME AS CHAR(10)) AS USER, 03 STATUS,USRCLS, 04 CAST(TIMESTAMP AS DATE) AS CREATED 05 FROM QSYS2.USER_INFO 06 WHERE STATUS = '*ENABLED') 07 WITH DATA ;

The above statement generates the following error as the default to preserve the data is assumed:

SQL State: 42613 Vendor Code: -20038 Message: [SQ20038] Clauses not valid in same definition. Cause . . . . . : PRESERVE and WITH DATA cannot be specified at the same time. -- The WITH DATA option cannot be specified with ON REPLACE PRESERVE ROWS. -- A field procedure cannot be defined for a column that has a generated expression. Recovery . . . : Change or remove a clause so that the definition is valid.

But I can use the delete rows, line 9, to clear the existing Table before new results are inserted into it:

01 CREATE OR REPLACE TABLE QTEMP.WKUSERINFO AS 02 (SELECT CAST(USER_NAME AS CHAR(10)) AS USER, 03 STATUS,USRCLS, 04 CAST(TIMESTAMP AS DATE) AS CREATED, 05 LMTCPB 06 FROM QSYS2.USER_INFO 07 WHERE STATUS = '*ENABLED') 08 WITH DATA 09 ON REPLACE DELETE ROWS ;

Having played with the create table command I do feel confident that if I need to recreate the Table to add columns to it I can modify the create table statement I have in a source member, rather than add an Alter table statement to the member.

 

You can learn more about the CREATE TABLE SQL statement from the IBM website here.

 

This article was written for IBM i 7.4 and 7.3.