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?
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!
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.