Using the 'sessionid' Option

The 'sessionid' option of the DBINFO function returns the session ID of your current session. When a client application makes a connection to the database server, the database server starts a session with the client and assigns a session ID for the client. The session ID serves as a unique identifier for a given connection between a client and a database server.

The database server stores the value of the session ID in a data structure in shared memory that is called the session control block. The session control block for a given session also includes the user ID, the process ID of the client, the name of the host computer, and a variety of status flags.

When you specify the 'sessionid' option, the database server retrieves the session ID of your current session from the session control block and returns this value to you as an integer. Some of the System-Monitoring Interface (SMI) tables in the sysmaster database include a column for session IDs, so you can use the session ID that the DBINFO function obtained to extract information about your own session from these SMI tables. For further information on the session control block, see the HCL OneDB™ Administrator's Guide. For further information on the sysmaster database and the SMI tables, see the HCL OneDB Administrator's Reference.

In the following example, the user specifies the DBINFO function in a SELECT statement to obtain the value of the current session ID. The user poses this query against the systables system catalog table and uses a WHERE clause to limit the query result to a single row.

SELECT DBINFO('sessionid') AS my_sessionid
   FROM systables
   WHERE tabname = 'systables';

In the preceding example, the SELECT statement queries against the systables system catalog table. You can, however, obtain the session ID of the current session by querying against any system catalog table or user table in the database. For example, you can enter the following query to obtain the session ID of your current session:

SELECT DBINFO('sessionid') AS user_sessionid
   FROM customer
   WHERE customer_num = 101;

You can use the DBINFO 'sessionid' option not only in SQL statements but also in SPL routines. The following example shows an SPL function that returns the value of the current session ID to the calling program or routine:

CREATE FUNCTION get_sess()
   RETURNING INT;
   RETURN DBINFO('sessionid');
END FUNCTION;