Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
Can UPDATE/DELETE be done on a CGTT?

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

New User

Joined: 03 Dec 2007
Posts: 58
Location: Bangalore

PostPosted: Mon May 19, 2014 8:32 pm    Post subject: Can UPDATE/DELETE be done on a CGTT?
Reply with quote


I was trying to update a CGTT table and i was getting -551 (auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name).

Can you please let me whether CGTT supports DELETE and UPDATE function.

We have DB2 version 10

Back to top
View user's profile Send private message


Global Moderator

Joined: 03 Oct 2009
Posts: 1789
Location: Bloomington, IL

PostPosted: Tue May 20, 2014 1:39 am    Post subject:
Reply with quote

I am rather sure that it does. The attempted UPDATE or DELETE is being done by an authid that actually does have those privileges, yes?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2467
Location: NY,USA

PostPosted: Wed May 21, 2014 3:40 am    Post subject:
Reply with quote

This is what I have got, I am not sure as never tried it but worth taking a look,

A created temporary table exists only as long as the process that uses it. Temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. When created, the schema for the table is stored in the DB2 system catalog (SYSIBM.SYSTABLES) just like any other table, but the TYPE column is set to 'G' to indicate a global temporary table. Created temporary tables are sometimes referred to as global temporary tables – but this is confusing since declared temporary tables are also referred to as global declared tables.

It is important to remember that a created global temporary table must be created using a DDL CREATE statement before it can be used in any program.

A created temporary table is instantiated when it is referenced in an OPEN, SELECT INTO, INSERT, or DELETE statement, not when it is created. Each application process that uses the temporary table creates a new instance of the table for its use. When using a created temporary table, keep the following in mind:

• Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.

• Indexes can not be created on created temporary tables so all access is by a complete table scan.

• Constraints can not be created on created temporary tables.

• A null is the only default value permitted for columns of a created temporary table.

• Created temporary tables can not be referenced by DB2 utilities.

• Created temporary tables can not be specified as the object of an UPDATE statement.

• When deleting from a created temporary table, all rows must be deleted.

• Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.

Work file data sets are used to manage the data of created temporary tables. The work database (DSNDB07) is used as storage for processing SQL statements that require working storage – not just for created temporary tables. So if you are using created temporary tables be sure to examine the DB2 Installation Guide for tactics to estimate the disk storage required for temporary work files.

When a temporary work file result table is populated using an INSERT statement, it uses work file space. No other process can use the same work file space as that temporary work file table until the table goes away. The space is reclaimed when the application process commits or rolls back, or when it is deallocated, depending which RELEASE option was used when the plan or package was bound. It is a good idea to keep the work files in a separate buffer pool to make it easier to monitor. IFCID 0311 in performance trace class 8 can be used to distinguish these tables from other uses of the work file.
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 Need to delete inactive records from ... Sanchayita Sinha DB2 5 Fri May 29, 2020 10:47 pm
No new posts Unable to delete dataset - VSAM datas... netcrawler JCL & VSAM 20 Fri Jul 26, 2019 2:06 pm
No new posts refresh data from production - update... jzhardy DB2 1 Mon May 06, 2019 5:06 pm
No new posts Need suggestion on CICS READ UPDATE subratarec CICS 8 Fri May 03, 2019 2:23 pm
No new posts How to delete a VSAM dataset from ins... Mike.Fulton JCL & VSAM 6 Fri Feb 15, 2019 5:58 am

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