Category Archives: Uncategorized

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

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.

IDUG – EMEA – 15th – Day Two

Been a long day at IDUG today with talks starting at 0830 till 1645 today, which is a lot to take in back to back but I will attempt to summarise and say what I have learnt quickly before I turn in for the night:

Smarter Analytics for big data – Steven Sit

Informative talk with some topics and content covered in other talks but good for reinforcement and introduction of new stuff. Things I learnt:

  1. Big data is about Volume, Velocity and Variety.
  2. It is iterative and you are probably not going to get it right first time.
  3. Get ready for Zettabyte!
  4. Learn JAQL and a whole lod of other languages for big data processing.

SQL on Fire – Serge Rielau

This was next level stuff, with him creating and testing functions and other things for problems that I have never considered, but may need now with Big data coming into the warehouse. This presentation will definatly take another look or two to understand what is going on in some of the code.

DB2 and Cloud computing why should I care? – Leon Katsnelson

This was a very good foundation and refresher for the concepts, uses and vendors of the cloud. Will defiantly be firing up some free instances once I get back home, and find the time. I learnt that:
  1. A US agency has managed to define what a cloud should be in a two page document.
  2. The cloud has use cases make sure yours is right

ADMIN_MOVE_TABLE() the new DBA’s Swiss army knife

Again this will take another look through to full appreciate, but on the face of it could be a good way to move say a testing table to production without too much hassel as long as you set it up right and dont fall into a GOTCHA.
  1. ADMIN_MOVE_TABLE does not have to be done all at once, and can be done in stages and equally cancelled.
  2. Does not move foreign keys!!

Stuffed with great enhancements DB2 9.7 FixPacks – Melanie Stofer

All kudos to  Melanie she went through 60 slides in 60 mins and there is 120 in the download! A lot of information to take in and no time to make notes at the pace she went through stuff, but all very useful stuff.

The evening was good with free beer from IDUG and a trip out to the Steak house round the corner on Triton Consulting with the guys from Triton and DBI software thank you very much, very tasty and good conversation. Freebies again were good with an excellent glass from Triton and I got my Ticket for the IBM customer dinner tomorrow night, dont forget to get yours!

Tomorrow I will be hopefully going to:

Wednesday, November 16, 2011

08:30 AM – 09:30 AM
Session 9
09:45 AM – 10:45 AM
Session 10
11:00 AM – 12:00 PM
Session 11
12:00 PM – 01:00 PM
Wednesday Lunch
01:00 PM – 02:00 PM
Wednesday SIGS
02:15 PM – 03:15 PM
Session 12
03:15 PM – 03:45 PM
Wednesday Coffee Break
03:45 PM – 04:45 PM
Session 13
05:00 PM – 06:00 PM
Session 14

For the SIG session I am torn between Data Warehousing and DB2 LUW Upgrade or Migrate to New features. I would like to know a lot more about what the agenda is for them. Anyone got any offers?

So may see you around tomorrow have a good night.