db2

Db2: how to format output?


I don't like the db2 console output

db2 => SELECT city,SUM(sales) as sum from offices group by city;

CITY                                                                                            SUM                              
---------------------------------------------------------------------------------------------------- ---------------------------------
Rome                                                                                                                        14000,
London                                                                                                                         19000,

I would prefer something like this

db2 => SELECT city,SUM(sales) as sum from offices group by city;

CITY                           SUM                              
------------------------
Rome                           14000
London                         19000                              

On Oracle I use

set feedback on;
set linesize 9000
set  colsep |
column column1 format a30
column column2 format a20
....

And I get a nice output How for format the columns on DB2? I'm interested in max size(a30 mean display 30 chars).


Solution

  • I have found a nice workaround using substr of SQL syntax

    Without substr

    select title,year from titles;
    
    TITLE                                                                                                                                                                                                                                                                                                                                                                                       YEAR                  
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------
    Fantasia                                                                                                                                                                                                                                                                                                                                                                                           1940
    

    EEK!

    With using of substr

    select substr(title,1,19) as title, substr(year,1,4) as year from titles;
    
    TITLE                                  YEAR
    -------------------------------------- ----
    FANTASIA                               1940
    

    Good!