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 SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
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


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