4.4 Rules and notes

Organization of this section
(1) Rules
(2) Notes
(3) Information that is output in the event of an SQL error
(4) Handling of comments
(5) Operation after CONNECT
(6) Handling of control characters in SQL statements
(7) Using a file that contains a BOM

(1) Rules

  1. The database definition utility can be executed only while HiRDB is active.
  2. The database definition utility can be executed from any server machine.
  3. When a definition SQL is executed, commit processing is executed for each SQL statement.
  4. A maximum of 2 MB of SQL statements can be executed.
  5. Multiple definition SQL statements cannot be specified on the same line.
  6. When CREATE INDEX is executed by the database definition utility, the index is created in the batch mode at the time of execution if there is row data in the table for which the index is being created. If PDDBLOG=NO is specified in the client environment definition at this time, the index is created in the batch mode without collecting database updating log information. Therefore, the HiRDB administrator must perform operations in the no-log mode. For details about operations in the no-log mode, see the HiRDB Version 9 System Operation Guide
  7. ALTER TABLE and DROP TABLE must not be executed for an extracted database subject to data linkage. If either of these commands is executed in such a case, database agreement is not guaranteed. However, the definition on the HiRDB side can be changed if the tables or columns in a database subject to data linkage are not being updated and all update information has already been transferred to the target side. If the HiRDB Datareplicator is then reactivated after using the hdeprep command to re-analyze the extraction definition of tables or columns to be extracted by the HiRDB Datareplicator side, database agreement is guaranteed. For details about the procedures, see the HiRDB Datareplicator Version 8 Description, User's Guide and Operator's Guide.
  8. If the value of the pd_sql_simple_comment_use operand is Y, the behavior of SQL statements containing control characters might be different from when the operand's value is N, as described below:
    • An SQL statement whose length exceeds 2,000,000 bytes (including linefeed codes) will result in an error.
    • An SQL statement that contains a control character within a word or a phrase will result in an error. The results will also vary depending on the operand's value.
  9. When executing pddef, make sure that the character encoding is the same as for the HiRDB server in which the definitions are registered. You must also use the specified character encoding for entering data.
    The table below shows how to set the LANG environment variable, PDLANG client environment definition, and PDCLTCNVMODE during execution of pddef.

    Table 4-2 Setting LANG, PDLANG, and PDCLTCNVMODE during execution of pddef

    Encoding of input characterCharacter encoding set for the HiRDB server used to register definitions#1Character encoding in the pddef execution environment
    LANG#2PDLANG#3PDCLTCNVMODE#3
    SJIS (Shift JIS Kanji Code)SJISSJISLinux edition: SJIS
    Other: Omitted
    Omitted
    UJIS (EUC Japanese Kanji Code)UJISUJISOmitted
    C (single-byte character code)CCOmitted
    chinese (EUC Chinese-language Kanji Code)chineseAnyCHINESE
    GB18030 (Chinese Kanji Code)chinese-gb18030AnyCHINESE-GB18030
    UTF-8 (Unicode)utf-8 or utf-8_ivsAny (C is recommended)UTF-8

#1: This is the value set in the -c option of the pdsetup command.

#2: The value of the LANG environment variable depends on the OS. For details about the character encoding specified in LANG, see the HiRDB Version 9 UAP Development Guide.

#3: For details about PDLANG and PDCLTCNVMODE, see the HiRDB Version 9 UAP Development Guide.

(2) Notes

The results of the database definition utility can be checked by the return code set by the utility or by referencing a data dictionary table. The return codes are as follows:

Note
If return code 4 is followed by return code 8, the system cancels processing and terminates with final return code 8.

(3) Information that is output in the event of an SQL error

If any of the SQL statements listed below results in an error, the GET DIAGNOSTICS statement can be executed to display error information (if the SQL statement contains a comment, ERROR_POSITION will display the values only without the comment):

The figure below shows the format of error information.

Figure 4-2 Format of error information

[Figure]

(4) Handling of comments

The system regards any data beginning with /* in an SQL statement as a comment. All characters following /* (including semicolons) are handled as part of the comment. The system regards the first */ that follows /* as the end of the comment. Such a comment is called an enclosed comment. Note that if Y is specified in the pd_sql_simple_comment_use operand in the system definition, all characters (including semicolons) from -- up to the first linefeed code in an SQL statement are treated as a comment. Such a comment is called a simple comment.

The following shows examples, where the underlined parts are handled as comments:

Example 1:
CREATE TABLE T1 (C1 INT) ; /* CU ADDRESS TABLE */   ...Start and end of comment

Example 2:
CREATE TABLE T1 /* CU ADDRESS TABLE */ (C1 INT) ;   ...Start and end of comment

Example 3:
CREATE TABLE T1 /* CU ADDRESS   ...Start and continuation of comment
TABLE */ (C1 INT) ;   ...............End of comment

Example 4:
CREATE TABLE T1 /* CU ADDRESS TABLE * (C1 INT) ;   ...Start and continuation of comment

Example 5:
CREATE TABLE T1 /* CU /* ADDRESS */ TABLE */ (C1 INT) ;
 ...Start and end of comment

Example 6:
CREATE TABLE T1 -- CU ADDRESS  ...Start and end of comment
(C1 INT) ;

If the /* ... */ character string or the --...line-feed-code character string is within double quotation marks (") or single quotation marks ('), is not regarded as a comment. For details about specifying comments, see the description of the SQL specification format in the manual HiRDB Version 9 SQL Reference. Note that this utility's handling of comments is different from the interactive SQL execution utility (pdsql).

(5) Operation after CONNECT

Once connection is established (CONNECT), internal communication with the server begins (operation code SINF). If this internal communication results in an error, an SQL error is output and processing terminates.

(6) Handling of control characters in SQL statements

When the database definition utility is executed, the handling of control characters in SQL statements depends on conditions. The following table shows the handling of control characters for each condition.

Table 4-3 Handling of control characters in SQL statements

No.Value of the pd_sql_simple_comment_use operand in the system definitionLinefeed codes are deletedControl characters (other than linefeed codes) are converted to spaces
1NYY
2YNN
Legend:
Y: Deleted or converted.
N: Not deleted or converted.

(7) Using a file that contains a BOM

If you selected utf-8or utf-8_ivs as the character encoding in the pdsetup command, you can use a file with a BOM as the input file for pddef. Note that even when a file with a BOM is used as the input file for pddef, the BOM is skipped.