Data Masking in DB2

User Data security is one of the critical aspects of database administration. Its DBAs responsibility to ensure data must only be accessed by authorized users and DBAs can control that by restricting access at various level. In some cases you want to completely or partially conceal data depending on who is accessing it, e.g. In Payroll application, manager can view only last 4 digit of employees SSN while a team member cannot see SSN of other employees but can see his/her SSN. If we try to achieve this at application level it will increase application complexity as well as might impact application performance but still your database is vulnerable. If a user gets access to database they can see any employee SSN. Best way to achieve it by Data Masking at database level. 

Data breaches might lead to financial loss, reputational damage, operational downtime, legal action, loss of sensitive data. With changing data privacy law and regulations its even critical now to ensure data is adequately protected. 

In DB2 LUW database Data Masking can be achieved with the help of Column Mask which was introduced in DB2 10.1 as part of Row and Column Access Control (RCAC) . In this blog we will go through steps to enable data masking in DB2 LUW and few scenarios of data masking.


How to Create Column Mask in DB2 LUW?

Column Mask can be created using CREATE MASK command. To create mask user must have SECADM authority, no other privilege is require like select on table or execute on function.

CREATE MASK <mask name> ON <table name> FOR COLUMN <column name> RETURN <case expression> ENABLE

Putty Fatal Error : Couldn't agree a key exchange algorithm

While working with PuTTY to SSH to Unix/Linux servers you might get error Putty Fatal Error : Couldn't agree a key exchange algorithm. Sometime this error can be specific to some servers while for other servers ssh works perfectly fine. In this blog we will see how to fix this issue.

Putty Fatal Error : Couldn't agree a key exchange algorithm

How do you raise a case with IBM support?

As a DB2 DBA or as a matter of fact if you are using any IBM product irrespective of hardware or software you might be in a situation where you might need support from IBM experts. To get IBM expert support you need to create SR (Service Request) with IBM , formerly known as PMR (Problem Management Report). In this blog we will go through steps to create IBM ID, add customer ID to your IBM id and finally create/view and update SR.


URLs to create IBM SR:

There is separate portal to create/view/update Hardware and Software SRs. You can use below URL to navigate to IBM SR portal.

Hardware SRs: https://www-946.ibm.com/support/servicerequest/Home.action

Software SRs : https://www.ibm.com/mysupport/s/?language=en_US

These URLs are valid as on date of publication of this blog, I will try my best to keep it updated with latest URL but in case it doesn't work, feel free to reach out to me to inform about it or put a comment below.

Tablespace Management in DB2

Tablespace is logical collection of containers which contains actual data of database. In DB2 LUW there is 3 types of tablespace SMS (System Managed Spaces), DMS (Database Managed Spaces) and Automatic Storage. In this blog we will go through different tablespace management operation and behavior of SMS, DMS and Automatic Storage for those operations.

DB2 Tablespace Type



















In this blog, we will go through different tablespace operations applicable for tablespaces using File or path based containers.

Create SAMPLE database in DB2

In this blog we will go though steps to create sample database. You might need SAMPLE database with tables, views, index, function, procedure and dummy data to conduct POC or for testing, learning purpose. You will need a DB2 instance to create SAMPLE database. Check blog How to create DB2 Instance on Linux for instructions to create DB2 instance and start it.

Prerequisite :

To create SAMPLE database you need a DB2 instance, also you should have SYSADM or SYSCTRL authorities for that instance.

Enable SSL for DB2 HADR Communication

In blog DB2 SSL we went through importance of SSL and steps to configure SSL for DB2 communication both at DB2 Server side and client side. In this blog we will go through steps to configure SSL for DB2 HADR communication. If you want you can configure SSL for both DB2 client and server communication as well as for HADR communication or only for HADR communication also. If you have not read blog DB2 SSL, I highly recommend to go through it first before moving into detailed steps to configure SSL for DB2 HADR communication.

In this blog we will use 2 Local KeyStore, one for Primary and other for Standby. We will consider a 2 node HADR setup with below configuration. In case you have more than 1 Standby nodes then you need to perform below steps on all other Standby nodes as well.

Primary Node : db2test1, Instance : db2i1

DB2 HADR Configuration










Standby Node : db2test2, Instance : db2i1

DB2 HADR Configuration









Encrypt DB2 HADR Database Setup without Downtime

In blog DB2 Native Encryption, How to Encrypt DB2 DatabaseHow to Encrypt DB2 Database using Centralized KeyStore we went through DB2 Native Encryption and understood step by step process to encrypt DB2 standalone database using both Local KeyStore and Centralized KeyStore. Encrypting DB2 standalone database involve downtime. In this blog we will go through step by step process to encrypt DB2 database in HADR setup without Downtime, before we proceed I would suggest to check above blogs to understand DB2 Native encryption and steps to configure it. 

In this blog we will use 2 Local KeyStore, one for Primary and other for Standby. You can use shared Local KeyStore as well, by creating KeyStore on shared File System.

We will consider a 2 node HADR setup with below configuration:

Primary Node : db2test1, Instance : db2i1

DB2 HADR Configuration










Standby Node : db2test2, Instance : db2i1

DB2 HADR Configuration









Shared Folder Access in Oracle Virtual Box Linux VM

You might be in a situation in which you need a shared file system between multiple VM or between your VM and host OS on which you can perform both read and write operation simultaneously. Oracle Virtual Box has a answer to this in form of Shared Folder feature but to use Shared Folder feature in your VM, you must install Virtual Box Guest Additions. This simple looking Virtual Box Guest additions is quiet complicated to setup in Linux VM, it becomes even more complicated if you are not a Linux admin. In this blog we will go through step by step process to setup Virtual Box Guest Additions and Add a Shared Folder in VM and access it.

Step1: Prerequisite

To configure Virtual Box Guest Addition, you need to have kernel-devel, gcc, make and elfutils-libelf-devel package installed. Run below command to check if packages are already present.

yum list gcc make kernel-devel elfutils-libelf-devel

yum list gcc make kernel-devel elfutils-libelf-devel







SQL1768N Unable to start HADR. Reason code = "5"

While initializing DB2 HADR you might receive error  SQL1768N  Unable to start HADR. Reason code = "5".  There are multiple scenario in which you might receive SQL1768N  Reason Code 5. In this blog we will go through scenarios in which you might get this error and solution to fix it. IBM documentation list below explanation for this error:


SQL1768N Unable to start HADR. Reason code = "<reason-code>".

Explanation:

The explanation corresponding to the reason code is:

5         Invalid service name for the hadr_local_svc configuration parameter.

User response:

The user response corresponding to the reason code is:

5      Ensure that the hadr_local_svc configuration parameter is set to a valid service name. For Unix platforms, edit the          /etc/services file. For Windows, edit %SystemRoot%\system32\drivers\etc\services. Alternatively, a literal port number can be specified for this parameter.

ChoudharySumit.com Completed 4 Months

Today 7th Jan 2021 ChoudharySumit.com completed 4 months since first blog was published. ChoudharySumit.com is extremely thankful and overwhelmed to share below statistics:



This was possible due to support of ChoudharySumit.com subscribers and users. Keep visiting for learning more about database technology and specially if you are interested and keen to know about DB2 LUW.

If you want to ask or inquire about something that is in your mind please do not hesitate to contact me

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.

Download IBM DB2

As part of your day to day activity as DB2 DBA, you might have come across requirement to install specify version of DB2, apply DB2 fix pack, install DB2 client on application servers or provide DB2 drivers to application team. In this blog we will go through step by step process to download DB2 LUW engine, db2 fix pack, db2 clients and drivers.

To download IBM DB2 LUW visit below URL

https://www.ibm.com/support/pages/download-db2-fix-packs-version-db2-linux-unix-and-windows


From this URL, you can check End of Support date for DB2 version and also download DB2 LUW products.