Creating Test Environments

1. Creating Test Environments Technology SIG Open Mic February 2011

2. Texas Instruments Environment • PeopleSoft 8.9 • PeopleTools 8.48 • Unix Solaris 9 (databases) • Unix Solaris 10 (Web and application servers) • Oracle 9i • Hitachi HDS Array for database storage • Processes described require DBA experience, privileges to the Oracle ID and root access on the UNIX servers.

3. Texas Instruments Full Database • Hitachi HDS array “shadow” image copy process • Creates an exact replica of the production database at the file system level • Typically have a single PeopleSoft schema per database • All shadowed schema’s have the same Oracle Owner (and OWNERID in psdbowner), but different passwords • Subsequent steps are taken to make the shadowed schema a “test” environment • Complete process takes approximately 4-6 hours of dedicated effort

4. Full Database – Prior to Shadow • Determine if any setup or disk group changes have occurred that could cause the Shadow copy to fail • Export any data on the target environment that needs to be saved • Tools tables • Operator security tables (use Data Mover scripts) • Test operator IDs • COBOL SQLstmt table • Special Oracle grants or other privileges • Stop the application server(s), process scheduler(s) and any UNIX cron processes on the target

5. Full Database – Following Shadow On the newly shadowed image copy • Change sys and system passwords • Modify dbid and update local spfile • Register instance to rman • Truncate and recreate rows in psdbowner • Change Oracle ID passwords • Refresh any data saved prior to the shadow

6. Full Database – Following Shadow • Operator Security • Perform security updates to change the symbolicid, accessid, and access password. This will retain the oprid and passwords from the source database. Run in Data Mover, bootstrap update psstatus set ownerid = '{SYMBOLICID}'; update psaccessprfl set symbolicid = '{SYMBOLICID}'; update psoprdefn set symbolicid = '{SYMBOLICID}'; update psaccessprfl set accessid = '{SYMBOLICID}'; update psaccessprfl set encrypted = 0; grant select on psstatus to people; grant select on psoprdefn to people; grant select on psaccessprfl to people; CHANGE_ACCESS_PASSWORD {SYMBOLICID} {NEWPASSWORD}; ** See presentation notes page or PeopleBooks for command details

7. Full Database – Following Shadow • PeopleTools 8.48.09 bug work-around • In SQL*Plus, update psaccessprfl set accessid = ‘<oracle ownerid>', accesspswd = '<ownerid pswd>'; • Commit your change • Log on Application Designer 2-tier (ORACLE) to get the accesspswd reset properly • In SQL*Plus, select * from psaccessprfl; • Make sure the accessid, accesspswd are encrypted and the encrypted flag = 1

8. Full Database – Following Shadow • Import any test operator IDs saved off earlier • Update ps_wl_template_gen to concatenate the test database name to the front of the wl_subject field • Reimport any Oracle grants or other privileges • Sync runstatus in the PRPRCSQUE and PSPRCSRQST tables * see presentation notes for details

9. Full Database – Following Shadow • Update any hard-coded values in the following tables • PSNODECONPROP • PSSTATUS • PSURLDEFN • PSWEBPROFNVP • PS_CDM_DIST_NODE • PS_INSTALLATION • PS_PRCSTYPEDEFN • Company developed tables

10. Full Database – Following Shadow • Avoid thousands of security errors in the SYSAUDIT report, by running the following SQL DELETE FROM PSROLECLASS WHERE NOT EXISTS (SELECT 'X' FROM PSROLEDEFN B WHERE B.ROLENAME = PSROLECLASS.ROLENAME)

11. Full Database – Following Shadow • Determine whether or not to cancel any Oracle DBMS scheduled jobs • Determine whether or not to drop or change any database links • Determine if any database synonyms need to be dropped or recreated • Delete CACHE files for Web, Application and Process Schedulers • Bring up app server(s)

12. Full Database – Following Shadow • Determine impact on batch environment, SQRs, COBOL, etc • Integration Broker • Modify IB Service Configuration • Modify IB gateways • Rename default local node • Update the Installation table's Application Server name • Bounce the web server and restart process schedulers • Run alter audit, dddaudit and sysaudit • Restart cron

13. Texas Instruments Partial Database • Creates a copy of the production database that contains a subset of the employee population • Subsequent steps are taken to make the database a test environment • Complete process takes approximately 8 hours of dedicated effort

14. Texas Instruments Selective Database • Multi-step process to build a test environment with a subset of the employee population • Employee data – tables containing employee details • Tools data – PeopleTools tables • Prompt table data – non-employee detail tables • Long raw – special handling for tables with this column type • Large payroll tables - tables containing emplid column and substantial amounts of historical data. TI determined that it's not necessary to copy all history data for our test needs • Subsequent steps are taken to make the PeopleSoft schema a “test” environment • All code samples are based on Oracle 9i database and PeopleTools 8.48

