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!

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.

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!