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

General PowerExchange ODBC Driver Parameters

General PowerExchange ODBC Driver Parameters

Enter general PowerExchange ODBC driver parameters in the odbc.ini file on Linux or UNIX.
The following table describes these general parameters:
Parameter
Default
Tokens
Usage
CAPXIMAGETYPE
None
BA, AI, or TU
The capture image type for accessing PowerExchange CDC or live data. The options are:
  • BA. Captures the before image data, before the latest change, and the after image data, after the latest change. The before image data is shipped with an action character forcing the deletion of the record. The after image data, which includes the change data, is in the format of an insert operation.
  • AI. Captures the after image data only.
  • TU. Captures the before and after image data, as it occurs. The before image data enables the application to ensure that no changes occurred in the record since the request. The after image data appears as an update operation. The update is a full image, but the application that captures this change can change the target record rather than delete it and insert a new record, as occurs with a BA.
CAPXTYPE
None
SL or RS
The type of extraction. The options are:
  • SL. Start the extraction from the point where the last extraction ended, either by restart tokens for z/OS, or by checkpoint timestamps for i5/OS.
  • RS. Restart the extraction from the start point of the last extraction. This option enables you to run the same extraction multiple times.
COMPATIBILITY
None
-
The integration mask. Includes the following options:
  • CPOOL. Initializes connection pooling.
  • DFN. If you specify DFN, the PowerExchange data source type is returned as follows:
    PWX:
    dbtype
    If you do not specify DFN, the following string is returned:
    PWX
    Informatica recommends that you use the default value.
  • ILMBRIDGE. Specify this option if the Informatica Data Archive product accesses data by means of a JDBC-ODBC bridge connection to the PowerExchange ODBC driver. The ILMBRIDGE option enables the following options: BINASCHAR, CLOSEDSTMREUSE, NOFKEYS, NOUNDERSCORE, SQLLEN4BYTES, and V3COLNAMES.
  • OWB. Defines various SQL validations and statement reuse properties. Used by Oracle Warehouse Builder.
  • V30. Causes the ODBC SQLColumns() call to return V3 column names. Used by the IBM DB2 Information Integrator (II).
  • VERBOSE. Causes extra messages to be generated regarding Compatibility options.
In addition, you can enable the following COMPATIBILITY options by specifying either the specific option or the ILMBRIDGE option:
  • BINASCHAR. Causes the ODBC SQLColAttributes() call to change BIN or VARBIN columns to CHAR or VARCHAR columns and double the lengths.
  • CLOSEDSTMREUSE. Causes a statement opened in read or select mode to be retained after an ODBC SQLFreeStmt() call of type SQL_CLOSE. Subset of StmtKeepAlive.
  • NOFKEYS. Causes the SQLForeignKeys() ODBC call to return an empty result set without a call to the PowerExchange Listener.
  • NOUNDERSCORE. Prevents underscore (_) characters from being replaced with the percent sign (%) in DTLDescribe processing. This option allows PowerExchange to process a single NRDB2 table.
  • SQLLEN4BYTES. Specifies that the JDBC-ODBC bridge calls SQLGetData with a 4-byte integer buffer to return the length of the target data.
  • V3COLNAMES. Causes ODBC V3 names to be returned for all metadata result sets, such as SQLTables(), SQLColumns(), and SQLPrimaryKeys(). Superset of the V30 flag.
COMPRESS
N
Y, N
Set this parameter to compress data.
Default is N.
CONFIRMWRITE
Y
Y, N, or T
For DB2, DB2UDB, DB2400C, MSSQL, NRDB, NRDB2, ODBC, or ORACLE data source types only.
The write mode.
The options are:
  • Y. Enables confirm write. Ensures that data sent to the PowerExchange ODBC driver is immediately sent to, rather than buffered by, the PowerExchange Listener. Additionally, a success or no success response is sent, which improves error recovery. However, sending a response can slow data transfer rates.
  • T. Enables asynchronous write with fault tolerance. For more information about asynchronous write, see the
    PowerExchange Bulk Data Movement Guide
    .
  • N. For DB2, DB2 UDB, and Oracle data sources only. Disables confirm write. Before using this setting, contact Informatica Global Customer Support.
Default is Y.
DATABASE
-
-
For DB2 for Linux, UNIX, and Windows only. Overrides the database connection value specified in the PowerExchange extraction map.
DB2DEGREE
None
Any string of text up to 30 characters
For DB2 for i5/OS and DB2 for z/OS only. Sends the following SQL command to DB2, specifying whether DB2 should use, or not use, inter-partition parallel processing:
SET CURRENT DEGREE
For more information about the effect of each value in the SQL command, see the relevant IBM DB2 documentation for the "SET CURRENT DEGREE" and "CHGQRYA DEGREE" commands.
For z/OS only:
Any text following the equals sign is taken as text to follow a
'SET CURRENT DEGREE ='
.
For example, specifying
DB2DEGREE=ANY
runs the statement
"SET CURRENT DEGREE = 'ANY'"
, and would set DB2 to be able to use parallel processing.
For i5/OS only:
Any text following the equals sign is taken as text to follow a
'CHGQRYA DEGREE'
statement.
For example, specifying
DB2DEGREE=SYSTEM
runs the statement
"CHGQRYA DEGREE(*SYSTEM)"
.
To maintain compatibility with previous releases, if the parameter begins with a digit, it is appended to a
"CHGQRYA DEGREE(*NBRTASKS)"
statement. Thus a
"CHGQRYA DEGREE(*NBRTASKS 1)"
statement can be generated with either a
DB2DEGREE=1
or
DB2DEGREE="*NBRTASKS 1"
statement.
DBQUAL1
None
See subsequent table.
See subsequent table.
DBQUAL2
None
See subsequent table.
See subsequent table.
DESCRIPTION
None
Text
Descriptive text.
DRIVER
None
Path/File
The path and file name of the PowerExchange ODBC driver. The file name can have the suffix.so or .sl according to the version of UNIX being run. Review the suffixes on the existing PowerExchange shared libraries to choose the correct one.
DTLAPP
-
-
Application name. Specifying the application name that identifies extracts increases the ability to identify individual use of a single connection. This is then available to task control.
DTLCONN_OVR
None
Not applicable
The name in the CAP_CONNECTION statement in the DBMOVER configuration file that points to the relevant data source.
DTLIMGOV
N
Y or N
For DB2390IMG only.
If the image copy data set is specified, PowerExchange checks that the image copy file exists and that a consistent image copy is selected. If either of these is not true, such as when the image copy file does not exist or is inconsistent, the request is rejected and a message is displayed.
You can override this behavior by setting the DTLIMGOV parameter to Y. This setting enables inconsistent image copies to be read.
ENCRYPT
N
N, Y, AES
The encryption option. The options are:
  • N. No encryption is used when moving data.
  • Y. Data is encrypted by using a special Informatica developed algorithm if it is being accessed by way of a PowerExchange Listener.
  • AES. Data is encrypted by using Advanced Encryption Standard (DES) algorithm.
If you specify an encryption option, you must also specify an encryption level in the ENCRYPTLEVEL parameter.
The values of DES and RC2 are deprecated. PowerExchange converts a value of DES or RC2 to AES.
ENCRYPTLEVEL
-
1, 2 or 3
The encryption level. Required if the ENCRYPT parameter is set to DES or RC2.
The options are:
  • 1
    . Use a128-bit encryption key.
  • 2
    . Use a 192-bit encryption key.
  • 3
    . Use a 256-bit encryption key.
INTERPRETASROWS
N
Y, N
Indicates whether to interpret the pacing size in rows or kilobytes. The options are:
  • Y. Interpret the pacing size in rows.
  • N. Interpret the pacing size in kilobytes.
Default is Y.
ISOLATION
NONE
ALL, CHG, CS, NONE, or RR
For DB2 for i5/OS only. The commit scope of the transaction. This parameter is typically set to CS.
JRNL
-
LIBRARY/JOURNAL
For i5/OS only. The fully qualified library and journal name that is to be used instead of that specified in the PowerExchange capture registration.
For example:
STQA/NEWJOURNAL
LIBRARYLIST
Maximum of 34 libraries.
Each library name is a maximum length of ten characters.
A space-delimited list of libraries that PowerExchange searches to qualify the first table name, if the table name is unqualified, on a select, insert, delete, or update statement.
If you specify a library list in both the LIBRARYLIST and OVRDBF parameters, and a table is found in both lists, the table found in the OVRDBF parameter takes precedence.
LOCALCODEPAGE
None
-
A number that identifies the code page to be used. For example 41 represents UTF-8.
Specify NONE to use the code page value specified in the DBMOVER configuration file at run time.
You can use the icucheck.exe command-line utility to generate a lookup list of numbers with the corresponding code page names. You can pipe the results of the utility to a file as follows:
icucheck.exe > iculist.txt
LOCATION
None
None
The location of the data source.
The location is defined by a NODE statement in the dbmover.cfg configuration file on the local system.
MAXROWS
0
0 to 2147483647
The maximum number of rows for retrieval.
Default is 0, which indicates unlimited rows.
MQGETOPT
R
B or R
The type of read operation to be performed from an MQSeries queue.
The options are:
  • B. Browse. Reads a copy of the data on the queue.
  • R. Read. Reads the data and removes it from the queue.
