5.7.4 Specification examples

This section presents specification examples of the column structure information file.

Organization of this subsection
(1) DAT format
(2) Fixed-size data format
(3) pdrorg-output binary format

(1) DAT format

(a) When the input data file contains unneeded data
Table definition:

CREATE TABLE T1 (COL1 INTEGER,COL2 DATE,COL3 CHAR(10));

Input data file and column structure information file:
[Figure]
(b) When a sequence generator is used

This example uses a sequence generator (USER01.SEQ_B01) to generate sequence numbers and then stores the sequence numbers in COL03.

Table definition:

CREATE TABLE T1 (COL1 INTEGER,
                COL2 CHAR(10),
                COL3 DECIMAL(10),
                COL4 INTEGER);

Input data file:

1,ABC,2500
2,DEF,30000
3,GHI,10
  :

Column structure information file:

COL1
COL2
COL4
*adddata*
COL3,sequence=(USER01.SEQ_B01)

(2) Fixed-size data format

Table definition:

CREATE TABLE T1 (COL1 INTEGER,COL2 DATE,COL3 CHAR(10));

Input data file and column structure information file:
[Figure]
Explanation
  1. Data consisting of 4 bytes of characters and 2 bytes of spaces can be specified on one line as shown in this example, instead of using two lines COL1,type=char(4) and *skipdata*,length=2.
  2. The skipdata statement is specified for left-justification purposes because the data for COL3 is characters.
  3. The length of one line equals the sum of the column lengths specified in the column structure information file. Therefore, the length of the input data is 9 bytes, although CHAR(10) is defined for the table.
  4. For a text file, the skipdata statement must be specified because the file contains a linefeed code. The length of one line (one record) in this fixed-size-data-format file is determined to be 26 bytes (25 bytes of data section + linefeed code).

(3) pdrorg-output binary format

This example loads table T1's unload data to table T2, where T1 has SGMLTEXT columns including BLOB parameter data and T2 has BLOB columns. The table definitions show only the row name and data type specification sections; the other sections are omitted.

(a) Order of column definitions do not match between T1 and T2
(b) T2 has more columns than T1
(c) T2 has more columns than T1
(d) Replacing column data

This example replaces LOB attribute data (C2) in T1's SGMLTEXT type column to T2's LOB column (C6) and T1's LOB column data (C3) to the LOB attribute (C5) of the SGMLTEXT type column in T2.

(e) Changing the data type of columns

This example loads the LOB attribute data (C2) from an SGMLTEXT type column to the BINARY attribute (C5) of an SGMLBIN type column and loads data (C3) from a LOB column to a BINARY type column (C6).

(f) When column definitions differ between T1 and T2

This example expands the definition length for the CHARACTER type from 10 to 21 and then loads data.

(g) Area required for data conversion

If data conversion results in BLOB data placed between the BINARY type data, BINARY parameter data of abstract data type, and BLOB parameter data of an abstract data type, you need to allocate memory to retain that BLOB data. If the utility is unable to allocate this memory, it terminates with a memory allocation error. Memory allocation is required in the following cases:

The figure below shows the data arrangement in an input data file in pdrorg-output binary format that is output from a table for which BINARY type columns, abstract data type columns with BLOB attribute, and BLOB type columns are defined.

Figure 5-27 Data arrangement in an input data file in the pdrorg-created binary format

[Figure]

Explanation
pdload allocates an input data area of at least M. The maximum size of M is determined from the target table definition (you can also specify a desired size in the maxreclen operand of the source statement).
If data is loaded in this arrangement, pdload processes the data sequentially and does not allocate any more area for input data.
If data conversion is specified in the column structure information file, pdload allocates an area of M + M2 + K. As the input data area, pdload allocates an area of M + M2 and to retain the BLOB data section preceding the data converted to the BINARY type, pdload allocates an area of K. You can specify the size of the K area using the bloblimit operand in the option statement. pdload allocates the area whose size is obtained from the target table definition or the option statement's bloblimit operand, whichever is smaller.