Please read the article at this link.

What do you think? Is open source database a viable solution?

The link says (as quoted by Forrester) that the market share for

• Open source database is at $850 million
• Commercial databases is $ 16 billion

Continue Reading »

Here is a good link on Explain plans and on displaying them.

http://optimizermagic.blogspot.com/2008/02/displaying-and-reading-execution-plans.html

I was reading this today and thought it was interesting.

Karen explains why having min and max in the same query impact performance and how to tackle the same.

Here is a good article on tuning DB2 databases.

And here is another that might help you.

Recently we migrated an entire DW module from Oracle to DB2. And our ETL was done using Business Objects Data Integrator.

Below is a sequence of steps that we followed to migrate the entire solution (including data structures, ETL code and universe)

Assumption:
–>All estimates done for 4 DB2 databases, 3 federated systems, 62 tables, 33 indexes, 6 DI jobs, 110 data flows, 151 transformations, 16 SQL transforms, 34 SQL() scripts, 220 objects in 23 classes and 2 reports.
–>Add 20% time as buffer for unexpected issues.
–>Some tasks can be executed in parallel, while some have strong dependency on completion of previous tasks.

Work breakdown of migration and rough estimation:

1. Initiation & planning : 40 Hrs
Establish objectives and goals
Requirements gathering
Establish scope
Plan resources
Identify development environment

2. Assessment : 40 Hrs
Assess tool and technology needs
Assess and understand technical requirements
Establish technical guidelines
Finalize tools and development environment

Continue Reading »

I am currently researching and learning more about IBM DB2.

We recently migrated a module from Oracle to DB2 without having a single DB2 resource. Most of it was done on a research, trial and error basis.

And we had some great learning experience.

I will share those experiences with you soon.

In the meanwhile, if you know some great DB2 resources or help sites, please do leave a comment.

Here is a very interesting scenario.

I have an employee table called EMP.

And I have a DEPT table.

Now, I write a query to find out all the employees who are part of any department. (DEPT_ID is properly updated and found in DEPT table)

Now, I want to know if any employee is not part of any department.

What??? No results? I know that employee with EMP_ID 9 doesn’t belong to any department. Where did he go?

Is Oracle drunk? Is it giving me wrong results? Wait let me check with another query.

Continue Reading »

I am a big fan of Vincent McBurney. Today I was reading this post.

I agree completely with his view on Denormalization. (Scroll to the bottom of the article).

At times, I have had difficulties in recalling if normalization is splitting or joining. And it does make a lot more sense to just call them ’split’ and ‘merge’ instead of ‘normalize’ and ‘denormalize’.

Just another instance where plain common sense could have been helped everyone.

Hi,
In Oracle databases, what is this high water mark? What impact does it have on the full table scan?

Well, HWM is an indication that segregates used and unused blocks. Above the high water mark you find the unused blocks and below it the used blocks.

Used blocks can be
1. Free - unoccupied
2. Used - occupied

As the data grows in a table, the high water mark starts moving upward. The only way to bring it down is to rebuild or truncate the table.

And a full table scan reads from the beginning of the table to the high water mark.

When you insert data into a table and then delete it later, the high water mark doesnt move down. So when you insert a million records into a table, the HWM moves up. A full table scan will scan as if there are a million rows. When you delete 0.9 million records from it, the high water mark doesnt come down. A full table scan will still scan as if there are a million records.

The ideal way to handle this is by repacking the table

alter table {TABLE_NAME}
move;

And keep 2 more things in mind.
1. Indexes size will remain same. That has to be repacked.
2. Indexes will be invalidated.

So, rebuild the indexes to fix these 2 issues.

Truncate:
1. Moves the high water mark down to beginning.
2. Doesnt need commit. It is a DDL command.
3. No condition needed. Removes all rows.
4. Extents are deallocated. (If you specify the reuse storage clause, then the extents are not deallocated)
5. No rollback.

Delete:
1. Doesnt change the high water mark.
2. Needs commit. It’s a DML command.
3. Can specify a condition and remove specific rows.
4. Extents are not deallocated.
5. Changes can be rolled back.