Bigsql like predicate error

Question

I am writing sql query for bigsql.

If it looks like this

 select t.city from table t where t.city like 'A%'

It works ok, but next one fails:

 select t.city from table t where t.city like 'A%' escape '\'

I only add escape expression and it give me following error

Error Code: -5199, SQL State: 57067]  DB2 SQL Error: SQLCODE=-5199, SQLSTATE=57067, SQLERRMC=Java DFSIO;1;2, DRIVER=4.15.82

I found this documentation http://www-01.ibm.com/support/knowledgecenter/SSPT3X_2.1.2/com.ibm.swg.im.infosphere.biginsights.bigsql.doc/doc/bsql_like_predicate.html?lang=en So seems escape should work.

If I escape escape character I get another error

 Error Code: -130, SQL State: 22019]  DB2 SQL Error: SQLCODE=-130, SQLSTATE=22019, SQLERRMC=null, DRIVER=4.15.82. 2) [Error Code: -727, SQL State: 56098]  DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-130;22019;, DRIVER=4.15.82

But if I use not '\' character as escape, but something another, like '/' it works fine.

Any ideas why it may happen?

Answer 1

Try this maybe. You might have to escape the escape character.

select t.city from table t where t.city like 'A%' escape '\\'

Answer 2

Based upon this sample:

\connect bigsql
drop table if exists stack.issue1;

create hadoop table if not exists stack.issue1 (
f1 integer,
f2 integer,
f3 varchar(200),
f4 integer
)
stored as parquetfile;

insert into stack.issue1 (f1,f2,f3,f4) values (0,0,'Detroit',0);
insert into stack.issue1 (f1,f2,f3,f4) values (1,1,'Mt. Pleasant',1);
insert into stack.issue1 (f1,f2,f3,f4) values (2,2,'Marysville',2);
insert into stack.issue1 (f1,f2,f3,f4) values (3,3,'St. Clair',3);
insert into stack.issue1 (f1,f2,f3,f4) values (4,4,'Port Huron',4);

select * from stack.issue1;

select * from stack.issue1 where f3 like 'M%';

\quit

I get the following results:

jsqsh --autoconnect --input-file=./t.sql --output-file=t.out
0 rows affected (total: 0.28s)
0 rows affected (total: 0.22s)
1 row affected (total: 0.37s)
1 row affected (total: 0.35s)
1 row affected (total: 0.38s)
1 row affected (total: 0.35s)
1 row affected (total: 0.35s)
5 rows in results(first row: 0.33s; total: 0.33s)
2 rows in results(first row: 0.26s; total: 0.26s)

cat t.out
+----+----+--------------+----+
| F1 | F2 | F3           | F4 |
+----+----+--------------+----+
|  1 |  1 | Mt. Pleasant |  1 |
|  0 |  0 | Detroit      |  0 |
|  4 |  4 | Port Huron   |  4 |
|  3 |  3 | St. Clair    |  3 |
|  2 |  2 | Marysville   |  2 |
+----+----+--------------+----+
+----+----+--------------+----+
| F1 | F2 | F3           | F4 |
+----+----+--------------+----+
|  1 |  1 | Mt. Pleasant |  1 |
|  2 |  2 | Marysville   |  2 |
+----+----+--------------+----+

This shows your syntax is correct, however, based upon the -5199 error code, this is an issue with the FMP processes not having enough memory or an issue with the Hadoop I/O component. You can get further information on this error by issuing db2 ? sql5199n from the command line.

The SQL error message should have directed you to the node where the error occurred and where the Big SQL log file is and the associated reader log files are located.

Answer 3

SQL5199 error generally means an issue with HDFS ( you can do a db2 \? SQL5199 to get details on the message -- as user bigsql ). Check the bigsql and DFS logs to see if that gives any pointers to the problem.

Hope this helps.