My success rate to getting those to work is only .5%

what is a magic?

db2 "select rtrim(INDSCHEMA), rtrim(INDNAME), rtrim(COLNAME) from syscat.INDEXCOLUSE"

does not work for me either. It works in concatenations though:

db2 "select rtrim(INDSCHEMA)||‘.’||rtrim(INDNAME)||’ ’||rtrim(COLNAME) from syscat.INDEXCOLUSE"

Cool, that just might work for what I am looking to do with it.

Bella, can you shine a light on it why it works the way it works, and maybe you can ask your IBM friends to get it to work

I assume you problem is the display in the CLP still showing too long. The reason is that the column has a maximum length and that is what DB2 uses to return the column lengths. Doing rtrim has no visual effect. It would work in an application where the strings had trailing blanks.

Andy

Yes Andy,

I am using telnet and it will stay there. I guess it does not have to win a pageant contest

You can shorten up the fields this way, but it may cause some truncation:


db2 "select varchar(INDSCHEMA,30), varchar(INDNAME,30), varchar(COLNAME,30) from syscat.INDEXCOLUSE"


Andy

truncation is the problem I can not afford. I am going with SUBSTR and giving plenty room to make sure that nothing will get cut.

Thank you.

Пума,

The number of dashes in the column heading can't be trimed. CLP has no idea what the next column is going to be like. Since we don't know the length of the next column, we need to consider the maximum length so that the data is column-aligned properly. substr gives CLP a clear indication that the output will have a constant column length so it's able to align the output properly.

If it's a display problem, maybe the following will work?(For me, on Linux, it works both with and without "-x".)

It is a display issue and no, -x did not work for me. Thank you.

Bella,

огромное спасибо

you're welcome, Oлег

The -x doesn't help, of course, because the maximum length of the column doesn't change. And since DB2 tries to come up with a tabular output and because it doesn't know the longest of all values in the result, it has to stick with the maximum length.

Since you cannot tolerate truncation, you cannot use SUBSTR() because that would truncate the values if they are too long - and you wouldn't even get a warning on that.

What I would do in your case is this:
- ignore the issue - why do some spaces bother you?
- use some scripting language like Perl and its connectivity modules for DB2 and fetch the results regularly
- use "sed" or some other tool to remove multiple spaces:
Also note that if you have LOBs in your output, those will be truncated at 4K or 8K (I don't know which). The reason is simply that the DB2 won't be so stupid as trying to put 2GB to the console, which just takes a few hours to write all the dashes and the values - and no one could really do anything with this anyway.

Knut,

I hear what you are saying as far as SUBSTR goes. I just refuse to accept that someone would generate a schema name longer then 15 char and tab or ind name longer then 50.

I was just trying to get the layout in a way that it would be easy to look at it, even so I would be doing further changes to it.

Your method did crossed my mind I was just trying to avoid it for some reason and get db2 to do it all. As it stands right now I will use your method if there is no royalty

Danke.

If you really want a "nice looking" tabular output, but with a minimum of useless spaces, you could use perl or sed to replace the inter-column spaces into TABs and load the resulting tab-separated file into Excel (or OpenOffice of course).
Or maybe even better, convert it to an HTML table and look at it with your favourite browser:

I don't like such very restrictive limitations. I consider self-explaining and well-chosen names extremely important for maintenance and the times where disk space was not available and that's why you had to use short, cryptic identifiers have long passed.

Also, I think you are on very thin ice with your assumption refusal - especially since you also used the word "generate"...