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.4.1
  • All Products

Table of Contents

Search

  1. Preface
  2. PowerExchange Navigator Introduction
  3. Data Maps
  4. Data Maps for Specific Data Sources
  5. Copybooks
  6. Registration Groups and Capture Registrations
  7. Extraction Groups and Extraction Maps
  8. Personal Metadata
  9. Database Row Test
  10. PowerExchange Navigator Examples
  11. Appendix A: PowerExchange Functions for User-Defined Fields
  12. Appendix B: User Access Method Programs
  13. Appendix C: Application Groups and Applications
  14. Appendix D: Data Map Properties
  15. Appendix E: Record, Field, and Table Properties
  16. Appendix F: DTL__CAPXTIMESTAMP Time Stamps
  17. Appendix G: Trace for Creating a Memory Map When Importing a COBOL Copybook

Navigator User Guide

Navigator User Guide

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 are selected in extraction maps except the DTL__
columnname
_CNT, DTL__
columnname
_IND, and DTL__CI_
columnname
 columns. To add 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
Provides 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
Provides 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 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 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 for i sources only, provides the relative record number.
DECIMAL
10
DTL__CAPXUOW
Provides 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
Provides 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 i (i5/OS) CDC 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. If LIBASUSER=P, this value is the name of the program that made the change.
  • For Db2 for z/OS CDC 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 CDC 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 IMS synchronous CDC sources, this value depends on the UIDFMTIMS parameter in the LRAP CAPI_CONNECTION statement. Depending on the parameter setting, this value can be a user ID, a PSB name, or both values in the format
    userid
    :
    psbname
    . If you do not specify the UIDFMTIMS parameter, the user ID is used by default.
  • For Microsoft SQL Server CDC 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 CDC sources, this value is a user ID that PowerExchange gets from Oracle, if available. Otherwise, this value is null.
VARCHAR
255
DTL__CAPXTIMESTAMP
Provides the time stamp that the source DBMS records for the database change record.
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 type of time stamp depends on the source type and certain parameters:
  • For Db2 sources on Linux, UNIX, or Windows, the transaction commit time stamp.
  • For Microsoft SQL Server sources, the time at which the change was written to the distribution database.
  • For MySQL sources, the time at which MySQL recorded the change event in the binary log.
  • For Oracle sources, the timestamp 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 on Linux, UNIX, or Windows and Oracle do not support microseconds in the time stamp.
CHAR
20
DTL__CAPXACTION
Indicates the type of change record that PowerExchange passed to the target during extraction processing. This indicator corresponds to the type of SQL change operation on the source database.
Valid values:
  • 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, indicates whether or not Db2 deleted the row because the table specifies the ON DELETE CASCADE clause. Valid values:
  • Y.
     Indicates that Db2 deleted the row because of a cascade delete rule.
  • N
    . Indicates that Db2 did not delete the row because of a cascade delete rule.
CHAR
1
DTL__BI_
columnname
Provides the before image of a column that an UPDATE operation changed.
Datatype of the source column
Length of the source column
DTL__CI_
columnname
Indicates whether or not an UPDATE operation changed the column value. Valid values:
  • Y
    . The column value was changed by an UPDATE operation.
  • N
    . The column was changed by an UPDATE operation.
  • null
    . The column was changed by an INSERT or DELETE operation. It was not changed by an UPDATE.
By default, the change indicator column is not included in extraction maps. To add it, you must edit an extraction map and select this auto-generated column.
CHAR
1
DTL__ST_
lob_columnname
For a Db2 for z/OS LOB column, indicates whether or not the column contains all of the LOB data. The ECCR provides incomplete LOB data if the data is not stored fully inline in the base table space or exceeds 32 KB in size. Valid values:
  • C
    . The column contains all of the LOB data. The ECCR was able to capture all of the LOB data because the data is stored fully inline in the base table space and does not exceed 32 KB.
  • I
    . The column contains incomplete LOB data. The ECCR was unable to capture all of the LOB data because the data is stored in an auxiliary table space, or the data is stored fully inline but exceeds 32 KB in size.
  • null
    . The column contains null data only.
If you have Db2 for z/OS source tables that include LOB data that is not stored fully inline in the base table, include this column. You can then use this column with PowerCenter transformations to retrieve all of the current LOB data for columns with incomplete data (DTL__ST_
columname
=I) and write the data to the target.
This field is included in extraction maps by default. To remove it, open the extraction map in the PowerExchange Navigator and deselect this auto-generated column.
CHAR
1
DTL__
columnname
_CNT
A binary count that PowerExchange generates for a variable length column of the type VARCHAR and VARBIN. The count is used to determine the length of the column during change data extraction processing.
By default, the binary count column is not included in extraction maps. To add it, you must edit an extraction map and select this auto-generated column.
NUM32U
0
DTL__
columnname
_IND
Indicates whether or not a nullable column contains a null. PowerExchange generates this column only for nullable columns.
By default, the null indicator column is not included in extraction maps. To add it, you must edit an extraction map and select this auto-generated column.
BIN
1
0 COMMENTS
We’d like to hear from you!