FPSADMIN.ADM_ALL_INFO_TO_COMPARE

>

> FPSADMIN.WIP_WAFER_HIST_LOOP

View FPSADMIN.ADM_ALL_INFO_TO_COMPARE

Use this view to compare two instances of the FPS Data Warehouse to ensure they match. This view gets all objects in all FPS schemas along with columns, dependencies, and even the number of characters in views and constraints. It is not possible to compile the PL/SQL code of functions, procedures, and triggers but this catches most discrepancies. Here are two ways to compare your database to the standard version. This is recommended after a major upgrade: EASY METHOD: If you are comparing to another database accessible over a database link: 1. Login as FPSADMIN in Toad or SQL Developer. 2. Run the query "select * from chk_all" and make sure there are no errors and all objects are valid. 3. Execute the procedure adm_prepare_to_compare. This takes 5-10 minutes. It compiles statistics on FPSADMIN objects and counts the number of non-whitespace characters in each view and constraint which we use in the comparison. 4. Run the following query below. If no rows returned, the two databases match. If rows, go through each difference and evaluate what caused it. This is often quite tricky. Start with the history comments if applicable as this will tell you if you are missing a newer version. Ask Cabe if you have questions. with old as ( select * from adm_all_info_to_compare ), new as ( select * from adm_all_info_to_compare@yourdblink ) select * from ( select 'new' as source, a.* from new a minus select 'new', a.* from old a union select 'old' as source, a.* from old a minus select 'old', a.* from new a ) order by 2, 3, 4, 5, 6, source desc; COMPLEX METHOD: If you are comparing your database to the standard version not accessible over a database link: 1. Login as FPSADMIN in Toad. You can do this in SQL Developer too but you have to choose the appropriate options in step #7 to match what is below for Toad. 2. Run the query "select * from chk_all" and make sure there are no errors and all objects are valid. 3. Execute the procedure adm_prepare_to_compare. This takes 5-10 minutes. It compiles statistics on FPSADMIN objects and counts the number of non-whitespace characters in each view and constraint which we use in the comparison. 4. Select * from adm_all_info_to_compare where is_custom_input = 'N'; 5. The is_custom_input = N filter removes custom APD/REF views, custom LOAD/APF/RTDLOAD objects, and CHGLOG tables/triggers in FPSINPUT which are different for each client. This leaves only the standard FPS objects in FPSADMIN/INPUT/BASE/APP which should match exactly at each site. If you wish to compare two instances at the same client like production and development then omit the is_custom_input filter. 6. Right click on the data and choose Export Dataset. 7. Choose format of Delimited Text, output of File to your preferred location, check only "Display all results" and "Include column headers" with others unchecked, don't quote strings or numbers, and delimiter of Pipe. Then click OK to save the file. 8. If you are comparing two instances at the same site like production and development then repeat steps 5-7 for the other instance. Then compare them to each other in steps 9 and 10 rather than the standard. This is recommended after a release to production. 9. Compare your file with the standard for the version saved in the Team -> DWH -> Compare folder. I prefer using the UNIX/Linux diff function so I copy (or remote copy) both files to a UNIX/Linux machine. This might be a server at the client or a Mac if you have one. Word or WinMerge are options too. I recommend saving the differences output to a new file which you can then view and edit separately. 10. Your differences file should be empty. If not, go through each difference and evaluate what caused it. This is often quite tricky. Start with the history comments if applicable as this will tell you if you are missing a newer version. Ask Cabe if you have questions.