Archive for May 5th, 2007

Wall Street Journal had an interesting article posted today about a potential merger between Microsoft and Yahoo – you can read more on that here. The takeover price tag is apparently $50 billion. This is yet another attempt from Microsoft to combat the growing market share in online advertising (remember DoubleClick takeover by Google?) and online office applications that Google apps offer. A potential merger/takeover will up the combined partnership share in the search advertising to 27% as compared to 65% that Google has. It will also narrow down the gap in overall online ads to 13%. This could also potentially mean good opportunities to MSFT’s Zune music player and the Xbox game console.

In the last 2 blog posts, we had covered temporary tables implementation in Oracle and MS SQL Server. In this one, we will cover the basics of the same feature in DB2 LUW. The way to define temporary tables in DB2 is to use ‘DECLARE GLOBAL TEMPORARY TABLE’ syntax. Temporary tables are widely used in stored procedure and functions. We can either declare or create global temporary tables. Syntax to create global temporary table is as under.

In above syntax, when declared,
• Temporary table should always be prefixed with SESSION as schema name. If it is not specified, then it is assumed. But if you specify anything other than SESSION, it will return an error.
• USERTEMP1 is the name of user temporary tablespace.
• WITH REPLACE will make sure that if table already exists in the current session, then it will be replaced and won’t return error SQL0601N (Identical object exists)
• NOT LOGGED indicates that any DML operation are not be logged.
• ON COMMIT indicates the action to be taken when COMMIT happens. In this case, we are deleting rows when commit occurs. We can specify to ‘PRESERVE ROWS’ also instead of ‘DELETE ROWS’.

Since it is easier to explain it using stored procedure rather than SQL/PL block, we will show you, usage of temporary tables using stored procedure.

Open DB2 command editor or any other editor and execute following code to create stored procedure. Make sure that statement terminator is defined as ‘@’.

–Get the data from the table to display it back to client application
BEGIN
DECLARE c2 CURSOR WITH RETURN TO CLIENT FOR
SELECT col1, col2, col3
FROM SESSION.TEMP_TABLE;

OPEN c2;
END;
END
@

In above procedure, we are inserting data into temporary table, committing it and returning data set to calling program. We have specified to delete the rows when commit happens so before returning record set, we are also committing the changes. Issue following command to execute the stored procedure from command editor.

call temp_proc1

Result is shown as under.

COL1 COL2 COL3
———– —————————— ————————–

0 record(s) selected.

Since we have specified to delete rows on commit and issued commit before returning dataset, we don’t see any records returned back to calling program. Now change above procedure and change ‘ON COMMITE DELETE ROWS’ to ‘ON COMMIT PRESERVE ROWS’. Drop and re-create the stored procedure and execute it. This time we will see the result set as we are preserving rows even after commit. Here is the result of execution.

Here we have executed two different stored procedures from two different sessions having same temporary table name. But it still returns the data pertaining to specific session only. Each session has its own copy of temporary table when it is defined as a same name in different session.

One interesting thing, we noticed that in earlier version of DB2 (version 8.2 and lower), during declaring temporary tables in user temporary tablespace which does not exist, it will not give you run time error when you execute the stored procedure but it will not return any data. Apparently it is fixed in version 9. We have performed above tests in version 9 and when we assigned user temporary tablespace which does not exist in the database, it gives error SQL0204N (<tablespace> is an undefined name).

In general following are the key points when temporary tables are used in DB2
• If schema is specified then it should always be SESSION. Any other schema prefix will give an error.
• WITH REPLACE clause is required to avoid error when global temporary table, we are creating alerady exists.
• Temporary tables should be created in user temporry tablespace.
• Specific action can be performed on both ON COMMIT and ON ROLLBACK clause.
• When ON COMMIT DELETE ROWS is used, make sure that COMMIT is the last statement. If commit is used somewhere in between and temporary table is referenced after commit, all data from the temporary table will be gone.