Db2 for z/OS database administrators often need to alter table definitions to support evolving requirements of applications. Issues like thefollowing oneshave arisen after those table definition changes. These issues can occur with single-table table spaces or multiple-table table spaces, but can be especially troublesome with multiple-table table spaces.
Database administrators commonly use an offline utility, such as DSN1COPY, to copy tables from one subsystem to another.Whenthe copiedtableshaverecent definition changes, the table definitions in the target catalog can become out of sync with the table data.
Most table definition changes generate new table versions. A maximum of 255 table versions are available, and when table spaces contain a lot of tables, you are likely to make many table definition changes and use all those versions quickly.When all the versions are used, you cannot perform any more table definition changes until you run REORGand MODIFY RECOVERYto recyclesome versions. However, if any tables are at version 0, you cannot release any versions. Database administrators have indicated that they need Db2 changes to resolve this situation.
The followingchangesto Db2 12 for z/OS provide solutions to the problemslisted above.All single-table or multiple-table table spaces can benefit from the changes, except work file table spaces, XML table spaces, and LOB table spaces.
To prevent problems due to a table’s data getting out of sync with the table’s catalog definition, Db2 writes information about the table structure in the table space's system pages. As a result, Db2 can determine a table’s structure without looking in the catalog.Db2 inserts this information automatically when you insert or update a rowor run LOAD REPLACE on an existing table,orrun REORGonthe table space that contains thetable, even when the table definition has had no changes. Db2 12 also inserts the information when you complete the definition of a new table. Alternatively, you can use the REPAIR utility with the INSERTVERSIONPAGES option to insert the information.
To make it possible to release versions when a table space has tables at version 0 and at version 255, the REPAIR utility has the new SETCURRENTVERSION option, which you specify with INSERTVERSIONPAGES. The SETCURRENTVERSION option changes the version number for any tables in the table space that are at version 0 to the current version of the table space. After this, you can run the REORG utilityfollowed by the MODIFY RECOVERY utilityto recycle unusedtable space versions.
To take advantage of thesechanges, apply the PTFs for the following Db2 12 APARs:
Db2 12 for z/OS introduces new SQL syntax that makes the difficult task of coding efficient SQL pagination—returning a portion of a result set based on data position—much easier for application developers. Read more in…