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

Dan,
Try using the Create table XXXX as ( .... your SELECT statement) WITH DATA.
This will eliminate the need to create the table before each run.
If you are creating a table in a library other than QTEMP, you will want to add WITH REPLACE to the end of the statement.
Hope this helps,
 
Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3
  
 





----- Original Message ----
From: Dan <dan27649@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, July 29, 2008 12:13:59 PM
Subject: Way to get STRSQL SELECT to run in batch?

I do a lot of one-timer SQLs, some for testing before embedding in an
application, some for data interrogation to determine problems.

Some of the SELECT queries I do, run for hours.  Of course, RUNSQLSTM
doesn't support SELECT, otherwise I'd use that to submit to batch.

The one that I have running now is set up to send output to a data file
(F13=Services, 1.Change session attributes, SELECT output: 3=File).

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?

But what a PITA!  First, I have to manually create the output table, then I
have to put the query in a source member for the RUNSQLSTM, and then submit
the RUNSQLSTM.  There's gotta be an easier way.

FWIW, here's the query that's now in its third hour:
    select DISTINCT CVCVCD, CVCVLC, CVDEDC, count(*)
    from cvgdetl, cusdetl_DE
      where CVCUSN = cdCUSN
        and CVRENN = cdRENN
        and CVSEQN = cdSEQN
        and cdstat = 'DE'
      group by CVCVCD, CVCVLC, CVDEDC

Any ideas would be greatly appreciated!

- Dan