An explanation of logging tokens
The first DB2.OPEN_LOG call builds a linked list hash table as shown below 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.
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.
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.
- 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.
- 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 by the stored procedure name but you will still get logging from multiple db2 agents in a single log file. However, you can still do a grep on this log file to separate the log coming from each db2 agent. This is possible since we also log name of the procedure and application ID of the DB2 agent. The application ID will allow you to do the grep to separate logs from one db2 agent to another. This is useful and important when you are trying to figure out performance issues of your stored procedure.
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 name of the SP that you used in your OPEN_LOG statement.