DB2 Database
 

DB2 DATABASE LEVEL COMMANDS

 

CREATE DATABASE:

DB2 CREATE DATABASE TEST
DB2 CREATE DATABASE TEST ON /MYDIR
DB2SAMPL <LOCATION>  -- CREATE A SAMPLE DATABASE
DB2 CREATE DATABASE TEST USING CODESET IBM-1252 TERRITORY US

USING DATABASE DIRECTORIES:

DB2 LIST DATABASE DIRECTORY - To view list of all databases on system/server

DB2 LIST DATABASE DIRECTORY ON /HOME/DB2INST1 - To view list of databases residing in certian location (On AIX)

DB2 LIST DATABASE DIRECTORY ON E:  - Same as above ( On WINDOWS )

THE DATABASE DIRECTORY HAS THE FOLLOWING STRUCTURE:
     $DBHOME/INSTANCE/NODE0000/SQL00001

FOR EXAMPLE:
MYSERVER : /HOME/DB2INST1/DB2INST1/NODE0000 > ls
SQL00001/  SQL00002/  SQL00003/  SQLDBDIR/

CONFIGURING THE DATABASE:

DB2 GET DB CFG FOR <DBNAME>  or

DB2 GET DATABASE CONFIGURATION FOR <DBNAME>

DB2 UPDATE DATABASE CONFIGURATION FOR <DBNAME> USING <Parameter> <Value>

eg: DB2 UPDATE DATABASE CONFIGURATION FOR <DBNAME> USING LOCKTIMEOUT 10

DELETING A DATABASE:

DB2 DROP DATABASE <DBNAME>

 

 DB2 TABLESPACES

 

TABLESPACE - LOGICAL SPACE GROUPING CONTAINERS TO HOLD DB STUFF

TABLESPACE CONTAINERS (PHYSICAL) - DIRECTORIES, FILES, RAW DEVICES

There are 2 types of Tablespaces in DB2:

SMS - SYSTEM MANAGED SPACE - DIRECTORY CONTAINERS MANAGED BY THE OPERATIN SYSTEM. AUTOMATICALL CREATE A FILE FOR EACH NEW TABLE OR INDEX TO HOLD THE DATA - AS LONG AS THERE IS ENOUGH DISK SPACE. 

DMS - DATABASE MANAGED SPACE - MANAGED BY THE DATABASE (USE COMMAND 'ADD CONTAINER' TO CHANGE THEIR SIZE)

There are 3 DEFAULT TABLESPACES (CREATED WITH ONE CONTAINER EACH BY DEFAULT): 
SYSCATSPACE - SYSTEM CATALOG TABLES   ..../NODE0000/SQL00001/SQLT0000.0 
TEMPSPACE - TEMPORARY TABLES   ..../NODE0000/SQL00001/SQLT0001.0 
USERSPACE1 - USER TABLES  ..../NODE0000/SQL00001/SQLT0002.0

To Create A New Tablespace

DB2 CREATE TABLESPACE MYDMSSPACE MANAGED BY DATABASE USING (FILE 'FILE1' 1000, FILE 'FILE2' 2000)

DB2 CREATE TABLESPACE MYSMSSPACE MANAGED BY SYSTEM  USING ('DIRECTORY1', 'DIRECTORY2')

To Drop a Tablespace

DB2 DROP TABLESPACE <TABLESPACE_NAME>

To Check Information about Tablespaces from Catalog Tables (MetaData)

DB2 'SELECT * FROM SYSCAT.TABLESPACES' - 

 

DB2 TABLES, DATATYPES, INDEXES, VIEWS

TABLES:

SYSTEM TABLES (SYSCATSPACE) MAY HAVE ONE OF FOUR SCHEMA NAMES: 
SYSIBM - READ-ONLY SYSTEM TABLES (E.G. SYSIBM.SYSTABLESPACES, SYSIBM.SYSTABLES, ETC.) 
SYSCAT - READ-ONLY VIEWS ON THE SYSTEM TABLES (E.G. SYSCAT.TABLESPACES, SYSCAT.TABLES, ETC.) 
SYSFUN - UDF (USER DEFINED FUNCTIONS) 
SYSSTAT - HOLD STATISTICS (E.G. SYSSTAT.TABLES,  SYSSTAT.COLUMNS, SYSSTAT.INDEXES, ETC.)

To List Tables :

DB2 LIST TABLES

DB2 LIST TABLES FOR SYSTEM

DB2 LIST TABLES FOR SCHEMA DB2INST1

COMPARE:

DB2 'SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 1 ROWS ONLY'

