>>
>>create table tstat (a char(5) not null ,
+>                     b smallint not null ,
+>                     c char(4),
+>                     d integer,
+>                     primary key (a,b) )
+>                     salt using 2 partitions ;

--- SQL operation complete.
>>insert into tstat values ('AAAA ',11,'AAAA',11);

--- 1 row(s) inserted.
>>insert into tstat values ('BBBB ',12,'BBBB',12);

--- 1 row(s) inserted.
>>insert into tstat values ('MMMM ',21,'MMMM',21);

--- 1 row(s) inserted.
>>insert into tstat values ('XXXXX',22,'XXXX', 22);

--- 1 row(s) inserted.
>>
>>control query default attempt_esp_parallelism 'on' ;

--- SQL operation complete.
>>control query default parallel_num_esps '2' ;

--- SQL operation complete.
>>control query default detailed_statistics 'ACCUMULATED' ;

--- SQL operation complete.
>>set session default statistics_view_type 'default' ;

--- SQL operation complete.
>>control query shape esp_exchange(hash_groupby(esp_exchange(scan)));

--- SQL operation complete.
>>
>>-- ACCUMULATED Stats Test
>>-- Also tests to mimic ODBC by calling explain options 'f'
>>-- and ensure qid current returns the qid user prepared statement
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>explain options 'f' s1 ;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  2.00E+000
3    .    4    esp_exchange                    1:2(hash2)            2.00E+000
2    .    3    hash_groupby                                          2.00E+000
1    .    2    esp_exchange                    2(hash2):2(hash2)     1.00E+002
.    .    1    trafodion_scan                  TSTAT                 1.00E+002

--- SQL operation complete.
>>log LOGRTS;
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>log;
>>display statistics for qid current;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>execute s1;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       2017/07/17 04:57:23.613254
Execute End Time         2017/07/17 04:57:26.843039
Execute Elapsed Time                 0:00:03.229785
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:26.841272
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        3.229785 secs
Max. Execute Time        3.229785 secs
Avg. Execute Time        3.229785 secs
Stats Collection Type    ACCUMULATED_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           689,510
SQL Process Busy Time    4,385,817
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       10,471                    KB
SQL Heap Used            80                        KB
SQL Heap WM              67                        KB
Processes Created        2
Process Create Time      141,744
Request Message Count    26
Request Message Bytes    39,392
Reply Message Count      15
Reply Message Bytes      38,792
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       2017/07/17 04:57:23.613254
Execute End Time         2017/07/17 04:57:26.843039
Execute Elapsed Time                 0:00:03.229785
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:26.841272
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        3.229785 secs
Max. Execute Time        3.229785 secs
Avg. Execute Time        3.229785 secs
Stats Collection Type    ACCUMULATED_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           689,510
SQL Process Busy Time    4,385,817
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       10,471                    KB
SQL Heap Used            80                        KB
SQL Heap WM              67                        KB
Processes Created        2
Process Create Time      141,744
Request Message Count    26
Request Message Bytes    39,392
Reply Message Count      15
Reply Message Bytes      38,792
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>log;
>>display statistics for qid current;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       2017/07/17 04:57:23.613254
Execute End Time         2017/07/17 04:57:26.843039
Execute Elapsed Time                 0:00:03.229785
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:26.841272
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        3.229785 secs
Max. Execute Time        3.229785 secs
Avg. Execute Time        3.229785 secs
Stats Collection Type    ACCUMULATED_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           689,510
SQL Process Busy Time    4,385,817
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       10,471                    KB
SQL Heap Used            80                        KB
SQL Heap WM              67                        KB
Processes Created        2
Process Create Time      141,744
Request Message Count    26
Request Message Bytes    39,392
Reply Message Count      15
Reply Message Bytes      38,792
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_302_S1
Compile Start Time       2017/07/17 04:57:16.975498
Compile End Time         2017/07/17 04:57:17.007581
Compile Elapsed Time                 0:00:00.032083
Execute Start Time       2017/07/17 04:57:23.613254
Execute End Time         2017/07/17 04:57:26.843039
Execute Elapsed Time                 0:00:03.229785
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:26.841272
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        3.229785 secs
Max. Execute Time        3.229785 secs
Avg. Execute Time        3.229785 secs
Stats Collection Type    ACCUMULATED_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           689,510
SQL Process Busy Time    4,385,817
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       10,471                    KB
SQL Heap Used            80                        KB
SQL Heap WM              67                        KB
Processes Created        2
Process Create Time      141,744
Request Message Count    26
Request Message Bytes    39,392
Reply Message Count      15
Reply Message Bytes      38,792
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;

*** ERROR[8923] The given Query Id MXID11000024709212367027407408000000000000206U3333300_302_S1 is not found.

--- SQL operation failed with errors.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_302_S1 ;