Default is R.
NOUPDATECDEP
N
Y or N
Indicates whether database row test output is to be added to the PowerExchange CDC audit trail.
The options are:
  • Y. Omit the database row test output from the PowerExchange CDC audit trail for the application.
  • N. Include the database row test output in the PowerExchange CDC audit trail for the application.
ORACOLL
-
-
Overrides the value specified in the ORACOLL parameter in the ORAD CAPI_CONNECTION statement in the DBMOVER configuration file.
Enables the use of a single set of capture registrations to capture data from up to ten Oracle instances at once.
ORACONN
-
-
Overrides the Oracle connection string value in the third positional parameter in the ORACLEID statement in the dbmover.cfg configuration file. For example:
ORACLEID=(
coll_id
,
oracle_sid
,
connect_string
,
cap_connect_string
)
Used in conjunction with the ORAINST value to enable the use of a single set of capture registrations to capture data from multiple Oracle instances.
If you specify an ORACONN value without an ORAINST value, Oracle capture uses the Oracle connection string value specified in the ORACLEID statement in the dbmover.cfg file.
ORAINST
-
-
Overrides the Oracle instance value in the second positional parameter in the ORACLEID statement in the dbmover.cfg configuration file.
For example:
ORACLEID=(
coll_id
,
oracle_sid
,
connect_string
,
cap_connect_string
)
Used in conjunction with the ORACONN value to enable the use of a single set of capture registrations to capture data from multiple Oracle instances.
If you specify an ORAINST value, but no ORACONN value, Oracle capture uses the Oracle connection string value specified in the ORACLEID statement in the dbmover.cfg file.
ORASCHEMA
-
-
Overrides the Oracle schema value in a PowerExchange registration group to enable the use of a single set of capture registrations to capture data from multiple schemas that might exist in an Oracle instance.
OVRDBF
-
Maximum of eight files. Each file specification is a maximum length of 43 characters, as follows:
  • Ten characters for the
    filename
    value
  • Ten characters for the
    newlibrary
    value
  • Ten characters for the
    newfilename
    value
  • Ten characters for the
    newmember
    value, if specified
  • Three characters for the slash (/) separators
Space-delimited list of file overrides in the following format:
filename
/
newlibrary
/
newfilename
/
newmembername
Alternatively, you can use the following format:
filename
/
newlibrary
/
newfilename
/
In this format, the member defaults to *FIRST.
When you use this override, any occurrence of the specified file name in a SQL statement is overridden with the
newlibrary/newfilename/newmembername
combination, regardless of whether the file is qualified or not. This includes any files that are specified in the LIBRARYLIST override.
If you specify a library list in both the LIBRARYLIST and OVRDBF parameters, and a table is found in both lists, the table found in the OVRDBF parameter takes precedence.
PACESIZE
Value of the API rows_requested parameter.
Numeric
The number of rows or kilobytes. Set this parameter when an application, such as an interactive application, cannot keep pace with the flow of data from the PowerExchange Listener.
For maximum performance, set this parameter to 0.
PWXOVERRIDES
None
-
Overrides that apply to PowerExchange ODBC connections for bulk data movement and CDC sessions:
  • ARRAYSIZE. The DB2 fetch array size, in number of rows, for DB2 bulk data movement operations that use the DB2 access method. The array size pertains to the DB2 multiple-row FETCH statements that PowerExchange uses to read data from DB2 source tables. Valid values are from 1 through 5000. Default is 25.
    PowerExchange dynamically lowers the array size when all the following conditions are true:
    • The database type is DB2.
    • The table contains LOB columns.
    • The ARRAYSIZE value is greater than 1.
    • Row size * ARRAYSIZE is greater than 16000000 bytes.
  • LOWVALUES. If you previously set the LOWVALUES statement in the DBMOVER configuration file to Y for PowerExchange Client for PowerCenter (PWXPC) sessions, you can use this ODBC parameter to specify an override of LOWVALUES=N for sessions that use PowerExchange ODBC connections. You must use PWXPC instead of ODBC to preserve hexadecimal '0' values, called
    low values
    , in source character fields when passing these values to a PowerCenter session for delivery to a VSAM target on
    z/OS
    or a sequential file target on Linux, UNIX, Windows, or z/OS.
  • TCPIP_OP_TIMEOUT. Specifies a timeout, in seconds, for a network operation. When the client-side application thread detects that the network operation exceeds this timeout interval, PowerExchange ends the connection and issues a timeout error message.
  • TCPIP_CON_TIMEOUT. Specifies a timeout, in seconds, for a connection attempt. If PowerExchange cannot establish an ODBC connection within this time interval, PowerExchange issues an error message.
  • TCPIP_HB_INTERVAL. Specifies a heartbeat interval, in seconds, that overrides the default
    TCP/IP
    heartbeat interval of 507 seconds. If PowerExchange does not receive data before the TCP/IP heartbeat interval elapses, PowerExchange ends the ODBC connection and associated workflow with a heartbeat timeout error. Use this override to prevent dropped connections from TCP/IP heartbeat timeout errors.
  • USE_CATALOG_METADATA. Specify USE_CATALOG_METADATA=Y to read metadata from the catalog during a DB2 bulk load session, rather than issuing a SELECT statement. Then, when PowerExchange reads the metadata from the catalog, it is not necessary to grant PowerExchange SELECT privileges on the table.
