DB2 Server for VSE & VM: Database Services Utility

Working with an Input Control Card File in DB2 Server for VSE

Creating a Control Card File

Create a control file as follows.

  1. Provide the following commands and statements shown in Figure 3 with the JCL statements needed to run the job.

    Figure 3. Example of a Simplified Input Control Card File

    // JOB DBS UTILITY EXAMPLE VSE MULTIPLE USER MODE JOB CONTROL
// EXEC PROC=ARIS62PL            >----DB2 Server for VSE Production Library Definition
// EXEC ARIDBS, SIZE=AUTO        >----invoke DBS Utility
 CONNECT your user ID IDENTIFIED BY your password;
 SELECT * FROM SQLDBA.DEPARTMENT;
 SELECT * FROM SQLDBA.PROJECT;
/*
/&

    The following statement runs the Database Services Utility: 

    // EXEC ARIDBS,SIZE=AUTO
  2. Ensure that the input control card file has a fixed record length.
  3. Store the input control card file.

Working with a Report

The Database Services Utility creates a report on the device that your installation assigned to SYSLST.

After you submit the Database Services Utility job that you created in Figure 3, and it finishes processing the input control card file, look at the results in the report shown in Figure 4.
Note:The report may contain error messages if errors occurred when the Database Services Utility was processing the commands in the input control card file.

Figure 4. Database Services Utility: Example Report Output

 
 ARI0801I DBS Utility started: 07/18/89 16:10:31.         <------------(1)
          AUTOCOMMIT = OFF ERRORMODE = OFF                <----*----(2)
          ISOLATION LEVEL = REPEATABLE READ               <----*
 ------> CONNECT "SQLDBA  " IDENTIFIED BY ********;
 ARI8004I User SQLDBA connected to database SQLDBA.
 ARI0500I SQL processing was successful.
 ARI0505I SQLCODE = 0  SQLSTATE = 00000  ROWCOUNT = 0
 ------>
 ------> SELECT * FROM SQLDBA.DEPARTMENT;                 <------------(3)
 SELECT * FROM SQLDBA.DEPARTMENT                                               PAGE       1
 DEPTNO DEPTNAME                      MGRNO  ADMRDEPT     ---*
 ------ ----------------------------- ------ --------        |
 A00    SPIFFY COMPUTER SERVICE DIV.  000010 A00             |
 B01    PLANNING                      000020 A00             |
 C01    INFORMATION CENTER            000030 A00             |
 D01    DEVELOPMENT CENTER                   A00             *-----(4)
 D11    MANUFACTURING SYSTEMS         000060 D01             |
 D21    ADMINISTRATION SYSTEMS        000070 D01             |
 E01    SUPPORT SERVICES              000050 A00             |
 E11    OPERATIONS                    000090 E01             |
 E21    SOFTWARE SUPPORT              000100 E01             |
 ARI0850I SQL SELECT processing successful: Rowcount = 9  ---*
 ------> SELECT * FROM SQLDBA.PROJECT;                <------------(5)

 
 SELECT * FROM SQLDBA.PROJECT                                                  PAGE       2
 PROJNO PROJNAME            DEPTNO RESPEMP PRSTAFF PRSTDATE   PRENDATE   --*
 ------ ------------------- ------ ------- ------- ---------- ----------   |
 AD3100 ADMIN SERVICES      A00    000010     6.50 1982-01-01 1983-02-01   |
 MA2100 WELD LINE AUTOMATIO D01    000010    12.00 1982-01-01 1983-02-01   |
 AD3111 PAYROLL PROGRAMMING B01    000020     2.00 1982-01-01 1983-02-01   |
 PL2100 WELD LINE PLANNING  B01    000020     1.00 1982-01-01 1982-09-15   |
 IF1000 QUERY SERVICES      C01    000030     2.00 1982-01-01 1983-02-01   |
 IF2000 USER EDUCATION      C01    000030     1.00 1982-01-01 1983-02-01   |
 OP1000 OPERATION SUPPORT   E01    000050     6.00 1982-01-01 1983-02-01   |
 OP2000 GEN SYSTEMS SERVICE E01    000050     5.00 1982-01-01 1983-02-01   |
 MA2110 W L PROGRAMMING     D11    000060     9.00 1982-01-01 1983-02-01   |
 AD3110 GENERAL AD SYSTEMS  E11    000090     6.00 1982-01-01 1983-02-01   |
 OP1010 OPERATION           E11    000090     5.00 1982-01-01 1983-02-01   *--(6)
 OP2010 SYSTEMS SUPPORT     E21    000100     4.00 1982-01-01 1983-02-01   |
 MA2112 W L ROBOT DESIGN    D11    000150     3.00 1982-01-01 1982-12-01   |
 MA2113 W L PROD CONT PROGS D11    000160     3.00 1982-02-15 1982-12-01   |
 MA2111 W L PROGRAM DESIGN  D11    000220     2.00 1982-01-01 1982-12-01   |
 AD3112 PERSONNEL PROGRAMMG D21    000250     1.00 1982-01-01 1983-02-01   |
 AD3113 ACCOUNT.PROGRAMMING D21    000270     2.00 1982-01-01 1983-02-01   |
 OP2011 SCP SYSTEMS SUPPORT E21    000320     1.00 1982-01-01 1983-02-01   |
 OP2012 APPLICATIONS SUPPOR E21    000330     1.00 1982-01-01 1983-02-01   |
 OP2013 DB/DC SUPPORT       E21    000340     1.00 1982-01-01 1983-02-01   |
 ARI0850I SQL SELECT processing successful: Rowcount = 20                --*
 ARI0802I End of command file input.                             <------------(7)
 ARI8997I ...Begin COMMIT processing.                                -xxxxx*
 ARI0811I ...COMMIT of any database changes successful.                    |
 ARI0809I ...No error(s) occurred during command processing.               *--(8)
 ARI0808I DBS processing completed: 07/18/89 16:10:33.               -xxxxx*

Notes for Figure 4:

(1)
The Database Services Utility start message.

(2)
The Database Services Utility default values. See "Set-Item Commands" in Chapter 8, "Command Reference" for details on changing these defaults.

(3)
The first SELECT statement that the Database Services Utility is to process.

(4)
Results of the Database Services Utility processing the SELECT statement show the rows retrieved from the table, a message to indicate that the SELECT statement was successful, and the number of rows retrieved.

(5)
The next SELECT statement that the Database Services Utility is to process.

(6)
Results of the Database Services Utility processing the SELECT statement show the rows retrieved from the table, a message to indicate that the SELECT statement was successful, and the number of rows retrieved.

(7)
Database Services Utility has processed all commands in the input control card file.

(8)
Database Services Utility completion messages.

If you encounter the following message, look at the report to find the error or errors: 

ARI0807E ...Error(s) occurred during command processing.

Error types are listed and discussed in Chapter 9, Error Handling and Debugging. Item (6) in Figure 5 shows an example of an error found in a report.

Figure 5. Example of a Database Services Utility Error

 
 ARI0801I DBS Utility started: 07/18/89 16:10:47.      <------------(1)
          AUTOCOMMIT = OFF ERRORMODE = OFF             <----*----(2)
          ISOLATION LEVEL = REPEATABLE READ            <----*
 ------> CONNECT "SQLDBA  " IDENTIFIED BY ********;
 ARI8004I User SQLDBA connected to database  SQLDBA.
 ARI0500I SQL processing was successful.
 ARI0505I SQLCODE = 0  SQLSTATE = 00000  ROWCOUNT = 0
 ------>
 ------> SELECT * FROM SQLDBA.DEPARTMENT;              <------------(3)
 SELECT * FROM SQLDBA.DEPARTMENT                                               PAGE       1
 DEPTNO DEPTNAME                      MGRNO  ADMRDEPT      ---*
 ------ ----------------------------- ------ --------         |
 A00    SPIFFY COMPUTER SERVICE DIV.  000010 A00              |
 B01    PLANNING                      000020 A00              |
 C01    INFORMATION CENTER            000030 A00              |
 D01    DEVELOPMENT CENTER                   A00              *-----(4)
 D11    MANUFACTURING SYSTEMS         000060 D01              |
 D21    ADMINISTRATION SYSTEMS        000070 D01              |
 E01    SUPPORT SERVICES              000050 A00              |
 E11    OPERATIONS                    000090 E01              |
 E21    SOFTWARE SUPPORT              000100 E01              |
 ARI0850I SQL SELECT processing successful: Rowcount = 9   ---*
 ------> SELECT * FROM SQLDBA.PROJJECT;                <------------(5)
 ARI0503E An SQL error has occurred.                           --*
          SQLDBA.PROJJECT was not found in the system catalogs.  |
 ARI0505I SQLCODE = -204  SQLSTATE = 52004  ROWCOUNT = 0         *--(6)
 ARI0504I SQLERRP: ARIXOCA SQLERRD1: -100 SQLERRD2: 0            |
 ARI0851E SQL SELECT processing unsuccessful: Rowcount = 0     --*
 ARI8998I ...Begin ROLLBACK processing.                   ----*
 ARI0811I ...ROLLBACK of any database changes successful.     |
 ARI0813I ...Suspend command execution:                       *-----(7)
          AUTOCOMMIT = OFF ERRORMODE = ON                     |
 ARI0802I End of command file input.                      ----*
 ARI0807E ...Error(s) occurred during command processing. <---*-----(8)
 ARI0808I DBS processing completed: 07/18/89 16:10:47.    <---*

Notes for Figure 5:  

(1)
The Database Services Utility start message.

(2)
The Database Services Utility default values. See "Set-Item Commands" in Chapter 8, "Command Reference" for details on changing these defaults.

(3)
The first SELECT statement that the Database Services Utility is to process.

(4)
Results of the Database Services Utility processing the SELECT statement shows the rows retrieved from the table, a message to indicate that the SELECT statement was successful, and the number of rows retrieved.

(5)
The next SELECT statement that the Database Services Utility is to process.

(6)
Messages indicating that the SELECT statement could not be successfully processed. The message indicates that the SQLDBA.PROJJECT table could not be found in the database; PROJJECT is misspelled. You should now correct the spelling in the input control card file and run the job again.

(7)
Indicates that the Database Services Utility encountered an error and cannot process any commands that follow. This message is not relevant to the present example because no more commands follow. If commands followed this one in error, they would not be processed.
Note:This command suspension can be controlled by the user; see "SET ERRORMODE" in Chapter 8, Command Reference.

(8)
Completion messages.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]