Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Index on Temporary tables created using WITH clause?

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

New User


Joined: 05 Jan 2009
Posts: 4
Location: bangalore

PostPosted: Tue Sep 20, 2011 10:29 am    Post subject: Index on Temporary tables created using WITH clause?
Reply with quote

I have a legacy SQL query. This has many temporary tables, created dynamically using WITH clause.
Eg:

WITH <temp-table name> (column 1, column2) AS
(Select col1, col2 from <table name>
Where col1 = col2
)

Is it possible to create indexes on such temporary tables? These tables are created only for a particular run. Hence I am not aware if any dynamic index could be created on such tables.

Kindly help me on this.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 20, 2011 11:46 pm    Post subject:
Reply with quote

Hi Shruthi,

Welcome to IBMMAINFRAMES!

Example you have given is not a temporary table, its a sort of a result table that can be referenced in a FROM clause of a SELECT statement that follows the WITH keyword. Its called Common Table Expression, shortly CTE.

But, Indexes can be created on a DECLARE GLOBAL TEMPORARY TABLE.

Check out DB2 V9 SQL Reference --> Chapter 5 Statements --> DECLARE GLOBAL TEMPORARY TABLE

Hope this helps,
Sushanth
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us