db2 for linux, unix & windows - e-learning -...
TRANSCRIPT
© 2010 IBM Corporation
IBM Software Group
DB2 for Linux, Unix & Windows
Scelte tecnologiche di gestione su DBMS centralizzati e distribuiti; servizi automatizzati e ruolo del DBA
Preparato per Uni Bicocca - Corso "Architetture Dati"
Dicembre 2010
Michele Benedetti & Francesco Airoldi
Software Group - IBM Italia
© 2010 IBM Corporation 2
DB2 Information Management
Agenda
Quick review of some DB2 basics
Part 1: how DB2 implements data access, integrity and security
• Transactions: Concurrency, Locking
• Transactions: Recovery
• Backup & Restore
• Security features
• Optimizer
Part 2: aspects of DB2 database administration
• Automated tasks and autonomic capabilities
• High-value DBA tasks
© 2010 IBM Corporation 3
DB2 Information Management
Quick review of some DB2 basics
Quick review of some DB2 basics
© 2010 IBM Corporation 4
DB2 Information Management
High level mapEnd user
Developer
DBA
Applications
DBMS engineDeveloptools
DBAtools
&utilities
Otherdevelop
tools
Othersystemtools
Databases(relational & XML)
SQL, XQuery
Optimizer5
Transactions: concurrency, locking
1
Automated tasks & autonomic capabilities
6
High-value tasks
7
2Transactions:
recovery 4Security features
3 Backup & Restore
© 2010 IBM Corporation 5
DB2 Information Management
DB2 architecturesAppl
Appl
Appl
DB2
DB2 DB2 DB2 DB2
DB2 DB2
disk
disk disk disk disk
disk
DB2 has three different proposition• Single SMP Server
• Intensive write/update workload• Scale up
• Shared Nothing Clusters• Intensive Read workload (BI)• I/O always in parallel• Scale up & out
• Shared Disk Cluster• Typical OLTP workload.
• 80% read, 20% write• Thousands of users• Highest level on SQL execution concurrency • Very simple SQL• Continuous availability and scalability as main goal• Scale up & out
© 2010 IBM Corporation 6
DB2 Information Management
DB2 Instance: db2inst1
Database PROD_DB3
DatabasePROD_DB5
Catalog
Catalog
Log
Log
DBconfig
DBconfig
Buffer Pools
Buffer Pools
DBMconfig
DB2 Server ArchitectureInstances and databases
• A DB2 instance can• Access more than one
database at a time• Most production databases
however are managed by one instance
• An instance is also called "Data Base Manager" (DBM)
• In a DB2 instance • Many threads are shared by all
databases• Instance configuration
parameter (dbm cfg) affects all databases in that instance
• Database configuration (db cfg) parameters exist for each database
SET db2instance = db2inst1db2start
DB2 Instance: db2inst2
Database TEST_DB3
DatabaseTEST_DB5
Catalog
Catalog
Log
Log
DBconfig
DBconfig
Buffer Pools
Buffer Pools
DBMconfig
© 2010 IBM Corporation 7
DB2 Information Management
DB2 Storage Structure Hierarchy
DB2 Database Storage Model
Database
Table space
Object
Extent
DB2 Page
Container
OS Page
(Tables – Indexes)
Logical Physical
© 2010 IBM Corporation 8
DB2 Information Management
DB2 Administration Tools
DB2 Control Center
DB2 Command Window (CLP)
DB2 Task Center
DB2 Health Center
© 2010 IBM Corporation 9
DB2 Information Management
9
New: Infosphere Optim Data Studio Portfolio Menu barMenu bar Tool barTool bar
Data Project
Explorer view
Data Project
Explorer view
Resize buttonResize button
SQL Results view
SQL Results view
Editor viewEditor view
Perspective(a collection of
views and editors)
Perspective(a collection of
views and editors)
Drag & drop views anywhere
Drag & drop views anywhere
Data Source
Explorer view
Data Source
Explorer view
© 2010 IBM Corporation 10
DB2 Information Management
Part 1
Part 1
How DB2 implements data access, integrity and security
© 2010 IBM Corporation 11
DB2 Information Management
Transactions: concurrency, locking
Transactions: Concurrency & Locking
© 2010 IBM Corporation 12
DB2 Information Management
In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:
AtomicityA transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helpsto avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.
ConsistencyA transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.
IsolationEvery transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.
DurabilityIf a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.
Transactions: concurrency, locking1ACIDACID properties are fundamental for every database management system
A number of features in DB2 (concurrency management, locking, logging, recovery...) are designed and implemented to ensure full compliance with these ACID properties in every possible situation
© 2010 IBM Corporation 13
DB2 Information Management
Basic concepts Transactions: concurrency, locking1
• Database Transactions
• Concurrency
• Concurrency Issues
• Concurrency Control• Isolation Levels• Locking• Specifying Isolation Levels
© 2010 IBM Corporation 14
DB2 Information Management
Database Transactions
• Transaction• sequence of one or more SQL operations, grouped together
as a single unit• also known as a unit of work (uow) or logical unit of work (luw)
• Committed Data• using the COMMIT statement commits any changes made
during the transaction to the database
• Uncommitted Data• changes during the transaction before the COMMIT statement
is executed
Transactions: concurrency, locking1
© 2010 IBM Corporation 15
DB2 Information Management
Concurrency
• Concurrency • Sharing of resources by multiple interactive users or application
programs at the same time
• Having multiple interactive users can lead to: • Lost Update• Uncommitted Read• Non-repeatable Read• Phantom Read
• Need to be able to control the degree of concurrency:• With proper amount of data stability• Without loss of performance
Transactions: concurrency, locking1
© 2010 IBM Corporation 16
DB2 Information Management
Concurrency Issues
• Lost Update• Occurs when two transactions read and then attempt to update the
same data, the second update will overwrite the first update before it is committed
Transactions: concurrency, locking1
1) Two applications, A and B, both read the same row and calculate new values for one of the columns based on the data that these applications read
3) Then B also updates the row
4) A's update is lost
2) A updates the row
© 2010 IBM Corporation 17
DB2 Information Management
Concurrency Issues...
• Uncommitted Read or Dirty Read• Occurs when uncommitted data is read during a transaction• Also known as a Dirty Read
1) Application A updates a value
2) Application B reads that value before it is committed
3) A backs out of that update
4) Calculations performed by B are based on the uncommitted data
Transactions: concurrency, locking1
© 2010 IBM Corporation 18
DB2 Information Management
Concurrency Issues...
• Non-repeatable Read• Occurs when a transaction reads the same row of data twice and
returns different data values with each read
1) Application A reads a row before processing other requests
2) Application B modifies or deletes the row and commits the change
3) A attempts to read the original row again
4) A sees the modified row or discovers that the original row has been deleted
Transactions: concurrency, locking1
© 2010 IBM Corporation 19
DB2 Information Management
Concurrency Issues...
• Phantom Read• Occurs when a search based on some criterion returns
additional rows after consecutive searches during a transaction
1) Application A executes a query that reads a set of rows basedon some search criterion
2) Application B inserts new data that would satisfy application A's query
3) Application A executes its query again, within the same unit of work, and some additional phantom values are returned
Transactions: concurrency, locking1
© 2010 IBM Corporation 20
DB2 Information Management
Concurrency Control
■ Isolation Levels– determine how data is locked or isolated from other concurrently executing
processes while the data is being accessed– are in effect while the transaction is in progress
■ There are four (or five?) levels of isolation in DB2:– Repeatable Read (RR) ANSI Serializable– Read Stability (RS) ANSI Repeatable Read– Cursor stability (CS)* ANSI Read Committed
• Currently Committed)** Equivalent to Oracle “Statement Level Snapshot” IL***– Uncommitted read ANSI Read Uncommitted
– * default Isolation Level value up to DB2 9.5– ** new in DB2 9.7 and new default Isolation Level– *** Not available in ANSI Standards
Transactions: concurrency, locking1
© 2010 IBM Corporation 21
DB2 Information Management
Locking
• Isolation levels are enforced by locks• locks limit or prevent data access by concurrent users or
application processes
• Locking Attributes • resource being locked is called object• objects which can be explicitly locked are databases, tables and
table spaces• objects which can be implicitly locked are rows, index keys, and
tables• implicit locks are acquired by DB2 according to isolation level and
processing situations• object being locked represents granularity of lock• length of time a lock is held is called duration and is affected by
isolation level
Transactions: concurrency, locking1
© 2010 IBM Corporation 22
DB2 Information Management
Types of Locks
• Share (S)• concurrent transactions are limited to read-only operations
• Update (U)• concurrent transactions are limited to read-only operations • if the transactions have not declared that they might update a row, the database
manager assumes that transaction currently looking at a row might update it
• Exclusive (X)• concurrent transactions are prevented from accessing the data in any way• does not apply to transactions with an isolation level of UR
• Database manager places exclusive locks on every row that is inserted, updated, or deleted
Transactions: concurrency, locking1
(simplified overview)
© 2010 IBM Corporation 23
DB2 Information Management
Types of Locks... Transactions: concurrency, locking1
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
then search for "Lock management"(detailed view)
© 2010 IBM Corporation 24
DB2 Information Management
Types of Locks... Transactions: concurrency, locking1
lock compatibility
© 2010 IBM Corporation 25
DB2 Information Management
Deadlock
Deadlock Detector• discovers deadlock cycles
• randomly selects one of the transactions involved to roll back and terminate
• transaction chosen is then sent an SQL error code, and every lock it had acquired is released
Transactions: concurrency, locking1
A deadlock occurs when Job A requests a lock for a data page held by Job B, and Job B requests a lock for a data page held by Job A. Both jobs are waiting on the other to finish before they can proceed.A deadlock must be resolved before either program can perform subsequent processing. DB2's solution is to target one of the two programs as the victim of the deadlock and deny that program's lock request by setting the SQLCODE to -911.
What is a deadlock?A deadlock occurs when two separate processes compete for resources held by one another. For example, consider the following processing sequence for two concurrently executing application programs:
Job AUpdate Table A/Page 1Lock established Intermediate processing Update Table B/Page 1Lock (wait)
Job B Update Table B/Page 1 Lock established Intermediate processing Update Table A/Page 1 DEADLOCK Lock (wait)
© 2010 IBM Corporation 26
DB2 Information Management
Repeatable Read
• Highest level of isolation• No dirty reads, non-repeatable reads or phantom reads
• Locks the entire table or view being scanned for a query
• Provides minimum concurrency
• When to use Repeatable Read:• Changes to the result set are unacceptable• Data stability is more important than performance
Transactions: concurrency, locking1
© 2010 IBM Corporation 27
DB2 Information Management
Read Stability
• Similar to Repeatable Read but not as strict• No dirty reads or non-repeatable reads• Phantom reads can occur
• Locks only the retrieved or modified rows in a table or view
• When to use Read Stability:• Application needs to operate in a concurrent environment• Qualifying rows must remain stable for the duration of the unit
of work• Only issue unique queries during a unit of work
• If the same query is issued more than once during a unit of work, the same result set should not be required
Transactions: concurrency, locking1
© 2010 IBM Corporation 28
DB2 Information Management
Cursor Stability
• Default isolation level up to DB2 9.5• No dirty reads• Non-repeatable reads and phantom reads can occur
• Locks only the row currently referenced by the cursor
• When to use Cursor Stability:• Want maximum concurrency while seeing only committed data
Transactions: concurrency, locking1
© 2010 IBM Corporation 29
DB2 Information Management
Currently Committed: new default for DB2 9.7 and higher• Currently Committed is a variation on Cursor Stability
• Increase TX concurrency (requires specific application logic)• Log based:
• No management overhead
YesWriter blocks WriterYesWriter blocks Reader
MaybeReader blocks WriterNoReader blocks Reader
ResultSituation
YesWriter blocks WriterNoWriter blocks ReaderNoReader blocks WriterNoReader blocks Reader
ResultSituation
Cursor Stability Currently Committed
Transactions: concurrency, locking1
ANSI standardImplemented in DB2 from V1
Not an ANSI standardImplemented in Oracle since its birth
Now implemented also in DB2
© 2010 IBM Corporation 30
DB2 Information Management
• Up to DB2 9.5• Cursor Stability is the default isolation level
• Now in DB2 9.7• Currently Committed is the default for NEW databases• Currently Committed is disabled for upgraded databases, i.e.,
Cursor Stability semantics are used
• Applications that depend on the old behavior (writers blocking readers) will need to update their logic or disable the Currently Committed semantics
Currently Committed Transactions: concurrency, locking1
© 2010 IBM Corporation 31
DB2 Information Management
No deadlocks, no timeouts in this scenario!No deadlocks, no timeouts in this scenario!
Currently Committed Semantics An example
commitcommit
select * from T1 where col5 = ? and col2 = ?
select * from T2 where col2 >= ?
update T2 set col1 = ? where col2 = ?
update T1 set col1 = ? where col2 = 2
Transaction BTransaction A
No lockingReads last committed version
of the data
No lockingReads last committed version
of the data
Transactions: concurrency, locking1
© 2010 IBM Corporation 32
DB2 Information Management
Currently Committed – How to use it?
• cur_commit – DB config parameter• ON: default for new DBs created in DB2 9.7 – CC semantics in place• DISABLED: default value for existing DBs – old CS semantics in place
• PRECOMPILE/BIND• CONCURRENTACCESSRESOLUTION: Specifies the concurrent
access resolution to use for statements in the package.• USE CURRENTLY COMMITTED• WAIT FOR OUTCOME
Transactions: concurrency, locking1
© 2010 IBM Corporation 33
DB2 Information Management
Uncommitted Read
• Lowest level of isolation• Dirty reads, non-repeatable reads and phantom reads can occur
• Locks only rows being modified in a transaction involving DROP or ALTER TABLE
• Provides maximum concurrency
• When to use Uncommitted Read:• Querying read-only tables• Using only SELECT statements • Retrieving uncommitted data is acceptable
• Uncommitted Read with Read-Write tables• UR behaves like CS with updateable cursors
Transactions: concurrency, locking1
© 2010 IBM Corporation 34
DB2 Information Management
Isolation Levels
Summary
Uncommited Read (UR)Repeatable Read (RR) or Read Stability (RS)
Read-only transactions
Cursor Stability (CS)Read Stability (RS)Read-write transactions
High data stability not required
High data stability required
Application Type
PossiblePossiblePossibleUncommitted read (UR)
PossiblePossible -Cursor Stability (CS) and CS with Currenlty Committed
Possible--Read Stability (RS)---Repeatable Read (RR)
Phantom Read
Non-repeatable Read
Dirty ReadIsolation Level
DEFAULT
Transactions: concurrency, locking1
© 2010 IBM Corporation 35
DB2 Information Management
Specifying Isolation Levels
• Precompile / Bind• ISOLATION option of PREP or BIND command• Can determine isolation level of a package by executing the
following query
• Statement Level• Use the WITH {RR, RS, CS, UR} clause• The WITH UR option applies only to read-only operations
• ensure that a result table is read-only by specifying FOR READ ONLY in the SQL statement
• Overrides the isolation level specified for the packageSELECT * FROM tb1 WITH RR
SELECT ISOLATION FROM syscat.packagesWHERE pkgname = 'pkgname'AND pkgschema = 'pkgschema'
Transactions: concurrency, locking1
© 2010 IBM Corporation 36
DB2 Information Management
Specifying Isolation Levels
• Dynamic SQL within the current session• SET CURRENT ISOLATION• For all subsequent dynamic SQL statements within the same
session
• JDBC or SQLJ at run time• SQLJ profile customizer (db2sqljcustomize command)
• CLI or ODBC at run time• CHANGE ISOLATION LEVEL command specified during the
program preparation process
CHANGE ISOLATION LEVEL TO RR
Transactions: concurrency, locking1
© 2010 IBM Corporation 37
DB2 Information Management
Transactions: Recovery
Transactions: Recovery
© 2010 IBM Corporation 38
DB2 Information Management
Concetti Generali di Recovery
• Chiarire che cosa si intende per “Recovery” di un database
• Differenze tra Recovery e Disaster Recovery• L’unità minima di recovery• Recovery di un database vs recovery di un server
• Implementazione di una strategia di Recovery• Asset necessari: log transazionali, salvataggi periodici, dischi/nastri, etc.• Autorizzazioni necessarie
• Capire• che cosa devo predisporre per poter ripristinare un database che
presenta dei problemi e che non è più accessibile• quali informazioni troverò nel database dopo il ripristino
2 Transactions: recovery
© 2010 IBM Corporation 39
DB2 Information Management
DB2 Logging
• Come DB2 gestisce gli aggiornamenti dei dati perché possano essere “recoverabili”
• Log Buffer e Log Files
• Log Circolare vs Log Archiviabile
• Che cosa succede quando si esegue un Roll Back
• Che cosa significa Crash Recovery
• Automatismi di gestione delle sequenze di DB2 V8 e V9
2 Transactions: recovery
Il "logging" (registrazione continua delle attività sul db) è il meccanismo fondamentale per il recovery, e anche per il backup / restore
© 2010 IBM Corporation 40
DB2 Information Management
DB2 Logging – Overview 2 Transactions: recovery
Log Buffer Buffer Pool
db2agentdb2loggr
db2pclnr
Online Active LogFiles
Database Files
synchronous write
on COMMIT/ ROLLBACK
MINCOMMIT
SOFTMAX
asynchronous write
when triggered
(chngpgs_threshold)
© 2010 IBM Corporation 41
DB2 Information Management
ARIES – Write-ahead Logging
• DB2 uses ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) as the transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging• ARIES was invented in the IBM Almaden Research Center by Dr. C. Mohan
• Write-ahead logging:• Must force the log record for an update before the corresponding data page gets
to disk: Guarantees Atomicity (all actions in a transaction happen, or none happen)
• Must write all log records for a transaction before commit: Guarantees Durability(if a transactions commits, its effects persist)
• How is it done:• Each log record has an unique Log Sequence Number (LSN)• Each data page contains a pageLSN• System keeps track of flushed LSN: before a page is written, ensure
pageLSN <= flushed LSN
2 Transactions: recovery
© 2010 IBM Corporation 42
DB2 Information Management
PRIMARY
SECONDARY
1
"n" 2
3
1 "n"
Database Configuration:USEREXIT = OFFLOGRETAIN = OFF
DB2 Logging – Circular Logging 2 Transactions: recovery
© 2010 IBM Corporation 43
DB2 Information Management
ONLINE ARCHIVEContains informationfor committed trx.Stored in the ACTIVElog subdirectory.
OFFLINE ARCHIVEArchive moved fromACTIVE log subdirectory.(may also be on other media)
12
13
14
15
16
DB2 calls db2uext2
ACTIVEContains information for non-committed trx
Database Configuration:USEREXIT = ONLOGRETAIN = RECOVERY + Others for variuosmethods – Use wizard
DB2 Logging – Archival Logging 2 Transactions: recovery
© 2010 IBM Corporation 44
DB2 Information Management
Infinite Logging: nuovo da DB2 8.2
• Va configurato con l’apposito Wizard del DB2 CC• Logica di funzionamento
• Contrariamente al vecchio LOG Archiving utilizza i log secondari• Log secondari allocati sino ad esaurimento disco• Se disco esaurito:
• Rollback delle transazioni non completate• Sospensione degli updates per aggiungere spazio disco
• Possibilità di controllare l’uso dello spazio log da parte di una singola transazione
• Vantaggioso in caso di esecuzione di workload molto diversi (es: batch notturni vs online di tipo OLTP)
2 Transactions: recovery
© 2010 IBM Corporation 45
DB2 Information Management
Transaction Log Space Usage
• MAX_LOG (Maximum Log Space Per Transaction) DB CFG Parameter• Maximum active log space consumed by one transaction as a percent of primary log
space• Values: Minimum=0 (ignore), maximum=100
• NUM_LOG_SPAN - Number of Logs Spanned – DB CFG Parameter• Number of active log files one active unit of work is allowed to span (Also useful for
eliminating idle applications that span multiple logs)• Values:
Minimum=0 (ignore), maximum=65535
• DB2_FORCE_APP_ON_MAX_LOG – db2set Variable• Determines error handling when MAX_LOG is reached
• TRUE (default):Any application exceeding the MAX_LOG will be forced off and rolled back
• FALSE:The application will receive a SQL1224N error message (Statement that caused the problem will fail but the application can still issue a ROLLBACK or COMMIT)
2 Transactions: recovery
© 2010 IBM Corporation 46
DB2 Information Management
Block Transactions On Log Disk Full• DB2 will not report any error because of disk full in the active log
path• Will attempt to create the log file again after 5 minutes• Will continue this behavior until log is successfully created• Record written to the db2diag.Log whenever a log create fails
• Applications that are updating data will not complete their operation until the log has been created• Read-only queries are normally not affected• Read-only queries may block if reading query is dependant on rows being locked by an
update transactions
• On V7 set via DB2 registry variable: DB2_BLOCK_ON_LOG_DISK_FULL=[ON|OFF]
• On V8, V9 database configuration parameter:BLK_LOG_DSK_FUL
2 Transactions: recovery
© 2010 IBM Corporation 47
DB2 Information Management
Roll Back & Crash Recovery
• Definizione: Una transazione si dice Recuperabile/Ripristinabile (ricoverabile) quando tutti gli updates generati sono stati scritti nei Log transazionali. In tal caso la transazione si dice anche “esternalizzata”. Una transazione è “completata” quando è stato eseguito un “Commit”per confermare gli aggiornamenti o un "Roll Back" per rimuoverli.
• Durante il Roll Back, DB2 rilegge tutti i log files che contengono informazioni sulla transazione alla ricerca dello stato del dato precedente ciascun aggiornamento.
• Se l’istanza DB2 viene chiusa forzatamente, alla successiva riattivazione del Database DB2 esegue un processo di Crash Recovery che comporta la rilettura del LOG per poter eventualmente effettuare un RollBack di transazioni non completate per avere certezza della consistenza del DB stesso. Durante tale fase il DB non è accessibile.
2 Transactions: recovery
© 2010 IBM Corporation 48
DB2 Information Management
Sxxxxxxx.LOG0000000
9999999
S ~0.LOG
BACKUP
RESTART NAMING SEQUENCE
When roll-forward is enabled
When S9999999.LOG is filled
When roll-forward is disabled
RESTORE DATABASEwithout rolling forward
ROLLFORWARD DATABASETo some point in S ~ 2.LOG
S ~1.LOG S ~2.LOG S ~3.LOG S ~4.LOG
Log File Naming 2 Transactions: recovery
© 2010 IBM Corporation 49
DB2 Information Management
Closing Active Log File On Demand
• Command allows users to close and archive the active log at any time• Allows users to acquire a complete set of log files up to the point
in time at which the command is executed• Only useable on recoverable databases • Command:db2 archive log for database <db-name>
• Usage notes• If user exit is enabled, an archive request is issued after the logs
are closed and truncated• Performance may be impacted during execution of the command
2 Transactions: recovery
© 2010 IBM Corporation 50
DB2 Information Management
Dual Logging
• High availability feature to prevent log failures in providing asecond copy of log files • If an error is encountered on either log path, that path will no longer be
used until the database attempts to access the next log file
• On DB2 V7 enabled through the DB2 registry variable DB2_NEWLOGPATH2=ON• Secondary path is current LOGPATH concatenated with the character '2‘
For example: if LOGPATH is "/db2/U9C/log_dir", then the secondary path for logging will be "/db2/U9C/log_dir2“
• On DB2 V8,V9 set via the MIRRORLOGPATH database configuration parameter• Can be set to any valid file system/ directory name
2 Transactions: recovery
© 2010 IBM Corporation 51
DB2 Information Management
DB2 Roll Forward Versus Restart Recovery
Two types of DB2 recovery:• Restart recovery (aka crash recovery)
• DB2 reruns all transactions that were performed previously but have not been written to disk
• The DB2 log control file header contains the log sequence number, which must be used as the starting point of the recovery
• Performed automatically during database restart (if DB parameter AUTORESTART is set to ON)
• Restore & roll forward recovery• Manually (using DB2 restore and DB2 roll forward commands)
2 Transactions: recovery
© 2010 IBM Corporation 52
DB2 Information Management
DB2 Restore / Roll Forward Workflow
1. Use db2 list history backup command to look for a backup image to use
2. Issue db2 restore database command to restore DB2
3. Retrieve the log files you need using db2uext2(for online backups at the least those requested in the history file)
4. Roll forward the database using the db2 rollforward command
5. Complete the roll forward and restart the database
2 Transactions: recovery
© 2010 IBM Corporation 53
DB2 Information Management
Roll Forward Pending
• Roll forward pending is set as a result of:• Restore of OFFLINE DATABASE backup omitting the command option
"WITHOUT ROLLING FORWARD"• Restore of an ONLINE DATABASE backup• Restore of ANY TABLE SPACE level backup• DB2 detects media failure isolated at a TABLE SPACE
• Scope of pending state managed by DB2:• DATABASE in pending state will not permit any activity• TABLE SPACE(s) in pending state will permit access to other table spaces
2 Transactions: recovery
© 2010 IBM Corporation 54
DB2 Information Management
Roll Forward – How Far?• End of Logs
• "End" means the end of the current log path• Other logs may need to be moved in the path
• Point in time• Specified in Coordinated Universal Time (UTC) or Local Time (Version 8)• Format: yyyy-mm-dd-hh.mm.ss.nnnnnn
• ONLINE backup• Requires roll forward past end of backup• Recovery history file is useful
• Table Space Point in time• Minimum roll forward time maintained for each table space• Backup required after roll forward
• Minimum roll forward time determined by DB2• Can roll forward a subset of table spaces to a specified point in time• Min PIT is the last DDL change or end of backup, whichever is later
2 Transactions: recovery
© 2010 IBM Corporation 55
DB2 Information Management
Nuovo comando RECOVER
• Introdotto con DB2 8.1• Unisce in un unico comando le operazioni di:
• Restore• Roll Forward• Scelta automatica dell’immagine di backup compatibile con il tipo di
forward recovery richiesto
2 Transactions: recovery
© 2010 IBM Corporation 56
DB2 Information Management
Backup & Restore
Backup & Restore
© 2010 IBM Corporation 57
DB2 Information Management
Architectural Components of Backup/Restore (and Recovery) in DB2
Data Files(Data Pages)
Recovery History File
Recovery(Transaction)
Logs
Backup / Restore
3 Backup & Restore
© 2010 IBM Corporation 58
DB2 Information Management
Concepts
• What kind of backup strategies can I perform?• Cold backup vs Hot backup• Full Backup vs Partial Backup
• For each kind of backup strategy, which are the prerequisite?• Circular vs Archive Log methods
• With each backup strategy, which kind of recovery can I perform?• Simple offline restore• Full vs partial Restore• What about the level of updates recovered?
• Forward recoveries
3 Backup & Restore
© 2010 IBM Corporation 59
DB2 Information Management
Archive Log (aka Log Retain)- Fully recoverable, DB, TS
- Dual logging & mirrored logging
- 3 recovery log states
1 - Active
2 - Online archived
3 - Off-line archived
Circular- Full "crash" recovery,
- No roll-forward recovery
Defined at database level
Logging Options & Components
Backup and Recovery: How to 3 Backup & Restore
© 2010 IBM Corporation 60
DB2 Information Management
DB2 Archive Log
RecoveryLogFiles
DB Shared Memory
Buffer Pool
Package Cache, DB Heap, etc.
Data Base Level
Log Buffer
db2pgclnr
[softmax]
db2loggerlogarch
method1
Data FilesLog
Control File
Archive Log Files
Active LogNon-Committed or Non-Externalized Transactions
Online Archive LogCommitted & Externalized Transactions
Off-line Archive LogFiles moved from active log
subdirectory to another media
3 Backup & RestoreArchive Logging
© 2010 IBM Corporation 61
DB2 Information Management
Backup and Recovery: How to...
●Excellent learning tools
●Do all of your backups, restores and even roll-forward recovery this way
●Perform and generate most of the command lines shown in these slides with these GUI tools
Launch all four from the Control Center
3 Backup & Restore
DB2 CC Logging-Backup-Recover Configuration Wizards
© 2010 IBM Corporation 62
DB2 Information Management
Backup and Recovery: How to...
●Excellent learning tool
●Perform and generate all of the logging config command lines shown in these slides with this GUI tool
●No need to learn config. parameters for DB2 logging up front
Screens walk you through logging setup
3 Backup & Restore
DB2 Control Center Logging Configuration Wizard
© 2010 IBM Corporation 63
DB2 Information Management
Number of secondary logs allocated if needed. -1 is unlimited active logs.Number of primary logs. Also determines number of logs in active log path.
Multi-function parameter used for rollforward and archived log retrieval operations.
Number of active log files that an active transaction can span. 0 means no limit.
Number of attempts that will be made to archive log files using the specified log archive method before they are archived to the path specified by the failarchpath.
Allows you to delay the writing of log records to disk until a minimum number of commits have been performed.
Location to change for log path away from default SQLOGDIR.Location where an identical copy of the logs will be kept.
Percentage of primary log space that can be consumed by one transaction. 0 means no limit.
The size of each configured log, in number of 4-KB pages.Amount of memory to use as a buffer for log records in 4-KB pages.
Works with logarchmeth1 & 2 for vendor options if using TSM or VENDOR values.
These parameters cause the database manager to archive log files to locations that are not the active log path. Values are OFF, RETAIN, USEREXIT, DISK, TSM, VENDOR
Alternate directory for the archive log files if the log archive method specified fails.
Prevent disk full errors from being generated.Wait in second between archive log failure attempts. 0 means no retry.
Usage
2logsecondSecondary Logs
NULLoverflowlogpathOverflow Log Path
3logprimaryPrimary Logs
1mincommitsNumber of Commits to Group
5numarchretryNumber of Archive Retries
NULLnewlogpathNew Log Path
0num_log_spanNumber of Log Span
NULLmirrorlogpathMirror Log Path
0max_logMax Logs per Transaction1000logfilsizLog File Size8logbufszLog Buffer
NULLlogarchopt1logarchopt2
Log Archive Options 1 & 2
OFFlogarchmeth1logarchmeth2
Log Archive Methods 1 & 2
NULLfailarchpathFailover Archive PathNOblk_log_dsk_fulBlock on Log Disk Full 20archretrydelayArchive Retry Delay
DefaultParameterFunction
3 Backup & RestoreDB2 Logging Parameters
© 2010 IBM Corporation 64
DB2 Information Management
Offline Backup (aka Cold Backup)The simplest backup
• Circular logging - usually (except to make a base backup for an archive logged database)
• Disconnect all users from database (or quiesce or stop)• Use Backup command
• At database or table space level• SYSADM, SYSCTRL or SYSMAINT authority needed
• Some consider a manual copy of data files to be a “cold backup” and you can do this with DB2 also, but this really is not a “DB2 backup”.
BACKUP DATABASE SAMPLE TO c:\db2backup;BACKUP DATABASE SAMPLE TO c:\db2backup;
3 Backup & Restore
© 2010 IBM Corporation 65
DB2 Information Management
Online Backup (aka Hot Backup) Archive Logging Configuration Required
• Set LOGARCHMETH1 to enable archive logging• Set required logging parameters
• LOGFILSZ• LOGBUFSZ• LOGPRIMARY• LOGSECOND
• Set optional logging parameters• NEWLOGPATH• MIRRORLOGPATH• BLK_LOG_DSK_FULL• OVERFLOWLOGPATH
• Take a complete offline database backup• quiesce database…• backup database…• unquiesce database…
• Don’t forget the logging wizard can help you do all of this!
3 Backup & Restore
© 2010 IBM Corporation 66
DB2 Information Management
Online BackupOperation & Scope
• BACKUP command enabled• Database or table space driven
• Logs hold changed pages for committed transactions• Very first full backup done offline
• Contains• Changed data pages• Unchanged data pages • System files• History files • Control files• Can include logs
• Can be throttled• Still, minimum activity usually desired during online backup• Parallelization is automatic but can be overridden if desired
• Requires sysadmin, sysctrl or sysmaint
3 Backup & Restore
© 2010 IBM Corporation 67
DB2 Information Management
Online Backup: Full vs PartialExamples
• Online Database backup
• Online table space backup including logs
• Online database backup compressed & throttled
BACKUP DATABASE SAMPLE ONLINE TO c:\db2backup;
BACKUP DATABASE SAMPLE ONLINE TO c:\db2backup;
BACKUP DATABASE SAMPLE TABLESPACE userspace1 ONLINE TO c:\db2backup INCLUDE LOGS;
BACKUP DATABASE SAMPLE TABLESPACE userspace1 ONLINE TO c:\db2backup INCLUDE LOGS;
BACKUP DATABASE SAMPLE ONLINE TO c:\db2backupCOMPRESS UTIL IMPACT PRIORITY 50;
BACKUP DATABASE SAMPLE ONLINE TO c:\db2backupCOMPRESS UTIL IMPACT PRIORITY 50;
3 Backup & Restore
© 2010 IBM Corporation 68
DB2 Information Management
Restore
• Full Database• From offline backup• From online backups: need considerations
• Partial Database• Tablespace level• From offline or online backup: need considerations
• Forward recovery• Optional step when restoring full offline backups and Archive Log
is enabled• Mandatory step when restoring full online backups: • Mandatory step when restoring partial/tablespace backups
3 Backup & Restore
© 2010 IBM Corporation 69
DB2 Information Management
Restore...
• Stop or quiesce database• Use TIMESTAMP from history file of backup desired
(easy to use)• Use Restore command:
(no roll-forward necessary)
• Can relocate containers during restore (“redirected restore”) • DB2 can generate a script for you to do this
• Can restore to a new database in one easy command
RESTORE DATABASE SAMPLE FROM "c:\db2backup" TAKE AT 20040901144022 WITHOUT PROMPTING;
RESTORE DATABASE SAMPLE FROM "c:\db2backup" TAKE AT 20040901144022 WITHOUT PROMPTING;
3 Backup & Restore
© 2010 IBM Corporation 70
DB2 Information Management
Restore / RecoverDatabase Considerations
• Database restore/recover done offline from online backup• Full restore/recover brings up & applies all crucial files
automatically• DB2 RESTORE: used with roll-forward for more control• DB2 RECOVER: for ease of use
RECOVER DB SAMPLE TO END OF LOGS;RECOVER DB SAMPLE TO END OF LOGS;
RESTORE DATABASE SAMPLE FROM c:\db2backup TAKEN AT 20041201194626 WITHOUT PROMPTING;
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE;
RESTORE DATABASE SAMPLE FROM c:\db2backup TAKEN AT 20041201194626 WITHOUT PROMPTING;
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE;
• RECOVER & ROLLFORWARD have two types:• End of logs• Point in time
3 Backup & Restore
© 2010 IBM Corporation 71
DB2 Information Management
Online RestoreTable Space Considerations
• Table space restore can be performed online like this:
RESTORE DATABASE SAMPLE TABLESPACE(userspace1) ONLINEFROM c:\db2backup TAKEN AT 20040901201428 WITHOUT PROMPTING;
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE TABLESPACE(userspace1) ONLINE;
RESTORE DATABASE SAMPLE TABLESPACE(userspace1) ONLINEFROM c:\db2backup TAKEN AT 20040901201428 WITHOUT PROMPTING;
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE TABLESPACE(userspace1) ONLINE;
• Roll-forward has two types:• End of logs• Point in time
• You can also rebuild an entire database from table space backups!
3 Backup & Restore
© 2010 IBM Corporation 72
DB2 Information Management
Incremental Backups
DB2 Incremental & Delta Backups
3 Backup & Restore
Backup all pages changed since the last full backup
Backup all pages changed since the most recent full, incremental or delta back up
© 2010 IBM Corporation 73
DB2 Information Management
Incremental BackupsUsing
• Enable database tracking by setting database parameter TRACKMOD to YES
• Full, then incremental database backups:
UPDATE DB CFG FOR SAMPLE USING TRACKMOD YES;UPDATE DB CFG FOR SAMPLE USING TRACKMOD YES;
BACKUP DATABASE SAMPLE ONLINE INCREMENTAL TO c:\db2backup;BACKUP DATABASE SAMPLE ONLINE INCREMENTAL TO c:\db2backup;
BACKUP DATABASE SAMPLE TO c:\db2backup;BACKUP DATABASE SAMPLE TO c:\db2backup;
BACKUP DATABASE SAMPLE ONLINE INCREMENTAL DELTA TO c:\db2backup;BACKUP DATABASE SAMPLE ONLINE INCREMENTAL DELTA TO c:\db2backup;
Pls. Note: immediately after having set the Trackmode to yes a Full backup is required in order to get synchronized with the changed pages that will be tracked from now on
3 Backup & Restore
© 2010 IBM Corporation 74
DB2 Information Management
Incremental RestoreUsing
• To restore without loss of data• Apply full backup• Use AUTOMATIC keyword to automatically restore from the full backup and apply
incremental backup images, then Roll-forward
• Don’t forget the Restore Wizard can help you!
3 Backup & Restore
RESTORE DATABASE SAMPLE INCREMENTAL AUTOMATIC FROM c:\DB2Backup TAKEN AT 20040901230402 WITHOUT PROMPTING;
RESTORE DATABASE SAMPLE INCREMENTAL AUTOMATIC FROM c:\DB2Backup TAKEN AT 20040901230402 WITHOUT PROMPTING;
For automatic restore, the timestamp is of The most recent incremental or delta backup
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE;
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND COMPLETE;
© 2010 IBM Corporation 75
DB2 Information Management
Recover Dropped Table
• Dropped table history is maintained (if DROPPED TABLE RECOVERY is on)
• Restore table space from previous backup
3 Backup & Restore
• Unload dropped Table data
• Recreate table using DDL from recovery history file• Load the data
ROLLFORWARD DB SAMPLE TO END OF LOGS AND COMPLETE RECOVER DROPPED TABLE 00000000000002010002000d TO c:\db2backup;
ROLLFORWARD DB SAMPLE TO END OF LOGS AND COMPLETE RECOVER DROPPED TABLE 00000000000002010002000d TO c:\db2backup;
LOAD FROM c:\db2backup\node0000\data OF DEL METHOD p(1) INSERT INTO recoverme;LOAD FROM c:\db2backup\node0000\data OF DEL METHOD p(1) INSERT INTO recoverme;
© 2010 IBM Corporation 76
DB2 Information Management
Backup & RestoreSupporting Utilities
• db2ckbkp (check backup)• Checks for completeness of backup images (which also may contain logs)
• db2ckrst (check incremental restore image)• Helps manage log chains to make sure they are complete for any given incremental restore
• db2adutl (Tivoli Storage Manager object management)• Allows users to query, extract, verify, and delete backup images, logs, and load copy images
saved using Tivoli Storage Manager
• db2tapemgr• Stores archived log files to tape
• db2pd (problem determination)• -LOGS option gives current information on all logs
Self tuning features extended to BACKUP utility
• Dramatic performance benefits• In worst case scenario, 4 times
performance gain• Automatic setting of:
• Buffer size• Number of buffers• Parallelism
3 Backup & Restore
© 2010 IBM Corporation 77
DB2 Information Management
Backup and RecoveryRecovery History Information
Recovery History File updated during• Database backup, restore, roll-forward or recovery• Table space backup, restore or roll-forward• Table space is created, dropped, altered, renamed or quiesced • Table is loaded, dropped or reorganized• Log file is archived• New log file is written to
list history backup all for sample;list history backup all for sample;
select * from table(sysproc.admin_list_hist()) as history; select * from table(sysproc.admin_list_hist()) as history;
3 Backup & Restore
© 2010 IBM Corporation 78
DB2 Information Management
Backup and RecoveryAutomated Management of Recovery Artifacts
• To automatically prune recovery history information for a specific timeframe, set DB CFG:
• REC_HIS_RETENTN• To manually prune your recovery history file, use this command:
• To automate the cleanup of backup images, log files & load copy images to coincide with the recovery history file, set DB CFG:
• AUTO_DEL_REC_OBJ• To keep a specific number of backups, set DB CFG:
• NUM_DB_BACKUPS
prune history 200412 and delete;prune history 200412 and delete;
Format YYYYMM
3 Backup & Restore
© 2010 IBM Corporation 79
DB2 Information Management
DB2 Recovery Expert Additional DB2 Tool Goes Beyond Core Backup/Restore Functionality
• Recovery of other SQL object types
• Stored procedures, • UDFs• Triggers• UDTs • Grants• Monitors • Indexes • …and more
• Faster recovery options• Mini logs can be used without requiring a restore & roll forward
• e.g. recover a single table with indexes, triggers & grants without a restore• Autonomic “multiple recovery path”
• Has estimated recovery “costs”• You choose path & point-in-time and Recovery Expert does the rest
• Row level recovery• Undo or redo any row or set of rows• Detailed log activity reports
• Grouper utility to recover related objects• Database RI• Application logic RI• Trigger relationships• User defined relationships
• Undo for LOB and long fields• Flash copy backup support• API interfaces
3 Backup & Restore
© 2010 IBM Corporation 80
DB2 Information Management
Security Features
Security Features
© 2010 IBM Corporation 81
DB2 Information Management
IBM Security Framework
Security ComplianceDimostrazione dell’applicazione delle policies, in conformità a standard, normative, leggi, accordi (PCI, FISMA, ecc…)
Identity and AccessCollaborazione sicura di utenti interni ed esterni; accesso controllato e sicuro agli asset aziendali: processi, applicazioni, informazioni…
Data and Information SecurityProtezione e messa in sicurezza dei dati e degli asset informativi; controllo degli accessi; monitoring, auditing, compliance
Application SecurityGestione continua, monitoraggio e audit della sicurezza delle applicazioni in tutto il loro ciclo di vita
Infrastructure SecurityGestione completa delle minacce e delle vulnerabilità sulla rete, sui server e sui sistemi utente
IBM Security Solutions
IBM Security Framework
Controllo AutomazioneVisibilità
4 Security features
© 2010 IBM Corporation 82
DB2 Information Management
Data and Information Security
Information
Discovery &
Classification
Define Controls
Enforce Controls
Retention
Monitor , Audit, Report i
Protezione del patrimonio informativo aziendalee conformità a policies, leggi e regolamentazioni
Tivoli Access Manager
IBM Data Server Security
IBM Optim Data Privacy Solutions
Guardium Solutions
FileNet Compliance SolutionsFileNet Records Manager FileNet Records Crawler with IBM Classification Module
Protezione completa per dati ed informazioni Dati strutturati (dbms)Dati semi-strutturati (p.e. xml)Dati non struttutari (documenti, contenuti…)
Mascheramento di dati sensibili nella creazione (ed eventuale esternalizzazione) di ambienti di sviluppo e test
Scoperta, identificazione, classificazione, difesa e controllo di proprietà intellettuale critica e di informazioni aziendali sensibili
Monitoraggio in tempo reale dell’infrastruttura dei dbms aziendali: sicurezza, integrità, compliance
4 Security features
© 2010 IBM Corporation 83
DB2 Information Management
Come utilizzare al meglio le feature di security dei dbms IBM e di specifici tools
• DB2 z/OS e DB2 for Linux, Unix, Win• basic security • encryption, LBAC, identity assertion,
db roles, audit facility…• Informix IDS• Audit Management Expert• Database Encryption Expert• Optim • …
IBM Data Server SecurityData threats
Config. threats
Audit threats
Exec. threats
CountermeasuresProducts
recommended
4 Security features
© 2010 IBM Corporation 84
DB2 Information Management
84
Security Terms used by DB2 (and other RDBMS)
Authentication vs. AuthorizationAuthentication: Determines who the user is by identifying with a password
DB2 use external as defaultAuthorization: Grants privileges to that user once authenticated
Explicit vs. Implicit AccessExplicit: Granted directly to user, role or groupImplicit: Granted when a database or database object is createdIndirect: Inherited through execution of packaged code
Data Control LanguageDCL: Subset of SQL that performs security definitions in the database
Security Views: Where security definitions are kept in the databaseCatalog (DB2): SYSCAT.%AUTH
Schemas: Where database objects are logically grouped togetherTrue (DB2) Creates schemas even if a user does not exist
4 Security features
© 2010 IBM Corporation 85
DB2 Information Management
85
DB2 Security Model Overview
AUTHORIZATION
DB2 Client
Data Encryption
Instance 1SERVER
AUTHENTICATION
Instance 2CLIENT
AUTHENTICATION
Instance 3GSSPLUGIN
AUTHENTICATION
Instance 4KERBEROS
AUTHENTICATION
DB2 Client
Data Encryption
Authenticate Here!
Authenticate Here!
DB2 Client
Data Encryption
DB2 Client
Data Encryption
KerberosServer
Authenticate Here!
AUTHENTICATION
• SYSADM• SYSCTRL• SYSMAINT• SYSMON
• IMPLICIT_SCHEMA• QUIESCE_CONNECT• CREATE_EXTERNAL_ROUTINE• CREATE_NOT_FENCED_ROUTINE• BINDADD
System (instance) Authorities Database Authorities
Object Privileges
Table
• SECADM• DBADM• CONNECT• CREATETAB• LOAD
• ACCESSCTRL• DATAACCESS• SQLADM• WLMADM• EXPLAIN
View Role
Index Package
XSR
Workload
Schema
Sequence
Routine
SecurityLabel
TableSpace
GlobalVariable
Nickname Server
Module Setsessionuser
Plug-ins
Authenticate Here!
4 Security features
© 2010 IBM Corporation 86
DB2 Information Management
86
Security Basics
Groupings:● Groups: Supports groups as defined in the OS● Authorities: Instance or database level groupings of privileges● Roles: Supports roles similar to Oracle, but can grant to users,
groups or other roles● Trusted Contexts: “Trusted Connections” established through
Trusted Contexts, can be tied to a role
Authentication: is plug-in controlled ● Security plug-ins (DLLs or exits) for client and server
authentication as well as group management ● Default plug-ins use OS authentication DLLs● Authentication is by one of these methods
► Client side► Server side► GSS Plug-in► Kerberos server
User definition: defined outside the database
Level: Security in DB2 starts at the instance A role is a database object that groups together one or more privileges and can be assigned to users, groups, PUBLIC, or other roles by using a GRANT statement, or can be assigned to a trusted context by using a CREATE TRUSTED CONTEXT or ALTER TRUSTED CONTEXT statement.
Roles provide several advantages that make it easier to manage privileges in a database system:
• Security administrators can control access to their databases in a way that mirrors the structure of their organizations (they can create roles in the database that map directly to the job functions in their organizations).
• ……….
4 Security features
© 2010 IBM Corporation 87
DB2 Information Management
DB2 Authorities
Instance level
Database level4 Security features
© 2010 IBM Corporation 88
DB2 Information Management
Grant / RevokeThe GRANT statement allows an authorized user to grant privileges. A privilege can be granted to one or more authorization names in one statement; or to PUBLIC, which makes the privileges available to all users. Note that an authorization name can be either an individual user or a group.
The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:
GRANT SELECT ON EMPLOYEE TO USER HERONThe following example grants SELECT privileges on the EMPLOYEE table to the group HERON:
GRANT SELECT ON EMPLOYEE TO GROUP HERON
The REVOKE statement allows authorized users to revoke privileges previously granted to other users.
4 Security features
© 2010 IBM Corporation 89
DB2 Information Management
89
DB2 Label Based Access Control(Also called “DB2 Advanced Access Control Feature *”)
• Label Based Access Control (LBAC)• Also called “DB2 Advanced Access Control Feature”
• A “label” is associated with both user sessions and data rows or columns
• Rules for comparing users & data labels allow access controls to be applied at the row level
SELECT * FROM EMPWHERE SALARY >= 50000 ;
SELECT * FROM EMPWHERE SALARY >= 50000 ;
• Labels may consist of multiple components• 1. Hierarchical (tree)
2. Group (array) 3. Set types
• Row labels appear as a single additional column in a protected table• Regardless of the number of label
components
• User labels are granted by a security administrator• SECADM authority granted by
SYSADM
No LBAC SEC=254 SEC=100 SEC=50 ID SALARY
255 60000
100 50000
50 70000
50 45000
60 30000
250 56000
102 82000
100 54000
75 33000
253 46000
90 83000
200 78000= row returned
* This feature available for Enterprise Server Edition only
4 Security features
© 2010 IBM Corporation 90
DB2 Information Management
Trusted ContextIn a typical three tiers scenario (Web + Appl. Server + DB2) the mid tier authorization is used for all the web users. It is therefore convenient to set up a "trusted context" for security across the application server and the database server environments. Trusted context can be used:
• To avoid/prevent from loss of user identity • To avoid lacking of accountability/auditing • To avoid over granting of privileges to the middle tier’s authid• To enforce security
4 Security features
Basics
Setup CREATE TRUSTED CONTEXT CTX1 BASED UPON CONNECTION USING SYSTEM AUTHID USER1 ATTRIBUTES (ADDRESS '192.0.2.1') WITH USE FOR USER2 WITH AUTHENTICATION, USER3 WITHOUT
AUTHENTICATIONENABLE
UsageOpen/establish the trusted connection
Switch to a user (with or without authentication)
© 2010 IBM Corporation 91
DB2 Information Management
Trusted Context...Trusted Contexts:• Are database objects that provide greater control when you use
restricted and/or sensitive privileges • Especially useful when many application servers use a single user id
• Allow middle-tier servers or applications to assert the identity of the end-user to the database• Can then acquire additional capabilities not allowed outside the trusted context
• Implicitly: Application uses trusted context as-is• Explicitly: Application can switch users without re-authenticating
• Create a “trusted connection” when a connection attribute matches trusted context attributes:• System authorization ID: Represents the user who establishes a database
connection • IP address (or domain name): Represents the host from which a database
connection was established • Data stream encryption: Represents the encryption setting (if any) for the data
communication between the database server and the database client
CREATE TRUSTED CONTEXT appsrvercxBASED UPON CONNECTION USING[1] SYSTEM AUTHID appsrvid
ENABLE[2] ATTRIBUTES (ADDRESS ‘9.13.116.224’ ENCRYPTION HIGH)[3] DEFAULT ROLE appsrvrl[4] WITH USE FOR usermgr WITH AUTHENTICATION ROLE mgrrole;
CREATE TRUSTED CONTEXT appsrvercxBASED UPON CONNECTION USING[1] SYSTEM AUTHID appsrvid
ENABLE[2] ATTRIBUTES (ADDRESS ‘9.13.116.224’ ENCRYPTION HIGH)[3] DEFAULT ROLE appsrvrl[4] WITH USE FOR usermgr WITH AUTHENTICATION ROLE mgrrole;
Trusted Contexts defined having:
[1] System authid – used to establish the connection
[2] Trust attributes – used to establish the connection
[3] Default role (optional)
[4] A list of authids allowed to switch (optional)
Trusted Contexts defined having:
[1] System authid – used to establish the connection
[2] Trust attributes – used to establish the connection
[3] Default role (optional)
[4] A list of authids allowed to switch (optional)
userB userDuserCuserA
Middle Tier
Application Server
Trusted ConnectionwasUserB, context2
Trusted ConnectionwasUserA, context1
Context2:
wasUserB allow userC; userD
Context1:
wasUserA allow userA; userB
DB2 Database
4 Security features
© 2010 IBM Corporation 92
DB2 Information Management
DB2 Secure Sockets Layer (SSL) and Transport Layer Security (TLS)
• DB2 can use both SSL & TLS• Provides Federal Information Processing Standard (FIPS) compliance
• Enable a client to authenticate a server• By exchanging digital certificates
• Provide private communication between client and server• By using AES encryption• Easy setup through configuration parameters
• There is an SSL handshake that does the following:• Client requests an SSL connection listing its SSL version and supported cipher suites• Server responds with a selected cipher suite• Server sends its digital certificate to the client• Client verifies the validity of the server's certificate (server authentication)• Client and server securely negotiate a session key• Client and server securely exchange information using the key selected above
Digital Certificate Database
Encrypted Communication
Signer Certificate Database
iKeyman tool iKeyman toolTCP/IP
SSL (JSSE)
JCC Client
TCP/IP
SSL (GSKit)
DB2 Server
In cryptography, the Advanced Encryption Standard (AES) is an encryptionstandard adopted by the U.S. government.
4 Security features
© 2010 IBM Corporation 93
DB2 Information Management
DB2 Audit• Managed by SYSADM and SECADM
• At Instance level by SYSADM by using db2audit tool• At database level by SECADM by using AUDIT POLICY and AUDIT commands• Audit is granular to the level of table, user, group, role, SYSADM, DBADM, SECADM
• Audit Features• Audit logs are separate for each database• Audit logs location can be changed• Audit logs can be archived• Audit policies can be created to control audit configuration within a database• Individual databases and objects can have their own audit configurations• Auditing individual SQL statements is completely configurable• CURRENT CLIENT special registers allows client information to be put in audit logs
db2audit configure datapath "C:\db2audit" archivepath "C:\db2auditArchive"
db2audit configure scope all status bothdb2audit start[…. SQL statements that trigger auditing]db2audit flushdb2audit stopdb2audit archivedb2audit extract delasc to c:\db2audit from files
c:\db2auditarchive\db2audit.instance. log.0.20071215103050
db2audit configure datapath "C:\db2audit" archivepath "C:\db2auditArchive"
db2audit configure scope all status bothdb2audit start[…. SQL statements that trigger auditing]db2audit flushdb2audit stopdb2audit archivedb2audit extract delasc to c:\db2audit from files
c:\db2auditarchive\db2audit.instance. log.0.20071215103050
db2 CREATE AUDIT POLICY SENSITIVE_DATA_POLICY CATEGORIES EXECUTE WITH DATA STATUS BOTHERROR TYPE AUDIT
db2 AUDIT TABLE EMPLOYEE USING POLICY SENSITIVE_DATA_POLICY
db2 CALL SYSPROC.AUDIT_ARCHIVE('C:\db2auditArchive', -2 )db2 CALL SYSPROC.AUDIT_DELIM_EXTRACT
(NULL,'C:\db2auditarchive‘,null, ‘%20071215%',null)
db2 CREATE AUDIT POLICY SENSITIVE_DATA_POLICY CATEGORIES EXECUTE WITH DATA STATUS BOTHERROR TYPE AUDIT
db2 AUDIT TABLE EMPLOYEE USING POLICY SENSITIVE_DATA_POLICY
db2 CALL SYSPROC.AUDIT_ARCHIVE('C:\db2auditArchive', -2 )db2 CALL SYSPROC.AUDIT_DELIM_EXTRACT
(NULL,'C:\db2auditarchive‘,null, ‘%20071215%',null)
DBAs should ask application developers to
start using CURRENT CLIENT_* registers in applications for better
audits
SYSADM (Instance Level - db2audit) SECADM (Database Level)
4 Security features
© 2010 IBM Corporation 94
DB2 Information Management
Optimizer
Optimizer
© 2010 IBM Corporation 95
DB2 Information Management
Optimizer5
http://www.globalguideline.com/articles/analysis.php?k=Optimization_of_DB2
The optimizer is the heart and soul of DB2. It analyzes SQL statements and determines the most efficient access path available for satisfying each statement.
DB2 accomplishes this by parsing the SQL statement to determine which tables and columns must be accessed. The DB2 optimizer then queries system information and statistics stored in the DB2 system catalog to determine the best method of accomplishing the tasks necessary to satisfy the SQL request.
DB2 Optimizer
© 2010 IBM Corporation 96
DB2 Information Management
Optimizing data access in DB2The notion of optimizing data access in the DBMS is one of the most powerful capabilities of DB2. Remember, you access DB2 data by telling DB2 what to retrieve, not how to retrieve it. Regardless of how the data is physically stored and manipulated, DB2 and SQL can still access that data. This separation of access criteria from physical storage characteristics is called physical data independence.
DB2's optimizer is the component that accomplishes this physical data independence.
If you remove the indexes, DB2 can still access the data (although less efficiently). If you add a column to the table being accessed, DB2 can still manipulate the data without changing the program code. This is all possible because the physical access paths to DB2 data are not coded by programmers in application programs, but are generated by DB2.
Compare this with non-DBMS systems in which the programmer must know the physical structure of the data. If there is an index, the programmer must write appropriate code to use the index. If someone removes the index, the program will not work unless the programmer makes changes. Not so with DB2 and SQL. All this flexibility is attributable to DB2's capability to optimize data manipulation requests automatically.
The optimizer performs complex calculations based on a host of information. To visualize how the optimizer works, picture the optimizer as performing a four-step process:
1 . Receive and verify the syntax of the SQL statement. 2 . Analyze the environment and optimize the method of satisfying the SQL statement. 3. Create machine-readable instructions to execute the optimized SQL. 4. Execute the instructions or store them for future execution.
The second step of this process is the most intriguing. How does the optimizer decide how to execute the vast array of SQL statements that you can send its way?
The optimizer has many types of strategies for optimizing SQL. How does it choose which of these strategies to use in the optimized access paths? IBM does not publish the actual, in-depth details of how the optimizer determines the best access path, but the optimizer is a cost-based optimizer. This means the optimizer will always attempt to formulate an access path for each query that reduces overall cost. To accomplish this, the DB2 optimizer applies query cost formulas that evaluate and weigh four factors for each potential access path: the CPU cost, the I/O cost, statistical information in the DB2 system catalog, and the actual SQL statement.
Optimizer5DB2 Optimizer...
© 2010 IBM Corporation 97
DB2 Information Management
DB2 Access Plan
Provide Information on these objects and parameters
An access plan specifies the order of operations for accessing data necessary to resolve a SQL or XQuery statement
Dynamic statements
Static statements
tables
Indexes
operators
Legend
Different from Oracle, DB2 doesn't use “hints”
DB2 automatically detects outdated statistics and update them on-the-fly! Table
Functions
Optimizer5
© 2010 IBM Corporation 98
DB2 Information Management
DB2 SQL Compiler Process
Parse Query Validate Syntax. Query created and stored in Query Graph Model.
Check Semantics Ensure no inconsistencies, verify data types, etc.
Rewrite Query Alter entire query into a form more easily optimized.
Pushdown Analysis Federated database usage only, remote or local evaluation determined.
Optimize Plan Determine best plan based on cost.
Remote SQL Gen. Federated database usage only, makes possible some part of the final plan to be remote.
Gen. Exec. Code Takes plan and Query Graph Model to build executable access plan.
Optimizer5
© 2010 IBM Corporation 99
DB2 Information Management
Have to do with the Optimizer
Reorg
Runstats
Learning Optimizer (LEO) --> autonomics
Static & Dynamic SQL
Explain facilities
Optimizer5
© 2010 IBM Corporation 100
DB2 Information Management
100
Explain FacilitiesDB2 Explain Tools Overview
● To see the access plan information available for packages of static SQL statements (static mode)
● Shows the actual implementation of access plan, not just optimizer information ● Shows on the fly execute and explain or explain from cache (dynamic mode)● Line command driven● Text output with text based graphs
db2explndb2expln
● Formatter for previously stored explain data from optimizer information only ● Line command driven● Text output with text based graphs
db2exfmtdb2exfmt
● Run from the Control Center or Command Editor or IBM Data Studio● Entirely GUI driven● Provides a visual graph of the plan● Allows drill-down on objects and nodes for more detail
Visual ExplainVisual Explain
Optimizer5
© 2010 IBM Corporation 101
DB2 Information Management
101
Explain FacilitiesDB2 Tool Characteristics
Characteristics Desired Explain Tables
Visual Explain
db2exfmt db2expln
GUI Interface Yes
Text Output Yes Yes
Static SQL supported Yes Yes Yes Yes
Dynamic SQL supported Yes Yes Yes Yes
CLI Applications supported Yes Yes Yes
Detailed Optimizer information supplied Yes Yes Yes
Suited for multiple statement analysis Yes Yes Yes
Information accessible from within application
Yes
Available to DRDA* application requests Yes
“Quick & Dirty” static SQL analysis Yes
* Distributed Database Relational Architecture
Optimizer5
© 2010 IBM Corporation 102
DB2 Information Management
102
Explain FacilitiesDB2 Explain Tables
Explain Table FunctionEXPLAIN_INSTANCE Main control table that has RI to all others.
A DELETE from this table will cascade to all the others.
EXPLAIN_STATEMENT Contains the text of the SQL statement as it exists for the different levels of Explain information.
EXPLAIN_ARGUMENT Represents the unique characteristics for each individual operator, if there are any.
EXPLAIN_OBJECT Identifies those data objects required by the access plan generated to satisfy the SQL statement.
EXPLAIN_OPERATOR Contains all the operators needed to satisfy the SQL statement by the SQL compiler.
EXPLAIN_PREDICATE Identifies which predicates are applied by a specific operator.
EXPLAIN_STREAM Represents the input and output data streams between individual operators and data objects.
“Advise” Tables: tables named ADVISE_* used for explain mode processing shown in following slide
Optimizer5
© 2010 IBM Corporation 103
DB2 Information Management
103
Explain FacilitiesDB2 Dynamic SQL Explain Modes
Explain Mode FunctionNO (default) • Disables explain. No explain info. captured.
• SQL statements executed and real-time statistics are used if available.
YES • Enables explain. Causes explain info. to be captured into explain tables. • SQL statements executed and real-time statistics are used if available..
EXPLAIN • Enables explain. Causes explain info. to be captured into explain tables. • SQL statements not executed.
REOPT • Enables explain. Causes explain info. to be captured into explain tables but only at re-optimization time when host variables are resolved.
• SQL statements executed and real-time statistics are used if available.
RECOMMEND INDEXES • Enables explain. Causes explain info. to be captured into explain tables & populates ADVISE_INDEX table.
• SQL statements not executed.
EVALUATE INDEXES • Enables explain. Uses indexes in ADVISE_INDEX table.• SQL statements not executed.
RECOMMEND PARTITIONINGS • Enables explain. Causes explain info. to be captured into explain tables & populates ADVISE_PARTITION table.
• SQL statements not executed.
EVALUATE PARTITIONINGS • Enables explain. Uses indexes in ADVISE_PARTITION table.• SQL statements not executed.
DB2 Dynamic SQL Explain Control:● Set special register with “SET CURRENT EXPLAIN MODE = [value]”● Set interactively or embed in an application; it can be dynamically prepared and executed.
Optimizer5
© 2010 IBM Corporation 104
DB2 Information Management
104
Explain FacilitiesDB2 Static SQL Explain Bind Values
Explain Bind Values FunctionNO (default) • Disables explain. No explain info. captured.
• SQL statements executed.
YES • Enables explain. Causes explain info. to be captured into explain tables for:Static SQL at prep / bind time Reoptimizable incremental statements at run time
SQL statements executed.
REOPT • Enables explain. Causes explain info. to be captured into explain tables for:Reoptimizable incremental statements at run timeDynamic SQL at run time overriding CURRENT EXPLAIN MODE.
• SQL statements executed.
ALL • Enables explain. Causes explain info. to be captured into explain tables for:Static SQL at prep / bind time Reoptimizable incremental statements at run timeDynamic SQL at run time overriding CURRENT EXPLAIN MODE.
• SQL statements executed.
DB2 Static SQL explain control:● External programs: Explained at bind (or pre-compile) time
In bind (or pre-compile) command, use “EXPLAIN [value]”● SQL PL routines (stored procedures, UDFs and methods): Explained when created
Global level - use registry variable setting “DB2_SQLROUTINE_PREPOPTS=EXPLAIN [value]”Session level - use stored procedure setting “SET_ROUTINE_OPTS(EXPLAIN [value])”
Optimizer5
© 2010 IBM Corporation 105
DB2 Information Management
105
Explain FacilitiesExplain Terminology
OPERATOR TYPE FUNCTIONINSERT, UPDATE, DELETE Perform the operations named.
FETCH Retrieves data from a table, given a specific pointer to the record.
TBSCAN A table scan is examination of an entire table row by row.
Index Scanning –IXSCAN, EISCAN, RIDSCN
IXSCAN points to rows, sorted for fast access. EISCAN operates with multiple ranges of values. RIDSCN retrieves the row pointers (Row IDs) from a set of index records.
XSCAN, XISCAN, XANDOR XML scan & navigation, XML index access, XML ANDing
FILTER Reduces a set of row to only those required.
UNIQUE Removes duplicates
Joins – NLJOIN, MSJOIN, UNION, IXAND, HSJOIN
Nested Loop, Merge Scan, Union (concatenation), Index ANDing (dynamic bit map), Hash
GRPBY Group by rows, and performs column function calculations on the groups.
SORT Sorts rows in the order of specified columns, and optionally eliminates duplicate entries.
TQUEUE Transfers table data between agents
GENROW Generates a table of rows
RETURN Return data from a query to a user
TEMP Stores a set of results for re-use elsewhere in the query.
Timerons An invented relative unit of measure. Timerons are determined by the optimizer based on internal values such as statistics that change as the database is used.
Node Numbered section of each explain plan. To read a plan, start at bottom right and work right to left, bottom to top. There are two kinds of nodes: Operator (the action that is performed) & Operand (the object the action is performed against)
Optimizer5
© 2010 IBM Corporation 106
DB2 Information Management
106
Explain FacilitiesDB2 Visual Explain in IBM Data Studio
SHAPE KEY
Tables: RectanglesIndexes: DiamondsOperators: OctagonsFunctions: HexagonsTqueues: Parallelograms
Operator Type
Timerons
Nodes
Operator #
Optimizer5
© 2010 IBM Corporation 107
DB2 Information Management
107
Explain FacilitiesDB2 Visual Explain Control Center
SHAPE KEY
Tables: RectanglesIndexes: DiamondsOperators: OctagonsFunctions: HexagonsTqueues: Parallelograms
Operator Type
Timerons
Nodes
Operator #
Optimizer5
© 2010 IBM Corporation 108
DB2 Information Management
108
Explain Facilitiesdb2exfmt Output
Rows
Total Cost
I/O Cost
Operator Type
Timerons (can be changed to show other stats)
Operator #
Nodes
Optimizer5
© 2010 IBM Corporation 109
DB2 Information Management
Extra: beyond the "local" optimization"Global Optimizer" in federated environments
Optimizer5
non relational
DB2SQL Srv
relational
Federateddatabase server
local data
database virtuale
Federateddatabase server
• memorizzazione locale di risultati di query federate
• a supporto di prestazioni migliori• trasparenza applicativa
Accesso trasparente a dati eterogenei
Benefici• integrazione senza rimpiazzo delle basi dati esistenti• semplificazione e standardizzazione delle interfacce di
accesso ai dati• visione unificata di dati su sistemi diversi (p.e.
standardizzazione nomenclature)• apertura a dati non strutturati
Aree di applicabilità• architetture applicative• ambienti analitici• acquisizioni e fusioni aziendali• ambienti multi-sito• portali• processi IT• ….
© 2010 IBM Corporation 110
DB2 Information Management
IBM InfoSphere Federation Server
EMP - owner J15USER3
EMPNO
100200300400500
OFFICENO
C200C202C204C206C208
EMPNO EMPNAME
100200300400500
SmithJonesAdamsMillerBennett
CREATE NICKNAME X_EMP FORDB2OS390.J15USER3.EMP
OFFICE - owner J15USER1
CREATE NICKNAME O_OFFICE FORORACLE.J15USER1.OFFICE
SmithJonesAdamsMillerBennett
EMPNAME OFFICENO
C200C202C204C206C208
IBM Federation Server
Oracle
DB2 for z/OSSELECT X_EMP.EMPNAME,
O_OFFICE.OFFICENOFROM X_EMP, O_OFFICE
WHERE X_EMP.EMPNO=O_OFFICE.EMPNO
DB2 Microsoft
A B C
A B C
applicazione
WS II
write read read
Federation Server usa il dbms "engine" del DB2, esteso con:
Global optimization• database statistics• ulteriori fattori di costo
• network bandwidth• data source capabilities• data volumes
• predicate pushdown
Supporto dei “nicknames”
• trasparenza applicativa• i nicknames sono usati
come normali tabelle o viste
Caching eterogeneo• migliori prestazioni per le query federate• estensione delle MQT ad ambienti federati
• memorizzazione permanente di risultati• usate dall’optimizer• enable / disable caching• refresh manuale o per replica
Optimizer5
© 2010 IBM Corporation 111
DB2 Information Management
IBM InfoSphere Federation Server...Prestazioni delle query federate: un esperimento interessante
Non-DB2db
(4GB)SQL-H
workloadIBM Federated Server
Native Query Interface
0.0
0.5
1.0
1.5
2.0
20 15 09 04 03 06 05 11 01 21 08 12 07 18 10 02 16 19 22 14
Ratio of query elapsed time Federated vs. Native interface
TPC-H query number
Tecnologia di“global optimizing”
Optimizer5
• Le prestazioni dipendono dallaconfigurazione e dal tipo di query
• Le query federate sono in un range 20% di quelle native nellamaggioranza dei casi
• Per poche query:Prestazioni peggiori: pushdown vs.network trafficPrestazioni migliori: più efficienteottimizzazione delle query
© 2010 IBM Corporation 112
DB2 Information Management
Wrapper: a library allowing access to a particular class of data sources or protocols (Net8, DRDA, CTLIB...). Gathers information about data source characteristics
Server: represents a specific data source
Nickname: a local alias to data on a remote server (mapped to rows and columns)
Server2Server1
Nicknam
e
Nicknam
e
Nicknam
e
(OptionalLocal Data)
(remote data source 1)
(remote data source 2)
WrapperB
Server3
Nicknam
e
Wrapper A
Table
Federated views
(remote data source 3)
Optimizer5
Federation Terminology
IBM InfoSphere Federation Server...
© 2010 IBM Corporation 113
DB2 Information Management
1. User’s client sends a request which goes to the Federation Server interface
2. Federation Server parses the request and creates query fragments to send to the sources
3. Each source system receives its query fragment, processes it and returns the result
4. Federation Server assembles the final result (which may mean additional processing) and the result is sent back to the client
(OptionalLocal Data)
Server2Server1
Nicknam
e
Nicknam
e
Nicknam
e
(remote data source 1)
(remote data source 2)
WrapperB
Server3
Nicknam
e
Wrapper A
Table
Federated views
(remote data source 3)
IBM InfoSphere Federation Server... Optimizer5
How Federation Server Works
© 2010 IBM Corporation 114
DB2 Information Management
Nicknam
e
Nicknam
e
Table
Rel. WrapperClient library
NR. WrapperClient library
Local + Remote Execution Plans
Federated Server
Non-SQL
Cost-based optimizer
IBM InfoSphere Federation Server...
• Decomposes, rewrites and distributes queries
• Cost-based optimizer chooses query plan with pushdown as appropriate
• Query execution engine drives wrappers, combines results
• Compensates for missing function in data source
• Invokes functions at remote sources as needed
Optimizer5
Data Federation Query Processing
© 2010 IBM Corporation 115
DB2 Information Management
IBM InfoSphere Federation Server...
SERVER
Physical Properties:Federated system configuration
Query Properties:Optimization class, data distribution,operators used, query type,cost models, FIRST N ROWS ?
Statistics:•Table Statistics•Column statistics•Index statistics
Non-Relational WrapperWrapper PlansCost Models
•Characteristics•Cpu/io ratio,•Commrate
•Capabilities•Type/version
Optimizer5
Federated Cost-Based Query Optimization
© 2010 IBM Corporation 116
DB2 Information Management
IBM InfoSphere Federation Server...
• MQT: local table defined by the result of a query
• Can include joins, aggregations over multiple nicknames
• Can be indexed, replicated in partitioned environment
• Optimizer “routes to” them transparently as appropriate
• Can include both local DB2 tables and nicknames
• Can include nicknames to nonrelational objects
• Use to replace remote access with local access
Materialized Query Tables (MQTs) Over Nicknames
Remote data
Remote data
Remotedata
MQT
Local Data
Local Data
Fed server
Nickname
Optimizer5
Local Caching
© 2010 IBM Corporation 117
DB2 Information Management
Multi-site Update
• location transparency: no need to track # update sites• transaction atomicity: all succeed or all fail
V8.x
S1
S2
Federated Server
UPDATE S1
UPDATE S2
UPDATE S1
S1
S2
Federated Server
UPDATE S1
UPDATE S2
UPDATE S1
UPDATE S2
V9.1
Disallowed: transaction atomicity
Cannot be guaranteed
Optimizer5IBM InfoSphere Federation Server...
© 2010 IBM Corporation 118
DB2 Information Management
Part 2
Part 2
Aspects of DB2 database administration
© 2010 IBM Corporation 119
DB2 Information Management
DBA day-by-day tasks
• Storage management
• Backup / Restore
• Performance & Tuning• System and dbm configuration
• Memory mgmt• ...
• Workload mgmt• Disegno fisico dei dati
• Indici, partizioni, MQT, MDC...• Data maintenance
• Reorg, runstats, rebind...
• Monitoring• a livello dbms• di sistema
Automatic Storage
Automatic Backup
Automatic Maintenance
STMM
Advisors
Tools
Health Monitor
Tivoli sw
WorkLoad Manager
© 2010 IBM Corporation 120
DB2 Information Management
DBA high value tasks
• Security management• accessi, compliance, encryption...
• Supporto alla ottimizzazione delle applicazioni• xml data management• Web Applications• ....
• Aspetti architetturali• High Availability / Disaster Recovery• Data volume explosion (Storage)
• Integrazione del "DB2 subsystem" con altri ambienti• DB2 Data WareHouse con ETL e front-end tools• Data replication, Data federation, Data events...• Service Oriented Architectures (--> Info Services)• Master Data Management• ....
• Data Governance
© 2010 IBM Corporation 121
DB2 Information Management
Automated Tasks & Autonomic Capabilities
Automated Tasks & Autonomic Capabilities
© 2010 IBM Corporation 122
DB2 Information Management
122
Autonomic Computing Definition
• Autonomic computing architecture provides a foundation on which self-managing information technology systems can be built
• Self-managing autonomic systems exhibit the characteristics of CHOP:
• Self-Configuring• Self-Healing• Self-Optimizing • Self-Protecting
• Originally, “autonomics” described the human body’s ability to regulate itself, e.g.
• Increased heart and breathing rate for more blood and oxygen flow during a crisis
• Sweating to cool down the body when it gets hot
Self-Configuring
Self-Healing
Self-Protecting
Self-Optimizing
IncreasedResponsiveness
Adapt to dynamic-ally changing environ-ments
Business resiliency
Discover, diagnose& act to prevent
disruptions
Operational efficiencyTune resource & balance workloads to maximize use of IT resource
Secure information and resources
Anticipate, detect, identify and protect against attacks
Autonomic Computing AttributesSelf-managing systems that deliver
As described by: Brent Miller Senior Technical Staff Member, Autonomic Computing Architecture, IBM Corporation
Autonomic computing systems have the ability to manage themselves and dynamically adapt to change in accordance with business policies and objectives.This enables computers to identify and correct problems often before they are noticed by IT personnel. They can also “learn”, adapt and protect themselves for future situations.
Automated tasks & Autonomic capabilities6
© 2010 IBM Corporation 123
DB2 Information Management
123
DB2 Self-Tuning Memory ManagerAutonomics For Managing DB2 Memory Usage
DB2 Self-Tuning Memory Manager uses “out of the box” autonomics which:• Constantly re-evaluated cached based memory usage
• Optimizes current workload up to 60 times an hour• Stops tuning if it reaches optimal configuration
• Is on by default, but can be turned off • Use db parameter SELF_TUNING_MEM OFF
• Can share OS memory with database memory automatically• Use db parameter DATABASE_MEMORY AUTOMATIC
• Can share various cached based database memory heaps with each other • Set these to AUTOMATIC
BUFFER POOLSPCKCACHESZLOCKLISTSHEAPTHRES_SHR SORTHEAP
QUESTION: Do I need to learn the DB2 cached based memory model in order to tune it?
NOTE:
DB2 Self-Tuning Memory Manager manages cache based memory in DB2. Functional based memory used by application
programs are controlled by the parameter APPLICATION_MEMORY, or if this is set to AUTOMATIC then
INSTANCE_MEMORY with control this.
ANSWER: Not at all. DB2 Self-Tuning Memory Manager handles this for you!
Automated tasks & Autonomic capabilities6Perf. & Tuning --> System and dbm cfg --> Memory mgmt
© 2010 IBM Corporation 124
DB2 Information Management
Instance Memory• Is the total amount of memory
addressable by DB2 Instance• 2 GB for Express-C, 4 GB for
Express, 16 GB for Workgroup• Managed by specific Parameter (can be
automatic and managed by STMM)
Database Memory• Is the total amount of memory used
internally by each DB and include:• All the buffers• All the caches• All the sort areas• Locks
• Managed by specific Parameter (can be automatic and managed by STMM)
Application Memory• Is the total amount of memory used by
agents (instance threads) servicing local and remote applications when connected to the DB
• Managed by specific Parameter (can be automatic and managed by STMM)
Basic Memory Usage in DB2 Automated tasks & Autonomic capabilities6
DB2 9.5 introduces Thread Based Engine• One Process per Instance base.• Any other “agent” servicing applications or internally
working for the Instance is a “thread” of the main instance process
Memory is allocated by the Instance process • For itself• On behalf of each thread
Memory consumption is based on three concepts
• Instance Memory• Database Memory• Application Memory
General RuleInstance Memory = Sum(all DB memories + all Application Memories) + overhead (10%)
When more than 1 instance is installedEach must address the Max memory addressable by DB2 Edition installedThe SUM of all the Instance Memory should not be more than real addressable memory (to
avoid OS paging activities)Better run at least WSE on 64 bits serversAvoid relying on 32 bit OS algorythm to go beyond 4GB (like Windows one)
Not efficientDB2 can think to have more real memory to address leading to less optimization and pure
performance
Perf. & Tuning --> System and dbm cfg --> Memory mgmt
© 2010 IBM Corporation 125
DB2 Information Management
125
DB2 Self-Tuning Memory ManagerDB2 “Automatic” Memory Usage
• Setting a few database parameters & the buffer pools to AUTOMATIC• Makes DB2 share the entire system memory, when needed, as shown below
• The database borrows memory from the operating system, or gives it back • The buffer pools share memory with each other, even converting the various page sizes • The other database memory heaps share with each other as well as the buffer pools
* Registry variable db2_mem_tuning_range limits upper and lower ranges of OS memory usage
SHEAPTHRES_SHR
SORTHEAP
(Sorts & Hash Joins)
LOCKLIST (Lock List)
PCKCACHESZ (Package Cache)
BUFFER POOL 1
BUFFER POOL 2
BUFFER POOL 3
BUFFER POOL 4
DATABASE_MEMORY
OPERATING SYSTEM MEMORY
Automated tasks & Autonomic capabilities6Perf. & Tuning --> System and dbm cfg --> Memory mgmt
© 2010 IBM Corporation 126
DB2 Information Management
126
DB2 Self-Tuning Memory ManagerParameter Setting Details
• DB2 Self-Tuning Memory Manager itself is on by default for newly created databases• To turn it off, use the Control Center GUI to change SELF_TUNING_MEM to OFF• Or use the following CLP command:
UPDATE DB CFG FOR DATABASE [db-name] USING SELF_TUNING_MEM OFF;UPDATE DB CFG FOR DATABASE [db-name] USING SELF_TUNING_MEM OFF;
• Buffer pools are set to SIZE AUTOMATIC by default if size is not specified• To freeze this, use the Control Center GUI to alter the buffer pool size to a set page amount• Or use the following CLP command:
ALTER BUFFERPOOL [bufferpool-name] size [pages];ALTER BUFFERPOOL [bufferpool-name] size [pages];
• DATABASE_MEMORY is AUTOMATIC by default for newly created databases• To turn it off, use the Control Center GUI to change DATABASE_MEMORY to COMPUTED• Or use the following CLP command:
UPDATE DB CFG FOR DATABASE [db-name] USING DATABASE_MEMORYCOMPUTED;
UPDATE DB CFG FOR DATABASE [db-name] USING DATABASE_MEMORYCOMPUTED;
Automated tasks & Autonomic capabilities6Perf. & Tuning --> System and dbm cfg --> Memory mgmt
© 2010 IBM Corporation 127
DB2 Information Management
127
DB2 Self-Tuning Memory ManagerParameter Setting Details (Continued)
• Package Cache and Lock List set to AUTOMATIC by default • To freeze their sizes, use Control Center GUI to alter their sizes to a fixed page amount• Or use the following CLP commands:
UPDATE DB CFG FOR DATABASE [db-name] USING PCKCACHE_SZ [pages];UPDATE DB CFG FOR DATABASE [db-name] USING PCKCACHE_SZ [pages];
• Sorting and hash joins are controlled by three different parameters• By default the DBM parameter SHEAPTHRES is set to 0 to put sorts in database shared
memory; it must be set to 0 for STMM to manage sort memory• By default DB parameters SHEAPTHRES_SHR and SORTHEAP are set to AUTOMATIC• Use the Control Center GUI to alter these or the following CLP commands to change them:
UPDATE DBM CFG USING SHEAPTHRES [pages];UPDATE DBM CFG USING SHEAPTHRES [pages];
UPDATE DB CFG FOR DATABASE [db-name] USING SHEAPTHRES_SHR [pages];UPDATE DB CFG FOR DATABASE [db-name] USING SHEAPTHRES_SHR [pages];
UPDATE DB CFG FOR DATABASE [db-name] USING SORTHEAP [pages];UPDATE DB CFG FOR DATABASE [db-name] USING SORTHEAP [pages];
UPDATE DB CFG FOR DATABASE [db-name] USING LOCKLIST [pages];UPDATE DB CFG FOR DATABASE [db-name] USING LOCKLIST [pages];
Automated tasks & Autonomic capabilities6Perf. & Tuning --> System and dbm cfg --> Memory mgmt
© 2010 IBM Corporation 128
DB2 Information Management
Table Space Design in DB2
• For tables in all table spaces (regular, temporary, DMS, SMS)
• Large Tablespaces ONLY for LOB
Automated tasks & Autonomic capabilities6Storage management
DB2 V8.x
DB2 V9.x New Large and Temporary Table Spaces
• For tables in LARGE table spaces (DMS only)
• Also all SYSTEM and USER temporary table spaces
For Large and Temporary Tablespaces:
© 2010 IBM Corporation 129
DB2 Information Management
129
Automatic StorageDB2 Automatic Storage – How does it work?
1. Create a DB2 database with the following to enable it:
… or alter an existing DB2 database with the following to enable it:
CREATE DATABASE … AUTOMATIC STORAGE YES ON [path/drive]…CREATE DATABASE … AUTOMATIC STORAGE YES ON [path/drive]…
2. Create table space with the following:
CREATE TABLESPACE … MANAGED BY AUTOMATIC STORAGE …CREATE TABLESPACE … MANAGED BY AUTOMATIC STORAGE …
ALTER DATABASE … ADD STORAGE ON [path/drive]…ALTER DATABASE … ADD STORAGE ON [path/drive]…
• That’s it! Under the covers, DB2 now creates, names and sizes the data files needed to support the table space without any DBA intervention.
• Path or drive can be any number of subdirectories, mount points or drives
• Path or drive list can be added to later with an ALTER DATABASE command
• DB2 manages all automatic storage containers within this path
• Smart enough to make temporary table spaces SMS, all others DMS
• Automatically stripes containers for you by your number of paths or drives
• Create other DMS or SMS table spaces outside automatic storage if desired
• Health Monitor indicators signal if paths or drives about to become full
Automated tasks & Autonomic capabilities6Storage management
© 2010 IBM Corporation 130
DB2 Information Management
ALTER TABLESPACE <tbspname> REDUCE[New Options]
New Options:MAX – (Only Automatic Storage
table spaces) moves water mark to lowest possible page and removes all data pages above water mark
STOP – (Only Automatic Storage table spaces) Stops extent movement operation
<value> [K|M|G] -(Only Automatic Storage table spaces) Moves Extents
<value> PERCENT -(Only Automatic Storage table spaces) Moves Extents
Affects:Will try to move water mark if possibleWill remove all unused pages above watermark
ALTER DATABASE DROP STORAGE PATH ON ‘path1’,…
If storage path is “Not In Use” then it is immediately removed from database
If storage path is “In Use” then:Storage path state is changed to “Drop Pending”Affected table spaces are marked as “An Underlying
Storage Path(s) has been Dropped”
A “Drop Pending” storage path is removed when no automatic storage table space is using it: Requires REBALANCE
Automatic StorageSome common operations
Automated tasks & Autonomic capabilities6Storage management
Convert table spaces to automatic storageALTER TABLESPACE <tbspname> MANAGED BY AUTOMATIC STORAGEorRESTORE DB <dbname> REDIRECT SET TABLESPACE CONTAINERS FOR
<tablespaceID> USING AUTOMATIC STORAGE
Affects:Converts DMS tablespace to a automatic
storage managed tablespaceNo container operations allowed after conversion
Alter Table Space to enable Automatic Storage
Dropping Storage Paths
Alter Table Space: Reduce
ALTER TABLESPACE <tbspname> REBALANCE
Limitations:Only Automatic Storage
table spacesAffects:
Rebalance data across storage paths
Moves data from previous nonautomatic storage containers to automatic storage paths
D t ff t d t
Alter Table Space: Rebalance
© 2010 IBM Corporation 131
DB2 Information Management
Automatic Storage Rebalance: REBALANCE
C0 C1
Two storage paths and a table space has a container on each
New storage paths not used by the
table space immediately
C1
p1 p2 p1 p2 p3 p4
ALTER DATABASE …ADD p3, p4
ALTER TABLESPACE myts REBALANCE
REBALANCE causes DB2 to create equal-sized
containers in new paths, and redistribute extents to them
p1 p2 p3 p4
ALTER TABLESPACE ... REBALANCE
C0 C1 C2 C3
If table space is not growing rapidly, consider REDUCingit to make space available
for other table spaces
p1 p2 p3 p4
C0 C1 C2 C3
ALTER TABLESPACE ... REDUCE(optional)
C0
High Water Mark
High Water Mark
Automated tasks & Autonomic capabilities6Storage management
© 2010 IBM Corporation 132
DB2 Information Management
C2
p1 p2
C3
p3
C1 C2
p1 p2
C3
p3
C1
p1
C3
p3
C1
ALTER TABLESPACE .. REBALANCE
ALTER DATABASE …DROP p2
Drop Pending
Dropping Storage Paths : Example
ALTER DATABASE <dbname> (options) STORAGE ON <path>New Options:
ADD – Enables database for automatic storage if not previously enabled and/or adds new storage path
DROP – Removes storage pathAffects:
Enables database for automatic storage if not previously enabledAdds new storage pathsDrop removes storage path once all extents have been moved
Alter Table Space: Add or Drop storage
Automatic Storageadding or dropping storage
Automated tasks & Autonomic capabilities6Storage management
© 2010 IBM Corporation 133
DB2 Information Management
Tablespace name = USERSPACE1Tablespace ID = 2...Rebalancer Mode = No RebalancingUnderlying storage path has been dropped = No | YesMinimum Recovery Time =...
Automatic storage path = /storagePath1Node number = 0State = In Use | Not In Use | Drop PendingFile system ID = 64768Storage path free space (bytes) = 66725142528File system used space (bytes) = 50100731904File system total space (bytes) = 317068410880
New tablespace snapshot field
New database snapshot field
SELECT DISTINCT(A.TBSP_NAME)FROM SYSIBMADM.SNAPTBSP A, SYSIBMADM.SNAPTBSP_PART BWHERE A.TBSP_ID = B.TBSP_ID AND
A.TBSP_CONTENT_TYPE IN ('ANY','LARGE') ANDB.PATH_DROPPED = 1
SQL to find tablespaces using dropped paths
Automated tasks & Autonomic capabilities6Storage managementAutomatic Storage: monitoring
Some examples
© 2010 IBM Corporation 134
DB2 Information Management
134
DB2 Automatic Maintenance Autonomics Approach Overview
• DB2 Automatic Maintenance:• Only performs activities that need to be done• Uses “set it and forget it” autonomics
• Determines when (and whether) to run• BACKUPs• RUNSTATs• REORGs
• Optimizes resources by• Phase I - Determining best time to run any given activity in:
• Online window • Offline window
• Phase II - Running only required activities
• Is easy to manage• GUI wizard walks you through the entire process• Saves your settings in a profile & can be run over and over again
• Has a “learning” capability• Internal scheduling continually estimates times to completion• If window not large enough
• Will not run those activities it cannot complete • Will notify you through the Health Monitor
REORG
BACKUP
RUNSTATS
You’ll like me. I’ll make your job
easier!
Automated tasks & Autonomic capabilities6Perf. & Tuning --> Data maintenance --> Reorg & RunstatsBackup / Restore
© 2010 IBM Corporation 135
DB2 Information Management
Automatic Maintenance Setup GUI Wizard vs. Line Command
GUI
LINE COMMAND CONFIGURATIONThere are stored procedures you
can use to manually update automatic maintenance:
AUTOMAINT_GET_POLICYAUTOMAINT_GET_POLICYFILE
AUTOMAINT_SET_POLICYAUTOMAINT_SET_POLICYFILE
Automated tasks & Autonomic capabilities6Perf. & Tuning --> Data maintenance --> Reorg & RunstatsBackup / Restore
© 2010 IBM Corporation 136
DB2 Information Management
136
Automatic Maintenance Concepts Used
All options use these concepts:• Maintenance Windows (or Timing)
• Defined as:• Periods of time for hours of the day, days of the week or month• For online and offline time periods• Inside or outside defined time periods
• DB2 uses this window to consider running selected utilities• This window should be a relatively unused time period for your database
• Notification• List of contacts can be built and used to notify results of auto maintenance actions
• Throttling• Auto maintenance uses “adaptive utility throttling” which it controls all by itself
• The defined maintenance window is used as the overriding priority• Uses a fixed 7% limit on resources
Perf. & Tuning --> Data maintenance --> Reorg & RunstatsBackup / Restore
Automated tasks & Autonomic capabilities6
© 2010 IBM Corporation 137
DB2 Information Management
137
Automatic Maintenance Auto_% DB Configuration Parameters
db cfg~~~~~ ~~
~~~~~~~~~~~~ ~~~
~~ ~~~~ ~auto_tbl_maint
auto_maint
auto_db_backup
auto_runstatsauto_reorg auto_stats_prof
auto_prof_updauto_stmt_stats
AUTO_MAINT Parent to all other auto_% parameters. Sets auto maintenance at a global level.Enabled: All recorded values for child parameters take effect.
AUTO_DB_BACKUP Enabled: Allows for automatic BACKUP operations.
AUTO_TBL_MAINT Parent parameter to all other (non backup) auto_% parameters. Enabled: All recorded values for child parameters take effect.
AUTO_REORG Enabled: Allows for automatic table and index REORG operations.
AUTO_RUNSTATS Enabled: Allows for automatic asynchronous RUNSTATS operations.
AUTO_STMT_STATS Enabled: Allows real-time, synchronous statistics gathering.
AUTO_STATS_PROF Turns on statistical profile generation, designed to improve applications whose workloads include complex queries, many predicates, joins, and grouping operations over several tables.Enabled: Recommendations are stored in the opt_feedback_ranking table.
AUTO_PROF_UPD Enabled: Runstats profile is to be updated with recommendations from opt_feedback_ranking table.
Perf. & Tuning --> Data maintenance --> Reorg & RunstatsBackup / Restore
Automated tasks & Autonomic capabilities6
© 2010 IBM Corporation 138
DB2 Information Management
138
Automatic Maintenance Backup Settings & Workings
BACKUP option uses these settings:• Criteria
• Time elapsed since last full backup since specified by number of hours• Also, if backup has never run before
• Log space used between backups more than specified number of 4K pages• Location
• File System (Disk)• Automatically deletes old copy in chosen directory
• Tape• Tivoli Storage Manager• X/Open Backup Services API (XBSA)• Vendor DLL
• Mode• Online - Archive log (retain) mode supported
• Default maintenance mode for this setting is 24 x 7• Offline – Archive log and circular log both supported
• Uses QUIESCE DATABASE in DEFER mode to allow transactions to complete
Backup / RestoreAutomated tasks & Autonomic capabilities6
© 2010 IBM Corporation 139
DB2 Information Management
139
Automatic Maintenance REORG Settings & Workings
Auto REORG option uses these settings:• All tables
• Can include or exclude system catalog tables• Selected tables
• Can use a simple or custom filter to select range of tables to run against• Custom filter can point to a “control table” which contains only the names of tables you desire
an automatic REORG to be performed on• Has other options for REORG like:
• Size of table limit (helps avoid REORG on very large tables)• Offline storage location (temporary storage location)• Dictionary rebuild/no rebuild (for compressed tables)• Index reorg mode (read or write options)
Auto REORG works by• Using reorgchk_tb_stats and reorgchk_ix_stats stored procedures
• Determines which tables and indexes to perform REORG on• Performs “classic” table REORG on table data
• Only during a specified offline table maintenance window• with no write access during reorganization • with no access at all during shadow copy change-over
• Performs index REORGs during online or offline table maintenance windows• Keeps working data in SYSTOOLS schema tables
Perf. & Tuning --> Data maintenance --> Reorg & RunstatsAutomated tasks & Autonomic capabilities6
© 2010 IBM Corporation 140
DB2 Information Management
Auto RUNSTATS – asynchronous collection• DBA selects range of tables for consideration (using GUI)• Utility is non contentious – uses new special kernel locking protocols• Utility uses a collection algorithm that learns over time how fast the
statistics change• Compares histograms on a per table basis• Determines the minimum set of statistics that give optimal performance
improvement• Asynchronously runs background RUNSTATS for tables determined to need it• Keeps working data in SYSTOOLS schema tables
Auto profile generation customizes your statistics gathering• DBA sets AUTO_STATS_PROF db cfg parameter – gathers
automatically• DBA sets AUTO_PROF_UPD db cfg parameter – updates automatically• Stores data in a “feedback warehouse” in SYSTOOLS, tables
OPT_FEEDBACK_*:• By default, statistics collected for synchronous and asynchronous
operations are basic table statistics with distribution information and detailed index statistics using sampling. This feature customizes this.
Automatic Maintenance RUNSTATS Settings & Workings
Perf. & Tuning --> Data maintenance --> Reorg & RunstatsAutomated tasks & Autonomic capabilities6
© 2010 IBM Corporation 141
DB2 Information Management
Real-time statistics - synchronous collection• DBA sets AUTO_STMT_STATS db cfg parameter• Utility collecting statistics when needed by that query at run time
• Ignores the maintenance windows; runs anytime queries run• Uses many different techniques to maximize synchronous collection
• Like placing statistics in statistics cache and writing to the catalog later• Limiting the time spent to collect statistics (5 seconds by default)• Submitting asynchronous collection if necessary• Fabricating statistics through extrapolation
Automatic Maintenance RUNSTATS Real-Time Statistics Collection
ASAP!
Perf. & Tuning --> Data maintenance --> Reorg & RunstatsAutomated tasks & Autonomic capabilities6
© 2010 IBM Corporation 142
DB2 Information Management
DBA high value tasks
DBA High value tasks
© 2010 IBM Corporation 143
DB2 Information Management
DB2 architectures DBA high-value tasks7High Availability
& Disaster RecoveryAppl
Appl
Appl
DB2
DB2 DB2 DB2 DB2
DB2 DB2
disk
disk disk disk disk
disk
DB2 has three different proposition• Single SMP Server
• Intensive write/update workload• Scale up
• Shared Nothing Clusters• Intensive Read workload (BI)• I/O always in parallel• Scale up & out
• Shared Disk Cluster• Typical OLTP workload.
• 80% read, 20% write• Thousands of users• Highest level on SQL execution concurrency • Very simple SQL• Continuous availability and scalability as main goal• Scale up & out
© 2010 IBM Corporation 144
DB2 Information Management
How DB2 address Availability Issues DBA high-value tasks7High Availability
& Disaster Recovery
Three main scenarios:• High Availability• Disaster Recovery• Continuous Availability
Various possibilities to address each of those• By use of integrated DB2 functions/implementation• By use of external Software
© 2010 IBM Corporation 145
DB2 Information Management
High Availability
Two servers nearby• Can use native DB2 HADR function
• Double writings on two syncronous copies of the same DB• Active-Standby Concept
• Can rely on OS SW• HACMP on IBM AIX, MS CS on Windows etc• Need an external shared disk
• Can be implemented via Scripts• c
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 146
DB2 Information Management
High Availability Disaster Recovery Basics
HADR replication takes place at the database level
TCP/IP
Database E
HADR
Primary
Database A
Standby
Database B
Server 1
Database E
HADR
Standby
Primary
Database A
Database C
Database D
Server 2
HADR HADRTCP/IP
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 147
DB2 Information Management
DB2 HADR Synchronization Modes
disk
Primary Standby
disk
disk disk
DB2
HADR Replication
TCP/IP port
DB2DB2 instance A
DB2 databasefrom instance A
DB2 instance B
DB2 databasefrom instance B
memory
TCP
IP
MAC
PHY
memory
TCP
IP
MAC
PHY
sync
nsync
async
sync
nsync
async1 Transaction3
2
1
Transaction Log
Transaction Log
1 Transaction2
3
23
Log writer Log writer
DBA high-value tasks7High Availability
& Disaster Recovery
TCP/IP port
© 2010 IBM Corporation 148
DB2 Information Management
Disaster Recovery
Two or more Servers located in a WAN• Can be addressed by HADR• Can be addressed by Mirroring/Replicating Disks
on a WAN• Can be address on subset of a Database by use
of Q-Replication SW
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 149
DB2 Information Management
Continuous Availability
Reduce Single Point of Failure• More than two servers in a cluster
Provide Transparent Recoveries• No interrupt on applications
Can handle more than one failure at the same time
Suited for OLTP applications
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 150
DB2 Information Management
DB2 pureScale Architecture
Leverages the global lock and memory manager technology from z/OS
Automatic workload balancing
Shared Data
InfiniBand network & DB2 Cluster Services
Cluster of DB2 nodes running on Power servers
Integrated Cluster Manager
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 151
DB2 Information Management
DB2 pureScale main goals
Address Continuous Availability• By use of numbers of DB2 members• Relying on a centralized lock manager (Global Lock Mgr) and on a centralized
Cache (Global Buffer Pool) provided by Cluster Facilities Members• Specific implementation that tolerates multiple Member/CFs failure• Transparent to applications
Address Scalability• Can add nodes whenever needed without stopping applications• Can scale up (on a single member) or out (by adding members)
Address Workload Balancing• Implicit in DB2 pureScale server architecture and standard DB2 drivers
(CLI/ODBC, .NET and Java)• Transparent to applications
DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 152
DB2 Information Management
DBA high-value tasks7High Availability
& Disaster Recovery
32 Members Over 95% Scalability
64 Members 91% Scalability
88 Members 87% Scalability
112 Members 81% Scalability
Number of Members in the Cluster
16 Members Over 95% Scalability
2, 4 and 8 Members Over 95% Scalability
Scalability for OLTP Applications
© 2010 IBM Corporation 153
DB2 Information Management
Single Failures Toleration DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 154
DB2 Information Management
Simultaneous Failures Toleration DBA high-value tasks7High Availability
& Disaster Recovery
© 2010 IBM Corporation 155
DB2 Information Management
Replication: another way to integration
Needed when integration allow/requires multiple data copies kept in sync
Two technologies so far• SQL Replication• Q – Replication
Can work on both:• Homogeneous RDBMS (eg: DB2)• Etherogeneous RDBMS (eg: Source Oracle, target DB2,
SQL Server etc)
Various products in IBM Portfolio
DBA high-value tasks7Data Replication
data
© 2010 IBM Corporation 156
DB2 Information Management
SQL replication (DB2 – DB2)
Filtering is possible
Vertical (selected colums)
Horizontal (selected rows=
Applied at source (capture) and/or at target (apply)
DBA high-value tasks7Data Replication
© 2010 IBM Corporation 157
DB2 Information Management
Scenarios DBA high-value tasks7Data Replication
© 2010 IBM Corporation 158
DB2 Information Management
Q - Replication DBA high-value tasks7Data Replication
© 2010 IBM Corporation 159
DB2 Information Management
Scenarios DBA high-value tasks7Data Replication
© 2010 IBM Corporation 160
DB2 Information Management
Data Governance
Esplosione dei volumi di dati
Estrazione e Archiviazione degli “oggetti”
applicativi
Scenario / Esigenze• Gestione del ciclo di vita dei dati
in funzione delle applicazioni• crescita dei volumi: archiviazione di dati
raramente acceduti (“svecchiamento”)• creazione di ambienti di test, anche con
protezione dei dati sensibili• dismissione di applicazioni
Soluzione: IBM Optim EDM sw• Optim Data Growth• Optim Test Data Mgmt• Optim Data Privacy
Databasedi
produzione
Applicazione
retrieve
archive Dati di riferimento
Dati direporting
Datistorici
ArchiviStorici
Correnti
Recuperati
XML ODBC/JDBCApplicazione
Accesso universale ai dati applicativi
extractfiles
maskedfiles
Databasedi sviluppo
e di testextract
mask load
Optim Extract - Store - Port - Protect
Data Growth - Data Privacy - Test Data Mgmt - Appl Upgrades - Appl Retirement
DBA high-value tasks7Data volume growth
IBM Optim Enterprise Data Mgmt
© 2010 IBM Corporation 161
DB2 Information Management
DWH architectural framework
Mainframe environment
IDMS IDMS DB2
Other envir
OraOra
SAP R/3 environment
Ora Ora
Ora
DWH
BW
Primary ETL
ETL int
ODSEDW
DMarts
Tran
sfPSAODS
InfocubesAggreg.
Mdim ETL
Dat
a m
odel
ling
Adm
inis
tratio
n
virtualization layer
End user tools Applembeddedanalytical
BEX
Mdim DMarts
Query governor
metadata
DMarts
R/3 conn 12
4
6
13
11
12
1816
15 3BW conn ETL
DW
front-end
7
5
17
Enablers8
14
9
10
Example: real customer case
DBA high-value tasks7Data Warehousing
© 2010 IBM Corporation 162
DB2 Information Management
Information Services
L’informazione corretta, univoca, fornita come servizio “su richiesta”
Information Services
Conformi alla SOARiutilizzabiliSemanticamente consistentiEsempi:
• verfiche di correttezza
• trasformazioni di formato
• arricchimento, aggregazione
• sincronizzazione• query federate• ……
IOD e SOA: l’informazione come servizio
"You will waste your investment in SOA unless you have enterprise information that SOA can exploit."
Gartner, March 2005
L’uso flessibile delle informazioni è la base di qualsiasi architettura SOA
Info 2.0
DBA high-value tasks7Service Oriented Arch
© 2010 IBM Corporation 163
DB2 Information ManagementAnalityc Services
• BI services (Cognos 8 BI)• On-line analytics, DMining (IWH)• …
Master Data Mgmt Services• All the 800+ services offered by
InfoSphere MDM Server• Entity Analytics services (EAS)
Information Integration Services• Reat-time ETL (IS DataStage)• Data cleansing (IS QualityStage)• Federated queries (IS Federation Srv)• Data event capture (IS Data Event Publ)• …..
Supporting Tools (service generation)
• IS Service Director• IBM DataStudio• Other IBM tools
(e.g. WS Integration Developer)• …
Content Services• Content mgmt, Record mgmt, eForms
services (FileNet P8)• Content federation (WS II CE)• ….
Metadata Services (and repository)• Import /export, Data lineage, Impact
analysis (IIS)• Business Glossary (IIS)
Data Services• Relational data access (DB2, IDS)• Stored Procedures (DB2)• XML data access (DB2)• Data access (IMS)• …
Service choreography• Content-centric business
processes & workflows (FileNet P8 BPM)
• Other IBM sw (e.g. WS Process Server)
SOA IODInfo
Services
DBA high-value tasks7Service Oriented Arch
© 2010 IBM Corporation 164
DB2 Information Management
Per maggiori dettagli e altre informazioni
© 2010 IBM Corporation 165
DB2 Information Management
DB2 for Linux, Unix & Windows 9.7http://www-01.ibm.com/software/data/db2/linux-unix-windows/edition-enterprise.html Home page (ESE)
© 2010 IBM Corporation 166
DB2 Information Management
DB2 for Linux, Unix & Windows 9.7Infocenter http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
© 2010 IBM Corporation 167
DB2 Information Management
DB2 for Linux, Unix & Windows 9.7Tech Guides http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg27009474
© 2010 IBM Corporation 168
DB2 Information Management
http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert730.html?S_TACT=105AGX19&S_CMP=db2certlp
http://www-03.ibm.com/certify/certs/dm_index.shtml#db
© 2010 IBM Corporation 169
DB2 Information Management
http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert731.html?S_TACT=105AGX19&S_CMP=ZHP
© 2010 IBM Corporation 170
DB2 Information Management
Questions ?