is cdc 2 operations and commands - ibm.com · ibm infosphere change data capture is running....

© 2010 IBM Corporation1

InfoSphere CDC

Operations and Commands

Information Management Software

2

Managing CDC using Command Line Interface


3

Commands for InfoSphere Change Data Capture

• Controlling replication commands

• Database transaction log commands

• Managing tables for replication commands

• Exporting and importing configuration commands

• Continuous Capture and staging store commands

• Monitoring replication commands


4

CDC instanceo10u2

Starting and stopping CDC• Instance start-stop method

• Windows：Windows service• Unix：start with dmts64 (64-bit) or dmts32(32-bit) command

• Replication start-stop• Start-stop with subscription unit• Method

• MC monitor view• dmstartmirror or dmrefresh• dmendreplication

CDC instanceo10u1

Source engine Target engineSubscriptionSUB1

Source engine Target engineSubscriptionSUB2

Replication startExample: dmstartmirror –I o10u1 –c –s SUB1

Instance startExample: dmts64 –I o10u1


5

Instance start-stop (Unix) • Start

• 64bit mode ：dmts64 –I < instance name>

• Synchronous command, so starts in nohup background

• Stop

• Normal shutdown: dmshutdown -I < instance name>

• Forced termination: dmterminate

• Stops all instances running on the server• This command is used when InfoSphere CDC cannot be completely shutdown even when the

dmshutdown command is used.• Requires the transaction queue to be cleared dmclearstagingstore (Oracle only) at next startup.

bash-3.00$ nohup /home/tso63/tso63/bin/dmts64 -I o10s2 &[1] 737310bash-3.00$ nohup output is sent to nohup.out

bash-3.00$ ps -fu $USERUID PID PPID C STIME TTY TIME CMD

tso63 368764 581768 1 23:13:41 pts/0 0:00 bashtso63 581768 917672 0 23:13:40 pts/0 0:00 -kshtso63 737310 368764 138 23:28:53 pts/0 0:30 /home/tso63/tso63/jre64/jre/bin/dmts64-tso63 786654 368764 3 23:29:30 pts/0 0:00 ps -fu tso63

bash-3.00$ cat nohup.outbash-3.00$ netstat -ano|grep 11001bash-3.00$ cat nohup.outIBM InfoSphere Change Data Capture is running.

bash-3.00$ netstat -ano|grep 11001tcp 0 0 *.11001 *.* LISTEN

bash-3.00$ ./dmshutdown -I o10s2Waiting for perpetuation of unit collection change log to disk. Please wait… IBM InfoSphere Change Data Capture has shutdown normally.

Execution example (CDC Oracle)

At correct start up a message is output to standard output and indicates that the port is listening

bash-3.00$ ./dmterminateWaiting. All instances have shutdown normally.


6

Instance start-stop (configuration tool)

• Start-stop as Windows service

• Also enables start-stop from CDC configuration tool

• There are no commands that correspond to dmterminate in Windows


7

Controlling Replication Commands

Icon Operation Command Action

Start replication

Start refresh dmrefreshRefreshes table mapping to be refreshed and completes. Conducts refresh operations one table at a time.

Start mirroring (continuous)

dmstartmirror -c

Starts continuous mirroring. Starts mirroring after the table mapping has been refreshed when there are items that require refreshing using mirroring format table mapping.

Start mirroring

(net change) dmstartmirror –n

Starts mirroring and completes when all unread logs have been read. Starts mirroring after the table mapping has been refreshed when there are items that require refreshing using mirroring format table mapping.

End replication

End replication (controlled)

dmendreplication -cCompletes after all changes partway through application have been applied to the target table.

End replication (immediate)

dmendreplication -i

Aborts all current operations. InfoSphere CDC implements rollback for changes being applied. Aborted changes are re-run at the next replication.


8

Synchronization


9

Approaches for Initial Synchronization

• 1. Refresh through InfoSphere Change Data Capture

• CDC Refresh may be slow for large tables

• Alternatives include Differential Refresh or Range Refresh to synchronize only differences or synchronize in increments

• 2. Application is quiesced

• Source database is active, but no transaction occuring

• Can often be done during service window

• Fastest and most dependable way of initial synchronization• No transactions in flight

• 3. Application is active (7x24)

• If a quiescent point is not available for tables to complete an load/unload then the in-doubt period must be handled


10

Approach #1 – CDC Refresh

• Use this operation to perform a one time copy of the data for the specified subscriptions. When you refresh a subscription, InfoSphere CDC ensures that the target tables are synchronized with the source tables.

• Typically, you would refresh target tables when you have set the replication method to Refresh on your tables. However, you can also refresh target tables that have a replication method set to Mirror and a status of Active or Refresh. When you refresh a table configured for mirroring, InfoSphere CDC refreshes the target table so that it is synchronized with the source table and then marks a table capture point as the starting point for mirroring.

• This command exits after it has successfully refreshed the specified tables. If you terminate this program while it is still running, InfoSphere CDC ends replication immediately for the specified subscriptions.


11

Refresh Table Considerations

• How Refresh works• CDC Refreshes a set of selected tables as one operation, but will only process a Refresh for one table at a

time within a single subscription.• Before the Refresh starts

• Target tables are truncated (by default) at the beginning of the Refresh • CDC drops indexes prior to load and then recreates them afterwards

• CDC queries the source database table which will cause a table scan to provide the rows.

• Refresh Order• The order in which each individual table is refreshed is based on the group order. Group order is set via

Management Console. If all tables have the same group order, then they'll be refreshed alphabetically.

• Bulk Load Refresh• Disabled when table mapping is set for Live Audit, tables contain LOB columns, User exits are configured,

row or member identifiers, target columns contain non-ASCII names, target truncate operation is disabled, row identifiers are specified, parameter refresh_end_on_error=true OR parameter refresh_allow_fast_loader=false

• Refresh while active• “Refresh while active” logic allows the Refresh to be run during periods where the source database is

processing changes to the source tables. The “start point ” will be stored in the CDC source metadata. When Refresh completes for this table, the log position of the source database is stored as the “end point ” in the CDC source metadata. When mirroring is restarted for the table, changes made between these points will be send with an “in-doubt” flag and ignores any errors during this period of time.


12

Refresh While Active • CDC will mark the Refresh Start position when CDC selects the source table

• CDC refreshes table

• Once refresh completes, mark refresh end point

• CDC goes back to scrapes changes that occur between start and end points �� in-doubt period, ignore any conflicts

• CDC continues in mirroring mode

Oracle Redo Log

SCN Number21 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 37 38 39 40

Refresh

Refresh Start Refresh End

In-doubt period

Mirroring

Set Capture Point

Active Mirroring


13

Differential Refresh

• A Differential Refresh updates the target table by applying only the differences between it and the source table. Instead of the target table being cleared at the start of the refresh and repopulated with data, as with the Standard Refresh, the Differential Refresh compares each row in the target table with each row in the source table to identify missing, changed or additional rows. The primary advantage of the Differential Refresh is that the target table stays online during the refresh operation.

• There are three possible methods for the Differenti al Refresh:

• Refresh Only —Performs a Differential Refresh by changing any target rows that differ from the source rows.

• Refresh and Log Differences —Performs a Differential Refresh, and also creates a log table in the target replication engine metadata to track all changes during the refresh.

• Only Log Differences —Creates and populates a log table in the target replication engine metadata to identify all differences between the source and target tables.


14

Differential Refresh Requirements & Restrictions

• Differential refresh is only available for tables that use Standard replication

• The collation sequence of the source and target tables must be identical

• Derived columns on the source table are not supported

• Any target columns which are mapped to derived expressions, constants or Journal control fields will be ignored

• The key columns of the target table must be mapped directly to columns on the source table

• Source and target tables must have same table structure


15

Range Refresh (Subset Refresh)

• Common uses for subset refresh functionality

• Re-synchronizing tables which are out-of-sync

• Refreshing very large tables in stages

• Accommodating smaller batch windows• Less interruption for other tables being replicated• Example:

• Refreshing a table of 1 billion rows can be spread over multiple days• Every day 200 million rows can be refreshed

• Synchronization check for subset of rows

• Using differential refresh functionality


16

Flag the table for refresh

• Flag the table for refresh

• A dialog will appear, you can select from two main options

• Standard refresh (all target rows will be truncated and refreshed)

• Differential refresh (only rows which are different between source and target will be refreshed)

• For both standard and differential refresh, one can choose to refresh a subset of the rows


17

Refreshing a subset of rows

• In the example, the “PENDING” rows will be refreshed

• When the refresh starts, it will first delete the rows on the target using the target WHERE clause

• All rows with ACTIVITY_STATUS=‘PENDING’ will be removed

• Subsequently rows on the source will be selected using the source WHERE clause

• All rows with STATUS=‘P’ will be transferred to the target side


18

Start the subscription

• Out of the 384 rows in the table, only 64 rows were refreshed• To optimize the refresh process, CDC will not provide a count of the total number of rows in the source table

• JDBC apply mode was used for refresh• No fast loader is used when refreshing a subset of rows


19

Differential refresh and row subsets

• Differential refresh

• Allows for refreshing/checking rows with discrepancies

• This functionality may also be used to perform a synchronization check

• For large tables, a full synchronization check may be time consuming• Row subsets provide ability to do partial synchronization checks


20

Flag table for differential refresh

• In this example all rows with STATUS=‘D’ will be checked

• Subsequently, when starting the subscription, all rows with STATUS=‘D’ will be sent to the target for synchronization checking

• No rows will be updated on the target, due to “Only Log Differences (Refresh Not Performed)”

• If the subscription is started in continuous mirror mode, the mirroring will resume after the differences are logged


21

Synchronization check completed

• CDC creates a table with the out of sync records on the target side• Table resides in the CDC schema


22

Approach #2 – Application Quiescent

• Requirement: No DML transactions occurring on the in-scope tables during the time of the load/export

• After the synchronization of the tables outside of CDC the log position can be marked using the dmmarktablecapturepointcommand or via the Management Console ‘Mark Table C apture Point for Mirroring’ operation

• Command:

• dmmarktablecapturepoint –I <instance> -s <list of subscriptions> -t <list of all tables> [-A for all tables]

• MC:


23

Approach #3 – Application Active (7x24)

• If the application is active or DML operations are performed during the export/import, load/unload procedure add itional CDC configuration is required to address this in-do ubt period

• CDC Configuration options:

• Conflict Detection

• Adaptive Apply


24

Approach #3a – Use Conflict Detection to address in-doubt period

1. Before starting the initial load of the data outside of CDC, map all table and set to replication method ‘Mirror’ and status ‘Active’.

• After mapping the table with replication method ‘Mirror’ the status will be set to ‘Refresh’• To change the status to ‘Active’, set ‘Mark Table Capture Point for Mirroring’

2. All tables that are synchronized outside of CDC need to configure conflict detection setup by opening each table mapping and settling the conflict detection to ‘Source Wins’ for every column

3. Once the initial load is complete start mirroring

4. When latency reaches near zero the in-doubt period is over and replication can be ended for al subscriptions

5. Conflict detection can be disabled by selecting the Conflict Resolution Method ‘None’ for every table mapping

6. All of the operations that were repeated will be logged in the TS_CONFAUD metadata table which can be purged


25

Approach #3b – Use Adaptive Apply to address in-doubt period

1. Initially map all tables with the ‘Adaptive Apply’ mapping type

2. ‘Mark Table Capture Point for Mirroring’ for all tables that will be synchronized

3. Perform the synchronization of all tables

4. Start mirroring

5. When latency reaches near zero, end replication

6. Remap tables with the ‘Standard’ mapping type. Select all tables and choose ‘Remap source table’

7. Reconfigure any target derived expressions, user exits or mappings for each table

8. Start mirroring

Note: Target settings, derived expressions, user exits and mappings are not maintained during the switch from Adaptive Apply mapping to Standard. They will need to be reconfigured again after the remapping


26

dmstartmirror – Start mirroring

• Issue this command from your InfoSphere CDC source to start mirroring on the specified subscriptions. This command starts mirroring for any table with a replication method of Mirror and a status of Refresh or Active. Tables with a replication method of Mirror and a status of Refresh are refreshed before mirroring begins.

• InfoSphere CDC provides two types of mirroring for source tables that are mapped to target tables: Continuous(-c parameter) and Scheduled End (Net Change) (-n parameter). The type of mirroring you select depends on your business needs.

• As its name implies, Continuous mirroring replicates changes to the target on a continuous basis. Use this type of mirroring when business requirements dictate that you need replication to be running continuously and you do not have a clearly defined reason to end replication at the present time.

• Scheduled End (Net Change) mirroring replicates changes (to the target) up to a user-specified point in the source database log and then ends replication. Use this type of mirroring when business requirements dictate that you only replicate your data periodically and you have a clearly defined end point for the state of your target database when replication ends. Scheduled End (Net Change) mirroring allows you to end replication at the following points in your source database log:• -n parameter—When specified without –tor –p, this pa rameter ends replication at the current time in

the source database log.• -t parameter—When specified with –n, this parameter ends replication at a user-specified date and

time.• -p parameter—When specified with –n, this parameter ends replication at a user-specified log

position.


27

Mirroring considerations

InfoSphere CDC Bookmark Position

Consists of 3 log positions:

• Restart position – This is the earliest open position (EOP) that is used by CDC to start from. CDC processes everything by transaction and must start reading from the oldest position.

• Commit position – This position is used to filter out transactions. Also called the last committed position (LCP). CDC starts at the EOP then it moves forward to find the LCP. The LCP represents the last commit of the previous transaction that was sent.

• Current position – Also called the last applied position (LAP). This is the last position that has been applied.


28

dmendreplication – End replication

• Use this command to end refresh or mirroring on the specified subscriptions. Ending replication allows you to prepare for transitional activities in your business environment and allows you to move to the next step in your business processes.

• If you are replicating data continuously with Continuous mirroring and business reasons arise that require an end to replication, InfoSphere CDC provides multiple options that suit most business needs.

• If your business requirements dictate that replication must end at a particular point in your source database log because the target database must be in a known state when replication ends, you can choose from the following Scheduled End to replication options:• -se parameter—When specified without –t or –p, this parameter ends

replication at the current time in the source datab ase log.• -t parameter—When specified with –se, this parameter ends replication at

a user-specified date and time.• -p parameter—When specified with –se, this parameter ends replication

at a user-specified log position.• -a parameter – When specified with –se, this paramet er ends replication

rapidly


29

Checkpoint:

• Q1: What is the difference between CDC’s Refresh an d Mirroring operations?

• Q2: Describe the available options to synchronize t he source and target tables when there is no quiescent period ava ilable?

• Q3: What are the implications of issuing the dmterm inate command? An immediate shutdown?

• Q4: What is the effect of issuing a dmmarktablecapture point command?


30

Database Transaction log Commands

• The following commands are used to manage your database transaction log or CDC bookmarks.

• The database transaction log commands are only available via command line

Database Transaction log command Description

dmdecodebookmark Display verbose information about CDC bookmark

dmshowbookmark Display bookmark information

dmsetbookmark Set CDC bookmark

dmshowlogdependency Show log dependency


31

How to monitor the latest CDC log position?

• InfoSphere CDC provides command line utilities that can be used to query the CDC bookmark position.

