5.8 Global variables

< Day Day Up >

Global variables in Oracle are distinct for each connected user. The variables are global to the connection. The appropriate conversion is to use DB2 global temporary tables.

Example 5-27 shows you a simple Oracle package with the definition and initialization of two global variables.

Example 5-27: Definition of global variables in Oracle

 CREATE OR REPLACE PACKAGE pkg_gv IS    global_variable_1  VARCHAR2(128) := NULL;    global_variable_2  INTEGER       := 1; END pkg_gv;

Definition and initialization

The following Example 5-28 is a DB2 stored procedure with the definition of a global temporary table. The table contains all the global variables you need within a session. Each column of the table corresponds to a global variable. The table needs only to have one row with the respective values.

Example 5-28: Temporary table with global variables

 CREATE PROCEDURE init_global_variables LANGUAGE SQL BEGIN    -- declare temporary table for global variables    DECLARE GLOBAL TEMPORARY TABLE session.global_variables (        global_variable_1    VARCHAR(128)        ,global_variable_2   INTEGER )    ON COMMIT PRESERVE ROWS;    -- initialize global variables    INSERT INTO session.global_variables (        global_variable_1       ,global_variable_2 )    VALUES ( null            ,0 ); END!

The INSERT statement is necessary to initialize the global variables.

Note

ON COMMIT PRESERVE ROWS indicates that rows of the table will be preserved after ending a transaction with COMMIT.

Using a procedure to initialize the temporary table yields two key benefits:

The procedure developer does not have to hunt through application code (which may be maintained by another person) to find the DDL of the temporary table.
The definition of the table is centralized at one place. Should the global variables require changes, you do not have to search for all the declarations, just change the definition at one place.

As mentioned before, the values of the global variables are distinct for each connection. This means that you have to define the DB2 global temporary table at the beginning of each session. To do this with the definition of Example 5-28, you need to call the stored procedure init_global_variables after you connect to the DB2 database in your application.

When developing/converting other procedures, which rely on these global variables, you will have to first run the init_global_variables procedure first to define the temporary table in the current connection. Otherwise, DB2 will not be able to resolve references to the temporary table at build time.

Setting values of global variables

To set a new value to a global variable, use an UPDATE statement to the corresponding column in the temporary table GLOBAL_VARIABLES:

    UPDATE session.global_variables       SET global_variable_1 = new_value;

Getting values of global variables

The get a value of a global variable use a SELECT statement to the corresponding column in the temporary table GLOBAL_VARIABLES:

    SELECT global_variable_1       INTO gv_value       FROM session.global_variables       FETCH FIRST 1 ROWS ONLY;

Under normal circumstances you only have one row in the GLOBAL_VARIABLES table. To be sure to get not more than one row as result of the SELECT statement use the FETCH FIRST 1 ROWS ONLY clause.

To protect user from direct access to the global temporary table you may optionally encapsulate the statements to set/get values in stored procedures. In this case, you have to implement a procedure for each global variable. After that, you have to grant the user the appropriate authority.

Using INOUT parameters

When using only a few global variables shared by a few procedures, it might be more practical to simply convert the global variables as parameters. In this case, change the parameter definition of the procedures (which uses global variables) by adding an INOUT parameter for each of the global variables.

< Day Day Up >