DB2 Server for VSE & VM: Database Services Utility

General Loading Procedures

Comparison Operators

In the procedures that follow, you specify a comparison operator. The following comparison operators are supported by the Database Services Utility: Comparison Operators

    =     Equal to
    ¬=    Not equal to
    <>    Not equal to
    >     Greater than
    >=    Greater than or equal to
    <     Less than
    <=    Less than or equal to

Suppose that you are loading data to a table named DEPARTMENT in columns DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT, but you do not have a manager for every department. You still want to insert the DEPTNO, DEPTNAME, and ADMRDEPT into the database. For those departments that do not have a manager, you want to insert a null value. Use the TCI subcommand's null-current-clause. Figure 20 illustrates one way to code the MGRNO TCI to load null values.

Figure 20. TCI Subcommand with a Null Clause

DATALOAD TABLE (DEPARTMENT) DEPTNO 1-3 DEPTNAME 5-40 MGRNO 42-47 NULL IF POS (42-47)=' ' <----6 blanks. ADMRDEPT 49-51 INFILE (NEWDEPT)

A translation of this clause is: make the corresponding table field null if input-record positions 42 through 47 are blank.

To specify a null condition in the TCI subcommand, do the following:

Leave one or more spaces after the TCI subcommand's endpos (or data type) parameter, and include:
```
NULL IF POS (startpos-endpos) operator constant
```
where startpos is the first character position in the input record that contains the comparison string, endpos is the last position of the string, constant is the value against which the string at position startpos-endpos is to be compared, and operator is a comparison operator. (See Comparison Operators for a list of comparison operators.) Do not put spaces within the brackets.
Proceed to the next DATALOAD subcommand.

Note: The positions checked for the null value need not be in the same positions occupied by the data field for the column. You can assign the null value to a column depending on any convention you choose. For example, to set the MGRNO column to NULL whenever a blank exists in position 11 of the DEPTNAME column, code the MGRNO TCI as follows:
MGRNO 42-47 NULL IF POS (11) = ' '

However, if the positions of the data fields and the positions specified by the startpos and endpos in the subcommand's null-current-clause overlap, your data may be overlaid. For more information on the null-current-clause, see page ***.

Alternative Method

Another way to insert null values into the database for new rows is by omitting a TCI subcommand for that column:
DATALOAD TABLE (DEPARTMENT) DEPTNO 1-3 DEPTNAME 5-40 ADMRDEPT 49-51 INFILE (NEWDEPT)

In the above example, there is no TCI subcommand for the MGRNO column of the DEPARTMENT table. For each new row inserted, the MGRNO field is null. The columns that the utility loads null values into must permit nulls.

Alternative Method
Another way to insert null values into the database for new rows is by omitting a TCI subcommand for that column: DATALOAD TABLE (DEPARTMENT) DEPTNO 1-3 DEPTNAME 5-40 ADMRDEPT 49-51 INFILE (NEWDEPT) In the above example, there is no TCI subcommand for the MGRNO column of the DEPARTMENT table. For each new row inserted, the MGRNO field is null. The columns that the utility loads null values into must permit nulls.

Loading CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP Values

The database manager supports the following date and time formats: International Standards Organization (ISO) form, IBM Standard for Europe form (EUR), IBM Standard for the U.S. form (USA), Japanese Industrial Standard Christian Era form (JIS), and an installation-defined form (LOCAL).

Suppose that someone created the following table with the SQL CREATE TABLE statement and you want to load data into the table.

CREATE TABLE PAYABLE
    (COMPANY       CHAR(20),
     PAYMENT_DUE   DATE,
     AMOUNT        DEC(9,2));

Some of the companies in the PAYABLE table are in arrears with their payments. For these organizations, you want the payment due date to be today's date. Use the TCI subcommand's null-current-clause. Figure 21 shows one way to code the TCI subcommand PAYMENT_DUE to load the current date. (This example does not use the sample tables; therefore, do not attempt to process it.)

Figure 21. TCI Subcommand with a Current-Date Clause

DATALOAD TABLE (PAYABLE) COMPANY 1-20 PAYMENT_DUE 22-31 CURRENT DATE IF POS(22-30) = 'IMMEDIATE' AMOUNT 35-45 INFILE(*) VESUVIUS, INC. 2000-05-01 5000.00 ATLANTIS CO. 28.05.1999 3820.00 TITANIC LTD. IMMEDIATE 7250.00 SKY INC. 05/22/1999 300.00 ENDDATA

A translation of the current date clause is: load the corresponding table field with the current date if input-record positions 22 through 30 contain the string IMMEDIATE.

To specify a current date in the TCI subcommand, proceed as follows:

Leave one or more spaces after the TCI subcommand's ENDPOS (or DATATYPE) parameter, and include:
```
CURRENT DATE IF POS (startpos-endpos) operator constant
```
where startpos is the first character position in the input record that contains the comparison string, endpos is the last position of that string, constant is the value against which the string startpos-endpos is to be compared, and operator is a comparison operator. See Comparison Operators for a list of comparison operators.
Proceed to the next DATALOAD subcommand.

To load current times and timestamps, replace CURRENT DATE with CURRENT TIME or CURRENT TIMESTAMP in Step 1 of this procedure. For example, in Figure 21, you could replace CURRENT DATE with CURRENT TIME if the PAYMENT_DUE column were TIME data type.
Note: The current date, current time, and current timestamp value is acquired by Database Services Utility at the start of the DATALOAD command processing, and will not change throughout the DATALOAD command processing.

Loading Data into Multiple Tables

You can load the same data records into more than one table, or load different data records in the same input file into their respective tables. When you are loading data into more than one table, the Database Services Utility automatically performs an UPDATE STATISTICS (unless a SET UPDATE STATISTICS OFF command has been issued) after the DATALOAD command processing is completed successfully.

Loading Mixed INFILE Records into the Correct Tables

If you had to load data into two tables, you would probably prepare two DATALOAD commands that could be run either separately or consecutively in the same (input) control file. This is shown in Figure 22.

Figure 22. Separate DATALOAD Commands Run Successively

DATALOAD TABLE (ACTIVITY) ACTNO 1-3 ACTKWD 5-10 ACTDESC 12-31 INFILE (*) 190 MARKET MARKETING 200 CUSTOM CUSTOMER SUPPORT 25 RSRCH RESEARCH 55 TRAIN TRAINING ENDDATA DATALOAD TABLE (DEPARTMENT) DEPTNO 1-3 DEPTNAME 5-23 MGRNO 25-30 ADMRDEPT 32-34 INFILE (*) F01 PERSONNEL 000110 A00 G01 MARKETING AND SALES 000120 A00 ENDDATA

If, for some reason, the input data records for two tables were mixed in one data group, you could run the single data group against multiple DATALOAD commands. This is possible with the use of the DATALOAD statement's input-record-id clause. Figure 23 shows two DATALOAD commands that share one INFILE(*) subcommand. Both DATALOAD commands have input-record-id clauses that specify the records that belong to each table.

Figure 23. DATALOAD Commands Sharing a Data File

DATALOAD TABLE (ACTIVITY) IF POS (54)='1' ACTNO 1-3 -------*------- ACTKWD 5-10 | ACTDESC 12-31 *--------------------* DATALOAD TABLE (DEPARTMENT) IF POS (54)='2' | DEPTNO 1-3 -------*------- *-------*-------* DEPTNAME 5-23 *----------*INPUT-RECORD-ID| MGRNO 25-30 | Clauses | ADMRDEPT 32-34 *---------------* INFILE (*) 190 MARKET MARKETING 1 F01 PERSONNEL 000110 A00 2 200 CUSTOM CUSTOMER SUPPORT 1 G01 MARKETING AND SALES 000120 A00 2 25 RSRCH RESEARCH 1 55 TRAIN TRAINING 1 ENDDATA ^ | *-------------* | |Column 54, *----* |Control Field| *-------------*

Translations of the input-record-id clauses are:

ACTIVITY Table. If position 54 of an input data record contains a 1, load the ACTIVITY table with that record.
DEPARTMENT Table. If position 54 of an input data record contains a 2, load the DEPARTMENT table with that record.

To load separate tables with mixed input data from a single file, proceed as follows:

Leave one or more spaces after the DATALOAD command's table-name parameter, and include:
```
IF POS (startpos-endpos) operator constant
```
where startpos is the first character position in the control field, endpos is the last position in that field, constant is the value against which the string startpos-endpos is to be compared, and operator is a comparison operator. See Comparison Operators for a list of comparison operators.
Enter each TCI subcommand on a new line by including:
```
column-name startpos-endpos data-type
```
where column-name is the name of the table column; startpos is the starting position of the input record; endpos is the last position in the input record; and data-type is the data format of the column values. If the data type is character (CHAR), you can omit it.
Repeat the previous two steps for each table to be loaded (that is, for each DATALOAD command).
Continue with command and data entry.

Note: Ensure that the control field occupies the same position or positions in each of the data records of the input file.

Loading a Single Record into Several Tables

When the Database Services Utility loads records from a mixed input file into multiple tables, each data record is inserted into a particular table only. You can also write DATALOAD commands so that a single input data record can be a source of rows in more than one table. For example, suppose that you want to expand your activities for each project. Each activity number added to the ACTIVITY table has a corresponding activity number added to the PROJ_ACT table. To use a single input data record to make entries in these two tables, you could code utility commands as shown in Figure 24.

Figure 24. Individual Records Supplying the Same Activity Number to Two Tables

DATALOAD TABLE(ACTIVITY) ACTNO 1-3 ACTKWD 5-10 ACTDESC 12-27 DATALOAD TABLE(PROJ_ACT) PROJNO 29-34 ACTNO 36-38 ACSTAFF 40-43 ACSTDATE 45-54 ACENDATE 56-65 INFILE (*) 190 MARKET MARKETING AD3100 190 0.50 1999-01-02 1999-04-30 200 CUSTOM CUSTOMER SUPPORT OP2000 200 1.50 1998-03-01 1999-12-31 55 TRAIN TRAINING IF2000 55 1.00 1999-02-01 1999-09-05 ENDDATA

In Figure 24, each DATALOAD command has its own set of TCI subcommands that point to unique positions on the same input record.
Sixteen DATALOADs Sharing One INFILE

When more than one DATALOAD command (without input-record-id clauses) precedes an INFILE subcommand, the input data identified by the INFILE subcommand is used for all tables identified in the DATALOAD commands. A maximum of 16 DATALOAD commands can precede an INFILE subcommand. You can load 16 tables at the same time.

Sixteen DATALOADs Sharing One INFILE
When more than one DATALOAD command (without input-record-id clauses) precedes an INFILE subcommand, the input data identified by the INFILE subcommand is used for all tables identified in the DATALOAD commands. A maximum of 16 DATALOAD commands can precede an INFILE subcommand. You can load 16 tables at the same time.

In Figure 24, the activity number is repeated twice (once for the ACTIVITY table and once for the PROJ_ACT table) in each input data record. Eliminate the need for duplicate fields by using TCI subcommands for each of the tables that point to the same physical location in the input record, as shown in Figure 25.

Figure 25. TCIs in Individual DATALOADs That Point to the Same Location

DATALOAD TABLE(ACTIVITY) *------------------------* ACTNO 1-3 <--------| | ACTKWD 5-10 | These two subcommands | ACTDESC 12-27 | point to the same | DATALOAD TABLE(PROJ_ACT) | physical location in | PROJNO 29-34 | the input data records.| ACTNO 1-3 <--------| | ACSTAFF 40-43 *------------------------* ACSTDATE 45-54 ACENDATE 56-65 INFILE (*) 190 MARKET MARKETING AD3100 0.50 1989-01-02 1989-04-30 200 CUSTOM CUSTOMER SUPPORT OP2000 1.50 1989-03-01 1995-12-31 55 TRAIN TRAINING IF2000 1.00 1989-02-01 1989-09-05 ENDDATA

To use similar TCI subcommands in multiple DATALOAD TABLE commands, proceed as follows:

Find the correct startpos-endpos value for each TCI subcommand that is common to more than one table.
Include the TCI statement in each affected DATALOAD command, and give the same startpos-endpos value for each.

Combining Records to Load Multiple Table Rows

Usually, one input data record provides information for one table row. This is illustrated in Figure 26.

