Generating Data Archiving Scripts to Implement your Lifecycle

Once you have modeled your lifecycles, you can instruct PowerDesigner to generate scripts to automate the creation, movement, and purging of data through your lifecycle phases.

1. Select Database > Information Lifecycle Management > Generate Data Archiving Scripts to open the Generate dialog.
2. Specify a directory in which to generate the scripts, and, optionally, select to check your model before generation.
3. Click the Selection tab, and select the tables for which you want to generate data archiving scripts.
4. [for age-based lifecycles] Click the Options tab, specify the start and end date for the period for which you want to generate scripts. You can generate scripts for all or part of the period covered by your lifecycle, and also to cleanup data created before the start date of your lifecycle.
   Note: For age-based lifecycles used to archive data from an external database, if you specify a generation start date before the start date of a table associated with the lifecycle, additional scripts will be generated to advance immediately older data created between the generation start date and the table lifecycle start date to the appropriate stages of the lifecycle.
5. [for age-based lifecycles] On the Options tab, specify the method for creating partitions. You can choose between creating partitions:
   • Individually, when the previous partition ends
   • All at the beginning (default)
6. Click OK to begin the generation.

   The scripts are generated in the specified directory and listed in the Results pane.

   The following scripts are generated for age-based lifecyles, and should be run on the date specified in the order specified by their numerical prefix. You can run the scripts manually or use Sybase Control Center to automate this process:

   • IQ.CreateRemoteServerAndLogin.date.sql - if you are achiving data stored in an external database.
   • One or more folders named yyyymmdd for each date on which scripts must be run containing one or more of the following scripts:
     • 01.IQ.CreateAndMovePartition.date.sql - one script per date on which a data movement action is required between the start and end dates you specify. For example, if you specify a start date of 01/01/2009 and an end date of 12/31/2009, a partition range of one month, and to create the partitions individually, then twelve scripts will be generated. The scripts should be run on the dates included in their filenames.
     • 02.IQ.PurgePartition.date.sql - one script per date on which a data purge action is required for partitions arriving at the end of the lifecycle.
     • 03.DB.DeleteSourceData.date.sql - if there is data to be purged in an external database.
   • OldData - if you have specified a generation start date earlier than your table start dates, this folder will be created and will contain dated subfolders containing scripts to create, move, and purge older data.

   The following scripts are generated for access-based lifecyles:

   • CreateProcedures.sql - creates procedures to test the idle time during which tables have not been accessed and to move and/or delete them on demand. This script should be run immediately to prepare the database for data movements called for by an access-based lifecycle
   • MoveData.sql - calls the procedures to test for and implement data movement based upon the specified idle times using the current date on the IQ server. This script should be scheduled to run regularly.
   • DeleteData.sql - calls the procedure to test for and implement data purging based upon the specified idle times and the specified minimum retention period using the current date on the IQ server. You can schedule this script to run regularly or run it by hand as needed.