Activating Db2 12 new function at migration

The use of most new capabilities and enhancements that Db2 12 introduces is prevented until you activate function level 500 or higher. You cannot fallback to Db2 11 and Db2 12 cannot coexist with Db2 11 after you activate function level 500.

Before you begin

Important: In data sharing, ensure that the data sharing group has no active Db2 11 members. For instructions for migrating data sharing members to Db2 12, see Migrating subsequent members of a group to Db2 12.

Important: Do not issue the ACTIVATE command or run job DSNTIJAF for activation of Db2 12 until you are certain that the subsystem or data sharing group can proceed on Db2 12, without the possibility of falling back to or coexistence with Db2 11. In data sharing, the ACTIVATE command has group scope. Fallback and coexistence become impossible with the successful activation of function level 500 or higher.

Important: Apply the for fallback SPE (APAR PI33871) and stop and restart Db2 11 for every subsystem or data sharing member that you plan to migrate to Db2 12. For data sharing, every member must be started in Db2 11 after the fallback SPE is applied. Inactive members that never started with the fallback SPE applied in Db2 11 cannot start in Db2 12 or Db2 11 after migration to Db2 12 and activation of function level 500 on any other member. See Required maintenance for Db2 12 installation or migration.

Start of change Ensure that no incompatible applications will interfere with the catalog update. For details, see Identifying applications that are incompatible with catalog updates. End of change

Determine the function level to activate. In most cases, you can activate a higher function level without separately activating each lower function level above the currently activated function level. However, activating a higher function level also results in the activation of all lower function levels. Before activating a function level, familiarize yourself with the new capabilities and changes that all lower function levels introduce:

About this task

The Db2 12 migration process uses the following function levels:

Function level 100: Db2 12 starts at function level 100 during migration to Db2 12. In function level 100, fallback to Db2 11 and coexistence with Db2 11 in data sharing remain possible, and most new capabilities in Db2 12 remain deactivated. Function level 100 is comparable in many respects to conversion mode in previous Db2 releases. For more information, see Function level 100 (activated by migration to Db2 12)
Function level 500: Function level 500 (V12R1M500) represents the first opportunity for applications to take advantage of most new capabilities in the Db2 12 initial release, including new SQL capabilities and many new subsystem parameter settings. Activation of function level 500 prevents any future fallback to Db2 11. Function level 500 is comparable in many respects to new-function mode in previous releases. For more information, see Function level 500 (activated at Db2 12 installation or after migration - October 2016)

Start of change Before you can tailor the Db2 catalog for function level 502, function level 500 or 501 must be activated first. However, activating a lower star (*) function level does not prevent the catalog from being tailored for function level 502. End of change

The availability of new function depends on the type of enhancement, the activated function level, and the application compatibility levels of applications. In the initial Db2 12 release, most new capabilities are enabled only after the activation of function level 500 or higher.

Virtual storage enhancements

Virtual storage enhancements become available at the activation of the function level that introduces them or higher. Activation of function level 100 introduces all virtual storage enhancements in the initial Db2 12 release. That is, activation of function level 500 introduces no virtual storage enhancements.

Subsystem parameters

New subsystem parameter settings are in effect only when the function level that introduced them or a higher function level is activated. Many subsystem parameter changes in the initial Db2 12 release take effect in function level 500. For more information about subsystem parameter changes in Db2 12, see Subsystem parameter changes in Db2 12.

Optimization enhancements

Optimization enhancements become available after the activation of the function level that introduces them or higher, and full prepare of the SQL statements. When a full prepare occurs depends on the statement type:

For static SQL statements, after bind or rebind of the package
For non-stabilized dynamic SQL statements, immediately, unless the statement is in the dynamic statement cache
For stabilized dynamic SQL statements, after invalidation, free, or changed application compatibility level

Activation of function level 100 introduces all optimization enhancements in the initial Db2 12 release. That is, function level 500 introduces no optimization enhancements.

SQL capabilities

New SQL capabilities become available after the activation of the function level that introduces them or higher, for applications that run at the equivalent application compatibility level or higher. New SQL capabilities in the initial Db2 12 release become available in function level 500 for applications that run at the equivalent application compatibility level or higher. You can continue to run SQL statements compatibly with lower function levels, or previous Db2 releases, including Db2 11 and DB2® 10. For details, see Application compatibility levels in Db2

Procedure

To activate Db2 12 new function for the first time, complete the following steps:

Issue a DISPLAY GROUP command to check that the code level of the Db2 subsystem or each data sharing group member supports your target function level.
In the DSN7100I message, the DB2 LVL column indicates the code level. For more information and examples, see Determining the Db2 code level, catalog level, and function level.
If necessary, apply maintenance, such as PTFs and RSUs, to bring your Db2 subsystem or data sharing group members up to the required code level for your target function level.
Run the Db2 installation CLIST, as described in Generating tailored Db2 12 installation, migration, or function level activation jobs. Then complete the following steps:
1. On panel DSNTIPA1, specify values in the ACTIVATE, INPUT MEMBER, and OUTPUT MEMBER fields.
  - In the INSTALL TYPE field, specify ACTIVATE.
  - In the INPUT MEMBER field, specify the name of the CLIST output member that you created when you installed or migrated to Db2 12, or most recently activated a Db2 12 function level.
  - In the OUTPUT MEMBER field, specify a new member name, to save your changes for future use.
2. On panel DSNTIPT, verify the SAMPLE LIBRARY field value, which is the name of the output data set that is to be created. An asterisk appears at the far left of this field if the data set already exists. If the data set already exists, the CLIST replaces the members that it customizes for activation of the new function level.
3. On panel DSNTIP00, specify V12R1M5nn in the TARGET FUNCTION LEVEL field, where 5nn is the function level that you want to activate. The format is VvvRrMmmm, where vv is the version, r is the release, and mmm is the modification level. The value is used in the ACTIVATE command in the DSNTIJAF job and in the CATMAINT utility control statement in the DSNTIJTC job.
4. If all Db2 applications can be bound and run at the target function level, modify the APPLCOMPAT and SQLLEVEL subsystem parameter settings. Otherwise, leave these fields unchanged.
5. Proceed through the remaining panels, and wait for the CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process.
If the target function level is V12R1M502 or higher, run the following jobs:
1. Run the DSNTIJA1 job to issue an ACTIVATE command to activate function level 501.
  Activation of function level 500 or 501 is required before you can tailor the Db2 catalog for function level 502 or higher.
2. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
3. Run the DSNTIJTC job to run the CATMAINT utility to tailor the Db2 catalog for the target function level.
  
  Important: Do not attempt to start Db2 at a lower code level after any part of the CATMAINT job for a higher function level completes. Run the CATMAINT job only after you are satisfied that Db2 can continue to run at the necessary code level. The code to tolerate catalog changes is contained in the code level that delivers the CATMAINT job.
4. If the target function level is V12R1M510, complete the steps described in Function level 510 (V12R1M510) (activation enabled by APAR PH33727 - April 2021).
Optional: Test the activation of the target function level, as described in Testing Db2 function level activation.
Run the DSNTIJAF job to issue an ACTIVATE command for the target function level.
If the command completes successfully, Db2 issues message DSN9022I. However, if any active member of the data sharing group is running on a previous release, the command fails, and Db2 issues message DSN9039I . Message DSNU757I indicates the result of the activate command.
For example, the following message indicates successful activation of function level 500:
```
DSNU757I  -DB2A DSNUGCCA                                                   
*** BEGIN ACTIVATE FUNCTION LEVEL (V12R1M500)                              
                FUNCTION LEVEL (V12R1M500) SUCCESSFULLY ACTIVATED          
                CATALOG LEVEL(V12R1M500)                                   
                CURRENT FUNCTION LEVEL(V12R1M500)                          
                HIGHEST ACTIVATED FUNCTION LEVEL (V12R1M100)                
                HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M500)               
DSN9022I  -DB2A DSNZACMD '-ACTIVATE FUNC' NORMAL COMPLETION
```
More actions are required after the ACTIVATE command completes successfully before most types of new capabilities and enhancements in the function level can be used. For example, new SQL capabilities require that applications use the appropriate application compatibility level, and optimization enhancements apply only after full prepare of the SQL statements.
After you are ready for applications to use the new capabilities in the function level, rebind them at the corresponding application compatibility level. For more information, see Controlling the Db2 application compatibility level.
Tip: Proceed with the following steps only if all Db2 applications can be bound and run at the target function level.
Optionally, when you are ready for all applications to use the new capabilities of the target function level, you can run the following jobs:
1. Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
2. Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter changes online.
3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNTIP00.
Customize and run job DSNTIJXA to update EXPLAIN tables and related objects to the format for Db2 12 function level 501or higher.

Important: Verify that the PTF is applied for APAR PH48053 before completing this step.

What to do next

Complete any of the following actions:

If the new function level includes optimization enhancements, Db2 must process a full prepare before any SQL statements can benefit. Whether a full prepare occurs depends on the statement type:
- For static SQL statements, after bind or rebind of the package
- For non-stabilized dynamic SQL statements, immediately, unless the statement is in the dynamic statement cache
- For stabilized dynamic SQL statements, after invalidation, free, or changed application compatibility level
If you did not run the jobs to update the APPLCOMPAT and SQLLEVEL subsystem parameters, resolve any application incompatibilities and increase the application compatibility level of your applications after you are satisfied that Db2 is stable at the target function level, as described in Controlling the Db2 application compatibility level.
If you encounter regressions or other problems when you activate a Db2 12 function level, minimize the impact to your applications while you resolve the problems by following the general approaches described in Responding to problems after function level activation.
You might need to update COPY, RECOVER, and REBUILD INDEX utility jobs to add new or remove obsolete Db2 catalog objects. When a utility encounters the following situations for Db2 catalog objects, it issues message DSNU1530I:
- The object is no longer used or no longer exists.
- The object is created in a higher catalog level or function level.
For best results, make these updates the utility jobs after you activate the function level that the changes support. For more information about the catalog changes in Db2 12, see Catalog changes in Db2 12.