AI in my daily Mainframe life - Episode 1: The day I deleted the wrong rows.

Hi there! Welcome to the first post of the brand new series, "AI in my daily Mainframe life" where my intent is to share AI usage stories in my daily work as a Mainframe developer. 

Here is a relatable thumbnail I generated using ChatGPT that I'll be using throughout the series. I love that coffee mug though, and would love to have it on my table. 

AI in my daily Mainframe Life

There are days at work where things go exactly as planned. And then, there are also days where you learn something the hard way. Yesterday was the latter. 

A colleague of mine asked me to get rid of some rows from a DB2 table that I was maintaining.

The request came over MS Teams, and there was a series of messages. It contained a set of rows to delete and a set of rows to retain. Both were listed next to each other.

I glanced through the message, assumed I understood it… and went ahead.

I prepared a JCL with my usual approach:

• A SELECT SQL to verify what will be deleted
• Followed by the DELETE SQL & commit statement
• The same SELECT SQL as the first step to ensure intended rows are deleted from the table.

I submitted the JCL after a round of manual scanning, and the job returned a MAXCC=00. Everything looked clean. Until today.

My colleague came back reporting several issues. I had deleted the rows that were supposed to be retained. I immediately informed him and took ownership to restore the deleted rows.

Recovery Plan

Luckily, the job I had submitted had a SELECT SQL before DELETE statement. This printed the rows in the job log.

