Fixing SQLCODE = -171 in Db2: Understanding the RIGHT() function error

    

Hiya! 👋 It's been a while since I wrote posts on my blog, and here I'm with a new problem that I faced recently. Stay tuned, as I also intend to write about a few other interesting challenges I faced recently, in the upcoming blog posts. 

Before going any further with the subject of discussion, I would like to announce 📢 that I've been recognized as an IBM Champion 🥁 for the 4th consecutive time in the IBM Z and LinuxONE expertise. I came to know about this program in the year 2021 after listening to a podcast of Subhasish Sarkar where he talked about the program and have been an advocate ever since. The more you contribute to the community that uses IBM products, such as IBM Z/Mainframe, the more likely you are to become an IBM Champion ⭐. My main source of contributions to the IBM Z community is through the articles that I create on this blog and through my acts of advocacy on LinkedIn. Last year, I wrote a series titled "Printing Shapes using COBOL" on a new blog that I created on Hashnode. If you haven't checked that out, click 👉 here. You'll learn to print some interesting shapes using COBOL. 

Alright! Let's continue with the topic in question: Fixing SQLCODE = -171 in Db2. 

                                       

Intro

It is super essential to not expose sensitive data stored in the Db2 tables after performing a database refresh. 

Database refresh is the process of extracting data from the tables in the production environment and loading it into the corresponding tables in the lower environment. Since Db2 tables in the lower environment are often subject to unintended modifications by developers, a refresh is usually performed to restore a clean state before testing critical changes.

In this post, we’ll explore a real-world scenario where this error appeared while trying to obfuscate data in a test environment. We’ll analyze the root cause, the faulty SQL, and the solution to prevent such errors.

Problem statement

Let's consider a scenario where we need to obfuscate a column storing bank account numbers in a table say, SAMPLE_TABLE.

Remember that there is a difference between masking data and obfuscating data. Masked data is typically used for display purposes (e.g., showing only the last four digits of a bank account number: XXXXXX1234). It is often reversible, meaning authorized users can see the full data. On the other hand, obfuscated data means permanently transforming where the original value cannot be derived (e.g., replacing the values with randomized numbers). Obfuscated data ensures that even if someone accesses the obfuscated data, it cannot be linked back to the original value.

Here 👇 is a sample SQL statement used for this purpose:

 UPDATE SAMPLE_TABLE  
 SET BANK_ACCOUNT_NUMBER = RIGHT(STRIP(DIGITS(CUSTOMER_ID * 1234),L,'0') ||  
                                 STRIP(DIGITS(CUSTOMER_ID * 2345),L,'0') ||  
                                 STRIP(DIGITS(CUSTOMER_ID * 3456),L,'0') ||  
                                 STRIP(DIGITS(CUSTOMER_ID * 4567),L,'0'),   
                                 LENGTH(STRIP(BANK_ACCOUNT_NUMBER)))  
 WHERE BANK_ACCOUNT_NUMBER <> ' ';  
 COMMIT;  

There are two columns involved in this SQL: BANK_ACCOUNT_NUMBER, a column defined as CHARACTER(35) and CUSTOMER_ID defined as INTEGER.

Fact 👉 The maximum length of a bank account number worldwide, when considering the International Bank Account Number (IBAN), can be up to 34 alphanumeric characters.

Logic behind the SQL 

• The obfuscated bank account number is generated by concatenating numeric transformations of CUSTOMER_ID
• The RIGHT() function extracts only the required number of digits, ensuring the new obfuscated value matches the original length.

The error: SQLCODE = -171

This SQL works fine most of the time, but in certain cases, it fails with:

 SQLCODE = -171, SQLSTATE = 42815  
 THE RIGHT FUNCTION HAS AN ARGUMENT THAT IS NOT VALID  

When does this happen?

Let’s assume CUSTOMER_ID = 99. 

The first argument of RIGHT() is computed as:

122166 || 232155 || 342144 || 452133 = 24 digits

Assume the BANK_ACCOUNT_NUMBER for this customer as 100000000000000000000145678 (27 digits).

Now, RIGHT(‘122166232155342144452133’, 27) fails because the string only has 24 characters, but we’re trying to extract 27 characters from the right side.

The fix: Ensuring a valid argument for RIGHT()

To prevent this error, we need to ensure that the second argument of RIGHT() never exceeds the length of the first argument. We can achieve this using the LEAST() function:

 UPDATE SAMPLE_TABLE  
 SET BANK_ACCOUNT_NUMBER = RIGHT(  
    STRIP(DIGITS(CUSTOMER_ID * 1234),L,'0') ||  
    STRIP(DIGITS(CUSTOMER_ID * 2345),L,'0') ||  
    STRIP(DIGITS(CUSTOMER_ID * 3456),L,'0') ||  
    STRIP(DIGITS(CUSTOMER_ID * 4567),L,'0'),  
    LEAST(LENGTH(STRIP(BANK_ACCOUNT_NUMBER)),   
       LENGTH(STRIP(DIGITS(CUSTOMER_ID * 1234),L,'0') ||  
           STRIP(DIGITS(CUSTOMER_ID * 2345),L,'0') ||  
           STRIP(DIGITS(CUSTOMER_ID * 3456),L,'0') ||  
           STRIP(DIGITS(CUSTOMER_ID * 4567),L,'0'))))  
 WHERE BANK_ACCOUNT_NUMBER <> ' ';  

How does this fix work?

LEAST(LENGTH(STRIP(BANK_ACCOUNT_NUMBER)), …) ensures that we don’t attempt to extract more characters than available. If the length of BANK_ACCOUNT_NUMBER is greater than the generated string, we only take what’s available.

Conclusion

We've hit the bottom of the post 🔚. A major takeaway for me in this scenario is to test edge cases to prevent out-of-bounds errors when dealing with string functions like RIGHT() and SUBSTR(). Have you encountered similar SQL issues? Let’s discuss in the comments! 

Disclaimer: This blog post is purely for educational purposes and does not contain any proprietary, confidential, or company-specific information. The examples provided are generic and fictional, intended to help developers understand SQLCODE = -171 in Db2.

Handling SQLCODE=-305 in COBOL Db2 Program

Hiya! 👋 In this post, let's have a look at a common issue that Mainframe Developers face when writing a COBOL program that interacts with Db2. 

Introduction

When dealing with COBOL Db2 programs, encountering SQLCODE=-305 can be a common issue. This error occurs when a null value is fetched from the database into a COBOL variable. COBOL does not know what nulls are. 

A null value is a special value that Db2 interprets to mean that no data is present. Null value is an unknown value and is not zero or blank

To handle this gracefully, there are several strategies you can employ. In this post, we will explore different methods to manage null values effectively, ensuring your COBOL Db2 programs run smoothly.

Understanding SQLCODE=-305

SQLCODE=-305 indicates that a null value was encountered in a column that was not expected to be null. This often happens during fetch operations when the program tries to retrieve data from the database. Without proper handling, this can cause the program to fail.

Click here to learn more about SQLCODE=-305 from Db2 manuals. 

Some options I recommend to handle nulls in COBOL DB2 Programs

1. Using Null Indicators
2. Using the VALUE Function
3. Using the COALESCE Function

Let's see them one by one. 

1. Using Null Indicators 

Null indicators are special variables used to determine whether a database column contains a null value. When fetching data, the null indicator variable captures the null status of the corresponding column, allowing the program to handle null values appropriately.

Declaring Null Indicators

In COBOL, a null indicator is typically declared as a half word binary field:

01  ACTUAL-VARIABLE        PIC X(20).
01  NULL-INDICATOR-VAR     PIC S9(4) COMP.

ACTUAL-VARIABLE is the variable that will store the fetched data.

NULL-INDICATOR-VAR is the null indicator variable.

Fetching Data with Null Indicators

When you retrieve a column with an indicator variable, DB2 puts the appropriate value in the indicator. To refer to an indicator variable in the INTO clause of a SELECT or FETCH statement, you need to code a colon, the appropriate Db2 column's host variable name, a space (optional), a colon, and the name of the indicator variable as shown below.

EXEC SQL
    FETCH NEXT FROM cursor-name
    INTO :ACTUAL-VARIABLE :NULL-INDICATOR-VAR
END-EXEC.

Handling Null Values

After fetching a row, you need to check the null indicator variable to determine if the column contains a null value:

IF NULL-INDICATOR-VAR < 0 THEN
    MOVE SPACES TO ACTUAL-VARIABLE
ELSE
    CONTINUE
END-IF.

Following table shows the value of the indicator variable and the corresponding column’s value:

Complete Example

Here’s a complete example of how to handle SQLCODE=-305 using null indicators in a COBOL DB2 program:

WORKING-STORAGE SECTION.
01  ACTUAL-VARIABLE        PIC X(20).
01  NULL-INDICATOR-VAR     PIC S9(4) COMP.

PROCEDURE DIVISION.
    EXEC SQL
        DECLARE cursor-name CURSOR FOR
        SELECT column-name FROM table-name
    END-EXEC.

    EXEC SQL
        OPEN cursor-name
    END-EXEC.

    PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL
            FETCH NEXT FROM cursor-name
            INTO :ACTUAL-VARIABLE :NULL-INDICATOR-VAR
        END-EXEC.

        IF SQLCODE = 0 THEN
            IF NULL-INDICATOR-VAR < 0 THEN
                MOVE SPACES TO ACTUAL-VARIABLE
            ELSE
                DISPLAY ACTUAL-VARIABLE
            END-IF
        END-IF
    END-PERFORM.

    EXEC SQL
        CLOSE cursor-name
    END-EXEC.

    STOP RUN.

2. Using the VALUE Function

The VALUE function allows you to provide a default value for columns that might contain nulls, thus avoiding the need for additional null indicator checks in your COBOL code. Remember, it takes exactly two arguments.

Example with VALUE Function

Here’s how you can use the VALUE function in your SQL query:

SELECT VALUE(column-name, 'default-value')
FROM table-name

In a COBOL DB2 program, it would look like this:

EXEC SQL
    DECLARE cursor-name CURSOR FOR
    SELECT VALUE(column-name, 'default-value')
    FROM table-name
END-EXEC.

3. Using the COALESCE Function

The COALESCE function can take multiple arguments and returns the first non-null value from the list. This function is also useful for handling nulls directly within your SQL queries.

Example with COALESCE Function

Here’s how you can use the COALESCE function in your SQL query:

SELECT COALESCE(middle_name, nickname, alias, 'N/A') AS name_or_default
FROM employees

In the above SQL, If all the columns in the COALESCE argument list are null, it should return 'N/A'.

In a COBOL DB2 program, it would look like this:

EXEC SQL
    DECLARE cursor-name CURSOR FOR
    SELECT COALESCE(column-name, 'default-value')
    FROM table-name
END-EXEC.

Conclusion

Handling null values in COBOL Db2 programs can be approached in various ways, each suitable for different scenarios. Besides using null indicators, VALUE, and COALESCE functions, you can leverage IFNULL/NVL functions, CASE statements, default values in table definitions, and additional application logic.

By understanding and utilizing these techniques, you can prevent SQLCODE=-305 error. Should you have any questions/suggestions, please add them in the Comments section below. 

Thx 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!

How to find the exact length of a string using COBOL?

In this post, let's see how we can find the exact length of a string in COBOL. 

By exact length, I mean not to account the trailing spaces while calculating the length. That's why we can't use FUNCTION-LENGTH because it returns the length which is sum of all the characters in the string plus the trailing spaces. 

