DB2 Basics: The DB2 LUW Sample Database

I find the DB2 sample database useful for trying new things and proving to myself how things work.

Why Use the Sample Database?

The DB2 sample database is useful for a variety of reasons.

Testing Connectivity

Sometimes when building a new database server, we need to test connectivity from an application server or some random client into the new server. Having the sample database available makes this easy because we can test the full range of what connecting does without having to have our real database created yet.

Testing Ability to Create Database

Part of verification on a newly built database server may include testing whether a specific user can create databases or whether a database can be created on a specific path. The sample database is perfect for this.

Testing Various Functionality

There is always some new feature or some aspect of DB2 that I am investigating to understand exactly how it works. While much of this may be for blogging purposes, some of it is just to build my own knowledge and understanding for a particular process. No DB2 expert knows everything about DB2 and every detail of how it works, and for me, one of the best ways to understand something is to do it. The sample database on a Linux VM is a safe place for me to do this without risking any development environments.

Especially on undersized machines like my local Linux VM, this command can take minutes to run.

This creates a UTF-8 database called SAMPLE on the path defined by the DBM configuration parameter DFTDBPATH. It creates tables and other objects in a schema with the same name as the user executing the command (really the CURRENT_SCHEMA special register). It also imports both standard relational data and XML data into the tables.

You can change the name of the database created by using the -name option on the db2sampl command, or the path using the -dbpath option.

Changing the database name can allow you to create more than one sample database. If you do not change the name, but use the -force option on the db2sampl command, DB2 will drop the existing sample database and re-do the process. I probably end up dropping and re-creating my sample database on my local VM every couple of months, because as I’m trying stuff I end up changing things, and need to reset them to work through something.

Dropping the Sample Database

If you don’t need the sample database any more, you can drop it using the drop database command. This command always strikes fear in my heart, and I hate running it, so whenever you type “DROP” think twice to make sure it’s what you want to do.

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.