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!

Modern Mainframe tools I use daily. Plus, a blog milestone!

A few days ago, I stumbled upon the Code4z Extension Pack for Visual Studio Code at my workplace. This bundle brings several modern tools to Mainframe developers right within VS Code. Here's what it includes:

✅ COBOL Control Flow
✅ COBOL Language Support
✅ Explorer for Endevor
✅ Zowe Explorer
⬜ Abend Analyzer for Mainframe
⬜ Data Editor for Mainframe
⬜ Debugger for Mainframe
⬜ HLASM Language Support

(I’ve highlighted the ones I’ve personally tried so far.)

Disclaimer:
The tools and setup showcased in this blog post were explored using my personal computer and a Mainframe ID obtained through the IBM Z Xplore learning platform. The content reflects my personal experimentation and learning and does not involve or represent any proprietary systems, data, or configurations from my workplace.

🛠️ Getting Started with COBOL in VS Code

COBOL Control Flow and the COBOL Language Support extensions were immediately put into use. I had already created a custom REXX tool that extracts the COBOL source code from Endevor Element Listing output (one advantage of this source code is that all the INCLUDEs on the COBOL program are expanded) and sends it via email as a .txt file. I'd then open this .txt file in VS Code and manually set the language mode of the file to COBOL.

You can easily find out that the file is recognized as COBOL source code with the help of syntax coloring.

📷 Click on the image to view it in full size.

A file with COBOL source code opened on VS Code. The language mode highlighted at the bottom right corner indicates COBOL. 

Right-clicking anywhere on the file presents you with a lot of useful options, my favorites being

• Go to Definition
• Find All References
• Generate COBOL Control Flow
• and, lately, Copilot.

COBOL Language Support features are shown upon right-clicking on the file. 

💡 Real Productivity Boosters

When analyzing a COBOL program using CA Endevor’s listing output mode, I often find myself jumping up and down the listing—switching between paragraphs to trace the program's flow. In the process, it’s easy to lose track of the original paragraph I started from, especially when the logic branches out deeply. I’ve often resorted to using a notebook just to jot down the key paragraph names to avoid getting lost.

This is where modern tools shine. With features like Peek Definition and Peek References, you can stay on the current paragraph and quickly explore where a variable, copybook, or another paragraph is defined or referenced—without losing context.

Now, I use Visual Studio Code instead of 3270 Terminal for any kind of COBOL program analysis, whether I’m debugging abends, understanding functionality, or implementing enhancements.

🔄 Visualizing Control Flow

Another useful feature is Generate COBOL Control Flow, which allows you to graphically visualize the COBOL program. 

🎥 Watch: COBOL Control Flow in Action

This 👇 video shows how the VS Code extension visualizes the control flow of a COBOL program, highlighting the entry point and relationships between paragraphs.

For me, the COBOL control flow helps answer questions such as

• Where does the program begin execution? The flowchart clearly shows the entry point and how the control moves across different paragraphs or sections.
• Are there any unreachable or unused paragraphs? If a paragraph exists but has no inbound flow, it may indicate dead or obsolete code.
• How complex is the program?
• Which paragraph calls which?  Instead of manually tracking PERFORM statements, you can visually trace the relationships between paragraphs.

Zowe Explorer & Endevor Integration

The next set of extensions that I configured were the Zowe Explorer and Explorer for Endevor. Though I've been using Zowe since 2020, this was the first time I tried my hand at it in a work setting. 

With the Explorer for Endevor extension, I was able to set up an Endevor synchronized workspace and retrieve elements on VS Code. This proved that the REXX tool to extract COBOL source code from listing output is futile. After all, why reinvent the wheel?

🤖 Using GitHub Copilot on the Mainframe

Thanks to GitHub Copilot access at work, my productivity has further improved. I use it along with Zowe Explorer for:

• Writing/Debugging DFSORT

• Resolving SQLCODE errors

• Enhancing JCL scripts

• and much more…

The use cases are endless. Here are some.

🧠 The Problem:

You wanted to create a DFSORT step that performs a transformation—like converting lowercase to uppercase—but weren’t sure about the syntax.

🛠️ The Workflow:

1. You started writing your JCL in VS Code.
2. Invoked GitHub Copilot's In-line Chat with a natural-language prompt:
• “Finish the OUTREC to convert lowercase letters to uppercase.”
3. Copilot suggested:
• OUTREC FIELDS=(1,80,TRAN=LTOU)
4. You tested the job and confirmed the expected result. This step is essential as the code generated by Copilot may not always be correct.

📽️ Demo:

👇 Here's a short demo of me using GitHub Copilot to complete a DFSORT JCL step:

🧩 Final Thoughts

Modern Mainframe tooling, especially with extensions like Code4z, Zowe Explorer, and Explorer for Endevor, has revolutionized how I approach development and debugging. VS Code has now become my go-to tool nowadays. If you're still sticking to just the green screen, I highly recommend giving these tools a try. You’ll be surprised how much more productive and enjoyable Mainframe development can become!

I'm planning a follow-up blog post to dive deeper into how GitHub Copilot can be integrated with Zowe - bringing native-like AI assistance to mainframe development tasks. Stay tuned!

Over to you! Have you explored any modern tools for mainframe development? I'd love to hear about your experiences. Drop a comment below, and let's keep up the conversation.

🏅 Featured Among the Top 30 Mainframe Blogs!

Before I wrap up this post, I’m thrilled to share a bit of good news. My blog has been ranked among the Top 30 Mainframe Blogs by Feedspot! 🎉

This recognition motivates me to continue sharing hands-on experiences from the Mainframe world. A big thank you to everyone who reads, shares, and engages with my content.

Using DFSORT to Generate Bulk SQL Queries: A Step-by-Step Guide