I had Zowe Explorer on my VS Code setup along with GitHub Copilot. But the job that I had submitted the previous day had moved to $AVRS ($AVRS stands for Sysout/Syslog Accumulation Viewing & Retrieval Solution. It's a product from SEA and it helps with the archival of sysout, syslog and JES datasets). 

I couldn’t access the job log via Zowe Explorer.

Coincidentally, I've signed up for an event titled, "$AVRS: Modernizing IBM Z Sysout and Syslog Management for Today’s Hybrid Enterprise". I'm hoping that I'll be able to figure out a way to access $AVRS job logs on Zowe with Rest API's after attending the webinar. The webinar is on May 26th. If you are interested, you can register here.

Now, let's go back to the main topic.

I switched to $AVRS on the Mainframe and created an XDC. I ran into a different problem. The XDC created from $AVRS was in RECFM=F and LRECL=255. This file was not readable on Zowe.

I then ran a SORT COPY and converted the RECFM to FB.

Now, I had the dataset opened on Zowe.

Enter AI

Instead of manually writing the INSERT statements for each row, I gave the dataset as context to GitHub Copilot and wrote a prompt explaining my needs.

GitHub Copilot did the heavy lifting for me by generating bulk INSERT SQL straight out of the logs. It helped save significant effort.

But not blind trust the results. 

The results from Copilot weren’t perfect though. I had to validate against the XDC data and fix minor formatting/data issues.

For instance, Copilot prepared the following SQL:

 INSERT INTO TABLE_NAME  
 (COLUMN1,  
  COLUMN2,  
  COLUMN3)  
 VALUES  
 -- values for row 1  
 ('VALUE1',  
  'VALUE2',  
  'VALUE3'),  
 -- values for row 2  
 ('VALUE1',  
  'VALUE2',  
  'VALUE3');  
 COMMIT;  

When I executed this SQL after validating the data, I got the following error:

 DSNT408I SQLCODE = -4743 ERROR: ATTEMPT TO USE A FUNCTION  
          WHEN THE APPLICATION COMPATIBILITY SETTING IS SET   
          FOR A PREVIOUS LEVEL.  
 DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE  

After some research, I found that the error was because of the multi-row INSERT SQL that Copilot had created. Unfortunately, this Db2 capability was not supported by the current application compatibility level. 

I again used GitHub Copilot to rewrite the multi-row INSERT SQL into multiple individual INSERT statements for each row.

And yes! The recovery was successful.

Where AI actually helped

Not in decision-making or in understanding the requirements. AI helped me in speeding up the repetitive work, thereby reducing the recovery time. 

Do you have a similar story? I'd love to read it in the comments section below.

See you in the next post. 

Db2 LASTUSED column: What it is and how it helps in program cleanup

Recently, I was assigned a code review task where I had to validate the changes made by a colleague. The impacted elements were:

• A COBOL-Db2 program

• A procedure division copybook

• A couple of DECLs

Since I was already part of the testing effort, I was familiar with these changes. Once we finalized the impacted list, a colleague raised an important question that I had overlooked.

Did you add the programs that use the procedure division copybook? They need recompilation too.

That struck me. I had completely missed including those programs in the impacted list. Unfortunately, it was already too late to officially modify the list. We quickly checked and found that only one other program was using this copybook.

The next challenge was to confirm if this program was really in use. 

After searching the job library and the Proc library, we couldn’t find a job that invoked it. But I wanted to be doubly sure. That’s when I started exploring if there was a way in Db2 to verify whether the program (or rather, its plan/package) was used recently.

That’s when I came across the LASTUSED column in the Db2 catalog tables:

• SYSIBM.SYSPLAN

• SYSIBM.SYSPACKAGE

When I queried the SYSIBM.SYSPACKAGE table for a few known programs, I noticed that the LASTUSED column was storing the date when the package was last executed. I confirmed this with our Db2 DBAs—and it opened up an interesting perspective.

The LASTUSED column in Db2 is similar to WhatsApp's Last Seen 😀—showing the last time it was accessed, not the details of how it was used.

The LASTUSED column records the date when the package/plan was last executed. IBM introduced this to help with package/plan cleanup—so teams can identify unused ones before freeing storage. 

How Db2 Updates LASTUSED

The LASTUSED column is defined as a DATE data type with NOT NULL and DEFAULT parameters in Db2 catalog tables (SYSIBM.SYSPACKAGE, SYSIBM.SYSPLAN). Here’s what happens under the hood:

When the package is initially created, LASTUSED is set to:

• TIMESTAMP—Timestamp indicating when the package was created 
• BINDTIME—Timestamp indicating when the package was last bound. 
• LASTUSED—The last date that the package was used. 

When a package is created:

• LASTUSED is initially set to 0001-01-01.

• The value is updated the first time the package is allocated, i.e., when an application program that references it is executed.

A few nuances worth knowing:

• The LASTUSED value is not always updated immediately. Sometimes there can be a delay of one or more days.

• The following commands preserve the existing LASTUSED value:

        BIND REPLACE (of the same package version)
        REBIND

• If a package contains only certain static SQL statements (like COMMIT or ROLLBACK), it can be used without being allocated, which means the LASTUSED column is not updated.

What LASTUSED can (and cannot) do

✅ Useful for obsolescence decisions

• If a package hasn’t been executed for years, the LASTUSED column gives you confidence that it might be safe to retire.

• This is especially handy when cleaning up old programs and freeing up resources.

⚠️ Not a Replacement for Dependency-based Recompilation

• If a copybook changes, all programs that use it must still be recompiled—regardless of their LASTUSED date.

• LASTUSED only tells you the history of execution, not whether recompilation is required.

Key Takeaways

• Always include copybook users in your impact analysis. Recompilation is mandatory, even if the program hasn’t been used recently.

• The LASTUSED column in Db2 catalog tables (SYSPLAN and SYSPACKAGE) is a valuable tool to validate program usage history. Use it as part of program retirement/cleanup strategy.

For me, this experience was a reminder that impact analysis is not just about identifying dependencies but also about validating real-world usage. And sometimes, a small curiosity-driven search can open up a whole new way of doing things smarter.

Closing CTA (Call to Action)

Have you used LASTUSED in your Db2 projects? Share your experience in the comments below! 

This blog post was written collaboratively with ChatGPT to refine structure and clarity.

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

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. 

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!