Volatile table in Mainframe

UDA
Enthusiast

Volatile table in Mainframe

Hi..

I was writing a Mainframe job to create a volatile table and insert data into it. It is throwing an error when I am directly giving the database name. Error is:

CREATE VOLATILE TABLE DATABASENAME.TABLE_NAME, NO FALLBACK(
COL1 VARCHAR(34),
COL2 BYTEINT,
COL3 SMALLINT)
PRIMARY INDEX (COL1);
*** Failure 5340 Database name, if specified, must be the login user name for a volatile table.
Statement# 1, Info =337

If I change like below, it's running fine.

DATABASE DATABASENAME;
CREATE VOLATILE TABLE TABLE_NAME, NO FALLBACK(
COL1 VARCHAR(34),
COL2 BYTEINT,
COL3 SMALLINT)
PRIMARY INDEX (COL1);

/* Table created */

I haven't seen this problem with ordinary tables. Is it just limited to Volatile tables? Can anyone explain why this is happening?
3 REPLIES
Enthusiast

Re: Volatile table in Mainframe

Volatile tables are temporay tables built in spool, therefore will not be written permenantly to disk, therefore needing no database decleration (hence the error being thrown when you specify a database target).

Your second example may set the default database for the session, but if you did a SEL * from database.tablename you would get an object not found, however sel * from tablename will work fine as it will retrieve the results from the spool based recordset and not from teh default database as you are expecting.

Read up on the different table types in the basic sql course notes and the sql manuals, volatile tables are probably not what you need (also if using a volatile table is what you need and you want to actually use data from within the temporary recordset, ensure you add the ON COMMIT PRESERVE ROWS option to your table DDL!

Good luck, Rob
Junior Contributor

Re: Volatile table in Mainframe

A volatile table is always created within the user, as the error message implies.
If you check the Messages manual you'll find:

Explanation: Volatile tables are associated with the
login user. This error will be returned when an user tries
to create a volatile table qualified with a database name
different from the login user name.

Remedy: Qualify table name with login user name or
do not specify database name in a Create Volatile Table
statement.

Dieter
Enthusiast

Re: Volatile table in Mainframe

Thanks a lot Rob and Dnoeth...

I just wanted to confirm the issue. I have got around the problem by not specifying the Database name.