Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
- Organization of this subsection
- (1) PDHOST=HiRDB-server-host-name[,secondary-system-HiRDB-server-host-name]
- (2) PDNAMEPORT=HiRDB-server-port-number
- (3) PDFESHOST=front-end-server-host-name[:port-number-of-unit-containing-front-end-server][,secondary-system-front-end-server-host-name][:port-number-of-unit-containing-secondary-system-front-end-server]
- (4) PDSERVICEGRP=server-name
- (5) PDSRVTYPE={WS|PC}
- (6) PDSERVICEPORT=high-speed-connection-port-number[,secondary-system-high-speed-connection-port-number]
- (7) PDFESGRP=FES-group[,switchover-FES-group[,switchover-FES-group]...]
- (8) PDCLTRCVPORT=client-receive-port-number
- (9) PDCLTRCVADDR={client-IP-address|client-host-name}
- (10) PDTMID=OLTP-identifier
- (11) PDXAMODE={0|1}
- (12) PDTXACANUM=maximum-number-of-concurrent-transaction-executions-per-process
- (13) PDXARCVWTIME=transaction-recovery-wait-time
- (14) PDXATRCFILEMODE={LUMP|SEPARATE}
- (15) PDXAAUTORECONNECT={YES|NO}
- (16) HiRDB_PDHOST=HiRDB-server-host-name[,secondary-system-HiRDB-server-host-name]
- (17) HiRDB_PDNAMEPORT=HiRDB-server-port-number
- (18) HiRDB_PDTMID=OLTP-identifier
- (19) HiRDB_PDXAMODE={0|1}
- (20) PDUSER=authorization-identifier[/password]
- (21) PDCLTAPNAME=identification-name-of-UAP-to-be-executed
- (22) PDCLTLANG={SJIS|CHINESE|UJIS|C|UTF-8|CHINESE-GB18030}
- (23) PDLANG={UTF-8|SJIS|CHINESE|CHINESE-GB18030|ANY}
- (24) PDDBLOG={ALL|NO}
- (25) PDEXWARN={YES|NO}
- (26) PDSUBSTRLEN={3|4|5|6}
- (27) PDCLTCNVMODE={AUTO|NOUSE|UJIS|UJIS2|UTF8|UTF8MS|UTF8_TXT|UTF8_EX|UTF8_EX2|UTF8MS_TXT|UCS2_UJIS|UCS2_UTF8}
- (28) PDCLTGAIJIDLL=user-defined-external-character-conversion-DLL-file-name
- (29) PDCLTGAIJIFUNC=user-defined-external-character-conversion-function-name
- (30) PDCLTGRP=client-group-name
- (31) PDTCPCONOPT={0|1}
- (32) PDAUTORECONNECT={YES|NO}
- (33) PDRCCOUNT=CONNECT-retry-count-with-automatic-reconnect-facility
- (34) PDRCINTERVAL=CONNECT-retry-interval-with-automatic-reconnect-facility
- (35) PDUAPENVFILE=UAP-environment-definition-file-name
- (36) PDDBBUFLRU={YES|NO}
- (37) PDHATRNQUEUING=NO
- (38) PDCLTBINDLOOPBACKADDR={YES|NO}
- (39) PDASTHOST=HiRDB-Control-Manager-Agent-host-name[,secondary-system-HiRDB-Control-Manager-Agent-host-name]
- (40) PDASTPORT=HiRDB-Control-Manager-Agent-port-number
- (41) PDSYSTEMID=HiRDB-identifier-of-HiRDB-server-managed-by-HiRDB-Control-Manager-Agent
- (42) PDASTUSER=OS-user-name/password
- (43) PDCMDWAITTIME=maximum-client-wait-time-during-command-execution
- (44) PDCMDTRACE=command-trace-file-size
- (45) PDIPC={MEMORY|DEFAULT}
- (46) PDSENDMEMSIZE=data-send-memory-size-in-client
- (47) PDRECVMEMSIZE=data-receive-memory-size-in-client
- (48) PDCWAITTIME=maximum-client-wait-time
- (49) PDSWAITTIME=maximum-server-wait-time-during-transaction-processing
- (50) PDSWATCHTIME=maximum-server-wait-time-outside-transaction-processing
- (51) PDCWAITTIMEWRNPNT=output-timing-for-SQL-runtime-warning
- (52) PDKALVL={0|1|2}
- (53) PDKATIME=packet-send-interval
- (54) PDTIMEDOUTRETRY=retry-count
- (55) PDNBLOCKWAITTIME=connection-establishment-monitoring-time-in-nonblock-mode
- (56) PDCONNECTWAITTIME=maximum-wait-time-in-HiRDB-client-during-server-connection
- (57) PDCLTPATH=trace-file-storage-directory
- (58) PDSQLTRACE=SQL-trace-file-size
- (59) PDUAPERLOG=client-error-log-file-size
- (60) PDERRSKIPCODE=SQLCODE[,SQLCODE]...
- (61) PDPRMTRC={YES|NO|IN|OUT|INOUT}
- (62) PDPRMTRCSIZE=maximum-data-length-of-parameter-information-output-to-SQL-trace
- (63) PDTRCMODE={ERR|NONE}
- (64) PDUAPREPLVL={[s][u][p][r]|a}
- (65) PDREPPATH=storage-directory-for-UAP-statistical-report-files
- (66) PDTRCPATH=storage-directory-for-dynamic-SQL-trace-files
- (67) PDSQLTRCOPENMODE={CNCT|SQL}
- (68) PDSQLTEXTSIZE=SQL-statement-size
- (69) PDSQLEXECTIME={YES|NO}
- (70) PDRCTRACE=reconnect-trace-file-size
- (71) PDWRTLNPATH=storage-directory-for-files-to-which-WRITE-LINE-statement-value-expression-values-are-output
- (72) PDWRTLNFILSZ=maximum-size-of-output-files-for-WRITE-LINE-statement-value-expression-values
- (73) PDWRTLNCOMSZ=total-size-of-WRITE-LINE-statement-value-expression-values
- (74) PDUAPEXERLOGUSE={YES|NO}
- (75) PDUAPEXERLOGPRMSZ=maximum-data-length-of-parameter-information
- (76) PDARYERRPOS={YES|NO}
- (77) PDDNDPTRACE=method-trace-file-size
- (78) PDVWOPTMODE ={0|1|2}
- (79) PDTAAPINFPATH=access-path-information-file-output-directory-name
- (80) PDTAAPINFMODE={0|1}
- (81) PDTAAPINFSIZE=access-path-information-file-size
- (82) PDSTJTRNOUT={YES|NO}
- (83) PDLOCKLIMIT=maximum-locked-resource-request-count-per-user
- (84) PDDLKPRIO={96|64|32}
- (85) PDLOCKSKIP={YES|NO}
- (86) PDFORUPDATEEXLOCK={YES|NO}
- (87) PDISLLVL=data-guarantee-level
- (88) PDSQLOPTLVL=SQL-optimization-option[,SQL-optimization-option]...
- (89) PDADDITIONALOPTLVL=SQL-extension-optimizing-option[,SQL-extension-optimizing-option]...
- (90) PDHASHTBLSIZE=hash-table-size-when-hash-join-subquery-hash-execution-is-applied
- (91) PDDFLNVAL={USE|NOUSE}
- (92) PDAGGR=group-count-resulting-from-grouping
- (93) PDCMMTBFDDL={YES|NO}
- (94) PDPRPCRCLS={YES|NO}
- (95) PDAUTOCONNECT={ON|OFF}
- (96) PDDDLDEAPRPEXE={YES|NO}
- (97) PDDDLDEAPRP={YES|NO}
- (98) PDLCKWAITTIME=lock-release-wait-time
- (99) PDCURSORLVL={0|1|2}
- (100) PDDELRSVWDFILE=SQL-reserved-word-deletion-file-name
- (101) PDHJHASHINGMODE={TYPE1|TYPE2}
- (102) PDCALCMDWAITTIME=maximum-wait-time-for-CAL-COMMAND-statement
- (103) PDSTANDARDSQLSTATE={YES|NO}
- (104) PDBLKF=block-transfer-row-count
- (105) PDBINARYBLKF={YES|NO}
- (106) PDBLKBUFFSIZE=communication-buffer-size
- (107) PDBLKFUPD={YES|NO}
- (108) PDBLKFERRBREAK={YES|NO}
- (109) PDNODELAYACK={YES|NO}
- (110) PDBINDRETRYCOUNT=bind-system-call-retries-count
- (111) PDBINDRETRYINTERVAL=bind-system-call-retry-interval
- (112) PDCLTSIGPIPE={CATCH|IGNORE}
- (113) PDDBACCS=generation-number-of-RDAREA-to-be-accessed
- (114) PDDBORGUAP={YES|NO}
- (115) PDSPACELVL={0|1|3}
- (116) PDCLTRDNODE=XDM/RD-E2-database-identifier
- (117) PDTP1SERVICE={YES|NO}
- (118) PDRDCLTCODE={SJIS|UTF-8}
- (119) PDCNSTRNTNAME={LEADING|TRAILING}
- (120) PDBESCONHOLD={YES|NO}
- (121) PDBESCONHTI=BES-connection-holding-period
- (122) PDODBSTATCACHE={0|1}
- (123) PDODBESCAPE={0|1}
- (124) PDGDATAOPT={YES|NO}
- (125) PDODBLOCATOR={YES|NO}
- (126) PDODBSPLITSIZE=partition-acquisition-size
- (127) PDODBCWRNSKIP={YES|NO}
- (128) PDJETCOMPATIBLE={YES|NO}
- (129) PDPLGIXMK={YES|NO}
- (130) PDPLUGINNSUB
- (131) PDPLGPFSZ=initial-size-of-delayed-batch-creation-index-information-file
- (132) PDPLGPFSZEXP=extension-value-of-delayed-batch-creation-index-information-file
- (133) PDJDBFILEDIR=exception-trace-log-file-storage-directory
- (134) PDJDBFILEOUTNUM=number-of-outputs-to-exception-trace-log-file
- (135) PDJDBONMEMNUM=number-of-acquired-information-items-in-exception-trace-log-memory
- (136) PDJDBTRACELEVEL=trace-acquisition-level-of-exception-trace-log
- (137) PDXDSHOST=XDS-host-name[,secondary-XDS-host-name]
- (138) PDXDSPORT=XDS-port-number
- (139) PDXVWOPT=output-of-access-path
(1) PDHOST=HiRDB-server-host-name[,secondary-system-HiRDB-server-host-name]
~ <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 edition
Specify the host name of the primary system.
- For the Windows edition
Specify the virtual network name registered for the MSCS or MSFC network name. For details about the virtual network, see the HiRDB Version 9 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
- 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.
- When a loopback address is specified in the pdunit -x operand in the system common definition
- Specify the same loopback address in this environment variable in IP address format.
~ <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 9 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.
(3) PDFESHOST=front-end-server-host-name[:port-number-of-unit-containing-front-end-server][,secondary-system-front-end-server-host-name][:port-number-of-unit-containing-secondary-system-front-end-server]
~ <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 edition
Specify the host name of the primary system.
- For the Windows edition
Specify the virtual network name registered for the MSCS or MSFC network name. For details about the virtual network, see the HiRDB Version 9 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
- 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, cancellation of the HiRDB server process might fail and a server process might remain after the client's maximum wait time (PDCWAITTIME) has elapsed.
- 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
- 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.
- This environment variable should be specified with PDSERVICEGRP.
- Notes
- 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.
- 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.
- 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.
- 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
- The time required to connect to the HiRDB server can be shortened by specifying this environment variable simultaneously with PDSERVICEPORT.
- When using a HiRDB/Parallel Server, also specify PDFESHOST.
- Note
- 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.
- 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 version.
- PC
- Specify this server type if the HiRDB server is the Linux or Windows edition.
(6) PDSERVICEPORT=high-speed-connection-port-number[,secondary-system-high-speed-connection-port-number]
~ <unsigned integer> ((5001-65535))
- This environment variable specifies the port number for high-speed connection when the high-speed connection facility is used. This is the port number for the scheduler process specified in the system definition. The port number for the scheduler process is specified in the following operands in the system definition:
- pd_service_port operand
- pd_scd_port operand
- -s option in the pdunit operand
For details about each operand, see the manual HiRDB Version 9 System Definition.
You specify this operand when a firewall or NAT is installed at the HiRDB server. For details about the settings applicable when a firewall or NAT is installed, see the HiRDB Version 9 Installation and Design Guide.
For multiple front-end servers, specify the high-speed connection port number of the front-end server to be connected.
Note that you must determine the connection target in such a manner that the server load is distributed, because the front-end server to be connected is fixed.
- 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:
- If the HiRDB server runs Windows or Linux, specify PC in the PDSRVTYPE environment variable.
- 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.
- If a communication failure occurs in an environment where the HiRDB server is connected via a wide area LAN, the system manager process might be affected adversely and might not be able to accept many concurrent HiRDB connection requests. Therefore, if your system connects via a wide area LAN, we recommend that you specify this environment variable.
(7) PDFESGRP=FES-group[,switchover-FES-group[,switchover-FES-group]...]
~ <character string> ((up to 1024 bytes))
Specify this environment variable if you use a high-speed connection with a HiRDB/Parallel Server. When you specify this environment variable, you must also specify the following operands in the system definition:
- pd_service_port operand
- pd_scd_port operand
- -s option in the pdunit operand
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). Delimit the connection destinations with a colon (:). A specification example is shown below.
host1:fes1:20001
- You can specify in PDFESHOST two host names (for the primary and secondary systems), but only one host name can be specified for a single FES group. Similarly, you can specify two port numbers in PDSERVICEPORT, but only one port number can be specified for a single FES group.
- 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
- If this environment variable is specified, the PDFESHOST, PDSERVICEGRP, and PDSERVICEPORT specifications are ignored.
- 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.
- 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.
- If a switchover FES group is specified and all connections to multiple FESs result in errors, only the error information for the last FES whose connection was attempted is returned to the UAP.
- Usage example
- When only one FES group is specified
![[Figure]](FIGURE/ZU060602.GIF)
- 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
![[Figure]](FIGURE/ZU060603.GIF)
- 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
![[Figure]](FIGURE/ZU060604.GIF)
- 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
- 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.
- The HiRDB client uses one port number for one connection to the HiRDB server. Consequently, one UAP uses multiple port numbers in the following cases:
The ODBC uses multiple connections.
The multi-connection facility is used.
- When multiple UAPs are executed concurrently, one port number can be used only by one UAP. Therefore, if a range that includes the same port numbers is specified for multiple UAP 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.
- 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.
- If the ODBC is used via a product such as Microsoft Access, multiple connections with the HiRDB server are established implicitly.
- 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.
- Any port number that is being used by other programs cannot be used by the HiRDB client.
- 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.
- 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.
(9) PDCLTRCVADDR={client-IP-address|client-host-name}
~ <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
- If you omit this environment variable, the IP address corresponding to the client machine's default host name is assumed. You must register the client machine's default host name in the hosts file or DNS (as a maximum of 256 bytes). An error will result if the default host name is not registered in the hosts file or DNS. If the HiRDB client and the HiRDB server are configured on the same machine in a HiRDB/Single Server and the default host name is not registered in the hosts file or DNS, the HiRDB server's host name will be assumed as the IP address.
- 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.
- PDCLTRCVADDR is ignored in the following cases:
The value 1 is specified in the pd_change_clt_ipaddr operand in the system definition.
MEMORY is specified in the PDIPC environment variable.
- The following table shows the client's IP address to be used depending on the combination of the values specified in PDCLTRCVADDR and PDHOST:
Value specified in PDCLTRCVADDR |
PDHOST |
Loopback address |
Local IP address |
Another machine's IP address |
Loopback address |
Value specified in PDCLTRCVADDR |
Local IP address |
Omitted |
Default host registered in the hosts file |
Default host specified in the hosts file |
No default host registered in the hosts file |
Type4 JDBC driver |
Value specified in PDHOST |
KFPZ02444-E error |
KFPZ02444-E error |
Other |
Value specified in PDHOST |
Value specified in PDHOST |
KFPZ02444-E error |
~ <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 9 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 for C++
- At the completion of a TPBroker for C++ transaction, a transaction completion process that is different from the one used by the UAP is used. Therefore, 1 must be specified in PDXAMODE when the client is linked with TPBroker. If 0 is specified, UAP 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 is used. Therefore, 1 must be specified in PDXAMODE when the client is linked with TPBroker. If 0 is specified, UAP 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 transactions cannot be completed.
- When the client is linked with TP1/EE (limited to the UNIX edition)
- Specify 0 in PDXAMODE. If PDXAMODE is omitted or if 1 is specified, transactions sometimes cannot be completed.
(12) PDTXACANUM=maximum-number-of-concurrent-transaction-executions-per-process
~ <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) x (number-of-HiRDB-servers-that-target-process-can-access)
- When linked with TP1/EE:
- Add the number of recovery and monitoring threads of TP1/EE to the above value.
- Specified value = (number-of-transactions-that-can-occur-in-target-process
- + number-of-recovery-and-monitoring-threads)
- x (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 x b
(c - d x 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
- 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.
- 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 API that is X/Open-compliant, 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.
- If the UAP is a resident process, we recommend that you specify SEPARATE.
(15) PDXAAUTORECONNECT={YES|NO}
When TP1/EE is linked, this environment variable specifies whether connection is to be re-established automatically when the HiRDB server connection status is checked at the time a transaction begins and TP1/EE is not connected to the HiRDB server. If the cause of disconnection is neither a machine failure nor a network failure, connection is re-established automatically even if this environment variable is omitted.
This environment variable is ignored, if specified, in the following cases:
- TP1/EE is not linked.
- Dynamic registration is used with the transaction manager.
- The value 0 is specified in the PDCWAITTIME client environment variable.
- YES
- Re-establish connection automatically when a transaction begins. This option affects transaction performance because communication with the HiRDB server is performed each time a transaction begins.
- NO
- Do not re-establish connection automatically when a transaction begins. If connection is lost due to a machine or network failure, an SQL error might be returned because connection will not be re-established.
- Notes
- The maximum amount of time spent checking the HiRDB server connection status is the value specified in the PDCWAITTIME client environment variable.
- If connection re-establishment fails, the error that caused the failure is returned to the UAP.
(16) HiRDB_PDHOST=HiRDB-server-host-name[,secondary-system-HiRDB-server-host-name]
~ <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
- If the IP address is to be inherited, specify the host name of the primary system.
- For the Windows edition
If the IP address is to be inherited, specify the virtual network name registered for the MSCS or MSFC network name. For details about the virtual network, see the HiRDB Version 9 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 9 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 9 Installation and Design Guide.
(20) PDUSER=authorization-identifier[/password]
~ <<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
- 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"'.
- 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.
(21) PDCLTAPNAME=identification-name-of-UAP-to-be-executed
~ <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.
(22) PDCLTLANG={SJIS|CHINESE|UJIS|C|UTF-8|CHINESE-GB18030}
This environment variable specifies the character code classification to be used in the UAPs to be processed by the preprocessor. UJIS cannot be specified with the Windows edition. When this environment variable is omitted in the Windows edition, SJIS is assumed.
- SJIS
- Shift JIS kanji codes are set as the character code classification. Use this environment variable if you specify SJIS in the Linux edition.
- 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.
- UTF-8
- UTF-8 is set as the character code classification.
- CHINESE-GB18030
- CHINESE-GB18030 is 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 |
Linux |
Windows |
SJIS |
ja_JP.SJIS |
ja_JP.PCK |
ja_JP |
Shift JIS |
Shift JIS |
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 |
UTF-8 |
UTF-8 |
UTF-8 |
UTF-8 |
UTF-8 |
UTF-8 |
CHINESE-GB18030 |
CHINESE-GB18030 |
CHINESE-GB18030 |
CHINESE-GB18030 |
CHINESE-GB18030 |
CHINESE-GB18030 |
No setting# |
ja_JP.SJIS |
ja |
ja_JP |
ja |
Shift JIS |
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 client#1 |
Character code classification of server |
SJIS |
CHINESE |
UJIS |
C |
UTF-8 |
CHINESE-GB18030 |
SJIS |
C |
-- |
-- |
-- |
-- |
-- |
CHINESE |
-- |
C |
-- |
-- |
-- |
-- |
UJIS |
-- |
-- |
C |
-- |
-- |
-- |
C |
C#2 |
-- |
C#2 |
C |
-- |
-- |
UTF-8 |
-- |
-- |
-- |
-- |
C |
-- |
CHINESE-GB18030 |
-- |
-- |
-- |
-- |
-- |
C |
- C: Can be connected.
- --: Cannot be connected.
- #1: 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.
- #2: 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.
(23) PDLANG={UTF-8|SJIS|CHINESE|CHINESE-GB18030|ANY}
You use this environment variable when you use a character encoding that is not supported by the OS in a UAP execution environment. When this environment variable is omitted, the value of the LANG environment variable is assumed.
SJIS is supported by the Linux edition only. For the Windows edition, you can specify only ANY.
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.
For details about the value of PDLANG, see 8.4.1(1) Notes about the character code classification.
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 9 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
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 9 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 9 SQL Reference.
(27) PDCLTCNVMODE={AUTO|NOUSE|UJIS|UJIS2|UTF8|UTF8MS|UTF8_TXT|UTF8_EX|UTF8_EX2|UTF8MS_TXT|UCS2_UJIS|UCS2_UTF8}
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.
The following table lists and describes the specification values:
Specification value |
Description |
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, ODBC 3.5 driver, HiRDB.NET data provider, or HiRDB SQL Executer version 02-06 or later. |
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, ODBC 3.5 driver, HiRDB.NET data provider, or HiRDB SQL Executer version 02-06 or later. |
- #
- The converted character code range is that of UCS-4.
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 |
GB18030 |
SJIS |
No conversion necessary |
UTF8, UTF8MS, UTF8_TXT, UTF8_EX, UTF8_EX2, UTF8MS_TXT |
UJIS, UJIS2 |
NOUSE |
Cannot be specified |
Unicode (UTF-8) |
Cannot be specified |
No conversion necessary |
Cannot be specified |
NOUSE |
Cannot be specified |
UJIS |
Cannot be specified |
Cannot be specified |
No conversion necessary |
NOUSE |
Cannot be specified |
UCS-2 |
Cannot be specified |
UCS2_UTF8 |
UCS2_UJIS |
Not needed |
Cannot be specified |
C |
NOUSE |
NOUSE |
NOUSE |
NOUSE |
Cannot be specified |
GB18030 |
Cannot be specified |
Cannot be specified |
Cannot be specified |
Cannot be specified |
No conversion necessary |
- Legend:
- Cannot be specified: Cannot be specified because conversion is not possible.
- No conversion necessary: Does not need to be specified because code conversion is not necessary.
Tables 6-27 and 6-28 show the differences in character code conversions when UTF8, UTF8_EX, and UTF8_EX2 are specified.
Table 6-27 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-28 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:
- 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).
- 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).
- 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.
(28) PDCLTGAIJIDLL=user-defined-external-character-conversion-DLL-file-name
~ <character string>
This environment variable is valid only in the Windows edition.
This environment variable specifies the name of the DLL file for user-defined external character conversion. This environment variable is effective during character code conversion only if a value other than NOUSE or UCS2_UTF8 is specified for PDCLTCNVMODE. If this environment variable is omitted, double-byte external characters are converted to double-byte number signs (#).
(29) PDCLTGAIJIFUNC=user-defined-external-character-conversion-function-name
~ <character string>
This environment variable is valid only in the Windows edition.
This environment variable specifies the name of the user-defined external character conversion function. This environment variable is effective for character code conversion only if 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 WINAPI#2 user-defined-external-character-conversion-function-name (
long direct,
unsigned char far *instr,
unsigned char far *outstr) ;
- #1: The DLL declaration format differs according to the compiler being used. Comply with the DLL format of the compiler being used.
- #2: 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
- 7: Data conversion from the HIRDB client UTF-16 (surrogate pair) to the HiRDB server UJIS
- Note
- When the Gaiji conversion DLL passes a Unicode character, the data is converted to two or four bytes of UCS-2 (UTF-16) 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 area size is fixed to two bytes when a value other than 7 is specified in direct, and four bytes when 7 is specified.
- instr[0] = First byte of external character to be converted
- instr[1] = Second byte of external character to be converted
- instr[2] = Third byte of external character to be converted
- instr[3] = Fourth byte of external character to be converted
- outstr
- Indicates the pointer to the post-conversion external character storage area. The area size is two bytes when a value other than 6 is specified in direct, and four bytes when 6 is specified.
- outstr[0] = First byte of character code (external character) after conversion
- outstr[1] = Second byte of character code (external character) after conversion
- outstr[2] = Third byte of character code (external character) after conversion
- outstr[3] = Fourth 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.
Table 6-29 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 |
External character codes of the Unicode BMP (Basic Multilingual Plane) |
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 character codes of the Unicode BMP (Basic Multilingual Plane) and JIS level 3 and 4 kanji codes placed in Unicode BMP |
External EUC Japanese kanji character codes |
UCS2_UJIS |
6 |
External EUC Japanese kanji character codes |
Any UTF-16 format codes |
UCS2_UJIS |
7 |
Unicode surrogate pair |
External character codes of the EUC Japanese kanji code |
UCS2_UJIS |
- The following table shows the external code ranges for each character code classification.
Table 6-30 External code ranges for each character code classification
Character code |
First byte |
Second byte |
Shift JIS kanji codes |
0xf0 to 0xfc |
0x40 to 0x7e |
0x80 to 0xfc |
Windows signed character set |
0xf0 to 0xfa |
0x40 to 0x7e |
0x80 to 0xfc |
EUC Japanese kanji codes |
0xf5 to 0xfe |
0xa1 to 0xfe |
Unicodes or MS-Unicodes# |
0xe0 to 0xf8 |
0x00 to 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.
- The following table shows the surrogate pair range.
Table 6-31 Surrogate pair range
Encoding |
High-order surrogate |
Low-order surrogate |
Byte 1 |
Byte 2 |
Byte 1 |
Byte 2 |
UTF-16 |
0xd8 to 0xdb |
0x00 to 0xff |
0xdc to 0xdf |
0x00 to 0xff |
~ <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 9 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 eliminated#1 |
Client#2 |
Server |
OLTP |
Normal connection |
Communication for connecting UAP to HiRDB server#3 |
V |
1 |
1 |
Failure recovery communication from OLTP to HiRDB server#4, #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 server#4, #5 |
V |
1 |
1 |
Other |
Normal connection |
Communication for connecting UAP to HiRDB server#3 |
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.
- #1: 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.
- #2: During failure recovery communication from OLTP, the OLTP failure recovery process becomes the client.
- #3: Some of the TCP ports that are used when the UAP connects to the HiRDB server are switched to TIME_WAIT status.
- #4: 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.
- #5: 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
- 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.
- 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 9 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.15 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
- Use PDRCCOUNT and PDRCINTERVAL to specify the number of CONNECT statement retries and the retry interval when reconnection is executed.
- 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.
- If automatic reconnect fails, an error indicating the cause is returned to the UAP.
- If specified, this environment variable is ignored and NO is assumed for an application that uses an API that is X/Open-compliant to access the HiRDB server.
- 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.
(33) PDRCCOUNT=CONNECT-retry-count-with-automatic-reconnect-facility
~ <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.
(34) PDRCINTERVAL=CONNECT-retry-interval-with-automatic-reconnect-facility
~ <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.
(35) PDUAPENVFILE=UAP-environment-definition-file-name
~ <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 9 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
- 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.
- 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.
- 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 9 System Operation Guide.
(38) PDCLTBINDLOOPBACKADDR={YES|NO}
This environment variable specifies whether a loopback address is to be used for bind() when the receive port used for communication with the HiRDB server is created.
- YES
- Use the loopback address to execute bind().
- NO
- Do not use the loopback address for bind().
Specify YES in this environment variable when you have specified a loopback address in the pdunit -x operand in the system common definition.
This environment variable is applicable to the Windows edition of HiRDB server. When you specify this environment variable, see the HiRDB Version 9 Installation and Design Guide for information about registration in the Windows firewall exception list.
(39) PDASTHOST=HiRDB-Control-Manager-Agent-host-name[,secondary-system-HiRDB-Control-Manager-Agent-host-name]
~ <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.
(40) PDASTPORT=HiRDB-Control-Manager-Agent-port-number
~ <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 edition, /etc/services; for the Windows edition, %windir%\system32\drivers\etc\services).
(41) PDSYSTEMID=HiRDB-identifier-of-HiRDB-server-managed-by-HiRDB-Control-Manager-Agent
~ <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.
(43) PDCMDWAITTIME=maximum-client-wait-time-during-command-execution
~ <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 11.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
- If the client and server are not on the same host, the PDIPC specification is ignored (the system assumes DEFAULT). In this case, the connection process may take longer.
- If you use the multithread-capable XA interface libraries (pdcltxm.dll for Windows edition clients or libzcltxk.sl(so) or libzcltyk.sl(so) for UNIX edition clients) to access HiRDB with the XA interface, and a UAP running on TPBroker for C++ or Weblogic Server is set as the client, the specification of this environment variable is ignored, and HiRDB assumes that DEFAULT was specified for PDIPC.
- If PDIPC=MEMORY is specified in UNIX-edition 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.
- If PDIPC=MEMORY is specified, the specification of PDCLTRCVADDR is ignored.
- 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 x number-of-retrieved-columns + 16 x number-of-?-parameters + SQL-statement-length)
4096
x 4
- The value calculated with this formula differs from the data size that is actually sent during communication.
(47) PDRECVMEMSIZE=data-receive-memory-size-in-client
~ <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.
- Estimation method
- Specify the value that satisfies the condition shown below, because this will help you identify the cause of slow SQL processing:
- watch_time or tran_expiration_time in the OpenTP1 system definition > PDCWAITTIME > pd_lck_wait_timeout
- If you do not use OpenTP1, you can ignore the OpenTP1 system definition.
- Notes
- 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.
- 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.
(49) PDSWAITTIME=maximum-server-wait-time-during-transaction-processing
~ <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. If the client machine or network is shut down, that shutdown status might not be detected.
If the specified value is too large and the client machine or network is shut down, it will take some time to detect the shutdown status.
Specify PDSWAITTIME to avoid process survival.
- Notes
- 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.
- 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.
(50) PDSWATCHTIME=maximum-server-wait-time-outside-transaction-processing
~ <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.
Specify 0 for a UAP for which the client is always connected (such as a resident SPP in OpenTP1 or a Cosminexus UAP using a connection pool). If 0 is specified, the HiRDB server continues to wait until it receives a request from the HiRDB client.
If 0 is specified and the client machine or network is shut down, that shutdown status might not be detected.
If the specified value is too large and the client machine or network is shut down, it will take some time to detect the shutdown status.
Specify PDSWATCHTIME to avoid process survival.
- Notes
- 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.
- 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 9 System Definition.
- Relationship with other environment variables
- The following figure shows the relationships among PDCWAITTIME, PDSWAITTIME, and PDSWATCHTIME.
Figure 6-2 Relationships among PDCWAITTIME, PDSWAITTIME, and PDSWATCHTIME
![[Figure]](FIGURE/ZU060601.GIF)
![[Figure]](FIGURE/ZU06060A.GIF)
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 9 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 9 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 specified, this environment variable is ignored and 0 is assumed for an application that uses an API that is X/Open-compliant to access HiRDB.
- 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
- 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.
- 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 (ETIMEDOUT error for the UNIX edition) 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 ETIMEDOUT 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 ETIMEDOUT 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. If the IP address for client connection is not inherited during system switchover after a network failure, switchover to the standby system will take a long time. In such an environment, you can reduce the time required for switchover to the standby system by specifying a small number of retries.
(55) PDNBLOCKWAITTIME=connection-establishment-monitoring-time-in-nonblock-mode
~ <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. If specified in the nonblock mode, PDTIMEDOUTRETRY=0 is always assumed.
- 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.
(56) PDCONNECTWAITTIME=maximum-wait-time-in-HiRDB-client-during-server-connection
~ <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 x 0.2, 300)
- Relationship with other environment variables
- The following figure shows the relationships among PDTIMEDOUTRETRY, PDNBLOCKWAITTIME, and PDCONNECTWAITTIME.
Figure 6-3 Relationships among PDTIMEDOUTRETRY, PDNBLOCKWAITTIME, and PDCONNECTWAITTIME
![[Figure]](FIGURE/ZU060610.GIF)
(57) PDCLTPATH=trace-file-storage-directory
~<path name> ((path name of current directory))
This environment variable specifies the storage directory for the SQL trace file and client error log file 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 11.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 (in bytes) of the client error log file to which the UAP error logs are to be output.
If 0 is specified, the maximum file size is assumed, and a client 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 client error logs, see 11.1.2 Client error log facility.
- Relationship with other environment variables
- Client error logs are output to the directory specified in 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 client 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 API that is X/Open-compliant, two client 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 11.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.
(62) PDPRMTRCSIZE=maximum-data-length-of-parameter-information-output-to-SQL-trace
~ <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 11.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
- If access path information or SQL runtime interim results are to be collected, the server load might increase because SQL objects are re-created even if they already exist in the buffer.
- Information for a UAP is not output in the following cases:
The program uses an API that is X/Open-compliant under OLTP.
The UAP terminates without executing DISCONNECT.
- If the size of the access path information and SQL runtime interim results exceeds 1 gigabyte, the information is not output.
- 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.
- With a HiRDB/Parallel Server, privilege check processing by the connected dictionary server is not included in the UAP unit information.
- If you specify output of access path information or SQL runtime interim results and also specify the inter-process memory communication facility (PDIPC=MEMORY in the client environment definitions), the PDIPC specification is ignored and PDIPC=DEFAULT is assumed.
(65) PDREPPATH=storage-directory-for-UAP-statistical-report-files
~ <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.
(66) PDTRCPATH=storage-directory-for-dynamic-SQL-trace-files
~ <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 11.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.
(70) PDRCTRACE=reconnect-trace-file-size
~ <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 11.1.7 Reconnect trace facility.
(71) PDWRTLNPATH=storage-directory-for-files-to-which-WRITE-LINE-statement-value-expression-values-are-output
~ <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 9 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
(72) PDWRTLNFILSZ=maximum-size-of-output-files-for-WRITE-LINE-statement-value-expression-values
~ <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
- If both PDWRTLNFILSZ and PDIPC=MEMORY are specified, the PDIPC specification becomes invalid.
- The files are output to the directory specified in PDWRTLNPATH.
- 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.
(73) PDWRTLNCOMSZ=total-size-of-WRITE-LINE-statement-value-expression-values
~ <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 value specified in this environment variable, 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 11.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.
(75) PDUAPEXERLOGPRMSZ=maximum-data-length-of-parameter-information
~ <unsigned integer> ((0-32008)) (bytes)
This environment variable specifies the maximum data length for the parameter information to be output to the client error log file and SQL error report file 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
- 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.
- 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.
(76) PDARYERRPOS={YES|NO}
This environment variable specifies whether the value indicating an erroneous array element is to be set in the SQL Communications Area when update processing using arrays results in an error.
- YES
- Set the value indicating an erroneous array element in the SQL Communications Area.
- NO
- Do not set the value indicating an erroneous array element in the SQL Communications Area.
The SQL Communications Area used to set the value indicating an erroneous array element is as follows:
- In C language, the location is set in SQLERRD2.
- In COBOL, the location is set in SQLERRD(3).
An erroneous array element is indicated by setting a value from -1 to -n, where -1 indicates the first element of the array and -n represents the last element. For details about the SQL Communications Area, see Appendix A. SQL Communications Area.
(77) PDDNDPTRACE=method-trace-file-size
~ <unsigned integer> ((0, 65536 to 2147483647)) (bytes)
This environment variable specifies the file size for method traces that are output by the HiRDB.NET data provider supporting ADO.NET 2.0.
When this environment variable is specified, the system outputs the method traces to the directory specified in PDCLTPATH.
If you specify 0, as much method trace information will be output as there is space on the disk. If a value in the range from 65536 to 2147483647 is specified, the specified value is treated as the maximum size. When this maximum size is reached, the trace output destination is changed. If this environment variable is omitted, no method trace information is output. For details about method traces, see 16.10 Troubleshooting function of HiRDB.NET Data Provider.
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 9 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
- Specify PDTAAPINFPATH to acquire access path information for HiRDB SQL Tuning Advisor.
- 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.
- 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.
- If the total of %PDDIR%-path-length + authorization-identifier-length + UAP-name-length is larger than 220 characters when the Windows-edition 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 11.1.4 UAP statistical report facility.
- 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.
(79) PDTAAPINFPATH=access-path-information-file-output-directory-name
~<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.
- 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)
(81) PDTAAPINFSIZE=access-path-information-file-size
~<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 9 System Definition. For details about the pdstbegin command, see the manual HiRDB Version 9 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.
(83) PDLOCKLIMIT=maximum-locked-resource-request-count-per-user
~ <unsigned integer> ((0-200000000)) <<0>> (count)
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.
If a locked resources shortage occurs, an SQL error results.
- Estimation method
- There are two methods:
- One method determines the maximum number of locked resources from the number of locked resources used by one UAP.
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 9 System Definition. For details about lock processing, see 3.4 Locking.
- The other method determines the maximum number of locked resources from the number of locked resources that can be used by one UAP among all the locked resources available to the HiRDB server that connects the UAP.
For details about how to estimate the number of locked resources, see the manual HiRDB Version 9 System Definition.
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 |
-- |
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 value specified in PDISLLVL in the 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.
- 1 is always assumed for a cursor declaration used with update processing, 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
- 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.
- 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 9 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
- Specify at least one identifier.
- When specifying two or more identifiers, separate them with commas.
- For details about the information (optimization methods) that can be specified with identifiers, see Specification values for the SQL optimization option.
- 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.
- The identifiers can be specified with uppercase and lowercase characters.
- 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.
- 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
- 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.
- Specify at least one unsigned integer.
- When specifying two or more unsigned integers, separate them with commas.
- For details about the information (optimization methods) that can be specified with unsigned integers, see Specification values for the SQL optimization option.
- 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.
- 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.
- 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.)
- 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.
- 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_optimize_level operand of the system definition is assumed. For details about the pd_optimize_level operand, see the manual HiRDB Version 9 System Definition.
- 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.
- 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.
- We recommend that you specify all the following identifiers when you omit the pd_optimize_level operand in the system definition:
"PRIOR_NEST_JOIN","PRIOR_OR_INDEXES",
"DETER_AND_INDEXES","RAPID_GROUPING",
"DETER_WORK_TABLE_FOR_UPDATE",
"APPLY_ENHANCED_KEY_COND",
"MOVE_UP_DERIVED_COND"
- 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 9 SQL Reference.
- Specification values for the SQL optimization option
- The following table shows the values that can be specified for the SQL optimization option.
Table 6-32 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 candidates#2 |
"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 server#2 |
"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 servers#1, #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 |
18 |
Facility for moving search conditions into derived table |
"MOVE_UP_DERIVED_COND" |
2097152 |
#1: 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.
#2: 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-32.
- HiRDB/Single Server
Specify item numbers 4, 6, 8, 9,14,16 and 18. 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",
"MOVE_UP_DERIVED_COND"
- Explanation of optimization methods
- 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.
- 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.
- 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 applicable only to a HiRDB/Parallel Server.
- 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.
- 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 applicable only to a HiRDB/Parallel Server.
- 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.
- 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.
- 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
- 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.
- 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 application is permitted 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).
- 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).
- 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.
- 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.
- 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.
The following table shows the relationships between SQL statements that create work tables and the option for suppressing creation of update-SQL work tables.
Table 6-34 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 UPDATE#1 |
-- |
C |
-- |
FOR UPDATE OF |
-- |
C#2 |
-- |
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 |
C#3 |
C#3 |
-- |
DELETE statement |
-- |
C |
-- |
C: Work tables are created.
--: Work tables are not created.
#1: This includes FOR UPDATE clauses that are assumed when data is updated using the cursor of this SELECT statement.
#2: 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.
#3: 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.
#4: Work tables for the ORDER BY clause are sometimes not created when indexes are used.
#5: 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.
- 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.
- 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 9 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.
- 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 9 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 9 Installation and Design Guide.
- Facility for moving search conditions into derived table
Normally, a work table for a derived table contains rows that do not satisfy the search condition for the derived table columns. If this optimization method is applied, the HiRDB system first eliminates the rows that do not satisfy the search conditions for the derived table columns, and then creates a work table for the derived table. This enables you to reduce the space used for work tables as well as the number of input/output operations on work tables. By using such an access path, you might be able to use indexes that are more efficient in narrowing down the rows to be retrieved.
When you install HiRDB version 08-02 or later for the first time, we recommend that you use this function.
- Notes about the facility for moving search conditions into derived table:
- If you upgrade your HiRDB system from a version earlier than 08-02 and use this function, the access path might change. If the access path changes, the selected index might not be able to efficiently narrow down the rows to be retrieved, thereby adversely affecting performance. In such a case, check the access path before and after you use this function to make sure that the optimum index is being used. If the optimum index is not being used, specify the index used in the SQL optimization specification for a used index, or stop using this function.
- Notes
- 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
- 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)
- If optimizing information is not retrieved with the optimizing information collection utility (pdgetcst), specifying the value for making multiple SQL objects has no effect.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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
- Specify at least one identifier.
- When specifying two or more identifiers, separate them with commas.
- For details about the information (optimization methods) that can be specified with identifiers, see Specification values for the SQL extension optimizing option.
- If no optimization is to be applied, specify NONE as the identifier.
- The identifiers can be specified with uppercase and lowercase characters.
- 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.
- 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
- Specify at least one unsigned integer.
- When specifying two or more unsigned integers, separate them with commas.
- For details about the information (optimization methods) that can be specified with unsigned integers, see Specification values for the SQL extension optimizing option.
- If no optimization is to be applied, specify 0 as the identifier.
- 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.
- The character string specified for "unsigned-integer"[,"unsigned-integer"]... can have up to 575 bytes.
- Relationship to the system definition
- We recommend that you specify both the following identifiers when you omit the pd_additional_optimize_level operand in the system definition:
- COST_BASE_2 and APPLY_JOIN_COND_FOR_VALUE_EXP
- 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 9 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 9 SQL Reference.
- Specification values for the SQL extension optimizing option
- The following table shows the specification values for the SQL extension optimizing option.
Table 6-35 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 |
Facility for applying join conditions including value expression |
"APPLY_JOIN_COND_FOR_VALUE_EXP" |
32 |
- Note
- Item numbers 2 to 3 become effective when the value for application of optimizing mode 2 based on cost is specified.
- 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. Facility for applying join conditions including value expression
- If this function is specified, a join condition is created for conditions including value expressions.
- Example:
- This example creates a join condition for the following expression:
- substr(t1.c1,1,100)=t2.c1
- If there are only join conditions including value expressions, this function changes the access path from direct product to nest-loop join, hash join, or merge join, so that high-speed SQL execution can be expected.
- Notes
- When you install HiRDB version 08-04 or later for the first time, we recommend that you use this function.
- If you upgrade your HiRDB system from a version earlier than 08-04 and use this function, the number of join conditions increases and the join sequence and join method might change. If the join sequence and join method change, the selected join sequence might not provide effective narrowing, or the selected join method might exhibit poor performance, thereby having adverse effects on overall performance. Furthermore, if the selected join sequence cannot narrow down the rows efficiently, the intermediate join results cannot be placed in the work table and an error might result. Therefore, check the access path before and after you apply this function to make sure that this function will not affect performance negatively or cause errors. If performance is affected adversely or errors occur due to application of this function, stop using the function or specify the join sequence by using INNER or LEFT OUTER in the join tables and specify the optimum join method by using an SQL optimization specification.
(90) PDHASHTBLSIZE=hash-table-size-when-hash-join-subquery-hash-execution-is-applied
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 9 SQL Reference manual.
For an internal Type 4 JDBC driver that is run with a Java stored procedure, use the same PDDFLNVAL specification as for the program that calls the Java stored procedure.
(92) PDAGGR=group-count-resulting-from-grouping
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 9 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.
(96) PDDDLDEAPRPEXE={YES|NO}
If a transaction that is already running (called the preceding transaction) is already making normal use of a resource (such as a table, stored routine, or abstract data type) that is to be manipulated by a transaction that executes a definition SQL statement (called the definition transaction), the definition transaction waits for the preceding transaction to be completed. If you specify YES in this environment variable, the HiRDB system ignores the preceding transaction's preprocessing results and executes the definition transaction.
- Reference note
- You can specify the timing of executing a definition transaction by specifying the following environment variables:
- PDDDLDEAPRPEXE
Ignores the preceding transaction's preprocessing results and executes the definition transaction.
- PDDDLDEAPRP
Executes the definition transaction after the preceding transaction's holdable cursor has closed and the transaction including that holdable cursor has been completed. When the definition transaction is executed, the holdable cursor's preprocessing results are ignored.
- PDLCKWAITTIME
Enables you to specify the lock-release wait time. If you use this environment variable together with PDDDLDEAPRP and PDLCKWAITTIME, you can prevent the definition transaction from resulting in a timeout error before the preceding transaction is completed.
- The following table shows whether the preceding transaction's preprocessing results are ignored when this environment variable is specified together with PDDDLDEAPRPEXE and PDDDLDEAPRP.
Client environment definition |
Preprocessing results used by holdable cursor |
Preprocessing results that are not used by holdable cursor |
PDDDLDEAPRPEXE |
PDDDLDEAPRP |
YES |
YES |
Y |
Y |
NO |
Y |
Y |
NO |
YES |
AP |
N |
NO |
N |
N |
- Legend:
- Y: Can be ignored.
- AP: Can be ignored after the preceding transaction is completed.
- N: Cannot be ignored.
- YES
- Ignore the preceding transaction's preprocessing results and execute the definition transaction. The following shows an example when YES is specified:
![[Figure]](FIGURE/ZU060698.GIF)
- Explanation:
- The definition transaction does not wait for completion of the preceding transaction.
- When the definition transaction is executed, the preceding transaction's preprocessing results are ignored and the preceding transaction can no longer be executed.
- Notes
- You can ignore the preprocessing results by specifying YES in this environment variable only when the definition transaction performs the following operations (except when the preceding transaction is executing preprocessing):
- Executes a definition SQL statement
- Closes the data dictionary LOB RDAREA for storing stored routines' SQL objects
- Any of the following operations performed by the database structure modification utility (pdmod):
Deleting a data dictionary RDAREA for storing database state analyzed tables (SQL_DB_STATE_ANALYZED) and database management tables (SQL_DB_MANAGEMENT) by using the remove rdarea statement (for deleting RDAREAs)
Changing the reference privilege or mixed character string data usage setting by using the alter system statement (for changing the data dictionary table attribute definitions)
Renaming an RDAREA by using the alter rdarea statement (for changing RDAREA attributes)
- Note that if the preceding transaction is executing a definition SQL statement (including the rebalancing utility), a definition transaction that uses the table, procedure, or function whose definition is being changed will result in an error.
- NO
- Do not execute a definition transaction prior to completion of the preceding transaction. The following shows an example when NO is specified:
![[Figure]](FIGURE/ZU060699.GIF)
- Explanation:
- The definition transaction waits for completion of the preceding transaction regardless of the SQL statement being executed by the preceding transaction.
- The definition transaction is executed after the preceding transaction has been completed.
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.
![[Figure]](FIGURE/ZU060605.GIF)
- 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.
![[Figure]](FIGURE/ZU060606.GIF)
- Explanation
- The definition SQL executed by UAP2 can be executed after DISCONNECT processing of UAP1.
- Relationship with other environment variables
- You can specify a lock-release wait time in PDLCKWAITTIME. By combining PDDDLDEAPRP and PDLCKWAITTIME, you can prevent a definition SQL statement from resulting in a timeout error before the transaction is completed.
(98) PDLCKWAITTIME=lock-release-wait-time
~<unsigned integer>((0 to 65535)) <<pd_lck_wait_timeout value in system definition>> (seconds)
This environment variable specifies the maximum amount of time the HiRDB client is to monitor a lock request for the lock to be released, beginning when the lock request is placed on wait status. If the lock is not released within the specified time, the SQL statement returns an error. If 0 is specified, the HiRDB client waits until the lock is released without monitoring the wait status.
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.
The following figure provides an overview of the processing for each setting.
Figure 6-4 Overview of processing for each setting of PDCURSORLVL
![[Figure]](FIGURE/ZU060608.GIF)
- 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 9 System Definition.
- Note
- For a Windows edition 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.
(102) PDCALCMDWAITTIME=maximum-wait-time-for-CAL-COMMAND-statement
~ <unsigned integer>>((0 to 65535)) <<PDCWAITTIME specification value>> (seconds)
This environment variable specifies the maximum amount of time the HiRDB client is to wait for termination of a command executed by the CALL COMMAND statement or a utility, beginning when its execution starts. If no response is received from the server within the specified time, an error is returned to the UAP and the server's process is canceled. If you specify 0, the HiRDB client will wait until a response is received from the HiRDB server.
(103) PDSTANDARDSQLSTATE={YES|NO}
This environment variable specifies whether the details of the SQLSTATE value are to be displayed.
- YES
- Display the details of the SQLSTATE value.
- NO
- Do not display the details of the SQLSTATE value.
- Relationship to the system definition
- If this environment variable is omitted, the pd_standard_sqlstate operand value in the system common definition is assumed. However, if an error occurs before connection establishment with the server is completed, HiRDB assumes that NO is specified in this environment variable.
For details about SQLSTATE, see the manual HiRDB Version 9 Messages.
If a Java stored procedure uses a type 4 JDBC driver, whether the details of the SQLSTATE value returned to the Java stored procedure are to be set depends on the combination of the client's (program that called the Java stored procedure) PDSTANDARDSQLSTATE specification, pd_standard_sqlstate in the system common definition, and PDSTANDARDSQLSTATE in the Java stored procedure. The following table shows the combinations of these specifications:
Value of client's PDSTANDARDSQLSTATE environment variable |
Value of pd_standard_sqlstate in the system common definition |
Value of PDSTANDARDSQLSTATE in Java stored procedure |
Whether details of SQLSTATE are to be set |
YES |
y, n, or omitted |
YES |
Details are set |
Omitted |
y |
YES |
NO |
y, n, or omitted |
NO or omitted |
Details are not set |
Omitted |
n or omitted |
NO or omitted |
The value of SQLSTATE returned from the ODBC driver follows the ODBC standards regardless of the pd_standard_sqlstate specification in the system common definition.
(104) PDBLKF=block-transfer-row-count
~ <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 9 Installation and Design Guide. The calculation expression for memory required in the client is shown below.
Memory calculation expression (kilobytes)
=
(600 + 19 x retrieval-column-count + (7 x retrieval-column-count +
defined-column-lengths#) x PDBLKF-value)
4096
x 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
(107) PDBLKFUPD={YES|NO}
This environment variable specifies whether the block transfer facility is to be suppressed when an extended cursor is used to retrieve a query with FOR UPDATE specified. When you specify this environment variable, you can perform retrieval using an extended cursor for a query with FOR UPDATE specified even when the block transfer facility is used.
- YES
- Suppress the block transfer facility.
- NO
- Do not suppress the block transfer facility.
- Application standard
- In general, you must either specify YES in this environment variable or omit this operand.
- If you specify NO, the results of update processing using an extended cursor might differ from the results obtained when YES is specified. The following shows an example.
- Example:
- This example searches a table (T1) containing 50 rows of data by using an extended cursor while block transfer is enabled (PDBLKF=100), and then uses the extended cursor to update the rows.
PREPARE :SQL1 FROM 'SELECT * FROM T1 FOR UPDATE' 1
ALLOCATE GLOBAL :CR1 CURSOR FOR :SQL1 2
OPEN :CR1 3
FETCH :CR1 INTO :C1; 4
UPDATE SET C1=999 WHERE CURRENT OF :CR1 5
- The row to be updated in line 5 varies according to the value specified in this environment variable as described below:
- When YES is specified
The row at the cursor location that was fetched in line 4 is updated.
- When NO is specified
Because the cursor position in line 4 has moved by the number of blocks, the row at the resulting cursor position is updated. If there is no row at the resulting cursor position, an error results.
- Notes
- If you enable this function, note the following:
- For a UAP written in C or C++ language, you must link a client library version 07-03 or later.
- For a UAP written in COBOL, you must use a preprocessor version 07-03 or later to create a post-source program.
(108) PDBLKFERRBREAK={YES|NO}
This environment variable specifies when an error is to be returned to the UAP after implicit rollback occurs while multiple rows are being acquired by the block transfer facility.
If an error occurs while multiple rows are being acquired from the HiRDB server, the HiRDB client returns an error to the UAP at the actual row where the error occurred. Depending on the nature of the error, implicit rollback might occur and the transaction might no longer be valid. In such a case, if another SQL statement is issued while the retrieval results are being acquired, the obtained results might be invalid. By specifying this environment variable, you can return an error when the UAP acquires the first row of the rows in the retrieval results held by the HiRDB client.
- YES
- Return an error when the first row of the rows in the retrieval results held by the HiRDB client is acquired.
- NO
- Return an error when the actual row resulting in the error is acquired.
The following figure illustrates the timing of acquiring an implicit rollback error:
![[Figure]](FIGURE/ZU060609.GIF)
This environment variable is limited to the AIX 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 9 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 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.
(110) PDBINDRETRYCOUNT=bind-system-call-retries-count
~ <integer> ((-1 to 1000))<<10>>
This environment variable specifies the number of retries that can be attempted when EADDRINUSE is returned for a bind system call in a UNIX domain.
If the client and server are on the same host, the HiRDB client prepares for UNIX domain communication by issuing a bind system call to assign a named file to a socket. In Solaris 9 or later, if SQL DISCONNECT and SQL CONNECT are executed within a brief interval, the bind system call might return EADDRINUSE. You can avoid this by re-issuing the system call.
If you specify -1, the HiRDB client retries until EADDRINUSE is no longer returned. If you specify 0, the HiRDB client returns an error to the UAP without retrying.
- Specification value guidelines
- If the KFPZ02444-E message has been output to the client error log file due to the network failure indicated in the KFPA11723-E message (reason=NETWORK) and the message displays func=bind and errno=EADDRINUSE with count and interval, increase the value of this environment variable as well as the value of PDBINDRETRYINTERVAL.
(111) PDBINDRETRYINTERVAL=bind-system-call-retry-interval
~ <unsigned integer> ((0 to 1000)) <<0>> (milliseconds)
This environment variable specifies the retry interval when EADDRINUSE is returned for a bind system call in a UNIX domain. If you specify 0, the HiRDB client retries immediately.
(112) PDCLTSIGPIPE={CATCH|IGNORE}
This environment variable specifies whether the HiRDB client's signal handler is to be set for the SIGPIPE signal.
This environment variable is applicable only to the UNIX edition multithread-capable client libraries. For details about the multithread-capable libraries, see 6.4.1 Directories and files for UNIX clients.
- CATCH
- Set the HiRDB client's signal handler in the SIGPIPE signal.
- IGNORE
- Set SIG_IGN (ignore) in the SIGPIPE signal.
The HiRDB client sets a signal handler in the SIGPIPE signal. If you execute a UAP using the multi-connection facility in a multi-thread environment, the signal handler set by the HiRDB client might be used as is. If a UAP execution process unloads the HiRDB client libraries in this status, the process will terminate abnormally when SIGPIPE occurs. If you use this environment variable to set SIG_IGN (ignore) in the SIGPIPE signal, you can avoid setting the HiRDB client's signal handler while a UAP execution process is running.
- Notes
- In a multi-thread environment, if the HiRDB client libraries might be unloaded by a process of the UAP (such as Cosminexus or TP1/EE) using COBOL, specify SIG_IGN (ignore) for the SIGPIPE signal. If you do not specify SIG_IGN (ignore), you must make sure that the HiRDB client libraries will not be unloaded.
(113) PDDBACCS=generation-number-of-RDAREA-to-be-accessed
~ <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. If you omit this environment variable, the value of PDDBACCS in the UAP environment definition is assumed.
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
- 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.
- 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.
- 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).
- Space conversion of a national character string is executed in two-byte units from the beginning of the string.
- 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 H.1 Hash function for table partitioning.
- 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 H.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 edition. 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
- Unicode (UTF-8) codes can be used in input/output data handled by embedded variables and data handled by the ? parameter.
- 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.
- 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.
- 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 9 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
- 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.
- PDBESCONHTI becomes valid when YES is specified in PDBESCONHOLD.
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
- 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.
- 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, if 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.5 driver is to be operated in a mode compatible with Microsoft Access, rather than on the basis of the ODBC 3.5 specification.
- YES
- The ODBC 3.5 driver is to operate in a mode compatible with Microsoft Access.
- NO
- The ODBC 3.5 driver is to operate as specified.
- Application standard
- Specify this environment variable when you use 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. If such events occur with other Microsoft products or interfaces, you might be able to prevent them by specifying this environment variable.
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 9 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.
(131) PDPLGPFSZ=initial-size-of-delayed-batch-creation-index-information-file
~ <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.
(132) PDPLGPFSZEXP=extension-value-of-delayed-batch-creation-index-information-file
~ <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.
(133) PDJDBFILEDIR=exception-trace-log-file-storage-directory
~<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 18.15 Exception trace log. For other details, see system property HiRDB_for_Java_FileDIR in 18.15.1(2)(b) Setting system properties.
(134) PDJDBFILEOUTNUM=number-of-outputs-to-exception-trace-log-file
~<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 18.15 Exception trace log. For other details, see system property HiRDB_for_Java_FileOutNUM in 18.15.1(2)(b) Setting system properties.
(135) PDJDBONMEMNUM=number-of-acquired-information-items-in-exception-trace-log-memory
~<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 18.15 Exception trace log. For other details, see system property HiRDB_for_Java_OnMemNUM in 18.15.1(2)(b) Setting system properties.
(136) PDJDBTRACELEVEL=trace-acquisition-level-of-exception-trace-log
~<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 18.15 Exception trace log. For other details, see system property HiRDB_for_Java_TraceLevel in 18.15.1(2)(b) Setting system properties.
(137) PDXDSHOST=XDS-host-name[,secondary-XDS-host-name]
~ <identifier> ((maximum 511 bytes)) <<value of PDFESHOST>>
This client environment definition is applicable only to XDS clients.
This environment variable specifies the host name of the XDS to be connected. You can also specify a domain name or IP address for the host name. Express an IP address as decimal numbers using a period as the delimiter between bytes.
The value to be specified depends on whether the IP address is to be inherited after the systems have been switched when the dual memory database function is used.
- For system switchover that inherits IP addresses
- Specify the primary host name in PDXDSHOST in the client environment definition.
- For system switchover that does not inherit IP addresses
- Specify both primary and secondary host names in PDXDSHOST in the client environment definition. If you specify only the primary host name, you must change the primary host name in this operand after system switchover has occurred.
If you omit this environment variable, the HiRDB client uses the host name specified in PDFESHOST. Note that the port number of the front-end server specified in PDFESHOST is not used. The name of the host to be connected depends on the specification of PDXDSHOST and PDFESHOST. The following table shows how the connection-target host name is determined.
Table 6-36 How to determine the connection-target host name
PDXDSHOST |
PDFESHOST |
Connection-target host name |
Y |
Y |
PDXDSHOST value |
N |
N |
Y |
PDFESHOST value |
N |
No host name specified |
- Legend:
- Y: Specified
- N: Omitted
(138) PDXDSPORT=XDS-port-number
~ <unsigned integer> ((5001 to 65535)) <<value of PDSERVICEPORT>>
This client environment definition is applicable only to XDS clients.
This environment variable specifies the port number of the XDS to be connected. This must be the port number specified in the -p option in the pdqmyrecvdef operand in the XDS server definition. For details about the pdqmyrecvdef operand, see the HiRDB Version 9 Memory Database Installation and Operation Guide.
If you omit this environment variable, the HiRDB client uses the first high-speed connection port number specified in PDSERVICEPORT.
The connection-target port number depends on the port number specification in PDXDSPORT, PDSERVICEPORT, and PDFESHOST. The following table shows how the connection-target port number is determined.
Table 6-37 How to determine the connection-target port number
PDXDSPORT |
PDSERVICEPORT |
PDFESHOST |
Connection-target port number |
Y |
Y |
Y |
PDXDSPORT value |
N |
N |
Y |
N |
N |
Y |
Y |
PDSERVICEPORT value |
N |
N |
Y |
No port number specified |
N |
- Legend:
- Y: Specified
- N: Omitted
(139) PDXVWOPT=output-of-access-path
~ <unsigned integer> ((0 to 1)) <<0>>
This client environment definition is applicable only to XDS clients.
This environment variable specifies whether the access path of the SQL statement used to access a table to be expanded to the memory database is to be output.
- 0
- Do not output the access path.
- 1
- Output the access path of a preprocessed SQL statement if the SQL statement was preprocessed in XDS. Note that if the SQL statement was found in the SQL pool, the access path will not be output.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.