Heterogeneous

1
The Heterogeneous DBA Surviving in a
Multi-Database Environment
2
Agenda

• Basic Components
• Terminology Differences
• Storage Management
• Object Comparisons
• Administrative Differences

3
RDBMS Components

• Oracle
• Instance
• File
• Database
• Tablespace
• Schema
• Table
• Index
• View
• Trigger
• Redo Log
• Rollback Segment
• Stored Procs
• SQL Plus
• Roles

• DB2
• Instance
• Container
• Database
• Tablespace
• Schema
• Table
• Index
• View
• Trigger
• Log
• NA
• Stored Procs
• DB2 CLP
• Groups ?

• MS SQL
• Instance/Server
• Files/File Groups
• Database
• NA
• Owner
• Table
• Index
• View
• Trigger
• Transaction Log
• NA
• Stored Procs
• SQL Query Analyzer
• Roles

4
Data Types

• Oracle
• CHAR(N)
• NCHAR(N)
• VARCHAR2(N)
• NVARCHAR2(N)
• NUMBER(P,S)
• DATE
• RAW(N)
• BLOB, CLOB,
• NCLOB, BFILE
• LONG, LONG RAW
• ROWID

• DB2 Unix/Win
• CHAR(n)
• VARCHAR(n)
• LONG VARCHAR
• CLOB
• GRAPHIC(n)
• VARGRAPHIC(n)
• LONG VARGRAPHIC
• DBLOB
• BLOB (n)
• SMALLINT, INTEGER, BIGINT
• DECIMAL(p,s), NUMERIC(p,s)
• REAL
• DOUBLE, FLOAT
• DATE
• TIME
• TIMESTAMP

• MS SQL
• CHAR(n)
• NCHAR(n)
• VARCHAR(n)
• NVARCHAR
• TEXT
• NTEXT
• IMAGE(n)
• BIT
• BINARY
• VARBINARY
• TINYINT,SMALLINT,INT, BIGINT
• DECIMAL(p,s)
• FLOAT
• REAL
• NUMERIC
• MONEY, SMALLMONEY
• SMALLDATETIME, DATETIME

5
Accessing the Database
DB2 UDB
Oracle
MS SQL

• Enterprise Mgr.
• GUI
• SQLPlus
• svrmgrl

• Control Center
• GUI
• Command center
• Command line processor
• Command window
• Script center
• Visual Explain

• Enterprise Mgr.
• GUI
• Profiler
• Query Analyzer
• Isql

6
Common Terms Different Meanings

• Oracle
• Stored
• Procedure
• PL/SQL
• Package
• grouping of PL/SQL blocks

• DB2 UDB
• Stored
• Procedure
• External - C,Java,Cobol
• SQL
• Package
• Pre-compiled access plan

• MS SQL
• Stored
• Procedure
• T-SQL
• Package
• Data transformation defining object

7
Different Terms Similar Meanings

• Oracle
• Data Block
• Dictionary
• Alert Log
• Redo Log
• Archive log
• Segments
• Statement
• Cache

• MS SQL
• Page
• Data Dictionary
• Error log
• Transaction Log
• Transaction Log Backup
• Tables, IX
• Procedure Cache

• DB2 UDB
• Data Page
• Catalog
• Diag log
• Log Files
• Log Retain
• Tables, IX
• Package
• Cache

8
Instance, Server and Databases

• Oracle
• Instance Server ID
• A collection of Processes
• One active database
• Init.ora parameter file

• DB2
• InstanceDB2instance
• A collection of Processes
• One or more active databases
• Instance (DBM) Configuration File
• Data Base Configuration file
• DB2SET Registry Variables

• MS SQL
• Instance/Server
• A collection of Processes
• One or more active databases
• Configuration Object
• Config Values
• Parameters

9
Oracle Instance
DB2
10
Oracle Process Model
11
DB2 Instance
12
DB2 Process Model
13
SQL Server/Instance
DB2
D B M C O N F I G
Master DB
Model DB
MSDB
Temp DB
ProdDB1
ProdDB2
ProdDB3
14
System Dictionary/Catalog
DB2
Oracle
MS SQL

