No More Posts Here

I have now moved my blogging to my own own domain hosted by www.1and1.co.uk. This is the last post and thank you for coming here.

I will no longer be posting here and will only be posting on my own domain. All of the old stuff is there and there will be a load of new stuff for you guys as well. So don’t forget to keep visiting.

 

GO HERE NOW FOR NEW STUFF

One week to go till no more posts!!!

A week ago I spent a fair bit of the weekend around OOH and going to a friends birthday party setting up and hosting this blog at my own domain hosted by www.1and1.co.uk. I also did a post and this is the penultimate warning that I will be moving.

I will no longer be posting here and will only be posting on my own domain. All of the old stuff is there and there will be a load of new stuff for you guys as well. So don’t forget to keep visiting.

This blog is dead. Long live this blog

I have finally taken the plunge and I am going to make use of the fact that my www.1and1.co.uk hosting now has the ability to create MySQL databases therefore I can start to host my own blog, well this one. This mean that in two weeks time I will no longer post here and I will be doing my all my new posting here.

If you head over to the “new” site you will find all the old content there hopefully in future I am going to be building on this no end and with some exciting new developments that I have been working on that are not necessarily in the normal vane of a DB2 DBA.

So a bit of a short post but as this is going to be one of the last on here then no need to linger.

A DB2 ETL Tool

This is going to be a really short post as it is more of a question. A request for enlightenment into what the rest of the DB2 community does in terms of getting their data from however you guys receive into your database (data warehouse). When you Google for “DB2 ETL Tools” then the first hit is on Developer Works which by the title (ETL solutions for IBM DB2 Universal Database) you think great there’s going to be a list on here, but instead it just points you too the EXPORT, LOAD and IMPORT commands and some very basic script examples of how to get these to work. The next few links are not that good either and googling around the subject you get to various products, non of which are designed to work with DB2 other than through a Java or worse ODBC driver, apart from IBM Data Stage which is very expensive.

I think to myself after seeing this then “How does everyone else do it?”. Having come from a SQL Server background with SSIS and DTS then initially I was shocked at how few tools there are for DB2, without using some verbose logging in text file and emailing you the content, this leads me to think “This is not the way the wide world can do it?”. I have over 400 ETL jobs where ~85% of them need to run everyday, therefore with another developer that has now left we deigned a way to load and record the loading of the jobs. He has now left and support for the tool he made is now becoming harder to do and even when it is it rarely goes smoothly.

So my question to the community is:

db2advis command an idiots guide

This article was prompted by the fact that the new Express-C version of DB2 uninstalls the much maligned Control Center and installs the IBM Data Studio that so far, after much reading of the reading materials, I struggle to get to run on a VM running 4 CPU’s of an 2GHz i7 Processor and 5Gb of RAM. So after realising that when you try and run the tuning part of of the application unless you have the appropriate licences then it only ever recommends runstats. Unless someone out there know why?

Therefore I needed a different way of running the design advisor to see what DB2 thinks it can do to optimise the query, which left me with my not so favorite environment the command line! So all you need to know about design advisor command (db2advis) with all the different options can be found here at the IBM Infocenter, what I am going to do is take you through what I run most often.

The command is:

db2advis

At the minimum you need to run

db2advis -d <Database name> -s "<SQL Statement>"

This will then output to the terminal window like this:

Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2012-06-19-17.31.31.666368
Recommending indexes...
 0 indexes in current solution
 [974.0000] timerons (without recommendations)
 [974.0000] timerons (with current solution)
 [0.00%] improvement

--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- no indexes are recommended for this workload.

--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================

--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--
-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
--
--<?xml version="1.0"?>
--<design-advisor>
--<statement>
--<statementnum>1</statementnum>
--<statementtext>
-- SELECT * FROM <SCHEMA>.<TABLE NAME>
--</statementtext>
--<objects>
--<identifier>
--<name><TABLE NAME></name>
--<schema><SCHEMA> </schema>
--</identifier>
--</objects>
--<benefit>0.000000</benefit>
--<frequency>1</frequency>
--</statement>
--</design-advisor>
-- ====ADVISOR DETAILED XML OUTPUT=============
--
0 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

On a query that did need optimising then handily the tool will give you an expected improvement and a list of actions to carry out to hopefully achieve this in terms of extra indexes and runstats.

GOTCHA Alerts:

1. The section under “Unused Existing Indexes” normally contains a whole list of drop indexes as it did not use them in the query, do not blindly do this! Please consider what it is suggesting and the obvious ramifications before carrying out these actions.

