- -e export-filename~<[identifier:] pathname> ((up to 1,023 bytes))
- For exporting table definition information, trigger definition information, or stored procedure information:
- Specifies a name for the export file.
- For generating a definition SQL:
- Specifies the name of the export file used at the time of export of the table definition information, trigger definition information, or stored procedure information that is to be created.
A colon and the absolute pathname of the host that contains the export file must also be specified. If the identifier is omitted, the host name of the server machine where the dictionary import/export utility is executed (server machine where the pdexp command is entered) is assumed. If the specified export file is not found, a new file belonging to the user who started the HiRDB is created.
- -i export-filename~<[identifier:] pathname> ((up to 1,023 bytes))
Specifies the name of the export file for importing table definition information, trigger definition information, or stored procedure information or for generating a definition SQL.
A colon and the absolute pathname of the host that contains the export file must also be specified. If the identifier is omitted, the host name of the server machine where the dictionary import/export utility is executed (server machine where the pdexp command is entered) is assumed.
Specifies that tables for which reference constraints have been defined are to be exported in the order they are specified in the control statements file. This option is used to prevent an increase in memory usage that will result if HiRDB has to analyze the order in which tables with reference constraints defined are to be imported or exported.
- Notes
- When you specify this option while exporting tables for which reference constraints have been defined, specify the referenced tables first in the control statement. Tables are imported in the order they were exported. If you attempt to import a referencing table while no referenced table has been defined, CREATE TABLE will result in an SQL error and the import processing will be canceled.
- -f control-statements-filename~<pathname> ((up to 1,023 bytes))
Specifies the name of the file that contains the dictionary import/export utility control statements.
Create the control statement file on the server machine where the dictionary import/export utility will be executed (server machine where the pdexp command will be entered). You can omit this specification if all table definition information, trigger definition information, or stored procedure information in the export file is to be imported or if all definition information in the export file is to be used to generate a definition SQL.
- Rules for control statements
- You can specify a maximum of 10,000 tables, triggers, or stored procedures for import or export.
- Only one item can be specified per line.
- A single specification cannot span multiple lines.
- Null lines are ignored.
- MASTER, ALL, and HiRDB cannot be used as schema names. (You can not specify PUBLIC when you export trigger definition information.)
- Specifications of tables (-t), triggers (-g), and stored procedures (-p) cannot be intermixed.
- If you specify both a base table and its view tables in a single control statement file, make sure that you specify the base table first.
- Comments cannot be specified in a control statements file.
- Importing or exporting table definition information or generating a definition SQL:
-t schema-name.table-identifier [-w]
[-t schema-name.table-identifier [-w]] |
- Importing or exporting trigger definition information or generating a definition SQL:
-g schema-name.trigger-identifier [-w]
[-g schema-name.trigger-identifier [-w]] |
- Importing or exporting stored procedure information or generating a definition SQL:
-p schema-name.routine-identifier
[-p schema-name.routine-identifier] |
- -t schema-name.table-identifier
- Specifies a table that is to be imported, exported, or subject to definition SQL generation.
- The same table cannot be specified more than once.
- To import or export a public view, specify PUBLIC as the schema name.
- schema-name~<identifier> ((1-30))
- Specifies the schema name. If a schema name is enclosed in double quotation marks ("), it is handled as case sensitive; otherwise, it is handled as all uppercase letters.
- table-identifier ~<identifier> ((1-30))
- Specifies a table identifier. If the table identifier is enclosed in double quotation marks ("), it is treated as being case sensitive; otherwise, it is treated as in all upper-case letters. If the table identifier contains a space, the entire table identifier must be enclosed in double quotation marks (").
- -w
- Specifies that the table definition of a referencing table for which WITH PROGRAM was not specified is to be imported as the table definition of a referencing table for which WITH PROGRAM is specified. When the -w option is omitted or the specified table definition is for a table other than a referencing table, the system imports its table definition as a definition in which WITH PROGRAM is specified. This option is ignored during export processing.
- -g schema-name.trigger-identifier[-w]
- Specifies a trigger that is to be imported, exported, or subject to definition SQL generation.
- The same trigger cannot be specified more than once.
- schema-name ~<identifier> ((1-30))
- Specifies a schema name. If a schema name is enclosed in double quotation marks ("), it is treated as being case sensitive; otherwise, it is treated as in all uppercase letters.
- trigger-identifier ~<identifier> ((1-30))
- Specifies a trigger identifier. If a trigger identifier is enclosed in double quotation marks ("), it is treated as being case sensitive; otherwise, it is treated as in all uppercase letters. If a trigger identifier contains a space, enclose the entire trigger identifier in double quotation marks (").
- -w
- Specifies that a trigger definition without WITH PROGRAM specified is to be imported to a trigger definition with WITH PROGRAM specified. If the -w option is specified to import a trigger definition with WITH PROGRAM specified, the system ignores any existing SQL object whose functions and procedures for the target table are in effect.
- Notes when multiple triggers are defined for a single table
- If multiple triggers are defined for a single table, the triggers function in the order they are defined. If the order of trigger definitions at the time of import processing is different from the order at the time of export processing, the order in which the triggers function will be different before and after migration. To achieve the same trigger operations before and after migration, you must specify the trigger names in the control statements in the order they are defined.
- The following describes how to create control statements in the order of the trigger definitions:
- When importing or exporting all triggers defined for a specific table:
- The following SQL statement obtains the trigger owner, trigger identifier, and trigger definition time of each trigger defined for table in the order of the trigger definitions:
SELECT TRIGGER_SCHEMA,TRIGGER_NAME,CREATE_TIME
FROM "MASTER".SQL_TRIGGERS
WHERE TABLE_SCHEMA='owner' AND TABLE_NAME='table-name'
ORDER BY CREATE_TIME WITHOUT LOCK NOWAIT;
- In the control statement, specify the schema names and trigger identifiers in chronological order of the trigger definition times.
- When importing or exporting all defined triggers:
- The following SQL statement obtains the trigger owner, trigger identifier, and trigger definition time of each trigger defined for tables in the order of the trigger definitions:
SELECT TRIGGER_SCHEMA,TRIGGER_NAME,CREATE_TIME
FROM "MASTER".SQL_TRIGGERS
ORDER BY CREATE_TIME WITHOUT LOCK NOWAIT;
- In the control statement, specify the schema names and trigger identifiers in chronological order of the trigger definition times.
- -p schema-name.routine-identifier
- Specifies a stored procedure to be imported, exported, or subject to definition SQL generation.
- The same stored procedure cannot be specified more than once.
- schema-name ~<identifier> ((1-30))
- Specifies a schema name. If a schema name is enclosed in double quotation marks ("), it is treated as being case sensitive; otherwise, it is treated as in all uppercase letters.
- To export a public procedure, specify PUBLIC for the schema name.
- routine-identifier~<identifier> ((1-30))
- Specifies a routine identifier. If a routine identifier is enclosed in double quotation marks, it is handled as case sensitive; otherwise, it is handled as all uppercase letters. A routine identifier must be enclosed in double quotation marks if it includes a space.
- -u authorization-identifier~<identifier> ((1-30))
Specifies the authorization identifier of the user who will execute the dictionary import/export utility. When this option is specified, a message will be displayed requesting entry of the specified user's password. When the user responds by entering his/her authorization identifier/password, HiRDB checks whether or not that authorization identifier has access privileges to the specified tables; if so, the user is connected to the HiRDB system.
Do not specify this option in an environment in which a response cannot be entered, such as in the background mode where & is appended in the shell or in a remote shell.
When this option is omitted, one of the following assumptions is made about the authorization identifier/password:
- The value set in the PDUSER environment variable is assumed when the dictionary import/export utility is executed. If you execute the utility in an environment in which a password cannot be entered, such as in the background mode where & is appended in the shell or in a remote shell, make sure that you specify PDUSER.
- If the PDUSER environment variable is not set, the user name entered in the log-in window is assumed. A message will be displayed requesting entry of the password, at which time the password must be entered as the response.
An authorization identifier enclosed between double quotation marks (") is treated as case sensitive. An authorization identifier that is not enclosed between double quotation marks (") is treated as all uppercase letters. If you use the Bourne shell (sh), C shell (csh), or Korn shell (ksh), it is also necessary to enclose the authorization identifier in single quotation marks.
- -o definition-SQL-output-filename~<pathname> ((up to 1,023 bytes))
Specifies the name of the file to which the generated definition SQL is to be output. If the specified file already exists, it is overwritten; otherwise, it is created.
Specifies that both import and definition SQL generation are to be executed. If this option is specified but the -o option omitted, only import is executed.
- -W execution-monitoring-interval ~<unsigned integer> ((0 to 3600))
Specifies (in minutes) the monitoring interval when the execution time of the dictionary import/export utility is to be monitored.
For guidelines on the value to specify and details about the resulting operation, see the description of the pd_cmd_exec_time operand in the system common definition in the manual HiRDB Version 9 System Definition.
If 0 is specified in this option, the utility's execution time is not monitored.
If this option is omitted, the value of the pd_cmd_exec_time operand in the system common definition takes effect.