A database configuration file is created for each database. This file is called SQLDBCON prior to Version 8.2, and SQLDBCONF in Version 8.2 and later. The creation of this file is done for you.
This file configuration parameters that affect the use of the database, such as: database code page, collating sequence, DB2 database release level) , Parameters indicating the current state of the database such as backup pending flag, database consistency flag, roll-forward pending flag) and also Parameters defining the amount of system resources that the operation of the database might use (for example, buffer pool size, database logging, sort memory size).
Space requirements for log filesSpace requirements for log files varies depending on your needs and on configuration parameter settings.
You will require 56 KB of space for log control files. You will also need at least enough space for your active log configuration, which you can calculate as (logprimary + logsecond) × (logfilsiz + 2 ) × 4096
where:
logprimary is the number of primary log files, defined in the database configuration file
logsecond is the number of secondary log files, defined in the database configuration file; in this calculation, logsecond cannot be set to -1. (When logsecond is set to -1, you are requesting an infinite active log space.)
logfilsize is the number of pages in each log file, defined in the database configuration file
2 is the number of header pages required for each log file
4096 is the number of bytes in one page.
Roll-forward recovery If the database is enabled for roll-forward recovery, special log space requirements should be taken into consideration:
With the logarchmeth1 configuration parameter set to LOGRETAIN, the log files will be archived in the log path directory. The online disk space will eventually fill up, unless you move the log files to a different location.
With the logarchmeth1 configuration parameter set to USEREXIT, DISK, or VENDOR, a user exit program moves the archived log files to a different location. Extra log space is still required to allow for: – Online archived logs that are waiting to be moved by the user exit program – New log files being formatted for future use
CREATE DATABASE DATAB1 AUTOMATIC STORAGE YES
Example 1: Converting a database on UNIX or Linux operating systemsAssume the database EMPLOYEE is a nonautomatic storage database, and that /data1/as and /data2/as are the paths you want to use for automatic storage table spaces. To convert EMPLOYEE to an automatic storage database, use the following statement:
ALTER DATABASE EMPLOYEE ADD STORAGE ON ’/data1/as’, ’/data2/as’
Buffer poolsA buffer pool is an area of main memory that has been allocated by the database manager for the purpose of caching table and index data as it is read from disk. Every DB2 database must have a buffer pool.
Each new database has a default buffer pool defined, called IBMDEFAULTBP. Additional buffer pools can be created, dropped, and modified, using the CREATE BUFFERPOOL, DROP BUFFERPOOL, and ALTER BUFFERPOOL statements. The SYSCAT.BUFFERPOOLS catalog view accesses the information for the buffer pools defined in the database.
How buffer pools are used When a row of data in a table is first accessed, the database manager places the page that contains that data into a buffer pool. Pages stay in the buffer pool until the database is shut down or until the space occupied by the page is required by another page. Pages in the buffer pool can be either in-use or not, and they can be dirty or clean:
- In-use pages are currently being read or updated. To maintain data consistency, the database manager only allows one agent to be updating a given page in a buffer pool at one time. If a page is being updated, it is being accessed exclusively by one agent. If it is being read, it might be read by multiple agents simultaneously.
- ″Dirty″ pages contain data that has been changed but has not yet been written to disk.
- After a changed page is written to disk, it is clean and might remain in the buffer pool.
A large part of tuning a database involves setting the configuration parameters that control the movement of data into the buffer pool and the writing of data from the buffer out to disk. If not needed by a recent agent, the page space can be used for new page requests from new applications. Database manager performance is degraded by extra disk I/O.
What is my buffer pool size ?The sizes of all buffer pools can have a major impact on the performance of your database. Before you create a new buffer pool, resolve the following items:
- What buffer pool name do you want to use?
- Whether the buffer pool is to be created immediately or following the next time that the database is deactivated and reactivated?
- Whether the buffer pool should exist for all database partitions, or for a subset of the database partitions?
Relationship between table spaces and buffer pools
Each table space is associated with a specific buffer pool. IBMDEFAULTBP is the default buffer pool. The database manager also allocates these system buffer pools: IBMSYSTEMBP4K, IBMSYSTEMBP8K, IBMSYSTEMBP16K, and IBMSYSTEMBP32K (formerly known as the “hidden buffer pools”).
Table spaces
A table space is a storage structure containing tables, indexes, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system. Table spaces are stored in database partition groups.
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE ’/db2files/DMS1’ 10 M) AUTORESIZE YES
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE ’/db2files/DMS1’ 10 M) AUTORESIZE YES INCREASESIZE 5 M
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE ’/db2files/DMS1’ 10 M) AUTORESIZE YES INCREASESIZE 50 PERCENT
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE ’/db2files/DMS1’ 10 M) AUTORESIZE YES MAXSIZE 100 M
How table spaces are extendedCREATE TABLESPACE TS1 MANAGED BY DATABASE USING (FILE ’C:\TS1CONT’ 1000, FILE ’D:\TS1CONT’ 1000, FILE ’E:\TS1CONT’ 2000, FILE ’F:\TS1CONT’ 2000) EXTENTSIZE 4 AUTORESIZE YES
ALTER TABLESPACE TS REDUCE SIZE 5 PERCENT
db2 ALTER TABLESPACE
SWITCH ONLINE
ALTER TABLESPACE NAME_OF_TBLS AUTORESIZE YES
Schema
A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.
CREATE SCHEMA statement, with the current user or a specified authorization ID recorded as the schema owner. It can also be implicitly created when another object is created, if the user has IMPLICIT_SCHEMA authority.
For example, a user with DBADM authority creates a schema called C for user A: CREATE SCHEMA C AUTHORIZATION A User A can then issue the following statement to create a table called X in schema C (provided that user A has the CREATETAB database authority): CREATE TABLE C.X (COL1 INT)
When a database is created, if it is not created with the RESTRICTIVE option, all users have IMPLICIT_SCHEMA authority. With this authority, users implicitly create a schema whenever they create an object with a schema name that does not already exist. When schemas are implicitly created, CREATEIN privileges are granted which allows any user to create other objects in this schema. The ability to create objects such as aliases, distinct types, functions, and triggers is extended to implicitly-created schemas. The default privileges on an implicitly-created schema provide backward compatibility with previous versions.
A user with ACCESSCTRL or SECADM authority can change the privileges that are held by users on any schema. Therefore, access to create, alter, copy, and drop objects in any schema (even one that was implicitly created) can be controlled.
