DB2 : everything about UDB universe

APAR NOTE :
http://www-01.ibm.com/support/docview.wss?uid=swg1IZ49936

IZ49936: UNDER CERTAIN CONDITIONS, OPTIMIZER MAY PICK LESS THAN OPTIMAL ACCESS PLAN DUE TO OVER-ESTIMATED CARDINALITY

Our team ran into a problem of using the 'fetch first 1 row only' (functional requirement of just finding whether atleast one row matched a criteria), and the return time for the query was 100 times longer than the one when used without the row limiting clause.
On viewing the explain data, we found it was using a table scan, and when used 'without' the clause, it used an appropriate index scan.
So what was the reason?? Because of the row-limiting clause, DB2 'thinks' (sometimes it doesn't align with the world) that the cardinality in the table formed due to a subquery with this clause is very low and hence uses a tablescan.

This has been fixed now, and all is well, but before this was put in place, I was able to 'tell the optimizer' about the cardinality using the runstats with distribution on the predicates, yes, RUNSTATS is that powerful..

I’ve always wanted db2 to have wider array of scalar functions.

Some like first day of month, last day of month are missing, but the most glaring one that many programmers use is the

max(val1,val2) = (val1 > val2, val1, val2),

and that is missing in UDB version 9.5 as of now. Not very sure about the latest 9.7 that boasts to save the world and solve the hunger problem though.

So I created one myself, and am heavily using it in my code so does other developers.

create function DB2ADMIN.MAXTWO(x date, y date)

returns date

begin atomic

if y is null or x >= y then return x;

else return y;

end if;

end;

Overload this for handling other datatypes and use them with descretion.

One developer asked me to help out his project. He wanted to remove the numbers,any special chars in his field values,

eg., '99kirkh-ammet99' to 'kirkhammet' and ' db2' to 'db'

I tried using translate, but didn't know what to do with the space that came in due to the function. Got help from one friend for the same.
Here is the final one : easy for experts, useful for newbies.

VALUES REPLACE(TRANSLATE('999kirk-hammet9 8', '', TRANSLATE('999kirk-hammet9 8',
'#', 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')), ' ', '') ;

To separate the command into pieces to explain it,

1. the inner translate :
TRANSLATE('999kirk-hammet9 8','#','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')

- converts any characters to #, and ref. the syntax of translate function (new), pads # if final string is smaller than initial. Please read the new syntax. Very useful function.

Result :
'####-######9 8'
2. Outer translate:
This converts all the values (in our example - #, -, 9, 8) to spaces in the value.

Result :
' kirk hammet '

3. Replace :
This replaces the spaces with empty places thus getting us - 'kirkhammet'

If anyone got useful functions or links for the same, please comment.

Old article, but worth the read, gives a brief intro to using local os scripts from db2 udfs.

http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip4.html

One of the junior DBAs asked me this question. I thought it was a good enough reason to blog.

I am not touching what primary key helps us achieve, but what's difference in executing just

create unique index x.y on table xx.yy allow reverse scans;

than

create unique index x.y on table xx.yy allow reverse scans;
alter table xx.yy add constraint -- and give the columns in the above defined unique index

unique index can have one row with null value in either of columns, while primary key disallows it :)