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. Appendix A: DTL__CAPXTIMESTAMP Time Stamps
  15. Appendix B: PowerExchange Glossary

CAPI_CONNECTION - AS4J Statement

CAPI_CONNECTION - AS4J Statement

The AS4J CAPI_CONNECTION statement specifies a named set of parameters that the Consumer API (CAPI) uses to connect to the change stream and control CDC processing for Db2 for i (i5/OS) CDC sources.
IBM i (formerly i5/OS)
Db2 for i
CAPI_CONNECTION - UOWC, AS400EVENTMSGQ, AS400USRJRNCODE
Yes for Db2 for i CDC
CAPI_CONNECTION=([DLLTRACE=
trace_id
]   ,NAME=
capi_connection_name
  [,TRACE=
trace_name
]   ,TYPE=(AS4J     ,INST=
instance_name
    ,JOURNAL=
library/journal_name
     [,ALWCLRPFM={
N
|Y}] [,ALWLIBRFSH={
N
|Y}] [,ALWNONRENT={
N
|Y}]     [,ALWPARTIAL={
N
|Y}]     [,AS4JRNEXIT={
N
|Y}]     [,EOF={
N
|Y}]    [,LIBASUSER={
N
|Y|M|P}]     [,NOCCUOWSZ=
uow_size
]     [,POLWAIT={
seconds
|
10
}] [,RTNBUFFSIZE={
kilobytes
|
960
}]     [,STOPIT=({
CONT
|TERM},{
number_exceptions
|
5
}] [,UOWRSTANY={
Y
|N}] ) )
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=(AS4J, ... )
Required. Type of CAPI_CONNECTION statement. For Db2 for i5/OS sources, this value must be AS4J.
INST=
instance_name
Required. User-defined name for the source instance. This name must match the name that you specify in the
Collection Identifier
property of the registration group.
If you are running PowerExchange Condense, this name must also match the name specified in DBID parameter in the CAPTPARM member.
JOURNAL=
library
/
journal_name
Required. Library name and journal name for the journal that contains change data for registered tables and from which PowerExchange extracts change data.
Optionally, you can override this journal specification by using one of the following methods:
  • For real-time extraction CDC sessions, use the
    Journal Name
    on the PWX CDC application connection.
  • For PowerExchange Condense, use the JRNL statement in the CAPTPARM configuration member.
  • For CAPXRT database row tests, use the
    AS400 Journal/Library
    property in the
    CAPXRT Advanced Parameters
    dialog box in the PowerExchange Navigator.
  • For real-time extraction CDC sessions that use ODBC connections, use the DTLJRNL parameter for the ODBC data source.
ALWCLRPFM= {
N
|Y}
Optional. Controls whether Db2 for i CDC processing stops or continues when PowerExchange encounters changes that result from an i5/OS Clear Physical File Member (CLRPFM) command or SQL TRUNCATE statement that is issued against a Db2 table registered for change data capture. PowerExchange cannot capture the deletions that result from a CLRPFM command or TRUNCATE statement.
IBM i version 7.2 introduced support for SQL TRUNCATE operations on i5/OS tables. If you have an earlier IBM i version, this parameter pertains to CLRPFM commands only.
Options are:
  • N
    . PowerExchange CDC processing stops when PowerExchange detects changes that are caused by a CLRPFM command or TRUNCATE statement.
  • Y
    . PowerExchange ignores the CLRPFM command or TRUNCATE statement and continues CDC processing. The data integrity of the CDC target might be damaged. If you also specify the AS400EVENTMSGQ statement in the DBMOVER configuration file, PowerExchange issues the DTL3002 message to the specified message queue when PowerExchange encounters a journal entry for a CLRPFM command or TRUNCATE statement.
    If you set this parameter to Y, the data integrity of the CDC targets might be damaged. Specify this parameter only at the direction of Informatica Global Customer Support.
    If you also specify a message queue in the AS400EVENTMSGQ parameter of the DBMOVER file, PowerExchange writes message DTL3002 to the queue if PowerExchange encounters a journal entry for a CLRPFM command or TRUNCATE statement.
Default is N.
ALWLIBRFSH={
N
|Y}
Optional. Controls whether you can refresh the CDC interest list after you add or delete libraries with instances of the same Db2 source table, without stopping and restarting real-time CDC sessions. This parameter is useful in the following situation: 1) you have instances of the same Db2 source table (or file) in multiple libraries, 2) the table instances are journaled to the same journal, and 3) you routinely add or delete libraries that contain an instance of the table.
Options are:
  • N
    . Disables refresh processing of the CDC interest list for library additions or deletions. If you add libraries, PowerExchange does not start change data capture for those libraries until you stop and restart the CDC session. In this case, some changes might be lost. If you delete libraries, the CDC session ends abnormally.
  • Y
    . Enables you to use the SNDPWXJRNE command to refresh the CDC interest list for library additions and deletions. The command sends journal entries for add library and delete library requests to the journal, using the PowerExchange default journal entry type codes or the unique codes that you define in the AS400USRJRNCODE statement. In PowerCenter, you must specify the asterisk (*) wildcard for the library name in the
    Library/File Override
    session property for the real-time session.
    If you also specify a message queue in the AS400EVENTMSGQ parameter of the DBMOVER file, PowerExchange writes message DTL3003I for an *ADDLIB (add library) event and message DTL3004I for a *DLTLIB (delete library) event to the queue.
Default is N.
ALWNONRENT={
N
|Y}
Optional. Controls whether PowerExchange generates a next receiver (NR) journal entry when i5/OS does not provide one in the journal receiver. PowerExchange requires an NR entry to switch to the next journal receiver in the chain when the current one becomes full. However, i5/OS might not provide an NR entry under certain conditions, such as when a high level of transaction activity is occurring at the time a journal receiver switch is required. If i5/OS does not provide an NR entry, PowerExchange CDC processing ends unless you enable PowerExchange to generate an NR entry.
Options are:
  • N
    . Do not generate an NR entry. If the i system does not provide an NR entry, CDC processing ends abnormally.
  • Y
    . Generate an NR entry so that CDC processing can continue when the i system does not provide an NR entry. The generated NR entry is for PowerExchange internal use only and is not written to the actual journal receiver.
Default is N.
ALWPARTIAL={
N
|Y}
Optional. Controls whether PowerExchange processes journal receivers in partial status.
Options are:
  • N
    . PowerExchange fails processing if a journal receiver is in partial status.
  • Y
    . PowerExchange processes journal receivers in partial status.
Default is N.
If you specify Y for this parameter, you might compromise the data integrity of the change data being extracted because required changes might be unavailable. Specify this parameter only at the direction of Informatica Global Customer Support.
AS4JRNEXIT={
N
|Y}
Optional. Controls whether PowerExchange uses an exit program installed at the Delete Journal Receiver exit point, QIBM_QJO_DLT_JRNRCV, to prevent the deletion of journal receivers being processed for CDC.
Options are:
  • N
    . PowerExchange does not lock journal receivers that it is processing.
  • Y
    . PowerExchange locks any journal receiver that it is processing so that the journal receiver cannot be deleted from the i system. PowerExchange records the journal receivers that it is processing in a lock file called PWXJRNLCKP in the CONDLIB library. When PowerExchange switches to the next journal receiver on the chain, PowerExchange removes the record for the previous journal receiver from the lock file.
    If you also specify a message queue in the AS400EVENTMSGQ parameter of the DBMOVER file, PowerExchange writes the DTL3001 message to the specified queue for each journal receiver that a change data extraction successfully processes.
Default is N.
EOF={
N
|Y}
Optional. Controls whether PowerExchange stops change data extractions when the end-of-log (EOL) is reached.
Options are:
  • N
    . Do not stop change data extractions when the EOL is reached.
  • Y
    . Stop change data extractions when the EOL is reached.
