List DB Directory

Jul10

=============================================================
List DB directories
=============================================================

Lists the contents of the system database directory. If a path is specified, the contents of the local database directory are listed.

[db2inst1@mercury ~]$ db2 list db directory
[db2inst1@mercury ~]$ db2 list database directory
[db2inst1@mercury ~]$ db2 list node directory

ScreenShot : List DB directories

[db2inst1@mercury ~]$ db2 list database directory on /home/db2inst1

These fields are identified as follows:
Database alias
The value of the alias parameter when the database was created or cataloged. If an alias was not entered when the database was cataloged, the database manager uses the value of the database-name parameter when the database was cataloged.
Database name
The value of the database-name parameter when the database was cataloged. This name is usually the name under which the database was created.
Local database directory
The path on which the database resides. This field is filled in only if the system database directory has been scanned.
Database directory/Database drive
The name of the directory or drive where the database resides. This field is filled in only if the local database directory has been scanned.
Node name
The name of the remote node. This name corresponds to the value entered for the nodename parameter when the database and the node were cataloged.
Database release level
The release level of the database manager that can operate on the database.
Directory entry type
The location of the database:
• A remote entry describes a database that resides on another node.
• An indirect entry describes a database that is local. Databases that reside on the same node as the system database directory are thought to indirectly reference the home entry (to a local database directory), and are considered indirect entries.
• A home entry indicates that the database directory is on the same path as the local database directory.
• An LDAP entry indicates that the database location information is stored on an LDAP server.

ScreenShot : List DB directories with database directory

Get DB2 Authorizations

Jul10

=============================================================
Get DB2 Authorizations
=============================================================

To Report the authorities of the current user from values found in the database configuration file and
the authorization system catalog view (SYSCAT.DBAUTH). use “db2 get authorizations”

[db2inst1@mercury ~]$ db2 get authorizations

ScreenShot : Get the Db2 Authorizations

NOTE : The GET AUTHORIZATIONS command does not display whether or not the current user holds SECADM authority. To find out who holds SECADM authority, use the following query:

SELECT GRANTEE FROM SYSCAT.DBAUTH
WHERE SECURITYADMAUTH = ‘Y’

List DB2 History

Jul9

=============================================================
List DB2 History
=============================================================

To display the events for the specific database use the “db2 list history” command

[db2inst1@mercury ~]$ db2 list history all for SAMPLE

ScreenShot : Lists the DB2 History Details for Sample Database

Other Options …

[db2inst1@mercury ~]$db2 list history since 19980201 for sample
[db2inst1@mercury ~]$db2 list history backup containing userspace1 for sample
[db2inst1@mercury ~]$db2 list history dropped table all for db sample

List DB2 Diagnostic Details

Jul9

=============================================================
List DB2 Diagnostic Informaiton
=============================================================

“Db2pd “ tool runs on Linux, UNIX and Windows platforms and can give you diagnostic information very quickly and in a very non intrusive manner.

Using db2pd you can display information about applications, databases, instances, transactions, bufferpools, logs, dynamic and static sql, memory pools and much more

You can redirect the results to a file using the “db2pd -file outfile_name”
If there is a need to check or find out which DB2 instance you are working on now, …

[db2inst1@mercury ~]$ db2pd -inst | head -2

ScreenShot : Lists the DB2 Diagnostic Information

=============================================================

To see information about the system you are running on run “db2pd -osinfo”

[db2inst1@mercury ~]$ db2pd -osinfo

ScreenShot : Lists the DB2 Diagnostic Information 2

Stop DB2 Instance

Jun7

Steps to Stop DB2 Instance

1) Swith User to the Db2 Instance User
2) Check if the db2 process is running using the “ps -ef | grep db2sys” where db2sysc is the DB2 engine (instance)
3) Execute “db2stop”
4) Check once again for the db2sys process

[root@mercury instance]# su – db2inst1[db2inst1@mercury ~]$ ps -ef | grep db2sys
db2inst1 10850 10849 0 May23 pts/7 00:00:01 db2sysc 0
db2inst1 11222 11135 0 00:10 pts/7 00:00:00 grep db2sys

[db2inst1@mercury ~]$ db2stop
05/24/2011 00:11:07 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

[db2inst1@mercury ~]$ ps -ef | grep db2sys

Screenshot: db2stop Command

5) Check the db2diag.logs for any errors

If while stopping the db2instance we get the following message ” the DataBase Manager cannot be stopped because the database is still active”
This would mean there are still some connections using the DB

so we have to force the applications to close

1) Use list applications to display the details
.

[db2inst1@mercury dbbackup]$ db2 list applications

2) Force the applications to close

[db2inst1@mercury dbbackup]$ db2 force applications all

3) Then execute the “db2stop” command

[db2inst1@mercury dbbackup]$ db2stop

Screenshot: db2stop Command

Start DB2 Instance

Jun7

Start DB2 Instance
Starts the current database manager instance background processes on a single database partition or
on all the database partitions defined in a partitioned database environment ie (db2node.cfg)

1) Switch User to the Db2 Instance User
2) Check if the db2 process is running using the “ps -ef | grep db2sys” where db2sysc is the DB2 engine (instance)
3) Execute “db2start”
4) Check once again for the db2sys process

[root@mercury instance]# su – db2inst1
[db2inst1@mercury ~]$ ps -ef | grep db2sys

[db2inst1@mercury ~]$ db2start
05/23/2011 22:38:42 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

[db2inst1@mercury ~]$ ps -ef | grep db2sys
db2inst1 10850 10849 19 22:38 pts/7 00:00:00 db2sysc 0
db2inst1 10864 10747 0 22:38 pts/7 00:00:00 grep db2sys

ScreenShot: Db2start command

5) Check the db2diag.logs for any errors

Delete DB2 Instance

Jun7

Removes a DB2 instance that was created by db2icrt and the instance entry from the list of instances.

The preparation and details to removing an instance using the command line are:
->Stop all applications that are currently using the instance.
->Stop the Command Line Processor by running db2 terminate commands in each DB2 command window.
->Stop the instance by running the db2stop command.
->Backing up the files in the INSTHOME/sqllib directory
->Not Mandatory but you can save the database manager configuration file, db2systm, the db2nodes.cfg file,

To Remove the Db2 Instance —

1) Login to the server as root or any user under which db2 was installed
2) Execute the db2idrop command within DB2DIR/instance/ ie “db2idrop InstName “ where InstName is the name of the instance being dropped.
This command removes the instance entry from the list of instances and removes the instance directory.
3) Optionally, as a user with root authority, remove the instance owner’s user ID and group , Do not remove these if you are planning to re-create the instance.

[root@mercury instance]# cd /opt/ibm/db2/V9.1/instance
[root@mercury instance]# ./db2ilist
[root@mercury instance]# ./db2idrop db2inst1
[root@mercury instance]# ./db2ilist

5) The db2idrop command removes the instance entry from the list of instances and removes the
sqllib subdirectory under the instance owner’s home directory.

6) This Removes the DB2 Instance

Santosh Nair's WebSphere And Unix Study Notes

Archives

Get DB2 Authorizations

List DB2 History

List DB2 Diagnostic Details

Stop DB2 Instance

Start DB2 Instance

Delete DB2 Instance