15. Partial Database – Prior to Copy • Export any data on the target environment that needs to be saved • Tools tables • Operator security tables (Data Mover) • Test operator IDs • COBOL SQLstmt table • Special Oracle grants or other privileges • Stop the application server(s), process scheduler(s) and any cron processes • Create new schema or drop all objects (tables, views, triggers, etc) in the target schema

16. Partial Database – Copy process • Identify the desired employee population for the target environment • Populate a temporary database table with the emplid of each employee to include in the partial database copy • Be sure to include any org rollup employees, report-to structures that may need to be in place • TI custom application in PeopleTools to store SQL queries that can be executed to build the employee population • Identify tables that have emplid column, but do not contain employee level data • Identify large employee tables that historical data is not required

17. Partial Database – Copy Process • Export the table and index ddl from the “source” schema using oracle export grants=n indexes=y rows=n compress=n direct=y recordlength=16384 owner=source • Import the ddl into the “target” schema to generate the create ddl using oracle import rows=n analyze=n grants=n fromuser=source touser=target • Optional: split the create ddl into tables/indexes; change size allocations to reflect smaller schema • Execute ddl to create tables/indexes in the target schema • You now have an empty copy of the “source” schema with the same table/index definitions

18. Partial Database – Copy Process • Identify the employee data tables SELECT TAB.table_name FROM dba_tables TAB, dba_tab_columns COL WHERE COL.column_name = 'EMPLID' AND COL.table_name = TAB.table_name AND TAB.owner = UPPER(source_owner) AND NVL(TAB.num_rows,0) > 0 AND TAB.table_name LIKE 'PS\_%' ESCAPE '\' AND TAB.table_name NOT IN ('PS_ROLEXLATOPR', 'PS_PAY_EARNINGS','PS_PAY_LINE‘, {other tables to exclude}) AND TAB.table_name NOT IN (SELECT COL2.table_name FROM dba_tab_columns COL2 WHERE COL2.owner = TAB.owner AND COL2.data_type = 'LONG RAW') ORDER BY TAB.table_name;

19. Partial Database – Copy Process • Generate SQL script to populate the employee data tables OPEN emplid_tables_cur; FETCH emplid_tables_cur INTO tblname; IF emplid_tables_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No tables with column EMPLID have rows'); ELSE -- Create .sql file containing COPY commands output_file := UTL_FILE.FOPEN(ctl_directory_in, 'pspay_emplid.sql', 'W'); UTL_FILE.PUT_LINE(output_file, 'connect ' || source_owner || '/' || source_pswd); UTL_FILE.PUT_LINE(output_file, 'set long 2000000000'); UTL_FILE.PUT_LINE(output_file, 'spool pspay_emplid.lst'); UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); WHILE emplid_tables_cur%FOUND LOOP UTL_FILE.PUT_LINE(output_file, 'COPY TO ' || target_owner || '/' || target_pswd || '@' || target_instance || ' -'); UTL_FILE.PUT_LINE(output_file, ' APPEND ' || tblname || ' -'); UTL_FILE.PUT_LINE(output_file, ' USING SELECT SRC.* FROM ' || tblname || ' SRC, PS_TI_PSPAY_EMPLID EMP -'); UTL_FILE.PUT_LINE(output_file, ' WHERE SRC.emplid = EMP.emplid;'); FETCH emplid_tables_cur INTO tblname; END LOOP; UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); UTL_FILE.PUT_LINE(output_file, 'spool off'); END IF; CLOSE emplid_tables_cur;

20. Partial Database – Copy Process • Identify the prompt data tables CURSOR prompt_tables_cur IS SELECT table_name FROM dba_tables WHERE table_name LIKE 'PS\_%' ESCAPE '\' AND NVL(num_rows,0) > 0 MINUS SELECT table_name FROM dba_tab_columns WHERE column_name = 'EMPLID' AND owner = UPPER(source_owner) AND table_name NOT IN ('PS_ROLEXLATOPR', {other tables excluded in emplid query}) MINUS SELECT table_name FROM dba_tab_columns WHERE column_name IN ('COMPANY','PAYGROUP','PAY_END_DT','OFF_CYCLE') GROUP BY table_name HAVING count(*) = 4 MINUS SELECT table_name FROM dba_tables WHERE table_name IN ({misc tables to exclude because data is not needed}) MINUS SELECT table_name FROM dba_tab_columns WHERE data_type = 'LONG RAW' GROUP BY table_name;