• Master DB
• System tables
• Superset of all databases
• Data Dictionary Tables
• dbo.sysxxxxx
• Contained in each DB
• Information Schema Views

• Base tables
• SYS.xxxxx
• System views
• SYS.GV_ or GV
• SYS.V_ or V
• ALL_
• DBA_
• USER_

• SYSIBM.xxxx
• SYSCAT
• Read-only views defined for catalog base tables
• SYSSTAT
• Updateable set of views
• Primarily used for access path manipulation

15
Types of Parallelism

• Oracle
• I/O
• CPU
• Cluster
• Intra-cluster
• Inter-cluster
• Parallel Query

• DB2 EE/EEE
• I/O
• CPU
• Intra-partition
• Inter-partition
• Parallel Recovery
• Parallel Backup

• SQL Server
• I/O
• CPU
• Parallel Query
• Parallel Backup

Some options only on partitioned objects
16
Bufferpools/Buffer Cache

• DB2
• Defined within a database 4,8,16,32K page
• Main or extended storage
• Defined via DDL
• Tablespaces assigned

• MS SQL
• Defined within an Instance
• Dynamically allocated by SQL Server
• Can be modified with sp_configure
• Can be manually configured for min, max and
  working set

• Oracle
• 3 bufferpools available
• 8i one fixed block (8k)
• 9i multi block size
• Defined in init.ora
• DB_BLOCK_BUFFERS 20000
• DB_BLOCK_LRU_LATCHES 6
• BUFFER_POOL_KEEP (BUFFERS14000, LRU_LATCHES1)
• BUFFER_POOL_RECYCLE (BUFFERS2000,
  LRU_LATCHES3)
• Objects assigned via DDL

17
Databases

• Oracle
• A structured collection and management of
  information
• May have morethan one for a SID
• Only one DB mounted/opened

• DB2
• Logical grouping of objects
• Typically one or two database/instance
• Catalog for each database defined within database
• SYSCATSPACE
• TEMPSPACE
• USERSPACE
• Database configuration file

• MS SQL
• Logical grouping of objects
• One to Many per Instance
• Catalog for each database defined within database
• Master and Model database
• Configuration Object

18
Oracle Storage Hierarchy
19
DB2 Storage Hierarchy
20
MS SQL Storage Hierarchy
21
Tablespaces

• Oracle
• Two Categories
• System tablespace
• Non-System tablespace
• Two types of Non-System
• Permanent
• Temporary
• Two types of tablespaces extent management
• Dictionary mapped
• Locally managed

22
System Non-System

• System Tablespacecontains
• Data dictionary information
• System rollback segment

• Non-System Tablespace contains
• Rollback segments
• Temporary segments
• Application data
• Application indexes

23
Tablespaces

• DB2
• Two Categories of Tablespace Management
• SMS System Managed Space
• DMS Database Managed Space
• Three Types of Tablespace
• Regular SMS DMS
• Temporary (System User) SMS DMS
• Long DMS
• Three Space Allocation Methods
• Directory SMS
• File DMS
• Device - DMS

24
Tablespace Storage Settings

• Oracle
• INITIAL
• NEXT
• MINEXTENTS
• MAXEXTENTS
• PCTINCREASE
• FREELIST GROUPS

• DB2
• PAGESIZE
• EXTENTSIZE
• PREFETCHSIZE

• MS SQL
• NA
• Files assigned at DB level
• Fixed Storage Settings 8k Pages, 64k extents and
  Allocations
• SIZE
• FILEGROWTH

25
Oracle Data Files
ALTER TABLESPACE app_data ADD DATAFILE
/DISK6/app04.dbf SIZE 200M AUTOEXTEND ON NEXT
10M MAXSIZE 500M
Tablespace APP_ DATA
app04.dbf 2M
app03.dbf2M
app01.dbf1M
app02.dbf1M
26
DB2 Containers
SMS

