-1 means that it no value was specified when the object was created and the default is used. On mainframe DB2, the default value is stored instead of -1 when not specified. Not sure why it is different on the other platforms.
On many of the columns in the DB2 catalog, -1 often means that runstats has not been run and therefore there are no statistics gathered for that value (such as number of rows, etc.). Note that this means that most catalog statistics are not updated real time, only when runstats is run.
Sorry, I should have mentioned the APAR Number(s) in my previous post :
In SQL Reference, "Alter table" statement description did not
inform about the default table pctfree value as 0 and the
meaning of -1 in catalog is to be considered as default.
User affected: All
Problem Description: No document found for default pctfree value
for table page.
Problem Summary: No document found for default pctfree value
for table page.
TEMPORARY FIX: none
First Fixed In:
No FixPaks containing the specified APAR were found
First Fixed In:
Information is not available
Visit the new-look IDUG Website , register to gain access to the excellent content.
I couldn't find meaning of -1 anywhere either. As Marcus pointed, I guess -1 does not indicate that runstats haven't been applied. Some like cardinality depend on runstats and are -1 when no runstats are run even once. Also do you guys know a way of calculating the PCTFREE value. We keep getting deadlocks inspite of following lot of things mentioned in the docs. Any ideas?
PCTFREE should be the percentage of rows inserted into the table between REORG's. However this assumes that the rows are inserted into the middle of the table (and not always at the end). Also, it does not take into consideration the number of rows deleted. If the rows are always inserted at the end, no freespace is needed. This depends on the serveal factors including the clustering index defined for the table, or use of the append clause, etc.
Indexes also need freespace for good performance. Just like the rows in the table, some indexes always add values at the end (in which case freespace should be set to zero or maybe 1), and other indexes add values dispersed throughout the index, in which case the same calculation for the table should be used above.
Information Warehouse databases where there is always a load replace or a reorg after each refresh/update, do not need freespace.
The questions of deadlocks or timeouts (not the same but often confused as the same) is a bit more complex and involves a number of factors. Probably need some serious education on DB2 or someone who can come and help you out.
Thanks for the response. I didn't get mixed up with PCTFREE and deadlocks. Its a different question which I shouldn't have posted with the PCTFREE issue.
Have done quite a bit of research and changes that improved the deadlock issue, still keep getting them sometimes. Thats the reason was asking if anyone had ideas which I missed.
Marcus, Anyway thanks for all your suggestions...
Sathya, We changed the isolation levels. To improve performance (to prevent applications from waiting), added bufferpools,enabled DB2EMPFA. On some tables,set volatile on. Some of the applications were rewritten with frequent commits. DB2_ENABLE_BUFPD to use intermediate buffering for improving query performance. For one of the queries, we changed the query optimization from default. Adding indexes and reorgs. This gave us quite an improvement. Once in a while we still get deadlocks .May be I should have mentioned these before asking the question.
Sorry, you misunderstood. I did not think you confused PCTFREE with deadlocks. But many people confuse deadlocks with timeouts. A deadlock is not the same as a timeout, although you may receive the same error message. Knowing whether you have a deadlock (sometimes known as a deadly embrace) vs. a timeout (due to contention that lasts longer than the timeout parameter) is key to understanding how to fix the problem.
It's not all right if you are trying to fix the problem and you don't know whether it is a deadlock or whether it is a timeout. Unfortunately some of the DB2 documentation also confuses this issue (unlike the DB2 mainframe docs which get it right).
A deadlock (deadly embrace) is not the same as a simple timeout. A deadly embrace will NEVER be resolved unless one thread is cancelled (or DB2 just cancels it because it exceeds the timeout parameter that has been set). That's were the "dead" part comes from in the name.
OTOH, a simple timout occurs when the wait time for a resource exceeds the time out parameter. If the timeout parameter was set high enough, or disabled, then the resource would eventually free up so the work could continue.
However, both of these may "timeout" so it may not be obvious which condition you have, and consequently it may not be obvious how to fix it.