In order for you to set up the logging framework, you might need help from your system and/or database administrator to set up the stored procedure logging directory. Before we delve into this, let us understand how external stored procedures (Our logging framework stored procedures are external procedures written in C) run in DB2.
The external procedures run into 2 modes in DB2 i.e. in FENCED mode or in TRUSTED mode. This is controlled when we register the external procedure by using FENCED or NOT FENCED keyword. It is a good idea to run C procedures in FENCED mode to protect DB2 from failures. The C programs are famous for NULL memory pointers and they are cause of segmentation fault and core dumps. If procedures are registered in TRUSTED mode (FENCED), they run inside db2agent process and such segmentation fault can bring db2 agent down and possibly may have a bad side effect on DB2. Running external SP in a fenced mode has a very small negligible overhead since it runs in a separate process using a fenced user id. The logging framework has code which will create a logging directory. If it runs in a FENCED mode, the logging directory will be created in the home directory of DB2 fenced user and in a TRUSTED mode this logging directory will be created in the home directory of DB2 instance.
For above, you do not need help from DBA or system administrator to create logging directories but you might want to create a separate mount point for your logging directory so that it does not fill up instance directory and thus making DB2 vulnerable.
A natural question comes – Should I run this logging framework as FENCED or TRUSTED? My answer at this time is that run this as FENCED and when code becomes fool proof, we can then move this to the TRUSTED mode. The mode can be changed easily just by changing the makefile in the following way.
KEEP=FENCED #KEEP=TRUSTED
System Administration activity – optional
If you want to create a separate mount point for splog, you may need to do the following steps otherwise it is not necessary.
- Create a separate mount point for splog directory and mount in the home directory of fenced or instance user. A symbolic link can also be created for ~/splog directory under the home of fenced or instance user.
- Create a directory with the same name as the database under the ~/splog directory. e.g. ~/splog/sample
- Create an archive directory under the ~/sqllib/splog/YourDBName directory. e.g. ~/splog/sample/archive
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 copy the bldrtn and bldapp programs from the ~/sqllib/samples/c directory. The platform-specific bldrtn and bldapp programs will use the right compiler and link options.
| Logging source File name | Purpose |
| splogger.c | Native routines in C, and stored procedure wrappers for logging methods. |
| splogger.exp | Name of the methods to be exported in the shared library. |
| spcat | DB2 catalog scripts to register external shared library routines as DB2 stored procedure in DB2 schema. |
| spalter | DB2 catalog scripts to ALTER stored procedures with a different shared library name, so that DB2 can load the new library into memory when DB2 KEEPFENCED is set to YES. |
| makefile | Make file to compile the source. |
| bldrtn | The script called by makefile to compile the C source. Please use the bldrtn file specific to your UNIX installation (from the ~/sqllib/samples/c directory), instead of the file provided. |
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
- Extract contents of SQLPLLogging.zip to a directory.
- Copy bldapp and bldrtn from ~/sqllib/samples/c in your article.
- Run make to build the shared library.
The makefile will use bldrtn and bldapp to compile the source and it will copy the shared library to ~/sqllib/function or ~/sqllib/function/unfenced directory depending upon NOT FENCED / TRUSTED mode. The makefile also catalogs the following 5 logging stored procedures in DB2.
- db2.update_sp_config
- db2.open_log
- db2.loginfo
- db2.logger
- db2.close_log