In the following example, we have a data item, WS-NAME which can accept alphanumeric data upto 100 characters (PIC X(100)) and that's a lot for a name 😉. FYI, a place in New Zealand holds the Guiness World record for longest place name (85 letters).

Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu 😦

Code:

  IDENTIFICATION DIVISION.  
 PROGRAM-ID. LENGTH.  
 DATA DIVISION.  
   WORKING-STORAGE SECTION.  
     01 WS-NAME PIC X(100) VALUE SPACES.  
 PROCEDURE DIVISION.  
   MOVE 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu' TO WS-NAME.  
   DISPLAY 'Length is ' FUNCTION LENGTH(WS-NAME).  
 STOP RUN.  

Result after executing this code is given below:

Length is 100

Want to try running this code? Click 👉 here.

Instead of displaying the exact length (85 characters) of the string, FUNCTION-LENGTH has displayed the total length of the data item. 

It's clearly evident that we can't rely on FUNCTION-LENGTH if we are given a task of finding and announcing Guiness World Record for a place with longest name (using COBOL).

So what do we do now? 🤔 

The common approach to tackle 🔧this problem is to reverse the data item (using FUNCTION-REVERSE) containing the place's name and count for the leading spaces (using INSPECT verb). Then, subtract the count of trailing spaces from the total length of the data item. 

We have to reverse the string because we can't use INSPECT verb to count for the trailing spaces.

Here is the code 👇

 IDENTIFICATION DIVISION.  
 PROGRAM-ID. LENGTH.  
 DATA DIVISION.  
   WORKING-STORAGE SECTION.  
     01 WS-NAME PIC X(100) VALUE SPACES.  
     01 WS-COUNT PIC 9(3) VALUE 0.  
     01 WS-ACTUAL-LENGTH PIC ZZ9 VALUE 0.  
 PROCEDURE DIVISION.  
   MOVE 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu' TO WS-NAME.  
   INSPECT FUNCTION REVERSE(WS-NAME) TALLYING WS-COUNT FOR LEADING SPACE.   
   SUBTRACT WS-COUNT FROM FUNCTION LENGTH(WS-NAME) GIVING WS-ACTUAL-LENGTH.  
   DISPLAY 'Length is ' WS-ACTUAL-LENGTH.  
 STOP RUN.  

Result after executing the code is given below:

Length is  85

Try running this code on JDOODLE 👉 here.

There you go! This approach works fine even for strings with embedded blanks. 

Hope this helps!

And, with this post I've got an intent to start a new series of posts that will be labelled as Interview Questions. You can access all the posts under this label from Labels section in the sidebar.

 

thx 👍

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.

How to prevent SQLCODE -803 in IBM DB2?

Hi ! 👋

On this April Fool's day 😀, we will be seeing how to prevent a specific SQL error code (-803) which is issued, when something isn't right when a user insert/update values in a Db2 table.

What's SQLCODE -803 🤔?

When a user try to insert/update a value in column which is constrained to have unique values and if the inserted/updated value is already present in the table, SQLCODE -803 will be issued. Consequently, the insert/update statement will not be processed. 

More info about the error code can be found 👉 here.

As a brief aside...

To better understand the stuff discussed in the remainder of this post, we need access to IBM DB2 database. At the time of writing this post, I just had Mainframe access to IBM's Master the Mainframe 2020 learning system and it didn't allow participants to try Db2 as the focus was put on Zowe, VS Code and plenty of other new stuff.

I started searching 🔍 on Google with phrases like 'how to practice db2 at home', 'practice db2 online' and that's how I found the Db2 Community edition Docker image. Docker wasn't new to me as I had already had prior experience by working on the last two challenges in the Part 3 of IBM's Master the Mainframe 2020 contest. 

Docker 🐬 is a technology which allows solutions that run in a standardized environment to be picked and made to run anywhere else. This means that every little file, library, piece of code, even full software pacakges and operating systems can be packaged up into a Docker Container 📦 and made available for distribution.

