DB2 Replication Options

 Replication Options

1) Db2 High availability disaster recovery (HADR): 
Active/Passive Replication, supports up to three remote standby servers.
when active DB Down, With HADR, a standby database can take over in seconds.  the original primary database can be brought back up and returned it to its primary database status, which is known as failback. A failback can be initiated when the old primary database is consistent with the new primary database. After reintegrating the old primary database into the HADR setup as a standby database, the database roles are switched to enable the original primary database as the primary database.

2) Db2 pureScale: designed for continuous availability,  All software components are installed and configured from a single host. pureScale scaling your database solution using Multiple database servers, which are known as members, process incoming database requests; these members operate in a clustered system and share data. You can transparently add more members to scale out to meet even the most demanding business needs. There are no application changes to make, no data to redistribute, and no performance tuning to do.

3) IBM Info-Sphere Data Replication product (IIDR): 

IIDR has three alternative components

1. Change data capture (CDC): for heterogeneous databases, ie, replication between Oracle and DB2.
2. SQL Replication: old way, used in broadcast topology, create staging tables in source DB which cost increase DB size to capture all DB changes.
3. Q Replication: use IBM MQ, capture all DB changes inside MQ, high volume, low latency.
   
   
   

Q Replication: the best solution in IIDR

Q Replication is a high-volume, low-latency replication solution that uses WebSphere MQ message queues to transmit transactions between source and target databases

Q Replication High availability scenarios

1. Two-nodes for failover: Update workloads execute on a primary node, Second node not available for any workload
2. Two-nodes with one read-only node for query offloading: Update workloads execute on a primary node, Read-only workloads are allowed on a second node
3.  Two-nodes, Active/Active, with strict conflict rules: Update workloads execute on two different nodes, Conflicts are managed, Deployed only when conflicts can be carefully managed.
4. Three-nodes with at least one read-only node: Update workloads execute on a primary node, Read-only workloads execute on second and third nodes, Conflicts are tightly managed
5. Three-nodes, Active/Active, with strict conflict rules: Update workloads execute on three different nodes, Conflicts are managed, using data partitioning, workload distribution, use when have unstable/slow connection topologies.

Q Replication components

1) The Q Capture and Q Apply programs and their associated DB2 control tables (listed as Capture, Apply, and Contr in the diagram)

2) The Administration tools that include the Replication Center (db2rc) and the ASNCLP command-line interface

3) The Data Replication Dashboard and the ASNMON utility that deliver a live monitoring web tool and an alert monitor respectively

4) Additional utilities like the ASNTDIFF table compare program and the asnqmfmt program to browse Q Replication messages from a queue WebSphere MQ

Notes:

- The Q Capture program is log-based
- The Q Apply program applies in parallel multiple transactions to the target DB2
- The Q Capture program reads the DB2 recovery log for changes to a source table defined to replication. The program then sends transactions as WebSphere MQ messages over queues, where they are read and applied to target tables by the Q Apply program.
- Asynchronous delivery: Q Apply program receive transactions without having to connect to the source database or subsystem. Both the Q Capture and Q Apply programs operate independently of each other—neither one requires the other to be operating.

InfoSphere Information Server

InfoSphere Information Server is an IBM data integration platform that provides a comprehensive set of tools and capabilities for managing and integrating data across various sources and systems. It is designed to help organizations address data quality, data integration, data transformation, and data governance challenges.

InfoSphere Information Server enables businesses to access, transform, and deliver trusted and timely data for a wide range of data integration use cases, such as data warehousing, data migration, data synchronization, and data consolidation. It offers a unified and scalable platform that supports both batch processing and real-time data integration.

Key components of InfoSphere Information Server include:

1) DataStage: A powerful ETL (Extract, Transform, Load) tool that allows users to design, develop, and execute data integration jobs. It provides a graphical interface for building data integration workflows and supports a wide range of data sources and targets.

2) QualityStage: A data quality tool that helps identify and resolve data quality issues by profiling, cleansing, standardizing, and matching data. It incorporates various data quality techniques and algorithms to improve the accuracy and consistency of data.

3) Information Governance Catalog: A metadata management tool that enables users to capture, store, and manage metadata about data assets, including data sources, data definitions, data lineage, and data ownership. It helps organizations establish data governance practices and provides a centralized repository for managing and searching metadata.

