Wednesday, September 30, 2020

Create or replace for SQL Tables

create or replace table on replace values

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.

8 comments:

  1. GOOD TO KNOW, I thought you were going to touch on OVERRIDING USER or SYSTEM values....with the identity columns.

    ReplyDelete
  2. Very good information thank you for this and your other posts!

    ReplyDelete
  3. Can we have concept of identity column in physical file creation also??

    ReplyDelete
    Replies
    1. Identity columns are not supported by DDS.

      You would have to compile your DDS file, and then add the identity column using the SQL ALTER TABLE statement.

      Delete
  4. Are there any short comings for having identity column in tables?? I do not generally see this in tables in my project. Its a good to have thing, then why they do not use it. Is it because people are not aware about it or there are some drawbacks with having identity column in a table.

    ReplyDelete
    Replies
    1. Not that I have found.

      It becomes a way to guarantee a unique record id.

      Then you can use the identity column from one table in another, for example an identity column from the Order Header in the Order Detail.

      Why are they not widely used? I have no idea. It could be that people are just not aware of them.

      Delete
  5. In this type of example, is there any benefit to using CREATE OR REPLACE TABLE rather than ALTER TABLE? Also a small side note, it's a bit confusing that the third column is called THIRD right after the data in the first example is FIRST SECOND and THIRD.

    ReplyDelete
    Replies
    1. As far as I have found using the CREATE OR REPLACE TABLE and the ALTER TABLE are equally useful.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.