The following new product features and enhancements to existing features are now available in Version 20.0 of CA RC/Update:
The following new product features and enhancements to existing features are now available in Version 20.0 of
CA RC/Update™ for DB2 for z/OS(CA RC/Update):
To ensure availability of all features and fixes, install all available maintenance. To download and receive maintenance automatically, use CA SMP/E Receive Order. To view a list of available solutions and fixes, select the link for PTFs since GA on the CA Database Management Solutions for DB2 for z/OS Maintenance Grid (login required).
Partition-Level DSSIZE PBR2 Alteration Support (LU00446)
Partition-level DSSIZE alteration support is now provided for PBR2 tablespaces and indexes. For PBR2 objects, the following additional statements are now supported:
- ALTER TABLESPACE PART DSSIZE
- CREATE INDEX DSSIZE
- ALTER INDEX DSSIZE
Db2 object management panels have been updated to show the editable partition-level DSSIZE values for tablespaces, and global and partition-level DSSIZE values for indexes.
CREATE TABLESPACE INSERT ALGORITHM Support (SO15804)
Support has been added to generate the INSERT ALGORITHM clause in CREATE TABLESPACE DDL on Db2 12 subsystems V12R1M500 or higher as shown in the following table:
INSERTALG Value from SYSTABLESPACE
Generate Defaults Value in OFS parmlib
1 or 2
Generate preserved value from target in resulting CREATE TABLESPACE DDL.
0, 1, or 2
Generate preserved value from target in resulting CREATE TABLESPACE DDL.
You can also generate a recovery analysis for a tablespace that has the INSERT ALGORITHM clause.
ALTER Support for PAGENUM RELATIVE (SO14832)
ALTER TABLESPACE PAGENUM RELATIVE is now supported on Db2 12 subsystems. This tablespace attribute now appears on the Tablespace Alter panel and can be altered. Generated DDL includes the clauses when required.
The CREATE TABLESPACE PAGENUM RELATIVE/ABSOLUTE clause is now supported on DB2 12 subsystems. PAGENUM identifies the type of page numbering used when creating partition-by-range (PBR) universal tablespaces: A (absolute) or R (relative). The PAGENUM attribute is valid for partition-by-range (PBR) tablespaces only.The PAGESET_PAGENUM subsystem parameter determines the PAGENUM default (A or R). When PAGENUM is specified, also specify NUMPARTS. MAXPARTITIONS cannot be specified. If SEGSIZE is specified, it cannot be zero (0). You can alter and template a PBR tablespace and preserve the PAGENUM attribute in the generated DDL.
GENERATED ALWAYS Non-Deterministic Expression Columns Support
GENERATED ALWAYS non-deterministic expression clauses are now recognized when migrating, altering, and comparing tables. A new option
GENERATED ALWAYS AS (non-deterministic-expression), has been added to the
Default indicator column attributefield. Generated DDL includes the clauses when required.
ON DELETE ADD EXTRA ROW Clause for ALTER TABLE
The ON DELETE ADD EXTRA ROW clause is now recognized when migrating, altering, and comparing temporal and history tables. Generated DDL includes the clause with the ADD VERSIONING statement when required.
COMPRESS Clause Support for LOB Tablespaces
Support has been added to generate the COMPRESS clause for LOB tablespaces. The clause is included in generated DDL when required for migrate, alter, and compare strategies. You can also use the COMP field on the LOB Tablespace Alter/Create panels to specify whether data compression applies to the rows of the tablespace or partition.
IMPLICITLY HIDDEN ROWID Column Support
Support has been added to recognize the IMPLICITLY HIDDEN clause for explicitly hidden ROWID columns in tables. The generated DDL includes the clause when required for migrate, alter, and compare strategies on Db2 subsystems running at least function level V12R1M503. Use the HIDDEN field on the Table Alter and Table Create panels to specify whether the column is visible in the result set SQL statements or whether the column is implicitly hidden. A pop-up panel displays when needed to indicate whether you want the column to be implicitly created by Db2 with the IMPLICITLY HIDDEN attribute or explicitly created with its defining syntax included in the generated DDL.
Partition Level DSSIZE Support for PBR2 Indexes
On Db2 12 subsystems, you can now generate DDL that contains the DSSIZE clause in CREATE INDEX statements. DSSIZE is valid for an index at the global and partition level on a tablespace with relative page numbering (PBR2). A DSSIZE value from 1 GB to 1024 GB is now supported for PBR2 indexes.
Global-level and partition-level support is controlled by the Generate common partition specifications at global level option in the OFS parmlib member. When set to Y, common partition specifications are generated at the global level instead of every partition level. The default is N, generate all partition specifications at every partition level.
When a range-partitioned tablespace is defined with relative page numbering, the index DSSIZE value at the partition level can be different from the index global DSSIZE value.
You can use the Db2 object management panels to create and template an index and honor the index and partition level DSSIZE values.
Indexes now Supported in Generate Common Partition Specifications at Global Level OFS Parmlib Option
The Generate common partition specifications at global level option (GENERATE_COMMON_PARTITION_SPECIFICATIONS_AT_GLOBAL_LEVEL) in the OFS parmlib member has been enhanced to now support partitioned indexes on Db2 11 and Db2 12 subsystems. Previous support was only for partitioned tablespaces.
When this parmlib option is set to Y, the common partition specifications (like FREEPAGE, PCTFREE, GBPCACHE, STOGROUP, VCAT, and so on) are generated at the global level for tablespaces and indexes. The default is N, generate all partition specifications at every partition level. On Db2 12 subsystems, the matching values are suppressed in the partitions.
Use this option with the Generate default clauses in CREATE statements option, which specifies whether to generate or suppress the default clauses in the CREATE statements for Db2 objects.
M504 Deprecated Objects Support
Db2 12 Function Level M504 prevents the creation of the following deprecated objects:
- Non-UTS segmented and 'classic' partitioned tablespaces
- Tables in non-UTS segmented and 'classic' partitioned tablespaces
- Hash tables (only supported in CA RC/Query)
CREATE DDL statements for deprecated objects are now generated as follows to enable creation of these objects after activating function level M504:
SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'; (deprecated object CREATE DDL statement) SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M504';
The SET CURRENT APPLICATION COMPATIBILITY statements are only generated when the current active function level of the Db2 subsystem is at least V12R1M504. While executing an process, the Batch Processor displays the current function level based on when the CONNECT occurs or is changed.
DB2 12 Continuous Delivery Support
IBM DB2 12 for z/OS function levels V12R1M100, V12R1M500, and V12R1M
nnnare now supported.
Version 20.0 of CA RC/Update supports the IBM DB2 12 Continuous Delivery Model, which introduces new features in the maintenance stream for faster adoption.
Customers who are moving to DB2 12 should upgrade to Version 20.0 of CA RC/Update to take advantage of new DB2 12 features. In Version 19.0 of CA RC/Update, only DB2 12 function levels V12R1M100 and V12R1M500 are supported.
To specify the new function levels for DB2 12, see Upgrade to New DB2 Releases and Function Levels.
KEY LABEL Clause Support
You can now generate DDL that contains the KEY LABEL or NO KEY LABEL clauses in the following CREATE statements on DB2 subsystems running at DB2 12 function level M502 (V12R1M502) or higher:
- CREATE STOGROUP
- CREATE TABLE
This support is provided when creating or templating storage group and table objects.
The following new utility models are provided:
- OREORGKL (KEY LABEL online IBM REORG)
- OREORGKR (KEY LABEL online IBM REORG for recovery)
Both utility models use a new automatic symbolic, CDSN, as the prefix of the image copy data set names in the respective TEMPLATE staements. See Utility Model Services Enhancements.
ALTER TABLE ON DELETE ADD EXTRA ROW Clause Support
The ON DELETE ADD EXTRA ROW clause is now generated for an ALTER TABLE statement when CLUSTERRID=1.When a row is deleted, an extra row is inserted into the history table to record information about the delete operation itself.
Partition Level DSSIZE Support
Before DB2 12, partition size (DSSIZE) was limited to 256 GB. Data pages were sequentially formatted based on the tablespace instead of each data partition. DB2 12 introduces partition-by-range tablespaces using relative page numbering (PBR RPN), known as PBR2 tablespaces. Relative page numbering represents page numbers without embedded partition numbers and replaces absolute page numbers. With PBR2 tablespaces, the maximum partition size is now 1024 GB. CA RC/Update now supports a DSSIZE value from 1 GB to 1024 GB for a tablespace using relative page numbering.
When a range-partitioned tablespace is defined with relative page numbering, you can now define the partitions for the tablespace with DSSIZE values that are different from the tablespace DSSIZE value. You can change the partition level DSSIZE values when you are altering or templating an existing partition-by-range tablespace with relative page numbering. To change these values, use the tablespace level DSSIZE field in the header portion of the product panels. When the tablespace and partition level DSSIZE values do not match, the following new pop-up window displays automatically. This window shows summary information for the new tablespace level DSSIZE, the total number of partitions, and the smallest and largest partition DSSIZE values. For example:
+------- Tablespace DSSIZE does not match partition DSSIZE(s) -------+ | | | The new global DSSIZE specified for this PBR2 tablespace does not | | match one or more of its individual partition DSSIZE values. | | | | New tablespace DSSIZE: 8 Total partitions: 6 | | Smallest DSSIZE found: 1 Largest DSSIZE found: 4 | | | | Do you want to propagate the new DSSIZE into all parts? N (Y,N) | | Enter Y to have DSSIZE in all parts changed to match new DSSIZE. | | | | Enter / below to suppress this pop-up for the remainder of your | | RCU/RCM session: | | _ Do this whenever I change the tablespace DSSIZE. | | | | Press HELP/PF1 for more information on this window. | +--------------------------------------------------------------------+
Enter Y to propagate the new DSSIZE value into all partitions. Otherwise, the existing DSSIZE values are preserved. To suppress the pop-up for the remainder of your product session, enter a forward slash in the Do this whenever I change the DSSIZE option.
You can use the DB2 object management panels to create, template, and alter a tablespace with relative page numbering and honor the tablespace and partition level DSSIZE values:
- For a tablespace with relative page numbering, the DSSIZE can be DFLT or an integer from 1 GB to 1024 GB at the tablespace level.
- For a tablespace with absolute page numbering, the DSSIZE can be DFLT, 1, 2, 4, 8, 16, 32, 64, 128, and 256.
DSSIZE is valid for only partitioned tablespaces that are not expressly defined as LARGE, and LOB tablespaces. For tablespaces that are defined as LARGE, DSSIZE must be DFLT. DSSIZE does not otherwise display as an option on the Create, Alter, and Template panels. When the tablespace and partition level DSSIZE values do not match, the previously described new pop-up window displays.
INCLUDE/EXCLUDE NULL KEYS Support for CREATE INDEX
The INCLUDE/EXCLUDE NULL KEYS clause is now supported for CREATE INDEX statements when using CA RC/Migrator, CA RC/Compare, and CA RC/Update.
Routine DDL Formatting Improvements
The DDL formatting for routines has been improved.
Utility Model Services Enhancements
The utility model definitions that are provided by model services have been enhanced. You can use these definitions to customize the output of utility statements that are executed by the Batch Processor. The utility models now:
- Use TEMPLATE syntax as the default when allocating data sets (applies to CHECK, CHECK_R, COPY, COPY_B, COPY_PC, COPY_R, REORG, REORG_R, REORGI, and REORGI_R)
- Support unloading of large data sets to VTAPE (applies to TEMPLATE support in IBMUNLD, OREORGTS, and OREORGT)
- Use SHRLEVEL CHANGE with LOB support
- Enforce mutual exclusivity betweenBroadcomand IBM utilities when including and excluding
- Support the new %DTQUAL automatic symbolic variable to ensure uniqueness from other models in the same analysis output
- Provide parallel IBM UNLOAD and LOAD support during recovery processing (applies to IBMULD_R and LOAD_R)
- Include new OREORGKL and OREORKR utility models that support the KEY LABEL clause
- Enable partition level online REORG support for the OREORGT utility model when limit keys are altered
New %DTQUAL Automatic Symbolic Variable
A new automatic symbolic, %DTQUAL, has been defined. This new variable builds a date and time string as part of the generated data set name to ensure uniqueness from any other model that could be part of the same analysis output. We recommend that you use %DTQUAL as follows in your DSN override setting:
The DSN override value is set in the Batch Processor DSN masks field when you load and customize the model services table during DB2 catalog customization. The Batch Processor DSN mask field lets you enter the symbolic prefix for the data set names that are generated for output when a utility is invoked during analysis. When set as specified, the following utility models can generate unique data sets for each utility without manual customization:
- CHECK, CHECK_R
- COPY, COPY_B, COPY_PC, COPY_R, QCOPY, QCOPY_B, QCOPY_R
- FUNLD, FUNLD_B, FUNLD_R, IBMULD, IBMULD_B, IBMULD_R, UNLOAD, UNLOAD_B, UNLOAD_R, XUNLD, and XUNLD_R
- OREORGT, OREORGTS
- REORG, REORG_R, REORGI, REORGI_R, RREORG, RREORG_R, RREORGI, RREORI_R
Load and customize the Model Services Table in CA RC/Migrator DB2 Catalog Customization Tasks
New IDENTITY and SEQUENCE Utility Models
The following new utility models are now available:
- IDENTITYGenerates the .CALL PROCEDURE statements that are required to execute the RMRTPE REXX stored procedure. RMRTPE is an optional DB2 catalog customization task that restarts numbering for identity columns of a recreated table after it is loaded.
- SEQUENCEGenerates the .CALL PROCEDURE statements that are required to execute the RMRSQPE REXX stored procedure. RMRSQPE is an optional DB2 catalog customization task that prevents duplicates for altered sequences. The stored procedure creates job-temporary DB2 sequences.
The @DEFAULT model contains these new utility models. To use them, you must load and customize the model services table, and then use the Utility Model Services profile option to update and activate.
IBM UNLOAD Conversion Support for CHAR to INT
The IBM UNLOAD model (IBMULD) now supports the following data type conversions:
- SMALLINT, INTEGER, BIGINT to CHAR or VARCHAR
- CHAR and CHARVAR to SMALLINT, INTEGER, or BIGINT
With this enhancement, you can now use IBM UNLOAD for data type conversions such as CHAR to numeric for a LOB or XML column in a table.
Parallel IBM UNLOAD and LOAD Recovery Support
Parallel unload and load support is now provided during recovery processing for the IBM UNLOAD and LOAD utilities (IBMULD_R and LOAD_R). IBMULD_R now generates a recover data set with &PART in it when &PART is added to &DATADSN. LOAD_R now generatesload data sets with &PART in the analysis. This enhancement lets you use parallel processing in recovery and improves performance of the unload and load process.
LOAD_R has been updated to use the new %TMPLTDSN automatic symbolic in TEMPLATE statements and to build the INTO clause using #SEG and #ENDSEG logic.
By default, this parallel processing is off. To activate, edit IBMULD_R and change symbol DATADSN to %DSN..P&PA..RD.
New OREORGKL and OREORKR Utility Models
The following new utility models are now available:
- OREORGKL (KEY LABEL online IBM REORG)IBM Online Reorg utility control statements with the KEY LABEL clause.
- OREORGKR (KEY LABEL online IBM REORG for Recovery)IBM Online Reorg utility control statements for recovery with the KEY LABEL clause.
When activated, these new online reorg models are generated to reorganize:
- A table and its dependent auxiliary tables when a table with the table level KEY LABEL clause is created by the analysis in an explicitly created tablespace.
- An auxiliary table that is associated to a base table with the KEY LABEL clause is created by the analysis and is in an explicitly created LOB tablespace.
Both utility models use a new automatic symbolic,
CDSN, as the prefix of the image copy data set names in the respective TEMPLATE statements. For CDSN, we extract the current value of the DSN symbol and change the %DSNQUAL2 and %DSNQUAL3 values to IBM symbolics (&DB and &SN) as follows:
- %DSNQUAL2 to &DB (database name)
- %DSNQUAL3 to &SN (space name–tablespace or indexspace)
For example, when:
- DSN is %USERID..%DSNQUAL2..%DSNQUAL3..%DTQUAL
- CDSN is %USERID..&DB..&SN..%DTQUAL
The &SN qualifier in the PTICOPY TEMPLATE data set name is required so that LOB tablespaces have distinct data set names specific to their tablespace name.
Partition Level Online REORG Support
Partition level online REORG support is now enabled for the OREORGT utility model. When limit keys are altered for range partitioned tables and OREORGT is included in the model being used, a partition level online reorg is generated with the affected partitions after the ALTER TABLE command.
The partition values (PART 1:n, where n is the last partition) are generated for the following range partitioned tablespaces: index-controlled partitioning (ICP), table-controlled partitioning (TCP), and partition-by-range (PBR).
Partition level online reorgs enable changes to take effect without a complete reorg of the tablespace. This means that there is less downtime when compared to a batch reorg.
Isolation Level Support
You can now specify the isolation level to be used for queries that are prepared and executed when browsing DB2 tables. Cursor stability (CS) and uncommitted read (UR) are supported at the global and user level. This feature prevents queries from locking the tables when the table is being extracted and changed simultaneously by multiple users. The isolation level is appended to the SELECT results using the WITH clause. For example, WITH CS or WITH UR.
For global support, a new parameter ISOLATN has been added to the RCEDIT parmlib member in the
hlq.CDBAPARM data set. CS is the default.
For user level support, a new parameter Isolation Level for RC Browse is now provided on the RC/Edit Parameters panel. If left blank (the default), the global level setting is used.
Archive Table Support
Archive table support is now provided. Archive tables in IBM DB2 for z/OS can automatically insert rows that are deleted from one table into a separate archive table. You can use archive tables to manage historical data without the need to move data to a separate table manually. Use of archive tables can improve performance and also reduce operating costs.
In CA RC/Update, you can now:
- Browse and fast browse the data of an archive table
- Compare data of an archive table against another table or archive table
- Drop a base table that has archiving enabledThe associated archive table is implicitly dropped.
- Use the R or RO (Drop with Recovery) option to drop a base table that has archiving enabledWhen the DROP base-table DDL is executed, DDL is generated to recover all implicitly dropped dependent objects, including the archive table.
The following functions are not currently supported:
- Edit, fast edit, RI browse, RI edit, or data copy an archive table
- Alter archive tables from the product panels
- Explicitly template, alter, or drop an archive table
New CREATE VIEW Default Option in OFS Parmlib Member
A new option O (original), is now provided for the Generate column names with SELECT(*) parameter in the OFS parmlib member. This new option extracts the original view text from SYSIBM.SYSVIEWS and generates a column list when the original CREATE VIEW statement has a column list. The Y (generate) and N (suppress) column list values are still accepted, although O is the new default and recommended setting.
A new health check is also provided to inform customers when they are running with a setting that may not generate a CREATE VIEW column list that is consistent with the original CREATE VIEW statement.
New OFS Health Check
A new health check, DB2_OFS_CREATE_VIEW, has been added for use with Object Framework Services (OFS). CA_DB2 is the owner. You can use this health check to review the setting of the Generate Column Names with SELECT(*) parameter in the OFS parmlib member.
GENERATE_COLUMN_NAMES_WITH_SELECT(*) controls whether column lists are generated from the original view text in SYSIBM.SYSVIEWS. The health check informs customers when they are running with a setting in OFS that may not generate a CREATE VIEW column list that is consistent with the original CREATE VIEW statement. We recommend that you review this setting and adjust as necessary before the DDL is updated.
Tablespace Bufferpool Size Alteration is now a Pending Change
The ALTER statement for tablespace bufferpool size changes is now generated after the immediate tablespace alter changes. This support provides performance improvements because tablespace bufferpool size changes can be made with less overhead.
Notification is provided to the user when a change in the bufferpool size generates a pending change.
CREATE TABLESPACE PAGENUM Support
The CREATE TABLESPACE PAGENUM RELATIVE/ABSOLUTE clause is now supported on DB2 12 subsystems. PAGENUM identifies the type of page numbering used when creating partition-by-range (PBR) universal tablespaces: A (absolute) or R (relative). Relative page numbering provides more flexibility and availability and higher limits in partition growth. The PAGENUM attribute is valid for partition-by-range (PBR) tablespaces only.
The PAGESET_PAGENUM subsystem parameter determines the PAGENUM default (A or R). When PAGENUM is specified, you must also specify NUMPARTS. MAXPARTITIONS cannot be specified. If SEGSIZE is specified, it cannot be zero (0).
CA RC/Update™ for DB2 for z/OS, you can alter and template a PBR tablespace and preserve the PAGENUM attribute in the generated DDL.