Category Archives: Triton

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.

Mentioned in dispatches from IBM

A while back me and one of the directors at Holiday Extras where I work did a telephone interview with IBM arranged through Triton about the migration we did a while back from DB2 on HP-UX to DB2 on VM’s. It was an interesting experience and we did not have too many revisions and it is now published you can read it here.

Thats about the long and the short of this post, all that is left is to wish you a Merry Christmas and a Happy New Year and hope you dont get too many OOH calls.

IDUG – EMEA – 17th – Final day

First of all can I apologise for the spelling and poor english in some of my posts from IDUG EMEA, I have no excuse other than they were generally wrote late at night and with a few beers inside me. With that out the way lets get on with what I did today. Surprise of the day was seeing a lady in the restaurant having cucumber, chocolate cake and scrambled egg at the same time, but hey if she enjoyed it fair enough.

The talks that I went to today:

IO, IO its off to disk we go – Scott Hayes

There was bit of repetition of the index talk that Scott gave yesterday as the two are really closely related, and seeing him dancing on a video this morning was quite entertaining too (I wonder if that was his wife, or does she know?). Between all the talks on performance monitoring and index and IO tuning I have been to at IDUG EMEA I and my junior are going to have loads to do for several weeks. From this I learnt:

  1. REORGCHK – Does a runstats every time it is called. I am going to have to give this one a try next time I run it and check the col in the TABLES table. ITs not that I dont believe you Scott, its just it works so quickly to do that.
  2. SSD disks are better for random IO or even though you might not be able to afford to buy enough SSD to fit a whole database on it why not just the some of the database that is used the most often!

Database I/O in the brave new world – Aamer Sachedina

This was the second IO talk I went to in the day but it was completely different to Scotts, Aamer looked at it from a more hardware point of view as opposed to the database point of view. It was interesting as the hardware side was always something that I have wanted to know more about and this gave me a good foundation, and some questions to ask my storage manager when I get back. I learnt that:

  1. Thin provisioning does not give you space at all and it is more like sudo space allocated, which can lead to a whole heap of trouble. Will be asking some questions when I get home
  2. If you are using thin provisioning then there are some special db2 registry variables that you need to set (db2set)
  3. Soon we will be getting Fiber channel over copper at the low levels of the SAN stack!

Understanding and tuning page cleaning – Kelly Schlamb

Another talk on improving the IO on my DB2 databases, I am going to be investigating these things as a matter of urgency once I get back to work, if not before as I am itching to improve and learn.  This talk was mainly to do with the differences between settings that you need between having DB2_USE_ALTERNATE_PAGE_CLEANING ON or OFF.

After the conference finished I went for some cheeky sight seeing with Colin a DB2er that I met while over here and Iqbal from Triton consulting and one of the DB2Geeks. We got the Prague Metro into the center of town and got to see some of the sights. We had no idea where we were going, but I think by pure accident we saw most of the sights, or at least things that a lot of other tourists took photos of; so they must be sights right?

Second surprise of the day was meeting @db2fred in the local restaurant to the hotel, that was not the surprise, the fact that he knew who I was before I had even opened my mouth absolutely threw me. Good to put a face to twitter name. So have a nice journey home tomorrow morning Fred.

Tomorrow is the last full day I have in Prague before I fly home early doors on Saturday, because of the money that coming to IDUG as the student of Iqbal (under the mentor scheme) saved me it enabled to sign up for Scott Hayes – Rocket Science: DB2 LUW Performance analysis and Tuning Workshop, which I am hoping will give me even more areas to work on the database and teach me even more about the correct set up. Again I can’t say Thank you enough for doing this for me enabling me to be able to take part in IDUG, Triton Consulting and Iqbal Goralwalla.