Some Useful DB2 Commands

If you are a beginner DB2 admin like me, you may find DB2 has different way compared with Oracle DB. Here are some useful commands for DB2 and don’t forget to execute these commands using DB2 instance user (db2instX):

db2 list active databases # list databases
db2 activate database [dbname] # activate database
db2 list db directory # list database directories
db2 connect to [dbname] # connect to database
db2 list applications # list applications connected
db2 terminate # terminate application connection
db2 deactivate database [dbname] # deactivate database
db2stop # stop db2 process
db2start # start db2 process
db2 get db cfg for [dbname] # show database configuration
update db cfg for [dbname] using logarchmeth1 DISK:[completePath] #set archive log

Ref: https://www.youtube.com/watch?v=9dTWU6Iu-BE

Advertisements
Some Useful DB2 Commands

Generate Database Data Dictionary Using Oracle SQL Developer

Data dictionary is a handy reference for SQL developer and programmer to know where to find out data. Thanks to Oracle SQL Developer, we can generate data dictionary from a database schema painlessly. The steps are:

1. Create new database connection.

2. Right click the connection and choose Generate DB Doc

dict-01

3. Browse to select the destination folder for data dictionary and select database objects to be generated for data dictionary.

dict-02

4. Wait the generation progress

dict-03

5. Review the nice result in the browser (example of result)

dict-04

Hope this can help many database developers.

Generate Database Data Dictionary Using Oracle SQL Developer

Trick to Boot VMWare from USB Device / Image

Sometime we just have the USB image (IMG file) and need to boot from it in VMWare. Unfortunately, VMWare BIOS currently does not support booting from USB device. I just found 2 solutions for this:

1. Using bootmanager iso from plop (http://www.plop.at/en/bootmanagers.html). By using the bootmanager, we can choose the media to boot from, including USB device.

2. Convert the USB Image file into VMDK format disk by using qemu. The syntax is:

qemu-img convert -f raw -O vmdk [source_file.img] [target_file.vmdk]

 

Trick to Boot VMWare from USB Device / Image

Copy Files and Folders in Windows and Preserve NTFS Attributes

When we need to copy files and folders in Windows while need to maintain its file system attributes, there is a handy utility which is included in Windows (especially the rather outdated Windows) which is called xcopy. The command is as simple as:

xcopy [sourcefolder] [destinationfolder] /X /H /E /V /Y

The explanation for the parameters is:

/X – Copies file audit settings and file ownership and ACL information.
/H – Copies hidden and system files.
/E – Copies directories and subdirectories, including empty ones.
/V – Verifies each new file.
/Y – Suppresses prompting to confirm you want to overwrite an existing destination file.

Credit to: PhilEddies (http://www.geekshangout.com/content/using-xcopy-copy-files-and-folders-and-keeping-ntfs-permissions)

Copy Files and Folders in Windows and Preserve NTFS Attributes

Simple Backup and Restore Script Using RMAN

Here’s simple backup script using RMAN:

#!/bin/sh
. ~/.bash_profile
export ORACLE_SID=[instance_name]
rman target / log=[/path/to/logfile.log] << EOF
RUN {
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
backup as compressed backupset database
   tag FULLBACKUPDATA;
sql "alter system switch logfile";
sql "alter system switch logfile";
backup archivelog all
   tag FULLBACKUPARCH
   not backed up 2 times
   delete input;
backup current controlfile;
delete noprompt obsolete RECOVERY WINDOW OF 2 DAYS;
}
EXIT;
EOF

Here’s simple restore script using RMAN duplicate (nocatalog command). Change the ORACLE_SID to the target restore instance first.

connect target sys/[syspassword]@[source_instance]
connect auxiliary /;
run {
set until sequence 23361 thread 1;

SET NEWNAME FOR DATAFILE 1 to '[/path/to/datafile1.dbf]';
SET NEWNAME FOR DATAFILE 2 to '[/path/to/datafile2.dbf]';
SET NEWNAME FOR DATAFILE 3 to '[/path/to/datafile3.dbf]';

set newname for tempfile 1 to '[/path/to/tempfile.dbf]';

duplicate target database to [target_instance]
logfile
           group 1
       ('[/path/to/redologfile1.log]') size 20M,
            group 2
       ('[/path/to/redologfile2.log]) size 20M,
            group 3
       ('[/path/to/redologfile3.log]') size 20M

NOFILENAMECHECK;

}
EXIT;
Simple Backup and Restore Script Using RMAN

Connect SQL*Plus to Hung Oracle Database

When Oracle Database cannot create session, user sometime can execute SQL*Plus with normal credential. But it didn’t return the usual SQL*Plus prompt after it. When the user check the sessions, the oracle background processes are still running.

There prefered solution for this problem is login with parameter -prelim as:

sqlplus -prelim "/ as sysdba"

and then execute shutdown command and restart the Oracle database.

Connect SQL*Plus to Hung Oracle Database

Create Single Archive File (TAR) from Multiple Files on Subdirectory

There’s a simple command on *NIX based system which is handy for admin to create a single archive file (tar) from multiple files on a subdirectory. Thanks to the joy of *NIX piping. The sample command is:

# find directory -name "*.log" -type f -mtime +2   
   | xargs tar -cvf a.tar -

Credit to: dennis.jacob
http://www.unix.com/solaris/80371-find-files-older-than-x-days-create-consolidated-single-tar-file.html

Create Single Archive File (TAR) from Multiple Files on Subdirectory