Posts
The session wide temporary tables are a very useful feature. You create the table, use it for your intermediate results and then you don't have to drop it, it's dropped automatically when the session ends.
This, except of the the ease of use that it provides has another advantage; in case of a session or system failure after a commit has been issued the table if not temporary would remain in the database after recovery.With the temporary table you have no such as issues.
An issue arises when you want to use multiple temporary tables with the same name within the same session ;e.g when the user presses a key I use a temporary table to store the product codes and print them in barcode labels. The user might want to re-print additional barcode labels so he must press the key again to initiate printing thus a 'Duplicate table' error occurs since the table has been already created. To overcome this issue I create multiple temporary tables by basing their name on the timestamp. The advandage hereis that you do not care how the table is named since you will not drop it as it is dropped automatically as soon as the session ends thus keeping functionallity and alivieting any conflicts.
in 4GL style :
h_temptable='session.temp'+varchar(date_part('min',date('now')))+
varchar(date_part('sec',date('now')));
declare global temporary table :h_temptable ( c_ylikoy char(10) not null with
default, h_count smallint not null with default) ON COMMIT PRESERVE ROWS WITH NORECOVERY ;
This, except of the the ease of use that it provides has another advantage; in case of a session or system failure after a commit has been issued the table if not temporary would remain in the database after recovery.With the temporary table you have no such as issues.
An issue arises when you want to use multiple temporary tables with the same name within the same session ;e.g when the user presses a key I use a temporary table to store the product codes and print them in barcode labels. The user might want to re-print additional barcode labels so he must press the key again to initiate printing thus a 'Duplicate table' error occurs since the table has been already created. To overcome this issue I create multiple temporary tables by basing their name on the timestamp. The advandage hereis that you do not care how the table is named since you will not drop it as it is dropped automatically as soon as the session ends thus keeping functionallity and alivieting any conflicts.
in 4GL style :
h_temptable='session.temp'+varchar(date_part('min',date('now')))+
varchar(date_part('sec',date('now')));
declare global temporary table :h_temptable ( c_ylikoy char(10) not null with
default, h_count smallint not null with default) ON COMMIT PRESERVE ROWS WITH NORECOVERY ;
Comments