<identifier> ((up to 511 bytes))

This environment variable specifies the host name of the HiRDB server to be connected.

For HiRDB/Single Server, this environment variable specifies the host name of the server machine on which the single server is installed. For HiRDB/Parallel Server, this environment variable specifies the host name of the server machine on which the system manager is installed. If PDFESHOST is specified, the PDFESHOST host name can be specified. When the PDFESHOST host name is specified, the HiRDB client can be connected to the HiRDB server even if a failure occurs in the system manager unit.

The FQDN or the IP address can also be specified instead of the host name. The specification methods are shown below.

Host name

The host name that was specified in the pdunit -x operand of the system common definition must be specified.

Example:

PDHOST=host1

FQDN

The Free Qualified Domain Name (FQDN) connects the host name and domain name of the HiRDB server with a period.

Example:

PDHOST=host1.soft.hitachi.co.jp

IP address

The IP address is specified with a decimal number that has each group of 3 digits separated with a period.

Example:

PDHOST=172.18.131.34

System switchover with IP address inheritance

• For the UNIX version

If the IP address is to be inherited, specify the host name of the primary system.

• For the Windows version
  If the IP address is to be inherited, specify the virtual network name registered for the MSCS network name. For details about the virtual network, see the HiRDB Version 8 System Operation Guide.
  

System switchover without IP address inheritance

Specify the host names of both the primary system and the secondary system. If you specify only the host name of the primary system, you must change the specification of this environment variable after system switching occurs. After system switching, change the host name to that of the new primary system.

When an X/Open-compliant UAP under OLTP is the client and HiRDB_PDHOST is specified in the system environment definition

The HiRDB_PDHOST specification takes precedence. The PDHOST setting is replaced with the value specified in HiRDB_PDHOST.

Rules for FQDN specification

1. Do not specify an FQDN if the version of the HiRDB server to be connected is earlier than 05-03. If an FQDN is specified and there is a server process remaining after the maximum wait time (PDCWAITTIME) of the client elapses, the server process cannot be cancelled by sending a cancel process to the HiRDB server.

<unsigned integer> ((5001-65535)) <<20000>>

This environment variable specifies the port number of the HiRDB server to be connected. Specify the HiRDB server port number to be accessed in the server machine of the host specified in PDHOST.

If there are multiple HiRDBs, the port number differs for each HiRDB server. However, specify the port number of the HiRDB server to be accessed.

For details about the pd_name_port operand, see the manual HiRDB Version 8 System Definition.

When an X/Open-compliant UAP under OLTP is the client and HiRDB_PDNAMEPORT is specified in the system environment definition

The specification of HiRDB_PDNAMEPORT takes precedence. The setting of PDNAMEPORT is replaced with the value specified in HiRDB_PDNAMEPORT.

<identifier> ((up to 523 bytes))

This environment variable is related to the HiRDB/Parallel Server.

If there are multiple front-end servers, this environment variable specifies the host name of the front-end server for the HiRDB server to be connected. If the client is to be connected to the host with a port number that is specified with -p in the pdunit system definition (when the system switchover facility is used), that port number must be specified.

The front-end servers determined by the system manager include recovery-unnecessary front-end servers.

The FQDN or the IP address can also be specified instead of the host name.

Host name

The host name that was specified in the pdunit -x operand of the system common definition must be specified.

Example:

PDFESHOST=host1

FQDN

The Free Qualified Domain Name (FQDN) connects the host name and domain name of the HiRDB server with a period.

Example:

PDFESHOST=host1.soft.hitachi.co.jp

IP address

The IP address is specified with a decimal number that has each group of 3 digits separated with a period.

Example:

PDFESHOST=172.18.131.34

System switchover with IP address inheritance

• For the UNIX version

If the IP address is to be inherited, specify the host name of the primary system.

• For the Windows version
  If the IP address is to be inherited, specify the virtual network name registered for the MSCS network name. For details about the virtual network, see the HiRDB Version 8 System Operation Guide.
  

System switchover without IP address inheritance

Specify the host names of both the primary system and the secondary system. If you specify only the host name of the primary system, you must change the specification of this environment variable after system switching occurs. After system switching, change the host name to that of the new primary system.

Rules for FQDN specification

1. Do not specify an FQDN if the version of the HiRDB server to be connected is earlier than 05-03.
2. In some cases, after the maximum client wait time (PDCWAITTIME) elapses, the server process cannot be cancelled and thus remains.

Rules for port number omission

If the port number is omitted, the port number that was specified with PDNAMEPORT is used as the default value. The port number that was specified with PDNAMEPORT is also used as the default value if the port number of the host containing the secondary system front-end server is omitted.

Relationship with other environment variables

1. This environment variable must be specified for multiple front-end servers when the front-end server to be connected is selected by the client user or when PDSERVICEPORT is specified.
2. This environment variable should be specified with PDSERVICEGRP.

Notes

1. If a program that uses the X/Open XA interface connects to a recovery-unnecessary front-end server, that program cannot reference or update the database. In this case, specify PDFESHOST and PDSERVICEGRP, and be sure to connect to a front-end server that is not a recovery-unnecessary front-end server.
2. If there are multiple front-end servers, specify equivalent host names in PDFESHOST so that the load is not concentrated on the connected front-end server.
3. The host name specified in PDFESHOST can also be specified in PDHOST. This allows the HiRDB client to be connected to the HiRDB server even if an error occurs in the system manager unit.
4. If reflection processing is performed using the two-phase commit method (fxa_sqle is specified for the reflection system definition commitment_method operand) as the synchronization point processing method in the reflected side Datareplicator, reflection processing fails if a recovery-unnecessary front-end server in the reflected-side HiRDB is connected. In this case, specify PDFESHOST and PDSERVICEGRP, and be sure to connect to a front-end server that is not a recovery-unnecessary front-end server.

<character string> ((up to 30 bytes))

This environment variable specifies the single-server name or front-end server name of the HiRDB server to be connected.

If multiple front-end servers are used with a HiRDB/Parallel Server, this environment variable specifies the server name of the front-end server to be connected.

Relationship with other environment variables

1. The time required to connect to the HiRDB server can be shortened by specifying this environment variable simultaneously with PDSERVICEPORT.
2. When using a HiRDB/Parallel Server, also specify PDFESHOST.

Note

1. If a program that uses the X/Open XA interface connects to a recovery-unnecessary front-end server, that program cannot reference or update the database. In this case, specify PDSERVICEGRP and PDFESHOST, and be sure to connect to a front-end server that is not a recovery-unnecessary front-end server.
2. If reflection processing is performed using the two-phase commit method (fxa_sqle is specified for the reflection system definition commitment_method operand) as the synchronization point processing method in the reflected side Datareplicator, reflection processing fails if a recovery-unnecessary front-end server in the reflected-side HiRDB is connected. In this case, specify PDFESHOST and PDSERVICEGRP, and be sure to connect to a front-end server that is not a recovery-unnecessary front-end server.

This environment variable specifies the server type of the HiRDB server to be connected.

WS

Specify this server type if the HiRDB server is the HP-UX, Solaris, or AIX 5L version.

PC

Specify this server type if the HiRDB server is the Linux or Windows version.

<unsigned integer> ((5001-65535))

This environment variable specifies the port number for high-speed connection. Specify the value that was specified for the pd_service_port operand in the system definition of the HiRDB server to be connected.

If multiple front-end servers are used, the high-speed connection port number of the front-end server to be connected should be specified. For details about the pd_service_port operand, see the manual HiRDB Version 8 System Definition.

Benefits

Specifying this operand can shorten the amount of time required to connect to the HiRDB server.

Relationship with other environment variables

When this operand is specified, the following operands must also be specified:

HiRDB/Single Server

• PDHOST
• PDNAMEPORT
• PDSERVICEGRP

HiRDB/Parallel Server

• PDHOST
• PDNAMEPORT
• PDFESHOST
• PDSERVICEGRP

Note

If you use the system switchover facility for mutual system switchover and specify different port numbers for each system in the pd_service_port operand of the system definitions, also specify a high-speed connection port number for the secondary system.

<character string> ((up to 1024 bytes))

This environment variable can be specified when HiRDB/Parallel Server is used and the pd_service_port operand is specified in the system definition.

To set up a high-speed connection, specify the FES group to be connected. In a configuration with multiple front-end servers, specify the FES group of the connection destination, and the switchover FES group for switching the connection if an error occurs in the first FES group.

The information to be specified for FES-group and switchover-FES-group is described below.

FES-group

A FES group collectively specifies all information for a high-speed connection destination (PDFESHOST, PDSERVICEGRP, and PDSERVICEPORT). A specification example is shown below.

 
host1:fes1:20001
 

switchover-FES-group

In a configuration with multiple front-end servers, a switchover FES group is a FES group to which the connection can be switched over if a failure occurs in the front-end server of the connected FES group. If an error occurs when a switchover FES group is specified, the connection switches to the switchover FES group. If multiple switchover FES groups are specified, the connection is switched over according to the sequence in which the groups are specified.

A switchover FES group is specified in the same way as a FES group.

Notes

1. When this environment variable is specified, the specifications for PDFESHOST, PDSERVICEGRP, and PDSERVICEPORT are ignored.
2. When a switchover FES group is specified, the number of connections for the switchover FES group may increase temporarily because the connection destination is switched if a server error occurs or the number of connected users is exceeded. You must therefore check and, if necessary, revise the value of the pd_max_users operand for the switchover FES group.
3. When switchover FES groups are specified, it may take a while for HiRDB to return an error to the UAP if errors occur in all specified switchover FES groups or if the number of connected users is exceeded.

Usage example

When only one FES group is specified

Explanation

If only one FES group is specified, the client is connected only to front-end server fes1 of host host1.

When one FES group and one switchover FES group are specified

Explanation

If an error occurs in the connection of 1, the client is connected with 2. If an error occurs in 2 as well, HiRDB returns an error to the UAP.

When one FES group and multiple switchover FES groups are specified

Explanation

If an error occurs in the connection of 1, Client 1 is connected with 2. If additional connection errors occur, Client 1 is connected with 3 and then 4. If errors occur in all connections, HiRDB returns an error to the UAP.

<unsigned integer> ((0, 5001-65535, 5001-65535, 5001-65535)) <<0>>

This environment variable specifies the receive port number or range of receive port numbers to be used when the HiRDB client communicates with the HiRDB server.

If this environment variable is omitted, the operating system automatically specifies the receive port number or range of receive port numbers. Therefore, this environment variable normally does not have to be specified.

Specification method

Specification examples of the receive port number are shown as follows.

• Specifying one port number:
  10000-10000 or 10000
  
• Specifying a range of port numbers:
  10000-10500
  

If 0 is specified, HiRDB assumes that this environment variable has not been specified.

Benefits

If a firewall has been set between the HiRDB server and the HiRDB client, and the receive port numbers that can pass through the firewall are limited, specifying this environment variable allows communications to pass through the firewall.

Notes

1. If a range of receive port numbers is specified in this environment variable, the HiRDB client automatically assigns an unused port number from the specified range. If there is no unused port number in the specified range, an error occurs.
2. The HiRDB client uses one port number for one connection to the HiRDB server. Consequently, one UAP process uses multiple port numbers in the following cases:
   The ODBC uses multiple connections.
   The multi-connection facility is used.
   
3. When multiple UAPs are executed concurrently, one port number can be used only by one UAP process. Therefore, if a range that includes the same port numbers is specified for multiple UAP processes to be executed concurrently, contention may occur when the port numbers are assigned. To ensure that the port numbers do not run out in this case, specify a range that includes more port numbers than the largest number of port numbers to be used.
4. Specify receive port numbers that are not in the range of port numbers that the operating system assigns automatically. The range of port numbers that the operating system assigns automatically differs for each operating system.
5. If the ODBC is connected through Microsoft Jet, multiple connections with the HiRDB server are established automatically.
6. When specifying a range that includes 10 or more port numbers, make sure that the range includes about 20% more port numbers than will actually be used. If this margin is not included, the efficiency of the port number search process drops.
7. Any port number that is being used by other programs cannot be used by the HiRDB client.
8. Port numbers being used by the HiRDB client cannot be used by other programs. If a service uses a fixed port number found in the range specified in this environment variable, there may be times when that service cannot be started.
9. Manage the programs found inside the firewall so that programs other than the HiRDB client do not improperly use port numbers that have been set, so that the HiRDB client can communicate through the firewall.

<unsigned integer> or <identifier> ((up to 256 bytes))

When multiple communication paths are set to the host of an HiRDB client and you wish to identify a communication path for communicating with the HiRDB server, this environment variable specifies the IP address, FQDN, or host name for that communication path. The specification methods are shown below.

IP address:

Specify a decimal address, using a period to delimit each byte.

Example:

PDCLTRCVADDR=172.18.131.34

FQDN:

The FQDN is comprised of the host name and domain name of a HiRDB server separated by periods.

Example:

PDCLTRCVADDR=host1.soft.hitachi.co.jp

Notes

1. The default value is the IP address corresponding to the standard host name of the client host.
2. If an invalid IP address or host name is specified in this environment variable, the HiRDB client cannot receive a response from the HiRDB server during connect processing to the HiRDB server. Therefore, an error (SQLCODE -732) occurs after the five-minute timer monitoring elapses.
3. If PDIPC=MEMORY is specified, the PDCLTRCVADDR specification is ignored.
4. If 1 is specified in the pd_change_clt_ipaddr operand of the system definitions, the PDCLTRCVADDR specification is ignored.

<identifier> ((4 characters))

This environment variable specifies a unique 4-character identifier of the applicable OLTP when multiple OLTPs use an X/Open-compliant API to access a HiRDB server.

If one of the following conditions applies to the specification of this environment variable, the HiRDB server cannot identify the OLTP to which a transaction belongs. Therefore, if a system failure or a transaction error occurs in an OLTP, the transaction completion timing is not synchronized.

• This environment variable is omitted in an operating mode in which multiple OLTPs access the HiRDB server.
• The identifiers assigned to the OLTPs are not unique in an operating mode in which multiple OLTPs access the HiRDB server.
• Multiple OLTP identifiers are assigned to the same OLTP.

When an X/Open-compliant UAP under OLTP is the client and HiRDB _PDTMID is specified in the system environment

The HiRDB_PDTMID specification takes precedence. The PDTMID setting is replaced with the value specified in HiRDB_PDTMID.

This environment variable specifies whether the transaction transfer facility is to be used when linking with a UAP that uses an X/Open-compliant API under OLTP.

0: Do not use the transaction transfer facility.

1: Use the transaction transfer facility.

This environment variable should be specified in accordance with instructions provided by the HiRDB administrator. For details about the transaction transfer facility, see the HiRDB Version 8 Installation and Design Guide.

When an X/Open-compliant UAP under OLTP is the client and HiRDB_PDXAMODE is specified in the system environment definition

The HiRDB_PDXAMODE specification takes precedence. The PDXAMODE setting is replaced with the value specified in HiRDB_PDXAMODE.

When the client is linked with OpenTP1

The trnstring operand of OpenTP1 and the specification of the DXAMODE setting must match.

When the client is linked with TPBroker

At the completion of a TPBroker transaction, a transaction completion process that is different from the one used by the UAP process is used. Therefore, 1 must be specified in PDXAMODE when the client is linked with TPBroker. If 0 is specified, UAP process transactions cannot be completed.

When the client is linked with TUXEDO

At the completion of a TUXEDO global transaction, a transaction manager server (TMS) that is different from the one used by the UAP process is used. Therefore, 1 must be specified in PDXAMODE when the client is linked with TPBroker. If 0 is specified, UAP process transactions cannot be completed.

When the client is linked with WebLogic Server

Specify 1 in PDXAMODE. If PDXAMODE is omitted or if 0 is specified, UAP process transactions cannot be completed.

When the client is linked with TP1/EE (limited to the UNIX version)

Specify 0 in PDXAMODE. If PDXAMODE is omitted or if 1 is specified, transactions sometimes cannot be completed.

<unsigned integer> ((1-2147483647)) <<20>>

This environment variable specifies the maximum number of transactions that can be executed simultaneously per process when a UAP that uses an X/Open-compliant API supporting multi-thread or X/Open-compliant API using multi-connection facility accesses HiRDB.

Estimation method

Estimate the value to be specified for this environment variable based on the following formula:

specification-value = (number-of-transactions-that-can-occur-in-target-process) (number-of-HiRDB-servers-that-target-process-can-access)

<unsigned integer> ((0-270)) <<2>> (seconds)

This environment variable specifies how long to wait before sending the next connection request to HiRDB, when OpenTP1 that accesses HiRDB using an X/Open-compliant API cannot connect to HiRDB during a transaction recovery process or a resource manager monitoring process, or when HiRDB cannot recover a transaction.

If 0 is specified, a connection request is issued for each HiRDB transaction recovery instruction.

Estimation method

Estimate the specification value from the following calculation equation:

specification-value = a b (c - d e)

a: 270

b: Total number of OpenTP1 transaction recovery processes to be connected to HiRDB

c: Total number of automatic allocation port numbers on the HiRDB single server or the server machine containing the system manager

d: Number of port numbers used during peak transaction times on the HiRDB single server or the server machine containing the system manager

e: If the system switchover facility is being used, use 2. If not, use 1.

Notes

1. If you specify a small time value in PDXARCVWTIME, and several transactions are stopped in OpenTP1, a port number shortage may occur on the HiRDB single server or the server machine containing the system manager. Therefore, if the time calculated with the estimation method is larger than the default value, you should specify the time calculated with the estimation method.
2. If startup of the HiRDB single server or system manager unit is completed immediately after the OpenTP1 transaction recovery process begins the wait time specified in PDXARCVWTIME, recovery of the transaction connected to HiRDB may take longer to complete.

This environment variable specifies the format of trace file names when the connection mode uses an X/Open-compliant API. If the connection mode does not use an X/Open-compliant API, the PDXATRCFILEMODE specification is ignored.

LUMP

Output the trace files without adding an execution process ID to each trace file name.

The LUMP specification is recommended if the UAP is non-resident and is executed repeatedly and the process ID changes with each execution. By specifying LUMP, you can prevent the number of trace files from increasing each time the non-resident UAP is executed and thus causing unstable operation of the operating system and other programs.

When LUMP is specified, the output destination for trace information becomes limited, and the trace output size may need to be increased. In addition, the processing time may increase because the trace output competes with the output of other processes.

SEPARATE

Output the trace files by adding an execution process ID to each trace file name.

The SEPARATE specification is recommended if the UAP is a resident process.

<identifier>

This environment variable specifies the host name of the HiRDB server to be connected. The value specified in this environment value is replaced with the PDHOST setting.

For a HiRDB/Single Server, this environment variable specifies the host name of the server machine in which the Single Server is installed. For a HiRDB/Parallel Server, this environment variable specifies the host name of the server machine in which the system manager is installed.

Other than the host name, you can specify the FQDN or the IP address. The specification methods are shown below.

Host name

The host name that was specified in the pdunit -x operand of the system common definition must be specified.

Example:

PDHOST=host1

FQDN:

The FQDN is comprised of the host name and domain name of a HiRDB server separated by periods.

Example:

PDHOST=host1.soft.hitachi.co.jp

IP address

The IP address is specified with a decimal number that has each byte separated with a period.

Example:

PDHOST=172.18.131.34

System switchover with IP address inheritance

• For the UNIX version

If the IP address is to be inherited, specify the host name of the primary system.

• For the Windows version
  When the IP address is inherited, specifies a virtual network name registered for the MSCS network name. For details about the virtual network, see the HiRDB Version 8 System Operation Guide.
  

System switchover without IP address inheritance

Specify the host names of both the primary system and the secondary system. If you specify only the host name of the primary system, you must change the specification of this environment variable after system switching occurs. After system switching, change the host name to that of the new primary system.

<unsigned integer> ((5001-65535))

This environment variable specifies the port number of the HiRDB server. The port number must be the value that was specified in pd_name_port of the system definition for the HiRDB server to be connected. The value specified in this environment variable is replaced by the PDNAMEPORT setting.

