Level: Introductory Knut Stolze, Information Integration Development, IBM Germany
Paul Yip, DB2 Partner Enablement, IBM Toronto Lab
13 Feb 2003 All is revealed! It's easier than you think to get and use an application ID for such purposes as testing connections and for auditing in connection pooling architectures. Sample binaries are included.
Introduction
Customers often ask us if there is any notion of a session or application identifier in IBM® DB2® Universal Database™ (UDB), and if so, how to access it. Applications can use this information to determine the state of connections, or for auditing purposes when users modify sensitive data.
Well, there is good news and bad news. The good news is that there is indeed an application identifier with every connection to a database. Starting with DB2 UDB Version 8.2 (DB2 V8.2) you also have the SQL function APPLICATION_ID to retrieve that application ID programmatically. The bad news is that DB2 releases prior to DB2 V8.2 do not provide a built-in SQL function to retrieve this easily -- you'll need to write one yourself. The function is not difficult to write or build, and Part 1 of this article will show you how to do this quickly. Part 2 of this article discusses some sample scenarios on how to use application ID.
Part 1. Building a function to get a connection's application ID
Please note that you can entirely skip ahead to part 2 if you are using DB2 V8.2 (or later) because the function described here is provided as a built-in function in the SYSFUN schema in this version. The sample scenarios explained in the next part are equally applicable for the built-in function.
To build this function, you will need to have the DB2 Application Development Client and a C or Java™ compiler installed.
(If you do not have a C compiler, see Installing the Binaries).
Issue all commands locally at the server as the database instance owner (for example, db2inst1).
To begin, the information that the function retrieves from DB2 is called an application ID. The application ID that is retrieved is the same value that you see when you issue
LIST APPLICATIONS
from the DB2 Command Line Processor (CLP):
Listing 1. Output of LIST APPLICATIONS from the CLP
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- ------------ ------ ------------------------ -------- -----
DB2ADMIN db2bp.exe 5 *LOCAL.DB2.00BE85034416 SAMPLE 1
|
In Listing 1, the fourth column indicates the application ID for the connection is *LOCAL.DB2.00BE85034416. For remote connections, *LOCAL is replaced with a hexadecimal representation of the IP address of the client machine.
The C function
The following C function (in Listing 2) will allow us to retrieve a connection's application ID.
Listing 2. A C function to retrieve a connection's application ID
#include <string.h>
#include <sqludf.h>
void SQL_API_FN getApplicationId(
SQLUDF_CHAR *applId, SQLUDF_NULLIND *applId_ind,
SQLUDF_TRAIL_ARGS, SQLUDF_DBINFO *dbinfo)
{
strncpy(applId, dbinfo->appl_id, 128);
*applId_ind = 0;
}
|
Building the C function
To build this function, you'll need an export file that defines the entry points for the library. The export file will be different depending on your platform:
After you've created one of these two files (depending on your platform) in the same directory where you put the C source code file (application_id.c), compile and link the code. DB2 provides a build routine script (bldrtn) to simplify the process. The script is located in the sqllib/samples/c directory.
DB2 V7: For Version DB2 UDB 7.x, use the script bldudf on UNIX and bldmudf on Windows. The syntax for its invocation is slightly different from what it is in Version 8. View the files for details on how to run those build scripts.
To build the user-defined function (UDF), execute:
INSTHOME
/sqllib/samples/c/bldrtn application_id
|
where
INSTHOME
is the path to the instance home directory. For example:
c:\program files\ibm\sqllib\samples\c\bldrtn application_id
or
/home/db2inst1/sqllib/samples/c/bldrtn application_id
|
This script will do two things:
- Compile and link the UDF into a shared library.
- Copy the resulting shared library (named
application_id on UNIX systems and application_id.dll on Windows systems) to the sqllib/function directory
The Java function
The same functionality provided above with a C function can be implemented using the Java programming language as shown in Listing 4. The code for the function is equally short.
Listing 4. A Java function to retrieve a connection's application ID
import java.sql.*;
import COM.ibm.db2.app.*;
public class appl_id extends UDF
{
public void getApplicationId(String result) throws Exception
{
try {
// set the output parameter based on DBINFO
set(1, getDBapplid());
}
catch (Exception e) {
setSQLstate("38XXX");
if (e.getMessage().length() > 0) {
setSQLmessage("Exception '" + e.getMessage() +
"' encountered.");
}
else {
setSQLmessage("Exception '" + e.toString() +
"' encountered.");
}
}
}
}
|
Building the Java function
Compiling such a short Java function is easily done. You use the Java compiler javac to convert the source file to Java byte code in a class file and copy the resulting class file to the sqllib/function directory as illustrated in Listing 5. Please note that "cp" is usually the UNIX command to copy files, and you should use "copy" on Windows systems.
Listing 5. Compiling and installing the Java function
javac appl_id.java
cp appl_id.class INSTHOME/sqllib/function
|
Installing the binaries
To simplify this process even further, you can use the libraries prebuilt from the C code or class file generated from the Java code.
The libraries built from the C code are all named application_id (or application_id.dll on Windows systems). Depending on the platform of your database server, you should copy the library from the respective directory from the downloadable zip file to the sqllib/function directory of the DB2 instance. Please note that different binaries exist for 32-bit and 64-bit instances.
The class file constructed from the Java code is named appl_id.class and it can be found in the "Java" subdirectory of the zip file.
You can find the zip file with the prebuilt libraries here.
Registering the function
The final step is to issue the CREATE FUNCTION statement to register the external function in your database. You have to use different CREATE FUNCTION statements, depending on whether you used the C code or the Java code. Listing 6a shows the statements for the UDF implemented in C, and listing 6b for the Java function. Substitute your database name for
DBNAME
:
Listing 6a. Registering the C function
db2 -td$
CONNECT TO
DBNAME
$
CREATE FUNCTION application_id()
RETURNS VARCHAR(128)
SPECIFIC applId EXTERNAL NAME 'application_id!getApplicationId'
NOT FENCED LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC
NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO $
|
Listing 6b. Registering the Java function
db2 -td$
CONNECT TO
DBNAME
$
CREATE FUNCTION application_id()
RETURNS VARCHAR(128)
SPECIFIC applId EXTERNAL NAME 'appl_id.getApplicationId'
NOT FENCED LANGUAGE JAVA PARAMETER STYLE DB2GENERAL
DETERMINISTIC
NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO $
|
In the above examples, we've changed the default delimiter on the command line to $ (using db2 -td$) which lets us conveniently enter multi-line commands. To indicate the end of the command, enter $ and then press the Enter key. If you execute the CREATE FUNCTION statement from the DB2 Control Center, you can change the statement terminator in the Tools Settings menu.
And while you are connected, test your function:
SELECT application_id() AS appid FROM SYSIBM.SYSDUMMY1 $
APPID
----------------------------------
*LOCAL.DB2.00B045155621
1 record(s) selected.
|
If you see the same output as in Listing 1, you're done!
Part 2. Application ID usage scenarios
In Part 1, we showed you how to build a function to retrieve a connection's application ID. Now we describe how you can use application ID to solve real problems. We present two scenarios:
Determining if a connection exists
Starting with Version 8.1, DB2 Universal Database provides a set of table functions that let you access DB2 snapshot monitor data via SQL.
The function SNAPSHOT_APPL_INFO() returns information for all currently connected applications, including their application ID. Using that information, we can determine whether a given application ID belongs to an existing connection or not. The following SELECT statement uses the snapshot function to return the application IDs for all existing connections to the database. Note that the table function returns more information, but we don't discuss that in this article. Please refer to the documentation of the function in the SQL Reference for more details.
SELECT appl_id
FROM TABLE (SNAPSHOT_APPL_INFO(CURRENT SERVER, -2)) AS t
|
If two connections exist, the output might look like this:
APPL_ID
--------------------------------
*LOCAL.stolze.0AFDD4122938
*LOCAL.stolze.0AD7F4121810
2 record(s) selected.
|
Accessing all the application-specific information from the DB2 monitor imposes some restrictions. For security reasons, only users with the SYSADM, SYSMAINT, or SYSCTRL privileges are by default allowed to access the monitor. This is too restrictive for a general way to access application IDs, in which each user might need to verify if a certain connection exists. To allow the unrestricted access to the snapshot functions, the DB2_SNAPSHOT_NOAUTH registry variable can be used. Executing the following command causes DB2 to not verify the privileges of the current user when one of the snapshot functions is accessed:
db2set DB2_SNAPSHOT_NOAUTH=on |
Please be aware that any user can now use the SNAPSHOT_APPL_INFO (and related) functions and process its results.
Let us verify this feature in a practical example. Here is the case where DB2_SNAPSHOT_NOAUTH registry variable has not been set. USER3 is an unprivileged user.
$ db2set DB2_SNAPSHOT_NOAUTH
DBI1303W Variable not set.
$ db2 "connect to test user USER3 using somepwd"
$ db2 "SELECT appl_id FROM TABLE ( SNAPSHOT_APPL_INFO( CURRENT
SERVER, -2 ) ) AS t"
APPL_ID
--------------------------------
SQL0443N Routine "*PPL_INFO" (specific name "") has returned an
error
SQLSTATE with diagnostic text "SQL1092 Reason code or token: USER3
, ". SQLSTATE=3855
|
As can be derived from the error message SQL1092, USER3 does not have the necessary privileges to access the monitor functions and, thus, cannot retrieve the list of the current connections and their application IDs.
Now, the instance SYSADM user turns on the less restrictive authorization requirements for the snapshot functions and restarts DB2. After that, USER3 is able to use the function SNAPSHOT_APPL_INFO without any further restrictions.
A SYSADM user performs:
$ db2set DB2_SNAPSHOT_NOAUTH=on
$ db2stop force
SQL1064N DB2STOP processing was successful.
$ db2start
SQL1063N DB2START processing was successful.
|
USER3 executes the following after the DB2_SNAPSHOT_NOAUTH has been properly set:
$ db2 connect to test
Database Connection Information
Database server = DB2/6000 8.1.0
SQL authorization ID = USER3
Local database alias = TEST
$ db2 "SELECT appl_id FROM TABLE ( SNAPSHOT_APPL_INFO
(CURRENT SERVER, -2 ) ) AS t"
APPL_ID
--------------------------------
*LOCAL.stolze.095A34142226
1 record(s) selected. |
Now, any user can use regular SQL to work with the results from the functions SNAPSHOT_APPL_INFO and our user-defined function application_id() to validate if a connection still exists.
Consider a scenario where your application must maintain its own locks on objects, for whatever reason. Let's implement the following logic:
- Whenever a user connects to the database, that user acquires a shared lock on the current data. (This is an application-specific lock and not a DB2 lock.) The lock is held until the user disconnects from the database.
- A batch process executes routinely, but must first ensure that no other shared locks (besides its own) exist on the object to be processed.
The logic is very straightforward and does not impose any difficulties, except for one situation:
What happens if a user connected to the database, acquired a shared lock and then connection abended? Perhaps the network broke down, or maybe there was a power outage, or maybe the user simply killed the client application without properly shutting it down. In such a case we would still have the shared lock stored somewhere in the database, but it would not be valid any longer, leaving garbage data.
Using the application IDs here lets us easily implement a way to check whether a given shared lock is still valid. When a user requests a shared lock upon connect, we also retrieve the related application ID using our UDF application_id(). Later on when the batch process executes, we verify that all of the application IDs that are associated with the shared locks are still valid. If not, then the invalid lock can be safely ignored because the connection does not exist any more.
The following table illustrates a schema of a table that can be used to store the shared locks in such a way that it is accessible for all users.
CREATE TABLE shared_locks (
USER_NAME VARCHAR(18) NOT NULL,
APPL_ID VARCHAR(128) NOT NULL,
OBJECT INTEGER ) |
Each row in that table shows which user is currently accessing the data. When a new connection is opened, the first operation of the client application is the following SQL statement:
INSERT
INTO shared_locks(user_name, appl_id, object)
VALUES ( USER, application_id(), ... ) |
When object X is to be processed by the batch process, we first check if there are any locks on it, and then ensure that each application ID for the lock is verified. The following combined SQL statement returns any remaining valid locks on object X data. All invalid locks are ignored.
SELECT appl_id
FROM shared_locks AS l,
TABLE (SNAPSHOT_APPL_INFO(CURRENT SERVER, -2)) AS t
WHERE t.appl_id = l.appl_id AND
l.object = objectX; |
Alternatively, a DELETE statement can be used to delete all the locks that are assigned to connections that no longer exist.
DELETE
FROM shared_locks
WHERE appl_id NOT IN
( SELECT t.appl_id
FROM TABLE ( SNAPSHOT_APPL_INFO(
CURRENT SERVER, -2) ) AS t ) |
After this delete, you know that all locks in the shared_locks table are valid, until the next connection gets dropped unexpectedly.
Creating audit trails in connection pool architectures
An easy way to enforce an audit trail of changes to data is by defining INSERT, UPDATE, and DELETE triggers on a table that keeps sensitive information. The information you'd likely include in the trail is:
- Old values, if applicable
- New values, if applicable
- The user ID of the connection associated with the INSERT, UPDATE, or DELETE operation
In modern connection pool architectures, however, all applications access data using a common middle-tier user ID; the application (commonly) maintains its own information about application users in database tables. In other words, users "Sally" and "John" may be application user IDs, but behind the scenes, those users connect to the database using the user ID AppUser defined at middle tier. To clarify the discussion, let's define two terms, which are illustrated in Figure 1:
-
Application users. Users defined and maintained by the application.
-
Database users. Users defined at the application server for connecting to the database on behalf of application users.
Figure 1. Users in a 3-tier architecture
Because connections in a pool don't truly disconnect from the database, connections using a particular database user will likely be shared by many application users over time. Therefore, the application ID alone (or the value from the DB2 special register USER) is no longer sufficient to maintain a proper audit trail.
To solve this problem, we can make use of application_id() in conjunction with the application user ID. Let's define the following table, LOGIN, to temporarily associate a connection's application ID and application user ID.
CREATE TABLE LOGIN (
appl_id VARCHAR(128) not null,
user_id VARCHAR(128) not null,
PRIMARY KEY (appl_id, user_id))
|
The method works as follows:
- When the application user logs into the application, the application automatically INSERTs a row of data into the LOGIN table, which associates the application ID of the database connection (using the UDF application_id()) and the application user ID This pair of values now uniquely identifies the user connection until he/she logs out of the application. For example:
INSERT INTO LOGIN VALUE ('*LOCAL.DB2.00BE85034416', 'SALLY') |
or
INSERT INTO LOGIN VALUE (application_id(), 'JOHN') |
- If the user ever performs INSERT, UPDATE, or DELETE to sensitive data, a trigger on that table activates and logs in the audit trail. The audit trail becomes a two-step process:
- The trigger first calls the application_id() function to get the application ID of the connection performing the SQL operation.
- Then, the trigger performs a lookup in the LOGIN table to match the application ID with the actual application user and writes the required audit information.
- When the user logs out of the application, the associated entry in the LOGIN table is deleted by the application.
The following code illustrates this method. Note that only an INSERT trigger is illustrated, but UPDATE and DELETE triggers would be similar. For more information on triggers, see the article How to Temporarily Disable Triggers in DB2 Universal Database.
db2 -td$
CONNECT TO
DBNAME
$
CREATE TABLE LOGIN (
appl_id VARCHAR(128) not null,
user_id VARCHAR(128) not null,
PRIMARY KEY (appl_id, user_id)) $
CREATE TABLE t1 (c1 INT) $
CREATE TABLE audit (c1 varchar(100)) $
CREATE TRIGGER auditT1
AFTER INSERT ON t1
REFERENCING NEW AS newrow
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE v_user VARCHAR(128);
SET v_user = (
SELECT user_id FROM login L
WHERE L.appl_id=application_id());
-- note: v_user will be null if application ID not found.
INSERT INTO audit VALUES (
'The value: ' || char(newrow.c1) ||
' was inserted by ' || COALESCE(v_user, 'unknown!'));
END$
|
To test the trigger, you can issue:
-- case where insert performed as non-registered user
insert into t1 values (0) $
-- case where insert performed by application user
insert into login values (application_id(), 'John') $
insert into t1 values (1),(2),(3) $
select * from audit $
|
Result:
-----------------------------------------------
The value: 0 was inserted by unknown!
The value: 1 was inserted by John
The value: 2 was inserted by John
The value: 3 was inserted by John
|
Summary
In this article, we discussed how every connection to a database has a unique application ID. We then showed you how to create and test a function called application_id() to retrieve this information easily. We also presented two common scenarios where the application ID can be used to solve real database application problems. Please note that the function described here is available as a built-in function starting with DB2 V8.2 so that you don't have to build the function yourself anymore. Prior to that version, you can follow the steps explained here to achieve the same functionality.
Top of page
Disclaimer
This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
Downloads | Name | Size | Download method |
|---|
| application_id.zip | 94 KB | HTTP | | application_id.tgz | 89 KB | HTTP |
About the authors  | 
| |
Knut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab where he worked on the DB2 Image Extender. He moved on to the DB2 Spatial Extender Version 8 and was responsible for several enhancements to improve the usability, the performance, and the standard-conformance of the Extender for more than two years.
Currently, he works as a teaching assistant at the University of Jena, Germany, and continues his work for IBM in the area of federated databases. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or at stolze@de.ibm.com.
|
| | |
Paul Yip is a consultant from the IBM Toronto Lab's Partner Enablement Team. Much of his work revolves around helping business partners migrate from other relational database management systems (RDBMS) platforms to DB2 with a specialization in database application development. He has written many articles for DB2 Developer domain and recently co-authored the book, DB2 SQL Procedural Language for Linux, UNIX™, and Windows®
. Paul can be reached at ypaul@ca.ibm.com. |
Rate this page
| 