Default is N.
Important
: Because this parameter affects all users of the AS4J CAPI_CONNECTION statement, Informatica recommends that you use one of the following alternative methods to stop change data extractions at EOL:
  • For CDC sessions that use real-time extraction mode, specify 0 for the
    Idle Time
    attribute of the PWX DB2i5OS CDC Real Time application connection.
  • For PowerExchange Condense, specify 1 for the COLL_END_LOG statement in the CAPTPARM configuration member.
  • For CDC sessions that use ODBC connections, specify 0 for the WAITTIME parameter in the ODBC data source.
LIBASUSER={
N
|Y|M|P}
Optional. Controls the type of value that PowerExchange writes to the DTL__CAPXUSER field of each change record.
Options are:
  • N
    . Writes the user ID of the user who made the change.
  • Y
    . Writes the library name and file name of the file where the change was made.
    If you set the ALWLIBRFSH parameter to Y, Informatica recommends that you specify LIBASUSER=Y. This setting enables you to identify the libraries that contain the table instances for which changes are extracted.
  • M
    . Writes the library name, file name, and data member name of the file where the change was made.
  • P
    . Writes the name of the program that made the change.
Default is N.
NOCCUOWSZ=
uow_size
Optional. Size, in number of records, of the UOW that PowerExchange creates when reading change records from journal receivers that were created without commitment control.
Enter a number from 1 through 50000.
If you do not specify commitment control when making changes to Db2 for i tables, PowerExchange creates a UOW for each change record. Use this parameter to increase the size of the UOWs that PowerExchange creates. PowerExchange and PowerCenter process larger UOWs more efficiently. Larger UOWs also reduce commit activity on the targets of the change data.
Because this parameter affects all users of this CAPI_CONNECTION statement, Informatica recommends that you use the
Minimum Rows Per commit
attribute on the PWX DB2i5OS CDC Real Time application connection to specify UOW size values for CDC sessions.
PowerExchange ignores this parameter for change records created with commitment control.
POLWAIT={
seconds
|
10
}
Optional. The number of seconds that PowerExchange waits after reaching the end of a journal receiver before checking for change data in that journal receiver.
Enter a number from 0 through 10. Default is 10.
RTNBUFFSIZE={
kilobytes
|
960
}
Adjust the buffer size, in kilobytes, that is used to collect journal entries for CDC as a result of PowerExchange calls to the IBM QjoRetrieveJournalEntries API. You can use this parameter to tune performance based on your environment.
Valid values are 128 through 12288. Default is 960 KB, which is the IBM default size.
If you also specify the RTNBUFFSIZE in the
PWX Overrides
field of the PowerCenter PWX DB2i5OS CDC application connection, the override takes precedence.
STOPIT=({
CONT
|TERM},
number_exceptions
|
5
)
Optional. Controls whether an extraction session stops after it encounters exceptions when processing change data from the journal. An exception can be one of the following:
  • An after image change without a before image change
  • An attempt to delete a record that has no data
For the first positional parameter, enter one of the following options:
  • CONT
    . The extraction session continues to run after the specified number of exceptions.
  • TERM
    . The extraction session ends after the specified number of exceptions.
Default is CONT.
For the second positional parameter,
number_exceptions
, enter the number of exceptions after which PowerExchange takes the action defined in the first positional parameter of the statement. The value 0 indicates that the extraction continues to run but does not report any exceptions. Valid values are 0 through 255. Default is 5.
UOWRSTANY={
Y
|N}
This parameter is deprecated. Y is the default value. When PowerExchange extraction sessions cold start, they skip any partial UOW for which the start-UOW point is on a previous journal receiver. The sessions begin reading change data with the next UOW in the current journal receiver.
If you omit this parameter, the default behavior is the same as if Y were specified. If you enter N, PowerExchange ignores the N value and uses Y.