Menu
I'm a Unix hack, so I've got a script I run whenever I want to run some SQL. I call DB2 with my query and dump stdout to a file. Very neanderthal, but it works. I've got a table with a varchar(28672) in it. The field is never that long, but db2 formats it to 28k wide.
So obviously, I change my query to select substr(field, 1,100) to get just the beginning of the field, and that's what it returns, just the first 100 characters, but it still formats the field that it outputs to 28672 characters. Any idea how to make it format to the size of the data output?
572
The LENGTH() function With the IBM DB2 LENGTH function, when using a CHAR column, values are always blank padded, so the function returns the size of the CHAR column. When using a VARCHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.
AFAIK, an SQL statement in DB2 (LUW) can be up to about 2 MiB, so you could in principle have somewhere in the region of 250k terms in the IN clause, allowing 8 characters per term including the comma.
The length of a graphic string is the number of double-byte characters. Unicode UTF-16 data is treated as graphic data; a UTF-16 supplementary character takes two DBCS characters to represent and as such is counted as two DBCS characters.
The CLP for DB2 will always return the maximum amount required for a field in its output based on the length of the column.
However, you can cast your field to another size VARCHAR:
SELECT CAST(your_field AS VARCHAR(100)) FROM your_table
If you do this, you'll probably want to suppress warnings for character truncation, which you can do using the CLP command UPDATE COMMAND OPTIONS:
UPDATE COMMAND OPTIONS USING w OFF
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
