Posted by 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.
How to Create the Sample Database
The sample database is very easy to create. You can simply run:
$ db2sampl Creating database "SAMPLE"... Connecting to database "SAMPLE"... Creating tables and data in schema "DB2INST1"... Creating tables with XML columns and XML data in schema "DB2INST1"... 'db2sampl' processing complete.
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.
Some of the details about the sample database are available in the IBM DB2 Knowledge Center.
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.
$ db2 drop db SAMPLE DB20000I The DROP DATABASE command completed successfully.
Sorry, maybe I haven’t understood, where can I download the sample dataset?
I click on the IBM link but I cannot see any download file.
The sample dataset is included with the Db2 code, you don’t have to download it separately.
Thanks for your expertise.
After creating the SAMPLE database, I need to load test it so I can test some monitoring commands.
Where can I get some workload scripts???
Usually you don’t run load tests against the sample DB. I don’t know of any workload scripts for it.