Figure 26. Normal Relationship: One Record for One Row

DATALOAD TABLE(ACTIVITY) ACTNO 1-3 ACTKWD 5-10 ACTDESC 12-27 INFILE (*) 190 MARKET MARKETING 200 CUSTOM CUSTOMER SUPPORT 25 RSRCH RESEARCH 55 TRAIN TRAINING ENDDATA

Using multiple DATALOAD commands, however, you can load more than one table row with each input data record. Figure 27 presents a way of using multiple DATALOAD statements to load combined records into the same table.

Figure 27. Combined Records: Each for Two Rows

DATALOAD TABLE(ACTIVITY) ACTNO 1-3 ACTKWD 5-10 ACTDESC 12-27 DATALOAD TABLE(ACTIVITY) ACTNO 29-31 ACTKWD 33-38 ACTDESC 40-55 INFILE(*) 190 MARKET MARKETING 200 CUSTOM CUSTOMER SUPPORT 25 RSRCH RESEARCH 55 TRAIN TRAINING ENDDATA

In the above example, two rows are inserted into the ACTIVITY table for each data record that is read.

To load combined records into the same table, proceed as follows:

Decide how many simple data records the combined record should contain; this is the number of DATALOAD statements needed. Provide the required number of DATALOAD TABLE commands, each with the same table name.
Provide each DATALOAD TABLE command with a set of identical TCI subcommands.
Determine the correct startpos-endpos values for each DATALOAD command set; provide the appropriate value to each TCI statement.
In VSE, provide the INFILE(*) data as combined records. In VM, enter the INFILE(*) data as combined records--with each record on a separate line. Ensure that this data is positioned to correspond to the startpos-endpos values in the TCI subcommands.

Processing Data That Spans More Than One Input Record

You can load data that is continued onto the next physical record. Figure 28 illustrates the command sequence necessary to load data from 80-byte input data records into the columns ACTNO and ACTDESC in the table named SQLDBA.ACTIONS where:

The column ACTNO is defined with the data type CHAR(10).
The column ACTDESC is defined with the data type VARCHAR(100).

The CONTINUED parameter of the INFILE subcommand indicates that continued records are in the input data. If position 1 of an input data record contains a character, the input data is continued onto the next physical input data record. The above example uses a C as a continuation character, but you can use any character. The first position of each input file data record is not included in the actual input data, so character positions begin numbering from the second position. Therefore, the physical position 2 of an input data record is referred to as position 1 in the TCI subcommand.

If position 1 of the input data record is blank, the input data is contained on a single logical input record, or it is the terminating row of a continued record. All actual input data records must be at least as long as the highest end-position value specified in a TCI subcommand. In this situation, the ACTDESC field must be 100 characters in length. Therefore, a second physical input line containing all blanks is necessary to extend the ACTDESC field of the second logical input record (beginning with ACTNO=1234567891) to the maximum length value of 100.

If the highest TCI endpos value exceeds the input record length, you need to use continued input records. To construct a Database Services Utility command to load continued physical input records into a table, proceed as follows:

For DB2 Server for VSE

Define the DATALOAD TABLE and TCI statements in the usual way.
Put the INFILE statement with the CONTINUED parameter on the next record:
```
INFILE (* CONTINUED(YES))
```
The CONTINUED parameter must be on the same record as INFILE.
Provide the data records:
```
C ...data...
```
where C can be the character C or any other character, and ...data... is the actual input-record data to a maximum of 79 characters.
Depending on the length of the physical input record, do one of the following:
- If the physical record length exceeds the logical record length (LRECL=80), continue entering the data, beginning in position 2 of the following input record. Do not put a continuation character on the second record.
- If the physical record length is less than the logical record length (LRECL=80), leave the following input record blank.
If you have to enter more than two records for a physical input record, put a continuation character at the beginning of each record except the last one. The blank in position 1 terminates that input record.
For each data record, repeat the preceding two steps.
Indicate the end of input data. On a new record, put:
```
ENDDATA
```

For DB2 Server for VM

Define the DATALOAD TABLE and TCI statements in the usual way.
Type the INFILE statement as far as the asterisk:
```
INFILE (*
```
Enter the CONTINUED parameter. On the same line, leave one space; then type:
```
CONTINUED(YES))
```
Enter the data records. Type:
```
C ...data...
```
where C can be the character C or any other character, and ...data... is the actual input-record data to a maximum of 79 characters.
Depending on the length of the physical input record, do one of the following:
- If the physical record length exceeds the logical record length (LRECL=80), continue entering the data, beginning in position 2 of the following input line. Do not put a continuation character on the second line.
- If the physical record length is less than the logical record length (LRECL=80), leave the following input line blank.
If you have to enter more than two lines for a physical input record, put a continuation character at the beginning of each line except the last one. The blank in position 1 terminates that input record.
For each data record, repeat the preceding two steps.
Indicate the end of input data. On a new line, type:
```
ENDDATA
```

Committing Work While Loading Data

If the SET AUTOCOMMIT ON command has been issued, the DATALOAD command can specify that the utility issue SQL COMMIT statements periodically during processing. The interval is specified in terms of a specific number of input records processed by DATALOAD. A record is considered to be processed by DATALOAD when it is read and appropriate action is taken. The action is one of the following:

Skip the record because input record selection criteria are not met
Insert data from the record into one or more tables.

You indicate the number of records by specifying the COMMITCOUNT(ccount) parameter on the INFILE subcommand. Each time that COMMIT processing is to begin, a message (ARI0800I) is written to the message file containing the number of input records processed up to this point. The message is written as a result of COMMITCOUNT processing. You also receive a message (ARI0811I) to inform you that the changes were committed successfully.

When to Use the COMMITCOUNT Parameter

You can use COMMITCOUNT to minimize lock interface with other users of a dbspace or a table. If you set the value of ccount low enough, escalation is avoided.

The COMMITCOUNT parameter also helps reduce log space requirements during execution with multiple user mode.

To cause the Database Services Utility to commit work during processing, proceed as follows:

On the same record as the INFILE subcommand, leave one or more spaces, then include:
```
COMMITCOUNT(ccount)
```
where ccount is a number from 1 to 2,147,483,647.
Specify the rest of the DATALOAD TABLE command set.

Specifying a COMMITCOUNT value commits that number of input records to the database as soon as the Database Services Utility has processed them.
Error-Processing Example

Assume that an error occurs in a job for which a COMMITCOUNT value of 1000 has been specified. If the error occurs during the processing of record 99 501 in a 100 000-record file, a ROLLBACK (implicit) command is processed only for the database row inserts performed for the last 500 records (records 99 001 to 99 500).
If any INSERT commands are processed during DATALOAD processing of the first 99 000 records, they have already been committed to the database. Specifying a COMMITCOUNT of 1000 causes COMMIT processing to be done after every 1000 input data records are processed. The last messages in the (VSE) report or (VM) message file are:
ARI0800I ...Begin COMMIT. Input Record Count = 99000 ARI0811I ...COMMIT of any database changes was successful.

Error-Processing Example
Assume that an error occurs in a job for which a COMMITCOUNT value of 1000 has been specified. If the error occurs during the processing of record 99 501 in a 100 000-record file, a ROLLBACK (implicit) command is processed only for the database row inserts performed for the last 500 records (records 99 001 to 99 500). If any INSERT commands are processed during DATALOAD processing of the first 99 000 records, they have already been committed to the database. Specifying a COMMITCOUNT of 1000 causes COMMIT processing to be done after every 1000 input data records are processed. The last messages in the (VSE) report or (VM) message file are: ARI0800I ...Begin COMMIT. Input Record Count = 99000 ARI0811I ...COMMIT of any database changes was successful.

Determining the Number of Records Processed

During DATALOAD processing of files containing more than 15,000 data records, a message (ARI8995I) is written to either the VSE operator console or your VM terminal after every 15,000 records to let you know that the job is running normally and that n input records have been processed. These messages appear unless:

You used the INFILE subcommand's COMMITCOUNT parameter and assigned the report or message file to your terminal.
The number of records is fewer than 15,000.

Skipping Bad Records

A bad data record is one that:

Contains a data field that cannot be converted to the data type of its target column
Contains a data value that causes an SQL INSERT data-conversion or a nonunique-column-value error.

If the COMMITCOUNT parameter is specified with AUTOCOMMIT ON, and ERRORMODE CONTINUE processing is in effect, DATALOAD processing skips bad data records. Processing continues under the following circumstances:

An error identified by ARI0866E occurs.
An SQL INSERT error identified by SQLCODE -405, -424, -530, -802, or -803 occurs followed by message ARI0862E, and insert blocking is not in effect.

Insert blocking is not in effect under the following conditions:

Database Services Utility is running with single user mode.
Database Services Utility is running with multiple user mode but was preprocessed with the NOBLOCK option.
Insert blocking is suppressed by the database manager.

A bad data record is not skipped, and DATALOAD processing is terminated under the following conditions:

The first 256 records of data are bad.
Multiple DATALOAD commands are used preceding an INFILE subcommand when an insert error occurs, and the record or a portion of the record has already been used for a successful insert by any of the DATALOAD commands.
An error, other than an SQL INSERT error identified by SQLCODE -405, -424, -530, -802, or -803, occurs.

Tables in Nonrecoverable Storage Pools

A nonrecoverable storage pool is a pooled storage area for which there is no automatic recovery action to restore data to the condition it was in before a system failure or a failed operation. The message:
ARI8990I The table tablename is in a nonrecoverable storage pool.

is written before DATALOAD table insert processing begins if one of the tables you are loading resides in a nonrecoverable storage pool. This message indicates that changes made to this table by the DATALOAD command are not deleted by a ROLLBACK statement if an error occurs.

Tables in Nonrecoverable Storage Pools
A nonrecoverable storage pool is a pooled storage area for which there is no automatic recovery action to restore data to the condition it was in before a system failure or a failed operation. The message: ARI8990I The table `tablename` is in a nonrecoverable storage pool. is written before DATALOAD table insert processing begins if one of the tables you are loading resides in a nonrecoverable storage pool. This message indicates that changes made to this table by the DATALOAD command are not deleted by a ROLLBACK statement if an error occurs.

Restarting the Loading Process

The Database Services Utility is designed to run despite minor errors. If, however, an error is serious enough to halt the utility, you must rerun your particular Database Services Utility command and reprocess all your files; you cannot simply restart the Database Services Utility from the point of failure.

The COMMITCOUNT parameter, introduced in the preceding section, saves processed data at intervals that you specify. This saves you processing time because, although you must rerun jobs from the beginning, you do not have to reprocess data that has already been committed to the database. The part of the DATALOAD TABLE command that lets you bypass records is the RESTARTCOUNT parameter.

In general, you want to skip any records that have been successfully processed and also any bad input records. To run a job that has errors, proceed as follows:

Prepare the DB2 Server for VSE job and commands for the Database Services Utility and submit the job to run; or prepare the necessary DB2 Server for VM files and invoke the Database Services Utility.

When the job cancels (in VSE) or halts (in VM) with an error, determine (from the messages sent) the number of records, if any, committed and the number of records processed up to the start of the error condition.

Note:

If you were running the Database Services Utility without SET AUTOCOMMIT protection, or with too high a COMMITCOUNT value, you could rerun the job with SET AUTOCOMMIT ON and an appropriately low COMMITCOUNT value to save the successfully processed records. If you choose this course of action, return to step 1.

Before rerunning the job, add the following parameter to the DATALOAD TABLE command:
```
RESTARTCOUNT(rcount)
```
where rcount specifies the number of input records to be skipped. In general, start processing from the last COMMIT action.
Remove the error condition, if possible.
Rerun the job.

If a DATALOAD job is canceled or halted repeatedly by errors, or if bad records are causing the errors, consider using the following:

Specify as the RESTARTCOUNT value the input record count of the last ARI0800I message. A sample ARI0800I message looks like this:
```
ARI0800I  ...Begin COMMIT. Input Record Count = 100
```
Specify the COMMITCOUNT value again to equal the number of records between the value found in the preceding step and the bad record.
Rerun DATALOAD up to the point of failure. This skips the previously committed records and commits the remainder up to the bad record.
Specify as a new RESTARTCOUNT value the input record count of the latest ARI0800I message plus n, where n is the number of bad records. (You can also specify the COMMITCOUNT parameter again to its original value.)
Rerun the job. DATALOAD processing begins at a point beyond the bad records and the previously committed work.

