DB2: Automated steps for DB refresh
-- PUT THE BACKUP AND THE DB2.RERESH.SH SCRIPTS IN THE SAME DIRECTORY
$ cp /usr/opt/app/dbdump/db_refresh/db2.refresh.sh cp /usr/opt/app/dbdump/db_refresh/wcdev1
$ -- AFTER THE BACKUP FILE IS COPIED AND REFRESH SCRIPT IS WHERE THEY ARE SUPPOSE TO BE, THEN ENSURE YOU ARE
LOGGED IN AS THE DATABASE OWNER.
IN THIS CASE, dev1ins1
-- VERIFY THAT ALL APPLICATIONS ARE DISCONNECTED:
$ db2 list applications
-- TO KICK OFF THE APPLICATION AND IMMEDIATELY BOUNCE THE DATABASE
$ db2set db2comm= ;db2 force applications all;db2stop force;db2start
# db2 force applications all;db2stop force; db2set db2comm=tcpip; db2start
#db2 "select count(*) from wcs.inventory"
-- THEN CHECK AGAIN TO ENSURE THE APPLICATIONS ARE DISCONNECTED
$ db2 list applications
db2 terminate
db2 deactivate db wcdev9
-- TO KICK OFF THE REFRESH
-- $ ./db2.refresh.sh -s wcstg1 -t wcdev1 -l /usr/opt/app/dbdump/db_refresh/wcdev1
$ ./db2.refresh.sh -s wcstg1 -t wcdev9 -l /usr/opt/app/dbdump/db_refresh
$ ./db2.refresh.sh -s wcprd1 -t wctst3 -l /usr/opt/app/dbdump/db_refresh
## ./db2.refresh.sh -s wcprd1 -t wctst1 -l /usr/opt/app/dbdump/db_refresh -b 5 <-- to start at step 5
[..]
BELOW IS WHAT YOU WILL SEE AT THE END OF THE REFRESH.
Stage 11 complete
--------------------------------------------------------------------
Beginning stage 12: Performing post refresh backup
--------------------------------------------------------------------
no
/usr/local/db2/admin_scripts/db2.backup_db.ksh
-------------------------------------------
Description
-------------------------------------------
When: 2022-05-23-13.49.49.762966
Where: WCDEV1 database on db2tst601.gcp.tmw.com
What: Backup succeeded [308]
Log: /usr/local/db2/admin_scripts/output/WCDEV1_Backup-1653330663.log
Response:
The backup was successful.
-------------------------------------------
Additional Information
-------------------------------------------
Backup successful. The timestamp for this backup image is : 20220523133104
Stage 12 complete
DB20000I The SQL command completed successfully.
DB20000I The TERMINATE command completed successfully.
Stage 13: SUCCESS
[dev1ins1@dev601 wcdev1
-- TO TURN ON THE LISTENER
-- $ db2set db2comm=tcpip ; db2stop force ; db2start
$ db2stop; db2set db2comm=tcpip; db2start
05/23/2022 13:56:00 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
05/23/2022 13:56:02 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
# These are the steps used to refresh a database manually.
# Where extra information may be useful, a URL has been provided to the
# command's information on Db2's Knowledge Center.
#
###################################################################################
1. Take backup of source database
a. Manually
db2 backup database <src_db> online to </path> compress without prompting
https://www.ibm.com/docs/en/db2/11.5?topic=commands-backup-database
b. Via script
/usr/local/db2/admin_scripts/db2.backup_db.ksh -i <instance> -d <database> -q
For example:
[stg1ins1@db2stg601 stg1ins1 stg1ins1]
$ /usr/local/db2/admin_scripts/db2.backup_db.ksh -i stg1ins1 -d wcstg1 -q
AND
[prd1ins1@db2prd601 prd1ins1 prd1ins1]
$ /usr/local/db2/admin_scripts/db2.backup_db.ksh -i prd1ins1 -d wcprd1 -q
Output to expect:
[prd1ins1@db2prd601 prd1ins1 prd1ins1]
$ /usr/local/db2/admin_scripts/db2.backup_db.ksh -i prd1ins1 -d wcprd1 -q
no
/usr/local/db2/admin_scripts/db2.backup_db.ksh
-------------------------------------------
Description
-------------------------------------------
When: 2022-03-22-17.00.20.850956
Where: WCPRD1 database on db2prd601.gcp.tmw.com
What: Backup succeeded [308]
Log: /usr/local/db2/admin_scripts/output/WCPRD1_Backup-1647986236.log
Response:
The backup was successful.
-------------------------------------------
Additional Information
-------------------------------------------
Backup successful. The timestamp for this backup image is : 20220322165717
2. Move backup of source to host with target database
a. SCP file from source host
scp <backup_image> <user>@<host>:</path>
For example:
3. Change permissions of source backup
# This step may need to be performed from root
a. Give group and others ability to read the file
chown <tgt_instance_owner> <backup_image>
[root@db2tst601 ~]# cd /usr/opt/app/dbdump/tst3ins3
[root@db2tst601 tst3ins3]# chmod 644 WCPRD1.0.prd1ins1.DBPART000.20220322165717.001
[root@db2tst601 tst3ins3]# chown tst3ins3:db2iadm1 WCPRD1.0.prd1ins1.DBPART000.20220322165717.001
[root@db2tst601 tst3ins3]# ls -lrt WCPRD1.0.prd1ins1.DBPART000.20220322165717.001
-rw-r--r-- 1 tst3ins3 db2iadm1 3020668928 Mar 22 17:06 WCPRD1.0.prd1ins1.DBPART000.20220322165717.001
b. Take note of the timestamp for use in the restore command
SAMPLE.0.db2inst1.DBPART000.20220122153155.001
|<---------->|
4. Backup any table data from target database
# For each table do the following
# This is typically done for masking data after the refresh
a. Export table data as needed
db2 -v "export to /<path>/<tabname>.del of del lobs to /<path> select * from <tabschema>.<tabname>"
https://www.ibm.com/docs/en/db2/11.5?topic=commands-export
5. Backup DDL from target database
a. Capture target database DDL
db2look -d <tgt_db> -createdb -printdbcfg -e -a -x -o /<path>/<tgt_db>_ddl.sql
https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2look-db2-statistics-ddl-extraction-tool
For example:
$ db2look -d wctst3 -createdb -printdbcfg -e -a -x -o /usr/opt/app/dbdump/tst3ins3/wctst3_ddl.sql
-- Generate statistics for all creators
-- Creating DDL for table(s)
-- Output is sent to file: /usr/opt/app/dbdump/tst3ins3/wctst3_ddl.sql
b. Capture target database grant statements
db2look -d <tgt_db> -x -o /<path>/grants.sql
https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2look-db2-statistics-ddl-extraction-tool
For example:
[tst3ins3@dev601 ~]
$ db2look -d wctst3 -x -o /usr/opt/app/dbdump/tst3ins3/wctst3_grants.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: TST3INS3
-- Output is sent to file: /usr/opt/app/dbdump/tst3ins3/wctst3_grants.sql
c. Capture role creation statements
grep "CREATE ROLE" ${refresh_path}/<tgt_db>_ddl.sql > /<path>/create_roles.sql
For example:
$ grep "CREATE ROLE" /usr/opt/app/dbdump/tst3ins3/wctst3_ddl.sql > /usr/opt/app/dbdump/tst3ins3/wctst3_create_roles.sql
[tst3ins3@dev601 ~]
$ ls -lrt /usr/opt/app/dbdump/tst3ins3/wctst3_create_roles.sql
-rw-r--r-- 1 tst3ins3 db2iadm1 96 Mar 22 17:22 /usr/opt/app/dbdump/tst3ins3/wctst3_create_roles.sql
[tst3ins3@dev601 ~]
$ cat /usr/opt/app/dbdump/tst3ins3/wctst3_create_roles.sql
CREATE ROLE "TIBCO ";
CREATE ROLE "TB_S ";
CREATE ROLE "HVR ";
CREATE ROLE "DB2READ ";
6. Create the restore script
db2 -v "restore db <src_db> from /<path> taken at <backup_timestamp> into <tgt_db> redirect generate script /<path>/<tgt_db>_restore.sql without prompting"
https://www.ibm.com/docs/en/db2/11.5?topic=commands-restore-database
For example:
$ db2 -v "restore db wcprd1 from /usr/opt/app/dbdump/tst3ins3 taken at 20220322165717 into wctst3 redirect generate script /usr/opt/app/dbdump/tst3ins3/wctst3_restore.sql without prompting"
restore db wcprd1 from /usr/opt/app/dbdump/tst3ins3 taken at 20220322165717 into wctst3 redirect generate script /usr/opt/app/dbdump/tst3ins3/wctst3_restore.sql without prompting
DB20000I The RESTORE DATABASE command completed successfully.
7. Edit the restore script
# Uncomment these lines and set as follows
# This is the path for storing the database files
a. Line 15: ON '/rd100/db2data'
# The path is used to store archive logs from backup for rolling forward
# Replace </path> with the desired path
b. Line 18: LOGTARGET '</path>'
e.g: LOGTARGET '/usr/opt/app/dbdump/tst2ins2'
# This is the path for active logs (the instance name will be appended by db2)
c. Line 19: NEWLOGPATH '/rd300/db2logs/instance_name'
e.g: NEWLOGPATH '/rd300/db2logs/tst3ins3'
8. Drop the target database
a. Terminate your own connection to the target database
db2 terminate
b. Force off any other connections to the target database
db2 force applications all
c. Deactivate the target database
db2 deactivate db <tgt_db>
https://www.ibm.com/docs/en/db2/11.5?topic=commands-deactivate-database
d. Drop the target database
db2 drop db <tgt_db>
https://www.ibm.com/docs/en/db2/11.5?topic=commands-drop-database
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[tst3ins3@dev601 tst3ins3]
$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[tst3ins3@dev601 tst3ins3]
$ db2 deactivate db wctst3
DB20000I The DEACTIVATE DATABASE command completed successfully.
[tst3ins3@dev601 tst3ins3]
$ db2 drop db wctst3
DB20000I The DROP DATABASE command completed successfully.
[tst3ins3@dev601 tst3ins3]
9. Restore the source database into the target database
db2 -tvf <restore_script.sql> | tee <output.file.txt>
$ db2 -tvf wctst3_restore.sql | tee wctst3_restore_output.txt
UPDATE COMMAND OPTIONS USING S ON Z ON WCPRD1_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
RESTORE DATABASE WCPRD1 FROM '/usr/opt/app/dbdump/tst3ins3' TAKEN AT 20220322165717 ON '/rd100/db2data' INTO WCTST3 LOGTARGET '/usr/opt/app/dbdump/tst3ins3' NEWLOGPATH '/rd300/db2logs' REDIRECT WITHOUT PROMPTING
SQL1277W A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
RESTORE DATABASE WCPRD1 CONTINUE
DB20000I The RESTORE DATABASE command completed successfully.
[tst3ins3@dev601 tst3ins3]
10. Rollforward the target database
# The overflow log path is the same path used for the logtarget in the restore script
db2 "rollforward db <tgt_db> to end of backup and complete overflow log path ('<logtarget>')"
https://www.ibm.com/docs/en/db2/11.5?topic=commands-rollforward-database
$ db2 "rollforward db wctst3 to end of backup and complete overflow log path ('/usr/opt/app/dbdump/tst3ins3')"
Rollforward Status
Input database alias = wctst3
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2022-03-22-22.00.18.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[tst3ins3@dev601 tst3ins3]
Connect to database:
[tst2ins2@dev601 ~]
$ db2 connect to wctst2
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = TST2INS2
Local database alias = WCTST2
11. Run post restore grants on target database
a. Run role creation statements
db2 -tvf <create_roles.sql> | tee <output.file.txt>
$ db2 -tvf wctst3_create_roles.sql | tee wctst3_create_roles_output.txt
CREATE ROLE "TIBCO "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "TIBCO" of type "ROLE". SQLSTATE=42710
CREATE ROLE "TB_S "
DB20000I The SQL command completed successfully.
CREATE ROLE "HVR "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "HVR" of type "ROLE". SQLSTATE=42710
CREATE ROLE "DB2READ "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "DB2READ" of type "ROLE". SQLSTATE=42710
b. Run grant statements
db2 -tvf <grants.sql> | tee <output.file.txt>
$ db2 -tvf wctst3_grants.sql | tee wctst3_grants_output.txt
CONNECT TO WCTST3
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = TST3INS3
Local database alias = WCTST3
GRANT SELECT ON TABLE "WCS "."ACACGPDESC" TO ROLE "DB2READ "
DB20000I The SQL command completed successfully.
GRANT SELECT ON TABLE "WCS "."ACACTACTGP" TO ROLE "DB2READ "
DB20000I The SQL command completed successfully.
GRANT SELECT ON TABLE "WCS "."ACACTDESC" TO ROLE "DB2READ "
DB20000I The SQL command completed successfully.
12. Reload any table data from step 4
# These statements can be very specific to the table being loaded
# The generic statement provided here is only a starting place
db2 -v "load from /<path>/<tabname>.del of del lobs from </path> replace into <tabschema>.<tabname> nonrecoverable"
https://www.ibm.com/docs/en/db2/11.5?topic=commands-load
example:
[dev2ins2@dev601 dev2ins2]
$ db2 -v "load from /usr/opt/app/dbdump/dev2ins2/STORECONF.del of del lobs from /usr/opt/app/dbdump/dev2ins2 replace into wcs.STORECONF nonrecoverable"
load from /usr/opt/app/dbdump/dev2ins2/STORECONF.del of del lobs from /usr/opt/app/dbdump/dev2ins2 replace into wcs.STORECONF nonrecoverable
SQL3109N The utility is beginning to load data from file
"/usr/opt/app/dbdump/dev2ins2/STORECONF.del".
SQL3500W The utility is beginning the "LOAD" phase at time "09/23/2022
15:21:27.561392".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "351" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "351".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "09/23/2022
15:21:27.684121".
SQL3500W The utility is beginning the "BUILD" phase at time "09/23/2022
15:21:27.687299".
SQL3213I The indexing mode is "REBUILD".
SQL3515W The utility has finished the "BUILD" phase at time "09/23/2022
15:21:27.770999".
Number of rows read = 351
Number of rows skipped = 0
Number of rows loaded = 351
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 351
You have new mail in /var/spool/mail/dev2ins2
[dev2ins2@dev601 dev2ins2]
13. Mask PCI data
# These are sql statements provided by the app/dev team
SQL1024N A database connection does not exist. SQLSTATE=08003
[dev2ins2@dev601 ~]
$ db2 connect to wcdev2
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = DEV2INS2
Local database alias = WCDEV2
14. Verify referential integrity
a. Check for tables in "check pending" status
# You will want to direct this output to file for reference on step 'b'
db2 -x "select tabschema, tabname from syscat.tables where status = 'C' order by parents"
b. Fix referential integrity on tables in "check pending" status
# For each table do the following
# The exception table is <tabname>_EXCEPT
i. Create exception table for storing data the violates referential integrity
db2 "create table <exception_table> like <tabschema>.<tabname>"
https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table
ii. Alter the exception table to include reason code
db2 "alter table <exception_table} add column X_TS TIMESTAMP add column X_REAS CLOB(32k)"
https://www.ibm.com/docs/en/db2/11.5?topic=statements-alter-table
iii. Set integrity of table
db2 "set integrity for <tabschema>.<tabname> immediate checked for exception in <tabschema>.<tabname> use <exception_table>"
https://www.ibm.com/docs/en/db2/11.5?topic=statements-set-integrity
iv. Check for unexpected violations in exception table
# Unexpected exceptions should be reviewed by the appropriate party
db2 -x "select count(*) from <exception_table> where char(substr(X_REAS,6,1)) not in ('I','F')"
v. Drop the exception table if there are not unexpected violations
db2 "drop table <exception_table>"
https://www.ibm.com/docs/en/db2/11.5?topic=t-dropping-tables
vi. Repeat steps for next table
c. Repeat these steps until step 'a' finds 0 tables in "check pending" status
OR
Go to /usr/opt/app/dbdump/db_refresh and run the following script:
fix-check-pending.sh
$ ./fix-check-pending.sh -d wcdev3
---------------------------------------------------------------------
There are 1 tables to check [ Pass 1 of 10 ]
---------------------------------------------------------------------
set integrity for WCS.STORECONF immediate checked
DB20000I The SQL command completed successfully.
If an error like the following:
set integrity for WCS.STORECONF immediate checked
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL3603N Integrity processing through the SET INTEGRITY statement has found
an integrity violation involving a constraint, a unique index, a generated
column, or an index over an XML column. The associated object is identified by
"WCS.STORECONF.F_3716". SQLSTATE=23514
RUN the following:
[tst4ins4@db2tst601 db_refresh]
$ db2 "SET INTEGRITY FOR WCS.STORECONF ALL IMMEDIATE UNCHECKED"
DB20000I The SQL command completed successfully.
[tst4ins4@db2tst601 db_refresh]
$ ./fix-check-pending.sh -d wctst4
There are no tables in check pending state.
[tst4ins4@db2tst601 db_refresh]
15. Validate the target database
a. Connect to target database
db2 connect to <tgt_db>
b. Check tablespace states
# All tablespaces should be in state 0x0000
db2 list tablespaces | grep State
c. Check that database is queryable
db2 "select * from syscat.bufferpools"
d. Check profile registry settings
db2set -all
https://www.ibm.com/docs/en/db2/11.5?topic=variables-registry-environment
Comments
Post a Comment