Migrating from IBM Db2 AI for z/OS 1.6.0 maintenance level 2 to IBM Db2 AI for z/OS 1.6.0 maintenance level 3

You can migrate your installation of IBM Db2 AI for z/OS 1.6.0 plus APARs PH59095, PH59096, and PH60511 (maintenance level 2) to IBM Db2 AI for z/OS 1.6.0 plus APAR PH60101, PH61349, PH61372, PH61373, and PH62655 (maintenance level 3).

Before you begin

Important: If you have not already done so, you must apply the required Db2 APARs for your target Db2 connections prior to migrating to IBM Db2 AI for z/OS 1.6.0 plus maintenance level 3. See Hardware and software requirements for the Db2ZAI installation for a list of the required Db2 APARs.

Note: It is best practice to migrate to the latest available Db2ZAI APAR level.

About this task

Any benefits you derived from the Db2ZAI SQL optimization function prior to migrating to maintenance level 3 will be retained after migrating to maintenance level 3.

However, in order to reap any additional benefits after applying maintenance level 3, you will need to follow the new process for improving query performance (see Improving query performance).

Note: Immediately after migration to Db2ZAI maintenance level 3, you will not be able to explore dynamic statements until Db2ZAI captures the information from a full prepare.

Procedure

To migrate from IBM Db2 AI for z/OS 1.6.0 maintenance level 2 to IBM Db2 AI for z/OS 1.6.0 maintenance level 3:

Db2ZAI 1.6.0 requires that IBM® z/OS maintenance be applied where Db2ZAI tasks run. See Hardware and software requirements for the Db2ZAI installation for a list of the z/OS PTFs to be applied.
Stop ML and stop Db2ZAI:
- Use the Db2ZAI user interface to stop ML on all target Db2 systems.
- Stop the Db2ZAI server processes (run $DB2ZAI_HOME/scripts/db2zai_services_stop, or stop using JCL). See Stopping the Liberty and Node.js servers with JCL or Stopping the Liberty and Node.js servers with USS.
Upgrade Db2ZAI:
1. (Optional) Back up the $DB2ZAI_HOME directory or the entire DB2ZAI_HOME zFS, as well as the Db2ZAI metadata database. See Backing up IBM Db2 AI for z/OS.
2. Complete the initial customization of JCL including variables for batch jobs:
  1. Customize hlq.SCOYSAMP(COYIVMC). COYIVMC is included in multiple hlq.SCOYSAMP(COYU*) jobs.
3. Run the batch jobs to upgrade the metadata Db2. Customize and run each of these jobs:
  - COYU061A – update the catalog level of the Db2ZAI connection.
  - COYU061B – clean up the timer tables.
  - COYU061C – update the metadata catalog.
  - COYU061D – reorg to remove pendings.
  - COYGMSID – grant job for the Db2ZAI metadata scheduler ID.
  Note:
  If you have multiple target connections created in Db2ZAI, you will need to submit job COYU061A once per target connection, modifying the CONNNAME in COYU061A to match the name of the target connection. For example, if you have two connections, you will need to edit this line, adding the first connection name and running the job then editing the line to add the second connection and running again:
```
//COYSYM0A SET CONNNAME=CONNNAME
```
4. Run the update.sh script in $DB2ZAI_INSTALLATION/configuration/scripts to update the Db2ZAI configuration to the new version (config.json + script directory):
```
./update.sh migrate
```
  Note:
  PH60101 applies a new version of the JDBC driver that encrypts the user ID and password by default. Decrypting the user ID and password requires a fully configured ICSF with an available Cryptographic Express Coprocessor.
  
  If a Cryptographic Express Coprocessor is not available in your environment, when running the update.sh script to migrate you can add the following additional property (when prompted):
```
securityMechanism=3;
```
  This tells the JDBC driver to pass the user ID and password in the clear, which removes the requirement for ICSF.
Upgrade the target Db2 catalog objects to the new version. Repeat these steps for each target Db2:
Note: Apply the Db2 SMP/E PTF(s) listed in the Hardware and software requirements topic to the target Db2 subsystems to support this Db2ZAI maintenance. See Hardware and software requirements for the Db2ZAI installation.
1. (Optional) Back up the existing target database. See Backing up IBM Db2 AI for z/OS.
  Note: If you choose not to back up your current installation, after you migrate you won't be able to fall back to that prior level.
2. Complete the customization of any new JCL include variables for batch jobs:
  - Customize hlq.SCOYSAMP(COYIVPC). COYIVPC is included in multiple hlq.SCOYSAMP(COYV*) jobs:
3. Customize and run the batch jobs provided in hlq.SCOYSAMP.
  1. COYV061A – create new indexes.
    Note: When you rerun COYV061A, you may see a SQLCODE -601. This error can be ignored.
  2. COYV061B – update stored procedures.
    Note: The JCL job COYV061B is required for APAR PH57810. If you ran this job previously, you don't need to run it again.
  3. COYV062A – update stored procedures.
    Note: The JCL job COYV062A is required for Db2ZAI 1.6.0 APAR PH59096. If you already ran this job previously, you don't need to run it again.
  4. COYV064A – update the target catalog.
  5. COYV064B – new target catalog.
  6. COYV064C – update predefined data.
  7. COYV064D – update procedures.
  8. COYV064E – migration data for SQL optimization.
  9. COYV064F – reorg to remove pendings.
  10. COYIPC04 – create alias for explain tables.
    Note: Before running COYIPC04, make sure that the SCHLERID in COYIVPC is set to the correct scheduler ID for the creation of connections and that the ID used to submit COYIPC04 has the privileges required to create an alias for the metadata scheduler ID.
Run the updated grant jobs for the various Db2ZAI users, including the Db2ZAI metadata administrator, the Db2ZAI target catalog administrator, the Db2ZAI target scheduler ID, and the Db2ZAI end users. See User types and roles to see the jobs to be run.
Start the server processes at the new release level using the JCL PROC or from the USS script. See Starting the Liberty and Node.js servers with JCL or Starting the Liberty and Node.js servers with USS.

Note: Make sure to update the current JCL PROC with the latest version or use a USS script to start the server processes.
Restart ML on all target Db2 systems using the Db2ZAI user interface.
Note:
As with step 3.d above, if a Cryptographic Express Coprocessor is not available or ICSF has not been configured in your environment where the target Db2 systems run, before starting ML, you should edit the target connections of those Db2 systems to add an additional JDBC property:
```
securityMechanism=3;
```
See Editing connections for instructions on how to edit target connections.
Follow the process described in Granting permission to access the Db2ZAI external services to grant permission to access the Db2ZAI external services, including the SQL optimization external services and all of the update actions that are available within the SQL optimization user interface.