Re: On/Near-line Archiving - Specs

Pholks, Ok, I guess that's enough interest in the specs. This document is really a cross between specs and documentation. A note on our schema: Our primary system of interest (in this case) handles material lists (MLs). These are identified by a single record at an assembly level and then multiple revisions of that record. The two keys of interest are assembly_number and assembly_rev. All other table which tie to a revision are marked with these two key-fields. Thus our archive is designed to work off of any table which has these two fields. Whenever a new table is added to the schema - if it has these two fields it becomes part of the archive. Other note - we preserve two of the potentially archived tables. One is pic_assembly_revision - which is the revision level header record and contains a status field which notes that the revision has been archived/restored, the second is a history file which contains all of the events which have ever taken place against this ML. I have been through the 3000-odd lines of code which relate to archive/restore and can find nothing that gives away the farm - and quite a bit which can be re-used by others building their own archive subsystem. Therefore if I get permission (Mgr out till Monday) and y'all understand the word 'disclaimer' I'll post/mail them as appropriate and desired. MATERIAL LIST (ML) ARCHIVE Considerations: Schema: The schema may change between archives. Restoration must be possible - and easy - between schema versions. Site Independence: The process should be able to archive the local sites version of the ML database despite table or field variation from the norm. Automation: The Archive process should be able to complete satisfactorily without user intervention. Warnings: No Material List should be archived before its time. That is users must be able to stop an ML from being archived. What is archived? All tables which include the fields 'assembly_number' and 'assembly_rev' EXCEPT for material_log (for history) and pic_assembly_revs. A row is loaded into material_log denoting the pending and actual archive. THe assm_status in pic_assembly_revs is reset to 'ARCH'. Note: pic_assembly_info is NOT archived because it does not contain the field 'assembly_rev'. Variables: REQUIRED -a dir $ARCH_DIR - Final resting place for an archived ML. If it is not specified on the command line, an attempt is made to read the environment variable $ARCH_DIR. Defaulted or not required: -t dir A temporary directory used by the archive process. This will be created if it does not exist. It will default to $HOME/temp. WARNING: THE ARCHIVE PROCESS 'OWNS' THIS DIRECTORY AND WILL REMOVE ALL FILES FROM IT. If it is not specified on the command line, an attempt is made to read the environment variable $TMP_DIR. NOTE: we now use -t$$ which creates a temp directory in /tmp/arch.$$ or /tmp/rest.$$ -g log_file This is were all error messages are written. In the event that the archive process is running in non- interactive mode, all messages will be directed here. It is defaulted to /tmp/arch_log -b If specified this will prevent messages from being directed to the screen. Default is Interactive (-l) indicates whether transaction logging is available. If available, the unload, compression, storing, and storage of the ML will all be protected within a transaction. THIS IS RECOMMENDED. Default is NO LOGGING. NO LONGER AN OPTION, DATABASE IS QUERIED TO DETERMINE IF LOGGING IS AVAILABLE AND IF IT IS, IT IS USED. -s n Where n is a schema version to use. The default is the current schema. (see tables:arc_schema) -d database Will direct the Archive process to point to a specific database. The default is xxxx. -f Display ongoing status. During the process a form will be displayed with statistics on compression of each table within the ML. This will slow things down marginally since statistics are computed and displayed. Note: this turns on Interactive mode (as opposed to batch). -h Don't use material_log. We require that all ML transactions be logged to material_log. Since some sites may not have this table, the -h will prevent this logging. -n Don't delete the archived data. This preserves the data within the Informix database but still generates the archive files. This is useful for testing, and potentially for export reasons. If not deleted, the ML status is not reset to ARCH. A history record is still written to material_log. -z n This turns on a 'sleep n', where n is a number of seconds to sleep, after the display of compression data for each table. This is made available for testing since normally the compression information is too rapid to read usefully Tables: arc_schema This is a copy of the schema of the archived ML. This schema allows the restore process to figure out how to restore an ML which was archived before a schema change. This also allows a site to archive MLs in a specific format. If this table does not exist it is created by the archive process. This schema is generated from the SQL system catalogs. For all tables which include the field 'assembly_rev'. schema_header This table is used to store a description for a schema. It may be left blank, but may be useful to keep track of particular schemas. This table is used in schema selection by the user and in schema reports. assy_retention Each ML which is in obsolete status is subject to archive. At the same time retention of these MLs may be desired. The archive process selects all MLs in 'OBSL' status for archive. Before performing the archive, it writes the data to this table and marks the date of archive for this ML to be five days from the current date. A warning is then generated in the logfile, to the screen, and, for BSMC, through ML_Flagging. This allows a user or administrator to manually reset the Delete Date field to a higher value and allow retention of the ML. A second purpose is the WARN_FLAG. An ML cannot be deleted if this flag is set to anything but 'Y'. This is to indicate that a warning has not been generated for this ML. When the list of MLs is read from this archive (all those whose DELETE DATE is less than today), this flag is checked. If the flag is set to 'N' (for example) a warning will be generated, as mentioned previously, the flag will be set to 'Y' and the DELETE DATE will be set forward 5 days. If this table does not exist, it is created and loaded. The archive process loads this table each time with new MLs subject to archival. Table Access Access to all of these tables is provided from the Archive menu. Logic: All user variables are collected. AR