Configuring Databases

You can configure IBM DB2, PostgreSQL, MySQL, and MSSQL, and Oracle server databases with ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture using this section.

Enabling CDC on IBM DB2 database

To enable ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture on DB2 SSH into the machine with username, perform the following steps.

Procedure

Start IBM DB2 database. You can use the following command:
```
db2  start db <DB_NAME>
```

Connect the DB and confirm that you can read metadata catalog. You can use the following command:

db2 connect to <DB_NAME>
db2 bind db2schema.bnd 

blocking all grant public sqlerror continue

The output should be:

db2 bind db2schema.bnd blocking all grant public sqlerror continue
LINE    MESSAGES FOR db2schema.bnd
------  ---------------------------------------------------
SQL0061W  The binder is in progress.
SQL0091N  Binding was ended with "0" errors and "0" warnings.
The command must finish with 0 errors and 0 warnings.

Backup the database:

db2 backup db <DB_NAME> to location /dev/null

Restart DB and connect to it.

db2 restart db <DB_NAME>
db2 connect to <DB_NAME>

Create CDC database objects by running the SQL scripts in:
Create CDC tables Script. Refer the script file at the path <TIBCO_HOME>\bw\palettes\cdc\<version_number>\samples\scripts.zip.

Check the status of CDC service on the database side by running the command:

asnccmd capture_schema=ASNCDC capture_server=<db_name> status

In DB2 by default, CDC artifacts are created in ASNCDC schema. Ideally the following output is observed for the above command:


2023-09-18-18.46.58.952383 ASN0520I  "AsnCcmd" : "ASNCDC" : "Initial" : The STATUS command response: "WorkerThread" thread is in the "is doing work" state.
2023-09-18-18.46.58.952395 ASN0520I  "AsnCcmd" : "ASNCDC" : "Initial" : The STATUS command response: "LogrdThread" thread is in the "is doing work" state.
The worker thread and the log read thread (LogrdThread) should be in ‘is doing work’ state.

If the above command times out without displaying the above output, start the service with the command below:

asncap capture_schema=ASNCDC capture_server=db_name &
The output for this command must show the number of active and inactive entities.
ASN0109I  CAPTURE "ASNCDC" : "WorkerThread". The Capture program has successfully initialized and is capturing data changes for "4" registrations. "0" registrations are in a stopped state. "1" registrations are in an inactive state.

Add desired table to capture mode by executing the stored procedure from the SQL client, the script file located at <TIBCO_HOME>\bw\palettes\cdc\<version_number>\samples\scripts.zip:

CALL CDCPLUGIN.ADDTABLE(<actual_schema>, <table_name>);
Example:
CALL CDCPLUGIN.ADDTABLE(‘BANK’, ‘HOLDING’);

Reinitialize CDC service by executing the following command:
Now data can be inserted/updated into the table or deleted from the table (for example: BANK.HOLDING) and data changes can be polled.
If the table needs to be removed from CDC, call the following stored procedure from SQL client, script file located at <TIBCO_HOME>\bw\palettes\cdc\<version_number>\samples\scripts.zip:
```
CALL CDCPLUGIN.REMOVETABLE(<actual_schema>, <table_name>);
Example:
CALL CDCPLUGIN.REMOVETABLE(‘BANK’, ‘HOLDING’);
Then ‘reinit’ the CDC service and ensure that the worker thread and log read thread are in ‘doing work’ state.
```
Note: Whenever, status command or reinit command times out, restart the CDC service as explained in the step: 7

Enabling CDC on PostgreSQL database

To enable ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture on PostgreSQL, after the plug-in installation is complete, ensure that the database is up and running. To conduct change data capture operations, create a new user role apart from SUPERUSER.

Procedure

Create a user/role for CDC.
Grant the role with LOGIN, SELECT, CERATE and REPLICATION privileges.
Create a new password for the role.
Go to <path_to>/postgres/<version_number>/main/postgresql.conf and open the file and perform the following operations:

Look for ‘listen_address’, set the value to ‘*’:

listen_address= ‘*’

Move to ‘WRITE-AHEAD Log’’ section. Set:

wal_level = logical

Move to ‘REPLICATION’ section and set:

max_wal_senders = 4

At the end of the file, create a section called ‘MODULES’ and set:

shared_preload_libraries = ‘pgoutput’

Open the file, pb_hba.conf:located at <path_to_postgres>/postgres/<version_number>/main

Go to the replication privileges section at the end of the file:

Make sure that the entries are consistent with the following and add a row for the CDC db user at the end:

=============================================
#Allow replication connections from localhost, by a user with the
# replication privilege.

local   replication   all                      trust
local   replication   all       127.0.0.1/32   trust
host    replication   all                      trust
host    all           all           all       md5
#host   all           all           ::/0
host    replication   <cdc_db_user> 0.0.0.0/0  all

Inorder to add a table in the Replication mode run the following SQL command:

ALTER TABLE <schema_name.db_name> replica identity FULL;

Enabling CDC on MySQL

To enable ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture on MySQL, alter root user and set a password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password_for_root>

Procedure

Create a user for CDC and grant all privileges on all databases

CREATE USER '<user_for_cdc>'@'%' IDENTIFIED BY '<cdc_password>';
Grant all privileges on *.*;
Flush privileges;
Exit;

The minimum set of privileges required for replication is:

Grant select, show databases, replication slave, replication client on *.* to ‘<cdc_db_user>’;

Go to the directory: <path_to>/mysql/mysql.conf.d
Open the mysqld.cnf file
Ensure to uncomment:

Port = 3306
Set bind-address = 0.0.0.0

Edit and set the following for replication at the end of the file:

server-id		= 1
log_bin			= /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds	= 2592000
max_binlog_size   = 100M
binlog_format = row
default_authentication_plugin = mysql_native_password

The binlog size can be updated based on your preference.

Save the file and restart the DB
Verify the status:
Expected - Active and Running.

For example, the following is the command for Linux

sudo systemctl status mysql

The server_id can also be queried on mysql prompt: select @@server_id;

Check the following variables:

a. mysql> SELECT FROM performance_schema.global_variables WHERE variable_name='log_bin'; 
SELECT variable_value AS ‘BINARY_LOGGING_STATUS (log-bin) ::”
FROM performance_schema.global_variables variable_name = ‘log_bin’;
This should return ‘ON’

b. SHOW global_variables WHERE variable_name = ‘binlog_row_value_options’;
The value returned should be empty. If not explicitly set empty value by executing:
set @@binlog_row_value_options=””;

Enabling CDC on MSSQL

To enable ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture on MSSQL, verify the following persmissions:

The user should be part of db_owner role for CDC database.
The user should have select grant on all captured DB objects.
The members of db_owner role can view information on all captured instances

To enable CDC on database:
The system stored procedures should be executed from SQL Server Management Studio

Procedure

From SQL Server Management Studio, select View menu.
Click Template Explorer.
Expand SQL Server Templates.
Expand Change Data Capture, expand Configuration option and then select Enable Database for CDC.
Run the stored procedure sys.sp_cdc_enable_db to enable the database for CDC after replacing the DB name in the USE statement at the top.

To enable CDC for each table:

Procedure

SQL Server Management Studio > Template Explorer > expand SQL Server Templates
Expand Change Data Capture, expand Configuration option, and select Enable Table Specifying Filegroup option.
Replace the table name in the USE statement with the name of the table that requires CDC enabled, and run the stored procedure sys.sp_cdc_enable_table.

The filegroup where SQLServer saves the CDC information for the selected table must already be present.

Enabling CDC on Oracle

To enable ActiveMatrix BusinessWorks™ Plug-in for Change Data Capture on Oracle server perform the following steps:

Procedure

Open the new SQL Plus and run the following queries:

Enter user-name: '/' as sysdba 

SQL> show con_name; 
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs; 
CON_ID CON_NAME          OPEN MODE  RESTRICTED
------------------------------
2 PDB$SEED             READ ONLY  NO 
3 ORCLPDB              READ WRITE NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 1.0335E+10 bytes

Fixed Size         12816984 bytes
Variable Size      1577058304 bytes
Database Buffers   8724152320 bytes
Redo Buffers       20733952 bytes
Database mounted.
Database opened.

SQL> show pdbs;
CON_ID CON_NAME    OPEN MODE  RESTRICTED
---------- ------------------------------ ----------
2 PDB$SEED         READ ONLY  NO
3 ORCLPDB          READ WRITE NO

SQL> show parameter recovery;
NAME        TYPE        VALUE
------------------------------------
db_recovery_file_dest         string
db_recovery_file_dest_size    big integer   0
recovery_parallelism          integer       0
remote_recovery_file_dest     string        0


SQL> alter system set db_recovery_file_dest_size = 20G;
System altered.

SQL> alter system set db_recovery_file_dest = 'C:\oracle19c\oradata\ORCL\recovery_area' scope=spfile;
System altered.

SQL> alter system set db_recovery_file_dest = 'C:\oracle19c\oradata\ORCL\recovery_area' scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0335E+10 bytes
Fixed Size         12816984 bytes
Variable Size      1577058304 bytes
Database Buffers   8724152320 bytes
Redo Buffers       20733952 bytes

Database mounted.

SQL> archive log list;
Database log mode            No Archive Mode
Automatic archival           Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   1
Current log sequence         2

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Old online log sequence       1
Next log sequence to archive  2
Current log sequence          2

SQL> show pdbs;
CON_ID CON_NAME      OPEN MODE  RESTRICTED
---------- ------------------------------ 
2 PDB$SEED           MOUNTED
3 ORCLPDB            MOUNTED

SQL> alter database open;
Database altered.

SQL> show pdbs;
CON_ID CON_NAME       OPEN MODE  RESTRICTED
---------- ------------------------------ 
2 PDB$SEED            READ ONLY  NO
3 ORCLPDB             READ WRITE NO

SQL> archive log list;
Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           USE_DB_RECOVERY_FILE_DEST

Old online log sequence       1

Next log sequence to archive  2

Current log sequence          2

SQL> show parameter recovery;
NAME           TYPE        VALUE
------------------------------------ 
db_recovery_file_dest string very_area C:\oracle19c\oradata\ORCL\reco
db_recovery_file_dest_size   big integer 20G
recovery_parallelism         integer      0
remote_recovery_file_dest    string

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> alter database add supplemental log data;
Database altered.

SQL> commit;
Commit complete.

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;
GROUP#    SIZE_MB STATUS
---------- ---------- ----------
1        200 INACTIVE
2        200 CURRENT
3        200 UNUSED

SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE ORDER BY 1, 2;
GROUP#
----------
MEMBER
---------------------------
 1
C:\ORACLE19C\ORADATA\ORCL\REDO01.LOG
 2
C:\ORACLE19C\ORADATA\ORCL\REDO02.LOG
 3
C:\ORACLE19C\ORADATA\ORCL\REDO03.LOG

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database Altered

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database Altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('C:\ORACLE19C\ORADATA\ORCL\REDO01.LOG') size 5G REUSE;
Database Altered

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

GROUP#    SIZE_MB STATUS
---------- ---------- ----------------
1       5120 UNUSED
2        200 CURRENT
3        200 UNUSED

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

GROUP#    SIZE_MB     STATUS
---------- ---------- ----------------
1          5120       UNUSED
2          200        CURRENT
3          200        UNUSED

Note: Change Group 3 first because Group 2 is in use because its status is CURRENT

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('C:\ORACLE19C\ORADATA\ORCL\REDO03.LOG') size 5G REUSE;
Database altered.

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;
GROUP#    SIZE_MB STATUS
---------- ---------- ----------------
1       5120 UNUSED
2        200 CURRENT
3       5120 UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

GROUP#    SIZE_MB STATUS
---------- ---------- ----------------
1       5120 CURRENT
2        200 ACTIVE
3       5120 UNUSED

Note: Now here the status of Group 1 log file is changed from UNUSED to CURRENT. Now we can change the size of the Group 2 logfile. We now need to wait for the database to eventually switch the status of log group 2 to INACTIVE.

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

GROUP#    SIZE_MB STATUS
---------- ---------- ----------------
1       5120 CURRENT
2        200 INACTIVE
3       5120 UNUSED

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
Database altered

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('C:\ORACLE19C\ORADATA\ORCL\REDO02.LOG') size 5G REUSE;
Database altered

SQL> SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;
GROUP#    SIZE_MB STATUS
---------- ---------- ----------------
1       5120 CURRENT
2       5120 UNUSED
3       5120 UNUSED

SQL> commit;
Commit complete.

SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

SQL> CREATE TABLESPACE logminer_tbs DATAFILE 'C:\oracle19c\oradata\ORCL\orclpdb\logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created.

SQL> alter session set "_ORACLE_SCRIPT"=TRUE;
Session altered

SQL> alter user sys identified by <SYS_USER_PASSWORD>;
User altered.

SQL> CREATE USER c##dbzuser IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS CONTAINER=ALL;
User created.

SQL> GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT LOGMINING TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL
Grant succeeded.

SQL>GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL;
Grant succeeded.

SQL> commit;
Grant succeeded.

Did you find this helpful?

Yes No

Configuring Databases

Enabling CDC on IBM DB2 database

Enabling CDC on PostgreSQL database

Enabling CDC on MySQL

Enabling CDC on MSSQL

Enabling CDC on Oracle