Controlling initial activities during a session

This section explains program parameters that help you control initial QMF activities, such as:

DSQSUSER (Connecting to the database)

When a user starts QMF, DB2 uses an authorization ID to determine whether the user is authorized to connect to the database. DB2 uses this same ID to determine a user's authorization to access objects and perform database activities.

You can use the DSQSUSER parameter to provide DB2 with an authorization ID and password to use for the database connection. For example, the following command connects user JONES, who has a password of MYPW:

When you specify the DSQSUSER parameter, QMF issues a CONNECT command to connect to the database. Thus, the rules for this parameter are the same as for the CONNECT command.

If you do not supply an SQL authorization ID and password, DSQSUSER defaults to the three-character VSE operator ID and password defined in the DB2 system catalog. You can issue the following SQL statements from the SQL query panel at any time during the QMF session to determine the ID that DB2 is currently using for database authorization:

If you supply a user ID, but no password, QMF displays an error message. The password you supply does not have to be identical to the pasword associated with the VSE logon ID.

DSQSMODE (specifying an interactive or noninteractive QMF session)

Some query and report-writing tasks users need to perform might not require interaction with QMF. For example, a salesperson might use the same QMF procedure every few days to query a set of tables for account status. Although the data changes, the procedure and tasks required to access the data remain the same.

Using the QMF program parameter DSQSMODE, you can save resources and time by starting a noninteractive session to perform your QMF work. Your terminal is then free for you to do other work while the transaction is running.

Because a noninteractive session displays no QMF panels, use the DSQSRUN (I) parameter to run an initial procedure that does the required QMF work and exits the program.

Use the DSQUSER parameter to specify an ID and password for the database connection if you do not want to use the default VSE operator ID and password.

DSQSRUN (naming a procedure to run when QMF starts)

Use the DSQSRUN parameter to pass the name of a QMF procedure that runs as soon as QMF starts. In a noninteractive session, use this procedure to perform the QMF work you need to do, then exit the program.

Qualify the procedure name with the SQL authorization ID of its owner if other users are using it to start QMF. For example, if user JONES owns the STARTPROC procedure, enter:

When you pass the name of an initial procedure, QMF issues a RUN PROC command, which runs the procedure you name.

Note: QMF does not allow blanks in the user ID and procedure syntax. For example, QMF does not recognize:

To use a procedure name with an imbedded blank, you must enclose the name in quotes:

Use DSQSRUN to help you automate noninteractive QMF work and allow users to perform interactive QMF work within the confines of a predefined procedure.

Running an initial procedure noninteractively

To conserve resources, you can run a procedure noninteractively by using a value of B for the DSQSMODE parameter and naming a procedure using the DSQSRUN parameter. For example, suppose that every Monday morning you need to produce an inventory status report. Each Sunday night you need to run a query that retrieves data from the same columns of a table called INVENTORY. Your query might look like the following sample, INVENTORY__QUERY:

The procedure you use to run this query and print the status report might look like the following procedure, INVENTORY__PROC:

The procedure includes an EXIT command because, when QMF is running noninteractively, no user is present to end the QMF session. EXIT ends the QMF session and frees the resources being held by QMF. Always use an EXIT command in an initial procedure that runs noninteractively.

Because the tasks involved in creating the report do not change (only the data changes), you can use the DSQSRUN parameter to query the INVENTORY table Sunday night and print the report to the storage queue named Q1, so you can have it Monday morning:

Performing interactive QMF work with an initial procedure

You can use an initial procedure in an interactive QMF session to predefine data access tasks for end users, allowing them to access only the data they need. For example, suppose a QMF end user has the responsibility of producing an inventory status report every Monday morning. The user might know the value that indicates low stock, but may not know exactly how to produce the status report. You could insert a variable in the query so that the user would only need to enter the value that indicates low stock. We could call this query INVENTORY__QUERY:

Because the user might want to view the data before printing it, the INVENTORY__PROC procedure might not include the EXIT command:

You can then use the DSQSRUN parameter without specifying the DSQSMODE parameter, so that you start an interactive session for the user:

The INVENTORY__PROC procedure prompts the user for the &LOWSTOCK variable value. The QMF Reference manual explains variables in more detail.

As soon as the user provides the value, QMF displays the report and the user can then view the report and issue a QMF PRINT command to print it.

For interactive sessions, instruct users to enter EXIT on the command line when they are finished viewing the report. The initial procedure runs repeatedly until an EXIT command is issued. Pressing the End function key from the report panel reruns the initial procedure; it does not display the QMF Home panel.

Additionally, when you use the DSQSRUN parameter, make sure that the DSQEC__RERUN__IPROC global variable is set to 0 and that the current object is not the QMF Home panel. The Developing QMF Applications manual provides more information on this global variable, as well as information about how to write procedures that help users perform QMF activities specified in predefined procedures and applications.

Passing variable values to an initial procedure

When you supply the name of an initial procedure on the DSQSRUN parameter, you can also supply values for variables contained in the procedure. You can specify one or more variables and their values following the procedure name on the DSQSRUN parameter.

