Completely. Thanks for the explanation.
From:
rob@xxxxxxxxx
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
08/27/2008 11:46 AM
Subject:
QCMDEXC in a sql SELECT was: TAATOOL Pricing and PRTLIBANL
MKirkpatrick,
The sql below creates a UDF, or User Defined Function. It basically
allows you to put QCMDEXC into a select statement. There's a little more
on UDF's at
http://faq.midrange.com/data/cache/185.html
You can retrieve your list of functions by querying the view SYSFUNCS.
Pretend you have a simple CLP that does something like this:
DCLF QTEMP/MBRINFO
DSPFD FILE(*ALL/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MBRINFO)
READ:
RCVF
IF /* check numerous fields to decide whether or not to reorg */
SBMJOB JOB(&MBFILE) CMD(RGZPFM FILE(&MBLIB/&MBFILE) MBR(&MBNAME)
GOTO READ
Now, if you already run numerous sql scripts against QTEMP/MBRINFO to
determine what files you can reorg. Like, perhaps based on the number of
deleted records or some such thing, you can use the following sql to
submit the reorgs based off of that criteria (providing you created the
udf as outlined earlier).
select QCMDEXC('SBMJOB JOB(' CONCAT MBFILE CONCAT ') CMD(RGZPFM FILE('
CONCAT MBLIB CONCAT '/' CONCAT MBFILE CONCAT ') MBR(' CONCAT MBNAME CONCAT
')'),
mbfile, mblib, mbndtr as DeletedCount, mbmxrl as MaxRcdLen,
mbndtr*mbmxrl as DeletedSpace, mbaccp
from qtemp/mbrinfo
where mbfila='*PHY' AND MBNDTR>0 AND MBFILE NLIKE 'QADB%'
order by mbndtr*mbmxrl desc
And you don't have to write little CL programs for one shotters.
Understand?
Rob Berendt
midrange.com
