Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2008
    Posts
    45

    Unanswered: PRYQTY and SQCQTY

    Hello everybody, I got a question here.

    I hear that to configure tablespace, we could use PRYQTY and SQCQTY (if I'm not mistaken), does anyone know the details ???

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Do you mean PRIQTY and SECQTY ?

  3. #3
    Join Date
    Aug 2008
    Posts
    45
    Thank you very much umayer, my bad. Thank you clearing that up. Since I never found any information considering the two parameters I wrote.

    Thank you

  4. #4
    Join Date
    Aug 2008
    Posts
    45

    Details

    Heya, I forgot to ask if anyone know the function of those parameters (PRIQTY and SECQTY) and the impact if we change their values.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    These are the space allocations for a tablespace in DB2 for z/OS. For the Secondary (and Primary) it is the size of the extent, not the number of extents. If you alter an existing tablespace, then it does not take effect until you do a reorg (and the underlying VSAM linear dataset is recreated).

    If you have applications doing tablespace scans, then it is best that the entire tablespace fit in the primary quantity so that the pages are contiguous on disk. Otherwise the extents may be scattered in different places on the disk. For an OLTP application, it is not as important.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by Marcus_A
    If you alter an existing tablespace, then it does not take effect until you do a reorg

    I disagree.
    Changes to the secondary space allocation (SECQTY) take effect the next time DB2 extends the data set, regardless of a REORG was done or not.

    http://publib.boulder.ibm.com/cgi-bi...748#SPTRTBSCSA

  7. #7
    Join Date
    Aug 2008
    Posts
    45

    Thank You !!!

    Thanks for the replies, all. I really appreciate it. I got that PRIQTY and SECQTY is used for allocating space for extent, right ?
    But I also found out that it also used for dataset. Hmmmm, I wonder if extent and dataset is the same ? Coz, I think they are different, extent is a contiguouos pages, right ? and dataset is tablespace or partitions, right ?

    Oh yeah, one more thing, does anyone know the difference between the tablespace parameter in z/OS and LUW ???

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by umayer
    I disagree.
    Changes to the secondary space allocation (SECQTY) take effect the next time DB2 extends the data set, regardless of a REORG was done or not.

    http://publib.boulder.ibm.com/cgi-bi...748#SPTRTBSCSA
    Yes, you are correct. But usually the main reason for changing the parms is to eliminate secondary extents and fit the entire tablespace in the primary. This does not happen until you reorg the table.

    One more thing. Even though the phrase "DB2 extends the data set" was taken from an IBM manual, I do not believe that DB2 actually extends the VSAM datasets (although I am a bit rusty on z/OS and have not worked with the latest release). What it does is extend the tablespace by creating an extent, which is an additional VSAM dataset associated with the tablespace. For this reason, it is advisable to keep the entire tablespace in the primary space allocation (one VSAM dataset) with as few extents as possible. However, if there is not enough free space on a volume for the entire tablespace (defined as the primary space allocation), then you may have live with some extents on a permanent basis.
    Last edited by Marcus_A; 08-08-08 at 03:58.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by linakichi
    Thanks for the replies, all. I really appreciate it. I got that PRIQTY and SECQTY is used for allocating space for extent, right ?
    But I also found out that it also used for dataset. Hmmmm, I wonder if extent and dataset is the same ? Coz, I think they are different, extent is a contiguouos pages, right ? and dataset is tablespace or partitions, right ?

    Oh yeah, one more thing, does anyone know the difference between the tablespace parameter in z/OS and LUW ???
    Everything I posted so far in this thread assumes you are using DB2 for z/OS, which stores tablespace in a completely different manner than DB2 for LUW.

    In DB2 for z/OS, the primary space allocation uses a VSAM dataset, and each extent (size is specified by the Secondary quantity) is another VSAM dataset. But DB2 will do the VSAM dataset allocation for you if you use STOGROUPS.

    The term extent in DB2 for LUW has a completely different meaning.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Aug 2008
    Posts
    45
    O no !! I'm in trouble. I always thought that the tablespace storing process at z/OS and LUW would be the same. If DB2 use VSAM dataset in z/OS, what does DB2 use in LUW ? The extent which means the contiguous page in tablespace, right ??

    Is that the only difference ? Or is there any differences in the storing mechanism ??

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by linakichi
    O no !! I'm in trouble. I always thought that the tablespace storing process at z/OS and LUW would be the same. If DB2 use VSAM dataset in z/OS, what does DB2 use in LUW ? The extent which means the contiguous page in tablespace, right ??

    Is that the only difference ? Or is there any differences in the storing mechanism ??
    The operating systems and the file systems are compeltely different. There is no comparision between the two. The term "extent" has a totally different meaning on these two platforms.

    It would be best if you stop this comparison, because that dog won't hunt.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    ... What it does is extend the tablespace by creating an extent, which is an additional VSAM dataset associated with the tablespace.
    Not really: when the first extent is filled up, the single VSAM dataset is asked to extend, i.e., to add a second extent. It's still the same, single VSAM file but now having two extents (i.e., two contiguous fragments on disk). After that second extent is filled up, the VSAM file is asked to allocate a third extent, etc. This process ends when all extents have been used up (typically 123 extents). At that point only, DB2 creates a new VSAM dataset with the same characteristics as the original one (and with 1 extent). And the process can go on (until the volume or storage group is completely filled up).
    In summary, for DB2, there are really two levels of allocation: the extents of the VSAM file, and after that the creation of a new VSAM file.

    Since version 8 of DB2, it became very easy to avoid the second level, i.e., it became easy to always stay within the initial VSAM file (possibly with 123 extents): just set SECQTY to -1. This way, the extents taken will become larger and larger for each next extent to be taken, so no space is spilled for small tablespaces, yet only a single VSAM file is needed if the tablespace grows very large.

    The disk fragmentation caused by multiple extents is nowadays less harmful than it was before: 3390 disks are "virtualized" by RAID setups, hence the concept of "contiguous" storage has lost most of its physical relevance.

    Bottomline: just set PRIQTY and SECQTY to -1, unless your tablespace is not going to grow (or you like to re-calculate PRIQTY and SECQTY before every REORG
    Last edited by Peter.Vanroose; 08-12-08 at 19:28.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    Not really: when the first extent is filled up, the single VSAM dataset is asked to extend, i.e., to add a second extent. It's still the same, single VSAM file but now having two extents (i.e., two contiguous fragments on disk).
    You are correct, it is the same VSAM dataset, just with additional extents. But at the OS level, they are tracked as different files (or fragments as you call them) within the VSAM dataset. You can get a report that shows the extents of a VSAM dataset. I only mention this because there are some people who are trying to compare z/OS with Linux/UNIX/Windows, and a VSAM dataset with extents is not really the same as a fragmented file in LUW.

    Also, I did not think that VSAM extents are necessarily contiguous on the disk. But I will admit that my z/OS skills are rusty.

    Most DBA's try to keep the entire tablespace in the PRIQTY just so the space will be continuous. But if the tablespace is very large and will not fit on to contiguous free space within a volume, that is not always possible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Aug 2008
    Posts
    45
    Thanks everyone for the replies.

    But, since I'm currently using Linux, the term "extent" isn't equal to VSAM dataset, right ? Does anyone knows how to use PRIQTY and SECQTY at LUW ?
    Last edited by linakichi; 08-18-08 at 05:26.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by linakichi
    Thanks everyone for the replies.

    But, since I'm currently using Linux, the term "extent" isn't equal to VSAM dataset, right ? Does anyone knows how to use PRIQTY and SECQTY at LUW ?
    Where do you see those parameters in DB2 for LUW?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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