21. Partial Database – Copy Process • Generate SQL script to populate the prompt data tables OPEN prompt_tables_cur; FETCH prompt_tables_cur INTO tblname; IF prompt_tables_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Prompt tables not found'); ELSE -- Create .sql file containing COPY commands output_file := UTL_FILE.FOPEN(ctl_directory_in, 'pspay_prompt.sql', 'W'); UTL_FILE.PUT_LINE(output_file, 'connect ' || source_owner || '/' || source_pswd); UTL_FILE.PUT_LINE(output_file, 'set long 2000000000'); UTL_FILE.PUT_LINE(output_file, 'spool pspay_prompt.lst'); UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); WHILE prompt_tables_cur%FOUND LOOP UTL_FILE.PUT_LINE(output_file, 'COPY TO ' || target_owner || '/' || target_pswd || '@' || target_instance || ' -'); UTL_FILE.PUT_LINE(output_file, ' APPEND ' || tblname || ' -'); UTL_FILE.PUT_LINE(output_file, ' USING SELECT * FROM ' || tblname || ';'); FETCH prompt_tables_cur INTO tblname; END LOOP; UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); UTL_FILE.PUT_LINE(output_file, 'spool off'); END IF; CLOSE prompt_tables_cur;

22. Partial Database – Copy Process • Identify the PeopleTools tables CURSOR tools_tables_cur IS SELECT table_name FROM dba_tables WHERE owner = UPPER(source_owner) AND NVL(num_rows,0) > 0 AND ((table_name LIKE 'PS%' AND table_name NOT LIKE 'PS\_%' ESCAPE '\') OR table_name IN ('PS_PAY_GARN_OVRD')) AND table_name NOT IN (SELECT COL.table_name FROM dba_tab_columns COL WHERE COL.owner = owner AND COL.data_type = 'LONG RAW') AND table_name <> 'PSAUDIT' ORDER BY table_name;

23. Partial Database – Copy Process • Generate SQL script to populate the PeopleTools tables OPEN tools_tables_cur; FETCH tools_tables_cur INTO tblname; IF tools_tables_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Tools tables not found'); ELSE -- Create .sql file containing COPY commands output_file := UTL_FILE.FOPEN(ctl_directory_in, 'pspay_tools.sql', 'W'); UTL_FILE.PUT_LINE(output_file, 'connect ' || source_owner || '/' || source_pswd); UTL_FILE.PUT_LINE(output_file, 'set long 2000000000'); UTL_FILE.PUT_LINE(output_file, 'spool pspay_tools.lst'); UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); WHILE tools_tables_cur%FOUND LOOP UTL_FILE.PUT_LINE(output_file, 'COPY TO ' || target_owner || '/' || target_pswd || '@' || target_instance || ' -'); UTL_FILE.PUT_LINE(output_file, ' APPEND ' || tblname || ' -'); UTL_FILE.PUT_LINE(output_file, ' USING SELECT * FROM ' || tblname || ';'); FETCH tools_tables_cur INTO tblname; END LOOP; UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); UTL_FILE.PUT_LINE(output_file, 'spool off'); END IF; CLOSE tools_tables_cur;

24. Partial Database – Copy Process • Identify the Payroll tables CURSOR pay_tables_cur IS SELECT table_name FROM dba_tables WHERE table_name IN ('PS_PAY_EARNINGS','PS_PAY_OTH_EARNS','PS_PAY_ONE_TIME', 'PS_PAY_DEDUCTION','PS_PAY_GARNISH','PS_PAY_TAX','PS_PAY_LINE', 'PS_PAY_DISTRIBUTN','PS_PAY_SPCL_EARNS','PS_PAY_TAX_OVRD','PS_PAY_PAGE') ORDER BY table_name;

25. Partial Database – Copy Process • Generate SQL script to populate the Payroll tables – part 1 OPEN pay_tables_cur; FETCH pay_tables_cur INTO tblname; IF pay_tables_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Pay tables not found'); ELSE -- Create .sql file containing COPY commands output_file := UTL_FILE.FOPEN(ctl_directory_in, 'pspay_paytbl.sql', 'W'); UTL_FILE.PUT_LINE(output_file, 'connect ' || source_owner || '/' || source_pswd); UTL_FILE.PUT_LINE(output_file, 'set long 2000000000'); UTL_FILE.PUT_LINE(output_file, 'spool pspay_paytbl.lst'); UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); WHILE pay_tables_cur%FOUND LOOP UTL_FILE.PUT_LINE(output_file, 'COPY TO ' || target_owner || '/' || target_pswd || '@' || target_instance || ' -'); UTL_FILE.PUT_LINE(output_file, ' APPEND ' || tblname || ' -'); IF tblname = 'PS_PAY_PAGE' THEN UTL_FILE.PUT_LINE(output_file, ' USING SELECT DISTINCT A.* FROM ' || tblname || ' A, -'); ELSE UTL_FILE.PUT_LINE(output_file, ' USING SELECT A.* FROM ' || tblname || ' A, -'); END IF; UTL_FILE.PUT_LINE(output_file, ' PS_PAY_CHECK C, -'); UTL_FILE.PUT_LINE(output_file, ' PS_TI_PSPAY_EMPLID B -' ); UTL_FILE.PUT_LINE(output_file, ' WHERE C.EMPLID = B.EMPLID -' ); UTL_FILE.PUT_LINE(output_file, ' AND C.COMPANY = A.COMPANY -'); UTL_FILE.PUT_LINE(output_file, ' AND C.PAYGROUP = A.PAYGROUP -'); UTL_FILE.PUT_LINE(output_file, ' AND C.PAY_END_DT = A.PAY_END_DT -'); UTL_FILE.PUT_LINE(output_file, ' AND C.OFF_CYCLE = A.OFF_CYCLE -'); UTL_FILE.PUT_LINE(output_file, ' AND C.PAGE_NUM = A.PAGE_NUM -');

26. Partial Database – Copy Process • Generate SQL script to populate the Payroll tables – part 2 IF tblname != 'PS_PAY_PAGE' THEN UTL_FILE.PUT_LINE(output_file, ' AND C.LINE_NUM = A.LINE_NUM -'); UTL_FILE.PUT_LINE(output_file, ' AND C.PAY_END_DT > ''31-DEC-2009'' -'); ELSE UTL_FILE.PUT_LINE(output_file, ' AND C.PAY_END_DT > ''31-DEC-2011'';'); END IF; IF tblname = 'PS_PAY_LINE' THEN UTL_FILE.PUT_LINE(output_file, ' AND C.PAYCHECK_NBR = decode(C.SEPCHK,0,0,C.PAYCHECK_NBR);'); ELSE IF tblname != 'PS_PAY_PAGE' THEN UTL_FILE.PUT_LINE(output_file, ' AND C.SEPCHK = A.SEPCHK;'); END IF; END IF; FETCH pay_tables_cur INTO tblname; END LOOP; UTL_FILE.PUT_LINE(output_file, 'SELECT TO_CHAR(SYSDATE,''DD-MON-YYYY HH24:MI:SS'') FROM dual;'); UTL_FILE.PUT_LINE(output_file, 'spool off'); END IF; CLOSE pay_tables_cur;

27. Partial Database – Copy Process • Identify the long raw tables CURSOR long_raw_tables_cur IS SELECT DISTINCT(a.table_name) FROM dba_tab_columns a WHERE a.owner = UPPER(source_owner) AND a.data_type = 'LONG RAW' AND a.table_name IN (SELECT b.table_name FROM dba_tables b);

28. Partial Database – Copy Process • Generate SQL script to populate the long raw tables OPEN long_raw_tables_cur; FETCH long_raw_tables_cur INTO tblname; IF long_raw_tables_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('LONG RAW tables not found'); ELSE -- Create exp paramater file output_file := UTL_FILE.FOPEN(ctl_directory_in, 'pspay_long_raw.par', 'W'); UTL_FILE.PUT_LINE(output_file, 'GRANTS=n'); UTL_FILE.PUT_LINE(output_file, 'INDEXES=y'); UTL_FILE.PUT_LINE(output_file, 'ROWS=y'); UTL_FILE.PUT_LINE(output_file, 'TABLES=('); WHILE long_raw_tables_cur%FOUND LOOP UTL_FILE.PUT_LINE(output_file, tblname || ','); FETCH long_raw_tables_cur INTO tblname; END LOOP; UTL_FILE.PUT_LINE(output_file, ')'); END IF; CLOSE long_raw_tables_cur;

29. Partial Database – Wrap Up • Run the four generated SQL scripts to copy the data from the “source” database to the “target” database. You optionally may want to use a different source for tools tables, if so, be sure this environment is at the same tools release and be sure to run a full alter audit after the data copies. • Follow the applicable steps from the Full Database process slides titled “Following Refresh” to make the environment a test environment.

30. Collaborate11 April 10-14, 2011 Orange County Convention Center Orlando, Florida, USA http://collaborate.oaug.org PeopleSoft Technology SIG Session Details Day: Sunday, April 10, 2011 Session ID: 4966 Time: 4:00 – 5:00 PM Room: W202A Presentation: Scott Schafer, Oracle PeopleTools 8.51 Test Framework