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.

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.

Just finished reading: Understanding Big Data: Analytics for Enterprise Class Hadoop and Streaming Data

I have just finished reading this book, I was excited about the IBM offering and the concepts around big data at IDUG, but after reading the book I want to find a project I can try this out on. The book can be downloaded from here: Understanding Big Data: Analytics for Enterprise Class Hadoop and Streaming Data.

The book is in two parts, Part 1: Big Data from the business prospective and Part 2: Big Data from the technology prospective. The first part of the book as it suggests does not touch on the technical aspects of big data only the benefits to businesses and how we all are already part of the Big Data world. The second part of the book explains at a high level all the different parts of the Hadoop cluster and how you get data in and out and process data in there. The second part also explains the IBM offering into this marketplace in the form of IBM InfoSphere BigInsights and Streams.

The as a high level description first part introduces the concept of the three V’s of big data, Volume, Velocity and Variety, the uses of these V’s in a number of different scenarios all of which are very interesting and I can easily see how it would bring you competitive advantage (probably the point of the case studies). The second part is for the techies explaining what Hadoop is and all of the different parts that make it up with MapReduce, common components and the file system. Also explaining all the other technologies surrounding Big Data such as Hive, Flume and Jaql.

So this is just a very light overview of the book, and well worth a read. I did it on my kindle, sometimes the text varies from page to page as it gets resized but overall it was fine.

My Christmas Present is Ace

So for Christmas my wonderful fiancée for Christmas bought me a new kindle Kindle, Wi-Fi 6″  and this has enabled me to do a lot more reading of all those ebooks and pdfs that you never realy get round to during the day and the laptop is too big for bed.

I am finding it great, I don’t actually have any “kindle” books on it only pdf’s but is is still great. With the pdf’s (presentations from IDUG this year and IBM redbooks and IBM educational books) depending on the quality of the all I have had to do so far is change the contrast, the kindle works out what size to display on screen although you can change this if you want. It remembers where you have go to even if you move off to another book and you can still bookmark. As they are not kindle books then it does not push the info around different kindle accounts but otherwise I cant fault it so far.

In future expect far more reviews of books and the ideas that they put across in future postings. I am going to start with a big data book that I picked up in hard copy format at IDUG, but have only just got round to reading it recently.

And thank you again honey for such a great gift.

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.

Automated DB2 Reorganisation, Runstats and Rebinds – Version 2

A while back I did the first version of this code (can be found here). Over time I have been running this code on our production servers, it started out by working fine but sometimes it would over run and interfere with the morning batch, so a different solution was needed. In a previous article I discussed if it was better to let the included automated DB2 functionality take care of the maintenance of tables etc, or to create your own process that uses included stored procedures to identify the tables that need reorganising.

So this new version of the script will only work between certain times and only do offline reorganisations, but is still possible to just reorganise a single partition of a range partitioned table. The reason for the time restriction is to take a leaf from the included automated scripts having an offline maintenance window, and to stop the scripts that I have created before overrunning into the morning batch. The previous version of the reorganisation script attempted to be to “clever” and do an online reorg of non partitioned tables and an offline reorg of the partitions of the range partitioned tables. The problem with this is that capturing when the online reorgs have finished (as they are asynchronous), so that the table can have it statistics run so that it is not identified again by the SYSPROC.REORGCHK_TB_STATS stored procedure. Equally another issue is that you would have to reorganise the index’s on the tables that you have on-line reorganised as they would not have been done, where as an offline reorganisation also does the indexes at the same time.

So I made the decision to do all the reorganisations offline, followed by a runstats and a rebind. The main controlling stored procedure looks like:

CREATE PROCEDURE DB_MAIN.RUN_ALL_AUTOMATED_MAINTENANCE(IN MAINT_SCHEMA VARCHAR(255), IN REORG_FINISH_TIME TIME, IN RUNSTATS_FINISH_TIME TIME, IN DAY_TO_REMOVE INTEGER)
LANGUAGE SQL
BEGIN
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --This procedure is the wrapper for all the rest to tidy it up a little bit.
 --It will only run the reorgs tille the time specified, then will just finish the one
 --that it is on once the time has expired.
 --Similar thing for the runstats so that it does not impact on the running of the
 --morning loads.
 --Rebind the procedures so that they get new packages based on the updated statistics
 --from the reorg and runstats.
 --All Reorg done off line as this is what DB2 does.
 --MAINT_SCHEMA = The schema you wish to be looked at
 --REORG_FINISH_TIME = The time you wish the reorgs to run until
 --RUNSTATS_FINISH_TIME = The time you wish runstats to run till
 --DAY_TO_REMOVE = The number of day back you wish staging tables to be emptied from
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Reorg the tables
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_REORG(MAINT_SCHEMA, REORG_FINISH_TIME, DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Runstat the tables that have been reorged
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_RUNSTATS(MAINT_SCHEMA, RUNSTATS_FINISH_TIME,DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Rebind the stored procedures to take advantage of the potentially new plans
 CALL DB_MAIN.RUN_AUTOMATED_REBIND_PROCEDURES(MAINT_SCHEMA);

END

This is now a three stage operation, the first two stages have time limits and so they will carry out new operations until this time limit is breached. What you have to realise here is that if the end time is 18:00:00 then it will start work right up until 17:59:59, this means if it picks up a particularly large reorganisation task at this last second then it will run till it has finished.

Some of the code especially the runstats stuff is quite a lot like the previous version just with a change for the time. As I cant upload a single .zip file as apparently it will be a security risk, and apparently a .sql file is also a risk please find a number of .doc files a the bottom of the article. Please just change the file extension and then you will be able to access them. I would very interested in having feedback from anyone who uses this code to see how you get on with it.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as reorganising tables can potentially dangerous.

FILES WITH CODE IN:

OverallRunnerStoredProcedure

ReorganiseTablesStoredProcedures

ReorganiseTableTables

ReorganiseTableViews

RunstatsTableTables

RunstatsTableViews

RunstatsTableStoredProcedures

RebindSchemaStoredProcedure

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.

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 1899:I/O, I/O, it’s off to Disk I go – I/O Optimization, Elimination, & SSD (Aquarius)
09:45 AM – 10:45 AM	Session 16 2194:Database I/O in the Brave New World (Aquarius)
11:15 AM – 12:15 PM	Session 17 1892:Understanding and Tuning Page Cleaning in DB2 (Aquarius)
12:30 PM – 01:30 PM	Thursday DB2 Panel

So have a good night and see you all in the morning.