Starting in DB2 LUW version 9, TRUNCATE is supported. This gave ability to quickly get rid of massive amounts of data from tables without worrying about possibility of filling up logs. But, one question that seems to linger around in the minds of many DB2 LUW DBAs is “Is TRUNCATE operation recoverable?” I set out to find this out myself recently. This blog post details such findings.
I hope everyone has had a great start to the 2016 year! I wanted to take a moment to express my gratitude for being a db2talk visitor and reader.
Just a couple of 2015’s db2talk highlights that I would like to share:
- In 2015, the number of views and unique visitors on db2talk almost quadrupled compared to 2014.
- DB2 Basics and DB2 Tips are two most popular categories.
- Blog posts related to awk and UNIX tips and tricks have been very popular in terms of blog-post hits.
In an earlier blog post, we looked at how to create a sample database in DB2 LUW. We also looked at changing the defaults to suit our needs…changed the default database name from SAMPLE to whatever we wanted and also changed the database path (DBPATH).
Here is the link to that blog post: https://db2talk.com/2015/09/08/creating-a-db2-luw-linux-unix-and-windows-sandbox-database/
I forgot to blog about this earlier. I was a presenter on DB2Night Show on 4th December. This topic was “DB2 LUW Tips, Tricks, SQL & Scripts for Lazy DBAs”. I appreciate DBI Software’s founder Scott Hayes for giving me this opportunity to be a presenter on his show.
Replay of this show is available at: http://www.dbisoftware.com/blog/db2nightshow.php?id=651
A copy of the presentation (that could be downloaded) is available in PDF format. Hope this helps.
In this blog post, I will share about few gotchas to look out for when backing up tablespaces in a DB2 DPF database. What is DPF? I wrote a detailed blog post on db2commerce.com.
Why Tablespace backups?
Why should we look into backing up tablespaces instead of the entire database? Below are couple of instances when we want to backup tablespaces:
- You just created a new tablespace; Before taking an incremental/delta backup, the newly created tablespace needs to be backed-up.
- Tablespace is in BACKUP PENDING state. This can happen when we perform a load operation for a recoverable database and specify the COPY NO parameter. We can remove the tablespace from the BACKUP PENDING state by backing up the tablespace.
- Is it practical to do database backups? A database backup for a multi-terabyte database (think Data Warehouse) is not practical all the time. It consumes too many resources (CPU, storage, lock contention etc.). If the recovery effort is well thought out, we can get away with performing tablespace level backups.
Quick note about my guest blog post on db2commerce.com. In this post, I go over in detail about troubleshooting an interesting delayed transaction replay situation on a DB2 HADR standby database. I discuss about eliminating the usual suspects and cover in detail about about how we fixed the problem. Check it out.
Merriam Webster Dictionary defines ‘Sample’ as “a small amount of something that is given to people to try” and that is exactly what the SAMPLE database that is shipped along with every IBM’s DB2 LUW installation is. This database has objects that reflect a real world like data model along with sample data. It could be a nice play ground for someone who is looking to experiment with or to learn new features in DB2 LUW.
In this blog post, we will look at how simple it is to create this sample database. We will also look at what this database has to offer to us.