Contact & password data in all CAWIS Non-Production regions should be maintained as is outlined below to ensure no real-world clients are contacted during MDM or EDM testing.
These conditions need to be revisited after every DBA migration of data from Production to the DEV/ACC/PreACC/Training regions, or following any inadvertent execution of the CCARCS/CAWIS synchronization routines (which pulls Prod data) into those regions.
see : PBI-125292
NOTE : It is strongly recommended that the DBA be requested to CLONE Production into the desired target region, rather than copy. Refresh attempts tend to fail on timeouts (too much production data changes during the copy) or conflicts with nightly backups otherwise. CAWIS contains a great volume of data and has a great deal of production data activity. (AAIR and SDR submissions etc…)
BATCH JOBS
ENSURE that any automated triggers for Batch jobs (CCARCS-CAWIS sync, Project Notifications) are inactive in all regions except Production. If the synchronization routine is run in Non-Production region, it can do a great deal of damage to the test data.
DB-LINKS
Ensure all DBLinks are pointed to the correctly corresponding region after a test region refresh
DEV to DEV,
ACC to ACC etc…
REGION-SPECIFIC DATA BACKUPS
Backup the following data into TXT files and attach to the PBI before beginning the refresh (or clone)
TR47_CAWIS_GENERAL_PROPS needs to be backed up from the target region, then restored afterward
SELECT
'UPDATE TR47_CAWIS_GENERAL_PROPS SET PROPERTY_VALUE = ''' ||
PROPERTY_VALUE || ''' WHERE PROPERTY_NAME = ''' ||
PROPERTY_NAME || ''' ; ' AS UPD1
FROM TR47_CAWIS_GENERAL_PROPS ;
GENERIC SYSTEM ACCOUNT PASSWORDS
The system account passwords need to be backed up from Production and applied into the target region. A list of “system” accounts is found in the Z980 table with Term_type_cd = ‘SYSID’
SELECT A01.USER_ID,
'UPDATE A01_USER SET PASSWORD = ‘'' || REPLACE(A01.PASSWORD,'''','''''') ||
''' WHERE USER_ID = ‘'' || A01.USER_ID || '’’ ;' UPD1
FROM A01_USER A01
WHERE A01.USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = 'SYSID') ;
AFTER A TEST REGION DATA REFRESH, PERFORM THE FOLLOWING TASKS
PASSWORDS
1)Passwords on all Userids are to be set to “XXXXXX”
UPDATE A01_USER A01 SET PASSWORD = 'jV1Vz@<=lz'
WHERE A01.USER_ID NOT IN
(SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = ‘SYSID’) ;
/* Apply the Production passwords for all SYSTEM Userids afterward */
2) Owner AAIR Access code/passwords are to be set to “XXXX1111”
UPDATE Y50_PARTY SET ACCESS_CODE = '37328B5E590E26C946B75A91E26B33B2'
WHERE CLIENT_ID > 0 ;
/* only the owners have client_ids on the Y50 table */
CONTACT DATA
4) All FAX numbers are to have their area codes replaced with 000-
SELECT * FROM A92_ADDRESS_PROPERTY
WHERE SUBSTR(ADDRESS_PROPERTY_TXT,1,3) <> '000' AND ADDRESS_PROPERTY_TYPE_CD = 2 ;
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = '000-' || SUBSTR(ADDRESS_PROPERTY_TXT,5,8)
WHERE SUBSTR(ADDRESS_PROPERTY_TXT,1,3) <> '000' AND ADDRESS_PROPERTY_TYPE_CD = 2 ;
5) All Email addresses are to be suffixed with "X"
SELECT * FROM A92_ADDRESS_PROPERTY
WHERE ADDRESS_PROPERTY_TXT LIKE '%@%' AND ADDRESS_PROPERTY_TXT NOT LIKE '%X' ;
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = UPPER(ADDRESS_PROPERTY_TXT) || 'X'
WHERE ADDRESS_PROPERTY_TXT LIKE '%@%' AND ADDRESS_PROPERTY_TXT NOT LIKE '%X' ;
/* ensure CAWIS admin’s email remains unmarked */
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = 'LAUREN.MCMAHON@TC.GC.CA'
WHERE ADDRESS_PROPERTY_TXT = 'LAUREN.MCMAHON@TC.GC.CAX' ;
6) ENSURE THAT CONTINUING AIRWORTHINESS ACCOUNTS REMAIN ACTIVE AND HAVE ADMIN ROLE CODES
The Userid’s for all members of Continuing Airworthiness are found on the Z980 table where Term_type_cd = ‘CAW’
UPDATE A01_USER SET ACTIVE_IND = 'Y'
WHERE ACTIVE_IND = 'N'
AND USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'CAW');
UPDATE A103_USER_ROLES SET CAWIS_ROLE_CD = 'AU'
WHERE CAWIS_ROLE_CD = 'NS'
AND USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'CAW');
7) Check PINS
Review all logins to ensure that the connection strings for that region are still valid.
Note : Its normal to find a small amount of records, with legitimate TC-email, or TC-employee personal emails, or TC cell phone numbers in the FAX fields at any given time. This is how data conditions are setup for MDM/EDM testing.
KM - 2022-08-10
Add Comment