Cast NULL in DB2 SQL statements

Question

I have found a lot of information about DB2 and its strong type system, but no comprehensive fact sheet about casting NULL. Also, there seem to be subtle differences between versions.

I don't quite understand when I can simply put null in a SQL clause, and when I need to explicitly cast null to any other type.

Examples (which one is right?)

Inserting (or updating):

INSERT INTO X (A) VALUES (null)
INSERT INTO X (A) VALUES (cast(null as integer))

Selecting:

SELECT null FROM SYSIBM.DUAL
SELECT cast(null as integer) FROM SYSIBM.DUAL

Calling (stored procedures):

X (null);
X (cast(null as integer));

Etc...

Does anyone know a good resource explaining when casts are necessary? And in what versions of DB2 / With what configuration parameters?

Solution

• For the INSERT operations, both will work.
• For the SELECT, the cast is necessary.
• For the stored procedure, I hypothesize that both will work.

The reasoning is relatively simple:

1. With the INSERT, the target column (A) tells the system which type the NULL is.
2. With the stored procedure, the declaration of the argument tells the system which type the NULL is.
3. With the SELECT, there is nothing apart from the cast to tell the system which type the NULL is.

Comments

[Lukas Eder]

Your reasoning makes sense and is good news to me. Do you know where that is documented?

[Jonathan Leffler]

The obvious starting point is DB2 9.7 Info Centre. One piece with some relevant info about changes in NULL handling in 9.7 here. Otherwise, it should be in here: SQL Reference.

[Lukas Eder]

Thanks a lot, that's exactly what I was looking for. Especially the fact that IBM changed something with version 9.7 was what I suspected but didn't find...