9.3 Rules and notes

Organization of this section
(1) Rules
(2) Rules for importing and exporting table definition information, trigger definition information, or stored procedure information
(3) Rules for definition SQL generation
(4) Rules for importing and exporting tables for which referential constraints are defined
(5) Notes
(6) Example of correcting errors when a definition SQL statement created during import processing exceeds 2 megabytes
(7) Using the files with a BOM

(1) Rules

  1. The dictionary import/export utility can be executed only while HiRDB is active. For a HiRDB/Single Server, the single server must already be started; for a HiRDB/Parallel Server, the following servers must be already be started:
    Table definition information import/export
    For export: Front-end server, dictionary server, and all back-end servers
    For import: Front-end server, dictionary server, and object back-end servers
    The master directory RDAREA, data directory RDAREA, and data dictionary RDAREAs must be placed in open status and released from shutdown status when exporting or importing is executed.
    Stored procedure information and trigger information import/export
    For export: Front-end server and dictionary server
    For import: Front-end server and dictionary server
    For import, data dictionary LOB RDAREAs must be defined in advance.
  2. The dictionary import/export utility must be executed at the server machine containing the single server or the server machine where the system manager is located.
  3. After the dictionary import/export utility has executed, a message is displayed requesting entry of the user's password; the password must then be entered (the entered password is not displayed). A password must be enclosed in double quotation marks if it includes lowercase letters.
  4. For the status of RDAREAs associated with the execution of the dictionary import/export utility, see Appendix C. RDAREA Status During Command Execution.
  5. To execute the dictionary import/export utility, set the LANG environment variable. To use character codes that are not supported by the OS in an environment in which the dictionary import/export utility is executed, you must set the PDLANG environment variable. For details about LANG and PDLANG, see the HiRDB Version 8 UAP Development Guide.
  6. If the specified export file is not found during export processing, the utility creates a new file belonging to the user who started the HiRDB. For this reason, the user who starts the HiRDB must have file creation privilege.
  7. During import processing, specify an export file that was created successfully during export processing. The HiRDB administrator must have referencing privilege for that export file.
  8. For a HiRDB/Parallel Server, the correspondences between servers and RDAREAs must be the same after importing as they were before exporting.
  9. Import/export processing cannot be performed between systems with a different magnitude of byte size or between systems with different character organizations.
  10. If you are using the SQL reserved word deletion facility, use the command execution window to set the PDDELRSVWDFILE client environment definition, and then execute the dictionary import/export utility. If the value of PDDELRSVWDFILE exceeds 8 (bytes), the PDDELRSVWDFILE setting is ignored. Furthermore, if the following conditions are not satisfied, an SQL syntax error may occur during import processing:
    • The value of PDDELRSVWDFILE in the client environment definition matches the value specified when the resources such as tables were defined.
    • The exporting and importing systems both have the same pd_delete_reserved_word_file operand value in the system common definition.
    • The exporting and importing systems both have the same files under $PDDIR/conf/pdrsvwd.

(2) Rules for importing and exporting table definition information, trigger definition information, or stored procedure information

  1. Importing and exporting cannot both be specified in the same execution of the utility.
  2. Table definition information, trigger definition information, or stored procedure information is exported in the order it is specified in the -t, -g, or -p option in the control statement file. The import order is the same as the order in which the same information was exported, regardless of the specification of the -t, -g, or -p option. Import or export processing is completed for each individual table, trigger, or stored procedure before going on to the next table or stored procedure.
  3. If there exists at the import destination the same table (a table with the same authorization-identifier.table-identifier), the same trigger (a trigger with the same authorization-identifier.trigger-identifier), or the same stored procedure (a stored procedure with the same authorization-identifier.routine-identifier), an import error results for that table, trigger, or stored procedure.
  4. Before starting import processing, make sure that the import destination system has been initialized and that schemas have been defined for the tables, triggers, or stored procedures to be imported.
  5. The dictionary import/export utility transfers table definition information from the export source to the import destination as is. Therefore, RDAREAs for storing the tables must have already been defined at the import destination with the same names as at the export source.
  6. The owner of a table, trigger, or stored procedure to be imported must have usage privileges for the RDAREAs that will store the table, trigger, or stored procedure.
  7. To import a stored procedure, the tables to be used by the stored procedure must have already been defined at the import destination. If a required table is not defined, the stored procedure cannot be imported.
  8. The -t, -g, and -p options cannot all be specified in the same control statement file.
  9. Import processing is disabled if an attempt is made to execute an SQL statement whose size exceeds 2 megabytes (size of an SQL statement defining tables, triggers, or stored procedures exceeding 2 megabytes).
  10. Definition information and stored procedure information about any of the following tables cannot be exported:
    • Table definitions containing an abstract data type (CREATE TABLE)
    • Index definitions specifying an index type (CREATE INDEX)
    • Function definitions (CREATE FUNCTION and system-defined scalar function)
    • Abstract data type definitions (CREATE TYPE)
    • Stored procedures containing an abstract data type (CREATE PROCEDURE)
    • Stored procedures that are declared in an abstract data type (CREATE TYPE)
    • Procedure calling another procedure from within a procedure definition (CREATE PROCEDURE)
    • Procedure calling a function definition (CREATE FUNCTION and system-defined scalar function) from within a procedure definition (CREATE PROCEDURE)
    • Comments that have an abstract data type column in the table specified in TABLE (COMMENT)
    • Comments for which the column specified in COLUMN is an abstract data type (COMMENT)
  11. When a shared table is imported using a HiRDB/Parallel Server, all the RDAREAs storing the shared table and shared indexes defined for the shared table in the target environment must be shared RDAREAs. If any are not shared RDAREAs, an SQL error occurs. If a non-shared table is to be imported but a table storage RDAREA is a shared RDAREA, an SQL error occurs. The table below describes the relationship between shared tables and target RDAREAs. This relationship does not apply to shared tables for a HiRDB/Single Server because shared RDAREAs are not created for a HiRDB/Single Server.
    Type of source tableRDAREA definition in the target systemCan be defined?pdexp processing after error*
    Table storage RDAREARDAREA storing index defined for the tableTableIndex
    Shared tableShared RDAREAShared RDAREAYYNot applicable.
    Shared RDAREANot a shared RDAREAYECancels processing and ignores the table definition.
    Not a shared RDAREAShared RDAREAENCancels processing.
    Not a shared RDAREANot a shared RDAREAEN
    Non-shared tableShared RDAREAShared RDAREAEN
    Shared RDAREANot a shared RDAREAEN
    Not a shared RDAREAShared RDAREAYECancels processing and ignores the table definition.
    Not a shared RDAREANot a shared RDAREAYYNot applicable.
    Legend:
    Y: Yes, can be defined.
    E: Yes, can be defined, but an SQL error results.
    N: No, cannot be defined.
    * pdexp suppresses internal COMMIT of the definition SQL statement and performs rollback if an error occurs during processing.
  12. A trigger created internally during definition of the referencing table cannot be exported. If such a trigger is specified during export processing, the utility displays the KFPX28504-W message (in which case the utility skips the corresponding trigger and exports the next trigger).

