tag:blogger.com,1999:blog-4516533711330247058.post4426113400820207266..comments2024-03-28T07:32:09.246-07:00Comments on Robert's Db2 blog: DB2 for z/OS: Getting to Universal Table SpacesRoberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger80125tag:blogger.com,1999:blog-4516533711330247058.post-90934918188958765732024-03-26T15:11:51.426-07:002024-03-26T15:11:51.426-07:00Doesn't matter if the utility is operating on ...Doesn'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).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-37472096464003398002024-03-26T10:51:33.273-07:002024-03-26T10:51:33.273-07:00Will this always lock database even if the utility...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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-27634968341543615692024-03-22T12:24:08.524-07:002024-03-22T12:24:08.524-07:00My assumption is that, in the event of a COPY util...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).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-49475545988479540172024-03-22T11:48:47.918-07:002024-03-22T11:48:47.918-07:00The utility I terminated was an Image copy on whol...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) <br />or <br />does the stopped utility cause such failure(sqlcode -666) only for the table that was the reason for an abnormal termination of copy utility ?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-34703810898084409932024-03-21T14:42:48.602-07:002024-03-21T14:42:48.602-07:00Refer to this page of the online Db2 documentation...Refer 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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-13039910336770063262024-03-21T10:20:31.042-07:002024-03-21T10:20:31.042-07:00Hello,I ran an alter for an tablespace to increase...Hello,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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-29225240990913643152023-12-26T06:39:30.891-08:002023-12-26T06:39:30.891-08:00SureSureAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-11640544296129752382023-12-20T09:16:05.144-08:002023-12-20T09:16:05.144-08:00You may need to open a case with IBM Support to ge...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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-68892166968494294582023-12-20T01:39:51.650-08:002023-12-20T01:39:51.650-08:00Hi,I faced below error in pbr table.Per resource t...Hi,I faced below error in pbr table.Per resource type 00002008- Table space partition write-claim class DB.SP.PT .third qulifier is parition.<br />Its very strange to have 0000000-we dont have partition 0 ,we only have part from 1.Then why does it show <br />DSNT408I SQLCODE = -913, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY <br /> DEADLOCK OR TIMEOUT. REASON CODE 00C900BA, TYPE OF RESOURCE<br />00002008, AND RESOURCE NAME DB.TS.00000000 <br />Is it new feature?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-13800250710350168662023-11-29T01:58:29.510-08:002023-11-29T01:58:29.510-08:00Gotcha.ThankyouGotcha.ThankyouAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-90351343320953851032023-11-07T08:26:40.213-08:002023-11-07T08:26:40.213-08:00It means that this functionality is not present in...It 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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-60037819786224682152023-11-07T01:21:51.039-08:002023-11-07T01:21:51.039-08:00Not do able at this time -means we dont have the f...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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-83190082429755480962023-11-05T15:38:19.177-08:002023-11-05T15:38:19.177-08:00Known requirement, but nt do-able at this time. Th...Known 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.<br /><br />Robert Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-3521090694328215082023-11-04T06:34:03.268-07:002023-11-04T06:34:03.268-07:00Rob,Can we drop a particular part alone in a pbr t...Rob,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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-87966716075944556772023-05-25T06:01:19.704-07:002023-05-25T06:01:19.704-07:00Ah that is a very good point thank you. Ah that is a very good point thank you. Stevenoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-5981459566069174252023-05-23T18:27:31.718-07:002023-05-23T18:27:31.718-07:00In that regard, Steve, I think it may depend on wh...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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-59389156551229359812023-05-23T14:03:19.171-07:002023-05-23T14:03:19.171-07:00Robert, Thank you for the quick reply. That is un...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.<br />Now 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.<br />Thanks again!<br />SteveStevenoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-90057903395199731932023-05-23T07:00:24.882-07:002023-05-23T07:00:24.882-07:00Hello, Steve. Sorry, but there's no magic here...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.<br /><br />Could 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.<br /><br />Robert Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-15146477528958249102023-05-22T10:55:27.118-07:002023-05-22T10:55:27.118-07:00Hi Robert,
Thanks for all the great info.
I've...Hi Robert,<br />Thanks for all the great info.<br />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.<br />Thanks in advance,<br />SteveStevenoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-70731126551206386932022-06-26T20:25:44.195-07:002022-06-26T20:25:44.195-07:00No can do, Fernando. If you check the description ...No 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."<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-60435009215418197672022-06-24T16:04:16.913-07:002022-06-24T16:04:16.913-07:00This Online Reorg AUX NO would be part of this UTS...This Online Reorg AUX NO would be part of this UTS conversion for the Simple TS.Fernandonoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-43908085718773385652022-06-24T15:55:41.419-07:002022-06-24T15:55:41.419-07:00Thanks Robert, sorry for this confusion. Yes, it&#...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? Fernandonoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-14900970892863301922022-06-17T14:25:39.635-07:002022-06-17T14:25:39.635-07:00Fernando, are you asking about converting a simple...Fernando, 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.<br /><br />If 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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-22155808359564740252022-06-17T07:49:23.300-07:002022-06-17T07:49:23.300-07:00Hello Robert, What's the best way to convert a...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. Fernandonoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-4516548179975343432022-03-31T14:29:55.295-07:002022-03-31T14:29:55.295-07:00Hey, Stefan.
If you are doing a redirected recove...Hey, Stefan.<br /><br />If 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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.com