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.