Implicit Casting in DB2 for z/OS

A short story about SQLCODE -420 and implicit data conversions

Emil Kotrc

Do you know those minor changes in your code that cannot break anything, but, well, actually they do? I had such experience recently. I did a very minor update to a WHERE clause of a SELECT statement in an embedded SQL, but the execution failed with SQLCODE -420 and with an error message saying "THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE DECFLOAT FUNCTION". I am pretty sure I haven't used DECFLOAT function anywhere in the program, so what really happened? Well, the problem was actually with the program itself, but it helped me to learn more about implicit data conversions in DB2. That's what I would like to explain in more detail in this blog.

Let's assume the following over-simplified program in C:

#include <stdio.h>                                     
EXEC SQL INCLUDE SQLCA;                                
                                                      
int check_sqlcode()                                     
{                                                      
 if (SQLCODE < 0) {                                   
   EXEC SQL BEGIN DECLARE SECTION;                    
   char msg[32673];                                   
   char msgid[11];                                    
   long sqlcode;                                      
   EXEC SQL END DECLARE SECTION;                      
   EXEC SQL GET DIAGNOSTICS CONDITION 1               
     :msg   = MESSAGE_TEXT,                            
     :msgid = DB2_MESSAGE_ID,                         
     :sqlcode = DB2_RETURNED_SQLCODE;                 
                                                      
   printf("SQLCODE %d: %s %s\n", sqlcode, msgid, msg);
   return 8;                                           
 } else {                                             
   return 0;                                          
 }                                                    
}                                                      
                                                      
int main(int argc, char **argv)                        
{                                                      
 int rc = 0;                                           
                                                      
 EXEC SQL BEGIN DECLARE SECTION;                      
 char hlastname[16];                                  
 char hworkdept[4];                                   
 short int hedlevel;                                  
 EXEC SQL END DECLARE SECTION;                        
                                                      
 hedlevel = 17;                                       
                                                       
 EXEC SQL                                             
 SELECT LASTNAME, WORKDEPT INTO :hlastname, :hworkdept
   FROM EMP                                  
   WHERE EDLEVEL = :hedlevel
   FETCH FIRST 1 ROW ONLY;                            
                                                     
 rc = check_sqlcode();                               
 if (rc == 0)                                        
   printf("last name = %s, work dept = %s\n",        
     hlastname, hworkdept);                          
                                                     
 return rc;                                          
}

Main focus here is on the main() function. The check_sqlcode() is just one of many ways how to print the SQL code and any error message. GET DIAGNOSTICS is more costly than calling DSNTIAR, but on the other hand is more compatible with SQL standards and the performance does not matter in this case.

Let's go back to the main() function. At the beginning of the main() function I define host variables, the application then sets the filter criteria, and executes a simple SELECT statement. It selects the last name and the working department of a row from the employee table that has the education level 17. In this case it does not matter what row we get, just any row that matches the criteria - therefore FETCH FIRST 1 ROW without any ordering. So far so good - everything works fine.

Now, let's say I wanted to add a WHERE clause and filter on SEX column in addition. Pretty easy right? So, I just added a new host variable and a new WHERE clause:

int main(int argc, char **argv)                        
{                                                      
 int rc = 0;                                          
                                                      
 EXEC SQL BEGIN DECLARE SECTION;                      
 char hlastname[16];                                  
 char hworkdept[4];                                   
 short int hedlevel;                                  
 short int hsex;
 EXEC SQL END DECLARE SECTION;                        
                                                      
 hedlevel = 17;                                       
 hsex = 'M';                                          
                                                       
 EXEC SQL                                             
 SELECT LASTNAME, WORKDEPT INTO :hlastname, :hworkdept
   FROM EMP                                  
   WHERE EDLEVEL = :hedlevel AND                     
         SEX = :hsex                                  
   FETCH FIRST 1 ROW ONLY;                           
                                                     
 rc = check_sqlcode();                               
 if (rc == 0)                                        
   printf("last name = %s, work dept = %s\n",        
     hlastname, hworkdept);                          
                                                     
 return rc;                                          
}

