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

CAPI_CONNECTION - UDB Statement

The UDB CAPI_CONNECTION statement specifies a named set of parameters that the Consumer API (CAPI) uses to connect to the change stream and to control CDC processing for DB2 for Linux, UNIX, and Windows sources.
Add this statement to the dbmover.cfg file on the system where DB2 capture registrations reside. This location corresponds to the
Location
node that you specify when defining a registration group. Usually, this location is where the source database resides.
Linux, UNIX, and Windows
DB2 for Linux, UNIX, and Windows
N/A
Yes for CDC
CAPI_CONNECTION=([DLLTRACE=
trace_id
]   ,NAME=
capi_connection_name
  [,TRACE=
trace_name
]   ,TYPE=(UDB [,AGEOUTPERIOD=
minutes
]     [,CCATALOG={
capture_catalog
|
creator
.DTLCCATALOG
}]    [,DBCONN=
database_name
]     [,EPWD=
encryted_password
]     [,LARGEOPS=
number_of_operations
]     [,MEMCACHE={
cache_size
|
1024
}]     [,MONITORINT={
minutes
|
5
}]     [,PASSWORD=
password
]    [,RSTRADV=
seconds
]     [,SPACEPRI={
AUTO
|MAX|NONE|
nn
}]     [,THREADING={
AUTO
|MAX|NONE|
nn
}]    [,UDBSCHEMA=
schema
]     [,UPDINT={
seconds
|
600
}]    [,UPDREC={
records
|
1000
}]     [,USERID=
user_id
]     ) )
AGEOUTPERIOD=
minutes
The age, in minutes, at which an outstanding UOW that has no change records of CDC interest will be removed from the calculation of the CDC restart point. The age is calculated as the difference between the start time of the outstanding UOW and the current time.
Use this parameter to prevent CDC failures that can occur if you shut down and then restart capture processing while the transaction is outstanding. After the restart, the DB2 transaction log in which the outstanding UOW started might not be available, causing the PowerExchange DB2 read process to fail.
Valid values are 60 to 43200. No default value is provided.
DLLTRACE=
trace_id
Optional. A user-defined name for 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. A unique user-defined name for this CAPI_CONNECTION statement.
Maximum length is eight alphanumeric characters.
TRACE=
trace_name
Optional. A user-defined name for the TRACE statement that activates the common CAPI tracing.
Specify this parameter only at the direction of Informatica Global Customer Support.
TYPE=(UDB, ... )
Required. The type of CAPI_CONNECTION statement. For DB2 for Linux, UNIX, and Windows sources, this value must be UDB.
CCATALOG={
capture_catalog
|
creator
.DTLCCATALOG
}
Optional. The name of the PowerExchange capture catalog table in the format
creator
.
table_name
.
Default is
creator
.DTLCCATALOG, where
creator
is the user ID that is used to connect to the database.
DBCONN=
database_name
Optional. The name of the override database that you want to connect to for data extraction instead of the database that is specified for the registration group. The override database must contain tables and columns that are identical to those in the original database. The registration tag names and extraction map names include the original database name.
EPWD=
encryted_password
Optional. An encrypted password that is used with the user ID in the USERID parameter for database access.
If you specify this parameter, you must also specify either the USERID parameter. However, do not also specify the PASSWORD parameter.
You can create encrypted passwords in the PowerExchange Navigator.
LARGEOPS=
number of operations
Optional. Overrides the default value that PowerExchange uses to identify transactions as large transactions for reporting purposes. Enter the number of DML operations (inserts, updates, and deletes), in thousands, that a transaction must contain to be considered a large transaction.
PowerExchange issues status messages for large transactions that meet this criteria. If PowerExchange issues too many messages, you can increase this value to limit the number of messages.
Valid values are 1 through 2147483 (1000 through 2,147,483,000 operations). The default value is one half of the MEMCACHE parameter value rounded up to the nearest thousand. Based on the default MEMCACHE value of 1024 KB, the default LARGEOPS value is 1000 (1,000,000 operations).
If a committed transaction spans multiple partitions in a DB2 database, PowerExchange reports the number of SQL operations and transaction size across all of the partitions.
MEMCACHE={
cache_size
|
1024
}
Optional. The maximum memory cache size, in kilobytes, that PowerExchange can allocate to reconstruct complete UOWs.
Enter a number from 0 through 2147483647. Default is 1024 KB. If you enter 0, the memory cache size is limited only by the available memory on the system. Informatica recommends that you enter 0.
For each extraction session, PowerExchange keeps all changes for each pending UOW in the memory cache until it processes the end-UOW record. PowerExchange incrementally allocates memory cache up to the limit that this parameter specifies. If the memory cache is too small to hold all of the changes in the pending UOWs, PowerExchange spills the changes in a UOW to sequential files, called UOW spill files, on disk.
Each UOW spill file contains change data from one UOW. A UOW might require multiple UOW spill files to hold all of the changes for that UOW. If the change stream contains multiple large UOWs and the memory cache is insufficient, PowerExchange might create numerous UOW spill files.
PowerExchange processes the change stream more efficiently if it does not need to use UOW spill files. A large number of UOW spill files can degrade extraction performance and cause disk space shortages.
If the change stream contains small UOWs, the default value might be sufficient. However, the default value is often too small to eliminate UOW spill files.
The location in which PowerExchange allocates the UOW spill files varies by operating system, as follows:
  • For Linux and UNIX, PowerExchange uses the current directory by default. To use a different directory, you must specify the TMPDIR environment variable.
    PowerExchange names the UOW spill files using the prefix "dtlq" and the operating system function tempnam.
    The UOW spill files are temporary files that are deleted when PowerExchange closes them. These files are not visible in the directory while they are open.
  • For Windows, PowerExchange uses the current directory by default for UOW spill files. To use a different directory, specify the TMP environment variable.
    PowerExchange names the UOW spill file names using the prefix "dtlq" and the Windows _tempnam function.
