Technical Blog Post
Abstract
How to enable REOPT or OPTPROFILE for a query shipped to a DB2 remote data source in federation.
Body
When federated query performance is slow due to the slow response time of query processing in remote data source side, you may want to tune the query processing in DB2 remote data source side.
Let's assume following situation.
- You have encountered a slow query in a SQL procedure in federation database.
- A part of the slow query shipped to remote DRDA data source by federation was actually slow.
Note, SQL compiler decides the query text to ship to remote data source. The query text being shipped can be found in RMTQTXT argument in SHIP operator in db2exfmt output. The remote data source processing the query can be found in shows SRCSEVER: (Source (ship from) server) argument in SHIP operator in db2exfmt output where SRCSEVER is a SERVER NAME specified in CREATE SERVER DDL.
Let me discuss how to enable REOPT option first.
When running complex queries or queries using range predicates with parameter markers, the typical performance tip is to enable REOPT option. There are number of ways to specify REOPT option depending on application programming interface listed in KC page.
Questions:
In this case, which package do you need to bind and on which database do you need to bind ? On which resource do you need to specify REOPT option ?
Answer. the steps required are:
- On remote database
db2 connect to <dbalias of remote data source>
cd $HOME/sqllib/bnd
db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA
- On federation instance
db2 update cli configuration for section <remote database> using REOPT 4
Note, 4 = SQL_REOPT_ALWAYS 3 = SQL_REOPT_ONCE
<remote database> is cataloged <database alias> specified in DBNAME option in the CREATE SERVER DDL.
The reason is that federation database acts as CLI client of the remote data source.
The bind should be done in remote data database while REOPT 3 or 4 should be specified in its client that is federation database.
You might come up with self questions when thinking about the above questions. Here's sample QAs to help understand the above answers.
Q. Does "sysproc.set_routine_opts('reopt always')" solve when creating/replacing the SQL procedure ?
A. No, reopt does not work in remote database with this way, but it does work on federation database which does not help in this case.
Q. Do we need to bind db2clipk.bnd with collection NULLIDRA option on federation database ?
A. No, this doesn't work in remote database. You need to bind it on remote database.
Q. Do we need to specify REOPT value in db2cli.ini on the client which invoke the SQL procedure ?
A. No, this doesn't work in remote database, it does only work on federation database which does not help in this case.
Similarly, if you want to specify an OPTPROFILE for queries shipped to a DRDA data source, you need to specify it in remote data source section of db2cli.ini in federation instance.
- On federation instance
db2 update cli configuration for section <remote database> using CURRENTOPTIMIZATIONPROFILE '<optprofile name>'
Conclusion.
DRDA wrapper for DB2 acts as CLI client of remote data source. You should focus on db2cli.ini in the federation instance.
UID
ibm11140478