Re: Way to get STRSQL SELECT to run in batch?

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

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.