Friday 19 April 2013

DB2 System Commands


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

No comments:

Post a Comment