Add key to SQL table in QTEMP

In my last post I discussed how you could Create a SQL table on the fly, and Manuel Moreno posed the question if it was possible to create a table like this with a key?

I consider this an excellent question, therefore, I created this post in response to his question.

I will be using the same scenario as I did in the original post. There I create a SQL table in QTEMP by using the CREATE TABLE AS SQL statement:

CREATE TABLE QTEMP/OUTFILE AS (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT FROM INFILE WHERE SOURCE = '04') WITH DATA

The statement above creates a table in QTEMP that contains the columns given and any record from the file INFILE where the field SOURCE is equal to ‘04’. But the table does not have an key.

The part of the CREATE TABLE AS statement that defines the key for the table is PRIMARY KEY. This creates a unique key, equivalent of a DDS file with the UNIQUE keyword in it. It is not possible to create just a non-unique keyed table.

When I tried to use the add key I was prevented for two reasons:

Firstly, the CREATE TABLE AS statement does not appear to allow me to use the PRIMARY KEY with a SELECT, that I used to create the table in this scenario. I was only able to use the PRIMARY KEY if I defined the columns.

Secondly, it is not possible to add a key to a table created in QTEMP.

If I define the columns I can define the PRIMARY KEY:

CREATE TABLE MYLIB/OUTFILE (SOURCE CHAR(2) NOT NULL, ORDNO CHAR(10) NOT NULL, CUSNO CHAR(7) NOT NULL, PART CHAR(15) NOT NULL, SPEC CHAR(2) NOT NULL, ORQTY DEC(10,0) NOT NULL, DUEDT DEC(7,0), PRIMARY KEY (CUSNO,ORDNO,DUEDT)) RCDFMT TEST_FMT

I prefer to create the table using the SELECT then I can add the key using the ALTER TABLE:

CREATE TABLE MYLIB/OUTFILE AS (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT FROM INFILE) DEFINITION ONLY RCDFMT TEST_FMT ALTER TABLE MYLIB/OUTFILE ADD PRIMARY KEY (CUSNO,ORDNO,DUEDT)

I would then have to move the table, no matter which of the two ways I created it, to QTEMP. I would like to have used the MOVOBJ command, but it will not allow me to move an object to QTEMP. Therefore, I use the CRTDUPOBJ command followed by a DLTF:

CRTDUPOBJ OBJ(OUTFILE) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(QTEMP) CST(*NO) TRG(*NO) DLTF FILE(MYLIB/OUTFILE)

And then I would use the INSERT SQL statement to select and copy the records from the file INFILE into the table in QTEMP:

INSERT INTO QTEMP/OUTFILE SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT FROM INFILE WHERE SOURCE = '04'

In my opinion the easiest option would be to create the table QTEMP and then build an index over it. An index is the SQL equivalent of a logical file:

CREATE TABLE QTEMP/OUTFILE AS (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT FROM INFILE WHERE SOURCE = '04') WITH DATA CREATE INDEX QTEMP/OUTIDX1 ON QTEMP/OUTFILE (CUSNO,PART) RCDFMT INDEX1 CREATE UNIQUE INDEX QTEMP/OUTIDX2 ON QTEMP/OUTFILE (CUSNO,ORDNO,DUEDT) RCDFMT INDEX2

By using CREATE INDEX OUTIDX1 is created without a unique key.

The CREATE UNIQUE INDEX OUTIDX2 is created with a unique key. If there are rows in the table with duplicate values in the key fields the index will not be created.

Now I can use the index to access the data in the table.

 

You can learn more about these from the IBM web site:

• ALTER TABLE
• CREATE INDEX and CREATE UNIQUE INDEX
• INSERT
• SELECT

 

This article was written for IBM i 7.1, and it should work with earlier releases too.