(3) Rules for definition SQL generation

  1. A definition SQL statement is not generated during export processing (-e option specified) even if the -o option is specified.
  2. Definition SQL statements are generated in the order they are exported.
  3. Definition SQL statements for the following tables, procedures, and functions are not generated:
    • Table definitions containing an abstract data type (CREATE TABLE)
    • Procedures specifying an abstract data type as a data type for a procedure parameter (CREATE PROCEDURE)
    • Procedures within a procedure definition (CREATE PROCEDURE)
    • Procedures and functions that are declared in an abstract data type (CREATE TYPE)
    • Index definitions specifying an index data type (CREATE INDEX)
    • Function definitions (CREATE FUNCTION)
  4. A semicolon (;) is added at the end of each line of a generated definition SQL statement so that the definition SQL can be used as an input to the database definition utility. In the case of CREATE PROCEDURE and CREATE TRIGGER, end_proc; is added.
  5. When CREATE PROCEDURE and CREATE TRIGGER are to be created, the values specified for the SQL optimization option and SQL extension optimizing option are converted to numeric values.
  6. When a definition SQL is to be created for a table for which a foreign key or check constraint has been defined, the utility creates a definition SQL statement for the foreign key or check constraint together with the definition SQL statement for the table. When a definition SQL statement for a foreign key or check constraint is created, the location where the foreign key or check constraint name is defined is determined by the value of PDCNSTRNTNAME in the client environment definition and the pd_constraint_name operand value in the system definition. For details about PDCNSTRNTNAME, see the HiRDB Version 8 UAP Development Guide; for details about the pd_constraint_name operand, see the manual HiRDB Version 8 System Definition.
  7. The following table describes the linefeed conditions for definition SQL statements that are generated:
    Generated definition SQL statementLinefeed condition
    CREATE TABLE
    • Between CREATE and the table identifier
    • At each column definition
    • At each table storage RDAREA
    • At each table option
    • At each foreign key
    • At each check constraint definition
    • At WITH PROGRAM
    • At every 80th byte, in the case of a row that exceeds 80 bytes
    CREATE INDEX
    • Between CREATE and the index identifier
    • Between ON and the table identifier
    • At each column definition
    • At each index storage RDAREA
    • At each index option
    • At every 80th byte, in the case of a row that exceeds 80 bytes
    CREATE ALIASAt every 80th byte, in the case of a row that exceeds 80 bytes
    COMMENTAt every 80th byte, in the case of a row that exceeds 80 bytes
    CREATE VIEW
    • Between CREATE and AS
    • At every 80th byte, in the case of a row that exceeds 80 bytes
    CREATE PROCEDURE
    • Between CREATE and a parameter mode specification
    • At a linefeed code in a definition source statement
    • At the SQL compile option
    • At an SQL procedure statement
    • At every 80th byte, in the case of a row that exceeds 80 bytes
    CREATE TRIGGER
    • Between CREATE and the trigger operation
    • At a linefeed code in a definition source statement
    • At the SQL compile option
    • At WITH PROGRAM
    • At every 80th byte, in the case of a row that exceeds 80 bytes

(4) Rules for importing and exporting tables for which referential constraints are defined

(a) Exporting
  1. When a referencing table and a referenced table are exported, the definitions of their primary key and foreign key are also exported unconditionally.
  2. If you are exporting both a referencing table and a referenced table at the same time, specify the referenced table first in the control statement (because tables are imported in the order they were exported).
(b) Importing
  1. When a referencing table and a referenced table are imported, the definitions of their primary key and foreign key are also imported unconditionally.
  2. When a referencing table is imported, the referenced table must have been defined. If an attempt is made to import a referencing table for which no referenced table has been defined, CREATE TABLE results in an SQL error. In such a case, the utility terminates import processing without processing the next table.
  3. If the import target contains a constraint with the same name, CREATE TABLE results in an SQL error. In such a case, the utility terminates import processing without processing the next table.
  4. For the primary and cluster keys, new index and table numbers are assigned in the target system.
  5. The index identifier for the primary key is determined by the table number used during import processing. Therefore, it may not be the same as for the source system.
  6. The following table describes the handling of SQL objects that use a referenced table when a referencing table is imported:
    Referencing action during table definition-w optionHandling of SQL object that uses referenced tableRemarks
    CASCADESpecifiedTo 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.
    OmittedThe 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.

    RESTRICTSpecifiedIf 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.
    OmittedThe 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.