*** ERROR[8923] The given Query Id MXID11000024709212367027407408000000000000206U3333300_302_S1 is not found.

--- SQL operation failed with errors.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_311_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_311_S1
Compile Start Time       2017/07/17 04:57:33.096199
Compile End Time         2017/07/17 04:57:33.096764
Compile Elapsed Time                 0:00:00.000565
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_311_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_311_S1
Compile Start Time       2017/07/17 04:57:33.096199
Compile End Time         2017/07/17 04:57:33.096764
Compile Elapsed Time                 0:00:00.000565
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>log;
>>display statistics for qid current;
Qid                      MXID11000024709212367027407408000000000000206U3333300_311_S1
Compile Start Time       2017/07/17 04:57:33.096199
Compile End Time         2017/07/17 04:57:33.096764
Compile Elapsed Time                 0:00:00.000565
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_311_S1
Compile Start Time       2017/07/17 04:57:33.096199
Compile End Time         2017/07/17 04:57:33.096764
Compile Elapsed Time                 0:00:00.000565
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    ACCUMULATED_STATS

--- SQL operation complete.
>>
>>-- PERTABLE stats
>>control query default detailed_statistics 'pertable' ;

--- SQL operation complete.
>>set session default statistics_view_type 'default' ;

--- SQL operation complete.
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>log;
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>log;
>>display statistics for qid current;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>display statistics for qid current accumulated ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for qid current accumulated ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>execute s1;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               5,738               2,914

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               5,738               2,914

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           2,914
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_316_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           2,914
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>log;
>>display statistics for qid current;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               5,738               2,914

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               5,738               2,914

--- SQL operation complete.
>>display statistics for qid current accumulated;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           2,914
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for qid current accumulated;
Qid                      MXID11000024709212367027407408000000000000206U3333300_316_S1
Compile Start Time       2017/07/17 04:57:39.632898
Compile End Time         2017/07/17 04:57:39.665467
Compile Elapsed Time                 0:00:00.032569
Execute Start Time       2017/07/17 04:57:49.683630
Execute End Time         2017/07/17 04:57:49.695340
Execute Elapsed Time                 0:00:00.011710
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:57:49.693689
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.011710 secs
Max. Execute Time        0.011710 secs
Avg. Execute Time        0.011710 secs
Stats Collection Type    PERTABLE_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           2,914
SQL Process Busy Time    5,776
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,338                    KB
Processes Created        0
Process Create Time      0
Request Message Count    27
Request Message Bytes    39,552
Reply Message Count      15
Reply Message Bytes      39,208
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>log;
>>obey PQIDOUT;
>>SET SESSION DEFAULT PARENT_QID 'MXID11000024709212367027407408000000000000206U3333300_316_S1';

--- SQL operation complete.
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_330_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_330_S1
Compile Start Time       2017/07/17 04:57:56.670883
Compile End Time         2017/07/17 04:57:56.671315
Compile Elapsed Time                 0:00:00.000432
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               MXID11000024709212367027407408000000000000206U3333300_316_S1
Parent Query System      SAME
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_330_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_330_S1
Compile Start Time       2017/07/17 04:57:56.670883
Compile End Time         2017/07/17 04:57:56.671315
Compile Elapsed Time                 0:00:00.000432
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               MXID11000024709212367027407408000000000000206U3333300_316_S1
Parent Query System      SAME
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT PARENT_QID 'NONE' ;

--- SQL operation complete.
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_332_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_332_S1
Compile Start Time       2017/07/17 04:57:59.906249
Compile End Time         2017/07/17 04:57:59.906902
Compile Elapsed Time                 0:00:00.000653
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_332_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_332_S1
Compile Start Time       2017/07/17 04:57:59.906249
Compile End Time         2017/07/17 04:57:59.906902
Compile Elapsed Time                 0:00:00.000653
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    PERTABLE_STATS

--- SQL operation complete.
>>log;
>>
>>-- TEST for OPERATOR stats
>>control query default detailed_statistics 'system' ;

--- SQL operation complete.
>>set session default statistics_view_type 'default' ;

--- SQL operation complete.
>>prepare s1 from select distinct d from tstat ;

--- SQL command prepared.
>>log ;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    OPERATOR_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    OPERATOR_STATS

--- SQL operation complete.
>>log;
>>execute s1 ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
>>log ;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               17                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               31                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                8                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               17                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               31                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                8                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           5,606
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 ACCUMULATED;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           5,606
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 PERTABLE;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 PERTABLE;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 PROGRESS;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

   Id             TDBName                Mode       DOP      TopN            BMOPhase     InterimRowCount             CPUTime
              BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt
               ScrFileCnt           ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6        EX_HASH_GRBY                DISK         2        -1           READ_ROWS                   0               2,283
                        4               1,024               7,687                 256                   0
                        0                  -1                   0                   0                   0                   0

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 PROGRESS;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

   Id             TDBName                Mode       DOP      TopN            BMOPhase     InterimRowCount             CPUTime
              BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt
               ScrFileCnt           ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6        EX_HASH_GRBY                DISK         2        -1           READ_ROWS                   0               2,283
                        4               1,024               7,687                 256                   0
                        0                  -1                   0                   0                   0                   0

--- SQL operation complete.
>>log;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 DEFAULT;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               17                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               31                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                8                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_335_S1 DEFAULT;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           2
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               17                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               31                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                8                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>log;
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2                7                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                5                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               11                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                6                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>get statistics for qid current accumulated ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           5,606
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>get statistics for qid current pertable ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

--- SQL operation complete.
>>get statistics for qid current progress ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

   Id             TDBName                Mode       DOP      TopN            BMOPhase     InterimRowCount             CPUTime
              BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt
               ScrFileCnt           ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6        EX_HASH_GRBY                DISK         2        -1           READ_ROWS                   0               2,283
                        4               1,024               7,687                 256                   0
                        0                  -1                   0                   0                   0                   0

--- SQL operation complete.
>>get statistics for qid current progress, options 'sl' ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528

   Id       DOP  EstRowsAccess       ActRowsAccess     EstRowUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime TableName
    1         2              0                   4            100                   4                   4                   0               9,682               5,606 TRAFODION.SCH.TSTAT

   Id             TDBName Mode       DOP      TopN    BMOPhase     InterimRowCount             CPUTime         BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt   FileCnt ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6 EX_HASH_GRBY        DISK         2        -1   READ_ROWS                   0               2,283                   4               1,024               7,687                 256                   0         0        -1                   0                   0                   0                   0

--- SQL operation complete.
>>get statistics for qid current default ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2                7                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                5                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               11                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                6                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>set session default statistics_view_type 'default' ;

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 10                  0                  4 1694
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 10                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2               10              1,674                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2                7                119                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                5                  7                  2                  4 3201
5    .    6    7    3    2    EX_HASH_GRBY             2                6              2,283                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               11                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                781                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               11                154                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                6                 13                100                  4 3346
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                4              3,179                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>set session default statistics_view_type 'accumulated' ;

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
Accessed Rows            4
Used Rows                4
SE IOs                   4
SE IO Bytes              176
SE IO MAX Time           5,606
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

--- SQL operation complete.
>>set session default statistics_view_type 'pertable' ;

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528
BMO Space Buffer Size    256
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

--- SQL operation complete.
>>set session default statistics_view_type 'progress' ;

--- SQL operation complete.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:09.741408
Execute End Time         2017/07/17 04:58:09.757985
Execute Elapsed Time                 0:00:00.016577
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  3
Number of Cpus           1
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:09.756320
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.016577 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.016577 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    8,241
SQL Space Allocated      896                       KB
SQL Space Used           766                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    28
Request Message Bytes    39,712
Reply Message Count      15
Reply Message Bytes      42,528

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         2   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   4                   0               9,682               5,606

   Id             TDBName                Mode       DOP      TopN            BMOPhase     InterimRowCount             CPUTime
              BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt
               ScrFileCnt           ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6        EX_HASH_GRBY                DISK         2        -1           READ_ROWS                   0               2,283
                        4               1,024               7,687                 256                   0
                        0                  -1                   0                   0                   0                   0

--- SQL operation complete.
>>set session default statistics_view_type 'pertable' ;

--- SQL operation complete.
>>
>>-- TEST for set session settings
>>set statistics pertable ;
>>execute s1 ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:36.062596
Execute End Time         2017/07/17 04:58:36.074703
Execute Elapsed Time                 0:00:00.012107
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:36.071991
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    2
Min. Execute Time        0.012107 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.014342 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    6,703
UDR Process Busy Time    0
SQL Space Allocated      896                       KB
SQL Space Used           773                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    18
Request Message Bytes    5,856
Reply Message Count      6
Reply Message Bytes      45,520
BMO Space Buffer Size    256
BMO Space Buffer Count   2
BMO Interim Row Count    0
Scr. Overflow Mode       DISK
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP  EstRowsAccess       ActRowsAccess     EstRowUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime TableName
    1         2              0                   4            100                   4                   4                   0               9,421               5,009 TRAFODION.SCH.TSTAT

--- SQL operation complete.
>>set statistics progress ;
>>execute s1 ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:36.083695
Execute End Time         2017/07/17 04:58:36.092334
Execute Elapsed Time                 0:00:00.008639
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:36.090303
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    3
Min. Execute Time        0.008639 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.012441 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    5,314
SQL Space Allocated      896                       KB
SQL Space Used           774                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    18
Request Message Bytes    5,856
Reply Message Count      6
Reply Message Bytes      45,520

   Id       DOP  EstRowsAccess       ActRowsAccess     EstRowUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime TableName
    1         2              0                   4            100                   4                   4                   0               5,561               2,855 TRAFODION.SCH.TSTAT

   Id             TDBName Mode       DOP      TopN    BMOPhase     InterimRowCount             CPUTime         BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt   FileCnt ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6 EX_HASH_GRBY        DISK         2        -1   READ_ROWS                   0               2,798                   4               1,024               7,687                 256                   2         0        -1                   0                   0                   0                   0

--- SQL operation complete.
>>set statistics default ;
>>execute s1 ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:36.103967
Execute End Time         2017/07/17 04:58:36.114108
Execute Elapsed Time                 0:00:00.010141
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:36.112085
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    4
Min. Execute Time        0.008639 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.011866 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                 18                  0                  4 328
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                 16                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2                6                294                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               15                 23                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                 19                  2                  4 3239
5    .    6    7    3    2    EX_HASH_GRBY             2                5              2,826                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2                8                 12                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                359                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               17                126                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                7                 18                100                  4 1816
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                2              1,672                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>set statistics all ;
>>execute s1 ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:36.123657
Execute End Time         2017/07/17 04:58:36.132754
Execute Elapsed Time                 0:00:00.009097
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:36.130652
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    5
Min. Execute Time        0.008639 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.011312 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    4,410
SQL Space Allocated      896                       KB
SQL Space Used           774                       KB
SQL Heap Allocated       200                       KB
SQL Heap Used            78                        KB
SQL Heap WM              10,341                    KB
Processes Created        0
Process Create Time      0
Request Message Count    18
Request Message Bytes    5,856
Reply Message Count      6
Reply Message Bytes      45,520

   Id       DOP  EstRowsAccess       ActRowsAccess     EstRowUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime TableName
    1         2              0                   4            100                   4                   4                   0               6,436               3,824 TRAFODION.SCH.TSTAT

   Id             TDBName Mode       DOP      TopN    BMOPhase     InterimRowCount             CPUTime         BMOHeapUsed    BMOHeapAllocated           BMOHeapWM       BMOSpaceBufSz      BMOSpaceBufCnt   FileCnt ScrIOSize           ScrIORead        ScrIOWritten           ScrIOTime        ScrIOMaxTime
    6 EX_HASH_GRBY        DISK         2        -1   READ_ROWS                   0               2,419                   4               1,024               7,687                 256                   2         0        -1                   0                   0                   0                   0

--- SQL operation complete.
Qid                      MXID11000024709212367027407408000000000000206U3333300_335_S1
Compile Start Time       2017/07/17 04:58:06.342753
Compile End Time         2017/07/17 04:58:06.391558
Compile Elapsed Time                 0:00:00.048805
Execute Start Time       2017/07/17 04:58:36.123657
Execute End Time         2017/07/17 04:58:36.132754
Execute Elapsed Time                 0:00:00.009097
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select distinct d from tstat ;
SQL Source Length        30
Rows Returned            4
First Row Returned Time  2017/07/17 04:58:36.130652
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    5
Min. Execute Time        0.008639 secs
Max. Execute Time        0.016577 secs
Avg. Execute Time        0.011312 secs
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

10   .    11   .    5    0    EX_ROOT                  1                5                  4                  0                  4 298
9    .    10   11   4    0    EX_SPLIT_TOP             1                5                  5                  2                  4
8    .    9    10   4    0    EX_SEND_TOP              2                6                289                  2                  4
7    .    8    9    4    2    EX_SEND_BOTTOM           2               15                  3                  2                  4
6    .    7    8    4    2    EX_SPLIT_BOTTOM          2                7                  7                  2                  4 2663
5    .    6    7    3    2    EX_HASH_GRBY             2                5              2,419                  1                  4 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             2               10                 11                100                  4
3    .    4    5    2    2    EX_SEND_TOP              4               18                223                100                  4
2    .    3    4    2    3    EX_SEND_BOTTOM           4               15                114                100                  4
1    .    2    3    2    3    EX_SPLIT_BOTTOM          2                7                  9                100                  4 1449
.    .    1    2    1    3    EX_TRAF_KEY_SELECT       2                2              1,326                100                  4 TRAFODION.SCH.TSTAT|4|176

