Changes between Initial Version and Version 1 of effDate

Timestamp:

12/02/14 13:40:31 (10 years ago)

Author:

niles

Comment:

--

effDate

@@ +1 @@
+= Setting the effective date for selected sunums =
+
+Sometimes 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.
+
+It 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.
+
+A crude way to do this is with a bash script :
+
+{{{
+#!/bin/bash
+
+# Trap interrupts so CNTL-C works
+trap "exit 0" SIGHUP SIGINT SIGTERM
+
+# Command to access SUMS database, with -t for plain formatting (no header/trailer)
+# Will have to edit for each site
+sumsDB="psql -t -p 5434 -Uproduction nso_drms_sums"
+
+# File we will append to
+/bin/rm -f existingDates.dat
+
+while read sunum
+do
+
+ # See if we can get effective date for this sunum
+ eDate=`echo select effective_date from sum_partn_alloc where ds_index=$sunum\; | $sumsDB`
+
+ # Did we get a string with length greater than 0 - if so, have the data.
+ if [ ${#eDate} -gt 0 ]
+ then
+  # Save the date we had off to file.
+  echo $sunum $eDate >> existingDates.dat
+
+  # Overwrite the effective date to June of 2015.
+  echo update sum_partn_alloc set effective_date=\'201506011200\' where ds_index=$sunum\; | $sumsDB
+ fi
+
+done < SUM62.aia.lev1
+
+exit 0
+}}}
+
+This 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 :
+
+{{{
+249403665 201409131510
+249403687 201409131521
+249403689 201409131522
+249403692 201409131534
+263265147 201410152238
+263265152 201410152237
+263265317 201410152240
+263265704 201410152245
+}}}
+
+As 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.
+
+To 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 :
+
+{{{
+sdac_drms_sums=# CREATE TABLE SUM62 ( sunum INTEGER );
+sdac_drms_sums=# \copy SUM62 from /my/path/to/file/SUM62.aia.lev1
+}}}
+
+
+Then, create a table (named SUM62_found here) that lists the sunums that were found and the effective dates that currently apply, like so :
+{{{
+sdac_drms_sums=# create table SUM62_found as ( select ds_index,effective_date from sum_partn_alloc where ds_index in ( select * from SUM62 ) );
+}}}
+This 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.
+
+Finally, set the effective date for the sunums that were found locally :
+{{{
+sdac_drms_sums=# update sum_partn_alloc set effective_date='201506011200' where ds_index in ( select ds_index from SUM62_found );
+}}}
+
+The SUM62 and SUM62_found tables will have to be dropped at a later time.