|
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> |
|
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
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.).
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
|