How can I limit the display length of a field returned in DB2 CLP (z/OS)?

Question

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?

Alleras

Answer 1

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

Yockey

You're a frikkin genius. Thanks. – Alleras

awesome! bonus: use SELECT CAST(your_field AS VARCHAR(100)) as your_field FROM your_table to preserve the column name. – Incest