Chapter 4: Installing APS/SQL Subschema Maintenance Utilities

This chapter contains information about installing APS with SQL subschemas.

APS/SQL Installation Requirements

Hardware and Software Requirements

APS/SQL Subschema Utilities run on any processor that supports:

• MVS, MVS/XA or MVS/ESA
• ISPF/PDF Version 2 (or higher)
• DB2 Version 1 Release 2 (or higher)

Virtual Storage Requirements

The same as those for APS for z/OS.

DASD Space Requirements

Approximately 1,250 tracks on a 3380 disk.

• 150 tracks for the APS/SQL Subschema Utilities software data sets (included in the APS Install library space estimate)

• 800 tracks (primary DB2 tablespace allocations) for the APS/SQL common data area

• 300 tracks (secondary DB2 tablespace allocations) for the APS/SQL common data area

Installing for the First Time

To establish the SQL components of APS/SQL Subschema Maintenance Utilities, you

• Create the APS/SQL common data area.

• Bind the Subschema Utilities application plans.

• Grant execution authority on the Subschema Utilities plans.

• Link the Subschema Utilities.

The installation process takes you through a number of tailoring and data collection steps. When you complete these steps you have generated

• JCL (DB2SDYNM) to bind the Dynamic SQL Processor executed by DB2SMAKE and DB2SGRNT (see below)

• JCL (DB2SLINK) to relink the Subschema Utilities load modules

• JCL (DB2SMAKE) to generate the DB2 tables for the APS/SQL common data area

• JCL (DB2SBIND) to bind the Subschema Utilities application plans with the APS/SQL common data area

• JCL (DB2SGRNT) to grant the DB2 authority necessary to access the APS/SQL common data area and the DB2 catalog and to execute Subschema Utilities

• ISPF panels necessary to execute the Subschema Utilities

• JCL in appropriate skeleton libraries to your specifications

You can use the CLISTs, panels, and skeletons as is, or you can modify them. We recommend testing the generated version of the utilities before customizing.

If you have never installed any version of the Subschema Maintenance Utilities, perform the following steps.

Step 1. Build Profile Variables

Access the APS Install Menu screen (=A.4.2.1.3.2) to specify DB2 libraries and system defaults for the APS/SQL installation. Process the BUILD option from the DB2 Install Menu and from the APS Install Menu.

Note: If you supplied and processed DB2 information when completing See Step 5. Execute the APS@PROC CLIST it is not necessary to do this step.

Step 2. Link the Utilities

If you are installing or reinstalling, link the APS/SQL Subschema Maintenance Utilities system software load modules. The APS/SQL modules must be link-edited with the IBM/SQL interface modules (DSNHADDR and DSNELI) and error handling module (DSNTIAR).

Data set &APSPRE.ISPSLIB(DB2SLINK) contains JCL to link APS/SQL modules to the IBM/DB2 interfaces. Edit this JCL according to the comment cards and submit this job.

Step 3. Bind the SQL Processor

You must bind the APS dynamic SQL processor, A1DPUTL, used by the JCL members DB2SMAKE (see step 4) and DB2SGRNT (see step 4). The JCL executes the SQL statements that create the APS/SQL common data area and grant authority for its use. Bind as follows.

Edit the data set &APSPRE.ISPSLIB(DB2SDYNM). Follow the directions for job card modification in DB2SDYNM.

Submit the job for execution. It is not necessary to grant execute authority on the A1DPUTL plan if the same user ID used when executing DB2SMAKE and DB2SGRNT is used for the DB2SDYNM JCL.

Step 4. Create Data Area and Table Views

You must create the APS/SQL common data area and the catalog table views. The common data area is a DB2 data base which is a physical and logical subset of the APS Application Dictionary.

1. Edit the data set &APSPRE.ISPSLIB(DB2SMAKE). Follow the directions for job card modification in the DB2SMAKE JCL. DB2SMAKE allocates a storage group, APSENC06. This allocation should either be
   • Modified to use volumes available at your installation.

   • Removed entirely from the input cards in DB2SMAKE. If you remove APSENC06, change all occurrences of APSENC06 to point to an existing storage group at your installation.

2. Submit DB2SMAKE for execution; it must complete with condition code zero (0).

3. If a CREATE fails, investigate the reason for the failure and correct it. Then, modify and submit &APSPRE.ISPSLIB(DB2SDROP), which drops the DB2 objects created by DB2SMAKE. If you need assistance, call the APS Answerline.

If you prefer SPUFI as the mechanism for processing DB2 DDL statements, copy the SYSIN control cards used in DB2SMAKE in pieces or as a whole into a separate data set and execute them interactively.

Note: Removing the COMMIT statements is optional. Optimal SPUFI performance can be achieved if they are left in the DDL statements for DB2SMAKE.

Step 5. Bind the Utilities

Bind the APS/SQL Subschema Utilities system software application plans as follows.

1. Edit the data set &APSPRE.ISPSLIB(DB2SBIND), following the directions for job card modification.

2. Do not change the bind parameters or plan names used in DB2SBIND. These plan names are created by the BUILD process executed during the interactive portion of the install. If the plan names generated in the DB2SBIND JCL are not acceptable, return to "Step 1. Build Subschema Utilities Profile Variables" and provide a different DB2 plan prefix.

3. Submit DB2SBIND for execution; it must complete with condition code zero (0) for each bind step.

4. If any bind step fails, investigate the reason for the failure and correct it. Then, resubmit the DB2SBIND.

   ---

   Note: Do not alter the Subschema Utilities plan suffixes.

   ---

5. When DB2SBIND is complete, execute the Subschema Utilities from the TSO user ID you used to create the APS/SQL common data area and to bind the Subschema Utilities application plans.

Step 6. Grant Utility Authorization

You must grant authorization for the subschema utilities to allow

• Execution of utility programs

• Access to the APS/SQL common data area

• Access to the DB2 system catalog tables referenced by the Subschema Utilities

• Edit the data set &APSPRE.ISPSLIB(DB2SGRNT). Follow the directions for job card modification in DB2SGRNT.

• Modify the grant cards in DB2SGRNT to restrict authority to certain users. In general, all authority for the Subschema Utilities is granted to PUBLIC (that is, all users with access to DBP).

• Submit DB2SGRNT for execution; it must complete with condition code zero (0). When DB2SGRNT is complete, the users specified in the grant cards can execute the Subschema Utilities.

If you prefer SPUFI for processing DB2 DDL statements, copy the SYSIN control cards used in DB2SGRNT in pieces into a separate data set and execute interactively.

Note: Removing the COMMIT statements is optional. You can achieve better SPUFI performance if they are left in the DDL statements for DB2SGRNT.

Step 7. Utility Security

Security for the utilities is enforced through the native DB2 grant authorization mechanism. The DB2 grant statements give the authorization necessary to browse objects from the utilities in the DB2 catalog and to browse the APS/SQL common data. DB2 grant statements provide authorization for a specific DB2 object type, for example, a database or a table.

You must decide who receives authority and for which objects. You assign SELECT authorization on specific DB2 system tables for the Subschema Utilities application plans. These authorizations are in addition to those provided by the DB2SGRNT JCL. The user ID binding the Subschema Utilities application plans using the DB2SBIND JCL must have SELECT authorization on the following DB2 system tables.

• SYSIBM.SYSCOLUMNS
• SYSIBM.SYSDATABASE
• SYSIBM.SYSFIELDS
• SYSIBM.SYSFOREIGNKEYS
• SYSIBM.SYSINDEXES
• SYSIBM.SYSINDEXPART
• SYSIBM.SYSKEYS
• SYSIBM.SYSPLANDEP
• SYSIBM.SYSRELS
• SYSIBM.SYSSTOGROUP
• SYSIBM.SYSSYNONYMS
• SYSIBM.SYSTABLEPART
• SYSIBM.SYSTABLES
• SYSIBM.SYSTABLESPACE
• SYSIBM.SYSTBAUTH
• SYSIBM.SYSVIEWDEP
• SYSIBM.SYSVIEWS
• SYSIBM.SYSVOLUMES

Step 8. Link EXPLAIN

This facility is for use with Online Express only. If you are installing or reinstalling the EXPLAIN Facility, link the EXPLAIN modules. The data set &APSPRE.ISPSLIB(A2OXLINK) contains JCL to link EXPLAIN modules. Edit the JCL according to the comment cards and submit the job.

Step 9. Bind EXPLAIN

If you are installing or reinstalling the EXPLAIN facility, bind the EXPLAIN facility application plan.

1. Edit the dataset &APSPRE.ISPSLIB(A2OXBIND). Follow the directions for job card modification.

2. Do not change to the bind parameters in A2OXBIND. These parameters retain authorizations granted during previous installations of the EXPLAIN facility. Therefore, when reinstalling, it is not necessary to execute A2OXGRNT (see See Step 10. Grant EXPLAIN Authorization).

   Do not change the plan names in A2OXBIND. These names are created by the BUILD process executed during the interactive portion of the install. To change the plan names generated in A2OXBIND JCL, return to A2OXBIND and provide a different DB2 plan prefix.

3. Submit A2OXBIND for execution.

Note: Do not alter EXPLAIN facility plan suffixes.

Step 10. Grant EXPLAIN Authorization

If you are installing the EXPLAIN Facility for the first time, grant authorization to allow execution of the EXPLAIN facility modules. If you are reinstalling the EXPLAIN facility, skip this step.

1. Edit the data set &APSPRE.ISPSLIB(A2OXGRNT).

2. Edit the JCL according to the comment cards and submit the job. This data set contains JCL to grant authorization for the EXPLAIN modules.

3. If you want to identify specific authorized users, modify the grant card in A2OXGRNT to your requirements. Otherwise, authority for the EXPLAIN facility is granted to PUBLIC.

Verifying APS/SQL Subschema Maintenance Utilities

First, create the sample SQL subschema, D2MASTER, using the Subschema Maintenance Utilities, and import the subschema into APS. You use the subschema, D2MASTER, when you execute the following sample SQL applications.

• ISPF Prototype/SQL
• ISPF Dialog/SQL
• IMS/SQL (if you are using Online Express)
• CICS/SQL

Verify Utility Access

1. Go to the APS Project Group Environment screen and make sure the Project and Group is set to the demo Project and Group created at installation (=A.0.2).

2. Go to the APS Generator Options screen (=A.0.1). Set the Target field to MVS and the SQL field to DB2.

3. Go to the APS Utilities Menus (=A.4).

4. Select option 4, SQL Subschema Maintenance Utilities.

5. Select option B, Browse Facility. APS displays the DB2 Browse menu.

   If the Browse menu does not display, APS displays one of the following error messages.

   • Subsystem Connection Failure. Check that the DB2 subsystem upon which the utilities was installed is operational. If not, restart the subsystem.

     Verify that the DB2 subsystem identifier specified during the APS for z/OS installation process is the actual subsystem identifier for your use. To verify this value, browse the member, APS 5000, in the library &PROJECT.&GROUP.GENPANEL and find the ISPF variable assignment for &SSMBSYSN. The &SSMBSYSN value should be the name of the DB2 subsystem where the utilities execute. If this value is incorrect, access the APS Install Menu by starting at the APS Main Menu and selecting the following options one by one: 4, 2, 1, 3, 2. Then specify the correct subsystem identifier.

   • DB2 SQL error. If an error may have occurred during the execution of the DB2SBIND JCL, correct the problem and rerun the DB2SBIND step.

     The Subschema Maintenance Utilities load library, &APSPRE.ISPLLIB, might not be allocated properly. Check the allocations for the utilities data sets. Verify that the ISPLLIB is allocated and that the DCB information for data sets also allocated does not conflict with the utilities load library DCB.

6. Enter the END command repeatedly until you return to the Main Menu.

7. Select option 0, Options

8. Select option 6, Job Control Cards. Modify the job cards to meet the requirements of your installation.

9. Enter the END command repeatedly until you return to the Main Menu.

Import a DB2 Database and Create a Subschema

First import the DB2 database, APSDMODB, to the APS/SQL common data area. Then, create the SQL subschema, D2MASTER, populating it with two tables from the common data area. Finally, import the subschema using the APS SQL Importer. The import process generates the DDI symbols and DCLGEN copybooks necessary for executing subsequent sample applications.

Create DDL statements

1. To access the APS/SQL Subschema Maintenance Utilities screen, type =A.4.4 on the APS Main Menu.

2. Select option I, DB2 Interactive Facilities.

3. Select option 1 and execute SPUFI using the member DB2LOAD, which is stored in the APS-supplied partitioned data set &PROJECT.&GROUP.CNTL.

4. Press PF3 and then press Enter.

5. Enter the END command repeatedly until you return to the Main Menu.

Import the APSDMODB database

1. From the APS Main Menu, select option 2, Dictionary Services.

2. From the Dictionary Services Menu, select option 1, Import Facilities.

3. From the Import Facilities screen, select option 1, SQL.

4. Select option 1, SQL Importer. The DB2/DB2P Object Import screen displays.

5. Type the following values for fields on the DB2/DB2P Object Import screen.

   Field	Value
   OBJECT NAME	Leave blank to display a list of the databases defined to the DB2 catalog.
   JOB CLASS	Specify a job class valid for your site.
   OBJECT ONLY	N
   REPORT ONLY	N
   SUBMIT JOB NOW	Y

6. Press Enter, to display the list of databases.

7. Select APSDMODB. Type S to the left of its name and press Enter.

8. Enter the END command twice to submit a job that imports APSDMODB.

9. Enter the END command repeatedly until you return to the Main Menu.

10. Go to the APS/SQL Subschema Maintenance Utilities screen (=A.4.4) to create the subschema D2MASTER to APS.

11. Select option S, APS/SQL Subschema Painter.

Create the D2MASTER subschema

1. Type A (for Add) in the OPTION field, type the name D2MASTER in the SUBSCHEMA NAME field, and press Enter to display a list of the tables defined in the APS/SQL common data area; the list displays on the Table/View List screen.

2. Select tables D2MASTER and D2STOCK by typing an S beside each table name and pressing Enter.

3. Enter the END command to display the Subschema Tables screen, which lists the two tables you just selected for the D2MASTER subschema.

4. Assign a copybook name for each table. The names should be the same as the table names. Rather than manually typing the names, you can type an equal sign (=) next to each table name and press Enter. This automatically inserts the table names into the COPYBOOK fields.

5. Enter the END command to add the D2MASTER subschema to the APS/SQL common data area.

6. Continue entering the END command until you return to the APS Main Menu.

Generate symbols and copybooks

1. Go to the APS SQL Importer screen (=A.2.1.1) to generate DDI symbols and DCLGEN copybooks for the D2MASTER subschema.

2. Type D2MASTER in the MEMBER field, select option 2, Generate DDISYMB symbols from DDIFILE, and press Enter. APS submits a batch job to create the DDISYMB symbols and copybooks.

Copyright © 2002 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.