db2Command Line
Utilities
>db2admin
- DB2 Administration Server
It is used to start and stop the
administration server.
Step1: su dasusr1
Step2: cd
/home/dasusr1/das/bin
Step3:./db2admin
start
>db2cc
– Starts the Control Center.
>db2cmd
- Open DB2 Command Window
Ex:
]$ db2cmd
>db2fs
- First Steps
Launches the
First Steps GUI, which contains links to the functions users need to begin
learning about and using DB2.
Ex:
]$ db2fs
>db2eva
-Event Analyzer
Starts the event analyzer,
allowing the user to trace performance data produced by DB2 event monitors that
have their data directed to files.
Ex: ]$ db2eva
>db2evmon
- Event Monitor
Productivity Tool
Formats
event monitor file and named pipe output, and writes it to standard output.
Syntax:
db2evmon –db <database-alias> evm <event-monitor-name>
'path-event-
monitor-target'
Ex: ]$ db2evmon -db sample -evm sample
>db2set
- DB2 Profile Registry Command. Displays, sets, or removes DB2 profile
variables. An External
environment
registry command that supports local and remote administration, via the DB2 Administration Server, of DB2's
environment variables stored in the DB2 profile registry.
Ex: ]$db2set DB2AUTOSTART=YES
>db2ckmig
- Database Pre-migration Tool
Verifies that a database can
be migrated. For detailed information about using this tool, see one of the
Quick Beginnings books.
Syntax: db2ckmig sample -l /home/db2inst1/mig.txt
Ex:
]$ db2ckmig sample
-l /home/itlokam/mig.txt
db2ckmig
was successful. Database(s) can be migrated.
>db2ckbkp
- Check Backup
This
utility allows the user to test the integrity of a backup image and determine
whether or not it can be restored.
Ex
: ]$ db2ckbkp -a
SAMPLE.0.itlokam.NODE0000.CATN0000.20060728215319.001
'
'
Image Verification Complete
– successful.
>db2ckrst
- It gives the restore command options
of the backup timestamp
Syntax:
db2ckrst -d <database name> -t <timestamp>
Ex: ]$ db2ckrst -d sample -t 20060728215319
Suggested restore order of images using timestamp 20060728215319
for database sample.
=================================
========================
Suggested restore
order of image using timestamp 20060728215319 for database sample.
=================================
=========================
>db2rfpen
- Reset rollforward pending state Command
Puts a database in
rollforward pending state. If you are using high availability disaster recovery
(HADR), the database is reset to a standard database.
Ex: ]$ db2rfpen on sample
>db2logsforrfwd
- List Logs Required for Rollforward Recovery Command
This
utility allows a user to find out which log files are required for a table
space rollforward operation.
Ex: bin]$ db2logsForRfwd
/home/itlokam/itlokam/NODE0000/SQL00001/DB2TSCHG.HIS
----------------------------------------------------------
Log file
number : S0000000.LOG
Tablespace
ID :
----------------------------------------------------------
Log file
number : S0000001.LOG
Tablespace
ID :
----------------------------------------------------------
Log file
number : S0000002.LOG
Tablespace
ID : 2 3
----------------------------------------------------------
Log file
number : S0000003.LOG
Tablespace
ID :
----------------------------------------------------------
Log file
number : S0000004.LOG
Tablespace
ID : 2
----------------------------------------------------------
Log file
number : S0000005.LOG
Tablespace
ID : 3
----------------------------------------------------------
Log file number : S0000006.LOG
Tablespace
ID : 3
----------------------------------------------------------
Log file
number : S0000007.LOG
Tablespace
ID : 3
----------------------------------------------------------
Log file
number : S0000008.LOG
Tablespace
ID : 0 3
----------------------------------------------------------
Log file
number : S0000009.LOG
>db2batch
- Benchmark Tool
It Reads SQL
statements from either a flat file or standard input, dynamically prepares and
describes the statements, and returns an answer set.
Syntax: db2batch -d database name
-f
filename
-a username/password
-r output file
Ex: ]$ db2batch -d sample -f / home/itlokam/sam.txt -r
/home/itlokam/res.txt
Bind is successful. Used bindfile:
/home/itlokam/sqllib/bnd/db2batch.bnd
Running in Embedded Dynamic mode.
>db2bfd
- Bind File Description Tool
Displays the contents of a
bind file. This utility, which can be used to Examine and to verify the SQL
statements within a bind file, as well as to display the precompile options
used to create the bind file, may be helpful in problem determination related
to an application's bind file.
Ex:
]$db2bfd -b
/home/db2inst1/sqllib/bnd/db2am.bnd
>db2level
- Show DB2 Service Level Command
Shows the current Version and
Service Level of the installed DB2 product. Output from this command goes to
the console by default.
Ex: ]$ db2level
DB21085I Instance "itlokam" uses
"32" bits and DB2 code release "SQL08020" with level identifier
"03010106".
Informational
tokens are "DB2 v8.1.0.64", "s040812",
"MI00086", and FixPak "7".
Product is
installed at "/opt/IBM/db2/V8.1".
>db2relocatedb
:It will rename the database
Step1: Create a
file with name relocate.cfg and add the following contents
DB_NAME=TESTDB,PRODDB
DB_PATH=/home/db2inst1
INSTANCE=db2inst1
NODENUM=0
Step2: db2relocatedb /home/db2inst1/relocate.cfg
>db2cfExp
- Connectivity Configuration Export Tool
Exports connectivity
configuration information to an Export profile, which can later be imported at
another DB2 Universal Database (UDB) workstation instance of similar instance type.
Ex: ]$ db2cfExp /home/db2inst1/profile.txt
>db2cfimp
- Connectivity Configuration Import Tool
Imports connectivity
configuration information from a file known as a configuration profile.
This
utility can be used to duplicate the connectivity information from another
similar instance that was configured previously. It is especially useful on
workstations that do not have the DB2CCA GUI tool installed, and in situations
where multiple similar remote UDB clients are to be installed, configured, and
maintained.
Ex: ]$ db2cfimp /home/db2inst1/profile.txt
>db2tbst
- Get Tablespace State. Accepts a hexadecimal table space state value, and
returns the
state. The state value is part of the output from LIST ABLESPACES.
Step1: Issue LIST TABLESPACES.
Step2: Copy the STATE value in that.
Step3:
db2tbst 0x0000
>db2uiddl
- Prepare Unique Index Conversion to V5 Semantics. Used to create DDL for
all indexes into a flat file.
Ex:
]$ db2uiddl
-d s > /home/db2inst1/sam.txt
>db2trc
- Trace
Controls the trace facility of a DB2
instance or the DB2 Administration Server.
Ex: ]$ db2trc dmp db2trc.dmp
>db2icrt
– Used to create Instance
Step1: Create a user db2inst2 and
add it to db2grp
Step2: Switch to root
Step3: cd /opt/IBM/db2/V8.1/instance
Step4: ./db2icrt -a server -u
db2fenc1 db2inst2
>db2start
- Start DB2.Starts the current database manager instance background
processes on a
single database partition
EX: ]$ db2start
>db2stop
- Stop DB2.Stops the current database manager instance.
EX:
]$ db2stop
>db2idrop
- Remove Instance
Step1: Switch to root
Step2: cd /opt/IBM/db2/V8.1/instance
Step3: ./db2idrop db2inst2
>db2ilist
- List Instances
Step1: Switch to root
Step2: cd /opt/IBM/db2/V8.1/instance
Step3: ./db2ilist
>db2imigr
– To Migrate Instance from lower version to higher version
Ex: ]$ db2imigr db2inst1
db2viper1
>db2iupdt
- Update Instances
Step1: Switch to root
Step2: cd /opt/IBM/db2/V8.1/instance
Step3: ./db2iupdt db2inst1
>db2iset – It is used to set the registry variables
for db2
instance]# ./db2iset
DB2NTNOCACHE=ON
DB2COMM=TCPIP
DB2AUTOSTART=YES
[root@ITLSYS64 instance]# ./db2iset
DB2AUTOSTART=YES
[root@ITLSYS64 instance]# ./db2iset
DB2NTNOCACHE=ON
DB2COMM=TCPIP
DB2AUTOSTART=YES
>db2isetup - Creation of instance through GUI
Ex: instance]#
./db2isetup
>db2advis
- DB2 Index Advisor
Advises users on what indexes to
create for one or more SQL statements.
syntax:
db2advis -d
dataname
-w
workload name
-s statement
-i filename
-a userid
-l disk limit
-passwd password
-t maxadvisetime
Ex: ]$ db2advis -d sample -i
/home/db2inst1/db2advis.in -t 5
Using user id as default
schema name. Use -n option to specify schema
Explain tables not set
up properly for schema ITLOKAM
The insert into the
ADVISE_INSTANCE table has failed.
0 solutions were
evaluated by the advisor
exiting with error code
[-219]
db2audit
- Audit Facility Administrator Tool
Step1:
Configuring the audit facility.
db2audit configure scope all status
both
Step2: Starting
the audit facility.
db2audit start
Step3: Checking
the log file
cd /home/db2inst1/sqllib/security
Open db2audit.log
Check for the logs
Step4: Stopping
the audit
db2audit stop
>db2empfa
- Enable Multi-page File Allocation
Enables the
use of multi-page file allocation for a database. With multi-page file
allocation enabled for SMS table spaces, disk space is allocated one Extent
rather than one page at a time.
Ex:
]$ db2empfa
sample
>db2ldcfg
- Configure LDAP Environment. Configures the Lightweight Directory Access
Protocol (LDAP)
user distinguished name (DN) and password for the
current logon user in an LDAP environment
using an IBM LDAP client.
Syntax:
db2ldcfg -u <username> -w<password> -r
Ex:
>db2licm
- License Management Tool
Performs
basic license functions in the absence of the Control Center. Adds, removes,
lists, and modifies licenses and policies installed on the local system.
-a: Adds a
license for a product.
-e: Updates the
enforcement policy on the system. Valid
values are: HARD and SOFT.
-p: Updates the
license policy type to use on the system.
The keywords CONCURRENT, REGISTERED, or CONCURRENT REGISTERED can be
specified. Specify OFF to turn off all
policies.
-r: Removes the
license for a product.
-u: Updates the
number of user entitlements that have been purchased.
-c: Updates the
number of connector entitlements that have been purchased.
-n: Updates the
number of entitled processors.
-l: Lists all the
products with available license information, including the product identifier.
-v: Displays
version information.
-? Displays help information.
Ex:
]$ db2licm
-a db2ese.lic
db2licm –v : o/p
8.2
>db2sql92
- SQL92 Compliant SQL Statement Processor. Reads SQL
statements from either a flat file or standard input, dynamically
describes and prepares the statements, and returns an answer set. Supports concurrent connections
to multiple databases.
Step1: Create a
file with name statements.sql
Step2: Add the
SQL statements
Select * from
employee;
Step3: db2sql92
-d s -f /home/db2inst1/statements.sql -r /home/db2inst1/response.sql
>db2mtrk
- Memory Tracker Command. It will give the memory
details of db2 instance
and database
Ex: ]$ db2mtrk -i -d -v -r 10
Tracking Memory on:
2006/07/28 at 23:41:38
Memory for
instance
Database Monitor Heap is of size 163840
bytes
Other Memory is of size 1163264 bytes
Total: 1327104 bytes
Memory for
database: SAMPLE
Backup/Restore/Util Heap is of size 16384
bytes
Package Cache is of size 638976 bytes
Catalog Cache Heap is of size 196608 bytes
Buffer Pool Heap is of size 838631424 bytes
Buffer Pool Heap is of size 655360 bytes
Buffer Pool Heap is of size 393216 bytes
Buffer Pool Heap is of size 262144 bytes
Buffer Pool Heap is of size 196608 bytes
Lock Manager Heap is of size 491520 bytes
Database Heap is of size 3473408 bytes
Other Memory is of size 0 bytes
Total: 844955648 bytes
Tracking Memory
on: 2006/07/28 at 23:41:48
>db2look
- DB2 Statistics and DDL Extraction Tool
Extracts the required DDL (data definition
language) statements to reproduce the database objects of a production database on a
test database. db2look generates the DDL statements by object
type
-d: Database Name: This must be specified
-e: Extract DDL file needed to duplicate
database
-xs: Export XSR objects and generate a script
containing DDL statements
-xdir: Path name: the directory in which XSR
objects will be placed
-u: Creator ID: If -u and -a are both not
specified then $USER will be u sed
-z: Schema name: If -z and -a are both
specified then -z will be ignored
-t: Generate statistics for the specified
tables
-tw: Generate DDLs for tables whose names
match the pattern criteria (wil dcard characters) of the table name
-h: More detailed help message
-o: Redirects the output to the given file
name
-a: Generate statistics for all creators
-m: Run the db2look utility in mimic mode
-r: Do not generate RUNSTATS statements for
mimic
-l: Generate Database Layout: Database partition
groups, Bufferpools and Tablespaces
-x: Generate Authorization statements DDL
excluding the original definer of the
object
-xd: Generate Authorization statements DDL
including the original definer of the
object
-f: Extract configuration parameters and
environment variables
-td: Specifies x to be statement delimiter
(default is semicolon(;))
-i: User ID to log on to the server where the
database resides
-w: Password to log on to the server where the
database resides
-noview: Do not generate CREATE VIEW ddl
statements
-wrapper: Generates DDLs for federated objects
that apply to this wrapper
-server: Generates DDLs for federated objects
that apply to this server
-nofed: Do not generate Federated DDL
-fd: Generates db2fopt statements for opt_buffpage
and opt_sortheap along with other cfg
and env parameters.
-v: Generate DDL for view only, this option is
ignored when -t is specif ied
-dp: Generate DROP statement before CREATE
statement
-ct: Generate DDL Statements by object creation
time
Ex: ]$db2look
-d s -e -o /home/db2inst1/script.sql
>db2pd:
As it’s a very important command line utility Refer to IBM site
