Results 1 to 11 of 11

Thread: query size

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: query size

    We are using db2 ese v8.1.3 on Win2k platform.

    We have a data warehouse query, which is quite big (actually, sums up "Net Sale of Units" from Forecast Actual table based on certain criteria) containing several hundreds of "OR" condition in the "WHERE" clause. Now we ran this big query against our database (with default db/dbm parameters) and found that the query actually crashes the server!!

    My question is:
    - Why does db2 server crash if the query is big? An exception message would have been fine.
    - Is there any limit on the number of expressions that can be included in the WHERE clause?
    - How do we handle if the query grow even further?

    Any thoughts / comments?

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: query size

    Hi,

    there is no sql statement that may result in the server crashing. If you can repeat the problem, I would file a pmr with IBM support.

    Johann

    Originally posted by dr_suresh20
    We are using db2 ese v8.1.3 on Win2k platform.

    We have a data warehouse query, which is quite big (actually, sums up "Net Sale of Units" from Forecast Actual table based on certain criteria) containing several hundreds of "OR" condition in the "WHERE" clause. Now we ran this big query against our database (with default db/dbm parameters) and found that the query actually crashes the server!!

    My question is:
    - Why does db2 server crash if the query is big? An exception message would have been fine.

    - Is there any limit on the number of expressions that can be included in the WHERE clause?
    - How do we handle if the query grow even further?

    Any thoughts / comments?

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: query size

    As Johann says, no query should crash the server ...

    For limits on SQL, refer to the 'SQL Limits' topic in SQL Reference for your version

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Posts
    218

    query size

    ... Yes, I agree with both of you. But, surprisingly database crashes if we run that query. Anyway, can this be due to fixpak?

  5. #5
    Join Date
    Apr 2003
    Posts
    191

    Re: query size

    Hi dr_suresh20,

    well, it is always a good idea to stay current with regard to fixes. You should definitely ask for support from IBM. Also, you may want to catch the output from db2diag.log when the crash occurs and post it here.

    Johann


    Originally posted by dr_suresh20
    ... Yes, I agree with both of you. But, surprisingly database crashes if we run that query. Anyway, can this be due to fixpak?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would suggest storing the predicate that contains several hundreds "OR" conditions in a separate DB2 table and then doing a sub-select. You could load the data in a persistent (regular) table, or create a temporary table each time the query was executed. It would obviously be more efficient to use a persistent table.
    Last edited by Marcus_A; 02-16-04 at 11:20.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Production. Possibility

    Hi Marcus,

    Your idea seems really great!!! However do you think that is feasible in production environments??

    No one can possibly create persistent tables for all such queries, especially in scenarios where one expects dynamic queries.

    Regards

    Nitin.
    HTH

    Nitin

    Ask the experienced rather than the learned

  8. #8
    Join Date
    Sep 2003
    Posts
    218
    Hi Johann:

    Please have a look at the db2diag.log file:

    ***
    2004-02-13-15.00.21.538000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:1448 Appid:none
    routine_infrastructure sqlerKillAllFmps Probe:5

    Bringing down all db2fmp processes as part of db2stop
    0x014AFB5C : 0x00000000 ....

    2004-02-13-15.00.22.320000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:516 Appid:none
    base sys utilities DB2StopMain Probe:911

    ADM7514W Database manager has stopped.

    2004-02-13-15.06.13.859000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.031000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.156000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.281000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.16.609000 InstanceB2 Node:000
    PID:1352(iwh2log.exe) TID:1368 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.16.734000 InstanceB2 Node:000
    PID:1352(iwh2log.exe) TID:1368 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.18.812000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:1332 Appid:none
    base sys utilities DB2StartMain Probe:911

    ADM7513W Database manager has started.

    2004-02-13-15.22.51.703000 InstanceB2 Node:000
    PID:2196(db2bp.exe) TID:2180 Appid:none
    database utilities sqlubckp Probe:26

    DiagData
    0x00E610E4 : 0xFFFFFBF5

    ***

    Hi Marcus:

    We cannot store these conditions in tables since they are dynamic in nature and they are controlled by user / application actions.

    I am still unable to figure out "why" db2 database crashes - however, suitable approach would have been to throw an exception.

    I have already raised a PMR with IBM. Let's see how it goes.

    Thanks.

  9. #9
    Join Date
    Apr 2003
    Posts
    191
    Hi dr_suresh20,

    looks like you have hit on something really nasty. Did you check if filesystems fill up, like that one for the temporary tablespace? You can find warnings like that in db2diag.log. Just search for full or temp.

    I wonder if the db2diag.log trace is complete? Because normally I would expect the offending statement to show up someplace in a hex dump.

    Anyway, please keep us informed about what IBM support finds out.

    Johann

    Originally posted by dr_suresh20
    Hi Johann:

    Please have a look at the db2diag.log file:

    ***
    2004-02-13-15.00.21.538000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:1448 Appid:none
    routine_infrastructure sqlerKillAllFmps Probe:5

    Bringing down all db2fmp processes as part of db2stop
    0x014AFB5C : 0x00000000 ....

    2004-02-13-15.00.22.320000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:516 Appid:none
    base sys utilities DB2StopMain Probe:911

    ADM7514W Database manager has stopped.

    2004-02-13-15.06.13.859000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.031000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.156000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.14.281000 InstanceB2 Node:000
    PID:796(db2dasstm.exe) TID:996 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.16.609000 InstanceB2 Node:000
    PID:1352(iwh2log.exe) TID:1368 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.16.734000 InstanceB2 Node:000
    PID:1352(iwh2log.exe) TID:1368 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
    The handle is invalid.


    2004-02-13-15.06.18.812000 InstanceB2 Node:000
    PID:1300(db2syscs.exe) TID:1332 Appid:none
    base sys utilities DB2StartMain Probe:911

    ADM7513W Database manager has started.

    2004-02-13-15.22.51.703000 InstanceB2 Node:000
    PID:2196(db2bp.exe) TID:2180 Appid:none
    database utilities sqlubckp Probe:26

    DiagData
    0x00E610E4 : 0xFFFFFBF5

    ***

    Hi Marcus:

    We cannot store these conditions in tables since they are dynamic in nature and they are controlled by user / application actions.

    I am still unable to figure out "why" db2 database crashes - however, suitable approach would have been to throw an exception.

    I have already raised a PMR with IBM. Let's see how it goes.

    Thanks.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I did not realize that such SQL statements that you were talking about were ad-hoc queries. However, if you talk to the end user, you may find that the combination of predicate values for the OR list has a specific meaning and that it may be able to be stored in a table. You will probably have to allow for multiple combinations of values in the OR list to be selected, but if you investigate further, I suspect you will find that the queries with the long OR list are repeated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2003
    Posts
    9

    Re: query size

    Originally posted by dr_suresh20
    ... Yes, I agree with both of you. But, surprisingly database crashes if we run that query. Anyway, can this be due to fixpak?
    I have to admint what dr suresh tells here. We have a UDB V7.2FP9 that where the DB instance once ONE user runs a quite big M$access macro via ODBC against the database on a Solaris 4 CPU box (16Gig Mem). The Macro does quite big select insert into statements, but a crashing DB?

Posting Permissions

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