How to disable cross-join in DB2?

grofaty · #1 (**permalink**) 07-20-09, 05:31

Hi,
using DB2 v9.5.2 on Linux I often get SQL from performance hell that is not using where conditional sample (programmer just forgets to put joins):

Code:

SELECT t1.col1, t2.col2
FROM table1 t1, table2 t2

but correct is:

Code:

SELECT t1.col1, t2.col2
FROM table1, table2 t2
WHERE t1.pk=t2.pk

Is there any way I could prevent executing this kind of SQLs. So to tell DB2 if "select...from..." is used without where conditional then return error and don't execute SQL.

But just in case if someone needs this kind of functionality I would like that "cross join" kind of syntax is allowed (if someone writes this kind of syntax then he/she is aware of what is he/she doing):
So sintax:

Code:

SELECT t1.col1, t2.col2
FROM table1 t1 CROSS JOIN table2 t2

should be allowed, but

Code:

SELECT t1.col1, t2.col2
FROM table1 t1, table2 t2

should not be allowed.

Regards

r937 · #2 (**permalink**) 07-20-09, 06:24

maybe educate your programmers instead?

grofaty · #3 (**permalink**) 07-20-09, 06:40

Yes, you are funny...

Programmers know how to write SQLs, but sometimes someone of them just forgets to write it.

ARWinner · #4 (**permalink**) 07-20-09, 08:29

There is nothing that I know of that you can do to prevent certain kinds of queries. If educating your programmers does not work, get new programmers. Either that, or all SQL that they create must go through a code review before it can be used.

Andy

Cougar8000 · #5 (**permalink**) 07-20-09, 11:01

Quote:

Originally Posted by grofaty

Yes, you are funny...

Programmers know how to write SQLs, but sometimes someone of them just forgets to write it.

Sounds like they forget this things way too many times. Which leads one to question is it forgetfulness or ...

If those are big tables that end up reading millions of records you can set up a monitor to check on it and then force those connections off. Down side to this is you might kill a "good" thread. It might not be appropriate in some cases.

Best to do what others already said.

n_i · #6 (**permalink**) 07-20-09, 11:31

You can't "disable cross-joins", however, you could use the workload management feature, which is integrated in DB2 9.5, to track and react (e.g. stop or throttle) heavy-hitting queries. Check the manual.

stolze · #7 (**permalink**) 07-20-09, 14:19

Disabling cross-joins won't do you much good anyway. There are a lot of other constructs that could lead to bad query performance. So you should review all the SQL statements - that's why there are shops that don't allow dynamic SQL and only run applications with carefully reviewed and tuned static SQL.

Internet Services

Web Development

Digital Marketing

Consumer Tech