#IDUG

Now I have DB2 Advanced Edition. –

What can I do with OPM,
OQWT, OCM and CDI?
Roland Schock
ARS Computer und Consulting GmbH
Session Code: D08
2014-09-11 | Platform: LUW
 #IDUG
2

This session

• Get to know your DB2 Advanced Edition

• Quickstart for OPM: Best practices and how to avoid common
pitfalls.
• Integration of OQWT (Optim Query Workload Tuner) in Data
Studio and OPM
• Introduction into OCM (Optim Configuration manager) and
how to deploy it properly
• How to get started with CDI (Continuous Data Ingest)
 #IDUG
3

History of DB2 Advanced Edition

• First introduced as AESE with DB2 9.7 FP3

• Special bundle of DB2 features (=get the lot) but with some restrictions
• Migration path from DB2 9.7 ESE to DB2 9.7 AESE available
• There are slight changes in the bundling of DB2 9.7 fixpacks and the
versions of the contained tools
• DB2 10.1 AESE
• Similar to DB2 9.7 AESE, but now with additional tools not available
otherwise
• DB2 10.5 AWSE and AESE
• Straightens the license path
 #IDUG
4

Product Bundling of DB2 AESE 9.7

• DB2 9.7 Enterprise Server Edition

• DB2 9.7 Storage Optimization feature
• DB2 Advanced Access Control feature
• DB2 Workload Management
• Optim Performance Manager 4.1
• Optim Development Studio 2.2.1
• Optim Database Administrator 2.2.3
• Homogeneous Replication Feature for DB2 (restricted use)
• InfoSphere Federation Server (restricted use)
 #IDUG
5

Product Bundling of DB2 AESE 10.1

• IBM Data Studio

• DB2 Workload Management
• Storage Optimization Feature, including Adaptive Compression
• Continuous Data Ingest
• InfoSphere Optim Performance Manager Extended Edition
• InfoSphere Optim Query Workload Tuner
• InfoSphere Optim Configuration Manager
• InfoSphere Data Architect (10 authorized users)
• InfoSphere Optim pureQuery Runtime for LUW
 #IDUG
6

Product Bundling of DB2 10.5

• AESE
• IBM Data Studio
• InfoSphere Optim Performance Manager Extended Edition
• InfoSphere Optim Query Workload Tuner
• InfoSphere Optim Configuration Manager
• InfoSphere Data Architect (10 authorized users)
• InfoSphere Optim pureQuery Runtime for Linux , UNIX , and Windows
• AWSE
• The edition includes largely the same functionality and tools as AESE.
• The main difference is that AWSE has processor core, socket, memory,
and terabyte restrictions.
• AWSE supports federation only with DB2 for LUW and Informix
 #IDUG
7

InfoSphere OPM Extended Edition

• Overview:
• OPM Architecture
• End to end monitoring with OPM Extended Insight
• OPM Extended Architecture

• User Interface
• Configuration
• Timeline
• Panels
 #IDUG
8

OPM Architecture
 #IDUG
9

OPM 5.2 Highlights

• Stored Procedure Monitoring for DB2 LUW

• Analyze stored procedure executions and its statements
• Get the top stored procedures by aggregated execution metrics over all
executed statements and nested stored procedures
• Drill down into a stored procedure to analyze executed statements
• Report scheduling, emailing, and retention
• Define and schedule jobs to create, email and persist reports
automatically
• Alert actions
• Define actions to be executed on the monitored or repository database
when an alert occurs
 #IDUG
10

OPM 5.2 Highlights

• New privileges
• is DatabaseOwner (web console privilege)
• can EnableAutomaticDataCollection (monitored database user
privilege)
• Operator (web console privilege)
• Enhanced monitoring configuration features
• New actions, new terminology and improved monitoring status display
on Databases panel
• Blackout events
• Allows to define one time or scheduled blackout events in that the monitored
database is not accessed at all by OPM, e.g. for maintenance purposes
 #IDUG
11

OPM 5.2 Highlights

• OPM self-monitoring
• Predefined system template for monitoring the repository database
• Allows easy health check of the repository database and OPM system
• Usability improvements on Performance Overview dashboard
• Display baseline information in the graph visually
• Visual indicator how the current value compares to the baseline
• Integration
• Connection profile sharing
• Allows Administrators to subscribe OPM with the master OCM server and
synchronize defined Database Connections across between OPM and OCM
 #IDUG
12

OPM 5.2 and prereqs

• Prerequisites changes
• AIX 5.3 no longer supported
• DB2 V9.1 and DB2 V9.5 no longer supported as DB2 versions for the
OPM server
• DB2 V9.1 no longer supported as DB2 versions for the monitored
database
• DB2 fix packs required for OPM server due to IC77366 BUFFER POOL
DECREASE / STMM HANG WAITING ON SPECIAL TEMP BUFFER POOL
PAGE
• DB2 V9.7 fix pack 5 or higher
• DB2 V10.1 fix pack 2 or higher
 #IDUG
