Changes between Initial Version and Version 1 of effDate


Ignore:
Timestamp:
12/02/14 13:40:31 (9 years ago)
Author:
niles
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • effDate

    v1 v1  
     1= Setting the effective date for selected sunums = 
     2 
     3Sometimes it is desirable to retain data for longer than was originally anticipated. This may happen if there is a data loss upstream, and the remote NetDRMS sites are the only sites to retain some of the data. If this happens, a list of storage unit numbers (sunums) will be published in a file. For the purposes of this discussion, the file that lists the sunums that we would like to retain will be named "SUM62.aia.lev1". To retain the data for these sunums, it is necessary to set the effective date to be in the future. Since the sum_rm program considers data to be eligible for deletion after the effective date has elapsed, this will preserve the data for longer than originally intended. How long to retain the data for is dependent on the specifics of the situation. 
     4 
     5It is also highly desirable to save out the original effective date for the sunums, since that both lists the sumuns that were found (which will be a subset of the desired sunums) and may potentially allow the changes to be backed out in the future by reverting to the original effective dates for the sunums. 
     6 
     7A crude way to do this is with a bash script : 
     8 
     9{{{ 
     10#!/bin/bash 
     11 
     12# Trap interrupts so CNTL-C works 
     13trap "exit 0" SIGHUP SIGINT SIGTERM 
     14 
     15# Command to access SUMS database, with -t for plain formatting (no header/trailer) 
     16# Will have to edit for each site 
     17sumsDB="psql -t -p 5434 -Uproduction nso_drms_sums" 
     18 
     19# File we will append to 
     20/bin/rm -f existingDates.dat 
     21 
     22while read sunum 
     23do 
     24 
     25 # See if we can get effective date for this sunum 
     26 eDate=`echo select effective_date from sum_partn_alloc where ds_index=$sunum\; | $sumsDB` 
     27 
     28 # Did we get a string with length greater than 0 - if so, have the data. 
     29 if [ ${#eDate} -gt 0 ] 
     30 then 
     31  # Save the date we had off to file. 
     32  echo $sunum $eDate >> existingDates.dat 
     33 
     34  # Overwrite the effective date to June of 2015. 
     35  echo update sum_partn_alloc set effective_date=\'201506011200\' where ds_index=$sunum\; | $sumsDB 
     36 fi 
     37 
     38done < SUM62.aia.lev1 
     39 
     40exit 0 
     41}}} 
     42 
     43This will update the effective date for these data, and write a file named existingDates.dat that contains every sunum that was found and the original effective date (which is actually stored as a string in YYYYMMDDhhmm format), so that the file will look something like this : 
     44 
     45{{{ 
     46249403665 201409131510 
     47249403687 201409131521 
     48249403689 201409131522 
     49249403692 201409131534 
     50263265147 201410152238 
     51263265152 201410152237 
     52263265317 201410152240 
     53263265704 201410152245 
     54}}} 
     55 
     56As mentioned earlier, this is a *very* crude way to achieve this goal. It requires a database access for each sunum. It may not even be operable if the number of sunums is large. A much preferred methodology is to perform all operations within the database. 
     57 
     58To do that, follow these steps. First, in the database, read the list of sunums in the file SUM62.aia.lev1 into a table named SUM62 in the database : 
     59 
     60{{{ 
     61sdac_drms_sums=# CREATE TABLE SUM62 ( sunum INTEGER ); 
     62sdac_drms_sums=# \copy SUM62 from /my/path/to/file/SUM62.aia.lev1 
     63}}} 
     64 
     65 
     66Then, create a table (named SUM62_found here) that lists the sunums that were found and the effective dates that currently apply, like so : 
     67{{{ 
     68sdac_drms_sums=# create table SUM62_found as ( select ds_index,effective_date from sum_partn_alloc where ds_index in ( select * from SUM62 ) ); 
     69}}} 
     70This has the same information as the existingDates.dat file that the bash script wrote. Note that the sunum is stored in the ds_index column of the sum_partn_alloc table. 
     71 
     72Finally, set the effective date for the sunums that were found locally : 
     73{{{ 
     74sdac_drms_sums=# update sum_partn_alloc set effective_date='201506011200' where ds_index in ( select ds_index from SUM62_found ); 
     75}}} 
     76 
     77The SUM62 and SUM62_found tables will have to be dropped at a later time.