DB2 Sample Programs

The sample programs are DB2 application programs supplied by IBM with DB2. They are normal DB2 application programs that require precompilation, compilation, linking, and binding, as described in Chapter 13, "Program Preparation." These programs run using the TSO Terminal Monitor Program, IKJEFT01 , as described in Chapter 18, "The Doors to DB2." Therefore, you must provide a DB2 system name, a program name, a DB2 load library name, and a plan name for each sample program execution.

You must verify the load library and plan names associated with these programs at your site with your DBA or system administrator. The JCL examples in the following sections specify the default load library, and plan names are the same as the sample program names .

The Dynamic SQL Processor ( DSNTEP2 )

DSNTEP2 is a PL/I application program that can be used to issue DB2 dynamic SQL statements. The sample JCL in Listing 35.11 demonstrates the capability of this program to issue DCL, DDL, and DML dynamically.

Listing 35.11. DSNTEP2 JCL

 //DB2JOBU  JOB (UTILITY),'DB2 SAMPLE SQL',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 SAMPLE SQL PROGRAM //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //       DD DSN=DSN810.DSNLOAD,DISP=SHR //BATCHSQL EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD  SYSOUT=* //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SYSTSIN  DD  * DSN SYSTEM(DSN) RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP81)  - LIB('DSN810.RUNLIB.LOAD') END /* //SYSIN   DD  * SELECT * FROM SYSIBM.SYSTABLES ; UPDATE DSN8810.DEPT SET DEPTNAME = 'CHANGED NAME' WHERE DEPTNO = 'D01' ; INSERT INTO DSN8810.ACT VALUES (129, 'XXXXXX', 'SAMPLE ACCT') ; DELETE FROM DSN8810.EMP WHERE SALARY < 1000 ; CREATE DATABASE TESTNAME BUFFERPOOL BP12 STOGROUP DSN8G810 ; GRANT DBADM ON DATABASE TESTNAME TO USERA ; /* // 

Because DSNTEP2 is an application program, it must be compiled, linked, and bound before it can be used. Additionally, because the source code is provided in PL/I, it can be modified easily by a knowledgeable PL/I programmer.

Prior to DB2 V6, you needed to have a PL/I compiler to use DSNTEP2 . However, as of V6 IBM now provides both the source code and an object code version of DSNTEP2 with DB2. So, you no longer need a PL/I compiler to use DSNTEP2 .

DSNTEP2 can process almost every SQL statement that can be executed dynamically. DSNTEP2 accepts

• The GRANT and REVOKE DCL statements

• The ALTER , COMMENT ON , CREATE , and DROP DDL statements

• The DELETE , INSERT , SELECT , and UPDATE DML statements

• The COMMIT , ROLLBACK , EXEC SQL , EXPLAIN , and LOCK statements

The only important statement that DSNTEP2 does not support is the LABEL ON DDL statement. DSNTEP2 can be modified easily to support this statement (if you have a PL/I compiler).

DSNTEP2 Guidelines

The following tips and techniques should be utilized when executing SQL statements using DSNTEP2 .

Code DSNTEP2 Input in the First 72 Bytes of the Input Data Set

DSNTEP2 reads SQL statements from an input data set with 80-byte records. The SQL statements must be coded in the first 72 bytes of each input record. SQL statements can span multiple input records and are terminated by a semicolon ( ; ). Semicolons are not permitted in the text of the SQL statement.

Be Aware of DSNTEP2 Error Handling

Each SQL statement is automatically committed by DSNTEP2 . When DSNTEP2 encounters an SQL error, it continues processing the next SQL statement in the input data set. When 10 SQL errors have been encountered , DSNTEP2 ends. If any SQL errors occurred during the execution of DSNTEP2 , a return code of 8 is received.

Do Not Rerun Committed Work

To rerun DSNTEP2 , remember that all SQL statements that completed with a 0 SQL code were committed. These statements should not be rerun. All SQL statements completed with a negative SQL code must be corrected and reprocessed.

Liberally Comment DSNTEP2 Input

Comments can be passed to DSNTEP2 in the SQL statements using two hyphens in columns 1 and 2 or a single asterisk in column 1.

Use DSNTEP2 to Batch Large Streams of SQL

Use DSNTEP2 to simulate SPUFI in a batch environment. This can be useful because it enables the execution of dynamic SQL statements from an input data set without monopolizing a TSO terminal as SPUFI does. This can have a significant effect when issuing multiple DDL statements to create DB2 objects.

Prepare DSNTEP2 For Use

The DSNTEP2 program must be prepared before it can be run to issue dynamic SQL. If you want to use the source code version of DSNTEP2 , you must precompile, compile, link and bind it. You need to bind the object code version of DSNTEP2 before you can use it.

These steps are usually performed by the systems programmer or DBA responsible for installing DB2. Be sure to use the correct plan for DSNTEP2 . Sometimes the installer will provide a new plan name for each new version of DB2 and a common technique is to append the version and release number to the plan name, for example DSNTEP81 for DB2 V8.

Consider Changing SYSPRINT Block Size

If you plan to execute many statements in a single DSNTEP2 step, consider changing the SYSPRINT BLKSIZE . As delivered by IBM, the block size of SYSPRINT is extremely small. Such a change should be made only by a DBA or system programmer because doing so requires changing the code of DSNTEP2 followed by re-installing the program.

The Dynamic SQL Update Program ( DSNTIAD )

DSNTIAD is an assembler application program that can issue the same DB2 dynamic SQL statements as DSNTEP2 , with the exception of the SELECT statement. For this reason, it usually is preferable for applications programmers to use DSNTEP2 rather than DSNTIAD .

DSNTAID is written in Assembler language. Because DSNTIAD is a sample program, its source code can be modified to accept SELECT statements. This task is complex and should not be undertaken by a beginning programmer.

Additionally, DSNTIAD supports the LABEL ON statement, whereas DSNTEP2 does not. Also note that DSNTIAD can be a little more efficient than DSNTEP2 because it is written in Assembler. Sample DSNTIAD JCL is provided in Listing 35.12.

Listing 35.12. DSNTIAD JCL

 //DB2JOBU  JOB (UTILITY),'DB2 SAMPLE UPD',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 SAMPLE SQL UPDATE PROGRAM //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //       DD DSN=DSN810.DSNLOAD,DISP=SHR //BATUPSQL EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD  SYSOUT=* //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SYSTSIN  DD  * DSN SYSTEM(DSN) RUN PROGRAM(DSNTIAD)  PLAN(DSNTIAD6)  - LIB('DSN810.RUNLIB.LOAD') END /* //SYSIN   DD  * UPDATE DSN8810.DEPT SET DEPTNAME = 'CHANGED NAME' WHERE DEPTNO = 'D01' ; INSERT INTO DSN8510.ACT VALUES (129, 'XXXXXX', 'SAMPLE ACCT') ; DELETE FROM DSN8510.EMP WHERE SALARY < 1000 ; CREATE DATABASE TESTNAME BUFFERPOOL BP12 STOGROUP DSN8G510 ; GRANT DBADM ON DATABASE TESTNAME TO USERA ; /* // 

DSNTIAD Guidelines

Use the following guidelines to ensure the effective execution of SQL using DSNTIAD .

Use DSNTIAD for DDL

Consider using DSNTIAD rather than DSNTEP2 to submit batch DDL. Doing so can be a little more efficient but you will not be able to combine SELECT statements in with your DDL.

Control DSNTIAD Execution Authority

Consider giving only DBAs and systems programmers the authority to execute DSNTIAD . Allow everyone to execute DSNTEP2 because it provides support for the SELECT statement.

Do Not Comment DSNTIAD Input

Unlike DSNTEP2 , DSNTIAD does not accept comments embedded in SQL statements.

Be Aware of DSNTIAD Error Handling

Each SQL statement is automatically committed by DSNTIAD . When an SQL error is encountered, DSNTIAD continues processing the next SQL statement in the input data set. When 10 SQL errors have been encountered, DSNTIAD ends. If any SQL errors occur during the execution of DSNTIAD , a return code of 8 is received.

Do Not Rerun Committed Work

When rerunning DSNTIAD , remember that all SQL statements that completed with a 0 SQL code were committed. All SQL statements that completed with a negative SQL code need to be corrected and reprocessed.

Prepare DSNTIAD for Use

The DSNTIAD program must be prepared before it can be executed. This requires a precompile, compile, link and bind. The systems programmer or DBA responsible for installing DB2 usually performs these steps. Be sure to use the correct plan for DSNTIAD . Sometimes the installer will provide a new plan name for each new version of DB2 and a common technique is to append the version and release number to the plan name, for example DSNTIAD7 for DB2 V7 and DSNTIAD8 for DB2 V8.

Consider Changing SYSPRINT Block Size

If you plan to execute many statements in a single DSNTIAD step, consider changing the SYSPRINT BLKSIZE . As delivered by IBM, the block size of SYSPRINT is extremely small. Such a change should be made only by a DBA or system programmer because doing so requires changing the code of DSNTIAD followed by re-installing the program.

The Sample Unload Program ( DSNTIAUL )

One option for creating a readable sequential unload data set for DB2 tables (without writing an application program) is the DSNTIAUL sample program. With DB2 V7, though, IBM now offers a true UNLOAD utility for unloading DB2 data to a sequential data set. Of course, you must first purchase the correct utility package from IBM before you will have the UNLOAD utility available. And, the REORG utility with the UNLOAD ONLY option also can be used to unload data from a DB2 table space. However, DSNTIAUL is still offered as a free-of-charge sample program for unloading data from DB2 tables.

DSNTIAUL is a DB2 application program written in assembler. It is provided free-of-charge with DB2 for z/OS. DSNTIAUL can be used to unload the data from one or more DB2 tables or views into a sequential data set. The LOAD utility then can use this data set. Additionally, DSNTIAUL can produce the requisite control cards for the LOAD utility to load the sequential data set back into the specific DB2 table. Consider the JCL provided in Listing 35.13.

Listing 35.13. DSNTIAUL JCL

 //DB2JOBU  JOB (UTILITY),'DB2 SAMPLE UNLD',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 SAMPLE UNLOAD PROGRAM //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //       DD DSN=DSN810.DSNLOAD,DISP=SHR //UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) //SYSTSPRT DD SYSOUT=* //SYSTSIN  DD * DSN SYSTEM(DSN) RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAU6) - LIB('DSN810.RUNLIB.LOAD') /* //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSREC00 DD DSN=DEPT.UNLOAD.DATASET,DISP=(,CATLG,DELETE), //         UNIT=SYSDA,SPACE=(CYL,(1,1)),DCB=BUFNO=20 //SYSPUNCH DD DSN=DEPT.RELOAD.UTILITY.INPUT,DISP=(,CATLG,DELETE), //         UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE) //SYSIN    DD * DSN8810.DEPT /* // 

After running the JCL in Listing 35.13, the DSN8810.DEPT table is unloaded into the data set specified for SYSREC00 . The SYSPUNCH data set contains the generated LOAD control cards for loading this data back into the DEPT table. The generated LOAD control cards look like the following:

 LOAD DATA INDDN SYSREC00 LOG NO INTO TABLE      DSN8810.DEPT   (    DEPTNO        POSITION(       1         )    CHAR(                 3) ,    DEPTNAME      POSITION(       4         )    VARCHAR                       ,    MGRNO         POSITION(      42         )    CHAR(                 6)               NULLIF(      48)='?',    ADMRDEPT      POSITION(      49         )    CHAR(                 3)   ) 

DSNTIAUL Guidelines

When unloading data from DB2 tables using DSNTIAUL , keep the following techniques in mind.

Use DSNTIAUL to Create Unloaded Flat Files

