DB2 REXX SQL for VM/ESA^(R): Installation and Reference

Appendix H. Support for CMS Work Units

The database manager and DB2 RXSQL will use CMS work units unless you specify WORKUNIT(NO) when you invoke the SQLINIT EXEC. CMS work unit support is available in VM/ESA.

CMS work unit support allows a virtual machine to have multiple independent paths into one or more DB2 Server for VM databases. You can have multiple active DB2 Server for VM logical units of work (LUW), each accessing the same or separate databases. The application can switch between work units while CMS, the DB2 Server for VM Resource Adapter, and DB2 RXSQL maintain all the necessary information.

For example, an application can copy data from one database to another without using a temporary file. Previously, you could not switch databases until your LUW ended. With CMS work unit support, you can copy data from one database to another using the following steps:

1. Establish a work unit, WU1.
2. Connect to database SQL1 and open a cursor on a SELECT statement.
3. Establish a work unit, WU2.
4. Connect to database SQL2 and open a cursor on an INSERT statement.
5. Make WU1 the current work unit.
6. Fetch into an array as many rows as feasible.
7. Make WU2 the current work unit.
8. Put all rows from array into SQL2.
9. Repeat steps 5-8 until all rows are read and put in the database.
10. Commit work on each database.

DB2 Server for VM will prevent an application from switching databases within the same LUW unless all work is committed or rolled back.

CMS work units also allow an EXEC to shield itself from other applications that may be accessing a database at the same time in the user's virtual machine.

For example, suppose a currently running EXEC with an active LUW in a database calls a second application EXEC that accesses the same or another database. In this situation, the called EXEC creates a new CMS work unit. Within the second CMS work unit, the called EXEC can access and modify the database, commit the changes, and return to the calling application without disturbing the LUW of the calling application. With two CMS work units, there are no DB2 RXSQL name or status conflicts between the applications.

To control the CMS work units, the application must use the VM/ESA CMS callable services library (CSL) interface for creating and managing work units. The REXX function, CSL, invokes the CMS callable services library. Figure 51 shows how to Get, Push, and Pop CMS work units in REXX.

Figure 51. Example Using the REXX Function CSL

/* Acquire a new work unit ID */ Call CSL('DMSGETWU retcode reason workunit') If retcode <> 0 then Say "Get WU rc="retcode "reason="reason /* Note: the work unit is not active yet */   ·   ·   · 'RXSQL ...' /* RXSQL creates environment for default work unit */   ·   ·   · /* Make work unit the active work unit */ Call CSL('DMSPUSWU retcode reason workunit') If retcode <> 0 then Say "Push WU rc="retcode "reason="reason   ·   ·   · 'RXSQL ...' /* RXSQL creates new environment for new work unit */   ·   ·   · Call CSL('DMSPOPWU retcode reason') If retcode <> 0 then Say "Pop WU rc="retcode "reason="reason   ·   ·   · 'RXSQL ...' /* RXSQL uses default work unit environment */   ·   ·   · Exit

DB2 RXSQL uses the DMS CSL routines to determine the current work unit identifier. If the DMSQWUID routine fails, DB2 RXSQL uses the DMSERP routine to determine the current work unit identifier. DB2 RXSQL determines which DB2 RXSQL control blocks are used to handle the request based on the current work unit identifier.

When DB2 RXSQL finds a new work unit identifier, it creates a complete set of DB2 RXSQL control blocks for that work unit. Work units do not have to share data. Even settings such as isolation level (SQLISL), date and time format settings (SQLDATE and SQLTIME), and tracing levels (TRACE) are not shared, and you must set them independently for each work unit.

When DB2 RXSQL detects end-of-command processing, it frees storage and removes all of the environments created by DB2 RXSQL.

Setting CMS Work Unit Support Off

If CMS work unit support is not required in your program, and you suspect it is causing your CPU time to be high, you may turn this feature off. CMS work unit support is turned off by specifying WORKUNIT(NO) when invoking the SQLINIT EXEC. If you specify this, both the database manager and DB2 RXSQL will not use CMS work unit support until you execute another SQLINIT with the WORKUNIT(YES) parameter.

The SQLINIT EXEC and DB2 RXSQL use the LASTING GLOBALV file to check whether CMS work unit support is on or off. If this file is corrupted, DB2 RXSQL will issue an error message indicating that the SQLINIT EXEC must be invoked again.

Switching Work Unit Support

Do not switch work unit support by using the SQLINIT EXEC from within your program. Unexpected results will occur if you do. The value for the WORKUNIT parameter is retrieved from the LASTING GLOBALV file when your program is started. It may not be changed until control is returned to CMS at the completion of your program.

Changing Database Parameters When Using CMS Work Units

Do not use the SQLINIT EXEC to change parameters like Date or Time formats after the first RXSQL or EXECSQL statement has executed. DB2 RXSQL retrieves these parameters from the LASTING GLOBALV file at the first invocation of RXSQL or EXECSQL when the application starts, or at first invocation of RXSQL or EXECSQL after a COMMIT RELEASE or ROLLBACK RELEASE request.

For predictable results with these parameters, use the DB2 RXSQL requests SQLDATE and SQLTIME after starting a new work unit before the first request to fetch or insert data. For a cursor operation, the parameters must be changed before opening the cursor.

For SQLDATE, SQLISL, and SQLTIME, the changed environment is applicable only within the CMS Work Unit in which these commands were issued.