Create Temp table in DB2

In IBM DB2, you can create temporary tables using the DECLARE GLOBAL TEMPORARY TABLE statement. Temporary tables are session-specific, meaning they are only visible to the session that creates them, and their data is automatically deleted when the session ends.

Here's the syntax for creating a temporary table in DB2:

DECLARE GLOBAL TEMPORARY TABLE table_name (

    column1 datatype1,

    column2 datatype2,

    ...

) ON COMMIT PRESERVE ROWS;

- table_name: This is the name of the temporary table you want to create.

- column1, column2, etc.: These are the columns of the temporary table.

- datatype1, datatype2, etc.: These are the data types of the columns.

- ON COMMIT PRESERVE ROWS: This clause specifies that the data in the temporary table should be preserved until the end of the session. You can also use ON COMMIT DELETE ROWS to delete the data at the end of the transaction.

Here's an example of creating a temporary table in DB2:

DECLARE GLOBAL TEMPORARY TABLE temp_employee (

    emp_id INTEGER,

    emp_name VARCHAR(100),

    emp_salary DECIMAL(10,2)

) ON COMMIT PRESERVE ROWS;

In this example:

- We create a temporary table named temp_employee with three columns: emp_id, emp_name, and emp_salary.

- The data type of emp_id is INTEGER, and the data types of emp_name and emp_salary are VARCHAR and DECIMAL, respectively.

- We specify ON COMMIT PRESERVE ROWS, meaning that the data in the temporary table will be preserved until the end of the session.

After creating the temporary table, you can use it like a regular table within your session. However, keep in mind that the data in the temporary table is only visible within the session that created it, and it will be automatically deleted when the session ends.