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.
Capturing the SQL workload “sees” the SQLCODEs when processed
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