When you specify the name of an initial procedure, QMF issues a RUN PROC command that runs the procedure. When you use variables in your procedure, the values you supply for these variables must conform to the syntax used for passing variables on a RUN command. For information about this syntax, see the QMF Reference manual.

For example, suppose you frequently need two pieces of information about employees in your organization. One piece of information is the name of the employee, and the other varies. You could define a query that includes NAME and uses a variable for the other column. Figure 54 shows an example query and procedure. The figure also shows how to pass a value for the variable when you enter the DSQSRUN parameter, and shows the RUN PROC command that QMF issues.

Figure 54. Passing a QMF column name using DSQSRUN

Query (named JONES.QUERY2): SELECT NAME, &COL FROM Q.STAFF
Procedure (named JONES.PROC2): RUN QUERY JONES.QUERY2 (&&COL=&COL
DSQSRUN parameter: QMFn I=JONES.PROC2(&COL=YEARS)
Resulting RUN command: RUN PROC JONES.PROC2 (&COL=YEARS)

Figure 55 shows a similar example, but instead of passing one column name to the procedure, it allows you to pass several, which return the employee's name, the department, and the employee's salary.

Figure 55. Passing several QMF column names using DSQSRUN

Query (named JONES.QUERY3): SELECT &COLS FROM Q.STAFF
Procedure (named JONES.PROC3): RUN QUERY JONES.QUERY3 (&&COLS=&COLS
DSQSRUN parameter: QMFn I=JONES.PROC3(&COLS=((DEPT,NAME, SALARY))
Resulting RUN command: RUN PROC JONES.PROC3(&COLS=((DEPT,NAME,SALARY)))

The next four examples show how to pass information you normally supply after the WHERE keyword in a query. (See the QMF Reference manual for more information about the WHERE keyword.)

These examples contain character strings. Special syntax is required due to the way QMF evaluates the values when it processes the RUN PROC command. Special characters (comma, blank, parentheses, quotes, apostrophe or single quote, and equal sign) can also be included in the string as shown.

For example, if you need to know the names and employee numbers of all the managers in your organization, you could run a query like the one in Figure 56. When you pass the character string MGR on the DSQSRUN parameter, be sure to enclose the value in single quotes.

Figure 56. Passing a string within single quotes using DSQSRUN

Query (named JONES.QUERY4): SELECT JOB, NAME, ID FROM Q.STAFF WHERE JOB=&JOB
Procedure (named JONES.PROC4): RUN QUERY JONES.QUERY4 (&&JOB=&JOB
DSQSRUN parameter: QMFn I=JONES.PROC4(&JOB='MGR')
Resulting RUN command: RUN PROC JONES.PROC4 (&JOB='MGR')

Figure 57 shows how to pass variable values that contain commas. Enclose the value SAN JOSE, CA in single quotes because it contains a comma.

Figure 57. Passing a comma within a string using DSQSRUN

Query (named JONES.QUERY5): SELECT * FROM Q.APPLICANT WHERE ADDRESS+&CITY
Procedure (named JONES.PROC5): RUN QUERY JONES.QUERY5 (&&CITY=&CITY
DSQSRUN parameter: QMFn I=JONES.PROC5(&CITY='SAN JOSE,CA')
Resulting RUN command: RUN PROC JONES.PROC5 (&CITY='SAN JOSE,CA')

Figure 58 shows how to pass variable values that contain single quotes (for example, an apostrophe in a name). When you pass the value on the DSQSRUN parameter, be sure to enclose the value in single quotes and use two single quotes for the apostrophe instead of one.

Figure 58. Passing an apostrophe as part of a string using DSQSRUN

Query (named JONES.QUERY6): SELECT * FROM Q.STAFF WHERE NAME=&NAME
Procedure (named JONES.PROC6): RUN QUERY JONES.QUERY6 (&&NAME=&NAME
DSQSRUN parameter: QMFn I=JONES.PROC6(&NAME='O''BRIEN')
Resulting RUN command: RUN PROC JONES.PROC6 (&NAME='O''BRIEN')

Figure 59 shows how to pass values for variables in two different parts of the query.

Figure 59. Passing multiple variable parameters and values using DSQSRUN

Query (JONES.QUERY7): SELECT * FROM Q.STAFF WHERE DEPT IN &DEPT AND JOB=&JOB
Procedure (named JONES.QUERY7): RUN JONES.QUERY7 (&&DEPT=&V1 &&JOB=&V2
DSQSRUN parameter: QMFn I=JONES.PROC7(&V1=(((10,38))) &V2='MGR')
Resulting RUN command: RUN PROC JONES.PROC7(&V1=(((10,38))) &V2='MGR')

DSQSDBCS (Setting printing for double-byte character set data)

If you use the Uppercase or Japanese NLF, you might need to print double-byte character set (DBCS) data. You can set the DSQSDBCS program parameter to YES to print DBCS data from non-DBCS terminals.

For example, suppose a user with an IBM 3279 display terminal needs to print a table (DBCSTABLE) whose nonnumeric columns contain DBCS data. The following statement starts the Uppercase NLF from a cleared CICS screen and allows the user to print DBCSTABLE using a command such as PRINT DBCSTABLE (PRINTER=DBCSPRT.