4) Data Click: A self-service data preparation tool that allows business users to discover, explore, and transform data without the need for extensive technical skills. It provides an intuitive and user-friendly interface for data profiling, data cleansing, and data enrichment.

5) Information Analyzer: A data profiling and analysis tool that helps assess the quality, structure, and content of data. It allows users to discover data anomalies, identify data relationships, and generate data quality reports.

InfoSphere Information Server provides a comprehensive and integrated platform for managing the entire data integration lifecycle, from data discovery and profiling to data quality management and data delivery. It helps organizations improve data consistency, data accuracy, and data governance, leading to better decision-making and increased operational efficiency.

for more information visit
https://www.youtube.com/watch?v=U_PN8QLTec8

Mongo DB on Ubuntu

Login to database

mongo

List available databases 

show dbs

or

 db.adminCommand( { listDatabases: 1 } )

Change active DB

use <db name>

List DB collections (tables)

show collections

List collection rows

db.<collectionName>.find()

or

db.<collectionName>.find().pretty()

Get documents count inside a collection

db.<collectionName>.count()

Bulk insert documents inside a collection

db.<collectionName>.insertMany( [

   { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },

   { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "P" },

   { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },

   { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },

   { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" },

] );

Delete a document from a collection

db.<collectionName>.deleteOne( {  "SystemName" : "Help" } )

db.<collectionName>.deleteMany({                   })

Search for documents inside a collection

db.<collectionName>.find({id:'xxxx'})

Backup Database 

mongodump -h sample.mongodbhost.com:27017 -d DATABASE_NAME -u USER_NAME -p SAMPLE_PASSWORD -o ~/Desktop

or

mongodump --uri "mongodb://usersname:password@127.0.0.1:27100/dbname?replicaSet=replica_name&authSource=admin" --out "C:\Umesh"




Restore Database 

mongorestore --host sample.mongohost.com --port 27017 --username USER_NAME --password SAMPLE_PASSWORD --db DATABASE_NAME .

or

mongorestore mongodb+srv://UserID:Password@ServerIP/DBName   Path2Dump

or

mongorestore --uri "mongodb://usersname:password@127.0.0.1:27100/dbname?replicaSet=replica_name&authSource=admin" -d dbname mongodbumppath




SQL Server FILESTREAM AND FILETABLE

If the stored objects are on average larger than 1 MB, FileStream is the best option because of the faster read access. For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

FILESTREAM: save large files to DB, FileStream data type is implemented as a varbinary (max) column.

  

FILETABLE: (sub type of filestream), support direct access to file from file system and support full-text search

SETTING UP FILESTREAM AND FILETABLE IN SQL SERVER

1) For Sql Server 2019: windows > run > SQLServerManager15.msc

2) Enable FILESTREAM access level with Transact-SQL code

--0 = Disables FILESTREAM support for this instance.

--1 = Enables FILESTREAM for Transact-SQL access.

--2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.

USE Master

GO

EXEC sp_configure filestream_access_level ,2

RECONFIGURE

3) Create folder "FILESERVER" on c:\

4)  Create DB with name "FILESDB"

CREATE DATABASE FILESDB

ON PRIMARY (NAME = FS,FILENAME = 'C:\FILESERVER\FILESDB.mdf'),

FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FStream, FILENAME = 'c:\FILESERVER\Fs')

LOG ON  (NAME = FILESDBLog, FILENAME = 'C:\FILESERVER\FILESDBLog.ldf')

WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FILESERVER')

GO

5) Create table with name "FileTable"

CREATE TABLE Documents AS FileTable

GO

6) View Table on HD by right click on FileTable and choose "Explore FileTable Directory"

User Latest DB2 11.5.4/ SQL Server 2022 on Docker

Prerequisites: Docker engine

1) Get Docker Image

docker pull ibmcom/db2

2) Start Docker image

DB Name: testdb
UserName: db2inst1

Password: P@ssw0rd

Map default DB2 port 50000 to 60000

Map HD : C:\DB2\TestDockerDB

docker run -it --name mydb2 --privileged=true -p 60000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=P@ssw0rd -e DBNAME=testdb -v C:\DB2\TestDockerDB:/database ibmcom/db2




3) To Login to Docker image

