DB2

Certification

table and index definition

db2 describe table item
db2 describe indexes for table item

db2 list tables;
db2 list tables for schema my_schema;
db2 list tables for all;

select * from syscat.tables;

load

db2 "load from pcefunc09.csv of del insert into pcefunc"

table and index reorg

# table reorg
reorg table ciginfo
reorg indexes all for table ciginfo

db2 reorg table db2inst1.item inplace
db2 "call sysproc.admin_cmd ('reorg table item inplace')"

db2 reorg table db2inst1.item
db2 "call sysproc.admin_cmd ('reorg table db2inst1.item')"

db2 "call sysproc.admin_cmd ('reorg indexes all for table db2inst1.item allow write access')"
db2 "reorg indexes all for table item allow write access cleanup only all on all dbpartitionnums"

db2 "reorg table item index PK_ITEM_ITEMID  allow read access on all dbpartitionnums" 
db2 "reorg table item index PK_ITEM_ITEMID  inplace allow write access on all dbpartitionnums" 

db2 "reorgchk current statistics on table db2inst1.item"
db2 reorgchk update statistics on table db2inst1.item 

runstats

runstats on table db2admin.ciginfo and indexes all

db2 "runstats on table db2inst1.item with distribution on all columns and detailed indexes all allow read access"
RUNSTATS ON TABLE DB2INST1.ITEM ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS

syscat data dictionary

SELECT tabschema, tabname,tableorg from where syscat.tables where tableorg = 'C';
DB2 "select tbspace from syscat.tables where tabschema='X1' and tabname='APR' with UR" 

db2 "select substr(tabschema,1,10) tabschema, substr(tabname,1,30) tabname from syscat.tables where tabname like 'SNAP%'"

SELECT tabname, bname FROM SYSCAT.tabdep WHERE dtype = '7';
select schemaname from syscat.schemata;

db2 "select snapshot_timestamp, db_status, substr(db_name,1,10) dbname, dbpartitionnum from sysibmadm.SNAPDB" dbpartitionnum  from sysibmadm.SNAPDB"

db2 "select UTILITY_TYPE, substr(UTILITY_DESCRIPTION,1,15), UTILITY_STATE, DBPARTITIONNUM from SYSIBMADM.SNAPUTIL"

db2 "select SNAPSHOT_TIMESTAMP, UTILITY_STATE, PROGRESS_START_TIME, PROGRESS_TOTAL_UNITS, PROGRESS_COMPLETED_UNITS,DBPARTITIONNUM  from SYSIBMADM.SNAPUTIL_PROGRESS"

db2 "select UTILITY_TYPE, substr(UTILITY_DESCRIPTION,1,15), UTILITY_STATE, DBPARTITIONNUM from SYSIBMADM.SNAPUTIL"

db2 "select SNAPSHOT_TIMESTAMP, UTILITY_STATE, substr(PROGRESS_DESCRIPTION,1,10) desc, PROGRESS_START_TIME, PROGRESS_TOTAL_UNITS, PROGRESS_COMPLETED_UNITS, DBPARTITIONNUM  from SYSIBMADM.SNAPUTIL_PROGRESS" 

db2 "SELECT SNAPSHOT_TIMESTAMP, DB_STATUS, substr(DB_NAME,1,10) DBNAME, DBPARTITIONNUM FROM TABLE(SNAP_GET_DB_V95('ITSODB'))" 

SELECT SUBSTR(FUNCSCHEMA,1,10) FUNCSCHEMA, SUBSTR(FUNCNAME,1,30) FUNCNAME FROM SYSCAT.FUNCTIONS WHERE FUNCNAME LIKE 'SNAP%' 


db2 "select * from sysibmadm.DB_HISTORY"

db2 "select * from table(SYSPROC.ADMIN_LIST_HIST())"
db2 "SELECT * FROM SYSIBMADM.LOG_UTILIZATION"

SELECT substr(grantor,1,8) AS grantor,
       SUBSTR(grantee,1,8) AS grantee,
       granteetype AS gtype,
       SUBSTR (tabschema,1,8) AS schema,
       SUBSTR (tabname,1,8) AS tabname,
       controlauth AS ctl,
       alterauth AS alt,
       deleteauth AS del,
       indexauth AS idx,
       insertauth AS ins,
       selectauth AS sel,
       refauth AS ref,
       updateauth AS upd
  FROM syscat.tabauth 
  WHERE grantee = 'EMMA';

SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE   ' FROM SYSCAT.TABAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX   ' FROM SYSCAT.INDEXAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN  ' FROM SYSCAT.COLAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA  ' FROM SYSCAT.SCHEMAAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER  ' FROM SYSCAT.PASSTHRUAUTH
ORDER BY GRANTEE, GRANTEETYPE, 3;

SYSUBM  table
SYSCAT  view
SYSSTAT stats

SYSCAT.TABLES
SYSCAT.INDEXES
SYSCAT.COLUMNS
SYSCAT.FUNCTIONS
SYSCAT.PROCEDURES

RUNSTATS ON TABLE "DB2"."ORG" ;

UPDATE SYSSTAT.INDEXES
SET NLEAF=-1,
NLEVELS=-1,
FIRSTKEYCARD=-1,
FIRST2KEYCARD=-1,
FIRST3KEYCARD=-1,
FIRST4KEYCARD=-1,
FULLKEYCARD=-1,
CLUSTERFACTOR=-1,
CLUSTERRATIO=-1,
SEQUENTIAL_PAGES=-1,
PAGE_FETCH_PAIRS='',
DENSITY=-1,
AVERAGE_SEQUENCE_GAP=-1,
AVERAGE_SEQUENCE_FETCH_GAP=-1,
AVERAGE_SEQUENCE_PAGES=-1,
AVERAGE_SEQUENCE_FETCH_PAGES=-1,
AVERAGE_RANDOM_PAGES=-1,
AVERAGE_RANDOM_FETCH_PAGES=-1,
NUMRIDS=-1,
NUMRIDS_DELETED=-1,
NUM_EMPTY_LEAFS=-1
WHERE TABNAME = 'ORG' AND TABSCHEMA = 'DB2';

authentication, authorization, user, group, role, grants, permission, privileges

pdf

-- super user and super group
super user: SYSADM, SYSCTRL, SYSMAINT, SYSMON
super group: SYSADM_GROUP, SYSCTL_GROUP, SYSMAIN_GROUP, SYSMON_GROUP

-- connect user group to a super group, all users in the snddba group tied to SYSADM authority
UPDATE DBM CFG USING SYSADM_GROUP snrdba
db2stop
db2start

db2 force applications all
db2stop force

GRANT SELECT ON TABLE ADM.ACCTABC TO USER BOB;

REVOKE INSERT On VIEW ADM.LEGERS FROM GROUP CLERKS;

grant select on table adm.taxcode to user xyz;

GRANT DBADM ON DATABASE TO USER sally;
REVOKE DBADM ON DATABASE FROM USER paul

GRANT LOAD ON DATABASE TO GROUP maint;
REVOKE LOAD ON DATABASE FROM GROUP maint;

connect

db2 connect
db2 connect to dbname
db2 connect to sample
db2 connect to itsodb
db2 connect to itsodb user db2inst1
CONNECT TO finance USER my_user_bob USING bobpsw;
db2 connect reset

script

db2 -tvf crdb.sql
db2 -tvf tscrt.sql
db2 -tvf unicode.ddl
db2 -tvf create_tables.sql
db2 -t -v -f xxx.sql -z yyy.log
db2 -td! -v -f xxx.sql -z yyy.log
db2 -td@ -v -f xxx.sql -z yyy.log
db2 -tvf sample.ddl > sample2.out

environment variables

set DBPATH=c:
set DBNAME=MYDB
set MEMORY=25

instance

db2ilist --> list db2 instance
db2icrt devinst
/opt/ibm/db2/V9.5/instance/db2icrt -s client db2vs
db2icrt -u devfenc devinst
db2idrop -f  instance_name
db2ilist

export DB2NODE=2
db2 terminate 
db2 add dbpartitionnum without tablespaces

db2stop
db2stop force

db2start
db2start dbpartitionnum 2
db2start dbpartitionnum 2 add dbpartitionnum hostname mensa port 1 without tablespaces

db2iupdt --> update oe patch an db instance

configuration information

db2 get admin cfg

db2 get admin cfg

           Admin Server Configuration
Authentication Type DAS                (AUTHENTICATION) = SERVER_ENCRYPT
DAS Administration Authority Group Name  (DASADM_GROUP) =
DAS Discovery Mode                           (DISCOVER) = DISABLE
Name of the DB2 Server System               (DB2SYSTEM) = TEDWAS
Java Development Kit Installation Path DAS   (JDK_PATH) = C:\Program Files\IBM\SQLLIB\java\jdk\
Java Development Kit Installation Path DAS   (JDK_64_PATH) =
DAS Code Page                            (DAS_CODEPAGE) = 0
DAS Territory                           (DAS_TERRITORY) = 0
Location of Contact List                 (CONTACT_HOST) =
Execute Expired Tasks                   (EXEC_EXP_TASK) = NO
Scheduler Mode                           (SCHED_ENABLE) = ON
SMTP Server                               (SMTP_SERVER) =
Tools Catalog Database                    (TOOLSCAT_DB) = TOOLSDB
Tools Catalog Database Instance         (TOOLSCAT_INST) = DB2
Tools Catalog Database Schema         (TOOLSCAT_SCHEMA) = TOOLSDB
Scheduler User ID                                = xtradba


-- dbm: GET DATABASE MANAGER CONFIGURATION
db2 get dbm cfg | grep "Node type"
db2 get admin cfg
db2 get dbm config | grep DIAGPATH
db2 get dbm config | grep _GROUP
db2 get dbm config | grep AUTHENTICATION
db2 get dbm cfg | grep SVCE

db2 update dbm config using DIAGPATH /db2diag

DB2 GET DB CFG
db2 get db cfg |findstr -i logarchmeth1
db2 get db config for itsodb | grep "NEWLOGPATH\|MIRRORLOGPATH\|Path to log files"
db2 get db config for itsodb | grep
db2 get db config for itsodb | grep -i automatic
db2 get db cfg for itsodb
db2 get db cfg for itsodb show detail

db2 "UPDATE DB CONFIG FOR itsodb USING MIRRORLOGPATH /db2log2/ITSODB"
db2 update db cfg
db2 update db cfg using logfilsiz 2000
db2 update db cfg for itsodb using DBHEAP 1200

# turning on archivelog
db2 update db cfg for itsodb using logarchmeth1 disk:/db2archive
db2 update db cfg using LOGARCHMETH1 DISK:D:  For windows drive D:
db2 get db cfg for itsodb | grep LOGARCHMETH1

db2 get db cfg for itso | grep Database
db2 update dbm cfg using DFTDBPATH /database

db2set registry variable

db2set DB2COMM=tcpip  --> no space around = 
db2set -all ---> list all variables
db2set -lr  ---> list all registry vaiables

db2set DB2COMM=TCPIP
db2set DB2DBDFT=ITSODB
db2set ANALYTICS=DB2_WORKLOAD

db2 list

db2 "list applications"

DB2 list tablespaces
db2 list tablespace containers for 4
db2 list tablespaces show detail

db2 list database directory
db2 list db directory  --> list db directory
db2 list node directory

DB2 list tables

DB2 LIST HISTORY BACKUP ALL FOR SAMPL
db2 list history backup all for itsodb

db2 list utilities
db2 list utilities show detail

db2 command

db2 ? list of all DB2 commands
db2 ?  get syntax help for a specific command, eg: "? list database"
db2 ? sqlnnnn get message and brief description of a specific SQLCODE
db2 ? db2nnnn get message and brief description of a DB2 error code

db2 interactive:
!  -- eg: !hostname
quit -- quit db2 interactive

db2 get instance
db2 force application all  or db2 force application (x,y), terminate old connections, don't stop new connection

SCHEMA

create schema my_schema_store authorization my_username;
set schema my_schema
select schemaname from syscat.schemata;

extract ddl

db2look is a handy tool that can be invoked from the command prompt and the Control Center. 
Extract database definition language (DDL) statements from database objects
db2look -d department -a -e -m -x -f -o db2look.sql
db2look -d department -z db2inst1 -xs -xdir /home/db2inst1 -o db2look.sql
db2look -d department -z db2inst1 -xs -xdir /home/db2inst1 -o db2look.sql
db2look -d sample -e -t staff org > staff_org.ddl
db2look -d sample -e -z db2 > db2.ddl
db2look -d sample -m > stats.dml

db2look -d sample -f>config.txt

db2 utilities

db2level: Show DB2 service level command
db2level: installation path, db2 version and fixpack level, instance width ( 32-bit or 64 bit ) 

db2ls: List installed DB products and features command

db2ls command can be used to list:
Where DB2 products are installed on your system and the DB2 product level

db2ls -- list the products installed on the server

db2ls -q -p -b /opt/ibm/db2/V9.5
db2ls -q -b baseInstallDirectory

Backup And Recovery

brass pdf

db2ckbkp  --> check backup image
db2ckbkp -h 

-- -----------
DB2 BACKUP DB SAMPL to D: COMPRESS  --> take offline backup
or DB2 BACKUP DATABASE SAMPL to D: COMPRESS  --> take offline backup

DB2 LIST HISTORY BACKUP ALL FOR SAMPL
-- -------------------------------------------------------------------

DB2 BACKUP DB SAMPL ONLINE to D: COMPRESS
DB2 BACKUP DB SAMPL ONLINE to D: COMPRESS INCLUDE LOGS

db2 backup db itsodb to /db2backup

db2 "backup db itsodb on all dbpartitionnums to /db2backup"

db2 "backup db itsodb tablespace (TBSPDMS_0) online to /db2backup"

db2 backup db itsodb online incremental to /db2backup

db2 backup db itsodb online incremental delta use tsm

db2 archive log for itsodb

db2 update db cfg for itsodb using TRACKMOD ON

db2 archive log for database  user  using 

# Up and Running with DB2 on Linux
# Assuming that database partition 0 is the catalog partition, the commands are:
db2_all '<<+0< db2 backup db itsodb to /db2backup'
db2_all '<<-0< db2 backup db itsodb to /db2backup' 

db2 "backup db itsodb on all dbpartitionnums online to /db2backup compress"

-- -----------

db2 restore db itsodb from /db2backup taken at 20080204081423

db2 "restore db itsodb from /db2backup taken at 20080206143831 logtarget /db2backup/logs"

db2 rollforward db itsodb to end of backup and stop

db2 "rollforward db itsodb to end of logs and stop"
db2 "rollforward db itsodb to end of backup overflow log path (/db2backup/logs)"
db2 "rollforward db itsodb stop"

db2 rollforward db itsodb query status

db2_all "db2 restore db itsodb from /db2backup taken at 20080201170437"

-- ------------
db2 "rollforward db itsodb to 2008-02-06-20.15.30 using local time"
db2 "rollforward db itsodb query status"

db2 "rollforward db itsodb stop"
db2 rollforward db itsodb stop

db2 "restore db itsodb tablespace (userspace1) online from /db2backup taken at 20080206145105"


db2 "rollforward db itsodb stop tablespace (userspace1) online "
db2 "rollforward db itsodb to 2008-02-06-22.51.24 tablespace (userspace1) online"
db2 "rollforward db itsodb stop tablespace (userspace1) online "  

-- ------------

db2 alter tablespace TSDATA1 dropped table recovery on

db2 backup db itsodb on all dbpartitionnums to "/db2backup"

db2pd and db2support

db2support: Problem analysis and environment collection tool command
db2support  -d sample -s -m   ------ default output file: detailed_system_info.html

db2pd: Monitor and troubleshoot DB2 database command

export DB2PDOPT="-agents"

db2pd tool for monitoring and troubleshooting DB2 problems
> -memsets

db2pd -memblock

db2pd -memb pid=159770

db2pd -tcbstats

db2pd -tablespaces

db2pd -db sample -dyn

db2pd -inst -alldbs

db2pd -db itsodb -transactions

db2pd -db itsodb -locks
db2pd -db sample -locks

db2pd -db itsodb -applications 1072

db2pd -db itsodb -logs

db2pd -db itsodb -fvp lam1

db2pd -db itsodb -storagepaths

db2pd -db itsodb -reorgs

db2pd -db itsodb -tcbstats 7 4

db2pd -applications

db2pd -app

db2pd -app -dyn

db2pd -agent

db2pdcfg -catch locktimeout count=1

db2pd -recovery

db2pd -transactions

db2pd -logs

db2pd -sysplex

db2pd -stack all
db2pd -stack 

db2pd -edus

db2 installation

db2_install 

DB2_db2inst1 60000/tcp

./db2setup -r /db2home/db2inst1/db2wse.rsp

db2licm -l                  --> list
db2licm -a db2wse_u.lic     --> add license file
db2licm -r db2ese           --> remove license file
db2 "select * from sysibmadm.env_feature_info"
db2licm -p db2consv concurrent
db2licm -c wsfs concurrent
db2licm -e db2ese HARD      --> enforcement policy
db2licm -e db2ese SOFT
db2licm -g        --> generate compliance report

db partition and db node

db2_all 'typeset -Z4 DB2NODE; mkdir -p /db/db2inst1/NODE$DB2NODE/ITSODB/SAMPLE_MPL'


db2_all 'db2 get db cfg for itsodb | grep LOGARCHMETH1'
db2_all 'typeset -Z4 DB2NODE; mkdir -p 
db2_all 'typeset -Z4 DB2NODE; mkdir -p /db2log1/ITSODB'
db2_all 'typeset -Z4 DB2NODE; mkdir -p /db2log2/ITSODB'

db2_all 'db2 connect to itsodb;db2 update db cfg for itsodb using NEWLOGPATH /db2log1/itsodb'

db2_all 'db2 connect to itsodb; db2 get db cfg for itsodb | grep Path'

db2_all '/db2home/db2inst1/database_backup.ksh db2inst1 itsodb'

db2_all 'db2 restore db itsodb "tablespace(tsdata1)"

file

diagnostic log (db2diag.log)

Error Message

SQL30082N  Attempt to establish connection failed with security 
reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001

DB2 LUW databases including VERSION 10.5 fixpack 5 with pureScale on IBM AIX

HADR

PureScale repq

DB2 9.0 or higher version.

DB2 DPF Database

DB2 Connect Gateway
Data Studio
Optim Performance Manager

BLU columnar DB