Malformed query detection

[Jonathan Ball]

We keep getting SQL statements running through the database server jobs
that have cross joins (Cartesian products), i.e. tables with no join
specification. These jobs consume enormous amounts of temporary storage
building their result sets before we detect them and end the jobs. I
know they're doing cross joins because I retrieve the SQL statements via
iSeries Navigator and examine them.

Is there a way to configure one of the database server exit points, e.g.
QIBM_QZDA_SQL1 or _SQL2, to do some kind of quick analysis of the SQL
statement looking for cross joined tables, and reject the statement if
found?

[Thad Rizzi]

Yes I believe you would be able to do that. Take a look at QSYSINC/
QRPGLESRC,EZDAEP. You see the data layouts for what is passed to the
exit program. There is a varying length field that will contain the
SQL statement. You should be able to ananlyze how you wish and then
aloow or deny access. I wrote an exit program to simply log activity
and was able to view the SQL statement.

Thad Rizzi

[Jonathan Ball]

Great, thanks. I'm not sure how I'd parse the statement to try to tell
if a table didn't have join predicates for it, but getting the statement
is the first thing.

[Thad Rizzi]

> is the first thing.- Hide quoted text -
>
> - Show quoted text -

Yeah parsing the statement is probably going to be your biggest
problem. Once you start logging you'll see that everything gets
recorded.

Good luck.

Thad Rizzi

[CRPence]

Would the query governor assist? CHGQRYA and QRYTIMLMT parm and
QQRYTIMLMT system value I believe. Some client interfaces may provide a
setting to enable it versus from an exit program [by CHGQRYA].

Regards, Chuck