This document is intended for application technologists working
for or with the Division of Information Technology at the University of
Wisconsin - Madison.
| DoIT Standards
and Guidelines for DB2 Applications Development |
|
||||||
This document is intended for application technologists working
for or with the Division of Information Technology at the University of
Wisconsin - Madison.
Section I.
Section II. DB2 OBJECTS
Section III. DB2 TOOLS
DB2 runs as a subsystem under MVS and is attached to TSO, IMS and CICS.
DoIT maintains two DB2 subsystems:
There are one or more databases for each Project Area on the test DB2 subsystem. Programmer/Analysts are granted Database Administrator (DBADM) authority over the Project databases at the discretion of the appropriate Project manager, giving them the ability to create and maintain DB2 objects within the Project databases.
Application programs are developed against data in the test subsystem. Corresponding objects exist in both test and production, therefore no source code changes are required when moving applications from test to production.
SQL is the language of DB2; the vehicle through which all work gets done. It consists of three distinct parts; Data Definition Language (DDL) used to manage DB2 objects, such as databases and tables, Data Manipulation Language (DML) used to retrieve, change, and delete data, and Data Control Language (DCL) used to administer DB2 security; that is, declare who has what privileges on various DB2 objects.
SQL has two characteristics that set it apart from traditional programming languages. First, it is based on the relational model, which means it operates on tables, not records. For example, a single SELECT statement can return from 0 to n rows, depending on the search criteria. Second, it is non-procedural, which means that the programmer specifies what data is desired, and not how to get it. SQL (and DB2) determines how a data request will be satisfied, and handles all the navigation through the physical data structure. This characteristic is of particular importance to Applications Development, as it provides true data independence in application programs, and significant reductions in program maintenance.
The SQL Reference Guide provides excellent detailed explanations of the keywords, usages, and syntax of SQL.
A DB2 database is a name associated with a collection of logically related DB2 objects. A database usually contains data related to a specific application system or functional area.
A database is created by choosing a database name (see Naming standards for databases) and forwarding a request to DoIT's Data Administrator. Most often the database name is determined and communicated during the dialog with the Data Administrator to develop a table. A database can only be created by a DB2 SYSADM.
The database name contains the code of the Project Area responsible for the tables stored within that database or the initials of the user.
If you want one database per Project Area use a two character database name (must be a valid DoIT Project Area code).
If you want more than one database per Project Area use a database name of four or more characters (must contain a valid DoIT Project Area as the first two characters).
For personal databases use a three character database name (must be
the initials of the user).
A DB2 tablespace is a VSAM dataset that contains the rows of one or more DB2 tables. Utilities, commands, etc. are run against tablespaces, not tables.
You explicitly direct DB2 to Create a Tablespace. You tell DB2 what name to use for the tablespace, and the primary and secondary quantities to use for allocation of disk space. A tablespace should always be created prior to the creation of the table(s) it will contain. Although it is not recommended, you can put more than one table in a tablespace.
Tablespaces can be created by anyone with DBADM authority in DSNT (all DoIT P/As, for their Project Area).
The easiest way of creating a tablespace is to use the interactive panels of GOOFY (see GOOFY in Section III). You can also create a tablespace by executing a CREATE TABLESPACE statement in SPUFI.
CREATE TABLESPACE GUIDE1
IN SAMDBASE
USING STOGROUP STG3380A **** This is an DoIT standard
PRIQTY 48 (48 kilobytes=12 4k pages=1 track)
SECQTY 48 (This is the minimum DASD allocation)
ERASE NO ** Strongly recommended
LOCKSIZE ANY ** Strongly recommended **
BUFFERPOOL BP0 **** This is an DoIT standard
CLOSE NO; ** Strongly recommended **
This sample creates a tablespace named GUIDE1 in a database named SAMDBASE.
Storage Group STG3380A and Bufferpool BP0 are DoIT standards for DSNT.
Erase NO, Locksize ANY, and Close NO are strongly recommended.
A tablespace will contain one and only one table. The tablespace name is crudely related to the name of the table it will contain.
A tablespace name must be six characters or less, generally formed by relentlessly squeezing the name of its contained table down to six characters or less.
The name must be unique from other tablespace names in the same database.
To request space for new DB2 tables in the production environment fill out the DB2 Table/Index/View Production Request form and send it to the Data Administrator, who will review the form and forward it to Technical Support for processing.
CREATE TABLE UW.GUIDE1_PERSON_ACCT
(PERSON ID CHAR(8) NOT NULL,
PERSON NAME CHAR(36) NOT NULL WITH DEFAULT,
ACCOUNT BALANCE DECIMAL(10,2) NOT NULL WITH DEFAULT)
IN TSDBASE.GDPSAC;
-- This statement creates a table named UW.GUIDE1_PERSON_ACCT in the tablespace named TSDBASE.GDPSAC. The table has three columns, Person ID, Person Name, and Account Balance. The data types CHAR, DECIMAL, and others, are explained in the DB2 SQL Reference manual under Data Types. The same manual also includes explanations of NULL and NOT NULL WITH DEFAULT.
Table names in DSNT are at the discretion of Area and Project Managers.
The owner of test tables that correspond to production tables must be "UW". The owner of tables that will not have corresponding tables in the production subsystem is the Logon ID of the Programmer/Analyst.
DB2 provides a LOAD utility that loads data from a sequential file to a DB2 table. To use this utility, you should first extract and format the source data using SAS, DABAL, or some other method, creating a sequential file. That file should then be used as input to the DB2 LOAD utility. The LOAD utility is described in detail in the DB2 Command and Utility Reference manual.
A view is a logical table that is derived from combining tables and/or other views into a single, logical table. A view can also be a subset of columns from a table or view. The data in a view is not physically stored. When a view is referenced, DB2 assembles the data from the underlying tables and views according to the definition. It is, therefore, essentially transparent to the user whether he is referencing a base table or logical view. You cannot INSERT rows into a view, nor DELETE rows from a view. UPDATE of views is strictly limited (generally, you cannot update a view if it is a result of a join). SELECT authority is granted for views exactly as though the view were a base table.
A view is created by executing a CREATE VIEW statement, which includes a SELECT statement that is the actual definition of the view. Before you execute the CREATE VIEW statement, you should first test the SELECT statement (using SPUFI, for example) to insure that it is syntactically and functionally correct. When you create a view, be sure to save the CREATE VIEW statement (in UDoIT.T.SPUFIN, for example) to use for having the view created in production.
You cannot, however, create a view using a qualifier other than your logon id (this means that you can't create views with "UW" as the qualifier). For application testing you should first create your views using your logon id as the qualifier. Then, when the application has been thoroughly tested and you are sure the view definition is correct, you should request the Senior Data Base Analyst to re-create the view with "UW" as the qualifier.
The syntax for the CREATE VIEW statement is explained in detail in the DB2 SQL Reference manual under CREATE VIEW.
The following statement creates a view from the QMF sample tables. The resulting view is a logical table of staff ID, staff name, and the name of the department in which the employee works.
CREATE VIEW UW.STAFF DEPTS
AS
SELECT S.ID,
S.NAME,
D.DEPTNAME
FROM Q.STAFF S,
Q.ORG D
WHERE S.DEPT = D.DEPTNUMB;
Note that the above statement is a relational join of two tables (Q.STAFF and Q.ORG), joining on department number.
Once the view has been created, it is accessed as though it were a table. For example, if you wanted to know in what department the employee with ID number 320 works, you could find out by querying;
SELECT DEPTNAME
FROM UW.STAFF DEPTS
WHERE ID = 320;
View names in DSNT are at the discretion of Area and Project Managers. The creator.viewname must be unique in the DSNT subsystem.
The view, when referenced, is always prefixed by the creator ID followed by a '.' (e.g. UW.). New views should first be created with the developer's logon ID as the viewname qualifier. Once the views have been tested, they will be re-created in DSNT and DSNP with "UW" as the viewname qualifier.
Columns are created at the time of table or view creation via the CREATE TABLE or CREATE VIEW statement, or they can be created (added to an existing table) via the ALTER TABLE statement in SPUFI, or by using the alter option on the GOOFY table panel (see GOOFY in Section 9). Note that columns can not be added to a view. To change a view, the view must be dropped and recreated.
The maximum number of columns allowed in a table or view is 300. The syntax of all the above statements is explained in detail in the DB2 SQL Reference manual under the appropriate listing.
New columns can be added interactively to an existing table by anyone with DBADM authority on the database in which the table resides, and are immediately available for processing.
Column names should reflect the data stored in the column and be as descriptive as possible (for example, GROSS VOUCHER AMOUNT is a more descriptive name than AMOUNT). A column name can be up to 18 characters long, including underscores.
Names for columns containing like data should be consistent across tables.
For example, a column containing Student ID Number in several tables should be named STUDENT ID (or whatever name you choose) in each table, not STUDENT ID in one table, STUDENT NUMBER in another table, etc. Consistent column names significantly improve program clarity and data element definitions. You'll probably need to give unique prefixes to columns with like names in your COBOL Include member (see DCLGEN in Section 9), but in the database itself, it's best to always use the same name for the same thing (i.e., Always STUDENT_ID, not XXX_STUDENT_ID one time and YYY_STUDENT_ID another time.)
Column names in test tables that correspond to tables in the production DB2 subsystem must be identical to the production column names.
Two columns are required in all tables that are updated by IMS/DC programs or DoIT written online TSO applications:
ROW ENTRY DTG CHAR(16)
The Date/Time of the online insert or update (including IMS BMP). Blank if the insert or update is done by the LOAD utility or by a batch program. In YYYYMMDDHHMMSSHH format, compatible with subroutine IA0012.
The TIMESTAMP datatype can also be used for this purpose.
ROW ENTRY LOGONID CHAR(8)
The User ID of the terminal operator making the insert or update. In IMS/DC applications, this is the USER keyword in SQL. In online TSO applications, this is the ZUSER ISPF variable. Blank if the insert or update is done by the LOAD utility or by a batch program.
The data types are explained in detail in the DB2 SQL Reference manual under the CREATE TABLE statement, under the specific data type name, and under SQL and DB2 Limits.
Use the data type that best suits the nature of the data in the column. For example, numeric data types should be used for columns that contain data that will have numeric operations performed upon it (like HOURLY RATE). Note that DECIMAL is the only numeric data type for which the COBOL translation is COMP-3; if you want to treat the data in a COBOL program in packed format (in a dump, for example), use DECIMAL.
Whether or not to allow nulls in a column is a decision that must be based upon the characteristics of the data itself. In rare circumstances an application may identify a requirement for the use of nulls in a column (equivalent to "missing values" in SAS), but generally speaking, the use of nulls should be avoided. If a value for a column is not always known, it is probably better to use NOT NULL WITH DEFAULT in the column definition. DB2 will then place a space or zero in the column (depending upon the column data type) when no other value is specified.
There are additional things to consider when deciding whether to use nulls:
Indexes in DB2 serve two purposes; they are used to enforce uniqueness of rows in a table, and to enhance the performance of database operations.
Once an index has been created, DB2 maintains the rows in the index, and DB2 decides when to use (and when not to use) the index. There is no way for a programmer to directly access a DB2 index.
Indexes can be created in any of three ways:
Before you create an index, always run TSO CLIST %TSDB2SIZ (option 'Z' of GOOFY) to get an estimation of the disk space required for the index.
CREATE UNIQUE INDEX UW.GDPRACX1 **** A DoIT standard Tablespace name + 'X1'
ON UW.GUIDE1_PERSON_ACCT
(PERSON ID ASC)
USING STOGROUP STG3380A **** This is an DoIT standard
PRIQTY 48 (48 kilobytes=12 4k pages=1 track)
SECQTY 48 (This is the minimum DASD allocation)
ERASE NO ** Strongly recommended **
BUFFERPOOL BP0 **** This is an DoIT standard
CLOSE NO; ** Strongly recommended **
Storage Group STG3380A and Bufferpool BP0 are DoIT standards for DSNT.
Erase NO, and Close NO are strongly recommended.
The CREATE INDEX statement is explained in detail in the DB2 SQL Reference manual under CREATE INDEX.
Indexes can be created or dropped interactively, even after a table has been created and loaded with data (although an index used to enforce uniqueness of rows in a table should never be dropped). Before any index is created, however, the index design should be reviewed by the Data Administrator.
Each DB2 table should have at least one index to enforce uniqueness of the rows. Other indexes (to enhance performance) can be created as required. Common uses of performance enhancement indexes are on columns frequently used in joins, or columns frequently used in SELECT statements.
In test DB2 (DSNT) an index can be created by anyone having INDEX authority on a table, or by anyone with DBADM authority on the database in which the table resides.
The owner of test indexes that correspond to production indexes must be 'UW'.
The index name itself is formed by borrowing the name of the tablespace and concatenating 'X1' for the first index (generally the table's unique or primary index), 'X2' for a second index, and so on.
The index name must be unique from other index names in the DSNT catalog.
The owner of test indexes that correspond to production indexes must be 'UW'.
Application Plans allocate DB2 resources and support database requests. They are required for each program that accesses DB2 data. They are the actual executable code that is merged with a load module to support the SQL embedded in the source code. A DB2 program will have exactly one Application Plan.
Application Plans result from a sequence of operations:
NOTE The above jobsteps are built dynamically by the clist %COMPILE as a result of the programmer-specified DB2 parameter values.
DB2 is accessed from the ISPF Primary Options panel in TSO by selectingthe "D" (DB2) option, which presents the DB2I (DB2 Interactive) panel.
On the DB2I panel you are able to specify a DB2 system name. There are two systems available: DSNT (test DB2, for applications development), and DSNP (production DB2, for production applications). All application development and testing must be done in DSNT.
The following is the DB2I Primary Menu panel. This panel is different than the DB2I panel described in the IBM DB2 manuals since it has been tailored for use at DoIT. Many of the options displayed here will be described in more detail in this Section of this Guide.
------------------------------------------------------------------------------
ADP's DB2I PRIMARY OPTION MENU
===>
DB2 SYSTEM NAME ===> DSNT (DSNT or DSNP)
Select one of the following DB2 functions and press ENTER.
1 SPUFI (Process SQL statements)
2 DCLGEN (Generate SQL and source language declarations)
3 COMPILE & LINK (Prepare a DB2 application program to run)
5 BIND/REBIND/FREE (BIND, REBIND, or FREE application plans)
7 DB2 COMMANDS (Issue DB2 commands)
A SWITCH AUTH ID (Switch to another DB2 Auth ID)
G GOOFY (Review, create, alter, drop, or secure DB2 objects)
P PROEDIT (Review, create, or edit DB2 tables)
Q QMF (Query Management Facility with ISPF/EDIT)
M QMF/MOVE (Move QMF Object to Production)
U QMF/UTILITIES (QMF Utilities Interactive Selection Panel)
X EXIT (Leave DB2I)
PRESS: END to exit HELP for more information
------------------------------------------------------------------------------
SPUFI is the acronym for SQL Processor Using File Input. It is a facility for executing SQL statements interactively (QMF and ProEdit are others). When using SPUFI the SQL statements are kept in PDS members, and the results of processing those statements are placed in a standard VSAM dataset. The ISPF facilities of Edit and Browse operate on the input and output datasets, and the panel flow between the ISPF facilites is automatic. Thus, SPUFI uses DB2 to operate on the data, and ISPF facilities to manage the input statements and the output results.
The SPUFI input dataset is a PDS named UDoIT.T.SPUFIN. The first three characters of a member name must be the Programmer/Analyst's logon ID.
Each DoIT Programmer/Analyst has a temporary 'iii.SPUFIOUT' dataset for SPUFI output (where 'iii' are the P/A's logon initials). The contents of this dataset will be replaced with each execution of SQL within SPUFI. 'iii.SPUFIOUT' is deleted at logoff.
How to use SPUFI is described in detail in the DB2 Application Programming Guide. Some of the example panels shown in the manuals may be different than those you see at DoIT (because the panels are tailored to our installation), but the functions provided are unchanged.
Information on how to use SPUFI is also available online from the SPUFI main panel by entering HELP on the command line or by pressing the F1 key.
SPUFI is accessed from option '1' on the DB2I panel.
Go to option 3.6 (Hardcopy Utility) of ISPF. Enter 'iii.SPUFIOUT' in the DATA SET NAME field (where iii = your logon ID). Make sure that SYSOUT CLASS = A, and that the job card is set up correctly.
DCLGEN stands for Declaration Generator. DCLGEN is an DoIT developed ISPF application that replaces the IBM provided function and which generates INCLUDE members for DB2 tables for use in COBOL and PL/I programs. These INCLUDE members contain SQL table declarations and working storage structures.
DCLGEN is accessed by selecting option 2 (DCLGEN) from the DB2I panel.
DCLGEN will return a Primary Options panel, from which the functions are available.
DCLGEN performs 3 functions:
(creates or replaces INCLUDE members)
(displays INCLUDE member name for a given table)
(deletes unwanted or obsolete INCLUDE members) The Member Generation function allows you to optionally enter a Prefix (from 1 to 4) characters that will be used as a prefix to the column names when generating the working storage structures. For example, if EMPLOYEE_ID_NUMBER is the name of a column in both the UW.BUDGET table and UW.PAYROLL table, you could use a prefix of BDGT when generating the member for the Budget table (resulting in a generated data name of BDGT-EMPLOYEE-ID-NUMBER).
The Member Generation function also allows you to optionally enter a Structure Name, which, if entered, will be used as the "01" level name. If the Structure Name option is not used, DCLGEN will use the DB2 table name. For example, if you entered a Structure Name of BUDGET-TABLE when generating the member for UW.BUDGET, you would get an "01" level data name of BUDGET-TABLE, otherwise, the "01" level name would be UW-BUDGET.
When you direct DCLGEN to generate an INCLUDE member, it constructs the INCLUDE member name in the format: aalnnnnn
where
aa is the Area Code responsible for the table (entered by the operator)
l is the language type, C for COBOL, P for PL/I. You can
generate either or both in one pass.
nnnnn is a sequence number. DCLGEN will use the next highest
sequence number within Area Code when you generate a member for a
new table. If you are replacing a member for an existing table will
reuse the existing number.
DCLGEN maintains a DB2 table called UW.TBL INCLUDES that cross
references the DB2 table names with the INCLUDE table names.
This table can be queried directly for reports.
The output from DCLGEN goes to UDoIT.T.DCLGEN. DCLGEN directly updates the library - no moves are required. UDoIT.T.DCLGEN is a PDS from which you can Print or Browse members via normal ISPF functions.
Put the following code in your program (in working storage):
INCLUDE member-name END-EXEC.
The DB2 Precompiler will comment out the above code, and place the specified member-name into your program.
Compile is a facility specifically tailored for the DoIT environment to allow you to easily specify the parameters needed to produce a job to Compile, Link and Bind an application program containing DB2 SQL code. This uses the same TSO Clist COMPILE that is available from any ISPF command line.
To use Compile, enter TSO COMPILE on any ISPF command line
GOOFY is a program developed by DoIT, designed to allow you to easily browse and manipulate DB2 object definitions and relationships.
Instructions on how to use GOOFY are available in online HELP panels, accessed by entering HELP on the command line or by pressing the F1 key from the GOOFY main panel.
To use GOOFY, select option "G" on the DB2I panel, and select the desired DB2 system (DSNT or DSNP). Once in GOOFY you may select any one of the various object displays by selecting the appropriate letter from the menu. Each of the displays was developed using a similar strategy.
Each display panel has a set of selection fields at the top of the screen. These are fields such as CREATOR, TABLE, DB, GRANTOR, etc. You may enter a value in one or more of these fields. The value may be fully specified (i.e., a full users ID), generically specified, (i.e., the first few characters of a table space name), unlimited match (i.e., entering an "*"), or substring matching using a "%" (i.e., entering "%APPT" to find all tables containing the string APPT). Any field left blank will not influence the selection. Set the selection criteria and hit the enter key.
Any DB2 system table rows which match your selection criteria will be displayed as rows on the bottom half of the screen. If more rows are found than will fit on the panel, you may scroll through them using the standard ISPF scrolling.
Many of the displays allow you to enter a command in the action column to the left of each row. The commands will vary depending upon the options available for the particular display. More than one row may be selected and modified at the same time and each row will be processed in turn. Depending upon the action, you may simply enter altered data immediately in fields on the selected row or you may be given another display panel which shows the requested information or allows additional data to be entered. If one of the selected rows has an error, you will be given an opportunity to correct the error or blank out the action before the following rows are processed. A verification screen is provided when rows are dropped to give you a "last chance" to change your mind.
You may enter row changes and selection changes or TSO commands all in one entry but the program handles them in a fixed order. Modified rows are processed first. Then any changes to the selection criteria. And finally any commands entered on the command line. Therefore, if you get an error on a row, you should correct it or blank out the action instead of entering END (PF3) since the END command will not be processed until all rows have been properly processed. An exception to this is the =x type of command which may take you out of GOOFY without any further processing.
GOOFY uses normal ISPF protocol for the Function keys. The following keys require some further explanation:
PF1 provides some help for each panel. If used after an error message appears, PF1 will provide a longer description of the error.
PF2 will allow split screen operation. Note that GOOFY is a DB2 application and DB2 limits you to only one DB2 application per TSO session so don't enter another DB2 application in the other screen.
While GOOFY makes viewing, creating, altering, or dropping objects easy, you still need to understand what the columns mean and what the various options for create or alter do. Please refer to the DB2 SQL Reference manual for this type of information. Every DB2 system table and the meaning of its columns is described in the manual. Every action performed by GOOFY which alters the DB2 system tables has a corresponding DB2 statement (GRANT, REVOKE, CREATE, ALTER, DROP) and is also fully described in the manual. Don't assume that the defaults used by GOOFY are appropriate for your particular case - understand what you are doing.
ProEdit is a software package from Computer Associates International. It is a tool that enables you to create DB2 tables and indexes, as well as maintain rows in a table, without knowing SQL. It is an excellent tool for creating and modifying test data.
ProEdit is accessed from option 'P' on the DB2I panel.
Watch out! ProEdit contains its own setting as to DSNT or DSNP. It's not necessarily the same as the setting on the main DB2I panel. If you discover you're not authorized to view or change data, maybe you've mistakenly left the ProdEdit setting at DSNP.
ProEdit has a number of features that are of value to DoIT Programmer/ Analysts. For example, it displays tables in either full-screen format or row format. Its commands and functions are very similar to those of ISPF, so the effort required to learn ProEdit is minimal. It can generate CREATE and SELECT SQL statements, and you can save the generated SQL statements in a PDS. Additional features are described in the manual, ProEdit User Guide.
It's generally recommended to keep your ProEdit EDIT profile settings at:
After reaching the ProEdit Edit menu, enter PROFILE on the command line to see your current settings.
If necessary then, issue the commands set autocommit OFF and set caps ON.
Query Management Facility (QMF) is an IBM query product that allows you to interactively access DB2 data and produce tailored reports.
QMF provides three query methods for accessing DB2. The first is SQL, in which the user constructs and executes standard SQL statements, the second is QBE (Query By Example), in which the user describes the desired results by using examples of the data, and the third is Prompted Query, in which the user constructs a query by making selections from a series of dialog panels.
The QMF General Information manual, available in the DoIT System Reference Library (SRL), provides a good overview of QMF and its capabilities for someone unfamiliar with QMF.
The best use of QMF is to construct database queries in which tailored output (printed or screen) is desired. The queries can be quickly constructed and tested, and then quite elegant forms can be tailored to present the data on a printed report or screen display. It is ideal for queries that do not require the complexity of a host programming language (e.g. COBOL).
QMF provides some nice tools for query management (i.e. Draw, Display, Run, etc). The QMF command language provides additional power not available in SPUFI. Reports can be dynamically tailored in QMF. The FORM facility is powerful and easy to use, and the dynamic nature enables reports to be designed quickly and easily modified if desired. QMF queries can include dynamic variables, in which QMF prompts the user for a value (e.g. Social Security Number), and then executes the query based upon the supplied value. QMF objects (queries, forms, etc.) can be saved and shared. This will allow a Programmer/Analyst to meet user reporting needs by developing QMF queries, procedures, forms, etc., moving them to production, and having them executed by I/O Control, much the same as batch reports are run today. QMF can be run in batch. The QMF command interface allows an application to issue QMF commands.
A QMF query can only contain one SQL statement. To process more than one SQL statement in a single execution, a QMF Procedure must be created that includes multiple queries.
QMF objects are the Query, Procedure, Form, and Data. Queries contain a single SQL statement. Procedures contain one or more QMF commands (i.e. RUN query, PRINT report). Forms describe the output format for the results of a query or procedure. Data saves the results of a query or procedure. These QMF objects can be saved in QMF datasets and retrieved when needed again.
In DSNT, QMF objects are either owned by the programmer/analyst who saves them or by the special AUTHID of "QMF". In DSNP, QMF objects which are generally shared are owned by AUTHID "QMF". In both DB2 systems, the first two characters of an object name must be the Area Code responsible for the object.
How to use QMF is described in detail in the QMF Learner's Guide and the QMF Reference.
QMF printed output is normally directed to the DoIT system printer when a PRINT command is executed, either directly or when using PF4 from a QMF panel. DoIT has written 2 CLISTs, TSQPRINT and TSQMFPRT, that will direct the printed output to a remote printer instead. The primary difference between the 2 CLISTs is that TSQPRINT provides more flexibility in entering parameters and produces header and trailer pages for the printed output.
TSQPRINT allocates the QMF print dataset, specifying the dataset attributes that determine the destination, class, laser printer form and number of copies, PRINTs the QMF object specified providing QMF with all the information needed for the print command and then reallocates the QMF print dataset sending the output to the appropriate destination.
TSQPRINT gets the information needed to allocate the dataset and print the QMF object through parameters which may be entered on the TSQPRINT panel or passed as CLIST parameters. Defaults are provided for most parameters.
From a QMF command line, QMF PROC or QMFBATCH job enter:
TSO TSQPRINT [optional parameters].
The CLIST parameters are the following: < TSO TSQPRINT OBJECT() DEST() FORM() CLASS() DATETIME() PAGENO() COPIES() PWID() PLEN() MBOX() NOSHOW
The parameters are optional, only code those needed.
The parameter value is entered between the parentheses.
Parameter names may be abbreviated to allow the command to fit on one line.
Abbreviations may be as short as one character for some parameters (i.e. O(REPORT) for object), but must be long enough to remove any ambiguity (i.e. at least 2 chars for CLASS and COPIES).
OBJECT: Object is the QMF object to be printed. It may be the word QUERY, PROC, PROFILE or REPORT to print the contents of a temporary storage area, or it may be the name of a QUERY, PROC, TABLE or VIEW in the database.
The default is REPORT.
DEST: Dest is the destination of the printed output. Options are:
LOCAL to print on the 9700s
RMT500 to print on the 2700 in the 2nd floor coffee room
RMT503 to print on the 2700 in the 3rd floor coffee room
TSUWTC to send the output to a Wang Office mailbox.
The default DESTination will be taken from your profile.
Itwill be the destination you set last.
FORM: This is the laser printer form. Forms are described in JXPFORM
on the SDSF Held Output Queue. TSQPRINT will automatically set
the appropriate page width and length for the following forms:
X003, X009, X030, X014, X100, X111, X101 and X104.
The default form is retrieved from your profile. It is the form you
used most recently.
CLASS: This is the SYSOUT CLASS of your print dataset. To send the output
to the SDSF Held Output Queue specify T. To send the output
directly to a printer use N for no holes, D for duplex with holes
or S for simplex with holes. CLASS is not used when sending output
to TSUWTC.
The default SYSOUT CLASS is retrieved from your profile. It is
the SYSOUT CLASS you used most recently.
DATETIME: DATETIME tells QMF whether or not to put the current date and
time on each page (YES or NO).
The default is retrieved from your profile. It is the value you
most recently set.
PAGENO: PAGENO tells QMF whether or not you want page numbers (YES or NO).
The default is retrieved from your profile. It is the value you
most recently set.
COPIES: COPIES specifies the number of copies to print. This should
usually be one. Multiple copies will appear as a single entry
on the SDSF Held Output Queue.
The default is one.
PAGE WIDTH and PAGE LENGTH: These two parameters allow you to
set the number of lines and columns QMF will put on each page.
Use these parameters when you are using a form that is not listed
above or when you want to override the values that TSQPRINT associates
with a form.
MAILBOX: Only used when TSUWTC is the destination. Mailbox is the
logonid of the person to whose Wang Office mailbox the output
will be sent.
The default is your own logonid.
NOSHOW: When the NOSHOW parameter is used the TSQPRINT panel will not
be displayed. This allows you to put calls to TSQPRINT in PROCs
without showing an extra panel. When NOSHOW is used all of the
information that is normally provided on the panel must be entered
as parameters or obtained from the defaults.
You want to see the defaults on the TSQPRINT panel and have the option of changing them before printing:
TSO TSQPRINT
You want to print the report and send it directly to the 2nd floor coffee room laser printer using form X100. You do not want to see the TSQPRINT panel:
TSO TSQPRINT DEST(RMT500) CLASS(D) FORM(X100) NOSHOW