DB2 Database objects overviews

11 September,2010 by Jack Vamvas

Servers,Instances,Databases Hierarchy
---------------------------------------
System
Instance (Multiple)
Databases (Multiple)  


Instance Management
---------------------------------------
DB2INSTANCE environment variable defines the default instance.
Multiple instances are possible .

Commands used to manage\create instances

db2icrt
db2idrop
db2ilist
db2imigr
db2iupdt
db2start
db2stop

How to create a database
--------------------------------------
The simple syntax is (there are many more syntax options available):
CREATE DATABASE [DatabaseName]


There are some naming rules:
Only characters allowed are : a-z,A-Z,0-9,@,#,$,_
The first character must not be a name
"DBM","SYS","IBM" cannot be the initial sequence
Must be a unique name


Tasks performed when a database is created
1)The directory\subdirectory structure is created in the chosen loation.
If no location is specified the directory structure is created on the location specified in dftdbpath
2)Management , monitoring & recovery files are created

SQLBP.1 - buffer pool information
SQLBP.2 - backup copy of SQLBP.1
SQLDBCON - database configuration details
SQLDBCONF - backup copy of SQLDBCON
db2rhist.asc - recovery history file - (backups,table space changes,restores,reorgs)
db2rhist.bak - backup copy of SQLDBCONF
SQLTMPLK - temporary table spaces details
SQLSPCS.1 - table space details
SQLSPCS.2 - backup copy of SQLSPCS.1
SQLINSLK -  safety catch for database being assigned on only 1 instance
SQLSGF.1 - automatic storage storage path details
SQLSGF.2 - backup copy of SQLSGF.1
SQLOGCTL.LFH - active transaction log files details
SQLOGMIR.LFH - mirrored copy of SQLOGCTL.LFH

DB2EVENT - subdirectory   with deadlocks event monitor
SQLOGDIR - subdirectory  with S0000000.LOG , S0000001.LOG , S000002.LOG

3)Buffer pool is created named IBMDEFAULTBP

4)SYSCATSPACE table space is created - to store system catalog tables\views.By default is Database Managed (DMS)
USERSPACE1 - table space to store user defined objects. By default is Database Managed (DMS)
TEMPSPACE1 - temporary storage area . System Manged Space (SMS)

5)System Catalog tables \ views created
6)Database is cataloged
7)Database configuration file is initialised
8)Schemas are created
SYSIBM
SYSCAT
SYSSTAT
SYSFUN
The user SYSIBM is the owner of each schema
9)Packages needed for DB2 utilities are created. A binding process.
10)Designated users are granted authorities\privileges.
a)Database creator is granted - DBADM authority, CONNECT,CREATETAB,BINDADD,CREATE_NOT_FENCED,IMPLICIT_SCHEMA,LOAD
b)PUBLIC - BIND & EXECUTE to bound utilities
c)PUBLIC - SELECT on system catalog tables
d)PUBLIC - EXECUTE WITh GRANT on SYSFUN schema functions
e)PUBLIC - EXECUTE on  SYSIBM schema procedures
f)PUBLIC - USE privilege on USERSPACE1 tablespace
g)PUBLIC - IMPLICIT_SCHEMA,CREATETAB,BINDADD,CONNECT privileges are granted


Catalog\Uncatalog a Node (or Servers)
--------------------------------------------
The following are some options for cataloging a server with various communications protocols

CATALOG NAMED PIPE NODE
CATALOG TCPIP NODE
CATALOG LOCAL NODE
CATALOG LDAP NODE


Database connection method
--------------------------------------------
Prior to manageing objects on the new database , a connection must be made.Basic syntax example:
CONNECT to MYDB USER User1 USING User1password

Managing Database Objects
-------------------------------------------
Most database objects are covered in this list:
Aliases
Indexes
Packages
Schemas
Sequences
Stored Porcedures
Tables
Triggers
user-defined data types
User-defined functions
Views





Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 Database objects overviews

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright