[z/OS] TRUNCATE TABLE -551s

Paul Ogborne

[z/OS] TRUNCATE TABLE -551s
Hi Folks,

In z/OS V9 NFM users with DELETE access on base tables are getting -551 using TRUNCATE TABLE.
The latest SQL Reference I could find (SC18-9854-04)?states that DELETE access should work.

Has anybody else had this problem please?

Regards,
Paul Ogborne.
________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Suresh Sane

Re: [z/OS] TRUNCATE TABLE -551s
(in response to Paul Ogborne)

Paul - do you have delete triggers on the table? If so, at least ALTER auth is needed also. You can always use the option to restrict when trigger exists

Thx
Suresh



Date: Fri, 5 Dec 2008 10:38:56 -0500From: [login to unmask email]: [DB2-L] [z/OS] TRUNCATE TABLE -551sTo: [login to unmask email] Folks,In z/OS V9 NFM users with DELETE access on base tables are getting -551 using TRUNCATE TABLE.The latest SQL Reference I could find (SC18-9854-04) states that DELETE access should work.Has anybody else had this problem please?Regards,Paul Ogborne.


AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services
_________________________________________________________________
Send e-mail faster without improving your typing skills.
http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008
______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Paul Ogborne

Re: [z/OS] TRUNCATE TABLE -551s
(in response to Suresh Sane)

Hi Suresh,

No, this is a basic base DB2 table with no triggers.
Do I understand correctly that this is working fine elsewhere then?  I wondered whether a java off-host client could be the cause in some way, but the auth error was repeated in SPUFI.

Regards,

Paul.

P.S. As this is our performance environment where I apply service every 3 months, I don't think it can be because we are behind in that respect either.


-----Original Message-----
From: Suresh Sane <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, 5 Dec 2008 17:19
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s



Paul - do you have delete triggers on the table?  If so, at least ALTER auth is needed also.  You can always use the option to restrict when trigger exists
 
Thx
Suresh

Date: Fri, 5 Dec 2008 10:38:56 -0500
From: [login to unmask email]
Subject: [DB2-L] [z/OS] TRUNCATE TABLE -551s
To: [login to unmask email]

Hi Folks,

In z/OS V9 NFM users with DELETE access on base tables are getting -551 using TRUNCATE TABLE.
The latest SQL Reference I could find (SC18-9854-04) states that DELETE access should work.

Has anybody else had this problem please?

Regards,
Paul Ogborne.

AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.



IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery
preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

Send e-mail faster without improving your typing skills. Get your Hotmail® account.

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the s
ite, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Simpson

Re: [z/OS] TRUNCATE TABLE -551s
(in response to Paul Ogborne)
I just tried the same test and also received the error. ID had DELETE authority on the table but truncate failed. Now I’m curious…



David Simpson

Senior Technical Advisor

Themis Training

[login to unmask email] <blocked::mailto:[login to unmask email]>

http://www.themisinc.com

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Paul Ogborne
Sent: Friday, December 05, 2008 5:18 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s



Hi Suresh,

No, this is a basic base DB2 table with no triggers.
Do I understand correctly that this is working fine elsewhere then? I wondered whether a java off-host client could be the cause in some way, but the auth error was repeated in SPUFI.

Regards,

Paul.

P.S. As this is our performance environment where I apply service every 3 months, I don't think it can be because we are behind in that respect either.


-----Original Message-----
From: Suresh Sane <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, 5 Dec 2008 17:19
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s

Paul - do you have delete triggers on the table? If so, at least ALTER auth is needed also. You can always use the option to restrict when trigger exists

Thx
Suresh

________________________________


Date: Fri, 5 Dec 2008 10:38:56 -0500
From: [login to unmask email]
Subject: [DB2-L] [z/OS] TRUNCATE TABLE -551s
To: [login to unmask email]

Hi Folks,

In z/OS V9 NFM users with DELETE access on base tables are getting -551 using TRUNCATE TABLE.
The latest SQL Reference I could find (SC 18-9854-04) states that DELETE access should work.

Has anybody else had this problem please?

Regards,
Paul Ogborne.

________________________________

AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

________________________________

Send e-mail faster without improving your typing skills. Get your Hotmail® account. < http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008 >

________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2- L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

________________________________

AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up < http://info.aol.co.uk/email1 > for a free AOL Email account with unlimited storage today.



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

David Simpson

Re: [z/OS] TRUNCATE TABLE -551s
(in response to David Simpson)
OK… RTFM produced the following snipit from the SQL Ref Guide:



Additionally, if the IGNORE DELETE TRIGGERS option is specified, the privilege set must include at least one of the following privileges:

>The ALTER privilege for the table

> Ownership of the table

> DBADM authority for the database

> SYSADM authority



Since IGNORE DELETE TRIGGERS is the default, this clause is operative when not specified. The strange thing to me is that the trigger authority provisions also seems to be operative even if there are no delete triggers defined on the table. When I add the clause RESTRICT WHEN DELETE TRIGGERS, then the TRUNCATE works fine.





David Simpson

Senior Technical Advisor

Themis Training

[login to unmask email] <blocked::mailto:[login to unmask email]>

http://www.themisinc.com

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Friday, December 05, 2008 7:11 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s



I just tried the same test and also received the error. ID had DELETE authority on the table but truncate failed. Now I’m curious…



David Simpson

Senior Technical Advisor

Themis Training

[login to unmask email] <blocked::mailto:[login to unmask email]>

http://www.themisinc.com

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Paul Ogborne
Sent: Friday, December 05, 2008 5:18 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s



Hi Suresh,

No, this is a basic base DB2 table with no triggers.
Do I understand correctly that this is working fine elsewhere then? I wondered whether a java off-host client could be the cause in some way, but the auth error was repeated in SPUFI.

Regards,

Paul.

P.S. As this is our performance environment where I apply service every 3 months, I don't think it can be because we are behind in that respect either.


-----Original Message-----
From: Suresh Sane <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, 5 Dec 2008 17:19
Subject: Re: [DB2-L] [z/OS] TRUNCATE TABLE -551s

Paul - do you have delete triggers on the table? If so, at least ALTER auth is needed also. You can always use the option to restrict when trigger exists

Thx
Suresh

________________________________


Date: Fri, 5 Dec 2008 10:38:56 -0500
From: [login to unmask email]
Subject: [DB2-L] [z/OS] TRUNCATE TABLE -551s
To: [login to unmask email]

Hi Folks,

In z/OS V9 NFM users with DELETE access on base tables are getting -551 using TRUNCATE TABLE.
The latest SQL Reference I could find (SC 18-9854-04) states that DELETE access should work.

Has anybody else had this problem please?

Regards,
Paul Ogborne.

________________________________

AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

________________________________

Send e-mail faster without improving your typing skills. Get your Hotmail® account. < http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008 >

________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2- L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

________________________________

AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up < http://info.aol.co.uk/email1 > for a free AOL Email account with unlimited storage today.



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >