Db2 SQL in batch

  • Post category:JCL
  • Reading time:1 mins read

Again a simple solution for a common problem: how to run a Db2 query from a batch script. Here we use the utility DSNTEP2 that is provided for this purpose with the Db2 installation.

In the STEPLIB, specify your names for Db2 runtime libraries.

In the SYSTEM (xxxx) clause specify your Db2 subsystem.

The SQL in the SYSIN label can be taken from in-stream, or from a dataset as below.

//GO EXEC PGM=IKJEFT01,DYNAMNBR=20 
//STEPLIB DD DSN=SYS2.SDSNEXIT,DISP=SHR 
//        DD DSN=SYS2.SDSNLOAD,DISP=SHR     
//SYSTSPRT DD SYSOUT=*  
//SYSTSIN DD * 
 DSN SYSTEM (xxxx)                                       
 RUN PROG (DSNTEP2)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*SYSIN    DD DISP=SHR,DSN=YOURDSN.DB2.SQL(YOURSQL) OR
//SYSIN    DD *
 SELECT * FROM YOURTABLE
/*

//Dean

The new mainframe is full of APIs (and has an extension for Docker Containers)

IBM has announced the new mainframe box, not unexpectedly called z15. The z15 is a huge machine and I would summarize this evolution “bigger and better” without being disrespectful.
Nevertheless, the accompanying announcement of the new release of the flagship operating system for the z15, z/OS 2.4 is massively interesting.

The most eye-catching new feature in z/OS 2.4 is z/OS Container Extensions. With this feature you can run Linux Docker containers under z/OS, next to your existing z/OS applications. Making your Containers thus part of your z/OS infrastructure has some powerful advantages.

  • Very easy integration of containers in the Continuous Availability solution for Disaster Recovery of your z/OS platform provided by GDPS. I think this is by far the most powerful benefit. Thus you can provide Linux on z solutions even more easily than through a native Linux for z deployment.
  • Exploit the capabilities of network virtualization of z/OS.
  • Optimized integration with z/OS workloads.

The second important item to look at is the evolution of z/OSMF. Contrary to what IBM Marketing tells us I believe the second important item in my opinion is not Container Pricing, and not Open Data Analytics on z/OS. The development of z/OSMF is more important because it is fundamentally changing the consumability of the platform, turning the platform into the most open computing platform in the market, through the availability op REST APIs on basically any resource on the platform. And fundamentally transforming the way the platform is managed.

It is exciting to see how the mainframe is changing at the moment. The movements of the last five years are really turning the mainframe from a self-oriented platform into one of the most open and accessible technologies on the market.

The only thing left in my opinion is an affordable, public and easily accessible facility to the platform in order to boost the collaborative community around the platform.

The definition of a user catalog

  • Post category:Catalog
  • Reading time:1 mins read

Ok another short one: just a small JCL script with working example for the definition of a user catalog. This would accompany the example of the alias definition for a user catalog that can be found here.

//DEFCAT   EXEC PGM=IDCAMS                               
//SYSPRINT DD SYSOUT=*                                  
//SYSIN    DD *                                       
DEFINE UCAT (NAME(SYS1.USERCAT.PROD) VOLUME(DASD1B) - 
        CYL(6,1) ICFCAT  )          

Enjoy.

Pierre G.

How to copy the contents of a catalog: IDCAMS

  • Post category:Catalog
  • Reading time:1 mins read

A very short one, but regularly asked by my mentees: how to copy the contents of a catalog? Just use IDCAMS’ REPRO facility, like you would for a regular VSAM dataset

//COPYCAT   EXEC PGM=IDCAMS                           
//SYSPRINT DD SYSOUT=*                         
//SYSIN    DD *                                      
REPRO  INDATASET(CATALOG.MVSICFM.VD9ECAT) -         
        OUTDATASET(SYS1.MSTRCTLG)    

//Pierre G

Change the volume of the entry for a dataset in a catalog

  • Post category:JCLUtilities
  • Reading time:1 mins read

When you have defined a dataset in the wrong catalog – in this case a master catalog – and you want to correct this you can use this technique.The DELETE NOSCRATCH option assures only the catalog entry is deleted. If you would omit this, the entire dataset would be deleted, so be cautious.

//DEFCAT   EXEC PGM=IDCAMS          
//STEPCAT DD DISP=SHR,DSN=SYS1.MSTRCTLG           
//SYSPRINT DD SYSOUT=*                          
//SYSIN    DD *                  
DELETE (SYS1.DATASET) NVSAM NOSCRATCH CAT(SYS1.MSTRCTLG)     
DEFINE NVSAM (NAME(SYS1.DATASET) DEVT(3390) VOL(VOL123)) - 
            CAT(SYS1.MSTRCTLG)   

//Pierre G

Allocate a page dataset / page space for new system, in a second master catalog

  • Post category:JCL
  • Reading time:1 mins read

This job shows you how you can define a page dataset in another master catalog than the currently active master catalog. This technique is typically used when you are building a new system from a driver system. The master catalog referred to via the CATALOG statement below is the to-be master catalog of the new system you are building.

//DEFPAGE EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//PAGE2    DD UNIT=3390,VOL=SER=PSYS1B,DISP=OLD
//SYSIN    DD *
  DEFINE PAGESPACE( -
      FILE(PAGE2) -
      NAME(SYS1.MVST.PLPA2) -
      CYLINDERS(300) -
      VOLUME(PSYS1B) ) -
    CATALOG(SYS1.MSTRCAT0)
/* 

//Pierre G.

Define an ALIAS for your User Catalog

  • Post category:Catalog
  • Reading time:1 mins read

This JCL defines an ALIAS in the current master catalog. The alias points to the user catalog named in the RELATE clause.

Use a STEPCAT pointing to another master catalog when needed.

//DEFCAT   EXEC PGM=IDCAMS 
//* STEPCAT DD DISP=SHR etc when needed     
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *        
  DEF ALIAS (NAME(PROD) RELATE(SYS1.USERCAT.PROD)) 
/*
//

//Pierre G

ABEND Assembler program

  • Post category:Assembler
  • Reading time:1 mins read

I can not remember why I needed this assembler program – and why it is in assembler – but here it is: the program who’s sole function is to ABEND

ABENDIT  CSECT
         EQUATES
         SAVE (14,12),,ABENDIT/SPDEPT/&SYSDATE/&SYSTIME/
         USING ABENDIT,R11             SET UP BASE ADDRESSABILITY
         LR    R11,R15                 LOAD BASE REG WITH ENTRY POINT
         LA    R14,SAVE                GET ADDRESS OF REGISTER SAVE
         ST    R13,4(0,R14)            SAVE CALLER'S SAVE AREA ADDR
         ST    R14,8(0,R13)            SAVE MY SAVE AREA ADDRESS
         LR    R13,R14                 LOAD SAVE AREA ADDRESS 
*        Business Logic
         ABEND 4321
*        Epilogue 
RETURN   EQU    *
         L      R13,4(R13) 
         RETURN (14,12)                RETURN TO CALLER 
         LTORG 
SAVE     DS     18F 
         END ABENDIT 

// Bert Grobs

Ways get utc / gmt time zone (offset) versus local time in Rexx on z/OS

  • Post category:Rexx
  • Reading time:2 mins read

Regularly we need to compare local time to absolute time UTC (or GMT) programmatically.

This can be done in various ways.

The cleanest way is to use the static system symbols that z/OS defines. See also section “Static System Symbols” in the z/OS Initialization and Tuning Reference.You can easily use this in a Rexx program. For example: 

/* Rexx */  
/* Display UTC Time elements */
say  mvsvar('SYMDEF','HR')      
say  mvsvar('SYMDEF','MIN')     
say  mvsvar('SYMDEF','SEC')     
say  mvsvar('SYMDEF','HHMMSS')  
say  mvsvar('SYMDEF','LHHMMSS') 

It is also possible through Unix System Services date command to obtain UTC:

date -u
Wed Aug 21 15:20:42 GMT 2019 

A more complex way (old fashioned) to achieve the same is addressing the CVT extension block:

                   
cvt = c2d(storage(d2x(16),4))                                
cvtext2 = c2d(storage(d2x(cvt + 328 ),4))
offset  = ((c2d(storage(d2x(cvtext2+56+2),4))) * 16) /3600000000 
timezone = "+0"||offset||"00"                   

Thanks to Henk for the latter two solution alternatives.

— Niek de Greef

IBM Integration Bus / Message Broker operations with IBM Integration API aka CMP

  • Post category:IBM Integration Bus
  • Reading time:11 mins read

IBM provides the IBM Integration API, also known under the less accurate and descriptive name Configuration Manager Proxy (CPM), through which you can write your applications to control IBM Integration Bus components.

The Java code below provices a working example of this API. The code presented revolves around a BrokerManager class. In the code below only one method on this class has been implemented, but it can easily be extended with other methods. 

The code uses a z/OS specific way to connect to a broker. On z/OS we do not connect through the network but locally. This needs a slightly different approach using the getLocalInstance method on the Broker Proxy API.

The main() method in this sample implements the instantiation and invocation of the BrokerManager, implementing a command line solution, but the same code could be used in different contexts seamlessly.

The documentation of the API can be found in the IBM Integration Bus Knowledge Center.

Information for the App Connect enterprise version can be found through this link. I have not tried that version.

package com.your.company.package.broker;
import com.ibm.broker.config.proxy.AdministeredObject;
import com.ibm.broker.config.proxy.AdministeredObjectListener;
import com.ibm.broker.config.proxy.ApplicationProxy;
import com.ibm.broker.config.proxy.AttributeConstants;
import com.ibm.broker.config.proxy.BrokerConnectionParameters;
import com.ibm.broker.config.proxy.BrokerProxy;
import com.ibm.broker.config.proxy.CompletionCodeType;
import com.ibm.broker.config.proxy.ConfigManagerProxyException;
import com.ibm.broker.config.proxy.ConfigManagerProxyLoggedException;
import com.ibm.broker.config.proxy.ConfigManagerProxyPropertyNotInitializedException;
import com.ibm.broker.config.proxy.ExecutionGroupProxy;
import com.ibm.broker.config.proxy.LogEntry;
import com.ibm.broker.config.proxy.MQBrokerConnectionParameters;
import com.ibm.broker.config.proxy.MQPropertyFileBrokerConnectionParameters;
import com.ibm.broker.config.proxy.MessageFlowProxy;
import com.ibm.mq.MQException;
import java.util.Enumeration;


public class BrokerManager {


    private BrokerProxy bp;
    private String bn;
    private String qm;
    private String hn;
    private int port;


    BrokerManager(String broker, String host, String qmgr, int port) {
        this.bn = broker;
        this.qm = qmgr;
        this.hn = host;
        this.port = port;
        System.out.println("Creating Broker Manager " + this.bn);
    }


    public boolean connect() {
        BrokerConnectionParameters bcp;
        boolean brokerIsResponding = false;
        // bcp = new MQBrokerConnectionParameters(this.hn, this.port, this.qm);
        // Not for z/OS
        // bcp = new MQBrokerConnectionParameters("", this.port, this.qm); but we will use
        // getLocalInstance for z/OS below
        try {
            this.bp = BrokerProxy.getLocalInstance(this.bn);
            // alternative this.bp = BrokerProxy.getInstance(bcp);
            // Ensure the broker is actually talking to us.
            brokerIsResponding = bp.hasBeenPopulatedByBroker(true);
            if (brokerIsResponding) {
                System.out.println("Broker" + this.hn + " broker " + this.bn + "is responding");
            } else {
                System.err.println("Broker" + this.bn + "is not responding");
                this.bp.disconnect();
                this.bp = null;
            }


        } catch (ConfigManagerProxyLoggedException e) {
            System.err.println("Broker" + this.bn + " CONNECT failed" + e.toString());
        }
        return brokerIsResponding;
    }


    public boolean setStartMode(String exg, String sm) {
        // Set startmode for all flows in given execution group
        //
        // AttributeConstants.STARTMODE_MAINTAINED which means at deploy time or
        // restart the message flow will start based on its isRunEnabled value.
        // AttributeConstants.STARTMODE_MANUAL which means at deploy time or
        // restart the message flow will be stopped.
        // AttributeConstants.STARTMODE_AUTOMATIC
        this.bp.setSynchronous(15000);
        boolean result = false;
        try {
            Enumeration<ExecutionGroupProxy> listEG = this.bp.getExecutionGroups(null);
            while (listEG.hasMoreElements()) {
                ExecutionGroupProxy eg = listEG.nextElement();
                if (eg.getName().equalsIgnoreCase(exg)) {
                    System.out.println("Connected to execution group " + exg );
                    Enumeration<MessageFlowProxy> listMsgFlow = eg.getMessageFlows(null);
                    while (listMsgFlow.hasMoreElements()) {
                        MessageFlowProxy flow = listMsgFlow.nextElement();
                        if (sm.equalsIgnoreCase("maintained")) {
                            flow.setStartMode(AttributeConstants.STARTMODE_MAINTAINED);
                        } else if (sm.equalsIgnoreCase("manual")) {
                            flow.setStartMode(AttributeConstants.STARTMODE_MANUAL);
                        } else if (sm.equalsIgnoreCase("automatic")) {
                            flow.setStartMode(AttributeConstants.STARTMODE_AUTOMATIC);
                        }
                        System.out.println(flow.getName() + " startmode set to " + sm);
                    }
                }
            }
        } catch (Exception e) {
            System.err.println("Error " + e.toString() + " during getExecutionGroup");
        }
        return result;
    }


    public static void main(String[] args) {
        // Variables, initialised
        String action = "";
        String startmode = "";
        String brokername = "";
        String exgname = "";
        String qmgr = "";
        String hostname = "";
        int brokerport = 0;
        
        // Parse the command line arguments
        // We currently only need these, so we do not make parsing of arguments more complex than this
        if (args.length < 4) {
            System.out.println("Error in input. Please provide: Brokername, Executiongroupname, Action, parametervalue");
        } else {
            brokername = args[0];
            exgname = args[1];
            action = args[2];
            startmode = args[3];
            
            // Create brokermanager and connect
            BrokerManager b = new BrokerManager(brokername, hostname, qmgr, brokerport);
            b.connect();
            // Perform given action, currently only
            if (action.equalsIgnoreCase("setstartmode")) {
                b.setStartMode(exgname, startmode);
            } else {
                System.out.println("You gave action: " + action + " Specify valid action: setstartmode.");
            }
        }
    }
}

Hope this works for you. Let me know if you have comments.

// Niek de Greef