Setup with initial test data inserted in Db2 / DB container

Hello. This time, I will show you how to set up a database and insert test data smoothly using IBM's Db2 container.

This code is published on GitHub, so please refer to it. https://github.com/rikkyrice/setup-db2-container

Overview

I dropped the Db2 container, but This method is recommended for those who want to create tables and test data in the background at the start-up stage.

Please see the article below for how to drop the Db2 container. Set up a Db2 DB container and insert a little data

This article is a derivative of the above article and provides steps to create a custom container.

Development environment

Premise

Getting Started Let me introduce you. I think that the operation is the same for Windows, Mac, and Linux. I have a Mac environment at home, so I tried it, but it worked fine.

1. Various preparations

First of all, prepare everything.

Directory structure

/project


project
├─data
|    ├─users_insert.csv
|    ├─tweets_insert.csv
|    └─replys_insert.csv
├─sql
|   ├─users_create.sql
|   ├─tweets_create.sql
|   └─replys_create.sql
├─createschema.sh
├─Dockerfile
└─env.list

1.1. Download container image

Save the Db2 container image in docker's local repository. Execute the following command.

$ docker pull ibmcom/db2:11.5.4.0

You don't need anyone who has already downloaded it. The first time will take quite some time.

Confirm the existence.

$ docker images
REPOSITORY               TAG                            IMAGE ID            CREATED             SIZE
ibmcom/db2               11.5.4.0                       d6b3abc02d57        3 months ago        2.69GB

This time, we will use this container image as the base image.

1.2. Configuration information file preparation

When you run the container, you need to load the defined configuration file. This can be specified with the -e option, but it is recommended to put it in a file because it is easier to manage Git. I have prepared a sample below.

The description of this file is here.

env.list


LICENSE=accept
DB2INSTANCE=db2inst1
DB2INST1_PASSWORD=password
DBNAME=USERDB
BLU=false
ENABLE_ORACLE_COMPATIBILITY=false
TO_CREATE_SAMPLEDB=false
PERSISTENT_HOME=true
HADR_ENABLED=false
ETCD_ENDPOINT=
ETCD_USERNAME=
ETCD_PASSWORD=

1.3. SQL file preparation

This time, I will prepare the data assuming the creation of a tweet application.

A simple table definition is below.

user

Tweet

reply

I will write an SQL file based on the above table definition. This time, I prepared it with the following feeling.

users_create.sql


CREATE TABLE users (
    id VARCHAR(36) NOT NULL,
    name VARCHAR(40) NOT NULL,
    mail VARCHAR(100) NOT NULL,
    password VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY (
        id
    )
);

tweets_create.sql