Use the following syntax:
PWXOVERRIDES=ARRAYSIZE=
array_size
PWXOVERRIDES=LOWVALUES=N
PWXOVERRIDES=TCPIP_OP_TIMEOUT=
network_operation_timeout
PWXOVERRIDES=TCPIP_CON_TIMEOUT=
connection_timeout
PWXOVERRIDES=TCPIP_HB_INTERVAL=
nnnnn
PWXOVERRIDES=USE_CATALOG_METADATA=Y
If you specify multiple overrides, use a semicolon (;) as a separator, for example:
PWXOVERRIDES=TCPIP_OP_TIMEOUT=
nnn
;TCPIP_CON_TIMEOUT=
nnn
PWXOVERRIDES=LOWVALUES=N
REJECTFILE
None
Up to 384 characters
Available only if the CONFIRMWRITE parameter is set to T, which enables asynchronous with fault tolerance mode.
The location and file name of the reject file that is used during asynchronous write operations.
Enter a value of PWXDISABLE to disable reject file logging.
For more information, see the
PowerExchange Bulk Data Movement Guide
.
STOPONERRORS
0
0 to 2147483647
Available only if the CONFIRMWRITE parameter is set to T, which enables asynchronous with fault tolerance mode.
The number of non-fatal errors that are allowed in the write phase before processing is terminated.
For more information, see the
PowerExchange Bulk Data Movement Guide
.
UAP
None
-
For DB2 for i5/OS data sources only. Overrides the fully qualified library and journal name specified in the PowerExchange capture registration.
For example:
STQA/NEWJOURNAL
WAITTIME
-
0
2 to 86399
86400
The maximum approximate time, in seconds, to wait for data before returning end-of-file (EOF). The options are:
  • 0. The EOF is returned when the end of the current log is reached. The end of the log is determined at the start of the extraction process, because the actual end point constantly changes.
  • 2 to 86399. The number of seconds to wait for data before returning EOF.
  • 86400. The EOF is never returned. The job waits forever.
XTRASCHEMA
-
Up to 128 characters with no spaces allowed.
Overrides the schema defined in the extraction map.
The following table describes the DBQUAL1 parameter:
Tokens
DBType
i5/OS Library/Filename
CAPX and CAPX/RT
SSID or DBName
DB2, DB2400C, and DB2UDB
Not applicable
DB2390IMG
Not applicable
IMSUNLD
DSN
MSSQL
Not applicable
NRDB and NRDB2
SQL *Net Name
ORACLE
The following table describes the DBQUAL2 parameter:
Tokens
DBType
Application name
CAPX and CAPX/RT
Not applicable
DB2, DB2400C, and DB2UDB
SSID
DB2390IMG
Not applicable
IMSUNLD
Database
MSSQL
Not applicable
NRDB and NRDB2
Not applicable
ORACLE
Any supported data source, such as DB2
Access method for file or database.