Handling character columns in generated SQL

This blog post is a continuation of my earlier post titled "Using DFSORT to Generate Bulk SQL Queries: A Step-By-Step Guide", where I had explained in detail how IBM’s DFSORT can be used to automatically generate thousands of DB2 SQL UPDATE statements as output based on an input file (typically an Excel sheet converted to CSV).

If you haven’t read it yet, grab a cup of coffee, then start reading it here.

I felt my previous post was missing an important detail. The post implicitly assumed that all substituted values in the SET and WHERE clauses were numeric. In real DB2 tables, however, you often need to work with character columns, which must be enclosed in single quotes in SQL. It then becomes tricky with DFSORT because DFSORT also uses single quotes to delimit character constants.

This was even pointed out by one of our blog readers, who DM’ed me on LinkedIn.

Fortunately, DFSORT provides a simple way to handle this.

To generate a single quote in the output, you must code two single quotes within a DFSORT constant. For example, what DB2 expects as'VALUE' must be written in DFSORT as ''VALUE''.

Does this ring a bell? Have you heard of escape sequences? I came across escape sequences when I was learning the C programming language years back. Well, the escape sequence is something not specific to one language.

Back to the main topic. Let’s extend the earlier example and add a condition on the FIRST_NAME column in the WHERE clause. The updated DFSORT step would look like this:

 //SORTSTEP EXEC PGM=SORT  
 //SYSOUT   DD SYSOUT=*  
 //SORTIN   DD DSN=INPUT.DATA,DISP=SHR  
 //SORTOUT  DD DSN=OUTPUT.SQL,  
 //         DISP=(NEW,CATLG,DELETE),  
 //         SPACE=(CYL,(1,1),RLSE),UNIT=SYSDA  
 //SYSIN    DD *  
  OPTION COPY  
  OUTFIL BUILD=(  
   C'--- UPDATE FOR ',7,6,X,15,7,80:X,/,  
   C'UPDATE EMPLOYEES ',80:X,/,  
   C'   SET SALARY = ',38,5,80:X,/,  
   C' WHERE EMP-ID = ',1,3,80:X,/,  
   C'   AND FIRST_NAME = ''',7,6,C'''',80:X,/,  
   C'   AND SALARY = ',31,5,80:X,/,  
   ';',80:X,/,  
   80:X)  
 /*  

Notice the line:

C'   AND FIRST_NAME = ''',7,6,C''''

Here is how it works:

• The opening ''' (i.e. '' inside a constant) produces a single quote.
• 7,6 inserts the FIRST_NAME value (e.g., John)
• The closing C'''' again produces a single quote.

As a result, the generated SQL output will correctly include the string literal.

 --- UPDATE FOR JOHN SMITH  
 UPDATE EMPLOYEES  
    SET SALARY = 5500  
  WHERE EMP-ID = 001  
    AND FIRST_NAME = 'John '  
    AND SALARY = 5000  
 ;  

That’s it. By incorporating this technique, you can extend the original solution to generate fully functional SQL statements for tables with mixed data types, making the approach much more practical for real-world batch updates.

Hope this helps. Thanks for reading!

IBM Z Xplore - Getting through JCL1 Challenge in Fundamentals level

Hello, 

In this blog post, let's take a look at JCL1 challenge in Fundamentals level of IBM Z Xplore. 

Before diving into it - For those who are new here, this is a part of series of posts that I'm doing to cover the challenges in IBM Z Xplore. Click 👉 here for the very first post. 

Intro

This challenge will let you get to know about the Job Control Language (very famous in the Mainframe world with the shorthand, JCL), though it isn't a programming language. Imagine JCL as something with which you'll get the work done in IBM z/OS. 

After you click on the JCL1 challenge's tile, you'll be presented with a page which walks you through a brilliant example (at the bottom) to understand JCL's better, so don't miss it. 

What's JCL?

The next page walks you through a sample JCL which (evidently) takes 2 inputs (FNAMES and LNAMES) and produce an output (COMBINED). 

Sample JCL

There is a program (PGM=CBL0001) which process the inputs and creates output. It's written in COBOL. More about COBOL - later! 

Once the JCL's are written, you must submit it (using SUBMIT command) so that it creates a job and get your work done in the background. This job is managed by something called as JES (Job Entry Subsystem). JES is responsible for receiving the jobs into the operating system,  scheduling them for processing by z/OS and controlling their output processing. 

Ready to take the Quiz?

Hit on the "Ready to take the Quiz?" button and you'll be shown with 3 questions on the next page. 

The questions are as follows:

1. What elements might you find in JCL?

2. When JCL gets submitted, it creates a _____ ? (The answer is same as what you (eventually) get after graduating from college? 😀)

3. In the screenshot example we showed earlier, what is the name of the program the JCL is seeking to run?

As usual, you will (should😉) get 3 brownie points if you answer the questions correctly. 

Let's get started with the actual challenge..

Grab a copy of JCL1 Challenge PDF. The only requirement before you begin this challenge is the completion of Files challenge as the knowledge that you grasped about Data Sets and Members are essential for this challenge. 

Steps 1 thru 3 - You'll have to locate a member named JCL1 in ZXP.PUBLIC.JCL PDS, submit it and find the job that you submitted, in the JOBS section of Zowe Explorer extension. 

💡Whenever you submit a job in VS Code, there is a message shown on the right side bottom of the screen, containing the Job ID. Clicking on the Job ID will (also) take you to the job's output. 

The job that you submitted simply allocate some datasets which are to be used in this, and other challenges. Notice the CC 0000 next to the job's name? Well, that's condition code (CC) of the job that you submitted and a zero means everything ran as expected. If it's some other number (greater than 4), then the job didn't run as expected and it's something which you should be looking at. 

In exams, you're fail 👎 if you get zero marks. In z/OS, you're good if you get zero as the return code out of a job. 

I loved this example 👇 that was quoted in the left side bottom of the second page in the challenge PDF.

Think of JCL as the order that a waiter writes up, and JES as the kitchen staff that looks at the order and decides how they’re going to handle it. The L in JCL stands for Language, but it really isn’t a programming language as much as it is a way for us to effectively describe tasks to the system.

Steps 6 thru 8 is all about using a JCL (JCL2 from ZXP.PUBLIC.JCL) to compile a code written in COBOL and then run some code. JCL is just acting as a medium to get the work done. work being the list of statements (aka code or program) written using COBOL. 

COBOL is a programming language used in many financial, healthcare, and government institutions. Its high degree of mathematical precision and straightforward coding methods make it a natural fit when programs need to be fast, accurate, and easy to understand.

            - Quoted from the Challenge PDF. 

In step 6, Copy JCL2 from ZXP.PUBLIC.JCL PDS to your own ZXXXXX.JCL PDS (Please replace ZXXXXX with your Z ID). 

JCL2 and the COBOL program explained

This JCL consists of 2 steps. Lines 3 thru 5 comprises of the first step, which compiles the code written in COBOL. Compiling is a process of converting the code written in human understandable form to Machine Code that is understood by z/OS. Compiling results in an output which is called as Load Module. 

In JCL, statements that define where data is coming from or going to are known as Data Definition Statements, or simply, DD Statements. Lines 4 and 5 are the datasets containing the source code written in human understandable form and the load module that will hold the Machine code.  

One more thing to note. During compilation, the program that you wrote will also be validated for any rule violations. Only error-free programs are compiled and turned into Machine code. 

Lines 9 thru 16 in the JCL comprises of second step, which executes the program that was compiled in the previous step. 

What the COBOL program does?

The COBOL program reads 2 input files consisting of First names and last names respectively; it writes onto an output file, the combined name (First name + last name). 

We should be letting the COBOL program know where to look for these input file(s) to read data from and the output file where to write the data to. We do this with the help of the Data Definition statements in the lines 11 thru 13, in the JCL.  Below, I've highlighted the lines in the JCL with a yellow rectangle box. 

Note the word after // in all the 3 lines. These are DD (Data Definition) names. The Data Definition statements (or DD statements) which define where data is coming from or going to, will have user defined DD names. We'll have references to these names in the COBOL program that we wrote so that we can establish a link between the data in these data sets and the program. 

Below, I've highlighted the statements in the COBOL program that refers to the DD names in the JCL 👇

For example, FIRST-NAME is assigned to FNAMES DD statement in the JCL and FIRST-NAME is what we reference in the COBOL code to read data present in the Data Set associated with FNAMES DD statement. 

Let's go back to the Challenge Instructions PDF. In Step 8, you have to submit JCL2 from ZXXXXX.JCL PDS. After submitting the JCL, you have to head to the output of the job. This time, you'll notice that the job didn't complete with Completion Code 0000. Instead, we got an ABEND (shorthand name for Abnormal End). 

Something is not right and your task is to fix it. 

Hint🧩: Carefully observe the DD statements in the JCL (lines 11 thru 13) and SELECT statements in the COBOL program (lines 11 thru 13). I've added the snapshots of these lines with yellow rectangle boxes above👆. The COBOL program resides in a PDS (ZXP.PUBLIC.SOURCE) for which you don't have edit access. You can only edit JCL2 residing in ZXXXXX.JCL PDS. Therefore, the DD names at the end of the SELECT statements in the COBOL program should match the DD names in the JCL.  

After fixing it, re-submit the JCL. This time, the job should've completed fine with Completion Code as 0000.

Hang on. You're not done yet!

Before marking this challenge as complete, you must do another task. 

Copy JCL3 from ZXP.PUBLIC.JCL to ZXXXXX.JCL. Take a look inside JCL3 for it contains 13 steps (Tip: Each step in this JCL starts with an EXEC statement). All these steps run a same program which is called as IEBGENER. IEBGENER is a utility program and one of its many uses is to copy data from one data set to another. 

Out of these 13 steps, only one output file is being created and it's ZXXXXX.JCL3OUT. In the first step, the output file is being created and in the other steps, data is appended (data added to the end of the data set) to the output data set. All this is possible with the help of DISP parameter. DISPosition parameters are used to describe how JCL should use or create a data set, and what to do with it after the job completes. Refer steps 12 and 13 in the challenge instruction PDF for more info.

Your job is to submit the JCL as it is (for the first time) and to check the output data set, ZXXXXX.JCL3OUT. You should not have any repeat stops in the output data set. If something is listed twice, you should go back to the JCL and edit it.

Here is a glimpse of the output data set, ZXXXXX.JCL3OUT after submitting JCL3 as it is 👇. 

If you go through the list of stops (lines 5 thru 16), you'll notice that couple of stops are repeated (lines 8 and 13). Now, we have to re-submit the JCL after ensuring that these stops aren't repeated. While you're on it, just keep in mind that what you're going to remove in the JCL can be just a line (the line number adds up to 6 😉) or an entire step. Don't leave an empty line on the JCL. When you are done with the edits in the JCL, locate and delete the output dataset ZXXXXX.JCL3OUT before re-submitting the JCL.

Here is the output dataset after fix 👇. 

The End

Submit your work by submitting CHKJCL in ZXP.PUBLIC.JCL. Go back to IBM Z Xplore learning platform -> Fundamentals level -> JCL1 challenge tile -> Scroll to the bottom and hit on Check JCL submission button. Hope you successfully completed the challenge. 

Next one is USS, so keep a tab on this blog for I'll soon add a post covering the next challenge.  Thanks for reading! 

How to concatenate all generations of a dataset?

 Hello 👋

In this post, let's see 👀 how we can concatenate all the existing generations of a dataset. In z/OS, a dataset which have generations (each generation is a successive update) is called as Generation Data Group (abbreviated to GDG).

This picture is for the thumbnail of this blog post. 

When you have a job whose task is to reference all existing generations of a data set, you would normally need to manually check the generation numbers and insert them into the JCL. 

One way around this is to just code the GDG base entry name and the system will automatically pick up all cataloged generations. You don't have to manually check for the generation numbers 💃.

 

JCL to concatenate the GDG generations.

Upon submitting the JCL,

JESYSMSG Listing after the completion of the job.

It is evident in the output produced from this job that the latest generation of this data set is accessed first (LIFO) and so on. 

How the order of concatenations can be modified?

LIFO order can be reversed using the GDGORDER parameter. 

Usage of GDGORDER Parameter in the JCL.

Upon submitting the JCL,

You can now see that the order of concatenation has changed.

It's worth noting that by default, the generations are concatenated in Last In First Out order. GDGRODER comes handy when you want to override the default. 

Hope this helps!

Updating Sequential files using COBOL

There is no doubt Mainframes running COBOL powers majority of world's business transactions. Some of the firms are Financial institutions, hospitals, government and logistics.    

The very first site that I worked for is (even now) a global leader on the market of business information. They collect, store and process a business's information to generate credit scores and business information reports. The scores assess the business and it helps, say, a bank to use the information in the report when deciding to offer a loan to that business. 

Master file:

    I was part of the application which generated the scores. We stored the scores for ~80 million businesses and we didn't maintain a database. Rather, we used a sequential Master file which was inclined to grow whenever a new business's score was generated. 

In addition to that, it was necessary that we updated the scores of the existing businesses in daily basis as a business is prone to changes (there might be a change in CEO; the business might go Out of Business or Bankrupt; the business might win a Suit; trade payment changes undergone by the business and so on). 

The Master file had a key field (a unique number assigned for each business) which uniquely identified each record. 

All the records were in sequence by the key field. 

The Master file's width (LRECL) was large enough to accomodate every information collected about the business. 

There were coded fields (e.g., codes used for Bankruptcy status, Out of business status etc.) to save space.

Transaction file:

    Daily changes of the business were stored in a file referred to as transaction file. The transaction file had all transactions to be posted to the Master file that have occurred since the previous update. The transaction file also had a key field (the same key as that of the Master file) and all the records in the transaction file were in sequence by the key field.

Updating a Master file:

    The process of making the Master file current is referred to as updating. The Master file is updated via sequential processing by reading in the Master file along with the transaction file and creating a new master file. At the end of the update process, there will be an old master and a new master; should something happen to the new master file, it can be recreated from the old. Refer to the following picture for better clarity.

Click on the image for a larger version.

The Old Master file (OLD-MASTER) contains master information that was complete and current till the previous updating cycle. The transaction file (TRANS-FILE) contains transactions or changes that occurred since the previous updating cycle. These transactions or changes must be incorporated into the master file to make it current and updated. As a result, a New Master file (NEW-MASTER) will include all OLD-MASTER data in addition to the changes stored on the TRANS-FILE that have occurred since the last update. 

As all the records are in sequence by the key field, we compare the key field in the Old Master file to the same key field in the transaction file to determine if the master record is to be updated; this comparison requires both the files to be in sequence by the key field.  

Let's take a look at the format of the two input files:

OLD-MASTER 📂 

(in sequence by M-BUSINESS-NO)

COLS        FIELD

1-9             M-BUSINESS-NO

10-39      M-BUSINESS-NAME

40-42      M-SCORE

43-100       M-FILLER

TRANS-FILE 📂

(in sequence by T-BUSINESS-NO)

COLS        FIELD

1-9             T-BUSINESS-NO

10-39      T-BUSINESS-NAME

40-42      T-SCORE

43-100       T-FILLER

How input transaction and Master records are processed?

Once all the files are opened, a record is read from both the Old Master file and the transaction file. As the files are already in sequence by their respective key fields, a comparison of M-BUSINESS-NO and T-BUSINESS-NO should be made to determine the next set of actions. Three possible conditions are met when comparing M-BUSINESS-NO and T-BUSINESS-NO fields: 

IF T-BUSINESS-NO = M-BUSINESS-NO

If the business numbers are equal, this means that a transaction record exists with the same business number as that on the Master file. When this condition is met, the transaction data is posted to the master record. This means, the record which goes into the New Master file will contain the updated score and other fields from the transaction file.

Once the record is written, the next record is read from both the Old Master file and Transaction file. 

IF T-BUSINESS-NO > M-BUSINESS-NO 

If T-BUSINESS-NO IS > M-BUSINESS-NO, this means that M-BUSINESS-NO < T-BUSINESS-NO. In this case, there is a record in the Master file with a business number less than the business number on the transaction file. Since both the files are in sequence by the business number, this condition means that a master record exists for which there is no corresponding transaction record. This means, the record read from the master file hasn't gone through any changes during the current update cycle and should be written as it is onto the New Master file.

Once write is made to the New Master file, next record is read only from the Old Master File. We do not read another record from the Transaction file as we haven't processed the last transaction record that caused T-BUSINESS-NO to compare greater than M-BUSINESS-NO of the OLD-MASTER.

IF T-BUSINESS-NO < M-BUSINESS-NO

Since both the files are in sequence by business number, this condition would mean that a transaction record exists for which there is no corresponding record in the Master file. This could mean that the scores are generated for a new business (voila! 😃). In this instance, a new master record is created entirely from the transaction file and is written onto the New Master file. 

Once written, the next record is read only from the Transaction file. We do not read another record from the Old Master file since we haven't processed the Master record that compared greater than T-BUSINESS-NO. 

The following example illustrates the update procedure along with the corresponding action to be taken:

A sample update program is shown below: (Language - COBOL)

  ID DIVISION.                     
  PROGRAM-ID. CBL4.                  
  AUTHOR. SRINIVASAN.                 
 *                           
  ENVIRONMENT DIVISION.                
  INPUT-OUTPUT SECTION.                
  FILE-CONTROL.                    
    SELECT OLD-MASTER ASSIGN TO OLDMAST.       
    SELECT NEW-MASTER ASSIGN TO NEWMAST.       
    SELECT TRANS-FILE ASSIGN TO TRANS.        
 *                           
  DATA DIVISION.                    
  FILE SECTION.                    
  FD OLD-MASTER                    
    RECORDING MODE IS F               
    RECORD CONTAINS 100.               
  01 OLD-MASTER-REC.                  
   05 M-BUSINESS-NO        PIC X(9).     
   05 M-BUSINESS-NAME      PIC X(30).    
   05 M-SCORE              PIC 9(3).     
   05 M-FILLER             PIC X(58).    
  FD TRANS-FILE                    
    RECORDING MODE IS F               
    RECORD CONTAINS 100.               
  01 TRANS-REC.                    
   05 T-BUSINESS-NO        PIC X(9).     
   05 T-BUSINESS-NAME      PIC X(30).    
   05 T-SCORE              PIC 9(3).     
   05 T-FILLER             PIC X(58).    
  FD NEW-MASTER                    
    RECORDING MODE IS F               
    RECORD CONTAINS 100.               
  01 NEW-MASTER-REC.                  
   05 N-BUSINESS-NO        PIC X(9).     
   05 N-BUSINESS-NAME      PIC X(30).  
   05 N-SCORE              PIC 9(3).   
   05 N-FILLER             PIC X(58).  
 *                         
  PROCEDURE DIVISION.               
  100-MAIN-MODULE.                 
      PERFORM 800-INITIALIZATION-RTN        
      PERFORM 600-READ-MASTER           
      PERFORM 700-READ-TRANS            
      PERFORM 200-COMPARE-RTN           
        UNTIL M-BUSINESS-NO = HIGH-VALUES    
          AND T-BUSINESS-NO = HIGH-VALUES    
      PERFORM 900-CLOSE-FILES-RTN         
      STOP RUN.                  
 *                         
  200-COMPARE-RTN.                 
      EVALUATE TRUE                
      WHEN T-BUSINESS-NO = M-BUSINESS-NO      
           PERFORM 300-REGULAR-UPDATE       
      WHEN T-BUSINESS-NO < M-BUSINESS-NO      
           PERFORM 400-NEW-ACCOUNT         
      WHEN OTHER                  
           PERFORM 500-NO-UPDATE          
      END-EVALUATE.                
 *                         
  300-REGULAR-UPDATE.               
      MOVE OLD-MASTER-REC TO NEW-MASTER-REC    
      WRITE NEW-MASTER-REC             
      PERFORM 600-READ-MASTER           
      PERFORM 700-READ-TRANS.           
 *                         
  400-NEW-ACCOUNT.                 
      MOVE SPACES TO NEW-MASTER-REC        
      MOVE T-BUSINESS-NO TO N-BUSINESS-NO     
      MOVE T-BUSINESS-NAME TO N-BUSINESS-NAME   
      MOVE T-SCORE TO N-SCORE            
      MOVE T-FILLER TO N-FILLER           
      WRITE NEW-MASTER-REC              
      PERFORM 700-READ-TRANS.            
 *                          
  500-NO-UPDATE.                   
      WRITE NEW-MASTER-REC FROM OLD-MASTER-REC    
      PERFORM 600-READ-MASTER.            
 *                          
  600-READ-MASTER.                  
      READ OLD-MASTER                
      AT END MOVE HIGH-VALUES TO M-BUSINESS-NO    
      END-READ.                   
 *                          
  700-READ-TRANS.                  
      READ TRANS-FILE                
      AT END MOVE HIGH-VALUES TO T-BUSINESS-NO    
      END-READ.                   
 *                          
  800-INITIALIZATION-RTN.              
      OPEN INPUT OLD-MASTER             
                 TRANS-FILE             
          OUTPUT NEW-MASTER.             
 *                          
  900-CLOSE-FILES-RTN.                
      CLOSE OLD-MASTER                
            TRANS-FILE                
            NEW-MASTER.               
 *                          

Two  files (Old Master file and Transaction file) are passed as input to the COBOL program. The program creates the New Master file as output.  Contents of the files are shown below:

Old Master file:

Contents of Old Master file.

Transaction file:

Contents of Transaction file

JCL used to compile and run the load module:

First step of the JCL compiles the COBOL program. If the compilation is successful, the second step will run to execute the load. 

After submitting the JCL, the following output file is created. 

New Master file:

Contents of New Master file.

Note the new record with business number as 000000004 added to the New Master file. Also, the scores of the existing businesses are updated. 

Use of HIGH-VALUES for End of file conditions:

With 2 input files, it's very unlikely that both the files will reach AT END conditions at the same time. There are high chances that the transaction file will run out of records before the Old Master file. In such cases, the remaining records from the Old Master file must be written to the New Master file. 

The COBOL reserved keyword, HIGH-VALUES is moved to the business number fields when the Old Master file/Transaction file has reached its end. 

HIGH-VALUES refer to the largest value in the system's collating sequence. This is a character consisting of "all bits on" in a single storage position. All bits on in EBCDIC represents a nonstandard, nonprintable character used to specify the highest value in the system's collating sequence. 

When the Transaction file reaches the end, HIGH-VALUES are moved to T-BUSINESS-NO. This ensures that the subsequent attempt to compare the T-BUSINESS-NO and M-BUSINESS-NO will always result in a "greater than" condition i.e., there is a record in the Master file with a business number less than the business number on the transaction file. This means the record read from the master file hasn't gone through any changes during the current update cycle and should be written as it is onto the New Master file.

HIGH-VALUES is a figurative constant that may be used only with fields that are defined as alphanumeric. If numeric fields are used, then moving all 9s (999999999) to the key field will always compare higher than any other number. Beware; if a business number of 999999999 is a possible entry, then moving all 9s during end-of-file condition could produce error. 

We've hit the end-of-file condition for this blog post 😉

    In this post, we learnt about the procedure used for updating sequential files in COBOL. This procedure is also referred to as 'file-matching logic'. Hope it was useful. 

    In the next post, I'll try to implement the same stuff but in Python. Thanks for reading! Should you have any queries/suggestions, please post it in the Comments section below 👍.

References used for this post:

Structured COBOL Programming - 8th Edition - Stern/Stern.

Using process statements in SuperCE utility

One of the item that I always strike off ✅ from my checklist whenever I'm assigned with a task of modifying an existing code is Source Code Comparison. It allows me to highlight the difference between different versions of the code. It also acts as a proof for the reviewer that only the intended parts of the code were modified. 

Although, CA Endevor lets us use the Changes (C) option to look at the actual lines we've changed, I rely upon SuperCE utility (option 3.13) to compare the modified code and the existing version of the code in Production environment. 

Welcome to my blog! 😀 In this blog post, we will look at the SuperCE (option 3.13) ISPF option - which is used to compare the content of two datasets - and the usage of Process statements which is similar to the usage of control statements in IBM's DFSORT utility. 

This one is for the Thumbnail 😁

Your time is precious. So, please use the following links to navigate to different sections of this post. 

• Intro
• How to access SuperCE utility and use it?
• How to use process statements to perform diverse data comparisons?
• Running SuperCE in batch mode
• Conclusion

Intro

SuperC (I guess the suffix 'C' after Super stands for Compare) is the standard option to compare two datasets of unlimited size and record length. SuperCE is the extended version of the standard SuperC Utility and it offers more flexibility like,

• Comparing the datasets in line, word or byte level, 
• Supplying process statements for specific compare requirements 
• Various listing types and so on. 

How to access SuperCE Utility and use it?

To access the SuperCE Utility from ISPF Primary Option Menu, type 3 (Utilities) and press Enter.

Click on the image for a larger version.

ISPF Primary Option Menu.

From the Utility Selection Panel, type 13 (SuperCE)  and press Enter. 

Selecting SuperCE from Utility Selection Panel.

Voila! 👏

SuperCE Utility Panel.

Alternatively, you can type =3.13 from ISPF Primary Option Menu (or command line for that matter) and hit Enter to directly get into SuperCE Utility panel.

 

How to use SuperCE Utility?

Now that we're inside the SuperCE Utility panel, let's use it. 

The true method of knowledge is experiment. 

 - William Blake

To use SuperCE utility, we should have two datasets. It can be a sequential dataset, PDS or a member inside a PDS. ❗ SuperC and SuperCE doesn't support tape datasets. 

I've got 2 PDS members with a simple COBOL program in each of them. For better understanding, I've named these members as NEW and OLD because the contents in the NEW member is an updated version of contents in OLD member.

The NEW member. This COBOL program accepts a name from the user and displays the name with a greet. 

The OLD member. As you probably know, this COBOL program simply displays a very famous message to the user. 

The next step is to input these datasets in the SuperCE Utility panel and do the comparison. The New DS Name field should be provided with the updated version of the dataset that you want to compare and the Old DS Name field should be provided with the previous version of the dataset. 

Using the SuperCE Utility panel.

Whenever you access the SuperCE Utility panel, it provides default setting for the Compare Type, Listing Type, Listing DSN, and Browse option. 

SuperCE Utility works the best for you with the following settings,

• Compare Type - Line (Compares the dataset for line differences)
• Listing Type - Delta (SuperCE provides a listing after the comparison. This listing shows some awesome stats. Delta option lists the differences between the source data sets, followed by the general summary)
• Listing DSN - This is where the listing output will be stored. SuperCE allocates a default DSN in case if you leave this field blank. If you want to store the results of comparison (I do, as I used to pass on this dataset to my code reviewer), you may provide your own DSN.  
• Display Output - Yes (This option tells ISPF that you want the output listing to be displayed. If you choose the option No, SuperCE will not show the listing but it shows the result of the comparison (Differences found or No differences found) at the top right corner of the panel). 
• Output Mode - View or Browse 
• Execution Mode - Foreground is the default. 

For more details about the SuperCE Panel Fields, click 👉 here.  

Let's hit Enter to allow SuperCE perform the comparison. The listing output after the comparison is shown below.

 

Listing output for Line Compare. 

In the Listing Output Section (Line #4 thru 21), the source lines are shown. 

Left side of each line is either marked with I (Insert) or D (Delete). 

The first source line at line #9, 000200 PROGRAM-ID. NEW.  , is marked with I (Insert) i.e., the listing tells that this line was inserted in the New DSN and wasn't found in the Old DSN. 

The next source line at line #10 is marked with D (Delete) i.e., the listing tells that this line is present in Old DSN but not in the New DSN. So, it must have been deleted in the updated version of the code. 

The Line Compare Summary and Statistics section at the bottom shows the overall summary of the comparison. 

How to use process statements to perform diverse data comparisons?

As you would've noticed in the listing output, the first 6 bytes (Column Numbers) of the COBOL code was also included for the comparison by SuperCE. 

Suppose you want to compare data residing in the columns 7 thru 72 in both the datasets, you should supply process statements for this requirement. 

The process statements panel can be accessed by typing E in the command line of SuperCE Utility panel, or by using Options action bar choice and choosing Option 1 - Edit Statements.

Accessing Process Statements panel.

In the following picture, some examples of the statements that can be used are shown in the bottom half of the screen. The actual statements required for your comparison should be typed in the EDIT window shown in the first half of the screen. 

Process Statements panel.

CMPCOLM process statement should be used to compare using a column range.

Inputting Process Statements. 

We can exit the screen now by pressing F3. A message, 'Statements DS saved' is displayed at the top right corner of the SuperCE Utility panel. 

Statements DS Saved.

The compare statements will be stored in the dataset provided in Statements DSN field in SuperCE Utility panel. This field can also be left blank allowing the system to create one dataset for you to store the process statements. 

On hitting Enter, the compare request will be invoked with the process options.

 

Listing Output

The Line Compare Summary shows that there are 4 line matches and 6 differences. At the bottom of the screen, the criteria used for this compare task is specified. 

There are many flavours of process statements that can be invoked depending on what you need to compare. Some of them are listed below. 

Example 1:

You can notice that the end of the process statement, CMPCOLM, contains a suffix of N and O, indicating that it is referencing the New DSN and Old DSN respectively. What follows the statement is the column range within the referenced dataset. 

With these statements, we tell SuperCE that we want to compare the data residing in columns 5 to 30 in the New DSN with data in columns 1 to 25 in the Old DSN. 

Example 2:

Suppose you want to ignore the comment lines in your COBOL code from being compared. 

DPLINE (Do not process lines) process statement do not process the lines that can be recognized by a unique character string, for comparison. 

DPLINE '*',7 scans for an asterisk ('*') in column 7 and ignores it from being compared.

Example 3:

Suppose if you want to compare only specific rows in each datasets.

The NFOCUS and OFOCUS process statements can be used to specify the rows to be used for the comparison. In this case, rows 1 thru 10 will be used from the New DSN while rows 11 thru 21 will be used from the Old DSN. 

More about Process Statements can be found 👉 here. 

Running SuperCE in batch mode

Sit back and relax. You can create a JCL from SuperCE Utility panel (with fewer hits on that Enter button) to run the comparison in batch mode. ISRSUPC is the program which is used for comparison.

After providing the datasets in the New and Old DSN, select the execution mode as Batch and press Enter. In the Submit Batch jobs panel, Job statement info is provided at the bottom of the screen. I've chose to Edit JCL before submit. 

SuperC Utility - Submit Batch jobs panel.

Upon hitting Enter, the JCL is shown to user. 

If you are adding Process Statements, a SYSIN DD statement will be added to the JCL. 

Conclusion

Hope you witnessed the uses of SuperCE utility. If SuperCE stands for Super Compare Extended, then adjective Super is well suited and appropriate. Should you have any questions/suggestions please leave it in the comments section below. Thx 👍

References: 

• z/OS ISPF User's Guide Vol II
• TSO/ISPF Curriculum z/OS v2.3 - Interskill Learning

Everything about IEFBR14 utility

It takes one full blog post to list out (almost) everything that can be done using a utility which is widely known as a program that does nothing. Ironic! 😀

Yup! I'm talking about IBM's IEFBR14 utility. 

Table of Contents 📚

Use the links given below to navigate to various sections of this post.

• Introduction
• Using IEFBR14 utility to create and delete datasets
• What happens when you provide a GDG base in IEFBR14 utility with DISP=(MOD,DELETE,DELETE)?
• What happens when you provide a PDS member in IEFBR14 utility with DISP=(MOD,DELETE,DELETE)?
• Uncataloging a dataset using IEFBR14 utility
• Running an IEFBR14 JCL from Zowe CLI
• Conclusion

Introduction 

When IEFBR14 is invoked, it branches to the address in Register 14, which returns back control to the operating system. The Assembler instruction to do this is BR 14. That's how this utility got the name as IEFBR14.

When IEFBR14 is called, it immediately returns back the control to the operating system with a completion code of 0. By returning the control to z/OS, IEFBR14 allows the system to process the disposition parameter on any DD statements that are specified along with the EXEC statement. We're simply exploiting this functionality of IEFBR14 for the creation and deletion of datasets. 

IEFBR14 was created because while DD statements can create or delete files easily, they cannot do so without a program to be run due to a certain peculiarity of the Job Management system, which always requires that the Initiator actually execute a program, even if that program is effectively a null statement. The program used in the JCL does not actually need to use the files to cause their creation or deletion — the DD DISP=... specification does all the work. Thus a very simple do-nothing program was needed to fill that role.

- Quoted text from Wikipedia.

Using IEFBR14 utility to create and delete datasets 

IEFBR14 is typically used for creating and deleting datasets. A sample JCL is shown below. 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.PS.A,                       
 000005 //     DISP=(MOD,DELETE,DELETE),                    
 000006 //     SPACE=(CYL,(1,0),RLSE)                      
 000007 //NEW01  DD DSN=Z01071.PS.B,                       
 000008 //     DISP=(NEW,CATLG,DELETE),                     
 000009 //     SPACE=(CYL,(1,0),RLSE),                     
 000010 //     DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)                                         
 ****** **************************** Bottom of Data ****************************  

IEFBR14 job step usually consists of an EXEC statement and DD statement for each dataset that we want to process.

As datasets are being referenced in each DD statement, a DISP parameter SHOULD be accompanied. 

Creation of datasets can also be done in foreground mode - 3.2 Data Set Utility panel - from ISPF. IEFBR14 utility is used when we want to create or delete the datasets as part of batch run.  

Creating a dataset using IEFBR14 utility

The JCL to create a new dataset using IEFBR14 utility is as follows: 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //NEW01  DD DSN=Z01071.NEW.DATASET,                    
 000005 //     DISP=(NEW,CATLG,DELETE),                     
 000006 //     SPACE=(CYL,(1,0),RLSE),                     
 000007 //     DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)                                          
 ****** **************************** Bottom of Data ****************************  

The DD statement named as NEW01 creates a new, empty dataset called Z01071.NEW.DATASET. All the information necessary to create the dataset has been provided in the JCL. 

DISP=(NEW,CATLG,DELETE) - creates a new dataset and catalogs it under normal termination. The dataset will be deleted if the job is terminated abnormally.

BLKSIZE of 800 is provided because the dataset created when providing BLKSIZE=0 may not be opened for view/edit as the system issues 'Invalid block size' message.

Click on the image for a larger version.

System issues 'Invalid block size' message when the dataset is created with BLKSIZE=0.

What happens when you try to create a dataset using IEFBR14 utility but without providing a DCB parameter in the DD statement 🤔?

The dataset gets created but it's not usable. Let's look at the dataset information.

 

Data Set Information shows the record format as ?, Record length and Block Size as 0.

Deleting a dataset using IEFBR14 utility

The JCL to delete an existing dataset before its creation is as follows: 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.PS.A,                       
 000005 //     DISP=(MOD,DELETE,DELETE),                    
 000006 //     SPACE=(CYL,(1,0),RLSE)                      
 ****** **************************** Bottom of Data ****************************  

The DD statement named as DEL01 deletes an existing dataset. If the dataset doesn't exist, MOD disposition creates the dataset and deletes it. Hence, SPACE parameter is provided. 

💣The job will fail with JCL error if the DD statement has got a DISP=(MOD,DELETE,DELETE) without SPACE= parameter and the dataset doesn't exist. 

SPACE= parameter may not be necessary if you're trying to delete an already existing dataset with DISP=(MOD,DELETE,DELETE).

DISP=(OLD,DELETE,DELETE) without a SPACE= parameter can also be coded if you're pretty sure about the existence of a dataset before running the utility job to delete and re-create the dataset.

What happens when you provide a GDG base in IEFBR14 utility with DISP=(MOD,DELETE,DELETE) 🤔?

Let's try this out!

I've created a GDG base named Z01071.TEST.GDG with 3 generations.  

The JCL is as follows:

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.TEST.GDG,                     
 000005 //     DISP=(MOD,DELETE,DELETE)                     
 ****** **************************** Bottom of Data ****************************  

The JESYSMSG listing after the completion of the job is shown below.

Just the generations that are part of the GDG base gets deleted; not the GDG base itself. 

💡 If you want to delete the GDG base in batch run, IDCAMS utility with DELETE command can be used. 

What happens when you provide a PDS member in IEFBR14 utility with DISP=(MOD,DELETE,DELETE) 🤔?

Let's experiment!

I've created a PDS named Z01071.TEST.PDS with 3 members in it. 

Let's try to delete the first member (MEMBER1) using the following JCL. 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.TEST.PDS(MEMBER1),                 
 000005 //     DISP=(MOD,DELETE,DELETE)                     
 ****** **************************** Bottom of Data ****************************  

The results are pretty surprising. Thank god, I didn't store anything important in the test PDS 😀.

The entire PDS is deleted. 

Uncataloging a dataset using IEFBR14 utility

There is a difference between uncataloging a dataset and deleting a dataset. 

When a dataset is uncataloged, it's removed from the catalog so that if you search for the dataset from the Data Set List Utility (=3.4) just by providing the dataset's name, you will not be able to find the dataset. You should also provide the Volume Serial number the dataset is residing upon.

 

Whereas, if a dataset is deleted, it's removed from VTOC (Volume Table of Contents) and the dataset may not be retrieved.   

Remember that both SMS and non-SMS data sets can be created and deleted using IEFBR14 utility. Only non-SMS data sets can be cataloged and uncataloged.

Let's try to uncatalog a dataset using IEFBR14 and the JCL is as follows: 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,                    
 000005 //     DISP=(OLD,UNCATLG)                        
 ****** **************************** Bottom of Data ****************************  

The JESYSMSG listing after the completion of the job is shown below.

Z01071.INPUT.PS.A dataset is uncataloged. 

Now that the dataset is uncataloged, let's try to search for the dataset just by providing the dataset's name. 

When we try to search for the dataset just by providing its name, No data set names found.

When we provide the Data set name as well as Volume Serial no., the dataset is listed. 

To catalog the dataset using IEFBR14 utility, the following JCL can be used. 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,                    
 000005 //     DISP=(OLD,CATLG),                        
 000006 //     UNIT=3390,                            
 000007 //     VOL=SER=VPWRKB                          
 ****** **************************** Bottom of Data ****************************  

Note: The UNIT and VOL=SER= parameters must be provided to catalog a dataset.

Running an IEFBR14 JCL using Zowe CLI

Let's put everything (creating, deleting, cataloging and uncataloging) together in a JCL, store it as a local file (.txt file) in Desktop and try to invoke the local file using Zowe CLI (Command Line Interface).

Prerequisites:

• Access to Zowe and z/OS MF (I've used the access obtained as part of Master the Mainframe 2020). 
• Zowe CLI must be installed on the system (For additional Zowe CLI documentation, visit https://docs.zowe.org)

Let's create a text file with the following contents. 

 //Z01071A JOB 1,NOTIFY=&SYSUID        
 //*                      
 //STEP1  EXEC PGM=IEFBR14          
 //DEL01  DD DSN=Z01071.PS.A,         
 //     DISP=(MOD,DELETE,DELETE)      
 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,      
 //     DISP=(OLD,UNCATLG)         
 //DEL02  DD DSN=Z01071.TEST.GDG,       
 //     DISP=(MOD,DELETE,DELETE)      
 //DEL03  DD DSN=Z01071.TEST.PDS(MEMBER1),  
 //     DISP=(MOD,DELETE,DELETE)   

The file is saved on my Desktop and it's named as IEFBR14.txt.

Zowe CLI is installed on your own computer, not on the mainframe. You'll use Zowe CLI to interface with Zowe and z/OSMF which is running on the mainframe. 

Steps to submit a JCL stored in a .txt file using Zowe CLI  are as follows: 

1. Open Command Prompt and type zowe. You'll get back a description, a listing of command groups, and options. 

Using the Zowe CLI. 

2. We have to use zos-jobs group to submit a JCL. Type zowe zos-jobs --help-examples  to view some examples. 

3. To submit a JCL from a local file, we should use the command, 

zowe zos-jobs submit local-file "IEFBR14.txt".

Upon the submission of the command, a status bar is shown submitting the local file to z/OS. 

Zowe CLI shows the JobID and the Jobname of the submitted job.

Let's use the JobID to locate the job from Zowe Explorer plug-in in VS Code. 

The JOBS Section on the left side bottom of the picture shows that the job with JobID as JOB04256 completed with return code 0. The JESYSMSG is opened on the right side using the Z Open Editor plug-in.  

Conclusion

Throughout this post, you have witnessed the uses of IEFBR14 utility. Most of the JCL's given in this post were commonly used on the sites that I've worked so far. If I had missed anything, please let me know through the Comments section below. Thx 👍