13

OPM 5.3 Highlights

• Support of DB2 10.5

• Support of DB2 10.5 as both a repository database and
a monitored database
• New performance metrics for column-organized tables
• Alert enhancements
• File system utilization, HADR standby states, Invalid log path,
max log space, log archive failed
• Embedded query tuning in web console
• Tuning of SQL queries using a subset of the OQWT features is now
embedded in the web console
• See details at http://www-01.ibm.com/support/docview.wss?uid=swg27023197
 #IDUG
14

End to end monitoring with OPM Extended Insight

• Part of installation: agents on monitored server

 #IDUG
15

End to end monitoring with OPM Extended Insight

 #IDUG
16

OPM Extended Architecture

 #IDUG
17

OPM Configuration

▪ System templates allow one-click configuration

▪ Use templates as a good starting point....
▪ Templates encompass OPM Best Practices
▪ User can choose from various templates for typical installations
(OLTP, BI, SAP, Dev, Test, etc…)
▪ Each templates enables the monitoring profiles that are
suitable for the type of workload
 #IDUG
18

OPM Configure Monitoring

 #IDUG
19

OPM Configuration

• Using Performance Data from pre 9.7 databases

• ATTACH
• Event Monitors
• Using the new(er) Monitoring Infrastructure introduced in 9.7+
• CONNECT
• Less intrusive
• Further details on deployment and configuration:
• http://www.redbooks.ibm.com/redpieces/abstracts/sg247925.html?Op
en
 #IDUG
20

Data collection, aggregation, and retention

• Core performance metrics, also referred to as Inflight data

• In real time performance metrics are collected at a defined
interval or on demand.
• The EI dashboard collects performance metrics from the
monitored database and configured .
• The Health Summary feature collects specific health data from
the monitored database at defined intervals.
• The WLM configuration monitoring profile enables the
collection of WLM configuration data and sampling metrics at
defined intervals.
 #IDUG
21

OPM Usage Tricks

• Understanding the time line

• Watch out: Alerts in time interval
• Drill down in
panels similar to:
 #IDUG
22

opmlicm

• Command to determine which OPM license is installed.

• Available in <install path>/bin
• opmlicm.sh for UNIX, Linux
• opmlicm.bat for Windows

• Example:
 #IDUG
23

InfoSphere Optim Query Workload Tuner

• OQWT lets you capture the actual access plans for SQL
statements
• Uses explain function in several ways
• Installed on the DBA workstation
• Workload and single SQL statements
 #IDUG
24

Access plans in OQWT

 #IDUG
25

InfoSphere Optim Query Workload Tuner

 #IDUG
26

InfoSphere Optim Configuration Manager

 #IDUG
27

InfoSphere Optim Configuration Manager

• Inventory of clients and servers

• Tracks changes to client/server properties at user defined
intervals
• Can compare client/server environments with best practices
configurations.
• Can generate alerts when configurations deviate.
• Control client behaviors and optimize database storage for
improved availability.
 #IDUG
28

InfoSphere Optim Configuration Manager

• The OCM Server captures and stores the following regarding

database servers:
• DB &DBM cfg
• Database Objects including tables, indexes, and sequences
• Authorizations (granted permissions)
• DB2 Version and Fix Pack Level
• Operating System (z/OS, AIX, Linux, etc.) plus OS Version.
• Hostname, IP address and other information about the connected
instances or subsystems.
 #IDUG
29

InfoSphere Optim Configuration Manager

• Database Client information collected about each client:

• Hostname and IP address of client plus port
• Client Operating System and version.
• Client or driver type (CLI, JDBC, etc.)
• Client or driver version
• Auth ID used to connect to the database.
• Other information that you can see on the database using a snapshot
for applications.
• JDBC Properties (Managed Client Only)
• Certain WebSphere Application Server (WAS) settings (Managed Client
Only)
 #IDUG
30

Continuous Data Ingest

• Move data into DB2 tables with no impact to running

applications.
• Feed near-real-time data marts and data warehouses.
• In such environments, the up-to-date data is so critical that
customers can not wait for the next load windows to get access
to newer data.
• A client-side utility that uses parallel sessions to insert data
into the tables.
 #IDUG
31

Continuous Data Ingest

• Insert new rows, update existing rows, or do a combination of

both which is sometimes called a merge or “upsert” operation.
• Delete rows in a database based on the data in a file or pipe.
• Allows to use DB2 functions and expressions so you can
manipulate the data as it is being ingested.
• Faster than the INSERT utility and can be used to load massive
amounts of data into a database in a reasonable amount of
time.
• Can exploit partitioned databases!
 #IDUG
