| < Day Day Up > |
|
Database administrators use many administration scripts to schedule and run their day-to-day activities. Conversion of these administration scripts from Oracle to DB2 also plays an important role in migration. This section discusses how certain administration commands and DDLs can be converted to DB2 commands and DDLs.
Oracle provides dynamic performance views that are updated dynamically by the Oracle instance. Dynamic performance views are prefixed by V_$ and have public synonyms created with the V$ prefix. Dynamic performance views are accessed by the database administrators, and are listed in V$FIXED_TABLE. These views are used by the database administrators to monitor the database. The information provided by these views are not static, and are dynamically updated by the database and instance. Examples of such views are V$INSTANCE, V$DATABASE, V$TABLESPACE, V$DATAFILE, etc.
DB2 UDB provides snapshot monitor to give the performance details of the database and instance. DB2 snapshot monitor gives a point-in-time view of how the database is performing. Section 9.4.4, "Problem determination tools" on page 301 gives a detailed look on these snapshot monitors. DB2 UDB Version 8.1 provides a list of built-in table functions to query the snapshot monitor output and to get the result sets in a table form. This can be thought of as equivalent to V$ views in Oracle. Though the information we get from the V$ views and table functions cannot be exactly same, some information is common, and both return dynamic data. Some of these table functions are SNAPSHOT_DBM, SNAPSHOT_DATABASE, SNAPSHOT_STATEMENT, SNAPSHOT_TABLE, etc. For example, to get the information about applications connected to the database, a table function call is made using:
SELECT * FROM TABLE( SNAPSHOT_APPL( cast (NULL as VARCHAR), -1)) as SNAPSHOT_APPL
An equivalent query to execute in Oracle for connected application is:
SELECT * FROM V$SESSION
Table 8-1 shows some of the V$ views and its equivalent table functions.
Dynamic performance views | Snapshot table functions |
---|---|
V$INSTANCE | SNAPSHOT_DBM |
V$DATABASE | SNAPSHOT_DATABASE |
V$TABLESPACE | SNAPSHOT_TBS |
V$DATAFILE | SNAPSHOT_CONTAINER |
V$SESSION | SNAPSHOT_APPL |
V$SQLTEXT | SNAPSHOT_STATEMENT |
V$LOCK | SNAPSHOT_LOCK |
V$BUFFERPOOL | SNAPSHOT_BP |
Note | For more information on table functions, refer to Part 3," Using snapshot monitor" in System Monitor Guide and Reference, SC09-4847-00. |
It is useful for DBAs to know how certain important administrative commands and DDLs used in Oracle can be converted to DB2. This is helpful for the DBAs to create certain maintenance scripts in the DB2 environment. Table 8-2 lists out some of the sample commands and the DDL converted to DB2 equivalents.
Oracle | DB2 UDB |
---|---|
CREATE DATABASE ORA_EMP MAXLOGFILES 2 MAXLOGMEMBERS 3 LOGFILE GROUP 1 ('/disk1/log1a.log','/disk1/log1b.log','/disk1 /log1c.log') SIZE 1M, GROUP 2 ('/disk2/log2a.log','/disk2/log2b.log','/disk2 /log2c.log') SIZE 1M DATAFILE '/disk1/system01.dbf' SIZE 100M; |
CREATE DATABASE DB2_EMP CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE '/disk1/syscatspace.dbf' 25600); UPDATE DB CFG FOR DB2_EMP USING LOGPRIMARY 2; UPDATE DB CFG FOR DB2_EMP USING NEWLOGPATH '/disk1'; UPDATE DB CFG FOR DB2_EMP USING MIRRORLOGPATH '/disk2'; UPDATE DB CFG FOR DB2_EMP USING LOGFILSIZ 256; |
CREATE TABLESPACE USER_DATA_TBS DATAFILE '/disk1/user_data_tbs_01.dbf' SIZE 50M MINIMUM EXTENT 1M PERMANENT |
CREATE REGULAR TABLESPACE USER_DATA_TBS MANAGED BY DATABASE USING (FILE '/disk1/user_data_tbs_01.dbf' 12800) EXTENTSIZE 1M |
CREATE TABLESPACE USER_TEMP_TBS DATAFILE '/disk1/user_temp_tbs_01.dbf' SIZE 50M MINIMUM EXTENT 1M TEMPORARY |
CREATE USER TEMPORARY TABLESPACE USER_TEMP_TBS MANAGED BY DATABASE USING (FILE '/disk1/user_data_tbs_01.dbf' 12800) EXTENTSIZE 1M |
CREATE TABLESPACE USER_LOB_TBS DATAFILE '/disk1/user_lob_tbs_01.dbf' SIZE 100M MINIMUM EXTENT 1M PERMANENT |
CREATE LARGE TABLESPACE USER_TEMP_TBS MANAGED BY DATABASE USING (FILE '/disk1/user_data_tbs_01.dbf' 25600) EXTENTSIZE 1M |
CREATE USER ORA_USR IDENTIFIED BY EXTERNALLY |
CREATE SCHEMA DB2_USR AUTHORIZATION DB2_USR -- identifies o/s user db2_usr |
GRANT CREATE SESSION, CREATE TABLE TO ORA_USR; |
GRANT CONNECT, CREATETAB ON DATABASE TO USER DB2_USR; |
ALTER SYSTEM KILL SESSION ('sid','serial') IMMEDIATE |
FORCE APPLICATION (appl handle) MODE ASYNC |
ALTER SYSTEM SUSPEND |
SET WRITE SUSPEND FOR DB |
ALTER SYSTEM QUIESCE RESTRICTED -- Only allowed in Oracle 9i |
QUIESCE DB database name |
ALTER SYSTEM ARCHIVE LOG |
ARCHIVE LOG FOR DB database name |
ALTER SYSTEM FLUSH SHARED_POOL |
FLUSH PACKAGE CACHE DYNAMIC |
DBMS_SPACE_ADMIN package |
INSPECT database command |
SET TRANSACTION ISOLATION LEVEL |
CHANGE ISOLATION LEVEL |
ANALYZE TABLE command |
RUNSTATS ON TABLE |
Oracle uses two level of backups: datafile backup and logical backup using the export utility. DB2 uses the BACKUP database command to backup the database. This can be thought of as an equivalent to the Oracle export utility. So, the logical backup scripts using export utility in Oracle can be converted to DB2 backup scripts. Example 8-6 shows a sample shell script used to export the database.
Example 8-6: Export script in Oracle
#!/usr/bin/ksh ##### -- Oracle daily logical backup script -- today=`date +%C%y%m%d` dumpfile=/oracle/backup/exp_$today.dmp logfile=/oracle/backup/exp_$today ORACLE_SID=ORA_EMP export ORACLE_SID exp system/manager file=$dumpfile log=$logfile buffer=10485760 full=y;
Example 8-7 shows the equivalent shell script used in the DB2 environment to back up the database.
Example 8-7: BACKUP database script in DB2
#!/usr/bin/ksh ###### DB2 daily backup script ########## today=`date +%C%y%m%d` logfile=/db2/backup/db2bkup_log$today.log BACKUPDIR=/db2/backup db1=DB2_EMP DB2INSTANCE=db2inst1 export DB2INSTANCE db2 backup db $db1 online to $BACKUPDIR with 4 buffers buffer 512>> $logfile;
The examples show how to back up the database into the disk. Like Oracle, DB2 also supports backing up the database directly into the tape. For more information, refer Data Recovery and High Availability Guide and Reference, SC09-4831-00.
| < Day Day Up > |
|