RE: Limiting QUSER DASD

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

John, 

I assume queries are eating up temporary storage, so view the following with
that assumption in mind.

There are number of things you could to address this. I'll start with easy
but not necessarily "the correct" way to address it:

1) I believe ODBC jobs run under QPWFSERVER class. You could try changing
its temporary storage limit (MAXTMPSTG) to something other than *NOMAX.
NOTE: this will kill any job that exceeds the limit immediately.
2) Using your idea may work, but I don't believe temporary storage is owned
by QUSER, but rather actual user that job is running under (DSPJOB->option 1
should show you Current Profile used).  I guess I could be wrong, as I don't
know of a bulletproof way to look up temporary storage owners.  Anyway, once
you test it you'll find out if it works or not.  Changing all of your
profiles may be exercise in futility, but I don't know how your environment
is set up.
3) Centerfield has a disk/HUNTER tool that can detect DASD spikes like this
and give you the details on the temporary storage allocations as well,
including who,what,where, when type of info.
This could help pinpoint the problems and possibly provide more information
to IBM in case they have temporary storage leaks that are PTFed, see:

https://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/8
8c105155938650086257045005c7b1e?OpenDocument

4) take some time and implement good indexing strategy for your Crystal
queries.  If you don't provide good SQL indexes over your tables, query
optimizer is left with only one implementation method - table scan.  And
that causes it to get creative with internal data structures for join
queries, order by's, group by's etc.  And when it gets truly creative, funny
things happen :)

Hope that helps.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: Limiting QUSER DASD

We've been having some people using query products that shall remain
*cough* Crystal Reports *cough* nameless.  We've had several instances
of runaway queries causing the associated QZDASOINIT jobs to start
chewing through DASD.  We've tried limiting the user profile who runs
these malformed queries to 50GB disk (there's about 350GB free) so that
the system won't crash but the QZDASOINIT job -- and the temporary
storage allocation for the queries -- runs under QUSER.

So, is there anything that would prohibit us from doing a CHGUSRPRF
USRPRF(QUSER) MAXSTG(50000000) to limit QUSER to, again, about 50GB?
QUSER has less than 10MB allocated right now.

Note that for different reasons we can't limit the query runtimes.