Make uvsh SQL output columnar instead of vertical

[bashbal]

I'm performing some queries on DS_JOBS and I don't want the VERTICAL output. Here's my SQL

SELECT
NAME fmt '70L',
CATEGORY fmt '70L',
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '55L'
FROM DS_JOBS
WHERE JOBTYPE = '2'
ORDER BY NAME
;

This always produces output like this:

Job name.... BulkSeqDailyVolumeBudgetForecast
Category.... Data Mart - Sales Accounting\Daily Volume Forecast Budget
DEPEND_JOBS. ExtrDailyVolumeForecastBudgetHash
. JCIErrorNotification
. ExtrDailyVolumeForecastBudget
. XfrmDailyVolumeForecastBudget

---------------------------

I would like the format to be by columns. Is there a way to do this?

Thanks,

Lyle

[mhester]

Vertical vs horizontal in this case is because you have formatted your output to be wider than your screen. This will not fit even if you spooled your ouput to a printer which can handle 132 character wide columns.

You need to revise your FMT lengths in order to achieve what you want.

Regards,

[kduke]

Write a job. Use this SQL in a UV stage. Output to a sequential file maybe fixed length.

Please post your JOBTYPE dictionary item. Come to think of it why not have a dictionary item for DEPEND_JOBS.

[mhester]

Kim,

JOBTYPE is a supplied "I" type of value - IF JOBTYPEIND = "" THEN 0 ELSE JOBTYPEIND found in the dict of DS_JOBS

[kduke]

I figured that out after the post. How big can make TERM?

I think Ray posted an answer to this.

[kduke]

Do a TERM command to see your settings.

Code: Select all

TERM
            Terminal Printer

Page width:       80     132
Page depth:       65      66
Page skip :        0
LF delay  :        0
FF delay  :        2
Backspace :      127
xterm

then change it with:

Code: Select all

term 1000,65,0,0,2,127

[bashbal]

My TERM settings were:

>TERM
Terminal Printer

Page width: 80 132
Page depth: 24 66
Page skip : 0
LF delay : 0
FF delay : 2
Backspace : 8
vt220

-----------------
I set them as Kim directed and it WORKS! :D I now have the output I'm looking for. Thank-you very much.

OMT, is there an easy way to eliminate the ^[[H^[[J page breaks? I just set it to 32767 which is large enough for my current query, but I may have larger queries in the future.

The purpose of all of this is to develop a faster way to perform usage analysis. Clicking on each job in Manager just takes to long. My real goal is to find out what jobs I have that are not called by any sequence job. We're trying to do some cleanup of our project.

[kduke]

The control chars are form feeds or clear screen codes for your term type. You can SET.TERM.TYPE to any defined at the UNIX level in terminfo. You can also suppress your column headings and regular headings with COL.HDR.SUPP and HDR.SUPP or something close to that. Do HELP COL. and see what comes up.
You also use sed to strip this off.

[bashbal]

The control chars are form feeds or clear screen codes for your term type. You can SET.TERM.TYPE to any defined at the UNIX level in terminfo. You can also suppress your column headings and regular headings with COL.HDR.SUPP and HDR.SUPP or something close to that. Do HELP COL. and see what comes up.
You also use sed to strip this off.

Thanks for pointing me in the right direction. I eventually found using COUNT.SUP COL.SUP in the SELECT statement removes some of the "garbage" output. The COL.SUP is a synonym for SUPPRESS COLUMN HEADING. The COUNT.SUP suppresses the row count at the end of the output. I tried COL.HDR.SUP(P), but this seemed to have no effect in the SELECT statement.

Anyway I'm set for now. Thanks for everyone's help.