CREATE TABLE tweets (
    id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    body VARCHAR(300),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY (
        id
    ),

    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

replys_create.sql


CREATE TABLE replys (
    id VARCHAR(36) NOT NULL,
    tweet_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    body VARCHAR(300),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (
        id
    ),

    FOREIGN KEY (tweet_id) REFERENCES tweets(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

1.4. Test data preparation

Prepare the test data. For test data, you can write an insert statement in a SQL file, but Since maintenance is difficult, this time I will write it in a CSV file and insert it.

The CSV file to be inserted is prepared below.

users_insert.csv


hogeeee,hoge,[email protected],hogehoge,2020-10-09-12.00.00.000000,2020-10-09-12.00.00.000000
fugaaaa,fuga,[email protected],fugafuga,2020-10-10-12.00.00.000000,2020-10-10-12.00.00.000000

tweets_insert.csv


dba11ffb-b8e0-642d-bb1b-4c8053bdb4bd,hogeeee,This is a test.,2020-10-09-12.00.00.000000,2020-10-09-12.00.00.000000
b193cb79-0e0c-85d9-2f0a-32d9774bb0aa,fugaaaa,This is a test.,2020-10-10-12.00.00.000000,2020-10-10-12.00.00.000000

replys_insert.csv


7e3991a6-d3da-252f-f14f-cfed35a512a7,b193cb79-0e0c-85d9-2f0a-32d9774bb0aa,hogeeee,I confirmed the test.,2020-10-11-12.00.00.000000
b2da92cf-6bd2-ac1c-618a-b36ef8eb94b1,dba11ffb-b8e0-642d-bb1b-4c8053bdb4bd,fugaaaa,I confirmed the test.,2020-10-11-12.00.00.000000

It's assumed that you have two users, each posting a tweet for testing, and each replying to that tweet.

1.5. Shell script

I added it in the article here, ibmcom / db2: 11.5.4.4 is behind the scenes when you do docker run First, the shell script /var/db2_setup/lib/setup_db2_instance.sh is executed. This shell script configures the Db2 instance based on the environment information and at the same time I am running a shell script called / var / db2_setup / include / db2_common_functions. This shell is setting up Db2. And finally

setup_db2_instance.sh


#!/bin/bash

.....abridgement.....

# If the /var/custom directory exists, run all scripts there. It is for products that build on top of our base image
if [[ -d /var/custom ]]; then
    echo "(*) Running user-provided scripts ... "
    for script in `ls /var/custom`; do
       echo "(*) Running $script ..."
       /var/custom/$script
    done
fi

.....abridgement.....

There is a description, In other words, the script in the / var / custom / directory inside the container is finally called. The shell script for data insertion is called here to make the initial settings.

But, By default, / var / custom does not exist, so you will have to create it yourself. This will be introduced in Creating a Dockerfile below.

Now let's create a table and write a shell script that issues instructions to insert data.

createschema.sh


#!/bin/bash

export PATH=/database/config/db2inst1/sqllib/bin/:$PATH

db2 connect to USERDB user db2inst1 using password

#Create table
db2 -tvf /var/custom/sql/users_create.sql
db2 -tvf /var/custom/sql/tweets_create.sql
db2 -tvf /var/custom/sql/replys_create.sql

#Insert data
db2 import from /var/custom/data/users_insert.csv of del insert into users
db2 import from /var/custom/data/tweets_insert.csv of del insert into tweets
db2 import from /var/custom/data/replys_insert.csv of del insert into replys

# Terminate
db2 terminate
touch /tmp/end.txt

First, I put it in my PATH so that I can use the db2 command. Then, execute the SQL files in order to create the table declared above.

What does db2 -tvf mean?

What does db2 import from $ {filename} of del insert into $ {tablename} mean?

1.6. Preparation of Dockerfile

Finally, create a Dockerfile to build the image of your custom Db2 container.

I think there are various settings, but I will strip it off quite a bit and make it a simple Dockerfile.

Again, the directory structure of the project is as follows.

/project


project
├─data
|    ├─users_insert.csv
|    ├─tweets_insert.csv
|    └─replys_insert.csv
├─sql
|   ├─users_create.sql
|   ├─tweets_create.sql
|   └─replys_create.sql
├─createschema.sh
├─Dockerfile
└─env.list

Dockerfile


FROM ibmcom/db2:11.5.4.0
RUN mkdir /var/custom
RUN mkdir /var/custom/sql
RUN mkdir /var/custom/data
COPY ./sql/*.sql /var/custom/sql/
COPY ./data/*.csv /var/custom/data/
COPY createschema.sh /var/custom/
RUN chmod 444 /var/custom/sql/*.sql
RUN chmod 444 /var/custom/data/*.csv
RUN chmod a+x /var/custom/createschema.sh

First, specify the dropped ʻibmcom / db2: 11.5.4.0` as the base image. We will customize this container.

By creating the / var / custom / directory as I mentioned earlier, The script specified there will be called during setup, so do mkdir. I made a dedicated directory for sql files etc.

I will copy the prepared file there. In addition, set the permissions to read-only. createschema.sh needs to execute a shell script, so give it execute permission.

Now you are ready to build your custom image.

2. Create custom container image

Next, create a customized container image using the prepared items. Everyone knows docker build.

This time, we will add the tag test-db: v1.0 to make it easier to identify.

$ docker build -t test-db:v1.0 .

Run the directory where the Dockerfile is. I think it will take a long time for the first time. If you have a cache, it will take about 1 second.

Check for existence when the build is complete.

$ docker images
REPOSITORY               TAG                            IMAGE ID            CREATED             SIZE
test-db                  v1.0                           186064b82d09        28 minutes ago      2.69GB

You can do it.

3. Custom container execution

Now let's launch a custom container.

$ docker run --name test-db --restart=always --detach --privileged=true -p 50000 --env-file env.list test-db:v1.0

The container name is test-db, and the configuration information is ʻenv.list`.

When I run it, I just spit out the container ID and I don't know when the setup is finished, so I'll take a look at the log.

$ docker logs -f test-db


SQL3109N  The utility is beginning to load data from file
"/var/custom/data/replys_insert.csv".
(*) Previous setup has not been detected. Creating the users...
(*) Creating users ...
(*) Creating instance ...
DB2 installation is being initialized.

 Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)

Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end

Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end

Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end

The execution completed successfully.

.........abridgement.........

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "2".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "2" rows were processed from the input file.  "2" rows were
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2

DB20000I  The TERMINATE command completed successfully.
(*) Running data ...
/var/db2_setup/lib/setup_db2_instance.sh: line 201: /var/custom/data: Is a directory
(*) Running sql ...
/var/db2_setup/lib/setup_db2_instance.sh: line 201: /var/custom/sql: Is a directory
          from "/database/data/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/".

2020-10-10-01.35.55.270890+000 E239025E525           LEVEL: Event
PID     : 18622                TID : 140605766231808 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : USERDB
APPHDL  : 0-7                  APPID: *LOCAL.db2inst1.201010013552
AUTHID  : DB2INST1             HOSTNAME: 99a855c216d7
EDUID   : 22                   EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:16544
STOP    : DATABASE: USERDB   : DEACTIVATED: NO

I think that a log like this will be spit out.

As an end flag DB20000I The TERMINATE command completed successfully. If you see this log, setup is complete.

If you go back a little to create the table,

CREATE TABLE users ( id VARCHAR(36) NOT NULL, name VARCHAR(40) NOT NULL, mail VARCHAR(100) NOT NULL, password VARCHAR(30) NOT NULL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY ( id ) )
DB20000I  The SQL command completed successfully.

If there is no error, The SQL command completed successfully is completed normally.

Regarding data insertion, you can see logs such as Number of rows committted = 2. This is a log that shows the status of the inserted data. This time, ʻinserted = 2 and rejected = 0, so everything is inserted normally. If this is rejected = 2`, then two data inserts have failed and need to be investigated.

If you make a mistake or want to run it again

#Forced deletion of execution container
$ docker rm -f test-db
test-db

#Delete container image
$ docker rmi test-db:v1.0

You can delete it cleanly. If you just want to re-execute the container, execute the above command, and if you change the SQL file or Dockerfile, execute both the upper and lower commands.

Supplement

if,

/bin/bash^M: bad interpreter: No such file or directory

If you get an error such as, and createschema.sh is not executed, Type the following command in an environment where sed can be typed (such as Git bash).

sed -i 's/\r//' createschema.sh

When a shell script saved in a Windows environment is executed in a Linux environment, The line feed code may be saved as \ r \ n, and you need to change it to \ n on Linux. Now delete the container again and try again.

4. Confirmation of the existence of the inserted data

Now, let's check if the inserted data is inserted properly with the expected value.

Actually enter the container, connect to the DB, and type the SELECT statement.

$ docker exec -it test-db bash -c "su - db2inst1"
Last login: Sat Oct 10 01:46:02 UTC 2020
[db2inst1@99a855c216d7 ~]$ db2 connect to userdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.4.0
 SQL authorization ID   = DB2INST1
 Local database alias   = USERDB

[db2inst1@99a855c216d7 ~]$ db2 "select * from users"
ID                                   NAME                                     MAIL                                                                                                 PASSWORD                       CREATED_AT                 UPDATED_AT
------------------------------------ ---------------------------------------- ---------------------------------------------------------------------------------------------------- ------------------------------ -------------------------- --------------------------
hogeeee                              hoge                                     [email protected]                                                                                        hogehoge                       2020-10-09-12.00.00.000000 2020-10-09-12.00.00.000000
fugaaaa                              fuga                                     [email protected]                                                                                        fugafuga                       2020-10-10-12.00.00.000000 2020-10-10-12.00.00.000000

  2 record(s) selected.

[db2inst1@99a855c216d7 ~]$ 

The data written in the csv file is inserted properly. To get out of the container, type ʻexit`.

Summary

How was it? It's easy, but I introduced how to set up with data inserted in Db2. This container image can be reused, so you can write another Dockerfile in another project and quickly create another custom image. You can easily build a database that meets the specifications of the project.

Also, since it is volatile, even if you insert data, if you delete it with docker rm -f, it can be easily initialized at the next startup, so it will be very easy to test.

I will investigate how to operate this database with GUI in the future.

I will also write an article that explains how to actually connect to this database and manipulate data using Go language.

References

Recommended Posts

Setup with initial test data inserted in Db2 / DB container
[Rails] Initial data creation with seed
Initial data input with [Rails] seed_fu!
Control test order in Junit4 with enumeration
Script to make yaml from CSV to put initial data in Rails with Fixtures
Test controller with Mock MVC in Spring Boot
Start SQL Server with Docker & register initial data
Launch Docker image with initial data injected with docker-compose
Visualize test methods running in TestNG with listeners