32

Continuous Data Ingest

• Only available in DB2 (AESE) in DB2 Version 10.1 and the IBM
Pure Application System.
• It is compatible with DB2 pureScale as long as you are licensed
for both the pureScale feature and DB2 AESE.
• As with load and import, ingest can take input from a file or set
of files.
• You can also get its input from a named pipe or set of pipes.
 #IDUG
33

Continuous Data Ingest Architecture

• The INGEST command is a client tool and can be exploited from

both client or server side.
• The DB2 client is responsible for reading the files (or pipes),
format the contents and perform remote INSERT operations
into DB2 database.
• Impacted by the network speed.
 #IDUG
34

• To avoid the network bottleneck

• Run the INGEST command locally on the DB2 server
• Input files must be copied into DB2 server before the ingest
execution
 #IDUG
35

• Transporter reads the input files or pipes.

• Formatter does the basic formatting of input files can be specified in
the INGEST command.
• Flusher writes data into DB2 tables using array insert operations and
responsible for transaction control.
 #IDUG
36

Tuning ingest performance

• Locking parameters
• ingest threads may lock each other and reducing the overall
performance.
• lock contention can be caused by other running transactions on the
database.
• Transaction logging
• LOGFIZSZ, LOGPRIMARY and LOGSECOND to make sure that it is not
going to impact the ingest operation.
 #IDUG
37

Tuning ingest Performance cont..

• commit_count and commit_period

• low values will cause a high number of transaction commits
• omitting both parameters (ingest will commit every 1 second)
• num_flushers_per_partition
• compute the optimal value for this parameter based on logical CPUs
available
• num_formatters and shm_max_size
• computes optimal values for these parameters at startup time
 #IDUG
38

Comparing ingest to other data movement methods

Attribute IMPORT INGEST LOAD

Slow. Use Very fast. Data
Fast. Use parallel
Speed sequential INSERT inserted directly
INSERT operations
operations into DB2 containers
High, although Low. Only read
Very high, use row
Concurrency some table level operations are
level locking
lockings may occur allowed
No, data inserted is
Logging Yes Yes
not logged
Table is put in
Loading Pending
Rollback. Table is Rollback. Table is
Behavior on failure state. Manual
accessible accessible
intervention is
required
LOBs and XML Data Yes No Yes
 #IDUG
39

DB2 Advanced Recovery feature

• New with DB2 10.5

• The only separately available feature for DB2 10.5
• Contains
• IBM DB2 Merge Backup for Linux , UNIX , and Windows V2.1
• IBM DB2 Recovery Expert for Linux , UNIX , and Windows V4.1
• IBM Optim High Performance Unload for DB2 for Linux , UNIX , and
Windows V5.1
• Program charges:
DB2 10.5 Advanced Recovery feature is available under Authorized User Single
Install, PVU, and per Terabyte charge metrics. You must acquire the same Metric
and number of entitlements as you have acquired for the DB2 for Linux , UNIX , and
Windows server that you will be running this feature against.
 #IDUG
40

IBM DB2 Merge Backup for Linux , UNIX , and Windows

V2.1
• Combine Backups (full, incremental, differential, tablespace
level, etc) and logfiles to a single backup image
• Interesting for big
databases with just
small backup windows.
 #IDUG
41

IBM DB2 Recovery Expert for Linux , UNIX , and

Windows V4.1
• Analyses transaction logs or recovers tables from backups
without requiring a full restore of the database/tablespace
• Use log-mining to extract changes of transactions
• Generate DML commands to undo unwanted changes via Roll-
Back or Go-Forward
 #IDUG
42

IBM Optim High Performance Unload for DB2 for Linux,

UNIX, and Windows V5.1
• Export reads tables sequentially
• HPU is writing out the data in parallel to multiple streams
• HPU Unload can also use copies of source tables to minimize
the impact on production tables.
 #IDUG
43

References

• DeveloperWorks
• Which distributed edition of DB2 9.7 is right for you?
http://www.ibm.com/developerworks/data/library/techarticle/dm-0909db2whichedition/
• Which distributed edition of DB2 10.1 is right for you?
http://www.ibm.com/developerworks/data/library/techarticle/dm-1204whicheditiondb2/

• What's new in DB2 10.5 for Linux, UNIX, and Windows

http://www.ibm.com/developerworks/data/library/techarticle/dm-1304whatsnewdb2105/
• Getting started with new DB2 INGEST command
http://www.ibm.com/developerworks/data/library/techarticle/dm-1304ingestcmd/
 #IDUG

Roland Schock
ARS Computer und Consulting GmbH
[email protected]

D08
Now I have DB2 Advanced Edition. – What can I do
with OPM, OQWT, OCM and CDI?