Category Archives: Varchar

DB2 Errors Solved: CLI0109E String data right truncation: SQLCODE=-1822, SQLSTATE=560BD

So have you ever seen this error message, and it might be an intermittent fault which makes it an even better problem to try and solve:

Unexpected error code “22001″ received from data source “<DATABASE>”. Associated text and tokens are “func=”open” msg=” CLI0109E  String data right truncatio”.. SQLCODE=-1822, SQLSTATE=560BD, DRIVER=4.0.100

We had this issue on some production code that did not raise its head till a few months after it had been working fine. The issue seemed to be that a piece of SQL that was generating the error was joining two federated tables on columns of different data types and sizes.  The join in question was a VARCHAR(5) to a VARGRAPHIC(50) over federation, these two data types can be joined together and the size does not matter in general until the VARGRAPHIC column went over 5 characters long,  causing the intermittent  error!

The solution apart from the obvious one we were facing with our data was to CAST the VARGRAPHIC into a VARCHAR(5) the same as the field that it was joining too. This is working now and has fixed the issue and is one to think about for the future.

Another short interesting one for a lunchtime post.

IDUG – EMEA – 16th – Day Three

Today was another long day, but was ended by an excellent dinner put on by IBM to thank its customers, with ostrich leg and proper sushi so now we know where all out licensing fee goes!! The talks that I attended did not teach me as much as I had hoped, but I did learn something in each of them though so not a total waste of time.

A DBA’s guide to using TSA – Fredric Engelen

This covered the basics of HADR and then went on to cover how you set up the TSA to take over the HADR, and did not cover the TSM that I hoped it would that I will be implementing soon at Holiday extras. Learn’t:

  1. db2rfpen – Will let force the rollforward of the primary database.

Managing DB2 Performance in an Heterogeneous environment - Jim Wankowski

This covered the differences and similarities between DB2 LUW and DB2 z/OS. Although it was informative I feel the title was not correct for the session and should have been different. I learnt:

  1. When a Sort happens on a VARCHAR column then the column is expanded to its full length – I may ask this question to Scott Hayes when I do his Rocket Science Seminar on Friday

Deep Dive into DB2 LUW offline table and index reorg – Saeid Mohseni

This session was very good, if you are a frequent reader of my blog then you will know that I am trying to get a straight answer to my questions on Reorganisation and Runstats in DB2 and so I got confirmed and learnt:

  1. DB2 reorgs need the current runstats on the table to be correct to give the correct results for the reorganisation identifying stored procedure.
  2. You can parallel run a reorg on a partitioned table index as long as the first, and subsequent runnings do not allow reads.

Data Warehousing – SIG

This was a little disappointing as it did not have an agenda so was unstructured, and I would have liked to have had a little more information on how it was going to be run. It was informative and if any one has heard of “Data Vaulting” then there is a lady from the Netherlands that would really like to know.

Back to the fifties . . . . . 50 fabulous ways for forecasting failures, flaws and finding flubber – Alexander Kopac

This was an excellent talk and there is a lot to try out when I get back home and enough work to keep us going for week probably. The presenter dressed up as a wizard and the bits of SQL he has given in the slides will hopefully make the DB2 team at HX wizards.One main thing to remember is:

  1. KISS – Keep It Simple Stupid

Useful but widely unknown DB2 Functions – Michael Tiefenbacher

Second talk from this guy and if I did not already know, used or have blogged about all the things that he presented this would have been an extreamly useful and I really should have read the Agenda better before getting in there.
And to the final talk of the day:

DB2 LUW Index design, best practice and case studies – Scott Hayes

This was a very good talk and used in conjunction with Alexander’s information I think will build a framework for reviewing indexes and designs at HX. I learnt that:
  1. I need to read up on CLUSTERED indexes
  2. Single column indexes are not good, even though it is the recommended by IBM
  3. You need a good problem statement to come up with a good solution – Can be applied to everything in life.
Tomorrow is the last day of the conference and so it finishes pretty early and so I might get some sight seeing done in the afternoon, but before that I plan on attending:

Thursday, November 17, 2011

08:30 AM – 09:30 AM
Session 15
09:45 AM – 10:45 AM
Session 16
11:15 AM – 12:15 PM
Session 17
12:30 PM – 01:30 PM
Thursday DB2 Panel
So have a good night and see you all in the morning.

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.