---
title: "DB2 SQL Global Temporary Tables top Usage"
description: "Best example SQL query to create Global temporary table in DB2. This is temporary table applicable to current session. It is like scratch pad table also called an intermediate table"
url: https://srinimf.com/2010/04/28/db2-temp-table/
date: 2010-04-28
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2010/04/pexels-photo-5707610.jpeg?fit=1880%2C1254&ssl=1
categories: ["DB2"]
tags: ["DB2 global temporary table", "Global Temporary Tables"]
type: post
lang: en
---

# DB2 SQL Global Temporary Tables top Usage

**[Global temp](https://srinimf.com/2023/01/09/how-to-create-a-table-from-existing-in-oracle/)orary tables (GTTs)** – if the data being processed will persist within the application’s logical [unit of work](https://srinimf.com/2015/03/17/db2-four-isolation-levels-rr-rs-cs-ur/) rather than repeatedly constituting or materializing data. If the same data is being retrieved or materialized multiple times throughout a program’s execution, you can load the data **one time into a GTT** and reference that table elsewhere in the code.

## Global Temporary Table.

Global temporary tables frequently used in **Data intensive projects**, Data Warehousing projects [and Analytics](https://srinimf.com/2012/06/05/data-warehousing-tools/) projects.

For example, a program has multiple cursors, and each cursor contains multiple table joins. Also, each cursor contains the same table, Table1, that happens to be the [driver table](https://srinimf.com/2018/06/09/real-idea-left-outer-join-you-always-need-to-code-in-db2-read-now/) in each query. As each cursor gets processed, the same rows may have to be retrieved from the database for processing on Table1. This causes extra I/O against the same data.

Related[SQL -904 Top Reasons to Get This Error](https://srinimf.com/2012/07/11/sql-code-904/)

**You May Also Like:** [Key differences of JOINs vs Sub-Query](http://wp.me/pPPyC-H)

So the idea is to retrieve only Table1 data and insert it into a GTT, and then reference the GTT in each cursor. This eliminates the duplicate I/O on Table1. GTTs are also great for creating a table of summarized data that is then referenced in other queries within the logical [unit of work](https://srinimf.com/2018/02/04/cursor-hold-on-commit-you-need-to-know-these-points/).

## Key points of Global Temporary Tables.

1. Can have Indexes and check constraints
2. Can issue update statements
3. Can issue SELECT on Declared temp tables

### Sample SQL query for Global Temporary Tables.

```
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP1
 (EMPNO CHAR(6) NOT NULL,
 FIRSTNME VARCHAR(12) NOT NULL,
 MIDINIT CHAR(1) NOT NULL,
 LASTNAME VARCHAR(15) NOT NULL,
 WORKDEPT CHAR(3),
 PHONENO CHAR(4)
 )
 ON COMMIT DROP TABLE or
 ON COMMIT DELETE / PRESERVE ROWS
 ;

CREATE INDEX SESSION.EMPX1 ON SESSION.TEMP_EMP1
 (LASTNAME ASC)
 ;

INSERT INTO SESSION.TEMP_EMP1
 SELECT EMPNO,
 FIRSTNME,
 MIDINIT,
 LASTNAME,
 WORKDEPT,
 PHONENO
 FROM EMP
 ;
SELECT *
FROM SESSION.TEMP_EMP1;
```

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2010%2F04%2F28%2Fdb2-temp-table%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2010%2F04%2Fpexels-photo-5707610.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20SQL%20Global%20Temporary%20Tables%20top%20Usage)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2010/04/28/db2-temp-table/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2010/04/28/db2-temp-table/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2010/04/28/db2-temp-table/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2010/04/28/db2-temp-table/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20SQL%20Global%20Temporary%20Tables%20top%20Usage&body=https%3A%2F%2Fsrinimf.com%2F2010%2F04%2F28%2Fdb2-temp-table%2F&share=email)
-

### Like this:

Like Loading…