1. The dictionary import/export utility can be executed only while HiRDB is active. In a HiRDB single server configuration, the single server must already be started. In a HiRDB parallel server configuration, the following servers must 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). If the password contains lowercase letters, enclose it between double quotation marks (").
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 9 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. In a HiRDB parallel server configuration, 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.
11. You must not issue definition SQL statements at the exporting system while export processing is underway.

1. Importing and exporting cannot both be specified in the same execution of the utility.
2. Trigger definition information or stored procedure information is exported in the order that it is specified in the -g or -p option in the control statements file. The import order is the same as the order in which the same information was exported, regardless of the specification of the -g or -p option.
3. Import or export processing is completed for each individual table, trigger, or stored procedure before going on to the next table or stored procedure.
4. 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. Export processing continues, except for the table resulting in the error.
5. 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.
6. 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.
7. 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.
8. 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.
9. The -t, -g, and -p options cannot all be specified in the same control statement file.
10. Import processing is disabled if an attempt is made to execute an SQL statement whose size exceeds 2,000,000 bytes (an SQL statement defining tables, triggers, or stored procedures that exceeds 2,000,000 bytes).
11. 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)
12. When a shared table is imported using a HiRDB parallel server configuration, 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 configuration because shared RDAREAs are not created for a HiRDB single server configuration.
    

    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.

    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.

13. 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).

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 9 UAP Development Guide; for details about the pd_constraint_name operand, see the manual HiRDB Version 9 System Definition.
7. The following table describes the linefeed conditions for definition SQL statements that are generated:
   

   Generated definition SQL statement	Linefeed 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#
   COMMENT	At 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#

   #

   The following table describes the operation depending on the value of the pd_sql_simple_comment_use operand in the pdsys system common definition:

   No.	Value of the pd_sql_simple_comment_use operand in the pdsys system common definition	Operation
   1	N	Inserts a line break.
   2	Y	Does not insert a line break.

   To use simple comments, Y must be set for the pd_sql_simple_comment_use operand. An SQL syntax error will occur during import processing if there is a mismatch between the value set for the pd_sql_simple_comment_use operand and whether the exported SQL statements contain simple comments,.

   The following table explains these combinations:

   No.	Whether the exported SQL statements contain simple comments	Value set for pd_sql_simple_comment_use during import processing
   		Y	N
   1	No	Y	Y
   2	Yes	Y	N

   Legend:

   Y: An SQL syntax error does not occur.

   N: An SQL syntax error (KFPA11105-E) occurs.

8. A definition SQL statement cannot be generated for a table for which a CHAR-type column with the UTF-16 character set specified is used as the partitioning key for key range partitioning or matrix partitioning (key range partitioning with a boundary value specified) and the length of the partitioning key exceeds the column's definition length.

(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. HiRDB determines the export order and exports referenced tables first regardless of the order in which the table definition information is specified in the control statements file. However, if you have specified the -v option, HiRDB exports tables in the order they are specified in the control statements file.
3. Trigger definition information and stored procedure information are exported in the order they are specified in the control statements file.

(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. 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.
3. For the primary and cluster keys, new index and table numbers are assigned in the target system.
4. 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.
5. 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 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.	--

   Legend:

   --: Not applicable

   
   

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:After importing, the table access privilege must be set to GRANT in the definition SQL.
   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 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 9 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.

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.

• When using pdexp to import a table whose syntax and identifiers listed below are omitted
  

  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
  8	SEGMENT REUSE in CREATE TABLE in a version earlier than 09-50	SEGMENT REUSE NO
  9	SEGMENT REUSE in CREATE TABLE in version 09-50 or later	SEGMENT REUSE

  The length of the definition SQL statement might exceed 2,000,000 bytes because the syntax for default values and the quotation marks for identifiers are added.
  

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.

If CREATE TABLE exceeds 2,000,000 bytes

If CREATE TABLE exceeds 2,000,000 bytes, pdexp deletes the column definition section for the large column IDs so that the CREATE TABLE statement fits within the 2,000,000-byte limit. Because the deleted column definition section needs to be added back, pdexp creates an ALTER TABLE statement that adds the deleted column definition section. The following shows an 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 errors resulting from the column definition section added by ALTER 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, the created ALTER TABLE results in an SQL error because column C100, which is the partitioning key, was added.

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. In the definition SQL statement output in step 1, move the column definitions of all ALTER TABLE statements in the range from the first ALTER TABLE statement to the ALTER TABLE statement that has the column definitions that resulted in the SQL error, to the column definitions of CREATE TABLE.
3. In the definition SQL statement output in step 1, delete the default values in CREATE TABLE so that the size does not exceed 2,000,000 bytes.
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 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 or utf-8_ivs as the character encoding in the pdsetup command, you might 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

Legend:

Y: Can be used

N: Cannot be used

You can also import and export definitions that contain simple comments and create definition SQL statements.

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

Legend:

Y: Contains simple comments.

N: Does not contain simple comments.

--: Not applicable

Note that an error occurs if simple comments are included and the value set for the pd_sql_simple_comment_use operand is not Y when the following utilities and program are executed:

• Database definition utility (pddef)
• Database load utility (pdload)
• HiRDB SQL Executer