• CDC bookmark format: • SCN.threadId.blockNumber.offset.blockEntry.arrayEntry; this is more granular than Oracle’s

numbering• SCN (System Change Number) is standard Oracle, remaining numbers are product-specific

and unknown to Oracle• If position must be set to SCN returned by Oracle: use SCN.0.0.0.0.0

• Step 1: Retrieve hex format of target bookmark

• dmshowbookmark – Display bookmark information• Use this command to view the latest replication position for a subscription committed on

the target

• Step 2: Decode bookmark on source using bookmark decoder

• dmdecodebookmark – Display verbose bookmark information• Use this command on the source to display verbose information about a bookmark


32

Example of bookmark output

• Step 1: Retrieve position from targetdmshowbookmark -I <INSTANCE> -s <SUBSCRIPTION>

Output:

000308000005A6A040000005A6A0400100010000C97A0090010 00005A6A0400100010000C97A00900100000000

• Step 2: Decode position on sourcedmdecodebookmark -I <INSTANCE> -b

000308000005A6A040000005A6A0400100010000C97A0090010 00005A6A0400100010000C97A00900100000000

Output:

IBM InfoSphere Change Data Capture Bookmark Decode Utility

Bookmark value: 000308000005A6A040000005A6A0400100010000C97A0 09001000005A6A0400100010000C97A00900100000000Header: 000308Restart position: 94806080Commit position: 94806080.1.1.51578.144.1Current position: 94806080.1.1.51578.144.1.0

Tip: If source and target use same database platfor ms you can combine the 2 steps using –v (verbose option)


33

dmsetbookmark – Set bookmark

• Use this command to change the scraping point for a subscription

• Options:• -a If specified, capture points for all tables in the subscription (except parked tables) will be reset to the bookmark

provided in (-b | -l| -f | -t) option.• -b Bookmark indicating the new scraping point (hex encoded string• obtained from dmshowbookmark)• -f Binary file containing a bookmark indicating the new scraping point• -l Bookmark indicating the new scraping point (user friendly string• obtained from dmdecodebookmark)• -s <A subscription name>

To set to a known SCN:• bin/dmsetbookmark -I <instance name> -s <subscription name> -

l '<start scn>;<commit scn>.0.0.0.0.0;<current scn>.0.0.0.0.0.0 '

• (Note: -l is lower case L, you must SINGLE QUOTE the bookmark)

• For Example:• bin/dmsetbookmark -I MYINSTANCE -s FINANCE -

l '976207;976207.0.0.0.0.0;976207.0.0.0.0.0.0'

To set to a bookmark obtained from the target using dmshowbookmark• bin/dmsetbookmark -I <instance name> -b <hex encoded bookmark string -

s <subscription name>

• For Example:• bin/dmsetbookmark -I MYINSTANCE -b 2FC5GJHKLKSJLKJL458K9K809IK9 -s FINANCE


34

dmsetbookmark Considerations

• The staging store should also be cleared upon setti ng bookmark

• Call dmclearstagingstore command

• Remove txq* files in instance folder

• Remove all files in stagingstore directory

• The set bookmark position indicates where CDC shoul d start scraping next time replication starts

• The individual tables may have “capture” positions. CDC does not send changes until it reaches the capture point.

• The -a option will update the capture positions on all non-parked tables in the subscription to the same start position


35

Logs required by CDC

• dmshowlogdependency – Show Log Dependency

• Use this command to display information about the database logs that are used by InfoSphere CDC and are required for replication. Use this command to implement a log retention policy. With this command you can display the following information:

• A list of all the database logs that are required for the specified instance. If you are shipping your archive logs, a log status is also displayed.

• A list of the required database logs that are missing for the specified instance. If you are shipping your archive logs, a log status is also displayed.

• The earliest open transaction in the database log for the specified instance.

• The database log that the specified instance of InfoSphere CDC is currently reading on the source.

• The database log for the subscription that the specified instance of InfoSphere CDC is currently applying on the target.


36

dmshowlogdependency output example

•Logs read by current CDC (-l option)

C:\Program Files\DataMirror\Transformation Server for UDB\bin>dmshowlogdependency.exe -I db2u1 -s TEST -l

C:\DB2\NODE0000\SQL00001\SQLOGDIR\S0000006.LOG

•List of logs unread by CDC (-i option)

C:\Program Files\DataMirror\Transformation Server f or UDB\bin>dmshowlogdependency.exe -I db2u1 -s TEST -i








•You can implement a log retention policy using the CDC commands to determine which logs are no longer required by CDC and can be purged


37

Checkpoint:

• Q5: How can we use the CDC commands to develop a lo g retention policy? Describe the logic in pseudo code


38

Managing Tables for Replication Commands

• The following commands help you manage the tables t hat you want to replicate with InfoSphere CDC.

Managing Tables for Replication command Description

dmdescribe Describe source tables.

Use this command to send source table mapping changes over to the target. CDC will resynchronize the source/target table mappings and update metadata on both sides.

Operations performed in the Management Console will automatically perform a describe operation when necessary.

dmflagforrefresh Mark table for Refresh

Use this command to flag a source table for refresh. When you flag a table for refresh, you are selecting the tables that you want to refresh at a future point in time. Use this procedure when you have selected Refresh as your replication method on a subscription

dmpark Use this command to park a source table. By parking a source table, you tell InfoSphere CDC that you do not want to capture changes for that particular table in a subscription.

When you park a table, InfoSphere CDC does not replicate any subsequent changes you make on the source table, which may result in inconsistent source and target tables.


39

Managing Tables for Replication Commands (cont’d)Managing Tables for Replication command Description

dmmarktablecapturepoint Mark a table capture point on a source table

Use this command to mark a table capture point on a source table and move the table to active state. If you changed the table before executing this command, those changes will not be replicated.

dmreaddtable Use this command to update the definition of one or more source tables in the InfoSphere CDC metadata. Run this command after you have changed the definition of a source table using your relational database

This command will set the table status to Parked after updating the source table definition in InfoSphere CDC metadata. If there is only one DDL change you can use the –a parameter to preserve the Active status for your tables.

dmreassigntable Use this command to update the definition of a target table in InfoSphere CDC metadata after you change the definition of the target table in your database.

dmsetreplicationmethod Use this command to change the replication method for tables in a subscription. When running this command, InfoSphere CDC changes the status of any Active tables to Refresh

Replication Methods:

•-m Specifies that tables will use Mirror (Change Data Capture) as the replication method

•-r Specifies that tables will use Refresh (Snapshot) as the replication method (Previous bookmark position will be lost, supplemental logging will be disabled)


40

Continuous Capture feature

• Continuous Capture is a product feature that is designed to accommodate those replication environments in which it is necessary to separate the reading of the database logs from the transmission of the logical database operations. This is useful when you want to continue processing log data even if replication and your subscriptions stop due to issues such as network communication failures over a fragile network, target server maintenance, or some other issue.

• You can enable or disable Continuous Capture without stopping subscriptions.

• Continuous Capture allows you to avoid spikes in your source system CPU resource utilization by continuing to process log data (and write to disk as necessary) even when subscriptions are stopped. This feature allows you to avoid situations where the product uses no CPU when subscriptions are stopped and high CPU when you start subscriptions after a prolonged target system outage.

• This functionality comes with the trade-off of additional disk utilization on the source machine in order to accumulate changes from the database log file when these are not being replicated to the target machine. These trade-offs should be evaluated and understood before deciding to use this feature in your replication environment.


41

Continuous Capture commands

• To use the commands in this section that are applicable to Continuous Capture, you must set the staging_store_can_run_independentlysystem parameter to false

• dmenablecontinuouscapture – Enable Continuous Capture

• Use this command to enable Continuous Capture for your staging store

• dmdisablecontinuouscapture – Disable Continuous Capture

• Use this command to disable Continuous Capture for your staging store


42

Staging Store Commands

• dmgetstagingstorestatus• Use this command to retrieve status information on the staging store and

Continuous Capture• Example output:

• dmgetstagingstorestatus -I <INSTANCE>The Staging Store is enabled.Continuous Capture is disabled.Capture is running.1 out of 2 subscriptions are running.The Staging Store is 0% full.Capture latency is 33 sec.

• dmclearstagingstore• Use this command to remove all contents from the InfoSphere CDC staging

store. The staging store is used to provide a cache of log records read from the database logs. There are times when the contents of the staging store are no longer valid and InfoSphere CDC will give instructions to clear the staging store of those cache records

• In addition to running this command, remove all tx* files (queue files), delete files in tmp and stagingstore folder


43

Subscription promotion operations

• Subscriptions created in the development environmen t are promoted to the production environment.

• When network communication is possible between the development environment and the production environm ent

• Promote subscription. In these cases, environment-dependent data is automatically changed to suit the relocated destination.

• When communication is not possible

• Export/import subscription settings to xml files.

• Commands:• dmexportconfiguration <path_to_configuration_file> [-L <locale>]

• dmimportconfiguration <path_to_configuration_file> [-L <locale>]


44

Definition promotion

• Enable definition copying

• Application example

• Apply definitions created in the test environment to the production environment (requires network connection)

• Create similar separate definitions based on a single definition


45

Definition export

• Able to store created definitions (subscription) as an XML file

• Able to manage history if XML file is managed with ClearCase and/or CVM

• Able to import exported XML files


46

Table Level Promotion

• The Promote Table Mappings wizard allows you to promote selected table mappings in a subscription rather than all table mappings.

Limitations of Subscription Level Promotion:

• A full replacement of the destination subscription is required.

• Promotion can be time-consuming when there are a large number of tables in the subscription.

• Perception of risk/error when modifying all table mappings in a subscription when only a small subset of changes were made

• Complex in scenarios where promotion failed for the subscription in between due to unavoidable error


47

What is Table Level Promotion

• Feature to promote table(s) mapping to new and exis ting subscription(s)• Way to migrate/copy table(s) mapping from test/development to staging and

production

• During promotion, schema mapping from source to source and target to target between source and target subscriptions are available in the MC

• During promotion, there are two options: first to replace only the selected table(s) mapping and second to replace all tables in selected subscription.

• Managed via the Management Console.

• Shows the difference in subscription XML before and after promotion

• The entire mapping properties of the selected table (s) get copied to target subscription e.g. filtering, trans lations, conflicts, user exits etc.• User exit(s) gets promoted even if it doesn’t exists at target,

• User exit(s) should be created before running the subscription(s), otherwise error.


48

What is Table Level Promotion

• Maintain the status of the table(s) within subscrip tion(s)• If the table mapping is new to the subscription, the table status of the

promoted table is set to MIRROR/REFRESH. The table needs to be refreshed before it can be mirrored.

• If the table mapping already exists, the status remains the same and the mapping properties will get over-written.

• Management Console detects when a table has been promoted and stores the history internally. The next time the same table is promoted, Management Console will show the previous target subscription as the default choice.


49

Graphical User Interface : MC

New table based promotion options


50

Backup

• Definition metadata

• Definition changes are captured with dmbackupmd

• Operation metadata

• Located in source and/or target DB, so captured as part of the DB backup

• You should always back up your metadata when there are changes to your subscription configuration and table status


51

Checkpoint:

• Q6: Describe examples of when Subscription level and Table level promotion can be used


52

Monitoring CDC


53

Monitoring replication commands

• The following commands help you monitor replication in InfoSphere CDC:

Monitoring commands Description

dmshowevents Display InfoSphere CDC events

dmclearevents Clear event logs

dmgetsubscriptionstatus Get subscription Status


54

Managing events from command line

• dmclearevents – Clear event logs

• Use this command to delete events from the Event Log view in Management Console

• System parameter events_max_retain controls the maximum number of events that CDC will store in the event log

• dmshowevents – Display InfoSphere CDC events

• Use this command to display InfoSphere CDC events to standard output. You can use this command as an alternative to showing InfoSphere CDC events in the Event Log view in Management Console

• The events are displayed in chronological order


55

dmgetsubscriptionstatus – Get subscription status

• Issue this command on the InfoSphere CDC source to retrieve status information for one or more subscriptions and send the results to standard output

• CDC Status

• Recovering – The subscription is in an undetermined state

• Idle – The subscription is not running

• Starting – The subscription is in start up mode and is not currently replicating data

• Running – The subscription is running and replicating data


56

Subscription status in the MC• Subscriptions configured on the Management Console will have one of the following status.

• Can be captured with dmgetsubscriptionstatus comman d.

Status Description

Refresh Indicates that InfoSphere CDC is refreshing data from source to target. After refresh is completed InfoSphere CDC starts subscription mirroring in accordance with the replication format selected for the subscription, or reverts to “non-active” status.

Continuous mirroring

Indicates that InfoSphere CDC is mirroring data between the source and the target. This occurs when continuous mirroring is started in subscription.

Netchange mirroring (Scheduled End)

Indicates that InfoSphere CDC is mirroring the changes to the source since completion of the last replication.

InfoSphere CDC completes replication after changes are mirrored to the target.

Ending Indicates that InfoSphere CDC is nearing completion of replication activities in subscription, and is in the process of shutting down.

Inactive Indicates that InfoSphere CDC is not replicating data in subscription.

Unknown Indicates that InfoSphere CDC cannot discern the subscription status. This is because there is no communication between the monitoring process and data store, or the subscription is using an external source data store.


57

Event log

• Displays events and messages generated by InfoSpher e CDC during replication activity

• Enables monitoring and identification of problems i n subscription replication activities

• The following actions are possible in “Event log” vie w

• Display events generated in specific subscriptions or data stores

• Filter events based on severity.

• Copy events

• It is possible to attach the event to a separate application. For example, there may be times when a user would like to copy the event to an email for technical support.

• Delete messages that no longer require checking from the “Event log” view.

• Ability to export event log to local computer.


58

Event log view

• Displays list for each event location

Log event location

Description

Source datastore

Displays events generated by source data store

Source Subscription

Displays events generated by source data store subscription

Target datastore

Displays events generated by target data store

Target subscription

Displays events generated by target data store subscription

Type Description

NotificationIn these events feedback is provided on InfoSphere CDC operations. These events do not generate errors.

DiagnosisThese events include information that will benefit diagnosis or analysis of problems that may be triggered by specific actions or operations.

Warning or alert

These events describe status detected by InfoSphere CDC that may trigger errors

Error or escape

These events describe explanations of errors detected by InfoSphere CDC or explanations of the status that triggered InfoSphere CDC shutdown.


59

Notification• Replication problems are linked to external monitor ing tools via email or user handlers

• Settings level• Data store• Subscription

• Overwrites data store level settings

• Notification methods• Email• User handler

Runs user handler in response to critical events in

source collection/refresh

E-mail notification


60

Latency threshold and notification setting

• Able to set up notification when the latency thresh old is exceeded for subscription• Setup

• “Configuration” � Click on “Subscription”• Right-click subscription and select “Latency threshold”.

• The following is output to the event log• Warning: Event type warning, event ID3170• Problem: Event type warning, event ID3172

• Also output to email and User Exit by setting notification as target application data


61

Checkpoint:

• Q7: What are 5 different ways to monitor the healt h of the system where CDC is running?

is cdc 2 operations and commands - ibm.com · ibm infosphere change data capture is running....

Documents