DB2 data loading best practice
Best practice recommendation for DB2® Insight Pack data loading.
- DB2 configuration
- Is DB2 configured
to produce a single log file for a single server, rotating log files
for a single server, or multiple logs for multiple servers?
You can use the use the db2diag tool that is included with DB2 to merge and consolidate the log files. More information about the db2diag utility can be found in the DB2 documentation at:
For version 10.1:
For version 9.7:
- Data loading
- Determine if you want to use the IBM® Tivoli® Monitoring Log File Agent (LFA) installed on the remote DB2 server to push data or to use the LFA installed on the local IBM Operations Analytics - Log Analysis server to pull data. In some scenarios, you must use the Data Collector client as described in scenario 3.
- Logfile agent configuration
- Use the logfile agent configuration files to specify the log files
you want to monitor. The DB2InsightPack-lfadb2.conf and DB2InsightPack-lfadb2.fmt files
are located in the directory:
<HOME>/IBM-LFA-6.30/config/lo
The log file scenarios here describe the specific settings for these files.
Scenario 1 - Individual log file on one DB2 Server
For a single log file on one DB2 server follow these best practices.
- DB2 Configuration
- DB2 is configured for a single log file (non-rotating), db2diag.log on one server
- Data Loading Method
- The recommended method for loading data is to use the LFA installed on the remote DB2 server to push data or to use the LFA installed on the local IBM Operations Analytics server to pull data.
- Logfile Agent Configuration - DB2InsightPack-lfadb2.conf file
- In the DB2InsightPack-lfadb2.conf file, specify
the following parameters to monitor the log files.
LogSources=<db2 log directory to monitor>/db2diag.log #FileComparisonModeThe FileComparisonMode parameter should be commented out since it only applies when using wildcards in a LogSources parameter
- Logfile Agent Configuration - DB2InsightPack-lfadb2.fmt file
- Use the default DB2InsightPack-lfadb2.fmt file.
// Matches records for any Log file: // REGEX AllRecords (.*) hostname LABEL -file FILENAME RemoteHost DEFAULT logpath PRINTF("%s",file) text $1 END
Scenario 2 - log file rotation on one DB2 server
For rotated log files on a single DB2 server follow these best practices.
- DB2 configuration
- DB2 is configured for rotating log files using the DIAGSIZE configuration option. The db2diag.log files are named dynamically as db2diag.<n>.log.
- Data Loading Method
The recommended method for loading data is to use the IBM Tivoli Monitoring Log File Agent (LFA) installed on the remote DB2 server to push data or to use the LFA installed on the local IBM Operations Analytics - Log Analysis server to pull data.
- Logfile Agent Configuration - DB2InsightPack-lfadb2.conf file
- In the DB2InsightPack-lfadb2.conf file, specify
the following parameters to monitor the rotating log files:
LogSources=<db2 log directory to monitor>/db2diag.*.log FileComparisonMode=CompareByAllMatches - Logfile Agent Configuration - DB2InsightPack-lfadb2.fmt file
- Use the following DB2InsightPack-lfadb2.fmt file
to specify a fixed log file name. Otherwise you must define multiple
logsources in the IBM Operations Analytics - Log Analysis Administrative
Settings page because the rotating log file name changes. The fmt file
allows a fixed file name in the logpath.
// Matches records for any Log file: // REGEX AllRecords (.*) hostname LABEL -file db2diag.log RemoteHost DEFAULT logpath PRINTF("%s",file) text $1 END
Scenario 3 - Consolidating log files from multiple DB2 servers
If you consolidate log files from multiple DB2 servers follow these best practices.
- DB2 Configuration
- If the database is spread across multiple partitions and members,
then a db2diag.log file is created in multiple
directories according to the DIAGPATH value. It
can be difficult to interpret the DIAGPATH and db2nodes.cfg to
find all the log files for each member and host. The best practice
recommendation is to use the db2diag tool, which
will bring the information from all the members together to create
a consolidated db2diag.log. The db2diag utility
allows you to filter based on timestamp and this should be done to
include only new log entries in the consolidated logs. Information
on this filter can be found here:
For version 9.7:
For version 10.1
- Data Loading Method
- The recommended method for loading data is to use the Data Collector client. Remove the previous consolidated db2diag.log file before creating or copying a new version into the directory from which you load data.