Why universal?
As I see it, their are two main reasons to convert non-universal table spaces to the universal kind. First, it's the only way in which you can leverage the benefits of partition-by-growth table spaces, one of two varieties of universal table space (the other being partition-by-range). A partition-by-growth (PBG) table space -- as the name implies -- is one that is partitioned as needed to accommodate a table's growth; so, if in creating a table space (or altering an existing table space) one specifies a DSSIZE (data set size) of 2G (2 gigabytes) then upon the table reaching 2 GB in size DB2 will add a second partition to the table space. If that partition fills up (i.e, when it reaches 2 GB in size), a third partition will be added to the table space, and so on up to the maximum number of partitions specified for the table space (that being MAXPARTITIONS, an alterable value).
What's good about this? Well, first and foremost it eliminates the 64 GB size limit that previously existed for table spaces that are not range-partitioned -- a PBG table space, like a range-partitioned table space, can reach a size of 128 TB (and that's for the non-LOB data in the table -- with LOB data the data capacity of a table space can far exceed 128 TB). Of course, you might think of a table in your DB2 for z/OS environment that would never approach 64 GB in size, and wonder, "Why should I convert THAT table's table space to universal PBG?" That would be a good question, if size were the only incentive for converting a non-universal table space to universal. There are, in fact, quite a few non-size-related reasons for getting these conversions done. I'll get to these presently, but first I want to clear up a misconception. Some folks think that PBG table spaces are not appropriate for small tables because of the "P" (for partition) in PBG: we've historically thought of partitioning as a means of getting more than 64 GB of data in a table, and so we equate "partition" with "big" and write off PBG for smaller tables. Time to change that thinking. Is PBG a good choice for a table that will never hold more than, say, 20 KB of data (this could be a reference or code table), even with 1G being the smallest allowable DSSIZE value? Sure it is. Will that little table's table space be 1 GB in size, with 20 KB of the space used and the rest wasted? Of course not. The table space's physical size will be determined by its PRIQTY and SECQTY specifications (primary and secondary space allocation, respectively). If those specifications are chosen appropriately, the table with 20 KB of data will occupy 20 KB of disk space. The 1G DSSIZE specification means that IF the table space size reaches 1 GB then DB2 will add another partition to the table space. If the table space size never reaches 1 GB then the table space will stay at one partition. Got it?
OK, on to the non-size related incentives for going universal. A growing list of DB2 features can ONLY be used in conjunction with universal table spaces. These include (and I've indicated the DB2 release through which these features were introduced):
- Clone tables (DB2 9)
- Hash-organized tables (DB2 10)
- "Currently committed" locking behavior (DB2 10 -- a means of reducing lock contention)
- Pending DDL (DB2 10 -- more on this below)
- LOB inlining (DB2 10)
- XML multi-versioning (DB2 10 -- required for a number of XML-related enhancements)
- ALTER TABLE with DROP COLUMN (DB2 11)
Getting there got a lot easier starting with DB2 10
In my universal table space incentive list above I mentioned pending DDL. This is a capability that became available with DB2 10 running in new-function mode. Big picture-wise, what pending DDL made possible was the non-disruptive alteration of a number of aspects of the definition of a table space or table or index. Want to change the SEGSIZE of a table space? The page size of an index? The DSSIZE (data set size) of a table space? No problem. Thanks to pending DDL, you just issue the appropriate ALTER statement and then materialize the change by way of an online REORG. Between the ALTER and the online REORG, is application access to the target object compromised? No. The table space or index affected is placed in the new (with DB2 10) and non-restrictive AREOR state (which basically means that a pending DDL change has been issued for the object but has not yet been materialized via online REORG).
So, what does this have to do with conversion of non-universal to universal table spaces? Well, it so happens that the only pending DDL change possible for a non-universal table space is a change that would, when materialized, result in the non-universal table space becoming universal. For a simple or segmented table space containing a single table, that change is an ALTER TABLESPACE that adds a MAXPARTITIONS specification to the object's definition. Issue such an ALTER, and after a follow-on online REORG the table space will be a universal partition-by-growth (PBG) table space. For a table-controlled partitioned table space, the change is an ALTER TABLESPACE that adds a SEGSIZE specification to the object's definition. Run an online REORG for the object after the ALTER, and voila -- you have a universal partition-by-range (PBR) table space. Easy.
Some considerations
As you plan for the conversion of your non-universal table spaces to the universal variety, there are things to which you should give some thought:
- The non-disruptive process described above for converting simple and segmented table spaces to PBG universal table spaces (ALTER with MAXPARTITIONS, followed by online REORG) is available, as I mentioned, only for single-table simple and segmented table spaces. For a simple or segmented table space containing multiple tables, you'd have to either go the unload/drop/create/re-load route (with one PBG universal table space created for each table in the multi-table simple or segmented table space), or wait and see if a future release of DB2 provides a non-disruptive universal table space conversion process for multi-table simple and segmented table spaces (this is a known requirement).
- The non-disruptive process for converting traditional range-partitioned table spaces to PBR universal table spaces (ALTER with SEGSIZE, followed by online REORG) is available, as I mentioned, only for table-controlled partitioned table spaces. For an index-controlled partitioned table space, you'll first need to accomplish the conversion to table-controlled partitioning. That's most easily done via issuance of an ALTER INDEX statement with NOT CLUSTER for an index-controlled partitioned table space's partitioning index (as described in a blog post I wrote a couple of years ago).
- Materialization of the change to universal table space from non-universal (via online REORG after the appropriate ALTER statement) will invalidate packages that depend on a given table space. These packages will be automatically rebound (by default) when the associated programs are next executed, or you can rebind them explicitly. By default, plan management should be active on your system (i.e., the value of the PLANMGMT parameter in ZPARM should be EXTENDED or BASIC). That being the case, on the off chance that a package rebind operation leads to performance degradation (performance will typically be the same or better following a package rebind), you can very quickly switch back to the previous instance of a package via a REBIND PACKAGE command with the SWITCH(PREVIOUS) option.
There you have it. If you've not already started the process of converting your non-universal table spaces to universal PBG or PBR table spaces, it's time to get that effort going. Your organization will benefit from your actions.
Hi Robert,
ReplyDeleteIs there a way to identify all the dependent packages that are to be Rebinded for a classic partitioned tablespace which is going to be converted into a UTS ?
Yes, this can be easily done. Just issue a SELECT against the catalog table SYSIBM.SYSPACKDEP. For example, if you were converting table space TS123 in database DB456 from classic partitioned to range-partitioned universal via ALTER TABLESPACE (with SEGSIZE) and online REORG (in a DB2 10 NFM or later environment), you could identify dependent packages ahead of time with a query like this one:
DeleteSELECT DCOLLID, DNAME, DCONTOKEN
FROM SYSIBM.SYSPACKDEP
WHERE BNAME = 'TS123'
AND BQUALIFIER = 'DB456'
AND BTYPE = 'R'
ORDER BY 1,2,3;
Robert
Thanks for this Blog! We would be converting to UTS. When we REORG the tablespaces after the ALTER to materialize to UTS, all the dependent packages becomes invalid and these need a Rebind. This Rebind might change the accesspath. My question: how do we save this accesspath before the conversion and eventual fallback to this accesspath if there is a need. Thanks in advance.
DeleteWell done, Robert. You made it easy to understand.
ReplyDeleteThanks. Glad that the blog entry was helpful.
DeleteRobert
Hi Robert,
ReplyDeleteWe are planning to convert partition table space (say XYZ123) to Universal Tablespace (with Partition by growt).
The partition Tablespace XYZ123 has the partition table created in it.Now our task is to convert the partition table to non partition table in Universal Tablespace.
Our approach is unload/drop/create/re-load route. Could you please let me know if you have better way of doing above task.
Thanks
Khalinder
Sorry about the delayed response. Two questions:
Delete1) Does the table space in question (XYZ123) currently use index-controlled partitioning or table-controlled partitioning?
2) Why do you want to convert this range-partitioned table space to a partition-by-growth table space? Why not just convert it to a universal partition-by-range table space? That could be done with an ALTER and an online REORG.
Robert
Thanks for the favor.
DeleteXYZ123 tablespace is currently use table controlled partition.
The data is not evenly distributed, 99.99 percent data is stored in single partition. So planning to convert to universal tablespace partition.by.growth.
If 99.9% of the data is in one of the table's partitions, is that partition the "last" partition of a table partitioned by a continuously-ascending value such as date? If so (and assuming that you have not yet reached the maximum number of partitions for a table, which is 4096 unless DSSIZE is quire large), could that situation have been avoided by periodically (e.g., every week or month or whatever) issuing an ALTER TABLE statement with the ADD PARTITION clause?
DeleteIn any case, to get from non-universal range-partitioned to universal partition-by-growth, I believe that you will in fact need to go the unload/drop/re-create/re-load route. To minimize the associated period of data unavailability, some organizations do the following: 1) unload from range-partitioned table X and load into table Y in a partition-by-growth table space; 2) assuming that table X was being updated while table Y was being loaded, use a DB2 log analysis tool to get the data-changing SQL that updated table X while table Y was being loaded, and apply those SQL statements to table Y to bring it into sync with table X (if table X was open for update during that sync-up operation, you may need to perform an additional sync-up -- in essence, you're duplicating what online REORG with SHRLEVEL CHANGE does); 3) when tables X and Y are finally in sync, stop all access to table X, change its name to z, and change the name of table Y to X (that renaming will invalidate packages dependent on table X, so you'll need to rebind those).
A more involved process, yes, but one that has been used to keep data unavailability to a minimum, when that is of overriding importance to an organization.
Robert
Hi Robert,
DeleteI am not clear about PBR. If I have partitions defined as PBR and 64G DSIZE, but when the particular ranged partition is almost filling 64G size, should I issue alter DSIZE statement to before it reaches 64G or it will extent automatically since it SMS managed data set. If I have to issue ALTER statement, can I alter the DSIZE when the dataset is in use?
It is great help if you can clarify this for me?
Thanks a lot.
What DB2 for z/OS version are we talking about here, and is it in conversion or new-function mode? How many partitions does the table have? What is the page size for the table space? What is the PIECESIZE for any non-partitioned secondary indexes defined on the table space?
DeleteRobert
Hi Robert,
DeleteSorry for not clear enough for my question. It is DB2 z/OS v10 new function mode. There are 3 partitions which were partitioned by range. We are still in the 1st partition, then ran into 'partition is full' error. Page size is 32K. DSSIZE 64G Segment size 32. There is no non-partitioned secondary indexes.
Thanks, Robert.
Sorry about the delayed response.
DeleteYou can change the DSSIZE to a larger value (e.g., 128G) via an ALTER TABLESPACE statement, and in a DB2 10 NFM system this will not impact data availability. The change will be treated as a pending DDL change (pending DDL is a feature that was introduced with DB2 10 for z/OS). That means the table space will be placed in AREOR status (which does not impact data accessibility) and information about the change will be placed in the new (with DB2 10 NFM) SYSPENDINGDDL table in the DB2 catalog. A subsequent online REORG of the table space (the entire table space) will apply the pending DDL change (the increase in DSSIZE).
Longer term, I would recommend changing the table space's partition limit key values, and adding partitions (three partitions is an exceedingly small number for a range-partitioned table space). With DB2 11 in new-function mode, alteration of partition limit key values is treated as a pending DDL change, and so can be accomplished in an online fashion (issue ALTER, then run online REORG to materialize the change). I'd also give consideration to adding partitions to the table.
Robert
Hi Robert,
DeleteNo problem. I figured out how to handle it before you got the chance to answer the question. I issued an ALTER TABLESPACE xxx DSSIZE 128G and it resolved the issue of 64 G limitation and without impacting data availability. I did reorg to remove AREOR status afterwards. Additional partitions were added. Thanks very much for taking time to answer the question. You are always very helpful.
Hi Robert,
DeleteWe are at DB2 11 NFM. Are there any updates to the above scenario with DB2 11? We have a need to convert a non-UTS partitioned table to UTS PBG. Any way to make that change and keep data availability without doing the "shell game" outlined in your 4/27/15 entry?
Thanks in advance,
Steve
No change since then. These is still no way to convert a range-partitioned table space to a PBG table space by way of an ALTER and an online REORG. That easy "ALTER and REORG" path to PBG only works for a simple table space or a traditional segmented table space.
DeleteRobert
Thanks Robert for the wonderful article.
ReplyDeleteGlad you liked it.
DeleteRobert
Hello Robert,
ReplyDeleteThanks for the great work.
Question : Can I convert segmented TS to UTS PBR, than dropping/ recreating the tablespaces ?
No. A table-controlled partitioned table space can be converted in a non-disruptive fashion to universal partition-by-range, but this is not true for a traditional segmented table space (I say "traditional" because universal table spaces are segmented as well as partitioned). You can convert a traditional segmented table space to universal partition-by-growth in a non-disruptive way (starting with DB2 10 in new-function mode), but if you want to make the traditional segmented table space a partition-by-range universal table space, you will have to go the unload/drop/re-create/re-load route.
DeleteRobert
Hi Robert,
ReplyDeleteThe blog's content is excellent for learners !!!
I have converted a lot of table spaces to UTS PBG and PBR. But recently, I found an issue during the conversion and was not able to figure it out.
Could you help me?
I have to convert a Index controlled Classic partitioned table space to UTS PBR. For this, have altered a tablespace by altering its index from NOT CLUSTER, COMMIT and then back to CLUSTER. Later, I saw the last partition of the table space went into REORP status. The same table space remained in RW when I performed the same change in another subsystem.
What could be the possible cause for this?
Sorry about the delayed response.
DeleteI recommend contacting the IBM Support Center regarding this issue. I would not have expected this result. It is possible that in the index-controlled partitioned table space, some rows in the last partition had a partitioning key value greater than the partition's limit key value (in some cases, the limit key value of an index-controlled partitioned table space's last partition is not strictly enforced -- a row with a partitioning key value greater than the limit key value of the last partition is simply placed in the last partition, versus being rejected in insert); however, my expectation would be that on being converted to table-controlled partitioning, the table space's last partition would have its limit key value changed by DB2 to its highest possible value, so that any row that had technically not belonged in that partition (due to having a partitioning key value larger than the last partition's limit key value) would be OK in the new table-controlled partitioned table space (the limit key value is strictly enforced for inserts into a table-controlled partitioned table space). If that adjustment of the last partition's limit key value to its highest possible value occurred when you converted the table space to use table-controlled partitioning, the partition shouldn't have gone into REORP status (i.e., "hard" REORG-pending status). Similarly, if the index controlled partitioned table space did not have any rows in the last partition with a partitioning key value larger than the partition's limit key value, the last partition should not have gone into REORP status.
Yours is a strange result, and I can't tell you why it occurred. Perhaps the IBM Support Center can figure out what happened.
Robert
Very well written and really helpful. Thanks Robert.
ReplyDeleteHi Robert, really enjoy your blogs. However can you please explain how you can switch to a previous version of a package when that version has been marked as Invalid by the reorg to materialize the UTS change.
ReplyDeleteI've tried this and as expected it doesn't work, it fails with a DSNT269I message - Previous version of the package is invalid.
Thanks, Mark
Somehow, I overlooked this comment. Thus the two-months-late response. Very sorry about that. Anyway, REBIND SWITCH is not a solution for dealing with an invalidated package. The intent of REBIND SWITCH (and of plan management, through which the previous instance of a package is retained when a new instance is generated via REBIND) is to provide a means of quickly going back to the previous instance of a package when a new instance, generated via REBIND, delivers less-good performance because of an access path change. When an online REORG is run to materialize a pending DDL change, packages dependent on the object affected by the pending DDL change are invalidated and the only way froward is a rebind of the package (not a REBIND SWITCH) -- either an explicit rebind or the automatic rebind that will otherwise occur when the invalidated package is next executed.
DeleteRobert
Hi Robert,
ReplyDeleteWhat makes a DBA prefer a PBG over a PBR ?
Is it true that if mainly working at partition level (i.e. Utilities like REORG PART) then PBR tablespaces are preferable ?
Thanks!
I wrote a blog entry on this topic a few months ago: http://robertsdb2blog.blogspot.com/2015/05/for-large-db2-for-zos-table-should-you.html. Check it out and see if it answers your question.
DeleteRobert
what is the way to covert UTS PBG and PBR and vice versa ?
ReplyDeleteApologies for the delayed response.
DeleteThe way to get from UTS PBG to PBR and vice versa is unload/DROP/re-create/re-load. It cannot be accomplished via ALTER and REORG.
What some organizations do to mitigate the data availability impact of a change requiring DROP and re-create (suppose you want to change table TAB_A from PBG to PBR):
1) Create a PBR table, TAB_B, that is logically equivalent to TAB_A (same columns, same order, etc.), and create indexes as needed on TAB_B.
2) Unload from TAB_A, and load TAB_B.
3) Use a log analysis tool (available from IBM and other vendors) to extract from the DB2 log all changes made to TAB_A since it was unloaded to populate TAB_B, and "replay" those changes for TAB_B, to bring TAB_B data up to currency with respect to TAB_A (TAB_A must be in read-only mode at this time, so that data in TAB_A won't be changed while data in TAB_B is being brought to currency).
4) Rename TAB_A to TAB_X, and rename TAB_B to TAB_A.
5) Rebind packages made invalid by the above RENAME TABLE action.
Robert
Is this still the way to convert from PBG to PBR?
DeleteI am dealing with an Identity column which throws a wrench in the REDO SQL as it generates INSERTs with DEFAULT value for it. Then any subsequent DELETEs fail with -100 not found as they have the actual value that was assigned to the Identity on the original table.
It appears that you are asking two questions.
DeleteQuestion 1: is unload/drop/re-create/re-load still the only way to change a PBG table space to PBR?
Answer: yes - at this time. The need for a better way to change a PBG table space to PBR has been understood by the IBM Db2 for z/OS development organization as a key requirement. Keep an eye out for a very important Db2 for z/OS announcement coming later this year.
Question 2: before I answer this one, could you let me know if this is a data replication scenario, and if so, if both the source and the target are Db2 for z/OS systems?
Robert
Thank you for the prompt response!
DeleteThis is not a data replication scenario. I was simply generating REDO SQL in order to apply any update activity since the unload of TAB_A.
I am now testing with FCIC Consistent instead of unloading directly from TAB_A. This allow me to minimize the length of the REDO SQL and I can get away with it as long as no newly inserted rows were subsequently updated or deleted.
Exciting news about IBM having an announcement later in the year.
Besides a consistent FlashCopy image copy, you might also consider the option of doing a redirected recovery of the table space to a desired point-in-time. Redirected recovery, which allows you to essentially use the RECOVER utility to create a copy of a table space, was introduced via the fix for APAR PH27043 (that fix came out in October of 2020). Here is a link to documentation on redirected recovery: https://www.ibm.com/docs/en/db2-for-zos/12?topic=recover-running-redirected-recovery.
DeleteRobert
I used the Db2 Admin Tool to try and specify redirected recovery. This resulted in message:
Delete"The target table space differs from the source table space: Tablespace type mismatch."
Should I be able to use redirected recovery when the source tablespace is PBG and the target one PBR?
Hey, Stefan.
DeleteIf you are doing a redirected recovery of a PBG table space, the target table space (the one that will be a copy of the source table space, generated by way of the redirected recovery action) must also be a PBG table space. In the section of the online Db2 for z/OS documentation that I referenced (https://www.ibm.com/docs/en/db2-for-zos/12?topic=recover-running-redirected-recovery), you'll see a "Table 1." In that table, you'll see that for object type "Table space," one of the "Characteristics that must match in the source and target" is "Type and organization: partition-by-growth (PBG), partition-by-range (PBR), partition-by-range with relative page numbering (PBR RPN), LOB, or XML." If there's a mismatch for any of those table space definition specifications, the redirected recovery operation will fail with an error.
Robert
Robert, I am converting a tablespace from LARGE (DSSIZE 4 GB) to DSSIZE 16 GB. The tablespace is a universal partitioned by range one (after converting the index based partitioning table to table partitioning and SEGSIZE 64 instead of 0.
ReplyDeleteI have 64 partitions in the table and the total tablespace size is over 200 GB. I thought I could split up the REORG jobs to reorg by multiple partitions in groups (say 8 parts per sequential job).
That would make the amount of resources needed for sorts for each REORG less than one big REORG. However, IBM stated that since the DSSIZE is specified at the TS level I must do one REORG job for the entire tablespace.
I would need to calculate space and determine if we have those resources. I was hoping I could do the REORGs by partition. Is what IBM stated correct?
The REORGs are done with SHRLEVEL REFERENCE and no updates will occur during the maintenance. The application can still read data while reorg'ing.
Thanks!
Hello, Derek.
DeleteAs an IBMer myself, I can state definitively that, yes, what IBM told you is correct: to change the table space's DSSIZE, you will have to issue the ALTER TABLESPACE statement (to specify the new DSSIZE) and then execute an online REORG of the entire table space to put the change into effect.
That changes with DB2 12 for z/OS, which became generally available a few months ago. In a DB2 12 environment, range-partitioned table spaces (universal PBR or "classic" partitioned - the latter term referring to a non-universal table space for which table-controlled partitioning is in effect) can utilize a new physical database design option called relative page numbering, or RPN. An RPN range-partitioned table space can be created, or an existing range-partitioned table space can be changed to RPN via an ALTER TABLESPACE with PAGENUM RELATIVE, followed by an online REORG of the entire table space. When a range-partitioned table space uses relative page numbering, new and useful options concerning DSSIZE are available. For one thing, DSSIZE for an RPN table space can be specified at the partition level. And, different partitions of a given RPN table space can have different DSSIZE specifications. And, allowable DSSIZE specifications no longer depend on the table space's page size and the number of the partitions associated with the table space. And, a DSSIZE change no longer requires an online REORG to become effective - it is an immediate change (unless the change decreases the DSSIZE value).
You can find more information about RPN table spaces in the IBM redbook titled, "IBM DB2 12 for z/OS Technical Overview." That document can be downloaded from this URL: http://www.redbooks.ibm.com/abstracts/sg248383.html?Open.
Robert
A clarification: an RPN table space must also be a universal table space. That said, if you have a classic range-partitioned table space (in other words, a non-universal table space that uses table-based, as opposed to index-based, range partitioning) then you can alter the table space with a SEGSIZE value (to get it to universal partition-by-range) and alter it again with PAGENUM RELATIVE, and the same online REORG that converts the table space from classic partitioned to universal PBR will also convert the table space to use relative page numbering.
DeleteRobert
Robert, thanks for all the information. how about one more twist: Convert LARGE (1000+ parts) table containing LOB data (inline with overflow) to UTS. REORG base TS only (AUX NO), or must one REORG TS and LOBs (AUX YES) to reset AVREO*?
ReplyDeleteYour question has me a little confused. You appear to be asking about conversion of a non-universal table space to universal, but you indicate that the table space contains a table with in-lined LOB data. That tells me the table space in question is already universal, because LOB in-lining is only possible for a table that is in a universal table space.
DeleteRobert
My mistake, they are not inline. It is two 512K BLOB columns (Graphic images actually).
DeleteIt is not segmented, so not UTS. Big questions is: Conversion requires base table REORG only? or UTS conversion requires full REORG AUX(YES)?
I am not aware of a requirement that LOB table spaces be reorganized when a base range-partitioned table space is reorganized to effect a conversion to universal. You should be fine reorganizing just the base table space.
DeleteRobert
Hi Robert,
ReplyDeleteIf we create LOB tablespace explicitly to maintain shop standards along with base tablespace as PBG, does it create another LOB object automatically when the first one fills up and if yes , does it follow the same standard as we defined for part 1. Please advise.
Regards,
Amit
Good question, Amit. As I recall, it works this way: if Db2 dynamically adds a partition to a PBG table space, and the table in the table space has a LOB column, Db2 will implicitly create the LOB table space and auxiliary table that will be associated with the new partition of the PBG table space. You will not be able to determine the names given by Db2 to the implicitly created LOB table space and auxiliary table. If you want to control those names, you can do that in this way: if the table started out with n partitions, well before partition n fills up and causes Db2 to dynamically add partition n+1, add that partition yourself via ALTER TABLE ADD PARTITION. Explicitly create for that new partition a LOB table space and an auxiliary table, and in so doing provide names that conform to your site's naming standards.
DeleteRobert
Hi Robert, Thank you for the great explanation. I have a question about converting simple(SEGSIZE=0) tablespaces to PBG. Do I need to alter SEGSIZE to some value and first and then do ALTER MAXPARTITIONS? Or can it be done by just one ALTER MAXPARTITIONS statement?(assuming SEGSIZE is defaulted to 32 or something). I could have experimented creating a simple tablespace to convert to PBG, but our V12R1M500 is no more allowing creating simple tablespaces (could be controlled by ZPARM).
ReplyDeleteThank you.
Indra.
Hello, Indra.
DeleteYou can't specify SEGSIZE in an ALTER TABLESPACE statement for a simple table space. To convert a simple table space that holds ONE table to universal partition-by-growth, you alter the table space with a MAXPARTITIONS specification and then online-REORG the table space to accomplish the actual transformation to universal PBG. SEGSIZE for the new universal PBG table space will default to 32 when you convert from a simple table space.
Robert
Thank you very much for spending time and explaining, despite your busy schedule. Real appreciate, Robert.
ReplyDeleteRegards,
Indra
Hello Robert,
ReplyDeleteThanks for this Blog!
We are planning to convert tablespaces to UTS. When we REORG the tablespaces after the ALTER to materialize to UTS, all the dependent packages will become invalid and these need a Rebind. This Rebind may change the accesspath.
Looking for your guidance: how do we save this accesspath before the conversion and eventual fallback to this accesspath if there is a need. Thanks in advance.
For a given package that will be invalidated as a result of materializing the pending DDL change (i.e., the change to a universal type for a table space), prior to the REORG you can extract access path information by rebinding the package with EXPLAIN(ONLY) specified. Information on the EXPLAIN option of REBIND can be found in the IBM Db2 for z/OS Knowledge Center at https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/comref/src/tpc/db2z_bindoptexplain.html.
DeleteRobert
Thank you very much Robert for your input! Should I go with BIND COPY EXPLAIN(ONLY) to a Dummy Collection. In case of any issue then I presume that I can fall back to this accesspath using OPTHINT ?
DeleteYou do not have to BIND COPY into a dummy collection. A REBIND PACKAGE command with the EXPLAIN(ONLY) option will not actually rebind the package - it will just provide EXPLAIN information for the package.
DeleteIf you need to restore the access path for the package, you can do that with OPTHINT, or with the BIND QUERY command and the SYSIBM.SYSQUERYPLAN catalog table (see https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/comref/src/tpc/db2z_cmd_bindquery.html).
Robert
Hello Robert, Thank you very much for your suggestions. It looks like REBIND PACKAGE command with EXPLAIN(ONLY) will create the EXPLAIN info for the package but this accesspath may be different than the the accesspath that is currently in use for a given package. Should I use EXPLAIN PACKAGE COPY CURRENT to generate the EXPLAIN output that is currently in use and I can fall back to this EXPLAIN output if there is any issue. Thanks Again for your inputs.
ReplyDeleteI think that rebinding a package with EXPLAIN(ONLY) and APREUSE(WARN) should provide you with the access paths that were generated when the package was last bound or rebound, assuming that those access paths are still available (as would likely be the case).
DeleteRobert
I have to apologize. In your most recent comment, you noted (correctly) that REBIND PACKAGE with EXPLAIN(ONLY) will cause a new EXPLAIN to be done for statements in the package, and that access plan information generated by that new EXPLAIN could differ from the actual access plan used for the package's statements. You then mentioned the possibility of issuing an EXPLAIN statement with the PACKAGE keyword, and I simply overlooked that. In fact, EXPLAIN PACKAGE will extract from the package the access plan information for the package's statements, as opposed to driving a new EXPLAIN for those statements; so, to get access plan information for statements in the current copy (i.e., the current instance of the package, versus the previous or original instance) of package PACK52604 in collection COLLA, you would issue this statement: EXPLAIN PACKAGE COLLECTION 'COLLA' PACKAGE 'PACK52604' COPY 'CURRENT';
DeleteSorry about not acknowledging EXPLAIN PACKAGE as the mechanism for extracting access plan information from a package, versus driving a new EXPLAIN for those statements (a new EXPLAIN, as noted above, is driven via REBIND PACKAGE with EXPLAIN(ONLY)).
Robert
What is the best way to move multi-tables in a single tablespace to UTS. I saw something regarding DB2 v12 f-Lvl 508. Unfortunately, we are on DB2 v12 f-Lvl 503. I don't send the movetb option.
ReplyDeleteThanks in advance!
I would say that the best approach would be to use the functionality, which you referenced, provided by Db2 12 for z/OS function level 508 for non-disruptive migration of tables from multi-table simple or segmented table spaces to multiple partition-by-growth universal table spaces (more information at https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m508.html). If you can wait to get from function level 503, where you are now, to function level 508, do that.
DeleteIf you must accomplish the movement of the tables in your current environment, the process will be unload, drop, re-create as a table in a universal table space, then re-load the data.
Robert
Hello Robert, What's the best way to convert a simple LOB TSBS1( it has 1 data set) into a UTS ? It has an aux table TAUXLOB1 in an AUX TS TSAUX1 with 160 data sets 4GB.
ReplyDeleteFernando, are you asking about converting a simple table space, which holds a table that has a LOB column, to a universal table space, or are you asking about converting the LOB table space itself to a universal table space? If the latter (converting LOB table space to UTS), that cannot be done because a LOB table space and a universal table space are two different table space types. In other words, an auxiliary table holding LOB data can exist in a LOB table space and only in a LOB table space, and a LOB table space by definition cannot be a universal table space.
DeleteIf you have a simple table space that holds a table that has a LOB column, and if that table is the only table in the simple table space, you can convert the simple table space to a universal PBG table space by altering the table space with a MAXPARTITIONS value and then executing an online REORG of the table space. The resulting PBG table space will maintain the relationship with the existing LOB table space. If the simple table space holds several tables, and the Db2 for z/OS environment is V13, or V12 with function level 508 or higher activated, you can convert the one multi-table simple table space to multiple single-table PBG table spaces using the process described in this blog entry: http://robertsdb2blog.blogspot.com/2022/05/db2-for-zos-online-path-from-multi.html.
Robert
Thanks Robert, sorry for this confusion. Yes, it's a simple TS that holds a table with a LOB column defined in an AUX table which exists into a LOB TS (with 160 data sets 4GB each). My concern is about if we can run an online Reorg with AUX NO trying to improve performance/elapsed time with it?
DeleteThis Online Reorg AUX NO would be part of this UTS conversion for the Simple TS.
DeleteNo can do, Fernando. If you check the description of the AUX option of REORG TABLESPACE in the Db2 for z/OS documentation (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement), you'll see the following: "AUX NO is ignored when the target table space has pending definition changes to convert it from a simple or segmented table space to a partition-by-growth table space. In this case, AUX YES is in effect."
DeleteRobert
Hi Robert,
ReplyDeleteThanks for all the great info.
I've been converting many of our non-UTS partitioned tablespaces to UTS PBR. As a result, I have a lot of partitioned indexes on the tables inside those PBR tablespaces. Is there a way to convert those partitioned indexes to non-partitioned, other than drop/create? Since many of the indexes are unique, recreating the index as non-partitioned would cause locks on the table during the rebuild phase. Our shop is 24x7x365 so that would cause us issues with availability.
Thanks in advance,
Steve
Hello, Steve. Sorry, but there's no magic here. This cannot be done with an ALTER. You're right in that REBUILD INDEX (which you would execute following a CREATE INDEX with DEFER YES), for a unique index, even with SHRLEVEL CHANGE, will not allow changes to table data (INSERT, DELETE, UPDATE of a column of the index) that would result in index changes, because the index can maintain uniqueness of the key only when the index is not changed during the rebuild.
ReplyDeleteCould you pull off this change while having only a very brief period of no-data-change activity for the underlying table? Yes, but it will be a somewhat involved process. Basically, you'd need to create a mirror of the table, with the desired non-partitioned index. That would require unload and load of the data into the mirror table, and then data change propagation (perhaps via a data replication tool, perhaps via a log analysis tool) to get the two tables (original and mirror) very close to in-sync, then a brief no-update period to make the synchronization 100%, then RENAME of the "original" table and RENAME of the mirror table (to give it the name that had belonged to the "original" table), followed by REBIND of the packages invalidated by the RENAME of the former "original" table. Again, rather involved, but not impossible - this procedure has been used by multiple organizations to effect changes to Db2 objects that cannot be effected via ALTER.
Robert
Robert, Thank you for the quick reply. That is unfortunate but like you said there are ways around it. We have a process similar to what you describe for some of our more invasive table changes.
DeleteNow I just have to decide if it would be worth the effort to change some or all of the indexes from partitioned to non-partitioned.
Thanks again!
Steve
In that regard, Steve, I think it may depend on whether queries accessing a range-partitioned table on which a partitioned index is defined tend to reference the table's partitioning key in query predicates. If queries tend not to reference the partitioning key in predicates, the partitioned index may not be helping query performance in any significant way, because in that case Db2 can't determine which partitions of the index could contain entries related to result set rows. That's a situation that might have you leaning towards replacing the partitioned index with a non-partitioned index.
DeleteRobert
Ah that is a very good point thank you.
DeleteRob,Can we drop a particular part alone in a pbr table .I have a pbr table with 25 parts.I want to drop/delete last 3 parts.
ReplyDeleteKnown requirement, but nt do-able at this time. The closest we can come to that now is the ROTATE PARTITION FIRST TO LAST option of ALTER TABLE, which at least lets you re-purpose an emptied-out partition from the "front" of the set of partitions by moving it (logically speaking) to the back of the set of partitions.
ReplyDeleteRobert
Not do able at this time -means we dont have the feature/option to do drop parts or is it not applicable in this case? I added parition (before seeing your response) instead of rotating.But first 3 parts are not removed.So per your statement, first three (oldest part) cant be dropped right?
ReplyDeleteIt means that this functionality is not present in Db2 for z/OS at this time. Partitions can be added to a table. Partitions cannot be dropped from a range-partitioned table.
DeleteRobert
Gotcha.Thankyou
DeleteHi,I faced below error in pbr table.Per resource type 00002008- Table space partition write-claim class DB.SP.PT .third qulifier is parition.
ReplyDeleteIts very strange to have 0000000-we dont have partition 0 ,we only have part from 1.Then why does it show
DSNT408I SQLCODE = -913, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY
DEADLOCK OR TIMEOUT. REASON CODE 00C900BA, TYPE OF RESOURCE
00002008, AND RESOURCE NAME DB.TS.00000000
Is it new feature?
You may need to open a case with IBM Support to get a definitive answer to this question, but I believe that partition 0 in this case may refer to an attempt to drain the write-claim class for ALL of the partitions of the range-partitioned table space.
ReplyDeleteRobert
Sure
DeleteHello,I ran an alter for an tablespace to increase maxpartition.I got sqlcode -666 stating "alter cannot be executed as an utlity is in progress".There was NO active utility in system .There was only few stopped utility(which is an copy utility on whole database level).Out of guess, I terminated the stopped utility and my alter statment ran fine.Sqlcode message states it is due to utility in progress,I'm wondering how can an stopped utility can be an cause of my alter statement execution.
ReplyDeleteRefer to this page of the online Db2 documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=utilities-monitoring. You will see the following in the description of a utility that is in the stopped state (with capital lettering added by me for emphasis): "The utility abnormally stopped before completion, BUT THE TABLE SPACES AND INDEXES THAT WERE ACCESSED BY THE UTILITY REMAIN UNDER UTILITY CONTROL." To release the resources still under the control of the utility in the stopped state, you terminate the utility, as you did with successful results.
DeleteRobert
The utility I terminated was an Image copy on whole database ,which has some 20 plus tables.The alter that I executed was one among tablespace in the database.Copy utility would already completed few in the database before failure or it need not have necessarily failed on the table that I was working on right? In this case,does the stopped utility cause such failure(sqlcode -666) for any tables that user access under the database(as copy ran on database level)
Deleteor
does the stopped utility cause such failure(sqlcode -666) only for the table that was the reason for an abnormal termination of copy utility ?
My assumption is that, in the event of a COPY utility being in a stopped state, it will still have control over resources on which it was operating when it went into the stopped state. Note, however, that the utility will have an S lock on the database descriptor (DBD) associated with an object on which it is operating. That S-lock would prevent execution of DDL statements that would affect the DBD (see Table 1 on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=locks-objects-that-are-subject).
DeleteRobert
Will this always lock database even if the utility is operating on table level/index/tablespace level or only if utility is operating on database level will this go for DBD lock.Does the DBD lock mean it will lock entire database on specified conditions on table 1?
DeleteDoesn't matter if the utility is operating on an individual object or on all objects associated with a given Db2 for z/OS database: a utility will get an S-lock on the relevant DBD (as indicated in the notes for Table 1 on the referenced page of the online documentation, that could be an X-lock on the DBD for the SWITCH phase of a LOAD or REORG). That S-lock on the DBD will block any process that requires an X-lock on the DBD (read: DDL).
DeleteRobert
Good to know
Delete