2. The indexes it suggests for creation under “List of Recommend Indexes” does not take into account for existing indexes that may have say three out of the four columns needed, so rather than create another index increasing your storage footprint, increasing insert and update times, etc then please consider extending existing indexes to the fourth column, rather than creating another index.

3. The names of the indexes suggested for creation can be changed, and I do as they come out with a name that is unintelligible to man nor beast at a later date on what columns might be in there.

4. The Schema of the index recommended to be created will be in whatever scema that is defined what running the command. The assumed default is the user that you are running as, which might not be where you want it.

So now you have run it for the first time then you want to get a little more complicated and you have a really long multi lined statement that you want to tune, well you can do that easily by passing the parameters for the command. You always have to pass the database (-d <DATABASE NAME>) parameter, you can then follow it with the -i parameter which tells it that it is taking an input file this can be called anything. This file that you are passing as an input needs to have a ; at the end of the statement. The other thing you can do is output what is normally returned to the terminal to a file using the normal Linux channels. You can limit the time DB2 considers what it is going to recommend by the -t parameter that, this is in minutes. I usually run something like this:

db2advis -d <DATABASE NAME> -i <INPUT FILENAME> -t <TIME (Mins)> > <OUTPUT FILENAME>

GOTCHAS:

1. Make sure if you want to see the improvements any changes you have carried out have made you need to make sure you take a copy of the output file, or call the file something else.

2. The advice that is given out is only for indexes, if you want other advice like MQT’s or MDC’s then you have to specify a further option of -m

As stated above if you want other advice then you need the -m option then a series of letters. Indexes is assumed but this is I, M recommends new MQT’s that could be used in optimising the query. C will look to see if a MDC or clustering index, finally P will recommend a different field to partition your table over. These can be used like -m IMCP, one or none can be used so your command would look a little like

db2advis -d <DATABASE NAME> -i <INPUT FILENAME> -t <TIME (Mins)> -m <Options M,C,P,I> > <OUTPUT FILENAME>

Some of you may be limited for disk space so the -l <MB Limit Size> option can be quite useful to keep the size down, especially if you have specified the -m M (consider advising MQT’s). Therefore an extended, and most of the options that I usually use would be:

db2advis -d <DATABASE NAME> -i <INPUT FILENAME> -t <TIME (Mins)> -m <Options M,C,P,I> -l <Size in Mb> > <OUTPUT FILENAME>

Or something like I would run

db2advis -d SAMPLE -i TROUBLE_QUERY_IN -t 5 -m ICM -l 1000 > TROUBLE_QUERY_OUT_1

I hope this has been informative and there are loads more options that can be used, full list here.

Can’t Believe DB2 does not have this function – Convert seconds to hours, minutes and seconds

For the third in the series and possibly the last I am going to look at the little doosie that was presented to me the other day where the seconds of time passed from a system are presented as an integer and needed converting to a time, when I say a time I mean a time that will add up in MS Excel where the hour part could be over 24, so not strictly a valid DB2 time.

We have a new VOIP phone system here at work and apart from all the times (call start, call end, logged on, logged off) being in the number of seconds from 01/01/1970 (Unix time) so DB2 can handle this very well adding the timestamp of 01/01/1970 with the seconds and you get the correct timestamp of the call. The other interesting thing it does is it records all call times in seconds and this needs converting to hh:mm:ss for reporting purposes. To do this I was reminded by a colleague of the MOD function that DB2 has. MOD returns the remainder as an integer, opposed to the number of times the value will dived.

So you can do something like this to get the hours, minuits and seconds as a VALUES statement where <INTEGER> is the total seconds:

VALUES TRIM(VARCHAR((<INTEGER> / 3600))) || 'h' || TRIM(VARCHAR((MOD(<INTEGER>,3600)) / 60)) || 'm' || TRIM(VARCHAR(MOD(MOD(<INTEGER>,3600 / 60), 60))) || 's'

If you substituted <INTEGER> for 3681 it returns the result:

 1       
 ------- 
 1h1m21s

Which as far as I can work out is correct. There are 3600 seconds in an hour, so to work out hours then you divide seconds by 3600, to get minuits you use MOD to get the remainder of the passed in value and then divide by sixty (the result of MOD will still be in seconds) the result of the division because it is an integer will be returned as an integer so there are some second s remaining. To work out the seconds remaining then you need to MOD the value from the working out the number of minuits to get the seconds.

So then the next step is too produce a function that returns a time:

CREATE FUNCTION GLOBAL.GET_TIME (IN IN_SECONDS INTEGER)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Phil C - 14/06/2012
    --Takes in an integer and returns a varchar representation of a time for 
    -- use in excel as opposed to anything beging a valid DB2 time type
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Create some vars
    DECLARE OUT_TIME VARCHAR(10);
    DECLARE HOUR_PART INTEGER;
    DECLARE MIN_PART INTEGER;
    DECLARE SEC_PART INTEGER;
    DECLARE HOUR_PART_V VARCHAR(5);
    DECLARE MIN_PART_V VARCHAR(3);
    DECLARE SEC_PART_V VARCHAR(2);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Set some vars
    SET HOUR_PART = (IN_SECONDS / 3600);
    SET MIN_PART = (MOD(IN_SECONDS,3600) / 60);
    SET SEC_PART = (MOD(MOD(IN_SECONDS,3600 / 60), 60));

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Fattern the values out
    IF (HOUR_PART < 10) THEN
        SET HOUR_PART_V = '0' || TRIM(VARCHAR(HOUR_PART)) || ':';
    ELSE
        SET HOUR_PART_V = TRIM(VARCHAR(HOUR_PART)) || ':';
    END IF;

    IF (MIN_PART < 10) THEN
        SET MIN_PART_V = '0' || TRIM(VARCHAR(MIN_PART)) || ':';
    ELSE
        SET MIN_PART_V = TRIM(VARCHAR(MIN_PART)) || ':';
    END IF;

    IF (SEC_PART < 10) THEN
        SET SEC_PART_V = '0' || TRIM(VARCHAR(SEC_PART));
    ELSE
        SET SEC_PART_V = TRIM(VARCHAR(SEC_PART));
    END IF;

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Create the end value
    SET OUT_TIME = (HOUR_PART_V || MIN_PART_V || SEC_PART_V);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Return the value
    RETURN OUT_TIME;

END

So this will return a time like result up to 9999:59:59 and you will be able to export a result set to Excel or the like and then it can be added up. DB2 will not accept this as a time! So when you run this:

VALUES GLOBAL.GET_TIME(3681)

Returns

 1        
 -------- 
 01:01:21

Which is the same result as at the start so I think the function is a good one. Till next time happy UDF’ing

Can’t Believe DB2 does not have this function – Friendly Dates

So our marketing gurus at where I work needed a english friendlier format (thanks @_PeUR on the description of my last post via twitter) for the text in the emails that they send out, personally I would be happy with a dd/mm/yyyy or yyyy-mm-dd but suppose it is what you are used too. Our place is a little unusual as the marketers are allowed access to the data to design their own SQL sets to create the mailing lists. I have seen all sorts of SQL to do this some good some bad, but mostly it makes the SQL harder to read for no added functionality that i sometimes have to debug. Therefore I decided to create a function so that it at least can now just be an inline function. This function takes in an ISO or compatible with the DATE data type and returns a long data ddth MMM YYYY so 13/06/2012 becomes 20th June 2012.

CREATE FUNCTION GLOBAL.CONVERT_DATE_LONG (IN_DATE DATE)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(30)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes Date in creates a long date
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Declare Vars
    DECLARE OUT_DATE VARCHAR(30);
    DECLARE DATE_PART VARCHAR(4);
    DECLARE MONTH_PART VARCHAR(20);
    DECLARE YEAR_PART  VARCHAR(4);

    IF(IN_DATE IS NOT NULL) THEN
        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set some vars
            --Get the Base Parts
            SET DATE_PART = DAY(IN_DATE, 2);
            SET MONTH_PART = MONTHNAME(IN_DATE);
            SET YEAR_PART = YEAR(IN_DATE,4);

            --Remove the 0 from the front of the date part
            IF(DATE_PART IN ('01','02','03','04','05','06','07','08','09')) THEN
                SET DATE_PART = RIGHT(DATE_PART,1);
            END IF;

            --Put the th nd or rd in the DATE_PART
            IF(DATE_PART IN ('3','23')) THEN 
                SET DATE_PART = DATE_PART || 'rd';
            ELSEIF (DATE_PART IN ('2','22')) THEN
                SET DATE_PART = DATE_PART || 'nd';
            ELSEIF (DATE_PART IN ('1','21')) THEN
                SET DATE_PART = DATE_PART || 'st';
            ELSE 
                SET DATE_PART = DATE_PART || 'th';
            END IF;

        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set and return the result    
        SET OUT_DATE = DATE_PART || ' ' || MONTH_PART || ' ' || YEAR_PART;

        RETURN OUT_DATE;
   ELSE
        RETURN NULL;
   END IF;

END

Is it that I cant believe that there is not the function or does everyone have something similar for the audience that they deal with most often expect to see their date or time formats in?

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.