Editing statement sets

Using the workbench, you can edit the statement sets that are in a pureQueryXML file. You can move statements to different statement sets, create new statement sets, delete statement sets, and rename statement sets.

A statement set within a pureQueryXML file is a set of statements that form the basis of one or four DB2® packages, depending on whether you want a package for one isolation level or one package for each of the four isolation levels.

The number of statement sets that are in a pureQueryXML file when you first edit that file depends on the value that you specify in Default.genProps for the sqlLimit option for the file before you open it. For example, suppose that you capture SQL statements into a pureQueryXML file named capture.pdqxml. Before opening it in the editor, you edit the Default.genProps file by adding this entry:

capture.pdqxml = -rootPkgName myPackage -sqlLimit 100

Suppose that you captured 300 SQL statements in capture.pdqxml. When you open the file for editing, you see that it is divided into three statement sets.

The value of the -sqlLimit option determines the maximum number of statements that a statement set can contain. (Although you set the value of the option to 100, the Configure utility kept together in myPackageB all the statements that refer to a particular cursor, causing the number of statements in that set to exceed 100.) The name of each set is the root package name with the next highest letter in the English alphabet appended to it, starting with A.

If the Configure utility is able to fit all captured SQL statements into a single statement set, it does not append a suffix to the name of that set.

Note: For DB2 databases, pureQuery Runtime captures special register values for each statement captured. When moving a SQL statement, ensure the statement set special register values match.
Moving SQL SELECT, UPDATE, INSERT, and DELETE statements from one statement set to another
If a pureQueryXML file contains more than one statement set, you can drag SQL statements from one set to another. You can move more than one statement at a time by holding down the CTRL key, clicking each statement, and then dragging them as a group to the target statement.

If you want to move a statement that belongs to a group of statements that use a named cursor, select and drag the statement to the target statement set. The editor moves the other statements in the group automatically.

If the number of statements in a set exceeds the value of -sqlLimit in the Default.genProps entry for the pureQueryXML file, when you save the pureQueryXML file either of two actions occur:

  • If the name of a statement set is blank, the editor takes the statements in unnamed statement sets and reconfigures them into new, named statement sets. The name of this new set is based on the current root package name for the pureQueryXML file. To the root package name, the Configure utility appends the next available letter from the English alphabet.
  • If the statement set has a name, the number of SQL statements in that set does not change. The value of the -sqlLimit option has no effect.
Note: You can also use the Configure utility options -groupSQLByStrings and -groupSQLBySpecialRegisters, to control how the editor configures SQL statements are into statement sets.
Tip: You cannot move DDL statements. However, if the file contains two or more statement sets that contain DDL statements and you want to consolidate DDL statements into a single statement set, remove the names of the statement sets that contain DDL statements. Then, save the file.
Creating statement sets
Right-click anywhere in the editor and select Create New Statement Set. The editor creates new statement set that has a blank name. You can move SQL statements from other statement sets into this new statement set.
There are three ways to name the new statement set:
  • To base the name of the statement set on the current root package name, save the file after moving one or more statements into it. The Configure utility gives the statement set a name that is based two factors:
    • The value of the -rootPkgName option in the corresponding Default.genProps file
    • The next available letter or letters in the English alphabet

    For example, if you create a set in a file that already has a set named myPackageA and another named myPackageB, the name of the new set is myPackageC. If the two sets that are already in the package are myPackageAA and myPackageAB, the name of the new package is myPackageAC.

  • To base the name of the statement set on a new root package name, follow these steps:
    1. Open the Default.genProps file for editing.
    2. Change the value of -rootPkgName for the pureQueryXML file.
    3. Save the Default.genProps file. When the editor asks you whether you want to build the project, click No.
    4. Save the pureQueryXML file.

    The Configure utility gives the statement a name that is derived from the new root package name. If you create more statement sets in the pureQueryXML file, their names are composed of the root package name and the next letter in the English alphabet, unless you change the root package name again or give them names that are not based on root package names.

  • To give the statement set a name that is not based on the root package name, follow these steps:
    1. Click in the node for the statement set and type the new name. The name must be unique within the file and must not contain characters that are invalid for the database that you are using. The name must also have space for the StaticBinder utility to append a single-digit number to it that indicates the isolation level of the package or packages that the utility creates from the statement set.
    2. Save the pureQueryXML file.
Deleting statement sets
You can delete any statement sets that you no longer need or that are empty.
  1. Right-click a package and select Delete Statement Set. The set of SQL statements is deleted from the pureQueryXML file.
  2. Save the pureQueryXML file.
None of the names of the other statement sets in the file change. For example, suppose a file contains the sets myPackageA through myPackageE. You delete myPackageC. The names myPackageD and myPackageE do not change to myPackageC and myPackageD.
Changing the names of statement sets
The name of a statement set is the base name of the DB2 packages that result from that set. For example, from a set that is named myPackageA, four DB2 packages can result: myPackageA1, myPackageA2, myPackageA3, and myPackageA4. Each number indicates the isolation level of the package.

You can change the names of statement sets in two ways.

Restriction: You cannot change the names of statement sets that contain only DDL statements.
  • If you want to base the new names on a new root package name, follow these steps:
    1. In the entry for the pureQueryXML file in the Default.genProps file, change the value of the -rootPkgName option to the root package name that you want the editor to base the new names on.
    2. Save the Default.genProps file. When the editor asks you whether you want to build your project, click No.
    3. In the pureQueryXML file, right-click each of the statement sets for which you want to change the name and select Clear Statement Set Name.
    4. Save the pureQueryXML file.

    If the number of SQL statements in all the unnamed statement sets exceeds the value of the -sqlLimit option in the entry for this file in the Default.genProps file, the Configure utility creates more than one statement set for them. For example, if there are 150 SQL statements in unnamed sets and sqlLimit is set to 125, the Configure utility creates two statement sets and names them, replacing the original unnamed statement sets.

  • If you want to type a new name for each of one or more statement sets, follow these steps:
    1. In the pureQueryXML file, right-click a statement set and select Clear Statement Set Name.
    2. Click in the node for the statement set and type the new name. The name must be unique within the file and must not contain characters that are invalid for the database that you are using. The name must also have space for the StaticBinder utility to append a single-digit number to it that indicates the isolation level of the package or packages that the utility creates from the statement set.
    3. Save the pureQueryXML file.

Feedback