• Physical storage device
• A container is assigned to an individual
  tablespace
• A TS may have gt 1 container
• Directory name
• SMS Only
• D\MYTS
• Raw Device
• DMS Only
• E
• File name
• DMS Only
• D\SODADB\SODA.UTILITY.DMS

Directory
/db/payroll/tbsp1/cont
DMS
File
Raw Device
27
MS SQL Data Files

• Filegroup/Filegroup2
• Collection of files
• Primary Data Files
• Secondary Data Files
• Log Data Files
• Applies to one database
• Each database can have multiple filegroups

28
Partitioning

• Oracle
• Table Definition
• 1-64k partitions
• Partitioning on range, hash, both or list
• Partitions assigned to TS
• Enhances Parallel Query
• Easier Data Management

• DB2 UDB EEE
• Enhances Parallelism
• Easier Data Management
• Nodegroupcontrols number of partitions
• Hash key dictates partition selection within
  Nodegroup
• TS assigned to Nodegroup

• MS SQL
• Federated Servers
• Partitioned Views
• Distributed Partition Views
• Data spread by key value
• Each server managed separately

29
Tables

• Oracle
• One to many tables defined in a tablespace
• Tables and Indexes are independent of each other
• Index Organized Tables
• DB2
• One to many tables defined in a tablespace
• Indexes directly tied to table definition
• Index automatically created on primary key
• MS SQL
• One to many tables defined in a database
• Index automatically created for unique column or
  primary key
• Up to 249 non-clustered indexes can be created on
  a table

30
Indexes

• Oracle
• Unique
• Non-unique
• Partitioning
• Function based
• Bit Map
• Indexed Organized Table (IOT)

• DB2
• Unique
• Non-unique
• Clustering

• MS SQL
• Unique
• Clustering
• Similar to Oracle IOT
• Non-Clustering

31
Oracle B-Tree Index
Index entry
Root
Branch
Index entry header
Key column length
Leaf
Key column value
ROWID
32
Oracle Bitmap Index
33
Indexes

• DB2
• Index placement is dependent on table definition.
  Tablespace must be specified when table is
  created
• All indexes for a table use same tablespace
• Tablespace is predefined before indexes are
  created
• Indexes can be defined in same tablespace as table

34
DB2 Index Structure
35
MS SQL Index Structure
36
Logging
Oracle
DB2
MS SQL

• Redo Logs apply to Instance
• NoArchive Mode
• Full DB recovery
• Archive Mode
• Recover DB, TS, DF, block
• 3 Redo log states
• Active
• Inactive
• Archived
• Redo Logs created in
• Groups Number of logs
• Members of groups (mirrors)
• Rollback Segments

• Defined at database
• Circular
• No roll-forward recovery
• Log Retain
• Fully recoverable
• Dual Logs (7.2)
• 3 log file states
• Active
• Online Archived
• Offline Archived
• User exit called for Archiving

• Defined at database
• Transaction Log File
• DB Backup
• Transaction Log Backup
• Truncate Log

37
Redo Log Groups Members
38
Oracle Archive Logging
39
RollBack Segment
Old image
New image
Table
Rollback Segment
Update transaction
40
Rollback Segments Purpose
Transaction rollback
Transaction recovery
Read consistency
Rollback segment
41
Oracle Read-Consistency
SELECT FROM table
Table
New image
Image at statement commencement
42
DB2 Circular Logging

• Crash Recovery only
• Log files are reused
• Secondary as needed
• Default method

1
2
n
1
S E C O N D A R Y
Primary
3
n
43
DB2 Log Retain
Online Archival - Contains information for
committed and externalized transactions. Stored
in the active log subdirectory

• Log files not reused
• Roll Forward Recovery

12
13
14
15
Offline Archival Files moved from active Log
subdirectory. Usually offline media
Active Contains information For non-committed
or Non-externalized Transactions.
16
44
SQL Server Transaction Log
45
Backups
Oracle
DB2
MS SQL

