Search code examples
sqldb2ibm-midrangerpgle

Is there a way to reduce gap between two column headings in DB2

I am working on IBM I series VR7, and running SQL(DB2) using CLLE.

I have a SQL procedure in a TXT file, having below command to create a table in QTEMP.

create table qtemp.FILE1 as (                                                  
select                                                                          
Field1,Field2,Field3,.....Field10 from FILE2 ) with data;

I am calling the above procedure from CLLE using below command.

RUNSQLSTM  SRCFILE(MyLib/MySrc) SRCMBR(Proc_txt) COMMIT(*NONE)

And then running below command to generate the spool.

RUNQRY     QRYFILE((FILE1)) OUTTYPE(*PRINTER) OUTFORM(*DETAIL) FORMSIZE(60 132) 
           FORMTYPE(*STD) COPIES(1) LINESPACE(1)

The issue I am facing is that I am getting 2 white spaces between columns while creating the table using the create table command. When that table is converted into a spool file using above RUNQRY command, the fields on the right side truncates as my report width is 132 by default and I can not change it. If the white spaces in the table created can be reduced to 1, my issue will be resolved. The SQL I am using IBM i Series' default and DB2 as database. I don't have much idea about their version.

Edit2: Another issue I had was of report having a field in second line. Actually as per requirement a field had to be in the second row under another field. For example I needed field10 under field5. I have fixed it too, read my answer below. Hope it helps people in need but I really doubt.

Edit1: I have updated the question as requested. Any help would be much appreciated. Thanks.

Solution

  • I was able to get what I needed. As others have suggested, I have finally used WRKQRY to control the column spacing. Reduced the column spacing to 1 and was able to get the columns needed in the 132 width.

    Another issue I had was of report having a field in second line. Actually as per requirement a field had to be in the second row under another field. For example I needed field10 under field5. So what I did was, I used the Line wrapping feature available in WRKQRY.

    How I did: Create a WRKQRY object and select the file needed.

    1. Sequenced the field I needed in second line, to the bottom.
    2. Go to Select Output Type and Output Form and take Y on Line Wrapping field. Put the wrapping width equal to your report width. Leave other fields as required.
    3. This way each record will have 10th field in next row, if it has data. You can add as many as fields.
    4. You may have to add some white spaces to the field for proper alignment. I would suggest to create a new field and use concat(||) operator available in WRKQRY.

    Thanks everyone for helping.