Create View on DB" problem

[ryltech]

Hello,

I have created a view in DB2 which runs fine on my version of DB2 which runs on a windows machine.

When i then try to run this same create view statement in iSeries (AS400) it comes back with the following error:

[SQL0101] SQL statement too long or complex.

The statement contains 4 sub selects and 8 joins. There seems to be a difference between the Windows versionand the iSeries...

Can anyone help?

 

[blom0344]

Help may be a problem in this one.
Depending on the AS400 version (we are now at level V5R3) you will find that AS400 is not quite up to the latest UDB versions. There are quite some functions missing, the optimizer has its problems etc.
You will probably need to update to the latest release. This may have consequences, cause on AS400 database version and operating system are one package!

Ties Blom
Information analyst

 

[juliane26]

Not complete, but extract from the AS/400 message reference:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzala/rzalaml.htm

The SQL statement is longer than the limit allowed for length or complexity. The reason code is &1.
One of the following reason codes indicates the error:
1 - The total number of subselects combined using UNION, EXCEPT, and INTERSECT is greater than 256.
2 - The total number of columns, constants, and operators is greater than the SQL limits.
3 - The sum of the lengths of the non-LOB columns in a select list, table, view definition, or user defined table function is greater than 32766 or the definition contains a LOB and the sum of the lengths specified on the ALLOCATE clause for varying-length fields and the non-varying field lengths is greater than 32740. The maximum length is reduced if any of the columns are varying-length or allow null values.
4 - The total number of subselects referenced in an outer subselect is greater than 32.
5 - The total length of the statement text or attribute string text is greater than 65535.
6 - The relative position value specified on the FETCH statement is outside the range of valid values.
7 - A system name could not be generated.

Simplify the statement or divide the statement up into more than one statement and try the request again.
For reason code 7, specify a different name for the table, view, index, or alias.
----

Well, do you know the reason code ? It does not appear to be too complex from what you say, but number 7 could be the case ...

Juliane

 

[ddiamond]

I run into this problem all the time on our AS400 V5R2 whenever my number of unions is greater than or equal to eight. I always assumed the as400 can only handle 8 unions, but from your list above, it looks like it should be able to handle 256. Of course that combined with all of my subqueries and joins could be pushing it over the top.

 

[ryltech]

Thanks everyone.

From this I would gues it is something to do with the number of joins I am using.

Just out of interest - this only happens when I attempt to create a view. If I run the select statement without the CREATE VIEW etc a the beginning it runs fine, but as soon as you attempt to create a view - i get the error message?

Does this shed any light?