Log stored procedures messages for DB2 on Linux or Unix

Document options Document options requiring JavaScript are not displayed Discuss Sample code Rate this page Help us improve this content

Level: Introductory

Vikram S. Khatri (vikram@zinox.com), Certified Consulting I/T Specialist, IBM　

19 Jan 2006
Updated 21 Aug 2007

This article presents a logging framework that you can use with IBM® DB2® for Linux, UNIX, and Windows stored procedures to log informational, error, and debug messages directly from the procedure itself. The logging framework methods use shared memory concepts to coordinate and change logging behavior at run time. Logging can be controlled globally for all procedures, or at the individual procedure level. This article has been updated for DB2 9.

Motivation

You might need to write informational, error, and debug messages to a log file for a number of reasons:

• To test, analyze and validate business logic.
• To track and review detailed error messages.
• To fix business logic bugs encountered in production environment by dynamically switching detailed logging or or off.
• To pinpoint performance problems in the stored procedures by examining execution times of the logging statements.

You can use this logging framework for stored procedures to fulfill the following requirements:

1. Dynamically turn on or off global or selective logging in the production environment.
2. Archive log files to maintain the current log file at a manageable size.
3. Identify logging statements for each stored procedure and each DB2 agent.
4. Split or combine log files for each DB2 agent.
5. Insure that logging itself is not resource intensive.

In addition to the framework offered in this article, you should investigate advanced methods for tracing and profiling SQL PL procedures, as outlined in articles listed in the Resources section below.

Back to top

Overall architecture and design

DB2 itself has its own logging methods to dump messages to the db2diag.log file. DB2 also provides facilities to capture and dump detailed traces, so that the DB2 developers can isolate and fix the reported issues. The logging framework presented in this article provides capabilities to capture the informational and debug messages to a file for analysis, so that the stored procedure developers can isolate and fix the problem.

Since DB2 stored procedures can be executed concurrently by different DB2 agents, it is necessary for each instance of the logging stored procedure to read and share all logging control information with other procedures. For this purpose, we use shared memory that is accessible to each instance of the logging procedure.

How to configure logging

To configure logging for a stored procedure, use the procedure DB2.UPDATE_SP_CONFIG. You can use this procedure to start, stop, and control logging, and to set parameters in shared memory so that all executing procedures can use the same logging control information.

In order to enable or disable logging for each stored procedure, or for all stored procedures simultaneously, save the information in a configuration file and load it in shared memory at the first invocation of the logging stored procedure. When modifications to the logging configuration parameters are made, the information is updated in shared memory and is also saved in the configuration file. (Note: This is very similar to the update of DB2 configuration parameters, where changes in some parameters take effect immediately, and some are deferred to the instance or database restart. In the case of this logging framework, any change in stored procedure logging parameters takes effect immediately).

One goal of the logging framework is to be conservative with system resources so that the logging procedure calls are not expensive, or do not incur much overhead. The framework is designed to handle logging by thousands of stored procedures without impacting performance.

For each logging call, we need to determine whether or not the message is to be sent to the log file. We look up a hash table with linked list based upon the hash key for the logging token name. For names sharing the same hash key, we maintain a linked-list at that same key-level to look up the configuration parameters for that specific logging token name. The use of the hash table with linked list provides the quickest lookup.

The log files themselves can grow very fast if you enable full logging on all the stored procedures. Archiving the log files allows you to move the log files to another location for analysis, storage, or deletion purposes.

How to initiate logging

To initiate logging from a DB2 stored procedure, first call DB2.OPEN_LOG with two parameters. The first parameter is a logging token name, and the second is a handle. To keep things simple, you can use the name of the stored procedure as the logging token name. You can also use a common logging token for a group of stored procedures for a particular business function. For example, if you have a nested procedure calling another six DB2 stored procedures to Get Best Rate Quote, you can use a token name GBQR for all seven DB2 stored procedures. The second parameter in DB2.OPEN_LOG is a handle of CHAR(160) bytes. This handle consists of two C structures. One of them contains shared memory location of the token and its logging actions, and the second structure contains the timestamp of when the DB2.OPEN_LOG was invoked.

How to log messages

The logging framework provides two stored procedures for logging messages: DB2.LOGINFO and DB2.LOGGER. Each stored procedure requires two parameters. The first is the logging handle obtained from DB2.OPEN_LOG, and the second is the message to be logged. DB2.LOGINFO logs an informational message if the logging token exists in the shared memory. DB2.LOGGER logs a debug message if the debug parameter is set to Y and a logging token exists in the shared memory.

Generally, you will use LOGINFO to log detailed error and other informational messages. You will use LOGGER to capture your debug messages to the log file. When you do not want to log debug messages to the log file, turn them off by setting the debug parameter to N for the logging token. To set this parameter, use the stored procedure DB2.UPDATE_SP_CONFIG, which is used for logging administration. If you want to turn off LOGINFO messages entirely, remove the logging token from shared memory by setting debug parameter to R to remove the token permanently.

How to close logging

At the end of your stored procedure, you can close logging by calling DB2.CLOSE_LOG. The CLOSE_LOG procedure requires the logging handle as the input parameter. The CLOSE_LOG writes the last message with the elapsed time from start of the logging to the end of the logging. The CLOSE_LOG call is useful to determine the elapsed execution time.

Back to top

Reference guide for the logging framework

An explanation of logging tokens

The first DB2.OPEN_LOG call builds a linked list hash table as shown in Figure 1 from the logging tokens kept in the shared memory. For each logging token read from the shared memory or configuration file on its first invocation, OPEN_LOG builds a hash key using the name of the token and the size of the hash table. For a hash table size of 10, get_quote, find_zip_code, verify_zip, and put_culls tokens share same hash key of 3. The linked list hash table contains information about the shared memory logging token pointers. This shared memory location is stored in the handle passed from the OPEN_LOG procedure to the subsequent calls to LOGGER and LOGINFO.

The logging methods can be used from your SQL-PL stored procedures (see example) or any other external stored procedures (COBOL, Java™, and so on). If you are using external stored procedures written in C, you can use either logging stored procedures, or the logging calls directly, bypassing stored procedure wrappers (see example).

The logging stored procedures framework consists of two parts. The first part is the logging administration to get and set configuration parameters. The second part is the logging stored procedures that are used from within the body of SQL-PL or any other external stored procedures.

Logging administration

There are three logging administration stored procedures. The DB2.UPDATE_SP_CONFIG stored procedure sets and stores the logging configuration parameters. The DB2.GET_SP_CONFIG stored procedure lists the logging parameters and their values. The third Stored Procedure DB2.GET_SP_LOG prints the contents of the log file.

Update stored procedure configuration

DB2.UPDATE_SP_CONFIG is the logging administration stored procedure to set the token name in the configuration file and in the shared memory. Generally, you will execute this stored procedure either from command line using CLP or DB2 command editor to update the logging parameters.

CALL DB2.UPDATE_SP_CONFIG(log_token_name, value);

log_token_name -- Name of the logging token. There are two types of logging token used by the framework: global, and private for each individual or group of stored procedures.

Global tokens -- The framework uses two global tokens.

1. GLOBAL_LOGGING -- The value is set to Y by default. When you set this parameter to N, all logging activity used by LOGINFO and LOGGER is stopped. Through this parameter, you can turn off the logging by all the stored procedure at run time.
2. LOG_SPLIT -- The value is set to N by default. When LOG_SPLIT is set to N, the logging framework logs all statements to the log file sp.log. When a stored procedure is executed by many different DB2 agents simultaneously, you will see all the log statements in a single log file, but you can still distinguish each log statement by looking at the unique application ID number for each DB2 agent. When all the statements are logged in a single file, you can filter using grep to see the statements logged by one particular DB2 agent. When this parameter is set to Y, the log files are split for each DB2 agent, and the name of each file begins with the application ID of the DB2 agent.

Private tokens -- An individual stored procedure or a group of stored procedures can have a token name that signals that output logging is required for this token.

Update logging tokens using the DB2.UPDATE_SP_CONFIG stored procedure:

• To turn off global logging:
  call DB2.UPDATE_SP_CONFIG('GLOBAL_LOGGING','N');
• To turn on global logging:
  call DB2.UPDATE_SP_CONFIG('GLOBAL_LOGGING','Y');

• To turn on log file splitting:
  call DB2.UPDATE_SP_CONFIG('LOG_SPLIT','Y');

  Note: When you turn on log file splitting, the log file name is the last part of the application ID that you get from the db2 list applications command. For example, a typical output of this command would look like this:

                
