2019-10 DECFLOAT examples: Fun with Numbers

This time I have a nice little real world story for you. Pull up your good old copy of the Db2 9 SQL Guide and I will begin…

What is a number?

Numbers used to be pretty straightforward and easy to spot.

If it was a numeric between -32768 and +32767 it was a SMALLINT or Small Integer, or even a half word for the greying people reading this.

If it was a numeric between -2147483648 and +2147483647 it was an INTEGER or full word. If it was a numeric between 1 – 1031 and 1031 – 1 it was a DECIMAL or packed, or comp-3

Along came BIGINT

After a few years we even got BIGINT (Between -9223372036854775808 and +9223372036854775807), which is much better than DECIMAL if you ask me and is also a double word. In all of these cases, you knew with 100% certainty that you were dealing with a number – Not a column name or anything else!

Then the brown stuff hit the fan… DECFLOAT.

DECFLOAT Arrives

In Db2 9 they introduced the horror that would become DECFLOAT to the Db2 z/OS world. It arrived with little fanfare and a massive amount of ifs and buts, but someone somewhere wanted it…

The problem?

The horrible problem that DECFLOAT dragged in to the party, was the fact that it could contain characters like :


  • SNaN or
  • NaN or
  • INF or
  • Infinity.

Yep, these are all valid DECFLOAT number definitions.

The law of numeric comparisons

The law of comparisons now looks like this:

-NaN < -SNaN < -INF < -0 < 0 < INF < SNaN < NaN

Clear on that?

  • NaN stands for „Not a Number“ with the „quiet“ attribute.
  • SNaN stands for “Signaling Not a Number” which “signals” when it is used (Imagine a Facebook wave here!) and
  • INF is simply infinity.

What problems does this cause?

Well, let’s imagine you have 100’s of tables in a huge Db2 data warehouse, all designed in 1983 with a column called NAN that stands for “National Advertising Notice” – NAN is a good size reduction, still clear about its contents and everything is fine right up until Db2 9 comes along!

Upper and Lower trouble

Now you might think, it is not that bad, IBM are using SNaN and NaN right? Column and Table names have always been upper and lower case sensitive so NAN is not the same as NaN right? Wrong!

The use of inf INFINITY SNAN NAN nAn snAN are all “recognized” and accepted as valid DECFLOAT numbers.

What can you do?

Delimit the column name with double apostrophes is the only correct way. “NAN” for example. Now you go and change 4000 COBOL programs etc.

If you know what you are doing…

Naturally if you are coding ok, you can happily “code” around the problem. Here is some example SQL to show you where you can get badly caught, and to introduce those of you who don’t know about DECFLOAT the joys you can have:

 CREATE TABLE BOXWELL.TESTCASE (NAN  CHAR(10) NOT NULL         
                               ,IBAN CHAR(32) NOT NULL)        
 ;                                                             
 INSERT INTO BOXWELL.TESTCASE VALUES ('TESTNAN' , 'TESTIBAN')
 ;
 COMMIT
 ;      

Let’s do some SELECTs

First up, a normal select against the test table:

 SELECT  NAN                                                 
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                       

Correct! Hoorah!

 ---------+---------+---------+---------+---------+---------+
 SELECT "NAN"                                                
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                       

Also correct! Delimiters work as planned!

Now the really evil one:

 SELECT COUNT(*)                                                         
 FROM BOXWELL.TESTCASE                                                   
 WHERE IBAN IN (SELECT NAN                                               
                FROM SYSIBM.SYSDUMMY1)                                   
 ;                                                                       
 ---------+---------+---------+---------+---------+---------+---------+-
 DSNT404I SQLCODE = 12, WARNING:  THE UNQUALIFIED COLUMN NAME NAN WAS     
          INTERPRETED AS A CORRELATED REFERENCE                           
 DSNT418I SQLSTATE   = 01545 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR             
 DSNT416I SQLERRD    = 0 0  1  1143510784  0  0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000001'  X'44289700' 
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION             
                                                                          
 ---------+---------+---------+---------+---------+---------+---------+-
           0                                                              
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                                   

You see what has happened here?

The SQL has “incorrectly” used NAN but gets only a +12 Warning from the parser. It then happily runs on and gives a result. Is this what should happen? I don’t think so!

It gets worse

Now try these queries:

 SELECT NAN                                                  
 FROM SYSIBM.SYSDUMMY1                                               ;                                                           
 ---------+---------+---------+---------+---------+---------+
                                                             
 ---------+---------+---------+---------+---------+---------+
 +NAN                                                        
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                       

Imagine having typos in your SELECT lines…

 SELECT sNAN                                          
 FROM BOXWELL.TESTCASE                                
 ;                                                    
 ---------+---------+---------+---------+---------+---
                                                      
 ---------+---------+---------+---------+---------+---
 +SNAN                                                
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                

Cool huh?

What can you do?

Monitor SQLCODE +12 – These should *never* really happen in production! How? Well you can use our WorkLoadExpert to discover these problems in two distinct ways.


  1. Capturing the SQL workload “sees” the SQLCODEs when processed

  2. If you have the SQLCODE Use Case then you get all SQLCODEs that are output by Db2


Doing it right

The recommendation is actually to CAST any of these literal values like CAST(‘snan’ as DECFLOAT) which removes *any* chance of getting it “confused” with a column name or anything else!

 SELECT CAST('NAN' AS DECFLOAT)            
 FROM SYSIBM.SYSDUMMY1                     
 ;                                         
 ---------+---------+---------+---------+--
                                           
 ---------+---------+---------+---------+--
 +NAN                                      
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1     

Where else?

Now that these beasts are also allowed in indexes you must really check all of their usage.

Have fun!

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect


Getagged mit: