5.11 Rules and notes

Organization of this section
(1) Rules
(2) Notes
(3) Whether or not options and control statements can be specified when pdload functions are used
(4) Using files with a BOM

(1) Rules

(a) Execution of the utility
(b) Whether or not the utility can be executed

Whether or nor pdload can be executed depends on the open attribute of the RDAREAs containing the tables, indexes, and LOB columns subject to data loading, as well as the status of the RDAREAs. For details, see Appendix C. RDAREA Status During Command Execution.

(c) Maximum number of concurrent executions of the utility

The maximum number of times pdload can be executed concurrently depends on the value of the pd_utl_exec_mode operand.

pd_utl_exec_mode=0 specified:
A maximum of 32 concurrent executions of pdload are permitted.
pd_utl_exec_mode=1 specified:
The maximum number of concurrent executions of pdload is the value of the pd_max_users operand.
(d) Using tables and indexes during utility processing
(e) Relationship between CREATE TABLE or CREATE INDEX and PCTFREE
(f) Synchronization points during data loading

Normally, synchronization point dumps are not collected during data loading. If other UAPs are executing while data loading is underway and a system failure occurs, the time required for restart increases. Therefore, you should not execute any UAPs during data loading.

Data loading with the synchronization point specification enables you to collect synchronization point dumps at intervals specified as the number of lines. In the event of abnormal termination, this method requires less start time than the normal data loading method that does not collect synchronization point dumps.

(g) Checking national character string data

Values stored in national character string data are not subject to checking as to whether or not the data is in multi-byte codes.

(h) Entering a file created in VOS3 using EasyMT

To input a file created by VOS3 using EasyMT, you must convert it to the character codes specified in the pdsetup command before using pdload to load the data.

(i) Using multi-volume MT

To use multi-volume magnetic tape, you need to install MTguide at the server machine, because it is used for volume switching.

(j) Facility for conversion to a DECIMAL signed normalized number

The facility for conversion to a DECIMAL signed normalized number handles the sign part of DECIMAL type, shown as follows. For details about the facility for conversion to a DECIMAL signed normalized number, see the HiRDB Version 8 System Operation Guide.

Thus, if the 0 data is normalized, -0 is converted to +0.

For an input data file in the DAT format that is subject to conversion of character string data, the facility stores the normalized values (sign part is X'C' for positive values and 0 data and X'D' for negative values) regardless of the system definitions.

When the facility for conversion to a decimal signed normalized number is used, the DECIMAL columns are output in the error data dump-image listing that is output to the error information file and in the input data that is output to the error data file as normalized values up to the column where an error was detected by pdload.

(k) Loading data to a table containing an abstract data type

For a table containing an abstract data type provided by a plug-in, you can call a constructor function to generate the values for the corresponding columns to load the data. However, this data loading is not possible if the constructor function is not created in the shared library.

For a table containing a user-defined abstract data type, data loading is not possible because the system cannot generate values for the corresponding columns.

(l) Loading data to a rebalancing table

If you have added an RDAREA to a rebalancing table, you cannot load data to the added RDAREA (during data loading in units of RDAREAs) unless you execute pdrbal on the rebalancing table (return code = 0).

For a table partitioned by flexible hash partitioning, the utility stores data and ignores the hash group; therefore, the data rearranged by pdrbal does not link to the hash key values.

(m) Data loading on external tables

You cannot execute data loading on external tables.

(n) Data loading on falsification prevented tables

You should note the following about executing data loading on falsification prevented tables:

(o) Data loading on a table with a trigger defined

Execution of pdload does not activate a trigger. Before executing pdload, check the contents of the table's trigger definition.

(2) Notes

(a) Result of utility execution

You can check the result of pdload with pddbst (or by running the UAP or using pdrorg to perform unload processing). When pdload processing is finished, a return code is set. Table 5-34 shows the pdload return codes.

Table 5-34 pdload return codes

Return codeDescriptionAction
0Normal termination.
All data in the input data file was loaded.
None
Normal termination.
All data in the input data file was output to the divided-input data files.
Use the divided-input data files to perform data loading in units of RDAREAs.
4Input data error.
Data loading was skipped because some data in the input data file was invalid.
See the error information file, correct the erroneous data in the input data file, and then re-execute data loading.
Input data error.
Normal data in the input data file was output to the divided-input data files, but erroneous data was not (when the -e option was specified, data was output to the divided-input data files until erroneous data was detected).
See the error information file, correct the erroneous data in the input data file, and then re-create the divided-input data files.
8Input data error.
When the dataerr operand was specified in the option statement, data loading was rolled back due to an error in the input data file.
Correct the erroneous input data and then re-execute data loading.
Key duplication error.
A key duplication error was detected during
batch creation of index.
Restore the database from its backup, correct the erroneous input data, then re-execute data loading.
Abnormal termination.
Data loading was cancelled due to an error.
Eliminate the cause of the error and then re-execute data loading.
Abnormal termination.
Data output from input data file to divided-input data files failed.
See the error message, eliminate the cause of the error, and then re-create the divided-input data files.
(b) Cancelling processing during utility execution

To cancel processing during the execution of pdload, use the pdcancel command. To terminate pdload forcibly because of a no-response error (such as when a routine job does not finish data loading within the normal amount of time), redirect the display result of the pdls command (with -d rpc -a specified) to a file and then execute the pdcancel -d command.

In this case, if pdload was executing in the creation mode (-d option specified), all the data stored in the table is deleted. If pdload was executing in the addition mode (-d option omitted), processing is rolled back.

If the facility for predicting reorganization time is used and pdload is terminated forcibly by a signal interrupt such as the kill command, the database management table cannot be updated. To terminate pdload while using the facility for predicting reorganization time, make sure that you use the pdcancel command.

(c) Order in which LOB column structure base tables are created when a LOB input file is set up on an EasyMT

When preparing a LOB input file for EasyMT, you need to take into consideration the order in which the columns and rows of the LOB column structure base table are loaded. If you prepare this file randomly without regard to the loading order, a large amount of time may be required for search processing on the LOB input file.

(d) LOB column structure base tables and resources such as the RDAREA and the buffer for LOB data

To load data to a table with LOB columns, you need to prepare all the required resources, such as the RDAREAs and buffers of the LOB column structure base table and LOB data, even if you are loading data only to the LOB column structure base table.

(e) File media that can be used during execution of a utility

The table below shows the file media that are supported during execution of pdload. When a regular file is used, the operating system parameters (kernel parameters) maxfiles, nfile, and nflocks are used during file open processing.

FileRegular FileFixed-length blocked tapeVariable-length blocked tape
Input data fileYY*Y
Control information fileY[Figure][Figure]
Column structure information fileY[Figure][Figure]
Null value/function information fileY[Figure][Figure]
Error information fileY[Figure][Figure]
Error data fileY[Figure][Figure]
LOB input fileY[Figure]Y
LOB middle fileY[Figure][Figure]
Index information fileY[Figure][Figure]
Work file for sortingY[Figure][Figure]
Process results fileY[Figure][Figure]

Y: Can be used

[Figure]: Cannot be used

* Cannot be used for binary format or fixed-size data format.

(f) Executing a utility on an extraction database that is subject to data linkage

You should note the following about executing pdload on a database to be extracted that is subject to data linkage:

(g) Using a large file

A large file system enables you to store a file whose size exceeds 2 gigabytes. Table 5-35 shows for each file type whether or not pdload supports a large file. Note that the maximum size of file the process can create depends on OS settings. Set the maximum value of system resources for the HiRDB administrator and root user to a value that is greater than the size of file to be created or to unlimited. AIX 5L requires special attention because its default file size is 1 gigabyte. You can use the limit or ulimit OS command to check the limit values for system resources. When you change the file size limitations in AIX 5L, you must also correct the /etc/security/limit file. For details, see the applicable OS and shell documentation. Because HiRDB is an INIT startup process, the OS must be restarted before the changes made by the user can take effect.

Table 5-35 Large file support by pdload

File typeSupport of large file
Input data fileY
Error information fileY
Error data fileY
LOB input fileY
LOB middle fileY
Index information fileY
Work file for sortingY
MT attribute definition fileN
Process results fileY

Y: Supported

N: Not supported

(h) Loading data using a DAT-format input file containing a comma (,) at the end of each row

When you load data using a DAT-format input file that contains a comma (,) at the end of each row, the data loading may fail due to a column count mismatch. In this case, specify the names of all table columns in the column structure information file and specify the skipdata statement at the end. There is no need to revise the input data.

If coding of the column structure information file is not feasible because of a large number of table columns involved, you can prepare the file with the following method:

  1. Use the SQL Executer to output table column names to a file.
    When directing the output of column names to a file, you need to output the column names in the order in which the columns are defined. Therefore, specify the SQL statement as follows:

    PUTFILE TO filename SELECT COLUMN_NAME FROM MASTER.SQL_COLUMNS
    WHERE TABLE_NAME ='table-name' ORDER BY COLUMN_ID

  2. Append the skipdata statement to the last row for the name of the file to which the results of editing in Step 1 are output.
(i) Loading data to a table with a plug-in index defined
(j) Using the differential index function of the HiRDB Text Search Plug-in

When you use the differential index function of the HiRDB Text Search Plug-in, pdload updates the following indexes according to the specification of PDPLUGINNSUB in the client environmental definition:

Presence of existing dataPDPLUGINNSUB specified
YNNot set
No existing data (data loading in creation mode)MMM
Existing data present (data loading in addition mode)SMS

M: Updates the MASTER index

S: Updates the differential index

(k) Loading data to a table containing a unique key index or primary key index

When loading data to a table containing a unique key index or primary key index, note the following:

(l) List created on the basis of a table subject to data loading

If you have created a list on the basis of a table subject to data loading, and execute a search process on the list after data loading, the following events may occur:

In this case, you need to re-create the list before executing the search.

(m) Locked resources required per server during execution of pdload

While executing pdload, each server requires the following amount of locked resources:

Legend:
a: Number of table storage RDAREAs
b: Number of index storage RDAREAs
c: Number of LOB column storage RDAREAs
d: Number of indexes
e: Number of segments used by table storage RDAREAs*
f: Number of segments used by index storage RDAREAs*
g: [Figure] Number of segments used by LOB column storage RDAREAs/64000 [Figure]*
x: 6 for HiRDB in the 32-bit mode; 4 for HiRDB in the 64-bit mode
A: MAX (number of table columns, number of table indexes, number of table storage RDAREAs)
* When you have specified the -d option and the number of segments used by the table storage RDAREAs, index storage RDAREAs, or LOB column storage RDAREAs is greater, you should use the number of segments actually in use; if the number of segments used by the data to be stored is greater, use the number of segments scheduled to be used for storing data.
(n) Messages displayed in the command execution window

pdload outputs progress messages to the standard output during processing. In the event of an error, error messages are output to standard error. If pdload is executed in an environment in which output to standard output or standard error is suppressed, pdload may stop responding due to a message output wait, or it may output the KFPL20003-E message to the message log file and terminate abnormally. For this reason, you should not execute pdload in an environment in which messages cannot be output to the standard output or standard error output. Note that the sequence and number of messages output to the standard output and to the standard error output may not match the sequence and number in the message log file and syslogfile. To obtain the accurate messages, view the message log file or syslogfile.

(o) Data loading to a shared table

To perform data loading on a shared table, the system places the RDAREAs containing the shared table and shared indexes defined for the target table in the EX lock mode. If the corresponding RDAREAs contain other tables and indexes, these tables and indexes cannot be referenced or updated. For details about the lock mode used for data loading on shared tables, see Appendix B.2 Lock mode for utilities.

(p) Data loading on a table for which referential constraints or check constraints have been defined

When data is loaded into a table for which referential constraints or check constraints have been defined, pdload does not check for data integrity. For this reason, you should use pdconstck to check data integrity during data loading. For details about how to check data integrity, see the HiRDB Version 8 Installation and Design Guide.

(q) Relationship with the facility for predicting reorganization time

When data loading is performed in the creation mode (-d specified), the history of data deletions from tables and indexes is applied to the results of the facility for predicting reorganization time.

If pdload has terminated abnormally, executing the pddbst condition analysis result accumulation facility results in an invalid prediction result* because the reorganization timing cannot be predicted in the data loading completed status. Therefore, if pdload terminates abnormally, re-execute pdload to terminate it normally, then execute the pddbst condition analysis result accumulation facility.

* For example, if rollback occurs due to abnormal termination of data loading, empty database information may be accumulated and used for prediction.

(3) Whether or not options and control statements can be specified when pdload functions are used

Whether or not options and control statements can be specified depends on the pdload functions being used. The applicable functions are as follows:

Tables 5-36 and 5-37 show whether or not options and control statements can be specified when these functions are used. For details about registering data in audit trail tables, see the HiRDB Version 8 System Operation Guide.

Table 5-36 Whether or not options can be specified when pdload functions are used

OptionWhether or not specifiable
Registering data in audit trail tableCreating divided-input data files
-dON
{-a|-b}M (specify -b)O (specify -a if the input data file is in the fixed-size data format)
-iON
-lON
-WMN
-kNN
{-c|-v}O (specify -v)O (specify -c if the input data file is in the fixed-size data format)
-nON
-uOO
-xON
-fNN
-sNO
-eNO
-rNN
-zO (assumed even when omitted)O
-yON
-oON
-mOO
-XOO
-qNN
Legend:
M: Mandatory
O: Optional
N: Cannot be specified

Table 5-37 Whether or not control statements can be specified when pdload functions are used

Control statementOperandWhether or not specifiable
Registering data in audit trail tableCreating divided-input data files
mtguide[Figure]NN
emtdef[Figure]NN
source[Figure]M (specify (uoc))M (specify input data file)
index[Figure]ON
idxwork[Figure]ON
sort[Figure]ON
lobdata[Figure]NN
lobcolumn[Figure]NN
lobmid[Figure]NN
srcuoc[Figure]MN
array[Figure]OO
extdat[Figure]NO
src_work[Figure]NM
optionspacelvlOO
tblfreeON
idxfreeON
jobNN
cutdtmsgNN
nowaitON
bloblimitNN
exectimeOO
null_stringNN
divermsgNN
dataerrNN
lengoverNO
report[Figure]ON
Legend:
M: Mandatory
O: Optional
N: Cannot be specified
[Figure]: Not applicable

(4) Using files with a BOM

If you selected utf-8 as the character encoding in the pdsetup command, you may be able to use a file with a BOM as the input file for pdload. Table 5-38 shows whether or not files with a BOM can be used with pdload. Note that even when a file with a BOM is used as the input file for pdload, the BOM is skipped. No BOM is included in the file that is output by pdload.

Table 5-38 Whether or not files with a BOM can be used in pdload (applicable to UTF-8)

Option or control statementInput fileUse of file with a BOM
-cColumn structure information fileY
-vNull value information fileY
[Figure]Control information fileY
emtdefMT attribute definition fileN
sourceInput data fileDATY
Extended DATY
BinaryN
Fixed-sizeN
Created with pdrorg -WN
EasyMT information fileN
indexIndex information fileN
lobdataLOB input fileN
lobcolumnLOB column input fileN
lobmidLOB middle fileN
Legend:
Y: Can be used
N: Cannot be used
[Figure]: Not applicable