Sunday, March 26, 2017

DB2 for z/OS: Running REORG to Reclaim Disk Space

Think of why you run the DB2 for z/OS REORG utility, and a number of reasons are likely to come quickly to mind: to restore row order per a table's clustering key; to reestablish free space (for inserts and/or for updates); to remove the AREO* status set for a table space following (for example) an ALTER TABLE ADD COLUMN operation; or to materialize a pending DDL change such as an enlargement of a table space's DSSIZE. How about disk space reclamation? If that REORG motivation has not previously occurred to you, perhaps it should.

Recently, a DBA at a large DB2 for z/OS site communicated to me the success that his team has had in reclaiming substantial amounts of disk space through online reorganization of certain table spaces. He also asked for a recommendation with regard to identifying table spaces for which a REORG could potentially deliver significantly reduced disk space consumption. In this blog entry, I'll describe the disk space reclamation scenario reported by the referenced DBA, I'll explain why there was space to be reclaimed in some of the table spaces administered by the DBA, and I'll provide the "reclamation indicator" metric that I suggested to the DBA as a means of identifying table spaces that could be reorganized in order to free up disk space.

First, the scenario. At the DBA's site, there are some tables, in segmented table spaces ("traditional" segmented table spaces, as opposed to universal table spaces, which also happen to be segmented), that have these key characteristics: they are clustered by a continuously-ascending key (so that "new" rows go to the "end" of the table), and the number of inserts into the table is roughly equaled by the number of rows that are deleted from the table over a period of time.

The DB2 DBA knew that for table spaces with the above-described characteristics, REORGs were not needed to maintain "clusteredness," because of the continuously-ascending clustering key that sent new rows to the end of the table (at least, clustering would remain in good shape until the table space reached its size limit -- more on this in a moment). For the same reason, free space for inserts in "interior" pages of the table space was not a concern. Still, with DB2 real-time statistics showing a very large number of inserts since the last REORG of a couple of these table spaces, the DBA determined that reorganizations might be in order. Online REORGs of the table spaces were executed, and the result was a freeing up of 64 GB of disk space: one table space went from 21 to 4 data sets of 2 GB apiece, and the other went from 17 data sets to 2 (a DB2 segmented table space is comprised of up to 32 data sets of 2 GB apiece, and that is why its size limit is 64 GB).

Why was there so much unused space in these table spaces? Because the continuously-ascending clustering key kept pushing the "end" of the table spaces "outward." Why would that happen? Why would DB2 grow these table spaces as a result of inserts, given the like number of row-delete operations that were targeting the associated tables? Shouldn't DB2 have been using the space freed up by deletes to accommodate new inserts, without growing the table space's size? Actually, DB2 was working as designed. It's true that, given a continuously-ascending clustering key and some deletes of older rows from the "front" of a table space, DB2 can "wrap" to the front and start inserting new rows in space cleared by deletes, but that will only happen if DB2 cannot extend the table space (i.e., if DB2 cannot make the table space larger). If DB2 can extend a segmented table space, it will in order to preserve a table's row-clustering order; so, in advance of hitting the 64 GB size limit for a segmented table space, DB2 would keep making the table space larger so that it could keep adding rows to the end of a table (assuming a continuously-ascending clustering key), and deletes of older rows would result in ever-larger amounts of available-but-unused space in the table space. That's why the disk footprint of the two table spaces became so much smaller following reorganization.

[It is important to keep in mind that, given a continuously-ascending clustering key and at least some row-delete operations, DB2 will insert new rows in the "front" of a segmented table space, using space freed up by DELETEs, if the table space cannot be made any larger (either because of reaching the 64 GB limit or as a result of running into a maximum-extents or a maximum-volumes situation). In that case, "wrapping to the front" for new inserts is better than failing the inserts.]

Having achieved this disk space reclamation success through REORGs, the aforementioned DBA wanted to add "potential for significant disk space reclamation" to the criteria used at his site for identifying table spaces that should be reorganized (a good proactice -- REORG table spaces when you have a good reason for doing so, not just "because it's been X amount of time since the last time this table space was REORGed"). How could he and his colleagues spot table spaces with large amounts of unused space? My recommendation: use for this purpose the ratio of disk space occupied by the table space to space in the table space occupied by rows in the table space. For the numerator, I'd use the SPACE value in the row for the table space in the SYSTABLESPACE catalog table. That value is updated when the STOSPACE utility is executed, so you would want to run STOSPACE on a regular basis (that should not be a big deal -- STOSPACE should be a very inexpensive utility to execute, CPU-wise). For the denominator, I would use the product of TOTALROWS from SYSTABLESPACESTATS (set by REORG and updated when INSERTs and DELETEs are executed) and AVGROWLEN in SYSTABLESPACE (updated by RUNSTATS, or by in-line statistics collected during REORG or LOAD). You can decide when that ratio would prompt you to run REORG to reclaim space. Would you do that when disk-space-to-row-space hits 2 (i.e., when the size of the table space is 2X the space occupied by rows)? When it hits 3? When it hits 4? One of those values might be reasonable for your environment.

One more thing: I have focused on traditional segmented table spaces in this blog entry because that is the table space type to which space reclamation via REORG is most relevant. For a range-partitioned table space, a given partition's size limit is determined by the DSSIZE specification, and the same is true for a partition-by-growth table space. Yes, you could see a partition-by-growth table space come to contain a high percentage of unused space given the combination of a continuously-ascending clustering key and a good deal of DELETE activity, but you could put a limit on that growth by way of a not-larger-than-needed MAXPARTITIONS value. With that said, even with range-partitioned and partition-by-growth table spaces you could see situations in which the ratio of table space size to space occupied by rows (the ratio described in the preceding paragraph) could get to be high enough to make a REORG attractive from a disk space reclamation perspective. And here there's some good news: starting with DB2 11 for z/OS, you can tell DB2 to drop partitions of a partition-by-growth table space made empty by a REORG or the entire table space (that functionality is enabled via the REORG_DROP_PBG_PARTS parameter in ZPARM).

