his part depicts making, initiating and deactivating the databases with the related sentence structure.
Database architecture
A database is an assortment of Tables, Schemas, Bufferpools, Logs, Storage gatherings and Tablespaces cooperating to deal with database tasks productively.
Database directory
Database registry is a sorted out storehouse of databases. At the point when you make a database, all the insights regarding database are put away in a database registry, for example, subtleties of default stockpiling gadgets, arrangement documents, and brief tables list and so forth.
Segment worldwide index is made in the case organizer. This index contains all worldwide data identified with the database. This segment worldwide catalog is named as NODExxxx/SQLyyy, where xxxx is the information parcel number and yyy is the database token.
In the segment worldwide registry, a part explicit index is made. This catalog contains neighborhood database data. The part explicit catalog is named as MEMBERxxxx where xxxx is a part number. DB2 Enterprise Server Edition condition runs on a solitary part and has just a single part explicit registry. This part explicit registry is particularly named as MEMBER0000.
Partitioned global directory
Catalog Location : <instance>/NODExxx/SQLxxx
The parcel worldwide index contains database related documents as recorded underneath.
- Worldwide stop write-to-record occasion observing documents
- Table space data documents [SQLSPCS.1, SQLSPCS.2]
- Capacity bunch control documents [SQLSGF.1, SQLSGF.2]
- Impermanent table space holder records. [/capacity way//T0000011/C000000.TMP/SQL00002.MEMBER0001.TDA]
- Worldwide Configuration document [SQLDBCONF]
- History documents [DB2RHIST.ASC, DB2RHIST.BAK, DB2TSCHG.HIS, DB2TSCHG.HIS]
- Logging-related documents [SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2]
- Locking documents [SQLINSLK, SQLTMPLK]
- Programmed Storage compartments
Member specific directory
Index area :/NODExxxx/SQLxxxx/MEMBER0000
This index contains:
- Items related with databases
- Cradle pool data documents [SQLBP.1, SQLBP.2]
- Neighborhood occasion checking records
- Logging-related records [SQLOGCTL.LFH.1, SQLOGCTL.LFH.2, SQLOGMIR.LFH].
- Neighborhood arrangement records
- Gridlocks occasion screen document. The point by point halt occasions screen documents are put away in the database registry of the list hub in the event of ESE and parceled database condition.
Creating database
You can make a database in case utilizing the "Make DATABASE" order. All databases are made with the default stockpiling bunch "IBMSTOGROUP", which is made at the hour of making an occasion. In DB2, all the database tables are put away in "tablespace", which utilize their individual stockpiling gatherings.
The benefits for database are consequently set as PUBLIC [CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT], notwithstanding, if the RESTRICTIVE alternative is available, the benefits are not allowed as PUBLIC.
Creating non-restrictive database
This order is utilized to make a non-prohibitive database.
Syntax: [To create a new Database. ‘database_name’ indicates a new database name, which you want to create.]
db2 create database <database name>
Example: [To create a new non-restrictive database with name ‘one’]
db2 create database one
Output:
DB20000I The CREATE DATABASE command completed successfully.
Creating restrictive database
Prohibitive database is made on summoning this order.
Syntax: [In the syntax below, “db_name” indicates the database name.]
db2 create database <db_name> restrictive
Example: [To create a new restrictive database with the name ‘two’]
db2 create database two restrictive
Creating database with different user defined location
Make a database with default stockpiling bunch "IBMSTOGROUP" on various way. Prior, you conjured the order "make database" with no client characterized area to store or make database at a specific area. To make the database utilizing client characterized database area, the accompanying system 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’.]
db2 create database '<db_name>' on '<data location>' dbpath on '<db_path_location>'
Example: [To create database named ‘four’, where data is stored in ‘data1’ and this folder is stored in ‘dbpath1’]
db2 create database four on '/data1' dbpath on '/dbpath1'
Viewing local or system database directory files
You execute this order to see the rundown of catalogs accessible in the present example.
Syntax:
db2 list database directory
Example:
db2 list database directory
Output:
System Database Directory
Number of entries in the directory = 6
Database 1 entry:
Database alias = FOUR
Database name = FOUR
Local database directory =
/home/db2inst4/Desktop/dbpath
Database release level = f.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = SIX
Database name = SIX
Local database directory = /home/db2inst4
Database release level = f.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Activating database
This order fires up every single important help for a specific database with the goal that the database is accessible for application.
Syntax:[‘db_name’ indicates database name]
db2 activate db <db_name>
Example: [Activating the database ‘one’]
db2 activate db one
Deactivating database
Using this command, you can stop the database services.
Syntax:
db2 deactivate db <db_name>
Example: [To Deactivate database ‘one’]
db2 deactivate db one
Connecting to database
After creating a database, to put it into use, you need to connect or start database.
Syntax:
db2 connect to <database name>
Example: [To Connect Database one to current CLI]
db2 connect to one
Output:
Database Connection Information
Database server = DB2/LINUXX8664 10.1.0
SQL authorization ID = DB2INST4
Local database alias = ONE
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]
db2 get db cfg for <db_name> | grep -i restrict
Example: [To check if ‘one’ database is restricted or not]
db2 get db cfg for one | grep -i restrict
Output:
Restrict access = NO
Configuring the database manager and the databdb2 get dbm cfg se
Occasion arrangement (Database administrator design) is put away in a record named 'db2system' and the database related setup is put away in a document named 'SQLDBCON'. These records can't be altered straightforwardly. You can alter these documents utilizing devices which call API. Utilizing the order line processor, you can utilize these orders.
Database Manager Configuration Parameters
Syntax: [To get the information of Instance Database manager]
db2 get database manager configuration
OR
db2 get dbm cfg
Syntax: [To update instance database manager]
db2 update database manager configuration
OR
db2 update dbm cfg
Syntax: [To reset previous configurations]
db2 reset database manager configuration
OR
db2 reset dbm cfg
Database Configuration Parameters
Syntax: [To get the information of Database]
db2 get database configuration
OR
db2 get db cfg
Syntax: [To update the database configuration]
db2 update database configuration
OR
db2 update db cfg
Syntax: [To reset the previously configured values in database configuration
db2 reset database configuration
OR
db2 reset db cfg
Syntax: [To check the size of Current Active Database]
db2 "call get_dbsize_info(?,?,?,-1)"
Example: [To verify the size of Currently Activate Database]
db2 "call get_dbsize_info(?,?,?,-1)"
Output:
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2021-07-02-10.27.15.556775
Parameter Name : DATABASESIZE
Parameter Value : 105795584
Parameter Name : DATABASECAPACITY
Parameter Value : 396784705536
Return Status = 0
Estimating space required for database
To gauge the size of a database, the commitment of the accompanying variables must be thought of:
- Framework Catalog Tables
- Client Table Data
- Long Field Data
- Huge Object (LOB) Data
- List Space
- Impermanent Work Space
- XML information
- Log document space
- Nearby database index
- Framework documents
Checking database specialists
You can utilize the accompanying punctuation to check which database specialists are allowed to PUBLIC on the non-prohibitive database.
Step 1: connect to database with authentication user-id and password of instance.
Syntax: [To connect to database with username and password]
db2 connect to <db_name> user <userid> using <password>
Example: [To Connect “one” Database with the user id ‘db2inst4’ and password ‘db2inst4’]
db2 connect to one user db2inst4 using db2inst4
Output:
Database Connection Information
Database server = DB2/LINUXX8664 10.1.0
SQL authorization ID = DB2INST4
Local database alias = ONE
Step2: To verify the authorities of database.
Syntax: [The syntax below shows the result of authority services for current database]
db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid ('public','g'))as t
order by authority"
Example:
db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid ('PUBLIC','G'))as t
order by authority"
Output:
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL * * N * * N *
BINDADD * * Y * * N *
CONNECT * * Y * * N *
CREATETAB * * Y * * N *
CREATE_EXTERNAL_ROUTINE * * N * * N *
CREATE_NOT_FENCED_ROUTINE * * N * * N *
CREATE_SECURE_OBJECT * * N * * N *
DATAACCESS * * N * * N *
DBADM * * N * * N *
EXPLAIN * * N * * N *
IMPLICIT_SCHEMA * * Y * * N *
LOAD * * N * * N *
QUIESCE_CONNECT * * N * * N *
SECADM * * N * * N *
SQLADM * * N * * N *
SYSADM * * * * * * *
SYSCTRL * * * * * * *
SYSMAINT * * * * * * *
SYSMON * * * * * * *
WLMADM * * N * * N *
20 record(s) selected.
Dropping Database
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]
db2 drop database <db_name>
Example: [To drop ‘six’ database from instance]
db2 drop database six
Output:
DB20000I The DROP DATABASE command completed successfully
