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.

About these ads
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: