Building a Static SQL Package Using the CONNX CDD Import Utility
The following steps show how to use CONNX to build a static SQL package with one SELECT, INSERT, UPDATE, and DELETE statement against tables defined in the DB2 UDB sample database.
1. Click the Add button in the CONNX Data Dictionary Manager window.
The Enter the Name of the New Table or View dialog box appears.
2. Enter a 1- to 18-character package name in the SQL Object Name text box.
3. Select DB2 Package in the Object Type list box.
4. Select the target database name in the Database list box.
5. Click OK.
6. The Package Properties tab is selected.
7. Type the name of the package owner in the Package Owner text box, for example, QUSER, SYSIBM, or PUBLIC.
This is an optional field.
8. The Pkg Collection ID text box contains the 1- to 18-character collection, library, owner, or schema name in which the package is created.
The default is NULLID.
9. The Comment text box is an optional field.
10. Select an isolation level in the Isolation Level list box.
11. The Default Collection ID text box is an optional 1- to 18-character entry that resolves flat table names to two-part table names.
12. The Grant Execute To text box can contain a user or group ID.
The default is PUBLIC.
13. Under Cursor Behavior select Close on Commit to build cursors which close after a commit or select Preserve on Commit to build cursors with hold, which preserve their current row pointers across commits.
Note:
Cursor behavior Option Preserve on Commit is not implemented for DB2 for OS/390 and DB2/MVS.
14. Under Release Resources select After Commit or After Disconnect to define when resources are released.
This static SQL package bind time parameter instructs the target DB2 server to release execution resources and serialization or sharing locks at commit or at disconnect time. After Commit instructs the DB2 target system to release resources and locks after a successful commit or rollback, whereas After Disconnect instructs the DB2 system to hold the resources and locks until the connection is disconnected.
15. Select the Create Explainable Package check box to create an SQL package which can be explained by the DB2 Explain utility.
16. Under Parallel Processing, an optional field, select None, Automatic, or Custom to specify the degree of I/O parallelism.
This static SQL package bind time parameter instructs the target DB2 server to use n (1 <= n <= 32767) parallel subqueries for all SELECT statements bound into the package. The default setting (Automatic) represents the special degree of ANY, which instructs the target DB2 system to determine the number of parallel subqueries at run time. Specifying a custom value of between 3 and 32767 is meaningful only when the DB2 target database is running on a symmetric multi-processing (SMP) architecture, such as an OS/390 Parallel Sysplex, an RS/6000, or a Windows Cluster Server. Specifying a custom value of 2 can improve performance for I/O-bound queries, even on single processor machines.