Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to disable cross-join in DB2?

    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
    Last edited by grofaty; 07-20-09 at 07:40.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe educate your programmers instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Yes, you are funny... Programmers know how to write SQLs, but sometimes someone of them just forgets to write it.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •