db2ibm-midrangedb2-400

SQL7008 - Why and how DB2 require users to journal a file even before it is created?


CREATE OR REPLACE TABLE QTEMP/TESTF0( 
 ID         NUM(3) PRIMARY KEY,       
 STRID      CHAR(3),                  
 NUMSI      SMALLINT,                 
 NUMI       INT,                      
 NUMBI      BIGINT                    
 ) RCDFMT TESTF0R;

I'm trying to create a table in QTEMP and the RUNSQLSTM command fails with following ERRMSG

SQL7008  30       1  Position 1 TESTF0 in QTEMP not valid for operation.

I take off the qualified schema QTEMP from the DDL and retry, I get a new error this time, but with a file object in QGPL (not sure how usable it'd be).

SQL7905  20       1  Position 1 Table TESTF0 in QGPL created but was not 
                     journaled.

I know some nuances of the command RUNSQLSTM, hence I tried CHGCURLIB to my test lib and re-tried the command, and it worked fine.

I read about some posts regarding SQL7008 resolution and they all suggested to journal the PF. But that's not possible until we have the file object. That lead me to thinking, could this be possible because the user libraries may be journalled by default, and the above ERRMSGs are due to QGPL being an exception here, and apparently QTEMP may never be journalled??? I'm curious. What's the workaround then for QTEMP tables?

Another thought that came while mucking around with this issue and it could be rather an IBM i Support Query/RFE, but let me first hear you all. - Owing to the fact that IBM is trying to make IBM i modern and more accessible, and pushing the use of DDL over DDS, why is that RUNSQLSTM doesn't have a support for *FILE object yet? Or a better system command that could prompt for the object library where the user wants the SQL objects to be created?


Solution

  • The most common reason for SQL7008 is that the table is not journaled, but there are other reasons, and you are facing one. SQL7008, like many messages, gives more detail about the error and how to recover.

    You probably have reason code 10

    10 -- A constraint or trigger is being added to an invalid type of table, or the maximum number of triggers has been reached, or all nodes of the distributed table are not at the same release level.

    You can create tables in QTEMP, but you can't add constraints (any type) or triggers to a table in QTEMP, so you can't create a table with a primary key. As a workaround to your case you can create a unique index in QTEMP that "constraints" your table.

    There is two ways to tell in which schema the table have to be created using SQL, in RUNSQLSTM or any other tool :

    • qualifying the name like you do with QTEMP
    • setting current schema

    But telling where it has to be created is not enough. No object can be journaled in QTEMP, that's true but in other schemas, tables can be journaled automatically when created usingh SQL under conditions that QGPL does not fill :

    • There is a journal named QSQJRN in the library, CREATE TABLE will attach the table to this journal. CREATE SCHEMA creates a library that contains a journal name QSQJRN among other objects
    • Command STRJRNLIB has been run on the library with the appropriate options.