DB2 Create Database takes long time

0
votes

I'm new to Db2 and just installed v11.5 on my Ubuntu 18.04.

I referred these two links for setup purpose:

IBM and DCON

I'm using DB2 CLI to create a database. On typing in create database <database_name> and press enter, it just stays there; there's no output.

I checked the db2diag.log as well, it stops at this:

2021-05-18-15.41.46.618309+330 E653248E505 LEVEL: Event PID : 29136 TID : 140104312022784 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SOURCE
APPHDL : 0-7 APPID: *LOCAL.db2inst1.210518101139 AUTHID : DB2INST1 HOSTNAME: Host EDUID : 23 EDUNAME: db2agent (instance) 0 FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000 START : DATABASE: SOURCE : ACTIVATED: NO

Tried 3-4 times; on one occasion I just let it be and it took around 30-40 mins but it created the database. I'm not sure if I'm missing out any initialization step.

Kindly guide.

System Spec: RAM: 16GB, CPU(s): 8, Model name: Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz

1
linuxdb2ubuntu-18.04
the create database speed depends on your resources : i/o performance (especially if these are virtualised/containerized), how much RAM is available to the instance, and your CPUs, and logging configuration. Please edit your question to add these details. If you are running in a container or VM it can also impact performance. - mao
@mao it's on my native machine. I'm not using any container/VM. I've updated the question with my system spec. - dev-eloper
What is your i/o configuration, your filesystem, spinning-disk or SSD , what is your lggging configuration. Your CPU is very slow if it is really 1GHz. - mao

1 Answers

2
votes

The time it takes to create your skeleton Db2-LUW database is mainly determined by your I/O and logging configuration, and whether or not you get swapping/paging. The CPU speed is less important than the I/O throughput for the create database action.

As an example: my Db2-LUW v11.5 on ubuntu 18.04 and 20.04, the create database completes with the following times as reported by the time tool (with zero paging) and no containerization/virtualization:

  • with nvme(ssd): around 2 minutes

  • with spinning disk ext4 4k sector size, 256mb cache sata3, 3.5inch 7200rpm: around 4 minutes

  • with spinning disk ext4 512byte sector size, 64mb disk cache sata3 2.5 inch 5400rpm : around 10 minutes.

If you have more than one physical drive and/or controller, it can help to put the transaction logs on a different drive / controller to the tablespaces.

So you can see that the performance varies greatly with the I/O configuration. You get what you pay for, and how you configure it.

For creation of other objects on the skelton database like tablespaces, tables, views, indexes, mqt's, routines etc, the performance will vary further, again dependent on your I/O and logging configuration.