Several times the tuning done using the FileNet Enterprise Manager (FEM) or IBM CM Administration Client like creating indexes for properties and attributes do not really improve the performance of a query.
In such a case the DB2 Advisor tool can really help and improve the query performance several times over.
I had faced issues before with IBM CM, where the eClient application was taking upto 30 seconds or more to open up a page for the Worklist and after tuning the indexes for the query the same page was loading under 2 second. Same applies with FileNet query related indexes.
Before we start using the DB2 Advisor tool we need to collect all the queries which are taking a lot of time for processing. The SQL query can be obtained for both FileNet and IBM CM by enabling trace level logging. The method for enabling trace level logging in both the products is as given below.
Login to FEM, right click on your Domain and select Properties
Click on the Trace Control tab, click on the Enable Trace Logging button
Check the Database check box as shown below
Login to IBM Content Manager Administration Client
Click Tools in the menu bar, select Log Configuration
Click on the Library Server button, select the Logging level as Trace (full)
NOTE: We do not need the Resource Manager trace logging for RMDB database queries as they do not really take up much processing time.
It is highly suggested that this trace level logging be enabled only during off business hour as this will affect the performance of the system and also would write a lot of log which can get overwhelming when trying to pin point the query that is creating the performance issue.
Reproduce the same condition for which you were seeing a slow query performance so that the SQL query gets logged into the trace log file.
Collect all the queries from the trace file which are taking a lot of time processing in a single plain text document with each line consisting of one query. Once you have the list, you can use the DB2 Advisor tool to get index recommendation.
Enable the DB2 Advisor if it is not already enabled by running the below command in db2cmd command prompt as shown using a DB2 administrator account.
db2 -tvf <db2-install-location>\MISC\EXPLAIN.DDL
To use the DB2 advisor, you need to be logged in as a DB2 administrator and issue the below command using the db2cmd command prompt.
db2advis -d <database-name> -i <plain-text-document-with-the-sql-query> -t 5 -a <schema-username> -x <schema-password>
Replace the <database-name> with your db name
<plain-text-document-with-the-sql-query> with your plain text file containing all the queries
<schema-username> with your schema username
<schema-password> with your schema password
This should throw up a big list of Recommended, Used Existing and Unused Existing Indexes. It is a good idea to redirect the output of the command to a text file so that you can view the entire list using the text editor as below.
Copy the recommended indexes without into a new plain text file and then implement these indexes by running the following command on db2cmd command prompt
db2 -tvf <file-name>
Alternatively if the recommended index list is small you could just copy each recommended indexes and run them individually on the db2cmd prompt, 'db2' needs to be prefixed for each of the command as shown below.
A database connection is required using a db2 admin account in either case to create the indexes.
You can repeat the process of getting the db2advis recommendation again after implementing the previous recommendation to further tune the queries. It is however not advised to iterate more than 3 times as this would create additional overhead for addition of record and also use additional disk space for maintaining the indexes.
Most importantly do not forget to disable the logging once the exercise is completed.
In such a case the DB2 Advisor tool can really help and improve the query performance several times over.
I had faced issues before with IBM CM, where the eClient application was taking upto 30 seconds or more to open up a page for the Worklist and after tuning the indexes for the query the same page was loading under 2 second. Same applies with FileNet query related indexes.
Before we start using the DB2 Advisor tool we need to collect all the queries which are taking a lot of time for processing. The SQL query can be obtained for both FileNet and IBM CM by enabling trace level logging. The method for enabling trace level logging in both the products is as given below.
FileNet DB Trace Level Logging
Click on the Trace Control tab, click on the Enable Trace Logging button
Check the Database check box as shown below
IBM Content Manager Travel Level Logging
Click Tools in the menu bar, select Log Configuration
Click on the Library Server button, select the Logging level as Trace (full)
NOTE: We do not need the Resource Manager trace logging for RMDB database queries as they do not really take up much processing time.
It is highly suggested that this trace level logging be enabled only during off business hour as this will affect the performance of the system and also would write a lot of log which can get overwhelming when trying to pin point the query that is creating the performance issue.
Reproduce the same condition for which you were seeing a slow query performance so that the SQL query gets logged into the trace log file.
Collect all the queries from the trace file which are taking a lot of time processing in a single plain text document with each line consisting of one query. Once you have the list, you can use the DB2 Advisor tool to get index recommendation.
Enable the DB2 Advisor if it is not already enabled by running the below command in db2cmd command prompt as shown using a DB2 administrator account.
db2 -tvf <db2-install-location>\MISC\EXPLAIN.DDL
To use the DB2 advisor, you need to be logged in as a DB2 administrator and issue the below command using the db2cmd command prompt.
db2advis -d <database-name> -i <plain-text-document-with-the-sql-query> -t 5 -a <schema-username> -x <schema-password>
Replace the <database-name> with your db name
<plain-text-document-with-the-sql-query> with your plain text file containing all the queries
<schema-username> with your schema username
<schema-password> with your schema password
This should throw up a big list of Recommended, Used Existing and Unused Existing Indexes. It is a good idea to redirect the output of the command to a text file so that you can view the entire list using the text editor as below.
Copy the recommended indexes without into a new plain text file and then implement these indexes by running the following command on db2cmd command prompt
db2 -tvf <file-name>
Alternatively if the recommended index list is small you could just copy each recommended indexes and run them individually on the db2cmd prompt, 'db2' needs to be prefixed for each of the command as shown below.
A database connection is required using a db2 admin account in either case to create the indexes.
You can repeat the process of getting the db2advis recommendation again after implementing the previous recommendation to further tune the queries. It is however not advised to iterate more than 3 times as this would create additional overhead for addition of record and also use additional disk space for maintaining the indexes.
Most importantly do not forget to disable the logging once the exercise is completed.
Thanks for this great post! - This provides good insight. You might also be interested to know more about generating more leads and getting the right intelligence to engage prospects.
ReplyDeleteTechno Data Group implements new lead gen ideas and strategies for generating more leads and targeting the right leads and accounts.
IBM ECM Users Email & Mailing List