Understanding DB2 backup filenames

Posted by David Yahalom under DB2 LUW

When backing up a db2 database the output filename might be confusing at first.
The filename generated by the backup utility might seem long an confusing but it is actually pretty straight forward and very informative.

For example, if I issued the following command:

db2 backup database dbone to /storage/backup_path/

I might get a file named:

DBONE.0.DB2INST1.NODE0000.CATN0000.20070101140000.001

So, what does all the numbers mean?

On UNIX/LINUX:

DBONE.0.DB2INST1.NODE0000.CATN0000.20070101140000.001
(1)-----(2)---(3)---------(4)---------(5)-------------(6)-----------(7)

On Windows:

DBONE.0DB2INST1NODE0000CATN000020070101140000.01
(1)-----(2)----(3)---------(4)----------(5)---------------(6)--------(7)

1) Database alias
2) Backup type.
3) Instance name
5) Database Partition (always NODE0000 for a non-partitioned database).
5) Catalog partition number (always CATN0000 for a non-partitioned database).
6) Date&time of when the backup file was taken.
7) Time image sequence number.

DTS in Oracle

Posted by David Yahalom under ETL, SQL Server, Oracle

If you are an SQL Server DBA you are probably well aware of the power and simplicity of Microsoft’s DTS.

DTS stands for Data Transformation Services and is a part of SQLServer 2000 Enterprise Edition (also called Data Integration Services in SQLServer 2005).

DTS is a powerful and simple to use ETL engine making complex data transformations jobs about as easy as using a mouse. I found myself using DTS almost exclusively everyime I had to move data between different database vendors. There are other, more complex and powerful packages (such as IBM’s DataStage), but because DTS is bundled with SQLServer 2000 EE it was always the most accessible tool for me.

Many DBAs are unaware that Oracle also has a very good ETL package called “Oracle Wearhouse Builder“. I have seen many DBAs installing SQLServer in a strict Oracle environment solely for the purpose of using DTS. If you qualify as one, have a look at Oracle Warehouse builder first as it just might suite your needs.

For more details.

Quickly clone a database

Posted by David Yahalom under DB2 LUW

I really like DB2s ability to quickly restore a backed up database into a new database with a different name in a different location. Just like in SQL server and easier to do then Oracle.

For example, if I have a database named prod_db which was backed up to the folder /storage/database/backups and I want to restore in as a database named dev_db into a different folder like /storage/database/development I can write the following command:

db2 restore db prod_db from /storage/database/backups TO /storage/database/development into dev_db

Quick, easy and painless!

Keep in mind that when restoring a database in DB2 you don’t specify the actual backup file in the command line. You only specify the directory and the DB2 DBM chooses the right backup file automatically. If you have several backup files in that directory you have to add the taken at parameter to the restore command.

taken at It she time stamp of the database backup image. The time stamp is displayed after successful completion of a backup operation, and is part of the path name for the backup image. It is specified in the form yyyymmddhhmmss. A partial time stamp can also be specified. For example, if two different backup images with time stamps 20021001010101 and 20021002010101 exist, specifying 20021002 causes the image with time stamp 20021002010101 to be used.

Oracle extent allocation: AUTOALLOCATE vs. UNIFORM

Posted by David Yahalom under General IT

Starting with Oracle 9i, DBAs can now create locally managed tablespaces.

A Locally Managed TBS manages its own list of free extents in a bitmap block placed inside the header of the first data file of the tablespace. Inside the bitmap block, each bit maps to a free block in the tablespace.

When creating a locally managed tablespace, you can specify the extent allocation method to be used.

AUTOALLOCATE - means that the extent sizes are managed by Oracle.
Oracle will choose the optimal next size for the extents starting with 64KB. As the segments grow and more extents are needed, Oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents. This might help conserve space but will lead to fragmentation. This is usually recommended for small tables or in low managed systems.

UNIFORM - specifies that the extent allocation in the tablespace is in a fixed uniform size. The extent size can be specified in M or K. The default size for UNIFORM extent allocation is 1M. Using uniform extents usually minimizes fragmentation and leads to better overall performance.

SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

I usually prefer to keep large production-grade tables in UNIFORM sized tablespaces and smaller tables or tables in unmanaged environments in AUTOALLOCATE tablespaces.

Hello world!

Posted by admin under Uncategorized

I’m a senior database consultant working for one of Israel’s largest IT services providers. I had the pleasure to design, implement and administer various Oracle databases in diverse environments for the past five years including some very large and complex enterprise-level implementations.

Some of my more recent projects included designing and implementing a DRP solution for a large Israeli retailer using Oracle DataGuard and a Human Resources related Data Warehouse system with a complex ETL process importing data from an IBM Z/Os mainframe as its primary data source.

For the past year, beside doing various consulting projects on Oracle databases, I’ve also gained experience working on a very large financial-sector DB2 LUW project allowing me to expand my expertize and knowledge in that platform as well.