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

Category Archives: Uncategorized

DB2 LUW Exam 611 – Holiday reading

April 5, 2013 1:30 am / Leave a Comment / dangerousDBA

This is a short post and I hope to get back into blogging proper once I am back from my hols. Its been a while since I last posted but seen as though we are on holiday and it is currently to hot to move I thought I would do a post. My holiday reading generally consists of technical manuals, papers , generally interesting stuff about history etc. This holiday has been no different I am currently trying to wade through the only currently IBM published material for the new IBM 611 DB2 LUW DBA Exam.

Preparation guide for DB2 10.1 LUW Exam 611

So this is the view that I currently have most days while trying to wade through the treacle that is this material:
image

This guide can be found here. while I don’t doubt that it is not all very good stuff, and I have to admit that some is bloggable when I get chance there is too much detail when compared to the past offerings from Roger Sanders.

The red, green and purple books offered the right level of detail for the exam but also enough to go off to the info centre and find out all the nitty gritty for yourself if needed. On the other hand IBM have gone the whole hog in this guide and included the whole info centre at 1121 pages giving you too much detail and no indication about what might actually be on the exam!

Too much detail

There are 45 or so pages on temporal tables (p253 – p301) whereas for indexes there are less than 30 (p327 – p351). So does this mean that temporal tables are new so they have devoted a lot more to it as indexes are old and everyone knows about them? Or does it mean that there will be a lot more questions on the topics that have more pages in this guide? Any offers?

I am also sorry to say the style offers no inspiration to carry on. It took me the best part of two days to clear the temporal table section, but I am fully caught up on any sleep I may have needed to catch up on!

Please someone release a less verbose updated version of this guide, like the good old green and purple books.

Posted in: DB2, DB2 Administration, DB2 Temporal Data Management, Exam, IBM, IBM DB2 LUW, Uncategorized / Tagged: 611, DB2, DB2 Administration, Exam, IBM DB2 LUW, V10.1

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

db2advis a beginners guide

July 9, 2012 9:51 pm / 1 Comment / dangerousDBA

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 favourite 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.

db2advis – The query optimisation command

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) 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

db2advis – Extended command

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 -loption 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.



Posted in: DB2 Administration, db2advis, IBM DB2 LUW, Reorganisation, Reorganise Indexes, Reorganise Tables, Runstats, Uncategorized / Tagged: DB2, DB2 Administration, db2advis, IBM DB2 LUW, Reorganise Indexes, Reorganise Tables, Runstats

SYSPROC.ADMIN_CMD – RUNSTATS

July 6, 2012 9:24 pm / 1 Comment / dangerousDBA

As I mentioned last post you can use SYSPROC.ADMIN_CMD to carry out runstats. I originally developed this Stored procedure using SYSPROC.ADMIN_CMD when I had a junior DBA that was new to the administration side of the database game, and therefore they could RUNSTATS without too many issues. Calling a RUNSTATS this way can be very useful in large stored procedure calls in a data-warehouse without having to break up a controlling Stored Procedure.

SYSPROC.ADMIN_CMD – RUNSTATS Code

So you can create a dynamic string to pass into the SYSPROC.ADMIN_CMD using parameters from the stored procedure call that are passed in something like:

RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS

Where IN_TABLESCHEMA and IN_TABLENAME are passed into the stored procedure by the user. You can create a stored procedure like:

CREATE PROCEDURE DB_MAIN.RUNSTATS  (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100))
LANGUAGE SQL
BEGIN
   DECLARE RSSTRING VARCHAR(255);

   SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS';

   CALL SYSPROC.ADMIN_CMD(RSSTRING);

END

As I said in my last post I have a load of stored procedures in a DB_MAIN schema that allows a database based “installable” schema that can carry out many maintenance tasks. The one above will do a general runstats against everything, I also created a more specific one for RUNSTAT’ing an index only:

CREATE PROCEDURE DB_MAIN.RUNSTATS_INDEX (IN IN_TABLESCHEMA VARCHAR(100), 
                                               IN IN_TABLENAME VARCHAR(100), 
                                               IN IN_INDEX_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE RSSTRING VARCHAR(1000);

SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ' || IN_TABLESCHEMA || '.' || IN_INDEX_NAME || ' ALLOW WRITE ACCESS';


CALL SYSPROC.ADMIN_CMD(RSSTRING);
END

This is obviously superseded by the first example but it will be quicker.

Future Posts

I will look at combining this with other SYSPROC.ADMIN_CMD run commands to perform the 4 R’s of DB2. The 4 R’s are:

  1. Reorganise Tables
  2. Reorganise Index’s
  3. Runstat’s Table / Index’s
  4. Rebind



Posted in: DB2 Administration, DB2 Built-in Stored Procedures, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise Indexes, Reorganise Tables, Runstats, SYSPROC.ADMIN_CMD, Uncategorized / Tagged: DB2, DB2 Administration, DB2 Development, IBM DB2 LUW, Reorganisation, Reorganise, Reorganise Indexes, Reorganise Tables, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD

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

Post Navigation

← Older Posts
 

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 Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode 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 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress