The UDB Log Reader Capture Agent configuration (.cab) file is created and maintained by the sqdconf utility. While this section focuses primarily on the initial configuration of the Capture Agent, sequences of SQDCONF commands to create and configure the capture agent can/should be stored in shell scripts. For more information, see SQDCONF for a full explanation of each command, parameters, and the utility's operational considerations.
$ sqdconf create <cab_file_name>
--type=DB2
--database=<database_name>
[--user=<name>]
[--password=<value>]
[--passcmd=<filename>]
[--ccsid=<coded_character_set_identifier>]
[--plan=<sqdata_plan>]
[--exclude-plan=<value>]
[--auto-exclude-plan=y | n]
[--exclude-user=<user_id>]
[--encryption | --no-encryption]
[--auth-keys-list="<name>"]
--store=<store_cab_file_name>| Keyword | Description |
|---|---|
| <cab_file_name> | Path and name of the Capture/Publisher Configuration (.cab) file. The directory must exist and the user-id associated with the agent must have the right to create and delete files in that directory. Precisely recommends including the Capture Agent alias as the third node in the directory structure and first node of the file name, for example, /home/sqdata/udbcdc1/udbcdc1.cab. |
| --type=Db2 | Agent type, in the case of the Db2 Log Reader Capture, is Db2. |
| --database=<database_name> | The UDB database name. |
| [--user=<name>] | User name/id required to connect to the underlying database. This user must have the authority to perform log capture/log mining on the underlying database. Optional depending on required Db2 client configuration. |
| [--password=<value>] |
Plain text passwords are exposed in the cab file. DO NOT use plain text passwords. Precisely highly recommends using |
| [--passcmd='./getpassword.sh'] | A script that will return the user password. |
| [--plan=<sqdata_plan>] | Plan name used to connect to the UDB (Db2/LUW) Instance. The default Plan is named SQDV4000 and need not be explicitly specified. This is an optional parameter that can be used to specify another Plan as needed. |
| [--exclude-plan=<name>] | Exclude transactions associated with the given plan name from capture. This parameter can be repeated multiple time. |
| [--auto-exclude-plan=NO] | Optionally exclude from capture Data that has been updated by an Apply Engine running under Connect CDC SQData's default Db2 Plan SQDV4000. |
| [--exclude-user=<user_id>] | Rarely used, will exclude database updates made by the specified User. |
| [--encryption | --no-encryption] | Enables or disables NaCL encryption of the published CDC record payload. See Encryption of Published Data for more details. |
| [--auth-keys-list="<name>"] | Required for NaCL software encrypted CDC record payload. File name must be enclosed in quotes and must contain public key(s) of only the subscribing Engines requiring encryption of the CDC record payload. See --encryption option. |
| --store=<store_cab_file_name> | Path and name of the Storage Agent Configuration (.cab) file. > - Path and name of the Storage Agent Configuration (.cab) file. In our example, /home/sqdata/udbcdc1/udbcdc1_store.cab. |
Next, the configuration file must be updated by adding an entry for each table to be captured using the add command. Onlyonetable andoneassociated datastore (target subscription) can be added at a time. Precisely highly recommends keeping a Recover/Recreate configuration file Job or shell script available should circumstances require recovery.
Add each source table to the list of source tables to be captured in the Capture Configuration (.cab) file. Each table is identified by its name and schema. A datastore representing a single target subscription must be specified for each table added. Additional target subscriptions can be "added" but that is performed with the modify command It is important to develop a standard for how Datastores will be identified, particularly if a large number will be defined. The source is marked inactive by default but remember, even sources marked active will not be captured until changes to the configuration file are applied.
sqdconf add <cab_file_name>
--schema=<name> --table=<name> | --key=<name>
--datastore=<url>
[--active | --inactive]
[--pending]| Keyword | Description |
|---|---|
| <cab_file_name> | Must be specified and must match the name specified in the previous create command. |
| --schema=<name> | Schema name, owner, or qualifier of a table. This parameter must be accompanied by the --table parameter. |
| --table=<name> | The un-qualified table name. Must be used with --schema to fully identify a relational source as schema.name. |
| --key=<name> | A fully qualified table name in the form of schema.name that identifies the source. This may be used in place of two parameters, --schema and --table. Both cannot be specified. |
| --datastore=<url> | -d <url> | While most references to the term datastore describe physical entities, a datastore URL represents a target subscription and takes the form: cdc://[localhost]/[<agent_alias>]/<subscriber_name> where:
|
| [--active | --inactive] | Mark a table as active or in-active for capture. The table will remain in the current state until the capture is stopped, applied and re-started. The default is --inactive. |
| [--pending] | This parameter allows a table to be added to the configuration before it exists in the database catalog. |
Example
$ sqdconf create /home/sqdata/udbcdc/udbcdc.cab
--type=Db2 --database<database_name>
--store=udbcdc_store.cab $ sqdconf add /home/sqdata/udbcdc/udbcdc.cab
--schema=SQDATA --table=DEPT --datastore=cdc:////UDBTOUDB --active $ sqdconf add /home/sqdata/udbcdc/udbcdc.cab
--table=SQDATA.EMP --datastore==cdc:////UDBTOUDB --active $ sqdconfdisplay /home/sqdata/udbcdc/udbcdc.cab- The sqdconf create command defines the location of the Capture agent's configuration CAB file. Once created, this command should never be run again unless you want to destroy and recreate the Capture agent.
- Destroying the Capture agent cab file means that the current position in the log and the relative location of each engine's position in the Log will be lost. When the Capture agent is brought back up it will start from the beginning of the oldest active log and will resend everything. After initial configuration, changes in the form of add and modify commands should be used instead of the create command. Note that you cannot delete a cab file if the Capture is mounted and a create on an existing configuration file will fail.
- There must be a separate ADD command executed for every source table to be captured.
- The command will fail if the same table is added more than one time for the same Target Datastore/Engine. See section below "Adding/Removing Output Datastores".
- The <subscriber_name> is case sensitive in that all references should be either upper or lower case. Because references to the "Engine" in z/OS JCL must be upper case, references to the Engine in these examples are all in upper case for consistency.
- The display command when run against an active configuration (.cab) file will include other information including:
- The current status of the table (i.e. active, inactive)
- The starting and current point in the log where data has been captured
- The number of inserts, updates and deletes for the session (i.e. the duration of the capture agent run)
- The number of inserts, updates and deletes since the creation of the configuration file