SQL DB2: Declare Temporary Table Add Column

Question

I have declared and temporary table successfully.

DECLARE GLOBAL TEMPORARY TABLE SESSION.MY_TEMP_TABLE
LIKE MYTABLES.AN_EXISTING_TABLE
INCLUDING IDENTITY
ON COMMIT PRESERVE ROWS
WITH REPLACE;

I then use the following to merge two tables and output this into my temporary table:

INSERT INTO SESSION.MY_TEMP_TABLE
SELECT a.*
FROM (SELECT * FROM MYTABLES.TABLE_A) as a
     LEFT JOIN
     (SELECT * FROM MYTABLES.TABLE_B) as b
ON a.KEY=b.KEY;

Now this above all works.

ISSUE: I now want to merge on two new variables from a further table (MYTABLES.TABLE_C), however it will not let me because I declared the temporary table with a certain number of columns and I am trying to add further columns. I did a google and it seems ALTER TABLE will not work with DECLARED TEMPORARY tables, any help please?

Answer 1

Session tables (DGTT) need to be declared with all the required columns , as you cannot use alter table to add additional columns to a session table.

A way around this limitation is to use session tables in a different manner, specifically to create a new session table on demand with whatever additional columns you need (possibly also including the data from other tables). This can be very fast when you use the NOT LOGGED option. It also works well if your session table uses DISTRIBUTE BY HASH on environments that support that feature.

Here is an example that shows 3 session tables, the third of which has all columns from the first two tables:

declare global temporary table session.m1 like emp including identity on commit preserve rows with replace not logged;

declare global temporary table session.m2 like org including identity on commit preserve rows with replace not logged;

declare global temporary table session.m3 as (select * from session.m1, session.m2) with data with replace not logged;

If you do not want to populate the session table at time of declaration you can use DEFINITION ONLY instead of WITH DATA (or use WITH NO DATA) and populate the table later via insert or merge.