IBM® Db2® Database on IBM Cloud Pak® for Data combines a proven, AI-infused, enterprise-ready data management system with an integrated data and AI platform built on the security-rich, scalable Red Hat® OpenShift® foundation. Derive insights with machine learning embedded into query processing. Where you want it. Stitch is a cloud-first, developer-focused platform for rapidly moving data. Hundreds of data teams rely on Stitch to securely and reliably move their data from SaaS tools and databases into their data warehouses and data lakes.

  • DB2 Tutorial
  • DB2 Useful Resources
  • Selected Reading

This chapter describes creating, activating and deactivating the databases with the associated syntax.

Database architecture

A database is a collection of Tables, Schemas, Bufferpools, Logs, Storage groups and Tablespaces working together to handle database operations efficiently.

Database directory

Database directory is an organized repository of databases. When you create a database, all the details about database are stored in a database directory, such as details of default storage devices, configuration files, and temporary tables list etc.

Partition global directory is created in the instance folder. This directory contains all global information related to the database. This partition global directory is named as NODExxxx/SQLyyy, where xxxx is the data partition number and yyy is the database token.

In the partition-global directory, a member-specific directory is created. This directory contains local database information. The member-specific directory is named as MEMBERxxxx where xxxx is a member number. DB2 Enterprise Server Edition environment runs on a single member and has only one member specific directory. This member specific directory is uniquely named as MEMBER0000.

Partitioned global directory

Directory Location : <instance>/NODExxx/SQLxxx

The partition-global directory contains database related files as listed below.

  • Global deadlock write-to-file event monitoring files
  • Table space information files [SQLSPCS.1, SQLSPCS.2]
  • Storage group control files [SQLSGF.1, SQLSGF.2]
  • Temporary table space container files. [/storage path//T0000011/C000000.TMP/SQL00002.MEMBER0001.TDA]
  • Global Configuration file [SQLDBCONF]
  • History files [DB2RHIST.ASC, DB2RHIST.BAK, DB2TSCHG.HIS, DB2TSCHG.HIS]
  • Logging-related files [SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2]
  • Locking files [SQLINSLK, SQLTMPLK]
  • Automatic Storage containers

Member specific directory

Directory location : /NODExxxx/SQLxxxx/MEMBER0000

This directory contains:

  • Objects associated with databases
  • Buffer pool information files [SQLBP.1, SQLBP.2]
  • Local event monitoring files
  • Logging-related files [SQLOGCTL.LFH.1, SQLOGCTL.LFH.2, SQLOGMIR.LFH].
  • Local configuration files
  • Deadlocks event monitor file. The detailed deadlock events monitor files are stored in the database directory of the catalog node in case of ESE and partitioned database environment.

Creating database

You can create a database in instance using the “CREATE DATABASE” command. All databases are created with the default storage group “IBMSTOGROUP”, which is created at the time of creating an instance. In DB2, all the database tables are stored in “tablespace”, which use their respective storage groups.

The privileges for database are automatically set as PUBLIC [CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT], however, if the RESTRICTIVE option is present, the privileges are not granted as PUBLIC.

Creating non-restrictive database

This command is used to create a non-restrictive database.

Syntax: [To create a new Database. ‘database_name’ indicates a new database name, which you want to create.]

Example: [To create a new non-restrictive database with name ‘one’]

Output:

Creating restrictive database

Restrictive database is created on invoking this command.

Syntax: [In the syntax below, “db_name” indicates the database name.]

Example: [To create a new restrictive database with the name ‘two’]

Creating database with different user defined location

Create a database with default storage group “IBMSTOGROUP” on different path. Earlier, you invoked the command “create database” without any user-defined location to store or create database at a particular location. To create the database using user- defined database location, the following procedure is followed:

Syntax: [In the syntax below, ‘db_name’ indicates the ‘database name’ and ‘data_location’ indicates where have to store data in folders and ‘db_path_location’ indicates driver location of ‘data_location’.]

Example: [To create database named ‘four’, where data is stored in ‘data1’ and this folder is stored in ‘dbpath1’]

Viewing local or system database directory files

You execute this command to see the list of directories available in the current instance.

Db2

Syntax:

Example:

Output:

Metabase Db2

Activating database

This command starts up all necessary services for a particular database so that the database is available for application.

Syntax:[‘db_name’ indicates database name]

Example: [Activating the database ‘one’]

Deactivating database

Using this command, you can stop the database services.

Syntax:

Example: [To Deactivate database ‘one’]

Connecting to database

After creating a database, to put it into use, you need to connect or start database.

Metabase

Syntax:

Example: [To Connect Database one to current CLI]

Output:

Verifying if database is restrictive

To check if this database is restrictive or not, here is the syntax:

Syntax: [In the following syntax, ‘db’ indicates Database, ‘cfg’ indicates configuration, ‘db_name’ indicates database name]

Example: [To check if ‘one’ database is restricted or not]

Output:

Configuring the database manager and the database

Instance configuration (Database manager configuration) is stored in a file named 'db2system' and the database related configuration is stored in a file named 'SQLDBCON'. These files cannot be edited directly. You can edit these files using tools which call API. Using the command line processor, you can use these commands.

Database Manager Configuration Parameters

Syntax: [To get the information of Instance Database manager]

OR

Syntax: [To update instance database manager]

OR

Syntax: [To reset previous configurations]

OR

Database Configuration Parameters

Syntax: [To get the information of Database]

OR

Syntax: [To update the database configuration]

OR

Syntax: [To reset the previously configured values in database configuration

OR

Syntax: [To check the size of Current Active Database]

Example: [To verify the size of Currently Activate Database]

Output:

Estimating space required for database

To estimate the size of a database, the contribution of the following factors must be considered:

  • System Catalog Tables
  • User Table Data
  • Long Field Data
  • Large Object (LOB) Data
  • Index Space
  • Temporary Work Space
  • XML data
  • Log file space
  • Local database directory
  • System files

Checking database authorities

You can use the following syntax to check which database authorities are granted to PUBLIC on the non-restrictive database.

Step 1: connect to database with authentication user-id and password of instance.

Syntax: [To connect to database with username and password]

Example: [To Connect “one” Database with the user id ‘db2inst4’ and password ‘db2inst4’]

Output:

Step2: To verify the authorities of database.

Syntax: [The syntax below shows the result of authority services for current database]

Example:

Output:

Metabase Ibm Db2

Dropping Database

Metabase Db2

Using the Drop command, you can remove our database from instance database directory. This command can delete all its objects, table, spaces, containers and associated files.

Syntax: [To drop any database from an instance]

Metabase Db2 Driver

Example: [To drop ‘six’ database from instance]

Output:





⇐ ⇐ Metabase Db2
⇒ ⇒ Chamath Palihapitiya Twitter