2.80 pdobils (Display SQL object buffer statistics)

Organization of this section
(1) Function
(2) Executor
(3) Format
(4) Operands
(5) Rules
(6) Notes
(7) Output format
(8) How to interpret statistical information

(1) Function

The pdobils command displays statistical information about the SQL objects stored in the SQL object buffer. This information enables you to identify SQL objects that take a long time to process or that are input and output frequently; you can also obtain the object reuse status of SQL objects.

(2) Executor

HiRDB administrator

(3) Format

 pdobils [-s server-name] [-R|-r][-C [delimiter] [-H]] [-e]
     [-U] [-NR] [-N SQL-object-number[,SQL-object-number]...]

(4) Operands

(a) -s server-name ~<identifier> ((1-8))

Specifies the name of a server (single server or front-end server) whose information is to be displayed or whose statistical information counters are to be initialized.

(b) -R|-r

Specifies that the statistical information counters are to be initialized. For example, this option is specified when overflow occurs in statistical information counters.#

-R:
Specifies that the counters are to be initialized after the SQL object buffer statistics have been output. We recommend that you specify this option if you will be obtaining statistical information repeatedly at a specific interval.
-r:
Specifies that the counters for the SQL object buffer statistics are to be initialized only. For details about the items for which counters are initialized, see Output format.
#: When an SQL object is reused many times, its counter in the SQL object buffer may overflow. When a counter overflows, * is displayed for the corresponding item in the statistical information.
(c) -C [delimiter] ~<character string>((1 to 10))

Specifies that the statistical information is to be output in DAT format.

You can specify the delimiter to be output between elements. If no delimiter is specified, the tab character is used as the delimiter. The hyphen (-) can be specified as the first character of the delimiter.

(d) -H

When the -C option is specified, this option specifies that the first line is to be a title line.

(e) -e

Specifies that only statistical information whose execution count (EXECUTE COUNT item in the output format) is one or greater is to be output.

(f) -U

Specifies that SQL object information for the current SQL statement and information about the UAP that is executing that SQL statement are to be output when the pdobils command is executed.

The following figure shows the relationship between the timing of the pdobils command's execution and the statistical information that is output.

Figure 2-6 Whether statistical information is output depending on the relationship between execution of the pdobils command with the -U option specified and the executing SQL statement

[Figure]

Explanation:
Whether statistical information is output depends on the timing of the pdobils command's execution. The following table shows when statistical information is output depending on the execution timing:
pdobils command execution timingWhether statistical information is output for the executing SQL statementRemarks
1NStatistical information might be output if the command is executed after the SQL statement preprocessing has been completed but before the results are returned to the UAP.
2NNone
3YStatistical information might not be output if the preprocessing results become invalid# while the SQL statement is executing.
Legend:
Y: Statistical information for the executing SQL statement is output.
N: Statistical information for the executing SQL statement is not output.
#
The preprocessing results become invalid when the transaction is completed.

(g) -NR

Specifies that linefeed codes (0x0A) and carriage return codes (0x0D) in SQL statements are to be replaced with space characters (0x20).

(h) -N SQL SQL-object-number[,SQL-object-number]...

Specifies the SQL object numbers of the SQL objects for which maintenance information is to be output.#

Specify for the SQL object numbers the values that are displayed in item 6. SQL object number in (7) Output format.

Note that if an SQL object whose SQL object number is specified in the command does not exist in the buffer, no maintenance information will be output for that SQL object.

#
If you are using multiple front-end servers, specify in the -s option the server name of the front-end server that contains the target SQL object. If no server name is specified when multiple front-end servers are being used, the KFPN91001-I message is displayed.

(5) Rules

  1. The pdobils command can be executed only when HiRDB is running.
  2. The pdobils command can be executed at any server machine.

(6) Notes

  1. The execution results of the pdobils command can be confirmed from the return code after the command has executed. Return code 0 indicates normal termination, and return code 4 indicates abnormal termination.
  2. Because the pdobils command manipulates the SQL object buffer, the overhead associated with this manipulation may increase the HiRDB workload.
  3. When the pdobils command is terminated forcibly, SQL processing on the SQL object buffer and post-processing may be placed in wait status for up to 1 minute. For this reason, it is unwise to terminate the pdobils command forcibly.
  4. The timing of acquiring statistical information depends on the value of the pd_sqlobject_stat_timing operand in the system definition.
  5. The amount of time required for an SQL object to execute is recorded in the SQL object buffer when the transaction is completed or the SQL object is released. The figure below illustrates the SQL object execution time (duration) that is displayed when the pdobils command is executed.

    Figure 2-7 SQL object execution time (duration) displayed when the pdobils command is executed

    [Figure]

