Deleting Duplicate records from a table

OLAP function can be very handy sometimes.
and you can read about it on a developerworks
here is the link
http://www.ibm.com/developerworks/data/library/techarticle/dm-0401kuznetsov/index.html

here is the query which I have used

create view del_tmp as select SNO,VEH_REGN_NO ,row_number() over(partition by veh_regn_no) as row# from DWH.DWH_REG;
select * from del_tmp where row#>1;
select * from DWH.DWH_REG;

select count(*) from del_tmp where row# >= 2;
delete from del_tmp where row# >= 2

DB2 SQL Practice for tuning database performance

1) Avoid distinct where ever possible. Check whether distinct is
required or not. No distinct when PK or UK are retrieved.

2) One can consider usage of union where OR condition exits &
eliminate distincts.

3) Conditions which are likely to fail should be kept first in a set
of conditions separated by AND.

4) Always use aliases.

5) Do not involve columns in an expression.
select * from emp where salary/12 >= 4000;
The query should be:
select * from emp where salary >= 4000 * 12;
i.e. Avoid using Arithmetic within SQL statements.Arithmetic in a SQL
statement will cause DB2 to avoid the use of an index.

6) Try to avoid usage of in-built or user defined functions.
select * from employee where substr(name,1,1) = 'G';
The query should be:
select * from employee where name = 'G%';

7) Avoid datatype mismatch since it may lead to implicit/explicit casting.
select * from emp where sal = '1000';
The query should be:
select * from emp where sal = 1000;

8) Substitute unnecessary group by & having with where clause.
select avg(salary) as avgsalary, dept from employee group by dept
having dept = 'information systems';
The query should be:
select avg(salary) as avgsalary, dept from employee where dept 'information systems';

Courtsey:Toolbox for IT

SQL Bible

SQL Bible

This is a very good place to learn and explore SQL Pragramming.