DB2 common command summary

#

6. Creating a database

When you create a database, complete the following tasks:

* Set all system catalog tables required for the database

* Assign database recovery logs

* Create a database configuration file, set the default value

* Bind database utilities and database

The definition of the initial database partition group

When you originally created a database, a database partition is created for all partitions specified in the db2nodes.cfg file. You can use

Add dbpartitionnum and Drop dbpartitionnum verify commands to add or remove other partitions.

Download three database partition groups:

* IBMCATGROUP used to accommodate Syscatspace tables, save system catalog table

* IBMTEMPGROUP for accommodating Tempspaces1 tablespace, save system temporary table

* IBMDEFAULTGROUP for accommodating UserSpace1 tables, default saving user tables and indexes

(It is best to restart DB2CLP after creating a new database)

EXAMPLE:

Create Database Personl

Catalog TableSpace

Managed by system using (path 'd: / pcatalog', 'E: / PCATALOG')

Extentsize 16 PrefetchSize 32

User Tablespace

Managed by database using (file 'd: / db2data / inion1' 5000,

FILE 'D: / DB2DATA / PERSION2' 5000)

Extensize 32 prefetchsize 64

Temporary TableSpace

Managed by System Using (Path 'f: / db2temp / personl')

With "Personnel DB for DSchiefer Co"

Create Database Sccrm Using CodeSet GBK Territory CN

# Delete the database

DROP DATABASE

# Database directory

* Local database directory (file SQLDBDIR in the directory of the node)

The local database directory file exists to define each path of the database (or "drive" in Win)

This directory that can be obtained from this location is included. This directory contains an entry. Contains information:

Database name, database alias, database note, name of the root directory of the database, other system information.

* System database directory (file SQLDBDIR in the directory in the instance)

For each instance of the Database Manager, there is a system database directory file, which is for

Each database on this instance contains an entry. Implicit catalogs when using CREATE DATABASE

Each database contains a message: the database name, database alias, database note, local database directory location,

Indicates that the database is an indirect indicator indicating that it is residing on the same machine with the system database directory file.

View local or system database directory files

List Database Directory on

List Database Directory

* Node directory

The database manager creates a node directory when catying the first database partition. To catalog the database partition,

Use the Catalog Node command. To display the contents of the local node directory, use List Node Directory

Create and maintain a node directory on each database client. For a client, you can save a

Or each remote workstation of multiple databases contains an entry. DB2 client uses this node directory

Communication endpoint information.

Catalog tcpip node my_node_name remote 10.10.10.10 Server 54321

Uncatalog node my_node_name

Catalog Database DB as my_data_alias at node my_node_name

# "Lightweight Directory Access Protocol" (LDAP) Directory Service

The directory service is a repository for resource information about multiple systems and services in a distributed environment; it

Provide clients and server access to these resources. Clients and servers will use directory services to find

How to access other resources.

LDAP is a method of accessing directory services for industry standards. Each database server instance is issued to LDAP,

And provide database information to the LDAP directory when creating a database. When the client is connected to the database, you can retrieve it from the LDAP directory.

Directory information of the server. No longer requires that each client stores directory information locally on each machine.

# Create a database partition group

You can create a database partition group using the CREATE DATABASE Partition Group statement. This statement specifies the table space

The container and table data will reside on a set of database partitions.

* Create a partition image for the database partition group.

* Generate partition image ID

* Insert the record into the following directory:

Syscat.dbpartitongroups, syscat.partitionmaps, syscat.dbpartitiongroupdef

Create Database Partition Group on dbpartitionnums (, )

# Create a table space

The physical storage device used by the table space is used to use the physical storage device used to store data or the logical container or table.

Create TableSpace

Managed by system

Using ('')

Create TableSpace

Managed by Database

Using (file '' )

DEVICE

* Designated partition group

Create TableSpace Plans in oddnodegroup

Managed by Database

Using (Device '/ dev / hdisk0' 1000, device '/ dev / n1hd01' 40000) on Node 1

(Device '/ dev / hdisk0' 10000, device '/ dev / n1hd03' 40000) on Node 3

(Device '/ dev / hdisk0' 1000, device '/ dev / n1hd05' 40000) on Node 5

Create Regular TableSpace Custtbs in Database Partition Group Customer, IN DATABASE, GROUP

PageSize 4 K Managed by Database Using

(File 'D: / TestDBTBS / Custtbs0_1' 5120) on dbPartitionnum (0)

Using

(File 'D: / Testdbtbs / Custtbs1_1' 5120) on dbpartitionnum (1)

Extentsize 16 Overhead 10.5 PrefetchSize 16

Create a single partition group in a multi-partition database.

Create Database Partition Group Single_TBS_GRP ON dbPartitionNum (0)

Create a single partition table space in a single partition group.

Create TableSpace Single_Part_TBS in Database Partition Group Single_TBS_GRP

Managed by Database Using (file 'd: / testdbtbs / salesle_part_tbs_1' 5120)

Use a character device in UNIX.

* Create a specific type of table space

Create a system temporary table space

Create System Temporary TableSpace TMP_TBSP

Managed by SystemUsing ('D: / TMP_TBSP', 'E: / TMP_TBSP')

Creating a system temporary table space in the partition database can only be generated in IBMTEMPGROUP

Create System Temporary TableSpace Tempsys_TBSP1

In Database Partition Group IBMTEMPGROUP

PageSize 4K Managed by System

Using ('D: / TestDBTBS / SYS_TEMP_0') on dbpartitionnum (0)

Using ('D: / TestDBTBS / SYS_TEMP_1') on dbpartitionnum (1)

Extentsize 16 Overhead 10.5 PrefetchSize 16

Create a user temporary table space

Create User Temporary TableSpace USR_TBSP

Managed by Database

Using (file 'd: / db2data / user_tzbsp' 5000,

FILE 'E: / DB2DATA / User_TBSP' 5000)

Creating a user temporary table space in a partition database can be generated in other partition groups in IBMTEMPGROUP.

Create User Temporary TableSpace User_Temp_TBSP

In Database Partition Group User_Temp_TBSGRP

PageSize 4K Managed by System

Using ('D: / TestDBTBS / User_Temp_0') on dbPartitionNum (0)

Using ('D: / TestDBTBS / User_Temp_1') on dbpartitionnum (1)

* Specify physical equipment

On Windows, specify a physical hard drive, use //./physicalDriven (N-0, 1, 2 ..)

Specify the logical drive to use //./n: (n is the logical drive letter in the system)

Specify a character device in UNIX.

#SMS When the object is increased, the file extends each time. If you need to increase the insert performance, you can consider enabling multiple page allocations, such as MDC tables.

Run db2empfa. For multi-partition databases, you must run this user for each partition. Once you are enabled, you cannot be prohibited.

#Dms

Single-partition table space size, 4kb - 64GB; 8KB-128GB; 16KB-256GB; 32KB-512GB

By default, each container retains a data block as overhead, the minimum size of the table space is 5 data blocks.

Three reserved for overhead. Two for user table data.

# Generate a table space on multiple nodes

Create TableSpace Ts1 Managed by Database Using

(Device '/ dev / rcont $ n' 20000)

Create TableSpace TS2 Management by Database Using

(File '/ DB2 / Containers / TS2 / Container $ N 100' 10000)

Create TableSpace TS3 Managed by System Using

('/ TS3 / CONT $ N% 2', '/ TS3 / CONT $ N% 2 2')

# 加手 空间 空间

Alter TableSpace Resource, ALTER TABLESPACE

Add (Device '/ dev / rhd9' 10000,

Device '/ dev / rhd10' 10000)

# Change the table space status

DB2 Alter TableSpace switch online # deletes table space

Drop tablespace

You can add a system temporary table space and then delete the old.

# Get Table Space Use Information

Get Snapshot for TableSpaces on Sample

# Get the Container in the table space

List tablepace containers for 0 (Tablespace_ID) show detail

# Create and fill the table

List the table:

List tables - List the current user's table

List table for all - Lists all tables defined in the database

List tables for schema schemaname - lists tables with specified mode

Describe Table Tablename - Display the structure of the specified table

Generate tables and primary keys

Create Table Department

(Deptno char (3) Not null,

DeptName Varchar (29) Not Null,

MGRNO Char (6),

AdmrDept Char (3) Not null,

Location char (16),

PRIMARY Key (dePTNO))

In resource

Exterior

CREATE TABLE EMPLOYEEEE

(Empno Char (3) Not Null Primary Key,

Firstnme varchar (12) Not null,

Lastname varchar (15) Not null,

WorkDept char (3),

Phoneno char (4),

Photo blob (10m) Not null,

Foreign Key Dept (WorkDept)

References Department On Delete No Action

In resource

On Delete No Action means that if the department has any employees, the department cannot be deleted.

Definition table check constraint

When you create or change the table, create a table check for the table by using the check constraint definition with the table association.

This constraint is automatically activated when the INSERT or UPDATE statement modifies the data in the table. Table Check constraints

Delete or Select has no effect. Check constraints cannot be related to the type table.

The constraint name cannot be the same as any other constraint specified in the same CREATE TABLE statement.

Specify a constraint name that automatically generates a unique identifier of 18 characters.

Table Check constraints are used to implement the uniqueness of the key or the data integrity rules that are not covered by integrity constraints.

Such as:

CREATE TABLE EMP_ACT

(Empno char (6) Not null,

Projno char (6) Not null,

Actno Smallint NOT NULL,

Emptime Decimal (5, 2),

EMSTDATE DATE,

Emendate Date,

Constraint actdates check (emstdate <= emendate))

In resource

Constrained EMSTDATE must be less than EMENDATE

Define information constraints

Information constraint is a rule that can be used by the SQL compiler, but the database manager does not force us to use it.

The SQL compiler includes a rewriting query phase, which transforms the SQL statement to might be optimized and improved

The access path of the required data is to improve query performance.

Create a column for a new table

Generation columns are defined in the basic table, in these columns, the stored value is calculated using the expression, not

Specify by inserting or updating. Can improve query performance, especially computationally complicated or inquiries

Multiple expression results.

CREATE TABLE T1 (C1 INT,

C2 Double,

C3 Double Generated Always AS (C1 C2)

C4 generated always as

(Case WHEN C1> C2 THEN 1 ELSE NULL END) Creating User Defining Temporary Table

The temporary table does not appear in the system directory, can not share this table

Declare Global Temporary Table GB1_TEMP

Like TMPLTAB1

On Commit Delete Roes

NOT Logged

IN USR_TBSP

Define the names of the columns used by this user temporary table and describe the names and descriptions of the columns of EMPLTABL.

Implicit definitions only include column names, data types, blanking feature, and column defaults. Other column properties, including

Unique constraint, foreign key constraint, trigger and index.

Define the identity of the new table

Automatically generate methods to ensure a unique numeric value for each row of the plug table.

Support only in the single partition database.

Create Table Test_Table (Col1 Int,

COL2 DOUBLE,

Col3 int not null generated always as identity

(Start with 100, Increment by 5))

Create a sequence

Sequence is a database object that allows automatic generation values. Sequences are particularly suitable for generating unique key values.

Unlike the identity list, the sequence does not cause the sequence to be associated with a particular table, and it is not bound to the only column, but only the table can be passed.

Submissions. It is only supported only in a single partition database.

The single partition database in a multi-partition environment is not.

Create Sequence Order_Seq

START WITH 1

INCREMENT BY 1

NomaxValue

Nocycle

Cache 24

Sequence of sequences

Insert Into Order (Orderno, Custno)

Values ​​(NextVal for Order_SEQ, 123456);

INSERT INTO LINE_ITEM (Orderno, Partno, Quantity)

Values ​​(Prevval For Order_Seq, 987654, 1)

Using nextVal, Prevval can use the same serial number in two different tables.

Table definition dimension

Reference Integrity Constraint Page 13 (17 pages of page)

The reference integrity constraint is defined when creating a table, or is defined using the ALTER TABLE statement after it.

The clause that establishes reference integrity is:

PRIMARY Key clause

UNIQUE CONSTRAINT clause

Foreign Key clause

REFERENCES clause

E.g:

Create Table Artists (ArtNo Int, ... Primary Key (ArtNO) Foreign Key DePt (WorkDept)

References Department On Delete No Action

Let us know all kinds of reference integrity rules:

Insert rules:

There is an implicit rule that cancels insert when not found the parent.

Delete rules:

RestRict: If you have a row, you can't delete your father.

CASCADE: Deleting the routing in the parent table automatically deletes any of the relevant lines in its dependent table.

NO Action: Forces the father of each sub-row after applying all other reference constraints.

Set Null: The foreign key field is set to null; other columns remain unchanged.

Update rule:

Restrict: If the row in the subordinate table matches the initial value of the key, the parent is not updated.

NO Action: If no row does not match the parent in the subordinate, the parent is not updated.

Increase the columns in the table

ALTER TABLE Employee

Add

Add unique restriction

ALTER TABLE Employee

Add constraint newid unique (Empno, Hiredate) Delete unique binding

ALTER TABLE Employee

Drop unique newid

Delete primary key

Alter Table

Drop Primary Key

Increase primary link

Alter Table Add Primary Key (col1, col2, ..)

Delete foreign key

Alter Table

DROP Foreign Key

Delete table check constraints

Alter Table

Drop check

Adding a table

Alter Table

Add constraint

Foreign Key

References Table_name

On Delete

On Update

Create a table in multiple tablespaces

Table data, any long column data associated with the table index and the table can be stored in the same table space, or

Place in different tablespaces. Can only use DMS.

CREATE TABLE

( )

IN

INDEX IN

Long in

Create a table in the partition database

Be careful to choose the appropriate partition construction, you can't keep it later. Moreover, any unique index must be defined as a supercoming of the partition key.

The size of the table is (partition number * partition size (4K is 64GB).

Create Table Mixrec (Mix_cntl Integer Not Null,

Mix_Desc char (20) Not null,

Mix_int integer not null)

In mixts12

Partitioning Key (MIX_INT) USING HASHING

A trigger

use:

Verify the input data

Generate value for newly inserted lines

Read it from other tables for cross-reference

Write other tables for audit tracking

CREATE TRIGGER

on

Create a user-defined function (UDF) or method

UDF extension and adds support for SQL's built-in functions, and can be used in any built-in function

Local use. Use two ways to create UDF:

External function, written in a programming language

The active function is generated from another existing function.

Three UDF: Scalar, return a single value answer.

Columns, return a single value answer from a set of similar (one column), such as AVG () can only define an active function.

Table, return a table to the SQL referenced to it, only reference table functions in the SELECT statement from the FROM clause.

UDF records in the syscat.functions and syscat.funcparms directory view.

User defined class UDT

UDT is a named data type created by the user in the database .udt can be single value type, it is with internal data types or

Structured Type Sharing a public representation, structured type has a naming property sequence, with each property has a type.

Structured Types can be another subtype of another structural type of type hierarchy.

Create a view

Create View (, , )

Select from with check option

The WITH CHECK OPTION clause indicates that any updated row or insertion of the view must be checked according to this view.

If it does not meet, it refuses it to increase data integrity.

Create View EMP_VIEW

Select Lastname as da00name,

Empno as da00num,

Phoneno

From Employee

Where workdept = 'A00'

WITH CHECK OPTION

Create a specific query table

The specific query table is a table defined based on the query results. Therefore, the specific query table usually contains a pre-calculated result.

These results are calculated according to the existing data in one or more tables referenced in the table definition. If the SQL compiler determines the query

This query will be executed for the specific query table when running at one or more basic tables when running at the specific query table.

Create an alias

Alias ​​is an indirect method of a reference table, an alias or view, so that the SQL statement can be independent of the limit name of the table or view.

Create Alias ​​Workers for Employee

Index, index expansion or index specification

Index is a list of row locations, sorted by one or more specified columns.

Index extensions are an index object that combines indexed indexes with structured types or single-value type columns.

The index specification is a metadata structure. It tells the optimizer alias that the data source object referenced (table or view)

Whether there is an index. Just the description of the index.

Index consultant, DB2ADVIS

The maximum number of indexes is 16, the maximum length is 1024 bytes.

Create Index on ()

Rename form or index

Table or index to be renamed cannot be a directory table or an index, summarize a table or an index, type table, and declared

The global temporary table and the name of the nickname.

And you cannot reference an existing table or index in any of the following objects:

View, trigger, reference constraint, summary table, existing reference

There is no check constraint in the table, and there is no other generation column other than the identity.

Rename Table . to

Rename Index . to

Indexes:

Is an ascending or descending (if not specified, the default is ascended)

Is unique, or unique (if not specified, the default is unique)

Compound

Used to perform cluster

Is two-way (this is controlled by Allow or Disllow Reverse Scans)

Includes other columns (this only applies to unique indexes).

Delete table

Drop Table

Delete index

Drop Index

Call the Performance Configuration Wizard by command line processor

Use AutoConfigure

Terminate all applications and database connections

DB2 Force Applicaitons All

# Authorization to the user

Grant Privilege ON Object-Type Object-Name

TO [{user | group | public}] Authorization-Name

[With grant option]

Grant Insert, Delete on Table Staff to User Rosita with Grant Option

# 消 用户 权利 权利 权利

Revoke Privilege ON Object-Type Object-Name

From [{user | group | public}] authorization-name