>>showstats for table t042_orderline on ol_o_id detail;

Detailed Histogram data for Table TRAFODION.ORDERENTRY.T042_ORDERLINE
Table ID: 1700229370398217975

Hist ID:    767276153
Column(s):  OL_O_ID
Total Rows: 10
Total UEC:  4
Low Value:  (1)
High Value: (9)
Intervals:  1

Number    Rowcount         UEC Boundary
====== =========== =========== ======================================
     0           0           0 (1)
     1          10           4 (9)


--- SQL operation complete.
>>
>>cqd HYBRID_QUERY_CACHE 'off';

--- SQL operation complete.
>>-- avoid caching the metadata queries
>>prepare xx from select * from t042_t1;

--- SQL command prepared.
>>prepare xx from select * from t042_BTA1P006;

--- SQL command prepared.
>>prepare xx from select * from t042_orderline;

--- SQL command prepared.
>>
>>sh rm hqc.log;
>>cqd HQC_LOG 'on';

--- SQL operation complete.
>>cqd HQC_LOG_FILE 'hqc.log';

--- SQL operation complete.
>>cqd HYBRID_QUERY_CACHE 'on';

--- SQL operation complete.
>>cqd QUERY_CACHE_USE_CONVDOIT_FOR_BACKPATCH 'ON';

--- SQL operation complete.
>>
>>prepare xx from select * from t042_orderline where ol_o_id = 1 ;

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

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
          1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
          1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
          1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO

--- 4 row(s) selected.
>>
>>prepare xx from select * from t042_orderline where ol_o_id = 2 ;

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

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
          2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw

--- 3 row(s) selected.
>>
>>prepare xx from select * from t042_orderline where ol_o_id = 3 ;

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

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
          3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh

--- 2 row(s) selected.
>>
>>prepare xx from select * from t042_orderline where ol_o_id = 162 ;

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

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_orderline where ol_o_id = 1 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

Found in HQC:
SQL query=select * from t042_orderline where ol_o_id = 2 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_orderline where ol_o_id = 2 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

Found in HQC:
SQL query=select * from t042_orderline where ol_o_id = 3 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_orderline where ol_o_id = 3 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

Found in HQC:
SQL query=select * from t042_orderline where ol_o_id = 162 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_orderline where ol_o_id = 162 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 
>>
>>---===== TEST BUILT-IN FUNCTION HQC Cacheability =====-------
>>-- CURRENT_TIMESTAMP - HQC cacheable and NOT parameterized
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (2);

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

--- 0 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (3);

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

--- 0 row(s) selected.
>>prepare xx from select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 3;

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

--- 0 row(s) selected.
>>prepare xx from select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 4;

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (2);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CURRENT_TIMESTAMP ( #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (3);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CURRENT_TIMESTAMP ( #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 3;
HQC key=SELECT CURRENT_TIMESTAMP ( #NP# ) FROM T042_ORDERLINE WHERE OL_D_ID = #NP# ; 

Found in HQC:
SQL query=select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 4;
HQC key=SELECT CURRENT_TIMESTAMP ( #NP# ) FROM T042_ORDERLINE WHERE OL_D_ID = #NP# ; 

HQC backpatch OK:
SQL query=select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 4;
HQC key=SELECT CURRENT_TIMESTAMP ( #NP# ) FROM T042_ORDERLINE WHERE OL_D_ID = #NP# ; 
>>
>>-- CURRENT_TIMESTAMP_RUNNING - HQC cacheable - no params
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 3;

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

--- 0 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 5;

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 3;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING AND OL_D_ID = #NP# ; 

Found in HQC:
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 5;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING AND OL_D_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 5;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING AND OL_D_ID = #NP# ; 
>>
>>-- dayofweek - HQC cacheable and parameterized
>>prepare xx from select dayofweek(timestamp '1973-12-24 12:12:02.00') from t042_ORDERLINE;

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

(EXPR)
------

     2
     2
     2
     2
     2
     2
     2
     2
     2
     2

--- 10 row(s) selected.
>>prepare xx from select dayofweek(timestamp '1975-12-24 12:12:02.00') from t042_ORDERLINE;

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

(EXPR)
------

     4
     4
     4
     4
     4
     4
     4
     4
     4
     4

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select dayofweek(timestamp '1973-12-24 12:12:02.00') from t042_ORDERLINE;
HQC key=SELECT DAYOFWEEK ( TIMESTAMP #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select dayofweek(timestamp '1975-12-24 12:12:02.00') from t042_ORDERLINE;
HQC key=SELECT DAYOFWEEK ( TIMESTAMP #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select dayofweek(timestamp '1975-12-24 12:12:02.00') from t042_ORDERLINE;
HQC key=SELECT DAYOFWEEK ( TIMESTAMP #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- EXTRACT - HQC cacheable and parameterized
>>prepare xx from select EXTRACT (YEAR FROM DATE '2017-09-28') from t042_ORDERLINE;

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

(EXPR)
------

  2017
  2017
  2017
  2017
  2017
  2017
  2017
  2017
  2017
  2017

--- 10 row(s) selected.
>>prepare xx from select EXTRACT (YEAR FROM DATE '1980-09-28') from t042_ORDERLINE;

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

(EXPR)
------

  1980
  1980
  1980
  1980
  1980
  1980
  1980
  1980
  1980
  1980

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select EXTRACT (YEAR FROM DATE '2017-09-28') from t042_ORDERLINE;
HQC key=SELECT EXTRACT ( YEAR FROM DATE #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select EXTRACT (YEAR FROM DATE '1980-09-28') from t042_ORDERLINE;
HQC key=SELECT EXTRACT ( YEAR FROM DATE #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select EXTRACT (YEAR FROM DATE '1980-09-28') from t042_ORDERLINE;
HQC key=SELECT EXTRACT ( YEAR FROM DATE #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- JULIANTIMESTAMP HQC cacheable and parameterized
>>prepare xx from select JULIANTIMESTAMP(DATE'2017-09-28') from t042_ORDERLINE;

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

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

  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000
  212373316800000000

--- 10 row(s) selected.
>>prepare xx from select JULIANTIMESTAMP(DATE'1990-09-28') from t042_ORDERLINE;

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

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

  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000
  211521240000000000

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select JULIANTIMESTAMP(DATE'2017-09-28') from t042_ORDERLINE;
HQC key=SELECT JULIANTIMESTAMP ( DATE #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select JULIANTIMESTAMP(DATE'1990-09-28') from t042_ORDERLINE;
HQC key=SELECT JULIANTIMESTAMP ( DATE #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select JULIANTIMESTAMP(DATE'1990-09-28') from t042_ORDERLINE;
HQC key=SELECT JULIANTIMESTAMP ( DATE #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- LOWER - HQC cacheable and NOT parameterized
>>prepare xx from select LOWER('TEXTA') from t042_ORDERLINE;

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

(EXPR)
------

texta 
texta 
texta 
texta 
texta 
texta 
texta 
texta 
texta 
texta 

--- 10 row(s) selected.
>>prepare xx from select LOWER('TEXTB') from t042_ORDERLINE;

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

(EXPR)
------

textb 
textb 
textb 
textb 
textb 
textb 
textb 
textb 
textb 
textb 

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select LOWER('TEXTA') from t042_ORDERLINE;
HQC key=SELECT LOWER ( #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select LOWER('TEXTB') from t042_ORDERLINE;
HQC key=SELECT LOWER ( #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- UPPER - HQC cacheable and NOT parameterized
>>prepare xx from select UPPER('ol_o_id_1') from t042_ORDERLINE;

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

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

OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1
OL_O_ID_1

--- 10 row(s) selected.
>>prepare xx from select UPPER('ol_o_id_2') from t042_ORDERLINE;

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

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

OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2
OL_O_ID_2

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select UPPER('ol_o_id_1') from t042_ORDERLINE;
HQC key=SELECT UPPER ( #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select UPPER('ol_o_id_2') from t042_ORDERLINE;
HQC key=SELECT UPPER ( #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- TRIM - HQC cacheable and NOT parameterized
>>prepare xx from select TRIM('L' FROM 'LO TE XTA') from t042_ORDERLINE;

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

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

O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 
O TE XTA 

--- 10 row(s) selected.
>>prepare xx from select TRIM('A' FROM 'LO TE XTA') from t042_ORDERLINE;

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

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

LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 
LO TE XT 

--- 10 row(s) selected.
>>prepare xx from select TRIM('  Robert1  ') from t042_ORDERLINE;

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

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

Robert1    
Robert1    
Robert1    
Robert1    
Robert1    
Robert1    
Robert1    
Robert1    
Robert1    
Robert1    

--- 10 row(s) selected.
>>prepare xx from select TRIM('  Robert2  ') from t042_ORDERLINE;

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

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

Robert2    
Robert2    
Robert2    
Robert2    
Robert2    
Robert2    
Robert2    
Robert2    
Robert2    
Robert2    

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select TRIM('L' FROM 'LO TE XTA') from t042_ORDERLINE;
HQC key=SELECT TRIM ( #NP# FROM #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select TRIM('A' FROM 'LO TE XTA') from t042_ORDERLINE;
HQC key=SELECT TRIM ( #NP# FROM #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select TRIM('  Robert1  ') from t042_ORDERLINE;
HQC key=SELECT TRIM ( #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select TRIM('  Robert2  ') from t042_ORDERLINE;
HQC key=SELECT TRIM ( #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- TRANSLATE - HQC cacheable and NOT parameterized
>>Prepare xx from select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;

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

(EXPR)
------

abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   

--- 10 row(s) selected.
>>Prepare xx from select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;

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

(EXPR)
------

abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   
abc   

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;
HQC key=SELECT TRANSLATE ( #NP# USING UCS2TOISO88591 ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;
HQC key=SELECT TRANSLATE ( #NP# USING UCS2TOISO88591 ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;
HQC key=SELECT TRANSLATE ( #NP# USING UCS2TOISO88591 ) FROM T042_ORDERLINE ; 
>>
>>-- CODE_VALUE - HQC cacheable and parameterized
>>prepare xx from select code_value ('aa'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

        97            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
        97            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
        97            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
        97            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
        97            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
        97            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
        97            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
        97            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
        97            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
        97            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>prepare xx from select code_value ('bb'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

        98            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
        98            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
        98            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
        98            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
        98            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
        98            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
        98            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
        98            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
        98            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
        98            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select code_value ('aa'), * from t042_ORDERLINE;
HQC key=SELECT CODE_VALUE ( #NP# ) , * FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select code_value ('bb'), * from t042_ORDERLINE;
HQC key=SELECT CODE_VALUE ( #NP# ) , * FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select code_value ('bb'), * from t042_ORDERLINE;
HQC key=SELECT CODE_VALUE ( #NP# ) , * FROM T042_ORDERLINE ; 
>>
>>-- BETWEEN - HQC cacheable but NOT parameterized
>>prepare xx from select * from t042_ORDERLINE where OL_NUMBER between 2 and 6;

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
          1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
          1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
          2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
          3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
          3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
          9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 8 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_NUMBER between 3 and 5;

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
          1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
          2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
          3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
          9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 5 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_NUMBER between 2 and 6;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_NUMBER BETWEEN #NP# AND #NP# ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_NUMBER between 3 and 5;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_NUMBER BETWEEN #NP# AND #NP# ; 
>>
>>-- LIKE - HQC cacheable and only parameterize first arg
>>prepare xx from select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQoq%';

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw

--- 1 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQ%';

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw

--- 1 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQoq%';
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DIST_INFO LIKE #NP# ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQ%';
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DIST_INFO LIKE #NP# ; 
>>
>>-- IN - HQC cacheable and NOT parameterized
>>prepare xx from select * from t042_ORDERLINE  where OL_I_ID in (18000, 19000, 20000);

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

--- 0 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE  where OL_I_ID in (19500, 21000);

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE  where OL_I_ID in (18000, 19000, 20000);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_I_ID IN ( #NP# , #NP# , #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE  where OL_I_ID in (19500, 21000);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_I_ID IN ( #NP# , #NP# ) ; 
>>
>>-- CONCAT - HQC cacheable and NOT parameterized
>>prepare xx from select * from t042_ORDERLINE where 'DoIQoq' = concat(OL_DIST_INFO, 'abc');

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

--- 0 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 'xyzq' = concat(OL_DIST_INFO, 'bc');

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

--- 0 row(s) selected.
>>prepare xx from select concat('a', 'b') from t042_ORDERLINE;

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

(EXPR)
------

ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    

--- 10 row(s) selected.
>>prepare xx from select concat('c', 'd') from t042_ORDERLINE;

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

(EXPR)
------

cd    
cd    
cd    
cd    
cd    
cd    
cd    
cd    
cd    
cd    

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 'DoIQoq' = concat(OL_DIST_INFO, 'abc');
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = CONCAT ( OL_DIST_INFO , #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 'xyzq' = concat(OL_DIST_INFO, 'bc');
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = CONCAT ( OL_DIST_INFO , #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select concat('a', 'b') from t042_ORDERLINE;
HQC key=SELECT CONCAT ( #NP# , #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select concat('c', 'd') from t042_ORDERLINE;
HQC key=SELECT CONCAT ( #NP# , #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- CONVERTTOHEX - HQC cacheable and NOT parameterized
>>prepare xx from select converttohex ('a'), * from t042_ORDERLINE;

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

(EXPR)  OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

61                1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
61                1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
61                1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
61                1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
61                2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
61                2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
61                2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
61                3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
61                3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
61                9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>prepare xx from select converttohex ('b'), * from t042_ORDERLINE;

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

(EXPR)  OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

62                1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
62                1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
62                1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
62                1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
62                2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
62                2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
62                2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
62                3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
62                3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
62                9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>
>>
>>log;

HQC::AddEntry(): passed
SQL query=select converttohex ('a'), * from t042_ORDERLINE;
HQC key=SELECT CONVERTTOHEX ( #NP# ) , * FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select converttohex ('b'), * from t042_ORDERLINE;
HQC key=SELECT CONVERTTOHEX ( #NP# ) , * FROM T042_ORDERLINE ; 
>>
>>-- CHAR_LENGTH - HQC cacheable and parameterized
>>prepare xx from select char_length ('a'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

         1            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
         1            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
         1            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
         1            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
         1            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
         1            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
         1            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
         1            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
         1            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
         1            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>prepare xx from select char_length ('b'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

         1            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
         1            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
         1            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
         1            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
         1            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
         1            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
         1            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
         1            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
         1            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
         1            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select char_length ('a'), * from t042_ORDERLINE;
HQC key=SELECT CHAR_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select char_length ('b'), * from t042_ORDERLINE;
HQC key=SELECT CHAR_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select char_length ('b'), * from t042_ORDERLINE;
HQC key=SELECT CHAR_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 
>>
>>-- OCTET_LENGTH - HQC cacheable and parameterized
>>prepare xx from select octet_length ('a'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

         1            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
         1            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
         1            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
         1            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
         1            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
         1            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
         1            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
         1            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
         1            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
         1            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>prepare xx from select octet_length ('b'), * from t042_ORDERLINE;

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

(EXPR)      OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
----------  -----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

         1            1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
         1            1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
         1            1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
         1            1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO
         1            2           10            3            1        57108               3  2002-06-25 12:06:46.000000            5           .00  MnEboHclGwXFSXp{pqeDLtbo
         1            2           10            3            5        93745               3  2009-02-03 15:02:17.000000            5           .00  JqdrpThOBHRwqbMEJIVXGmKu
         1            2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw
         1            3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
         1            3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh
         1            9           10            3            4        44466               3  1979-10-17 07:10:53.000000            5           .00  LsDxFlXHBuSBTzPkLZTMjRVD

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select octet_length ('a'), * from t042_ORDERLINE;
HQC key=SELECT OCTET_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select octet_length ('b'), * from t042_ORDERLINE;
HQC key=SELECT OCTET_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select octet_length ('b'), * from t042_ORDERLINE;
HQC key=SELECT OCTET_LENGTH ( #NP# ) , * FROM T042_ORDERLINE ; 
>>
>>-- POSITION - HQC cacheable and parameterized
>>prepare xx from select position('oIQo' in OL_DIST_INFO ) from t042_ORDERLINE;

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

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

         0
         0
         0
         0
         0
         0
         2
         0
         0
         0

--- 10 row(s) selected.
>>prepare xx from select position('xyzoIQo' in OL_DIST_INFO ) from t042_ORDERLINE;

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

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

         0
         0
         0
         0
         0
         0
         0
         0
         0
         0

--- 10 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 2 = position('oIQo' in OL_DIST_INFO );

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw

--- 1 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 9 = position('xyzoIQo' in OL_DIST_INFO );

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select position('oIQo' in OL_DIST_INFO ) from t042_ORDERLINE;
HQC key=SELECT POSITION ( #NP# IN OL_DIST_INFO ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select position('xyzoIQo' in OL_DIST_INFO ) from t042_ORDERLINE;
HQC key=SELECT POSITION ( #NP# IN OL_DIST_INFO ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 2 = position('oIQo' in OL_DIST_INFO );
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = POSITION ( #NP# IN OL_DIST_INFO ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 9 = position('xyzoIQo' in OL_DIST_INFO );
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = POSITION ( #NP# IN OL_DIST_INFO ) ; 
>>
>>
>>-- SUBSTRING - HQC cacheable ONLY first arg is parameterized
>>prepare xx from select substring('aaaa'from 1 for 2) from t042_ORDERLINE;

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

(EXPR)
------

aa    
aa    
aa    
aa    
aa    
aa    
aa    
aa    
aa    
aa    

--- 10 row(s) selected.
>>prepare xx from select substring('abba'from 1 for 2) from t042_ORDERLINE;

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

(EXPR)
------

ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    
ab    

--- 10 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 6);

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          2           10            3            6        68201               3  1973-12-24 12:12:02.000000            5           .00  DoIQoqGSfRX{UDPwXhLRwkSw

--- 1 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 5);

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

--- 0 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where 'DoIQoq' = substring('DoIQoqabc' from 1 for 5);

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

--- 0 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select substring('aaaa'from 1 for 2) from t042_ORDERLINE;
HQC key=SELECT SUBSTRING ( #NP# FROM #NP# FOR #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select substring('abba'from 1 for 2) from t042_ORDERLINE;
HQC key=SELECT SUBSTRING ( #NP# FROM #NP# FOR #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select substring('abba'from 1 for 2) from t042_ORDERLINE;
HQC key=SELECT SUBSTRING ( #NP# FROM #NP# FOR #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 6);
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = SUBSTRING ( OL_DIST_INFO FROM #NP# FOR #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 5);
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = SUBSTRING ( OL_DIST_INFO FROM #NP# FOR #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where 'DoIQoq' = substring('DoIQoqabc' from 1 for 5);
HQC key=SELECT * FROM T042_ORDERLINE WHERE #NP# = SUBSTRING ( #NP# FROM #NP# FOR #NP# ) ; 
>>
>>-- CASE/IfThenElse - HQC cacheable and parameterized
>>prepare xx from select case when OL_O_ID <> 5 THEN 1 else 0 end from t042_ORDERLINE;

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

(EXPR)
------

     1
     1
     1
     1
     1
     1
     1
     1
     1
     1

--- 10 row(s) selected.
>>prepare xx from select case when OL_O_ID <> 6 THEN 2 else 1 end from t042_ORDERLINE;

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

(EXPR)
------

     2
     2
     2
     2
     2
     2
     2
     2
     2
     2

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select case when OL_O_ID <> 5 THEN 1 else 0 end from t042_ORDERLINE;
HQC key=SELECT CASE WHEN OL_O_ID <> #NP# THEN #NP# ELSE #NP# END FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select case when OL_O_ID <> 6 THEN 2 else 1 end from t042_ORDERLINE;
HQC key=SELECT CASE WHEN OL_O_ID <> #NP# THEN #NP# ELSE #NP# END FROM T042_ORDERLINE ; 
>>
>>-- CAST - HQC cacheable and parameterize
>>prepare xx from select cast('aaa' as char(20)) from t042_ORDERLINE;

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

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

aaa                 
aaa                 
aaa                 
aaa                 
aaa                 
aaa                 
aaa                 
aaa                 
aaa                 
aaa                 

--- 10 row(s) selected.
>>prepare xx from select cast('bbb' as char(30)) from t042_ORDERLINE;

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

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

bbb                           
bbb                           
bbb                           
bbb                           
bbb                           
bbb                           
bbb                           
bbb                           
bbb                           
bbb                           

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select cast('aaa' as char(20)) from t042_ORDERLINE;
HQC key=SELECT CAST ( #NP# AS CHAR ( #NP# ) ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select cast('bbb' as char(30)) from t042_ORDERLINE;
HQC key=SELECT CAST ( #NP# AS CHAR ( #NP# ) ) FROM T042_ORDERLINE ; 
>>
>>-- bitOperator HQC cacheable and parameterized
>>prepare xx from select (1 | 0 )& (1 ^ 0) from t042_ORDERLINE;

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

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

          1
          1
          1
          1
          1
          1
          1
          1
          1
          1

--- 10 row(s) selected.
>>prepare xx from select (2 | 1 )& (3 ^ 1) from t042_ORDERLINE;

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

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

          2
          2
          2
          2
          2
          2
          2
          2
          2
          2

--- 10 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select (1 | 0 )& (1 ^ 0) from t042_ORDERLINE;
HQC key=SELECT ( #NP# | #NP# ) & ( #NP# ^ #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select (2 | 1 )& (3 ^ 1) from t042_ORDERLINE;
HQC key=SELECT ( #NP# | #NP# ) & ( #NP# ^ #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select (2 | 1 )& (3 ^ 1) from t042_ORDERLINE;
HQC key=SELECT ( #NP# | #NP# ) & ( #NP# ^ #NP# ) FROM T042_ORDERLINE ; 
>>
>>-- MOD - HQC cacheable and parameterized
>>prepare xx from select mod(4,3) from t042_ORDERLINE;

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

(EXPR)
------

     1
     1
     1
     1
     1
     1
     1
     1
     1
     1

--- 10 row(s) selected.
>>prepare xx from select mod(8,5) from t042_ORDERLINE;

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

(EXPR)
------

     3
     3
     3
     3
     3
     3
     3
     3
     3
     3

--- 10 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_O_ID = mod(4,3);

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          1           10            3            1        38994               3  2027-11-26 18:11:34.000000            5           .00  chuwgjxIpnypxU[YfcxPhUBF
          1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl
          1           10            3            3        75288               3  2001-06-20 01:06:58.000000            5           .00  ejOFCzrytcrSqNOrOjZ{{yLH
          1           10            3            4        17362               3  2026-05-25 20:05:09.000000            5           .00  Yr[mwMsXouLnDgQH{MIXdsyO

--- 4 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_O_ID = mod(8,5);

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH
          3           10            3            3        44744               3  2027-10-15 09:10:27.000000            5           .00  {{rts[zOMuPPrWNmIQdy[eBh

--- 2 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select mod(4,3) from t042_ORDERLINE;
HQC key=SELECT MOD ( #NP# , #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select mod(8,5) from t042_ORDERLINE;
HQC key=SELECT MOD ( #NP# , #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select mod(8,5) from t042_ORDERLINE;
HQC key=SELECT MOD ( #NP# , #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_O_ID = mod(4,3);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = MOD ( #NP# , #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_O_ID = mod(8,5);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = MOD ( #NP# , #NP# ) ; 
>>
>>-- MATH FUNC - HQC cacheable and parameterized
>>prepare xx from select bitand(1,2), bitor(0,1), bitxor(0,0),bitnot(0), abs(-1) from t042_ORDERLINE;

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

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

          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1
          0            1            0      -1       1

--- 10 row(s) selected.
>>prepare xx from select bitand(4,1), bitor(1,0), bitxor(1,1),bitnot(1), abs(-2) from t042_ORDERLINE;

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

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

          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2
          0            1            0      -2       2

--- 10 row(s) selected.
>>
>>log;

Not HQC Cacheable but added to SQC:
SQL query=select bitand(1,2), bitor(0,1), bitxor(0,0),bitnot(0), abs(-1) from t042_ORDERLINE;
HQC key=SELECT BITAND ( #NP# , #NP# ) , BITOR ( #NP# , #NP# ) , BITXOR ( #NP# , #NP# ) , BITNOT ( #NP# ) , ABS ( - #NP# ) FROM T042_ORDERLINE ; 

Not in HQC but in SQC:
SQL query=select bitand(4,1), bitor(1,0), bitxor(1,1),bitnot(1), abs(-2) from t042_ORDERLINE;
>>
>>-- CONVERTTIMESTAMP - HQC cacheable and parameterized
>>prepare xx from select CONVERTTIMESTAMP(212664316335000000) from t042_ORDERLINE;

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

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

2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000
2026-12-18 01:12:15.000000

--- 10 row(s) selected.
>>prepare xx from select CONVERTTIMESTAMP(212842400938000000) from t042_ORDERLINE;

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

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

2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000
2032-08-09 05:08:58.000000

--- 10 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212664316335000000);

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          1           10            3            2        16909               3  2026-12-18 01:12:15.000000            5           .00  nkuftmCZosGnTOEDeeHniXPl

--- 1 row(s) selected.
>>prepare xx from select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212842400938000000);

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

OL_O_ID      OL_D_ID      OL_W_ID      OL_NUMBER    OL_I_ID      OL_SUPPLY_W_ID  OL_DELIVERY_D               OL_QUANTITY  OL_AMOUNT     OL_DIST_INFO
-----------  -----------  -----------  -----------  -----------  --------------  --------------------------  -----------  ------------  ------------------------

          3           10            3            2        92861               3  2032-08-09 05:08:58.000000            5           .00  MC{juFoKVMnIYqecIEiMl[HH

--- 1 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select CONVERTTIMESTAMP(212664316335000000) from t042_ORDERLINE;
HQC key=SELECT CONVERTTIMESTAMP ( #NP# ) FROM T042_ORDERLINE ; 

Found in HQC:
SQL query=select CONVERTTIMESTAMP(212842400938000000) from t042_ORDERLINE;
HQC key=SELECT CONVERTTIMESTAMP ( #NP# ) FROM T042_ORDERLINE ; 

HQC backpatch OK:
SQL query=select CONVERTTIMESTAMP(212842400938000000) from t042_ORDERLINE;
HQC key=SELECT CONVERTTIMESTAMP ( #NP# ) FROM T042_ORDERLINE ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212664316335000000);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CONVERTTIMESTAMP ( #NP# ) ; 

HQC::AddEntry(): passed
SQL query=select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212842400938000000);
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_DELIVERY_D = CONVERTTIMESTAMP ( #NP# ) ; 
>>
>>-- LaunchPad bug: 1408148
>>select '0123456789' from t042_t1;

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

0123456789
0123456789

--- 2 row(s) selected.
>>select '' from t042_t1;

(EXPR)
------

      
      

--- 2 row(s) selected.
>>-- Non-parameterized literals should be compared case sensitively.
>>-- query 1: should return row1
>>select b from t042_t1 where a like 'BOO_%';

B         
----------

row1      

--- 1 row(s) selected.
>>-- query 2: should return row2
>>select b from t042_t1 where a like 'boo_%';

B         
----------

row2      

--- 1 row(s) selected.
>>-- query 3: should return row1
>>select b from t042_t1 where a like 'BOO%';

B         
----------

row1      

--- 1 row(s) selected.
>>-- query 4: should return row2
>>select b from t042_t1 where a like 'boo%';

B         
----------

row2      

--- 1 row(s) selected.
>>-- query 5: should return row2
>>select * from t042_t1 where a = 'book' or b = 'ROW1';

A           B         
----------  ----------

book        row2      

--- 1 row(s) selected.
>>-- query 6: should return zero 
>>select * from t042_t1 where a = 'Book' or b = 'ROW2';

--- 0 row(s) selected.
>>-- query 7: should return row1
>>select * from t042_t1 where a = 'BOOK' or b = 'ROW2';

A           B         
----------  ----------

BOOK        row1      

--- 1 row(s) selected.
>>
>>-- LaunchPad bug: 1408485
>>select t.varchar0_uniq as t_varchar0_uniq
+>, t.char2_2 as t_char2_2
+>, t.char3_4 as t_char3_4
+>, u.varchar0_uniq as u_varchar0_uniq
+>, u.char2_2 as u_char2_2
+>from t042_BTA1P006 t, t042_BTA1P006 u
+>where (t.char2_2, 'AA', t.char3_4 ,'CJAAAAAC')
+>=('AA' ,u.char2_2 , 'AAAAAAAA' , u.varchar0_uniq)
+>order by 1, 2
+>;

T_VARCHAR0_UNIQ  T_CHAR2_2  T_CHAR3_4  U_VARCHAR0_UNIQ  U_CHAR2_2
---------------  ---------  ---------  ---------------  ---------

AEAAJAAB         AA         AAAAAAAA   CJAAAAAC         AA       
CCAAFAAC         AA         AAAAAAAA   CJAAAAAC         AA       

--- 2 row(s) selected.
>>
>>log;

HQC::AddEntry(): passed
SQL query=select '0123456789' from t042_t1;
HQC key=SELECT #NP# FROM T042_T1 ; 

HQC::AddEntry(): passed
SQL query=select '' from t042_t1;
HQC key=SELECT #NP# FROM T042_T1 ; 

HQC::AddEntry(): passed
SQL query=select b from t042_t1 where a like 'BOO_%';
HQC key=SELECT B FROM T042_T1 WHERE A LIKE #NP# ; 

HQC::AddEntry(): passed
SQL query=select b from t042_t1 where a like 'boo_%';
HQC key=SELECT B FROM T042_T1 WHERE A LIKE #NP# ; 

HQC::AddEntry(): passed
SQL query=select b from t042_t1 where a like 'BOO%';
HQC key=SELECT B FROM T042_T1 WHERE A LIKE #NP# ; 

HQC::AddEntry(): passed
SQL query=select b from t042_t1 where a like 'boo%';
HQC key=SELECT B FROM T042_T1 WHERE A LIKE #NP# ; 

HQC::AddEntry(): passed
SQL query=select * from t042_t1 where a = 'book' or b = 'ROW1';
HQC key=SELECT * FROM T042_T1 WHERE A = #NP# OR B = #NP# ; 

HQC::AddEntry(): passed
SQL query=select * from t042_t1 where a = 'Book' or b = 'ROW2';
HQC key=SELECT * FROM T042_T1 WHERE A = #NP# OR B = #NP# ; 

HQC::AddEntry(): passed
SQL query=select * from t042_t1 where a = 'BOOK' or b = 'ROW2';
HQC key=SELECT * FROM T042_T1 WHERE A = #NP# OR B = #NP# ; 

Not HQC Cacheable but added to SQC:
SQL query=select t.varchar0_uniq as t_varchar0_uniq , t.char2_2 as t_char2_2 , t.char3_4 as t_char3_4 , u.varchar0_uniq as u_varchar0_uniq , u.char2_2 as u_char2_2 from t042_BTA1P006 t, t042_BTA1P006 u where (t.char2_2, 'AA', t.char3_4 ,'CJAAAAAC') =('AA' ,u.char2_2 , 'AAAAAAAA' , u.varchar0_uniq) order by 1, 2 ;
HQC key=SELECT T . VARCHAR0_UNIQ AS T_VARCHAR0_UNIQ , T . CHAR2_2 AS T_CHAR2_2 , T . CHAR3_4 AS T_CHAR3_4 , U . VARCHAR0_UNIQ AS U_VARCHAR0_UNIQ , U . CHAR2_2 AS U_CHAR2_2 FROM T042_BTA1P006 T , T042_BTA1P006 U WHERE ( T . CHAR2_2 , #NP# , T . CHAR3_4 , #NP# ) = ( #NP# , U . CHAR2_2 , #NP# , U . VARCHAR0_UNIQ ) ORDER BY #NP# , #NP# ; 
>>
>>-- test compile time
>>
>>sh more /proc/loadavg  | cut -d' ' -f 1-3 | sed -e 's/^/System load: /' >> LOG042;
System load: 0.37 0.36 0.74
>>sh grep "model name" /proc/cpuinfo | head -1 | cut -d '@' -f 2 | sed -e 's/^/CPU frequency: /' >> LOG042;
CPU frequency:  2.40GHz
>>set statistics on;
>>prepare xx from select * from t042_orderline where ol_o_id = 1 ;

--- SQL command prepared.

Start Time             2017/01/31 17:26:48.806392
End Time               2017/01/31 17:26:48.808005
Elapsed Time                      00:00:00.001613
Compile Time                      00:00:00.001613
Execution Time                    00:00:00.000000


--- SQL operation complete.
>>explain options 'f' xx;

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

Found in HQC:
SQL query=select * from t042_orderline where ol_o_id = 1 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_orderline where ol_o_id = 1 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 
>>
>>
>>
>>prepare xx from select * from t042_orderline where ol_o_id = 2 ;

--- SQL command prepared.

Start Time             2017/01/31 17:26:48.970491
End Time               2017/01/31 17:26:48.972007
Elapsed Time                      00:00:00.001516
Compile Time                      00:00:00.001516
Execution Time                    00:00:00.000000


--- SQL operation complete.
>>explain options 'f' xx;

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

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  T042_ORDERLINE        4.00E+000

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

Found in HQC:
SQL query=select * from t042_orderline where ol_o_id = 2 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 

HQC backpatch OK:
SQL query=select * from t042_orderline where ol_o_id = 2 ;
HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; 
>>
>>set statistics off;
>>
>>--Stats in Query Cache
>>select num_entries, text_entries, num_plans from table(querycache('user', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

         65            10          65

--- 1 row(s) selected.
>>select num_entries, text_entries, num_plans from table(querycache('meta', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

         31             0          31
          2             0           2

--- 2 row(s) selected.
>>select num_entries, text_entries, num_plans from table(querycache('ustats', 'local'));

--- 0 row(s) selected.
>>select num_entries, text_entries, num_plans from table(querycache('all', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

         65            10          65
         31             0          31
          2             0           2

--- 3 row(s) selected.
>>
>>-- if you find a failure in this test, compare the actual log file and the expected
>>-- file which list the queries involved
>>select num_hits, num_params, '====QUERY:', cast(substring(text, 1, 200) as char(200 bytes) character set utf8)
+>  from table(querycacheentries('user', 'local')) order by 1,2,4;

NUM_HITS    NUM_PARAMS  (EXPR)      (EXPR)
----------  ----------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         0           0  ====QUERY:  select '' from t042_t1;                                                                                                                                                                                 
         0           0  ====QUERY:  select '' from t042_t1;                                                                                                                                                                                 
         0           0  ====QUERY:  select '0123456789' from t042_t1;                                                                                                                                                                       
         0           0  ====QUERY:  select * from t042_BTA1P006;                                                                                                                                                                            
         0           0  ====QUERY:  select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQ%';                                                                                                                                          
         0           0  ====QUERY:  select * from t042_ORDERLINE  where OL_DIST_INFO like 'DoIQoq%';                                                                                                                                        
         0           0  ====QUERY:  select * from t042_ORDERLINE  where OL_I_ID in (18000, 19000, 20000);                                                                                                                                   
         0           0  ====QUERY:  select * from t042_ORDERLINE  where OL_I_ID in (19500, 21000);                                                                                                                                          
         0           0  ====QUERY:  select * from t042_ORDERLINE where 'DoIQoq' = concat(OL_DIST_INFO, 'abc');                                                                                                                              
         0           0  ====QUERY:  select * from t042_ORDERLINE where 'DoIQoq' = substring('DoIQoqabc' from 1 for 5);                                                                                                                      
         0           0  ====QUERY:  select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 5);                                                                                                                     
         0           0  ====QUERY:  select * from t042_ORDERLINE where 'DoIQoq' = substring(OL_DIST_INFO from 1 for 6);                                                                                                                     
         0           0  ====QUERY:  select * from t042_ORDERLINE where 'xyzq' = concat(OL_DIST_INFO, 'bc');                                                                                                                                 
         0           0  ====QUERY:  select * from t042_ORDERLINE where 2 = position('oIQo' in OL_DIST_INFO );                                                                                                                               
         0           0  ====QUERY:  select * from t042_ORDERLINE where 9 = position('xyzoIQo' in OL_DIST_INFO );                                                                                                                            
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212664316335000000);                                                                                                                
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_DELIVERY_D = CONVERTTIMESTAMP(212842400938000000);                                                                                                                
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (2);                                                                                                                               
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP (3);                                                                                                                               
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_NUMBER between 2 and 6;                                                                                                                                           
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_NUMBER between 3 and 5;                                                                                                                                           
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_O_ID = mod(4,3);                                                                                                                                                  
         0           0  ====QUERY:  select * from t042_ORDERLINE where OL_O_ID = mod(8,5);                                                                                                                                                  
         0           0  ====QUERY:  select * from t042_orderline;                                                                                                                                                                           
         0           0  ====QUERY:  select * from t042_t1 where a = 'BOOK' or b = 'ROW2';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1 where a = 'BOOK' or b = 'ROW2';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1 where a = 'Book' or b = 'ROW2';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1 where a = 'Book' or b = 'ROW2';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1 where a = 'book' or b = 'ROW1';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1 where a = 'book' or b = 'ROW1';                                                                                                                                                   
         0           0  ====QUERY:  select * from t042_t1;                                                                                                                                                                                  
         0           0  ====QUERY:  select LOWER('TEXTA') from t042_ORDERLINE;                                                                                                                                                              
         0           0  ====QUERY:  select LOWER('TEXTB') from t042_ORDERLINE;                                                                                                                                                              
         0           0  ====QUERY:  select TRIM('  Robert1  ') from t042_ORDERLINE;                                                                                                                                                         
         0           0  ====QUERY:  select TRIM('  Robert2  ') from t042_ORDERLINE;                                                                                                                                                         
         0           0  ====QUERY:  select TRIM('A' FROM 'LO TE XTA') from t042_ORDERLINE;                                                                                                                                                  
         0           0  ====QUERY:  select TRIM('L' FROM 'LO TE XTA') from t042_ORDERLINE;                                                                                                                                                  
         0           0  ====QUERY:  select UPPER('ol_o_id_1') from t042_ORDERLINE;                                                                                                                                                          
         0           0  ====QUERY:  select UPPER('ol_o_id_2') from t042_ORDERLINE;                                                                                                                                                          
         0           0  ====QUERY:  select b from t042_t1 where a like 'BOO%';                                                                                                                                                              
         0           0  ====QUERY:  select b from t042_t1 where a like 'BOO%';                                                                                                                                                              
         0           0  ====QUERY:  select b from t042_t1 where a like 'BOO_%';                                                                                                                                                             
         0           0  ====QUERY:  select b from t042_t1 where a like 'BOO_%';                                                                                                                                                             
         0           0  ====QUERY:  select b from t042_t1 where a like 'boo%';                                                                                                                                                              
         0           0  ====QUERY:  select b from t042_t1 where a like 'boo%';                                                                                                                                                              
         0           0  ====QUERY:  select b from t042_t1 where a like 'boo_%';                                                                                                                                                             
         0           0  ====QUERY:  select b from t042_t1 where a like 'boo_%';                                                                                                                                                             
         0           0  ====QUERY:  select case when OL_O_ID <> 5 THEN 1 else 0 end from t042_ORDERLINE;                                                                                                                                    
         0           0  ====QUERY:  select case when OL_O_ID <> 6 THEN 2 else 1 end from t042_ORDERLINE;                                                                                                                                    
         0           0  ====QUERY:  select concat('a', 'b') from t042_ORDERLINE;                                                                                                                                                            
         0           0  ====QUERY:  select concat('c', 'd') from t042_ORDERLINE;                                                                                                                                                            
         0           0  ====QUERY:  select converttohex ('a'), * from t042_ORDERLINE;                                                                                                                                                       
         0           0  ====QUERY:  select converttohex ('b'), * from t042_ORDERLINE;                                                                                                                                                       
         0           0  ====QUERY:  select t.varchar0_uniq as t_varchar0_uniq , t.char2_2 as t_char2_2 , t.char3_4 as t_char3_4 , u.varchar0_uniq as u_varchar0_uniq , u.char2_2 as u_char2_2 from t042_BTA1P006 t, t042_BTA1P006 u where (t
         0           1  ====QUERY:  select '0123456789' from t042_t1;                                                                                                                                                                       
         0           1  ====QUERY:  select cast('aaa' as char(20)) from t042_ORDERLINE;                                                                                                                                                     
         0           1  ====QUERY:  select cast('bbb' as char(30)) from t042_ORDERLINE;                                                                                                                                                     
         0           1  ====QUERY:  select position('oIQo' in OL_DIST_INFO ) from t042_ORDERLINE;                                                                                                                                           
         0           1  ====QUERY:  select position('xyzoIQo' in OL_DIST_INFO ) from t042_ORDERLINE;                                                                                                                                        
         0           3  ====QUERY:  select t.varchar0_uniq as t_varchar0_uniq , t.char2_2 as t_char2_2 , t.char3_4 as t_char3_4 , u.varchar0_uniq as u_varchar0_uniq , u.char2_2 as u_char2_2 from t042_BTA1P006 t, t042_BTA1P006 u where (t
         1           0  ====QUERY:  select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from t042_ORDERLINE;                                                                                                                              
         1           1  ====QUERY:  select * from t042_ORDERLINE where OL_DELIVERY_D = CURRENT_TIMESTAMP_RUNNING and OL_D_ID = 3;                                                                                                           
         1           1  ====QUERY:  select CONVERTTIMESTAMP(212664316335000000) from t042_ORDERLINE;                                                                                                                                        
         1           1  ====QUERY:  select CURRENT_TIMESTAMP (2) from t042_ORDERLINE where OL_D_ID = 3;                                                                                                                                     
         1           1  ====QUERY:  select EXTRACT (YEAR FROM DATE '2017-09-28') from t042_ORDERLINE;                                                                                                                                       
         1           1  ====QUERY:  select JULIANTIMESTAMP(DATE'2017-09-28') from t042_ORDERLINE;                                                                                                                                           
         1           1  ====QUERY:  select char_length ('a'), * from t042_ORDERLINE;                                                                                                                                                        
         1           1  ====QUERY:  select code_value ('aa'), * from t042_ORDERLINE;                                                                                                                                                        
         1           1  ====QUERY:  select dayofweek(timestamp '1973-12-24 12:12:02.00') from t042_ORDERLINE;                                                                                                                               
         1           1  ====QUERY:  select octet_length ('a'), * from t042_ORDERLINE;                                                                                                                                                       
         1           1  ====QUERY:  select substring('aaaa'from 1 for 2) from t042_ORDERLINE;                                                                                                                                               
         1           2  ====QUERY:  select mod(4,3) from t042_ORDERLINE;                                                                                                                                                                    
         1           4  ====QUERY:  select (1 | 0 )& (1 ^ 0) from t042_ORDERLINE;                                                                                                                                                           
         1           9  ====QUERY:  select bitand(1,2), bitor(0,1), bitxor(0,0),bitnot(0), abs(-1) from t042_ORDERLINE;                                                                                                                     
         5           1  ====QUERY:  select * from t042_orderline where ol_o_id = 1 ;                                                                                                                                                        

--- 75 row(s) selected.
>>select num_hits, num_params, '====QUERY:', cast(substring(text, 1, 200) as char(200 bytes) character set utf8)
+>  from table(querycacheentries('meta', 'local')) order by 1,2,4;

NUM_HITS    NUM_PARAMS  (EXPR)      (EXPR)
----------  ----------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         0           1  ====QUERY:  SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN (
         0           1  ====QUERY:  SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN (
         0           1  ====QUERY:  SELECT OBJECT_UID, OBJECT_NAME, OBJECT_TYPE, GRANTEE_ID, GRANTEE_NAME, GRANTEE_TYPE, GRANTOR_ID, GRANTOR_NAME, GRANTOR_TYPE, PRIVILEGES_BITMAP, GRANTABLE_BITMAP FROM TRAFODION."_PRIVMGR_MD_".OBJECT_PR
         0           1  ====QUERY:  SELECT object_uid,object_name,grantee_id,grantee_name,grantor_id,grantor_name,column_number,privileges_bitmap,grantable_bitmap FROM TRAFODION."_PRIVMGR_MD_".COLUMN_PRIVILEGES where object_uid = 170022
         0           1  ====QUERY:  select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 1700229370398217975 for read committed access                                                             
         0           1  ====QUERY:  select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 1700229370398217975 for read committed access                                                      
         0           1  ====QUERY:  select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob
         0           1  ====QUERY:  select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob
         0           2  ====QUERY:  select O.catalog_name, O.schema_name, O.object_name, I.keytag, I.is_unique, I.is_explicit, I.key_colcount, I.nonkey_colcount, T.num_salt_partns, T.row_format, I.index_uid from TRAFODION."_MD_".INDEXES
         0           2  ====QUERY:  select column_name, column_number, column_class, fs_data_type, column_size, column_precision, column_scale, datetime_start_field, datetime_end_field, trim(is_upshifted), column_flags, nullable, trim(c
         0           2  ====QUERY:  select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398218011 for read committed access order by keyseq_number 
         0           2  ====QUERY:  select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398222005 for read committed access order by keyseq_number 
         0           3  ====QUERY:  select O.object_name, C.constraint_type, C.col_count, C.constraint_uid, C.enforced, C.flags from TRAFODION."_MD_".OBJECTS O, TRAFODION."_MD_".TABLE_CONSTRAINTS C where O.catalog_name = 'TRAFODION' and
         0           3  ====QUERY:  select column_name, column_number, keyseq_number, ordering, cast(0 as int not null)  from TRAFODION."_MD_".KEYS where object_uid = 1700229370398217975 and nonkeycol = 0 for read committed access order
         0           4  ====QUERY:  select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'OBJECTS'  and object_type = 'BT';       
         0           4  ====QUERY:  select object_uid, object_type from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'DESCRIBE__'  and object_type = '  '                     
         0           5  ====QUERY:  select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_ORDERLINE'  and object_type =
         1           1  ====QUERY:  SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN (
         1           1  ====QUERY:  SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN (
         1           1  ====QUERY:  SELECT OBJECT_UID, OBJECT_NAME, OBJECT_TYPE, GRANTEE_ID, GRANTEE_NAME, GRANTEE_TYPE, GRANTOR_ID, GRANTOR_NAME, GRANTOR_TYPE, PRIVILEGES_BITMAP, GRANTABLE_BITMAP FROM TRAFODION."_PRIVMGR_MD_".OBJECT_PR
         1           1  ====QUERY:  SELECT object_uid,object_name,grantee_id,grantee_name,grantor_id,grantor_name,column_number,privileges_bitmap,grantable_bitmap FROM TRAFODION."_PRIVMGR_MD_".COLUMN_PRIVILEGES where object_uid = 170022
         1           1  ====QUERY:  select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 1700229370398220983 for read committed access                                                             
         1           1  ====QUERY:  select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 1700229370398220983 for read committed access                                                      
         1           2  ====QUERY:  select O.catalog_name, O.schema_name, O.object_name, I.keytag, I.is_unique, I.is_explicit, I.key_colcount, I.nonkey_colcount, T.num_salt_partns, T.row_format, I.index_uid from TRAFODION."_MD_".INDEXES
         1           2  ====QUERY:  select column_name, column_number, column_class, fs_data_type, column_size, column_precision, column_scale, datetime_start_field, datetime_end_field, trim(is_upshifted), column_flags, nullable, trim(c
         1           3  ====QUERY:  select O.object_name, C.constraint_type, C.col_count, C.constraint_uid, C.enforced, C.flags from TRAFODION."_MD_".OBJECTS O, TRAFODION."_MD_".TABLE_CONSTRAINTS C where O.catalog_name = 'TRAFODION' and
         1           3  ====QUERY:  select column_name, column_number, keyseq_number, ordering, cast(0 as int not null)  from TRAFODION."_MD_".KEYS where object_uid = 1700229370398220983 and nonkeycol = 0 for read committed access order
         1           5  ====QUERY:  select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_T1'  and object_type = 'BT'  
         2           1  ====QUERY:  SELECT HISTOGRAM_ID, COLUMN_NUMBER, COLCOUNT, INTERVAL_COUNT, ROWCOUNT, TOTAL_UEC, JULIANTIMESTAMP(STATS_TIME), LOW_VALUE, HIGH_VALUE, JULIANTIMESTAMP(READ_TIME), READ_COUNT, SAMPLE_SECS, COL_SECS, SA
         2           1  ====QUERY:  SELECT HISTOGRAM_ID, INTERVAL_NUMBER, INTERVAL_ROWCOUNT, INTERVAL_UEC, INTERVAL_BOUNDARY, CAST(STD_DEV_OF_FREQ AS DOUBLE PRECISION), V1, V2, V5 FROM TRAFODION.ORDERENTRY.SB_HISTOGRAM_INTERVALS WHERE T
         2           3  ====QUERY:  select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 1700229370398217975 and text_type = 2 and sub_id = 0 for read committed access order by seq_num                             
         3           3  ====QUERY:  select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 1700229370398220983 and text_type = 2 and sub_id = 0 for read committed access order by seq_num                             
        12           4  ====QUERY:  select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'TABLES'  and object_type = 'BT';        

--- 33 row(s) selected.
>>select num_hits, num_params, '====QUERY:', cast(substring(text, 1, 200) as char(200 bytes) character set utf8)
+>  from table(querycacheentries('ustats', 'local')) order by 1,2,4;

--- 0 row(s) selected.
>>select count(*) from table(querycacheentries('all', 'local'));

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

                 108

--- 1 row(s) selected.
>>-- should be the sum of the user, meta and ustats caches
>>
>>
>>--Stats in Hybrid Query Cache
>>select * from table(hybridquerycache('user', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

        37          60                       5                     211

--- 1 row(s) selected.
>>select * from table(hybridquerycache('meta', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

        29          31                       5                     211
         2           2                       5                     211

--- 2 row(s) selected.
>>select * from table(hybridquerycache('ustats', 'local'));

--- 0 row(s) selected.
>>select * from table(hybridquerycache('all', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

        37          60                       5                     211
        29          31                       5                     211
         2           2                       5                     211

--- 3 row(s) selected.
>>
>>select num_hits, num_PLiterals,  num_NPLiterals from table(hybridquerycacheentries('user', 'local')) order by 1, 2, 3;

NUM_HITS    NUM_PLITERALS  NUM_NPLITERALS
----------  -------------  --------------

         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               1
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               2
         0              0               3
         0              0               3
         0              0               3
         0              0               3
         0              0               3
         0              0               4
         0              1               0
         0              1               0
         0              1               0
         0              1               1
         0              1               1
         1              0               1
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1               1
         1              1               2
         1              2               0
         1              4               0
         5              1               0

--- 60 row(s) selected.
>>select num_hits, num_PLiterals,  num_NPLiterals from table(hybridquerycacheentries('meta', 'local')) order by 1, 2, 3;

NUM_HITS    NUM_PLITERALS  NUM_NPLITERALS
----------  -------------  --------------

         0              1               0
         0              1               0
         0              1               0
         0              1               0
         0              1              10
         0              1              10
         0              1              14
         0              1              14
         0              2               1
         0              2               1
         0              2               1
         0              2               3
         0              3               1
         0              3               1
         0              3               1
         0              4               0
         0              4               0
         0              4               0
         0              5               0
         0              5               0
         1              1               0
         1              1               0
         1              1               0
         1              1               0
         1              1              10
         1              1              10
         1              2               1
         1              2               3
         1              3               1
         2              1               0
         2              1               1
         2              3               0
         3              3               0

--- 33 row(s) selected.
>>select num_hits, num_PLiterals,  num_NPLiterals from table(hybridquerycacheentries('ustats', 'local')) order by 1, 2, 3;

--- 0 row(s) selected.
>>select count(*) from table(hybridquerycacheentries('all', 'local'));

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

                  93

--- 1 row(s) selected.
>>-- should be the sum of the user, meta and ustats caches
>>
>>-- virtual table ISP queries are not cacheable.
>>-- hqc.log should be empty.
>>sh cat hqc.log >> LOG042;
>>
>>log;
>>
>>-- clear all (hybrid)query cache entries
>>delete all from table(querycache('all', 'local'));

--- 0 row(s) deleted.
>>select num_entries, text_entries, num_plans from table(querycache('all', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

          0             0           0
          0             0           0
          0             0           0

--- 3 row(s) selected.
>>select * from table(hybridquerycache('all', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

         0           0                       5                     211
         0           0                       5                     211
         0           0                       5                     211

--- 3 row(s) selected.
>>
>>-- LP 1409830
>>insert into t042_t1 values ('trans1', 'xxx');

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values ('trans2', 'xxx');

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement.

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

>>select * from t042_t1;

A           B         
----------  ----------

trans1      xxx       
BOOK        row1      
book        row2      

--- 3 row(s) selected.
>>
>>-- LP 1409863 
>>SELECT POSITION('April spring time' IN 'April rain') from t042_t1;

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

         0
         0
         0

--- 3 row(s) selected.
>>SELECT POSITION('' IN 'April rain') from t042_t1;

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

         1
         1
         1

--- 3 row(s) selected.
>>
>>
>>select num_entries, text_entries, num_plans from table(querycache('all', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

          4             4           4
          0             0           0
          0             0           0

--- 3 row(s) selected.
>>select * from table(hybridquerycache('all', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

         2           3                       5                     211
         0           0                       5                     211
         0           0                       5                     211

--- 3 row(s) selected.
>>
>>-- clear all (hybrid)query cache entries
>>cqd query_cache '0';

--- SQL operation complete.
>>select num_entries, text_entries, num_plans from table(querycache('all', 'local'));

NUM_ENTRIES  TEXT_ENTRIES  NUM_PLANS 
-----------  ------------  ----------

          0             0           0
          0             0           0
          0             0           0

--- 3 row(s) selected.
>>select * from table(hybridquerycache('all', 'local'));

NUM_HKEYS   NUM_SKEYS   NUM_MAX_VALUES_PER_KEY  NUM_HASH_TABLE_BUCKETS
----------  ----------  ----------------------  ----------------------

         0           0                       5                     211
         0           0                       5                     211
         0           0                       5                     211

--- 3 row(s) selected.
>>
>>--LP 1421374
>>select * from p9tab order by 1;

C1                         C2                         C3                         C4               C5
-------------------------  -------------------------  -------------------------  ---------------  -------------------------

 2.00000000000000000E+000   3.33333000000000000E+002   4.00000000000000000E+004   4.0000000E-001   1.00000000000000000E+002
 1.00000000000000000E+001   1.00000000000000000E+002   1.00000000000000000E+003   1.0000000E+004   1.00000000000000000E+005
 2.10000000000000000E+001   2.10000000000000000E+002   2.10000000000000000E+003   2.1000000E+004   2.10000000000000000E+005
 5.50000000000000000E+001   5.50000000000000000E+002   5.50000000000000000E+003   5.5000000E+004   5.50000000000000064E+005
 6.10000000000000000E+001   5.20000000000000000E+001   4.30000000000000000E+001   4.1234569E+000   5.12345678900999936E+000
 8.00000000000000000E+001   8.00000000000000000E+002   8.00000000000000000E+003   8.0000000E+004   8.00000000000000000E+005
 1.01000000000000000E+002   1.02000000000000000E+002   1.03000000000000000E+002   1.4000000E+001   1.50000000000000000E+001
 6.00000000000000000E+002   5.20000000000000000E+001   4.30000000000000000E+001   4.1234564E+000   6.09320000000000000E+000
                        ?                          ?                          ?                ?   0.00000000000000000E+000

--- 9 row(s) selected.
>>select c4 from p9tab where c1 = 61E0;

C4             
---------------

 4.1234569E+000

--- 1 row(s) selected.
>>
>>log off;
