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 SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm


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