0

SET LINES 200

The above statement in oracle increases the screen width from its default size to 200. I want to achieve the same in DB2, ie. its default width is set to 50, how can i increase it?

Even if I spool the output to a file, the rows are terminating at 50th character and continuing in next line. I want all to be in the same line.

SQL> select ' this is the column i dont want to be displayed in multiple lines ' from dual;


this is the column i dont want to be displayed in
multiple lines

Thanks in advance.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
Naveen
  • 1
  • 1
  • 1
    Um, what? There is no such thing as "screen width" in an RDBMS. That's solely a property of a display program (or potentially an outfile of some sort). What was the spooling command? What program are you using to view db records? Most of the ones I've used wouldn't give you an actual new line, they'd make you scroll right/left. – Clockwork-Muse Jul 15 '14 at 12:39
  • bro i mean to say linesize. i used as spool file_name. I am trying to achieve this within shell script. this is specifically in DB2 – Naveen Jul 15 '14 at 12:56
  • $SQL_COMMAND -s /nolog </dev/null 2>&1 CONNECT_TO_SQL whenever sqlerror exit 1; whenever oserror exit 1; set feedback off; set serveroutput on; set heading off; spool my_spool.txt; SELECT KEY||'='||VALUE FROM CONFIGURATIONS; spool off; EOF – Naveen Jul 15 '14 at 12:59
  • above is my db2 commands used in shell script where $SQL_COMMAND=clpplus -nw – Naveen Jul 15 '14 at 13:00
  • Again, from a db standpoint that doesn't exist, that's solely a property of a db viewer or similar program. When a table-viewer talks to the db, it says "Hey I'm interested in table `xyz`, column `abc`, give me the contents", and the db replies back "okay, here's 300 characters of ....". Reporting back as multiple lines would be problematic, as it would appear to be multiple lines in the table (which would imply wrong results). – Clockwork-Muse Jul 15 '14 at 13:02
  • That should be an edit to your question. For the output of your command - is `KEY=` repeated each time? If no, that implies your original data may have a carriage return/line feed in it. Are you sure the db column isn't simply defined as `VARCHAR(50)` (that it's not 50 characters)? – Clockwork-Muse Jul 15 '14 at 13:04
  • @Clockwork-Muse Those kinds of options exists in DB2 CLPPlus, that is the equivalent to SQL*Plus. I agree with your comment about db point of view, but CLPPlus is a reporting tool (you define header, footer, line size, etc.) included in DB2. We, in the DB2 LUW world, do not use that tood, but people that come from Oracle does. Most of use, we use DB2CLP, but not the new CLPPlus. – AngocA Jul 15 '14 at 13:42
  • @Naveen I have worked a little bit with CLPPlus, and I have also found some issues. I advise you to report this kind of wrong behavior in IBM DeveloperWorks forums. – AngocA Jul 15 '14 at 13:45
  • @AngocA CLPPlus (apparently) is optionally installed with DB2 client. Perhaps naming it as part of "DB2" itself confuses new users of a RDBMS since it's a _reporting tool_ rather than an actual part of the _database_ . Tricky distinctions here. Hard to give truly accurate help when definitions blur based on names. – user2338816 Jul 16 '14 at 00:18
  • I ve declared that field as varchar(256) so no point in thinking that the filed declaration might be wrong bro .. – Naveen Jul 17 '14 at 08:35
  • finally what i wanted is to have the data in single row in a spooled file, i modified my spool file which has splitted up lines to single line using shell commands and came up with the solution. anyways thanks for the suggestions and experiences shared with. – Naveen Jul 17 '14 at 08:36

1 Answers1

2

The default column length in cli plus is 50. You can adjust this by setting a column format (see the cli plus manual).

e.g. COLUMN 1 FORMAT A500

jontas
  • 21
  • 2