• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Category Archives: Date

XML Shredding – Data Model and code

October 19, 2012 10:01 pm / Leave a Comment / dangerousDBA
ShreddingERD

In a previous post I wrote about shredding xml especially when you don’t know how your XML is formatted or it is always different, which where I work the XML is not XML it is a pseudo XML that conforms to an XML column.

What am I giving you

So below you will find some “light” documentation on the system that is like the system that I have created at my workplace to shred XML and store it in a way that will be easy to query. I will also go through some of the GOTCHA’s that I have seen so far in doing this. At the bottom of the page you will find a file with all the code you need.

GOTCHA’s

  1. You may find that no size of VARCHAR column is big enough to hold your XML data in the query especially when the initial stages of the recursive query therefore you may need to to use a CLOB column. I have found this is a little quicker than a very large VARCHAR column, and you will not need to create a temporary tablespace bufferpool so large to hold the query.
  2. You may have elements that are named the same thing (e.g. email, Email, EMAIL, eMAIL) and exist down the same XPATH then I have created the SHREDDED_REQUESTS_ELEMENTS_GROUP table and this will create a store for all the same named and located elements normalised around the XPATH and element name being lowered.
  3. The query code will produce entries that contain multiple values when the element is not the lowest element. So if you say had /booking/personal with /email and /name under it then you would get three rows output with /booking/personal with a value of the the values of /email and /name concatenated together, and the two additional rows of /booking/personal/email and /booking/personal/name, therefore you will need to build into the WHERE clause to exclude any paths like /booking/personal out of the result sets.

XML Shredding – ERD

XML Shredding – The Tables

There are four tables in the solution a staging table and then three others that make up a mini-flake like schema.

SHREDDED_REQUESTS_STAGE

The staging tables for the solution where the initial shredding of the XML is inserted into. This table is where the rest of the processing starts. The thing to watch out for here is the size of the XML_ELEMENT_VALUE column; here you need to make sure that you do not make this column too small. You could get rid of the date column but I find it useful if you are processing multiple days.

SHREDDED_REQUESTS

This table is where the data resides at the end of the process and resides for the rest of the data life cycle. It is essentially the same staging table but I have made a surrogate key of the the column XML_ELEMENT_NAME and XML_XPATH. This is to reduce the storage needs of the table and makes it easier to search.

SHREDDED_REQUEST_ELEMENTS

This is the store for the XML_ELEMENT_NAME and XML_XPATH once they have been made a surrogate. It also holds the key out too the grouping table.

SHREDDED_REQUESTS_ELEMENTS_GROUPS

This is the “final” table in the solution, and you may be able to leave it off because I had to create it too enable the normalisation of all the same element names in different case (e.g. EMAIL, Email, email and eMail) issues in the XML_ELEMENT_NAME and XML_XPATH.

XML Shredding – The Process

The process happens in four stages:

1.Shed the XML into the staging table

So this stage has to happen otherwise there is very little point in this process. It uses a bit of recursive SQL as out lined in a previous post and all the element values, XPaths and element names with the appropriate identifier.

To speed this up and in the code supplied in the attachment you will see that I use an EXPORT statement to unload and an LOAD to make sure that the process happens as fast as possible, with the minimum of log contention and use. It is basic maths that you are going to be inserting a lot of rows, say your site does 100,000 requests and there are 50 elements on each request that is 5,000,000 rows!

So something like:


CREATE PROCEDURE {SCHEMA}.ADD_SHREDDED_REQUESTS_STAGE(IN IN_START DATE, IN IN_END DATE)
LANGUAGE SQL
BEGIN
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes in two dates
    --Does this using a EXPORT and LOAD so that it works quicker than insert
    --   as the actual query takes no time at all and it is only the inserting 
    --   that takes the time.
    --Uses the ADMIN_EST_INLINE_LENGTH Function from http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0054083.html 
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Declare Vars
        DECLARE EXPORTSTRING VARCHAR(3000);
        DECLARE LOADSTRING VARCHAR(3000);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Process
            --Create the EXPORT string
            SET EXPORTSTRING = 'EXPORT TO "/{some file system you can output too}/SHREDDED_REQUESTS_STAGE" OF DEL MODIFIED BY COLDEL0x09 
                                WITH pathstable (REQUEST_ID,REQUEST_DATE, name, node, xpath, value) AS (
                                  SELECT HM.REQUEST_ID,
                                         HM.REQUEST_DATE,
                                         x.name AS name, 
                                         x.node AS xmlnode,
                                         ''/'' || x.name AS xpath,
                                         x.value as value
                                  FROM {SCHEMA}.{TABLE WITH XML COLUMN} HM,
                                       XMLTABLE(''$REQUEST_XML/*''
                                        COLUMNS
                                          name varchar(300) PATH ''./local-name()'',
                                          value varchar(12000) PATH ''xs:string(.)'',
                                          node    XML PATH ''.'') AS x
                                   WHERE HR.REQUEST_DATE BETWEEN ''' || IN_START || ''' AND ''' || IN_END || '''                                        
                                        AND ADMIN_EST_INLINE_LENGTH(HM.REQUEST_XML) {} -1
                                  UNION ALL
                                  SELECT REQUEST_ID,
                                         REQUEST_DATE,
                                         y.name AS name, 
                                         y.node AS xmlnode, 
                                         xpath|| ''/'' || y.name AS xpath,
                                         y.value as value
                                  FROM pathstable,
                                       XMLTABLE(''$XMLNODE/(*,@*)'' PASSING pathstable.node AS "XMLNODE"
                                        COLUMNS
                                         name varchar(300) PATH ''local-name()'',
                                         value varchar(12000) PATH ''xs:string(.)'',
                                         node    XML PATH ''.'') AS y
                                ) SELECT REQUEST_ID, REQUEST_DATE, name, xpath, value
                                  FROM pathstable
                                  WHERE NAME {} ''serialized''
                                    AND NAME {} ''_viewstate''
                                    AND NAME {} ''__shorturl_postdata'' 
                                    AND NAME {} ''API_Reply''
                                    AND NAME {} ''apiReply'' ';

            --Execute the EXPORT string
            CALL SYSPROC.ADMIN_CMD(EXPORTSTRING);

            --Create the LOAD string
            SET LOADSTRING = 'LOAD FROM "/{some file system you can output too}/SHREDDED_REQUESTS_STAGE" OF DEL MODIFIED BY COLDEL0x09
                              METHOD P (1,2,3,4,5)
                              INSERT INTO {SCHEMA}.SHREDDED_REQUESTS_STAGE(
                                REQUEST_ID,
                                REQUEST_DATE,
                                XML_ELEMENT_NAME,
                                XML_XPATH,
                                XML_ELEMENT_VALUE
                              ) COPY YES TO "/{some file system you can output too}-copy/" INDEXING MODE AUTOSELECT';

            --Execue the LOAD string
            CALL SYSPROC.ADMIN_CMD(LOADSTRING);

END

2.Gather the group elements

If you have nicely formed XML without upper / lower case and everything in-between you might not need this stage, but here I gather the element names and paths and normalise them to lower, and insert the new ones to be used in the next stage. All the normalised values are given an ID

So something like:


CREATE PROCEDURE {SCHEMA}.ADD_SHREDDED_REQUEST_ELEMENTS_GROUPS()
LANGUAGE SQL
BEGIN
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Add based on what is not in the main elements table the new combinations
    --   from the shredded requests stage table that do not exist. 
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    INSERT INTO {SCHEMA}.SHREDDED_REQUEST_ELEMENTS_GROUPS(
        LOWER_XML_ELEMENT_NAME,
        LOWER_XML_XPATH
    )
    SELECT DISTINCT LOWER(HSRS.XML_ELEMENT_NAME) AS LOWER_XML_ELEMENT_NAME,
                    LOWER(HSRS.XML_XPATH) AS LOWER_XML_XPATH
     FROM {SCHEMA}.SHREDDED_REQUESTS_STAGE HSRS
     WHERE NOT EXISTS (SELECT *
                      FROM {SCHEMA}.SHREDDED_REQUEST_ELEMENTS_GROUPS HSRE
                      WHERE LOWER(HSRS.XML_ELEMENT_NAME) = HSRE.LOWER_XML_ELEMENT_NAME
                        AND LOWER(HSRS.XML_XPATH) = HSRE.LOWER_XML_XPATH);

    COMMIT;
END

3.Gather the elements

At this stage I gather all the new elements and paths (un-normailised) and put them into the table matched against the grouped version. This gives you a table that can be used in the next stage to create the final table with a far more index friendly integer for the elements and paths.

Again an example of the SP:


CREATE PROCEDURE {SCHEMA}.ADD_SHREDDED_REQUEST_ELEMENTS()
LANGUAGE SQL
BEGIN
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Add based on what is not in the main elements table the new combinations
    --   from the shredded requests stage table that do not exist. 
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    INSERT INTO {SCHEMA}.SHREDDED_REQUEST_ELEMENTS(
        XML_ELEMENT_NAME,
        XML_XPATH,
        SHREDDED_REQUEST_ELEMENT_GROUP_ID
    )
    SELECT DISTINCT HSRS.XML_ELEMENT_NAME,
                    HSRS.XML_XPATH,
                    HSREG.SHREDDED_REQUEST_ELEMENT_GROUP_ID
    FROM {SCHEMA}.SHREDDED_REQUESTS_STAGE HSRS INNER JOIN {SCHEMA}.SHREDDED_REQUEST_ELEMENTS_GROUPS HSREG ON LOWER(HSRS.XML_ELEMENT_NAME) = HSREG.LOWER_XML_ELEMENT_NAME
                                                                                                    AND LOWER(HSRS.XML_XPATH) = HSREG.LOWER_XML_XPATH
    WHERE NOT EXISTS (SELECT *
                      FROM {SCHEMA}.SHREDDED_REQUEST_ELEMENTS HSRE
                      WHERE HSRS.XML_ELEMENT_NAME = HSRE.XML_ELEMENT_NAME
                        AND HSRS.XML_XPATH = HSRE.XML_XPATH);

    COMMIT;
END
GO

4.Add to the permanent store

Use the table from the previous stage and load the data into the final table SHREDDED_REQUESTS. Loading only the surrogate key for the element and path of the XML value.

Something like:


CREATE PROCEDURE {SCHEMA}.ADD_SHREDDED_REQUESTS()
LANGUAGE SQL
BEGIN
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Add to the main table based on what is in the staging table. 
    --Everything to go over so if there is 1 day or 100 all goes!!
    --Using Export and load to get it done quicker and save on log space. 
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Declare Vars
        DECLARE EXPORTSTRING VARCHAR(3000);
        DECLARE LOADSTRING VARCHAR(3000);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Process

    --Create the EXPORT string
    SET EXPORTSTRING = 'EXPORT TO "/{some file system you can output too}/SHREDDED_REQUESTS" OF DEL MODIFIED BY COLDEL0x09
                        SELECT HSRS.REQUEST_ID,
                               HSRS.REQUEST_DATE,
                               HSRE.SHREDDED_REQUEST_ELEMENT_ID,
                               HSRS.XML_ELEMENT_VALUE
                        FROM {SCHEMA}.SHREDDED_REQUESTS_STAGE HSRS INNER JOIN {SCHEMA}.SHREDDED_REQUEST_ELEMENTS HSRE ON HSRS.XML_ELEMENT_NAME = HSRE.XML_ELEMENT_NAME
                                                                                                                AND HSRS.XML_XPATH = HSRE.XML_XPATH';

    --Execute the EXPORT string
    CALL SYSPROC.ADMIN_CMD(EXPORTSTRING);

    --Create the LOAD string
    SET LOADSTRING = 'LOAD FROM "/{some file system you can output too}/SHREDDED_REQUESTS" OF DEL MODIFIED BY COLDEL0x09
                      METHOD P (1,2,3,4)
                      INSERT INTO {SCHEMA}.SHREDDED_REQUESTS(
                        REQUEST_ID, 
                        REQUEST_DATE, 
                        SHREDDED_REQUEST_ELEMENT_ID, 
                        XML_ELEMENT_VALUE
                      ) COPY YES TO "/{some file system you can output too}-copy/" INDEXING MODE AUTOSELECT';
                      
    --Execue the LOAD string
    CALL SYSPROC.ADMIN_CMD(LOADSTRING);
END

XML Shredding – The file

As a reward for getting through all of the above, or for skipping it all and going for the juicy bit at the end here is the file – enjoy

GOTCHA’s
  1. I have compression on the servers at work, I have kept COMPRESS YES statement in, if you don’t have it then you will need to remove it other wise you will be in violation of your licences.
  2. You will have to change all {values} to get it to work, to your schemas etc that are relevant to your servers
  3. Please make sure you test before you use, you have been warned



Posted in: Date, DB2 Administration, DB2 Data Types, DB2 Development, Decompose XML, IBM DB2 LUW, Recursive Query, Recursive SQL, Shred XML, V10, V9.7, Vargraphic, XML, XMLTABLE / Tagged: ADMIN_EST_INLINE_LENGTH, DB2, DB2 Administration, DB2 Development, Decompose XML, EXPORT, IBM, IBM DB2 LUW, LOAD, Recursive Query, Recursive SQL, V10.1, V9.7, XML, XML PATH, XMLTABLE

Excel addable time function – Friday Function

August 17, 2012 12:30 pm / 2 Comments / dangerousDBA

For the third in the series 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, minutes 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 minutes 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



Posted in: Date, DB2 Administration, DB2 Data Types, DB2 Development, IBM DB2 LUW, Time, User Defined Function / Tagged: convert, DB2, DB2 Administration, DB2 Development, IBM DB2 LUW, Time, UDF, User Defined Functions

Dates to “Long” – Friday function

August 3, 2012 1:00 pm / 3 Comments / dangerousDBA

Time for another Friday function.

The ask

This time I was asked by our marketing bods to come up with something that made the dates in the SQL that is merged into our marketing and informational emails (glorified mail merge) from a dd/mm/yyyy (13/06/2012) or yyyy-mm-dd (2012-06-13) to a ddth MMM YYYY so becomes 13th June 2012.

They had been trying to do this themselves, with some mixed and non standard ways of doing it, generally it just made the code really hard to debug when they had issues, personally I could not find anything in the IBM info centre or online to do this so I created them a UDF that takes in a date and supplies out a “long” format date.

DB2 Date to “Long” format – Code


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','31')) 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

Queston?

Is it that there is not the function built into DB2 that does this or does everyone have something similar for the audience that they deal with?

Future Post

I will be looking at converting an integer that represents seconds into a time



Posted in: Convert, Date, DB2 Administration, DB2 Data Types, DB2 Development, IBM DB2 LUW, UDF, Uncategorized, User Defined Function, Varchar / Tagged: convert, Date, DB2, DB2 Administration, DB2 Development, IBM DB2 LUW, UDF, User Defined Functions, Varchar

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

June 14, 2012 8:14 pm / 2 Comments / dangerousDBA

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

Posted in: Data types, Date, DB2, DB2 Administration, DB2 built in functions, DB2 Maintenance, IBM, MOD, Time, Uncategorized

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

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

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: added functionality, colum, data types, db2, inline function

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2022 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress