The report is a log of Database Services Utility processing activity on DB2 Server for VSE. You can use JCL to assign the output to a variety of output devices: printer, tape, or disk.
The message file is a log of Database Services Utility processing activity on DB2 Server for VM. The SQLDBSU EXEC or the CMS FILEDEF command can direct the message file to a variety of output devices; you can display or print its contents in three distinct forms.
Everything in a report or message file belongs in one of three categories:
All parts of a set of commands, SQL or Database Services Utility, are considered command input. Even the data embedded in a DATALOAD TABLE statement is command input. Record for record or line for line, this type of output matches the format of the (input) control file. A command-input record in the report starts with an arrow (------>).
Except when suppressed by a Database Services Utility control parameter in a calling application program, all system messages, SQL and Database Services Utility, are sent to either the report or message file. A system-output record or line starts with a message identifier beginning with ARI.
To include data in a report, use the LIST (YES) parameter in DATALOAD's INFILE subcommand. You can identify data in the report by the absence of arrows (------>) or message identifiers (ARI...). Figure 49 shows a simulated report printout.
To include data in a message file, use the LIST (YES) parameter in DATALOAD's INFILE subcommand. Through the SELECT statement, the Database Services Utility allows a limited amount of system-user interaction. Do not use the utility as an alternative to ISQL, but if you are in a Database Services Utility session, you can enter SQL commands to query the database from your workstation (assigned as control file) without leaving the utility. You can identify data in the message file by the absence of arrows (------>) or message identifiers (ARI...). Figure 49 shows a simulated message-file printout.
ARI0801I DBS Utility started: 07/24/89 17:38:53.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
------> CONNECT "TARA " IDENTIFIED BY ********;
ARI8004I User TARA connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------> ACQUIRE PRIVATE DBSPACE NAMED TARASPACE;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------> CREATE TABLE DEPARTMENT (DEPTNO CHAR(3) NOT NULL,
------> DEPTNAME VARCHAR(36) NOT NULL,
------> MGRNO CHAR(6) ,
------> ADMRDEPT CHAR(3) NOT NULL,
------> PRIMARY KEY (DEPTNO)) IN TARASPACE;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------> DATALOAD TABLE (DEPARTMENT)
------> DEPTNO 1-3
------> DEPTNAME 5-32
------> MGRNO 34-39 NULL IF POS (34-39)=' '
------> ADMRDEPT 41-43
------> INFILE(*)
ARI0852I DATALOAD TABLE processing started.
ARI8981I Dynamic statistic accumulation was disallowed
for table 'TARA'.'DEPARTMENT',
reason code = 01.
------> A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00
------> B01 PLANNING 000020 A00
------> C01 INFORMATION CENTER 000030 A00
------> D01 DEVELOPMENT CENTER A00
------> E01 SUPPORT SERVICES 000050 A00
------> D11 MANUFACTURING SYSTEMS 000060 D01
------> D21 ADMINISTRATION SYSTEMS 000070 D01
------> E11 OPERATIONS 000090 E00
------> E21 SOFTWARE SUPPORT 000100 E00
------> ENDDATA
ARI0875I 9 row(s) loaded into table TARA.DEPARTMENT.
ARI8996I ...Begin UPDATE STATISTICS for TARA.DEPARTMENT.
ARI0855I DATALOAD processing successful.
------> SELECT * FROM DEPARTMENT;
In Figure 49, note that the arrows show command input. Each arrow corresponds to either a record in the input control card file or a line in the control file. Note also that the rest of the records or lines start with ARI, denoting messages.
Figure 50 illustrates the next part of the simulated report or message file printout.
Figure 50. Sample Output Containing Data
SELECT * FROM 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 E01 SUPPORT SERVICES 000050 A00 D11 MANUFACTURING SYSTEMS 000060 D01 D21 ADMINISTRATION SYSTEMS 000070 D01 E11 OPERATIONS 000090 E00 E21 SOFTWARE SUPPORT 000100 E00 ARI0850I SQL SELECT processing successful: Rowcount = 9
The output is formatted such that the data in columns and rows as a normal table. Figure 50 is an example of the column (or tabular) form of output. No message- or command-input designations appear at the start of data records. Figure 51 shows the end of the simulated report printout.
Figure 51. Concluding Messages
ARI0802I End of command file input ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No error(s) occurred during command processing. ARI0808I DBS processing completed: 07/24/89/17:38:55.
Every Database Services Utility job ends with messages from the Database Services Utility that summarize the errors, if any, that occurred and give the completion timestamp, the system status, and a return code. For a complete listing of messages, see the DB2 Server for VM Messages and Codes manual.
If either command-file input or query results data has record or line lengths too wide for the page or screen, the Database Services Utility automatically switches to a block-form presentation. Figure 52 shows a simulated printout in block form.
Figure 52. Sample Printout of Block Output Format
ARI0801I DBS Utility started: 11/13/89 17:42:51
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
------> COMMENT '***********************************************
------> *** BLOCK FORMAT PRINTOUT EXAMPLE ***
------> ***********************************************'
------> CONNECT "MIKE " IDENTIFIED BY ********;
ARI8004I User MIKE connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE= 0 SQLSTATE = 00000 ROWCOUNT = 0
------> ALTER TABLE ACTIVITY ADD
------> "FULL DESCRIPTION" VARCHAR(250);
ARI0500I SQL PROCESSING WAS SUCCESSFUL.
ARI0505I SQLCODE= 0 SQLSTATE = 00000 ROWCOUNT = 0
------> UPDATE ACTIVITY SET "FULL DESCRIPTION" = 'A FULL DESCRIPTION
------> OF ACTIVITIES WOULD BE DISPLAYED HERE. THE DESCRIPTION COU
------> LD OVERFLOW TO THE NEXT DISPLAY LINE FOR THE COLUMN BECAUSE
------> THE COLUMN CAN CONTAIN UP TO 250 DATA POSITIONS';
ARI0500I SQL processing was successful.
ARI0505I SQLCODE= 0 SQLSTATE = 00000 ROWCOUNT = 0
------> SELECT * FROM ACTIVITY WHERE ACTNO < 30
SELECT * FROM ACTIVITY WHERE ACTNO < 30 PAGE 1
***** 1 *****
ACTNO: 10 ACTKWD: MANAGE ACTDESC: MANAGE/ADVISE
FULL DESCRIPTION: A FULL DESCRIPTION OF ACTIVITIES WOULD BE DISPLAY
ED HERE. THE DESCRIPTION COULD OVERFLOW TO THE N
EXT DISPLAY LINE FOR THE COLUMN BECAUSE THE COLUM
N CAN CONTAIN UP TO 250 DATA POSITIONS
***** 2 *****
ACTNO: 20 ACTKWD: ECOST ACTDESC: ESTIMATE COST
FULL DESCRIPTION: A FULL DESCRIPTION OF ACTIVITIES WOULD BE DISPLAY
ED HERE. THE DESCRIPTION COULD OVERFLOW TO THE N
EXT DISPLAY LINE FOR THE COLUMN BECAUSE THE COLUM
N CAN CONTAIN UP TO 250 DATA POSITIONS
ARI0850I SQL SELECT processing successful: Rowcount = 2
As in a tabular format query result, the block format has a heading and page number; from there on, however, differences appear. Each row of the query result is presented in a separate block of lines preceded by a subheading that identifies the number of the row in the answer set. Individual fields of a row are preceded by their column names and are to be read from left to right, and top to bottom.
The list form of output is similar to block form. Figure 53 shows an example of the list output format.
Figure 53. Sample Printout of List Output Format
SELECT * FROM DEPARTMENT PAGE 1
***ROW: 1
DEPTNO: A00
DEPTNAME: SPIFFY COMPUTER SERVICES DIV.
MGRNO: 000010
ADMRDEPT: A00
FULL DESCRIPTION: A FULL DESCRIPTION OF THE DEPARTMENT WOULD BE
DISPLAYED HERE. THE DESCRIPTION COULD OVERFLOW TO
THE NEXT DISPLAY LINE FOR THE COLUMN.
The list output format resembles the block output format in that there is a heading and page number. Each row of the query result is presented in a separate list of records or lines preceded by a subheading that identifies the number of the row in the answer set. Individual fields of a row are preceded by their column names and are presented in separate records. The difference, however, is that with list output format, the output for each selected row begins on a new page, and the column name and data for each select-list column begins on a new output record or display line.
You can specify the format used by the Database Services Utility for SQL SELECT statement output by using either the SET FORMAT command or the FORMAT control parameter. If you do not specify the format to be used, the Database Services Utility uses column or block format as appropriate. For more information on the SET FORMAT command, refer to SET FORMAT. For a description of the control parameter FORMAT, see Database Services Utility Control Parameters.
The example in Figure 54 shows the output from a DATALOAD command that was processed with the LIST(YES) option of the INFILE subcommand in effect.
Figure 54. Using YES in the LIST Parameter
-------> DATALOAD TABLE (SQLDBA.ACTIVITY) IF POS(1)='Y'
-------> ACTNO 2-11
-------> ACTDESC 12-111 *---------*
-------> INFILE (* LIST(YES) CONTINUED(YES)) |Record 1.|
ARI0852I DATALOAD PROCESSING STARTED. *--*------*
N2345678901THE DATA IN THIS RECORD IS NOT LOADED INTO THE TABLE. <---------------*
XY2345678902THIS DESCRIPTION OF AN ACTIVITY FILLS UP A WHOLE PHYSICAL INPUT *---------*
RECORD AND CONTINUES. <-----*Record 2.|
XY2345678903THIS ACTIVITY DESCRIPTION DOES NOT FILL THE RECORD. <------------* *---------*
<------------------------* |
------> ENDDATA *--*---------------* *-*-------*
ARI0875I 2 row(s) loaded into table SQLDBA.ACTIVITY. |This blank line | |Record 3.|
ARI0855I DATALOAD processing successful. |contains positions| *---------*
|80-111 of data |
|record 3. |
*------------------*
|
With continued record processing, each field of the records must contain the maximum number of characters. The second and third records' ACTDESC fields are 100 characters in length because they are to be loaded (position 1 is Y). The system does not check the length of the first record's ACTDESC field because this record is not to be loaded (position 1 is N).
The example in Figure 55 shows output for a DATALOAD command that was processed using the LIST(NO) option of the INFILE subcommand:
Figure 55. Using NO in the LIST Parameter
------> DATALOAD TABLE (SQLDBA.ACTIVITY) IF POS(1)='Y' ------> ACTNO 2-11 ------> ACTDESC 12-111 ------> INFILE (* LIST(NO) CONTINUED(YES)) ARI0852I DATALOAD PROCESSING STARTED. ------> ENDDATA ARI0875I 2 row(s) loaded into table SQLDBA.ACTIVITY. ARI0855I DATALOAD processing successful. |
Reading messages, command input, and data in output reports and message files is an important task performed frequently by users of the Database Services Utility.
To read DB2 Server for VSE report output to recover from an error, proceed as follows:
To read DB2 Server for VM message file output to recover from an error, proceed as follows: