Make uvsh SQL output columnar instead of vertical
Moderators: chulett, rschirm, roy
Make uvsh SQL output columnar instead of vertical
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
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
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,
You need to revise your FMT lengths in order to achieve what you want.
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Kim,
JOBTYPE is a supplied "I" type of value - IF JOBTYPEIND = "" THEN 0 ELSE JOBTYPEIND found in the dict of DS_JOBS
JOBTYPE is a supplied "I" type of value - IF JOBTYPEIND = "" THEN 0 ELSE JOBTYPEIND found in the dict of DS_JOBS
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Do a TERM command to see your settings.
then change it with:
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
Code: Select all
term 1000,65,0,0,2,127
Mamu Kim
Success!
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.
>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.
Lyle
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.
You also use sed to strip this off.
Mamu Kim
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.kduke wrote: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.
Anyway I'm set for now. Thanks for everyone's help.
Lyle