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 ???

Do you mean PRIQTY and SECQTY ?

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

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

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.

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

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 ???

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.

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.

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.

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

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.

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?