One of the frequent tasks that I have dealt with is bulk updates to the DB2 table. I'll be provided with an excel spreadsheet containing thousands of rows, each specifying the current and target values for the columns of the table. Manually writing an UPDATE SQL for each row of the excel sheet is not only tedious but highly inefficient. Even if it takes just 30 seconds to write the UPDATE SQL for a row, you'll end up spending 8.3 hours (roughly 1 business day) to complete the task.

In this blog post, I'll show you how to automate the generate of SQL queries for bulk updates. There are many ways to automate it, but I'll be using IBM DFSORT.  

What is DFSORT?

DFSORT is a high-performance sort, merge, copy, and data manipulation utility used on IBM mainframes. It's an incredibly versatile tool that can handle a variety of data processing tasks, including the generation of SQL queries from a dataset.

The Scenario

Imagine you receive an Excel spreadsheet with thousands of rows, each containing the current and target values for specific columns in your DB2 table. Your task is to update the table with these target values. The sheet consists of details about Employees and the task is to update the current salary with the target salary. 

Preparing the Dataset

First, convert the Excel spreadsheet into a format suitable for processing in Mainframe, such as a comma-separated values (CSV) file. Once you have the CSV file, you need to upload it to the mainframe and create a dataset (INPUT.DATA) that DFSORT can process. At the site I work for, I use WS FTP Pro software to download/upload files from/to Mainframe server. 

Assuming your CSV data looks like this:

 001,John,Smith,Sales,5000,5500  
 002,Jane,Doe,HR,6000,6500  
 003,Mike,Johnson,IT,5500,6000  
 ...  

Each field is:

• Employee ID: Position 1-3
• First Name: Position 5-10
• Last Name: Position 12-17
• Department: Position 19-24
• Current Salary: Position 26-30
• Target Salary: Position 32-36

Reformatting the CSV file 

