IBM SG24-6320-00 manual Database preparation SQL script

Draft Document for Review July 28, 2004 7:33 pm 6320ax02.fm

db2 -tvf pre.migration.sql > %logfile%

echo Disconnect db2 terminate

Database preparation SQL script

The SQL file executes the changes needed on the database needed to prepare the database for migration.

The pre.migration.sql file will perform the following tasks:

1.Clear MSGSTORE table (not to be used on production database, refer to Section 10.3, “Database preparation” on page 188 for more information)

2.Update status on ORDERS and ORDERITEMS table

3.Update administrators profile type

4.Remove roles for our administrators and add appropriate roles

5.Create master catalog

6.Update KEYS table for the targeted tables

The content of the pre.migration.sql file:

Example: C-3 Content of the pre.migration.sql file

--CLEAR MSGSTORE TABLE

--NOTE : MESSAGES IN PRODUCTION ARE DEALT WITH DIFFERENTLY delete from MSGSTORE;

--UPDATE STATUS FROM C TO S

update orders set status='S' where status='C'; update orderitems set status='S' where status='C';

--UPDATE ADMINISTRATORS (REGISTERTYPE IN ('A','S')

--AND THAT HAVE ENTRIES IN ACCMBRGRP TO HAVE PROFILETYPE='B'

update users set profiletype='B' where registertype in ('A','S') and (select count(*) from accmbrgrp where accmbrgrp.users_id=users.users_id)>1;

--USERS ADMIN#1 AND ADMIN#2 ARE USED BY THE CUSTOMER FOR ACCELERATOR

--THEY HAVE ROLE -3=CUSTOMER SERVICE REPRESENTIVE AND -5=ORDERCLERK AND -8=MERCHANDISING MANAGER

--REMOVE ALL THESE ROLES AND ADD -6=STORE ADMINISTRATOR IN THE POST MIGRATION

delete from accmbrgrp where users_id in (<users_id>);

-- ADD STORE ADMINISTRATOR ROLE TO THEM

insert into accmbrgrp values (-6,<users_id>,-2000,NULL); insert into accmbrgrp values (-6,<users_id>,-2000,NULL);

Appendix C. Migration scripts 263