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
No comments:
Post a Comment