IBM InfoSphere Data Replication 10.2.1 ( ) InfoSphere CDC for LUW

IBM InfoSphere Data Replication 10.2.1
(InfoSphere CDC for LUW)
October 2013
1
© 2013 IBM Corporation
Information Management Software
© IBM Corporation 2013. All Rights Reserved.
Disclaimer: Information regarding potential future products is intended to
outline our general product direction and it should not be relied on in making a
purchasing decision. The information mentioned regarding potential future
products is not a commitment, promise, or legal obligation to deliver any
material, code or functionality. Information about potential future products may
not be incorporated into any contract. The development, release, and timing of
any future features or functionality described for our products remains at our
sole discretion.
2
© 2013 IBM Corporation
Information Management Software
Agenda
• Consumability
•
•
•
•
Event Messaging/Tracing
Simplified Tracing for Communications
Improved Data Guard Log Shipping configuration for Oracle
Enhancements to External Refresh Command & CHCCLP
• Performance
• Minimize Network Load
• Improved handling of Large Objects (LOBs)
• Fast Apply
• Support Fast Apply for a DB2 DPF source
• Improved scalability of Fast Apply
3
© 2013 IBM Corporation
Information Management Software
Agenda…
• Database Currency
•
•
•
•
•
4
Extended Support of DB2 LUW v10.5
DDL Replication for DB2 LUW
DB2 PureData as Target
Oracle Index Organized Tables
Truncate operation on DB2 LUW source
© 2013 IBM Corporation
Information Management Software
IIDR Products and Platforms included in 10.2.1 Release
InfoSphere Data Replication
10.2.1 (DB2 LUW)
InfoSphere Data Replication
10.2.1 (Oracle)
Management Console
10.2.1
Access Server
10.2.1
5
© 2013 IBM Corporation
Information Management Software
Consumability - Improved error messages/Tracing
Enhanced SQL errors:
• Event messages include additional contextual information in
addition to the DB2 SQL error code when database errors
occur. This helps identify and troubleshoot the problem records
Long Running Transactions:
• A new event message has been added which will indicate if
there is a long running open transaction
• Event ID 2952
CDC has detected {0} transactions that are still open
after {1} minutes of processing.
• The product will check every hour, and if there is a transaction
that has been open for more than 2 hours, an event message
will be produced
6
© 2013 IBM Corporation
Information Management Software
Consumability - Simplified Tracing for Communications
• Communications tracing is now always enabled and generated in
<IIDR install directory>/instance/<instance
name>/log/comms directory
• Communications trace is also managed by the trace system
parameters global_trace_files_total_mb and
global_trace_files_each_mb or alternatively in the
comms.ini parameters TRACE_DIR_QUOTA and
TRACE_FILE_QUOTA
• Communications trace files are collected within dmsupportinfo in
the comms_trace subdirectory.
• In addition to tracing, event handling for communication related
failures has been enhanced
7
© 2013 IBM Corporation
Information Management Software
Consumability – Enhancements to External Refresh Command & CHCCLP
• External Refresh Command
• dmmarkexternalunloadstart and
dmmarkexternalunloadend commands for External Table
Synchronizations are now available for both Direct and Rule
Based Mappings for Oracle and DB2 LUW
• Both commands can also be called at a subscription level or
table specific level
• Execute command dmmarkexternalunloadstart [–I
<instance name> -L <locale>] –s <subscription
name> [-t <table name> exclude for all tables]
• Execute command dmmarkexternalunloadend [–I
<instance name> -L <locale>] –s <subscription
name> [-t <table name> exclude for all tables]
• CHCCLP scripting has been enhanced to support rules based
syntax
© 2013 IBM Corporation
Information Management Software
Oracle Log Shipping Enhancements
• New ability to offload more intensive database queries to
standby database
• IIDR must be installed on the Physical Standby server to
establish a bequeath connection
New bequeath connection
Read only database
Connection still required
IIDR- CDC
Oracle
Data Guard Log Shipping
Primary
Archive logs
Oracle database server
Oracle
Standby
Shipped archive logs
IIDR server
© 2013 IBM Corporation
Information Management Software
Oracle Log Shipping Enhancements
• CDC will only reference the standby database's DataGuard
instance to determine the availability of archive logs.
• Logs can be shipped using DataGuard log shipping or manually
shipped and then registered with the DataGuard instance on the
standby database
SQL> alter database register logfile '/var/arch/arch_1_101.arc‘
IIDR will continue to detect
available logs by querying
standby database. IIDR
Manual log shipping
commands are not needed
Data Guard Log Shipping
Manual Registration of Logs
IIDR- CDC
Oracle
Primary
Oracle database server
Oracle
Standby
IIDR server
© 2013 IBM Corporation
Information Management Software
Oracle Log Shipping Enhancements
• Configuration changes
New optional Physical Standby
configuration:
If database credentials are entered
and validated, IIDR will establish a
new bequeath connection to physical
standby
Specify SYS AS SYSDBA user on
physical standby
© 2013 IBM Corporation
Information Management Software
Performance – Smart Ship
• Extension of previous work to minimize the amount of data applied to the target
database, these same types of optimizations can occur earlier in the pipeline,
affecting both the amount of data sent from source to target and the amount of
data processed on the source system.
• Source and target will negotiate to determine the optimal amount of data. This
negotiation can be affected by things such as:
• Derived Expressions
• Presence of user exits
• Conflict Detection Resolution (CDR) configured
• Mapping Type other than Standard (e.g. Consolidation)
• In some situations significant reduction in resource utilization can occur. In many
situations for Update operations only keys and changed columns need to be
processed and for Deletes often only the key columns.
• This can provide especially significant benefit in the case where a table has LOB
columns that are often not changed when Update operations are done.
• It is a requirement that both the source and target engines must support these
capabilities for the optimization to be enabled
• Note: In 10.2.1 the optimization to avoid processing unchanged LOB values is
only done for Oracle and DB2 LUW
12
© 2013 IBM Corporation
Information Management Software
Performance – Minimize Network Load
• This feature allows the user to indicate when having minimal network
data sent is a more important factor than having low latency
• Generally opportunities available to CDC to reduce the network data
will also reduce overall resource consumption and reduce latency, but
this is not always the case.
• The default setting is off (ie. optimize for throughput and low latency is
the primary goal)
• This setting is similar to the transferable work setting. A user may or
may not see any change in the behavior of CDC when changing this
setting as IBM cannot provide any guidance as to the situations where a
trade off between network data and latency may exist
• In 10.2.1 the Smart Ship optimizations are affected by this setting. This
may change in future releases where the Smart Ship negotiation may be
able to become more sophisticated so that there are no longer situations
where such a trade off exists.
13
© 2013 IBM Corporation
Information Management Software
Performance –Minimize Network Load
• The feature is disabled by default
• CHCCLP command changes:
• add subscription [minimizeNetworkLoad]
• modify subscription [minimizeNetworkLoad]
© 2013 IBM Corporation
Information Management Software
Performance – Processing inline LOBs from the Log
• Supported only for DB2 LUW and Oracle
• If the size of a LOB is small enough that the database will
inline the LOB data in the log, then CDC will be able to
process the LOB change from the log and not have to
query the source table
• The specific LOB values that are inlined by the database
will vary, typically for Oracle it will inline a LOB that is less
than 3900 bytes
15
© 2013 IBM Corporation
Information Management Software
Performance – Fast Apply
• Fast Apply now supports DB2 DPF source systems
• Improved scalability and efficiency of Fast Apply algorithms
16
© 2013 IBM Corporation
Information Management Software
Rules Based Replication for DB2 LUW
• In addition to Oracle, DB2 LUW now supports Rules Based
Replication!
• Similar level of DDL support as IIDR-QRep
• Rules Based Replication allows:
• Homogeneous replication between like databases
• Replication of most common DDL changes for tables
• Efficient way to configure replication mapping based on
Rules based section
• a High Availability and Disaster Recovery solution for
DB2 LUW databases
17
© 2013 IBM Corporation
Information Management Software
Rules Based Replication for DB2 LUW – Requirements
•
•
•
•
Supported on DB2 LUW 10.1 and above
Homogeneous DB2 LUW to DB2 LUW replication only
Source and target database must have identical versions
Source and target tables must have identical structure with
no transformations
• LOG_DDL_STMTS system parameter is required
• Supported DDL operations include CREATE TABLE, DROP
TABLE, ALTER TABLE ADD COLUMN, ALTER TABLE
ALTER COLUMN SET DATA TYPE
• Temporal tables will implicitly be configured as sourcemaintains history
• Both base and history tables must be in scope
© 2013 IBM Corporation
Information Management Software
Rules Based Replication for DB2 LUW – Limitations
• Unsupported DDL operations include but not limited to
DROP COLUMN, RENAME, REORG, MOVE, ADD
PARTITION, Index operations
• Bidirectional replication is not supported
• Range partitioned tables are not supported
• DDL operations during external unload or Refresh is not
handled
• Loads are ignored resulting in unsynchronized tables
© 2013 IBM Corporation
Information Management Software
Rules Based Replication Features
• Command dmshowsupplementalloggingdependency is used to
display logging requirements
• Do you need LOG_DDL_STMTS enabled? - Yes/No
• Command dmreducesupplementalloggingdependency is used to
indicate if logging requirements can be reduced
• Can I disable LOG_DDL_STMTS? - Yes/No
• System parameter mirror_audit_ddl_statements controls whether
DDL changes are audited in the target database
• DDL changes are audited in the TS_DDLAUD metadata table
• CHCCLP scripting has also been enhanced to support rules based
syntax
• DB2 LUW Rules Based Replication supports External Refresh
commands
© 2013 IBM Corporation
Information Management Software
Database Currency - DB2 LUW v10.5 Support
• Extended support for DB2 v10.5
• The level of support is equivalent to what CDC provided
for DB2 v10.1
• CDC will not take advantage of any additional features of
DB2 v10.5
• CDC supports replicating tables with extended row sizes
21
© 2013 IBM Corporation
Information Management Software
Database Currency – PureData Support
• Added support for targeting Pure Data appliances
• Pure Data for Analytics
• Pure Data for Transactions
• CDC must use a remote apply configuration targeting
these appliances.
• Since a mount point cannot be created for fast load
refresh, JDBC refresh with multiple apply threads will be
used instead
© 2013 IBM Corporation
Information Management Software
Database Currency – Index Organized Tables (IOTs)
• Added full support of Index Organized Tables for Oracle
• Former limitations included:
• An IOT comprised of character data.
• Populating an IOT using SQL*Loader
• LOB columns with IOTs
23
© 2013 IBM Corporation
Information Management Software
Support of Truncate operation
• DB2 LUW source will now replicate TRUNCATE(CLEAR)
operations
• As do the current versions IBM i, Oracle, SQL Server
© 2013 IBM Corporation
Information Management Software
Questions?
?
25
© 2013 IBM Corporation