- Quoted text from Challenge instructions of ANSB1 - Master the Mainframe 2020.

 

IBM has made available, a Docker image (read image as snapshot) of Db2 11.5.4 Community Edition, so all that we have to do is to install this image on your local machine 💻. The procedure to install can be found 👉 here. 

Note: The procedure assumes that you've already installed Docker Desktop on your system and you most probably do if you've finished the last 2 challenges in Part 3 of MTM2020. Else, you can follow the guidelines given in the procedure to install Docker Desktop first. Docker Desktop is nothing but a software which is used to run the Docker images as applications. 

Alternatively, IBM allows us to download and install IBM Db2 Community edition, a free to download, use and redistribute edition of the IBM Db2 data server, which has both XML database and relational database management system features. But, I recommend the Docker image method as I found it to be easy and simple. You can install the docker image and start working with sample tables just over a coffee 🍵 break. 

Alright! Let's get down to business. 

Kickstarting the Db2 Docker Image and creating a sample database...

After completing the procedure to install the IBM Db2 Community Edition Docker image on your machine, you should be able to see the container up and running in the Docker Desktop 👏. 

This image shows the container named db2server in running state. 

Where do we issue SQL commands in Db2 Container 🤔?

Open the command prompt and type the following command to access the running Db2 instance within your Docker container:

docker exec -ti db2server bash -c "su – db2inst1"

If the command is successful, you'll see the last login details along with Db2 instance db2inst1 connected to the container named db2server with a $ symbol at the end.

Getting into the container is successful. We can start executing DB2 queries. 

We're all set to issue Db2 commands. Let's create the Db2 SAMPLE database, which will have a set of tables pre-loaded with data. We can then use these tables and their data in our queries. The procedure to create the sample database can be found 👉 here.

Commands used to create DB2 SAMPLE database.

From the list of tables displayed after issuing db2 list tables command, let's use the EMPLOYEE table to reproduce SQLCODE -803. 

 

Running SQL statements in IBM Db2 Docker container is more or less similar to running statements in SPUFI in z/OS.  

The EMPLOYEE table has got 42 records. 

All the rows of EMPLOYEE table are displayed after the execution of SELECT SQL query.

In order to reproduce -803 SQLCODE, let's try to re-insert the last record to the EMPLOYEE table as I knew that the first column of the table i.e., EMPNO can only have unique values. 

Make note of the rectangle boxes. The first box shows the last row from the previously executed SELECT query. The second box shows the INSERT SQL query with values same as that of the last row. The third box shows the SQL error code, SQL0803N and the error description.

The error code after executing the INSERT SQL query is SQL0803N, where 0803N is in Zoned decimal format. The last character, N in '0803N', stores the negative sign as well as the value '3'.

Preventing SQLCODE -803...

Method 1: We can write an  SELECT SQL query in COBOL Db2 program before INSERTing a row. If the row is already present, SQLCODE will be 0. Else, SQLCODE will be +100 (no row(s) found). Evaluate the SQLCODE after the execution of the SELECT query and INSERT accordingly. 

Method 2: We can make use of the MERGE INTO statement. The INSERT and UPDATE statements can be incorporated into a MERGE statement by taking an input data source, comparing it to the contents of the existing table and performing one of the actions (INSERT or UPDATE) if the record exists or it does not. 

For example, let's try to update an existing row in the EMPLOYEE table and insert a new row to the table - all these in one statement.

The full statement is as follows. I've used different colors to describe the purpose of each part of the statement.  

MERGE INTO EMPLOYEE AS EM 

USING (VALUES (200340,'ROY','R','ALONZO','E21',5698,'07/05/1997','FIELDREP',16,'M','05/17/1956',31840,500,1907),(200341,'SRINIVASAN','','JV','D11',2882,'03/05/2021','DESIGNER',16,'M','02/03/1992',31840,500,1907)) 

