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 - PG Statement

CAPI_CONNECTION - PG Statement

The PG 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 PostgreSQL sources.
Windows
PostgreSQL
Yes for PostgreSQL CDC
CAPI_CONNECTION=(NAME=
capi_connection_name
[,DLLTRACE=
trace_id
]   ,TYPE=(PG ,SERVER={
database_server
|
localhost
}[,
port
] [,CAPTUREALL={Y|
N
}] [,DATABASE=
database_name
[,ENABLELWM={Y|
N
}] [,FETCHLIMIT=
maximum_rows_fetched
|
100
]    [,ONDATATRUNC={WARN|
FAIL
}]    [,ONTABLETRUNC={WARN|
FAIL
}] [,RECONNTRIES={
reconnection_attempts
|
12
}] [,RECONNWAIT={
seconds
|
5
}] [,REPLSTORETBL=
schema.table_name
|
public.pwx_repl
}      [,RSTRADV=
seconds
]      ) )
NAME=
capi_connection_name
Required. A unique user-defined name for this CAPI_CONNECTION statement.
Maximum length is eight alphanumeric characters.
CAPTUREALL={
N
|Y}
This optional parameter controls whether PowerExchange captures and stores change data for all tables in the source database or only for the tables registered for CDC. Use this parameter to limit the amount of data that is stored in the replication store table to only the data from registered source tables.
Options are:
  • N
    . Capture DML changes and store the changes in the replication store table, only for registered source tables. This option limits the amount of data that is collected and stored in the store table.
  • Y
    . Capture DML changes and store the changes in the replication store table, for all tables in the source database, including unregistered tables. This setting is not recommended because it can cause more data than is needed for CDC processing to be stored in the replication store table.
Default is N.
DLLTRACE=
trace_ID
Optional. The 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.
TYPE=(PG, ... )
Required. The type of CAPI_CONNECTION statement. For PostgreSQL sources, this value must be PG.
SERVER={
host_name
|localhost}[,
port_number
]
Required. The host name or IP address of the server where the PostgreSQL source database runs. You can enter "localhost" if the database server and PowerExchange Listener run locally on the same machine.
If you want the source server to listen on a port number other than the default port, you can optionally append the port number to the server name.
DATABASE=
database_name
Required. The name of the PostgreSQL source database on the specified server.
ENABLELWM={
N
|Y|
Optional. When you use the PowerExchange Logger for Linux, UNIX, and Windows, controls whether the PowerExchange consumer API (CAPI) capture process deletes data read from the replication store table after the data has been hardened to PowerExchange Logger log files. You can use this parameter to improve capture performance and to prevent the replication store table from growing too large when the PowerExchange Logger is in use.
Options are:
  • N
    . Do not delete processed data from the replication store table after the data has been hardened to the PowerExchange Logger log files. With this option, the replication store table might become very large, causing CDC performance to be degraded.
  • Y
    . Delete processed data from the replication store table after the data has been hardened to the PowerExchange Logger log files. After a log file switch, the PowerExchange Logger sends a low water marker (LWM) to the CAPI process to identify the last end UOW prior to the file switch. At the end of the next capture cycle, after the CAPI connection process has read to the end of the available data, the CAPI deletes all of the rows in the replication store table up to the LWM.
Default is N.
FETCHLIMIT
Optional. The maximum number of rows that can be fetched from the replication store table in response to a PowerExchange SELECT call. Valid values are 0 through 1000000. A value of 0 means no maximum limit is in effect.
Default is 100.
ONDATATRUNC={WARN|
FAIL
}
Optional. Indicates whether PowerExchange issues a warning message and continues processing or ends abnormally when it needs to truncate data from PostgreSQL columns that are longer than 98,304 bytes.
Default is FAIL.
ONTABLETRUNC={WARN|
FAIL
}
Optional. Indicates whether PowerExchange issues a warning message and continues processing or ends abnormally when it encounters a TRUNCATE TABLE record in the change stream.
Default is FAIL.
RECONNTRIES={
reconnection_attempts
|
12
}
Optional. The maximum number of times that PowerExchange tries to reconnect to the PostgreSQL database server after a connection is dropped while PowerExchange is retrieving data from the replication slot or reading data from the replication store table. If a connection retry is unsuccessful, PowerExchange waits for number of seconds specified in the RECONNWAIT parameter before trying to reconnect to the database server again. Use this parameter in conjunction with the RECONNWAIT parameter to improve connection resiliency.
Valid values are 0 through 2147483647. A value of 0 results in no connection retries. Default is 12.
RECONNWAIT={
seconds
|
5
}
Optional. The number of seconds that PowerExchange waits between attempts to reconnect to a PostgreSQL database server. Use this parameter in conjunction with the RECONNTRIES parameter to improve connection resiliency.
Valid values are 0 through 3600. A value of 0 results in no waiting between connection attempts. Default is 5.
REPLSTORETBL={
schema.table_name
|
public.pwx_repl
}
Optional. The name of the PowerExchange replication store table that contains the change data from the PostgreSQL logical replication slot. PowerExchange reads change data from this table.
If this value is not specified, the default name of public.pwx_repl is used.
RSTRADV=
seconds
Optional. The time interval, in seconds, that PowerExchange waits before advancing restart and sequence tokens for a registered data source during periods when UOWs do not include any changes of interest for the data source. When the wait interval expires, PowerExchange returns the next committed "empty UOW," which includes only updated restart information.
PowerExchange resets the wait interval to 0 when one of the following events occur:
  • PowerExchange finishes processing a UOW that includes changes of interest.
  • PowerExchange returns an empty UOW because the wait interval expired without PowerExchange receiving any changes of interest.
Valid values 0 through 86400. No default is provided. A value of 0 disables restart advance processing.
For example, if you specify 5, PowerExchange waits 5 seconds after it completes processing the last UOW or after the previous wait interval expires. Then PowerExchange returns the next committed empty UOW that includes the updated restart information and resets the wait interval to 0.
If you do not specify RSTRADV, PowerExchange does not advance restart and sequence tokens for a registered source during periods when PowerExchange receives no changes of interest. In this case, when PowerExchange warm starts, it reads all changes, including those not of interest for CDC, from the restart point.