Before you can run the SQL in your .NET application statically, you must capture the SQL statements in the application.

Running your application with pureQuery enabled captures the following types of SQL statements in your application:

   SELECT
   UPDATE
   INSERT
   DELETE

The capture process captures SQL statements for each active data connection in the app.config file. A data connection is active when its connection string contains a captureMode keyword that is set to ON.

Each of the active data connections must be associated with one of the DB2^® databases that your application accesses. For Windows applications, the association is made automatically. For Web applications, you must make the association manually.

Note:  If you are not capturing the SQL statements for all of the data connections in the same pass, change the automatically generated connection strings. Changing the connection strings prevents the confusion of duplicate connection strings from multiple passes.

Prerequisites:

• The solution that contains the .NET application must be open in the Solution Explorer.
• You must select a data connection for each database for which you want to capture SQL statements.

To capture SQL statements in a .NET application:

1. In the Solution Explorer, open the app.config file for the project.
2. Optional: Change the automatically generated connection strings, and then save the app.config file. Change a given connection string both in the name attribute of a <connectionStrings> subelement and in the key attribute of an <appSettings> subelement.
3. If the .NET application that you are developing is a Web application, perform the following actions:
   1. In the Solution Explorer, open the web.config file for your Web application.
   2. For each active data connection in the app.config file, do the following steps:
      1. Determine the corresponding child <add> element of the <connectionStrings> element in the web.config file.
      2. Copy the following keywords in the value attribute of the <appSettings><add> element in the app.config file and paste them in the connectionString attribute of the corresponding <connectionStrings><add> element:
            captureMode
   pureQueryXML
   connection
   rootPkgName
   3. Save and close the web.config file.
4. Close the app.config file.
5. Run the application.

A captured SQL (.capt) file is created for each data connection whose Capture SQL value you set to On in the Set Up .NET Application to Optimize SQL Queries window. If you are recapturing SQL statements and are using the same name for a captured SQL file as in a previous capture, the file is overwritten. Each file contains all of the information about the captured SQL statements, including the cursor type, collection name, package name, and owner.

The captured SQL files are stored in the locations that you specified in the Set Up .NET Application to Optimize SQL Queries window. The captured SQL files are copied to the Visual Studio project that contains your .NET application. The files are located in the Captured SQL Files folder of the project. The Solution Explorer is refreshed to show the captured SQL files.

See Also

Improving Performance and Security in .NET Applications with Static SQL | Using Static SQL in .NET Applications | Reviewing Captured SQL Statements

.NET Development Forum   DB2 FAQs

© Copyright IBM Corporation 2002, 2019. All Rights Reserved.