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.

DB2 RAND()

Not done this for a while with moving house and looks like it will still be difficult with BT messing up the telephone lines in the building, but O2 are providing no end of support.  So thought I would do a short post in my lunch hour at work (actually taking a “break” for once) and do a short post.

One of the MySQL DBA colleagues asked me the other day if I could do a random update on a table, apart from the fact I could not understand at the time why you would want to randomly update a number of rows and not have an idea what you have done till you interrogate the data again, that’s another article for the what if I am allowed to publish it (doesn’t that sound intriguing!). The main IBM page in the DB2 Information Centre is here on the RAND() OLAP function.

So what’s the best way to update say 1000 rows in a 10000 row table randomly in DB2, I am not going to cover the way to do it on MySQL but heres how you could do it:

UPDATE <SCHEMA>.<TABLE> ST
SET <COLUMN> = SOMEVALUE
WHERE ST.<IDENTIFIER COLUM> = (SELECT <IDENTIFIER COLUM>
                              FROM <SCHEMA>.<TABLE>
                              ORDER BY RAND()
                              FETCH FIRST 1000 ROWS ONLY)

This code will randomly update 1000 rows, and will choose a different 1000 each time. You could but RAND() to other uses to create a random number but you will need to remember that it only generates a number between 1 and 0

So that’s it, one of my shorter ones.

Just finished reading: Customer Experience Analytics

This is another excellent free ebook from IBM that renders well on a kindle. This book can be down loaded from the IBM Information Management Book store  or the direct link here.

Overall like the Understanding Big Data book I reviewed last time I think it is a good introduction to the subject matter giving you a quick way to get up to speed with some of the concepts involved and the evolution going on around the social sphere and customer experience. The book is again split up into sections, this time three: Part One: The CEA Opportunity, Part Two: The Customer Experience Analytics [sic] Solution and Part Three: How to Package a Customer Experience Analytics [sic] Program.

Part One: The CEA Opportunity covers a few case studies of how various industries use customer experience to fuel decisions that affect the business and the customer.   It then moves on to how that our societies are moving toward increasingly automated way of interacting during the sales and marketing processes makes collecting the data for CEA a lot easier and quicker to act upon. The third chapter in this part looks at the evolution of the customer decision making process, and how a single customers influence on the wider world can (should) affect how a business deals with them. This raised some interesting thoughts in that basically people that are “listened” to (facebook, twitter, text messages in a social group) should be treated differently when they have a complaint than those that “listen” and do not contribute back, pushing “stardom” down onto those that are not famous, but are popular in a social group. The final chapter in this section looks at the “bazaar” of data that exists for CEA and touches on big data concepts again.

Part Two: The Customer Experience Analytics [sic] Solution is a slightly technical, but more theoretical look at with out pushing any particular products how you would go about creating you CEA solution. It covers Master Data Management (MDM), Stream computing, Predictive Modelling and a couple of other topics, but not to a depth to make you a master of these areas but at least enough to let you in on the conversation.

Part Three: How to Package a Customer Experience Analytics [sic] Program is basically how you would put together a business case for CEA and the conclusion of the book. The business case for CEA varies from needed to stay in business (mobile phone compaines) to currently only done on an Ad-Hoc basis and needs to be built up in the company or the industry.  It would be hard to place the company that I currently work for on this scale as I am un-aware of what and if anyone else does in the sector that we are in, but I think it has legs and should be something that we should be pushing, would defiantly like to get involved in the technical side.  I also think what we do have in place is to rigid in the way it carries out its current matching and we really need to be pulling in or getting the social sphere of the customer somehow.