5.1.5 Data loading using the automatic numbering facility
Using the automatic numbering facility during data loading enables you to set sequence numbers generated by the sequence generator in a table column.
The figure below shows data loading when the automatic numbering facility is used.
Figure 5-3 Data loading using the automatic numbering facility
![[Figure]](figure/zu050880.gif)
- Explanation
- Data is read from the input data file into the input buffer (the sequence number acquisition method in this example is the buffer unit acquisition method).
- The sequence generator is called to obtain as many sequence numbers as the number of rows that have been read.
- The sequence generator returns the obtained sequence numbers and sets the next value to be passed as the current value.
- pdload reads data from the input buffer and assembles the rows of data that include the obtained row sequence numbers.
- Assembled rows of data that are loaded.
When you use the automatic numbering facility for data loading, the input data file must be in one of the following formats:
- DAT format
- Extended DAT format
- Fixed-size data format
- Binary format
- pdrorg-output binary format (unload file for pdload)
- Organization of this subsection
- (1) Setting sequence numbers
- (2) Acquiring sequence numbers
- (3) Data types for which sequence numbers can be set
- (4) Error handling procedures
- (5) Notes
(1) Setting sequence numbers
When you perform data loading using the automatic numbering facility, you can choose the method for handling the column in the input data file in which the sequence numbers are to be set. There are three methods for storing sequence numbers in a column.
- Complete column-data replacement
- This method sets only sequence numbers as the column data in the sequence number column in the input data file. Whatever data is already in the column is entirely replaced with the sequence numbers.
- Partial column-data replacement
- This method replaces with sequence numbers only the data in the sequence number column in the input data file that satisfies a specified replacement condition.
- Column data addition
- If the input data file does not contain a column for storing sequence numbers, this method adds a column for the sequence numbers, which become input data. Note that this method cannot be specified if the input data file is in binary format.
You specify the sequence number storage method in the column structure information file or the null value/function information file.
(2) Acquiring sequence numbers
When you use the automatic numbering facility during data loading, you can select the timing for acquisition of the sequence numbers from the sequence generator as well as the acquisition unit. There are three methods for acquiring sequence numbers.
- Number batch acquisition method
- This method acquires all sequence numbers generated by the sequence generator in the batch mode after data loading has been completed.
- Specification unit acquisition method
- This method acquires sequence numbers for each specified unit during data loading.
- Buffer unit acquisition method
- If the input data file is in fixed-size data format or binary format, or is a pdload unload file, this method acquires during data loading as many sequence numbers as can be loaded into the input buffer. If the input data file is in DAT or extended DAT format, this method then assigns a sequence number to each row during data loading.
(3) Data types for which sequence numbers can be set
You can set sequence numbers in columns of the following data types:
- INTEGER
- SMALLINT
- DECIMAL
- FLOAT
- SMALLFLT
- CHAR
- VARCHAR
- MCHAR
- MVARCHAR
If the sequence generator does not handle any of these data types, pdload performs data conversion during data loading. If the data obtained after conversion cannot be set in the column due to an invalid data type, data loading results in an error.
(4) Error handling procedures
(a) In the event of a logical error
If a logical error occurs in the input data, the sequence number assigned before the error occurred becomes an absent number.
(b) In the event of a data conversion error
If a data conversion error occurs for a data type shown in 5.1.5(3) Data types for which sequence numbers can be set, pdload terminates with an error regardless of whether the -e option was specified. In such a case, whether the data loaded so far is to be committed or rolled back is determined by the dataerr operand specified in the option statement. If rollback is specified, the sequence numbers used during data loading will become absent numbers.
(5) Notes
The following notes apply to using the automatic numbering facility during data loading.
- If multiple columns in a table are to store sequence numbers and a different sequence generator is used for each such column, the sequence numbers generated by each sequence generator are stored. If the same sequence generator is specified for all the columns, the same row sequence numbers will be set in all the columns.
- When the utility uses a UOC to load data from an input data file, it passes to the UOC the column data before sequence numbers were replaced or added. pdload replaces or adds the row sequence number after it receives the data edited by the UOC.
- Data loading using the automatic numbering facility is not supported for repetition columns (if attempted, an error results). You must use one of the following methods if you wish to set sequence numbers in repetition columns:
- Include sequence numbers in the input data by using a program such as HiRDB SQL Executer or a UAP, and then execute the INSERT statement.
- Load data without using the automatic numbering facility and then execute the UPDATE statement on the repetition columns in which sequence numbers are to be set by using a program such as HiRDB SQL Executer or a UAP.
- Data loading using the automatic numbering facility locks the sequence generator. When the number batch acquisition method is used to acquire sequence numbers, other utilities and UAPs might be placed in lock-release wait status because the sequence generator is locked until data loading is completed.
- If the input data is in DAT format, extended DAT format, or fixed-size data format and a logical error occurs after sequence numbers have been assigned, the input data values are output to the error data file.
If the input data is in binary format, the sequence numbers are output to the error data file.