CAPI_CONNECTION=([DLLTRACE=
trace_id]
                 ,NAME=
capi_connection_name
                [,TRACE=
trace_name]
                 ,TYPE=(MSQL
                      ,DISTDB=
distribution_database
                      ,DISTSRV=
distribution_server
                      [,BATCHSIZE=
number]
                      [,DWFLAGS={
flag1flag2flag3flag4|
NNNN}]
                      [,ENABLELWM={
N|Y}]
                      [,EOF={
N|Y}]
                      [,GUIDBRACES={
Y|N}]
                      [,MEMCACHE={
cache_size|
256}]
                      [,MULTIPUB={N|
Y}]
                      [,POLWAIT={
seconds|
1}]
                      [,RECONNTRIES={
number|
12}]
                      [,RECONNWAIT={
seconds|
5}]
                      [,RSTRADV=
seconds]
                      [,SQLNOLOCK={N|Y}]
                      [,UIDFMT={DBNAME|
NONE}]
                  )
) 

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=(MSQL, ... )

Required. Type of CAPI_CONNECTION statement. For Microsoft SQL Server sources, this value must be MSQL.

DISTDB=

distribution_database_name

Required. The name of the distribution database.

DISTSRV=

distribution_database_server

Required. The network name of the server that hosts the distribution database. This name is different from the network name of the SQL Server publication instance if the distribution database resides on a different instance.

If the database server uses a port number other than the default port number of 1433, append the non-default port number to the server name by using the following format:

\

server_name

,

port_number

\

. Otherwise, capture processing fails.

BATCHSIZE=

number

Optional. The number of rows from which PowerExchange captures change data before closing the cursor and then reopening it. This parameter allows resources to be released periodically to reduce the capture processing load on system memory and to reduce temporary tables in the tempdb database. Valid values are 0 through 2147483647. No default is provided.

Specify this parameter only at the direction of Informatica Global Customer Support. It can degrade CDC performance because PowerExchange issues the data read query more often.

DWFLAGS={

flag1flag2flag3flag4

|

NNNN

}

Optional. Series of four positional parameters that control whether processing stops or continues when data loss, truncation, schema changes, or unrecognized transaction log records occur.

Specify this statement only at the direction of Informatica Global Customer Support.

Enter the following positional parameters:

flag1
. Controls whether PowerExchange stops a change data extraction when PowerExchange retrieves data of an unexpected length from the distribution database. Enter Y to continue processing or enter N to stop processing.
flag2
. Controls whether PowerExchange stops a change data extraction when it detects a schema change. Enter Y to continue processing or enter N to stop processing.
flag3
. Controls whether PowerExchange stops a change data extraction when PowerExchange does not find the requested start sequence in the transaction log. Enter Y to continue processing or enter N to stop processing.
flag4
. Controls whether PowerExchange stops a change data extraction when PowerExchange finds an unrecognized record in the transaction log. Enter Y to continue processing after error message PWX-15742 or enter N to stop processing.

Default is NNNN, which indicates none of the parameters are set.

ENABLELWM={

N

|Y}

Optional. When you use the PowerExchange Logger for Linux, UNIX, and Windows, controls whether the PowerExchange consumer API (CAPI) connection process deletes data read from the SQL Server distribution database after the data has been hardened to PowerExchange Logger log files or after the PowerExchange publication expiry time has elapsed. You can use this parameter to improve distribution database performance and to prevent the distribution database from growing too large in size when the PowerExchange Logger is in use.

Enter one of the following options:

N
. The distribution database cleanup job deletes data from the distribution database after the expiry time for the PowerExchange publications elapses. This option might degrade the performance of the distribution-database cleanup job and cause excessive growth of the distribution database.
Y
. The CAPI connection process deletes processed data from the distribution database 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 to the CAPI connection 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 in the distribution database, the CAPI deletes all of the processed data for the PowerExchange publications up to and including the low water mark data from the distribution.dbo.MSrepl_commands table in the distribution database.
The user ID under which the PowerExchange Logger runs must have delete authority on the MSrepl_commands table.
This option can help improve distribution-database performance and control distribution-database size. However, if the SQL Server Log Reader Agent is writing very large UOWs to the distribution database when the CAPI connection processes the low water mark data, the performance of the distribution database might be temporarily degraded because the CAPI connection process must wait for a lock on the MSrepl_commands table.
If you run multiple extractions against a single distribution database for different publication databases and use ENABLELWM=Y for one CAPI connection and ENABLELWM=N with a RSTRADV value for another CAPI connection, PowerExchange might issue error message PWX-15756 for the connection with ENABLELWM=N. The message incorrectly reports that change data has been lost. To suppress this error, add the DWFLAGS=NNYN parameter to MSQL CAPI_CONNECTION statement.

Default is N.

EOF={

N

|Y}

Optional. Controls whether PowerExchange stops change data extractions when the end-of-log (EOL) is reached.

Enter one of the following options:

N
. PowerExchange does not stop change data extractions when the EOL is reached.
Y
. PowerExchange stops change data extractions when the EOL is reached.

Default is N.

Because this parameter affects all users of the MSQL CAPI_CONNECTION statement, Informatica recommends that you use one of the following alternative methods to stop change data extractions at the EOL:

For CDC sessions that use real-time extraction mode, enter 0 for the
Idle Time
attribute of the PWX MSSQL CDC Real Time application connection.
For the PowerExchange Logger for Linux, UNIX, and Windows, enter 1 for the COLL_END_LOG statement in the pwxccl.cfg configuration file.
For CDC sessions that use ODBC connections, enter 0 for the WAITTIME parameter in the ODBC data source.

GUIDBRACES={

Y

|N}

Optional. Controls whether PowerExchange retains or removes braces {} around GUID values in data captured from registered SQL Server columns that have the uniqueidentifier datatype. If the PowerCenter session will write the data to SQL Server target uniqueidentifier columns, set this parameter to N to remove the braces. Otherwise, the session will encounter writer errors.

N
. Remove braces.
Y
. Retain braces.

Default is Y.

MEMCACHE={

cache_size

|

256

}

The maximum size, in kilobytes, of the memory cache that stores change data for a single SQL operation that is captured from the SQL Server distribution database. The memory cache stores the full row image, which can include both the before image and after image and any LOB data.

Valid values are 0 through 2147483647. Default is 256. If you enter 0, the default value is used.

MULTIPUB={N|

Y

}

Optional. Indicates whether you capture change data from the distribution database for articles in a single publication database or in multiple publication databases. This option can affect the performance of CDC processing in real time extraction mode and in continuous extraction mode with the PowerExchange Logger for Linux, UNIX, and Windows.

Enter one of the following options:

N

. Specify this option if you capture change data for articles in a single publication database. Informatica recommends this option in this scenario because it causes PowerExchange to extract changes much more efficiently. It can also help reduce resource usage.

Y

. Use this option to extract change data for articles in multiple publication databases in a single CDC session or in a single PowerExchange Logger for Linux, UNIX, and Windows pass. If you do not use this option in this scenario, extraction processing fails with message PWX-15757.

This option might cause change records to be written to the distribution database more slowly. To improve performance, add the following index to the distribution database:

USE [distribution]
GO
/****** Object: Index [IX_MSrepl_transactions] Script Date: 03/31/2012 11:56:07 ******/
CREATE NONCLUSTERED INDEX [IX_MSrepl_transactions] ON [dbo].[MSrepl_transactions] 
(
[entry_time] ASC,
[publisher_database_id] ASC,
[xact_seqno] ASC,
[xact_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Default is Y.

You can change the MULTIPUB setting after capturing changes. To maintain data integrity, follow the procedure for changing the MULTIPUB parameter setting in the

PowerExchange CDC Guide for Linux, UNIX, and Windows

. If you use the PowerExchange Logger for Linux, UNIX, and Windows and change the setting from Y to N, you must cold start the PowerExchange Logger.

POLWAIT={

seconds

|

1

}

Optional. The maximum number of seconds that PowerExchange waits after reaching the end of log before polling the source database for more change data.

For Microsoft SQL Server sources, the polling frequency also depends on the PowerExchange Logger NO_DATA_WAIT2 parameter, or if you do not use the PowerExchange Logger, the polling frequency depends on the PWX Latency attribute on the PWX CDC application connection. If the NO_DATA_WAIT2 or PWX Latency value is less than the POLWAIT value, the lesser value takes precedence. In this case, PowerExchange polls the source more frequently than expected based on the POLWAIT parameter only.

Valid values are 1 through 2147483647. Default is 1.

RECONNTRIES={

number

|

12

}

The maximum number of times that PowerExchange tries to reconnect to the Microsoft SQL Server database after the connection is dropped. Use this parameter in conjunction with the RECONNWAIT parameter if you get the following ODBC connection error and want to improve connection resiliency:

PWX-15790 ODBC driver for Microsoft SQL Server returned error [08S01][Informatica][ODBC SQL Server Wire Protocol driver]Unexpected Network Error. ErrNum = 10054.

Valid values are 0 or any positive number. A value of 0 results in no connection retries. Default is 12.

RECONNWAIT={

seconds

|

5

}

The number of seconds that PowerExchange waits before any attempt to reconnect to a Microsoft SQL Server database after the connection has been dropped. Use this parameter in conjunction with the RECONNTRIES parameter if you get the PWX-15790 message for an ODBC driver error and want to improve connection resiliency.

Valid values are 0 through 3600. A value of 0 results in no waiting between connection retries. Default is 5.

RSTRADV=

seconds

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 completes 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.

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.

A value of 0 can degrade performance. In addition to the UOWs that contain changes for registered sources of interest, PowerExchange returns an empty UOW for every UOW that does not contain changes for the registered sources of interest.

SQLNOLOCK={N|Y}

Optional. Controls whether PowerExchange SELECT statements use the NOLOCK hint when querying the SQL Server distribution database for change data capture. The NOLOCK hint can avoid lock contention with the SQL Server utilities but might cause PowerExchange to miss some change records.

Enter one of the following options:

N
. PowerExchange SELECT queries that retrieve data from the distribution database do
not
use the NOLOCK hint. If locks are held on some change records, PowerExchange queries cannot retrieve the data until the locks are released. With this setting, PowerExchange queries might take longer to complete. However, no changes are skipped and data integrity is preserved. Use this option only when the MULTIPUB parameter is set to Y.
Y
. PowerExchange SELECT queries that retrieve data from the distribution database use the NOLOCK hint. Use this option only when the MULTIPUB parameter is set to N. If the MULTIPUB parameter is set to Y, SQL Server might use allocation order scans to retrieve data for PowerExchange queries, which can result in missed change data and data corruption.
Instead of using SQLNOLOCK=Y, Informatica recommends that you set the isolation level for the distribution database to READ_COMMITTED_SNAPSHOT ON to avoid data integrity problems.

Default is

N

if MULTIPUB is set to Y, or

Y

if MULTIPUB is set to N.

UIDFMT={DBNAME |

NONE

}

Optional. Controls the type of value that PowerExchange uses to populate the generated DTL__CAPXUSER column in each change record. Options are:

DBNAME
. Returns the Microsoft SQL Server publication database name.
NONE
. Returns a null because a user ID is not available.

Default is NONE.