creating transitory table

Hi Everyone, I have an requirement to use a temporary table which have to be created globally and need user level access to that table to view only particular user data. Same concept like GTT(Global temporary table) table. Is this possible in Informix, or any other way to fulfill this requirement. Thanks buddies..!

There is no direct equivalent of the Oracle GTT concept in Informix. There are two options: 1) Use a normal table but include a user_id column that is auto-filled with the USER constant. Then create a VIEW on the table that does not return the user_id column but filters for only rows that contain the current user's ID in the user_id column. If you need session specific separation for multiple sessions with the same user_id, then use session ID instead (you will have to use an insert trigger on the table to insert the session id (DBINFO('sessionid'). If you always want to have to user's view empty when they connect, just have their ssydbclose() function delete all rows for that user_id or sessionid. It is best if the users only deal with the VIEW and never the real table, so you will have to create INSTEAD OF triggers on the VIEW for inserts, updates, and deletes. 2) Have the user's sysdbopen() function create a temp table when the session connects. Then every session will automatically have a private temp table that they can use and it will be destroyed when they disconnect.