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

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

> > 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

Creating a Table is not necessary a table can be filled at creation time
with data from an other table.

Example:
Create Table MySchema/MyTable
    as (select CVCVCD, CVCVLC, CVDEDC, count(*) as Nbr
           from cvgdetl, cusdetl_DE 
           where     CVCUSN = cdCUSN
                 and CVRENN = cdRENN
                 and CVSEQN = cdSEQN
                 and cdstat = 'DE'
       group by CVCVCD, CVCVLC, CVDEDC)
With Data;

... And make sure that there are the following access paths (keyed logical
files or SQL indexes) available:
CVUSN, CVRENN, CVSEQN, CVCVCD, CVCVLC, CVDEDC
CVUSN, CDRENN, CDSEQN, CDSTAT 

I assume your main problem is missed access paths.
For more information about indexing strategies read the following white
paper:
Indexing and statistics strategies for DB2 for i5/OS
http://www-03.ibm.com/servers/enable/site/education/abstracts/indxng_abs.htm
l

Check also the adviced indexes. Beginning with V5R4 all adviced indexes are
stored in the system table SYSIXADV in library QSYS2. You may also use the
iNavigator Database to see the adviced indexes.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

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.