--- SQL operation complete.
>>set statistics off ;
>>
>>-- TEST for RMS Stats
>>get statistics for RMS 0 ;
Node name                     gselva-trafodion-ins.novalocal:0
Node Id                       0
RMS Version                   2511
SSCP PID                      22045
SSCP Creation Timestamp       2017/07/17 04:52:22.097222
SSMP PID                      22061
SSMP Creation Timestamp       2017/07/17 04:52:22.365450
Source String Store Len       254
Stats Heap Allocated          67,106,592
Stats Heap Used               1,719,136
Stats Heap High WM            1,728,808
No.of Process Stats Heaps     8
No.of Process Regd.           6
No.of Query Fragments Regd.   12
RMS Semaphore Owner           -1
No.of SSCPs Opened            2
No.of SSCPs Open Deleted      0
Last GC Time                  2017/07/17 04:52:22.200075
Queries GCed in Last Run      0
Total Queries GCed            0
SSMP Request Message Count    577
SSMP Request Message Bytes    323,872
SSMP Reply Message Count      576
SSMP Reply Message Bytes      148,000
SSCP Request Message Count    42
SSCP Request Message Bytes    17,904
SSCP Reply Message Count      42
SSCP Reply Message Bytes      54,992
RMS Stats Reset Timestamp     2017/07/17 04:52:22.200076
No. Query Invalidation Keys   12


--- SQL operation complete.
>>-- get statistics for RMS all is not tested because it can filtering issues
>>
>>-- Misc tests to increase code coverage
>>control query shape cut;

--- SQL operation complete.
>>select 'FUNKY_OPT_UNIQUE', count(*) from table(statistics(NULL,'CPU=0'));

(EXPR)            (EXPR)              
----------------  --------------------

FUNKY_OPT_UNIQUE                     0

--- 1 row(s) selected.
>>select 'FUNKY_OPT_UNIQUE', count(*) from table(statistics(NULL,'CPU_OFFENDER=-1'));

(EXPR)            (EXPR)              
----------------  --------------------

FUNKY_OPT_UNIQUE                     0

--- 1 row(s) selected.
>>-- Coverage for SscpNewIncomingConnectionStream::processCpuStatsReq
>>select 'FUNKY_OPT_UNIQUE', count(*) from table(statistics(NULL,'CPU_OFFENDER=-1'));

(EXPR)            (EXPR)              
----------------  --------------------

FUNKY_OPT_UNIQUE                     0

--- 1 row(s) selected.
>>select 'FUNKY_OPT_UNIQUE', count(*) from table(statistics(NULL,'DISK_OFFENDER=-1'));

(EXPR)            (EXPR)              
----------------  --------------------

FUNKY_OPT_UNIQUE                     0

--- 1 row(s) selected.
>>log;
Stats Heap Allocated          67,106,592
>>
>>-- TEST for EXPLAIN_IN_RMS 
>>control query default explain_in_rms 'on' ;

--- SQL operation complete.
>>control query shape cut;

--- SQL operation complete.
>>prepare s1 from select d from tstat ;

--- SQL command prepared.
>>log;
>>explain options 'f' for qid MXID11000024709212367027407408000000000000206U3333300_365_S1 ;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  1.00E+002
.    .    1    trafodion_scan                  TSTAT                 1.00E+002

--- SQL operation complete.
>>log;
>>
>>-- TEST for index name - Soln 10-090330-0390 
>>create index tstati on tstat(d);

--- SQL operation complete.
>>prepare s1 from select d from tstat ;

--- SQL command prepared.
>>explain options 'f' s1 ;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  1.00E+002
.    .    1    trafodion_index_scan            TSTATI                1.00E+002

--- SQL operation complete.
>>log ;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_485_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_485_S1
Compile Start Time       2017/07/17 04:58:57.643874
Compile End Time         2017/07/17 04:58:57.908399
Compile Elapsed Time                 0:00:00.264525
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select d from tstat ;
SQL Source Length        21
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    OPERATOR_STATS

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_485_S1 ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_485_S1
Compile Start Time       2017/07/17 04:58:57.643874
Compile End Time         2017/07/17 04:58:57.908399
Compile Elapsed Time                 0:00:00.264525
Execute Start Time       -1
Execute End Time         -1
Execute Elapsed Time                 0:00:00.000000
State                    CLOSE
Rows Affected            -1
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           0
Transaction Id           -1
Source String            select d from tstat ;
SQL Source Length        21
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    0
Min. Execute Time        0.000000 secs
Max. Execute Time        0.000000 secs
Avg. Execute Time        0.000000 secs
Stats Collection Type    OPERATOR_STATS

--- SQL operation complete.
>>log;
>>execute s1 ;

D          
-----------

         11
         12
         21
         22

--- 4 row(s) selected.
>>log ;
>>SET SESSION DEFAULT STATISTICS_VIEW_TYPE 'DEFAULT' ;

--- SQL operation complete.
>>display statistics for QID MXID11000024709212367027407408000000000000206U3333300_485_S1 PERTABLE;
Qid                      MXID11000024709212367027407408000000000000206U3333300_485_S1
Compile Start Time       2017/07/17 04:58:57.643874
Compile End Time         2017/07/17 04:58:57.908399
Compile Elapsed Time                 0:00:00.264525
Execute Start Time       2017/07/17 04:59:04.378853
Execute End Time         2017/07/17 04:59:04.398868
Execute Elapsed Time                 0:00:00.020015
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           -1
Source String            select d from tstat ;
SQL Source Length        21
Rows Returned            4
First Row Returned Time  2017/07/17 04:59:04.397836
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.020015 secs
Max. Execute Time        0.020015 secs
Avg. Execute Time        0.020015 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    1,251
UDR Process Busy Time    0
SQL Space Allocated      32                        KB
SQL Space Used           5                         KB
SQL Heap Allocated       39                        KB
SQL Heap Used            35                        KB
SQL Heap WM              42                        KB
Processes Created        0
Process Create Time      0
Request Message Count    0
Request Message Bytes    0
Reply Message Count      0
Reply Message Bytes      0
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         1   TRAFODION.SCH.TSTATI
              0                   4            100                   4                   2                   0              17,779              17,779

--- SQL operation complete.
>>get statistics for QID MXID11000024709212367027407408000000000000206U3333300_485_S1 PERTABLE;
Qid                      MXID11000024709212367027407408000000000000206U3333300_485_S1
Compile Start Time       2017/07/17 04:58:57.643874
Compile End Time         2017/07/17 04:58:57.908399
Compile Elapsed Time                 0:00:00.264525
Execute Start Time       2017/07/17 04:59:04.378853
Execute End Time         2017/07/17 04:59:04.398868
Execute Elapsed Time                 0:00:00.020015
State                    CLOSE
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           -1
Source String            select d from tstat ;
SQL Source Length        21
Rows Returned            4
First Row Returned Time  2017/07/17 04:59:04.397836
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.020015 secs
Max. Execute Time        0.020015 secs
Avg. Execute Time        0.020015 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    1,251
UDR Process Busy Time    0
SQL Space Allocated      32                        KB
SQL Space Used           5                         KB
SQL Heap Allocated       39                        KB
SQL Heap Used            35                        KB
SQL Heap WM              42                        KB
Processes Created        0
Process Create Time      0
Request Message Count    0
Request Message Bytes    0
Reply Message Count      0
Reply Message Bytes      0
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         1   TRAFODION.SCH.TSTATI
              0                   4            100                   4                   2                   0              17,779              17,779

--- SQL operation complete.
>>log;
>>
>>-- EXECDirect test to check if QID current is proper
>>select * from tstat ;

A      B       C     D          
-----  ------  ----  -----------

MMMM       21  MMMM           21
XXXXX      22  XXXX           22
AAAA       11  AAAA           11
BBBB       12  BBBB           12

--- 4 row(s) selected.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_509___SQLCI_DML_LAST__
Compile Start Time       2017/07/17 04:59:07.758944
Compile End Time         2017/07/17 04:59:07.764317
Compile Elapsed Time                 0:00:00.005373
Execute Start Time       2017/07/17 04:59:07.764460
Execute End Time         2017/07/17 04:59:07.772291
Execute Elapsed Time                 0:00:00.007831
State                    DEALLOCATED
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           -1
Source String            select * from tstat ;
SQL Source Length        21
Rows Returned            4
First Row Returned Time  2017/07/17 04:59:07.771225
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.007831 secs
Max. Execute Time        0.007831 secs
Avg. Execute Time        0.007831 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    1,070
UDR Process Busy Time    0
SQL Space Allocated      32                        KB
SQL Space Used           5                         KB
SQL Heap Allocated       39                        KB
SQL Heap Used            35                        KB
SQL Heap WM              42                        KB
Processes Created        0
Process Create Time      0
Request Message Count    0
Request Message Bytes    0
Reply Message Count      0
Reply Message Bytes      0
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         1   TRAFODION.SCH.TSTAT
              0                   4            100                   4                   2                   0               5,664               5,664

--- SQL operation complete.
>>
>>-- Compare QID CURRENT to QID <qid>
>>control query default detailed_statistics 'ACCUMULATED' ;

--- SQL operation complete.
>>obey TESTRTS(compare_current_qid);
>>prepare s1 from select count(*) from
+>tstat A, tstat B, tstat C, tstat D;

--- SQL command prepared.
>>
>>execute s1;

(EXPR)              
--------------------

                 256