PowerExchange allocates the cache size for each extraction operation. If you use a large MEMCACHE value and run many concurrent extraction sessions, memory constraints can occur.
MONITORINT=
minutes
Optional. The time interval, in minutes, at which PowerExchange checks transaction activity for long outstanding transactions and large transactions. A long outstanding transaction is one that remains active for two monitoring intervals, and a large transaction is one that meets the LARGEOPS criteria. When this interval elapses, PowerExchange issues messages that identify the large transactions and long outstanding transactions and report their processing activity. PowerExchange also issues messages that identify the current position in the change stream. Valid values are 0 through 720. A value of 0 disables monitoring. Default is 5.
PASSWORD=
password
Optional. A clear text password that is used with the user ID in the USERID parameter for database access.
If you specify this parameter, you must also specify either the USERID parameter. However, do not also specify the EPWD parameter.
RSTRADV=
seconds
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.
Enter a number from 0 through 86400. No default is provided.
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.
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. 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.
SPACEPRI={
primary_space
|
2147483647
}
Optional. The amount of disk space, in bytes, that PowerExchange uses to allocate UOW spill files as temporary files.
Enter a number from 1 through 2147483647. Default is 2147483647 bytes.
THREADING={
AUTO
|MAX|NONE|
nn
}
Optional. Controls the number of threads that the UDB CAPI uses to capture change data from a DB2 database. Use this parameter to improve the performance of capture processing. If you have a partitioned database, you can use a maximum of one thread for each database partition node plus two additional threads for CAPI and merge processing.
Valid values are:
  • AUTO
    . Use up to nine threads.
  • MAX
    . Use one thread for each database partition plus two additional threads for CAPI and merge processing. The maximum number of threads is 99.
  • NONE
    . Do not use multiple threads for capture processing.
  • nn
    . A user-specified number of threads. Valid values are 1 to 99. For a partitioned database, if you enter a value that exceeds the sum of (
    number_of_database_partitions
    + 2), the CAPI does not use the excess threads.
Default is AUTO.
UDBSCHEMA=
schema
Optional. A schema name that overrides the schema name in capture registrations.
UPDINT={
seconds
|
600
}
Optional. The minimum number of seconds that PowerExchange must wait after encountering a virtual time stamp (VTS) in the DB2 log records for a partition before writing a positioning entry to the PowerExchange capture catalog table. The positioning entry, which contains a log sequence number (LSN) and VTS, indicates the location in the DB2 logs.
Enter a number from 1 through 2147483647. Default is 600 seconds.
The minimum number of records that is specified in the UPDREC parameter must also be met before PowerExchange can write positioning entries to the capture catalog table.
UPDREC={
records
|
1000
}
Optional. The minimum number of DB2 log records that PowerExchange must read for a partition before writing a positioning entry to the PowerExchange capture catalog table. The positioning entry contains an LSN and VTS and indicates a location in the DB2 logs.
Enter a number from 1 through 2147483647. Default is 1000 records.
The minimum wait period that is specified in the UPDINT parameter must also be met before PowerExchange can write positioning entries to the capture catalog table.
USERID=
user_id
Optional. A database user ID that has SYSADM or DBADM authority.
If you specify this parameter, you must also specify the PASSWORD or EPWD parameter.