Create index on Declared Temporary Table fails with -551

Carlos Olson

Create index on Declared Temporary Table fails with -551
I searched the ListServ archives, Google and the SQL Reference manual but cannot figure this out for the life of me. Why can I declare the global temporary table but I cannot create the index. The SQL Reference guide states that all privileges are implicitly granted to public for declaring global temporary tables but I can't find info on privileges required to create the index. I granted use of bufferpools, use of TEMP tablespace, I even granted DBADM on database TEMP to QRSIMAG. What privilege does QRSIMAG below require short of SYSADM? (I have no problem executing this with my SYSADM ID).

PAGE 1
***INPUT STATEMENT:
SET CURRENT SQLID = 'QRSIMAG'
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
SET SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
DECLARE GLOBAL TEMPORARY TABLE XYZ (COLA CHAR (5) NOT NULL,
COLB CHAR (5) NOT NULL, COLC CHAR (5) NOT NULL)
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DECLARE SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
CREATE UNIQUE INDEX SESSION.INDEX1 ON SESSION.XYZ
( COLA )
;
SQLERROR ON CREATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Thanks,
Carlos Olson
Database Administrator

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________

Roger Hecq

Re: Create index on Declared Temporary Table fails with -551
(in response to Carlos Olson)
Have you explicitely granted "all privileges" on the GTT to QRSIMAG? I
recall being frustrated by a similar problem a few years ago.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Carlos Olson
Sent: Thursday, August 20, 2009 1:06 PM
To: [login to unmask email]
Subject: [DB2-L] Create index on Declared Temporary Table fails with
-551

I searched the ListServ archives, Google and the SQL Reference manual
but cannot figure this out for the life of me. Why can I declare the
global temporary table but I cannot create the index. The SQL Reference
guide states that all privileges are implicitly granted to public for
declaring global temporary tables but I can't find info on privileges
required to create the index. I granted use of bufferpools, use of TEMP
tablespace, I even granted DBADM on database TEMP to QRSIMAG. What
privilege does QRSIMAG below require short of SYSADM? (I have no problem
executing this with my SYSADM ID).

PAGE 1
***INPUT STATEMENT:
SET CURRENT SQLID = 'QRSIMAG'
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
SET SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
DECLARE GLOBAL TEMPORARY TABLE XYZ (COLA CHAR (5) NOT NULL, COLB CHAR
(5) NOT NULL, COLC CHAR (5) NOT NULL) ; RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DECLARE SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
CREATE UNIQUE INDEX SESSION.INDEX1 ON SESSION.XYZ ( COLA ) ;
SQLERROR ON CREATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000'
X'FFFFFFFF' X'000
INFORMATION


Thanks,
Carlos Olson
Database Administrator

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a
lot about DB2 version 9 (LUW) that hopefully will enable me to convince
management a timely migration will be of benefit for the business."
_____________________________________________________________________
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________

Mike Vaughan

Re: Create index on Declared Temporary Table fails with -551
(in response to Roger Hecq)
I'm able to execute that sequence just fine without SYSADM (on DB2 9 NFM). However, just a guess -- try qualifying your GTT ("SESSION.XYZ") on the DECLARE.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Carlos Olson
Sent: Thursday, August 20, 2009 12:06 PM
To: [login to unmask email]
Subject: [DB2-L] Create index on Declared Temporary Table fails with -551

I searched the ListServ archives, Google and the SQL Reference manual but cannot figure this out for the life of me. Why can I declare the global temporary table but I cannot create the index. The SQL Reference guide states that all privileges are implicitly granted to public for declaring global temporary tables but I can't find info on privileges required to create the index. I granted use of bufferpools, use of TEMP tablespace, I even granted DBADM on database TEMP to QRSIMAG. What privilege does QRSIMAG below require short of SYSADM? (I have no problem executing this with my SYSADM ID).

PAGE 1
***INPUT STATEMENT:
SET CURRENT SQLID = 'QRSIMAG'
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
SET SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
DECLARE GLOBAL TEMPORARY TABLE XYZ (COLA CHAR (5) NOT NULL, COLB CHAR (5) NOT NULL, COLC CHAR (5) NOT NULL) ; RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DECLARE SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
CREATE UNIQUE INDEX SESSION.INDEX1 ON SESSION.XYZ ( COLA ) ;
SQLERROR ON CREATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Thanks,
Carlos Olson
Database Administrator

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________

Carlos Olson

Re: Create index on Declared Temporary Table fails with -551
(in response to Mike Vaughan)
Thank you Roger Hecq and Mike Vaughn for suggestions but it turns out that this was a good example of bad naming conventions or less than clear error messages. In the message below:

DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG

The OBJECT QRSIMAG is the stogroup of the TEMP database and tablespace used by DGTTs. The problem was resolved with:

GRANT USE OF STOGROUP QRSIMAG TO QRSIMAG

It could have lead to a faster resolution if the stogroup had been named something like SGQRSIMG or if the DB2 error message stated "STOGROUP" instead of "OBJECT".



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Vaughan, Mike
Sent: Thursday, August 20, 2009 1:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Create index on Declared Temporary Table fails with -551

I'm able to execute that sequence just fine without SYSADM (on DB2 9 NFM). However, just a guess -- try qualifying your GTT ("SESSION.XYZ") on the DECLARE.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Carlos Olson
Sent: Thursday, August 20, 2009 12:06 PM
To: [login to unmask email]
Subject: [DB2-L] Create index on Declared Temporary Table fails with -551

I searched the ListServ archives, Google and the SQL Reference manual but cannot figure this out for the life of me. Why can I declare the global temporary table but I cannot create the index. The SQL Reference guide states that all privileges are implicitly granted to public for declaring global temporary tables but I can't find info on privileges required to create the index. I granted use of bufferpools, use of TEMP tablespace, I even granted DBADM on database TEMP to QRSIMAG. What privilege does QRSIMAG below require short of SYSADM? (I have no problem executing this with my SYSADM ID).

PAGE 1
***INPUT STATEMENT:
SET CURRENT SQLID = 'QRSIMAG'
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
SET SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
DECLARE GLOBAL TEMPORARY TABLE XYZ (COLA CHAR (5) NOT NULL, COLB CHAR (5) NOT NULL, COLC CHAR (5) NOT NULL) ; RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DECLARE SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
CREATE UNIQUE INDEX SESSION.INDEX1 ON SESSION.XYZ ( COLA ) ;
SQLERROR ON CREATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Thanks,
Carlos Olson
Database Administrator

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial-
"I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business."
_____________________________________________________________________