--- 1 row(s) selected.
>>
>>log;
>>sh grep "^QID is " QID_TEMP | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > STATS_CMD_QID;
>>log;
1c1,2
< >>get statistics for qid current;
---
> >>obey STATS_CMD_QID;
> >>get statistics for qid MXID11000024709212367027407408000000000000206U3333300_512_S1;
21c22
< Number of Cpus           1
---
> Number of Cpus           2
38a40,50
>    Id       DOP          Table Name
>   EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
>     1         0   TRAFODION.SCH.TSTATI
>               0                   0            100                   0                   0                   0                   0                   0
>     6         0   TRAFODION.SCH.TSTATI
>               0                   0            100                   0                   0                   0                   0                   0
>    11         0   TRAFODION.SCH.TSTATI
>               0                   0            100                   0                   0                   0                   0                   0
>    16         0   TRAFODION.SCH.TSTATI
>               0                   0            100                   0                   0                   0                   0                   0
> 
>>
>>
>>control query default detailed_statistics 'PERTABLE' ;

--- SQL operation complete.
>>obey TESTRTS(compare_current_qid);
>>prepare s1 from select count(*) from
+>tstat A, tstat B, tstat C, tstat D;

--- SQL command prepared.
>>
>>execute s1;

(EXPR)              
--------------------

                 256

--- 1 row(s) selected.
>>
>>log;
>>sh grep "^QID is " QID_TEMP | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > STATS_CMD_QID;
>>log;
1c1,2
< >>get statistics for qid current;
---
> >>obey STATS_CMD_QID;
> >>get statistics for qid MXID11000024709212367027407408000000000000206U3333300_516_S1;
21c22
< Number of Cpus           1
---
> Number of Cpus           2
>>
>>
>>control query default detailed_statistics 'system' ;

--- SQL operation complete.
>>obey TESTRTS(compare_current_qid);
>>prepare s1 from select count(*) from
+>tstat A, tstat B, tstat C, tstat D;

--- SQL command prepared.
>>
>>execute s1;

(EXPR)              
--------------------

                 256

--- 1 row(s) selected.
>>
>>log;
>>sh grep "^QID is " QID_TEMP | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > STATS_CMD_QID;
>>log;
1c1,2
< >>get statistics for qid current;
---
> >>obey STATS_CMD_QID;
> >>get statistics for qid MXID11000024709212367027407408000000000000206U3333300_520_S1;
21c22
< Number of Cpus           1
---
> Number of Cpus           2
>>
>>
>>obey TESTRTS(exeindp2_stats);

*** ERROR[15014] Section exeindp2_stats was not found in file TESTRTS.

>>--Negative tests using STATISTICS stored procedure
>>select * from table (statistics(null, 'QID='));

--- 0 row(s) selected.
>>select * from table (statistics(null, 'QID=mxid'));

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id mxid.

--- 0 row(s) selected.
>>select * from table (statistics(null, 'QID=MXID01001'));

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID01001.

--- 0 row(s) selected.
>>select * from table (statistics(null, 'CPU='));

--- 0 row(s) selected.
>>select * from table (statistics(null, 'PID='));

*** ERROR[8920] Invalid CPU or PID passed in. Unable to retrieve Runtime Statistics.

--- 0 row(s) selected.
>>select * from table (statistics(null, 'SYSTEM='));

--- 0 row(s) selected.
>>select * from table (statistics(null, 'MERGE=2'));

--- 0 row(s) selected.
>>
>>--Negative tests using GET STATISTICS command
>>get statistics for qid ;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for qid ;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for qid mxid ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID.

--- SQL operation failed with errors.
>>get statistics for qid "mxid" ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id mxid.

--- SQL operation failed with errors.
>>get statistics for qid mxid01001 ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID01001.

--- SQL operation failed with errors.
>>get statistics for qid mxid0100202 ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID0100202.

--- SQL operation failed with errors.
>>get statistics for qid mxid010010A0788 ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID010010A0788.

--- SQL operation failed with errors.
>>get statistics for qid mxid01002020880 ;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id MXID01002020880.

