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.

What does the DB2 documentation says for that error code and reason code?

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.

That's what I did...

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

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