The following table describes the optional

Maximum Rows Per commit

connection attribute:

You can use the

Maximum Rows Per commit

attribute to commit change records between UOW boundaries. Use this attribute when you have extremely large UOWs in the change stream that might cause locking issues on the target database or high resource consumption on the PowerCenter Integration Service machine. PWXPC uses the

Maximum Rows Per commit

value to commit data to the targets before the end of a UOW is received, a process called a

subpacket commit

.

The

Maximum Rows Per commit

value is the number of change records in a source UOW that PWXPC processes before issuing a real-time flush to commit the change data to the target. When PWXPC flushes change data to the target, it writes the following message to the session log:

For example, if each UOW contains 1,000 change records and you specify 100 for

Maximum Rows Per commit

, PWXPC issues a real-time flush to commit the change records after each 100 records, which results in 10 commits for each UOW.

As part of the commit processing, any locks in the target databases for these changes are released.

PWXPC resets the

Maximum Rows Per commit

counter when either the specified number of records is met or the end of the UOW is reached.

Commit processing is not controlled solely by the

Maximum Rows Per commit

attribute. The

UOW Count

and

Real-Time Flush Latency

attributes also determine the target commit frequency. The

Maximum Rows Per commit

attribute differs from the

UOW Count

attribute in that it specifies a count of records within a UOW whereas the

UOW Count

specifies a count of complete UOWs.

By using a subpacket commit for large UOWs, you can minimize lock contention on the target and reduce storage use on the PowerCenter Integration Service machine. However, if you specify a low

Maximum Rows Per commit

value, the session might consume more system resources on the target machine because PWXPC sends commit requests to the target more frequently. Balance performance and resource consumption with latency requirements when setting the

Maximum Rows Per commit

,

UOW Count

, and

Real-Time Flush Latency

values.

When a session processes changes for multiple sources, the

Maximum Rows Per commit

count is cumulative across all sources in the group. PWXPC sends a commit request to PowerCenter when the maximum count of change records is reached, regardless of the number of sources to which the changes apply. For example, assume that a UOW contains 900 change records for one source followed by 100 change records for a second source and then another 500 change records for the first source. If you set the

Maximum Rows Per commit

attribute to 1000, PWXPC issues the commit after the thousandth change record, that is, after the 100 changes for the second source.