• Database Cold
• Offline OS level
• All datafiles
• All control files
• All online redo log files
• The init.ora file
• Tablespace Hot
• Online OS level
• Archive log list
• Alter tablespace abc begin backup
• Alter system switch logfile
• Alter database backup controlfile to 'file_name'
• RMAN backup/recovery utility
• Export Database Logical
• Standby Database

• Database Online/Offline
• Tablespace
• Components
• Full/Incremental/Delta Copy
• DB Configuration
• Tablespace definitions
• Backup History File
• Active/Archive Logs

• Database
• Data Files
• Log Files
• Trans Log
• Components
• Full
• Differential

46
Recovery Info
Oracle
DB2
MS SQL

• Control File
• Database name
• Data file location
• Redo log file location
• Tablespace names
• Current log sequence number
• Checkpoint information
• Log history
• Backup information
• RMAN - Recovery Catalog

• Recovery History File
• Updated
• Backup of DB/TS
• Restore/Roll Forward of DB/TS
• Drop/Load/Reorg/Stats of a table
• Quiesce/Alter TS
• Contains
• Part of DB which was copied
• When DB was copied
• Location of the copy
• Time of last restore
• DDL of tables

• SYSDEVICES-contains metadata
• Logical Backup File Name
• Size
• Type
• Physical File Name

47
Types of Recovery
Oracle
DB2
MS SQL

• Instance Recovery
• Uses Redo logs to recover from System/Instance
  failures
• NoArchive Mode
• Full Database (cold)
• Archive Mode
• Point in time
• Automatic
• Import (logical)

• Crash
• Uses logs to recover from System/Instance
  failures
• Version (non-recoverable DB)
• Image copy (TOCOPY)
• Roll-Forward
• Image copy plus log apply
• Import/Load (logical)

• Simple
• Recover to most recent full or differential
• Full
• Can recover to point in time (trans log)
• Bulk-Logged
• Point in time less bulk load

48
Utilities
Oracle
DB2
MS SQL

• IMPORT
• EXPORT
• SQLLOADER
• ANALYZE
• DB_VERIFY
• RMAN

• BACKUP
• IMPORT
• EXPORT
• LOAD
• RESTORE
• REORG (Table)
• REORGCHK
• RUNSTATS

• BACKUP
• RESTORE
• EXPORT
• IMPORT
• BCP
• DBCC
• CREATE STATISTICS

49
Optimizer

• MS SQL
• Cost based
• Hints

• Oracle
• Rules - Hints
• Cost based
• Choose

• DB2
• Cost based more sophisticated than Oracle
• Seven levels of optimization
• Adjusted based on query complexity
• No Rules

50
Explain
Oracle
DB2
EXPLAIN_INSTANCE
PLAN_TABLE
EXPLAIN_STATEMENT
EXPLAIN_OPERATOR
EXPLAIN_STREAM
EXPLAIN_ARGUMENT
EXPLAIN_PREDICATE
EXPLAIN_OBJECT
51
Monitoring
Oracle
DB2
MS SQL

• Performance Snapshots - V
• Instance/Database
• Memory
• Disk
• User/Session
• Contention
• UTLBSTAT and UTLESTAT
• Collects Stats over time
• TKPROF
• Formats SQL trace data from applications

• Snapshot Monitor
• Show status of database counters at instant in
  time
• Buff, Lock, Sort, Stmt, Tbl, UOW
• Event Monitor
• Status after the event
• Databases
• Tablespaces
• Connections
• Tables
• Statements
• Transactions
• Deadlocks

• Windows System Monitor
• Error Alerts
• Performance Alerts
• Event-Forwarding
• SQL Profiler Trace
• Stored Procedures
• sp_locks
• sp_monitor
• sp_..

52
Summary

• To be a successful heterogeneous DBA
• Have a sound foundation of relational principles
• Understand the nuances of the individual
  platforms
• Understand that the principles of database
  administration are similar regardless of the RDMS

53
THANK YOU FOR LISTENING