0

I'm trying to query a DB2 and I declare a temporary table like this

declare global temporary table session.results
    (code_statement varchar, count_statement integer) with replace on commit preserve rows not logged;

delete from session.results;

I then insert into the table with multiple statements like this one

insert into session.results(code_statement, count_statement)
    values ('tA full join tB',
        (select count(*) as count_statement from tableA A full join tableB B on A.key = B.key));

The problem is that when I do any of this insertions (no matter if I use full join or any other) and use something >= 21 characters for the varchar value, SQL returns [Error Code: -433, SQL State: 22001] DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC=<text>, DRIVER=3.50.152

I've tested and I'm 100% sure this is the problem. Whenever I insert 21+ characters in that string, it crashes.

I've tried to use varchar(50), varchar(400), char(50) and it does not get solved.

I've also checked docs and googled for hours and haven't found any solution.

Thanks in advance.

UPDATE: I have no idea how but it is now working as expected. It requires to set a size to the varchar, and also accepts bigger strings. The point is that I have no idea what happened and why it's solved. I just kept playing around and split the declare into multiple lines. Also changed "Max Chars" in DbVis (from -1 to 100) but I have set it back and it's still working.

It makes no sense for me. I'll keep the question opened since I don't know what solved the issue.

jjimenezg93
  • 162
  • 4
  • 16
  • Db2 should throw an exception (sqlcode -604) for the declare dgtt, if you don't size the varchar column. At least, that's what Db2 V11.1 on unix does when using the CLP. – mao Sep 12 '17 at 10:39
  • @mao I have updated the question. Indeed it now throws **`-604`** when not setting `varchar`'s size. Any kind of cache, buffering... hidden magic? – jjimenezg93 Sep 12 '17 at 10:51

1 Answers1

0

Db2 requires that you specify an integer value to size the varchar. Otherwise Db2 should throw an sqlcode -604 exception. You need to size the column appropriately, taking into account character encoding if using utf-8. If you have a reproducible, portable, scenario that shows the -433 (value too long) exception when using valid syntax and values then update the question.

mao
  • 11,321
  • 2
  • 13
  • 29
  • As already pointed out, the problem was not only that syntax was incorrect. That's simply because now it complains when I do not set `varchar`'s size, but it was not before. Before, it was not complaining and the error it was complaining about was AFTER the `declare` statement. Besides that, when I commented out the line which was forcing the **`-433`**, the script was running correctly. – jjimenezg93 Sep 12 '17 at 11:13
  • Always useful to compare client-tool (dbvis) behaviour to CLP when unexpected results happen. – mao Sep 12 '17 at 11:22