Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: error message while executing sql on syscat.tables

    Hi,

    when executing the following statement:

    select distinct substr(tabname,5,4),substr(tabname,10,2),'dummy' from syscat.tables where tabschema = 'user' AND tabname like 'TRX___________' and substr(tabname,5,4)||substr(tabname,10,2) between to_char(current_timestamp-1 year + 1 month,'YYYYMM') and to_char(current_timestamp,'YYYYMM')

    , I get the output:
    <<<
    1 2 3
    ---- -- -----
    SQL0437W Performance of this complex query may be sub-optimal. Reason code:
    "1". SQLSTATE=01602

    2010 01 dummy
    2010 02 dummy
    2010 03 dummy
    2010 04 dummy
    2010 05 dummy
    2010 06 dummy
    2010 07 dummy
    2010 08 dummy
    2010 09 dummy
    2010 10 dummy
    2009 11 dummy
    2009 12 dummy

    12 record(s) selected with 1 warning messages printed.
    >>

    What could be the problem here and what to do?

    I have daily tables, like TRX_2010_08_01, TRX_2010_08_02 and so on.
    Based on the result I want to create monthly views with "union all". But with that error message is not possible anymore. I'm talking about 650 tables.
    I do also have daily views, like TRX_V_2010_08_01. An SQl-cmd based on syscat.views doesn't show that problem.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What does the DB2 documentation says for that error code and reason code?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    I only have the explanation in german. they are talking that the performance has been set to a lower value...so like the error message that occured.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by saez View Post
    I only have the explanation in german. they are talking that the performance has been set to a lower value...so like the error message that occured.
    No, I mean in the manual. Look up the error message in the DB2 Message Reference Vol 2. The manuals can be downloaded in PDF format from the IBM website.

    You can also find it in the DB2 InfoCenter website.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2009
    Posts
    24
    That's what I did...

    01602 "Die Optimierungsstufe wurde auf einen niedrigeren Wert gesetzt. "

  6. #6
    Join Date
    Mar 2010
    Posts
    32
    ur error states the following :-

    The statement may achieve sub-optimal performance since the complexity of the query requires resources that are not available or optimization boundary conditions were encountered. The following is a list of reason codes:

    1
    The join enumeration method was altered due to memory constraints

    try the following as stated in db2 manual :-


    # Increase the size of the statement heap (stmtheap) in the database configuration file.
    # Break the statement up into less complex SQL statements.
    # Change the current query optimization class to a lower value

Posting Permissions

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