Migration Approaches 

• AWS Migration Strategies with minimal downtime                       Sr.No 1.

• AWS Migration Strategies with near-zero downtime                    Sr.No 2.

• AWS Migration Strategies with zero outage                                    Sr.No 3.

Migration Create BufferPool & TableSpaces

-----------------DBeaver----BUFFERPOOLS FP-------------------------------

call rdsadmin.create_bufferpool('db2test','BP_16K',2500,'Y','Y',16384);

call rdsadmin.create_bufferpool('db2test','IDX_EWAL_BP',2500,'Y','Y',16384);

call rdsadmin.create_bufferpool('db2test','IDX_TXN_BP',2500,'Y','Y',16384);

-----------------DBeaver-----TB SPACE ------------------------

call rdsadmin.create_tablespace('db2test','TBSPC_DAT_EWAL_16K','BP_16K',16384,1000,25,'U');

call rdsadmin.create_tablespace('db2test','USERTEMPSPACE01_8K','BP_16K',16384,1000,100,'T'); ----temporary tablespace

call rdsadmin.create_tablespace('db2test','TABLESPACE01','BP_16K',16384,1000,25,'U');

call rdsadmin.create_tablespace('db2test','LOADSPACE','BP_16K',16384,1000,25,'T'); ----temporary tablespace

1. Check Table Space list

(1)  SELECT TBSP_NAME, TBSP_ID, TBSP_TYPE, TBSP_STATE FROM SYSIBMADM.TBSP_UTILIZATION;

          (2)  SELECT * FROM SYSCAT.TABLESPACES WHERE OWNER ='RDSDB'

Connection Linux EC2 machine to connect RDS DB2 database

• db2 "catalog tcpip node testdb2 remote test-db.rds.amazonaws.com server 50000"

• db2 "catalog database intdb2 as testdb2 at node testdb2"

• db2 "catalog database rdsadmin as testrds at node testdb2"

Check List the Cataloged Databases 

db2 list database directory

  --------------Connect DB2 database -------------------------

• db2 connect to testdb2 user admin using admin1234   -----------------Supper User

• db2 connect to testrds user admin using admin1234     ---------------- RDS User

• db2 set schema testdb

• db2top -d testdb2 -n testdb2 -u admin -p admin1234   -------------------- db2top connection 

Create DDL  & E xecution Plan         

Copy  DDL db2testdb.ddl from S3 bucket to EC2 machine 

aws s3 cp s3://rds-dbs-files-int01/db2-monitoring-script/db2testdb.ddl .

---Deploy DDL script to database 

Connect database 

db2 "catalog database intdb2 as testdb2 at node testdb2"

db2 -tvf db2testdb.ddl |tee -a db2testdb.ddl.output

Disable FOREIGN key before full load below run the queries

(1)

db2 -x "

SELECT substr(R.reftabschema,1,20) AS P_Schema,

    substr(R.reftabname,1,40) AS PARENT,

    substr(R.tabschema,1,20) AS C_Schema,

    substr (R.tabname,1,40) AS CHILD,

    substr(R.constname,1,80) AS CONSTNAME,

    substr(LISTAGG(C.colname,', ') WITHIN

           GROUP (

                  ORDER BY C.colname),1,40) AS FKCOLS

FROM syscat.references R,

  syscat.keycoluse C

WHERE R.constname = C.constname

  AND R.tabschema = C.tabschema

  --AND R.tabname = C.tabname

   AND R.tabschema IN ('testdb')

GROUP BY R.reftabschema,

      R.reftabname,

      R.tabschema,

      R.tabname,

      R.constname" > testdb_FOREIGN_KEY_RI.txt

(2)

cat testdb_FOREIGN_KEY_RI.txt | while read P_SCHEMA PARENT C_SCHEMA CHILD CONSTNAME FKCOLS

do

echo "ALTER TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $CONSTNAME NOT ENFORCED;" >> testdb_RI_Not_Enforced.sql

echo "Alter TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $CONSTNAME ENFORCED;" >> testdb_RI_Enforced.sql

done

(3) 

db2inst1@ip-10-999-25-166 ~]$ db2 -tvf testdb_RI_Not_Enforced.sql

(4) 

db2inst1@ip-10-999-25-166 ~]$ db2 -tvf testdb_RI_Enforced.sql

 Table Count & Maintanances  

---------------------------------------TABLES ROWS COUNT----------------------------------------

db2 -x "SELECT distinct (trim(T.TABSCHEMA))|| '.' ||(trim(T.TABNAME)) from SYSCAT.TABLES T where tabschema IN ('testdb') and TYPE='T' order by 1" > testdb.lst

sort testdb.lst >testdb.2st

for i in `cat testdb.2st`

do

db2 -x "select trim('$i'), trim(count(*)) from $i"

done

 -----------------------------------------MAINTANANCE ----------------------------------------

RDS REORG Statement  Dbeavars

CALL SYSPROC.ADMIN_CMD('REORG TABLE testdb.SEARCH_AUTHID_ALLOTMENT_COUNT');

Linux Command for RUNSTATS 

db2 RUNSTATS ON TABLE PWM.TBL_OF_TBL ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL ;

------------------------------------- Reorg & Stats All Tables-------------------------

db2 -x "select trim(tabschema) || '.' || trim(tabname) from syscat.tables where tabschema in ('testdb') and TYPE='T' order by 1"  >  testdb_REORG.lst

for i in `cat  testdb_REORG.lst`

do

echo $i

date

db2 "REORG TABLE $i" >>testdb-reorg.txt

db2 "RUNSTATS ON TABLE $i WITH DISTRIBUTION  ON ALL COLUMNS AND sampled DETAILED INDEXES ALL ALLOW WRITE ACCESS"  >>testdb-stat.txt

done

Error & Soloution  

Error:

Alter TABLE TESTDB.AUTH_ITESTS ALTER FOREIGN KEY FK1_AUTHINCRTES ENFORCED

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0912N  The maximum number of lock requests has been reached for the

database. Reason code = "1"  SQLSTATE=57011

Solution

Step(1)  vi fk.sql    Past below command  Open vi fk.sql   add below steps

UPDATE COMMAND OPTIONS USING C OFF;

LOCK TABLE TESTDB.AUTH_ITEST3  IN EXCLUSIVE MODE;

LOCK TABLE TESTDB.AUTH_ITEST3 IN EXCLUSIVE MODE;

Alter TABLE TESTDB.AUTH_ITESTS  ALTER FOREIGN KEY FK1_AUTHIDS_GRPID ENFORCED;

COMMIT;

(Step2)  Connect database

db2 "catalog database intdb2 as testdb2 at node testdb2"

Step (3) Run db2 -tvf fk1.sql

$ db2 -tvf fk1.sql