Tag Archives: Added Functionality

A DB2 ETL Tool

June 24, 2012 7:14 am / Leave a Comment /

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:

Posted in: Blogging, DB2, DB2 Administration, DB2 Ecosystem, DB2 Maintenance, IBM / Tagged: , , , , , , , ,

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

June 13, 2012 8:16 pm / Leave a Comment /

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?

Posted in: Data types, Date, DAY, DB2, DB2 Administration, DB2 Built in commands, DB2 built in functions, IBM, MONTHNAME, User Defined Functions, YEAR / Tagged: , , , ,