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

[Dan <dan27649@xxxxxxxxx>]

Eric, you are my hero!  This is *exactly* what I was looking for!

The only thing I had to change was to add "as COUNT" after "count(*)"

One extraordinary side benefit is that submitting this to batch, this query
ran in under 5 seconds.  The interactive query that's been running for over
three hours now seems stuck on, for at least the last 30 minutes:
   Query running. 9 records selected, 44329941 processed.
The interactive job's run attributes shows that the CPU clock is running, up
to 940084 milliseconds now.

I am flabbergasted.  What the he11 is going on with the interactive
query?!?!?!?

BTW, I am using my home-grown EXCSQLSTM command with which to submit the
query.

Thanks again!
Dan

On Tue, Jul 29, 2008 at 12:30 PM, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>wrote:

> One easy way:
>
> CREATE TABLE mylib/test1 as
> ( place your SQL select statement here... )
> with data
>
> This will instruct SQL to create a table that matches the result set
> returned by your SQL select.  The with data clause instructs SQL to save the
> data from your select into the newly created table.
>
> hth,
> Eric DeLong
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
> Sent: Tuesday, July 29, 2008 11:14 AM
> To: Midrange Systems Technical Discussion
> 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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.