RE: QTEMP and SQL DDL

Way past my bed time :-) Sorry we won't see you in Orlando - but maybe in
the fall.

Constraints are a database thing - it is just the way they are implemented.
Hey, SQL is a database thing <vbg>. You would have the same problem (I
think) if you created a PF in QTEMP (from DDS) and tried to use the ADDPFCST
command to it.

Regards

Paul Tuohy
ComCon
www.comconadvisor.com
www.systemideveloper.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: 06 December 2007 19:48
To: Midrange Systems Technical Discussion
Subject: Re: QTEMP and SQL DDL

Paul,

I've obviously got a lot of work to do before I can understand all of
this. Wish I could be in Orlando for your conference to listen to you
and Skip. Alas, not possible this spring.

Looking back at the prior discussion (thanks, Elvis, but to me it is
Friday -sorta- as I only put in a couple of hours on Fridays), I
experimented further. I did not, for example, realize that constraints
were an SQL thing; thought they were a database thing. Until I, first,
compiled a DDS member and, then, copied an SQL-defined table to another
library. No constraint, of course. Learn something new every day.

Isn't it getting close to your bedtime?

* 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>



Paul Tuohy wrote:

Hi Jerry,

I would be very surprised if you could define a constraint in a QTEMP
library. Constraints are not objects - they are SQL entries in the cross
reference tables (QSYS/QADBFCST IIRC) - and I don't know how one of those
entries could "point" to a QTEMP library for a specific job.

Regards

Paul Tuohy
ComCon
www.comconadvisor.com
www.systemideveloper.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: 06 December 2007 17:13
To: Midrange-L
Subject: QTEMP and SQL DDL

Whenever I try to use RUNSQLSTM to create a table from DDL statements
and use DFTRDBCOL(QTEMP), it fails - but only if the table has a key
constraint. Non-keyed tables are created without incident.

The error in the listing is: SQL0199 30 46 Position 29 Keyword
KEY not expected. Valid tokens: (.

The statement is: CONSTRAINT APOITM UNIQUE KEY( ASCO# , ASDIV# ,
ASDP# , ASACCT , ASSUB , ASVEN# , ASVREF , ASTRCD )
Not much in the job log except RUNSQLSTM failed.

Now, when is use PRIMARY, instead of UNIQUE, I get a job log message
CPD32B0 with reason code 15 - The file cannot be in the QTEMP library.
And the listing has: SQL7008 30 1 Position 1 APOITM in QTEMP not
valid for operation.

I've use RUNSQLSTM to process the DDL statements against other libraries
and the table gets created. And I have created, as I said, non-keyed
tables with DDL in QTEMP.

For what it's worth, I created the table using iNav and then generated
the SQL using iNav over that table.

Did I fubar the definition, or is this an SQL limitation?