Table of Contents

Search

  1. Preface
  2. Introduction to PowerExchange
  3. DBMOVER Configuration File
  4. Netport Jobs
  5. PowerExchange Message Logs and Destination Overrides
  6. SMF Statistics Logging and Reporting
  7. PowerExchange Security
  8. Secure Sockets Layer Support
  9. PowerExchange Alternative Network Security
  10. PowerExchange Nonrelational SQL
  11. PowerExchange Globalization
  12. Using the PowerExchange ODBC Drivers
  13. PowerExchange Datatypes and Conversion Matrix
  14. DTL__CAPXTIMESTAMP Time Stamps
  15. PowerExchange Glossary

CAPI_CONNECTION - ORCL Statement

CAPI_CONNECTION - ORCL Statement

The ORCL CAPI_CONNECTION statement specifies a named set of parameters that the Consumer API (CAPI) uses to connect to the change stream and control PowerExchange Oracle CDC with LogMiner processing for Oracle sources.
Linux, UNIX, and Windows
Oracle
CAPI_CONNECTION - UOWC and ORACLEID
Yes for PowerExchange Oracle CDC with LogMiner
CAPI_CONNECTION=([DLLTRACE=
trace_id
]  ,NAME=
capi_connection_name
 [,TRACE=
trace_name
]  ,TYPE=(ORCL     [,ARRAYSIZE=
array_size
|
100
]     [,BYPASSUF={
N
|Y}]     [,CATBEGIN=
hh:mm
|
00:00
]     [,CATEND=
hh:mm
|
24:00
]     [,CATINT=
minutes
|
1400
]     [,COMMITINT=
minutes
|
5
]     [,GENRLOCK={
N
|Y}]     [,IGNUFMSG={
N
|Y}]     [,LOGDEST=
logdest_id
]     [,LGTHREAD=
instance_number
]     [,ONLINECAT={
N
|Y}]     ,ORACOLL=
collection_id
[,ROWID={
N
|Y|ALLOW}]     [,SELRETRY=
retry_number
|
1000
]     [,SNGLINST={
N
|Y}]      ) )
DLLTRACE=
trace_id
Optional. User-defined name of the TRACE statement that activates internal DLL tracing for this CAPI.
Specify this parameter only at the direction of Informatica Global Customer Support.
NAME=
capi_connection_name
Required. Unique user-defined name for this CAPI_CONNECTION statement.
Maximum length is eight alphanumeric characters.
TRACE=
trace_name
Optional. User-defined name of the TRACE statement that activates the common CAPI tracing.
Specify this parameter only at the direction of Informatica Global Customer Support.
TYPE=(ORCL, ... )
Required. Type of CAPI_CONNECTION statement. For PowerExchange Oracle CDC with LogMiner sources, this value must be ORCL.
ARRAYSIZE={
array_size
|
100
}
Optional. The number of rows of the prefetch array that PowerExchange uses to read the Oracle redo logs.
Enter a number from 0 through 2147483647. Default is 100.
A value less than 100 can degrade Oracle CDC with LogMiner performance.
A value of 0 disables prefetch. Enter 0 only at the direction of Informatica Global Customer Support.
BYPASSUF={
N
|Y}
Optional. Controls whether PowerExchange ends abnormally or issues a warning message when Oracle LogMiner returns an unformatted log record.
LogMiner returns unformatted log records when Global Temporary Tables are updated, or when ONLINECAT=Y and the log data that is being read is inconsistent with the catalog.
Enter one of the following options:
  • N
    . PowerExchange ends with an error whenever it receives an unformatted log record from Oracle LogMiner.
  • Y
    . PowerExchange writes a warning message to the PowerExchange message log that indicates unformatted log data was found and then continues processing. Depending on the amount of unformatted log data, PowerExchange might write many warning messages. To suppress these warning messages, specify Y for the IGNUFMSG parameter.