Use DSNTIAUL to produce sequential data sets containing DB2 data from one or more tables. Running DSNTIAUL is significantly easier than coding an application program to extract the desired data.

Use WHERE and ORDER BY with DSNTIAUL

DSNTIAUL can accept WHERE clauses and ORDER BY clauses to limit the data to be unloaded and sort the unloaded data, respectively. However, the combination of the table name and its associated WHERE and ORDER BY clauses cannot exceed 72 total characters .

Use DSNTIAUL to Unload from a View

DSNTIAUL can unload data from DB2 views. When data from multiple tables must be unloaded into a single data set, create a view that joins the two tables and use DSNTIAUL to unload the data from that view.

Use the 'SQL' Parameter

Complete SELECT statements can be specified in SYSIN . This is accomplished by specifying PARMS('SQL') in the SYSTSIN data set. When PARMS('SQL') is specified, the 72-byte restriction is lifted. The largest SQL statement that can be specified is 32,765 bytes.

Keep Your SYSREC Data Sets Synchronized

Unloaded data is placed into a data set associated with the SYSREC xx DD statement. When multiple tables will be unloaded to multiple data sets using DSNTIAUL , be careful when you specify the SYSREC xx data sets. SYSREC00 refers to the first unload utility card, SYSREC01 refers to the second, and so on. Because SYSREC00 is the first DD statement, the number associated with the SYSREC xx DD statement is 1 less than the corresponding input statement being processed .

Unload No More Than 100 Tables with a Single DSNTIAUL Execution

No more than 100 input control cards can be successfully processed by a single execution of the DSNTIAUL utility.

Consider Using LOCK TABLE with DSNTIAUL

The LOCK TABLE statement can be used with DSNTIAUL to create a consistent unload file. By issuing the LOCK TABLE statement, you ensure that no modifications are made to the table during the timeframe of the unload execution.

Consider Using DSNTIAUL for Data Movement and Storage

You can deploy the DSNTIAUL program for many useful purposes. Any activity that requires bulk movement of data from a DB2 table is ideal for DSNTIAUL . Consider the following uses:

• To migrate data from one DB2 subsystem to another

• To save data when the structure of a table must be changed by dropping and re-creating it

• To copy data before a table structure change is made (because old image copy data sets cannot be applied after a structure change)

• To create a comma-delimited file (other DBMSs can accept a delimited file as input to a load or restore process)

Prepare DSNTIAUL for Use

The DSNTIAUL program must be prepared before it can be executed. This requires a precompile, compile, link, and bind. These steps are usually performed by the systems programmer or DBA responsible for installing DB2. Be sure to use the correct plan for DSNTIAUL . Sometimes the installer will provide a new plan name for each new version of DB2 and a common technique is to append the version and release number to the plan name, for example DSNTIAU6 for DB2 V6.

Only Use DSNTIAUL As a Last Resort

DSNTIAUL should be used only as a last resort for unloading DB2 data. There are much better options available for DB2 unloading in terms of ease-of-use and speed. DSNTIAUL should be used only by those shops that cannot afford to purchase either the IBM utilities or a faster third party UNLOAD utility.

Interpreting DSNTIAUL , DSNTIAD , and DSNTEP2 Return Codes

There are four possible return codes that can be returned by DSNTIAUL , DSNTIAD , and DSNTEP2 . Be sure to examine the return codes shown in Table 35.1 and take appropriate action. If a non-zero return code is received by DSNTIAUL , you may need to re-run DSNTIAUL to unload the desired data.

Table 35.1. DSNTIAUL , DSNTIAD , and DSNTEP2 Return Codes

Return Code	Interpretation
	Successful completion.
4	A warning code was received by an SQL statement. If the statement was a SELECT , DB2 did not perform the unload.
8	An error code was received by an SQL statement. If the statement was a SELECT , DB2 did not perform the unload.
12	The program could not open a data set, an SQL statement returned a severe error (in the “800 or “900 range), or an error was encountered in the SQL message formatting routine.