Database resources Link

Few gems on internet on the database concepts:

• http://www.eternallyconfuzzled.com/tuts/datastructures/jsw_tut_bst1.aspx

Hashing is such an important concept to understand,I find this very good.

• http://research.cs.vt.edu/AVresearch/hashing/buckethash.php

Database

While studying database course(RDBMS) in college I never thought I would end up doing database administration.At that time what we ever thought was doing coding and programming .However I was more into Discrete Mathematics and Data Structure and Operating System rather than Programming languages.
Though I liked the small book called "The Programming in C" by Dennis Ritchie. Its short and sweet.And believe me,it's very very powerful and concise.I never fully leverage it to become a good programmer but I think It can help whosoever wants to be a good programmer.
Now since I m now concerned with database so I would focus on database part.
Back in college days I barely realized that OS ,Data Structure and database are so intimately related but now I realize it do so. They are close kin.And I endorse the fact that theory plays as important role in understanding database as the hands on.
Those times, I had no real exposure to any DBMS apart from few times I played with MS SQL server.Now I work on IBM DB2 so got to know the practical aspect of database course.At that time I thought SQL is such an easy part for DBMS course all we needed to do was few SQL statements like "select *  from ".
But now it seems the whole world out there to understand. There are hordes of books available on this subject and many resources on internet, just Google any topic and u have plethora of links and some are worth following too :-) Like I just Google  "Hash table" and I get a very good article on wikipedia :
http://en.wikipedia.org/wiki/Hash_table
In a daily life of a DBA a topic like hash table hardly figures but I think One must have deeper understanding of the underlying DBMS, no matter what vendor 's flavor u are working on.

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

db2 monitoring

db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
db2 "get snapshot for tablespaces|tables| on db db_name"

and to see which switches are active:
use
db2 "get dbm monitor switches"

Note:don't use double quotes while running the command from CLP

the following links provide good explanation for monitoring database:

http://space.itpub.net/?uid-47598-action-viewspace-itemid-330882

http://www.performancewiki.com/db2-event-monitors.html

http://it.toolbox.com/blogs/db2luw/using-the-snapshot-monitor-and-monitoring-via-sql-5631

General Tips for tuning DB2 database

performance related db parms

what your hard disk nature , no of CPU, BPs

what is your hardware environment , physical disks, No of CPU, physical disk
properties , seek time , RPM.

get snapshot for bufferpool , check hit ratio, ( physical , logical reads,
if physical read are more , increase BP size)

also see package lookup , insert activity , if insert activity more increase
packagecachesize (PCKCACHESZ).

create national BP , allocate at highly accessed tablespaces

calculate tablespace transfer rate , over head based on physical disk seek
time , RPM

update dbm parm

Enable intra-partition parallelism (INTRA_PARALLEL) = YES ( need to
turned for data ware house environments
db2 update dbm cfg using INTRA_PARALLEL YES

DB parms

Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 ( no CPU for
your server)
Number of I/O servers (NUM_IOSERVERS) = 3( no physical
disks)

db2 update db cfg for dbanme using NUM_IOCLEANERS no CPU for your server
NUM_IOSERVERS no physical disks

any application in DB or server side 20% of tuning can be done , rest 80%
tuning need to done in application side, may application design, logic ,
flow , way of SQL writing.

SQL performance tips :-

avoided cantusion join, always make optimizer to use nested loop join.

join large table with small tables, no not in SQL, make better use of index,
good key for index

run db2advis ( indexe advisor to find indexes for SQL)

db2advis -d dbname -i sqlinputfile -o indexeDDL

Courtsey:Toolbox for IT

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

db2diag log analysis tool

db2diag is one file which gives clues to error that might have occurred to database.
But instead of going to db2diag file we can run a command from CLP to see the error messages and info about any specific SQL error code.

It is the log analysis tool and it allows you to search, filter, and format the DB2 diagnostic logs. One of the simple examples is that I wanted to see when errors or warnings were produced on my test system. I could do that by calling:

db2diag -l Error,Warning -fmt "%ts %level %db"

sample output:
2010-02-09-13.46.24.828000 Error XXLIC_DB
2010-02-09-13.46.24.828000 Error XXLIC_DB
2010-02-09-13.46.25.046000 Warning XXLIC_DB
2010-02-09-13.46.25.062000 Warning XXLIC_DB
2010-02-09-13.46.25.093000 Warning XXLIC_DB

The output is much quicker to digest than the diagnostic itself.

what I have been upto recently...

This is the first entry in 2010..must say being regular with blog is something I need to prioritize as this is not only a way to share the knowledge and experience which many can find useful but can be very handy to place important stuff which I can come back to refer....
for the past month I have been traveling to many database sites and making migration, version upgrade and other fine tuning tasks...
Troubleshooting is one thing that we come across often so I m going do dedicate one post for that.