Default is N.
Enter Y if the Oracle instance contains Global Temporary tables. Otherwise, do not include the BYPASSUF parameter.
CATBEGIN={
hh
:
mm
|
00:00
}
Optional. Earliest time of day, in a 24-hour clock format, at which PowerExchange requests Oracle to write a copy of the Oracle catalog to the redo logs.
If you specify a value for the CATBEGIN parameter, you must also specify a value for the CATEND parameter.
Default is 00:00.
CATEND={
hh
:
mm
|
24:00
}
Optional. Latest time of day, in a 24-hour clock format, at which PowerExchange requests Oracle to write a copy of the Oracle catalog to the redo logs.
If you specify a value for the CATEND parameter, you must also specify a value for the CATBEGIN parameter.
Default is 24:00.
CATINT={
minutes
|
1440
}
Optional. Time interval, in minutes, between requests to copy the Oracle catalog to the redo logs.
Enter a number from 1 through 1440. Default is 1440.
If this interval elapses but the time is outside the time period that is specified by the CATBEGIN and CATEND parameters, PowerExchange does not request Oracle to make a copy of the Oracle catalog. Instead, PowerExchange waits the amount of time that you specify in the CATBEGIN parameter to request a catalog copy.
COMMITINT={
minutes
|
5
}
Optional. Time interval, in minutes, between the SQL COMMIT operations that PowerExchange issues to commit the transactions that the Oracle LogMiner session generates.
Enter a number from 1 through 60. Default is 5.
Although PowerExchange does not update data in user tables while reading change data from the redo logs, the Oracle LogMiner interface automatically generates transactions for the LogMiner sessions that PowerExchange initiates. Oracle leaves these transactions open, or in-flight, until the LogMiner session ends.
To be able to restart change data extraction operations efficiently, PowerExchange must occasionally issue SQL COMMIT operations to end these in-flight transactions. Otherwise, the restart of all future real-time extraction operations might be impacted because PowerExchange always begins reading change data at the beginning of the oldest in-flight UOW.
GENRLOCK={
N
|Y}
Optional. Controls whether PowerExchange generates a safe restart point for requests for restart points that match the current end-of-log (EOL).
Enter one of the following options:
  • N
    . PowerExchange generates restart points that match the current EOL, ignoring any in-flight transactions for the source tables.
  • Y
    . PowerExchange generates safe restart points for source tables.
Default is N.
A safe restart point for a source table is a point in the change stream that does not skip any in-flight UOWs for that table. To generate a safe restart point for a source table, PowerExchange gets an exclusive lock on the table to stop further changes. PowerExchange then searches the Oracle catalog for the point in the change stream that matches the earliest active transaction for the table and uses this point as the restart point. If no in-flight UOWs exist for a table, PowerExchange uses the current EOL. PowerExchange releases the lock on the source table after restart point generation completes. Then changes can be written to the table again.
PowerExchange generates restart tokens that match the current EOL in the following situations:
  • You cold start the PowerExchange Logger for Linux, UNIX, and Windows and the pwxccl configuration file does not specify the SEQUENCE_TOKEN and RESTART_TOKEN parameters.
    PowerExchange gets locks for all of the tables that are associated with active capture registrations to be used for PowerExchange Logger processing.
  • The restart token file for a CDC session specifies the CURRENT_RESTART option on the RESTART1 and RESTART2 special override statements.
    PowerExchange gets locks only for the tables in the CDC session to which the special override statements apply.
  • A database row test in the PowerExchange Navigator that uses the SELECT CURRENT_RESTART SQL statement.
    PowerExchange gets a lock for the table represented by capture registration associated with the extraction map used in the database row test.
  • A DTLUAPPL utility operation that uses the RSTTKN GENERATE option.
    PowerExchange gets a lock for the table represented by the capture registration specified in the utility control statements.
IGNUFMSG={
N
|Y}
Optional. Controls whether PowerExchange writes warning messages to the PowerExchange message log file for unformatted data records.
Enter one of the following options:
  • N
    . PowerExchange does not write any warning messages.
  • Y
    . PowerExchange writes warning messages.
Default is N.
LOGDEST=
logdest_id
Optional. The numeric identifier for the archive log destination that you want to force PowerExchange to use. This archive log destination must be local to the Oracle instance that PowerExchange is using.
Enter a number from 1 through 10.
For example, to use archived logs from the destination set by the LOG_ARCHIVE_DEST_3 parameter in the init.ora file, specify LOGDEST=3.
The SNGINST parameter affects how PowerExchange uses the archive log destination and the Oracle instance that you specify in LOGDEST and LGTHREAD.
If you specify Y for the ONLINECAT parameter, PowerExchange validates and then ignores the LOGDEST and LGTHREAD parameters.
LGTHREAD=
instance_number
Optional. The instance number for the Oracle instance. PowerExchange uses this instance number to identify the archived redo logs to process.
Enter a number from 1 through 2147483647.
For non-RAC environments, if you specify this parameter, set it to 1.
The SNGINST parameter affects how PowerExchange uses the archive log destination and the Oracle instance that you specify in LOGDEST and LGTHREAD.
If you specify Y for the ONLINECAT parameter, PowerExchange validates and then ignores the LOGDEST and LGTHREAD parameters.
ONLINECAT={
N
|Y}
Optional. Controls whether PowerExchange directs Oracle LogMiner to use the Oracle online catalog or the copy of the catalog in the redo logs to format log data for CDC.
Enter one of the following options:
  • N
    . Oracle LogMiner uses the copy of the catalog from the archived redo logs and PowerExchange tracks schema changes to ensure that data loss does not occur.
  • Y
    . Oracle LogMiner uses the online catalog and PowerExchange cannot track schema changes.