Explanation
The display of the amount of time (duration) it takes an SQL object to execute depends on the timing of execution of the pdobils command. The following table shows the validity of the time that is displayed for each point of execution:
Execution sequence of pdobils commandSQL1SQL2SQL3SQL4
1NNNN
2YYNN
3YYYY
Legend:
Y: The displayed execution duration time is valid for this SQL object.
N: The displayed execution duration time is not valid for this SQL object.
Note 1
When the cursor is used, the displayed execution duration time is the server's total processing time from opening to closing of the cursor.
Note 2
While the pdobils command is executing, the SQL object buffer is used by other UAPs. If there is an SQL object whose transaction is completed during execution of the pdobils command and the corresponding information is available, the information is applied to the output information.
Note 3
The execution time changes depending on the mode (recommended mode or compatibility mode) as shown below:
Operation modeExecution time
Recommended modeMicroseconds
v09-04 compatibility modeSeconds (a fraction of a second is discarded)

(7) Output format

The output format for the information that is output by the pdobils command depends on the combination of the specified options, as shown below:

Specification of -U and -N optionsSpecification of -C option
OmittedSpecified
The -U and -N options are both omitted.General output formatDAT format for general output
Only the -U option is specified.Format for output current execution informationDAT format for output current execution information
Only the -N option is specified.Maintenance information output format--
Legend:
--: Not a valid combination.

(a) General output format

pdobils VV-RR-ZZ [70]
<< SQL OBJECT CACHE INFORMATION >> DATE: 2004/01/01 TIME: 00:00:00 [1]
HOST NAME                   : node01 [2]
SERVER NAME                 : fes01 [3]
CACHE SIZE(KB)              : 2048 [4]
SQL OBJECT TOTAL SIZE(B)    : 1932556​ [5]

<< SQL OBJECT LIST >>
*SQL OBJECT NO               : 1 [6]
STATUS                      : ACTIVE [7]
TYPE                        : STATIC SQL [8]
SIZE                        : 7792 [9]
EXECUTE COUNT               : 10 [10]
EXECUTE TIME AVG(s)         : 1.234567 [11]
EXECUTE TIME MAX(s)         : 9.000000 [12]
# SERVICE NAME              :          [13]
# UAP NAME                  : Unknown [14]
# CONNECT NO                : 123 [15]
# SQL NO                    : 456 [16]
# RECORD DATE/TIME          : 2004/01/01 00:00:00 [17]
DB REFERENCE GET COUNT       : 55500 [28] AVG 5550 [29] MAX 5600 [30]
DB UPDATE GET COUNT          : 0 [31] AVG 0 [32] MAX 0 [33]
DB READ COUNT                : 800 [34] AVG 80 [35] MAX 85 [36]
DB WRITE COUNT               : 0 [37] AVG 0 [38] MAX 0 [39]
LOB REFERENCE GET COUNT      : 0 [40] AVG 0 [41] MAX 0 [42]
LOB UPDATE GET COUNT         : 0 [43] AVG 0 [44] MAX 0 [45]
LOB READ COUNT               : 0 [46] AVG 0 [47] MAX 0 [48]
LOB WRITE COUNT              : 0 [49] AVG 0 [50] MAX 0 [51]
LIST REFERENCE GET COUNT     : 0 [52] AVG 0 [53] MAX 0 [54]
LIST UPDATE GET COUNT        : 0 [55] AVG 0 [56] MAX 0 [57]
LIST READ COUNT              : 0 [58] AVG 0 [59] MAX 0 [60]
LIST WRITE COUNT             : 0 [61] AVG 0 [62] MAX 0 [63]
WKFILE READ COUNT            : 0 [64] AVG 0 [65] MAX 0 [66]
WKFILE WRITE COUNT           : 0 [67] AVG 0 [68] MAX 0 [69]
PREPROCESSOR USER            : user1 [18]
PREPROCESSOR SOURCE          : abc.ec [19]
SECTION NO                   : 123 [20]
ISOLATION LEVEL              : 2 [21]
OPTIMIZE LEVEL               : 10 [22]
ADDITIONAL OPTIMIZE LEVEL    : 0 [23]
DEFAULT SCHEMA               :   [24]
ROUTINE ID                   : 123 [25]
ACCESS TYPE SSCT# n          : user1.STOCK (T:0x0002007​b) INDEX SCAN (I:0x00030005​)[71]
SQL                          : SELECT PROD_NO,PROD_NAME,PLANNED,UNT_PRC,QTY,COST FROM STOCK [26]

