There are some things you can use QTEMP for but as a general rule you should
avoid it. Its transient nature is not part of standard SQL.
That said I still use QTEMP for work files and such, but these are vanilla
files (i.e. no primary keys, referential constraints etc.).
Elvis
Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp
-----Original Message-----
Subject: SQL and QTEMP
Just started using SQL's DDL, as opposed to DDS, to create files. Done
about a dozen so far (new and re-vamped) with only cosmetic problems
(oversized fingers on undersized keyboard).
But today I tried creating a file in QTEMP from DDL. The procedure (CL)
issued a SQL9010 error (RUNSQLSTM command failed). Higher up in the
joblog is CPD32B0 (Constraint is not valid):
Cause . . . . . : Constraint *N cannot be added for file ALPBSEL in
library
QTEMP for TYPE value *N. For a referential constraint (TYPE *REFCST),
the
parent file *N in library *N has a delete rule of *N and update rule
of *N.
The constraint was not added because of errors. The reason code is 15.
15 - The file cannot be in the QTEMP library
The DDL is pretty simple:
CREATE TABLE ALPBSEL (
ABLOCAL1 CHAR(5) CCSID 37 NOT NULL ,
ABLOCAL2 CHAR(5) CCSID 37 NOT NULL ,
ABLOCAL3 CHAR(5) CCSID 37 NOT NULL ,
PRIMARY KEY( ABLOCAL3 ,
ABLOCAL2 , ABLOCAL1 ) )
;
LABEL ON TABLE ALPBSEL
IS 'Alabama Price Book Selections' ;
LABEL ON COLUMN ALPBSEL
( ABLOCAL1 IS 'First Local Code' ,
ABLOCAL2 IS 'Second Local Code' ,
ABLOCAL3 IS 'Third Local Code' ) ;
LABEL ON COLUMN ALPBSEL
( ABLOCAL1 TEXT IS 'First local code selected' ,
ABLOCAL2 TEXT IS 'Second local code selected' ,
ABLOCAL3 TEXT IS 'Third local code selected' ) ;
I used the following to try to create it:
RUNSQLSTM SRCFILE(ADSOE200/QSQLSRC) SRCMBR(ALPBSEL) +
COMMIT(*NONE) DFTRDBCOL(QTEMP)
Does SQL just refuse to play in the QTEMP sandbox or did I miss a
parameter in the DDL or RUNSQLSTM?
midrange.com
