how can i generate a unique table name at runtime from db2?

[workingbee]

I’m writing a unittest, which will clear a table and insert a set of values into the table before it starts to test some stored procs. My problem is that when multiple users are running the test at the same time, one user might clear the table after the other user inserts a set of values, which will make the latter user’s test fail.

I am wondering if it’s possible that each user will get a unique table name at runtime so that they will operate on totally different tables, or any other way to solve this problem? Any advice will be highly appreciated, I’ve been banging my head all day for this problem… Thanks a lot

[cburnett]

Presuming that each user connects to DB2 using their own userid, there is no problem here because table names only need to be unique within the qualifying schema name which defaults to the connecting userid.

Thus if FRED connects to the database:

CONNECT TO database USER FRED USING password

and then creates a table TEST:

CREATE TABLE TEST

the table FRED.TEST will be created. If JOE was to login, his table would be called JOE.TEST.

[workingbee]

Thanks a lot for the reply. Unfortunately, all these users will use the same application id to connect to the database, :( And we have very strict DBA and we are only allowed to create tables under some certain schema.

Presuming that each user connects to DB2 using their own userid, there is no problem here because table names only need to be unique within the qualifying schema name which defaults to the connecting userid.

Thus if FRED connects to the database:

CONNECT TO database USER FRED USING password

and then creates a table TEST:

CREATE TABLE TEST

the table FRED.TEST will be created. If JOE was to login, his table would be called JOE.TEST.

[cburnett]

A couple of things you might want to try:

Declared Global Temporary tables are basically in-memory tables which are specific to the session and accessed using SESSION.tablena me.

If you need the data to be persistent, then suggest you use a sequence to generate the next sequence number and use this to build the table name dynamically.