10.2.4 Operands related to FETCH
- 11) pd_hb_ary_fec_num = number of rows-to-be-fetched-at-once-with-FETCH-facility-that-uses-array
<unsigned integer>((1-30000))- Specifies the number of rows to be fetched at one time with FETCH that uses an array.
- If the number of search result rows from a foreign table can be predicted to some degree, specify this operand. The communication overhead between HiRDB and the foreign server can be expected to decrease. For the SQL execution time and the number of FETCH operations to a foreign table based on the array FETCH count to the foreign server, check the statistical information related to foreign server usage. For details on the statistical information related to foreign server usage, see the Statistics analysis utility in the manual HiRDB Version 8 Command Reference.
- If this operand is omitted, HiRDB determines automatically a value that is optimized for the HiRDB processing.
- Specification guidelines
- If the specification value is too small, the number of communications between HiRDB and the foreign server increases, resulting in a greater overhead.
- If the specification value is too large, too large a memory area is used, resulting in a memory resource shortage.
- If the number of search result rows from a foreign table varies depending on the job, Hitachi recommends that this operand be omitted. Whether omitting this operand is advantageous depends on whether one or more rows of data collected from the foreign table fit in the communication buffer size specified by the pd_sql_send_buff_size operand. If the data fit, HiRDB efficiently processes the data collected from the foreign table. If the data does not fit, HiRDB suppresses the memory used for accessing the foreign table.
- 12) PDCURSORLVL 0 | 1 | 2
- Specifies the timing for issuing a cursor open/close request to the HiRDB server when the cursor is used for search processing.
- The function of and specification method for this operand are the same as for the PDCURSORLVL operand in the client environment definition. For details about the PDCURSORLVL operand, see the HiRDB Version 8 UAP Development Guide. For an external server with a different HiRDB version, see the applicable manual.
- 0:
- When the HiRDB client receives a cursor open/close request from an application, it issues the execution request as is to the HiRDB server.
- 1:
- If there is no search data, the HiRDB server returns SQLCODE=100 and at the same time closes the cursor without a request from the HiRDB client. When the HiRDB client receives a cursor close request from an application and the HiRDB client has already detected SQLCODE=100, it does not issue a cursor close request to the HiRDB server; the HiRDB client issues a cursor close request only if it has not detected SQLCODE=100.
- For a cursor open request, the processing is the same as when 0 is specified.
- 2:
- When the HiRDB client receives a cursor open request from an application, it does not issue the execution request to the HiRDB server; instead, the HiRDB client issues a cursor open request when the first fetch request is issued.
- For a cursor close request, the processing is the same as when 1 is specified.