Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Create Primary key / Index on Global Temporary table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
A_programmers

New User


Joined: 24 Mar 2010
Posts: 6
Location: USA

PostPosted: Sat Oct 23, 2010 12:08 am    Post subject: Create Primary key / Index on Global Temporary table
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

Site Director


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

PostPosted: Sat Oct 23, 2010 1:45 am    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Sat Oct 23, 2010 3:14 pm    Post subject:
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    Post subject:
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: 6
Location: USA

PostPosted: Tue Nov 02, 2010 2:52 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us