It's no secret that most DBAs will be maintaining a long list of commands for specific tasks and it's no surprise that at times, DBA would still end up looking/googling for a specific command.
In this post, I would like to share commands which specifically a Db2 DBA uses to accomplish various tasks. I have tried my best to include as many commands as I can which I have used in various assignments of mine. I would also be updating this sheet continuously. All that readers have to do is hit a "Ctrl+F" and enter what they are looking for.
This post would cover commands related to Db2 for Linux, UNIX and Windows
Instance Commands
In this post, I would like to share commands which specifically a Db2 DBA uses to accomplish various tasks. I have tried my best to include as many commands as I can which I have used in various assignments of mine. I would also be updating this sheet continuously. All that readers have to do is hit a "Ctrl+F" and enter what they are looking for.
This post would cover commands related to Db2 for Linux, UNIX and Windows
Conventions
- Commands and headings will be in bold and blue color
- Few commands will have to be executed only by specific user (like Instance owner, root user etc) and be vigilant of a note
- db and database will be used interchangeably in some commands
- A location will be called as
- path on Linux/UNIX OS,
- drive on Windows OS.
- There will be few OS specific commands and look out for a special instruction
- /usr/local/bin/db2ls - to list various versions of Db2 LUW software installed.
- db2greg -dump - Alter and display the global registry
- db2ilist - to list the instances from current version of Db2
- db2level - to list the version and fixpack, bit info (32 bit or 64 bit) and location where Db2 is installed
- db2licm -l - to list license information
- db2licm -a <license_file> - to apply license
- db2licm -g <output_file> - to generate compliance report in the output file
- db2icrt -u <fenced_user> <instance_user> - to create an instance (to be executed by root user)
- db2idrop <instance_user> - to drop an instance (to be executed by root user)
- db2start - to start an instance
- db2stop - to stop an instance gracefully
- (db2start and db2stop will not work with Client isntances)
- ipclean - to stop all inter process communication and related processes are stopped/cleaned. Recommended to run immediately after db2stop
- db2_kill - to stop an instance forcefully (This will call ipclean implicitly)
- db2gcf -s - to check status of the Instance.
- If the output of this command is
- Available - then instance is up and running
- Operable - then instance is in stopped state
- ps -ef | grep -i db2sysc | grep -vi grep - to list all the active/running instances
- db2set -lr - to list all available Registry variables
- db2set -all - to list all configured Registry variables
- db2set <REGISTRY VARIABLE> -to view current value of given Registry variable
- db2set <REGISTRY VARIABLE>=<VALUE> -to allocate a value to Registry variable (no space should be there before and after = sign)
- db2set <REGISTRY VARIABLE>= -to unallocate value of given Registry variable
- cat ~<instance_user>/sqllib/ctrl/.fencedID - to view the fenced user of the Instance
- db2 "get dbm cfg" - to view Instance configuration file
- db2 "get dbm cfg show detail" - to view Instance configuration file with current and delayed values. This will need Instance attachment
- db2 "attach to <instace_name>" - to attach to an Instance
- db2 "update dbm cfg using <CFG_PARAM_NAME> <CFG_PARAM_VALUE>" - to update the value of a configuration parameter in Instance configuration file
- to Switch from one Instance to other
- su - <target_instance> - On Linux and UNIX OS
- set DB2INSTANCE=<target_instance> - On Windows OS
- ps -eo user,pid,lstart,cmd | grep -Ei "STARTED|db2sysc" | grep -vi grep -to get the start time of Db2 instance on Linux
- db2 "get snapshot for DATABASE MANAGER" | grep -i start - to get the start time of Db2 instance
Database level commands
- While establishing connection to database, Db2 instance will check if DB is active.
- If DB is active, connection will be established immediately.
- If DB is inactive, DB will be activated and then connection will be established.
- Hence, It is always recommended to activate all the local database(s) immediately after Instance is started to avoid delay in establishing "first connection" to the database.
- db2 "connect to <dbname>" - to connect to a Local database
- db2 "connect to <dbname> user <username>" - to connect to a Local/Remote database (use DB alias while connecting to remote database)
- db2 "connect to <dbname> user <username> using <password>" - to connect to a Local/Remote database (use DB alias while connecting to remote database) - not a recommended way as password is visible
- db2 "connect" - to check the connection status from current terminal
- db2 "get connection state" - to check the connection status from current terminal
- db2 "connect reset" - to close the connection
- db2 "terminate" - to close the connection along with associated back-end "db2bp" procecess
- db2 "force application(<appl_handle1>)" - to close specific connection
- db2 "force application(<appl_handle1>, <appl_handle2>..)" - to close specific connection(s)
- db2 "force application all" - to close all connections in given Instance
- db2 "list applications for db <db_name>" | awk '{print $3}' | grep -Ev "^$|Application|Name|----------" | paste -sd, - | awk '{print "force application("$0")"}' -to force all the applications connected to a particular database
- db2 "get db cfg for <dbname>" - to view Database configuration file of given Database
- db2 "get db cfg for <dbname> show detail" - to view Database configuration file with current and delayed values. This will need Database Connection
- db2 "update db cfg for <dbname> using <CFG_PARAM_NAME> <CFG_PARAM_VALUE>" - to update the value of a configuration parameter in Database configuration file
- db2 "activate db <dbname>" - to activate a database(meaning allocates required memory, primary transaction logs, and establishes system connections to db)
- db2 "deactivate db <dbname>" - to deactivate a database(meaning deallocates allocated memory, primary transaction logs, and closes system connections from db)
- db2 "call get_dbsize_info(?,?,?,-1)" -to get the size and capacity of database
Health check
set -o vi
grepp() { [ $# -eq 1 ] && perl -00ne "print if /$1/i" || perl -00ne "print if /$1/i" < "$2";}
db2 "list db directory" | grepp "Database name" | grep " Database name" | sort | uniq | awk '{print $4}' | while read dbname
do
db2 -v "connect to $dbname"
db2 -v "select substr(TABSCHEMA,1,15) TABSCHEMA, substr(TABNAME,1,30) TABNAME, CARD, FPAGES, NPAGES, STATUS,STATS_TIME,TYPE from syscat.tables where STATUS <> 'N' with ur"
db2 -v "select TBSP_ID, substr(TBSP_NAME,1,30) TBSP_NAME, substr(TBSP_STATE,1,50) TBSP_STATE,DBPARTITIONNUM,TBSP_UTILIZATION_PERCENT from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> 'NORMAL' with ur"
done
here we are checking the status and list tables and tablespaces whose state is not normal
List command
List command in Db2 has many facets. We can use list command to get a lot of information.
- db2 "list db directory" - to list System database directory (i.e. all the databases that can be accessed from current instance)
- db2 "list db directory on <path|drive>" - to list Local database directory (i.e. all the databases that have their files on a given Path or Drive)
- db2 "list node directory" - to list contents of Node directory (i.e. all other instance(s) apart from current instance - these instances can be on same Server or on remote Server)
- db2 "list dcs directory" - to list contents of Database connection Services directory (these are used to access databases on non distributed systems like z/OS etc)
- db2 "list active databases" - to list all the active databases along "user" connection count, Database path in the given instance
- db2 "list applications" - this will display all the user connections to the database with following details
- Auth Id (Authorization ID - the username used to establish connection to database)
- Application Name
- Appl. Handle (Unique number given to each connection established to database)
- Application Id
- DB Name
- # of Agents(Number of Agents)
- db2 "list applications show detail" - In addition to above "list applications" command, this command will display the following details
- Seq#
- Coordinating member number
- Coordinator pid/thread
- Status
- Status Change Time
- DB Path
Status means connection status and it indicates what is being performed. It can have following values.
- Connect Completed
- UOW Waiting
- UOW Executing
- Lock-Wait etc
- So what are these different types of Connections?
- It can be understood as 2 types of connections will be there
- Systems connections - these are established by Instance user to perform background tasks.
- Systems connections would look like this- *LOCAL.DB2.220123230641 - *LOCAL.DB2.YYMMDDHHMMSS
- User connections - these are established by Users using db2 "connect" stmt or perform activity like Backup etc.
- User connections can further be categorized into two types
- Local connections would look like - *LOCAL.db2inst1.220124001608 - *LOCAL.<instancename>.YYMMDDHHMMSS
- Remote connections would look like - 192.168.29.110.43292.240727173909 - A.B.C.D.nnnnn.YYMMDDHHMMSS (here A.B.C.D is IPv4 address)
- db2 "list tables" - this will display all the Tables, Views, Aliases and MQTs
- db2 "list tables for ALL" - this will display all the Tables, Views, Aliases and MQTs
- db2 "list tables for USER" - this will display all the Tables, Views, Aliases and MQTs created by USER currently connected to database
- db2 "list tables for SYSTEM" - this will display all the catalog/system tables
- db2 "list tables for SCHEMA <schema-name>" - this will display all Tables, Views, Aliases and MQTs in given schema
- db2 "list tablespaces" - this will display all the Tables, Views, Aliases and MQTs
- db2 "list tablespaces show detail" - this will display all the Tables, Views, Aliases and MQTs
Storage group related commands
- Command - to be updated soon
Bufferpool related commands
- db2 "select substr(BPNAME,1,18) BPNAME, BUFFERPOOLID,substr(DBPGNAME,1,10) DBPGNAME,NPAGES,PAGESIZE,ESTORE,NUMBLOCKPAGES,BLOCKSIZE,substr(NGNAME,1,20) NGNAME from SYSCAT.BUFFERPOOLS with ur" -to print/display the Bufferpools from current database
- db2pd -db <db_name> -bufferpools
- -to print/display the Bufferpools from given database(here in example, db is sample)
- db2 "select substr(TBSP_ID,1,10) tbspid, substr(TBSP_NAME,1,20)tbspname, int(TBSP_PAGE_SIZE) TS_PG_SIZE,DBPARTITIONNUM,substr(TBSP_TYPE,1,4) as TYPE,substr(TBSP_CONTENT_TYPE,1,5) CONTENT,TBSP_UTILIZATION_PERCENT TS_USE_PERCENT,int(TBSP_TOTAL_PAGES) TBSP_TOTAL_PAGES,int(TBSP_USABLE_PAGES) TBSP_USABLE_PAGES,int(TBSP_USED_PAGES) TBSP_USED_PAGES,int(TBSP_FREE_PAGES) TBSP_FREE_PAGES,int(TBSP_PAGE_TOP) TBSP_PAGE_TOP,case TBSP_AUTO_RESIZE_ENABLED when '1' then 'Enabled' when '0' then 'Disabled' else 'SMS' END AS AUTO_RESIZE, case TBSP_USING_AUTO_STORAGE when '1' then 'Enabled' when '0' then 'Disabled' END AS AUTO_STORAGE from SYSIBMADM.TBSP_UTILIZATION with ur" - to check for the tablespace utilization
- db2 -v "SELECT TBSP.TBSPACEID,SUBSTR(TBSP.TBSPACE, 1, 20) TBSPACE, TBSP.PAGESIZE, BP.BUFFERPOOLID, SUBSTR(BP.BPNAME, 1, 20) BPNAME FROM SYSCAT.TABLESPACES TBSP JOIN SYSCAT.BUFFERPOOLS BP ON TBSP.BUFFERPOOLID = BP.BUFFERPOOLID WITH UR" -to print Tablespaces along with their corresponding Bufferpools
Schema related commands
Creating schema
Schema are used for logical separation of database objects and
schemas can be created in two ways
- Explicitly - using "create schema" command
- db2 "create schema <new_schema_name>"
- Implicitly - provided the user has implicit schema creation privilege. If the given schema doesn't exist at the time of object creation and user has implicit schema create privilege, then schema will be created as per the value of current schema.
- db2 "values current schema" - to see value of current schema
- db2 "set current schema <new_schema_name>" - to change value of current schema
Dropping Schema
- db2 "drop schema <schema_name> restrict" - to drop a schema; restrict is mandatory in drop schema command to ensure that only empty schemas are dropped
Listing Schemas in Database
- db2 "select substr(SCHEMANAME,1,20) SCHEMANAME, substr(OWNER,1,20) OWNER, OWNERTYPE, substr(DEFINER,1,20) DEFINER, DEFINERTYPE,CREATE_TIME from syscat.schemata with ur" - to list all the schemas present in the database
Size of the Schema
- db2 " " - to calculate the size of the schema
Table related commands
- db2 "load query table schemaname.tablename" - to check status of the table
- db2 "SELECT substr(tabschema, 1, 20) tabschema,
substr(tabname, 1, 20) tabname
FROM syscat.datapartitions
GROUP BY tabschema,
tabname
HAVING COUNT(*) > 1
WITH UR" - to list range partitioned tables
- db2 "SELECT Substr(typename, 1, 20) TYPENAME,
Substr(definer, 1, 20) DEFINER,
Substr(c.tabschema, 1, 20) SCHEMA,
Substr(c.tabname, 1, 20) TABNAME,
Substr(c.colname, 1, 20) COLNAME
FROM syscat.columns c INNER JOIN syscat.tables t
ON c.tabschema = t.tabschema AND c.tabname = t.tabname
WHERE definer NOT IN ( 'SYSIBM', 'SYSSTAT', 'SYSTOOLS')
AND typename IN ( 'BLOB', 'CLOB', 'DBCLOB')
WITH UR" - to list tables having LOB columns
Index related commands
- Command - to be updated soon
View related commands
- Command - to be updated soon
Stored procedure related commands
- Command - to be updated soon
Transaction log related commands
- db2pd -db <dbname> -transactions -alldbpartitionnums| awk '$14>0 {print $2" "$13" "$14}' - to list the applications consuming most the transaction log space.
- db2 -v "select substr(db_name, 1, 20) DB_NAME,
log_utilization_percent,
total_log_used_kb,
total_log_available_kb,
total_log_used_top_kb,
dbpartitionnum
from sysibmadm.log_utilization
with ur" - to list the transaction log utilization and allocation details
Event monitor related commands
- db2 "select substr(EVMONNAME,1,20) EVMONNAME,AUTOSTART, event_mon_state(evmonname) as event_mon_state from syscat.eventmonitors with ur" -to know the event monitor name, autostart setting and state
- db2 "set event monitor <event_monitor_name> state 1" -to start event monitor
- db2 "set event monitor <event_monitor_name> state 0" -to stop event monitor
Disclaimer:
All the commands posted here were tested and were in working condition. Please use these commands as reference only, exercise extreme care while executing these commands and these should not be relied for any specific application.
No comments:
Post a Comment