DB2 graphic QMF Version 8

Description of generation options

Before you generate a program, you should be familiar with the generation options. These options allow you to control the characteristics of the program that will be generated. These options are set on the Generation Options panel and the pop-up options windows are accessed from there. These options can affect the speed and performance of the generated program.

Environment

You can generate programs for any of the batch, TSO, CICS, or stored procedure environments.

Batch programs

Batch report programs are straightforward. Data is read from an SQL cursor, formatted, and written to the REPORTnn DDs. Once all of the data is read, the report files are closed and the program ends. Data layer programs are always generated as if for the batch environment, but they can be called by programs running in the batch or TSO environments.

TSO programs

TSO report programs must be run under TSO and ISPF because they make calls to ISPF dialog manager services. You can generate two types of TSO programs: display programs and print programs.

CICS programs

CICS programs are pseudo-conversational. You can generate two types of CICS programs: display programs and print programs.

Stored procedure programs

Stored procedure programs run under the control of DB2 in response to a CALL statement executed by a client application. Stored procedure programs accept input parameter values, execute an SQL statement, and optionally return a result set (if the executed SQL statement was SELECT). Stored procedure programs do not format or return QMF reports.

Program Type

The setting of the Program Type option determines the basic function of the generated program.

Specifying PRINT will result in a program that formats a report for printed output. This type of report has regular page breaks, and includes a carriage control character as the first character of each line of the report. The QMF PRINT REPORT command generates this type of report.

Specifying DISPLAY will result in a program that formats a report and displays it online to the user. The QMF DISPLAY REPORT command generates this type of report.

Specifying NO REPORT results in a program that does not print or display a report, but it retains additional QMF processing, such as SAVE DATA processing. This type of program must be specified when generating a stored procedure program. For programs that are not stored this type of program can be used as a skeleton or model for other processing.

Specifying DATA LAYER will result in a program that executes an SQL statement, using input host variables values and returning output data (if any) via the LINKAGE SECTION. Data layer programs differ from the other program types because they cannot contain additional QMF processing.

Program Type for batch programs must be PRINT, NO REPORT, or DATA LAYER. For TSO and CICS programs it can be DISPLAY, PRINT, or REPORT. For stored procedure programs it must be NO REPORT.

Note:
TSO and CICS PRINT programs can be invoked only from a corresponding DISPLAY program, which is, a program for the same report, using the same host variables.

The above options apply to programs generated from SELECT queries. If the input query is not a SELECT statement, there is no concept of a report, so the program type must be NO REPORT or DATA LAYER.

Save Data

You can generate programs with source code to emulate the operation of the SAVE DATA command of QMF. The data fetched by the program will be saved to the specified DB2 table. If the table does not exist, it will be created. If the table exists, the generated program will check that the column layout of the query's SELECT clause matches the table. If it does not, program execution will terminate with an error. Otherwise, the existing data will optionally be deleted (depending on the ACTION parameter to the SAVE DATA command), and the new data will be inserted.

The options for the SAVE DATA processing include the table name in which to save the data, the table space in which to create the table, the comment to put on the table, the action to take on data in the table, and the mode in which to run the SQL for the SAVE DATA command (static or dynamic). These options are specified on the Data Processing Options pop-up window. If the input to the generation process was a procedure that contained a SAVE DATA command, then the options specified on the command will be displayed as the default in the pop-up window.

If the SAVE DATA table name is unqualified, then it will be qualified with the package or plan OWNER or QUALIFIER parameters specified at bind time (if using static SQL) or with the CURRENT SQLID of the user at runtime (if using dynamic SQL). It is possible to generate a program that uses static SQL for most of its processing, but to use dynamic SQL specifically for SAVE DATA processing. This allows the qualification of the SAVE DATA table name to be based on the runtime user, without forcing the rest of the program to use dynamic SQL.

Programs that perform the SAVE DATA operation incur additional overhead, because they contain DDL statements (to create the table) and DML statements (to delete existing data and insert new data). When binding the plan for such a program, VALIDATE(RUN) should be specified if the processing uses static SQL, rather than VALIDATE(BIND). In addition, the user running such a program must have permission to create, delete from, insert into, and comment on the target table.

TSO and CICS display programs that perform the SAVE DATA operation must read all of the data for the report from DB2 before displaying a report. This ensures that the data is saved before exiting the program. This also avoids DB2 holding locks while a user is browsing a report, potentially for a long time.

Note:
Stored procedure and data layer programs cannot include SAVE DATA functionality.

Erase Table(s)?

You can generate programs with source code to emulate the operation of the ERASE TABLE command of QMF.

At the end of a successful run of the program, the specified table will be dropped from DB2 by executing a DROP TABLE SQL statement.

Note:
This operation deletes data from the table and drops the table.

If the input to the generation process was a procedure that contained an ERASE TABLE command, then the table name specified will be displayed as the default on the Data Processing Options pop-up window. If the procedure contained more than one ERASE TABLE command, then the string *MULTIPLE will be displayed as the default on the window, and the field will be protected. The names of the tables being dropped cannot be changed.

If the input was not a procedure or if it was a procedure that did not contain ERASE TABLE commands, you can build a program that performs an ERASE TABLE by manually specifying a table name on the Data Processing Options pop-up window. Only one table can be specified.

Note:
Data layer programs cannot include ERASE DATA functionality.

Write Data To File

You can generate programs with source code to save data to a sequential file, instead of a DB2 table. This allows you to eliminate a significant amount of DB2 processing. All of the DDL and DML associated with saving data to DB2 tables will be omitted from the program, which will eliminate database locking contention. As with the SAVE DATA option, you can select this feature even if you are generating from a query or from a procedure with no SAVE DATA command.

Programs generated with this option write the fetched data to a DD named DATAOUT. Program Generator automatically calculates the minimum LRECL for this DD based on each selected column's length and indicator variable. If you want to specify a different LRECL, you can enter it in the LRECL field on the Data Processing Options pop-up window. If the LRECL field is left blank or contains an asterisk (*), the computed value will be used. You can determine the LRECL that is actually used by looking at the generated program.

You can SAVE DATA to a table and to a sequential file in the same program. These options are not mutually exclusive.

TSO display programs that have the print capability are required to enable this option. In this case, the data is written to the RAADATA DD. The print program invoked by the display program then reads from the RAADATA DD to format and print its report. This eliminates the need for the print program to re-read the data from DB2.

Note:
Stored procedure and data layer programs cannot include Write Data To File functionality.

Read Data From File

You can generate a program that reads data from a file instead of retrieving data from DB2. A query must be specified to generate the program. The query is used to determine the expected column layout of the input file. The program will not include SQL statements to retrieve data using the input query. Instead, it will read the input data from a DD named DATAIN. This option typically reads data from a sequential file that was written by another program generated with the Write Data To File option set to yes.

Predicates specified in the input query (in its WHERE clause) will not be used when reading the data from a sequential file. To obtain the same effect as the SQL statement, the predicates must be manually added to the generated program.

TSO print programs are required to enable this option. These programs read data from the RAADATA DD that was written by their corresponding display programs.

Note:
Stored procedure and data layer programs cannot include Read Data From File functionality.

Print Program Name

Online print programs provide print functionality from TSO and CICS display programs. While viewing a report online, you can request a printed version of the report by entering the PRINT command. The display program (if it has been so enabled) invokes the online print program to print the report to the RAAPRINT DD or a GDDM printer (for TSO programs) or to a CICS temporary storage or transient data queue (for CICS programs).

For TSO programs, you can specify a GDDM printer nickname on the PRINT command; otherwise, the report will be written to the RAAPRINT DD. For CICS programs, you can specify the name and type of the desired queue on the PRINT command; otherwise, the report will be written to a temporary storage queue named RAAPnnnn, where nnnn is the terminal ID.

For TSO programs, the syntax of the PRINT command is:

PRINT (PRINTER=nickname

where nickname is the name of a GDDM printer. The (PRINTER=nickname parameter is optional. If the nickname is omitted, the report will be written to the RAAPRINT DD (which must be allocated before issuing the PRINT command).

For CICS programs, the syntax of the PRINT command is:

PRINT queuename queuetype

where queuename is the name of the CICS data queue (up to eight characters) used to contain the printed report and queuetype is the type of that queue (TS or TD). If queuetype is omitted, it defaults to TS. If queuename is omitted, it defaults to RAAPnnnn.

Follow these steps to achieve print functionality:

  1. Generate an online print program (a program with TSO or CICS for environment and print for report presentation type) with a particular name, such as Q1PRINT.
  2. Generate a TSO or CICS display program, such as Q1DISP.
  3. Fill in the Print Program Name field on the Report Options pop-up window with Q1PRINT, specifying Q1PRINT as the print program.

For CICS programs, PPT entries for the display and print programs must be defined to CICS.

Attachment Facility

Programs that contain embedded SQL statements need to connect to DB2 before executing the statements.

CAF Module Usage

There are two types of call attachment options available:

DSNALI is a DB2-supplied module which must be link-edited with the generated program. Calls are made to it to establish a connection with DB2 and open the application plan.

AACAF (supplied as an alias of RAACAF in QMFHPO.SRAALOAD) is link-edited with the generated program. At runtime, AACAF dynamically loads the standard DB2 CAF module DSNALI. The primary advantage to using AACAF instead of using DSNALI is that, because DSNALI is dynamically loaded at runtime, you will not have to re-link the generated program if your DB2 version changes. If DSNALI is link-edited with the generated program, then you have to re-link the program when DSNALI changes.

SQL Type

You can generate programs that use static or dynamic SQL. Usually, static SQL is the preferred method because it uses less CPU time by the generated program. In some cases, you can use dynamic SQL. For example, programs that use dynamic SQL are able to work with Information Builders, Inc's. EDA/Dynamic Extender for DB21

. Dynamic SQL also allows more flexibility in the SQL that is run. For example, unqualified SQL object names will be qualified by the CURRENT SQLID at runtime rather than by the OWNER or QUALIFIER bind parameters at bind time.

You can generate a program that uses static SQL by default, but uses dynamic SQL for SAVE DATA processing. To do this, enter S for SQL Type on the DB2 Options pop-up window and enter D for SQL Type on the Data Processing Options pop-up window. See "Save Data" above.

In general, you should choose static SQL unless there is a specific need for the functionality of dynamic SQL.

HPO/Sort

If the HPO/Sort option is specified, Program Generator will automatically remove (by commenting out) any ORDER BY clause in the input query, and generate a program that invokes a COBOL internal sort. This feature can greatly reduce CPU time consumption, as an internal sort is typically more efficient than DB2 sorting. However, because DB2 can use indexes and clustering to effect a sort without actually sorting, you should use this option with caution. If you know that DB2 will invoke a sort to satisfy an ORDER BY clause, replacing it with an internal sort will usually reduce CPU usage.

This option causes the generated program to read and release to the sort program the entire set of qualifying rows. These rows are sorted and returned to the generated program, then the report processing continues. You should be familiar with the sort program (for example, DFSORT) that is enabled at your site. You may need to include additional file input to set in-core sort sizes and other sort parameters to make the program as efficient as possible.

For documentation purposes, the query embedded in the generated program will still contain the ORDER BY clause in the form of a comment.

Note:
Stored procedure and data layer programs cannot include HPO/Sort functionality.

HPO/Runtime

If the HPO/Runtime option is specified, Program Generator will generate a program that makes calls to HPO-supplied runtime routines in order to format the report. The program will contain fewer lines of code, as most of the data processing will be performed in the common routines. Because the program will have fewer lines of code, it will be easier to maintain.

If you do not specify this option, Program Generator will generate a program that does not rely on any support routines at runtime. In fact, this type of program does not have any special linkage requirements. You can generate a completely self-contained, standalone program. This type of program has more lines of code because all data processing is performed in the program.

Note:
Stored procedure and data layer programs cannot include HPO/Runtime functionality.

DSNTIAR Calls

If the DSNTIAR Calls option is set to YES, the generated program will include static calls to the standard DB2 DSNTIAR routine to retrieve formatted SQL error message text when SQL errors occur.

Note:
If portability to other platforms is important, then these calls can be omitted. In this case, only SQL error codes are returned when SQL errors occur.

RAACPY Calls

If the RAACPY Calls option is set to YES, the generated program will include static calls to the RAACPY routine.

Note:
The use of this option sacrifices portability in favor of improved performance. COBOL source code for the RAACPY routine is provided in QMFHPO.SRAASAMP.

Report Width, Report Length, DateTime, and PageNo

The following four options to the QMF PRINT REPORT command are used by the generated program to determine the appearance of the printed report: WIDTH, LENGTH, DATETIME, and PAGENO. These options are taken from any PRINT REPORT commands in an input procedure or (in the case of WIDTH and LENGTH) from the QMF profile. You can, however, override the values used for these options during the generation process.

These options are only relevant for a printed report.

Host Variable File LRECL

Batch programs that use host variables read values for those variables from the HOSTVARS DD. You can specify the LRECL of the HOSTVARS DD, or allow it to default to 80.

Host Variable Format and Delimiter

Host variable values in the HOSTVARS DD can be specified in columnar format, with one value per record, or in tabular format, with all the values on one record, separated by a delimiter character, which can be specified as part of the generation process.

More than one set of host variable values can be specified in the HOSTVARS DD. Batch programs that use host variables will continue reading the HOSTVARS DD as long as variable values are available, producing multiple versions of the report for each set of variable values. If the values are specified in columnar format, the report will be generated for each block of records in the HOSTVARS DD that form a complete host variable set. If the values are specified in tabular format, the report will be generated for each record in the HOSTVARS DD.

Programs that will be run from the End User Facility cannot use tabular host variables.

Note:
The Spill File and Buffering parameters affect the execution and performance of HPO/Runtime programs, but cannot be set from the Generation Options panel. They can only be changed by manually modifying the generated source code. They are not applicable to standalone programs.

Spill File

Most HPO/Runtime programs require a fixed amount of memory to run. However, HPO/Runtime programs generated from forms that contain certain QMF form usage codes require all of the data to be fetched before the report can be written. These programs will perform this function automatically, however, the data must be buffered as it is read. This buffering is done in memory by default, due to efficiency concerns. For very large tables, though, memory may be exhausted before all of the data is read. Because of this, a program can be directed to use of a spill file for buffering the data. This is done by setting the value of the USE-SPILL-FILE variable in the generated program. A value of 0 for this variable (the default) indicates that buffering should be done in memory. A value of 1 indicates that a spill file should be used.

When a program that uses a spill file runs, it uses a DD named RAASPILL for the spill file. This DD should be defined as a temporary file, for example:

//RAASPILL   DD    UNIT=SYSDA,SPACE=(CYL,(50,50))

Buffering

When an HPO/Runtime program runs, it repeatedly calls the HPO runtime modules to process the fetched data. On each call to the runtime modules, the program passes a buffer containing some number of rows of data, along with a count of how many rows are in the buffer. The number of rows in each buffer passed to the library has a direct impact on the performance of the program because each call to the library incurs an overhead cost. Thus, if the library is called every time a row is fetched, a substantial cost in overhead is incurred. If some number of rows are buffered together, the number of calls is reduced, and so is the overhead associated with the calls. As a result, a large buffer size should be used to improve performance.

Alternatively, the buffer size is limited by the amount of memory available to the executing program. (Additionally, some compilers put a restriction on the maximum size of objects defined in programs, which limits the size of the buffer.) The buffer size should be set as large as possible, within any such limits.

To change the number of rows per buffer in a OS/VS COBOL or VS COBOL II program, three items in the working storage section must be set to that number: the value of the variable FB-INDEX-MAX, and the OCCURS clause on the FETCH-BLOCK and INDICATORS-BLOCK variables. This specifies the number of rows per buffer, not the total buffer size (the total buffer size is the number of rows per buffer multiplied by the size of each row).


1.

Note:
Trademark of Information Builders, Inc.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004