View previous topic :: View next topic
|
Author |
Message |
A_programmers
New User
Joined: 24 Mar 2010 Posts: 19 Location: USA
|
|
|
|
Hi All,
I am facing problem while creating primary key / index on Global Temporary tables.
I have created Global Temporary table inside COBOL program as :
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TBL
( TEST_ID CHAR(20) NOT NULL PRIMARY KEY,
TEST_NUM CHAR(08) NOT NULL PRIMARY KEY)
I am getting error as below :
IGYPS0226-E DSNH199I DSNHPARS LINE 6381 COL 112 INVALID KEYWORD "PRIMARY"; VALID
SYMBOLS ARE: FOR NOT , )
I tried to create Unique index as :
CREATE UNIQUE INDEX SESSION.TEMP_TBL_X01
ON SESSION.TEMP_TBL
( TEST_ID ASC,
TEST_NUM ASC)
Index creates successfully but during execution I am getting error :
I am getting sql error -551
SQLCA as : h R TESTENV CREATE INDEX BP11
DSNXODD2
42501
Does anybody now, how to create primary key / unique index on Global Temporary tables ? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
DId you look up the meaning of the -551?
At the top of the page is a link to "IBM Manuals" among which are the DB2 Messages & Codes (i prefer the UDB M&C).
If you find something in the manual that is not clear, post what you found and your doubt. Someone will be able to clarify. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
stolen from the answer provided by
BillR
from the other website that you posted this question
obviously without doing any research.
Quote: |
5.31 CREATE GLOBAL TEMPORARY TABLE © Copyright IBM Corp. 1982, 2008
Topic lines 261 to 277 of 383
Restrictions and extensions: Let T denote a temporary table:
Columns of T cannot have default values other than null.
A column of T cannot have a LOB or ROWID data type (or a distinct type
based on one).
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.
etc...
You can refer to T in the following statements:
ALTER FUNCTION
CREATE PROCEDURE
DECLARE TABLE
ALTER PROCEDURE
CREATE SYNONYM
DELETE (if it does not include a WHERE clause)
COMMENT
CREATE TABLE LIKE
CREATE ALIAS
CREATE VIEW
CREATE FUNCTION
DESCRIBE TABLE
DROP TABLE
INSERT
LABEL
SELECT INTO
|
also, DB2 is rather particular when it comes to syntax.
Making-up your own keywords does not fly.
e.g. PRIMARY KEY as part of column declaration. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
your create index failed because the person executing does not have authority on bp11 (which is probably the default buffer pool for indexes) |
|
Back to top |
|
|
A_programmers
New User
Joined: 24 Mar 2010 Posts: 19 Location: USA
|
|
|
|
GuyC,
You are right, the reason was person was not having authority to create index on bp11.
What I thought was, If I have the authority to create table, I should certainly have authority to create index on this table.
But this was not true, so I was getting above error. |
|
Back to top |
|
|
|