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.