| >>showstats for table t042_orderline on ol_o_id detail; |
| |
| Detailed Histogram data for Table TRAFODION.ORDERENTRY.T042_ORDERLINE |
| Table ID: 3587850068614533656 |
| |
| Hist ID: 1404295272 |
| 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 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: 2.75 2.02 1.56 |
| >>sh grep "model name" /proc/cpuinfo | head -1 | cut -d '@' -f 2 | sed -e 's/^/CPU frequency: /' >> LOG042; |
| CPU frequency: model name : Intel Core Processor (Haswell) |
| >>set statistics on; |
| >>prepare xx from select * from t042_orderline where ol_o_id = 1 ; |
| |
| --- SQL command prepared. |
| |
| Start Time 2018/08/27 16:40:52.450248 |
| End Time 2018/08/27 16:40:52.453421 |
| Elapsed Time 00:00:00.003173 |
| Compile Time 00:00:00.003173 |
| 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 2018/08/27 16:40:52.648361 |
| End Time 2018/08/27 16:40:52.651112 |
| Elapsed Time 00:00:00.002751 |
| Compile Time 00:00:00.002751 |
| 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 |
| ----------- ------------ ---------- |
| |
| 64 9 64 |
| |
| --- 1 row(s) selected. |
| >>select num_entries, text_entries, num_plans from table(querycache('meta', 'local')); |
| |
| NUM_ENTRIES TEXT_ENTRIES NUM_PLANS |
| ----------- ------------ ---------- |
| |
| 22 0 22 |
| 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 |
| ----------- ------------ ---------- |
| |
| 64 9 64 |
| 22 0 22 |
| 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 '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 ; |
| |
| --- 73 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 is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 3587850068614533656 for read committed access |
| 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 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 3587850068614533656 and nonkeycol = 0 for read committed access order |
| 0 4 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'DESCRIBE__' and obje |
| 0 4 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'OBJECTS' and object_type = |
| 0 5 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_ORDERLINE' and |
| 1 1 ====QUERY: select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 3587850068614538557 for read committed access |
| 1 1 ====QUERY: select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob |
| 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 2 ====QUERY: select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 3587850068614533750 for read committed access order by keyseq_number |
| 1 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 3587850068614538557 and nonkeycol = 0 for read committed access order |
| 1 5 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_T1' and object_ |
| 2 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 GRANTEE_ID IN (1000 |
| 2 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 GRANTEE_ID IN (3333 |
| 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 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 |
| 2 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 = 358785 |
| 2 1 ====QUERY: select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 3587850068614533656 for read committed access |
| 2 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 |
| 2 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 |
| 2 3 ====QUERY: select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 3587850068614533656 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 = 3587850068614538557 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, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'TABLES' and object_type = |
| |
| --- 24 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) |
| -------------------- |
| |
| 97 |
| |
| --- 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 59 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 |
| ---------- ---------- ---------------------- ---------------------- |
| |
| 21 22 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 59 5 211 |
| 21 22 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 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 |
| |
| --- 59 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 2 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 14 |
| 1 2 1 |
| 1 2 1 |
| 1 3 1 |
| 2 1 0 |
| 2 1 0 |
| 2 1 0 |
| 2 1 0 |
| 2 1 1 |
| 2 1 2 |
| 2 1 2 |
| 2 2 3 |
| 2 3 0 |
| 3 3 0 |
| |
| --- 24 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) |
| -------------------- |
| |
| 83 |
| |
| --- 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 |
| ----------- ------------ ---------- |
| |
| 3 3 3 |
| 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 2 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; |