Default is N.
When you configure PowerExchange to use the online catalog for formatting log data, PowerExchange still uses catalog copies to determine the restart point for change data extraction operations. Therefore, you must copy the online catalog to the Oracle redo logs on a regular basis.
Change data extraction operations generally initialize faster when you configure PowerExchange to create LogMiner sessions with the online catalog instead of a catalog copy. However, when LogMiner uses the online catalog, it does not track DDL changes, and cannot format log records for tables that have schema changes.
If LogMiner uses the online catalog and you make schema changes while LogMiner is reading log data, LogMiner passes unformatted log records for subsequent changes to PowerExchange. If you specify N for the BYPASSUF parameter or accept the default value of N, PowerExchange fails the extraction request after Oracle passes the first unformatted record. Otherwise, PowerExchange skips the unformatted record and continues processing, which results in change data loss. Therefore, specify N for the ONLINECAT parameter, or allow it to default, if you have the following requirements:
  • You specify Y for the BYPASSUF parameter and need to change the schema of tables registered for capture while change data extraction operations are running.
  • You need to start an extraction from a point in the Oracle redo logs that contains table data that PowerExchange captured under a previous schema.
ORACOLL=
collection_id
Required. Oracle collection identifier, which must match the value specified in the ORACLEID statement.
ROWID={
N
|Y|ALLOW}
Controls whether Oracle physical rowid values are included in captured change records for tables that do not have Oracle row movement enabled. PowerExchange writes the rowid values to the PowerExchange-generated DTL__CAPXROWID column. For example, use this parameter if you have unkeyed source tables on which you need to perform some processing that requires a unique row ID when extraction sessions run.
Enter one of the following options:
  • N
    . Do not capture rowid values. The DTL__CAPXROWID column contains null values.
  • Y
    . Capture rowid values for tables that do not have row movement enabled and write the values to the DTL__CAPXROWID column in change records. If a table has row movement enabled, capture processing ends abnormally.
  • ALLOW
    . Capture rowid values for tables that do not have row movement enabled and write the values to the DTL__CAPXROWID column in change records. If a table has row movement enabled, return null values to the DTL__CAPXROWID column and continue capture processing. You might want capture processing to continue if you do not need rowid values for the tables that have row movement enabled.
This parameter pertains to PowerExchange Oracle CDC with LogMiner. If you use PowerExchange Express CDC for Oracle, set the similar ROWID parameter in the OPTIONS statement of the Express CDC configuration file instead.
Default is N.
SELRETRY={
retry_number
|
1000
}
Optional. The number of times that PowerExchange immediately loops back to the Oracle LogMiner call before implementing a graduated-scale wait loop.
After PowerExchange retries the call to LogMiner the specified number of times, PowerExchange implements a wait interval between each subsequent retry. The wait interval begins at one millisecond and gradually increases to one second. When LogMiner returns data, PowerExchange resets the wait interval to 0, and the process begins again for the next call to LogMiner.
For the
retry_number
variable, enter a number from 0 through 2147483647. Default is 1000.
If you specify a nonzero value, PowerExchange uses nonblocking SQL to ensure that it can process a user request to shut down an extraction session in a timely manner.
If you specify 0, PowerExchange does not use nonblocking SQL. This setting improves CPU consumption but can prolong shutdown of an extraction session. On quiescent Oracle instances, PowerExchange does not honor a shutdown request until Oracle returns log data. On Oracle instances where update activity is occurring, shutdown behavior does not noticeably change.
If you capture change data from an Oracle 12.1 or later source, set the SELRETRY parameter to 0. Otherwise, the Oracle LogMiner sessions for PowerExchange CDC fail when trying to fetch change data.
SNGLINST={
N
|Y}
Optional. Controls whether PowerExchange uses only the archived redo logs from a specific Oracle instance and archive log destination.
Enter one of the following options:
  • N
    . PowerExchange uses the specified Oracle instance to search for the archived redo logs that contain copies of the Oracle catalog. After PowerExchange passes these logs to an Oracle LogMiner session, LogMiner determines the other archived redo logs to read.
  • Y
    . PowerExchange uses only the archive log destination and Oracle instance that you specify in LOGDEST and LGTHREAD parameters to read archived redo logs. LogMiner does not read any other archived redo logs. After PowerExchange processes the logs from the specified location, the change data extraction operation ends.
    If you specify Y, you must also specify the LOGDEST and LGTHREAD parameters to identify the archive log destination and Oracle instance to use. In a RAC environment, you must run separate change data extraction processes for all remaining Oracle instances in the RAC and determine how to properly merge the change data so that it can be applied to the targets.
Default is N.