After uploading the CSV file to Mainframe, we need to reformat the CSV file into a fixed-field dataset where each column value starts at a specific position. We can achieve this using DFSORT's PARSE feature.

 //REFORMAT EXEC PGM=SORT  
 //SYSOUT  DD SYSOUT=*  
 //SORTIN  DD DSN=INPUT.CSV,DISP=SHR  
 //SORTOUT DD DSN=INPUT.DATA,DISP=(NEW,CATLG,DELETE),  
 //       SPACE=(CYL,(1,1)),UNIT=SYSDA  
 //SYSIN  DD *  
  OPTION COPY  
  INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=3),  
         %02=(ENDBEFR=C',',FIXLEN=6),  
         %03=(ENDBEFR=C',',FIXLEN=7),  
         %04=(ENDBEFR=C',',FIXLEN=6),  
         %05=(ENDBEFR=C',',FIXLEN=5),  
         %06=(FIXLEN=5)),  
     BUILD=(%01,3X,%02,2X,%03,X,%04,2X,%05,2X,%06)  
 /*  

Explanation

OPTION COPY: Instructs DFSORT to copy the input records.

INREC PARSE: Defines how to parse the input CSV records:

• %01=(ENDBEFR=C',',FIXLEN=3): Parses the first field (Employee ID) up to the comma and with a fixed length of 3.
• %02=(ENDBEFR=C',',FIXLEN=6): Parses the second field (First Name) up to the comma and with a fixed length of 6.
• %03=(ENDBEFR=C',',FIXLEN=7): Parses the third field (Last Name) up to the comma and fixed length of 6.
• %04=(ENDBEFR=C',',FIXLEN=6): Parses the fourth field (Department) up to the comma and fixed length of 6.
• %05=(ENDBEFR=C',',FIXLEN=5): Parses the fifth field (Current Salary) up to the comma and fixed length of 5.
• %06=(FIXLEN=5): Parses the sixth field (Target Salary) with fixed length of 5.

BUILD: Rebuilds the records with fixed positions and adds spaces as needed.

Generating SQL Queries with DFSORT

Now that we have the fixed-field dataset (INPUT.DATA), we can proceed to generate the SQL queries:

 //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)),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 SALARY = ',31,5,80:X,/,  
        C';',80:X,/,  
        80:X)   
 /*   

Explanation

OPTION COPY: Instructs DFSORT to copy the input records.

OUTFIL: Defines the output file, the default being SORTOUT.

BUILD: Specifies the format of the output records. Here's a breakdown:

• C'-- UPDATE FOR ': Adds a comment with the employee's name.
• 7,6,X,15,7,80:X,/,: Extracts the first name and last name. Adds a line break (/) after 80th byte.
• C'UPDATE EMPLOYEES ',80:X,/,: Static text for the SQL SET statement. Adds a line break (/) after 80th byte so that further statements of the SQL can be written in the next line. 
• C'  SET SALARY = ',38,5,80:X,/,: Static text for the SQL SET statement. Extracts the target salary value from position 38, length 5 and places it after the SET statement. Adds a line break after 80th byte.
• C' WHERE EMP_ID = ',1,3,80:X,/,: Static text for the WHERE clause. Extracts the employee ID from position 1, length 3 and places it after the first column in the WHERE clause. Adds a line break after 80th byte. 
• C' AND SALARY = ',31,5,80:X,/,: Adds a condition for the current salary. Extracts the current salary value from position 31, length 5. As usual, a line break is added after the 80th byte. 
• 32,5: Extracts the target salary value from position 32, length 5.
• C';',80:X,/,: Adds a semicolon to end the SQL statement. A line break is added after the 80th byte.
• 80:X: Adds an empty line.

The Output

After running the DFSORT step, the OUTPUT.SQL dataset will contain SQL update statements like this:

 -- UPDATE FOR JOHN  SMITH  
 UPDATE EMPLOYEES   
   SET SALARY = 5500   
  WHERE EMP_ID = 001   
   AND SALARY = 5000  
 ;  
   
 -- UPDATE FOR JANE  DOE  
 UPDATE EMPLOYEES   
   SET SALARY = 6500   
  WHERE EMP_ID = 002   
   AND SALARY = 6000  
 ;  
   
 -- UPDATE FOR MIKE  JOHNSON  
 UPDATE EMPLOYEES   
   SET SALARY = 6000   
  WHERE EMP_ID = 003   
   AND SALARY = 5500  
 ;  

It is to be noted that for one record in the input dataset, DFSORT wrote 7 lines in the output dataset. Now that the bulk SQL queries are generated, the next step would be to execute the SQL queries using IKJEFT01 utility. 

Conclusion

By leveraging DFSORT’s powerful data manipulation capabilities, you can automate the generation of SQL queries for bulk updates to your DB2 tables. This method not only saves a significant amount of time but also minimizes errors associated with manual SQL writing. Whether you’re handling hundreds or thousands of updates, DFSORT provides a robust and efficient solution for your data processing needs on the mainframe.

There are other ways to generate bulk SQL queries. In the next blog post, I'll show you how to achieve the same task using REXX. 

Hope this helps. Should you have any questions, please leave them in the Comments section below. 

Solving a problem statement using IBM DFSORT

Folks, in this post let's try to solve a problem - which I recently came across - using IBM's DFSORT utility. 

Problem statement

There are 2 flags for an account number. 

If both the flags are 'Y', the output dataset should contain two records with account number and the name of the flag. 

If any one of the flag is 'Y', output dataset should contain only one record with account number and the name of the flag. 

For example, Account AAA has got two flags set to 'Y', so the output dataset should contain two records for the account AAA.

Input:

Output:

My approach

As soon as I realized that a record in the input must be broken into 2 when both the flags of an account number are 'Y', I recalled the usage of / or n/ which is used to insert blank records in the output. But,  / or n/ is supported only by the OUTFIL control statement. 

So, OUTFIL control statement with IFTHEN...WHEN condition can be used to validate the flags and write the account number and the name of the flag to the output dataset. 

As the problem statement implicitly states that an account number with both the flags as 'N' be omitted, OMIT COND can be used to exclude such records before sorting.

Alright! Let's code the control statements. 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //STEP01 EXEC PGM=SORT                           
 000003 //SORTIN DD *                               
 000004 AAA Y Y                                   
 000005 BBB Y N                                   
 000006 CCC N Y                                   
 000007 DDD N N                                   
 000008 //SORTOUT DD SYSOUT=*                            
 000009 //SYSOUT DD SYSOUT=*                            
 000010 //SYSIN  DD *                               
 000011  SORT FIELDS=COPY                             
 000012  OMIT COND=(5,1,CH,EQ,C'N',AND,7,1,CH,EQ,C'N')               
 000013  OUTFIL IFTHEN=(WHEN=(5,1,CH,EQ,C'Y',AND,7,1,CH,EQ,C'Y'),         
 000014     BUILD=(1,3,X,C'KA',/,1,3,X,C'TN')),                
 000015     IFTHEN=(WHEN=(5,1,CH,EQ,C'Y',AND,7,1,CH,EQ,C'N'),         
 000016     BUILD=(1,3,X,C'KA')),                       
 000017     IFTHEN=(WHEN=(5,1,CH,EQ,C'N',AND,7,1,CH,EQ,C'Y'),         
 000018     BUILD=(1,3,X,C'TN'))                        
 ****** **************************** Bottom of Data ****************************  

There are 3 conditions coded in the OUTFIL IFTHEN control statement. 

• The first condition (in line #13) checks for both the flags to be 'Y'. If true, 2 records should be written in the output dataset with the account number and the name of the flag in each record. Note the BUILD parameter (in line #14) with a / to indicate a new output record to be started after writing the account number with the first flag name. The account number and the second flag's name will be written in the new output record. 
• The second and third conditions writes to the output dataset, the account number and the flag name, if any of the flag is 'Y'.

Output after submitting the JCL:

 ********************************* TOP OF DATA **********************************  
 AAA KA                                       
 AAA TN                                       
 BBB KA                                       
 CCC TN                                       
 ******************************** BOTTOM OF DATA ********************************  

There you go!

Now, it's your turn. Use the comments section to show how your approach would've been (or will be) for this problem statement.

How to sort on the bits of a byte using IBM DFSORT?

Recently, I came across a DFSORT coding challenge titled as "Odds & Evens".

The problem statement goes like this - Given a file with valid sequence numbers in columns 1 thru 6, sort the file so the corresponding output has all the even numbered records first, followed by all the odd numbered records.

I put on my thinking cap 🎩 for a while and came up with the following answer:

 ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ***************************** Top of Data ******************************  
 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 //STEP01  EXEC PGM=SORT                           
 //SORTIN  DD *                               
 000001                                   
 000002                                   
 000003                                   
 000004                                   
 000005                                   
 000006                                   
 000007                                   
 000008                                   
 000009                                   
 000010                                   
 000011                                   
 000012                                   
 000013                                   
 000014                                   
 000015                                   
 000016                                   
 000017                                   
 000018                                   
 000019                                   
 000020                                   
 //SORTOUT DD SYSOUT=*                            
 //SYSOUT  DD SYSOUT=*                            
 //SYSIN   DD *                               
   INREC IFTHEN=(WHEN=GROUP,RECORDS=2,PUSH(10:SEQ=1))            
   SORT FIELDS=(10,1,CH,D,1,6,CH,A)                     
   OUTREC FIELDS=(1,6)                            
 /*                                     

I just formed a group of 2 records and PUSH'ed sequence numbers (of 1 byte) for each record of the group. As there are only 2 records in a group, the sequence number will be 1 for the first record and 2 for the second record. The sequence number will be restarted from 1 when a new group is started. 

Then, I used the sequence number field (at col 10) in the SORT statement to sort it in the descending order so that all the records with sequence number as 2 will be at the top.  A secondary sort was applied on the first 6 bytes. 

Submitting this job, I got the following output,

  COMMAND INPUT ===>                                            SCROLL ===> CSR   
 ********************************* TOP OF DATA **********************************  
 000002                                       
 000004                                       
 000006                                       
 000008                                       
 000010                                       
 000012                                       
 000014                                       
 000016                                       
 000018                                       
 000020                                       
 000001                                       
 000003                                       
 000005                                       
 000007                                       
 000009                                       
 000011                                       
 000013                                       
 000015                                       
 000017                                       
 000019                                       
 ******************************** BOTTOM OF DATA ********************************  

WHEN=GROUP is one amazing feature in DFSORT, thanks to Frank Yaeger from IBM DFSORT Development team, as he is one of the brains behind the invention of WHEN=GROUP.

We got the answer. Are we done here?

Nope, I'm just done with the Intro. 

The main reason behind the idea of writing this blog post was that when I was looking at other answers, I stumbled upon a solution which had a syntax that I've never seen before. It goes like this: 

SORT FIELDS=(6.7,0.1,BI,A),EQUALS

Most of us would use the SORT control statement to specify the control field based on which the sorting should take place. We provide,

1. the position of the field within the record
2. the length of the field (in bytes)
3. the format of the data in control field
4. the order in which field must be sorted (ascending or descending)

Let's take the first 2 items. The position of the field within the record is the byte positon relative to the beginning of the record. The length of the field is usually expressed in integer numbers of bytes. We deal with Bytes (and a pet lover has to deal with bites 🐶 sometimes).

Let's take a look under the hood 🔧

A byte consists of 2 nibbles and each nibble is 4 bits long. A bit is either 0 or 1.

IBM Mainframe uses the EBCDIC character encoding. Each character is represented by its 8 bit EBCDIC Code. When we turn on the Hex mode, we will be able to see an hex value for each byte. When the hex value of each byte is converted to binary, we'll get the corresponding bits. 

For example, 

SRINI becomes,

E2        D9        C9        D5        C9                  Hexadecimal

11100010  11011001  11001001  11010101  11001001    Binary

📣IBM DFSORT allows us to sort on the bits of a byte with "bytes.bits" notation. 

How to sort on the bits of a byte?

Now, we know that each character has got an 8 bit binary value, we can use the bytes.bits notation to sort using bits.

• First, specify the byte location relative to the beginning of the record and follow it with a period.
• Then, specify the bit location relative to the beginning of that byte. Remember that the first (high-order) bit of a byte is bit 0 (not bit 1); the remaining bits are numbered 1 through 7.

In SORT FIELDS=(6.7,0.1,BI,A),EQUALS statement,

6.7 - says that the starting postion is the last bit in byte 6. 

0.1 - says that the length is 1 bit. 

BI  - for Binary format as we want to sort on bits

A  - for Ascending order. 

But why 6.7 as the start position of the control field? 

That's because by looking at the 6th byte of every sequence number, we can say whether that's an even number or odd number.

Example:

000001 - 👀 -> that's an odd number

000002 - 👀 -> that's an even

000003 - 👀 -> that's an odd

000004 - 👀 -> that's an even

000005 - 👀 -> that's an odd

000006 - 👀 -> that's an even. I'm tired😑

....

....

.... and so on.

Another significance is that for each even number, the Least significant bit (the last bit) is 0 and for each odd number, it's 1. 

Example:

1         EBCDIC character

F1        Hexadecimal  

11110001  Binary

2         EBCDIC character

F2        Hexadecimal

11110010  Binary

3         EBCDIC character   

F3        Hexadecimal

11110011  Binary

4         EBCDIC character

F4        Hexadecimal

11110100  Binary

Hence, if we sort the last bit of 6th byte in ascending order, we would get all the even numbered records first, followed by the odd numbered records. 

The EQUALS parameter is coded in the SORT statement to preserve the original sequene in the output. If EQUALS is not coded, then the output will have all the even numbered records first, followed by the odd numbered record but the even/odd numbered records will not be in sorted order.

Let's try running this SORT operation using Python 🐍

We can make use of the Python API's provided by ZOAU to run the SORT operation. Z Open Automation Utilities (abbreviated to ZOAU) lets you perform many tasks on z/OS without needing to get into JCL. IBM has developed a bridge between Python and z/OS by creating API's for Python which allow Pythonistas to access z/OS resources. 

Before we start, we need the following stuff to run the SORT operation from Python:

1. VS Code with Zowe explorer and IBM Z Open Editor extensions.
2. Access to Zowe explorer.
3. Access to USS (Unix System Services). 
4. Little bit of Python Skills.

Note: Access to Zowe explorer and USS can be obtained when you sign up for MTM2020.

First, we need to create a new file under your home directory (/z/zxxxxx) in Unix Sytem Services. Use the touch command to create a new file.

I created one using this command, touch run_sort.py. Then, I used the IBM Z Open Editor to write the following code inside this file.

 

I've used Trinket to embed the Python code in this blog post. Note that you may not be able to RUN 🏃 this script as the ZOAU utilities for Python aren't available in Trinket.

Let's walk through the code.

Lines 1 thru 3: The required ZOAU libraries for Python are imported so that you can use them in your code. 

Lines 5 thru 9: Line #5 uses the os.getenv() method in Python with 'USER' as argument. As the operating system that Python is running under is z/OS, USERID variable is assigned with your TSO user ID. 

Lines 6 thru 9 has got 3 variables of string type to store the dataset names. 

Lines 11 thru 28: Lines 11 thru 28 mimics the functionality of IEFBR14 utility. These lines delete the datasets before creation. We make use of the zoautil_py.Datasets module which has got several dataset related functions like create, delete, exists and so on. 

Lines 30 thru 41: Writes data into the SORTIN and SYSIN datasets. 

Line #31 defines an empty list called num.This list is created to store the sequence numbers from 1 to 20. Read more about lists and how to access the elements in a list 👉 here. 

Lines 34 and 35 creates sequence numbers from 1 to 20 with the help of for loop and range() function in Python. zfill() method is used to populate leading zeros. As zfill() method can be applied only on string data, the numbers are type converted to string using str() function (in line #35). All the sequence numbers are appended to the list, num. The list is then written to the SORTIN dataset.

Lines 40 and 41 writes the SORT statements to the SYSIN dataset. The write functionality is achieved via zoautil_py.Datasets module.

Lines 43 thru 53: Line 44 creates an empty list called dd_names to store the DD names that are needed for the SORT program to run. 

In Line 53, MVSCmd.execute API is called to run the program SORT with arguments MSGPRT=CRITICAL,LIST (which goes to the PARM parameter in EXEC statement) and the list of DDStatements created in lines 47 thru 50. When this instruction is executed, a job might be submitted on z/OS in the background. 

Line #55 checks the return code from MVSCmd.execute API call. If it's zero, a message is displayed in the terminal and the output dataset (SORTOUT) created from the previous execution is read. 

When the program is run with python3 run_sort.py command in terminal, we get the following output.

Note: Click on the picture to get an enlarged view. 

The records in the output dataset are displayed in the terminal after running the Python code. The even numbered records are at the top, followed by the odd numbered records.

The datasets that were created from Python can also be accessed from the terminal. The datasets are shown below:

The input dataset to the SORT program. The sequence numbers were generated in Python.

The SYSIN input to the SORT program. The SORT statements were written from Python.

The output dataset. 

We have reached the bottom of this post and we discussed about two things:

1. How to sort on the bits of a byte using IBM DFSORT?
2. How to perform DFSORT operation using Python? 

I hope the content in this post was helpful to you. Please post your questions/suggestions in the Comments section of this post. 

Thx and Happy Weekend!

How to convert rows to columns in a dataset using IBM DFSORT?

Welcome! In this blog post I am writing  about another scenario. Are you ready?

One of the site that I worked for dealt with Invoices, approvals and payments. There was a workflow which usually began from the point where the system received invoices in bulk. The system will then review and approve the supplier invoices. Upon approval, the supplier was paid. 

The application was geographically spread out in 3 regions of the globe and the workflow was common across the regions.

There was a requirement to create a report capturing the monthly statistics on invoices, approvals and payments across the 3 regions and send it over email to appropriate stakeholders. 

Invoices, approvals and payments' data were stored in different DB2 tables. Hence, 3 SQL SELECT queries were combined using UNION ALL to pull the necessary details. The output from SQL had the following data:

INVOICES|+00099999

APPROVALS|+00012345

PAYMENTS|+00003456

It was a pipe delimited file, so if you imported the file to Microsoft Excel with pipe delimiter, you'll have 2 columns and 3 rows as shown below.

Since we had to pull this data for 3 regions, there were 3 output datasets with the same record structure.

The next step was to create a report out of these files. The expected final output was,

The final report. Rows are converted to columns. There is an header and trailer record too. 

We used ICETOOL ⛄ to create the report

There are a lot of stuff which we put together with ICETOOL to accomplish the output. Let's look at them one by one. 

ICETOOL is a multipurpose DFSORT utility that uses the capabilities of DFSORT to perform multiple operations on one or more datasets in a single step.

Step 1: 

Each input dataset was a pipe delimited file. The 3 SQL output datasets were concatenated and passed as one input to the ICETOOL step. There were 2 columns (fields) in each record of the dataset. But the columns didn't start in fixed position. For example, the count for Invoices would start at 10th position whereas for Approvals, it was at 11th position. So, we had to make the columns start at fixed positions and we used the PARSE operand in DFSORT to do that. 

As we knew each output dataset from SQL will have only 3 records and they all look similar (except for the variation in count values), WHEN=GROUP in DFSORT was used to create 3 groups. Each group was assigned a unique ID and each record in the group was assigned with sequence numbers.

WHEN=GROUP is used to introduce a group of records to DFSORT.

 

Well, you may ask why we should do this? 🤔 With sequence numbers and ID's for the group, we can manipulate/arrange the records in the way we wish.  

At this stage, the output looked like below:

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 INVOICES  +000999991 1                           
 000002 APPROVALS +000123451 2                           
 000003 PAYMENTS  +000034561 3                           
 000004 INVOICES  +000000192 1                           
 000005 APPROVALS +000000162 2                           
 000006 PAYMENTS  +000000152 3                           
 000007 INVOICES  +001000503 1                           
 000008 APPROVALS +000123613 2                           
 000009 PAYMENTS  +000034713 3                           
 ****** **************************** Bottom of Data ****************************  

Following points are to be noted from the output:

1. The fields were in fixed positions now💪. 

2. There were 3 groups: each group was assigned with a unique ID in position 21. Each record in a group was assigned with sequence numbers in position 23.

Let's see how we converted the fields from rows to column?

Consider the first 3 records (first group). 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 INVOICES  +000999991 1                           
 000002 APPROVALS +000123451 2                           
 000003 PAYMENTS  +000034561 3   

Note: Ignore the string literals (the first 10 bytes) and the (+) symbol in 12th position (as the counts can't go beyond zero in our case) for the remainder of this blog post.

The count values (in position 13 thru 20) are placed row wise, one after the another. If we sum these count values, we'll get 115800. 

Right! What if we had the count values in the following way? The count values are still placed row wise, one after the other, but they are in different positions in each record. And, there are lot of zeros🤔.

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 00099999 00000000 00000000                         
 000002 00000000 00012345 00000000                          
 000003 00000000 00000000 00003456  

Now, if we sum each of the columns (we have got 3 columns here) we would get the following output. 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 00099999 00012345 00003456   

You just witnessed how we converted the rows to columns 👏. Awesome?  All thanks goes to those zeros 👍 in each record which helped us consolidate 3 records to one. Those zeros acted as placeholders. 

We implemented this idea using DFSORT. Instead of zeros, we inserted Binary Zeros (Hex Value - 00) to the input records in the following way: 

• The group's first record was populated in first 8 bytes. All other positions were filled with Binary zeros. 
• The group's second record was populated in positions 10 thru 17. All other positions were filled with Binary zeros.
• The group's third record was populated in positions 19 thru 26. All other positions were filled with Binary zeros. 

A group's first, second and third records were identified with the help of sequence numbers that we populated for each group (in 23rd position) with the help of PUSH parameter. 

Binary zeros can be used in DFSORT as placeholders so that it can be filled with data at a later point of time.

Let's look at the code now. 

INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=10),%02=(ENDBEFR=C'|',     

            FIXLEN=9)),BUILD=(%01,12:%02)                        

SORT FIELDS=COPY                                                 

OUTREC IFTHEN=(WHEN=GROUP,RECORDS=3,PUSH(21:ID=1,23:SEQ=1)),     

       IFTHEN=(WHEN=(23,1,CH,EQ,C'1'),BUILD=(1:13,8,18Z,70:21,1, 

              80:X)),                                            

       IFTHEN=(WHEN=(23,1,CH,EQ,C'2'),BUILD=(9Z,10:13,8,18:8Z,   

              70:21,1,80:X)),                                    

       IFTHEN=(WHEN=(23,1,CH,EQ,C'3'),BUILD=(18Z,19:13,8,70:21,1,

              80:X))                                             

What does this code do? 

INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=10),%02=(ENDBEFR=C'|',     

            FIXLEN=9)),BUILD=(%01,12:%02)

ENDBEFR and FIXLEN parameters of PARSE operand are used to define the rules for extracting vaariable length data to %nn fixed parsed fields. ENDBEFR stops extracting data at the byte before the specified string. FIXLEN is used to specify the length of the fixed area to contain the extracted data. %01 is the fixed parsed field which holds the string literal such as INVOICES, APPROVALS and PAYMENTS. %02 is the fixed parsed field which holds the count value. The BUILD statement is used in INREC PARSE to build the record with fixed parsed fields. 

SORT FIELDS=COPY - SORT statement with COPY option.

OUTREC IFTHEN=(WHEN=GROUP,RECORDS=3,PUSH(21:ID=1,23:SEQ=1)),     

       IFTHEN=(WHEN=(23,1,CH,EQ,C'1'),BUILD=(1:13,8,18Z,70:21,1, 

              80:X)),                                            

       IFTHEN=(WHEN=(23,1,CH,EQ,C'2'),BUILD=(9Z,10:13,8,18:8Z,   

              70:21,1,80:X)),                                    

       IFTHEN=(WHEN=(23,1,CH,EQ,C'3'),BUILD=(18Z,19:13,8,70:21,1,

              80:X))  

There are 4 IFTHEN...WHEN conditions coded in OUTREC. The first condition assigns a unique ID for each set of 3 records and sequence number for each record in the group. PUSH parameter helps in doing that. A group is identified with WHEN=GROUP and RECORDS=3 parameters

 

2nd, 3rd and 4th IFTHEN...WHEN conditions reformat the input records which has got sequence numbers as 1, 2 and 3 respectively. Binary zeros are inserted in the BUILD statement. For example, 18Z means insert 18 Binary zeros. 

After running these SORT statements, we got the following output:

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 00099999                                                             1   
 000002          00012345                                                    1   
 000003                   00003456                                           1   
 000004 00000019                                                             2   
 000005          00000016                                                    2   
 000006                   00000015                                           2   
 000007 00100050                                                             3   
 000008          00012361                                                    3   
 000009                   00003471                                           3   
 ****** **************************** Bottom of Data ****************************  

Turning the HEX mode ON, you'll be able to see the binary zeros (X'00') spread out over each record.

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 000001 00099999                                                             1   
        FFFFFFFF0000000000000000004444444444444444444444444444444444444444444F44  
        000999990000000000000000000000000000000000000000000000000000000000000100  
 ------------------------------------------------------------------------------   
 000002          00012345                                                    1   
        000000000FFFFFFFF0000000044444444444444444444444444444444444444444444F44  
        000000000000123450000000000000000000000000000000000000000000000000000100  
 ------------------------------------------------------------------------------   
 000003                   00003456                                           1   
        000000000000000000FFFFFFFF4444444444444444444444444444444444444444444F44  
        000000000000000000000034560000000000000000000000000000000000000000000100  
 ------------------------------------------------------------------------------   
 000004 00000019                                                             2   
        FFFFFFFF0000000000000000004444444444444444444444444444444444444444444F44  
        000000190000000000000000000000000000000000000000000000000000000000000200  
 ------------------------------------------------------------------------------   
 000005          00000016                                                    2   
        000000000FFFFFFFF0000000044444444444444444444444444444444444444444444F44  
        000000000000000160000000000000000000000000000000000000000000000000000200  
 ------------------------------------------------------------------------------   
 000006                   00000015                                           2   
        000000000000000000FFFFFFFF4444444444444444444444444444444444444444444F44  
        000000000000000000000000150000000000000000000000000000000000000000000200  
 ------------------------------------------------------------------------------   
 000007 00100050                                                             3   
        FFFFFFFF0000000000000000004444444444444444444444444444444444444444444F44  
        001000500000000000000000000000000000000000000000000000000000000000000300  
 ------------------------------------------------------------------------------   
 000008          00012361                                                    3   
        000000000FFFFFFFF0000000044444444444444444444444444444444444444444444F44  
        000000000000123610000000000000000000000000000000000000000000000000000300  
 ------------------------------------------------------------------------------   
 000009                   00003471                                           3   
        000000000000000000FFFFFFFF4444444444444444444444444444444444444444444F44  
        000000000000000000000034710000000000000000000000000000000000000000000300  
 ------------------------------------------------------------------------------   

   

It's time 🕒 to sum the records to one, thereby converting the rows to column. 

Using SUM FIELDS with Binary Zeros has got a perk⭐

Hex representation of the first count value (00099999) is F0F0F0F9F9F9F9F9. 

Hexadecimal representation of EBCDIC displayable characters. 

The hex value of  a binary zero is 00. 

When the count value is added with binary zeros (in BI format), 

 F0F0F0F9F9F9F9F9

+0000000000000000

 ----------------

 F0F0F0F9F9F9F9F9 → 00099999

We get the same value 😀. 

Let's take another example. The Hex value of my name (in upper case) is E2D9C9D5C9E5C1E2C1D5

Hexadecimal representation of EBCDIC displayable characters.

When my name, a string, is added with Binary zeros (in BI format), I get my name back. 

  E2D9C9D5C9E5C1E2C1D5

+00000000000000000000

 --------------------

 E2D9C9D5C9E5C1E2C1D5 → SRINIVASAN

The takeaway is - with Binary zeros, we can even use SUM FIELDS on strings 😎.

Step 2:

We summed the count values on their Group ID. This means, 

• The count value in the first record (in positions 1 thru 8) of each group was summed up with first 8 bytes of binary zeros in records 2 and 3 of the group. 
• The count value in the second record (in positions 10 thru 17) of each group was summed up with 8 bytes of binary zeros (in positions 10 thru 17) in records 1 and 3 of the group. 
• The count value in the third record (in positions 19 thru 26) of each group was summed up with 8 bytes of binary zeros (in positions 19 thru 26) in records 1 and 2 of the group. 

Let's look at the code. 

SORT FIELDS=(70,1,CH,A)                                     

SUM FIELDS=(1,8,BI,10,8,BI,19,8,BI)                         

OUTREC FIELDS=(1,8,X,10,8,X,19,8,X,C'1',71:SEQNUM,1,ZD,80:X) 

If a field is in BI format, it can be 2, 4 or 8 bytes long. Here, the count value and binary zeros are 8 bytes long, hence we SUM fields on 8 bytes of BI.

OUTREC FIELDS is used to format the summed output fields. 

We got the following output. 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 00099999 00012345 0000346 1                     1   
 000002 00000019 00000016 0000002 1                     2   
 000003 00100050 00012361 0000348 1                     3   
 ****** **************************** Bottom of Data ****************************   

If you compare this output with one that we've got after using PARSE operand, 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 INVOICES  +000999991 1                           
 000002 APPROVALS +000123451 2                           
 000003 PAYMENTS  +000034561 3                           
 000004 INVOICES  +000000192 1                           
 000005 APPROVALS +000000162 2                           
 000006 PAYMENTS  +000000152 3                           
 000007 INVOICES  +001000503 1                           
 000008 APPROVALS +000123613 2                           
 000009 PAYMENTS  +000034713 3                           
 ****** **************************** Bottom of Data ****************************  

you'll notice that the rows are converted to columns now. 

That's it. I hope you now understand the usage of binary zeros. In the next post, I will continue with the rest of the code that we prepared to meet the requirements. 

Should you have any questions/suggestions, please use the Comments section below. Thx.  

How to exclude a group of records using IBM DFSORT?

One of the site I worked for, dealt with Invoices. We used Electronic Data Interchange (EDI) to exchange trading information in a standard format between suppliers/customers. 

EDI eliminates a lot of manual intervention by the transmission of electronic messages between computer systems. Do not confuse the term electronic message with an e-mail message. An email message 📧 is usually not processed by the receiving system whereas, EDI messages are intended to undergo automatic processing in the receving system. 

There is a specific format in EDI to transmit invoice data and it is EDI 810. It is an electronic version of an invoice that contains the information requied for a usual paper-invoice purchase transaction. 

Use of EDI to transmit data can shorten the lead time between invoice receipt and fulfillment of orders. EDI is often referred as 'paperless trading'.

The EDI 810 documents which were received from the site's customers were translated to a proprietary file format as required by the backend application. The translated file usually had a lot of batches and within each batch there were a lot of invoices. An Invoice would have detail record(s) within it. Refer the following pictures for a better understanding on the file's structure.

Note: Click on the picture to get an enlarged view.

Translated EDI 810 file with lot of batches in it.

A batch from the translated EDI 810 file with many invoices; Each invoice with many detail records.

Each batch would have a batch header and batch trailer record. Each invoice within a batch would have an invoice header and an invoice trailer record. Between an invoice header and trailer records, would be the invoice detail records. 

The format of the file had a record type field in each record to identify whether that record is a batch header, a batch trailer, an Invoice header, an Invoice detail, an Invoice trailer and so on. 

This translated file was fed as input to a COBOL DB2 program. During the program's execution, each Invoice and its detail record(s) were fed to an internal table with maximum limit hardcoded as 99,999. Sometimes, an invoice's detail records would exceed 99,999 thereby making the program to fail with ABEND SOC4. The abend occurred so frequently that we had to implement a permanent fix. 

We decided to remove the entire batch which had invoice(s), whose detail records exceeded 99,999.

Experts suggested to write a COBOL program to exclude the batch, but I had other ideas (DFSORT 😎)

If that title sounds familiar, all thanks goes to Mc Dowell's advertisement which featured MS Dhoni 🏏.

The approach we adopted was to uniquely number each record in the following way:

• Assign a unique ID, 9 bytes long, to each batch in the file.
• Assign a unique ID, 9 bytes long, to each Invoice that is part of the batch
• Assign sequence numbers, 9 bytes long, for each record that is part of the Invoice.

We used the numbered file to check if the invoice sequence number exceeded 99,999. If true, the batch ID of that invoice was written to an intermediate file.

JOINKEYS application in DFSORT was used to match the numbered file and the intermediate file to create 2 output files; BYPASS file that had the records of the batch which had to be bypassed (as the invoice detail records exceeded 99,999) and FINAL file that had the records of all other batches (whose invoice detail records doesn't exceed 99,999).

Note: For better understanding, I've created an input file which is somewhat similar to what I had at my site. I'll be using that input file in an example to show how we can exclude a group of records from the file. The ID's and sequence numbers are only 5 bytes long in the example, as opposed to the 9 bytes that we used at our site. Also, in the example we will exclude the batch which holds an invoice with detail records exceeding 20 as opposed to 99,999. 

1st byte of the input file is the Record type field. 
0 - Batch header record. 
1 - Invoice header record. 
8 -  Invoice trailer record. 
9 - Batch trailer record.
Other record types are out of scope for the problem that we are dealing.

 

The input file with 2 batches. Each batch has got an invoice. The 2 batch's invoice data records exceed 20.

Step 1: 

The first step is to assign,

• a unique ID for each batch,
• a unique ID for each invoice within that batch,
• sequence numbers for all the records between an Invoice header and Invoice trailer record.

To do this, we will use DFSORT's IFTHEN WHEN=GROUP feature, with BEGIN and PUSH parameters. 

First step. Assigning unique IDs. 

INREC in DFSORT is used to format fields before sorting.

First INREC IFTHEN condition assigns a unique ID for each batch:

WHEN=GROUP - Used to introduce a group of records to DFSORT. 

BEGIN=(1,1,CH,EQ,C'0') - Each '0' in the first byte indicates the start of a new batch. 

PUSH(41:ID=5) - Assigns an identifier, 5 bytes long starting from 41st position, for each batch. The identifier is +1'd when a new batch is started. 

Second INREC IFTHEN condition assigns a unique ID for each invoice within a batch and sequence of numbers for each records that are part of the invoice. 

WHEN=GROUP - Used to introduce a group of records to DFSORT.

BEGIN=(1,1,CH,EQ,C'1') - Each '1' in the first byte indicates a new invoice within that batch.

PUSH(46:ID=5,51:SEQ=5) - Assigns an identifier, 5 bytes long starting from 46th position, for each Invoice. The identifier is +1'd when a new invoice is started. Also, assigns sequence number, 5 bytes long starting from 51st position. The sequence number will be restarted from 1 whenever a new invoice header record is encountered.

OUTREC in DFSORT is used to format fields after sorting.

The OUTREC IFTHEN conditions given at the last are used to populate zeros, in the place of Invoice ID and Invoice sequence number, in the Batch header and trailer records. 

Running the first step will give the following output. 

This is how the numbered file looks like.
Col 41 thru 45 contains the Batch ID. 
Col 46 thru 50 contains the Invoice ID. 
Col 51 thru 55 contains the Invoice Sequence number. 

Step 2: 

The second step is to find that batch ID whose invoice sequence number exceeds 20. Output file created from step 1 is passed as input to this step. It's evident from the input file that Batch 2 contains an Invoice which has got more than 20 detail records. 

INCLUDE COND=(51,5,ZD,EQ,21) - Only those records whose sequence number equals 21 is included for the processing. 

SORT FIELDS=(41,5,CH,A) - SORT the file based on the batch ID. 

SUM FIELDS=NONE - Remove duplicates. Sometimes, there can be multiple invoices within a batch with sequence number exceeding 20. In such cases, Batch ID might be written more than once. Hence, we are removing duplicates. 

OUTREC FIELDS=(1:41,5,80:X) - Write the Batch ID to an intermediate output file. 

We get the following output after running Step 2:

Batch ID whose invoice number is exceeding 20, is written to the output file.

Step 3:

The final step is to use JOINKEYS application in DFSORT to write 2 output files. One with the excluded batch and the other one with batch that should be sent as input to COBOL DB2 program. 

Input files to be used:

The output file from Step 2 (the one contains the batch ID which should be excluded).

The output file from Step 1 (the numbered file). 

Note: The order of the input files in JCL should be in such a way that the file with no duplicates should be provided first. 

JOINKEYS FILES=F1,FIELDS=(1,5,A) - Defines the key field from the first file with which the matching should occur. 

JOINKEYS FILES=F1,FIELDS=(41,5,A) - Defines the key field from the second file with which the matching should occur. 

REFORMAT FIELDS=(F2:1,55,F1:1,5,?) - The REFORMAT statement indicates the fields from File 1 and File 2 that we should include in the joined records. '?' symbol is used as an indicator with the following possible values:

"B" - indicates that it is a paired record.

"1" - indicates that it is an unpaired record from File 1.

"2" - indicates that it is an unpaired record from File 2. 

JOIN UNPAIRED,F2 - This statement is similar to RIGHT JOIN in DB2. The joined records will contain both the paired and unpaired records from File 2. 

SORT FIELDS=COPY - Sort statement. 

OUTFIL FNAMES=BYPASS,BUILD=(1,55),INCLUDE=(61,1,CH,EQ,C'B') - The output file with BYPASS as the DD name in the JCL will be written with the paired records i.e, all the records in the input file 2 which have 00002 as the Batch ID. 

OUTFIL FNAMES=FINAL,BUILD=(1,40),INCLUDE=(61,1,CH,EQ,C'2') - The output file with FINAL as the DD name in the JCL will be written with the unpaired records from File 2 i.e., the records whose batch ID is not 00002. 

After running Step 3, we get the following output files. 

The BYPASS file that contains all the records with Batch ID 00002.

The FINAL file which will be fed as input to the COBOL DB2 program. 

At our site, we used the BYPASS file in subsequent steps to extract crucial information such as the Batch number, Invoice number and so on, in order to send an email to business with details about the batches that were omitted for the day. 

That's it. As always, thanks much for reading. Have any questions or suggestions? Please post them in the comments section below. Thx!

P.S. Screen grab of Mainframe screens throughout this post were taken from Master the Mainframe system.