8.3.1 Examples
(1) Unloading a row-partitioned table in units of tables
- Example 1
- This example unloads a row-partitioned table (TABLE1) in units of tables. It uses the unload data as an input data file (binary format) for pdload. All data is integrated in a single data file.
- The example assumes that the following table and indexes have been defined:
CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT)
IN ((PDBUSER01) C1 > 10,(PDBUSER02))
- Index definition (partitioning key index):
CREATE INDEX INDEX1 ON TABLE1(C1)
IN ((PDBUSER03),(PDBUSER05))
- Index definition (non-partitioning key index):
CREATE INDEX INDEX2 ON TABLE1(C2,C1)
IN ((PDBUSER04),(PDBUSER06))
- Overview
![[Figure]](figure/zu830020.gif)
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080240.gif)
- Explanation of the command
- This example unloads the row-partitioned table (TABLE1) in units of tables.
- -k unld: Specification for unloading
- -W bin: Specification for using the unload data file as an input data file (binary format) for pdload
- -g: Specification for integrating all data in a single unload data file
- -t TABLE1: Name of the table being unloaded
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/usr/unload_file1 1 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/usr/unload_file1: Name of the unload data file
(2) Unloading and reloading a table while modifying the table partitioning conditions
- Example 2
- To modify a table's (TABLE1's) partitioning conditions, this example first unloads the table, modifies the table partitioning conditions, then reloads the table.
- The example assumes that the following table and index have been defined:
CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT) IN PDBUSER01
- Index definition (partitioning key index):
CREATE INDEX INDEX1 ON TABLE1(C1) IN PDBUSER03
- Overview
![[Figure]](figure/zu830050.gif)
(a) Unloading the table before modifying the table partitioning conditions
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080370.gif)
- Explanation of the command
- The example unloads the table (TABLE1).
- -k unld: Specification for unloading
- -t TABLE1: Name of the table being unloaded
- -g: Specification for modifying the table partitioning conditions
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
(b) Modifying the table's (TABLE1's) partitioning conditions
First, drop TABLE1 with DROP TABLE. Then, redefine the table's (TABLE1's) partitioning conditions and modify the index definition, shown as follows:
CREATE TABLE TABLE1(C1 INT NOT NULL,
C2 CHAR(8),
C3 INT)
IN ((PDBUSER01) C1 > 10,(PDBUSER02))
CREATE INDEX INDEX1 ON TABLE1(C1)
IN ((PDBUSER03),(PDBUSER04)) |
(c) Reloading to the table after modifying the partitioning conditions
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080380.gif)
- Explanation of the command
- The example reloads to the table (TABLE1).
- -k reld: Specification for reloading
- -t TABLE1: Name of the table being reloaded
- -g: Specification for modifying the table partitioning conditions
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
idxwork bes1 /usr/idxwork 2
idxwork bes2 /usr/idxwork 2
sort bes1 /usr/sortwork 3
sort bes2 /usr/sortwork 3 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the directory for index information files to which index information is to be output:
bes1, bes2: Names of the servers used to create index information files
/usr/idxwork: Name of the directory for index information files
- Specifies the work directory for sorting:
bes1, bes2: Names of the servers used to create the sort work file
/usr/sortwork: Name of the directory in which the sort work file is created
(3) Unloading and reloading to migrate table data into another table
- Example 3
- This example migrates data from a table (TABLE1) to another table.
- The example assumes that the following tables and indexes have been defined:
- Source table and index definition:
CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT) IN PDBUSER01
CREATE INDEX INDEX1 ON TABLE1(C1) IN PDBUSER03
- Target table and index definition:
CREATE TABLE TABLE11(C1 INT NOT NULL,C2 CHAR(8),C3 INT) IN PDBUSER11
CREATE INDEX INDEX11 ON TABLE11(C1) IN PDBUSER13
- Overview
![[Figure]](figure/zu830060.gif)
(a) Unloading the source table
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080390.gif)
- Explanation of the command
- The example unloads the table (TABLE1).
- -k unld: Specification for unloading
- -t TABLE1: Name of the table being unloaded
- -g: Specification for migrating data into another table
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
(b) Reloading to the destination table (reloading to another table)
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080400.gif)
- Explanation of the command
- The example reloads to the table (TABLE11).
- -k reld: Specification for reloading
- -t TABLE11: Name of the table being reloaded
- -g: Specification for migrating data into another table
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
idxwork bes2 /usr/idxwork 2
sort bes2 /usr/sortwork 3
tblname TABLE1 4 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the directory for index information files to which index information is to be output:
bes2: Name of the server used to create index information files
/usr/idxwork: Name of the directory for index information files
- Specifies the work directory for sorting:
bes2: Name of the server used to create the sort work file
/usr/sortwork: Name of the directory in which the sort work file is created
- Specifies reloading the table data into another table:
TABLE1: Name of the source table
(4) Unloading and reloading while modifying the partitioning conditions of a table with an abstract data type
- Example 4
- To modify the partitioning conditions of a table (TABLE1) with an abstract data type (SGMLTEXT), this example first unloads the table, modifies the table partitioning conditions, then reloads the table. The abstract data type (SGMLTEXT) is provided by the HiRDB Text Search Plug-in.
CREATE TABLE TABLE1(C1 INT,C2 SGMLTEXT ALLOCATE (SGMLTEXT IN LOBUSER01)
PLUGIN'<DTD>sgml.dtd</DTD>' ) IN PDUSER01
- Overview
![[Figure]](figure/zu830030.gif)
(a) Unloading the table before modifying the table partitioning conditions
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080281.gif)
- Explanation of the command
- The example unloads the table (TABLE1).
- -k unld: Specification for unloading
- -t TABLE1: Name of the table being unloaded
- -j: Specification for unloading a table with an abstract data type with LOB attribute
- -g: Specification for modifying the table partitioning conditions
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
unld_func type=SGMLTEXT,func=unsgmltext(sgmltext) 2 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the constructor parameter reverse creation function:
SGMLTEXT: Name of the abstract data type (column C2)
unsgmltext: Name of the constructor parameter reverse creation function (for the actual name, see the applicable plug-in manual)
sgmltext: Data type of the argument of the constructor parameter reverse creation function
(b) Modifying the table's (TABLE1's) partitioning conditions
First, drop TABLE1 with DROP TABLE. Then, redefine the table's (TABLE1's) partitioning conditions as follows:
CREATE TABLE TABLE1(C1 INT NOT NULL,
C2 SGMLTEXT ALLOCATE
(SGMLTEXT IN ((LOBUSER01),(LOBUSER02)))
PLUGIN'<DTD>sgml.dtd</DTD>' )
IN ((PDUSER01) C1<2000,(PDBUSER02)) |
(c) Reloading to the table after modifying the partitioning conditions
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080282.gif)
- Explanation of the command
- The example reloads to the table (TABLE1).
- -k reld: Specification for reloading
- -t TABLE1: Name of the table being reloaded
- -j: Specification for reloading to a table with an abstract data type with LOB attribute
- -g: Specification for modifying the table partitioning conditions
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
reld_func type=SGMLTEXT,func=sgmltext(blob) 2 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the constructor function:
SGMLTEXT: Name of the abstract data type (column C2)
sgmltext(blob): Name of the constructor function (parentheses enclose the data type of the constructor function's argument)
(5) Unloading and reloading to migrate table data from a table with an abstract data type to another table
- Example 5
- This example migrates data from a table (TABLE1) with abstract data type (SGMLTEXT) to another table. The abstract data type (SGMLTEXT) is provided by the HiRDB Text Search Plug-in.
- Source table and index definition:
CREATE TABLE TABLE1(C1 INT,
C2 SGMLTEXT ALLOCATE
(SGMLTEXT IN LOBUSER01)
PLUGIN'<DTD>sgml.dtd</DTD>'
C3 SGMLTEXT ALLOCATE
(SGMLTEXT IN LOBUSER02)
PLUGIN'<DTD>sgml.dtd</DTD>'
) IN PDUSER01
- Target table and index definition:
CREATE TABLE TABLE2(C1 INT,
C2 SGMLTEXT ALLOCATE
(SGMLTEXT IN LOBUSER03)
PLUGIN'<DTD>sgml.dtd</DTD>'
C3 SGMLTEXT ALLOCATE
(SGMLTEXT IN LOBUSER04)
PLUGIN'<DTD>sgml.dtd</DTD>'
) IN PDUSER02
- Overview
![[Figure]](figure/zu830040.gif)
(a) Unloading the source table
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080283.gif)
- Explanation of the command
- The example unloads the table (TABLE1).
- -k unld: Specification for unloading
- -t TABLE1: Name of the table being unloaded
- -j: Specification for unloading a table with an abstract data type with LOB attribute
- -g: Specification for migrating data into another table
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
unld_func type=SGMLTEXT,func=unsgmltext(sgmltext) 2 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the constructor parameter reverse creation function:
SGMLTEXT: Name of the abstract data type (column C2)
unsgmltext: Name of the constructor parameter reverse creation function (for the actual name, see the applicable plug-in manual)
sgmltext: Data type of the argument of the constructor parameter reverse creation function
(b) Reloading to the target table (another table)
- Relationship between input/output files and RDAREAs
![[Figure]](figure/zx080284.gif)
- Explanation of the command
- The example reloads to the table (TABLE2).
- -k reld: Specification for reloading
- -t TABLE2: Name of the table being reloaded
- -j: Specification for reloading to a table with an abstract data type with LOB attribute
- -g: Specification for migrating data to another table
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
reld_func type=SGMLTEXT,func=sgmltext(blob) 2
tblname TABLE1 3 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies the constructor function:
SGMLTEXT: Name of the abstract data type (column C2)
sgmltext(blob): Name of the constructor function (parentheses enclose the data type of the constructor function's argument)
- Specifies reloading table data to another table:
TABLE1: Name of the source table
(6) Creating an input data file for pdload in fixed-size data format
- Example 6
- This example unloads data from the table PRODUCT_TABLE as an input data file for pdload in fixed-size data format.
CREATE TABLE PRODUCT_TABLE
(PRODUCT_NUMBER SMALLINT NOT NULL,PRODUCT_NAME NVARCHAR(12),QUANTITY INTEGER)
IN ((RDAREA1)PRODUCT_NUMBER<=10000,(RDAREA2)PRODUCT_NUMBER<=20000,(RDAREA3));
- pdrorg command
pdrorg -k unld -t PRODUCT_TABLE -W fixtext,@,cr control_file |
- Explanation
- -k unld: Specification for unloading
- -t PRODUCT_TABLE: Name of the table being unloaded
- -W fixtext,@,cr: Specification for unloading the data as an input data file for pdload in fixed-size data format (@ is the padding character, and use of 1-byte linefeed codes is specified)
- control_file: Name of the control information file
- Contents of the control information file (control_file)
unload bes1:/hd001/unload_file 1
fixtext_option enclose=" format=integer,type1 format=smallint,type2 2 |
- Explanation:
- Specifies the unload data file:
bes1: Name of the server containing the unload data file
/hd001/unload_file: Name of the unload data file
- Specifies how to edit the output data:
enclose=": Encloses in the enclosing character (") the data for PRODUCT_NAME in a column whose data type is NVARCHAR.
format=integer,type1: Outputs in output format type1 the data for QUANTITY in a column whose data type is INTEGER.
format=smallint,type2: Outputs in output format type 2 the data for PRODUCT_NUMBER in a column whose data type is SMALLINT.
- Output data
PRODUCT_NUMBER PRODUCT_NAME QUANTITY
(6 bytes) (14 bytes) (11 bytes) (1 byte)
###100 "BOOTS"@@@@@@@ @@@@@@@@@@@ linefeed-character
@@@@@@ ""@@@@@@@@@@@@ #0000000100 linefeed-character
-12345 @@@@@@@@@@@@@@ -0000000300 linefeed-character |
- Note
- The hash mark (#) represents a space character, and one row consists of 32 bytes.