I have created temporary tables in other DBMSs (Postgresql, Oracle, MariaDB, SQLite, MSSQL with modification) like this:

CREATE TEMPORARY TABLE test(
    id INT NOT NULL PRIMARY KEY,
    data VARCHAR(255)
);

When I try this in Db2, I get a most helpful error message:

SQL Error [42995]: The statement was not processed because the statement refers to a created temporary table or a declared temporary table and includes functionality that cannot be used with temporary tables.. SQLCODE=-526, SQLSTATE=42995, DRIVER=4.33.31

… which, I must confess, doesn’t enlighten me very much.

On experimenting, I’ve narrowed the fault down to the PRIMARY KEY.

Is there a way of including a primary key in a DB2 temporary table?

I created a table space using:

CREATE USER TEMPORARY TABLESPACE temp_tablespace
MANAGED BY AUTOMATIC STORAGE;

which was basically copied from somewhere.

I am using Db2 12 in a Docker container (LUW).

I have created temporary tables in other DBMSs (Postgresql, Oracle, MariaDB, SQLite, MSSQL with modification) like this:

CREATE TEMPORARY TABLE test(
    id INT NOT NULL PRIMARY KEY,
    data VARCHAR(255)
);

When I try this in Db2, I get a most helpful error message:

SQL Error [42995]: The statement was not processed because the statement refers to a created temporary table or a declared temporary table and includes functionality that cannot be used with temporary tables.. SQLCODE=-526, SQLSTATE=42995, DRIVER=4.33.31

… which, I must confess, doesn’t enlighten me very much.

On experimenting, I’ve narrowed the fault down to the PRIMARY KEY.

Is there a way of including a primary key in a DB2 temporary table?

I created a table space using:

CREATE USER TEMPORARY TABLESPACE temp_tablespace
MANAGED BY AUTOMATIC STORAGE;

which was basically copied from somewhere.

I am using Db2 12 in a Docker container (LUW).

• db2
• temp-tables

1 Answer 1

While primary keys cannot be created on temporary tables in DB2, regular indices can, according to the documentation:

You can also create indexes on the declared temporary table.

So what you could to partially simulate a primary key would be to add a unique index which is not null:

CREATE TEMPORARY TABLE test(
    id INT NOT NULL,
    data VARCHAR(255)
);

CREATE UNIQUE INDEX idx ON test (id);