Volatile Table Use, Scope, & Ownership within Stored Procedures

Database

Volatile Table Use, Scope, & Ownership within Stored Procedures

I would like to do some select queries within a stored procedure and store the output to a volatile table.  I am not clear on the owernship and scope of the volatile tables. I have run into some errors.  Here is an example stored procedure to pull ages.

--Example: Stored Procedure to pull ages.

replace Procedure database.tmpAge ( IN tblSearchCohort varchar(300) not cs, IN tblOUTPUT varchar(300) not cs)

begin

--Would like to put a validation check for tmpBirthday here....

SET DYNASQL1 = '

create volatile table tmpBirthday as (

SELECT id, birthday

FROM ' || tblSearchCohort || ' as A

INNER JOIN SourceTable as B on A.id = B.id

) with date primary index (id)

on commit preserve rows; '

CALL DBC.SysExecSQL( DYNASQL1 );

SET DYNASQL2 = '

create volatile table ' || tblOUTPUT || ' as (

SELECT id, cast( cast(birthday - indexdate as decimal(18,3) / 365.250 as INT ) as age

FROM ' || tblSearchCohort || ' as A

INNER JOIN tblBirthday as B on A.id = B.id

) with date primary index (id)

on commit preserve rows; '

CALL DBC.SysExecSQL( DYNASQL2 )

DROP TABLE tmpBirthday;

END;

1.  I would like to check the DBC/spool to see whether the table names already exist and send a message that the procedure can't run until the drop conflicting table names.  In the above example, I would like to instantiate a volatile table called "tmpBirthday" as an interim step, use it for computation and potentially drop it before the procedure ends. If the user already has a table tmpBirthday, send a flag that the tmpAge procedure cannot run.

2. I am also running into 'concurrent change conflicts' when users send in table specifications for volatile tables for the tblSearchCohort.  These are not consistent. 

The above example was written on the fly, so there may be some minor inconsistencies. I realize I don't need the interim table for this particular usage, but I will for other SP I have planned.

2 REPLIES
Junior Contributor

Re: Volatile Table Use, Scope, & Ownership within Stored Procedures

There's no way to find out which VTs exist within your current session using dbc tables.

Only "HELP VOLATILE TABLE;" will return that info, but it's not allowed within a SP.

The easiest way to avoid naming problems is a naming convention like: all Volatile Table names start with "VT_", but no real table.

Then you just have to add make shure that it's actually dropped at the end of the SP or you simply start the SP with a DROP and catch a potential "table doesn't exist" in a CONTINUE handler.

Dieter

Re: Volatile Table Use, Scope, & Ownership within Stored Procedures

I have found a way to dynamically name the tables within the procedure which will reduce the likelihood of such conflicts.  However, I am still not clear out how to structure the continue handler with dynamic naming.