| View previous topic :: View next topic |
| Author |
Message |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Wed Mar 11, 2009 6:26 am Post subject: Error defining GTT |
 |
|
i'm introducing a column, FIELDX in addition to the existing columns of a GTT as follows
| Code: | CREATE GLOBAL TEMPORARY TABLE
OWNER01.TABLE_NAME
(
FIELD1 CHAR(03),
FIELD2 CHAR(13),
FIELD3 CHAR(40),
FIELD4 SMALLINT,
FIELD5 DECIMAL(16,0),
FIELD6 CHAR(02),
FIELDX SMALLINT GENERATED ALWAYS AS IDENTITY
(START WITH -1, INCREMENT BY 1,
CYCLE, MINVALUE -3, MAXVALUE 3)
)
CCSID EBCDIC
; |
But when i tried to execute the above CREATE GTT statement i'm getting the following error
| Code: | SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD GENERATED. TOKEN ) , WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 506 0 0 -1 685 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000' X'FFFFFFFF' X'000002AD' X'00000000' SQL DIAGNOSTIC
INFORMATION |
can someone help on this
_________________
Thanks |
|
| Back to top |
|
 |
dragone_007
Beginner
Joined: 18 Mar 2008
Posts: 24
Topics: 6
|
| Posted: Wed Mar 11, 2009 10:47 am Post subject: |
|
|
Hi edkir98,
I've alwas seen coded :CCSID EBCDIC for creating PLAN_TABLES to EXPLAIN SQL statements, not for CREATE-ing TABLES or TEMPORARY TABLES. Try again without CCSID EBCDIC specified and tell me about the result. I'm just curious ... |
|
| Back to top |
|
|
dragone_007
Beginner
Joined: 18 Mar 2008
Posts: 24
Topics: 6
|
| Posted: Wed Mar 11, 2009 10:49 am Post subject: |
|
|
| I'forgot to ask if your table has any Primary Key or any CONSTRAINT ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Mar 11, 2009 11:10 am Post subject: |
|
|
edkir98,
If I remember correctly CREATE GTT canNOT have an identity column , But a DECLARE GTT can have an identity column.
Kolusu |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Mar 12, 2009 3:45 am Post subject: |
|
|
hi Kolusu,
1) i tried removing the CCSID EBCDIC but still then am getting the same error.
2)
the following is an extract from
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH10/1.2.1.2.2?SHELF=&DT=20010209162045
| Quote: | | "If the base table or created temporary table that you copy has identity columns, you can specify that the corresponding columns in the declared temporary table are also identity columns. Do that by specifying the INCLUDING IDENTITY COLUMN ATTRIBUTES clause when you define the declared temporary table. " |
so think a CREATE GTT can have identity columns..
hi dragone_007, i'm not sure if in a CREATE GTT there can be a primary index and Check constraint defined.. but i'm not using any indexes or check constraints.
_________________
Thanks |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Mar 12, 2009 3:53 am Post subject: |
|
|
hi dragone_007
Please see
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/5.30?DT=20010718164132
T is a CREATE GTT
| Quote: |
T cannot have unique constraints, referential constraints, or check constraints.
T cannot be defined as the parent in a referential constraint.
T cannot be referenced in:
A CREATE INDEX statement.
A LOCK TABLE statement.
As the object of an UPDATE statement in which the object is T or a view of T. However, you can reference T in the WHERE clause of an UPDATE statement.
DB2 utility commands.
|
_________________
Thanks |
|
| Back to top |
|
|
dragone_007
Beginner
Joined: 18 Mar 2008
Posts: 24
Topics: 6
|
| Posted: Thu Mar 12, 2009 5:31 am Post subject: |
|
|
Hi edkir98,
Thanks for your clarification regarding GTT |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Mar 12, 2009 11:07 am Post subject: |
|
|
Can someone help me regarding the error i got..
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 12, 2009 11:10 am Post subject: |
|
|
edkir98,
 Did you even read my Post properly? Understand the differences between a DECLARE and CREATE GTT.
Create GTT does NOT allow having an identity column where as DECLARE GTT will let you have as it is based off a table
kolusu |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Mar 19, 2009 9:09 am Post subject: |
|
|
hi Kolusu,
the point i wanted to make was that there could be identity columns in CREATE GTT as well
you said
| Quote: | | Create GTT does NOT allow having an identity column |
But the manual says
| Quote: | | If the base table or created temporary table that you copy has identity columns, ...... |
which obviosuly means that a Create GTT can have identity columns..
that was the point i wanted to make..
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