docker exec -it mydb2 bash

For More Configuration Check

https://hub.docker.com/r/ibmcom/db2

To run SQL Server 2022, use the next command

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest
















redis

sudo apt update
sudo apt install redis-server

OR

sudo apt-get update
sudo apt-get install build-essential tcl
curl -O http://download.redis.io/redis-stable.tar.gz     # or     wget http://download.redis.io/redis-stable.tar.gz
tar xzvf redis-stable.tar.gz
cd redis-stable
make
make test
sudo make install


Update redis config, allow manage Redis as a service
===================================================
sudo nano /etc/redis/redis.conf
supervised systemd


Configuring a Redis Password
============================
sudo nano /etc/redis/redis.conf
requirepass <password>

Reload the redis service to adapt the new changes made on the configuration file.
sudo systemctl reload redis

Validate Status
===============
sudo systemctl status redis



Stop\Start Redis
================
/etc/init.d/redis-server stop
/etc/init.d/redis-server start

OR

sudo service redis-server start
sudo service redis-server stop
sudo service redis-server restart

OR

sudo systemctl restart redis

running the server manually with config

 /usr/bin/redis-server /etc/redis/redis.conf

Test the Redis Instance Functionality
=====================================
redis-cli ping
OR
redis-cli -a <password> ping
OR

redis-cli -h redis15.localnet.org -p 6390 ping

it should answer with PONG, but in some cases it can answer with "LOADING Redis is loading the dataset in memory"


How to fix "LOADING Redis is loading the dataset in memory"?
1) sudo service redis-server stop
2) delete redis dump and temp files from /var/lib/redis/
3) /etc/init.d/redis-server start



Uninstall redis
sudo apt-get --purge remove redis-server
sudo rm -rf /etc/redis/dump.rdb





How to flush Redis cache and delete everything using the CLI?

redis-cli flushall

How to Browse/View The Values Stored in Redis?

Redis Commander allow web view of redis DB

Install it using 

npm install -g redis-commander

Activate the web-server using 

redis-commander  --redis-password <Password>

Solve Redis Errors

Problem 1

PHP Fatal error:  Uncaught RedisException: MISCONF Redis is configured to save RDB snapshots, but it is currently not able to persist on disk. Commands that may modify the data set are disabled, because this instance is configured to report errors during writes if RDB snapshotting fails (stop-writes-on-bgsave-error option). Please check the Redis logs for details about the RDB error.

Solution

 nano /etc/redis/redis.conf

On redis.conf line ~235 let's try to change config like this

- stop-writes-on-bgsave-error yes
+ stop-writes-on-bgsave-error no

Problem 2

 

# WARNING: The TCP backlog setting of 511 cannot be enforced because /proc/sys/net/core/somaxconn is set to the lower value of 128.

# WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.

Solution

sudo nano /etc/sysctl.conf

# Add at the bottom of file
vm.overcommit_memory = 1
net.core.somaxconn=1024

Now for these configs to work, you need to reload the config

sudo sysctl -p

Problem 3

# WARNING you have Transparent Huge Pages (THP) support enabled in your kernel. This will create latency and memory usage issues with Redis. To fix this issue run the command 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' as root, and add it to your /etc/rc.local in order to retain the setting after a reboot. Redis must be restarted after THP is disabled.

Solution

sudo nano /etc/rc.local

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
    echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi

DB2 Basic commands

How to connect to DB2 through CMD, you can connect to db2 using db2admin through CMD, open CMD as administrator then run the next command

runas /user:db2admin db2cmd.exe

---------------------------

Some applications like Crystal Report can't connect to remote DB2 instance, in this case we should catalog remote DB2 DB to act as if it is local DB!

To catalog remote DB we should create node point to remote server then catalog DB by name inside this node.


Steps:
db2cmd
db2 catalog tcpip node <NodeName> remote <IP> server <Port>
db2 catalog db <DB Name> at node <NodeName>

Note: we can catalog DB with alise name
db2 catalog db <DB Name> as <NewDB_Name> at node <NodeName>

To list available nodes 
db2 list node directory


To list available nodes and available DBs

db2 list db directory

To un-catalog DB / Node
db2 uncatalog db <DB Name>
db2 uncatalog node <Node Name> 

Create Database
db2 create db <DB Name>

To Connect to DB
db2 connect to books user db2admin using your_password

To View available tables after connect to DB
list tables

Backup database
db2 backup database <DB Name>   to "c:\mrafie" exclude logs without prompting 

note: when backup "TEST1" DB the backup name will be <DB Name>.0.DB2.xxx.TimeStamp

Drop Database
db2 drop db <DB Name>

Restore DB
db2 restore db <DB Name> from "c:\mrafie"

Connect to exist DB
db2 connect to <DB Name>

Notes: some times when connect to DB you get error "Error roll-forward pending" and you can solve this error using the next command
db2 rollforward db <DB Name> to end of logs and stop

View Current DB2 version
db2level

View DB2 instances (in case you have many DB2 versions installed)
db2ilist 

Stop/Start DB2 instance 
db2stop
db2start

Change DB configuration
db2 connect to test1
update database configuration using DLCHKTime 5000 immediate;

Change DB2 Instance configuration 
update DB config using DLCHKTime 5000 immediate;

View DB2 memory/processor usage 
db2pd -osinfo

View DB2 license info
db2licm -L






Sample Task

db2 Create database with name "strestst" then backup it and restore to new database with name "newstres"

1)
db2 CREATE DATABASE "strestst" AUTOMATIC STORAGE YES ON 'c:' DBPATH ON 'c:' USING CODESET 1256 TERRITORY AA

2)
db2 CONNECT TO "strestst"
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 CONNECT RESET
db2 DEACTIVATE DATABASE "strestst"
db2 BACKUP DATABASE "strestst" TO "c:\rafie" COMPRESS EXCLUDE LOGS WITHOUT PROMPTING


3)
db2 CREATE DATABASE "newstres" AUTOMATIC STORAGE YES ON 'c:' DBPATH ON 'c:' USING CODESET 1256 TERRITORY AA

4)
db2 RESTORE DATABASE strestst FROM "c:\rafie" TAKEN AT 20181108112835 INTO "newstres" WITHOUT PROMPTING

5)
db2 ROLLFORWARD DATABASE "newstres" TO END OF LOGS AND COMPLETE



Get DB2 Port on Windows

On the Windows DB2 server, open a DB2 Command Window and execute the command

db2 get database manager configuration | findstr /i svce

This should provide some output like:

 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

SVCENAME is the port that DB2 is listening on. To resolve this name to an actual port number, you need to refer to the services file, which is located at %SystemRoot%\system32\drivers\etc\services.

1. For Linux
2.  Open a command prompt.
3. Enter cd /usr/etc.
4. Enter cat services.
5. Scroll through the list of services until you find the connection port number for the database instance of the remote database. The instance name is usually listed as a comment. If it is not listed, complete the following steps to find the port:
   1. Open a DB2® command prompt.
   2. At the DB2 command prompt, verify that you are on the correct instance:

      get instance

      DB2 reports the current instance.
   3. Run the following command to find the service name for your instance:

      get dbm cfg | grep SVCE

      DB2 reports the service name. For example:

      TCP/IP Service name          (SVCENAME) = db2cdb2inst24

   4. Use the service name to find the port number in the services file. For example, enter a command similar to this one:

      grep service_name /etc/services

      DB2 returns the information. For example:

      service_name   50012/tcp   # Connection port for DB2 instance instance

Grant Access to User

Example 1: Grant SELECT privileges on table DSN8A10.EMP to user PULASKI.

   GRANT SELECT ON DSN8A10.EMP TO PULASKI;

Example 2: Grant UPDATE privileges on columns EMPNO and WORKDEPT in table DSN8A10.EMP to all users at the current server.

   GRANT UPDATE (EMPNO,WORKDEPT) ON TABLE DSN8A10.EMP TO PUBLIC;

Example 3: Grant all privileges on table DSN8A10.EMP to users KWAN and THOMPSON, with the WITH GRANT OPTION.

   GRANT ALL ON TABLE DSN8A10.EMP TO KWAN,THOMPSON WITH GRANT OPTION;

Example 4: Grant the SELECT and UPDATE privileges on the table DSN8A10.DEPT to every user in the network.

   GRANT SELECT, UPDATE ON TABLE DSN8A10.DEPT
     TO PUBLIC;

Example 5: Grant ALTER privileges on table DSN9910.EMP to role ROLE1:

   GRANT ALTER ON TABLE DSN9910.EMP TO ROLE ROLE1;

View Table Columns/Types

DESCRIBE TABLE orders;

Db2 ALTER TABLE ALTER COLUMN

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DATA TYPE TIMESTAMP;

1 2 3

ALTER TABLE orders     ALTER COLUMN note     SET DATA TYPE VARCHAR(255);

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DEFAULT CURRENT_TIMESTAMP;

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DEFAULT CURRENT_DATE;

Export DB2 Table 

export to file myfile.ixf and use file format ixf and log export messages to msg.txt

db2cmd
db2 connect to DB1 user db2admin
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff

Import DB2 Table 

import from file myfile.ixf and use file format ixf and log import messages to msg.txt

db2cmd
db2 connect to DB1 user db2admin
db2 import from myfile.ixf of ixf messages msg.txt INSERT_UPDATE into staff

DB2 Import Options

- CREATE - first create the target table before inserting the data.
- INSERT - Data is inserted into the target table.
- INSERT_UPDATE - Data is inserted into the target table. if matching primary keys, row is updated with the new data.
- REPLACE - The target table is first truncated, then data is inserted.
- REPLACE_CREATE - If target table already exists, it is truncated. If it does not exist, it is first created.

Task Export table and reimport it in another database

db2cmd

db2 connect to xxx user db2admin

db2 EXPORT to APPFORM.ixf OF ixf SELECT * FROM PASSPORTS.APPFORM WHERE 1=2

db2 LOAD FROM APPFORM.ixf OF ixf REPLACE INTO PASSPORTS.APPFORM NONRECOVERABLE INDEXING MODE INCREMENTAL

SQL1092N "USERID does not have the authority to perform the requested command or operation."

To enable the domain user ID to access the database, complete the following steps.

1. Add the domain user ID to the local group DB2ADMNS.
2. With a user having SYSADM privileges or local administrator authority, open a DB2 command window and run the following commands from the prompt. (Alternatively, you can open a DB2 administrator command window.)

   db2set DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL        
   db2 update dbm cfg using sysadm_group DB2ADMNS
   db2stop                                        
   db2start

3. Restart the DB2 Windows services with the login ID set to the domain user ID.

Fix Operation not allowed for reason code "1" on table xxx

db2 set integrity for  PASSPORTS.CITIZEN immediate checked

Fix SQL0668N  Operation not allowed for reason code "7" on table xxx

db2 REORG TABLE PASSPORTS.PURCHASEORDER

The REORGCHK command provides table or index statistics that indicate whether reorganization may result in improved usage or disk space, and/or improved performance.

db2 reorgchk

or

db2 reorgchk update statistics on table system




How to Select Date in format yyyy-MM-dd?

Select CHAR(myAPPISSUEDATE, ISO) from MyTable;

DB21061E Command line environment not initialized.

This happens when the DB2CLP environment variable is not set. 

at the prompt enter these commands:

db2cmd -i -w db2clpsetcp
echo %DB2CLP%

To configure the DB2 client to behave as Unicode application, you have to set the DB2CODEPAGE registry variable to '1256'.

Following are the steps to set the DB2CODEPAGE registry variable to '1208':

1. Open a DB2 Command window or Command Line Processor (CLP) by navigating through the following path:
   Start > IBM DB2 > DB2Copy1 > Command Line Tools > Command Window
2. Set the DB2CODEPAGE registry variable to '1256'.
   db2set DB2CODEPAGE=1256

Get error when upgrade database:

db2 upgrade database alxdb

SQL1006N The code page "0140722399268602I" of the application does not match

the code page "0000000000000000I" of the database.

we can set the correct code page using db2codepage=1256 or

set it using system variable

and command will success

 

Determine code page for a DB2 database

db2 get db cfg for db1

if need increase Tablespace size

   

ALTER TABLESPACE TSASNCA

     AUTORESIZE YES INCREASESIZE 10 PERCENT

SQL5035N  The connection failed because the database product has been updated

and the database needs to be upgraded  SQLSTATE=55001

Steps:

1. Launch 'DB2 Command Window - Administrator'

2. Run a command similar to the following:

db2 upgrade database <databasename>

for more information check
https://www.db2tutorial.com/