Db2 – get leading zeros when converting from decimal to char in a db2 query

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN 
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement. If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform. I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1. I don't know about the other platforms (AS/400, etc.).

Per documentation:

It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1.

But geometric types are not arrays.
While the value as a whole can be NULL, parts of a point (or any geometric type) cannot.

All geometric types can be input as string literals and cast to the respective type. None of them accept NULL as part of the input. This results in an error message:

SELECT '(NULL,1)'::point;

ERROR: invalid input syntax for type point: "(NULL,1)"
LINE 1: SELECT '(NULL,1)'::point

On the other hand, if you try passing one or more NULL values to a constructor function, you get NULL for the whole value - which is a bit inconsistent, but still makes sense: if one component is unknown, the whole value is unknown. All of these tests return TRUE:

SELECT point(NULL, NULL) IS NULL
      ,point(1, NULL) IS NULL
      ,point(NULL, 1) IS NULL
      ,point(NULL, 1) IS NULL
      ,circle(point(1, 1), NULL) IS NULL;

This leaves the case where an UPDATE silently ignores an assignment of NULL to pt[0] or pt[1]. This should really do one of two things:

• Set the point to NULL - like SELECT point(NULL,1) does.
• Raise an exception - like SELECT '(NULL,1)'::point does.

I would say this is a bug and should be reported.