Creating standalone Db2 subsystem provisioning services

• Enable Cloud Provisioning Software Services in the z/OSMF server.

• Enable a network resource pool (NRP) in the z/OSMF server, with a port allocation range enough for the number of instances provisioned. Each Db2 subsystem requires two ports, a port for DRDA and REST services and a RESYNC port.

• Provide the SMP/E Db2 product target libraries, with the the following Db2 12 APARs applied: PI85657, PI97635, PI99403, PH02971, PH05259, and PH06733; and if Db2 REST services will be enabled on the provisioned Db2 subsystems, APARs PI70652 and PI96649.

• Certify that the SMP/E Db2 product target libraries for SDSNEXIT, SDSNLINK, SDSNLOAD, SDSNLOD2 and IRLM RESLIB are APF-authorized Note: SDSNLOD2 is a PDSE data set, which contains JDBC and SQLJ DLLs. Although DB2 does not require that SDSNLOD2 be APF-authorized, be aware that if this data set is in a STEPLIB data set concatenation of an address space that does need APF authorization, SDSNLOD2 must also be APF-authorized. The provisioning template concatenates SDSNLOD2 when verifying JDBC local connection (Type-2) in Optional Features.

• Provide data set names, including for host languages (see Section 7: Host language data sets, in the dsntivin file.)

• Provide directories for the following installed FMIDs:

  • JDBCC12 for Db2 JDBC/SQLJ. All variables must be set in Section 6: Db2 Java properties, in the dstnivin file.

  • JDBCC17 for Db2 ODBC. The following variables must be set in Section 7: Host language data sets, in the dstnivin file: CCOMP, CPPAUTCL, LELKED, LEPLMSGL, and LERUN.

  • HDDA211 for z/OS Application Connectivity.

• Verify installation, and provide directories where indicated, for the following installed FMIDs:

  • JDBCC12 for Db2 JDBC/SQLJ. All variables must be set in Section 6: Db2 Java properties, in the dstnivin file.

  • JDBCC17 for Db2 ODBC. The following variables must be set in Section 7: Host language data sets, in the dstnivin file: CCOMP, CPPAUTCL, LELKED, LEPLMSGL, and LERUN.

  • HDDA211 for z/OS Application Connectivity.

  • HDBCC1K for Db2 Utilities Suite for z/OS.

• Provide a range of TCP/IP ports to be used under the Network Resource Pool (NRP). The ports in this range cannot be part of the TCP/IP Profile.

• Provide the Db2 authorization IDs in the following table for Section 4: Db2 authorization IDs in the dsntivin file:

• Define RACF STARTED class profiles to all potential provisioned Db2 subsystem instances associating an ID to be used by each Db2 address space.

• Define RACF DSNR class profiles to control access to any provisioned Db2 subsystem from another environment, such as CICS, IMS, TSO, RRS, BATCH, DDF and REST services.

• Define RACF SERVER class profiles to control access to any provisioned Db2 subsystem because they will use stored procedures in a WLM-established address space.

• Define SMS constructs, such as SMS classes and storage groups, for Db2 provisioning. The SMS storage groups can be per instance or shared by all potential provisioned Db2 instances.The storage administrator can decide if image copy data sets and archive log data sets are to share the SMS storage groups with other Db2 data sets.

• Together with the security administrator, provide access authorization to all prefixes in the following table to the Db2 IDs, including the ID that executes the steps of the Db2 provisioning workflow.

• Define ACS routines to be used to determine the SMS classes and storage groups for data sets allocation during a Db2 subsystem provisioning.

• Define USERCATs and ALIASes, associating them to their specific SMS storage group. Important: The provisioning process determines the ssid value. You must do the definition work for all potential instances. If you are allowing 5 instances, then you must have 5 sets of definitions below corresponding to the 5 ssid(s) that can be generated. For example: DY00SYS, DY01SYS, an so on.

(1) The HLQ here will be concatenaded with the instantiated ssid and used as the prefix in all aliases and non SMP/E Db2 data sets.

When you execute the provisioning workflow, you are prompted to specify whether you want to execute the optional features.

To provision a standalone Db2 subsystem, the sample template completes the following steps:

1. Instantiates the Db2 subsystem ID (ssid) being provisioned

2. Sets the variables (section 9 of DSNTIVIN) according to the name convention rules

3. Acquires Db2 ports from the NRP

4. Defines aliases for the Db2 SMP/E target libraries for the instantiated Db2 subsystem

5. Allocates private proclib for instantiated Db2 subsystem

6. Assigns IRLM SSID based on the instantiated Db2 subsystem name

7. Dynamically defines Db2 and IRLM subsystems to z/OS

8. Executes all mandatory steps to install and verify a standalone Db2 subsystem, including: Java definitions, all Db2-supplied stored procedures, SQL Install Verification, Db2 storage groups for user data (ssidUSG), and optionally DDF REST services, ODBC, and JDBC connections (Type-2 and Type-4)

The following actions are available from z/OSMF Cloud Provisioning Software Services for the provisioned Db2 subsystem:

• Start the Db2 subsystem

• Stop the Db2 subsystem

• Display DDF information

• Display group information

• Enable the optional features (ODBC, JDBC, and REST services)

• Deprovision the Db2 subsystem

The deprovision workflow removes all definitions and data sets related to the deprovisioned Db2 subsystem. To deprovision a Db2 subsystem, the sample template completes the following high-level actions:

1. Issues a STOP DB2 command; this step will not fail the workflow execution if Db2 is already stopped

2. Releases DRDA/REST services TCP/IP port

3. Releases RESYNC TCP/IP port

4. Deletes IVP and non-VSAM data sets

5. Deletes JAVAENV data set and the Java environment files

6. Deletes Image Copy data sets

7. Deletes Db2 catalog, directory, BSDS, active & archive log data sets, Db2 user data sets

8. Deletes Db2 start up and WLMENV procedures

9. Deletes the Db2 ZPARM module

10. Deletes WLM application environments

11. Deletes aliases defined against Db2 and IRLM SMP/E target libraries

12. Deletes Db2 and IRLM subsystem definitions from z/OSMF

The JDBC enablement optional feature requires connection to the provisioned Db2 subsystem to perform the BIND for the JDBC packages as well as to verify a remote connection (JCC-Type-4).

When connecting, a user ID and password must be passed to the connection statement. Instead of sending clear text passwords, the sample template uses generated RACF PassTickets. Users of the application can then use the PassTickets to authenticate within a RACF-secured network. This procedure prevents the need to store password credentials within the z/OSMF environment.

You must certify that the ID used to execute the workflows has the RACF authority to generate PassTickets to others.

To enable the usage of RACF PassTicket by the sample template, take the following actions:

1. Activate the RACF PassTicket class, by issuing the following commands:

SETROPTS CLASSACT(PTKTDATA) RACLIST(PTKTDATA) SETROPTS GENERIC(PTKTDATA)

1. Define RACF profiles for the application in PTKTDATA, by issuing the following commands:

RDEFINE PTKTDATA <applName> SSIGNON(KEYMASKED(<key>)) 
APPLDATA('NO REPLAY PROTECTION')

In the preceding example: <applName> is the name of the application that requests and uses the PassTickets. Provisioned Db2 subsystems accept TCP/IP connections only, therefore we should use the value of the the IPNAME as <applName>.

<key> is a session key with the value of 16 hexadecimal digits (for an 8-byte or 64-bit key). The session key must be identical to the key in the PassTicket definition in each RACF instance. The key for each application must be the same on all subsystems in the configuration.

APPLDATA('NO REPLAY PROTECTION') is the option that you can use to permit reuse of the same PassTicket multiple times.

The following example shows these commands for provisioning five Db2 subsystems. As described in the Naming Convention section, the name of the subsystem being provisioned (ssid) is used for the IPNAME value. Because we expect five instances to be provisioned, you can activate them all in one single job, considering that they will be all under the same RACF database.

//STEP01 EXEC PGM=IKJEFT01                     
//SYSTSPRT DD SYSOUT=*                         
//SYSTSIN DD *                                 
  RDEL PTKTDATA (DY00)                         
  RDEL PTKTDATA (DY01)                         
  RDEL PTKTDATA (DY02)                         
  RDEL PTKTDATA (DY03)                         
  RDEL PTKTDATA (DY04)                         
  RDEL PTKTDATA (IRRPTAUTH.DY*.*)              
  RDEF PTKTDATA DY00 -                         
   SSIGNON(KEYMASKED(E001193519561977)) -       
   UACC(NONE) APPLDATA('NO REPLAY PROTECTION')  
  RDEF PTKTDATA DY01 -                         
   SSIGNON(KEYMASKED(E001193519561977)) -       
   UACC(NONE) APPLDATA('NO REPLAY PROTECTION')  
  RDEF PTKTDATA DY02 -                         
   SSIGNON(KEYMASKED(E001193519561977)) -       
   UACC(NONE) APPLDATA('NO REPLAY PROTECTION')  
  RDEF PTKTDATA DY03 -                                             
   SSIGNON(KEYMASKED(E001193519561977)) -                           
   UACC(NONE) APPLDATA('NO REPLAY PROTECTION')                      
  RDEF PTKTDATA DY04 -                                             
   SSIGNON(KEYMASKED(E001193519561977)) -                           
   UACC(NONE) APPLDATA('NO REPLAY PROTECTION')                      
  RDEFINE PTKTDATA IRRPTAUTH.DY*.*                                 
  PERMIT IRRPTAUTH.DY*.* CLASS(PTKTDATA) ID(WFexecutorID) ACCESS(UPDATE)  
  SETROPTS RACLIST(PTKTDATA) REFRESH 

The following tables show the authorizations required for certain steps of the sample template. All other steps run under the authorization ID that executes the workflow. You can specify the authorization ID that executes the workflow in the AGEXECID variable in section 4 of the DSNTIVIN input variable file. If the AGEXECID value is blank, the sign-on ID executes the steps.