DB2: Post DB refresh scripts

 db2 -v "export to /usr/opt/app/dbdump/dev4ins4/COLLDESC_2rows.del of del lobs to /usr/opt/app/dbdump/dev4ins4  select * from wcs.COLLDESC where collateral_id in ( select collateral_id from wcs.COLLATERAL where name like '%MO_CONFIGURATION_DATA_CONTENT%')"


db2 -v "export to /usr/opt/app/dbdump/dev4ins4/COLLDESC.del of del lobs to /usr/opt/app/dbdump/dev4ins4  select * from wcs.COLLDESC"


db2 -v "export to /usr/opt/app/dbdump/dev4ins4/STORECONF.del of del lobs to /usr/opt/app/dbdump/dev4ins4  select * from wcs.STORECONF"


db2 -v "export to /usr/opt/app/dbdump/dev4ins4/KEYS.del of del lobs to /usr/opt/app/dbdump/dev4ins4  select * from wcs.KEYS"



(1) Masking data after post db refresh.

truncate table wcs.xuseroptin immediate;

-- truncate table wcs.staglog immediate;  <-- only when refreshed from wcstg1

-- truncate table wcs.staglog_archive immediate; <-- only when refreshed from wcstg1

-- truncate table wcs.cacheivl immediate;  <-- only when refreshed from wcstg1.

truncate table wcs.xemailevent immediate;

update  wcs.ppcextdata set datavalue ='dummyLastName' where attributename = 'billto_lastname';

commit;

update  wcs.ppcextdata set datavalue ='dummyFirstName' where attributename = 'billto_firstname';

commit;

update  wcs.ppcextdata set datavalue ='123-123-1234' where attributename = 'billto_phone_number';

commit;

UPDATE wcs.ORDITEMEXTATTR SET VALUE = 'dummyRecepientEmail@dummyRecepientEmail.com' WHERE NAME ='RecepientEmail' AND TYPE='GIFTCARD_DETAILS';

commit;


Summary:-

(1) On The TST environment, run below query and capture the values

SELECT counter FROM wcs.keys WHERE tablename = 'orders';--400029200

SELECT counter FROM wcs.keys WHERE tablename = 'orderitems';--700055500

(2) Do the database refresh


(3) Run below queries

It has to be updated to the ‘before’ value + 500.

UPDATE wcs.keys set counter=(<<Get the value from the above and update here>> + 500) where TABLENAME='orders';

UPDATE wcs.keys SET counter = (<<Get the value from the above and update here>> + 500) WHERE TABLENAME = 'orderitems';

 

example:

[tst4ins4@db2tst602  ~]

$ db2 "SELECT counter FROM wcs.keys WHERE tablename = 'orderitems'"

COUNTER
--------------------
           687671622

  1 record(s) selected.

[tst4ins4@db2tst602  ~]
$ db2 "SELECT counter FROM wcs.keys WHERE tablename = 'orders'"

COUNTER
--------------------
               61300

  1 record(s) selected.


[tst4ins4@db2tst602  db_refresh]
$ db2 "SELECT counter FROM wcs.keys WHERE tablename = 'orderitems'"

COUNTER
--------------------
           615676071

  1 record(s) selected.

[tst4ins4@db2tst602  db_refresh]
$ db2 "SELECT counter FROM wcs.keys WHERE tablename = 'orders'"

COUNTER
--------------------
           305651610

  1 record(s) selected.

[tst4ins4@db2tst602  db_refresh]

UPDATE wcs.keys set counter=(61300 + 500) where TABLENAME='orders';
UPDATE wcs.keys SET counter = (687671622 + 500) WHERE TABLENAME = 'orderitems';

$ db2 "UPDATE wcs.keys set counter=(61300 + 500) where TABLENAME='orders'"

DB20000I  The SQL command completed successfully.

[tst4ins4@db2tst602  db_refresh]

$ db2 "UPDATE wcs.keys SET counter = (687671622 + 500) WHERE TABLENAME = 'orderitems'"

