Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerExchange for CDC and Mainframe 10.0
  • All Products

Table of Contents

Search

  1. Preface
  2. Change Data Capture Introduction
  3. PowerExchange Listener
  4. PowerExchange Condense
  5. DB2 for i5/OS Change Data Capture
  6. Remote Logging of Data
  7. Introduction to Change Data Extraction
  8. Extracting Change Data
  9. Monitoring CDC Sessions
  10. Managing Change Data Extractions
  11. Tuning CDC Sessions
  12. Appendix A: DTL__CAPXTIMESTAMP Time Stamps

CDC Guide for i5/OS

CDC Guide for i5/OS

Back Next

PowerExchange-Generated Columns in Extraction Maps

PowerExchange-Generated Columns in Extraction Maps

Besides the table columns that are defined in capture registrations, extraction maps include columns that PowerExchange generates.
These PowerExchange-generated columns contain CDC-related information, such as the type of SQL change and time stamp.
When you import an extraction map in Designer, PWXPC includes the PowerExchange-generated columns in the source definition.
When you run a database row test on an extraction map, the PowerExchange Navigator displays the PowerExchange-generated columns in the results. By default, the PowerExchange Navigator hides these columns from view when you open the extraction map. To display these columns, open the extraction map, right-click anywhere within the
Extract Definition
 window, and select
Show Auto Generated Columns
.
By default, all columns except the DTL__
columnname
_CNT and DTL__
columnname
_IND columns are selected in an extraction map. To select these columns, you must edit the extraction map.
The following table describes the columns that PowerExchange generates for each change record:
Column
Description
Datatype
Length
DTL__CAPXRESTART1
A binary value that represents the position of the end of the UOW for that change record followed by the position of the change record itself.
The length of a sequence token varies by data source type, except on z/OS where sequence tokens for all data source types have the same length.
The value of DTL__CAPXRESTART1 is also known as the
sequence token
, which when combined with the
restart token
 comprises the restart token pair.
A sequence token for a change record is a strictly ascending and repeatable value.
VARBIN
255
DTL__CAPXRESTART2
A binary value that represents a position in the change stream that can be used to reconstruct the UOW state for the change record, with the following exceptions:
  • Microsoft SQL Server CDC. A binary value that contains the DBID of the distribution database and the name of the distribution server.
  • Change data extracted from full condense files on z/OS or i5/OS. A binary value that contains the instance name from the registration group of the capture registration.
The length of a restart token varies by data source type. On z/OS, restart tokens for all data source types have the same length, except for change data extracted from full condense files.
The value of DTL__CAPXRESTART2 is also known as the
restart token
, which when combined with the
sequence token
 comprises the restart token pair.
VARBIN
255
DTL__CAPXROWID
For PowerExchange Oracle CDC with LogMiner and Express CDC for Oracle, provides the physical rowid value. PowerExchange can include rowid values in change records for Oracle tables only if the tables do not have row movement enabled.
To enable the capture of rowid values, you must configure one of the following parameters:
  • For PowerExchange Oracle CDC with LogMiner, set the ROWID parameter in the ORCL CAPI_CONNECTION statement to Y or ALLOW.
  • For PowerExchange Express CDC for Oracle, include the OPTIONS ROWID=Y statement in the Express CDC configuration file.
The rowid is useful for processing rows in unkeyed tables during CDC extraction sessions.
CHAR
18
DTL__CAPXRRN
For DB2 on i5/OS only, the relative record number.
DECIMAL
10
DTL__CAPXUOW
A binary value that represents the position in the change stream of the start of the UOW for the change record.
VARBIN
255
DTL__CAPXUSER
The user ID of the user who made the change to the data source, with the following exceptions:
  • For Adabas 8.3 CDC sources, this value is the Security User-id (SECUID) of the user if the Adabas File Definition includes the system field SY=SECUID.
  • For Datacom table-based CDC sources, this value is the MUF name.
  • For DB2 for i5/OS sources, this value depends on the LIBASUSER parameter in the AS4J CAPI_CONNECTION statement. If LIBASUSER=Y, this value is the library name and file name of the file where the change was made. If LIBASUSER=M, this value is the library name, file name, and data member name of the file where the change was made. If LIBASUSER=N, this value is the user ID of the user who made the change.
  • For DB2 for z/OS sources, this value depends on the UIDFMT parameter in the LRAP CAPI_CONNECTION. Depending on the parameter setting, this value can be a DB2 connection identifier, correlation identifier, connection type, plan name, user ID, or all of these values in the format
    UID:PLAN:CORR:CONN:CTYPE
    . If you do not specify the UIDFMT parameter, this value is the user ID of the user who made the change.
  • For IDMS sources, this value is the value that the user program puts in the program name field of the application subschema control block. Usually, this value is the user program name.
  • For Microsoft SQL Server sources, this value depends on the UIDFMT parameter in the MSQL CAPI_CONNECTION statement. If UIDFMT=DBNAME, this value is the SQL Server publication database name. If UIDFMT=NONE, this value is a null.
  • For Oracle sources, this value is a user ID that PowerExchange gets from Oracle, if available. Otherwise, this value is null. This information applies to both PowerExchange Oracle CDC with LogMiner and PowerExchange Express CDC for Oracle.
VARCHAR
255
DTL__CAPXTIMESTAMP
The time stamp that the source DBMS records for a change on the source database.
This value can be either the time stamp that the source DBMS writes to the change record in the database logs or the time stamp of the transaction commit on the source database.
The time stamp type depends on the source type and certain parameters:
  • For DB2 for Linux, UNIX, and Windows sources, the transaction commit time stamp.
  • For Microsoft SQL Server sources, the time at which the change was written to the distribution database.
  • For PowerExchange Express CDC for Oracle sources, the time stamp type is controlled by the TIME_STAMP_MODE parameter in the OPTIONS statement of the Express CDC configuration file.
  • For all sources that require a UOWC CAPI_CONNECTION statement, the time stamp type is controlled by the TIMESTAMP parameter in the UOWC CAPI_CONNECTION statement in the DBMOVER file.
For more detailed information about time stamps for each source type, see DTL__CAPXTIMESTAMP Time Stamps.
The time stamp format is: 
YYYYMMDDhhmmssnnnnnn
Where:
  • YYYY is the four-digit year.
  • MM is the month.
  • DD is the day.
  • hhmmssnnnnnn is hours, minutes, seconds, and microseconds.
DB2 for Linux, UNIX, and Windows and Oracle do not support microseconds in the time stamp.
CHAR
20
DTL__CAPXACTION
A single character that indicates the type of change record that PowerExchange passes to the target during extraction processing. A DTL__CAPXACTION value corresponds to the type of change operation on the source database.
Valid values are:
  • I. Insert.
  • D. Delete.
  • U. After image of an update.
  • T. Before image of an update. (ODBC connections only)
If you specify an
Image Type
 of
BA
 on the connection for a CDC session, PowerExchange generates a delete record followed by an insert record for a source update. In the delete record, the DTL___CAPXACTION column contains the value D. In the insert record, the DTL__CAPXACTION column contains the value I.
If you specify an
Image Type
 of
AI
 on the connection for a CDC session, PowerExchange generates one record for an update. In this record, the DTL___CAPXACTION column contains the U value.
If you use an ODBC connection to write change data to a staging table and either set the ODBC driver CAPXIMAGETYPE parameter to TU or enter the SQL escape sequence DTLIMTYPE=TU in PowerCenter, this column can contain a value of T or U. For each source update, PowerExchange delivers two records to the staging table: one for the before image and another for the after image. In the before image record, the DTL__CAPXACTION column contains the T value. In the after image record, The DTL__CAPXACTION column contains the U value.
CHAR
1
DTL__CAPXCASDELIND
For DB2 for z/OS sources only, a single character that indicates whether DB2 has deleted the row because the table specifies the ON DELETE CASCADE clause. Valid values are:
  • Y. Indicates that DB2 deleted this row because of a cascade delete rule.
  • N. Indicates that DB2 did not delete this row because of a cascade delete rule.
CHAR
1
DTL__BI_
columnname
For UPDATE operations, the value of the before image of the selected column in the change record.
Datatype of the source column
Length of the source column
DTL__CI_
columnname
For UPDATE operations, a single character that indicates whether the selected column was changed. Valid values are:
  • Y. Indicates that the column changed.
  • N. Indicates that the column did not changed.
  • Null value. Indicates an INSERT or DELETE operation.
CHAR
1
DTL__
columnname
_CNT
Binary count column. PowerExchange generates this column for variable length columns of types VARCHAR and VARBIN to determine the length of the column during change data extraction processing.
By default, binary count columns are not selected in an extraction map. You must edit an extraction map to select these columns.
NUM32U
0
DTL__
columnname
_IND
Null indicator column. PowerExchange generates this column for nullable columns to indicate the nullable value for the column.
By default, null indicator columns are not selected in an extraction map. You must edit an extraction map to select these columns.
BIN
1
0 COMMENTS
We’d like to hear from you!