So, add disk space reclamation to your reasons for running REORG (if you have not already done so), and consider using the ratio I've provided to look for candidate table spaces.

73 comments:

  1. Why TOTALROWS*AVGROWLEN, why not DATASIZE?

    Michael Harper, TD Bank

    ReplyDelete
    Replies
    1. Great question, Michael. The reason: as I understand it, DATASIZE is NOT updated as a result of SQL data-change operations (UPDATE, INSERT, DELETE). DATASIZE (in SYSIBM.SYSTABLESPACESTATS) is updated as a result of executing REORG or LOAD. That being the case, DATASIZE only tells you how much space was occupied by rows the last time the table space was REORGed or LOADed. If there have been a lot of INSERTs since that time, DATASZE under-reports the amount of space occupied by rows.


      The effect of utilities, and of SQL statements, on the DB2 real-time statistics fields is documented in the DB2 for z/OS "Managing Performance" manual. Also online in the DB2 for z/OS Knowledge Center, of course (searching there for DATASIZE will bring up, among the "hits," to section on utility and SQL impact on real-time stats fields).

      Robert

      Delete
  2. Hi Robert, excuse me if this is not the right blog post.
    Being in V12 we noticed that after migrating to v12 when inserting records,
    the allocated space of the tablespace increases considerably,
    also extending them and decreasing substantially the Pct of space used by ACTIVE tables.
    when we reorganize it returns to the original sizes.
    Before with v11 it did not happen.
    The tablespace is a UTS PBR

    Why could this behavior have changed?

    I attach the history
    Note that from the migration of db2 to v12 in 2018-11-23
    changes the increment of allocate space while increasing the number of rows
    The first reorg turned to RRF that's why the change in allocacion

    Part Date/Time of Update Space (KB) Rows Pct Ac Pct Dp Exts
    ---- ------------------- ----------- ----------- ------ ------ ------
    2 2019-02-10-20.29.11 1617504 384299 0 0 48
    2 2019-02-03-14.12.22 1357776 375146 0 0 46
    2 2019-01-27-18.18.54 1065456 368624 1 0 37
    2 2019-01-20-16.49.38 809088 363353 1 0 32
    2 2019-01-13-15.01.41 510720 357345 2 0 24
    2 2019-01-06-13.31.03 311136 347015 3 0 19
    2 2018-12-30-18.53.03 184464 339751 5 0 15
    2 2018-12-23-16.09.50 24192 334111 43 0 6
    2 2018-12-16-17.21.36 762048 326013 1 0 35
    2 2018-12-09-17.51.33 510720 317613 2 0 29
    2 2018-12-02-18.38.16 311136 306212 3 0 20
    2 2018-11-25-17.57.04 38304 301272 25 0 7
    2 2018-11-18-15.35.31 13104 295903 73 0 3
    2 2018-11-11-17.14.28 13104 287465 71 0 3
    2 2018-11-04-16.22.33 13104 277529 87 0 3
    2 2018-10-28-17.23.37 13104 269506 67 0 5
    2 2018-10-21-16.15.35 13104 263974 65 0 5
    2 2018-10-14-17.58.13 13104 258109 64 0 5
    2 2018-10-07-16.02.30 13104 248715 87 0 5
    2 2018-09-30-14.17.34 13104 238653 59 0 3
    2 2018-09-23-16.06.47 8736 232407 87 0 3
    2 2018-09-16-14.44.28 8736 226012 84 0 3


    Thanks,
    Elvio

    ReplyDelete
    Replies
    1. If the only thing that changed about the table and table space (besides changing from BRF to RRF) is the change in Db2 for z/OS Version (Version 11 to Version 12), I do not know why the size of the table space increased so dramatically. You might need to open a ticket with the IBM Support Center to get this matter resolved.

      Robert

      Delete

  3. Thanks Robert, I'll deal with the problem with Ibm Support Center.

    ReplyDelete
  4. Rob,I ran reorg which has list of indexes using listdef,the job failed as there were claimers .I saw the display claimers results in the sysprint.Does the reorg list the cliamers results,in which phase? I have never seen display db(*) claimers messages in any reorg job failures.Any idea?

    ReplyDelete
    Replies
    1. This was an enhancement for REORG that was introduced with Db2 12 for z/OS. The message should be issued as part of the SWITCH phase of an online REORG, if REORG cannot get the drain lock on the "original" data sets (referring to the data sets of the table space or partitions and the indexes processed by the utility) that is required before REORG can switch to the shadow data sets containing the reorganized data. For the drain to be obtained, all processes with claims on original data sets must release those claims by committing. Sometimes, a process with a claim on a data set related to an online REORG will not commit in a timely manner, and the online REORG fails because it can't get the drain lock required for the switch at the end of utility processing. Displaying, in utility output, the claimers that caused the drain to fail helps you to do something to keep that from happening in the future (for example, if application XYZ held a claim that caused the drain to fail, you can work with the application XYZ team to determine why the application didn't commit in a timely manner, and the team can then take action to increase the application's commit frequency).

      You can read more about this Db2 12 enhancement in section 11.3.6 of the IBM redbook titled, "Db2 12 for z/OS Technical Overview." That redbook can be downloaded from this URL: https://www.redbooks.ibm.com/abstracts/sg248383.html

      Robert

      Delete
  5. Hi Robert,I have scheduled reorg job which fails whenever there are claimers on the object.Is there a way to skip this error?

    ReplyDelete
    Replies
    1. There is nothing that can be "skipped" here. Prior to going into the SWITCH phase (the last phase of online REORG, aside from the end-of-utility clean-up phase), which makes the shadow data sets the new "original" data sets for the table space being REORGed, the utility has to drain the current "original" data sets. The drain cannot succeed if a process with a claim on the page set or partition being REORGed does not release its claim (any process that accesses a Db2 for z/OS database object gets a read or a write claim on that object). Claims are released when a process commits, so if your online REORG job is failing because the required drain action cannot complete, it is likely because one or more processes accessing the database object in question are not committing in a timely manner. In that case, you have a couple of options. One option would be to communicate with the appropriate people (maybe an application development team) about the process (or processes) accessing the database object that appear to not be committing regularly (the output of a failed online REORG job should include information identifying the claimers that prevented the pre-SWITCH drain from succeeding). See if you can get those people to make the process in question commit more frequently.

      The other option is to include a FORCE specification in the REORG utility control statement. You could go with FORCE READERS if the claims that are not being released are read claims; otherwise, FORCE ALL can be used if at least some of the claim-holding processes hold write claims on the database object. FORCE will basically do what a -CANCEL THREAD command would do if issued for the claim-holding processes. This could upset someone whose process gets cancelled. You'd have to decide if enabling the REORG to complete successfully is worth that.

      Robert

      Delete
    2. Thanks for wonderful explanation!! Helpful

      Delete
  6. Hi Robert,
    When I run reorg on a database level(it has lob tablespaces),it tries to reorg lob's along with base tablespace and as it proceeds through the other tablespaces under database,when it comes to actual lob tablespaces in the databases which is already been reorged along with base earlier it fails stating ic dataset already exist.How do I bypass this error?

    ReplyDelete
    Replies
    1. What is the error message (likely begins with DSNU) generated when the REORG fails? And, what does the REORG utility control statement look like?

      Robert

      Delete
    2. LISTDEF LISTD INCLUDE TABLESPACE dbname.*
      REORG TABLESPACE LIST LISTD
      COPYDDN COPYDSN
      TIMEOUT TERM
      MAXRO 5 DRAIN_WAIT 15 RETRY 10 RETRY_DELAY 5
      SHRLEVEL CHANGE FASTSWITCH YES


      DSNU407I 199 07:00:41.63 DSNUBCKB - DATA SET ALREADY USED FOR PREVIOUS IMAGE COPY
      DSNU408I 199 07:00:41.63 DSNUBCKB -"filename"
      VOLUME=(CATG)
      FILE SEQUENCE NUMBER=1

      Delete
    3. the dataset(lob ts) which it states as already ic'd is taken part of same job (when it reorg's base tabespaces) in the initial list

      Delete
    4. My guess is that there is a table space in the database that will cause the value of the AUX option of REORG TABLESPACE to default to YES (e.g., a partition-by-growth base table space with one or more LOB columns, where the table space has a MAXPARTITIONS value that is greater than one - see the description of the AUX option on this page in the online documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=tablespace-syntax-options-reorg-control-statement). If that is the case then the default of AUX YES will cause the LOB table space to be processed along with the base table space, and if the LOB table space is subsequently processed on its own you'll get the error that you saw.

      What to do about this? A couple of possibilities come to mind: you could try an explicit specification of AUX NO in the REORG utility control statement; alternatively, you could use a TEMPLATE to name the image copy data sets, with a specification that will ensure a unique name for the second image copy generated for the one LOB table space.

      Robert

      Delete
    5. I have coded two different templates(lob & base) but how do I point them to use lob's & base in same reorg card.should we code same templates in reorg card & will that take appropriate templates automatically for lob 's and base?
      template copydsn ' '
      template lobdsn ' '
      LISTDEF LISTD INCLUDE TABLESPACE dbname.*
      REORG TABLESPACE LIST LISTD
      COPYDDN COPYDSN----------
      TIMEOUT TERM
      MAXRO 5 DRAIN_WAIT 15 RETRY 10 RETRY_DELAY 5
      SHRLEVEL CHANGE FASTSWITCH YES

      Delete
    6. The way I see it, it's not a matter of having one template for base table spaces and one for LOB table spaces. The problem appears to me to be caused by the same LOB table space being operated on twice by this REORG job. The LOB table space is likely first reorganized and backed up because of an implicit AUX YES situation, as I mentioned previously. That same LOB table space is again operated on by the REORG just because it is in the database in which all table spaces are being REORG-ed. When the LOB table space is operated on again, REORG tries to back it up again, and it tries to use the same name for that backup data set that it used the first time it backed up the LOB table space, and that (I believe) is what causes the error.

      As I mentioned previously, one solution is to keep the REORG from operating on the same LOB table space twice. I believe that can be accomplished by adding AUX NO to the options specified for the REORG job.

      If you would prefer to have the same LOB table space operated on twice by the REORG job (not sure why you would want that), you'll need to ensure that the data set for the second backup of the one LOB table space has a name different from the data set used the first time the one LOB table space was backed up. You could consider using &UNIQ. in the TEMPLATE-generated image copy data set name to achieve name-uniqueness (you can read about &UNIQ. and other TEMPLATE variables in the Db2 for z/OS online documentation, at https://www.ibm.com/docs/en/db2-for-zos/12?topic=template-syntax-options-control-statement.

      Again, my preference would be to prevent the REORG job from operating on one LOB table space more than once.

      Also, consider whether you should even run database-level REORGs. That could result in many objects being REORGed that do not need to be REORGed, and that is not an efficient use of CPU and disk resources.

      Robert

      Delete
    7. Excellent!! thanks for all your help .Appreciate your guidance/help that you are providing via this site.

      Delete
    8. Well, not as "excellent" as I should have been. I just realized that I got wires crossed in my brain regarding the AUX option of the REORG utility. I mentioned an explicit specification of AUX NO for the REORG to avoid the problem you encountered. Bad suggestion. When a table with a LOB column is in a multi-partition PBG table space (or even a single-partition PBG table space that has a MAXPARTITIONS value that is greater than 1), and that table space is REORGed, the REORG utility MUST execute as though AUX YES had been specified, because in that case the REORG could cause a row to go from one partition of the PBG table space to another, and because each partition has its own associated LOB table space (or LOB table spaces, if the table in question has multiple LOB columns), if that happened and the associated LOB table spaces were not also reorganized then there would be inconsistencies between the PBG table space and its associated LOB table space. Can't have that. Thus forced AUX YES behavior in that case.

      What I SHOULD have told you: consider doing two things in combination: 1) add an explicit AUX YES to your database-level REORG job, and 2) in your LISTDEF control statement add BASE to your INCLUDE specification. That BASE should result in the list of to-be-REORGed objects being limited to base table spaces, and the AUX YES in the REORG job should ensure that any LOB table spaces are reorganized along with their associated base table spaces. That should prevent the REORG from acting twice on a single LOB table space.

      Sorry about that earlier brain mix-up on my part.

      Robert

      Delete
    9. No worries Rob,thanks always.Also on your earlier suggestion-to consider whether you should even run database-level REORGs. That could result in many objects being REORGed that do not need to be REORGed, and that is not an efficient use of CPU and disk resources.

      I want to reorg all tabpesapces in a database(this db has alsmost 170 ts's) in this case,as it was hard for me to explicitly mention all those 170 tablespaces and manually coding them into multiple jobs, I chose to reorg on db level.Is there any other way you meant?

      Delete
    10. Also, to add above statement.I even thought about excluding the lob's from listdef but when the list of ts's under the database were 170,I couldnt manually check each & every tablespace's to know which is lob & which is not, to filter & exclude them from the list.And thats why I thught it is ok to even reorg'ing(lob's) twice if it happens.

      Delete
    11. With regard to your first question (selecting table spaces that actually need reorganizing), that can be done in a couple of ways. One is to use the Db2-provided stored procedure DSNACCOX (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=db2-dsnaccox). Another option is to use a Db2 utility automation tool, such as IBM's Db2 Automation Tool for z/OS (see https://www.ibm.com/docs/en/db2-autotool/4.3.0?topic=db2-automation-tool-overview). If your organization is interested in that tool, your IBM representative can provide additional information.

      As for excluding LOB table spaces from a LISTDEF list, that is what the BASE option of INCLUDE is for. Read about the effect of a BASE specification here: https://www.ibm.com/docs/en/db2-for-zos/12?topic=listdef-syntax-options-control-statement.

      Robert

      Delete
    12. Thanks Rob.When I know in advance if the particular database that I'm gonna reorg has lob's in it ,then I can consider giving base option. Else we normally use plain reorg card.(reorg tablespace dbname.tsname/dbname.*).Since I ended up in this error(I came to know there are few lob's ts in this db),I used this base keyword & now it ran fine.Now I'm thinking if we use reorg card with base keyword in it for all cases,even if db has lob 's ts 's or not ,thats gonna run fine and no harm in it(I guess).So that there is no pain in checking if database has lob's or not ,to come to a decision of using base keyword before running reorg.

      Delete
    13. BASE is not a REORG option - it is a LISTDEF option (see the information about LISTDEF options at https://www.ibm.com/docs/en/db2-for-zos/12?topic=listdef-syntax-options-control-statement).

      Making BASE a standard option of the LISTDEF you use for REORGs should be fine as long as you make AUX YES a part of your REORG utility control statement (see information about REORG options such as AUX at https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement).

      BASE in the LISTDEF control statement will exclude LOB table spaces from the list. AUX YES in the REORG control statement will cause REORG to operate on LOB table spaces associated with base table spaces. The end result should be that a given LOB table space will be operated on by REORG only once.

      Robert

      Delete
    14. Hello sir,I read your explanation above for lob's processed in reorg.one of my tablespace has lob's in it with maxpartition 20 .When I ran reorg for this tablespace it failed with REASON=X'0513' initially ,but when I specified AUX NO it ran fine.How is that possible? Per you statement above , AUX YES is default and it cant be overidden(even if we specify aux no) as it is pbg with maxpart > 1

      Delete
    15. If you execute REORG for a PBG table space that has a MAXPARTITIONS value greater than 1 and holds a table with one or more LOB columns, AUX YES will be in effect IF YOU DO NOT SPECIFY THE AUX OPTION. If you do specify AUX NO then AUX NO will be in effect (other than for a few exception situations that are documented - see the description of the AUX option on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement).

      Robert

      Delete
    16. When a table with a LOB column is in a multi-partition PBG table space (or even a single-partition PBG table space that has a MAXPARTITIONS value that is greater than 1), and that table space is REORGed, the REORG utility MUST execute as though AUX YES had been specified, because in that case the REORG could cause a row to go from one partition of the PBG table space to another, and because each partition has its own associated LOB table space (or LOB table spaces, if the table in question has multiple LOB columns), if that happened and the associated LOB table spaces were not also reorganized then there would be inconsistencies between the PBG table space and its associated LOB table space. Can't have that. Thus forced AUX YES behavior in that case.I thought this makes more sense.so i was convinced that aux yes is forced behavior for all pbg ts with lob's?

      Delete
    17. I mentioned an explicit specification of AUX NO for the REORG to avoid the problem you encountered. Bad suggestion. When a table with a LOB column is in a multi-partition PBG table space (or even a single-partition PBG table space that has a MAXPARTITIONS value that is greater than 1), and that table space is REORGed, the REORG utility MUST execute as though AUX YES had been specified, because in that case the REORG could cause a row to go from one partition of the PBG table space to another, and because each partition has its own associated LOB table space (or LOB table spaces, if the table in question has multiple LOB columns), if that happened and the associated LOB table spaces were not also reorganized then there would be inconsistencies between the PBG table space and its associated LOB table space. Can't have that. Thus forced AUX YES behavior in that case.
      So even if i include aux no-aux yes is default behavior for maxpart >1 pbg,your statement makes sense.and thats why i thought aux no should be ignored .but it was not.ibm manual states-aux no will be overiden only when there are pending chnages .so im confused

      Delete
    18. AUX YES is not forced when you are REORG-ing a PBG table space that has one or more LOB columns and has a MAXPARTITIONS value greater than one. As previously stated, please review the description of the AUX option on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement. On that page, under the descripton of AUX YES, you will see this sentence: "If the AUX keyword is omitted, in the following cases, AUX YES is the default." One of the "following cases" is, "REORG TABLESPACE of a partition-by-growth base table space with one or more LOB columns, where the table space has a MAXPARTITIONS value that is greater than one." Just because AUX YES is the default in that case when the AUX option is not specified in the REORG utility control statement does not mean that AUX YES behavior is forced.

      Also, it is not true that a full-table space REORG of a PBG table space can cause rows to go from one partition to another when the table in question has one or more LOB columns. Refer to this page in the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-reorganization-partition-by-growth-table-spaces. On that page you will see this sentence: "If the partition-by-growth table space contains LOB columns, REORG TABLESPACE minimizes partitions by eliminating existing holes, but does not move the data from one partition to another."

      Robert

      Delete
  7. Hi Robert... regarding to lob table spaces... does DB2 insert new rows using space cleared by deletes as it does with UTS ?

    ReplyDelete
    Replies
    1. Essentially, "yes." The pages in a LOB table space used for storing a LOB value are "deallocated" when the LOB value is deleted, which is to say that they are marked as being available for use for storing some other LOB value.

      Robert

      Delete
  8. Hello,I ran into below issue during reorg
    DSNT500I 270 01:46:21.45 DSNUGBAC - RESOURCE UNAVAILABLE
    REASON 00C200E1
    TYPE 00000220
    NAME DBxxx.DSNDBC.xxx.xxxx.I0001.A001
    DSNU017I 270 01:46:21.45 DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION
    CAUSE=X'00D70100'
    The log gives me below info.What does that mean ?
    IEC161I 069(00000008,0000271C)-162,DBxxDBM1,IEFPROC DB2UDBM1, 508
    IEC161I A0022243,,,DBxx.DSNDBC.xxx.xxx.I0001.A001,,
    IEC161I CATALOG.DEVDB2A
    IEC161I 069(00000008,0000271C)-162,DBxxDBM1,IEFPROC DB2UDBM1, 638
    IEC161I A0022246,,,DBxx.DSNDBC.xxx.xx.I0001.A001,,
    IEC161I CATALOG.DEVDB2A
    reorg card:
    REORG TABLESPACE LIST tsx
    LOG NO
    NOSYSREC
    SORTDATA NO
    SORTDEVT SYSDA
    STATISTICS TABLE(ALL) INDEX(ALL)
    COPYDDN COPYTAP
    TIMEOUT TERM
    MAXRO 5 DRAIN_WAIT 15 RETRY 10 RETRY_DELAY 5
    UNLDDN SYSREC
    SHRLEVEL CHANGE FASTSWITCH YES

    ReplyDelete
  9. I encountered ABENDU0046 in a reorg.I have included sysut1,syserr,sysrec dd cards(with templates & listdef).
    DSNU1038I 281 03:42:21.45 DSNUGDYN - DATASET ALLOCATED. TEMPLATE=COPYDSN
    DDNAME=SYS000xx, FILE SEQUENCE=0001
    DSN=xxxxx
    DSNU2904I 281 03:42:21.46 DSNURPCT - DATA RECORDS WILL BE UNLOADED VIA TABLE SPACE SCAN FROM TABLESPACE
    xxxxxx
    DSNU3340I 281 03:42:21.46 DSNUGSRT - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE
    DSNU016I 281 03:42:39.92 DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'0000'
    I tried having region =0m but nothing worked.

    ReplyDelete
    Replies
    1. Sorry, I can't help with that situation. You might need to open a case with IBM Support.

      Robert

      Delete
  10. Hello,I get informational copy pending messages for the indexes in reorg job output.But the tablespaces in which the index is defined ,is defined as 'logged' attribute.Then why does it place in informational copy pending?

    ReplyDelete
    Replies
    1. Perhaps the indexes are defined with COPY YES.

      Robert

      Delete
    2. Ah!you are right,i see it is defined with "copy yes"
      1)I see this message after "image copy successfull & catalog update sucessfull messages" in output so I'm not sure in which phase of reorg this "informational copy pending" gets listed?
      2)But why do we get these messages in reorg? what is the use of it?

      Delete
    3. 1) I don't know the REORG phase in which indexes are placed in informational COPY-pending status. This could be part of the cleanup work done in the UTILTERM phase of REORG.
      2) Please refer to the information under the heading, "Fallback recovery considerations" on this page of the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-before-running-reorg. There you will see this sentence: "REORG also places a reorganized index in informational COPY-pending (ICOPY) status. You should take a full image copy of the index after the REORG job completes to create a valid point of recovery."

      Robert

      Delete
  11. I have given nosysrec in my reorg job,but why would it expect sysrec dd card?
    DSNU047I 285 03:26:22.71 DSNURORG - A REQUIRED DD CARD OR TEMPLATE IS MISSING. NAME=SYSREC
    DSNU2903I 285 03:26:22.72 DSNURORG - PARTITION LEVEL INLINE COPY DATASETS WILL BE ALLOCATED
    DSNU012I 285 03:26:24.78 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST TURN CODE=8
    reorg statment:
    REORG TABLESPACE LIST TSDEF99
    LOG NO
    SORTDATA NO
    SORTKEYS
    NOSYSREC
    SORTDEVT SYSDA SORTNUM 255
    STATISTICS TABLE(ALL) INDEX(ALL)
    KEYCARD FREQVAL NUMCOLS 1 COUNT 10
    REPORT YES
    UPDATE ALL
    COPYDDN CPYTPRT
    MAXRO 5 DRAIN_WAIT 15 RETRY 10 RETRY_DELAY 5
    SHRLEVEL CHANGE FASTSWITCH YES
    parallel 0

    ReplyDelete
    Replies
    1. Please see the information about the NOSYSREC option on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement. On that page, you will see situations in which the NOSYSREC option is not allowed. One of those is, "No data will be sorted during the REORG TABLESPACE job. Examples: SORTDATA NO is specified..." You have SORTDATA NO in your REORG control statement.

      Robert

      Delete
    2. Interesting.Thanks for quick help.

      Delete
    3. Hello Rob,Found an interesting scenario.Even if I specify sortdata still it expects sysrec dd card.Per the url you shared-nosysrec is ignored only if sortdata no,unload only/pause is specifed.But I have not used them.What could be the reason it expects sysrec dd card.Below is my reorg control card.Any thoughts?
      REORG TABLESPACE LIST LS1 LOG NO
      SORTDATA
      NOSYSREC
      SORTDEVT SYSDA
      STATISTICS TABLE(ALL) INDEX(ALL)
      KEYCARD FREQVAL NUMCOLS 1 COUNT 10
      REPORT NO
      UPDATE ALL
      COPYDDN COPYDDN
      TIMEOUT TERM
      MAXRO 5 DRAIN_WAIT 15 RETRY 10 RETRY_DELAY 5
      SHRLEVEL CHANGE FASTSWITCH YES

      Delete
    4. That documentation page indicates that an unload data set will be required "No data will be sorted during the REORG TABLESPACE job. Examples: SORTDATA NO is specified, or no index is defined on the data that is being reorganized." You are REORG-ing a list of table spaces. Perhaps a table in one of those table spaces has no indexes.

      If that is not the case (if all tables associated with the list of table spaces have indexes) then I do not know why a SYSREC data set is required for this REORG job - you might need to open a case with IBM Support to get an answer to that question.

      Robert

      Delete
    5. I have raised the case thankyou.But curious to know why does the sortdata & tables without index requires sysrec.what is the relation/need of requiring sysrec.why do we need sysrec.

      Delete
    6. Refer to the documentation on REORG TABLESPACE options, at https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement. In the description of the NOSYSREC option, you will see the following: "Specifies that REORG TABLESPACE is not to use an unload data set. The utility uses the output of sorting as the input to reload but does not use an unload data set for this process."

      The operative phrase there is, "The utility uses the output of sorting as the input to reload." This means that the output of sorting will be used INSTEAD OF THE RECORDS IN THE SYSREC DATA SET as input to the RELOAD phase of the utility. If there is no sorting, there is no sort output, and if there is no sort output then the input to the RELOAD phase will have to be the records in the SYSREC data set.

      Robert

      Delete
    7. I thought only load needs sysrec and syspunch to load data to target table.Reorg is such reorganisation of table,so not sure why does it need sysrec ?

      Delete
    8. Refer to this page in the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=utilities-reorg-tablespace. On that page, under the description of the UNLOAD phase of REORG TABLESPACE, you will see the following: "Unloads the table space and sorts data if a clustering index exists and the utility job does not include the SORTDATA NO options. For SORTDATA processing, if you specify NOSYSREC, the utility passes rows in memory to the RELOAD phase; otherwise, it writes them to a sequential data set." The sequential data set referred to in the last sentence is the one associated with SYSREC.

      Robert

      Delete
  12. Whenever I want to view rts of a tablespace,I query systablespacestats.How does the data gets written/updated to systablespacestats ( termed as "rts").What's the prcoess involved

    ReplyDelete
    Replies
    1. Db2 for z/OS provides the information found in the real-time statistics catalog tables. Externalization of that data is described on this page of the online Dn2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=statistics-when-db2-externalizes-real-time.

      Robert

      Delete
    2. Thats was useful Robert.But giving a read on this page,I see a line mentioning -"
      1)During utility operations. Some utilities modify the statistics tables"-What are those utilities operation ? modify means with new values?
      2)"If any objects that are required for externalization of real-time statistics are unavailable, Db2 issues message DSNT535I or message DSNT536I to provide more information about the unavailable resources"-But I have never seen messages for rts not getting resources.I have only seen generic resource unavailable lessages with 904 for any batch or online prcoesses.Have you ever seen resource unavailability for rts?
      Any idea ?

      Delete
    3. 1) Please see this page in the online Db2 for z/OS documentation, "How utilities affect the real-time statistics": https://www.ibm.com/docs/en/db2-for-zos/12?topic=statistics-how-utilities-affect-real-time.

      2) Not personally, but I could certainly understand that such a scenario could arise. Should be rare event, but not impossible.

      Robert

      Delete
    4. Good to know !! thankyou

      Delete
  13. Why/when do we need to have/include keepdictionary in reorg?
    I see below messages appear in reorg job output.How does the compress attribute related to keepdictionary
    DSNU242I + 013 DSNURFUI - KEEPDICTIONARY OR COPYDICTIONARY REQUESTED BUT COMPRESS ATTRIBUTE NOT DEFINED
    FOR TABLE SPACE TS, PARTITION 1
    DSNU242I + 013 DSNURFUI - KEEPDICTIONARY OR COPYDICTIONARY REQUESTED BUT COMPRESS ATTRIBUTE NOT DEFINED
    FOR TABLE SPACE TS, PARTITION 2

    ReplyDelete
    Replies
    1. Refer to the description of the DSNU242I message on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=messages-dsnu242i. You will see that the error message is generated when KEEPDICTIONARY is specified for a REORG of a table space that is not defined with COMPRESS YES. The dictionary referred to is the compression dictionary used to compress rows of a table defined with COMPRESS YES. When a table space is not defined with COMPRESS YES, it will not have an associated compression dictionary, and so the KEEPDICTIONARY option is not relevant because there is no dictionary to keep. For a compressed table space, KEEPDICTIONARY tells REORG to retain the current dictionary versus building a new one. As for why KEEPDICTIONARY would or would not be specified for a table space defined with COMPRESS YES, see the description of the KEEPDICTIONARY option on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=tablespace-syntax-options-reorg-control-statement.

      Note that when a partitioned table space is compressed, each partition has a compression dictionary.

      Robert

      Delete
  14. I surfed to know on this compression dictionary as I have not heard/read about it.Does this compress dictionary applies/applied only for reorg & load? when and all do this compression dictionary gets invoked?

    ReplyDelete
    Replies
    1. A compression dictionary is usually built as part of a LOAD or REORG utility execution, but it is also possible for a compression dictionary to be built from INSERT activity.

      Once built, a compression dictionary is used to compress data when rows are added to a table via LOAD or INSERT. The dictionary is also used to de-compress data when data is read by a program.

      You can read more about Db2 data compression on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=performance-compressing-your-data.

      Robert

      Delete
  15. I submitted an reorg for partlevel with below copy dataset specification.In the sysprint,I'm able to see dataset allocation messages,image copy completed messages,switch phase complete messages as well.But at end of sysprint,it states db2 is unable to unallocate image copy dataset(that was already allocated in same job) and job failed as well.Why would reorg try to unallocate the ic dataset that has been has already allocated during inline copy process in same job?
    TEMPLATE COPY DSN '&SS..&TS..P&PA..D&MO.&DA.&YE(3,2).'
    UNIT CTAPE BUFNO 60 RETPD 7 VOLCNT(99)
    DISP (NEW,CATLG,CATLG) STACK NO TRTCH NOCOMP
    DSNUGDYN - DATASET ALLOCATED. TEMPLATE=COPY
    DDNAME=SYS00001, FILE SEQUENCE=0001
    DSN="dsname"
    DSNUGDYN - DATASET ALLOCATED. TEMPLATE=COPYTAP
    DDNAME=SYS00002, FILE SEQUENCE=0001
    DSN="dsname"
    DSNURPCT - MAXIMUM UTILITY PARALLELISM IS 35 BASED
    ICS

    DSNURBID - COPY PROCESSED FOR TABLESPACE EMP
    NUMBER OF PAGES=1906148
    AVERAGE PERCENT FREE SPACE PER PAGE = 8.09
    PERCENT OF CHANGED PAGES =100.00
    ELAPSED TIME=04:03:27
    DSNURBID - COPY PROCESSED FOR TABLESPACE EMP
    NUMBER OF PAGES=2443775
    AVERAGE PERCENT FREE SPACE PER PAGE = 8.27
    PERCENT OF CHANGED PAGES =100.00
    ELAPSED TIME=04:03:27
    DSNURSWT - SWITCH PHASE COMPLETE, ELAPSED TIME = 00
    DSNURSWT - DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE EMP
    DSNURSWT - DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE
    DSNUSUTP - SYSTABLEPART CATALOG UPDATE FOR EMP

    DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR EMP
    DSNUSEF2 - RUNSTATS CATALOG TIMESTAMP = 2024-01
    DSNU031I 069 12:33:39.50 DSNUGSDA - UNABLE TO UNALLOCATE "dsname"
    DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED

    ReplyDelete
    Replies
    1. This could be happening because the fix for APAR PH27493 (which came out in 2020) was applied to the Db2 12 code (or this could be a Db2 13 for z/OS system - same functionality is part of the base Db2 13 code). Db2 12 with that fix (or Db2 13) will try to close and deallocate data sets that were created as a result of utility execution - in particular, image copy data sets - once their creation and population is complete. The reason? This was done to make it easier for a Db2 subsystem to operate within the DSMAX limit (referring to the Db2 ZPARM parameter that sets a limit on the number of data sets that are open and allocated to the Db2 subsystem - specifically, to the Db2 DBM1 address space - at one time. The thinking is that an image copy data set, once created, does not need to remain in an allocated and open state - it will be reallocated and reopened when needed as input to a RECOVER job. This being the case, why not close and deallocate it so that we open up more space under the DSMAX limit. More information on this APAR is at https://www.ibm.com/support/pages/apar/PH27493.

      Robert

      Delete
    2. But this is an reorg job not with copy job.Then why would try to unallocate/catalog dataset.Weird think is that ,same job for different table(lesser volume) ran fine.But the sysprint of the job did not have "db2 unable to unallocate" message at end of reorg.Not sure why would an reorg job try to unallocate dataset at end of reorg(after switch phase) is this behavior of reorg?

      Delete
    3. A REORG, yes, but it appears to be a REORG for which inline image copies were created.

      In the case of the job that ran fine without an "unable to unallocate" message, that does not necessarily mean that Db2 did not deallocate a data set - it could indicate that Db2 was able to deallocate data sets with no problem.

      If, for the one job, the data set that Db2 was trying to deallocate were one (such as an image copy data set) for which there is no need to leave the data set allocated, the reason for the deallocation, as I explained earlier (see the text of the referenced APAR), is to open up room under the subsystem's DSMAX limit. If data sets allocated in the course of a Db2 utility execution, and for which there is no reason to leave the data sets allocated after utility has completed, why not deallocate them? Why leave these data sets (such as image copy data sets) allocated to Db2, when doing so might cause the DSMAX limit to take effect, resulting in the closure of (possibly) table space and/or index space data sets?

      Robert

      Delete
    4. Condering this case,then why dont we see such "deallocation message" at end for all reorg jobs.Lets say I have never seen this messages in any reorg jobs earlier ,this is the first time I'm seeing this.Is there a triggering point for these messages to be displayed in job?

      Delete
    5. The only explanation that comes to my mind: you saw the deallocation-related message for the one REORG job because Db2 was unable to deallocate a data set associated with the job. For other utility jobs, you did not see deallocation-related messages because Db2 did not encounter any problems in deallocating data sets.

      That's all I have to offer. If you are seeking more information, you might be able to get it via a case opened with IBM Support.

      Robert

      Delete
  16. I understand the ICOPY will be placed on an index defined with copy yes when a reorg is run on the tablespace.I have an index placed in icopy,but the reorg was not run on that tablespace, so I surfed to know what all activities would place index in icopy.But no luck.
    Any thoughts?

    ReplyDelete
    Replies
    1. Check this page of the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=copy-preparing-recovery-by-using-utility. On that page, you will see the following: "For an index that was defined with the COPY YES attribute the following utilities can place the index in ICOPY status:
      * REORG INDEX
      * REORG TABLESPACE LOG YES or NO
      * LOAD TABLE LOG YES or NO"

      Robert

      Delete
    2. Interesting thing I found is that,there was no entry for reorg or load in syscopy to this table.But an load resume yes was attempted on this table ,but the load was not complete (failed) as the table was in incomplete state as aux table was not defined.Also the failed job does not have informational messages stating index is placed in ICOPY.So now,even when the load is not complete does index gets placed in ICOPY?

      Delete
    3. Adding to my above response,the table was also dropped and recreated ,RI was added before load failed.Could any of these put an ICOPY? But the IBM manual you have posted does not list any .So I'm wondering what could be the reason

      Delete
    4. The main thing is to correctly understand ICOPY status. It's not a restrictive status such as copy-pending or rebuild-pending. It's just Db2 reminding you that you might want to image-copy this index because a large-scale index-changing process (e.g., REORG or LOAD). Image-copying the index in that case (relevant only if the index defined with COPY YES) would mean faster recovery of the index due to reduced need for log-apply processing after the image copy of the index has been restored as part of the recovery process. So, again, it's a friendly reminder from Db2.

      Robert

      Delete
    5. But when there is no trace of reorg or load,how will the index be placed in ICOPY is what Im wondering about .Only thing I know for sure is,there was an load attempted which failed due to incomplete state of the table.Do you think failed utlity which did not make entry into syscopy will be cause for this ICOPY state? I have also stated other activites done on this like drop,recreate ,load (failed)..this was the only activities to the table .

      Delete
    6. I don't see that a LOAD utility that fails to complete would put COPY YES indexes in ICOPY status - REBUILD-pending status, maybe, but not ICOPY.

      The only processes I know of that can cause a COPY YES index to go into ICOPY status are IBM Db2 utility processes - as previously mentioned, REORG or LOAD (also REBUILD INDEX and QUIESCE).

      Keep in mind that the index in question might have been in ICOPY status for some time.

      You might need to open a case with IBM Support to get a better idea as to how the index with which you are concerned got into ICOPY status.

      Robert

      Delete
    7. Wouldn't reorg on tablespace take care of doing image copy on index as well when it is taking inline copy on tablespace?
      TEMPLATE COPYDS DSN ''
      UNIT CTAPE
      DISP (NEW,CATLG,CATLG) STACK YES TRTCH COMP

      REORG TABLESPACE LIST TEMP
      KEEPDICTIONARY
      NOSYSREC
      SORTDEVT SYSDA SORTNUM 18
      COPYDDN COPYDS
      TIMEOUT TERM MAXRO 10 DRAIN_WAIT 15

      Delete
    8. When you execute an online REORG for a table space, the REORG will image-copy the table space because that is REQUIRED to have a post-REORG point of recoverability for the table space. There is NOT a requirement to image copy an index defined with COPY YES (image copying is not otherwise relevant for an index) as part of an online REORG, because even without an index image copy the index can be recovered by way of REBUILD INDEX.

      COPY YES does not mean that an index MUST be periodically image copies. It means that the index CAN be backed up via the COPY utility.

      Robert

      Delete