Auth Id  Application    Appl.      Application Id                 DB       # of
Name           Handle                                    Name    Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2INST1 db2jcc_applica 864        GA0A0A34.A905.051223231443     FALCON   1    
DB2INST1 db2jcc_applica 867        GA0A0A34.A904.051223231442     FALCON   1    
DB2INST1 db2jcc_applica 866        GA0A0A34.A906.051223231441     FALCON   1    
DB2INST1 db2jcc_applica 862        GA0A0A34.A907.051223231440     FALCON   1
      

If each of the above DB2 agents is executing same stored procedure, there will be four log files, with these names:
051223231443.log
051223231442.log
051223231441.log
051223231440.log

• To turn off log file splitting:
  call DB2.UPDATE_SP_CONFIG('LOG_SPLIT','N');
• To create a TESTSP logging token with Debug set to Y:
  call DB2.UPDATE_SP_CONFIG('TESTSP','Y');
• To update the TESTSP logging token with Debug set to N:
  call DB2.UPDATE_SP_CONFIG('TESTSP','N');
• To remove the TESTSP logging token from shared memory and from the configuration file:
  call DB2.UPDATE_SP_CONFIG('TESTSP','R');

The DB2.UPDATE_SP_CONFIG stored procedure updates the shared memory and writes all logging tokens to a configuration file. The log directory is named splog, and it needs to be created under the ~/sqllib directory. There are two ways in which this directory can be created:

• If the DB2 fenced user is different from the instance user:
  • Create the splog directory under sqllib, and change the file permissions to 777.
  • Under the splog directory, create a directory with same name as your database name.
  • Create an archive directory under the database name directory.
• If the DB2 fenced user is the same as the instance user:
  • The framework will create the necessary file and directories, as the logging framework will have the necessary permissions to create them.
  • If you want to keep the splog folder separate from the sqllib folder, you can create a separate mount point for splog and create a symbolic link ~/sqllib/splog pointing to that mount point.

The logging admin stored procedure DB2.UPDATE_SP_CONFIG will create or modify the .splogrc configuration file in the log directory. For example, if your database name is "sample" and the DB2 fenced user is separate than the instance user, you will create the sqllib/splog directory, then create the sqllib/splog/sample directory, and then create the sqllib/splog/sample/archive directory. If the fenced user is the same as the instance user, the framework will create these directories automatically for you.

                
db2inst1@p595 /home/db2inst1/sqllib/splog/sample=>ls -al
drwxrwsrwx   3 db2inst1 db2grp1         512 Dec 22 13:53 .
drwxrwsrwx   3 db2inst1 db2grp1         512 Dec 21 23:45 ..
-rw-r--r--   1 db2inst1 db2grp1        6811 Dec 22 15:46 .splogrc
-rw-r--r--   1 db2inst1 db2grp1        5192 Dec 22 13:50 051222034739.log
-rw-r--r--   1 db2inst1 db2grp1        3904 Dec 22 15:45 051222195007.log
drwx--S---   2 db2inst1 db2grp1         512 Feb 09 1971  archive
-rw-r--r--   1 db2inst1 db2grp1      768700 Dec 22 15:46 sp.log
      

The DB2.UPDATE_SP_CONFIG stored procedure will create or update the .splogrc file in the ~/sqllib/splog/YourDBName directory.

                
$ cat .splogrc
# *************************************************************************
# ** (C) COPYRIGHT International Business Machines Corp. 2000 - 2005
# ** All Rights Reserved.
# **
# ** Vikram Khatri vikram.khatri(at)us.ibm.com
# ** Use it at your own risk. No warranties implied and no support available
# *************************************************************************
global_logging=Y
log_split=N
testsp=No
      

There should not be any need to edit the configuration file directly. Even if you edit this file directly, you will not see the effects of the parameters immediately, as the stored procedure DB2.UPDATE_SP_CONFIG also updates the shared memory which is the main place for DB2.LOGINFO and DB2.LOGGER to pick up the logging parameters.

After you create a logging token (for example, TESTSP), you can use this token when calling DB2.OPEN_LOG to start the logging. The DB2.LOGINFO call after OPEN_LOG will only check if TESTSP token exists in shared memory. If it does exist, the LOGINFO messages will be loggged. The DB2.LOGGER, using the TESTSP token, will check the DEBUG parameter and if it is set to Y, the debug messages will be logged.

Get stored procedure configuration

Since it is not necessary to have access to the .splogrc configuration file, you can see the settings of the logging tokens with the help of DB2.GET_SP_CONFIG stored procedure.

                
db2inst1@p595 /home/db2inst1/sqllib/splog/sample=>db2 "call db2.get_sp_config()" 
Result set 1
--------------
        
SP_NAME              DEBUG_MODE
-------------------- ----------
TESTSP               No        

Get stored procedure log file

The stored procedure log files are stored in ~/sqllib/splog/YourDBName directory. If you have used LOG_SPLIT=Y, you will have individual log files for each DB2 agent. If you do not have login access to the DB2 server, you could fetch the contents of the log file through the use of DB2.GET_SP_LOG stored procedure. This procedure requires the name of the log file as an input parameter. If LOG_SPLIT is set to N, you will use sp.log as the name of the log file. Otherwise, you can find out the application ID using the db2 list applications command, and use the last part of the application ID as the log file name.

                
db2inst1@p595 /home/db2inst1/sqllib/splog/sample=>db2 "call db2.get_sp_log('sp.log')" 
Result set 1
--------------
12-25-2005 22:29:39.839:[testsp:051226021028] Begin Stored Procedure *** [681574408]
12-25-2005 22:29:39.839:[testsp:051226021028] The database name from dbInfo is SAMPLE
12-25-2005 22:29:39.839:[testsp:051226021028] The HOME variable is /home/db2inst1
12-25-2005 22:29:39.839:[testsp:051226021028] The Application ID is *LOCAL.db2inst1.051226021028
12-25-2005 22:29:39.839:[testsp:051226021028] End   Stored Procedure *** Elapsed 0.000
12-25-2005 22:36:04.048:[testsp:051226021028] Begin Stored Procedure *** [681574408]
12-25-2005 22:36:04.048:[testsp:051226021028] The database name from dbInfo is SAMPLE
12-25-2005 22:36:04.048:[testsp:051226021028] The HOME variable is /home/db2inst1
12-25-2005 22:36:04.048:[testsp:051226021028] The Application ID is *LOCAL.db2inst1.051226021028
12-25-2005 22:36:04.048:[testsp:051226021028] End   Stored Procedure *** Elapsed 0.000
      

Logging framework stored procedures

There are four logging stored procedures that you can use in your SQL-PL stored procedures to log messages. Table 1 shows the details.

Back to top

Getting everything set up

In order for you to set up the logging framework, you will need help from your database administrator to set up the stored procedure logging directory and a user temporary table space.

DBA activity

1. Create a splog directory under the ~/sqllib directory. Your DBA can create a symbolic link ~/sqllib/splog pointing to some other place where you have access to view the log files.

2. Set proper file permissions on ~/sqllib/splog so that the DB2 fenced user has full access.

3. Create a directory with the same name as the database under the ~/sqllib/splog directory.

4. Create an archive directory under the ~/sqllib/splog/YourDBName directory.

5. Create a user temporary table space for use by the global temporary table. The logging administration stored procedures DB2.GET_SP_CONFIG and DB2.GET_SP_LOG use a global temporary table to return the result set to the client. The script below shows an example, which you can modify to create a user temporary table space in your environment.

   
   
   Listing 6. Create user temporary table space
   

   CREATE USER TEMPORARY TABLESPACE dgtt IN DATABASE PARTITION GROUP PG0 PAGESIZE 4096 MANAGED BY SYSTEM USING ('/stagefs/dbins17a/dgtt/dgtttbsp.tablespace') ON DBPARTITIONNUMS (0) EXTENTSIZE 32 PREFETCHSIZE AUTOMATIC BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;

   
   

Developer activity

This logging framework has been tested on AIX® 5.3.1 64 bit and Linux™ 64-bit platforms using Suse 9 and RHEL 3. On your particular UNIX® 32/64 bit platform, please use the bldrtn and embprep programs from ~/sqllib/samples/c instead of the files provided with this framework. The platform-specific bldrtn and embprep programs will use the right compiler and link options.

Compile source

You need to compile the source to build the external library that will be used by the logging stored procedures. Please follow these steps to compile the source

1. Extract SQLPLLogging.zip to a directory.
2. Copy embprep and bldrtn from ~/sqllib/samples/c in the above directory.
3. Run make to build the shared library.

The makefile will use embprep and bldrtn to compile the source and it will copy the shared library to ~/sqllib/function directory. The makefile also catalogs the stored procedures in DB2.

Back to top

Using the logging framework through SQL-PL stored procedures

The following sample example of SQL-PL stored procedure TESTSP shows use of the logging stored procedures.

                
$ db2 CONNECT TO SAMPLE
$ db2 "call DB2.UPDATE_SP_CONFIG('TESTSP','Y')"
$ db2 CONNECT RESET
        
$ db2 -tf testsp.sql
        
File testsp.sql
-----------------		
--#SET TERMINATOR @
        
CONNECT TO SAMPLE
@
SET CURRENT SCHEMA = 'TEST'
@
DROP PROCEDURE TESTSP
@
CREATE PROCEDURE TESTSP()
LANGUAGE SQL
BEGIN
  DECLARE h  CHAR(160) FOR BIT DATA;
  DECLARE count INTEGER DEFAULT 0;
  CALL DB2.OPEN_LOG('TESTSP',h);
  SET count = count + 1;
  CALL DB2.LOGINFO(h,'this is a test loginfo '||CHAR(COALESCE(count,0))||'
         message');
  SET count = count + 1;
  CALL DB2.LOGGER(h,'this is a test logger '||CHAR(COALESCE(count,0))||'
          message');
                CALL DB2.CLOSE_LOG(h);
END
@
CONNECT RESET
@
        
Call TESTSP Stored Procedure
----------------------------
        
$ db2 "call test.testsp()"
      

Back to top

Using the logging framework through C stored procedures

The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include the splogger shared library in your project.

Include logger.h in your C stored procedure that will define the logging methods prototypes.

                
#define BYTEHANDLESIZE 160 		
void openLog(char *name, char *handle);
void closeLog(char *handle);
void setDBName(struct sqludf_dbinfo *dbInfo);
void logger(char *handle, char *fmt, ... );
void logginfo(char *handle, char *fmt, ... );
      

                
SQL_API_RC SQL_API_FN Snow
(
   char outMessage[100],
   sqlint16 *outMessageNullInd,
   char sqlstate[6],
   char qualName[28],
   char specName[19],
   char diagMsg[71],
   struct sqludf_dbinfo *dbInfo
)
{
   char byteHandle[BYTEHANDLESIZE];  
   char dbName[128] = "";
       
   setDBName(dbInfo);		  
   openLog("TESTSP", byteHandle);
   strncpy(outMessage, (char *)(dbInfo->dbname), dbInfo->dbnamelen);
   outMessage[dbInfo->dbnamelen] = '\0';
   logger(byteHandle, "The database name from dbInfo is %s", outMessage);
   strcat(outMessage, "::");  
   logger(byteHandle, "The HOME variable is %s", getenv("HOME"));
   strcat(outMessage, getenv("HOME"));
   strcat(outMessage, "::");
   strcat(outMessage, (char *)(dbInfo->appl_id));
   logger(byteHandle, "The Application ID is %s", (char *)(dbInfo->appl_id));
   *outMessageNullInd = 0;
   closeLog(byteHandle);
   return 0;  
}
      

Notice the use of setDBName in addition to the logging API calls. Your C program must call this method, because it obtains the database name from the dbInfo DB2 structure to set the log file name.

Use the following SQL to register the example stored procedure.

                
CREATE PROCEDURE DB.SNOW
(
   OUT   MSG       VARCHAR(100)
)
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE C
PARAMETER STYLE SQL
DBINFO
FENCED NOT THREADSAFE
NO SQL
PROGRAM TYPE SUB
EXTERNAL NAME 'splogger!Snow';
      

Back to top

Frequently asked questions

Question: When I try to compile using makefile, I get the error "bldrtn not found."

Answer: The file permissions on bldrtn might have changed, depending on how you copied the files to your system. Try changing the file permissions for bldrtn, embprep, and spcat using the chmod command. For example:
$ chmod +x embprep bldrtn spcat bld spalter

Question: When I build splogger, I get a "database connection does not exist" error during the execution of spcat. I do not see any connect statement in spcat. Where is it?

Answer: There is no connect statement in spcat, as it assumes the implicit connection to the database.

                
$ db2set DB2DBDFT=<your database name>
$ db2stop force
$ db2start						
$ db2 activate db <your database name>
      

Question: I do not have instance access to DB2. How do I compile and build the splogger?

Answer: Modify the makefile and specify values for ALIAS, UID, and PWD. Put a CONNECT statement in spcat and spalter.

Question: What is the purpose of the bld script?

Answer: The bld script is used to compile splogger as a stand-alone program instead of a shared library, for testing. It makes a splog binary file, which you can use to execute the main program in splogger.sqc.

Question: When I run make, I get the error "make: 1254-004 The error code from the last command is 4."

Answer: This is a normal result from spcat. It uses the DROP PROCEDURE command on the DB2 logging stored procedures, and if they do not yet exist in the database, you will see the error message above. The next time you run spcat, you will not get the error.

Question: How do I use this logger when I am also making modifications to the framework and using it from my SQL-PL stored procedures? We cannot set KEEPFENCED to NO due to performance issues, as we have other external C stored procedures running on our system.

Answer: Set the DB2 instance-level parameter KEEPFENCED=YES using the CLP command UPDATE DBM CFG USING KEEPFENCED YES. After restarting the instance, the external stored procedures library will be loaded in memory by DB2 on first invocation of the stored procedure. These shared libraries will remain in memory until you restart the instance. If you are making changes to this external library and copy them into the ~/sqllib/function directory, DB2 will not use it, as the library is already loaded in memory. This is not a DB2 limitation, but rather is the way operating systems are designed to work. (However, you can load and unload a shared library on a mainframe.)

Obviously, you want to set the KEEPFENCED parameter to YES on a production system, and if you have to make changes to your external stored procedures (including splogger), there is a workaround to force DB2 to load the new library. The spalter script uses this approach. It renames the shared library and alters the stored procedures using the new library name. DB2 will load the new library, but the old library will still remain in memory until you recycle the instance.

                
#! /bin/ksh
TOK=$(date +"%y%m%d%H%M%S")
SHLIBNAME=splogger$TOK
rm -f ~/sqllib/function/splogger*
cp -f splogger ~/sqllib/function/$SHLIBNAME
db2 -tv << !EOF
ALTER PROCEDURE DB2.UPDATE_SP_CONFIG EXTERNAL NAME '${SHLIBNAME}!UpdateSPConfig';
ALTER PROCEDURE DB2.GET_SP_CONFIG    EXTERNAL NAME '${SHLIBNAME}!GetSPConfig';
ALTER PROCEDURE DB2.GET_SP_LOG       EXTERNAL NAME '${SHLIBNAME}!GetSPLog';
ALTER PROCEDURE DB2.OPEN_LOG         EXTERNAL NAME '${SHLIBNAME}!OpenLog';
ALTER PROCEDURE DB2.CLOSE_LOG        EXTERNAL NAME '${SHLIBNAME}!CloseLog';
ALTER PROCEDURE DB2.LOGGER           EXTERNAL NAME '${SHLIBNAME}!Logger';
ALTER PROCEDURE DB2.LOGINFO          EXTERNAL NAME '${SHLIBNAME}!Loginfo';
!EOF
      

Question: In the log file, I see a number on the line "Begin Stored Procedure *** [681574408]". What is this number?

Answer: This is the shared memory location used by the splogger library. When you stop DB2, it will free up shared memory used by DB2. For the splogger shared library, there is no way to determine when to free up this shared memory. You can use this number to free up the shared memory used by this library. Use the command ipcrm -m 681574408 to free up the memory.

Question: I am using the framework but I do not see any log file. Where is it?

Answer: This is the most common issue. Look for your log file in the /tmp directory. This is related to the fenced user ID and file permissions on the ~/sqllib/splog directory. Ask your DBA to create a splog directory in ~/sqllib and grant full permissions on this splog directory to the DB2 fenced user. You can also create a splog directory in the fenced user's home directory, and create a soft link at ~/sqllib/splog pointing to this splog directory. The logging stored procedures are designed to run as a FENCED process, and they run under the DB2 fenced user ID.

Question: What is the hardw method in the framework, and what is its purpose?

Answer: The logging framework is designed to debug the stored procedures, and the hardw method is used to debug these logging routines.

Question: The logging to the file has stopped. I do not see any more messages showing up in the log file. What happened?

Answer: Verify that the file system containing the splog directory is not full.

Question: What other DB2 parameters do I need to adjust when using this framework?

Answer: None.

Question: The output from DB2.GET_SP_LOG is messed up. What is wrong?

Answer: The stored procedure DB2.GET_SP_LOG reads four thousand bytes from the log file at a time. Redirect the output to a file.

Question: I am trying to compile this framework on the Windows® platform and I am getting lot of compiler errors. What should I do?

Answer: This framework has been tested on AIX and Linux platforms only. I am in the process of converting this framework to Windows platform using C#.

Question: I am getting compiler errors when I try to compile the framework on the Solaris platform. What do I do?

Answer: I have not tested this framework on the Solaris platform. Please let me know the errors and I will try to fix them.

Question: I am getting compiler errors when I try to compile on the Linux platform. What should I do?

Answer: Copy the bldrtn and embprep files from ~/sqllib/samples/c to your directory and try again.

Question: Is there any plan to include this framework in DB2 product?

Answer: Not at this time.

Question: Can I modify this framework for use in my products?

Answer: Yes. This is an open source stored procedure logger. Please retain the IBM Copyright message in each source file.

Question: When I try to free the shared memory using the ipcrm command, I get an error stating that I am not allowed to remove the shared memory. How do I fix this?

Answer: The logger stored procedures run under the DB2 fenced user. Log in as the DB2 fenced user and try again to remove it. If the DB2 fenced user is nobody, ask your system administrator to free the shared memory using the ipcrm command.

Question: I have used the framework and our stored procedures are fully debugged now. I do not need any more logging calls. I want to even save even the few milliseconds it uses for performance reasons. What is the best way to deactivate logging?

Answer: You can always turn off global logging by setting global_logging to N. However, if you want to shave every millisecond from your stored procedure's execution time, comment out all CALL DB2. statements when you are sure that your SQL-PL or external stored procedures are ready for the prime time. I use a simple sed script to put the comments and use them later on if need be.

                
db2 "call db2.update_sp_config('global_logging','y')"

                
#!/bin/ksh
# Comment Logger Calls		
cat actsp.sql | sed 's/CALL DB2./--CALL DB2./' > sp.sql
db2 -td@ -f sp.sql 
       
#!/bin/ksh
# Uncomment Logger Calls		
cat sp.sql | sed 's/--CALL DB2./CALL DB2./' > actsp.sql
db2 -td@ -f actsp.sql
      

Question: I am interested in enhancing the functionality of this framework. How do I go about it?

Answer: This is an open source framework for DB2 stored procedure logging. We welcome users to contribute and provide feedback.

Back to top

Conclusion

The logging framework can be used for logging informational and debug messages to a log file, and performance measurement. You can use it on production systems to turn on or off stored procedure logging on the fly, and it will help you understand and manage your stored procedures.

Back to top

Acknowledgements

Special thanks to Burt VialPando and Nora Sokolof for their technical review of this article.

Back to top

Download

	Information about download methods

Resources

• Participate in the discussion forum.
  
  
• The article Tune SQL procedures (developerWorks, January 2005) offers tips and tricks for tuning SQL-PL using an open source SQL Profiling tool.
  
  
• The article SQL Procedure Language: Difference across platforms (developerWorks, December 2004) gives an overview of the differences between SQL-PL on LUW and mainframe.
  
  
• The article SQL procedure tracing (developerWorks, September 2004) describes an API for tracing SQL-PL and external procedures.
  
  
• The article Profiling SQL procedures (developerWorks, June 2004) describes a GUI-based Java tool to profile executing procedures and present collected data with each procedure’s source code.
  
  
• The article SQL procedures performance: Hints and tips (developerWorks, January 2003) gives hints and techniques for how to improve the performance of SQL-PL stored procedures.
  
  
• The book DB2 SQL Procedure Language for Linux, UNIX, and Windows is an excellent resource for DB2 SQL-PL language.

About the author

		Vikram Khatri works for IBM in Sales and Distribution as a part of DB2 Migration team and has 18 years of IT experience. Vikram enjoys DB2 database administration. Supporting DB2 technical sales requires him to work on migration projects as well as high performance benchmark tests.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top