Creating a DB2 LUW (Linux, Unix and Windows) Sandbox database

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.

Creating Sample Database:

Authority Required: SYSADM or SYSCTRL.

After DB2 has been installed, the simplest way to create a sample database is by using the command 'db2sampl'. This command is at $HOME/sqllib/bin where $HOME is the Instance Owner’s home directory. Usually, this path is in PATH of the instance owner. So, you should be able to run this command from anywhere.

Lets give this a shot:

$ 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.

Now that we created SAMPLE database, we will have the following questions:

  • Where (path) is the database created?
  • Could we change database’s name and the location it is created?
  • What objects are created?
  • What database privileges/authorities are granted?
  • How does the data model and sample data look like?
  • How to go back to a clean slate after data has been modified?

Where is the database created?

When we created SAMPLE database, we did not tell DB2 where (location) to put the database. When the ‘path’ is not specified, DB2 creates database at the ‘Default Database path’ specified by the DFTDBPATH parameter in the Instance Configuration. We could get its value from the Instance Configuration file.

 $ db2 get dbm cfg | grep -i DFTDBPATH
 Default database path                       (DFTDBPATH) = /home/db2inst1

Now, let us check if DB2 actually created the SAMPLE database at /home/db2inst1. We could do this by looking for the field Local database directory in the output of 'db2 list db directory' command. See below:

$db2 list db directory
Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Changing the defaults

What if we want to create database at a different path? How about having ability to choose a different name for our database? We do have these choices.

The 'db2sampl' command comes with few options (shown below) that enable us to customize properties of our database. The database could be created at an alternate path, given a name that we choose etc. It would be a good idea to stay away from ‘force’ option as the command drops an existing database and creates one with the same name.

$ db2sampl -help
  db2sampl command
  Purpose:
  Creates a sample database with database objects and data used by DB2 samples.

  Syntax:

  db2sampl [-dbpath ] [-name <database-name] [-force]
           [-verbose | -quiet] [-sql] [-xml] [-v8] [-?]

  Options:

    -? or ? or help       : Display the db2sampl command syntax.
    -dbpath    : Store the database files in the path .
    -name  : Set the database name to .
    -force                : Drop existing database with the same name.
    -sql                  : Create SQL database objects and data.
    -xml                  : Create XML database objects and data.
    -verbose              : Print status messages to standard output.
    -quiet                : Suppress printing of status messages.
    -v8                   : Create SAMPLE database from DB2 UDB V8.

In the example below, we are choosing the database name to be TESTDB and the location where it is created as /db2inst1/data1 .

 $ db2sampl -dbpath /db2inst1/data1 -name TESTDB

  Creating database "TESTDB" on path "/db2inst1/data1"...
  Connecting to database "TESTDB"...
  Creating tables and data in schema "DB2INST1"...
  Creating tables with XML columns and XML data in schema "DB2INST1"...

  'db2sampl' processing complete.

Now, output from db2 list db directory looks like below:

Database alias                       = TESTDB
 Database name                        = TESTDB
 Local database directory             = /db2inst1/data1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

In the rest of the blog post, we will refer to the database name as ‘SAMPLE’, the default name for sample database.

Default Privileges/Authorities

When the SAMPLE database is created, DB2 grants authorities/privileges to a pseudo-group called PUBLIC that allows any user (who successfully authenticates) to do the following:

  • Connect to the database
  • Create/drop tables in a new/existing (non-catalog) schema
  • Create packages

Connecting to SAMPLE database:

As PUBLIC has been granted ability to connect, any user (who successfully authenticates) should be able to connect. At the command line on the database server, there are two ways in which we could connect.

An implicit connection is in which we do not supply user id/password in the connection string. In this type, as we already authenticated via OS when we logged in, DB2 will not do an additional authentication step. We will connect to DB2 using the same user id as the OS log in.

In the example below, we logged in as db2inst1. We then issued the command to connect to the database SAMPLE (case in-sensitive) without supplying user name. From the output, we notice that we re connected to the database using the same user id db2inst1

db2inst1@server1:~ $ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

An explicit connection is in which the user id is specified in the connection string. Password is mandatory for explicit connections. Here is an example:

 $ db2 connect to sample user dev1
Enter current password for dev1:
   Database Connection Information
 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = dev1
 Local database alias   = SAMPLE 

Database objects in SAMPLE database

The query below shows that, we have 6 aliases, 1 MQT, 170 tables and 299 views.

$ db2 "select type, count(*) as count 
from syscat.tables group by type"

TYPE     COUNT
---- -----------
A              6
S              1
T            170
V            299

  4 record(s) selected.

Something to pay attention to is that the above list contains DB2’s catalog objects as well. How to know the counts for non-catalog objects? Just add a ‘where’ clause to exclude catalog objects that are in schemas starting with SYS. All DB2 catalog objects are in schemas that start with SYS. Here we go:

$ db2 "select type, count(*) as count 
from syscat.tables where tabschema not like 'SYS%' 
group by type with ur"

TYPE    COUNT
---- -----------
A              5
S              1
T             22
V             19
  4 record(s) selected. 

Quick note on licensing:

At IBM’s Knowledge Center link for the command db2sampl, we see the following note. On a personal note, I wish IBM does not enforce licensing requirements for SAMPLE database or ease them.

On DB2® Workgroup Server Edition and DB2 Express® Server Edition, the SAMPLE database includes materialized query tables (MQT), and multidimensional cluster tables (MDC) that causes a license violation. This violation can only be removed by upgrading to DB2 Enterprise Server Edition.

This is because, Complex Query Support that includes MQTs, MDCs etc. is only included from DB2 Enterprise and above as shown below. Refer to this link for more details. Note that this link is specifically for DB2 10.5.

Complex query support by DB2 edition
DB2 edition Details
DB2 Express-C
DB2 Express
DB2 Workgroup
Not available
DB2 Enterprise
DB2 Advanced Workgroup
DB2 Advanced Enterprise
Included

Below is how we could check for the type of DB2 license we have.

$ db2licm -l
Product name: "DB2 Advanced Enterprise Server Edition"
License type: "Developer"
Expiry date: "Permanent"
Product identifier: "db2aese"
Version information: "10.5"
Enforcement policy: "Soft Stop"

Tables and sample data:

In order to know approximate row counts, we will quickly run runstats on all the tables so that the catalog data is correct.

$ db2 -x "select char(tabschema,20), char(tabname,70) 
from syscat.tables where type = 'T' with ur" | 
awk '{print "runstats on table "$1"."$2}' | 
db2 -v | tee -a runstats.log

(I blogged about using awk to simplify DBAs tasks. If you are new to awk or if the above syntax looks foreign, I would encourage you to take a look at that post. Imagine baby-sitting runstats command for all the database objects.. that is not fun. With awk, we can get away with a one-liner.)

Now that we have run runstats, catalog data should be reasonably accurate. One point to note is that the SCHEMA in which objects are created is the Instance Owner ID. I do not think there is a way to change this when creating SAMPLE database.


 $ db2 "select char(tabschema,20) as SCHEMA, 
 char(tabname,20) as TABLE, card 
 from syscat.tables where type = 'T' 
 and tabschema not like 'SYS%' 
 with ur"
SCHEMA               TABLE                CARD
-------------------- -------------------- --------------------
DB2INST1             CL_SCHED                                5
DB2INST1             DEPARTMENT                             14
DB2INST1             ACT                                    18
DB2INST1             EMPLOYEE                               42
DB2INST1             EMP_PHOTO                               8
DB2INST1             EMP_RESUME                              8
DB2INST1             PROJECT                                20
DB2INST1             PROJACT                                65
DB2INST1             EMPPROJACT                             73
DB2INST1             IN_TRAY                                 3
DB2INST1             ORG                                     8
DB2INST1             STAFF                                  35
DB2INST1             SALES                                  41
DB2INST1             STAFFG                                 35
DB2INST1             EMPMDC                              10000
DB2INST1             PRODUCT                                 4
DB2INST1             INVENTORY                               4
DB2INST1             CUSTOMER                                6
DB2INST1             PURCHASEORDER                           6
DB2INST1             CATALOG                                 0
DB2INST1             SUPPLIERS                               2
DB2INST1             PRODUCTSUPPLIER                         2
  22 record(s) selected.

Data Model:

At this point, I became curious to know how the data model looks like. This is where IBM’s Data Studio (a free download) came handy. We are not going to discuss about Data Studio in this blog post. Refer to this blog post by Dean Compher for details on Data Studio. Once you connect to the database in Data Studio, click on ‘Schemas’, right click on (Instance Owner ID) ‘DB2INST1’, point to ‘Show’ and ‘In Overview Diagram’. Here is a screenshot.. Give it a try.

Reverse Engineering Data Model for SAMPLE Database

Below is how the data model for SAMPLE database looks like. Click on the image (for more details) to enlarge it. This data model is quite impressive. It has number of:

  • Primary Key and Unique Key constraints, Indexes
  • User defined packages, Stored Procedures, Functions.
  • MQTs, Triggers, Views and XML objects

Sample Database

Database Size:

Below is how we could find the database size. In the command below, we are making a call to a stored procedure along with supplying few parameters. This command requires a database connection. From the output, the size of the database is 157294592 bytes (150 MB).

 $ db2 "call get_dbsize_info(?,?,?,-1)"

  Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2015-09-06-06.46.51.888839

  Parameter Name  : DATABASESIZE
  Parameter Value : 157294592

  Parameter Name  : DATABASECAPACITY
  Parameter Value : 173996523520

  Return Status = 0 

Going back to clean slate

What if you we have messed up data in the SAMPLE database and we would like to go back to a clean slate. The easiest way would be to ‘drop’ the database and re-create it. It takes only two commands.. Note that this is not a practical approach for databases in the real world that have application data. Use caution before you do this as the database cannot be recovered unless sufficient care is taken beforehand.

 $ db2 "drop database SAMPLE"
DB20000I  The DROP DATABASE command completed successfully.

 $ 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.

Conclusion

If you have not explored into what DB2’s SAMPLE database has to offer, check it out. It is a safe play ground to explore. If you find something in the SAMPLE database that you would like to share, I would appreciate you doing so in comments. Thank you for reading.

Advertisements

3 thoughts on “Creating a DB2 LUW (Linux, Unix and Windows) Sandbox database

  1. Pingback: DB2 Sample Database – Modifying storage path | db2talk

  2. Thank you for your helpful articles. Could you suggest an online resource that acts as a guide or learning path towards administering DB2 LUW. I’m only trying to achieve associate level skills not pro level. I have few years of DBA background in SQL server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s