| >> |
| >> |
| >>obey TEST017(setup); |
| >>-------------- |
| >>create table T017TTB ( a int not null primary key, b int, c int); |
| |
| --- SQL operation complete. |
| >> |
| >>create table T017TTA ( a int , b int, c int); |
| |
| --- SQL operation complete. |
| >> |
| >>create table T017TTC ( a int not null primary key, b int, c int) SALT using 2 partitions on (a); |
| |
| --- SQL operation complete. |
| >> |
| >>create table T017TTD ( a int not null not droppable, b int, c int) store by (a); |
| |
| --- SQL operation complete. |
| >> |
| >>create table T017TTE ( a int , b int, c int); |
| |
| --- SQL operation complete. |
| >> |
| >>create table "T017_Delimit" ( a int not null primary key, b int, c int); |
| |
| --- SQL operation complete. |
| >> |
| >>load with no output, no recovery into T017TTA |
| +> select |
| +> 0 + (10 * x10) + x1, |
| +> 1000 + (10 * x10) + x1, |
| +> 10000 + (10 * x10) + x1 |
| +> from (values(1)) as starter |
| +> transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| +> transpose 0,1,2,3,4,5,6,7,8,9 as x1 ; |
| |
| --- 100 row(s) loaded. |
| >> |
| >> |
| >>create table a5table1 |
| +> ( ref_num largeint |
| +> , z_text char(3) |
| +> , emp_num smallint |
| +> ) no partition |
| +> ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into a5table1 values |
| +> (100,'abc',99), (100,'abd',99), (100,'abe',99), (100,'abc',100), |
| +> (100,'abd',100), (100,'abe',100), (100,'abc',101), (100,'abd',101), |
| +> (100,'abe',101), (100,'abb',null), (100,'abc',null), (100,'abd',null), |
| +> (100,'abe',null), (100,null,99), (100,null,100), (100,null,101), |
| +> (200,'abc',200), (200,'abd',200), (200,'abe',200); |
| |
| --- 19 row(s) inserted. |
| >> |
| >> |
| >> |
| >> |
| >>obey TEST017(tests_simple); |
| >>---------------------- |
| >>create index T017TTBIDXb on T017TTB(b) no populate; |
| |
| --- SQL operation complete. |
| >>create unique index T017TTBIDXc on T017TTB(c) no populate; |
| |
| --- SQL operation complete. |
| >> |
| >>create index T017TTCIDXb on T017TTC(b) no populate; |
| |
| --- SQL operation complete. |
| >>create unique index T017TTCIDXc on T017TTC(c) no populate; |
| |
| --- SQL operation complete. |
| >> |
| >>create index T017TTDIDXb on T017TTD(b) no populate; |
| |
| --- SQL operation complete. |
| >>create unique index T017TTDIDXc on T017TTD(c) no populate; |
| |
| --- SQL operation complete. |
| >> |
| >>create index T017TTEIDXb on T017TTE(b) no populate; |
| |
| --- SQL operation complete. |
| >> |
| >>execute indexinfo using 'T017TTB'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTBIDXB IX N Y |
| T017TTBIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTC'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTCIDXB IX N Y |
| T017TTCIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTD'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTDIDXB IX N Y |
| T017TTDIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTE'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTEIDXB IX N Y |
| |
| --- 1 row(s) selected. |
| >> |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(index_table T017TTBIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTBIDXc); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTCIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTCIDXc); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>alter table T017TTB enable all indexes; |
| |
| --- SQL operation complete. |
| >>alter table T017TTC enable all indexes; |
| |
| --- SQL operation complete. |
| >>alter table T017TTD enable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>execute indexinfo using 'T017TTB'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTBIDXB IX Y Y |
| T017TTBIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTC'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTCIDXB IX Y Y |
| T017TTCIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTD'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTDIDXB IX Y Y |
| T017TTDIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >> |
| >>alter table T017TTB disable all indexes; |
| |
| --- SQL operation complete. |
| >>alter table T017TTC disable all indexes; |
| |
| --- SQL operation complete. |
| >>alter table T017TTD disable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>execute indexinfo using 'T017TTB'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTBIDXB IX N Y |
| T017TTBIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTC'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTCIDXB IX N Y |
| T017TTCIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >>execute indexinfo using 'T017TTD'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTDIDXB IX N Y |
| T017TTDIDXC IX N Y |
| |
| --- 2 row(s) selected. |
| >> |
| >>load with no output, no recovery into T017TTB select * from T017TTA; |
| |
| --- 100 row(s) loaded. |
| >>load with no output, no recovery into T017TTC select * from T017TTA; |
| |
| --- 100 row(s) loaded. |
| >>load with no output, no recovery into T017TTD select * from T017TTA; |
| |
| --- 100 row(s) loaded. |
| >>load with no output, no recovery into T017TTE select * from T017TTA; |
| |
| --- 100 row(s) loaded. |
| >>--------------------------------------------------- |
| >>populate all indexes on T017TTB; |
| |
| --- SQL operation complete. |
| >>execute indexinfo using 'T017TTB'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTBIDXB IX Y Y |
| T017TTBIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(index_table T017TTBIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTBIDXc); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>populate all indexes on T017TTC; |
| |
| --- SQL operation complete. |
| >>execute indexinfo using 'T017TTC'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTCIDXB IX Y Y |
| T017TTCIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(index_table T017TTCIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTCIDXc); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>--verify contents of index table defined on table with Salt column |
| >>showddl table(index_table T017TTcIDXb); |
| |
| CREATE TABLE TRAFODION.HBASE.T017TTCIDXB |
| ( |
| "B@" INT NO DEFAULT |
| , "_SALT_" INT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY ("B@" ASC, "_SALT_" ASC, A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>select "B@" , I.A ,t.b,t.c ,case when I."_SALT_"<>T."_SALT_" THEN 1 else 0 end from table(index_table T017TTcIDXb) I join T017TTc t on I.A=t.A order by "B@"; |
| |
| B@ A B C (EXPR) |
| ----------- ----------- ----------- ----------- ------ |
| |
| 1000 0 1000 10000 0 |
| 1001 1 1001 10001 0 |
| 1002 2 1002 10002 0 |
| 1003 3 1003 10003 0 |
| 1004 4 1004 10004 0 |
| 1005 5 1005 10005 0 |
| 1006 6 1006 10006 0 |
| 1007 7 1007 10007 0 |
| 1008 8 1008 10008 0 |
| 1009 9 1009 10009 0 |
| 1010 10 1010 10010 0 |
| 1011 11 1011 10011 0 |
| 1012 12 1012 10012 0 |
| 1013 13 1013 10013 0 |
| 1014 14 1014 10014 0 |
| 1015 15 1015 10015 0 |
| 1016 16 1016 10016 0 |
| 1017 17 1017 10017 0 |
| 1018 18 1018 10018 0 |
| 1019 19 1019 10019 0 |
| 1020 20 1020 10020 0 |
| 1021 21 1021 10021 0 |
| 1022 22 1022 10022 0 |
| 1023 23 1023 10023 0 |
| 1024 24 1024 10024 0 |
| 1025 25 1025 10025 0 |
| 1026 26 1026 10026 0 |
| 1027 27 1027 10027 0 |
| 1028 28 1028 10028 0 |
| 1029 29 1029 10029 0 |
| 1030 30 1030 10030 0 |
| 1031 31 1031 10031 0 |
| 1032 32 1032 10032 0 |
| 1033 33 1033 10033 0 |
| 1034 34 1034 10034 0 |
| 1035 35 1035 10035 0 |
| 1036 36 1036 10036 0 |
| 1037 37 1037 10037 0 |
| 1038 38 1038 10038 0 |
| 1039 39 1039 10039 0 |
| 1040 40 1040 10040 0 |
| 1041 41 1041 10041 0 |
| 1042 42 1042 10042 0 |
| 1043 43 1043 10043 0 |
| 1044 44 1044 10044 0 |
| 1045 45 1045 10045 0 |
| 1046 46 1046 10046 0 |
| 1047 47 1047 10047 0 |
| 1048 48 1048 10048 0 |
| 1049 49 1049 10049 0 |
| 1050 50 1050 10050 0 |
| 1051 51 1051 10051 0 |
| 1052 52 1052 10052 0 |
| 1053 53 1053 10053 0 |
| 1054 54 1054 10054 0 |
| 1055 55 1055 10055 0 |
| 1056 56 1056 10056 0 |
| 1057 57 1057 10057 0 |
| 1058 58 1058 10058 0 |
| 1059 59 1059 10059 0 |
| 1060 60 1060 10060 0 |
| 1061 61 1061 10061 0 |
| 1062 62 1062 10062 0 |
| 1063 63 1063 10063 0 |
| 1064 64 1064 10064 0 |
| 1065 65 1065 10065 0 |
| 1066 66 1066 10066 0 |
| 1067 67 1067 10067 0 |
| 1068 68 1068 10068 0 |
| 1069 69 1069 10069 0 |
| 1070 70 1070 10070 0 |
| 1071 71 1071 10071 0 |
| 1072 72 1072 10072 0 |
| 1073 73 1073 10073 0 |
| 1074 74 1074 10074 0 |
| 1075 75 1075 10075 0 |
| 1076 76 1076 10076 0 |
| 1077 77 1077 10077 0 |
| 1078 78 1078 10078 0 |
| 1079 79 1079 10079 0 |
| 1080 80 1080 10080 0 |
| 1081 81 1081 10081 0 |
| 1082 82 1082 10082 0 |
| 1083 83 1083 10083 0 |
| 1084 84 1084 10084 0 |
| 1085 85 1085 10085 0 |
| 1086 86 1086 10086 0 |
| 1087 87 1087 10087 0 |
| 1088 88 1088 10088 0 |
| 1089 89 1089 10089 0 |
| 1090 90 1090 10090 0 |
| 1091 91 1091 10091 0 |
| 1092 92 1092 10092 0 |
| 1093 93 1093 10093 0 |
| 1094 94 1094 10094 0 |
| 1095 95 1095 10095 0 |
| 1096 96 1096 10096 0 |
| 1097 97 1097 10097 0 |
| 1098 98 1098 10098 0 |
| 1099 99 1099 10099 0 |
| |
| --- 100 row(s) selected. |
| >> |
| >>populate all indexes on T017TTD; |
| |
| --- SQL operation complete. |
| >>execute indexinfo using 'T017TTD'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTDIDXB IX Y Y |
| T017TTDIDXC IX Y Y |
| |
| --- 2 row(s) selected. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(index_table T017TTDIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table T017TTDIDXc); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>--verify contents of index table defined on table with syskey and store by clause |
| >>showddl table(index_table T017TTdIDXb); |
| |
| CREATE TABLE TRAFODION.HBASE.T017TTDIDXB |
| ( |
| "B@" INT NO DEFAULT |
| , A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY ("B@" ASC, A ASC, SYSKEY ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>select "B@" , I.A ,t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end from table(index_table T017TTdIDXb) I join T017TTd t on I.A=t.A order by "B@"; |
| |
| B@ A B C (EXPR) |
| ----------- ----------- ----------- ----------- ------ |
| |
| 1000 0 1000 10000 0 |
| 1001 1 1001 10001 0 |
| 1002 2 1002 10002 0 |
| 1003 3 1003 10003 0 |
| 1004 4 1004 10004 0 |
| 1005 5 1005 10005 0 |
| 1006 6 1006 10006 0 |
| 1007 7 1007 10007 0 |
| 1008 8 1008 10008 0 |
| 1009 9 1009 10009 0 |
| 1010 10 1010 10010 0 |
| 1011 11 1011 10011 0 |
| 1012 12 1012 10012 0 |
| 1013 13 1013 10013 0 |
| 1014 14 1014 10014 0 |
| 1015 15 1015 10015 0 |
| 1016 16 1016 10016 0 |
| 1017 17 1017 10017 0 |
| 1018 18 1018 10018 0 |
| 1019 19 1019 10019 0 |
| 1020 20 1020 10020 0 |
| 1021 21 1021 10021 0 |
| 1022 22 1022 10022 0 |
| 1023 23 1023 10023 0 |
| 1024 24 1024 10024 0 |
| 1025 25 1025 10025 0 |
| 1026 26 1026 10026 0 |
| 1027 27 1027 10027 0 |
| 1028 28 1028 10028 0 |
| 1029 29 1029 10029 0 |
| 1030 30 1030 10030 0 |
| 1031 31 1031 10031 0 |
| 1032 32 1032 10032 0 |
| 1033 33 1033 10033 0 |
| 1034 34 1034 10034 0 |
| 1035 35 1035 10035 0 |
| 1036 36 1036 10036 0 |
| 1037 37 1037 10037 0 |
| 1038 38 1038 10038 0 |
| 1039 39 1039 10039 0 |
| 1040 40 1040 10040 0 |
| 1041 41 1041 10041 0 |
| 1042 42 1042 10042 0 |
| 1043 43 1043 10043 0 |
| 1044 44 1044 10044 0 |
| 1045 45 1045 10045 0 |
| 1046 46 1046 10046 0 |
| 1047 47 1047 10047 0 |
| 1048 48 1048 10048 0 |
| 1049 49 1049 10049 0 |
| 1050 50 1050 10050 0 |
| 1051 51 1051 10051 0 |
| 1052 52 1052 10052 0 |
| 1053 53 1053 10053 0 |
| 1054 54 1054 10054 0 |
| 1055 55 1055 10055 0 |
| 1056 56 1056 10056 0 |
| 1057 57 1057 10057 0 |
| 1058 58 1058 10058 0 |
| 1059 59 1059 10059 0 |
| 1060 60 1060 10060 0 |
| 1061 61 1061 10061 0 |
| 1062 62 1062 10062 0 |
| 1063 63 1063 10063 0 |
| 1064 64 1064 10064 0 |
| 1065 65 1065 10065 0 |
| 1066 66 1066 10066 0 |
| 1067 67 1067 10067 0 |
| 1068 68 1068 10068 0 |
| 1069 69 1069 10069 0 |
| 1070 70 1070 10070 0 |
| 1071 71 1071 10071 0 |
| 1072 72 1072 10072 0 |
| 1073 73 1073 10073 0 |
| 1074 74 1074 10074 0 |
| 1075 75 1075 10075 0 |
| 1076 76 1076 10076 0 |
| 1077 77 1077 10077 0 |
| 1078 78 1078 10078 0 |
| 1079 79 1079 10079 0 |
| 1080 80 1080 10080 0 |
| 1081 81 1081 10081 0 |
| 1082 82 1082 10082 0 |
| 1083 83 1083 10083 0 |
| 1084 84 1084 10084 0 |
| 1085 85 1085 10085 0 |
| 1086 86 1086 10086 0 |
| 1087 87 1087 10087 0 |
| 1088 88 1088 10088 0 |
| 1089 89 1089 10089 0 |
| 1090 90 1090 10090 0 |
| 1091 91 1091 10091 0 |
| 1092 92 1092 10092 0 |
| 1093 93 1093 10093 0 |
| 1094 94 1094 10094 0 |
| 1095 95 1095 10095 0 |
| 1096 96 1096 10096 0 |
| 1097 97 1097 10097 0 |
| 1098 98 1098 10098 0 |
| 1099 99 1099 10099 0 |
| |
| --- 100 row(s) selected. |
| >> |
| >>populate all indexes on T017TTE; |
| |
| --- SQL operation complete. |
| >>execute indexinfo using 'T017TTE'; |
| |
| (EXPR) OBJECT_TYPE INDEX_VALID_DEF TABLE_VALID_DEF |
| -------------------------------------------------------------------------------- ----------- --------------- --------------- |
| |
| T017TTEIDXB IX Y Y |
| |
| --- 1 row(s) selected. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(index_table T017TTEIDXb); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>--verify contents of index table defined on table with syskey |
| >>showddl table(index_table T017TTeIDXb); |
| |
| CREATE TABLE TRAFODION.HBASE.T017TTEIDXB |
| ( |
| "B@" INT NO DEFAULT |
| , SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY ("B@" ASC, SYSKEY ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>select "B@" , t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end from table(index_table T017TTeIDXb) I join T017TTe t on I."B@"=t.B order by "B@"; |
| |
| B@ B C (EXPR) |
| ----------- ----------- ----------- ------ |
| |
| 1000 1000 10000 0 |
| 1001 1001 10001 0 |
| 1002 1002 10002 0 |
| 1003 1003 10003 0 |
| 1004 1004 10004 0 |
| 1005 1005 10005 0 |
| 1006 1006 10006 0 |
| 1007 1007 10007 0 |
| 1008 1008 10008 0 |
| 1009 1009 10009 0 |
| 1010 1010 10010 0 |
| 1011 1011 10011 0 |
| 1012 1012 10012 0 |
| 1013 1013 10013 0 |
| 1014 1014 10014 0 |
| 1015 1015 10015 0 |
| 1016 1016 10016 0 |
| 1017 1017 10017 0 |
| 1018 1018 10018 0 |
| 1019 1019 10019 0 |
| 1020 1020 10020 0 |
| 1021 1021 10021 0 |
| 1022 1022 10022 0 |
| 1023 1023 10023 0 |
| 1024 1024 10024 0 |
| 1025 1025 10025 0 |
| 1026 1026 10026 0 |
| 1027 1027 10027 0 |
| 1028 1028 10028 0 |
| 1029 1029 10029 0 |
| 1030 1030 10030 0 |
| 1031 1031 10031 0 |
| 1032 1032 10032 0 |
| 1033 1033 10033 0 |
| 1034 1034 10034 0 |
| 1035 1035 10035 0 |
| 1036 1036 10036 0 |
| 1037 1037 10037 0 |
| 1038 1038 10038 0 |
| 1039 1039 10039 0 |
| 1040 1040 10040 0 |
| 1041 1041 10041 0 |
| 1042 1042 10042 0 |
| 1043 1043 10043 0 |
| 1044 1044 10044 0 |
| 1045 1045 10045 0 |
| 1046 1046 10046 0 |
| 1047 1047 10047 0 |
| 1048 1048 10048 0 |
| 1049 1049 10049 0 |
| 1050 1050 10050 0 |
| 1051 1051 10051 0 |
| 1052 1052 10052 0 |
| 1053 1053 10053 0 |
| 1054 1054 10054 0 |
| 1055 1055 10055 0 |
| 1056 1056 10056 0 |
| 1057 1057 10057 0 |
| 1058 1058 10058 0 |
| 1059 1059 10059 0 |
| 1060 1060 10060 0 |
| 1061 1061 10061 0 |
| 1062 1062 10062 0 |
| 1063 1063 10063 0 |
| 1064 1064 10064 0 |
| 1065 1065 10065 0 |
| 1066 1066 10066 0 |
| 1067 1067 10067 0 |
| 1068 1068 10068 0 |
| 1069 1069 10069 0 |
| 1070 1070 10070 0 |
| 1071 1071 10071 0 |
| 1072 1072 10072 0 |
| 1073 1073 10073 0 |
| 1074 1074 10074 0 |
| 1075 1075 10075 0 |
| 1076 1076 10076 0 |
| 1077 1077 10077 0 |
| 1078 1078 10078 0 |
| 1079 1079 10079 0 |
| 1080 1080 10080 0 |
| 1081 1081 10081 0 |
| 1082 1082 10082 0 |
| 1083 1083 10083 0 |
| 1084 1084 10084 0 |
| 1085 1085 10085 0 |
| 1086 1086 10086 0 |
| 1087 1087 10087 0 |
| 1088 1088 10088 0 |
| 1089 1089 10089 0 |
| 1090 1090 10090 0 |
| 1091 1091 10091 0 |
| 1092 1092 10092 0 |
| 1093 1093 10093 0 |
| 1094 1094 10094 0 |
| 1095 1095 10095 0 |
| 1096 1096 10096 0 |
| 1097 1097 10097 0 |
| 1098 1098 10098 0 |
| 1099 1099 10099 0 |
| |
| --- 100 row(s) selected. |
| >> |
| >>--case where no indexes |
| >>drop index T017TTBIDXb; |
| |
| --- SQL operation complete. |
| >>drop index T017TTBIDXc; |
| |
| --- SQL operation complete. |
| >>drop table T017TTB; |
| |
| --- SQL operation complete. |
| >>create table T017TTB ( a int not null primary key, b int, c int); |
| |
| --- SQL operation complete. |
| >> |
| >>load with no output, no recovery into T017TTB select * from T017TTA; |
| |
| --- 100 row(s) loaded. |
| >> |
| >>populate all indexes on T017TTB; |
| |
| --- SQL operation complete. |
| >>alter table T017TTB enable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>--shoud give error |
| >>update T017TTB set c = 1 ; |
| |
| --- 100 row(s) updated. |
| >>create unique index T017TTBIDXc on T017TTB(c) no populate; |
| |
| --- SQL operation complete. |
| >>populate all indexes on T017TTB; |
| |
| *** ERROR[8110] Duplicate rows detected. |
| |
| *** ERROR[1053] Unique index TRAFODION.HBASE.T017TTBIDXC could not be created because the specified column(s) contain duplicate data. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>--delimited table name |
| >> |
| >>insert into "T017_Delimit" values (1,1,1),(2,2,2); |
| |
| --- 2 row(s) inserted. |
| >> |
| >>create index idxdelim on "T017_Delimit"(b) no populate; |
| |
| --- SQL operation complete. |
| >>populate all indexes on "T017_Delimit"; |
| |
| --- SQL operation complete. |
| >>------- |
| >> |
| >>--verifying fix for Bug 1359872 |
| >>-- |
| >>select count(*) from a5table1; |
| |
| (EXPR) |
| -------------------- |
| |
| 19 |
| |
| --- 1 row(s) selected. |
| >> create index a5iraea10 on a5table1 (ref_num asc, z_text, emp_num asc) ; |
| |
| --- SQL operation complete. |
| >> |
| >> create index a5iraey10 on a5table1 (ref_num asc, z_text, emp_num desc) ; |
| |
| --- SQL operation complete. |
| >> |
| >>select count(*) from table(index_table a5iraea10); |
| |
| (EXPR) |
| -------------------- |
| |
| 19 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table(index_table a5iraey10); |
| |
| (EXPR) |
| -------------------- |
| |
| 19 |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop index T017TTCIDXb; |
| |
| --- SQL operation complete. |
| >> |
| >>create index T017TTCIDXb on T017TTC (b) HBASE_OPTIONS (DATA_BLOCK_ENCODING = 'FAST_DIFF', COMPRESSION = 'GZ') SALT LIKE TABLE; |
| |
| --- SQL operation complete. |
| >>cqd traf_reload_natable_cache 'ON'; |
| |
| --- SQL operation complete. |
| >>select * from table (index_table T017TTCIDXb) order by "_SALT_@","B@","A"; |
| |
| _SALT_@ B@ A |
| ---------- ----------- ----------- |
| |
| 0 1000 0 |
| 0 1001 1 |
| 0 1002 2 |
| 0 1004 4 |
| 0 1009 9 |
| 0 1010 10 |
| 0 1012 12 |
| 0 1013 13 |
| 0 1014 14 |
| 0 1017 17 |
| 0 1019 19 |
| 0 1023 23 |
| 0 1027 27 |
| 0 1030 30 |
| 0 1034 34 |
| 0 1036 36 |
| 0 1037 37 |
| 0 1041 41 |
| 0 1043 43 |
| 0 1044 44 |
| 0 1045 45 |
| 0 1047 47 |
| 0 1049 49 |
| 0 1050 50 |
| 0 1051 51 |
| 0 1052 52 |
| 0 1053 53 |
| 0 1054 54 |
| 0 1055 55 |
| 0 1056 56 |
| 0 1058 58 |
| 0 1059 59 |
| 0 1061 61 |
| 0 1064 64 |
| 0 1067 67 |
| 0 1069 69 |
| 0 1072 72 |
| 0 1073 73 |
| 0 1076 76 |
| 0 1077 77 |
| 0 1078 78 |
| 0 1079 79 |
| 0 1080 80 |
| 0 1082 82 |
| 0 1084 84 |
| 0 1085 85 |
| 0 1086 86 |
| 0 1087 87 |
| 0 1088 88 |
| 0 1090 90 |
| 0 1093 93 |
| 0 1097 97 |
| 0 1098 98 |
| 0 1099 99 |
| 1 1003 3 |
| 1 1005 5 |
| 1 1006 6 |
| 1 1007 7 |
| 1 1008 8 |
| 1 1011 11 |
| 1 1015 15 |
| 1 1016 16 |
| 1 1018 18 |
| 1 1020 20 |
| 1 1021 21 |
| 1 1022 22 |
| 1 1024 24 |
| 1 1025 25 |
| 1 1026 26 |
| 1 1028 28 |
| 1 1029 29 |
| 1 1031 31 |
| 1 1032 32 |
| 1 1033 33 |
| 1 1035 35 |
| 1 1038 38 |
| 1 1039 39 |
| 1 1040 40 |
| 1 1042 42 |
| 1 1046 46 |
| 1 1048 48 |
| 1 1057 57 |
| 1 1060 60 |
| 1 1062 62 |
| 1 1063 63 |
| 1 1065 65 |
| 1 1066 66 |
| 1 1068 68 |
| 1 1070 70 |
| 1 1071 71 |
| 1 1074 74 |
| 1 1075 75 |
| 1 1081 81 |
| 1 1083 83 |
| 1 1089 89 |
| 1 1091 91 |
| 1 1092 92 |
| 1 1094 94 |
| 1 1095 95 |
| 1 1096 96 |
| |
| --- 100 row(s) selected. |
| >> |
| >>explain options 'f' Load transform into table(index_table T017TTCIDXb ) select "_SALT_","B","A" from T017TTC for read uncommitted access; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+002 |
| 3 . 4 esp_exchange 1:2(range) 1.00E+002 |
| 1 2 3 tuple_flow 1.00E+002 |
| . . 2 trafodion_load_prepa T017TTCIDXB 1.00E+000 |
| . . 1 trafodion_index_scan T017TTCIDXB 1.00E+002 |
| |
| --- SQL operation complete. |
| >> |
| >>explain options 'f' Load transform into table(index_table T017TTCIDXb ) select "_SALT_","B","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+002 |
| 3 . 4 esp_exchange 1:2(range) 1.00E+002 |
| 1 2 3 tuple_flow 1.00E+002 |
| . . 2 trafodion_load_prepa T017TTCIDXB 1.00E+000 |
| . . 1 trafodion_index_scan T017TTCIDXB 1.00E+002 |
| |
| --- SQL operation complete. |
| >>cqd traf_reload_natable_cache reset; |
| |
| --- SQL operation complete. |
| >> |
| >>drop index T017TTCIDXb; |
| |
| --- SQL operation complete. |
| >> |
| >>create index T017TTCIDXb on T017TTC (b); |
| |
| --- SQL operation complete. |
| >>cqd traf_reload_natable_cache 'ON'; |
| |
| --- SQL operation complete. |
| >>select * from table (index_table T017TTCIDXb) order by "B@","_SALT_","A"; |
| |
| B@ _SALT_ A |
| ----------- ---------- ----------- |
| |
| 1000 0 0 |
| 1001 0 1 |
| 1002 0 2 |
| 1003 1 3 |
| 1004 0 4 |
| 1005 1 5 |
| 1006 1 6 |
| 1007 1 7 |
| 1008 1 8 |
| 1009 0 9 |
| 1010 0 10 |
| 1011 1 11 |
| 1012 0 12 |
| 1013 0 13 |
| 1014 0 14 |
| 1015 1 15 |
| 1016 1 16 |
| 1017 0 17 |
| 1018 1 18 |
| 1019 0 19 |
| 1020 1 20 |
| 1021 1 21 |
| 1022 1 22 |
| 1023 0 23 |
| 1024 1 24 |
| 1025 1 25 |
| 1026 1 26 |
| 1027 0 27 |
| 1028 1 28 |
| 1029 1 29 |
| 1030 0 30 |
| 1031 1 31 |
| 1032 1 32 |
| 1033 1 33 |
| 1034 0 34 |
| 1035 1 35 |
| 1036 0 36 |
| 1037 0 37 |
| 1038 1 38 |
| 1039 1 39 |
| 1040 1 40 |
| 1041 0 41 |
| 1042 1 42 |
| 1043 0 43 |
| 1044 0 44 |
| 1045 0 45 |
| 1046 1 46 |
| 1047 0 47 |
| 1048 1 48 |
| 1049 0 49 |
| 1050 0 50 |
| 1051 0 51 |
| 1052 0 52 |
| 1053 0 53 |
| 1054 0 54 |
| 1055 0 55 |
| 1056 0 56 |
| 1057 1 57 |
| 1058 0 58 |
| 1059 0 59 |
| 1060 1 60 |
| 1061 0 61 |
| 1062 1 62 |
| 1063 1 63 |
| 1064 0 64 |
| 1065 1 65 |
| 1066 1 66 |
| 1067 0 67 |
| 1068 1 68 |
| 1069 0 69 |
| 1070 1 70 |
| 1071 1 71 |
| 1072 0 72 |
| 1073 0 73 |
| 1074 1 74 |
| 1075 1 75 |
| 1076 0 76 |
| 1077 0 77 |
| 1078 0 78 |
| 1079 0 79 |
| 1080 0 80 |
| 1081 1 81 |
| 1082 0 82 |
| 1083 1 83 |
| 1084 0 84 |
| 1085 0 85 |
| 1086 0 86 |
| 1087 0 87 |
| 1088 0 88 |
| 1089 1 89 |
| 1090 0 90 |
| 1091 1 91 |
| 1092 1 92 |
| 1093 0 93 |
| 1094 1 94 |
| 1095 1 95 |
| 1096 1 96 |
| 1097 0 97 |
| 1098 0 98 |
| 1099 0 99 |
| |
| --- 100 row(s) selected. |
| >> |
| >>explain options 'f' Load transform into table(index_table T017TTCIDXb ) select "B","_SALT_","A" from T017TTC for read uncommitted access; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 3 . 4 root 1.00E+002 |
| 1 2 3 tuple_flow 1.00E+002 |
| . . 2 trafodion_load_prepa T017TTCIDXB 1.00E+000 |
| . . 1 trafodion_index_scan T017TTCIDXB 1.00E+002 |
| |
| --- SQL operation complete. |
| >> |
| >>explain options 'f' Load transform into table(index_table T017TTCIDXb ) select "B","_SALT_","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 3 . 4 root 1.00E+002 |
| 1 2 3 tuple_flow 1.00E+002 |
| . . 2 trafodion_load_prepa T017TTCIDXB 1.00E+000 |
| . . 1 trafodion_index_scan T017TTCIDXB 1.00E+002 |
| |
| --- SQL operation complete. |
| >>cqd traf_reload_natable_cache reset; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >>log; |
