Re: Take your system down with Cartesian Joins

[CRPence <CRPbottle@xxxxxxxxx>]

 The described scenario, a crash due to storage used up by a TABLE as 
output from a cross join, is best prevented by one or more of:

  - MAXSTG() parameter for the user that owns the output file.
  - SIZE() parameter on the output file.
  - Break handling program on QSYSMSG *MSGQ which will see the storage 
messages
  - System value QSTGLOWACN set to *REGFAC, then QIBM_QWC_QSTGLOWACN 
exit point with format STGL0100, according to sysval QSTGLOWLMT  see:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzakz/rzakzqstglowacn.htm
  Note: in the above system value, perhaps using the *CRITMSG settings 
defined by CHGSRVA to identify those to be notified via message CPI099B, 
to defer action to an operator.

 And for temporary storage being an issue instead, one or more of:

  - temporary storage limit for a job via the class [CHGCLS]
  - tmpstg limit via the query attributes [CHGQRYA QRYSTGLMT()]

  The origin of the issue is the TABLE which enables no limits 
protection, and thus is outside of the query itself.  AFaIK there is no 
control at the SQL statement level nor session level for such an issue, 
except to use the FETCH FIRST x ROWS ONLY clause.  That could be used by 
a utility, such as QM /temporary/ cached results or DB2 Web Query, to 
implement a protection, but the SQL engine itself can not implicitly 
limit a statement [for those who would ask why it does not; e.g. just to 
be sure to /prevent/ a crash].

Regards, Chuck

Lukas Beeler wrote:
> On Tue, Aug 12, 2008 at 6:51 PM,  <elehti@xxxxxxxxxxxxxxxxxx> wrote:
> > How to fill up your disk (system auxiliary storage pool) and take
> > down your system. Create a query  to output to a file. Join two or
> > more tables but neglect to join the tables. This will produce a
> > Cartesian join. http://www.orafaq.com/wiki/Cartesian_join
>
> I don't have much to do with the DBA side of things on the i, but I'm
> sure you can limit the resources that are allowed to be used by a 
> single query, or the storage used by a single user/login.
>
> I know that i can do this kind of stuff in SQL Server.