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.
- myPackageA, which contains 100 statements
- myPackageB, which contains 110 statements
- myPackageC, which contains 90 statements
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:
- Open the Default.genProps file for editing.
- Change the value of -rootPkgName for the pureQueryXML
file.
- Save the Default.genProps file. When the editor asks you whether
you want to build the project, click No.
- 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:
- 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.
- Save the pureQueryXML file.
- Deleting statement sets
- You can delete any statement sets that you no longer need or that
are empty.
- Right-click a package and select Delete Statement Set.
The set of SQL statements is deleted from the pureQueryXML file.
- 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:
- 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.
- Save the Default.genProps file. When the editor asks you whether
you want to build your project, click No.
- 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.
- 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:
- In the pureQueryXML file, right-click a statement set and select Clear
Statement Set Name.
- 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.
- Save the pureQueryXML file.