<< SQL OBJECT STATUS COUNT >> [27]
STATUS     | TYPE      STATIC SQL        DYNAMIC SQL       ROUTINE           TOTAL
ACTIVE              :  0                 0                 0                 0
LRU                 :  0                 0                 0                 0
TEMPORARY           :  0                 0                 0                 0
COMPILE/TRANSFER    :  0                 0                 0                 0
PROCESS             :  -                 -                 -                 0
RELEASE             :  -                 -                 -                 0
TOTAL               :  0                 0                 0                 0

Note 1
The information that is displayed depends on the server's type and status.
Note 2
Items 6 through 26, 28 through 69, and 71 are repeated for each SQL object. The information that is displayed depends on the type and status. For details, see Notes.
Note 3
If the -s option is omitted, items 1 through 69 and 71 are displayed for each single server or front-end server.
Note 4
When the -R or -r option is specified, items 10 through 17 and 28 through 69 are initialized.
Explanation
  1. Statistical information display time
    Displays the time the statistical information was displayed.
  2. Host name
    Displays the name of the host for which statistical information was acquired.
  3. Server name
    Displays the name of the server for which statistical information was acquired.
  4. Size of SQL object buffer
    Displays the size of the SQL object buffer (in kilobytes).
  5. Total size of SQL objects
    Displays the total size of SQL objects (in bytes) stored in the SQL object buffer. If the SQL object buffer is used by another UAP during execution of the pdobils command, the sum of the sizes of each SQL object may not match the total size of all SQL objects.
  6. SQL object number
    Displays the management number of the SQL object.
  7. Status
    Displays one of the following, indicating the status of the SQL object:
    StatusDescription
    ACTIVEA UAP is using the SQL object.
    LRUAlthough the SQL object is stored in the SQL object buffer, no UAP is currently using it.
    TEMPORARYThe SQL object is not shared among processes. This is applicable when either of the following is true:
    • Creation of the SQL object is specified in the PDVWOPTMODE or PDUAPREPLVL client environment definition.
    • A dynamic SQL object is stored in the SQL object buffer.
    COMPILE/TRANSFERA static SQL object is being created, or the SQL object is being transferred from the front-end server to the back-end server or dictionary server.
    PROCESSThe SQL object is being managed in the process memory.
    RELEASEThe SQL object is being released or is no longer valid.
  8. Type
    Displays the type of the SQL object:
    STATIC SQL: Static SQL object
    DYNAMIC SQL: Dynamic SQL object
    ROUTINE: Routine SQL object
  9. Size
    Displays the size of the SQL object (in bytes).
  10. Execution count#4
    Displays the number of times the SQL object has executed.
  11. Average execution time
    Displays the average execution time for the SQL object (in seconds).
  12. Maximum execution time
    Displays the maximum execution time for the SQL object (in seconds).
  13. Service name
    When UAPs were executed in an OLTP environment, displays the name of the service that recorded the maximum execution time.
  14. UAP name
    Displays the name of the UAP that recorded the maximum execution time (not displayed if the UAP name cannot be identified).
  15. CONNECT sequence number#4
    Displays the CONNECT sequence number of the UAP that recorded the maximum execution time.
  16. SQL number#4
    Displays the number of the SQL that recorded the maximum execution time.
  17. Recorded time
    Displays the time at which the maximum execution time was recorded.
  18. Preprocessing user's authorization identifier
    Displays the authorization identifier of the user who executed preprocessing of the UAP source program. This item is displayed for static SQL objects.
  19. Preprocessed source program name
    Displays the name of the source program that was preprocessed (this item is displayed for static SQL objects).
  20. Section number
    Displays the section number (this item is displayed for static SQL objects).
  21. Data guarantee level
    Displays the data guarantee level (this item is displayed for static and dynamic SQL objects).
  22. SQL optimization option
    Displays the SQL optimization option (this item is displayed for static and dynamic SQL objects).
  23. SQL extension optimizing option
    Displays the SQL extension optimizing option (this item is displayed for static and dynamic SQL objects).
  24. Default schema name
    Displays the assumed authorization identifier when the authorization identifier is omitted from the SQL statement (this item is not displayed when an authorization identifier is specified in the SQL statement) (this item is displayed for dynamic SQL objects).
  25. Routine's object ID
    Displays the routine's object ID (this item is displayed for routine SQL objects).
  26. SQL statement
    Displays the SQL statement (this item is displayed for static and dynamic SQL objects).
  27. Type and number of SQL objects for each status
    Displays for each status the number of SQL objects of each type that are being managed in the SQL object buffer. When the -e option is specified, only SQL objects whose EXECUTE COUNT is one or greater are subject to totaling.
  28. Reference count for data, index, and directory pages (total)#2, #4
    Displays the total number of times that buffer acquisition requests were issued to the global buffer using the SQL object for referencing purposes.
  29. Reference count for data, index, and directory pages (average)#1, #2, #4
    Displays for the SQL object the average number of times that acquisition requests for referencing were issued to the global buffer.
  30. Reference count for data, index, and directory pages (maximum)#2, #4
    Displays for the SQL object the maximum number of times that acquisition requests for referencing were issued to the global buffer.
  31. Update count for data, index, and directory pages (total)#2, #4
    Displays the total number of times that buffer acquisition requests for updating were issued to the global buffer.
  32. Update count for data, index, and directory pages (average)#1, #2, #4
    Displays for the SQL object the average number of times that acquisition requests for updating were issued to the global buffer.
  33. Update count for data, index, and directory pages (maximum)#2, #4
    Displays the maximum number of times that buffer acquisition requests for updating were issued to the global buffer.
  34. Real READ count for data, index, and directory pages (total)#2, #3, #4
    Displays the total number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
  35. Real READ count for data, index, and directory pages (average)#1, #2, #3, #4
    Displays for the SQL object the average number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
  36. Real READ count for data, index, and directory pages (maximum)#2, #3, #4
    Displays for the SQL object the maximum number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
  37. Real WRITE count for data, index, and directory pages (total)#2, #3, #4
    Displays the total number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
  38. Real WRITE count for data, index, and directory pages (average)#1, #2, #3, #4
    Displays for the SQL object the average number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
  39. Real WRITE count for data, index, and directory pages (maximum)#2, #3, #4
    Displays for the SQL object the maximum number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
  40. LOB column data page reference count (total)#4
    Displays the total number of times that buffer acquisition requests were issued to reference LOB column data pages.
  41. LOB column data page reference count (average)#1, #4
    Displays for the SQL object the average number of times that acquisition requests were issued to reference LOB column data pages.
  42. LOB column data page reference count (maximum)#4
    Displays for the SQL object the maximum number of times that acquisition requests were issued to reference LOB column data pages.
  43. LOB column data page update count (total)#4
    Displays the total number of times that buffer acquisition requests were issued to update LOB column data pages.
  44. LOB column data page update count (average)#1, #4
    Displays for the SQL object the average number of times that buffer acquisition requests were issued to update LOB column data pages.
  45. LOB column data page update count (maximum)#4
    Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to update LOB column data pages.
  46. Real READ count for LOB column data pages (total)#4
    Displays the total number of real READ operations that were performed on LOB column data pages.
  47. Real READ count for LOB column data pages (average)#1, #4
    Displays for the SQL object the average number of real READ operations that were performed on LOB column data pages.
  48. Real READ count for LOB column data pages (maximum)#4
    Displays for the SQL object the maximum number of real READ operations that were performed on LOB column data pages.
  49. Real WRITE count for LOB column data pages (total)#4
    Displays the total number of real WRITE operations that were performed on LOB column data pages.
  50. Real WRITE count for LOB column data pages (average)#1, #4
    Displays for the SQL object the average number of real WRITE operations that were performed on LOB column data pages.
  51. Real WRITE count for LOB column data pages (maximum)#4
    Displays for the SQL object the maximum number of real WRITE operations that were performed on LOB column data pages.
  52. List page reference count (total)#4
    Displays the total number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
  53. List page reference count (average)#1, #4
    Displays for the SQL object the average number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
  54. List page reference count (maximum)#4
    Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
  55. List page update count (total)#4
    Displays the total number of times that buffer acquisition requests were issued to the global buffer to update list pages.
  56. List page update count (average)#1, #4
    Displays for the SQL object the average number of times that buffer acquisition requests were issued to the global buffer to update list pages.
  57. List page update count (maximum)#4
    Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to the global buffer to update list pages.
  58. Real READ count for list pages (total)#4
    Displays the total number of real READ operations that were performed on list pages.
  59. Real READ count for list pages (average)#1, #4
    Displays for the SQL object the average number of real READ operations that were performed on list pages.
  60. Real READ count for list pages (maximum)#4
    Displays for the SQL object the maximum number of real READ operations that were performed on list pages.
  61. Real WRITE count for list pages (total)#4
    Displays the total number of real WRITE operations that were performed on list pages.
  62. Real WRITE count for list pages (average)#1, #4
    Displays for the SQL object the average number of real WRITE operations that were performed on list pages.
  63. Real WRITE count for list pages (maximum)#4
    Displays for the SQL object the maximum number of real WRITE operations that were performed on list pages.
  64. READ count for work table files (total)#4
    Displays the total number of READ operations that were performed on work table files.
  65. READ count for work table files (average)#1, #4
    Displays for the SQL object the average number of READ operations that were performed on work table files.
  66. READ count for work table files (maximum)#4
    Displays for the SQL object the maximum number of READ operations that were performed on work table files.
  67. WRITE count for work table files (total)#4
    Displays the total number of WRITE operations that were performed on work table files.
  68. WRITE count for work table files (average)#1, #4
    Displays for the SQL object the average number of WRITE operations that were performed on work table files.
  69. WRITE count for work table files (maximum)#4
    Displays for the SQL object the maximum number of WRITE operations that were performed on work table files.
  70. HiRDB version
    When there is no ZZ, only VV-RR is displayed.
  71. Accessed table and retrieval method
    ACCESS TYPE SSCT# n : aa...a (T:bb...b) cc...c (I:dd...d)
    n: System-specific information.
    aa...a: Name of table that is accessed. *.* is displayed for the base table for a viewed table.
    bb...b: ID of the table that is accessed.
    cc...c: Method used to retrieve the table that is accessed. For details about retrieval methods, see 17.5.6 Types of retrieval methods.
    dd...d: Index ID. (I:dd...d) is not displayed when cc...c is TABLE SCAN.
#1
The average value is rounded off. If the total value overflows, **** is displayed as the average value.
#2
This does not include accesses to data dictionary tables that store definition information, such as for tables and indexes.
#3
Deferred write processing involves writing pages updated in the global buffer to disk when the number of updated pages reaches a specified value, rather than when the COMMIT statement is issued. Deferred write processing outputs updated pages to HiRDB files asynchronously with the database processes.
The prefetch facility enables database processes to read multiple pages of table data on disk volumes in batch mode. The asynchronous READ facility reads multiple pages of table data on disk volumes in batch mode asynchronously with the database processes.
#4
The counter may overflow. If an overflow has occurred, * is displayed as the lead digit of the corresponding counter. Counting continues when an overflow occurs.
Notes
Whether items 6 through 26 are displayed depends on the combination of type and status, as shown in the table below. The numbers correspond to the numbers in the output format.

Table 2-14 Whether each item is displayed depending on the combination of type and status (applicable to static SQL objects)

No.Type
STATIC SQLDYNAMIC SQLROUTINE
StatusStatusStatus
ALTCPRALTCPRALTCPR
6YYYYYYYYYYYYYYYYYY
7YYYYYYYYYYYYYYYYYY
8YY--------YY--------YY--------
9YY--------YY--------YY--------
10YY--------YY--------YY--------
11CC--------CC--------CC--------
12CC--------CC--------CC--------
13CC--------CC--------CC--------
14CC--------CC--------CC--------
15CC--------CC--------CC--------
16CC--------CC--------CC--------
17CC--------CC--------CC--------
18YY--------------------------------
19YY--------------------------------
20YY--------------------------------
21YY--------YY--------NN--------
22YY--------YY--------NN--------
23YY--------YY--------NN--------
24NN--------YY--------------------
25------------------------YY--------
26YY--------YY--------------------
28CC--------CC--------CC--------
29CC--------CC--------CC--------
30CC--------CC--------CC--------
31CC--------CC--------CC--------
32CC--------CC--------CC--------
33CC--------CC--------CC--------
34CC--------CC--------CC--------
35CC--------CC--------CC--------
36CC--------CC--------CC--------
37CC--------CC--------CC--------
38CC--------CC--------CC--------
39CC--------CC--------CC--------
40CC--------CC--------CC--------
41CC--------CC--------CC--------
42CC--------CC--------CC--------
43CC--------CC--------CC--------
44CC--------CC--------CC--------
45CC--------CC--------CC--------
46CC--------CC--------CC--------
47CC--------CC--------CC--------
48CC--------CC--------CC--------
49CC--------CC--------CC--------
50CC--------CC--------CC--------
51CC--------CC--------CC--------
52CC--------CC--------CC--------
53CC--------CC--------CC--------
54CC--------CC--------CC--------
55CC--------CC--------CC--------
56CC--------CC--------CC--------
57CC--------CC--------CC--------
58CC--------CC--------CC--------
59CC--------CC--------CC--------
60CC--------CC--------CC--------
61CC--------CC--------CC--------
62CC--------CC--------CC--------
63CC--------CC--------CC--------
64CC--------CC--------CC--------
65CC--------CC--------CC--------
66CC--------CC--------CC--------
67CC--------CC--------CC--------
68CC--------CC--------CC--------
69CC--------CC--------CC--------
71YY--------YY--------------------
Legend:
Y: Displayed
C: Displayed if the value of item 10 is 1 or greater. If the value of item 10 is 0, this information is not displayed.
N: Not displayed
--: Not applicable
Statuses:
A: ACTIVE
L: LRU
T: TEMPORARY
C: COMPILE/TRANSFER
P: PROCESS
R: RELEASE
(b) Format of output current execution information

