Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Pctfree

  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: Pctfree

    Any idea as to what the value of -1 for PCTFREE indicates?

    Thanks
    Kota

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Pctfree

    For Table or index ?

    If it is for table, the value of -1 means that the pctfree value is the default,ie 0 ...

    I'm not sure what this would mean for an index ...

    HTH

    Sathyaram

    Originally posted by Kota
    Any idea as to what the value of -1 for PCTFREE indicates?

    Thanks
    Kota
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I am not quite sure for PCTFREE parameter, but in general parameter witch value is -1 means that this parameter is turned off.


    PCTFREE about index:

    If PCTFREE=0 then
    CREATE INDEX index name ON schema.tablename (col1 ASC) PCTFREE 0 MINPCTUSED 10

    Result: OK


    If PCTFREE=-1 then
    CREATE INDEX index name ON schema.tablename (col1 ASC) PCTFREE -1 MINPCTUSED 10

    Result: Syntax error!!!


    Hope this helps,
    Grofaty
    Last edited by grofaty; 07-28-03 at 06:28.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    -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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry, I should have mentioned the APAR Number(s) in my previous post :

    IY42129
    IY42132

    APAR Description:


    ERROR DESCRIPTION:
    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.
    LOCAL FIX:
    none
    PROBLEM SUMMARY:
    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.

  6. #6
    Join Date
    Jun 2003
    Posts
    127
    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?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  8. #8
    Join Date
    Jun 2003
    Posts
    127
    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...

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you tell what you have done, maybe someone will tell what you missed ...
    (in a new thread)
    Cheers

    Sathyaram


    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.
    Last edited by sathyaram_s; 07-28-03 at 13:10.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jun 2003
    Posts
    127

    LOCKS

    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.

    Thanks...

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  12. #12
    Join Date
    Jun 2003
    Posts
    127
    Thats alright. Don't worry. As long as we exchange ideas or knowledge, thats good.

    thank you

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).

  14. #14
    Join Date
    Jun 2003
    Posts
    127
    Sorry. I missed your question earlier (you've been referring in a couple of previous postings). We are getting a deadlock timeout. Now I guess I'm in sync with your question.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

Posting Permissions

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