DB2 : everything about UDB universe

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.

Just to link with a usenet group's thread reg. the colseq column in keycoluse catalog view. I always thought this was supposed to list the foreign key columns and it's relation with the parent columns. But it seems db2 has a bug w.r.t this view.

Here is a list of statements I ran.
DROP TABLE ARUN.TEST2;
DROP TABLE ARUN.TEST1;
create table arun.test1 (a char(1) not null, b char(1) not null);
create unique index arun.test1_idx2 on arun.test1(b,a) CLUSTER;
create unique index arun.test1_idx1 on arun.test1(a,b) ;

alter table arun.test1 add constraint pk_test1 primary key(a,b);

create table arun.test2(a2 char(1),b2 char(1));
create unique index arun.test2_idx1 on arun.test2(a2,b2) cluster;

alter table arun.test2 ADD constraint fk_test2 foreign key(b2,a2) references arun.test1;



--The below statement gives the relation between foreign key columns and their corresponding parents

SELECT
SUBSTR(R.CONSTNAME, 1, 18) AS KEYNAME,
SUBSTR(KF.COLNAME, 1, 18) AS COLNAME,
SUBSTR(KP.COLNAME, 1, 18) AS REFCOLNAME
FROM
SYSCAT.REFERENCES R
INNER JOIN SYSCAT.KEYCOLUSE KF
ON R.TABSCHEMA = KF.TABSCHEMA
AND R.TABNAME = KF.TABNAME
AND R.CONSTNAME = KF.CONSTNAME
INNER JOIN SYSCAT.KEYCOLUSE KP
ON R.REFTABSCHEMA = KP.TABSCHEMA
AND R.REFTABNAME = KP.TABNAME
AND R.REFKEYNAME = KP.CONSTNAME
WHERE
R.TABSCHEMA = 'ARUN' AND
R.TABNAME = 'TEST2'
AND KF.COLSEQ = KP.COLSEQ
ORDER BY
R.TABSCHEMA,
R.TABNAME,
R.CONSTNAME,
KF.COLSEQ
WITH UR;

I hoped for this output, which is what I wanted..

Keyname Colname Refcolname

'FK_TEST2 ' 'A2 ' 'A '
'FK_TEST2 ' 'B2 ' 'B '

Instead I was blessed with

Keyname Colname Refcolname

'FK_TEST2 ' 'B2 ' 'A '
'FK_TEST2 ' 'A2 ' 'B '

Link to the usenet group's thread..

Everybody loves the new flavor DB2 introduced to the delete command ,

delete from (select * from table [where ] [fetch first n rows only])

because they can limit the number of rows to be deleted in one shot. It has been of immense help since I frequently delete millions of rows and it alleviates the problem of lock memory and log file usage. I just put this statement in a loop and run that for a specific number of times (or till it gets 0 rows returned, with a little complex code). Now the only setback we have is, we cannot use a select statement that joins tables, and would be staring at SQL0150N error. Now I just set out to solve this and using the mighty 'exists' clause, which has saved me during more than one bad situation, was actually able to mimic join and still limit delete to a set of rows.

The original query was
select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3;

The following will not work because joins are not allowed in the 'delete from (' clause
delete from (select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3 fetch first n rows only)
This would throw SQL0150N error.

So I changed the above to
delete from
(
SELECT * FROM table1 T1 where exists
(select 1 from TABLE2 T2 where T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3)
);
Works wonderfully.