Type of source table | RDAREA definition in the target system | Can be defined? | pdexp processing after error# | ||
---|---|---|---|---|---|
Table storage RDAREA | RDAREA storing index defined for the table | Table | Index | ||
Shared table | Shared RDAREA | Shared RDAREA | Y | Y | Not applicable. |
Shared RDAREA | Not a shared RDAREA | Y | E | Cancels processing and ignores the table definition. | |
Not a shared RDAREA | Shared RDAREA | E | N | Cancels processing. | |
Not a shared RDAREA | Not a shared RDAREA | E | N | ||
Non-shared table | Shared RDAREA | Shared RDAREA | E | N | |
Shared RDAREA | Not a shared RDAREA | E | N | ||
Not a shared RDAREA | Shared RDAREA | Y | E | Cancels processing and ignores the table definition. | |
Not a shared RDAREA | Not a shared RDAREA | Y | Y | Not applicable. |
Generated definition SQL statement | Linefeed condition |
---|---|
CREATE TABLE |
|
CREATE INDEX |
|
COMMENT | At every 80th byte, in the case of a row that exceeds 80 bytes |
CREATE VIEW |
|
CREATE PROCEDURE |
|
CREATE TRIGGER |
|
Referencing action during table definition | -w option | Handling of SQL object that uses referenced table | Remarks |
---|---|---|---|
CASCADE | Specified | To maintain data integrity between the referencing table and referenced table, a trigger is created internally. Because the SQL object is created at that time, the function, procedure, and triggered SQL object that use the referenced table are ignored. | If the function, procedure, and triggered SQL object that use the referenced table already exist, the SQL object is disabled. Therefore, you must re-create the SQL object after importing the referencing table. |
Omitted | The function, procedure, and triggered SQL object that use the referenced table are ignored. CREATE TABLE results in an SQL error because data integrity cannot be maintained between the referencing table and referenced table unless that SQL object is re-created. | -- | |
RESTRICT | Specified | If the function, procedure, and triggered SQL object that use the referenced table are enabled, data integrity is lost because rows are updated or deleted in the referenced table. Therefore, the SQL object is disabled. | If the function, procedure, and triggered SQL object that use the referenced table already exist, the SQL object is disabled. Therefore, you must re-create the SQL object after importing the referencing table. |
Omitted | The function, procedure, and triggered SQL object that use the referenced table are ignored. CREATE TABLE results in an SQL error because data integrity cannot be maintained between the referencing table and referenced table unless that SQL object is re-created. | -- |
When pdexp imports a table, it creates and then executes a definition SQL statement. The length of the created SQL statement might exceed the maximum length of 2,000,000 bytes for the reason described below.
Table 9-1 Default values and quotation marks for the syntax and identifiers that are specified in definition SQL statements
No. | Syntax or identifier | Default value or quotation marks |
---|---|---|
1 | Column restriction definition | PARTIAL |
2 | PCTFREE (percentage of unused space) | 30 |
3 | PCTFREE (percentage of free pages in segments) | 10 |
4 | Delete operation | RESTRICT |
5 | Update operation | RESTRICT |
6 | Restriction name definition | Timestamp during restriction definition |
7 | Identifier | Quotation marks are added to distinguish uppercase letters from lowercase letters |
An example of modifying a definition SQL statement whose length exceeded 2,000,000 bytes is shown below. This is an example of CREATE TABLE.
CREATE TABLE "root"."T1"
("C1" INTEGER NOT NULL,
"C2" CHAR(100),
:
:
"C98" VARCHAR(100)
) IN (("RDUSER01") "C100" < 1500
("RDUSER02"))
PCTFREE=(30,10)
FOREIGN KEY ("C1","C2") REFERENCES "root"."T2"
ON DELETE RESTRICT ON UPDATE RESTRICT CONSTRAINT CNST01
; ................................................1
ALTER TABLE "root"."T1"
ADD "C99" INTEGER
; ................................................2
ALTER TABLE "root"."T1"
ADD "C100" INTEGER NOT NULL
; ................................................2
CREATE TABLE "root"."T1"
("C1" INTEGER NOT NULL,
"C2" CHAR(100),
:
:
"C98" VARCHAR(100),
"C99" INTEGER, ...............Added in step 2
"C100" INTEGER NOT NULL ......Added in step 2
) IN (("RDUSER01") "C100" < 1500
("RDUSER02"))
PCTFREE=(30,10)
FOREIGN KEY ("C1","C2") REFERENCES "root"."T2"
CONSTRAINT CNST01 .........Partially deleted in step 3
;
If you selected utf-8 as the character encoding in the pdntenv command, you may be able to use a file with a BOM as the input file for pdexp. The table below shows whether files with a BOM can be used with pdexp. Note that even when a file with a BOM is used as the input file for pdexp, the BOM is skipped. No BOM is included in the file that is output by pdexp.
Table 9-2 Whether or not files with a BOM can be used in pdexp (applicable to UTF-8)
Option | Input file | Use of file with a BOM |
---|---|---|
-l | Export file | N |
-f | Control statements file | Y |
You can also import and export definitions that contain simple comments and create definition SQL statements. However, some measures are needed for the reason described below.
A linefeed code is inserted after every 80 bytes in the created definition SQL statements. If a definition contains simple comments, linefeed codes are inserted, resulting in syntax errors. Therefore, to use the created definition SQL statements, you must delete the inserted linefeed codes. The following table shows whether created SQL statements contain simple comments and the locations of simple comments:
No. | SQL statement | Simple comments | Location of simple comments |
---|---|---|---|
1 | CREATE TABLE | Y | Within the check constraint definition |
2 | CREATE INDEX | N | -- |
3 | COMMENT | N | -- |
4 | CREATE PROCEDURE | Y | Anywhere after CREATE |
5 | CREATE TRIGGER | Y | Anywhere after CREATE |
6 | CREATE VIEW | Y | Anywhere after CREATE |
The following table shows whether the created SQL statements can be executed by utilities and program products:
No. | Utility and program product | Whether executable | Description |
---|---|---|---|
1 | Database definition utility (pddef) | Y | Executable once the linefeed codes inserted in the simple comments have been deleted. |
2 | Database load utility (pdload) | N | Not executable because the database definition utility (pddef) is executed internally when the table definition and table data are both stored at the same time (-w all) by using table transfer unload files. |
3 | HiRDB SQL Executer | Y | Executable once the linefeed codes inserted in the simple comments have been deleted. |
Note that if pd_sql_simple_comment_use=Y is not specified in the system definition, execution of the utilities and program products results in an error.