DB2 'SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROWS ONLY'

DB2 "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME LIKE 'A%'"
 

DATABASE OBJECT SYSIBM SYSTEM TABLE SYSCAT SYSTEM TABLE VIEW SYSSTAT UPDATEABLE VIEW
TABLESPACE SYSTABLESPACES TABLESPACES
TABLE SYSTABLES TABLES TABLES
SCHEMA SYSSCHEMATA SCHEMATA
VIEW SYSVIEWS VIEWS
COLUMN SYSCOLUMNS COLUMNS COLUMNS
INDEX SYSINDEXES INDEXES INDEXES
DATA TYPE SYSDATATYPES DATATYPES
CHECK CONSTRAINT SYSCHECKS CHECKS
REFERENTIAL INTEGRITY SYSRELS REFERENCES
FUNCTION SYSFUNCTIONS FUNCTIONS FUNCTIONS
BUFFERPOOL SYSBUFFERPOOLS
BUFFERPOOLS

 

Different types of tables in DB2

USER TABLES - SIMPLY TABLES CREATED BY USERS (PERMANENT TABLES)
TEMPORARY TABLES:   

  • DERIVED TEMP. TABLES - CREATED DYNAMICALLY BY DB2 AS NEEDED IN TEMP. TABLESPACES. AUTOMATICALLY DROPPED AT THE END OF TRANSACTION.
  • DECLARED TEMPORARY TABLES (VERSION 7) - CREATED BY USER WITH THE DECLARE COMMAND WITH THE SCHEMA CALLED SESSION. 
  •  DB2 DECLARE GLOBAL TEMPORARYTABLE ... ( ????)

Datatypes in DB2

DB2 'SELECT TYPENAME FROM SYSCAT.DATATYPES'

TYPENAME
------------------
BIGINT           (8 BYTES)
BLOB             (BINARY LARGE OBJECT - UP TO 2 GB - VIDEO, SOUND & PICTURES)
BOOLEAN
CHARACTER   (CHAR(1) IS DEFAULT, CHAR(N), WHERE N = 1..254)
CLOB             (CHARACTER LARGE OBJECT - 32K .. 2 GB) - ALSO DBCLOB TO STORE 2-

                      BYTE   TEXTS (CHINESE, ETC.)
DATALINK
DATE             (4 BYTES)
DECIMAL        (DECIMAL(5,0) BY DEFAULT)
DOUBLE         (SIMILAR TO REAL,BUT 8 BYTES)
INTEGER         (4 BYTES)
LONG VARCHAR   (OLD TYPE, WHEN VARCHAR WAS SHORTER)
REAL              (4 BYTES)
REFERENCE
SMALLINT       (2 BYTES)
TIME              (3 BYTES)
TIMESTAMP    (10 BYTES)
VARCHAR       (1..32672 BYTES)

  17 RECORD(S) SELECTED.
DB2 ALSO HAVE OTHER DATA TYPES (e.g. USER-DEFINED TYPES)
 

DIFFERENCE BETWEEN DATE, TIME, AND TIMESTAMP TYPES:

DATE (4 BYTES)
TIME (3 BYTES) 
TIMESTAMP (10 BYTES)

SELECT CURRENT DATE FROM (VALUES 1) A
SELECT CURRENT TIMESTAMP FROM (VALUES 1) A
SELECT CURRENT TIME FROM (VALUES 1) A

SELECT CURRENT SERVER FROM (VALUES 1) A
SELECT USER FROM (VALUES 1) A 
DB2 "VALUES(USER)"

CREATE TABLE INST.TEST (D DATE, T TIME, TS TIMESTAMP) IN INST_4K

INSERT INTO INST.TEST VALUES (DATE(CURRENT TIMESTAMP), TIME(CURRENT TIMESTAMP), TIMESTAMP(CURRENT TIMESTAMP))

SELECT * FROM INST.TEST 

          D                     T                   TS
1       2002-04-29      09:33:53        2002-04-29 09:33:53.207283
--------------------------------------------

DATETIME ARITHMETIC:

LABELED:
  SELECT SALES_DATE, SALES_DATE + 1 YEAR + 2 DAYS FROM SALES

DATE: YYYYMMDD - DECIMAL(8,0)
  SELECT SALES DATE, SALES_DATE + 00010001. FROM SALES
  SELECT SALES_DATE, CHAR(SALES_DATE - '04/01/1994') FROM SALES

TIME:  HHMMSS - DECIMAL (6,0)
  SELECT ... TIME('12:23:10') - 011005.
               TIME('12:23:10') - '01:01:01'