pdobils VV-RR-ZZ [70]
<< SQL OBJECT CACHE INFORMATION >> DATE: 2004/01/01 TIME: 00:00:00 [1]
HOST NAME                   : node01 [2]
SERVER NAME                 : fes01 [3]
CACHE SIZE(KB)              : 2048 [4]
SQL OBJECT TOTAL SIZE(B)    : 1932556​ [5]

<< UAP INFORMATION >> [72]
PROCESS ID               : 12345678​ [73]
TRANSACTION ID(GID/BID)  : ccccccccdddddddd/cccccccceeeeeeee [74]
SERVICE NAME             : [75]
UAP NAME                 : pdsql [76]
CONNECT NO               : 234 [77]

<< SQL OBJECT LIST >>
*SQL OBJECT NO               : 1 [6]
STATUS                      : ACTIVE [7]
TYPE                        : STATIC SQL [8]
SIZE                        : 7792 [9]
EXECUTE COUNT               : 10 [10]
EXECUTE TIME AVG(s)         : 1.234567 [11]
EXECUTE TIME MAX(s)         : 9.000000 [12]
# SERVICE NAME              :          [13]
# UAP NAME                  : Unknown [14]
# CONNECT NO                : 123 [15]
# SQL NO                    : 456 [16]
# RECORD DATE/TIME          : 2004/01/01 00:00:00 [17]
DB REFERENCE GET COUNT       : 55500 [28] AVG 5550 [29] MAX 5600 [30]
DB UPDATE GET COUNT          : 0 [31] AVG 0 [32] MAX 0 [33]
DB READ COUNT                : 800 [34] AVG 80 [35] MAX 85 [36]
DB WRITE COUNT               : 0 [37] AVG 0 [38] MAX 0 [39]
LOB REFERENCE GET COUNT      : 0 [40] AVG 0 [41] MAX 0 [42]
LOB UPDATE GET COUNT         : 0 [43] AVG 0 [44] MAX 0 [45]
LOB READ COUNT               : 0 [46] AVG 0 [47] MAX 0 [48]
LOB WRITE COUNT              : 0 [49] AVG 0 [50] MAX 0 [51]
LIST REFERENCE GET COUNT     : 0 [52] AVG 0 [53] MAX 0 [54]
LIST UPDATE GET COUNT        : 0 [55] AVG 0 [56] MAX 0 [57]
LIST READ COUNT              : 0 [58] AVG 0 [59] MAX 0 [60]
LIST WRITE COUNT             : 0 [61] AVG 0 [62] MAX 0 [63]
WKFILE READ COUNT            : 0 [64] AVG 0 [65] MAX 0 [66]
WKFILE WRITE COUNT           : 0 [67] AVG 0 [68] MAX 0 [69]
PREPROCESSOR USER            : user1 [18]
PREPROCESSOR SOURCE          : abc.ec [19]
SECTION NO                   : 123 [20]
ISOLATION LEVEL              : 2 [21]
OPTIMIZE LEVEL               : 10 [22]
ADDITIONAL OPTIMIZE LEVEL    : 0 [23]
DEFAULT SCHEMA               :   [24]
ROUTINE ID                   : 123 [25]
ACCESS TYPE SSCT# n          : user1.STOCK (T:0x0002007​b) INDEX SCAN (I:0x00030005​)[71]
SQL                          : SELECT PROD_NO,PROD_NAME,PLANNED,UNT_PRC,QTY,COST FROM STOCK [26]

<< SQL OBJECT STATUS COUNT >> [27]
STATUS     | TYPE      STATIC SQL        DYNAMIC SQL       ROUTINE           TOTAL
ACTIVE              :  0                 0                 0                 0
LRU                 :  0                 0                 0                 0
TEMPORARY           :  0                 0                 0                 0
COMPILE/TRANSFER    :  0                 0                 0                 0
PROCESS             :  -                 -                 -                 0
RELEASE             :  -                 -                 -                 0
TOTAL               :  0                 0                 0                 0

Explanation:
Items 1 through 71 are the same as for the general output format.
72. Information about the UAP executing the SQL object
Displays information about the UAP that is executing the SQL object. If multiple UAPs are executing the SQL object, the information about the UAPs and the SQL object (items 6 through 26 and 71 through 77) is displayed multiple times.
73. Process ID of the server to which the UAP executing the SQL object is connected
This is the process ID of the front-end server or single server to which the UAP executing the SQL object is connected.
74. Transaction ID of the UAP executing the SQL object
TRANSACTION ID(GID/BID): aa...a/bb...b
Displays the transaction ID of the UAP executing the SQL object.
aa...a: Transaction's global identifier
bb...b: Transaction's branch identifier
75. Service name of the UAP executing the SQL object
Displays the service name of the UAP when the SQL object is being executed in an OLTP environment.
76. Name of the UAP executing the SQL object
Displays the name of the UAP executing the SQL object.
If the UAP name cannot be identified, nothing is displayed.
77. Connection number of the UAP executing the SQL object
Displays the connection number of the UAP executing the SQL object.
(c) Format of output maintenance information

pdobils VV-RR-ZZ [70]
<< SQL OBJECT CACHE INFORMATION >> DATE: 2004/01/01 TIME: 00:00:00 [1]
HOST NAME                   : node01 [2]
SERVER NAME                 : fes01 [3]
CACHE SIZE(KB)              : 2048 [4]
SQL OBJECT TOTAL SIZE(B)    : 1932556​ [5]

<< SQL OBJECT #N 12345/67890 >> [78]#
0001: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789​xx
0002: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789​xx

#
This item is output for each SQL object number that is specified.
If there is no SQL object that corresponds to a specified SQL object number, this item is not displayed.
Explanation:
Items 4, 5, and 70 are the same as for the general output format.
1. Time the SQL object buffer statistics was displayed
Displays the time the maintenance information was displayed.
2. Host name
Displays the name of the host for which maintenance information was acquired.
3. Server name
Displays the name of the server for which maintenance information was acquired.
78. Number and size of an SQL
SQL OBJECT #N aa...a/bb...b
N: Displays the management number in the SQL object buffer.
aa...a: Displays the size in bytes of the maintenance information.
bb...b: Displays the size in bytes of the SQL object.
(d) DAT format for general output

A delimiter string is used to separate the items that are output in the DAT format for general output. The output is to the standard output.

Characters such as TAB (X'09'), NL (X'0a'), and CR (X'0d') contained in the SQL statements are output as is. The line break character (0x0a) is displayed at the end of each line.

The following table lists the items that are output in the DAT format for general output.

Table 2-15 Items that are output in the DAT format for general output

Item name that is outputContent
DATEDATE part in [1]
TIMETIME part in [1]
HOST NAME[2]
SERVER NAME[3]
CACHE SIZE(KB)[4]
SQL OBJECT TOTAL SIZE(B)[5]
SQL OBJECT NO[6]
STATUS[7]
TYPE[8]
SIZE[9]
EXECUTE COUNT[10]
EXECUTE TIME AVG(s)[11]
EXECUTE TIME MAX(s)[12]
SERVICE NAME[13]
UAP NAME[14]
CONNECT NO[15]
SQL NO[16]
RECORD DATEDATE part in [17]
RECORD TIMETIME part in [17]
DB REFERENCE GET COUNT[28]
DB REFERENCE GET COUNT(AVG)[29]
DB REFERENCE GET COUNT(MAX)[30]
DB UPDATE GET COUNT[31]
DB UPDATE GET COUNT(AVG)[32]
DB UPDATE GET COUNT(MAX)[33]
DB READ COUNT[34]
DB READ COUNT(AVG)[35]
DB READ COUNT(MAX)[36]
DB WRITE COUNT[37]
DB WRITE COUNT(AVG)[38]
DB WRITE COUNT(MAX)[39]
LOB REFERENCE GET COUNT[40]
LOB REFERENCE GET COUNT(AVG)[41]
LOB REFERENCE GET COUNT(MAX)[42]
LOB UPDATE GET COUNT[43]
LOB UPDATE GET COUNT(AVG)[44]
LOB UPDATE GET COUNT(MAX)[45]
LOB READ COUNT[46]
LOB READ COUNT(AVG)[47]
LOB READ COUNT(MAX)[48]
LOB WRITE COUNT[49]
LOB WRITE COUNT(AVG)[50]
LOB WRITE COUNT(MAX)[51]
LIST REFERENCE GET COUNT[52]
LIST REFERENCE GET COUNT(AVG)[53]
LIST REFERENCE GET COUNT(MAX)[54]
LIST UPDATE GET COUNT[55]
LIST UPDATE GET COUNT(AVG)[56]
LIST UPDATE GET COUNT(MAX)[57]
LIST READ COUNT[58]
LIST READ COUNT(AVG)[59]
LIST READ COUNT(MAX)[60]
LIST WRITE COUNT[61]
LIST WRITE COUNT(AVG)[62]
LIST WRITE COUNT(MAX)[63]
WKFILE READ COUNT[64]
WKFILE READ COUNT(AVG)[65]
WKFILE READ COUNT(MAX)[66]
WKFILE WRITE COUNT[67]
WKFILE WRITE COUNT(AVG)[68]
WKFILE WRITE COUNT(MAX)[69]
PREPROCESSOR USER[18]
PREPROCESSOR SOURCE[19]
SECTION NO[20]
ISOLATION LEVEL[21]
OPTIMIZE LEVEL[22]
ADDITIONAL OPTIMIZE LEVEL[23]
DEFAULT SCHEMA[24]
ROUTINE ID[25]
ACCESS TYPE[71]
SQL[26]
(e) DAT format for output current execution information

A delimiter string is used to separate the items that are output in the DAT format for output current execution information. The output is to the standard output.

Characters such as TAB (X'09'), NL (X'0a'), and CR (X'0d') contained in the SQL statements are output as is. The line break character (0x0a) is displayed at the end of each line.

The following table lists the items that are output in the DAT format for output current execution information.

Table 2-16 Items that are output in the DAT format for output current execution information

Item name that is outputContent
DATEDATE part in [1]
TIMETIME part in [1]
HOST NAME[2]
SERVER NAME[3]
CACHE SIZE(KB)[4]
SQL OBJECT TOTAL SIZE(B)[5]
PROCESS ID[73]
TRANSACTION ID(GID/BID)[74]
SERVICE NAME[75]
UAP NAME[76]
CONNECT NO[77]
SQL OBJECT NO[6]
STATUS[7]
TYPE[8]
SIZE[9]
EXECUTE COUNT[10]
EXECUTE TIME AVG(s)[11]
EXECUTE TIME MAX(s)[12]
SERVICE NAME[13]
UAP NAME[14]
CONNECT NO[15]
SQL NO[16]
RECORD DATEDATE part in [17]
RECORD TIMETIME part in [17]
DB REFERENCE GET COUNT[28]
DB REFERENCE GET COUNT(AVG)[29]
DB REFERENCE GET COUNT(MAX)[30]
DB UPDATE GET COUNT[31]
DB UPDATE GET COUNT(AVG)[32]
DB UPDATE GET COUNT(MAX)[33]
DB READ COUNT[34]
DB READ COUNT(AVG)[35]
DB READ COUNT(MAX)[36]
DB WRITE COUNT[37]
DB WRITE COUNT(AVG)[38]
DB WRITE COUNT(MAX)[39]
LOB REFERENCE GET COUNT[40]
LOB REFERENCE GET COUNT(AVG)[41]
LOB REFERENCE GET COUNT(MAX)[42]
LOB UPDATE GET COUNT[43]
LOB UPDATE GET COUNT(AVG)[44]
LOB UPDATE GET COUNT(MAX)[45]
LOB READ COUNT[46]
LOB READ COUNT(AVG)[47]
LOB READ COUNT(MAX)[48]
LOB WRITE COUNT[49]
LOB WRITE COUNT(AVG)[50]
LOB WRITE COUNT(MAX)[51]
LIST REFERENCE GET COUNT[52]
LIST REFERENCE GET COUNT(AVG)[53]
LIST REFERENCE GET COUNT(MAX)[54]
LIST UPDATE GET COUNT[55]
LIST UPDATE GET COUNT(AVG)[56]
LIST UPDATE GET COUNT(MAX)[57]
LIST READ COUNT[58]
LIST READ COUNT(AVG)[59]
LIST READ COUNT(MAX)[60]
LIST WRITE COUNT[61]
LIST WRITE COUNT(AVG)[62]
LIST WRITE COUNT(MAX)[63]
WKFILE READ COUNT[64]
WKFILE READ COUNT(AVG)[65]
WKFILE READ COUNT(MAX)[66]
WKFILE WRITE COUNT[67]
WKFILE WRITE COUNT(AVG)[68]
WKFILE WRITE COUNT(MAX)[69]
PREPROCESSOR USER[18]
PREPROCESSOR SOURCE[19]
SECTION NO[20]
ISOLATION LEVEL[21]
OPTIMIZE LEVEL[22]
ADDITIONAL OPTIMIZE LEVEL[23]
DEFAULT SCHEMA[24]
ROUTINE ID[25]
ACCESS TYPE[71]
SQL[26]

(8) How to interpret statistical information

The following describes how to interpret statistical information.