DB20000I  The SQL command completed successfully.

[tst4ins4@db2tst602  db_refresh]





STG:


db2 "truncate table wcs.staglog immediate"

db2 "truncate table wcs.staglog_archive immediate"

db2 "truncate table wcs.cacheivl immediate"


STG1 and PRD1

db2 "truncate table wcs.xuseroptin immediate"

db2 "truncate table wcs.xemailevent immediate"

db2 "update  wcs.ppcextdata set datavalue ='dummyLastName' where attributename = 'billto_lastname'"

db2 "update  wcs.ppcextdata set datavalue ='dummyFirstName' where attributename = 'billto_firstname'"

db2 "update  wcs.ppcextdata set datavalue ='123-123-1234' where attributename = 'billto_phone_number'"

db2 "update wcs.ORDITEMEXTATTR SET VALUE = 'dummyRecepientEmail@dummyRecepientEmail.com' WHERE NAME ='RecepientEmail' AND TYPE='GIFTCARD_DETAILS'"





(2) Back up these two rows and update after post db refresh. This seems to be challenging as this is text with double quotes.

 


select * from wcs.COLLDESC where collateral_id in ( select collateral_id from wcs.COLLATERAL where name like '%MO_CONFIGURATION_DATA_CONTENT%');



restoring two rows

These are the two rows:

$ cat /usr/opt/app/dbdump/dev4ins4/COLLDESC_2rows.del

715837980,-1,,,"COLL.del.001.lob.0.4604/",,,19

715837980,-2,,,"COLL.del.001.lob.4604.4604/",,,18


DELETE first:

$ db2 "delete from wcs.colldesc where COLLATERAL_ID=715837980"


This is the command to restore just two rows:

-- If there are double quotes in the data:

$ db2 -v "load from /usr/opt/app/dbdump/dev4ins4/COLLDESC_2rows.del of del lobs from /usr/opt/app/dbdump/dev4ins4  modified by delprioritychar insert into wcs.COLLDESC nonrecoverable"


-- If there are no double quotes in the data:

$  db2 -v "load from /usr/opt/app/dbdump/dev4ins4/COLL.del of del lobs from /usr/opt/app/dbdump/dev4ins4 insert into wcs.COLLDESC nonrecoverable" 



Maybe do a 'count' before and after the restore for verification:

$  db2 "select count(*) from wcs.COLLDESC"


1

-----------

       1639








(3) Backup  STORECONF and KEYS tables on lower environment  and restore it.


[dev4ins4@dev601  dev4ins4]

$ db2 -v "load from /usr/opt/app/dbdump/dev4ins4/STORECONF.del of del lobs from /usr/opt/app/dbdump/dev4ins4 replace into wcs.STORECONF nonrecoverable"

load from /usr/opt/app/dbdump/dev4ins4/STORECONF.del of del lobs from /usr/opt/app/dbdump/dev4ins4 replace into wcs.STORECONF nonrecoverable

SQL3109N  The utility is beginning to load data from file

"/usr/opt/app/dbdump/dev4ins4/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/dev4ins4

[dev4ins4@dev601  dev4ins4]





db2 -v "load from /usr/opt/app/dbdump/dev4ins4/KEYS.del of del lobs from /usr/opt/app/dbdump/dev4ins4 replace into wcs.KEYS nonrecoverable"




db2 "set integrity for wcs.colldesc immediate checked"  <-- use this only if the error happens




Clean up the tablespaces, the following in reverse order:


db2 alter tablespace userspace1 reduce max

db2 alter tablespace tab8k reduce max

db2 alter tablespace tab16k reduce max

db2 alter tablespace SYSTOOLSPACE reduce max

db2pd -d wcdev10 -extentmovement -repeat 3


 db2 list tablespaces show detail

db2 list tablespaces | grep -i name

 db2 list tablespaces                        <---- start here


db2 list tablespaces | more  <-- USERSPACE1 



Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more