Everything about DB2 tables

Two SQL based ways to get detailed information on a DB2 table:

For example, we may want to know how much disk space has been allocated for a particular table, or what state it is in. Should it be reorganized? Has the last load job finished loading data in to it and so on

First Method:
using ADMINTABINFO view is one

SELECT * FROM SYSIBMADM.ADMINTABINFO;

The query is returning about 29 columns and this is for every table in the database.


Second Method:
The other is a to use SYSPROC.ADMIN_GET_TAB_INFO_V95 table function.


It accepts table schema and table name as parameters and returns exactly the same information as the ADMINTABINFO view.


SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO_V95('ADMINISTRATOR', 'SALES'));

some typical DB2 SQL Queries

how to select a random row with DB2: 

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

DB2 Functions:

Two types of functions can be applied to data in a DB2 table using SQL:

column functions and scalar functions.

example of Column function:

SELECT SUM(SALARY)
FROM EMP
WHERE WORKDEPT = 'D21';

example of Scalar function:

SELECT DAYOFWEEK(HIREDATE)
FROM EMP
WHERE EMPNO = '000100'; 

SELECT CEILING(3.5), CEILING(3.1), CEILING(-3.1), CEILING(-3.5)
FROM FROM SYSIBM.SYSDUMMY1; 

db2 updates large number of records

Disable Logging while executing updates for large number of records:
alter table activate not logged initially;
update set column=value where ;

but with caution  :
take a backup of the table by exporting the table  before executing the above query . If anything goes wrong (that is if your transaction rolls back for any other reason) you will have to drop the table and loose all the data.
I have faced a problem when I didn't exercise caution and eventually I needed to restore the whole database which was such a pain in the neck!
Error message was like you cannot access the table as NOT Logged Initially disable the logging and when the query took longer than expected I killed the query and consequentially table was not accessible.

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