Dan wrote:
I suppose the way to do this is to create the output table with the fields
specified in the SELECT, then submit to batch a RUNSQLSTM with the same
query, but prefaced with a "INSERT INTO xxxxxxxxxx (" It seems to me that
this would give the same results. Any disagreement there?
That would certainly work.
The problem I have with RUNSQLSTM is it's too hard to introduce
variables. I realize that with an ad-hoc type of query, you may not
think you need them -- but personally, I do a lot of stuff that varies.
For example, I do a lot of quick queries on production data to see how
often a particular code is used. For example, I might do something like
this:
select stype, count(*) from custmas
group by stype order by stype
"stype" is the statement type that customers get -- so I do a query on
that to see how many of the different statement types are in use and how
frequently each one is used. I find that sort of analysis useful in
making design decisions when programming.
Every project I do does a very similar type of query -- but it'll be on
a different field in a different file. Rather than re-write the query
each time, I like to use a variable, like this:
select $COL, count(*) from $TABLE
group by $COL order by $COL
Now all I need to do is supply a value for the $COL variable and a value
for the $TABLE variable, and I don't have to re-write the statement.
So that's my point -- this sort of thing is impossible to do with
RUNSQLSTM.
I've been finding the db2 command in QShell to be a really easy way to
implement this sort of thing. I'll just write a simple QShell script
like this:
[code]
#!/usr/bin/qsh
LIB=$1
TABLE=$2
COL=$3
db2 "select $COL,count(*) from $LIB.$TABLE \
group by $COL, order by $COL" \
> /tmp/report.txt
[/code]
Now I can just run that Qshell script (or submit it to batch, if you like)
SBMJOB CMD(QSH CMD('myscript.sh MYLIB MYTABLE MYCOL'))
Of course, there are many ways to accomplish the same thing. RUNSQLSTM,
RPG, STRSQL, iNav's Run SQL Scripts, QMQRY, REXX, etc. This is just the
one that I find the easiest.
midrange.com
