RE: SQL and QTEMP

Jerry,

While SQL doesn't seem to want to create a table with a primary key in QTEMP.

You can create a table without a primary key and add a unique index.

Or you can create the table with a primary key in another library and CRTDUPOBJ to QTEMP.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: Friday, July 20, 2007 1:39 PM
To: Midrange-L
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?


--
* Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
615.995.7024
fax
615.995.1201
email
jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.