TIMESTAMP:  YYYYMMDDHHMMSS.NNNNNN   DECIMAL(20,6)
  TIMESTAMP(20010301171230') - 00000000011005.000022
  CHAR( TIMESTAMP(20010301171230') - '2001-03-01-16.02.24.999978' )

USEFUL FUNCTIONS:
DATE(), DAY(), DAYS(),HOUR(), MINUTE(), MONTH(), SECOND(), TIME(), TIMESTAMP(), TIMESTAPM_ISO(),YEAR()

IDENTITY COLUMNS:

CREATE TABLE TEST_IDENTITY ( 
  MYID INT GENERATED ALWAYS AS IDENTITY,
  LNAME VARCHAR(25), 
  FNAME VARCHAR(20)
); 
INSERT INTO TEST_IDENTITY (LNAME, FNAME) VALUES ('L1','F1');
INSERT INTO TEST_IDENTITY  (LNAME, FNAME) VALUES ('L2','F2');
INSERT INTO TEST_IDENTITY  (LNAME, FNAME) VALUES ('L3','F3');
INSERT INTO TEST_IDENTITY  (LNAME, FNAME) VALUES ('L4','F4');

SELECT * FROM TEST_IDENTITY

NOTE: WHEN DEFINING IDENTITY COLUMN, YOU CAN ALSO PROVIDE ATTRIBUTES, FOR EXAMPLE:

  • START WITH NN
  • INCREMENT BY NN
  • MINVALUE NN
  • NO MINVALUE
  • MAXVALUE NN
  • NO MAXVALUE
  • CYCLE
  • NO CYCLE
  • CACHE NN
  • ORDER
  • NO ORDER

YOU CAN ALSO PROVIDE A IDENTITY "GENERATION-EXPRESSION"

NOT NULL , WITH DEFAULT:
CREATE TABLE INVENTORY (
  PRODUCTID INT NOT NULL WITH DEFAULT 12345,
  QUANTITY INTEGER,
   PRICE DECIMAL(4,2),
   DEPT SMALLINT WITH DEFAULT)

DB2 TABLE CONSTRAINTS:  PRIMARY KEY, FOREIGN KEY

 

DB2 ALTER TABLE SALARY ADD CONSTRAINT CHK_SALBONUS CHECK (SALARY + BONUS < 200000)

DB2 CREATE TABLE SALARY ( EMPLOYEEID SMALLINT NOT NULL, ...... ,  CONSTRAINT UEMPID UNIQUE(EMPLOYEEID))

INSERT RULE
DELETE RULES  (NO ACTION, RESTRICT, CASCADE, SET NULL)
UPDATE RULES (NO ACTION, RESTRICT)

CREATE TABLE TAB1 ( ID, ...)
CREATE TABLE TAB2 (ID, ...)
CREATE TABLE TAB3 ( 
   A .., 
   B ..,
   C ..,
   CONSTRAINT CA FOREIGN KEY(A) REFERENCES TAB1(ID), 
   CONSTRAINT CB FOREIGN KEY(B) REFERENCES TAB2(ID)) 

INDEXES:

DB2 CREATE INDEX  I1 ON TAB1 ( COL1 DESC)
DB2 CREATE INDEX  I2 ON TAB1 (COL1, COL2)
DB2 CREATE UNIQUE INDEX I1 ON TAB1(COL1)
DB2 CREATE INDEX I1 ON TAB1(COL1) CLUSTER
DB2 CREATE INDEX I1 ON TAB1(COL1) ALLOW REVERSE SCANS
DB2 ALTER INDEX ...
DB2 DROP INDEX ...

VIEWS:

DB2 "CREATE VIEW AS SELECT COL1 FROM TAB1 WHERE COL1 LIKE 'A%' "

BUFFERPOOLS - CACHE FOR DATABASE PAGES (SEE IN SYSCAT.BUFFERPOOLS TABLE)
LOG FILES - LOGGING CAN BE TURNED OFF TO SPEED UP OPERATIONS
 


 

DB2

AUTHENTICATION

ATTACH - GET ACCESS TO THE INSTANCE
CONNECT - GET ACCESS TO THE DATABASE
 

DB2 ATTACH TO DB2INST1 USER MYUSER USING MYPASSWORD
DB2 CONNECT TO MYDATABASE USER MYUSER USING MYPASSWORD
DB2 CONNECT TO MYDATABASE USER MYUSER USING MYPASSWORD NEW NEWPASS CONFIRM NEWPASS

CHANGING AUTH.TYPE OF THE DATABASE SERVER:

DB2 UPDATE DBM CFG USING AUTHENTICATION <AUTHENTICATION_TYPE>

AUTH.TYPES: 
  SERVER, SERVER_ENCRYPT, CLIENT, DCE, DCE_SERVER_ENCRYPT (DCE = DISTRIBUTING COMPUTER ENVIRONMENT), KERBEROS, KRB_SERVER_ENCRYPT DCS,              DCS_ENCRYPT (DCS = DATABASE CONNECTION SERVICES)

TRUST_ALLCLNTS PARAMETER - YES, NO, DRDAONLY

YES:

ON THE SERVER:
DB2 UPDATE DBM CFG USING AUTHENTICATION CLIENT
DB2 UPDATE DBM CFG USING TRUST_ALLCLNTS YES
DB2 UPDATE DBM CFG USING TRUST_CLNTAUTH SERVER

ON THE CLIENT:
DB2 CATALOG DATABASE MYDB AT NODE ND1 AUTHENTICATION CLIENT

NO:

ON THE SERVER:
DB2 UPDATE DBM CFG USING AUTHENTICATION CLIENT
DB2 UPDATE DBM CFG USING TRUST_ALLCLNTS NO
DB2 UPDATE DBM CFG USING TRUST_CLNTAUTH SERVER

ON THE CLIENT:
DB2 CATALOG DATABASE MYDB AT NODE ND1 AUTHENTICATION CLIENT

TRUST_CLNTAUTH PARAMETER - CLIENT, SERVER
 

CLIENT:

ON THE SERVER:
DB2 UPDATE DBM CFG USING AUTHENTICATION CLIENT
DB2 UPDATE DBM CFG USING TRUST_ALLCLNTS NO
DB2 UPDATE DBM CFG USING TRUST_CLNTAUTH CLIENT

ON THE CLIENT:
DB2 CATALOG DATABASE MYDB AT NODE ND1 AUTHENTICATION CLIENT

SERVER:

ON THE SERVER:
DB2 UPDATE DBM CFG USING AUTHENTICATION CLIENT
DB2 UPDATE DBM CFG USING TRUST_ALLCLNTS YES
DB2 UPDATE DBM CFG USING TRUST_CLNTAUTH SERVER

ON THE CLIENT:
DB2 CATALOG DATABASE MYDB AT NODE ND1 AUTHENTICATION CLIENT

GAINING AUTHORIZATION = GAINING GROUP MEMBERSHIP IN ONE OF 5 GROUPS:

SYSADM - INSTANCE KING,
SYSCTRL - INSTANCE CONTROL - ALMOST AS POWERFUL AS SYSADM, BUT NEEDS EXPLICITLY GRANTED PRIVILEGES,
SYSMAINT - INSTANCE MAINTENANCE, CAN NOT ACCESS USER DATA
DBADM - DATABASE-LEVEL AUTHORITY (DB - NOT INSTANCE).
LOAD - DATABASE-LEVEL AUTHORITY TO INSERT LARGE AMOUNTS OF DATA.

SYS* AUTHORITIES - CAN BE ASSIGNED ONLY TO GROUPS, ARE INSTANCE-LEVEL
DBADM, LOAD AUTHORITIES - CAN BE ASSIGNED TO GROUP AND USERS, ARE DATABASE-LEVEL
 

OPRATION SYSADM SYSCTRL SYSMAINT DBADM LOAD
UPDATE DBM CFG YES -- -- -- --
CONTROL WHICH GROUP HAVE SYSADM, SYSCTRL, SYSMAINT AUTHORITY YES -- -- -- --
CONTROL WHICH USERS OR GROUPS HAVE DBADM AUTHORITY YES -- -- -- --
CREATE/DROP DATABASE YES YES -- -- --
CREATE/DROP TABLESPACE YES YES -- -- --
BACKUP DATABASE/RESTORE/ROLLFORARD YES YES YES -- --
DB2START / DB2STOP YES YES YES -- --
UPDATE DATABASE CFG FILE YES YES YES --  --
GRANT/REVOKE DB PRIVILEGES YES -- -- YES --
GRANT CONTROL PRIVILEGEON AN OBJECT YES -- -- YES --
CREATE TABLE YES -- -- YES --
LOAD INTO TABLES YES -- -- YES YES
RUNSTATS YES YES YES YES YES

DB2 UPDATE DBM CFG USING SYSCTRL_GROUP DB2GRP
DB2UPDATE DBM CFG SYSMAINT_GROUP DB2MNT

DB2 GRANT DBADM ON DATABASE TO USER <USERNAME>
DB2 GRANT DBADM ON DATABASE TO GROUP <GROUPNAME>

DB2 REVOKE DBADM ON DATABASE FROM USER <USERNAME>
DB2 REVOKE DBADM ON DATABASE FROM GROUP <GROUPNAME>

DB2 GRANT LOAD ON DATABASE TO USER <USERNAME>
DB2 GRANT LOAD ON DATABASE TO GROUP <GROUPNAME>

DB2 REVOKE LOAD ON DATABASE FROM USER <USERNAME>
DB2 REVOKE LOAD ON DATABASE FROM GROUP <GROUPNAME>

PRIVILEGES - ARE STORED IN THE DATABASE SYSTEM CATALOG VIEWS:

SYSCAT.DBAUTH - DATABASES AUTHORITIES (DB2 GET AUTHORIZATION - WILL REPORT THE DATABASE AUTHORITIES OF THE CURRENT USER)
   (DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, LOAD)
SYSCAT.TABAUTH - TABLE AND VIEW PRIVELEGES
   (CONTROL, ALTER, DELETE, INDEX, INSERT, SELECT, REFERENCE, UPDATE)
SYSCAT.INDEXAUTH - INDEX PRIVILEGES
   (CONTROL)
SYSCAT.SCHMAAUTH - SCHEMA PRIVILEGES
   (ALTERIN, CREATEIN, DROPIN)
SYSCAT.PACKAGEAUTH - PACKAGE PRIVILEGES
   (CONTROL, BIND, EXECUTE)

HERE IS HOW TO GRANT/REVOKE THOSE PRIVILEGES:

DB2 GRANT SELECT ON MYTABLE TO PUBLIC

DB2 GRANT <DB_PRIVILEGE> ON DATABASE TO USER <USERNAME>
DB2 GRANT <DB_PRIVILEGE> ON DATABASE TO GROUP <GROUPNAME>

DB2 REVOKE <DB_PRIVILEGE> ON DATABASE FROM USER <USERNAME>
DB2 REVOKE <DB_PRIVILEGE> ON DATABASE FROM GROUP <GROUPNAME>

MORE EXAMPLES:
DB2 GRANT <PRIVILEGE> ON <TBL/VW NAME> TO USER <USERNAME>
DB2 GRANT CONTROL ON INDEX <INDEX_NAME> TO USER <USER_NAME>
DB2 GRANT <PRIVILEGE> ON SCHEMA <SCHEMA_NAME> TO USER <USER_NAME>
DB2 GRANT <PRIVILEGE> ON PACKAGE <PACKAGE_NAME> TO USER <USER_NAME>
2 REVOKE ALL PRIVILEGES ON TABLE MYTABLE FROM MYUSER

PRIVILEGES TO AN OBJECT ARE ALSO GRANTED IMPLICITLY WHEN SOMEBODY CREATES AN OBJECT. (DATABASE, TABLE, VIEW, ETC.).


 

CONCURRENCY

PACKAGE - EXECUTABLE FORM OF ONE OR MORE QUERIES - SYSCAT.PACKAGES
           A PACKAGE IS CREATED USING PREP AND BIND COMMANDS. YOU CAN SET LEVEL OF ISOLATION (OR CONCURRENCY) OF THE PACKAGE. THERE ARE 4 LEVELS:

UR (UNCOMMITTED READS),

CS (CURSOR STABILITY),

RS (READ STABILITY),

RR (REPEATABLE READ)


THE LOWEST ISOLATION (HIGHEST CONCURRENCY) IS UR, THE HIGHEST ISOLATION - RR. DEFAULT - CS.
 

DB2 PREP SOMETHING.SQC ISOLATION RS
DB2 BIND SOMETHING.BND ISOLATION RR
DB2 PREP SOMETHING.SQC BINDFILE USING SOMETHING.BND ISOLATION RS
DB2 BIND SOMETHING.BND

LOCKS


- MODE (STATE) - IN (INTENT NONE), IS (INTENT SHARE), NS (NEXT KEY SHARE), S (SHARE), IX (INTENT EXCLUSIVE), SIX (SHARE WITH INTENT EXCLUSIVE), U (UPDATE), NX (NEXT KEY EXCLUSIVE), NW (NEXT KEY WEAK EXCLUSIVE), X (EXCLUSIVE), W (WEAK EXCLUSIVE), Z (SUPER EXCLUSIVE)
- OBJECT,
- DURATION

DB2 LOCK TABLE TEST IN SHARE MODE
DB2 ALTER TABLE TEST LOCKSIZE TABLE
DB2 ALTER TABLE TEST LOCKSIZE ROW