Tag Archives: Db2

First post on the new home of my blog

This is the first post on my old blog hosted in the new location. I have a new project in the pieline, this will hopefully be a new place for more of my DB2 projects and musing, and this blog can be reverted to its orignal purpose of being more of a personal blog. Thats not to mean that I wont be still putting some DB2 stuff on there but less so.

A bit of history of my blog is that I started it as a bit of an outlet for rant and musings to let off some steam, but then as my DB2 DBA career developed then the blog got more and more DB2 stuff has gone on there, and thats why you guys visit. But to keep it consistant and so I can get this one back to its original then I am going to start a dedicated DB2 blog.

So keep your eyes on Twitter and on the blog for announcements in the next few days.

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 – 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?