8.2 Administration scripts

 < Day Day Up > 



8.2 Administration scripts

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.

8.2.1 Dynamic performance views and table function

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.

Table 8-1: V$ views and 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.

8.2.2 Frequently used commands and DDLs by DBA

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.

Table 8-2: Commands and DDL conversion

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 

8.2.3 Backup scripts conversion

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

start example
 #!/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; 
end example

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

start example
 #!/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; 
end example

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 > 



Oracle to DB2 UDB Conversion Guide2003
Oracle to DB2 UDB Conversion Guide2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 132

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net