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
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.
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.
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
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.
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=
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
);
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.
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?
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.
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.
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.
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.
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`.
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.
Recommended Posts