Create SAMPLE database in DB2

In this blog we will go though steps to create sample database. You might need SAMPLE database with tables, views, index, function, procedure and dummy data to conduct POC or for testing, learning purpose. You will need a DB2 instance to create SAMPLE database. Check blog How to create DB2 Instance on Linux for instructions to create DB2 instance and start it.

Prerequisite :

To create SAMPLE database you need a DB2 instance, also you should have SYSADM or SYSCTRL authorities for that instance.

Create default SAMPLE database :

You can create SAMPLE database using below command

db2sampl

This will create a database with name SAMPLE in default database directory. It may take 30 seconds to few minutes for database creation.

Create SAMPLE database with custom name:

If you need SAMPLE database with name different from SAMPLE, you can achieve that with the help of below command.

db2sampl -name <database name>

This will create a database with name TESTDB. database name must follow naming convention for databases.

If database with name TESTDB already exist you might receive error SQL1005N  The database alias "TESTDB" already exists in either the local database directory or system database directory.

Create SAMPLE database by overwriting existing database:

In case you are getting SQL1005N  The database alias  already exists error while creating SAMPLE database, use below command.

db2sampl -name <database name> -force

This will drop existing database TESTDB and create database with same name. If TESTDB is in active state you might receive error SQL1035N The operation failed because the specified database cannot be connected to in the mode requested.

Create SAMPLE database with custom database path:

If you need sample database to be created in different database path instead of default database path you can use below command

db2sampl -dbpath <dbpath> -name TESTDB2

This will create database with name TESTDB2 on path /dbdata.

Create Encrypted SAMPLE database:

If you want to create an encrypted sample database, use below command, but before creating encrypted sample database ensure Encryption is configured for DB2 instance. Check blog DB2 Native Encryption , How to Encrypt DB2 database , How to Encrypt DB2 Database using Centralized KeyStore to understand about DB2 Native Encryption and steps to configure DB2 Native Encryption using either Local KeyStore or Centralized KeyStore.

db2sampl -encrypt

This will create encrypted SAMPLE database. You can also use -name and -dbpath option with -encrypt option. If you have not configured encryption and still using -encrypt option you might receive error SQL1728N  The command or operation failed because the keystore could not be accessed. Reason code "1"

Create Encrypted SAMPLE database with custom Master Key Label:

If you want to create an encrypted sample database with custom Master Key Label use below command.

db2sampl -encrypt -encropts "Master Key Label=<Label_Name>"

This will create encrypted SAMPLE database with custom Master Key Label SAMPLE.DB2I1.DB2TEST1 . If Master Key with label SAMPLE.DB2I1.DB2TEST1 is not present in your KeyStore then you might get error SQL1729N  The command failed because the label "abc.DB2I1.DB2TEST1" does not exist in the keystore. 

All above options can be combined and used together.

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.