IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Create Primary key / Index on Global Temporary table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 19
Location: USA

PostPosted: Sat Oct 23, 2010 12:08 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Oct 23, 2010 1:45 am
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Oct 23, 2010 3:14 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 26, 2010 1:38 pm
Reply with quote

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
View user's profile Send private message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 19
Location: USA

PostPosted: Tue Nov 02, 2010 2:52 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts How to create a list of SAR jobs with... CA Products 3
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top