(5) Notes

  1. The results of the dictionary import/export utility can be checked on the basis of the return value set by the utility and by whether or not there are any error messages. The utility returns one of the following values:
    0: Terminated normally
    4: Warning error occurred
    8: Some processing terminated normally, but some processing was skipped
    12: Terminated without completing any processing
  2. After importing, the table access privilege must be set to GRANT in the definition SQL.
  3. When a view table is to be imported, the corresponding base table (or tables) must have already been imported or defined and access privileges set.
  4. Multiple instances of the dictionary import/export utility cannot be executed simultaneously. If the utility is already executing, it cannot be executed again until the current execution has terminated.
  5. Import processing is functionally the same as executing the following definition SQL statements at the import destination system (the specification of each SQL should be checked before import processing is executed):
    Table definition information import/export
    • ALTER TABLE
    • COMMENT
    • CREATE ALIAS
    • CREATE INDEX
    • CREATE TABLE
    • CREATE VIEW
    Trigger definition information import/export
    • CREATE TRIGGER
    Stored procedure information import/export
    • CREATE PROCEDURE
    For details about the SQL specifications, see the HiRDB Version 8 SQL Reference.
  6. If export processing is interrupted by forced termination of the utility or a server interrupt, the table definition information, trigger definition information, or stored procedure information may not have been exported to the export file, even if the KFPX28402-I message is displayed. In such a case, re-execute the dictionary import/export utility and terminate export processing normally.
  7. To execute pdexp, access privileges for the export file and definition SQL output file must have been granted to the HiRDB administrator.

(6) Example of correcting errors when a definition SQL statement created during import processing exceeds 2 megabytes

When importing data, pdexp creates a definition SQL statement and then executes the statement. It is possible that the size of the SQL statement will exceed the permitted maximum length of 2 megabytes, because syntax for default values is also created for the definition SQL statement.

The following example corrects CREATE TABLE because the size of this definition SQL statement exceeds 2 megabytes.

Example of CREATE TABLE whose size exceeds 2 megabytes:
When CREATE TABLE exceeds 2 megabytes, the utility deletes the column definitions starting with the largest column ID so as to keep its size within 2 megabytes. To add the deleted column definitions, the utility creates ALTER TABLE. The following shows a coding example:

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

Explanation
  1. CREATE TABLE is created without columns C99 and C100.
  2. ALTER TABLE is created to add columns C99 and C100, which were deleted from CREATE TABLE.
SQL error resulting from deleting a column definition in CREATE TABLE:
An SQL error occurs if a column deleted in 1 above is any of the following:
  • Partitioning key
  • Cluster key
  • Column specified in a referential constraint
  • Column specified in a check constraint search condition
In this example, CREATE TABLE results in an SQL error because the partitioning key C100 has been deleted.
How to avoid SQL errors:
You can use the following procedure to avoid SQL errors:
  1. Specify the -o option during import processing and create the definition SQL statement (output the definition SQL to a file).
  2. Delete the default value section from the CREATE TABLE definition SQL statement that was output in step 1.
  3. In the definition SQL statement output in step 1, move the first ALTER TABLE through the corresponding ALTER TABLE (the column definitions that result in the SQL error because they were deleted from CREATE TABLE) to the column definitions of CREATE TABLE.
  4. Use pddef to execute the edited definition SQL statement.
The following show an example of editing the above definition SQL statement:

CREATE TABLE "root"."T1"
("C1" INTEGER NOT NULL,
"C2" CHAR(100),
    :
    :
"C98" VARCHAR(100)
"C99" INTEGER   ...............Added in step 3
"C100" INTEGER NOT NULL   .....Added in step 3
) IN (("RDUSER01") "C100" < 1500
("RDUSER02"))
PCTFREE=(30,10)
FOREIGN KEY ("C1","C2") REFERENCES "root"."T2"
 CONSTRAINT CNST01   .........Partially deleted in step 2
;

(7) Using the 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 pdexp. Table 9-1 shows whether or not 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-1 Whether or not files with a BOM can be used in pdexp (applicable to UTF-8)

OptionInput fileUse of file with a BOM
-lExport fileN
-fControl statements fileY
Legend:
Y: Can be used
N: Cannot be used