DB2 : Masking column with db2 mask

This is tutorial to masking data using db2 mask

First, we need to create user :

#useradd -u 103 -g db2iadm1 -G dasadm1 db2emp
#useradd -u 102 -g db2iadm1 -G dasadm1 db2man

user db2man will see all clear data without mask, and db2emp will get masked data.

Then, create role and assigned to specify user.

[db2admin@db2server ~]$ db2 create role RMAN
DB20000I  The SQL command completed successfully.
[db2admin@db2server ~]$ db2 GRANT ROLE RMAN TO USER DB2MAN
DB20000I  The SQL command completed successfully.
[db2admin@db2server ~]$ db2 create role REMP
DB20000I  The SQL command completed successfully.
[db2admin@db2server ~]$ db2 GRANT ROLE REMP TO USER DB2EMP
DB20000I  The SQL command completed successfully.

Create file .sql : save as maskr2.sql

CREATE OR REPLACE MASK MASK_TLPV ON DB2ADMIN.PROMISE_T2_MASK
FOR COLUMN V_TELP
RETURN
        CASE    WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'RMAN') = 1)
                THEN V_TELP
        ELSE 'XXX-XX-' || SUBSTR(V_TELP,8,4)
        END
ENABLE;

execute this file :

[db2admin@db2server ~]$ db2 -tvf latest_mask.sql
DB20000I  The SQL command completed successfully.

[db2admin@db2server ~]$ db2 alter table PROMISE_T2_MASK ACTIVATE COLUMN ACCESS CONTROL;
DB20000I  The SQL command completed successfully.

DB2 SQLCODE: -551, SQLSTATE: 42501 ID does not have the required authorization or privilege

DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: DB2MAN;SELECT;DB2ADMIN.PROMISE_T2_VENDOR_MASK
 Message: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.  Authorization ID: "DB2MAN".  Operation: "SELECT". Object: "DB2ADMIN.PROMISE_T2_VENDOR_MASK".

This user need privilige to perform select operation, we can solve this problem by
db2 grant select on table "db2admin"."promise_t2_vendor_mask" to user db2man with grant option;

DB2 : Create New Instance

Step by step for create new Instance on DB2

1. Create New User 
[root@db2server ~]# useradd -u 107 -g db2iadm1 -G dasadm1 db2pln
[root@db2server ~]# passwd db2pln
Changing password for user db2pln.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.

2. Execute db2icrt
[root@db2server ~]# cd /opt/ibm/db2/V10.5/instance/
[root@db2server instance]# ls

[root@db2server instance]# ./db2icrt -u db2fenc1 db2pln
DBI1446I  The db2icrt command is running.
DB2 installation is being initialized.

Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)

Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end

Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end

Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end

The execution completed successfully.

For more information see the DB2 installation log at "/tmp/db2icrt.log.18947".
Required: Review the following log file also for warnings or errors:
"/tmp/db2icrt_local.log.*"
DBI1070I  Program db2icrt completed successfully.

3. Setup port number
nano /etc/services
add following line :
db2c_db2pln     60006/tcp

4. Update dbm cfg settings
db2 update dbm cfg using SVCENAME db2c_db2pln

5. Restart instance
db2 force application all
db2stop
db2start

DB2 : Using Reorgchk

db2 reorgchk calculates statistics on the database to determine if tables or indexes, or both, need to be reorganized or cleaned up.

1. Execute following command to create file list tables :
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' from syscat.tables where type = 'T' and tabschema = 'DB2ADMIN' " > reorgchkdb.out

from my example, i have a schema = db2admin, change the desired schema on this query.

2. From previous command, we can see the output from reorgchkdb.out

3. After file reorgchkdb.out generated, we can execute reorgchkdb.out
$ db2 -tvf reorgchkdb.out > output_result_reorgchk.out

4. View result in file output_result_reorgchk.out

--- indicates that, since the formula results of F1, F2, and F3 are within the set bounds of the formula, no table reorganization is suggested. The notation *-* indicates that the results of F1 and F3 suggest table reorganization, even though F2 is still within its set bounds. The notation *-- indicates that F1 is the only formula exceeding its bounds. 
When we see asterisk (*) it suggest table reorganization.

reference : https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001971.html

Stress Test With PyLot

Step by step stress test with Pylot.

1. We need to install python first from
https://www.python.org/downloads/

(version 2.6 recommended, because when using 3.x, i got error when execute run.py pylot)

2. Install and Download wxPython for GUI Mode
Download from https://wxpython.org/download.php

3. Install Pylot
Download pylot from www.pylot.org/gettingstarted.html

4.

Configure certificate on Apache

Step by step to configure ceriticate on Apache Web Server

1. Create .key file & .cer file
[root@localhost certificate]# pwd
/home/source_deploy/certificate
[root@localhost certificate]# openssl pkcs12 -in runagworld_net_comodo.pfx -nocerts -nodes -out mydomain.key
[root@localhost certificate]# openssl pkcs12 -in runagworld_net_comodo.pfx -clcerts -nokeys -out mydomain.cer

2. Edit httpd.conf
[root@localhost certificate]# nano /etc/httpd/conf/httpd.conf

Listen 8084

Add following line
<IfModule mod_ssl.c>
        NameVirtualHost *:8084
        <VirtualHost *:8084>
                SSLEngine on
                SSLCertificateFile /home/source_deploy/certificate/mydomain.cer
                SSLCertificateKeyFile /home/source_deploy/certificate/mydomain.key
        </VirtualHost>
</IfModule>


We setup 8084, in this case, our JBoss application server running on port 8084

3. Reload httpd
[root@localhost certificate]# service httpd reload

4. Test application, with url
https://<our_ip>:8084/

Deploy Java Web Application with JBoss & MySQL

Step by step to deploy Java Web Application with Jboss & MySQL

1. Restore Database Backup to MySQL
[root@testgg2 DVD]# mysql -u root -p mydatabase < /home/backup_file_database.sql

2. Install & Download Jboss
http://jbossas.jboss.org/downloads
Unzip jboss-eap-6.1.0.zip

3. Upload MySQL Driver
path /home/jboss/jboss-eap-6.1/modules/system/layers/base/com/mysql

[root@testgg mysql]# pwd
/home/jboss/jboss-eap-6.1/modules/system/layers/base/com/mysql
[root@testgg mysql]# ls -l
total 4
drwxr-xr-x 2 root root 4096 May  4 00:55 main
[root@testgg mysql]# cd main/
[root@testgg main]# ls
module.xml  mysql-connector-java-5.1.30.jar
[root@testgg main]#

4. Edit standalone.xml
[root@testgg configuration]# pwd
/home/jboss/jboss-eap-6.1/standalone/configuration
[root@testgg configuration]# nano standalone.xml


on <driver> tag, add :
<driver name="mysql" module="com.mysql">
                        <driver-class>com.mysql.jdbc.Driver</driver-class>
                    </driver>

5. Specify JDBC on standalone .xml
copy these lines into <datasource> tag
<datasource jndi-name="java:jboss/promiseCMDS" pool-name="promiseCMDS" enabled="true">
                   <connection-url>jdbc:mysql://localhost:4249/my_database_name</connection-url>
                    <driver>mysql</driver>
                    <security>
                        <user-name>user</user-name>
                       <password>password</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                </datasource>

6. Upload .war & .ear on deployments folder

7. Start Jboss