DBINFO Function

The following diagram shows the syntax of the DBINFO function.

DBINFO Function:
 
|--DBINFO------------------------------------------------------->
 
>--(--+-'dbspace'--,--+-tblspace_num-+-----------------------------------+--)--|
      |               '-expression---'                                   |
      +-+-'sqlca.sqlerrd1'-+---------------------------------------------+
      | '-'sqlca.sqlerrd2'-'                                             |
      |  (1)                                                             |
      '------+-'sessionid'---------------------------------------------+-'
             +-'dbhostname'--------------------------------------------+
             +-+-'version'--,--'specifier'-+---------------------------+
             | '-'serial8'-----------------'                           |
             |  (2)                                                    |
             '------+-'coserverid'-----------------------------------+-'
                    +-'coserverid'--,--table.column--,--'currentrow'-+
                    '-'dbspace'--,--table.column--,--'currentrow'----'
 

1. Informix extension
2. Extended Parallel Server only

DBINFO Options

The DBINFO function is actually a set of functions that return different types of information about the database. To invoke each function, specify a particular option after the DBINFO keyword. You can use any DBINFO option anywhere within SQL statements and within UDRs.

The following table shows the different types of database information that you can retrieve with the DBINFO options. The Option column shows the name of each DBINFO option. The Effect column shows the type of database information that the option retrieves.

The Page column shows the page where you can find more information about a given option.

Using the 'dbspace' Option Followed by a Tblspace Number

The 'dbspace' option returns a character string that contains the name of the dbspace that corresponds to a tblspace number. You must supply an additional parameter, either tblspace_num or an expression that evaluates to tblspace_num. The following example uses the 'dbspace' option. First, it queries the systables system catalog table to determine the tblspace_num for the table customer, then it executes the function to determine the dbspace name.

SELECT tabname, partnum FROM systables
   where tabname = 'customer'

If the statement returns a partition number of 1048892, you insert that value into the second argument to find which dbspace contains the customer table, as the following example shows:

SELECT DBINFO ('dbspace', 1048892) FROM systables
   where tabname = 'customer'

If the table for which you want to know the dbspace name is fragmented, you must query the sysfragments system catalog table to find out the tblspace number of each table fragment. Then you must supply each tblspace number in a separate DBINFO query to find out all the dbspaces across which a table is fragmented.

Using the 'sqlca.sqlerrd1' Option

The 'sqlca.sqlerrd1' option returns a single integer that provides the last serial value that is inserted into a table. To ensure valid results, use this option immediately following a singleton INSERT statement that inserts a single row with a serial value into a table.

The following example uses the 'sqlca.sqlerrd1' option:

EXEC SQL create table fst_tab (ordernum serial, partnum int);
EXEC SQL create table sec_tab (ordernum serial);
EXEC SQL insert into fst_tab VALUES (0,1);
EXEC SQL insert into fst_tab VALUES (0,4);
EXEC SQL insert into fst_tab VALUES (0,6);
EXEC SQL insert into sec_tab values (dbinfo('sqlca.sqlerrd1'));

This example inserts a row that contains a primary-key serial value into the fst_tab table, and then uses the DBINFO function to insert the same serial value into the sec_tab table. The value that the DBINFO function returns is the serial value of the last row that is inserted into fst_tab.

Using the 'sqlca.sqlerrd2' Option

The 'sqlca.sqlerrd2' option returns a single integer that provides the number of rows that SELECT, INSERT, DELETE, UPDATE, EXECUTE PROCEDURE, and EXECUTE FUNCTION statements processed. To ensure valid results, use this option after SELECT, EXECUTE PROCEDURE, and EXECUTE FUNCTION statements have completed executing. In addition, to ensure valid results when you use this option within cursors, make sure that all rows are fetched before the cursors are closed.

The following example shows an SPL routine that uses the 'sqlca.sqlerrd2' option to determine the number of rows that are deleted from a table:

CREATE FUNCTION del_rows (pnumb int)
RETURNING int;

DEFINE nrows int;

DELETE FROM fst_tab WHERE part_number = pnumb;
LET nrows = DBINFO('sqlca.sqlerrd2');
RETURN nrows;

END FUNCTION

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 IBM Informix Administrator's Guide. For further information on the sysmaster database and the SMI tables, see the IBM Informix 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;

Using the 'dbhostname' Option

You can use the 'dbhostname' option to retrieve the hostname of the database server to which a database client is connected. This option retrieves the physical computer name of the computer on which the database server is running.

In the following example, the user enters the 'dbhostname' option of DBINFO in a SELECT statement to retrieve the hostname of the database server to which DB–Access is connected:

SELECT DBINFO('dbhostname') 
   FROM systables
   WHERE tabid = 1

The following table shows the result of this query.

Using the 'version' Option

You can use the 'version' option of the DBINFO function to retrieve the exact version number of the database server against which the client application is running. This option retrieves the exact version string from the message log. The value of the full version string is the same as that displayed by the -V option of the oninit utility.

Use the specifier parameter of the 'version' option to specify which part of the version string you want to retrieve. The following table lists the values that you can enter in the specifier parameter, shows which part of the version string is returned for each specifier value, and gives an example of what is returned by each value of specifier.

Each example returns part of the complete version string Dynamic Server Version 10.00.UC1.

The following example shows how to use the 'version' option of DBINFO in a SELECT statement to retrieve the major version number of the database server that the DB–Access client is connected to:

SELECT DBINFO('version', 'major')
   FROM systables
   WHERE tabid = 1

The following table shows the result of this query.

Using the 'serial8' Option

The 'serial8' option returns a single integer that provides the last SERIAL8 value that is inserted into a table. To ensure valid results, use this option immediately following an INSERT statement that inserts a SERIAL8 value.

The following example uses the 'serial8' option:

EXEC SQL create table fst_tab 
   (ordernum serial8, partnum int);
EXEC SQL create table sec_tab (ordernum serial8);

EXEC SQL insert into fst_tab VALUES (0,1);
EXEC SQL insert into fst_tab VALUES (0,4);
EXEC SQL insert into fst_tab VALUES (0,6);

EXEC SQL insert into sec_tab 
   select dbinfo('serial8')
   from fst_tab where partnum = 6;

This example inserts a row that contains a primary-key SERIAL8 value into the fst_tab table and then uses the DBINFO function to insert the same SERIAL8 value into the sec_tab table. The value that the DBINFO function returns is the SERIAL8 value of the last row that is inserted into fst_tab. The subquery in the last line contains a WHERE clause so that a single value is returned.

Using the 'coserverid' Option with No Other Arguments (XPS)

The 'coserverid' option with no other arguments returns a single integer that corresponds to the coserver ID of the coserver to which the user who entered the query is connected.

Suppose that you use the following statement to create the mytab table:

CREATE TABLE mytab (mycol INT) 
   FRAGMENT BY EXPRESSION
      mycol < 5 in rootdbs.1
      mycol > 5 in rootdbs.2

Further, suppose that the dbspace named rootdbs.1 resides on coserver 1, and the dbspace named rootdbs.2 resides on coserver 2. Also suppose that you use the following statements to insert rows into the mytab table:

INSERT INTO mytab VALUES ('1');
INSERT INTO mytab VALUES ('6');

Finally, suppose that you are logged on to coserver 1 when you make the following query, which displays the values of all columns in the row where the value of the mycol column is 1. This query also displays the coserver ID of the coserver to which you are logged on when you enter the query:

SELECT *, DBINFO ('coserverid') AS cid
   FROM mytab
   WHERE mycol = 1

The following table shows the result of this query.

mycol

cid

1

1

Using the 'coserverid' Option Followed by Table and Column Names (XPS)

Use the 'coserverid' option followed by the table name and column name and the 'currentrow' string to find out the coserver ID where each row in a specified table is located. This option is especially useful when you fragment a table across multiple coservers.

In the following example, the user asks to see all columns and rows of the mytab table as well as the coserver ID of the coserver where each row resides. For a description of the mytab table, see Using the 'coserverid' Option with No Other Arguments (XPS):

SELECT *, DBINFO ('coserverid', mytab.mycol, 'currentrow')
   AS dbsp
   FROM mytab

The following table shows the result of this query.

mycol

cid

1

1

6

2

The column that you specify in the DBINFO function can be any column in the specified table.

Using the 'dbspace' Option Followed by Table and Column Names (XPS)

Use the 'dbspace ' option followed by the table name and column name and the 'currentrow' string to find out the name of the dbspace where each row in a specified table is located. This option is especially useful when you fragment a table across multiple dbspaces.

In the following example, the user asks to see all columns and rows of the mytab table as well as the name of the dbspace where each row resides. For a description of the mytab table, see Using the 'coserverid' Option with No Other Arguments (XPS).

SELECT *, DBINFO ('dbspace', mytab.mycol, 'currentrow')
   AS dbsp
   FROM mytab

The following table shows the result of this query.

mycol

cid

1

rootdbs.1

6

rootdbs.2

The column arguments to DBINFO can be any columns in the specified table.