If you were running a DATALOAD job with the COMMITCOUNT set to 100, but the job was unsuccessful at record 151, you could run DATALOAD again with a new COMMITCOUNT value and restart after the number of records that were committed:

DATALOAD TABLE(SMITH.DEPARTMENT)
IF POS (50) = 'X'
INFILE(SOMEDEPT) COMMITCOUNT(50) RESTARTCOUNT(100)

Now you have committed all the records up to the bad record. To skip the bad record, change the RESTARTCOUNT value, and restore the COMMITCOUNT parameter to its original value:

DATALOAD TABLE(SMITH.DEPARTMENT)
IF POS (50) = 'X'
INFILE(SOMEDEPT) COMMITCOUNT(100) RESTARTCOUNT(151)

Alternative Method

You can specify that the Database Services Utility ignore certain error conditions and continue processing records:

Immediately before the DATALOAD command set in the (input) control file, include:
SET ERRORMODE CONTINUE

On the same line as INFILE, add:
COMMITCOUNT(ccount)

where ccount is the number of input records to be processed before a COMMIT action is taken.
Run the job.

Although this procedure skips bad records, it does not pinpoint them. After a job is finished, compare the loaded table with source input documents to locate missing table rows.
For more information on Database Services Utility's error handling, see Chapter 9, Error Handling and Debugging.

Alternative Method
You can specify that the Database Services Utility ignore certain error conditions and continue processing records: Immediately before the DATALOAD command set in the (input) control file, include: SET ERRORMODE CONTINUE On the same line as INFILE, add: COMMITCOUNT(`ccount`) where `ccount` is the number of input records to be processed before a COMMIT action is taken. Run the job. Although this procedure skips bad records, it does not pinpoint them. After a job is finished, compare the loaded table with source input documents to locate missing table rows. For more information on Database Services Utility's error handling, see Chapter 9, Error Handling and Debugging.

Statistics Collection

The database manager generates table statistics while loading the data and calculates index statistics while creating an index. This method of creating statistics avoids doing a dbspace scan and a separate scan of the index pages, which are done when you issue an UPDATE STATISTICS statement.

The database manager generates table statistics while the Database Services Utility DATALOAD, RELOAD TABLE, and RELOAD DBSPACE commands are loading data only if the SET UPDATE STATISTICS command is set to ON. Other rules that must be met if statistics are to be collected for DATALOAD processing are:

The DATALOAD command is loading data into only one table.
No indexes exist on the table. If indexes do exist, the Database Services Utility issues an UPDATE STATISTICS after the load is complete to generate index and table statistics.
The table being loaded currently contains no data; statistics are accumulated only for rows that are being loaded. If the table already contains data and statistics are generated when more rows are loaded, the statistics would not accurately describe the entire table. For example, to load 5000 rows into a 500000-row table, and have the table's statistics describe only the 5000 rows that were loaded, would not be accurate. For this reason, statistics are not generated when you specify the RESTARTCOUNT option. The use of RESTARTCOUNT implies that a DATALOAD with the COMMITCOUNT option had already loaded rows into the table, a failure occurred, and the DATALOAD is being restarted at the point of the most recent COMMIT. Clearly, there are already rows in the table.
The database manager determines that rows are already in the table when the ROWCOUNT column of the SYSCATALOG table is a positive number for the table you are loading. Generally, a positive number in the ROWCOUNT column indicates that the table contains rows, but if you delete all the data from the table without updating the statistics, ROWCOUNT still contains a positive number. You must update the statistics to set the ROWCOUNT to zero before loading data into that table.

If, for any of the above reasons, table statistics were not generated while data was being loaded, the Database Services Utility executes an SQL UPDATE STATISTICS statement for each table loaded after DATALOAD or RELOAD command processing successfully ends. Statistics are neither updated automatically nor is an UPDATE STATISTICS statement executed under either one of the following conditions:

A SET UPDATE STATISTICS OFF command was issued before the DATALOAD or RELOAD command.
A view name was specified instead of a table name.

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