For multiple HiRDBs, the port number differs for each HiRDB server, so you must specify the port number of the HiRDB server to be connected.

For details about pd_name_port, see the manual HiRDB Version 8 System Definition.

<integer> ((4 characters))

This environment variable specifies a unique identifier for the applicable OLTP when multiple OLTPs use an X/Open-compliant API to access a HiRDB server. The value specified in this environment variable is replaced by the PDTMID setting.

If one of the following conditions applies to the specification of this environment variable, the server cannot identify the OLTP to which a transaction belongs. Therefore, if a system failure or transaction error occurs in an OLTP, the transaction conclusion timing is not synchronized.

• This environment variable and the PDTMID specification are omitted in an operating mode in which multiple OLTPs access the HiRDB server.
• The identifiers assigned to the OLTPs are not unique in an operating mode in which multiple OLTPs access the HiRDB server.

This environment variable specifies whether or not the transaction transfer facility is used when a UAP that uses an X/Open-compliant API under OLTP is being used as the client. The value specified in this environment variable is replaced with the PDXAMODE setting.

0: Do not use the transaction transfer facility.

1: Use the transaction transfer facility.

This operand should be specified in accordance with instructions provided by the HiRDB administrator. For details about the transaction transfer facility, see the HiRDB Version 8 Installation and Design Guide.

<<current user's name without password>>

This environment variable cannot be omitted in the Windows environment. It can be omitted in the UNIX environment.

This environment variable specifies the authorization identifier and password in the format authorization-identifier/password. If specification of a password is not necessary (when setup is for users who do not have passwords), the password can be omitted.

Regardless of whether the specification uses upper case or lower case characters, the password is handled as upper case. However, if lower-case characters are enclosed in quotation marks, the password is handled as lower-case characters.

Note

• When you use OpenTP1, do not register PDUSER as a system environment variable. If you do, abort code psti0rf will be output when OpenTP1 starts, and HiRDB will quit.

Notes

1. When a UAP under OpenTP1 is used as the client, specify the authorization identifier and password in the format 'authorization-identifier/password'. If you wish to use lower-case alphabetic characters for the authorization identifier and password, use the format '"authorization-identifier"/"password"'.
2. When you omit the password and specify only the authorization identifier, entry of a password may be requested, depending on the utility. In such a case, use the format authorization-identifier/password to specify a character string for the password. If the command is executed from the UAP (COMMAND EXECUTE is executed), the password cannot be omitted.
3. If the directory server linkage facility is used, the directory server performs user ID and password management and user authentication when HiRDB is connected (HiRDB does not perform these tasks). Therefore, you must specify the user ID and password registered in the directory server. If you specify a user ID and password that are not registered in the directory server and then execute a utility, an error will occur during user authentication.

<character string> ((30 characters)) <<Unknown>>

This environment variable specifies identification information about the UAP that will access the HiRDB system (that is, a UAP identifier). This name is used to identify the UAP being executed.

The name specified in this environment variable is displayed as the UAP name in the following information:

• Display result of the pdls command
• SQL trace file
• Connection user information file (%PDDIR%\spool\cnctusrinf)

Note

• If non-alphanumeric characters are specified in the UAP identification name, the pdcancel command may not be executed. For this reason, only alphanumeric characters should be specified for name.
• Do not use the following character strings in the UAP identification names:
  Character string that begins with pd^*
  Character string that begins with hds
  Character string that begins with 0
  

^* If a character string that begins with pd is specified in the identification name of a UAP, that UAP may not be monitored by the skipped effective synchronization point dump monitoring facility.

This environment variable specifies the character code classification to be used in the UAPs to be processed by the preprocessor. For the Windows version, only SJIS can be specified, and SJIS is assumed when this environment variable is omitted.

SJIS

ja_JP.SJIS (ja_JP or ja_JP.PCK) is set as the character code classification. For Linux, use PDLANG to set SJIS.

CHINESE

chinese_s is set as the character code classification.

UJIS

ja_JP.EUC (ja_JP.eucJP or ja) is set as the character code classification.

C

Single-byte character codes are set as the character code classification.

During UAP preprocessing, the character code classification is determined as shown in the following table.

PDCLTLANG	Client operating system
	HP-UX	Solaris	AIX 5L	Linux	Windows
SJIS	ja_JP.SJIS	ja_JP.PCK	ja_JP	Error	ja_JP.SJIS
CHINESE	chinese-s	chinese-s	chinese-s	chinese-s	EUC Chinese character code (GB2312)
UJIS	ja_JP.eucJP	ja	ja_JP	ja_JP.eucJP	Error
C	C	C	C	C	C
No setting*	ja_JP.SJIS	ja	ja_JP	ja	ja_JP.SJIS
Other	Error	Error	Error	Error	Error

^* If a character code was set in the LANG environment variable during preprocessing, that character code is assumed.

The following table shows whether connection is possible based on the character code classification combination between the server and the client.

Character code classification of client1	Character code classification of server
	SJIS	CHINESE	UJIS	C
SJIS	C
CHINESE		C
UJIS			C
C	C2		C2	C

C: Can be connected.

: Cannot be connected.

¹ A Windows client can connect with any character code classification used in a server. A VOS3 system client can connect with the server when the character code classification in the server is SJIS.

² Connection is possible when the server is set to the default character code classification. For Solaris and Linux, the default character code classification is UJIS. For other operating systems, the default character code classification is SJIS.

This environment variable is not valid in the Windows environment.

This environment variable specifies that the character code classification for UAP execution is Unicode (UTF-8) or the EUC Chinese character code. In Linux, this environment variable specifies SJIS if SJIS is to be used. When this environment variable is omitted, the specification value of the LANG environment variable is assumed.

When ANY is specified, the client can connect to a server that uses any character codes. However, the client (application) needs to be aware of the character codes used by the connected server for data operations and creation of SQL statements.

This environment variable specifies whether a database update log is to be collected when UAPs are executed.

ALL

Execute UAPs in the log collection mode.

When ALL is specified, the error correction operation becomes simple, but a significant amount of processing time is required when a large volume of data is updated.

NO

Execute UAPs in the no-log mode.

If a UAP terminates abnormally during execution, the database updates performed by the transaction cannot be recovered. The NO option reduces processing time to the extent that no time is spent on collecting a database update log. However, backups must be made before and after UAP execution, and approval to specify NO must be obtained from the HiRDB administrator.

For details about how to execute UAPs in the no-log mode, see the HiRDB Version 8 System Operation Guide.

The following log information is collected regardless of the specification of this environment variable:

• Log information about updates to the master directory, data directory, and data dictionary RDAREAs
• Log information about updates to user RDAREA definition information

In a distributed database environment, the system collects a log of all database updates, regardless of how this environment variable is specified at the server.

This environment variable specifies whether return codes with warnings are to be accepted from the server.

YES: Accept return codes with warnings.

NO: Do not accept return codes with warnings.

When YES is specified for this environment variable, the error decision method must be changed for UAPs (including stored procedures) that process all SQLCODEs other than 0 and +100 as errors. For details about error decision methods, see 3.6 SQL error identification and corrective measures.

This environment variable specifies the maximum number of bytes used to represent one character. This environment variable is valid only when the character code classification is Unicode (UTF-8); it affects the length of the SUBSTR scalar function. For details about SUBSTR, see the manual HiRDB Version 8 SQL Reference.

Relationship with system definition

If this environment variable is omitted, the setting for the pd_substr_length operand of the system common definition is assumed.

Note

For details about when you specify this environment variable, see the pd_substr_length operand in the manual HiRDB Version 8 SQL Reference.

This environment variable specifies how character codes are to be converted when the character code classifications of the HiRDB server and the HiRDB client are different. Character code conversion can be performed only when the HiRDB client uses Shift JIS kanji codes or UCS-2, and the HiRDB server uses EUC Japanese kanji codes or Unicodes.

AUTO

The HiRDB client automatically checks the character code classification used in the HiRDB server and converts the character codes if possible. Character code conversion can be applied when the HiRDB client uses shift JIS kanji codes and the HiRDB server uses EUC Japanese kanji codes or Unicodes. When AUTO is specified, NOUSE, UJIS or UTF8 is set as the specification value.

NOUSE

Character code conversion is not used. Data is transferred without execution of character code conversion.

UJIS

The HiRDB client assumes that the HiRDB server uses EUC Japanese kanji codes, and converts character codes without checking what is used in the HiRDB server. The HiRDB client must be using shift JIS kanji codes. If the HiRDB client accepts data of variable-length character string types (VARCHAR, MVARCHAR, and NVARCHAR), it uses the number of spaces equivalent to the SQLLEN value to clear the SQLDATA area indicated by the SQL descriptor area.

UJIS2

The processing is the same as for UJIS. However, if the HiRDB client accepts data of the variable-length character string types (VARCHAR, MVARCHAR, and NVARCHAR), it does not use spaces to clear the SQLDATA area indicated by the SQL descriptor area.

UTF8

The HiRDB client uses shift JIS kanji codes and converts characters codes by assuming that the HiRDB server uses Unicodes(UTF-8). However, if the HiRDB client accepts data of variable-length character string types (VARCHAR and MVARCHAR), it uses the number of spaces equivalent to the SQLLEN value to clear the SQLDATA area indicated by the SQL descriptor area.

UTF8MS

The processing is the same as for UTF8. However, the HiRDB server uses MS-Unicodes, and the HiRDB client uses the Windows encoding character set to convert character codes.

UTF8_TXT

The processing is the same as for UTF8. However, the HiRDB client does not convert character codes for data of fixed-length character string types (CHAR and MCHAR) or variable-length character string types (VARCHAR and MVARCHAR).

UTF8_EX

Processing is the same as for UTF8. However, when the HiRDB client receives a backslash (0x5C) from the HiRDB server, it does not convert the character code, but handles it as a SJIS backslash (0x5C). If the HiRDB client receives a Unicode (UTF-8) backslash (0xC2A5), it converts it to a SJIS backslash (0x5C), in the same way as when UTF8 is specified.

When a backslash (0x5C) is entered at the HiRDB client, the character code is not converted and 0x5C is passed to the HiRDB server.

UTF8_EX2

The processing is the same as for UTF8_EX. However, when a SJIS backslash (0x5C) is entered at the HiRDB client, it is converted to a Unicode (UTF-8) backslash (0xC2A5), in the same way as when UTF8 is specified, and it is passed to the HiRDB server.

UTF8MS_TXT

The processing is the same as for UTF8MS. However, the HiRDB client does not convert character codes for data of fixed-length character string types (CHAR and MCHAR) or variable-length character string types (VARCHAR and MVARCHAR).

UCS2_UJIS

To convert character codes, the HiRDB client uses UCS-2 and the HiRDB server uses EUC Japanese kanji codes. If the HiRDB server uses character codes other than EUC Japanese kanji codes, an error is generated when the HiRDB server is connected. You can specify UCS2_UJIS only when accessing the system from a Unicode-compliant ODBC 3.0 driver or from version 02-06 or later of HiRDB SQL Executer.

UCS2_UTF8:

To convert character codes, the HiRDB client uses UCS-2 and the HiRDB server uses Unicodes (UTF-8). If the HiRDB server uses character codes other than Unicodes (UTF-8), an error is generated when the HiRDB server is connected. You can specify UCS2_UTF8 only when accessing the system from a Unicode-compliant ODBC 3.0 driver or from version 02-06 or later of HiRDB SQL Executer.

Note that the converted character code range is that of Unicodes (UTF-16), including surrogate pairs.

AUTO is specified when the character code classification of the HiRDB server cannot be identified. UJIS is specified when the character code classification of the HiRDB server can be identified as EUC Japanese kanji codes.

The following character strings are converted:

• Character strings in SQL statements
• Data codes in the SQL descriptor area that are CHAR, VARCHAR, NCHAR, NVARCHAR, MCHAR, or MVARCHAR character strings
• Column names stored in the Column Name Descriptor Area (SQLCNDA)
• Error messages stored in the SQL Communications Area
• Data type names stored in the Type Name Descriptor Area (SQLTNDA)

The following table shows the PDCLTCNVMODE settings in terms of the combination of HiRDB client and HiRDB server character codes.

Character codes used by HiRDB client application	Character codes at HiRDB server
	SJIS	Unicode (UTF-8)	UJIS	C
SJIS	No conversion necessary	UTF8, UTF8MS, UTF8_TXT, UTF8_EX, UTF8_EX2, UTF8MS_TXT	UJIS, UJIS2	NOUSE
Unicode (UTF-8)	Cannot be specified	No conversion necessary	Cannot be specified	NOUSE
UJIS	Cannot be specified	Cannot be specified	No conversion necessary	NOUSE
UCS-2	Cannot be specified	UCS2_UTF8	UCS2_UJIS	Not needed
C	NOUSE	NOUSE	NOUSE	NOUSE

Legend:

Not needed: Cannot be specified because conversion is not possible.

No conversion: Does not need to be specified because code conversion is not necessary.

Tables 6-26 and 6-27 show the differences in character code conversions when UTF8, UTF8_EX, and UTF8_EX2 are specified.

Table 6-26 Differences in character code conversions when UTF8, UTF8_EX, and UTF8_EX2 are specified (for characters received from a HiRDB server)

Character received from HiRDB server (Unicode (UTF-8))	PDCLTCNVMODE setting	Character code after HiRDB client conversion (SJIS)
0x5C (backslash)	UTF8	0x815F (double-byte backslash)
	UTF8_EX	0x5C (\ symbol)*
	UTF8_EX2
0xC2A5 (\ symbol)	UTF8	0x5C (\ symbol)
	UTF8_EX
	UTF8_EX2

* Character code is not converted.

Table 6-27 Differences in character code conversions when UTF8, UTF8_EX, and UTF8_EX2 are specified (for characters entered at the HiRDB client)

Character entered at HiRDB client (SJIS)	PDCLTCNVMODE setting	Character code after HiRDB client conversion (Unicode (UTF-8))
0x5C (\ symbol)	UTF8	0xC2A5 (\ symbol)
	UTF8_EX	0x5C (backslash)*
	UTF8_EX2	0xC2A5 (\ symbol)

* Character code is not converted.

Notes