--- SQL operation failed with errors.
>>get statistics for cpu ;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for cpu ;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for cpu a;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for cpu a;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for cpu "A";

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for cpu "A";
                         ^ (26 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for pid ;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for pid ;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for pid a,a ;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for pid a,a ;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>get statistics for pid a;

*** ERROR[15001] A syntax error occurred at or before: 
get statistics for pid a;
                       ^ (24 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>-- Test to determine if invalid token is taken as stmt name
>>select * from table (statistics(null, 'AAA='));

--- 0 row(s) selected.
>>
>>obey TESTRTS(esp_idle_timeout);
>>get process statistics for current ;

Node Id:                      0
PID                           24709
Start Time                    2017/07/17 04:56:47.408000
EXE Memory Allocated          24 MB
EXE Memory Used High WM       95 MB
EXE Memory Used               24 MB
IPC Memory Allocated          1 MB
IPC Memory Used High WM       1 MB
IPC Memory Used               1 MB
Static Stmt Count             0
Dynamic Stmt Count            3
Open Stmt Count               0
Reclaimable Stmt Count        0
Reclaimed Stmt Count          0
Total ESPs Started            2
Total ESPs Startup Completed  2
Total ESPs Error in Startup   0
Total ESPs Deleted            0
Num ESPs In Use               2
Num ESPs Free                 0
Recent Qid                    MXID11000024709212367027407408000000000000206U3333300_520_S1

--- SQL operation complete.
>>set session default esp_idle_timeout '60' ;

--- SQL operation complete.
>>cqd esp_idle_timeout '60' ;

--- SQL operation complete.
>>control query shape esp_exchange(hash_groupby(esp_exchange(scan)));

--- SQL operation complete.
>>select distinct d from tstat ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
>>get process statistics for current ;

Node Id:                      0
PID                           24709
Start Time                    2017/07/17 04:56:47.408000
EXE Memory Allocated          24 MB
EXE Memory Used High WM       95 MB
EXE Memory Used               24 MB
IPC Memory Allocated          1 MB
IPC Memory Used High WM       1 MB
IPC Memory Used               1 MB
Static Stmt Count             0
Dynamic Stmt Count            3
Open Stmt Count               0
Reclaimable Stmt Count        0
Reclaimed Stmt Count          0
Total ESPs Started            4
Total ESPs Startup Completed  4
Total ESPs Error in Startup   0
Total ESPs Deleted            0
Num ESPs In Use               2
Num ESPs Free                 2
Recent Qid                    MXID11000024709212367027407408000000000000206U3333300_548___SQLCI_DML_LAST__

--- SQL operation complete.
>>sh sleep 70;
>>select distinct d from tstat ;

D          
-----------

         12
         11
         21
         22

--- 4 row(s) selected.
>>get process statistics for current ;

Node Id:                      0
PID                           24709
Start Time                    2017/07/17 04:56:47.408000
EXE Memory Allocated          24 MB
EXE Memory Used High WM       95 MB
EXE Memory Used               24 MB
IPC Memory Allocated          1 MB
IPC Memory Used High WM       1 MB
IPC Memory Used               1 MB
Static Stmt Count             0
Dynamic Stmt Count            3
Open Stmt Count               0
Reclaimable Stmt Count        0
Reclaimed Stmt Count          0
Total ESPs Started            6
Total ESPs Startup Completed  6
Total ESPs Error in Startup   0
Total ESPs Deleted            2
Num ESPs In Use               2
Num ESPs Free                 2
Recent Qid                    MXID11000024709212367027407408000000000000206U3333300_550___SQLCI_DML_LAST__

--- SQL operation complete.
>>control query shape cut;

--- SQL operation complete.
>>
>>obey TESTRTS(hive_tests);
>>select [last 0] * from hive.hive.store;

--- 0 row(s) selected.
>>get statistics for qid current ;
Qid                      MXID11000024709212367027407408000000000000206U3333300_553___SQLCI_DML_LAST__
Compile Start Time       2017/07/17 05:00:25.145886
Compile End Time         2017/07/17 05:00:30.328535
Compile Elapsed Time                 0:00:05.182649
Execute Start Time       2017/07/17 05:00:30.328886
Execute End Time         2017/07/17 05:00:30.370297
Execute Elapsed Time                 0:00:00.041411
State                    DEALLOCATED
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           -1
Source String            select [last 0] * from hive.hive.store;
SQL Source Length        39
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
No. of times executed    1
Min. Execute Time        0.041411 secs
Max. Execute Time        0.041411 secs
Avg. Execute Time        0.041411 secs
Stats Collection Type    OPERATOR_STATS
SQL Process Busy Time    4,950
UDR Process Busy Time    0
SQL Space Allocated      72,584                    KB
SQL Space Used           72,577                    KB
SQL Heap Allocated       139,153                   KB
SQL Heap Used            139,152                   KB
SQL Heap WM              139,152                   KB
Processes Created        0
Process Create Time      0
Request Message Count    0
Request Message Bytes    0
Reply Message Count      0
Reply Message Bytes      0
BMO Space Buffer Size    0
BMO Space Buffer Count   0
BMO Interim Row Count    0
Scr. Overflow Mode       UNKNOWN
Scr. File Count          0
Scr. IO Size             0
Scr. Read Count          0
Scr. Write Count         0
Scr. IO Max Time         0
Sort TopN                -1

   Id       DOP          Table Name
  EstRowsAccess       ActRowsAccess    EstRowsUsed         ActRowsUsed              SE_IOs        SE_IO_KBytes       SE_IO_SumTime       SE_IO_MaxTime
    1         1   HIVE.HIVE.STORE
              0                  12              0                  12                   1                   3               2,059               2,059

--- SQL operation complete.
>>
>>obey TESTRTS(clean_up);
>>drop table tstat;

--- SQL operation complete.
>>
>>log ;
