Sometimes we use DGTT to hold complex result sets with many columns.
We must DECLARE the DGTT first… and then second, INSERT into the DGTT
The DECLARE requires us to think about each column name and type if we list them explicitly.
OR. We can DECLARE the DGTT “as (fullselect) with no data” where the “fullselct” is a copy of the SQL SELECT that will be later used with the INSERT into the DGTT.
The advantage of using the “as (fullselect) with no data” is that we don't need to think too much about the column names and types and sizes. They all come from the provided SELECT.
BUT it is subjectively “ugly” to have same “select” repeated twice in the SQL script. First, in the DGTT and second in the INSERT into DGTT.
Why can't DB2 allow us to dynamically DECLARE the DGTT and INSERT into this DGTT in one SQL statement. It seems logical!
And the DB2 syntax for DECLARE GLOBAL TEMPORARY TABLE suggests this might be possible because it obliges us to add ‘with no data' when using FULLSELECT to declare the table.
> The existing documentation is very clear that when ‘when no data' is used that the SELECT is not executed.
How about syntax option alternative/opposite to ‘with no data' to allow us to insert immediately? (you can pick the syntax)
It seems like IBM was thinking about this already! Why else have ‘with no data' unless IBM was thinking of the ‘opposite' functionality for the future!!
ORACLE and SQLSERVER implement “declared temporary tables” differently than DB2… but the concepts exist. And in those database technologies, it is possible to declare the temporary table and insert into it in the same time! Just one SQL statement!
Basically, I would like to do the same with DB2!
|Who would benefit from this IDEA?||application developers and dbas and keen end-users > everyone who use DGTT.|
How should it work?
-- below is an example of using one SQL statement to
-- I WILL NOT BOTHER WITH MY OWN VERSION OF THE NEW
-- SYNTAX... YOU CAN IMPLEMENT HOWEVER YOU WANT
-- declare my dgtt using a SELECT to provide column names and type
|Priority Justification||this extension to DGTT functionality seems logically. also... this functionality exists with oracle and sqlserver (and maybe DB2 for LUW?)... so why not extend to DB2 for zOS!|
|Client Name||Brian Laube|