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?
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 :
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 :