• If a data string contains 2-byte external characters, they are replaced with full-sized number signs (#), except when the client environment definitions PDCLTGAIJIDLL and PDCLTGAIJIFUNC are specified in the client. EUC 3-byte external characters cannot be used.
• Single-byte katakana characters are 1-byte codes in the shift JIS kanji codes and 2-byte codes in the EUC Japanese kanji codes. Therefore, if a character string contains single-byte katakana characters, the data length changes when the character codes are converted. If a character string received from the server contains single-byte katakana characters, the character string becomes shorter after conversion. If a character string to be sent to the server contains single-byte katakana characters, the character string becomes longer after conversion.
  In the Unicodes, characters that are not ASCII (0x0-0x7f) characters are expressed as 2- to 4-byte characters. Therefore, the data length changes when the character codes are converted. If a character string received from the server contains non-ASCII characters, the character string becomes shorter after conversion. If a character string to be sent to the server contains non-ASCII characters, the character string becomes longer after conversion.
  When the character string length changes, the following processing takes place:
  

1. When data codes set in the SQL descriptor area are CHAR or MCHAR character strings:
   If the character string length becomes shorter, the HiRDB client pads the converted codes with single-byte spaces (0x20) until the original character string length is reached. (The length becomes shorter when the HiRDB client receives a UJIS character string containing single-byte katakana characters or a Unicode character string containing non-ASCII characters from the HiRDB server.)
   If the character string length becomes longer, the HiRDB client passes the entire converted character string to the HiRDB server without truncating the character string. (The length becomes shorter when the HiRDB client passes a UJIS-converted character string containing single-byte katakana characters or a Unicode-converted character string containing non-ASCII characters to the HiRDB server.)
   Therefore, an adequate length must be allocated for the column that stores the character string. If all characters can be identified as single-byte katakana characters, the area must have a byte count that is twice the character count (for Unicodes, the area must have a byte count that is three times the character count).
   
2. When data codes set in the SQL descriptor area are VARCHAR or MVARCHAR character strings, or for character strings in SQL statements, column names stored in the Column Name Descriptor Area (SQLCNDA), error messages stored in the SQL Communications Area, or data type names stored in the Type Name Descriptor Area (SQLTNDA):
   If the character string becomes shorter, the character string length is changed to the post-conversion character string length.
   If the character string becomes longer, the character string length is changed to the post-conversion character string length.
   If all characters can be identified as single-byte katakana characters, the area must have a byte count that is twice the character count (for Unicodes, the area must have a byte count that is three times the character count).
   
3. For NCHAR or NVARCHAR character strings pointed to from the SQL Descriptor Area (data codes are NCHAR or NVARCHR character strings)
   Because single-byte katakana characters cannot be used, the length remains unchanged, even after conversion.
   

• If a CHAR or VARCHAR column is used to store binary data, the character code conversion process may produce unexpected conversions when the column is accessed. In this case, disable character code conversion (specify NOUSE in PDCLTCNVMODE).
• Character code conversion cannot be executed on BLOB-type data. For example, if a BLOB column is being used to store text data, have the UAP execute the character code conversion.
• The following two methods are used for character mapping between shift JIS kanji codes and Unicodes:
  JIS method
  This method conforms to the mapping method defined by JIS X 0221. The JIS method is used when a value other than UTF8MS or UTF8MS_TXT is specified in PDCLTCNVMODE.
  Conversion targets: Shift JIS to JIS X0221
  Kanji scope: JIS level-1 kanji set and JIS level-2 kanji set
  MS method
  This method conforms to the mapping method defined by Microsoft. The MS method is used when UTF8MS or UTF8MS_TXT is specified in PDCLTCNVMODE.
  Conversion targets: Windows signed character set to MS-UNICODE
  Kanji scope: JIS level-1 kanji set, JIS level-2 kanji set, IBM expansion kanji, NEC-selected IBM expansion kanji, NEC special characters
  
• Note that the shift JIS kanji codes and the Windows signed character set have different external character code ranges.
• To apply the MS method, which can handle more kanji characters, specify UTF8MS or UTF8MS_TXT in PDCLTCNVMODE. Before you use the MS method, make sure that you fully understand the problems that may occur as a result of mapping differences.
• A user-defined external character conversion DLL file for UJIS cannot be applied directly to Unicode external character conversion. To execute Unicode external character conversion, you must use a user-defined external character conversion DLL file to which a Unicode external character conversion function has been added.

Notes when the client uses UCS-2 character codes

• Error messages (SQLERRMC) set in the SQL Communications Area may exceed 254 bytes, depending on the character code conversion. In such cases, a maximum of 254 bytes are set for column names and type names and any excess is truncated.
• To receive CHAR, MCHAR, VARCHAR, or MVARCHAR data, the SQL description area data length requires twice the maximum definition length.
• Data cannot be received if it exceeds the following applicable value after character code conversion:
  30,000 bytes for CHAR, NCHAR, and MCHAR
  32,000 bytes for VARCHAR, NVARCHAR, and MVARCHAR
  Therefore, data stored in the server may not be able to be searched if fixed-length character type data exceeds 15,000 bytes, and if variable-length character type data exceeds 16,000 bytes.
  
• The length of data specified by input parameters cannot exceed the following values:
  32,000 bytes for VARCHAR, NVARCHAR, and MVARCHAR
  32,000 bytes for VARCHAR, NVARCHAR, and MVARCHAR
  
• When data codes set in the SQL description area are CHAR, MCHAR, VARCHAR, or MVARCHAR and the character strings are sent to the server, the character string length is changed to the post-conversion character string length (for the fixed-length, SQLLEN is changed to the post-conversion character string length).
• Do not add BOM at the beginning of UCS-2 character strings. Such character strings with BOM are not converted correctly. The UCS-2 byte order is processed as the byte order of the host that runs the program.

<character string>

This environment variable is valid only in the Windows version.

This environment variable specifies the name of the DLL file for user-defined external character conversion. This environment variable is valid only if a value other than NOUSE is specified for PDCLTCNVMODE. If this environment variable is omitted, double-byte external characters are converted to double-byte number signs (#).

<character string>

This environment variable is valid only in the Windows version.

This environment variable specifies the name of the user-defined external character conversion function. This environment variable is valid only when PDCLTGAIJIDLL is specified.

Descriptive format for a user-defined external character conversion function

The descriptive format for a user-defined external character conversion function is as follows:

  _declspec(dllexport)1 WINAPI2 user-defined-external-character-conversion-function-name (
     long        direct,
     unsigned char far *instr,
     unsigned char far *outstr) ;

¹ The DLL declaration format differs according to the compiler being used. Comply with the DLL format of the compiler being used.

² The export function name (user-defined external character conversion function name) of the created DLL differs depending on which compiler is used. Use one of the following methods to check which export function name to specify:

• During DLL creation, specify the project settings so that a MAP file is output. Then check the export function name from the MAP file.
• Use the dumpbin command (dumpbin /exports DLL-name) of Microsoft Visual C++ to check the export function name.

Input

direct

Indicates the conversion direction. A value from 1 to 6 is set.

1: Data conversion from the HiRDB client to the HiRDB server

2: Data conversion from the HiRDB server to the HiRDB client

3: Data conversion from the HiRDB client to the HiRDB server (for Unicodes)

4: Data conversion from the HiRDB server to the HiRDB client (for Unicodes)

5: Data conversion from the HiRDB client UCS-2 to the HiRDB server UJIS

6: Data conversion from the HiRDB server UJIS to the HiRDB client UCS-2

Note

When the Gaiji conversion DLL passes a Unicode, the data is converted to 2 bytes of UCS-2 format data. Conversion to data in UTF-8 format is performed by a library.

instr

Indicates the pointer to the external character storage area for characters to be converted. The character string size is fixed to two bytes.

instr[0] = First byte of external character to be converted

instr[1] = Second byte of external character to be converted

outstr

Indicates the pointer to the post-conversion external character storage area. The character string side is fixed to two bytes. A calling side HiRDB client library secures or releases the area.

outstr[0] = First byte of character code (external character) after conversion

outstr[1] = Second byte of character code (external character) after conversion

Even though code conversion could not be performed, set an appropriate value as the converted value (the passed value is used unconditionally).

For UCS-2 (Unicodes) external character codes, byte columns need to be returned with a big endian byte column. For example, for , set 0x67 for the first byte and 0x71 for the second byte.

Output

*outstr

Stores the converted character string.

Note

The following table shows the character code combinations that can be specified for *instr and *outstr.

direct	instr	outstr	PDCLTCNVMODE
1	External character codes of the shift JIS kanji codes	External character codes of the EUC Japanese kanji codes	UJIS or UJIS2
2	External character codes of the EUC Japanese kanji codes	External character codes of the shift JIS kanji codes	UJIS or UJIS2
3	External character codes of the shift JIS kanji codes	Unicode external character codes	UTF8 or UTF8_TXT
	External character codes of the Windows signed character set	MS-Unicode external character codes	UTF8MS or UTF8MS_TXT
4	Unicode external character codes	External character codes of the shift JIS kanji codes	UTF8 or UTF8_TXT
	MS-Unicode external character codes	External character codes of the Windows signed character set	UTF8MS or UTF8MS_TXT
5	External Unicode character codes	External EUC Japanese kanji character codes	UCS2_UJIS
6	External EUC Japanese kanji character codes	External Unicode character codes	UCS2_UJIS

The following table shows the external code ranges for each character code classification.

Character code	First byte	Second byte
Shift JIS kanji codes	0xf0-0xfc	0x40-0x7e
		0x80-0xfc
Windows signed character set	0xf0-0xfa	0x40-0x7e
		0x80-0xfc
EUC Japanese kanji codes	0xf5-0xfe	0xa1-0xfe
Unicodes or MS-Unicodes*	0xe0-0xf8	0x00-0xff

^* Because Microsoft has assigned its own characters to codes 0xe000 to 0xe757, and 0xf8f0 to 0xf8ff, the user-defined external character conversion DLL is not called for these external character codes.

<letter> ((1 character))

This environment variable specifies a client group name when the connection frame guarantee facility for client groups is used. The client group name that was specified in the pdcltgrp operand of the system definition is specified with an uppercase letter. Even if a lowercase letter is specified, the system assumes that an uppercase letter was specified.

If the pdcltgrp operand of the system definition is not specified, or if this environment variable specifies a client group name that is not specified by the pdcltgrp operand, the specification for this environment variable becomes invalid. For details about the connection frame guarantee facility for client groups, see the HiRDB Version 8 System Operation Guide.

This environment variable is valid when the HiRDB client connects to a HiRDB server of version 06-02 or later. This environment variable is specified when the number of TCP ports used for communication to the server is to be reduced.

According to the TCP protocol specifications, after a TCP connection ends, the TCP port may switch to TIME_WAIT status for a fixed period of time (1 to 4 minutes) during which it cannot be used in a new connection. The TIME_WAIT-status port is used by the TCP connection that was completed. When PDTCPCONOPT is set to 1, the number of TIME_WAIT-status TCP ports that occur in the HiRDB client and server can be reduced.

0

Do not reduce the number of TCP ports that are used in communication with the HiRDB server.

1

Reduce the number of TCP ports that are used in communication with the HiRDB server.

The following table shows the number of TIME_WAIT-status TCP ports that can be eliminated when 1 is specified.

UAP execution environment	Connection mode from UAP to HiRDB server	Communication type	Environment variable effect	Number of TCP ports in TIME_WAIT status that can be eliminated1
				Client2	Server
OLTP	Normal connection	Communication for connecting UAP to HiRDB server3	V	1	1
		Failure recovery communication from OLTP to HiRDB server4, 5	V	1	1
	High-speed connection and FES host direct connection	Communication for connecting UAP to HiRDB server	I
		Failure recovery communication from OLTP to HiRDB server4, 5	V	1	1
Other	Normal connection	Communication for connecting UAP to HiRDB server3	V	1	1
	High-speed connection and FES host direct connection	Communication for connecting UAP to HiRDB server	I

Legend:

V: Becomes valid when 1 is specified for PDTCPCONOPT.

I: Becomes invalid even if 1 is specified for PDTCPCONOPT.

: Does not apply.

¹ The number of TCP ports that are switched to TIME_WAIT status depends on the timing when packets that participate in the termination protocol for TCP connections arrive. Therefore, the number changes according to the network status. Consequently, the number of TIME_WAIT-status TCP ports that can be deleted may change.

² During failure recovery communication from OLTP, the OLTP failure recovery process becomes the client.

³ Some of the TCP ports that are used when the UAP connects to the HiRDB server are switched to TIME_WAIT status.

⁴ Failure recovery communication from OLTP to the HiRDB server takes place when the OLTP failure recovery process calls an X/Open-compliant XA interface function (such as xa_open, xa_recover, or xa_rollback) to recover a transaction interrupted by a failure. At this time, some of the TCP ports used in XA interface execution are switched to TIME_WAIT status. The number of TIME_WAIT-status TCP ports that can be eliminated is the number that can be eliminated when one XA interface function is called. Therefore when n XA interface functions are called, n times that number can be eliminated.

⁵ The method of specifying the environment variable for the OLTP failure recovery process differs in each OLTP environment. For example, in OpenTP1, the environment variable is specified in a transaction service definition.

Application standard

Specify 1 in PDTCPCONOPT if either of the following conditions is satisfied:

• If the number of TCP ports that the OS allocates automatically is less than 5000 (the TCP port range differs according to the OS)
• If PDXAMODE is set to 1 in a UAP under OpenTP1

However, if the specification value of the pd_max_users operand in the system definitions is less than 100, or if the pd_registered_port operand is specified, you do not need to specify 1 even if one of the above conditions is satisfied.

Notes

1. If the version of the HiRDB server to be connected is earlier than Version 06-02, do not specify 1 in PDTCPCONOPT. If you specify 1, a shortage may occur in the communication sockets that the HiRDB server can use.
2. When you specify 1 in PDTCPCONOPT, you must check and, if necessary, revise the value of the maxfiles_lim operating system parameter in the HiRDB server. For details about estimating values for operating system parameters, see the HiRDB Version 8 Installation and Design Guide.

This environment variable specified whether or not the automatic reconnect facility is to be used.

For details about the automatic reconnect facility, see 4.16 Automatic reconnect facility.

YES

Use the automatic reconnect facility.

When this facility is used, it automatically reconnects the HiRDB client to the HiRDB server if the connection is disconnected because of a service process failure, system switchover, or network failure.

NO

Do not use the automatic reconnect facility.

Application standard

Apply the automatic reconnect facility if the HiRDB server is executing the system reconfiguration command (pdchgconf) or updating to the HiRDB update version (pdprgcopy or pdprgrenew). If the automatic reconnect facility is used in this situation, the HiRDB client can continue processing without returning an error to the UAP, even if the connection with the HiRDB server is disconnected.

Notes

1. Use PDRCCOUNT and PDRCINTERVAL to specify the number of CONNECT statement retries and the retry interval when reconnection is executed.
2. The time during which the automatic reconnect facility operates with SQL statements other than the CONNECT statement is monitored based on the PDCWAITTIME time. If the PDCWAITTIME time is exceeded, automatic reconnect processing is aborted.
3. If automatic reconnect fails, an error indicating the cause is returned to the UAP.
4. If an application uses an X/Open-compliant API to access the HiRDB server, the PDAUTORECONNECT specification is ignored and NO is always assumed.
5. If one of the following conditions is satisfied, the automatic reconnect facility is enabled only when the CONNECT statement is executed:
   The HiRDB server version is earlier than 07-00.
   The XDM/RD E2 connection facility is being used.
   The XDM/RD E2 version is 10-02 or earlier.
   

<unsigned integer> ((1-200)) <<5>>

This environment variable specifies the number of times the CONNECT statement is retried during reconnection by the automatic reconnect facility. This environment variable becomes effective when PDAUTORECONNECT=YES is specified.

<unsigned integer> ((1-600)) <<5>> (seconds)

This environment variable specifies the CONNECT retry interval at which the automatic reconnect facility executes reconnect processing. The interval is specified in units of seconds. This environment variable becomes effective when PDAUTORECONNECT=YES is specified.

<identifier> ((up to 8 characters))

This environment variable specifies the name of the UAP environment definition file that defines the execution environment if the UAP is to be executed in a separate environment. Specifying PDUAPENVFILE allows you to switch the execution environment of each UAP.

For details about UAP environment definitions, see the manual HiRDB Version 8 System Definition.

If the UAP environment definitions contain an error, a definition error occurs during CONNECT execution. If the UAP environment definition file does not contain any definitions, the PDUAPENVFILE specification is ignored.

Uppercase and lowercase characters in the UAP environment definition file name are not discriminated in HiRDB for Windows systems. Note therefore that files that have the same name except for case differences are treated as the same file.

This environment variable specifies whether or not the LRU method for global buffers is to be changed for each UAP in an OLTP environment.

YES:

Use the LRU method.

NO:

Do not use the LRU method. In this case, pages that do not hit the buffer become the target for being flushed out of the global buffer regardless of the access frequency when the global buffer becomes full. For that reason, the number of pages to be cached in the global buffer can be minimized.

Application standard

You will usually omit this environment variable (use the LRU method). In the OLTP environment, when a large number of searches are performed or a large number of UAP updates are executed using the global buffer, the most recent contents cached in the global buffer are flushed out, which may cause a temporary drop in system performance. In order to avoid this, specify PDDBBUFLRU=NO for a UAP that performs a large volume of searches or updates in an OLTP environment.

Notes

1. Pages accessed by a UAP that does not use the LRU method are subject to being flushed out of the global buffer regardless of the access frequency. For that reason, a UAP that does not use the LRU method could cause a drop in response performance, due to an increase in the number of inputs/outputs caused by the drop in the buffer hit ratio.
2. SQL processing by a UAP secures 1 to 4 global buffer sectors simultaneously. Therefore, even though the UAP does not use the LRU method, pages cached in the global buffer for each UAP may be flushed out from the 1 to 4 global buffer sectors.
3. If the LRU method is not used for a UAP to be updated, writing to the database becomes frequent. For that reason, log output triggers occur frequently compared to when the LRU method is used and the amount of output log information increases. In such a case, a lack of system log file capacity may occur, so you should take one of the following actions:
   Re-evaluate the size of the system log file
   Specify NO in the PDDBLOG operand of the client environment definition.
   The formula is shown below for estimating the log size when the LRU method is not used. Note that when the system definition's pd_log_rec_leng operand is set to 1,024, the amount of output log information when the LRU method is not used can be minimized.
   Updated-GET-count^* x value-of-pd_log_rec_leng-operand
   ^* You can check the updated GET count from the DIDUC value of the UAP statistics report, or from the DIDUC value of the UAP statistical information.
   

When queuing is specified in the pd_ha_transaction operand of the system definition, this environment variable is specified when application of the transaction queuing facility is to be changed for each client. If the transaction queuing facility is not to be applied to a client, specify NO.

NO

Do not apply the transaction queuing facility during connection processing from the client.

For details about the transaction queuing facility, see the HiRDB Version 8 System Operation Guide.

<identifier> <<PDHOST specification value>>

When a UAP executes a command, this environment variable specifies the host name of the HiRDB Control Manager-Agent to be connected. The COMMAND EXECUTE statement of SQL is used when a UAP executes a command.

When a UAP executes a command, the HiRDB Control Manager-Agent actually executes that command.

For HiRDB/Parallel Server, the host name of the server machine that contains the system manager is specified.

In addition to the host name, you can specify the FQDN or the IP address. The specification methods are as follows:

Host name

The host name that was specified in the pdunit -x operand of the system common definition must be specified.

Example:

PDHOST=host1

FQDN:

The FQDN is comprised of the host name and domain name of a HiRDB server, separated by periods.

Example:

PDASTHOST=host1.soft.hitachi.co.jp

IP address

The IP address is specified with a decimal number that has each group of 3 digits separated with a period.

Example:

PDHOST=172.18.131.34

System switchover without IP address inheritance

Specify the host names of both the primary system and the secondary system. If you specify only the host name of the primary system, you must change the specification of this environment variable after system switching occurs. After system switching, change the host name to that of the new primary system.

<unsigned integer> ((5001-49999))

This environment variable specifies the port number of the HiRDB Control Manager - Agent to be connected when a command is executed from a UAP.

Specify a port number that is registered in the services file (for the UNIX version, /etc/services; for the Windows version %windir%\system32\drivers\etc\services).

<identifier> ((4 characters))

When a command is executed from a UAP, this environment variable specifies the HiRDB identifier of the HiRDB server being managed by the HiRDB Control Manager - Agent to be connected. Specify the HiRDB identifier with the pd_system_id operand of the system definitions.

<<PDUSER specification value>>

This environment variable specifies the user name and password for the OS that runs commands executed from a UAP. This must be the user name and password for an OS that has the execution privilege for the commands. Specify in the format user-name/password.

If a password specification is not required (i.e., the setting is for a user without a password), the password can be omitted.

The user name and password for an OS are handled as upper case characters regardless of whether the specification is in upper case or lower case. However, if lower-case characters are enclosed in quotation marks, they are handled as lower-case characters.

<unsigned integer> ((0, 6-43200)) <<0>> (minutes)

When a command is executed from a UAP, this environment variable specifies, the maximum time that the HiRDB client waits for a response from the HiRDB Control Manager - Agent after it sends a request to the server.

If 0 is specified, the HiRDB client continues to wait until a response is returned from the HiRDB Control Manager - Agent.

If there is no response from HiRDB Control Manager - Agent after the specified amount of waiting time has elapsed, an error is returned to the client (UAP). If a command in the UAP is still processing at that time, either HiRDB Control Manager - Agent or the command must be canceled.

<unsigned integer> ((0, 4096-2000000000)) (bytes)

When a command is executed from a UAP, this environment variable specifies, the size of the command trace output file.

If 0 is specified, the maximum file size is assumed, and a command trace that exceeds the maximum size is not output. If a value from 4,096 to 2,000,000,000 is specified, the specified value becomes the file size, and the output destination switches when the file size exceeds the specified value. If this environment variable is omitted, a command trace is not collected.

For details about command traces, see 10.1.5 Command trace facility.

Relationship with other environment variables

The command trace output file is created in the directory specified by PDCLTPATH. If PDCLTPATH is omitted, the file is created in the current directory when the UAP is executed. (If the UAP is executed from OpenTP1, the file is created under the OpenTP1 installation directory \tmp\home\server-namexx.)

This environment variable specifies the inter-process communication method to be used when the server and client are found in the same host.

MEMORY

Use the memory for inter-process communication. This is called the inter-process memory communication facility.

DEFAULT

Use the default communication method (TCP/IP or PIPE) in each platform for inter-process communication.

Notes

1. If the client and server are not in the same host, the PDIPC specification is ignored (the system assumes that DEFAULT was specified). In this case, the connection process may take longer.
2. If you use the XA interface library for multiple threads (pdcltxm.dll for Windows clients, and libzcltxk.sl(so) or libzcltyk.sl(so) for UNIX clients) to access HiRDB with the XA interface, and a UAP running on TPBroker or Weblogic Server is set as the client, the specification of this environment variable is ignored, and it is assumed the DEFAULT specification was specified for PDIPC.
3. If PDIPC=MEMORY is specified in UNIX-version clients, HiRDB allocates a common memory size equal to values specified for PDSENDMEMSIZE and PDRECVMEMSIZE, for each client connection. Consequently, a common memory shortage may occur if multiple clients are executed concurrently. To avoid a memory shortage, consider the common memory size that can be used when specifying PDSENDMEMSIZE and PDRECVMEMSIZE.
4. If PDIPC=MEMORY is specified, the specification for PDCLTRCVADDR is ignored.
5. If PDIPC=MEMORY is specified, and concurrently p, r, or a is specified in PDUAPREPLVL or PDWRTLNFILSZ is specified, the specification for PDIPC becomes invalid.

<unsigned integer> ((4-2097152)) <<16>> (kilobytes)

This environment variable specifies the data storage area size, in multiples of 4 KB, to be used when the client sends data to the server, when the inter-process memory communication facility is used. This environment variable becomes effective when PDIPC=MEMORY is specified.

If the specified value is not a multiple of 4, the value is rounded up to multiple of 4.

If data larger than the size specified here is sent, the inter-process memory communication facility cannot be used. (The communication method for PDIPC=DEFAULT is used.)

Estimation method

Estimate the value to be specified for this environment variable based on the following formula:

specification-value (bytes) = (400 + 16 number-of-retrieved-columns + 16 number-of-?-parameters + SQL-statement-length) 4096 4

The value calculated with this formula differs from the data size that is actually sent during communication.

<unsigned integer> ((4-2097152)) <<32>> (kilobytes)

This environment variable specifies the data storage area size, in multiples of 4 KB, to be used when the client receives data from the server, when the inter-process memory communication facility is used. This environment variable becomes effective when PDIPC=MEMORY is specified.

If the specified value is not a multiple of 4, the value is rounded up to a multiple of 4.

If data larger than the size specified here is received, the inter-process memory communication facility cannot be used. (The communication method for PDIPC=DEFAULT is used.)

Estimation method

Estimate the value to be specified for this environment variable based on the following formula:

specification-value (bytes) = (600 + 25 number-of-retrieved-columns + column-data-length) 4096 4

If the data type of column-data-length is VARCHAR, replace column-data-length with structure-length in the preceding formula. If the HiRDB client accepts array FETCH statements or repetition columns, use column-data-length number-of-array-columns or column-data-length number-of-repetition-column-elements.

If PDBLKF is specified, calculate the value based on the following formula:

specification-value (bytes) = (600 + 19 number-of-retrieved-columns + (7 number-of-retrieved-columns + column-data-length) PDBLKF-value) / 4096 4

The value calculated with this formula differs from the data size that is actually sent during communication.

<unsigned integer> ((0-65535)) <<0>> (seconds)

This environment variable specifies the maximum time that the HiRDB client waits for a response from the HiRDB server after sending a request to the HiRDB server. Specify PDCWAITTIME when implementing interval monitoring of long running SQL statements.

Notes

1. When 0 is specified, the HiRDB client continues to wait until it receives a response from the HiRDB server. If the HiRDB client does not receive a response from the HiRDB server before the maximum wait time elapses, the HiRDB client returns an error to the UAP. If this occurs during transaction processing, the process in the HiRDB server is cancelled.
2. When 0 is specified, the no response status may be set in the HiRDB client if one of the following errors occurs:
   Communication error (communication error between a HiRDB client and a HiRDB server or between two HiRDB servers (including temporary errors))
   Process not responding because of a disk error
   Hitachi therefore recommends that you specify a nonzero value that is larger than maximum SQL execution time. If the UAP executes an SQL statement for which lock-release wait occurs, you must also consider the pd_lck_wait_timeout operand value in the system definitions when determining the PDCWAITTIME value.
   

<unsigned integer> ((0-65535)) <<600>> (seconds)

This environment variable specifies the maximum time that the HiRDB server waits for the next request from the HiRDB client to arrive after returning a response to the previous request from the HiRDB client. This function monitors the time during transaction processing (from startup of SQL execution to commit or rollback). The monitoring time is reset when the request from the HiRDB client arrives at the HiRDB server.

If the HiRDB server does not receive a request within the specified amount of time, it assumes that an error occurred in the UAP and rolls back the current transaction. The HiRDB server also severs the connection with the HiRDB client without notifying the HiRDB client.

If 0 is specified, the HiRDB server continues to wait until it receives a request from the HiRDB client.

Specify PDSWAITTIME to avoid process survival.

Notes

1. When the block transfer facility (PDBLKF) is used, the HiRDB client executes FETCH statement processing until all rows that were block-transferred from the HiRDB server are processed. The HiRDB client does not send another request to the HiRDB server until the FETCH statement processing ends. Therefore, if the block transfer facility is used, the value specified in this environment variable must include the amount of time the FETCH statement requires to process the number of blocks that will be transferred.
2. This environment variable must be specified for the operating mode in which the client is a UAP under OLTP. Otherwise, the default value of 600 seconds is used, and connections may be severed inappropriately.

<unsigned integer> ((0-65535)) (seconds)

This environment variable specifies the maximum time that the HiRDB server waits for the next request from the HiRDB client to arrive after returning a response to the previous request from the HiRDB client. This function monitors the time outside transaction processing (i.e., outside the interval from start of SQL execution to commit or rollback). The monitoring time is reset when the request from the HiRDB client arrives at the HiRDB server.

If the HiRDB server does not receive a request within the specified amount of time, it assumes that an error occurred in the UAP and severs the connection with the HiRDB client without reporting the disconnection to the HiRDB client.

If 0 is specified, the HiRDB server continues to wait until it receives a request from the HiRDB client.

Specify PDSWATCHTIME to avoid process survival.

Notes

1. This environment variable must be set to 0 for the operating mode in which the client is a UAP under OLTP, or if the UAP always connects to the HiRDB server regardless of whether a transaction is being processed.
2. If the HiRDB server disconnects the connection with the HiRDB client, it does not report the disconnection to the HiRDB client.

Relationship to the system definition

If this environment variable is omitted, the HiRDB server uses the value that was specified in the pd_watch_pc_client_time operand of the system definition and monitors processing until the start of a transaction. For details about the pd_watch_pc_client_time operand, see the manual HiRDB Version 8 System Definition.

Relationship with other environment variables

Figure 6-2 shows the relationships among PDCWAITTIME, PDSWAITTIME, and PDSWATCHTIME.

Figure 6-2 Relationships among PDCWAITTIME, PDSWAITTIME, and PDSWATCHTIME

This environment variable specifies the output time of the SQL runtime warning information file when the SQL runtime warning output facility is used.

The SQL runtime warning output facility outputs an SQL runtime warning information file and a warning message (KFPA20009-W) if the runtime of an SQL statement exceeds a fixed time. For details about the SQL runtime warning output facility, see the HiRDB Version 8 System Operation Guide.

Use one of the following methods to specify the output timing of the SQL runtime running information file:

Percentage of the PDCWAITTIME specification value (when the decimal point is not specified)

<unsigned integer> ((0-99)) (%)

Specify the timing as a percentage of the PDCWAITTIME specification value. For example, if you specify 100 (seconds) in the PDCWAITTIME operand and 90 (%) in PDCWAITTIMEWRNPNT, HiRDB checks the SQL runtime after it executes an SQL statement. If the result indicates that the SQL runtime was 90 seconds or longer but less than 100 seconds, HiRDB outputs warning information.

Percentage of the PDCWAITTIME specification value (when the decimal point is specified)

<unsigned decimal number> ((0-99.999999)) (%)

Specify the timing as a percentage (including the decimal point) of the PDCWAITTIME specification value.

Output time of SQL runtime warning

<unsigned decimal number> ((0-PDCWAITTIME)) (seconds)

Specify the output time for the SQL runtime warning. (For example, if the output time is 60 seconds, specify PDCWAITTIMEWRNPNT=60sec.) A decimal point can be specified in the time specification. The specified value must be less than the PDCWAITTIME specification value.

Relationship with system definitions

When PDCWAITTIMEWRNPNT is omitted, the specification value of the pd_cwaittime_wrn_pnt operand in the system definitions is assumed. For details about the pd_cwaittime_wrn_pnt operand, see the manual HiRDB Version 8 System Definition.

This environment variable specifies whether the facility that periodically sends packets from the HiRDB client to the HiRDB server is to be used.

This environment variable is effective only when the multi-thread versions of the HiRDB client libraries are used.

If a value other than 0 is specified, one packet sending thread is generated for each connection with HiRDB. The packet send interval can be specified in PDKATIME.

If an application uses an X/Open-compliant API to access HiRDB, the actual PDKALVL specification is ignored and 0 is always assumed.

0

Do not use the facility that sends packets periodically.

1

Use the facility that sends packets periodically. The packet transmission thread sends packets to the connection path with the HiRDB server at fixed time intervals.

HiRDB does not reset the PDSWAITTIME and PDSWATCHTIME monitoring times that the HiRDB server uses for time monitoring.

If the HiRDB client and the HiRDB server are installed in the same machine, do not specify 1.

2

Use the facility that sends packets periodically. The packet transmission thread sends packets to the connection path with the HiRDB server at fixed time intervals and receives packets returned from the HiRDB server.

HiRDB resets the PDSWAITTIME and PDSWATCHTIME monitoring times that the HiRDB server uses for time monitoring.

If a packet from the HiRDB server is not returned within the PDCWAITTIME time specified in the client environment definitions, the connection is invalidated. If this happens, the timeover SQLCODE (-732) is returned to the application when the SQL execution thread executes the next SQL statement.

If the SQL execution thread receives an SQL request from the application while the packet transmission thread is waiting for a response from the HiRDB server, the SQL execution thread is set to wait status until the packet transmission thread receives a response from the HiRDB server. Consequently, the SQL runtime may be delayed. Also, because the select() system call is issued during the reception wait period, the CPU usage is higher than when 1 is specified as the setting value. If the PDSWAITTIME and PDSWATCHTIME monitoring times that the HiRDB server uses for time monitoring do not need to be reset, Hitachi recommends that you specify 1 as the setting value.

Application standard

Network management applications such as routers and firewalls sometimes feature an idle-time monitoring facility that disconnects the connection if there is no packet flow for a fixed period of time. By specifying a value other than 0 in PDKALVL, you can retain the HiRDB connection and prevent a Web application waiting for a service request from using the network management application to improperly disconnect the HiRDB connection.

When the time-monitoring environment variables (PDSWAITTIME and PDSWATCHTIME) are set to infinite in the HiRDB server, uncompleted processes may still remain in the HiRDB server if the HiRDB client machine fails or a network failure occurs. By specifying 2 in PDKALVL, you can avoid connection disconnect by the time monitoring facilities of the HiRDB server without having to set the time-monitoring values in the HiRDB server to infinite.

Application examples

1. If the following conditions apply, specify 1 in PDKALVL and specify a time that is shorter than the firewall monitoring time in PDKATIME. (For example, if the firewall monitoring time is 1,200 seconds, specify 1,000 seconds in PDKATIME.)
   
   • The Web application issues SQL execution requests to the DB server at irregular times, and no SQL statements are executed for long periods of time.
   • A firewall has been set up between the Web server and the DB server, and the firewall disconnects the connection if there is no packet flow for a fixed period of time.
2. If the following conditions apply, specify 2 in PDKALVL and specify a time that is shorter than the PDSWATCHTIME monitoring time in PDKATIME. (For example, if the PDSWATCHTIME monitoring time is 3,600 seconds, specify 3,000 seconds in PDKATIME.)
   
   • A connection-pooling application accesses HiRDB.
   • A connection is reused for each SQL execution request but is sometimes disconnected according to the PDSWATCHTIME monitoring time because the connection is not used for a long time.

<unsigned integer> ((60-65535)) <<3000>> (seconds)

This environment variable specifies the interval at which the HiRDB client regularly sends packets to the HiRDB server. The interval is specified in units of seconds. Specify a time that is shorter than the reset monitoring time.

PDKATIME is enabled when a value other than 0 is specified in PDKALVL.

If the SQL execution thread is executing an SQL statement when a packet is scheduled to be sent, the packet transmission thread does not send the packet and instead waits until the next transmission time.

<unsigned integer> ((0-32767)) <<2>>

This environment variable specifies the number of times the connect() system call can be retried when a WSAETIMEDOUT error (ETIMEOUT error for the UNIX version) of winsock occurs in the case of a connect() system call that is executed when a HiRDB client connects to the HiRDB server.

Benefit

When connect() system calls to the HiRDB server become too great, filling the listen queue, a WSAETIMEDOUT error or ETIMEOUT error is returned from connect(). Such a connection error can be avoided by retrying the connect() system call.

Note

In the event of a WSAETIMEDOUT error or ETIMEOUT error that occurs due to a network failure or server machine power outage, the return from the connect() system call may take some time. Therefore, if a large number of retries is set, it may take a while for a connection error to be returned to the UAP. In particular, switchover to the standby system takes a long time if a network failure or other failure occurs while the facility for system switchover without IP address inheritance is being used. In an environment that uses the facility for system switchover without IP address inheritance, the switchover time to the standby system can be shortened by setting a small number of retries.

<unsigned integer> ((0-120)) <<0>> (seconds)

This environment variable specifies the connection establishment monitoring time in nonblock mode when connection completion between the HiRDB server and client is monitored.

If 1 or a higher value is set for this environment variable, the communication between the HiRDB server and client is set to nonblock communication and completion of the connect() system call is monitored. This is called the nonblock mode. If 0 is specified, the system waits until the timeout time of the OS for the connection to be completed. This is called the block mode.

Application standard

Specify this environment variable (set the nonblock mode) if you want to avoid having the connect() system call wait several tens of seconds (the actual time depends on the OS) if a LAN failure occurs. Specifying this environment variable allows the system to detect LAN failures earlier.

Estimation method

If the specified value is too small, an unwarranted error may occur depending on the network status. Set a value higher than the value obtained from the following calculation expression:

MAX (A + 1, 8)

A:

Arrival time between the HiRDB server and client as measured by an OS command such as ping. The arrival time of ping and other commands fluctuates depending on the network load. Assume the highest load status when measuring the arrival time.

<unsigned integer> ((1-300)) <<300>> (seconds)

This environment variable specifies the maximum wait time that the HiRDB client waits for a response from the HiRDB server when it connects with the HiRDB server.

If a system switchover or a system failure occurs after the HiRDB server accepts a connection request from the HiRDB client, the HiRDB client waits only the specified amount of time for a response.

Application standard

If the system switchover facility is being used, specify this environment variable to allow applications to detect failures early. If this environment variable is specified together with PDNBLOCKWAITTIME, failures are detected even earlier.

Estimation method

If the specified value is too small, normal connection processing may result in an error if the processing takes too long because of the network status or the scheduling wait during connection processing. Set a value higher than the value obtained from the following calculation expression:

MIN (value-of-pd_max_users-operand-in-system-definition 0.2, 300)

<path name> ((path name of current directory))

This environment variable specifies the storage directory for SQL trace files and error log files created by the HiRDB client.

<unsigned integer> ((0, 4,096-2,000,000,000)) (bytes)

This environment variable specifies the size of the SQL trace file into which SQL trace information for the UAP is to be output.

If 0 is specified, the maximum file size is assumed, and an SQL trace that exceeds the maximum size is not output. If a value from 4,096 to 2,000,000,000 is specified, the specified value becomes the file size, and the output destination switches when the file size exceeds the specified value. When this environment variable is omitted, the SQL trace is not output.

For details about the SQL trace, see 10.1.1 SQL tracing.

Relationship with other environment variables

The SQL trace is output to the directory specified by PDCLTPATH. If no value is specified for PDCLTPATH, the SQL trace is output to the current directory when the UAP is started. (When the UAP is started from OpenTP1, the current directory is %PDDIR%\tmp\home\server-namexx.)

Estimation method

Calculate the size of the SQL trace file from the number of SQL statements to be collected. For each SQL statement to be collected, calculate the size of the individual rows (80 bytes) and the size of the SQL statement, and use the overall total as an estimate for the value to be specified.

<unsigned integer> ((0, 4096-2000000000)) <<65536>> (bytes)

This environment variable specifies the size of the file into which the error log of the UAP is to be output.

If 0 is specified, the maximum file size is assumed, and an error log that exceeds the maximum size is not output. If a value from 4,096 to 2,000,000,000 is specified, the specified value becomes the file size, and the output destination switches when the file size exceeds the specified value.

For details about the error log, see 10.1.2 Error logging.

Relationship with other environment variables

The error log is output to the directory specified by PDCLTPATH. If no value is specified for PDCLTPATH, the SQL trace is output to the current directory when the UAP is started. (When the UAP is started from OpenTP1, the current directory is %PDDIR%\tmp\home\server-namexx.)

This environment variable specifies SQLCODEs for which message output to the error log is to be suppressed. Up to 10 SQLCODEs can be specified.

For example, to suppress SQLCODEs -901 and -917, specify this environment variable as follows:

PDERRSKIPCODE=-901,-917

Benefits

Depending on the UAP structure, there are errors that will inevitably occur during SQL processing. If this type of error occurs frequently during normal processing, the file system may be overwhelmed. Especially for a UAP that uses an X/Open-compliant API, two error log files are created for each process. If this environment variable is specified, message output can be suppressed for specific errors, and the load on the file system can be reduced.

Application standard

Apply this environment variable if both of the following conditions are satisfied:

• Errors occur frequently because of the UAP structure.
• The cause of an error can be identified beforehand, and there is no need to investigate the cause.

When this environment variable is specified, the cause of unforeseen errors cannot be investigated. Use caution when applying this environment variable.

This environment variable specifies whether parameter information and retrieval data are to be output in the SQL trace information. For details about the output contents, see 10.1.1 SQL tracing.

YES

Output input parameter information in the SQL trace. If YES is specified, retrieval data information and the input parameters are output.

NO

Do not output parameter information in the SQL trace.

IN

Output the input parameter information in the SQL trace. This also applies to the IN and INOUT^* parameters of the CALL statement.

OUT

Output the output parameter information and retrieval data information in the SQL trace. This also applies to the OUT and INOUT^* parameters of the CALL statement.

INOUT

Output the input parameter information, the output parameter information, and the retrieval data information in the SQL trace. The INOUT parameter^* of the CALL statement is output twice.

^* Information on the INOUT parameter of the CALL statement is used only as output data.

<unsigned integer> ((4-32008)) <<256>> (bytes)

This environment variable specifies the maximum data length of the parameter information and retrieval data to be output in the SQL trace. For variable-length character string-type, BLOB-type, and BINARY-type data, the area of the character string length is included in the data length.

This environment variable is valid only when a value other than PDPRMTRC=NO is specified.

Increasing the specified value of this environment variable increases the amount of information that is output. Therefore, the size of the SQL trace file (PDSQLTRACE specification value) must also be increased.

This environment variable specifies whether troubleshooting information (pderr*.trc information) other than SQL trace information is to be output.

ERR: Output pderr*.trc information.

NONE: Do not output pderr*.trc information.

This environment variable specifies output information for the UAP statistical report. A file to which a UAP statistical report is output is called a UAP statistical report file. This environment variable becomes effective when PDCLTPATH is specified.

If this environment variable is omitted, only SQL trace information is output.

For details about UAP statistical reports, see 10.1.4 UAP statistical report facility.

s: SQL unit information is output. SQL trace information is also output.

u: UAP unit information is output.

p: Access path information is output.

r: SQL runtime interim results are output.

a: All information is output.

s, u, p, and r can be specified in different combinations (such as su, sr, or upr). Specifying supr is the same as specifying a. If u, p, r, up, ur, pr, or upr is specified, SQL trace information is not output.

Notes

1. If the output of access path information or SQL runtime interim results is specified, the server load may increase because SQL objects are re-created even if they already exist in the buffer.
2. UAP unit information is not output for programs that use an X/Open-compliant API under OLTP.
3. If the size of the access path information or SQL runtime interim results exceeds one gigabyte, the information is not output.
4. The value 0 is displayed in the time display (for example, SQL execution time, load wait time, or CPU time) if the value is too small to be retrieved by a system call of the operating system.
5. With a HiRDB/Parallel Server, privilege check processing by the connected dictionary server is not included in the UAP unit information.
6. If you specify output of access path information or SQL runtime interim results and also specify PDIPC=MEMORY in the client environment definitions, PDIPC=DEFAULT results.

<path name> ((up to 256 bytes))

This environment variable specifies the directory in which UAP statistical report files are to be created if the files are to be created in a different directory from the directory specified by PDCLTPATH. This environment variable is effective only when PDUAPREPLVL is specified.

Information is output to the UAP statistical report file each time the UAP is connected or disconnected. The file name is formed from the connection time (HH:MM:SS:mmm) and the connection number (XXX). Examples are pdHHMMSSmmm_XXX_1.trc and pdHHMMSSmmm_XXX_2.trc.

<path name> ((up to 256 bytes))

This environment variable specifies the storage directory for dynamic SQL trace files that the HiRDB client creates. This environment variable must be specified when dynamic SQL trace files are collected with the trace acquisition command (pdtrcmgr).

When the directory specified here is specified in the pdtrcmgr command, an SQL trace file is created in the specified directory from the next connection. For details about pdtrcmgr, see 10.1.6 SQL trace dynamic acquisition facility.

This environment variable specifies the open mode for SQL trace files when PDREPPATH is specified.

CNCT

Opens and closes the SQL trace file in CONNECT and DISCONNECT units, and outputs trace information. When CNCT is specified instead of SQL in PDSQLTRCOPENMODE, the SQL trace output time can be shortened because the overhead is reduced.

When CNCT is specified, the system continues to write information as long as the SQL trace file is open. Therefore, some SQL trace information may be discarded if DISCONNECT cannot be executed properly.

SQL

Opens and closes the SQL trace file in operation units (SQL units), and outputs trace information.

<unsigned integer> ((4096-2000000)) <<4096>> (bytes)

This environment variable specifies the size of the SQL statement to be output to the SQL trace.

If this environment variable is omitted during access path acquisition, 2000000 is assumed instead of 4096.

This environment variable specifies whether the SQL runtime is to be output to the SQL trace.

YES

Output the SQL runtime.

The unit for the SQL runtime that is output is microseconds. Normally runtime values over 24 hours are not output in the SQL trace.

NO

Do not output the SQL runtime.

<unsigned integer> ((0, 4096-2000000000)) (bytes)

This environment variable specifies the size of the output file for UAP reconnect trace information.

If 0 is specified, the maximum file size is assumed, and UAP reconnect trace information that exceeds the maximum size is not output. UAP reconnect trace information also is not output when this environment variable is omitted.

If a value from 4,096 to 2,000,000,000 is specified, the specified value becomes the file size, and the output destination switches when the file size exceeds the specified value.

The reconnect trace is output to the directory specified in PDCLTPATH. If PDCLTPATH is not specified, the reconnect trace is output in the current directory when the UAP is executed (current directory during J2EE server execution if the UAP is executed from Cosminexus). For details about the reconnect trace, see 10.1.7 Reconnect trace facility.

<path name> ((up to 256 bytes))

This environment variable specifies the storage directory for files to which value expression values of WRITE LINE statements are to be output. For details about the WRITE LINE statement, see the manual HiRDB Version 8 SQL Reference.

If PDWRTLNPATH is omitted, the directory specified in PDCLTPATH is assumed.

Two files are created in the specified directory (or the directory specified in PDCLTPATH if PDWRTLNPATH is omitted). The files that are created differ depending on whether or not an X/Open-compliant API (TX_function) is used. The names of the created files are shown as follows.

If TX_function is not used

pdwrtln1.trc and pdwrtln2.trc

If TX_function is used

pdwrtlnxxxxx-1.trc and pdwrtlnxxxxx-2.trc

xxxxx: Process ID when the UAP is executed

<unsigned integer> ((0, 4096-2000000000)) (bytes)

This environment variable specifies the maximum size of the files to which value expression values of WRITE LINE statements are to be output.

If 0 is specified, the maximum file size is the maximum file size that the OS can manage. If the maximum size is exceeded, the value expression values of WRITE LINE statements are not output. Value expression values of WRITE LINE statements also are not output if this environment variable is omitted.

If a value from 4,096 to 2,000,000,000 is specified, the specified value becomes the maximum file size, and the output destination switches when the file size exceeds the specified value.

Notes

1. If both PDWRTLNFILSZ and PDIPC=MEMORY are specified, the PDIPC specification becomes invalid.
2. The files are output to the directory specified in PDWRTLNPATH.
3. If a file becomes full when values are being output, values are output to the other file. When this happens, the information already stored in the switchover-destination file is deleted, and new information is written to that file. Therefore, if the file contains required information, save that information before switchover occurs. To find out which file is currently being used, use the following method. The file that has the more recent update date is the current file.
   In UNIX: Execute the ls -l command of the OS.
   In Windows: Execute the DIR command from the command prompt, or use Windows Explorer to check the files.
   

<unsigned integer> ((1024-131072)) <<1024>> (bytes)

This environment variable specifies the total size of the value expression values in WRITE LINE statements.

If the total size of the value expression values in WRITE LINE statements exceeds the PDWRTLNCOMSZ specification value, the excess information is ignored. In this case, **PDWRTLNCOMSZover** is output in the following line.

This environment variable specifies whether the facility for output of extended SQL error information is to be used.

For details about the facility for output of extended SQL error information, see 10.1.3 Facility for output of extended SQL error information.

YES

Use the facility for output of extended SQL error information.

NO

Do not use the facility for output of extended SQL error information.

Relationship to the system definition

When this environment variable is omitted, the specification value of the pd_uap_exerror_log_use operand in the system definition is assumed.

<unsigned integer> ((0-32008)) (bytes)

This environment variable specifies the maximum data length of parameter information to be output to error log and SQL error report files when the facility for output of extended SQL error information is used. Parameter information is output when a value of 1 or higher is specified, but parameter information is not output when 0 is specified.

Relationship to the system definition

When this environment variable is omitted, the specification value of the pd_uap_exerror_log_param_size operand in the system definition is assumed.

Notes

1. For variable-length character string-type, BLOB-type, and BINARY-type data, the area of the character length is included in the specification value of this environment variable.
2. If the data length of the parameter information to be output exceeds the specification value of this environment variable, the excess portion of the information is truncated.

This environment variable specifies whether access path information is to be acquired for the access path display utility.

The access path information file is created under the SQL information directory (%PDDIR%\spool\pdsqldump) of the unit containing the single server or the front-end server to which the UAP is connected.

For details about the access path display utility, see the HiRDB Version 8 Command Reference manual.

0

Do not collect access path information.

1

Collect access path information and output the information to the access path information file. No information is output for SQL statements that have SQL objects in the buffer.

2

Collect access path information and output the information to the access path information file. For SQL statements that have SQL objects in the buffer, the SQL objects are re-created and the information is output.

Notes

1. Specify PDTAAPINFPATH to acquire access path information for HiRDB SQL Tuning Advisor. For details about the access path information file for HiRDB SQL Tuning Advisor, see 10.1.8 Access path information file for HiRDB SQL Tuning Advisor.
2. Note that when 1 is specified, no information is output for SQL statements that have SQL objects in the buffer. If you want the information output to include information about SQLs that have SQL objects in the buffer, specify 2.
3. If 2 is specified, the server load increases compared to when 1 is specified because SQL objects are also re-created for SQL statements that have SQL objects in the buffer.
4. If the total of %PDDIR%-path-length + authorization-identifier-length + UAP-name-length is larger than 220 characters when the Windows-version HiRDB is used, creation of the access path information file may fail. If this happens, use the UAP statistical report facility and get access path information. For details about the UAP statistical report facility, see 10.1.4 UAP statistical report facility.
5. The following table shows the relationships between the SQL types and the PDVWOPTMODE specification values.
   

   SQL type	Condition	PDVWOPTMODE specification value
   		0	1	2
   Static SQL	SQL objects are not found in the buffer.		Y	Y
   	SQL objects are found in the buffer.			Y
   dynamic SQL	SQL objects are not found in the buffer.		Y	Y
   	SQL objects are found in the buffer.			Y
   Routine definition	None		Y	Y
   CALL statement	Index information for procedure SQL objects is invalid as a result of index addition or deletion.		Y	Y
   	Other condition

Y: Access path information is output.

: Access path information is not output.

<path name>

This environment variable specifies the output destination directory when an access path information file for HiRDB SQL Tuning Advisor is output. If an output processing error occurs even with this environment variable specified, because the output destination directory does not exist or because there is no write privilege for the specified directory, the access path information is not output. Note that even when an output processing error occurs, there is no error in the executing SQL. For details about the access path information file for HiRDB SQL Tuning Advisor, see 10.1.8 Access path information file for HiRDB SQL Tuning Advisor.

Notes

• This environment variable is ignored when the dynamic browsing function of HiRDB SQL Tuning Advisor is used.
• The inter-process memory communication facility cannot be used when this environment variable is specified. Even though you specify MEMORY for the PDIPC operand in the client environment definition, operation is the same as when DEFAULT is specified.

This environment variable specifies the file name format of the access path information files that are output for HiRDB SQL Tuning Advisor.

0:

The file names are pdtaapinf1 and pdtaapinf2.

1:

The file names are in the format pdtaapinfHHMMSSmmm_XXX_1 and pdtaapinfHHMMSSmmm_XXX_2.

HHMMSSmmm:

Connection time (same as the connection start time of the applicable CONNECT output in an SQL trace)

XXXXXXXXXX:

Connection sequence number (maximum of 10 digits)

<unsigned integer>((100000 - 2000000000)) (409600) (bytes)

This environment variable specifies the file size of an access path information file that is output for HiRDB SQL Tuning Advisor. When the file size specified here is reached in the current access path information file, the output destination is switched to the other file. After that, the two files are used alternately by repeating this switching.

This environment variable specifies whether UAP statistical information is to be output to a statistical log file for each transaction.

YES

Output UAP statistical information to a statistical log file for each transaction.

NO

Output UAP statistical information to a statistical log file for each connection.

To specify the start of UAP statistical information output, use the pdstbegin operand of the system definition or the pdstbegin command. For details about the pdstbegin operand, see the manual HiRDB Version 8 System Definition. For details about the pdstbegin command, see the manual HiRDB Version 8 Command Reference.

If this environment variable is omitted when the UAP is operating in an OLTP environment, UAP statistical information is output to a statistical log file for each transaction. If this environment variable is omitted when the UAP is operating in another environment, UAP statistical information is output to a statistical log file for each connection.

<unsigned integer> ((0-32,767)) <<0>>

This environment variable specifies the maximum number of lock requests (that is, the maximum number of locked resource requests) that a UAP can issue to one server.

If 0 is specified or this environment variable is omitted, the HiRDB system does not check the maximum number of lock requests. In this case, the maximum possible number of lock requests is issued.

Estimation method

The number of locked resources depends on the SQL. Estimate the number of locked resources depending on the lock processing to determine the value to be specified in this operand. For details about how to estimate the locked resource count, see the manual HiRDB Version 8 System Definition. For details about lock processing, see 3.4 Locking.

This environment variable specifies the deadlock priority value of the UAP and becomes effective when Y is specified in the pd_deadlock_priority_use operand of the system definition.

If a deadlock occurs between two programs, the program with the smaller value specified in this environment variable is processed at a higher priority. The program with the larger value is terminated with an error, and that program is rolled back.

If a deadlock occurs between two programs that have the same deadlock priority value, the one with the transaction that started earlier is processed first. The following table lists the deadlock priority values:

PDDLKPRIO specification			Deadlock priority value
96			96
64			64
32			32
Omitted	When the X/Open XA interface is used		96
	When the X/Open XA interface is not used		64
	For distributed server of distributed database		64
	Utility		64
	Operation command	pddbchg, pdhold (-b and -s), pdorbegin, or pdorend	Specification value of the pd_command_deadlock_priority operand in the system definition
		Other operation command	64

: Not applicable

This environment variable specifies whether an unlocked conditional search can be performed.

YES

Enables an unlocked conditional search.

NO

Disables an unlocked conditional search.

When YES is specified in this environment variable, the conditional search of a retrieval process (including retrieval for DELETE and UPDATE) is performed without locking all items. For details about unlocked conditional search, see 3.4.5 Unlocked conditional search.

This environment variable specifies whether WITH EXCLUSIVE LOCK is to be applied to the lock option of SQL statements in which the FOR UPDATE clause is specified (or assumed) in the UAP. If WITH EXCLUSIVE LOCK is applied, the specification value of the PDISLLVL client environment definition is ignored.

YES

Apply WITH EXCLUSIVE LOCK to the lock option of SQL statements in which the FOR UPDATE clause is specified.

NO

Apply the PDISLLVL specification value to the lock option of SQL statements in which the FOR UPDATE clause is specified.

If PDFORUPDATEEXLOCK is specified for an SQL statement in a routine, the specification becomes invalid. To apply WITH EXCLUSIVE LOCK to an SQL statement specifying the FOR UPDATE clause in a routine, specify WITH EXCLUSIVE LOCK as an SQL compile option when defining the routine.

<unsigned integer> ((0-2)) <<2>>

This environment variable specifies the data guarantee level of an SQL statement. The data guarantee level is the point in a transaction up to which data is to be guaranteed. This environment variable has the same function as the WITHOUT LOCK option that is specified in the SELECT statement.

This environment variable enables batch determination of all lock options for the SQL statements in a UAP. Note that the data guarantee level specified in the lock option of an SQL statement takes precedence over this operand.

For details about the data guarantee level, see 4.6 Data guarantee levels.

0

If another user is updating data, users are allowed to reference the same data without having to wait for completion of the update processing. This specification can improve the processing concurrency level. However, if the same row of data is retrieved twice within the same transaction, the same data might not be received. For example, if a stock table is retrieved with SELECT * FROM STOCK, the user can retrieve the desired data without having to wait for lock release, even when another user is updating the stock table. This corresponds to the SELECT statement with WITHOUT LOCK NOWAIT.

For the cursor declaration used with update processing, a value of 1 is always assumed, even if 0 is specified.

1

If a user is retrieving data, other users are not allowed to update that data until retrieval processing is completed. Other users are allowed to reference or update that data when the retrieval terminates, even if the transaction has not terminated. This specification therefore improves the apparent concurrent execution property. However, if the same row is retrieved twice in the same transaction, the same data may not be retrieved. For example, if data is being retrieved from a stock table with SELECT * FROM STOCK, other users are allowed to update or reference the stock table after the retrieval ends, without having to wait for the transaction to terminate. This corresponds to the SELECT statement with WITHOUT LOCK WAIT.

2

All other users are prohibited from updating the data being retrieved until the retrieval transaction terminates. For example, if a stock table is retrieved with SELECT * FROM STOCK, the contents of the stock table are guaranteed until the transaction terminates. This corresponds to the SELECT statement with WITH SHARE LOCK.

For a cursor declaration that accompanies an update, WITH EXCLUSIVE LOCK is assumed.

Notes

1. The data guarantee level of SQL statements in a stored procedure is determined by the specifications for CREATE PROCEDURE, CREATE TYPE, ALTER PROCEDURE, and ALTER ROUTINE. Therefore, when a procedure is executed, the data guarantee level is not affected by this environment variable.
2. If this environment variable is omitted along with the lock option in an SQL statement, the WITH SHARE LOCK option is assumed for the SQL statement. For details about lock options, see the HiRDB Version 8 SQL Reference manual.

<identifier or unsigned integer>

This environment variable specifies optimization methods for determining the most efficient access path by taking the database status into consideration.

Although SQL optimization options can be specified either with identifiers (character strings) or numbers, specifying the options with identifiers is usually recommended.

Specifying the SQL optimization methods with identifiers

PDSQLOPTLVL="identifier"[,"identifier"]...

Examples

• Applying prioritized nest-loop-join and rapid grouping processing:
  PDSQLOPTLVL="PRIOR_NEST_JOIN","RAPID_GROUPING"
  
• Applying no optimization method:
  PDSQLOPTLVL="NONE"
  

Rules

1. Specify at least one identifier.
2. When specifying two or more identifiers, separate them with commas.
3. For details about the information (optimization methods) that can be specified with identifiers, see Specification values for the SQL optimization option.
4. If no optimization is to be applied, specify NONE as the identifier. However, if another identifier is specified together with NONE, then NONE becomes invalid.
5. The identifiers can be specified with uppercase and lowercase characters.
6. Even if the same identifier is specified more than once, HiRDB recognizes only one specification. However, try not to specify the same identifier more than once.
7. The character string specified for "identifier"[,"identifier"]... can have up to 575 bytes.

Specifying the SQL optimization methods with numbers

PDSQLOPTLVL=unsigned-integer[,unsigned-integer]...

Examples

• Applying making multiple SQL objects, suppressing use of AND multiple indexes, and forcing use of multiple indexes
  Specification when unsigned integers are separated by commas:
  PDSQLOPTLVL=4,10,16
  Specification when the sum of the unsigned integers is specified:
  PDSQLOPTLVL=30
  
• Specification when 14 (4+10) is already specified and 16 is added:
  PDSQLOPTLVL=14,16
  
• Applying no optimization method:
  PDSQLOPTLVL=0
  

Rules

1. When HiRDB is updated from a version before 06-00 to version 06-00 or later, the total value specification of the earlier version remains effective. If the optimization options do not need to be changed after HiRDB is updated to Version 06-00 or later, the specification value of this operand does not need to be changed.
2. Specify at least one unsigned integer.
3. When specifying two or more unsigned integers, separate them with commas.
4. For details about the information (optimization methods) that can be specified with unsigned integers, see Specification values for the SQL optimization option.
5. If no optimization is to be applied, specify 0 as the identifier. However, if another identifier is specified together with 0, then 0 becomes invalid.
6. Even if the same unsigned integer is specified more than once, HiRDB recognizes only one specification. However, try not to specify the same unsigned integer more than once.
7. Multiple optimization methods can also be specified by specifying the sum of the unsigned integers. However, do not add the same optimization method value more than once. (Otherwise, the specified result may be interpreted as an unintended optimization methods.)
8. If multiple optimization method values are added together and specified, it becomes difficult to determine which optimization methods are being specified. Hitachi therefore recommends that you separate the values with commas. If several optimization method values have already been added and specified, and a new optimization method becomes necessary, you can separate the new value with a comma and specify it after the previous specification.
9. The character string specified for "unsigned-integer"[,"unsigned-integer"]... can have up to 575 bytes.

Relationship to the system definition

1. When this environment variable is omitted, the value specified in the pd_optimize_level operand of the system definition is assumed. For details about the pd_optimize_level operand, see the manual HiRDB Version 8 System Definition.
2. If the pd_floatable_bes or pd_non_floatable_bes operand is specified in the system definitions, the specifications for increasing the target floatable servers (back-end servers for fetching data) and limiting the target floatable servers (back-end servers for fetching data) specifications become invalid.
3. If KEY is specified (for index key value locking) in the pd_indexlock_mode operand of the system definitions, the specification for suppressing creation of update-SQL work tables becomes invalid.

Relationship with SQL

The SQL optimization option for an SQL statement in a stored procedure is determined by the specifications for CREATE PROCEDURE, CREATE TYPE, ALTER PROCEDURE, or ALTER ROUTINE, and is not affected by the PDSQLOPTLVL specification.

If an SQL optimization specification is specified in an SQL statement, the SQL optimization specification has priority over the SQL optimization option. For details about SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.

Specification values for the SQL optimization option

Table 6-28 shows the values that can be specified for the SQL optimization option.

Table 6-28 Specification values of the SQL optimization option

Number	Optimization method	Specification value
		Identifier	Unsigned integer
1	Forced nest-loop-join	"FORCE_NEST_JOIN"	4
2	Making multiple SQL objects	"SELECT_APSL"	10
3	Increasing the target floatable servers (back-end servers for fetching data)1, 2	"FLTS_INC_DATA_BES"	16
4	Prioritized nest-loop-join	"PRIOR_NEST_JOIN"	32
5	Increasing the number of floatable server candidates2	"FLTS_MAX_NUMBER"	64
6	Priority of OR multiple index use	"PRIOR_OR_INDEXES"	128
7	Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server2	"SORT_DATA_BES"	256
8	Suppressing use of AND multiple indexes	"DETER_AND_INDEXES"	512
9	Rapid grouping processing	"RAPID_GROUPING"	1024
10	Limiting the target floatable servers (back-end servers for fetching data)1, 2	"FLTS_ONLY_DATA_BES"	2048
11	Separating data collecting servers1, 2	"FLTS_SEPARATE_COLLECT_SVR"	2064
12	Suppressing index use (forced table scan)	"FORCE_TABLE_SCAN"	4096
13	Forcing use of multiple indexes	"FORCE_PLURAL_INDEXES"	32768
14	Suppressing creation of update-SQL work tables	"DETER_WORK_TABLE_FOR_UPDATE"	131072
15	Deriving high-speed search conditions	"DERIVATIVE_COND"	262144
16	Applying key conditions that include scalar operations	"APPLY_ENHANCED_KEY_COND"	524288
17	Facility for batch acquisition from functions provided by plug-ins	"PICKUP_MULTIPLE_ROWS_PLUGIN"	1048576

¹ If increasing the target floatable servers (back-end servers for fetching data) and limiting the target floatable servers (back-end servers for fetching data) are both specified, neither optimization method becomes effective. Instead, the servers operate as separating data collecting servers.

² When a HiRDB/Single Server is used, this option becomes invalid, even if specified.

Recommended specification values

The recommended specification values are indicated with item numbers in the examples and the following table. These numbers correspond to the numbers in the Number column of Table 6-28.

• HiRDB/Single Server
  Specify item numbers 4, 6, 8, 9,14, and 16. An example of how these numbers are specified with identifiers is shown as follows.
  

PDSQLOPTLVL="PRIOR_NEST_JOIN",
            "PRIOR_OR_INDEXES",
            "DETER_AND_INDEXES",
            "RAPID_GROUPING",
            "DETER_WORK_TABLE_FOR_UPDATE"
            "APPLY_ENHANCED_KEY_COND"

• HiRDB/Parallel Server
  Table 6-29 shows the recommended specification values for the SQL optimization option.
  

  Table 6-29 Recommended specification values for the SQL optimization option (for HiRDB/Parallel Server)

  Condition		Specification value
  Use as many back-end servers as feasible for SQL processing so that the individual SQL statements can be processed quickly	To process SQL statements involving mass-data searches quickly	Specify item numbers 3 to 9, 14 and 16. Identifier specification example: PDSQLOPTLVL="FLTS_INC_DATA_BES", "PRIOR_NEST_JOIN", "FLTS_MAX_NUMBER", "PRIOR_OR_INDEXES", "SORT_DATA_BES", "DETER_AND_INDEXES", "RAPID_GROUPING", "DETER_WORK_TABLE_FOR_UPDATE" "APPLY_ENHANCED_KEY_COND"
  	To process searches with numerous results (several tens of data items) quickly	Specify item numbers 3, 4, 6 to 9, 14 and 16. Identifier specification example: PDSQLOPTLVL="FLTS_INC_DATA_BES", "PRIOR_NEST_JOIN", "PRIOR_OR_INDEXES", "SORT_DATA_BES", "DETER_AND_INDEXES", "RAPID_GROUPING", "DETER_WORK_TABLE_FOR_UPDATE" "APPLY_ENHANCED_KEY_COND"
  To separate back-end servers for each job	To process SQL statements involving mass-data searches quickly	Specify item numbers 4 to 10, 14 and 16. Identifier specification example: PDSQLOPTLVL="PRIOR_NEST_JOIN", "FLTS_MAX_NUMBER", "PRIOR_OR_INDEXES", "SORT_DATA_BES", "DETER_AND_INDEXES", "RAPID_GROUPING", "FLTS_ONLY_DATA_BES", "DETER_WORK_TABLE_FOR_UPDATE" "APPLY_ENHANCED_KEY_COND"
  	To process searches with numerous results (several tens of data items) quickly	Specify item numbers 4, 6 to 10, 14 and 16. Identifier specification example: PDSQLOPTLVL="PRIOR_NEST_JOIN", "PRIOR_OR_INDEXES", "SORT_DATA_BES", "DETER_AND_INDEXES", "RAPID_GROUPING", "FLTS_ONLY_DATA_BES", "DETER_WORK_TABLE_FOR_UPDATE" "APPLY_ENHANCED_KEY_COND"
  Other conditions		Specify item numbers 4, 6 to 9, 14 and 16. Identifier specification example: PDSQLOPTLVL="PRIOR_NEST_JOIN", "PRIOR_OR_INDEXES", "SORT_DATA_BES", "DETER_AND_INDEXES", "RAPID_GROUPING", "DETER_WORK_TABLE_FOR_UPDATE" "APPLY_ENHANCED_KEY_COND"

Explanation of optimization methods

1. Forced nest-loop-join
   If indexes are defined in the columns of the join condition, only nest-loop-join is used in join processing. For details about the join processing method for nest-loop-join, see 4.5.6 Join methods.
   However, if one of the following conditions applies, a method other than nest-loop-join may be used in join processing:
   
   • An entity (for example, a scalar operation) other than a column is specified in the join condition.
   • The join condition is not a = predicate.
   • The column in the join condition is not the first configuration column of the index. Also, if the column in the join condition is the n-th configuration column of the index, a = predicate or a restriction condition of the IS NULL predicate is not specified for any of the preceding configuration columns (first configuration column to n -1 -th configuration column).
   • A join condition is not specified in the ON search condition for an outer join.
   • In the join condition, a plug-in presentation function that executes a search using the indexes of two tables to be joined, or a structured repetition predicate, is specified for the two tables.
   • A HiRDB/Parallel Server is used, and a partitioned column of the inner table is not specified in the join condition for an outer join that uses a partitioned table as the inner table.
   • A HiRDB/Parallel Server is used, and the outer join uses a flexible hashed partitioned table as the inner table.

   Notes

   1. If a joined table is to be processed by nest-loop-join, the table that was specified as the outer table in the SQL is used as the outer table.
   2. If an index is defined in only one of the columns of the join condition and the join is to processed with nest-loop-join, the table with the defined index becomes the inner table.
   3. Except when a joined table is involved, if a join where indexes are defined in the columns on both sides of the join condition is processed by nest-loop-join, HiRDB judges and determines the outer and inner tables of the nest-loop-join. However, if a view table or WITH clause query name is not specified in the FROM clause, HiRDB determines the outer and inner tables according to the following rules:
   If partitioned tables of a HiRDB/Parallel Server are to be joined, and all partitioned columns of one table but not all partitioned columns of the other table are specified in the join condition, the table for which all partitioned columns are specified in the join condition becomes the inner table.
   If (a) previously does not apply, the first table specified in the FROM clause becomes the outer table.
   4. If "forced nest-loop-join" is applied in the HiRDB/Parallel Server and mass data is to be joined, partition the tables with joined columns as much as possible.
   
2. Making multiple SQL objects
   Multiple SQL objects are created in advance, and the optimum SQL object is selected during execution, based on the value of an embedded variable or the ? parameters.
   
3. Increasing the target floatable servers (back-end servers for fetching data)
   Normally, back-end servers that are not used for fetching data are used as floating servers. With this optimization method, back-end servers that are used for fetching data can also be used as floating servers. However, the HiRDB system calculates the number of back-end servers that can be used as floating servers, and not all back-end servers end up being used as floating servers. To use all back-end servers, also specify the specification for increasing the number of floatable server candidates.
   For details about how to allocate floatable servers, see 4.5.4 Allocating floatable servers (HiRDB/Parallel Server only).
   This specification is valid only for a HiRDB/Parallel Server.
   
4. Prioritized nest-loop-join
   If indexes are defined in the columns of the join condition, nest-loop-join is used with priority in join processing. For details about the join processing method for nest-loop-join, see Section 4.5.6 Join methods.
   This optimization method is different from 1. Forced nest-loop-join. Forced nest-loop-join always executes nest-loop-join if indexes are defined in the join condition, even if there is no narrowing condition (except when restrictions apply). On the other hand, while prioritized nest loop join always executes nest-loop-join if a narrowing condition is specified, HiRDB determines the join method if there is no narrowing condition. However, if one of the following conditions applies, a method other than nest-loop-join may be used in join processing, even if a narrowing condition is specified:
   
   • An entity (for example, a scalar operation) other than a column is specified in the join condition.
   • The join condition is not a = predicate.
   • The column in the join condition is not the first configuration column of the index. Also, if the column in the join condition is the n-th configuration column of the index, a = predicate or a restriction condition of the IS NULL predicate is not specified for any of the preceding configuration columns (first configuration column to n -1 -th configuration column).
   • A join condition is not specified in the ON search condition for an outer join.
   • In the join condition, a plug-in presentation function that executes a search using the indexes of two tables to be joined or a structured repetition predicate is specified for the two tables.
   • A HiRDB/Parallel Server is used, and a partitioned column of the inner table is not specified in the join condition for an outer join that uses a partitioned table as the inner table.
   • A HiRDB/Parallel Server is used, and the outer join uses a flexible hashed partitioned table as the inner table.
   • The optimizing information collection utility (pdgetcst) is being executed.
   • The narrowing condition is a search condition that includes only a CHAR, VARCHAR, MCHAR, or MVARCHAR column that has a definition length of at least 256 bytes; an NCHAR or NVARCHAR column that has a definition length of at least 128 characters; or a BLOB column.
   • The narrowing condition is a search condition that includes only a NOT or OR operator.

   Notes

   1. If a joined table is to be processed by nest-loop-join, the table that was specified as the outer table in the SQL is used as the outer table.
   2. If an index is defined in only one of the columns of the join condition and the join is processed with nest-loop-join, the table with the defined index becomes the inner table.
   3. Except when joined tables are involved, if an index is defined in both columns of the join condition and the join is to be processed with nest-loop-join, HiRDB determines which table becomes the outer table and which becomes the inner table in the nest-loop-join. However, if a view table or WITH clause query name is not specified in the FROM clause, and only a join clause is specified in the search conditions, HiRDB determines the outer and inner tables according to the following rules:
   If partitioned tables of HiRDB/Parallel Server are being joined, specify all partitioned columns of one table in the join conditions. If the partitioned columns of the other table being joined contains columns that were not specified in the search conditions, the table with the partitioned columns that were all specified in the join conditions becomes the inner table.
   If the preceding rule does not apply, the first table specified in the FROM clause becomes the outer table.
   4. If 1. Forced nest-loop-join is also specified, this optimization option becomes invalid.
   
5. Increasing the number of floatable server candidates
   Normally, the HiRDB system calculates and allocates the number of floating servers that are necessary from the floatable servers that can be used. When this optimization method is applied, all usable floating servers are used, except for back-end servers that are used for fetching data.
   If you wish to include the back-end servers used for fetching data for use as floatable servers, also specify the value for increasing the target floatable servers (back-end servers for fetching data).
   For details about how to allocate floatable servers, see 4.5.4 Allocating floatable servers (HiRDB/Parallel Server only). This specification is valid only when a HiRDB/Parallel Server is used.
   
6. Priority of OR multiple index use
   Specify this method to give application priority to the method that uses OR multiple indexes in searching. The OR multiple index use method is used when multiple conditions are combined with OR in the search condition. This method uses an index to search each condition and evaluates the search condition by taking a sum set of the search results.
   When A OR B OR C ... OR Z is specified in the WHERE clause or OR search condition and the data is narrowed by using = for all conditions combined with OR, a high-speed search can be realized by applying priority of OR multiple index use.
   Even when the value for priority of OR multiple index use is not specified, HiRDB applies OR multiple index use when retrieving data if the number of ORs is small. However, as the number of ORs increases, the retrieval costs that HiRDB expends in internal calculations also increases, and HiRDB may stop applying OR multiple index use. If this happens, specify the value for priority of OR multiple index use so that OR multiple index use is always applied, even if the number of ORs becomes large.
   

   Notes

   1. If an AND condition is specified together with the OR conditions, and the AND condition uses an index to narrow the data, that index may be used in the search process.
   2. This optimization method is applied when all conditions specified with OR are narrowed with = in the comparison predicate. Also, a single-column index or the index that becomes the first configuration column of a multi-column index must be defined for all columns that were narrowed with =.
   3. In a join search of two or more tables, this optimization method may not be applied if HiRDB determines that searching the data by using a joined column index would be faster.
   4. For some SQL statements, AND multiple index use, which involves sum sets, is applied instead of OR multiple index use. In such cases, high-speed retrieval is also possible, just as when OR multiple index use is applied. However, if AND conditions are specified, product sets and sum sets may be combined when use of AND multiple indexes is applied.
   If the performance is poor when use of AND multiple indexes specified with product sets is applied, you can improve the performance with either of the following methods:
   Specify both priority of OR multiple index use and suppressing use of AND multiple indexes at the same time.
   If several column conditions linked with AND can be narrowed, define a multi-column index that includes these condition columns.
   5. If application of optimizing mode 2 based on cost is not used in the SQL extension optimizing option, multiple indexes are not used in the join search. However, if there is a condition that cannot be evaluated without applying multiple index use, multiple indexes are used regardless of the specification of this optimization method.
   
7. Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server

Usually, group processing, ORDER BY processing, and DISTINCT set function processing use a floating server. However, when this optimization is used for a single table search, group processing, ORDER BY processing, and DISTINCT set function processing are performed at the back-end server (local back-end server) where the table is defined.

For details about grouping processing methods, see 4.5.5 Grouping processing methods (HiRDB/Parallel Server only).

When rapid grouping processing is used, or when as a result of an index search HiRDB determines that there is no need to sort for group processing, ORDER BY processing, or DISTINCT set function processing, a faster processing method is selected.

1. Suppressing use of AND multiple indexes
   This specification prevents the use of access paths that use AND multiple indexes.
   AND multiple indexes are used when a search condition contains multiple conditions connected by AND, and a different index is defined for each column (e.g., SELECT ROW FROM T1 WHERE C1=100 AND C2=200). In this case, the indexes are used to create work tables for the rows that satisfy the conditions, and a product set is obtained from the resulting work tables.
   If the AND multiple indexes being used include OR multiple indexes, this specification suppresses multiple index use for the AND portion but not for the OR portion.
   Depending on the data characteristics, the product set is effective in some cases and can worsen performance in others. Multiple index use is effective when using those indexes significantly narrows the number of items to be searched, and the amount of duplicated data is reduced when the product set is taken.
   Apply this optimization method if you think that using AND multiple indexes is not effective.
   The use of AND multiple indexes cannot be suppressed if a query specification contains multiple conditions that include columns for the same table, in the following locations:
   
   • In a retrieval condition for a structured repetition predicate
   • In the first argument of a plug-in distribution function that retrieves data with an index
2. Rapid grouping processing
   This optimization method uses hashing to rapidly process the groups specified in the GROUP BY clause of the SQL statement.
   For details about the rapid grouping facility, see 4.9 Rapid grouping facility.
   
3. Limiting the target floatable servers (back-end servers for fetching data)
   Normally, back-end servers that are not used for fetching data are used as floating servers. When this optimization method is applied, only back-end servers used for fetching data are used as floating servers. This specification is valid only for a HiRDB/Parallel Server. For details about how to allocate floatable servers, see 4.5.4 Allocating floatable servers (HiRDB/Parallel Server only).
   
4. Separating data collecting servers
   If increasing the target floatable servers (back-end servers for fetching data) or limiting the target floatable servers (back-end servers for fetching data) is specified, the separating data collecting server method is applied.
   When this method is applied, the back-end servers that are not transmitting data are allocated for data collecting if the SQL statement requires data from multiple back-end servers to be collected in one back-end server. The back-end servers that are not used for data collecting (included back-end servers for fetching data) are allocated as floatable servers for other uses.
   For details about how to allocate floatable servers, see 4.5.4 Allocating floatable servers (HiRDB/Parallel Server only).
   
5. Suppressing index use (forced table scan)
   Normally, the HiRDB system determines whether or not an index is to be used. When this optimization method is applied, the method that does not use an index is forcibly used.
   However, index use cannot be suppressed if nest loops are linked with the JOIN command, a structured repetition predicate is specified in the retrieval condition, or a condition for an index-type plug-in-dependent function is specified.
   
6. Forcing use of multiple indexes
   Specify this optimization option to forcibly select the use of AND multiple indexes when searching tables.
   If several conditions linked with AND area specified and this optimization method is not specified, normally only up to two indexes are used even if AND multiple index use is selected. The number of indexes to be used changes slightly according to the table definitions, index definitions, and search conditions.
   When this optimization method is specified, all conditions that can narrow the search range by using indexes are used.
   The use of AND multiple indexes is effective when using those indexes significantly narrows the number of items to be searched, and the amount of duplicated data is reduced when the product set is taken.
   If application of optimizing mode 2 based on cost is not used in the SQL extension optimizing option, multiple indexes are not used in the join search. However, if there is a condition that cannot be evaluated without applying multiple index use, multiple indexes are used regardless of the specification of this optimization method.
   
7. Suppressing creation of update-SQL work tables
   If this optimization method is used when index key value with no lock is applied, HiRDB does not create work tables for internal processing even if an index is used for a search, UPDATE statement, or DELETE statement specified in the FOR UPDATE clause. The SQL statement can therefore be processed at high speed.
   If an index key value with no lock is not applied, HiRDB creates work tables.
   To check whether an index is being used, use the access path display utility.
   If you specify suppressing creation of update-SQL work tables and also use non-locking of index key values, the restrictions of table manipulation when a cursor is used are relaxed.
   Table 6-30 shows the relationships between SQL statements that create work tables and the option for suppressing creation of update-SQL work tables.
   

   Table 6-30 Relationships between SQL statements that create work tables and suppressing creation of update SQL-work tables

   SQL statement		Indexes are used		Indexes are not used
   		This optimization is applied	This optimization is not applied
   SELECT statement	FOR UPDATE1		C
   	FOR UPDATE OF		C2
   	FOR READ ONLY	C	C	C
   	ORDER BY	C 4 5	C 4	C
   	Previous clauses are not specified	5
   UPDATE statement	Only values are specified for updated values in the SET clause		C 3
   	A non-value entity is specified for an updated value in the SET clause	C3	C3
   DELETE statement			C

   C: Work tables are created.
   : Work tables are not created.
   ¹ This includes FOR UPDATE clauses that are assumed when data is updated using the cursor of this SELECT statement.
   ² Work tables are not created if the configuration column of the index to be used is not specified in the FOR UPDATE OF column name.
   ³ Work tables are not created if the configuration column of the index to be used is not specified in the column to be updated on the left side of the SET clause.
   ⁴ Work tables for the ORDER BY clause are sometimes not created when indexes are used.
   ⁵ A table from which data is being retrieved with a cursor can be updated by another SQL statement. However, if the index that is being used for retrieval with the cursor is updated, the results retrieved with the cursor are not guaranteed.
   
8. Deriving high-speed search conditions
   When this optimization method is specified, HiRDB derives high-speed search conditions.
   A high-speed search condition refers to a condition that is derived from a WHERE clause search condition or an ON search condition in a FROM clause by CNF conversion or condition shifting. When a high-speed search condition is derived, the retrieval performance improves because the rows to be retrieved are narrowed down at an early stage. However, generating and executing a high-speed search condition may take a long time, and the intended access path is not always achieved. Therefore, whenever possible, specify the high-speed search condition directly in an SQL statement instead of specifying this optimization option. For details about deriving high-speed search conditions, see 4.5.11 Deriving high-speed search conditions.
   
9. Applying key conditions that include scalar operations
   When this optimization method is specified and all columns included in the scalar operation specified in a limiting condition are index configuration columns, HiRDB narrows the search by evaluating the condition for each index key value. This condition is evaluated as a key condition.
   

   HiRDB operation when applying key conditions that include scalar operations is specified

   When HiRDB uses an index to retrieve data, it evaluates the data in the following sequence:

   1. HiRDB narrows the search range of the index (search condition).

   2. For the narrowed results obtained in 1., HiRDB evaluates the condition for each key value of the index and narrows the search further (key condition).

   3. For all key values evaluated as true in 2., HiRDB use the row identifier (ROWID) to reference the data page and evaluate the condition.

   If this optimization method is not specified, conditions that include a scalar operation are evaluated as described in 3. If this optimization method is specified, such conditions are evaluated as described in 2. Consequently, the number of rows for which data page referencing is performed becomes smaller, and the number of input/output operations can be decreased. For details about search conditions and key conditions, see the manual HiRDB Version 8 Command Reference.

   Notes on applying key conditions that include scalar operations

   1. When this optimization method is specified, HiRDB judges that narrowing using indexes is an effective method of data retrieval, and consequently, it becomes easier for indexes to be used. Therefore, do not specify this option unless beneficial results can be expected when indexes in conditions containing scalar operations are used to narrow searches.

   2. A condition is not evaluated as a key condition if one of the following applies:

   The condition includes a columns that is not an index configuration column.

   The condition includes a system-defined scalar function.

   The condition includes the system built-in scalar function IS_USER_CONTAINED_IN_HDS_GROUP.

   The condition includes function calling.

   The condition includes a repetition column that has an integer subscript.

   3. The evaluation of a structured repetition predicate that includes a scalar operation causes an error because such predicates cannot be evaluated without using an index. Therefore, the key condition is applied, even if this optimization method is not specified.

17. Facility for batch acquisition from functions provided by plug-ins

If a function provided by a plug-in is specified in the search conditions and HiRDB uses a plug-in index to retrieve data, HiRDB normally obtains the results returned from that function (row position information and, if necessary, passing inter-function values) one row at a time.

When this optimization method is applied, the number of times the function provided by the plug-in is called can be decreased because the results returned by the function provided by the plug-in can be obtained in batches of multiple rows. Consequently, the retrieval performance also improves. Note that when the facility for batch acquisition from functions provided by plug-ins is applied, HiRDB creates an internal work table.

Even if this optimization method is not specified, HiRDB sometimes unconditionally applies the facility for batch acquisition from functions provided by plug-ins if it determines that data can be retrieved at high-speed if the facility is always applied. The following table describes the cases when the facility for batch acquisition from functions provided by plug-ins is applied.

Type of specified SQL statement		Specification of facility for batch acquisition from functions provided by plug-ins
		No	Yes
SQL statement that requires a work table for the base table search results*	Function that does not support batch acquisition	NA	NA
	Function that supports batch acquisition	UA	UA
SQL statement that does not require a work table for the base table search results*	Function that does not support batch acquisition	NA	NA
	Function that supports batch acquisition	NA	WTA

Legend:

UA:

The facility for batch acquisition from functions provided by plug-ins is applied unconditionally.

WTA:

A new work table is created, and the facility for batch acquisition from functions provided by plug-ins is applied.

NA:

The facility for batch acquisition from functions provided by plug-ins is not applied.

^* For details about SQL statements that require a work table file, see the manual HiRDB Version 8 Description.

Notes on the facility for batch acquisition from functions provided by plug-ins

1. When HiRDB obtains results returned by a function provided by a plug-in, it must create a work table internally. Normally, this optimization method improves the retrieval performance since the time needed to create a work table is usually shorter than the time needed to accept results returned one row at a time. However, sometimes the retrieval performance drops when this optimization method is specified. Therefore, if the effect of the drop in performance is large, do not specify this optimization method.
2. If this optimization method is specified for a retrieval in which a work table is not created, the time when the first FETCH occurs is delayed. This is because the process that returns results to the client each time a row is fetched changes to a process that returns results to the client after all rows satisfying the search condition of the function provided by the plug-in are fetched and the work table is created. If a drop in the performance of the first FETCH process becomes a problem, do not specify this optimization method.
3. When this optimization method is applied, the required memory size increases because the results of the functions provided by plug-ins are obtained in batches of multiple rows. For details about the required memory size, see the HiRDB Version 8 Installation and Design Guide.

Notes

1. For a table for which no indexes have been defined, the following optimization methods have no effect, even if they are specified:
   Forced nest-loop-join
   Making multiple SQL objects
   Prioritized nest-loop-join
   Priority of OR multiple index use
   Suppressing use of AND multiple indexes
   Suppressing index use (forced table scan)
   Forcing use of multiple indexes
   Suppressing creation of update-SQL work tables
   Applying key conditions that include scalar operations
   
2. When the ASSIGN LIST statement is used (except in subqueries), HiRDB always uses indexes to retrieve data. Therefore, specifying the following optimization methods has no effect:
   Suppressing use of AND multiple indexes
   Suppressing index use (forced table scan)
   
3. If optimizing information is not retrieved with the optimizing information collection utility (pdgetcst), specifying the value for making multiple SQL objects has no effect.
4. If the number of groups to be grouped is large, rapid grouping processing may not have any effect. In this case, a value of the required size should be specified by estimating PDAGGR. Specifying a value larger than the estimated value will have no beneficial effect. Remember that the use of process-specific memory increases as a larger value is specified.
5. For SQL statements that use all back-end servers to fetch data, even if the value for increasing the target floatable servers (back-end servers for fetching data) is not specified, the back-end servers for fetching data are used as floatable servers.
6. If the optimization option values for priority of OR multiple index use and suppressing index use (forced table scan) are specified at the same time, the specification for priority of OR multiple index use becomes ineffective.
7. If the optimization option values for suppressing use of AND multiple indexes and forcing use of multiple indexes are specified at the same time, multiple index use is suppressed for AND sections and is forced for OR sections.
8. If the optimization option values for forcing use of multiple indexes and suppressing index use (forced table scan) are specified at the same time, the specification for forcing use of multiple indexes becomes ineffective.
9. SQL optimization option values that are stored in the SQL_ROUTINES dictionary table are converted to decimal format (sum of unsigned integers used to specify individual optimization methods).
10. If Suppressing creation of update-SQL work tables is specified and a search with a FOR UPDATE clause specification is performed, the same row may be retrieved several times if the configuration column of an index used in the search is updated for a value that satisfies the search condition.
    

    Example:

    [Index definition]
    CREATE INDEX X1 ON T1(C1)
    [Cursor definition]
    DECLARE CR1 CURSOR FOR SELECT C1 FROM T1 WHERE C1>0 FOR UPDATE

    When this cursor is used and the next FETCH and UPDATE statements are repeated, the row that was updated to C1=10 is retrieved several times.
    

    FETCH CR1 INTO :XX
    UPDATE T1 SET C1=10 WHERE CURRENT OF CR1

    Correction methods:

    • Change the search condition so that the update value in the UPDATE statement does not satisfy the search condition of the search.
      Example:
      Change WHERE C1>0 to WHERE C1>0 AND C1< >10.
      
    • Do not specify the optimization option value for suppressing creation of update-SQL work tables for UAPs that have problems with the same row being retrieved more than once. For a stored routine, redefine the routine instead of specifying the optimization option value when defining the routine.
    • Delete the affected column from the configuration columns of the indexes to be used in the search. However, if an index configuration column is deleted, the search performance may become worse if that column significantly narrows the search scope with the search condition. Also, if part of an index is deleted, the number of index key duplications increases, and lock wait and deadlock may occur frequently. This correction method is therefore not highly recommended. If you plan to employ this correction method, be sure to verify the effects thoroughly.
    The same line may also be updated several times if an index configuration file to be used in an UPDATE statement is specified as the column name of the SET clause in that UPDATE statement, and a value that satisfies the search condition of the WHERE clause is specified as the update value.
    

<identifier or unsigned integer>

This environment variable specifies optimization methods for determining the most efficient access path by taking the database status into consideration.

The SQL extension optimizing methods can be specified with identifiers (character strings) or numbers.

Specifying the SQL extension optimizing methods with identifiers

PDADDITIONALOPTLVL="identifier"[,"identifier"]...

Examples

• Applying application of optimizing mode 2 based on cost and hash join, subquery hash execution:
  PDADDITIONALOPTLVL="COST_BASE_2","APPLY_HASH_JOIN"
  
• Applying no optimization method:
  PDADDITIONALOPTLVL="NONE"
  

Rules

1. Specify at least one identifier.
2. When specifying two or more identifiers, separate them with commas.
3. For details about the information (optimization methods) that can be specified with identifiers, see Specification values for the SQL extension optimizing option.
4. If no optimization is to be applied, specify NONE as the identifier.
5. The identifiers can be specified with uppercase and lowercase characters.
6. Even if the same identifier is specified more than once, HiRDB recognizes only one specification. However, try not to specify the same identifier more than once.
7. The character string specified for "identifier"[,"identifier"]... can have up to 575 bytes.

Specifying the SQL extension optimizing methods with numbers

PDADDITIONALOPTLVL=unsigned-integer[,unsigned-integer]...

Examples

• Applying application of optimizing mode 2 based on cost and hash join, subquery hash execution:
  PDADDITIONALOPTLVL=1,2
  
• Applying no optimization method:
  PDADDITIONALOPTLVL=0
  

Rules

1. Specify at least one unsigned integer.
2. When specifying two or more unsigned integers, separate them with commas.
3. For details about the information (optimization methods) that can be specified with unsigned integers, see Specification values for the SQL extension optimizing option.
4. If no optimization is to be applied, specify 0 as the identifier.
5. Even if the same unsigned integer is specified more than once, HiRDB recognizes only one specification. However, try not to specify the same unsigned integer more than once.
6. The character string specified for "unsigned-integer"[,"unsigned-integer"]... can have up to 575 bytes.

Relationship to the system definition

When this environment variable is omitted, the value specified in the pd_additional_optimize_level operand of the system definition is assumed. For details about the pd_additional_optimize_level operand, see the manual HiRDB Version 8 System Definition.

Relationship with SQL

The SQL extension optimizing option for an SQL statement in a stored procedure is determined by the specifications for CREATE PROCEDURE, CREATE TYPE, ALTER PROCEDURE, or ALTER ROUTINE, and is not affected by the PDADDITIONALOPTLVL specification.

If SQL optimization specifications are specified in SQL statements, those specifications have priority over the SQL extension optimizing options. For details about SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.

Specification values for the SQL extension optimizing option

Table 6-31 shows the specification values for the SQL extension optimizing option.

Table 6-31 Specification values of the SQL extension optimizing option

Number	Optimization method	Specification value
		Identifier	Unsigned integer
1	Application of optimizing mode 2 based on cost	"COST_BASE_2"	1
2	Hash join, subquery hash execution	"APPLY_HASH_JOIN"	2
3	Suppressing foreign server execution of SQL statements that contain join operations	"DETER_JOIN_SQL"	67108864
4	Forcing foreign server execution of SQL statements that contain direct products	"FORCE_CROSS_JOIN_SQL"	134217728
5	Suppressing derivation of unconditionally created high-speed search conditions for foreign server execution	"DETER_FSVR_DERIVATIVE_COND"	1073741824

Note 1

Item numbers 2 to 5 become effective when the value for application of optimizing mode 2 based on cost is specified.

Note 2

Item numbers 3 to 5 are optimization methods that become effective when data is retrieved from an external table. In other cases, these methods are ineffective.

Recommended specification values

• When HiRDB is installed for the first time, Hitachi recommends that you use optimizing mode 2 based on cost. When you use optimizing mode 2 based on cost to improve the accuracy of optimization, execute the optimizing information collection utility as needed. If the database scope (number of table rows) differs between the test environment and actual environment, the access path may change in the test environment and actual environment when the optimizing information collection utility is executed. In the test environment, specify the numbers of table rows (NROWS) being used in the actual environment in the optimizing information parameter file, specify the-s option in the optimizing information collection utility, and execute the utility.
• If you are upgrading HiRDB from a version before version 06-00, investigate whether optimizing mode 2 based on cost should be used. If you are setting up the same operating environment that was used before the version upgrade, do not use optimizing mode 2 based on cost. However, some of the SQL statements that are supported from version 06-00 always use optimizing mode 2 based on costs in optimization processing.
• If hash join is not being used, hash join, subquery hash execution does not need to be specified.

Explanation of optimization methods

1. Application of optimizing mode 2 based on cost
   This optimization method uses optimizing mode 2 based on cost to execute optimization processing. For details about optimizing mode 2 based on cost, see 4.5.1 SQL optimizing modes.
   
2. Hash join, subquery hash execution
   When a join search is executed, this optimization method applies hash join to optimize the search process. If the search involves a subquery, hashing is used to process the subquery. When deciding whether or not to apply this optimization method, consider the join methods, the execution methods for subqueries with no external references, and the execution methods for subqueries with external references. For details about these methods, see 4.5.6 Join methods, 4.5.8 Execution of subqueries with no external references, and 4.5.9 Execution of subqueries with external references.
   When this optimization method is applied, the system definitions must be specified beforehand. For details about preparing for application of hash join and subquery hash execution, see 4.5.10 Preparing for application of hash join and subquery hash execution.
   
3. Suppressing foreign server execution of SQL statements that contain join operations
   When SQL statements for accessing a foreign table are created from queries that contain accesses to the foreign table, this optimization method suppresses the creation of SQL statements that contain join operations.
   When this optimization method is specified, HiRDB does not create SQL statements that contain join operations. Instead, HiRDB creates SQL statements that fetch foreign table data that becomes the join input, and HiRDB performs the join processing.
   For details about suppressing foreign server execution of SQL statements that contain join operations, see the manual HiRDB External Data Access Version 7.
   
4. Forcing foreign server execution of SQL statements that contain direct products
   When this optimization option is specified, HiRDB tries to create SQL statements that contain direct products as must as possible when it creates SQL statements that access a foreign table from queries that contain accesses to the foreign table.
   For details about forcing foreign server execution of SQL statements that contain direct products, see the manual HiRDB External Data Access Version 7.
   
5. Suppressing derivation of unconditionally created high-speed search conditions for foreign server execution
   High-speed retrieval conditions that are derived unconditionally and can be executed in foreign servers can be suppressed.
   When high-speed search conditions are derived, generation and execution of the high-speed search condition may take a long time and the resulting access path may not be the intended path. In such cases, specify this optimization method.
   If the value for deriving high-speed search conditions is specified in the SQL optimization options, this optimization method is ignored even when specified.
   For details about deriving high-speed search conditions, see 4.5.11 Deriving high-speed search conditions.
   

For 32-bit mode

<unsigned integer> ((128-524288)) (kilobytes)

For 64-bit mode

<unsigned integer> ((128-2097152)) (kilobytes)

This environment variable specifies the hash table size when the value for hash join, hash subquery execution is specified in the SQL extension optimizing options.

Specify a value that is a multiple of 128. If the specified value is not a multiple of 128, the value is rounded up to the next multiple of 128.

When the server is set to 32-bit mode, an upper limit of 524,288 is assumed when a value from 524,289 to 2,097,152 is specified.

Specification value reference

See 4.5.10 Preparing for application of hash join and subquery hash execution.

Relationship to the system definition

If this environment variable is omitted, the HiRDB uses the value that was specified in the pd_hash_table_size operand of the system definition.

This environment variable specifies whether the default value is to be set into an embedded variable if the table data fetched into an embedded variable is a null value.

USE

Use the default value setting facility for null values.

NOUSE

Do not use the default value setting facility for null values.

For details about the default value setting facility for null values, see the HiRDB Version 8 SQL Reference manual.

For 32-bit mode

<unsigned integer> ((0-30000000)) <<1024>>

For 64-bit mode

<unsigned integer> ((0-2147483647)) <<1024>>

This environment variable specifies the maximum number of groups allowed in each server so that the memory size used in GROUP BY processing can be determined. This environment variable becomes effective when rapid grouping processing is specified as the SQL optimization option.

Estimation method

• When at least 1,024 groups will be created or when the expected performance cannot be achieved:
  Specify a large value for this environment variable; however, consider the balance with the required memory size, and increase the specified value gradually.
  If a memory shortage occurs when 1,024 is specified, specify a value according to the amount of memory available.
  
• When fewer than 1,024 groups will be created or if a memory shortage occurs:
  Specify a small value for this environment variable. If a value of the required size cannot be specified because the amount of required memory is too large, specify the largest value that can be specified, even if the value is less than the number of groups.
  

Note

If the specified value is too large, a memory shortage may occur. If the number of groups created exceeds the specified value, processing may become slow because the allocated memory size is insufficient. For details about the formula for calculating the required memory size used by the rapid grouping facility, see the HiRDB Version 8 Installation and Design Guide.

When a definition SQL is to be executed in a transaction that is executing a data manipulation SQL, this environment variable specifies whether the transaction is to be committed automatically before the definition SQL is executed. When the transaction is automatically committed before the definition SQL is executed, the open holdable cursors are closed, and the results of the preprocessed SQL statements become invalid.

YES

Automatically commit the transaction that executes the data manipulation SQL before executing the definition. When this value is specified, the open holdable cursors are closed, and the results of the preprocessed SQL statements become invalid.

NO

Execute the definition SQL after explicitly committing the transaction executing the data manipulation SQL.

This environment variable specifies whether an open cursor is to be closed automatically if a PREPARE statement reuses the SQL identifier that is using that open cursor.

PDPRPCRCLS becomes effective if the -Xe option is not specified during preprocessing. For details about preprocessing, see 8.2 Preprocessing.

YES

Close the open cursor automatically.

NO

Do not close the open cursor automatically.

This environment variable specifies whether the HiRDB client is to connect automatically with the HiRDB server if an SQL statement is executed while the client is not connected to the server.

ON

Connect to the HiRDB server automatically and then execute the SQL statement.

OFF

Do not connect to the HiRDB server automatically. In this case, the SQL statement generates an error (SQLCODE=-563).

If the SET SESSION AUTHORIZATION statement is executed while the HiRDB client is not connected to the HiRDB server, an error (SQLCODE=-563) occurs regardless of the PDAUTOCONNECT specification.

When you create a UAP, Hitachi recommends that you specify OFF in PDAUTOCONNECT because the HiRDB client must be able to determine whether it is properly connected to HiRDB.

This environment variable specifies whether the definition information of a table being used by a closed holdable cursor can be changed by another UAP between transactions. When a definition SQL is executed, the preprocessing of the holdable cursor becomes invalid.

YES

Allow another UAP to change the definition information of the table between transactions of the UAP using the holdable cursor.

The following figure shows an example of the processing when YES is specified.

Explanation

The definition SQL executed by UAP2 can be executed after the holdable cursor of UAP1 is closed and the transaction containing that holdable cursor is completed. Also, if the holdable cursor of UAP1 is reopened, error SQLCODE=-1512 occurs (the processing becomes invalid).

NO

Do not allow another UAP to change the definition information of the table between transactions of the UAP using the holdable cursor.

The following figure shows an example of the processing when NO is specified.

Explanation

The definition SQL executed by UAP2 can be executed after DISCONNECT processing of UAP1.

This environment variable specifies the timing for sending an open/close cursor request from a HiRDB client to the HiRDB server when performing a search using the cursor. By specifying this environment variable, the request is not sent to the HiRDB server when the open cursor request is received from the application but rather the open cursor is requested when data is fetched for the first time. Also, when it is detected that there is no data to be searched (SQLCODE=100), the cursor is closed. This environment variable reduces the communication overhead.

0:

The HiRDB client requests execution of cursor open/close to the HiRDB server when it receives a request from the application.

1:

When there is no data to be searched, the HiRDB server closes the cursor when it returns SQLCODE=100, without a request from the HiRDB client. If the HiRDB client has already detected SQLCODE=100 when it receives a close cursor request from the application, the HiRDB client does not send the close cursor request to the HiRDB server. The close cursor request is sent only when SQLCODE=100 has not been detected.

For an open cursor request, the operation is the same as when 0 is specified.

2:

When the client receives an open cursor request from the application, it does not request that the HiRDB server execute it, but requests opening of the cursor at the same time it sends the initial request to fetch data.

For the close cursor request, the operation is the same as when 1 is specified.

Figure 6-3 provides an overview of the processing for each setting.

Figure 6-3 Overview of processing for each setting of PDCURSORLVL

Notes

• Even when 1 or 2 is specified for this environment variable, if the client receives a request to close the cursor for a results-set returned from the procedure, the client requests that the HiRDB server execute the request.
• When 1 or 2 is specified for this environment variable, a cursor close is added to the number of SQL executions in the UAP statistical information, but the cursor close is not output to the SQL statistical information. Also, when 2 is specified for this environment variable, a cursor open is added to the number of SQL executions in the UAP statistical information, but the cursor open is not output to the SQL statistical information.
• When 1 or 2 is specified for this environment variable, the open/close cursor operation code is output to the SQL trace. If the FETCH statement is used to open or close the cursor, SQL statistical information, access path information, and SQL runtime interim results of the open/close cursor are output to the FETCH side.
• In the case of a HiRDB/Parallel Server, the first data fetch may take a long time if there is a long delay from when the open cursor is executed until the first data is fetched.
• When 2 is specified for this environment variable, even though the PREPARE statement is executed again on a cursor that is open before the initial fetch call, an error does not occur because the open cursor request is not sent to the HiRDB server. When the PREPARE statement is executed again, the cursor opening needs to be executed again because the PREPARE statement information is used as cursor information.
• When 1 or 2 is specified for this environment variable, even though the PREPARE statement or OPEN statement is executed without executing the CLOSE statement after SQLCODE=100 is detected, an error does not occur because the cursor is already closed. Also, when the FETCH statement is executed following detection of SQLCODE=100, SQLCODE=-501 (which indicates that the cursor is not open) is returned without producing a no-data-to-be-searched situation.

<identifier> ((up to 8 characters))

This environment variable specifies the name of the SQL reserved word deletion file when the SQL reserved word deletion facility is used. The keywords to be deleted from the SQL reserved words are specified in the SQL reserved word deletion file.

Relationship to the system definition

When PDDELRSVWDFILE is specified, an SQL reserved word deletion file must be specified in the pd_delete_reserved_word_file operand of the system definition. For details about the SQL reserved word deletion file, see the manual HiRDB Version 8 System Definition.

Note

For a Windows version HiRDB, the SQL reserved word deletion file name is not case sensitive. Note. therefore, that files having the same name except for case differences are treated as the same file.

This environment variable specifies the hashing method when Apply hash join, subquery hash execution is selected as the SQL extension optimizing option.

TYPE1

This specification preserves the HiRDB performance found in versions before version 07-02.

TYPE2

Hashing is performed more uniformly compared to TYPE1.

Specification value guidelines

• Normally specify TYPE2. However, if the hashing method does not distribute the records uniformly because of the data in the columns specified in the join condition, specify TYPE1.
• TYPE1 is the HiRDB hashing method found in versions before version 07-02. If TYPE1 is specified after the HiRDB version is upgraded and the expected performance is not achieved, specify TYPE2.

Relationship to the system definition

When this environment variable is omitted, the specification of the pd_hashjoin_hashing_mode operand in the system definition is assumed.

<unsigned integer> ((1-4096)) <<1>>

This environment variable specifies the number of rows to be sent in one transfer when the server transfers retrieval results to the client.

Note that the actual number of rows that are sent changes according to the specification value of the PDBLKBUFFSIZE client environment definition. For details about the number of rows to be sent, see 4.7(4) Number of rows transferred in one transmission.

Specifying a large value reduces the communication overhead and shortens the retrieval time, but much more memory becomes necessary. Therefore, be sure to consider the balance of retrieval time and memory.

For details about the calculation expression for memory required in the server, see Formula for the size of memory required during block transfer or array FETCH in the manual HiRDB Version 8 Installation and Design Guide. The calculation expression for memory required in the client is shown below.

Memory calculation expression (kilobytes)

= (600 + 19 retrieval-column-count + (7 retrieval-column-count + defined-column-length^*) PDBLKF-value) 4096 4

^* The unit is bytes.

This environment variable specifies whether the block transfer facility is to be applied when a table having a BINARY-type selection expression with a defined length of 32,001 bytes or more is searched. For details about the block transfer facility, see 4.7 Block transfer facility.

YES

Apply the block transfer facility.

NO

Do not apply the block transfer facility.

When this value is specified, the data is transferred one row at a time even if the value specified in the PDBLKF client environment definition is 2 or higher and the value specified in PDBLKBUFFSIZE is 1 or higher.

<unsigned integer> ((0-2000000)) <<0>> (kilobytes)

This environment variable specifies the size of the server-client communication buffer used by the block transfer facility.

If 0 is specified, HiRDB calculates the communication buffer size (in units of bytes) from the value of the PDBLKF client environment definition and the maximum length of one row.

The value specified in PDBLKBUFFSIZE affects the following values for buffer size and number of rows:

• Size of the server-client communication buffer used for search result transfer
• Number of search result rows that a single server or a front-end server sends to the client during one communication

This environment variable is limited to the AIX 5L version.

This environment variable specifies whether immediate acknowledgment is to be used for communication between the HiRDB server machine and the HiRDB client machine. For details about using immediate acknowledgment for HiRDB communication, see the HiRDB Version 8 Installation and Design Guide.

YES:

Use immediate acknowledgment.

NO:

Do not use immediate acknowledgment.

Notes

• This environment variable is not valid when the HiRDB server to be connected is on the same machine.
• If you use the tcp_nodelayack OS parameter to specify sending of an immediate acknowledgement, the capability to delay acknowledgment sending is suppressed throughout the entire system. In such a case, immediate acknowledgment is used in the entire system regardless of the setting of this environment variable.

Relationship with system definition

• When the HiRDB server is another server machine in an AIX 5L version environment, immediate acknowledgment can also be used for HiRDB servers. To use immediate acknowledgment between HiRDB servers, set Y for the pd_ipc_tcp_nodelayack operand in the system common definition.

<unsigned integer> ((0-10))

If the inner replica facility is being used and an RDAREA that is not the current RDAREA in the inner replica group is to be accessed, this environment variable specifies the generation number of that RDAREA. The generation number of the original RDAREA is 0.

PDDBACCS is applied to all inner replica groups defined in HiRDB. If a replication RDAREA of the generation specified in PDDBACCS is not defined, the current RDAREA in the target inner replica group is processed. Therefore when setting up a test environment that uses replica RDAREAs, you must check that replica RDAREAs of the specified generation are defined for all RDAREAs to be accessed. This is so that an RDAREA for actual operation is not accessed by mistake.

This environment variable specifies whether to execute a UAP on the original RDAREA during online processing in a replica RDAREA.

YES

Execute the UAP on the original RDAREA being held for online reorganization.

NO

Do not execute the UAP on the original RDAREA being held for online reorganization.

This environment variable specifies the space conversion level for data storage, comparison, and search processing. Space conversion is not executed when a definition SQL is executed.

0

Do not convert spaces.

1

Convert spaces in data for literals, embedded variables, and ? parameters of data manipulation SQL as follows:

• If a character string literal is determined to be a national character string literal, two single-byte space bytes are converted to a double-byte space character. If one single-byte space character appears alone, it is not converted.
• For a mixed character string literal, one double-byte space character is converted to two single-byte space characters.
• During data storage to a national character string-type column or comparison with a national character string-type value expression, two single-byte space bytes in embedded variables and ? parameters are converted to one double-byte space character. If one single-byte space character appears alone, it is not converted.
• During data storage to a mixed character string-type column or comparison with a mixed character string-type value expression, double-byte space characters in embedded variables and ? parameters are converted to two single-byte space characters.

3

In addition to the conversions for space conversion level 1, convert each double-byte space character to two single-byte space characters when data in a national character string-type value expression is searched.

Relationship to the system definition

If this environment variable is omitted, the HiRDB system uses the value that was specified in the pd_space_level operand of the system common definition.

Notes

1. If the space conversion level is changed, the UAP results obtained before and after the change may be different. To obtain the same UAP results, do not change the space conversion level.
2. If space conversion level 3 is specified and the data is sorted, the expected results may not be obtained because HiRDB applies space conversion to the sort results.
3. When data is stored to a cluster key column, a unique error may occur as a result of space conversion. If this occurs, store the data without applying space conversion, or make all spaces in the existing database uniform (apply space conversion with the database reorganization utility).
4. Space conversion of a national character string is executed in two-byte units from the beginning of the string.
5. If space conversion level 1 or 3 is specified and the UAP uses the hash function for table partitioning to determine the storage RDAREA of a hash-partitioned table, the space conversion level must also be specified as an argument of the hash function for table partitioning. Otherwise, the results of the hash function for table partitioning may become invalid. For details about the hash function for table partitioning, see G.1 Hash function for table partitioning.
6. If space conversion level 1 or 3 is specified and the UAP executes key range partitioning on a key range-partitioned table that has a national character string-type or mixed character string-type column in the partitioning key, the partitioning key value must be converted with the space conversion function. Otherwise, the results of key range partitioning may become invalid. For details about the space conversion function, see G.2 Space conversion function.

<identifier>

This environment variable specifies the identifier of the XDM/RD E2 database to be connected when the XDM/RD E2 connection facility is used. The database identifier refers to the RD node name specified in the XDM subsystem definitions.

This environment variable specifies whether OpenTP1 service names are to be reported to XDM/RD E2 when the XDM/RD E2 connection facility is used.

PDTP1SERVICE cannot be specified if HiRDB client library cltdll.dll is being used in the Windows version. This environment variable can be specified if the HiRDB client is relinked to another HiRDB client library (for example, pdcltm32.dll).

YES

Report OpenTP1 service names to XDM/RD E2.

When OpenTP1 service names are reported to XDM/RD E2, the XDM/RD E2 statistical information can be analyzed for each service. This function is supported only if the XDM/RD E2 version is 09-01 or later.

When OpenTP1 is not used, or if the service is not an OpenTP1 service (for example, if the service is SUP), the service name is not reported even when YES is specified.

NO

Do not report OpenTP1 service names.

This environment variable is valid for Windows clients. For UNIX clients, this environment variable is invalid even when specified.

This environment variable specifies the character code classification used by the client when the XDM/RD E2 connection facility is used.

SJIS

Use shift JIS kanji codes.

UTF-8

Use Unicode (UTF-8) codes. When UTF-8 is specified, specify NOUSE in the PDCLTCNVMODE client environment definition, or omit PDCLTCNVMODE.

Rules when UTF-8 is specified

1. Unicode (UTF-8) codes can be used in input/output data handled by embedded variables and data handled by the ? parameter.
2. Only ASCII codes can be specified in SQL statements specified in a UAP. To specify a non-ASCII character (kanji, single-byte katakana, or Gaiji character) in an SQL statement, use the PREPARE or EXECUTE IMMEDIATE statement and specify the SQL statement in an embedded variable.
3. Error messages returned from XDM/RD E2 and stored in the SQL Communications Area, column names stored in the Column Name Descriptor Area (SQLCNDA), and data type names stored in the Type Name Descriptor Area (SQLTNDA) use Unicode (UTF-8) codes. For this reason, if characters other than ASCII codes are contained in these values and are output as Shift JIS kanji codes, they may not be displayed correctly.
4. When the XDM/RD E2 side converts character codes from Unicode (UTF-8) to EBCDIK or KEIS, or EBCDIK or KEIS to Unicode (UTF-8), the data length may be changed. Therefore, pay attention to the definition length of embedded variables.

This environment variable specifies the position of the constraint name definition when a referential or check constraint is defined.

LEADING

Specify the constraint name definition before the constraint definition.

TRAILING

Specify the constraint name definition after the constraint definition.

Relationship to the system definition

If this environment variable is omitted, the value of the pd_constraint_name operand in the system definition is assumed.

This environment variable can be specified when HiRDB/Parallel Server is used.

This environment variable specifies whether the BES connection holding facility is to be used. For details about the BES connection holding facility, see the HiRDB Version 8 System Operation Guide.

YES

Use the BES connection holding facility.

NO

Do not use the BES connection holding facility.

Relationship to the system definition

If this environment variable is omitted, the value of the pd_bes_connection_hold operand in the system definition is assumed.

<unsigned integer> ((0-3600)) (seconds)

This environment variable specifies the BES connection holding period when the BES connection holding facility is used.

When the BES connection holding facility is used, the back-end server monitors the time elapsed from when a transaction ends until the next transaction is executed. If the time until the next transaction is executed falls within the PDBESCONHTI specification value, the back-end server continues the BES connection holding facility. If the time exceeds the PDBESCONHTI specification value, the back-end server disconnects the connection with the front-end-server.

Notes

1. If 0 is specified, the back-end server does not monitor the time. The connection between the front-end server and the back-end server is disconnected only when the connection between the front-end server and the client is disconnected, such as when the DISCONNECT (xa_close when the XA library is used) SQL statement is executed or the time specified by the PDCWAITTIME client environment definition is exceeded.
2. PDBESCONHTI becomes valid when YES is specified in PDBESCONHOLD.

<unsigned integer> ((1-4096))

This environment variable can be specified when the HiRDB is the HP-UX or AIX 5L version.

This environment variable specifies the number of rows to be transferred in one transfer operation when retrieval results are transferred from a distributed server to a distributed client. A value roughly between 50 and 80 should be specified. Specify 1 for this environment variable if batch retrieval is not used.

Specifying a large value reduces the communications overhead and the search time but increases the amount of required memory. Therefore consider the balance with the memory size when determining the value. The following formula can be used to calculate the amount of required memory:

(328 + 32 a + b) c (in bytes)

a

Number of items specified in the SELECT clause

b

Total length of the character string data output by one transfer operation

c

Value specified for PDRDABLKF

Relationship with other definitions

When PDRDABLKF is omitted, the number of batch retrieval rows specified by the block_fetch_count operand (SQL environment definition of DF/UX) is assumed. For details about the block_fetch_count operand, see the manual Distributed Database System DF/UX.

This environment variable specifies whether the column information or index information collected the first time an ODBC function (SQLColumns() or SQLStatistics()) is issued is to be cached.

0

Do not cache the information.

Column information or index information is collected by accessing the server each time the SQLColumns() or SQLStatistics() function is called.

1

Cache the column information or index information collected the first time the function is called.

However, the cache is not refreshed while the server is connected. Thus, if the table definition is altered while the server is connected, column information or index information that is different from the actual definition is returned. Therefore, the server connection must be terminated first.

Benefits

When the SQLColumns() and SQLStatistics() functions are called repeatedly with the same parameters, the number of communications with the server can be reduced by returning the retrieval results stored in the cache to the UAP.

Notes

To determine whether specifying this option will be effective, collect an ODBC trace and investigate whether the SQLColumns() or SQLStatistics() function is issued repeatedly with the same parameters during the same connection.

The number of rows that can be cached is x:

SQLColumns()

Approximately 60,000/(50 + table-owner-name-length + table-name-length + column-name-length + comment-length) rows

SQLStatistics()

Approximately 60,000/(50 + table-owner-name-length + table-name-length + index-name-length + column-name-length) rows

This environment variable specifies whether the ESCAPE "&" character is to be specified for the pattern character in a retrieval using a cataloging ODBC function (SQLTables(), SQLColumns(), etc.).

0

Do not specify the ESCAPE "&"character for the pattern character.

1

Specify the ESCAPE "&"character for the pattern character.

Notes

1. If the column attribute of the dictionary table is CHAR (dictionary datatype mchar nouse specified by the database initialization utility) and a double-byte character containing code 0x26 is used in the table name and the column name, 0 should specified in this option. If 1 is specified and the HiRDB system is accessed through ODBC, some tables and columns may not be recognized.
2. If an underscore (_) is used in the identifier of a table name, 1 should be specified in this option. If 0 is specified, some ODBC-compatible software programs may not be able to access the identifier that uses the underscore.

This environment variable specifies whether the SQLGetData function of the ODBC functions is to repeatedly retrieve data for columns from which data has already been retrieved.

Normally, when data retrieval is repeated for a column after data has already been retrieved from that column, SQL_NO_DATA is returned as the return value.

YES

The SQLGetData function can retrieve data repeatedly for columns from which data has already been retrieved.

NO

When the SQLGetData function goes to retrieve data for columns for which data has already been retrieved, SQL_NO_DATA is returned as the return value.

Application standard

This environment variable is specified when data is to be retrieved repeatedly for the same column. For example, this environment variable is specified when a host UAP that expects SQL_SUCCESS for repeated data retrieval executions is used.

Note

When Internet Banking Server is used, set PDGDATAOPT=YES in the HiRDB.ini file of the HiRDB client. If this specification is not set, the following problem may occur after the customer information management utility or transaction history management utility of Internet Banking Server is used to log into HiRDB. After a function selection key, such as Register Customer, Update Customer Information, or Reference Customer Information is pressed, screen operations other than the Return button may become disabled.

This environment variable specifies whether the locator facility is to be used to partition and retrieve data when a database access tool is used to retrieve BLOB-type or BINARY-type column data. The database access tools are the ODBC driver, the OLE DB provider, and the HiRDB.Net data provider.

YES

Use the locator facility to partition and retrieve data when a database access tool is used to retrieve BLOB-type or BINARY-type column data.

NO

Do not use the locator facility when a database access tool is used to retrieve BLOB-type or BINARY-type column data.

Application standard

If NO is specified (NO is also the assumed value when this environment variable is omitted), the database access tool allocates a data reception area that has the defined length of the column. The HiRDB client also requires a data reception area that has the defined length of the column.

Since a memory shortage may occur during execution if the defined length of the column is large, specify YES to avoid running out of memory. Note that when YES is specified, the number of communications with the HiRDB server increases by the number of partition acquisitions.

<unsigned integer> ((4-2097152)) <<100>> (kilobytes)

This environment variable specifies the partition acquisition size when PDODBLOCATOR=YES is specified.

Specification value guideline

Consider the distribution of the actual data length, and specify a value that reduces the number of partition acquisitions but does not trigger a memory shortage.

This environment variable specifies whether warnings should be skipped for ODBC connections. For non-ODBC connections, this environment variable is ignored even if it is specified.

YES

The ODBC driver returns SQL_SUCCESS as the SQLFetch() return value even if SQLFetch() processing is prolonged and SQLWARN is set.

NO

The ODBC driver returns SQL_SUCCESS_WITH_INFO as the SQLFetch() return value if SQLFetch() processing is prolonged and SQLWARN is set.

Application standard

When SQLWARN is set in the SQL Communications Area of HiRDB during retrieval processing, the ODBC driver returns SQL_SUCCESS_WITH_INFO as the SQLFetch() return value. However, depending on the higher-level application^* that calls the ODBC driver, retrieval processing may be terminated by the SQL_SUCCESS_WITH_INFO return value. If YES is specified in this environment variable, SQL_SUCCESS is returned as the return value even if SQLWARN is set to the SQL Communications Area during retrieval processing, and retrieval processing can continue.

^* For example, if ADO.Net is used and connected to HiRDB through an ODBC connection, retrieval processing may be terminated by the SQL_SUCCESS_WITH_INFO return value.

This environment variable specifies whether the ODBC 3.0 driver is to be operated in Microsoft Jet database engine compatible mode rather than based on the ODBC 3.0 specifications.

YES:

The ODB C3.0 driver operates in Microsoft Jet database engine compatible mode.

NO:

The ODBC 3.0 driver operates as specified.

Application standard

Specify this environment variable when an application program uses a Microsoft Jet database engine, such as Microsoft Access, to access HiRDB. When this variable is not specified, #Delete may be displayed as the search result, or inserted data may be converted incorrectly.

This environment variable specifies whether delayed batch creation of plug-in indexes is to be used. For details about delayed batch creation of plug-in indexes, see the HiRDB Version 8 System Operation Guide.

YES

Use delayed batch creation of plug-in indexes.

NO

Do not use delayed batch creation of plug-in indexes.

For details, see the manual for the target plug-in.

<unsigned integer> ((1-1048574000)) <<8192>> (kilobytes)

This environment variable specifies the initial size of the index information file for delayed batch creation of plug-in indexes. This specification is effective when the index information file is to be created in the HiRDB file system area.

When this environment variable is specified, PDPLGIXMK=YES should also be specified.

<unsigned integer> ((1-1048573000)) <<8192>> (kilobytes)

This environment variable specifies the extension size of the index information file for delayed batch creation of plug-in indexes. When the index information file becomes full, the file is extended by the value specified in this environment variable. This specification is effective when the index information file is to be created in the HiRDB file system area.

When this environment variable is specified, PDPLGIXMK=YES should also be specified.

<path name> PDCLTPATH setting

This environment variable specifies the Exception trace log file storage directory with the Type4 JDBC driver. To specify the file storage directory, specify the absolute path of the directory (maximum of 256 bytes). This environment variable can be specified only when the Type4 JDBC driver is used.

For details about the Exception trace log, see 17.13 Exception trace log. For other details, see system property HiRDB_for_Java_FileDIR in 17.13.1(2)(b) System property settings.

<unsigned integer>((1-50)) (5)

This environment variable specifies the number of outputs to the Exception trace log file with the Type4 JDBC driver. This environment variable can be specified only when the Type4 JDBC driver is used.

For details about the Exception trace log, see 17.13 Exception trace log. For other details, see system property HiRDB_for_Java_FileOutNUM in 17.13.1(2)(b) System property settings.

<unsigned integer>((500-10000)) (1000)

This environment variable specifies the number of acquired information items in the Exception trace log memory. This environment variable can be specified only when the Type4 JDBC driver is used.

For details about the Exception trace log, see 17.13 Exception trace log. For other details, see system property HiRDB_for_Java_OnMemNUM in 17.13.1(2)(b) System property settings.

<unsigned integer>((0~5)) (1)

This environment variable specifies the trace acquisition level of the Exception trace log with the Type4 JDBC driver. If 0 is specified, the Exception trace log is not acquired. This environment variable can be specified only when the Type4 JDBC driver is used.

For details about the Exception trace log, see 17.13 Exception trace log. For other details, see system property HiRDB_for_Java_TraceLevel in 17.13.1(2)(b) System property settings.