Skip to content

Support Use of zFS File Type for SP Components

Overview

Native SQL Db2 stored procedures can have names up to 128 bytes in length and can be case sensitive. PDS member names are restricted to a maximum length of 8 bytes, which are not case sensitive.

To remove the restriction of PDS member names on the names of Native SQL stored procedures, ZMF allows stored procedures with long names to be stored and managed in ZMF as zFS-based library types.

Component Design

The following aspects of component design enable ZMF to store and manage Native SQL stored procedures as zFS-based library types.

ZMF Administrative Functions

You define a zFS libtype with a Db2 subtype of N. Follow the instructions in the ChangeMan ZMF Administrator’s Guide to copy global library type definitions into the application - Library Types Part 1 of 2 (CMNCLLT0) panel. See the Db2 library type NSZ in the following examples:

CMNCLLT0           STEV - Library Types Part 1 of 2           Row 1 to 28 of 28 
Command ===> _________________________________________________ Scroll ===> CSR

    Lib                                       Order Lke Seq Defer Target Sel 
    type Description                         +                    type   Opt
___ NSZ  Native SQL stored procs hosted by zFS    0   P       Y           D

CMNDGLT0                STEV - Db2 Library Types             Row 11 to 11 of 11
Command ===> _________________________________________________ Scroll ===> CSR 

Lib                                                        Sub  End SQL
type     Description                                       type sentence
NSZ      Native SQL stored procs hosted by zFS              N      @

And define the target directories as active:

                    Db2 Active Library/Directory List         Row 8 to 28 of 38 
Command ===> _________________________________________________ Scroll ===> CSR 

     Logical
     name     Type    Db2 active library or directory name
     PRODLCL1  S      /cmndev/STEV/U900DP/Prod00/NSZ                   +
     PRODRMT1  S      /cmndev/STEV/U900P/Prod00/NSZ                    +
     UNIT1     S      /cmndev/STEV/promo10/NSZ +

The usual invocation of CMNDB2DD to process these components will be generated for the various actions, for example, promote/demote, install/backout, and so on.

CMNDB2DD Program Processing

This program (member CMNDB2DD of the CMNZMF.LOAD distribution library) detects whether the SQLIN and STGLIB input DDnames point to a library or a zFS path name. Appropriate I/O routines are used to access the stored procedure definition.

If PASSTHRU=YES is selected, the output (templated) procedure definition is written either to a library member or a to a zFS file depending on what is allocated to the xxxxOUT DD statement.

A synonym for the MBR= sysin parameter is CMP=. (They mean the same thing: CMP=longNameStoredProcedure; or MBR=longNameStoredProcedure).

The data interface to the High-Level Language (HLL) exit includes a long component name (Language Environment [LE] field name is DB2DCMPL, REXX variable name is 'longComponent'). The original short name fields remain in place (DB2DCMPN and REXX 'component'). The long format name is always filled in. The short format name is only filled in if the component name is 8 or fewer bytes in length.

Skeletons

The following skeletons use PATH= and PATHOPTS DD parameters when the indicated library type is a zFS libtype.

  • CMN$$PSQ (local promote/demote)

  • CMN$$RSQ (remote promote/demote)

  • CMN$$SQL (install/backout)

CMNSTGER Processing

The ISPF function program CMNSTGER, which allows you to stage a component directly from Db2 into a package, has been changed to handle a target zFS libtype, as shown in the Stage Native SQL SP from Db2 (CMNSTG25) panel in the following example:

CMNSTG25                           Stage Native SQL SP from Db2 
Command ===>   

        Package: STEV001561           Status: DEV        Install date: 20221111 

Stored Procedure:  
    Db2 id . . . . . D10L 
    Location . . . . 
    Schema . . . . . WSER58                                                   +  
    Name . . . . . . "zFSnativeSQLstoredProcedure001"                         + 
    Version  . . . .                                                          + 
    Version Ind  . . 

Component:  
    Name  . . . . .                                                           + 
    Library type. . NSZ 

Enter "/" to select option  
  / Add package information to component  
  _ Lock component in package  
  / Mixed Case

The following messages are displayed when panel content is processed:

CMN408I - STEV001561 Component zFSnativeSQLstoredProcedure001.NSZ 
    ACTIVATED 20200522 085455. CN(INTERNAL)

*** 
and:

CMN2575I - zFSnativeSQLstoredProcedure001 component staged from D10L

If the component name is missing, the same name as the stored procedure name is used but with any double or single quotes removed.

CMNVCOMP Component Checkin Service

This service also allows for the checkin of long name stored procedure components direct from Db2 into a zFS library type. The double quotes on the stored procedure name are not actually necessary: both the ISPF client and the service itself will strip both double and single quotes from stored procedure names before using them. The quotes are tolerated because Db2 requires them to avoid folding everything to upper case. We rely on the MIXCASE variable in the ISPF client and just take names as is (no folding) in the service.

<?xml version="1.0"?>
<service name="CMPONENT">
    <scope name="SERVICE">
        <message name="CHECKIN">
            <header>
                <subsys>L</subsys>
                <product>CMN</product>
            </header>
            <request>
                <package>STEV001561</package>
                <component>"zFSnativeSQLstoredProcedure001"</component>
                <componentType>NSZ</componentType>
                <chkInSourceLocation>D</chkInSourceLocation>
                <sourceDb2Subsys>D10L</sourceDb2Subsys>
                <sourceDb2Schema>WSER58</sourceDb2Schema>
                <addZmfInfoToDb2Object>Y</addZmfInfoToDb2Object>
                <listCount>00001</listCount>
                <targetComponent>zFSnativeSQLstoredProcedure001</targetComponent>
            </request>
        </message>
    </scope>
</service>

Backward Compatibility

All existing PDS member based facilities are unchanged.

Installation and Configuration

No environmental changes are needed to implement this enhancement to stored procedure names. You need only define one or more zFS library types to be Db2 enabled with a Db2 subtype of N (native SQL stored procedure).