Db2

Hello @Mark Gillis,

Have you try this:

https://www.ibm.com/support/pages/sql0659n-max-size-table-has-been-reached

I am not from the Db2 team, but I have found this Technote. 

I hope that helps!

Thanks Cintia. I had already found that support article but it refers to a LOAD command rather than a query, and the diagnostic entries do not match. 

My problem is really that the error says that is 'an object' but there doesn't seem to be any way of finding what it is referring to. It's likely a bufferpool or a tablespace but I really need to know which (or what, if it's neither of those).

Regards

Mark

Hello Mark,

I understand that the Technote is related to the LOAD command, however it is related to a sort component just like as the order by clause in your case.

It looks like the temporary tablespace size being used to sort the data is not big enough to hold all the data for the sort.

You can try to follow the steps from the Technote to see if that helps.

Thanks, Cintia.

Hi Cintia

it could be the sort, or it could be the group by, or maybe something else. But the problem is that the two recommendations in the technote are

1) provide a temporary tablespace of equal or higher pagesize to the one in which the table sits, and there already is, and

2) Increase the size of the bufferpool that holds the desired temporary tablespace, and the buffer pools are all defined as AUTORESIZE with AUTOMATIC STORAGE

If there was a memory constraint you'd normally see something in diagnostic log. So either something is not being reported in the diagnostics or a different tablespace / bufferpool is somehow being invoked. And without something in the error message to say which it is, we're looking for a needle in a haystack.

Regards

Mark

Did we get it right, that there is nothing about this error in the db2diag.log? Very strange if so...

Indeed. I've used the db2diag tool to try and search for relevant messages, such as

db2diag -g "function:=sqlbAlterAutomaticBufferPool" 
db2diag -g "function:=stmmCheckIfFreeMemoryIsEnoughForSizeIncr" 

db2diag -l Error,Severe,Critical -fmt "Time: %{tsyear}-%{tsmonth}-%{tsday} %{tshour}:%{tsmin}:%{tssec} %level %appid %msg %data" | grep -i mem

and simply looking for the details between the start and failure times, but there is nothing relevant. 

I may have found a workaround using an MQT but I would still like to find a way of getting some more explicit information with this error message in case it occurs elsewhere

That is odd that there is no information in the diaglog.  What is the DIAGLEVEL set to?  If it's 0-2, maybe increasing it temporarily to 3 or 4 would get you that more detailed information in the diaglog. 

I'm not authorized to see the full article at the Cintia's link above, but you should probably look for these messages with the following command instead.

db2diag -g "message:=SQLD_RC_MAX_OBJ_SIZE"

and look at a number of messages around...

It's been a reeeeally long time since I had v9.5, but there was something in the upgrade from v8 to v9.5 (sms and dms to automatic storage?). I know we had to rebuild the database in order to take advantage of the larger tablespace sizes when we upgraded to v10.x to use larger automatic storage tablespaces.. V8.5 tablespaces were extremely limited compared to current versions, and depending on the tablespace definition, when you upgrade to v9.5, you could end up with the v8.x limited tablespace size. V9.5 max tablespace size is 2tb, so take a look at your temporary tablespace definition and make sure that they are defined to use 2tb. 

Try db2 "? sql0659" from operating system 

Regards

Thanks for the input guys.

Unfortunately nothing in the diagnostic log (current or any of the archived versions going back 2 or 3 months) with a message of SQLD_RC_MAX_OBJ_SIZE

And all tablespaces are defined as LARGE and AUTORESIZE, so shouldn't have any constraint on size except for the limits for that server. And hitting those limits should trigger some sort of diagnostic message.

I agree that operating in a v9.5 database isn't helping matters but I don't see anything in even more recent versions that expand on the SQL0659 message to suggest which object is the limiting factor.

Regards

Mark Gillis

If you look at the files in the SMS table spaces there will be S00002.TDA temporary data file huge file in your case and you map that particular physical file with the corresponding database table name.

Is it possible that the query is producing more rows than expected (Cartesian product)?
Maybe have someone do a sanity check on the SQL to ensure the joins are coded properly.
It's a long-shot, but I thought I'd mention it.

The problem described here is not about not optimal queries leading to SQL0659N, but on inability to get the object hitting the limit.

It seems that my guess about finding the message with the "SQLD_RC_MAX_OBJ_SIZE" substring doesn't help.

So, can someone look at their db2diag.log when you got SQL0659N to understand, if there are some messages around with the object identifiers hitting the limit?

If there are no such messages, then we can only guess like: if it was a select statement, then some huge amount of data tried to be written to some (which one if there is a number of the) temporary tablespace (DMS regular?), and the corresponding object there hit the tablespace limit. Not very convenient really...

Hi 

If the concern is about temporary table exceeding size limits in a temporary tablespace,  you can run 

db2pd -db dbname -tcbstats 1

Look for temporary tables created by application number and size information is provided in bytes.

Hopefully you can identify which application is creating that huge temporary table and the size of it will help you i assume.

Thanks 

Harishkumar 

Thanks for the various inputs. I'm afraid the issue is as @Mark Barinstein describes:

"The problem described here is not about not optimal queries leading to SQL0659N, but on inability to get the object hitting the limit"

I did eventually solve the problem by introducing an MQT with dedicated Tablespace and BufferPool resources, but the issue for me is locating the actual object that the SQL0659 is referring to. The diagnostic log has no relevant info and the diaglevel is set to the default 3. I know the application and I know that it's not a cartesian product as previous executions have worked, and the SQL does work when embedded in an MQT.

I think this is just exposing a lack of detail in the error message. A lot of (most) error messages will give you some detail and point the finger at the offending article.  For example SQL0407 might give you some internal IDs and you might need to run a query against using "TBSPACEID=n1, TABLEID=n2, COLNO=n3" but you can find the object in question. SQL0659 just says that the size of a table object has been exceeded.

We've probably gone as far as we can with this folks. I don't think there is currently a solution so I will try my luck with the IBM Ideas Portal.

Regards

Mark Gillis

Thank you for submitting your idea in the IBM Data and AI Aha Ideas Portal: DB24LUW-I-2195 SQL0659N to supply relevant data

When a query fails with SQL0659 ("Maximum size of a table object has been exceeded") there is no indication of which object is at fault. In the execution I am examining there is also nothing relevant in the diagnostic log.

An indication in the error message of which tablespace (for instance) or other objects that has run out of resource would allow timely and effective correction.