How to Generate, Save and Re-use the Db2 DDL and SQL That Are Needed to Install Optim z/OS

Steps

The utility being introduced to generate and save the DDL and SQL statements is the FOPINS2 CLIST.  It is found in the Optim SFOPCLST library and is executed directly by invoking it via the command “EXEC data.set.name(FOPINS2)” within TSO.  Prior to invocation it requires the specification of the Optim SFOPLLIB, SFOPINST, SFOPPENU, SFOPCLST and SFOPMENU libraries which are assigned to internal variables used by it.  To do so, manually edit FOPINS2 and navigate to the following lines to specify your data set names:

/* Set the following DSN values to those of your Optim SFOP* DSNs */

SET SFOPLLIB = &STR('your.Optim.SFOPLLIB')                

SET SFOPINST = &STR(' your.Optim.SFOPINST')                

SET SFOPPENU = &STR(' your.Optim.SFOPPENU')               

SET SFOPCLST = &STR(' your.Optim.SFOPCLST')               

SET SFOPMENU = &STR(' your.Optim.SFOPMENU')               

Upon invocation of FOPINS2, a panel is displayed with prompts for the user to specify the DB2 subsystem name, its release, the Optim plan name, the plan owner and qualifier, a secondary SQLID to be used for the DDL/SQL statements and the DSN of the partitioned data set the generated statements will be saved to.  If the target subsystem is at the version 12 level the DB2 Function Level will also be specified.  All but the plan owner, plan qualifier and secondary SQLID values are required (see figure 1).  After providing the values needed, pressing ENTER will display a menu with the individual steps to be completed to generate the DDL/SQL statements and save them in the PDS specified on the previous panel (see figure 2).  Help for each panel is available by pressing the PF1 key or issuing the ‘HELP’ primary command.  Selection of the options displayed will result in the display of panels containing fields for the various parameters required to create each object to be entered.  By executing each of the steps shown the DDL/SQL will be generated and displayed via an ISPF edit session allowing the user to review it, make any changes necessary and add comments.  Once all of the steps have been successfully performed and the statements have been executed successfully, Optim will now have all of the DB2 objects it needs to function in a basic capacity.  The tasks that must be completed using the FOPINSTL CLIST will finalize the installation.  These tasks are outlined below.

FOPINSTL tasks to be completed following execution of FOPINS2 to complete a full installation:

Step 9:   Unload Optim Sample Data and ODM Optim Connect (if being installed)

Step 10: Customize Optim Site Options

Step 11: Setup Optim Directory and Sample Database subtasks as follows:

Step 1 - Create/Load the Sample Database (optional, will only require execution of Step 4: Loading of the Sample Tables)

Step 2 - Load Sample Access Definitions and Column Maps (optional)

Step 3 - Create Copies of DB2 Relationships (optional)

Step 4 - Create OPTIM Primary Keys for all Unique Indices (optional)

If Optim’s Legacy component is licensed and being installed steps 5 through 8, which are optional, must be completed:

Step 5 - Create Legacy VSAM Sample Files   

Step 6 - Load Legacy VSAM Sample Data      

Step 7 - Create Legacy IMS Sample Databases

Step 8 - Load Legacy IMS Sample Data

Step 12: Customize the Batch Execution Environment

Please read the following notes relating to the tasks outlined above.

• Please refer to the Optim for z/OS Customization Guide, chapter 4 for explanations and details on how to complete each of the above tasks. 
• Note that once the Optim Site Options have been customized, SFOPLLIB member FOPMDFLT can be used as a model to create individualized site defaults modules.  Please refer to chapter 9 for details on how to do this if desired.
• Step 11 sub steps 3 and 4 can also be performed using the JCL marked UTIL3FK and UTIL3PK in Appendix ‘A’ of this document.
• Optim makes use of ISPF skeletons in order to generate the JCL needed to process requests in the batch environment.  You must complete step 12 in order to make these skeletons useable as they define certain parameters and library definitions storing them in the skeleton members.
   

Figure 1 – FOPINS2 Main Panel

Figure 2 – FOPINS2 Task Menu Panel