The execution of this version now fails with the SQLCODE -420 and the same SQL error message as above. Have you noticed the mistake I've made? Yes, I've just copy-pasted the hsex host variable from hedlevel, but I haven't changed the type. So, the host variable hsex has a bad data type - small integer, it should have been a character instead (refer to the table DDL). It is pretty obvious. However, why the error message mentioned the DECFLOAT function?

Well, then you need to understand the data casting in DB2. Casting means changing the data type from one type to a different one. For example, character to timestamp, character to decimal, and similar. DB2 supports two kinds of casting - explicit and implicit:

  • Explicit casting means, that the application developer explicitly changes the type. There is a CAST specification that allows explicit casting and there are also built-in functions that allow the same (like CHAR, TIMESTAMP). For SQL compatibility reasons CAST expression is recommended.
  • Implicit casting means that DB2 changes the type silently without any explicit specification. There are several rules when this can happen and this feature has been greatly enhanced in DB2 10. Since then, DB2 can even implicitly cast between character and numeric types in both directions. On the other hand, some issues with the casting have occurred in DB2 10, so there was a new ZPARM BIF_COMPATIBILITY

In my case, we are really speaking about the implicit casting because there is no explicit cast specification. The host variable hsex was incorrectly defined as a small integer, but the column was actually a single character ('M' or 'F'). So, what DB2 did was trying to cast the character to DECFLOAT for comparison according to the following rule:

When a character string or graphic string value is compared with a numeric value, DB2 implicitly converts the string value to DECFLOAT(34) and applies numeric comparison rule between the DECFLOAT(34) value and the other numeric value.

OK, so the DECFLOAT function has been applied to the SEX column, containing only 'M' or 'F' values, which does not conform the rules for forming a decimal number as stated in the DECFLOAT function description. And that explains the error I got.

What would be the correction of my program? Yes, all I needed to do is changing the type of the hsex host variable: short int hsex; to char hsex;.

So, what is the final message of this blog? Well, I think there are two outcomes. First, be careful when doing copy-paste :-). Moreover, consider using the table declarations in the program so that the precompiler could warn you in time. Second, keep an eye on data conversions that may occur in your applications. Some of them are explicit, but keep in mind that there might occur some implicit data conversion that can have an impact on your application. In my case, I was lucky and I got an error message after the first invocation, but that does not necessary need to be your case.

Is the implicit conversion positive or negative? Well, it depends, so I'll leave the final decision up to you. However, I would recommend at least reviewing the application compatibility (APPLCOMPAT BIND option) that lists the differences between the specified compatibility level and the current level. You can see the impact on your applications if you change the compatibility level in the future.

References

3 Comments

Great article

June 27, 2016 08:01 PM by Daniel Luksetich

There are performance implications to implicit casting and it is important to manually cast when appropriate to avoid any performance concerns

Implicit Casting

June 29, 2016 06:39 AM by Walter Janißen

Unfortunately you did not mention, how devastating implicit casting can be for performance. I talked a lot to folks from the lab, but I couldn’t convince them to provide a ZPARM to prohibit it altogether. I only saw queries, where the user forgot to put quotes around the literals. Due to implicit casting these queries ran, but burn a lot of CPU-cycles.

SELECT something FROM T1 WHERE char-col = integer-value is stage2 and therefore a tablespace scan occurs. 

Maybe if there are others who complain, something could be achieved, but I think, the optimizer cannot solve this problem in general.    

thank you

June 30, 2016 12:30 PM by Emil Kotrc

thanks a lot for useful comments especially when the performance issues were not covered in the blog

Recent Stories
Introduction to Db2 for z/OS System Profiles

10 questions to review and understand your active log data set configuration

DB2 for z/OS query support over FTP