AS ET (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) 

ON (EM.EMPNO = ET.EMPNO) 

WHEN MATCHED THEN UPDATE 

SET EM.BONUS = 1000 

WHEN NOT MATCHED THEN INSERT (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) VALUES (ET.EMPNO,ET.FIRSTNME,ET.MIDINIT,ET.LASTNAME,ET.WORKDEPT,ET.PHONENO,ET.HIREDATE,ET.JOB,ET.EDLEVEL,ET.SEX,ET.BIRTHDATE,ET.SALARY,ET.BONUS,ET.COMM)

The first statement, highlighted in blue color, is the MERGE INTO statement, which is used to identify the name of the table (in our case, it's EMPLOYEE) to which data will be inserted or updated. The AS EM code is used to specify the alias for the EMPLOYEE table that can be referenced later in the code.  

The second part of the statement, highlighted in green color, specifies the input data source i.e., two sets of values. The first set (with EMPNO as 200340) is already existing in the table and the second set (with EMPNO as 200341) is a new row that has to be inserted into the table. When you're coding the MERGE INTO statement in a COBOL Db2 program, then the input source can be specified as an host variable array. 

The third part of the statement, highlighted in purple color, is AS ET which is used to specify the alias for the input source. 

The fourth part of the statement, highlighted in orange color, are the column names specified to associate the input data with the target data during any insert or update operations that follow. 

The fifth part of the statement, highlighted in black color, is the search criteria upon which any action will be taken. In this case, if the EMPNO value in the EMPLOYEE table is equal to the input source's EMPNO value, then the actions mentioned in the next part of the statement will be invoked. 

The sixth and last part of the statement, highlighted in brown color, indicates that when the search criteria is matched, an update will occur in the EMPLOYEE table, updating the BONUS column to 1000. The WHEN NOT MATCHED code indicates that if a record doesn't exist, then an insert action will occur and add all the necessary column values from the input source into the EMPLOYEE table. 

Let's run the full statement in Db2 container. 

The SQL command completed successfully.

Let's check the EMPLOYEE table now. 

Note the last 2 rows. The last but one row's BONUS column is updated to 1000. The last row is newly inserted. There are now 43 rows in EMPLOYEE table as opposed to the 42 rows, before executing the MERGE INTO statement. 

We've hit the bottom of this post...

In this post, we discussed the following: 

• How to install IBM Db2 Docker image on your machine?
• How to create Db2 SAMPLE database containing a set of tables with pre loaded data after installing and configuring Db2 Community Edition Docker image on your machine?
• How to issue SQL statements on Db2 Community Edition Docker image?
• How to prevent SQLCODE -803 using MERGE INTO statement?

Hope this helps! Should you have any questions/feedback please post it in the Comments section below. Thx! 

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 pass more than 100 bytes of data from JCL to COBOL?

There is a famous interview question one can expect in COBOL interviews.

How many ways you can pass data from JCL to COBOL program? 

If your answer is 3 (via SYSIN, via PARM and File Input), this blog post is for you as it's time 🕐 to update your answer. Let's begin..

I have prepared the following COBOL Program:

 =COLS> ---1----+----2----+----3----+----4----+----5----+----6----+----7--     
 ****** ***************************** Top of Data ******************************  
 000100 ID DIVISION.                                
 000200 PROGRAM-ID. CBL3.                             
 000210 AUTHOR. SRINIVASAN.                            
 000220 DATA DIVISION.                               
 000230 WORKING-STORAGE SECTION.                          
 000240 01 WS-DISPLAY           PIC X(10).               
 000241 01 WS-LENGTH            PIC S9(4) SIGN LEADING         
 000242                   SEPARATE.                
 000250 LINKAGE SECTION.                              
 000260 01 WS-PARM-GROUP.                             
 000270   05 WS-PARM-LEN         PIC S9(4) COMP.             
 000280   05 WS-PARM-DATA         PIC X(100).               
 000290 PROCEDURE DIVISION USING WS-PARM-GROUP.                  
 000300   ADD WS-PARM-LEN TO ZERO GIVING WS-LENGTH.               
 000400   MOVE WS-PARM-DATA(91:10) TO WS-DISPLAY.                
 000410   DISPLAY 'PARM LEN  :' WS-LENGTH.                   
 000420   DISPLAY 'WS-DISPLAY :' WS-DISPLAY.                   
 000500   STOP RUN.                               
 ****** **************************** Bottom of Data ****************************  

Pretty simple program. I've defined 2 data items in the LINKAGE SECTION to handle the PARM data passed from JCL to COBOL program. The first data item (WS-PARM-LEN) holds the length of the data passed from the JCL and the second data item (WS-PARM-DATA) holds the data itself. 

In the PROCEDURE DIVISION, I'm simply displaying the length of data passed and a portion of the data i.e., 10 bytes starting from 91st position. 

One important thing that you should note here in the program is the order of the data items defined in the LINKAGE SECTION. The data field is always preceeded by a two-byte length field defined in binary format. 

Let's take a look at the JCL now. 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071C  JOB 1,NOTIFY=&SYSUID                       
 000002 //***************************************************/           
 000003 //* COBOL COMPILE AND LINK EDIT                       
 000004 //COBRUN  EXEC IGYWCL                            
 000005 //COBOL.SYSIN  DD DSN=&SYSUID..PDS(CBL3),DISP=SHR              
 000006 //LKED.SYSLMOD DD DSN=&SYSUID..LOAD(CBL3),DISP=SHR             
 000007 //***************************************************/           
 000008 // IF RC = 0 THEN                              
 000009 //***************************************************/           
 000010 //RUN     EXEC PGM=CBL3,PARM='12345678901234567890123456789012345678901   
 000011 //             23456789012345678901234567890123456789012345678901234567   
 000012 //             890'                             
 000013 //STEPLIB   DD DSN=&SYSUID..LOAD,DISP=SHR                  
 000014 //SYSOUT    DD SYSOUT=*,OUTLIM=15000                    
 000015 //CEEDUMP   DD DUMMY                            
 000016 //SYSUDUMP  DD DUMMY                            
 000017 //***************************************************/           
 000018 // ELSE                                   
 000019 // ENDIF                                  
 ****** **************************** Bottom of Data ****************************  

The first step is a PROC which is for COBOL program compilation and Link Edit. The second step (RUN 🏃) will run if the return code from the first step is 0. 

In the second step, PARM parameter is used (in line #10) to pass 100 bytes of data. Note that we can type the PARM data till 71st postion in the JCL and if it is to be continued on the next line, we can start anywhere from column 4 thru 16. 

After submitting this job, we get the following output written in SYSOUT. 

 ********************************* TOP OF DATA **********************************  
 PARM LEN   :+0100                                  
 WS-DISPLAY :1234567890                               
 ******************************** BOTTOM OF DATA ********************************  

Now, let's see what happens if we try to pass PARM data with 101 bytes (I've added one additional byte in line #12). 

 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--  
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071C  JOB 1,NOTIFY=&SYSUID                       
 000002 //***************************************************/           
 000003 //* COBOL COMPILE AND LINK EDIT                       
 000004 //COBRUN  EXEC IGYWCL                            
 000005 //COBOL.SYSIN  DD DSN=&SYSUID..PDS(CBL3),DISP=SHR              
 000006 //LKED.SYSLMOD DD DSN=&SYSUID..LOAD(CBL3),DISP=SHR             
 000007 //***************************************************/           
 000008 // IF RC = 0 THEN                              
 000009 //***************************************************/           
 000010 //RUN     EXEC PGM=CBL3,PARM='12345678901234567890123456789012345678901   
 000011 //             23456789012345678901234567890123456789012345678901234567   
 000012 //             8901'                             
 000013 //STEPLIB   DD DSN=&SYSUID..LOAD,DISP=SHR                  
 000014 //SYSOUT    DD SYSOUT=*,OUTLIM=15000                    
 000015 //CEEDUMP   DD DUMMY                            
 000016 //SYSUDUMP  DD DUMMY                            
 000017 //***************************************************/           
 000018 // ELSE                                   
 000019 // ENDIF                                  
 ****** **************************** Bottom of Data ****************************  

After submitting the job, it is found that the job has failed with JCL error.

  

IEF642I EXCESSIVE PARAMETER LENGTH IN THE PARM FIELD

The maximum number of bytes that we can pass from JCL to COBOL, using PARM parameter, is 100.

How to pass more than 100 bytes of data from JCL to COBOL? 🤔

We can make use of PARMDD parameter to pass more than 100 bytes of data from JCL to COBOL. The best way to understand PARMDD is by looking at an example.

Note that PARMDD and PARM parameters are mutually exclusive.

I've just modified the previous JCL by replacing the PARM parameter with PARMDD. There are no changes made to the COBOL program and it remain as it is. Hence, RESTART=RUN is coded in the JOB statement.   

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071C  JOB 1,NOTIFY=&SYSUID,RESTART=RUN                 
 000002 //***************************************************/           
 000003 //* COBOL COMPILE AND LINK EDIT                       
 000004 //COBRUN  EXEC IGYWCL                            
 000005 //COBOL.SYSIN DD DSN=&SYSUID..PDS(CBL3),DISP=SHR              
 000006 //LKED.SYSLMOD DD DSN=&SYSUID..LOAD(CBL3),DISP=SHR             
 000007 //***************************************************/           
 000008 // IF RC = 0 THEN                              
 000009 //***************************************************/           
 000010 //RUN     EXEC PGM=CBL3,PARMDD=MYDD                     
 000011 //STEPLIB   DD DSN=&SYSUID..LOAD,DISP=SHR                  
 000012 //MYDD      DD DISP=SHR,DSN=Z01071.PARMDD.INPUT.PS             
 000013 //SYSOUT    DD SYSOUT=*,OUTLIM=15000                    
 000014 //CEEDUMP   DD DUMMY                            
 000015 //SYSUDUMP  DD DUMMY                            
 000016 //***************************************************/           
 000017 // ELSE                                   
 000018 // ENDIF                                  
 ****** **************************** Bottom of Data ****************************  

Make a note of the things that are in bold. The PARMDD parameter must be used in conjunction with a DD statement. 

Here, the PARMDD keyword specifies a DD name, MYDD, which is then coded on a DD statement (in line #12) that specifies a dataset, Z01071.PARMDD.INPUT.PS, whose record length is 130. There is one record in this dataset which is 125 bytes long. 

After submitting this JCL, we get the following output. 

 ********************************* TOP OF DATA **********************************  
 PARM LEN  :+0125                                  
 WS-DISPLAY :1234567890                               
 ******************************** BOTTOM OF DATA ********************************  

The COBOL program doesn't have any File definitions. Rather, it contain instructions to handle information retrieved from PARM. We are making use of the same instructions/defintions (coded within the LINKAGE SECTION) for PARMDD as well. 

PARMDD is different from File input way of passing data. Both involve datasets but the definitions coded within the program to handle the file differs.

We are passing 125 bytes of data with PARMDD but the WS-PARM-DATA field in the program is declared only with 100 bytes. So, the last 25 bytes will be truncated. Hence, it is imperative that we code proper definitions in the program to handle data passed by PARMDD parameter.  

We've reached the bottom of the post. So, if someone asks you how many ways you can pass data from JCL to COBOL, answer them 4

1. File Input
2. via SYSIN
3. via PARM
4. and via PARMDD

Hope this helps. Should have any questions/suggestions, please post it in the Comments section of this post. Thx.