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!