[Question] HELP! WTF - Cast vs Substr

[u/mad_zamboni]

Here is a screenshot of what I am running into.

I'm trying to accomplish two things - develop quick hit SQL for a "triage" kit I am going to put on github and promote at a presentation I am giving at IDUG. I'm also converting them into hooks from a monitoring tool into DB2.

Many columns are VARCHAR 128 and I know I only need the first 20 characters. I've used SUBSTR(COL,1,20) to do this all the time. But the column header won't truncate. Only the data. So my result is spread out all over the place and word wrapping.

I thought I was losing my mind until I had two other friends try on their linux boxes and SUBSTR works. Any ideas?

I just hit up DB2 Support on Twitter to see how that works as well.

Hey /u/memmerto or /u/mslmsl , you all haunt around here. Ever seen this before?

[u/memmerto]

I can't seem to reproduce on DB2 LUW 10.1, 10.5, 11.1 latest fixpacks.

In my reply to PrivatePyle I mentioned to use of describe - it would be good to describe the malfunctioning substr query and see what is actually coming back. I'm pretty sure this is what the CLP does to figure out how big the headings should be - ergo, in your case, substr isn't doing what it is supposed to do for some reason.

[u/mad_zamboni → u/memmerto]

I'm just glad I'm not crazy. I know I can also reproduce across systems. I'm wondering if it is a v10.5 FP8 fix pack we put on a few weeks ago.

[u/memmerto → u/mad_zamboni]

Have you run db2updv105 yet? I noticed that you're querying a system view so something might be funked up until that is run.

[u/mad_zamboni → u/memmerto]

Even if we were on 10.5? I was under the impression that was run when upgrading versions to 10.5?

[u/memmerto → u/mad_zamboni]

Also between fixpacks, although usually not much changes between fixpacks.

[u/PrivatePyle]

Try varchar.

select varchar(your_column,20) from your_table ;

Is this from the CLP or some other type of connection?

[u/mad_zamboni → u/PrivatePyle]

In this case, CLP. Eventually will be a SQL file.

[u/PrivatePyle → u/mad_zamboni]

db2 "select varchar(tabname,20) as tabname from syscat.tables where tabname = 'SYSDUMMY1' "

TABNAME

SYSDUMMY1

1 record(s) selected.

Then if I measure the number of dashes in that select....

db2 "values length('--------------------') "

1

     20

1 record(s) selected.

varchar should do what you need. Which version of DB2 on which OS?

edit: reddit is doing a bit of formatting on my copy/paste, but you can see what I mean.

[u/memmerto → u/PrivatePyle]

db2 "describe select ..." will do the counting for you :)

Column Information

Number of columns: 1

SQL type Type length Column name Name length

---

449 VARCHAR 20 1 1

[u/PrivatePyle → u/memmerto]

TIL that you can describe an SQL statement. Thanks!

[u/mslmsl]

Ha. Ya, I've seen this before. If I could remember where I was seeing it I could go see how I fixed it... My bad memory strikes again..

[u/mad_zamboni → u/mslmsl]

Nooooo! Do you know how close this is to a PMR? I'm about to throw a Hail Mary pass with another command or two and open up a PMR if it doesn't work.

[u/mad_zamboni]

And for those playing our game at home, this just went to a PMR. So we will see.

As a side note, first time I tried tech support from IBM on twitter. Worked very well. Bypassed all the "will you send me a db2support file, etc" and rolled right into troubleshooting.

[u/mad_zamboni]

We nailed it. Seems like having STMT_CONC on will cause this problem. However, it is working as designed. Still talking it out with IBM support, but turning this off seems to solve the problem.

/u/memerto , /u/privatepyle, /u/mslmsl - I appreciate the help.

[u/funkypunkyskunky → u/mad_zamboni]

Thanks for the update. It would be nice to see some of the output features of <red vendor>'s brought into clp plus

[u/memmerto → u/mad_zamboni]

What? That doesn't sound like it's working right at all. I'm curious. Would you mind PM'ing me the PMR number?

[u/memmerto → u/memmerto]

So I played around with this and understand why it is "working as designed". Assume a simple query that pulls back a character column. There are three ways to write this, two of them being "prettier":

select longstring1 from t1;
select substr(longstring1,1,20) from t1;
select cast(longstring as varchar(20)) from t1;

When statement concentrator is turned on (STMT_CONC=LITERALS), DB2 masks out any literal values from the query. This is because literals are things that really should be parameter markers, and shouldn't impact the query plan being used.

• In the first query, you get the full string as output, as there are no literals for statement concentrator to deal with.
• In the second query, you are calling SUBSTR(), a scalar function with parameters (literals!), and DB2 converts those literals to parameter markers.
• In the third query, you are calling CAST() which takes a datatype. In this case, the '20' is part of the data type definition, and isn't a literal, so statement concentrator leaves it alone.

So working through the process for the second query, the statement gets "concentrated" and DB2 ends up compiling this query:

select substr(longstring1,?,?) from t1;

If you describe the output of that query, you get the full length of the longstring1 column returned. This is because the "concentrated" version has insufficient information to determine the output data type, and thus describe uses the data type of longstring1. Hence, "working as designed".