Category Archives: Left

Can’t Believe DB2 does not have this function – Convert Times

Seen as though DB2 LUW has had a major update 10.1, and I have not got round to blogging about it thought there was no point as everyone else has done it to death by now and maybe I can at later date if I see a hole someone has missed. Over the last few day I have been asked to some interesting things with “my” data like converting times to a more “friendly” format

So I needed to convert my nicely cleansed times (19:37:45 or 04:23:32) into a time format that is more compatible with your average email reader (2:30pm or 5:20am) as opposed to  a techie that might be all right with the nice 24 hour times. I did a lot of searching around and I could not find any inbuilt functions of DB2 LUW that would do this and so I had to create my own user defined function. Please if you know of anything feel free to correct me, but searching the info center then there was nothing.

CREATE FUNCTION GLOBAL.CONVERT_TIME_12H(IN_TIME TIME)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes 24hrs time in gives back 12hrs time suffixed with AM or PM
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    DECLARE OUT_TIME VARCHAR(10);

    IF(INT(LEFT(IN_TIME,2)) <= 12) THEN
        SET OUT_TIME = LEFT(IN_TIME,5) || 'am';

        IF(INT(LEFT(IN_TIME,2)) IN (10,11)) THEN
            RETURN REPLACE(OUT_TIME,'.',':');
        ELSE
            RETURN REPLACE(SUBSTR(OUT_TIME,1),'.',':');
        END IF;

    ELSE
        IF(INT(LEFT(IN_TIME,2)) = 24) THEN
            SET OUT_TIME = '00:00am';
        ELSE
            SET OUT_TIME = LEFT((IN_TIME - 12 HOURS),5) || 'pm';
        END IF;

        RETURN REPLACE(OUT_TIME,'.',':');
    END IF;

END

As you can see it takes in standard DB2 time, and I think managed to get all the gotchas like midnight not being 12 hours less. As I said before if you know of a function to do this in DB2 that is built in I would love to know.

The problem with VARGRAPHIC data type

So many of you may have come across this issue when you have to deal with foreign chars in the data that you hold. When I say foreign I mean the chars that are above decimal position 126 in any ASCII table.

The problem with these is that they are not a single byte, they are a double byte, when you want to store these in a DB2 database it can cause some issues; unfortunately I don’t know about the other major providers offerings but hopefully I can offer some insights into to what I have found so far. IBM offer two pieces of advice on coping with this

  1. Make the VARCHAR field twice the length that you expect it too be
  2. Use a VARGRAPHIC field

We were doing a migration, which is a whole different story and decided to change the fields that would be accepting user input values from batch files and other jobs data types from VARCHAR to VARGRAPHIC, as they potentially contain the most varied characters.

Below is some test code and results to show you the interesting differences here:

CREATE TABLE DEV.DATA_TYPE_TEST(
VARGRAPHIC_FIELD VARGRAPHIC(20),
VARCHAR_FIELD VARCHAR(20)
) IN DEV_TS
INSERT INTO DEV.DATA_TYPE_TEST
VALUES ('Mr Bloggs','Mr Bloggs'),('Mr Blôggs','Mr Blôggs')

Possibly not the best example but it will do for this purpose , when you issue the following query to get the sizes of the field, you can see that it will cause some issues down the line with left, right and substr functions

SELECT VARGRAPHIC_FIELD,
LENGTH(VARGRAPHIC_FIELD) LEN_VARCHAR_FIELD,
VARCHAR_FIELD,
LENGTH(VARCHAR_FIELD) LEN_VARCHAR_FIELD
FROM DEV.DATA_TYPE_TEST

RESULTS:

VARGRAPHIC_FIELD     LEN_VARCHAR_FIELD     VARCHAR_FIELD     LEN_VARCHAR_FIELD
-------------------  --------------------  ----------------  --------------------
Mr Bloggs            9                     Mr Bloggs         9
Mr Blôggs            9                     Mr Blôggs         10

So this leads to some issues if you want too only get part of the field:

SELECT VARGRAPHIC_FIELD,
LEFT(VARGRAPHIC_FIELD, 6) LEFT_6_VARGRAPHIC,
VARCHAR_FIELD,
LEFT(VARCHAR_FIELD, 6) LEFT_6_VARCHAR
FROM DEV.DATA_TYPE_TEST

RESULTS:

VARGRAPHIC_FIELD     LEFT_6_VARGRAPHIC     VARCHAR_FIELD     LEFT_6_VARCHAR
-------------------  --------------------  ----------------  -----------------
Mr Bloggs            Mr Blo                Mr Bloggs         Mr Blo
Mr Blôggs            Mr Blô                Mr Blôggs         Mr Bl

As you can see the function works fine on the VARGRAPHIC field, but not on the VARCHAR field. The function seems to be getting the first six characters by position, but the foreign characters in the VARCHAR field are stored as a double byte spread across two positions in the VARCHAR field, the whole char is not returned and so is not displayed . The VARGRAPHIC field handles this better and for each position the two byte characters are held in one position and the whole char is returned.

Other things to note that you can’t EXPORT from a VARCHAR field and LOAD into a VARGRAPIC field using an IXF file. DB2 will load all the fields that are of the same or compatible types, and null all the VARCHAR to VARGRAPIC conversion fields.

When you are loading a batch file using the LOAD command in DB2 if you try to load a data point into a varchar field, and the data point is twenty characters long and the field is twenty characters then you will also fit issues. As we have already seen the in a VARCHAR field the foreign characters will count for 2 characters, therefore if you load a string into that field that looks twenty characters long and it has a foreign character in it is actually twenty-one characters. This will obviously pop the field and not necessarily issue any warnings, the LOAD statement then seems to truncate it too twenty so you will be then missing a character from the data point.