Product: | DBConnectivity for IBM Db2 LUW Products |
Version: | All |
ID: | 1149 |
Summary: | Use Static SQL to store precompiled SQL statements on the IBM Db2 server |
To improve performance, you can set up your environment to store precompiled SQL statements on the host. This is useful when you have a statement or statements that you know will be executed many times. When configuring a data source, you can specify that you want to record the statement(s) in a package. The package is stored on the PC, then moved to the host when needed. You can create one package only, and it can hold up to 512 statements. The second and subsequent times a statement is executed, DB2 reuses the compiled statement from the package, saving the compile time and the time it takes to create the links between the tables. For example, an application executes the following statement:
UPDATE TABLE SET FIELD = ?
The driver can register the compiled statement in a package. After the package is copied to the DB2 host, and the statement is executed again, the host uses the compiled statement in the package. This feature is very useful with applications like MS Access where parameter queries are often used. The steps involved are:
- Create a data source in the Toolbox.
- In the data source configuration dialog, on the Static SQL tab, select the type of SQL statements you want to record in a package.
- Specify the location and name of the package file to store the SQL statements on your PC.
- Connect to the server using the data source and run your queries.
- When all the necessary statements have been executed, disconnect from the server. A package file containing the compiled SQL statements you recorded has been created on your PC.
- Copy the local package file to the host using the Toolbox Package Manager.
- Expand the tool tree on the left side of the window.
- Click the Static SQL file folder.
- In the Static SQL Package dialog, enter the following information:
Local Package - The package file name and path that you entered in the data source configuration.
Host Package - The name you want to call the package file on the DB2 host. The package is created in the package collection you specified in the Data tab when you created your data source. To create the package on the host, you need BINDADD permission for the package collection.
Login ID - Your login ID for the DB2 host.
Password - Your password for the DB2 host.
Datasource - The data source name for which you created the Static SQL package.
Click OK to connect to the DB2 server and copy your file. The file will be created on the server only if you have BINDADD permission for the package collection.
You can copy the package file to multiple database servers. However, you should be aware that once you have copied the file, any changes you make to the file on the PC (by recording additional statements, for example) will not be made in the version(s) of the file that you copied to the server(s). You need to copy the file to the server again if you want the contents of the local file and host file to remain identical.
- Edit your data source to switch off recording SQL statements.
- Edit your data source's Static SQL tab and check Use Existing Statements. This deactivates the recording and tells the driver to use the package you have recorded.