Tuesday, January 14, 2014

Add key to SQL table in QTEMP

create table alter table create index key unique

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:

 

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

7 comments:

  1. Add key to SQL table in QTEMP:
    What happens when you are using "Declare Global Temporary Table" in a SQLRPGLE program type and also need to create an index on it? I used CREATE INDEX and compiling my program did not recognize the name of the index. The OS is V7R1.

    ReplyDelete
    Replies
    1. Without your programming code I cannot answer your question.

      I do not want to say much about DECLARE GLOBAL TEMPORARY TABLE as I do not want this to be a spoiler for a future post.

      Thanks

      Delete
    2. Is there any way to specify DESC keyword with the key? I think it is not working in SQL. As earlier I have added a discussion related to this but anybody didn't replied yet.

      Delete
  2. Great article, explains everything very well. I was not able to create an index on a table being created in QTEMP. It was getting frustrating. Your post pinpoints all of the scenarios and why. Thank you.

    ReplyDelete
  3. Nice article...good stuff...clear explaination...

    ReplyDelete
  4. Hello. Thanks for your article.
    One question, is it possible to create a table using "CREATE TABLE LIKE", and duplicate the constraints?

    I cannot find the correct command parameters for that.
    Thanks!

    ReplyDelete

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.