In this article, we will be using the db2uext2.cdisk sample c program provided by DB2, which is located in your c directory. On Unix, the c directory is located at <instance home>/sqllib/samples. On Windows, this directory is located in Program Files/IBM/<instance name>/samples.

Setting up a user exit program on Windows

Modify and compile user exit program

1. Create a directory called C:\mylogs

2. Copy the C:\Program files\IBM\SQLLIB\samples\c\db2uext2.cdisk to a working directory.

3. For this example, the following part of the user exit program should be verified to reflect the path c:\\mylogs\\.

#define ARCHIVE_PATH "c:\\mylogs\\" 
#define RETRIEVE_PATH "c:\\mylogs\\" 
#define AUDIT_ACTIVE 1 /* enable audit trail logging */ 
#define ERROR_ACTIVE 1 /* enable error trail logging */ 
#define AUDIT_ERROR_PATH "c:\\mylogs\\" 
		/* path must end with a slash */ 
#define AUDIT_ERROR_ATTR "a" /* append to text file */ 
#define BUFFER_SIZE 32 
	/* # of 4K pages for output buffer */

4. Make sure a supported C compiler is installed on your system (i.e. Microsoft Visual Studio) and your environment has the compiler's path.

5. From the command line, rename the db2uext2.cdisk to db2uext2.c and build it:

cl db2uext2.c

Once the program is compiled, db2uext2.exe and db2uext2.obj files will be created.

6. Place the db2uext2.exe executable in the /SQLLIB/BIN directory so that the database manager can find and execute it to archive and retrieve logs.

Create and prepare the database for user exit

7. Create the SAMPLE database with the db2sampl command at the DB2 command window. This will let you use the sample tables for the examples below.

db2sampl

8. Update the database configuration file so that user exit is turned on for the database. Note that only one database can be assigned the user exit program since the bin directory is shared by all DB2 instances.

db2 "update db cfg for sample using userexit on" 
db2stop force db2start

Note that the logretain database configuration parameter does not have to be on to enable roll forward recovery of log files since the userexit parameter also does this.

9. For testing purposes, you can encourage log files to be archived by decreasing the logfilsiz database configuration parameter.

db2 "update db cfg for sample using logfilsiz 200"

The value 200 is the number of 4k pages for each log file. In our example, the size of each log file will be initialized to 800k (200 * 4k).

10. Since the userexit database configuration parameter is set to YES, a full database backup is required before a connection to the database can be made. Make a note of the timestamp produced from the backup command as this will be used during the database restore.

db2 "backup db sample to c:\backups"

At this point, your user exit program is enabled and ready to be used. The user exit program will be invoked every 5 minutes to check the active log directory for log files that need to be archived. Note that the larger the log file specified with the logfilsiz database configuration parameter, the longer it will take to fill a log file thereby making that log file more vulnerable to disk failure, corruption, etc. Depending on your transaction loads, you should only specify a log file size that will fill up in a reasonable period of time so that the user exit program can archive the log file off to a safe directory.

Under a more ideal scenario it is best to archive the log files to a different disk on a separate system. This reduces I/O on the main disk and adds a second level of protection if the disk or system crashes.

Setting up a user exit program on AIX

Modify and compile user exit program

1. Create the following directory structure:

/mylogs/SAMPLE/NODE0000

Then give recursive permissions to each directory in the structure:

chmod -R 777 /mylogs

2. Copy the db2uext2.cdisk to a working directory and give the file permissions:

cp /home/db2v8_32/sqllib/samples/c/db2uext2.cdisk 
/home/db2v8_32/db2uext2.c

chmod 777 /home/db2v8_32/db2uext2.c

3. For this example, the following part of the user exit program should be updated to reflect the path /mylogs/.

#define ARCHIVE_PATH "/mylogs/" 
	/* path must end with a slash */ 
#define RETRIEVE_PATH "/mylogs/" 
	/* path must end with a slash */ 
#define AUDIT_ACTIVE 1 
	/* enable audit trail logging */ 
#define ERROR_ACTIVE 1 
	/* enable error trail logging */ 
#define AUDIT_ERROR_PATH "/mylogs/" 
	/* path must end with a slash */ 
#define AUDIT_ERROR_ATTR "a" 
	/* append to text file */ 
#define BUFFER_SIZE 32 
	/* # of 4K pages for output buffer */

4. Make sure you have a C compiler installed on your system and your environment has the compiler's library and path in it.

5. From the command line, build the db2uext2.c program:

cc -o db2uext2 db2uext2.c

Once the program is compiled, a db2uext2 executable will be created.

6. Move the db2uext2 executable to the sqllib/adm directory.

mv db2uext2 /home/db2v8_32/sqllib/adm/

Create and prepare the database for user exit

7. Create the SAMPLE database with the db2sampl command. This will allow you use the sample tables for the examples below.

db2sampl

8. Update the database configuration file so that user exit is turned on for the database.

			
db2 "update 
			
db cfg for sample using userexit on" 
			
db2stop force db2start

Note that the logretain database configuration parameter does not have to be on to enable roll forward recovery of log files since the userexit parameter also does this.

9. For testing purposes, you can encourage log files to be archived by decreasing the logfilsiz database configuration parameter.

db2 "update db cfg for sample using logfilsiz 200"

The value 200 is the number of 4k pages for each log file specified by logprimary and logsecond parameter.

10. Since the userexit database configuration parameter is set to YES, a full database backup is required before a connection to the database can be made. Make a note of the timestamp produced from the backup command as this will be used during the database restore.

db2 "backup db sample to /home/db2v8_32/backups"

At this point, your user exit program is enabled and ready to be used. The user exit program will be invoked every 5 minutes to check the active log directory for log files that need to be archived. Note that the larger the log file specified with the logfilsiz database configuration parameter, the longer it will take to fill a log file thereby making that log file more vulnerable to disk failure, corruption, etc. Depending on your transaction loads, you should only specify a log file size that will fill up in a reasonable period of time so that the user exit program can archive the log file off to a safe directory.

Under a more ideal scenario it is best to archive the log files to a different disk on a separate system. This reduces I/O on the main disk and adds a second level of protection if the disk or system crashes.

Source: http://www.ibm.com/developerworks/db2/library/techarticle/0307kline/0307kline.html