If you use the LOAD from CURSOR utility to load target
tables from a DB2® Version 9.7
or newer source and you manually create the nickname that is used
for loading, you need to ensure that the nickname uses the correct
federated server option for concurrent access.
About this task
Starting
with Version 9.7, a new federated server option, CONCURRENT_ACCESS_RESOLUTION=W,
is used to ensure that LOAD from CURSOR waits until all in-progress
transactions that modify the source table are completed before beginning
the load. This behavior is known as "wait for outcome." The change
was required to account for the default currently committed access
behavior in DB2 for Linux, UNIX, and Windows Version
9.7 and newer.
Note the following considerations:
- If you let the Replication Center or ASNCLP create the nickname
that is used for LOAD from CURSOR, the server option CONCURRENT_ACCESS_RESOLUTION=W
is added to the server for that nickname. In some situations you might
need to manually create the nickname, or the nickname that is used
for loading might be shared by other applications. In these situations,
you must set CONCURRENT_ACCESS_RESOLUTION=W manually for the nickname.
- The procedures for setting concurrent access are different if
the Q Apply server is at Version 9.7 or newer, or pre-Version 9.7.
There is currently no solution to enforce wait
for outcome behavior when Q Apply on z/OS® uses
LOAD from CURSOR on a DB2 V9.7
source database on Linux, UNIX, or Windows to perform the load. In this case,
the best solution is to suspend any applications that update the source
table from the time the Q subscription is started until the load phase
begins (identified by Q subscription state change to L or A in the
IBMQREP_SUBS table).
Note: You can only set this option for a registered server of
type DB2/UDB Version 9.7 or newer.
Procedure
To ensure that nicknames that are used for load have correct
concurrent access setting, use one of the following procedures depending
on whether the Q Apply program is at Version 9.7 or newer, or older
than Version 9.7:
| Version of Q Apply program |
Procedure |
|---|
| Version 9.7 or newer |
Issue the following command at the Q Apply server:db2 alter server server_name
OPTIONS(ADD CONCURRENT_ACCESS_RESOLUTION 'W');
|
| Pre-Version 9.7 |
Note: If you are unable to follow this procedure, suspend
any applications that update the source table during the beginning
of the load.
- From the Q Apply server, connect to the source database.
- Bind the SQL packages that are used for Call Level Interface
(CLI) connections with a generic bind option in a specific package
by using the following command:
db2 bind @db2cli.lst generic
"CONCURRENTACCESSRESOLUTION WAIT_FOR_OUTCOME"
COLLECTION ASN
- Add the following name-value pair to the db2cli.ini file
at the federated database, below the stanza that declares the options
for the server definition to which the nickname belongs:
[data_source_name]
CURRENTPACKAGESET=ASN
Where data_source_name is
the source database that the db2cli.bnd packages
were bound against.
|
Recommendation: If you use
a federated server for both replication and other purposes, create
a new dedicated server for use by replication that has the CONCURRENT_ACCESS_RESOLUTION=W
option set, and allow other applications to use the existing server
name.
Q Replication and Event Publishing
