Re: Don't believe SQL debug joblog

On 07-Jan-2015 18:25 -0600, Buck Calabro wrote:

I was running in debug for other reasons and I saw SQL7919 reason
code 5 in the job log. The indicated column in error was CITY. In
my case, it was SELECT ... COALESCE(CITY, ' ') FROM a whopping load
of LEFT JOINs. The ellipsis indicates about 40 columns omitted for
clarity.

SQL7919 rc5 indicates that the incoming data is VARCHAR but
the destination variable is CHAR. There are no VARCHAR
columns in any of the tables in this SQL statement, and none
as the target of the FETCH INTO. After a fruitless search of the
Knowledge Center, I decided to brute force it for the moment and did
SELECT CAST(COALESCE(CITY, ' ') as CHAR(30)). Now CITY was OK, but
STATE was throwing the msg SQL7919 rc5.

Clearly something wasn't right. After some more manual / forum /
email searches, I assumed that the SQL7919 was bogus, gave up and
went for another brute force solution. One by one I started taking
columns out of the cursor and fetch until it ran without error.

It turns out it was a size mis-match - and SQLSTATE was 01004 the
whole time. I should have trusted the SQLSTATE from the beginning,
although there is virtually no hint from the database as to WHICH
columns are being truncated.

Bottom line: Trust SQLSTATE. Don't trust the messages issued in
debug mode.

There was nothing incorrect that I can see about the described scenario. If the concern is for the column named in the message, then realize that the expression\column-list of the SELECT has a column-name only for those expressions in the list that are merely a column-name, and thus for any other expression a column name can only be derived\inferred from just one [and is likely to be the first] column _named in_ the expression exhibiting the issue.

If CITY is CHAR, the reason the expression "COALESCE(CITY, ' ')" is typed by the SQL as VARCHAR is because the literal [what the DB2 for i SQL calls a constant] value of the single blank is implicitly typed as VARCHAR; in effect, specifying the literal is the equivalent of having specified explicitly, the expression VARCHAR(' '). That is documented here:

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzseldesc.htm>
_Data types of result columns_
"...
_When the expression is_: | _Data type of result column_:
...
character-string constant length n | VARCHAR(n)
...
"

For resolution\avoidance of the messaging in debug [or an SQLWARN], either the result of the desired expression could be cast to CHAR [as shown was done to resolve the concern], or the literal itself could be cast to CHAR to ensure that the overall result will be CHAR. Thus an alternate resolution to the one chosen\shown could be one of the following [where CHAR casting scalar function could be replaced with the CAST scalar function]:
SELECT COALESCE(CITY, CHAR(' '))
SELECT COALESCE(CITY, CHAR(' ', 1))
SELECT COALESCE(CITY, CHAR(' ', # /* where #=length(CITY) */))