#! /usr/bin/ksh
#
+-----------------------------------------------------------------------+
# | Program ID: ms.sh
# |
# | Purpose: Put the data into the production table
by:
# | 1. FTPing the file from the vax
# | 2. Converting to uppercase for
BOA searches
# | 3. Drop the 'PRV' work table
# | 4. Rename the 'CUR' work table
to 'PRV'
# | 5. Create the 'CUR' work table
# | 6. Run SQL*Loader to load the
'CUR' work table from the
# | vax file & show errors
# | 7. disable_indexes_nonunique
(table_name)
# | 8. disable_constraints
(table_name, 'R')
# |
9. disable_constraints
(table_name, 'P')
# | 10. truncate production table
# | 11. insert/select from the
current week
# | 12. insert/select from the
previous week
# | 13. rebuild_indexes_nonunique(table_name)
# | 14.
enable_constraints(table_name)
# | 15. print status that shows
counts, indexes and constraints
# |
# | Programmer: Lynn C. Tobias
# | Program Date: 03/23/2003
# |
# | Input File(s): ~/.netrc contains ftp macros
# | master_list_###.fnl2 from vax
# | master_list.upp input to production tables
# |
# | Table(s) Used: Master_List production table build
# | Work_Master_List_PrvWk work table with last week's data
# | Work_Master_List_CurWk work table with this week's data
# | Xref_Period to get current period
# | All_Table has table information
# |
# | Called by:
# | Calls: datadict.sql format Oracle dictionary information
# | crmscur.sql Create Master_List current week table
# | countsv.sql Count records by version and client
# | indexsv.sql Show pertinent index information
# | cnstrsv.sql Show primary and foreign key
information
# |
# |
Common_Procs:
# | Disable_Indexes_Nonunique
# | Disable_Constraints
# | Truncate_Tbl
# |
# | Output: ms_drop_ren_cr_###.lst spool file for drop/rename/create
# | steps7_12.lst spool for for steps 7-12
# | statms.lst final status (counts, etc)
# |
# | Revisions: Prog Date Version Comment/Change
# | ---- -------- -------
---------------------------------+
# |
#
+-----------------------------------------------------------------------+
#
+-----------------------------------------------------------------------+
# | 1. FTP this file using the macro in the
~/.netrc file
# +-----------------------------------------------------------------------+
cd $datadir
echo "\$ ms" | ftp -vi lark
cd /db13/strview
#
+-----------------------------------------------------------------------+
# | 2. Convert all data to uppercase (easier
to search in BOA)
#
+-----------------------------------------------------------------------+
dd if=$datadir/master_list_$curper.fnl2
of=$datadir/master_list.upp conv=ucase
#
+-----------------------------------------------------------------------+
# | This can only be done once per week so
check for the spool file before
# | running the following SQL session.
# |
# | 3-5. Drop the table that's 2 weeks old.
Rename the current (which is now
# | 1 week old to 'PRV'. Create a current table to hold this week's
data.
#
+-----------------------------------------------------------------------+
if [ -e ms_drop_ren_cr_$curper.lst ];
then echo "Steps 3-5 have already been
run for this week";
else goto nodrop
fi
SqlPlus -s $schema << !
@Datadict
Set Echo On
Set Feedback On
/*-------------------------------------------------------*/
/* Get the period code to put in the spool
file name */
/*-------------------------------------------------------*/
Column PerCd New_Value xPerCd
Select To_Char(Period_Code-2) Percd
From Xref_Period
Where Trunc(Sysdate) Between Trunc(Begin_Date)
And Trunc(Closing_Date);
Spool ms_drop_ren_cr_&xPerCd..lst
/*----------------------------------------------------------*/
/* 3. Drop the table with data that's 2 weeks
old and check */
/*----------------------------------------------------------*/
Drop Table Work_Master_List_PrvWk;
Select Owner, Table_Name, Tablespace_Name,
Pct_Free, Pct_Used
From All_Tables
Where Table_Name Like 'WORK_MASTER_LIST_%'
And Table_Name Like '%WK';
/*-------------------------------------------------------*/
/* 4. Rename the current week's table to
'PRV' */
/*-------------------------------------------------------*/
Alter Table Work_Master_List_Curwk
Rename To Work_Master_List_Prvwk;
Select Owner, Table_Name, Tablespace_Name,
Pct_Free, Pct_Used
From All_Tables
Where Table_Name Like 'WORK_MASTER_LIST_%'
And
Table_Name Like '%WK';
/*-------------------------------------------------------*/
/* 5. Create a 'CUR' table for current week's
data */
/*-------------------------------------------------------*/
@CrMsCur
Select Owner, Table_Name, Tablespace_Name,
Pct_Free, Pct_Used
From All_Tables
Where Table_Name Like 'WORK_MASTER_LIST_%'
And Table_Name Like '%WK';
Spool Off
exit
!
nodrop:
#
+-----------------------------------------------------------------------+
# | Search for any errors in the file
#
+-----------------------------------------------------------------------+
grep "ORA-"
ms_drop_ren_cr_$curper.lst
#
+-----------------------------------------------------------------------+
# | List the file for the log
#
+-----------------------------------------------------------------------+
cat ms_drop_ren_cr_$curper.lst
# +-----------------------------------------------------------------------+
# | 6. Load data from the flat file to the
'CUR' table using the
# | information in the control file.
#
+-----------------------------------------------------------------------+
SqlLdr $schema Direct=True Control=ms.ctl
Data=$datadir/master_list.upp
#
+-----------------------------------------------------------------------+
# | List the '.bad' file is created -- this
needs to be checked.
# +-----------------------------------------------------------------------+
ls -ltrc ms.bad
#
+-----------------------------------------------------------------------+
# | Display key lines from the '.log' file
#
+-----------------------------------------------------------------------+
grep "Rows successfully loaded" ms.log
grep "Rows not loaded due to data
errors" ms.log
grep "Total logical records" ms.log
grep "Elapsed time" ms.log
#
+-----------------------------------------------------------------------+
# | Load the production table by disable
indexes and constraints, truncating
# | the current data, loading data from the
work tables to production.
# | The constraints and indexes are then put
back in place
# +-----------------------------------------------------------------------+
SqlPlus -s
$schema << !
Set Echo On
Set Feedback On
Spool steps7_12
Define SVPart
= MS
Define SVTable = Master_List
/*-------------------------------------------------------*/
/* 7. Disable_indexes_nonunique (Table_name)
/*-------------------------------------------------------*/
Exec Common_Procs.Disable_Indexes_Nonunique
('&SVTable');
/*-------------------------------------------------------*/
/* 8. Disable_Constraints (Table_Name, 'r')
/*-------------------------------------------------------*/
Exec
Common_Procs.Disable_Constraints('&SVTable','R');
/*-------------------------------------------------------*/
/* 9. Disable_Constraints (Table_Name, 'p')
/*-------------------------------------------------------*/
Exec
Common_Procs.Disable_Constraints('&SVTable','P');
/*-------------------------------------------------------*/
/* 10. Truncate Production Table
/*-------------------------------------------------------*/
Variable Outnum Number;
Execute Common_Procs.Truncate_Tbl
('&SVTable', :outnum);
Prompt
Prompt &SVTable Truncate Success:
Prompt 1 = Done
Prompt 0 = Error
Prompt
Print Outnum
/*-------------------------------------------------------*/
/* 11. Insert/select from the current week
/*-------------------------------------------------------*/
Alter Session Enable Parallel Dml;
Alter Session Set Skip_Unusable_Indexes=True;
Set Feedback On
Set Echo On
host date
Insert /*+Parallel(Master_List,2)*/
Into Master_List Nologging
Select /*+Parallel(Work_Master_List_Curwk,2)*/
*
From Work_Master_List_Curwk;
Commit;
host date
/*-------------------------------------------------------*/
/* 12. Insert/select from the previous week
/*-------------------------------------------------------*/
Insert /*+Parallel(Master_List,2) */
Into Master_List Nologging
Select /*+Parallel(Work_Master_List_Prvwk,2)
*/ *
From Work_Master_List_Prvwk;
Commit;
Select *
From Master_List
Where Rownum < 11;
host date
Spool Off
/*-------------------------------------------------------*/
/* 13. Rebuild_Indexes_Nonunique(Table_Name)
/*-------------------------------------------------------*/
Exec
Common_Procs.Rebuild_Indexes_Nonunique('&SVTable');
/*-------------------------------------------------------*/
/* 14. Enable_Constraints(Table_Name)
/*-------------------------------------------------------*/
Exec
Common_Procs.Enable_Constraints('&SVTable');
/*-------------------------------------------------------*/
/* 15. Print status that shows counts, indexes
and constraints
/*-------------------------------------------------------*/
@Datadict
Set Echo Off
Define Program = STAT&SVPart..Sql
Define Listing = stat&SVPart..lst
Spool stat&SVPart
@CountSV
@IndexSV
@CnstrSV
Spool Off
exit
!
