DB2 Version 10.1 for Linux, UNIX, and Windows

Loading DB2 audit data into tables

After you have archived and extracted the audit log file into delimited files, and you have created the database tables to hold the audit data, you can load the audit data from the delimited files into the database tables for analysis.

About this task

You use the load utility to load the audit data into the tables. Issue a separate load command for each table. If you omitted one or more columns from the table definitions, you must modify the version of the LOAD command that you use to successfully load the data. Also, if you specified a delimiter character other than the default when you extracted the audit data, you must also modify the version of the LOAD command that you use.

Procedure

Issue the db2 command to open a DB2® command window.
To load the AUDIT table, issue the following command:
```
   LOAD FROM audit.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE
             INSERT INTO schema.AUDIT
```
Note: Specify the DELPRIORITYCHAR modifier to ensure proper parsing of binary data.

Note: Specify the LOBSINFILE option of the LOAD command (due to the restriction that any inline data for large objects must be limited to 32K). In some situations, you might also need to use the LOBS FROM option.

Note: When specifying the file name, use the fully qualified path name. For example, if you have the DB2 database system installed on the C: drive of a Windows operating system, you would specify C:\Program Files\IBM\SQLLIB\instance\security\audit.del as the fully qualified file name for the audit.del file.

To load the CHECKING table, issue the following command:

   LOAD FROM checking.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.CHECKING

To load the OBJMAINT table, issue the following command:

   LOAD FROM objmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.OBJMAINT

To load the SECMAINT table, issue the following command:

   LOAD FROM secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.SECMAINT

To load the SYSADMIN table, issue the following command:

   LOAD FROM sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.SYSADMIN

To load the VALIDATE table, issue the following command:

   LOAD FROM validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE
             INSERT INTO schema.VALIDATE

To load the CONTEXT table, issue the following command:

   LOAD FROM context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.CONTEXT

To load the EXECUTE table, issue the following command:

   LOAD FROM execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE 
             INSERT INTO schema.EXECUTE

After you finish loading the data into the tables, delete the .del files from the security/auditdata subdirectory of the sqllib directory.
When you have loaded the audit data into the tables, you are ready to select data from these tables for analysis.

What to do next

If you have already populated the tables a first time, and want to do so again, use the INSERT option to have the new table data added to the existing table data. If you want to have the records from the previous db2audit extract operation removed from the tables, load the tables again using the REPLACE option.