Optimizing ODBC wrapper performance with the ODBC tuning utility (db2fedsvrcfg)

You can optimize the performance of the ODBC wrapper with the ODBC tuning utility, db2fedsvrcfg. This utility runs a set of predefined queries against the data source and tests the results for accuracy. The utility creates a set of ALTER SERVER statements that you can run against the server to set the server options for optimal performance.

About this task

The following items must be configured on your federated server:

Federation
The ODBC wrapper
The ODBC client software
Variables for the system environment. The ODBCINI and LIBPATH variables might be required.

For information about installation, configuration, and ODBC requirements, see the documentation that is provided with each of these products.

Procedure

If your ODBC server has not been defined, use the Db2® Command Line Processor to connect to the federated server and create an ODBC wrapper and server.
Optional: If the test tables already exist on your ODBC data source, connect to your ODBC data source and drop them.
Run the ODBC tuning utility from the Db2 command line with the options that you want to use. The utility might take some time to complete.
Verify that the utility ran successfully. If the utility ran successfully, you see the following message in the command window or in the output file that you specified:
```
ALTER SERVER "DS1" OPTIONS (ADD option1, 'value1') 
ALTER SERVER "DS1" OPTIONS (ADD option2, 'value2')
ALTER SERVER "DS1" OPTIONS (ADD option3, 'value3') 
.....    

The db2fedsvrcfg command completed successfully.
```
If the command ran unsuccessfully, you see an error message indicating the reason for the error. Correct the problem and run the command again.
You must create the test tables manually under the following circumstances:
- If you want to use table names that are different from the default
- If the data source that you are accessing through ODBC is read-only
- If the ODBC tuning utility is unable to create the test tables that are required by the utility
Connect to the federated server where the data source is defined.
Use the db2look utility to save your existing server settings before you run the file that is created by the ODBC tuning utility. See the documentation for the db2look utility for information about saving your existing server settings.
Optional: If your ODBC server is defined, you can connect to the federated server and drop the server options. The utility creates ALTER SERVER statements in the format described in step 4. If these server options have already been added, the ALTER SERVER statements will fail.
Use the following command to run the ALTER SERVER statements that were generated by the utility against the federated database. The ALTER SERVER statements that were created by the ODBC tuning utility are contained in the db2fedsvrcfg.sql file.
```
db2 -tvf db2fedsvrcfg.sql
```
Verify that the results of the ALTER SERVER statements. If any of the statements failed, you can modify the statements in the db2fedsvrcfg.sql file and run the statements again until they succeed.
After you have completed tuning the server with the ODBC tuning utility, set the PUSHDOWN server option to 'Y' to complete the optimization process.