Writing of a specific migration procedure

Basic principles

On folder validation:

• each flow table is renamed (by default with the letter U before its name). The activity code MIG is assigned to each table (this will make it possible to identify them when the temporary tables are deleted, once the migration will have been validated).
• The content of these flow tables is kept strictly identical to the flow tables of origin.
• Their indices are deleted (except for the first index and possibly an additional one that may prove useful to speed up the migration processing).
• One or more flow table(s) are created empty according to the dictionary of the new version (are obviously included the specific fields or the specific modifications to standard fields made in the original table). The empty table often has the same name as the original table, but not always (it's possible that this table no longer exists in the new version. For instance, the data they contained were transferred to another table).

The migration of specific tables can be managed using the same mechanism. This is described in the paragraph below.

The following subprogram is able to determine the name of the table after it has been renamed:
     # FOLDER = code of folder
     # PTABLE = name of the movement table before migration
     # PTABLE = name of the renamed movement table
 # If ERR>0: an error has been generated (the processing will be interrupted)
     Call MIGTABNAME(FOLDER,PTABLE, MTABLE, ERR) From TRTMIG

The basic renaming rule (but there are exceptions, especially if the name of the table exceeds 12 characters) is to rename the table XXX by putting a "U" before its name. As an example, a table named ZMYTABLE will be renamed as UZMYTABLE. The entry point TABNAME in processing TRTMIG is used to assign the entry point MTABLE by defining PTABLE with a rule that can be different.

Each unitary procedure is defined using a program that migrates one or several tables in the base. This processing must be called UUMGSPExxxnn, with xxx as the root identifying the table to migrate and nn, two numbers (thus to enable the use of several processing routines). The operating method is as follows:

• the table(s) of origin is read (its name is found using the subprogram presented above) but it is not updated.
• The processing must write the modified, completed or transcoded data in the final table(s). These final tables have been described in the dictionary of the new version. These tables do exist but they should be empty at the beginning of the migration.
• The processing must have been designed so as to make it possible to divide the migration into parallelizable procedures.

In order to obtain the best possible performances:

• the final table is delivered empty at the beginning, and it is indexed (a single index). The other indices are created at the end of the unit procedure.
• The partial processing of the information of origin will be made possible (for example, only the last 2 fiscal years will be migrated), as will be the later processing of the non-migrated data.
• this table will be preferably written in grouped mode (using syntaxes Writeb), running Commit on the table for each stack of N entries (N being limited to a resasonable amount).
• The migration log will be updated.

In order for the unit procedures to be easily interrupted and resumed,

• on each Commit, a pointer will be written in a dedicated table to specify which data have already been processed, and provide some additional information.
• On each Commit, a test will be run to check whether an interruption request has been made.
• The procedure will be written so that it can be rerun starting from the empty destination tables.

Description of the migration processing

The following elements are contained in the migration processing:

• At the beginning, lines will enable the direct triggering of the procedure.
• A subprogram RAZ_UUMGSPExxxnn will make it possible to return to the initial situation in order to restart the procedure.
• A subprogram MAJ_UUMGSPExxxnn contains the migration procedure.
• A subprogram PATCH.
• A subprogram UTI_MOUL.

These subprograms are described below.

In the given examples, processing UUMGTRTZMY01 is supposed to be corresponding to the unit migration phase of a single table, called ZMYTABLE. The following events take place further to the preliminary folder revalidation phase:

• ZMYTABLE has been renamed, according to the rule defined above (the table populated with the pre-migration values is called UZMYTABLE by default).
• The new table ZMYTABLE has been created with the new structure, but this table is empty.

The migration procedure will populate table ZMYTABLE using the information in table UZMYTABLE.

Preliminary lines

The preliminary lines directly start the processing from the editor, using the Execution function. The following lines are found:

# Definition of the current folder, and read of the folder table
Local Char FOLDER(30): FOLDER = nomap
# Name of the migration procedure
Local Char PROG(20): PROG="UUMGTRTZMY01"
If !GSERVEUR
  Call SAIDOS(FOLDER,"") From SAIDOS
Endif
If FOLDER<> ""
  If clalev([F:ADS])=0: Local File ADOSSIER [ADS]: Endif
  Read [ADS] DOSSIER=[L]FOLDER: If fstat: Raz [F:ADS]: Endif
  # Opening of the log and display of a timing window
  If !GSERVEUR
    Call TEMPON("O") From GESECRAN
    Call OUVRE_TRACE(PROG) From LECFIC
  Endif
  # Launching of the procedure
  Call MAJ_UUMGSPEZMA00(FOLDER)
  # Closing the log
  If !GSERVEUR
    Call TEMPOFF     From GESECRAN
    Call FERME_TRACE From LECFIC
    Call LEC_TRACE From LECFIC
  Endif
Endif
End

Reset to zero subprogram

The subprogram declared by
Subprog RAZ_UUMGTRTZMY01(FOLDER)
is used to return to the initial situation, if need be, in order to relaunch the procedure.

The subprogram must at least include the following basic phases:

• Check that the original and destination tables do exist, for example, by means of the following lines:
        # Find the original table for the table to populate (here UZMYTABLE)
        Call MIGTABNAME(FOLDER,"ZMYTABLE",OLDTABLE,ERR) From TRTMIG
        If ERR: End: Endif
        # Does the table of origin really exist?
        If filinfo(filpath("FIL",OLDTABLE,"fde",[F:ADS]DOSSIER),0)<0
             End
        Endif
• Logs should only be generated for errors (no progress or warning message).
• Empty the table(s) where the update procedure inserts data. For that purpose, the following subprogram is available:
        # Sudden and irreversible dump of table ZMYTABLE
        Call MIGTABRAZ(FOLDER,"ZMYTABLE",ERR)
        If ERR: End: Endif
• If common tables with several processing routines must be partly purged, standard transactions will be used, of type
         Trbegin [...]: Delete [...] Where ... : Commit
• Reset to zero of the information on the already migrated flow for this table, using the following subprogram:
        # No migrated lines at the moment
        Call MIGRAZKEY(FOLDER,"UUMGTRTZMY01",ERR) From TRTMIG

Update subprogram

Subprogram MAJ_UUMGTRTZMY01 is used to transcodify the content of table UZMYTABLE to populate table ZMYTABLE. It must perform the following tasks:

• The name of the migration procedure must be defined at the beginningt
  Local Char PROG(20): PROG="UUMGTRTZMY01"
• The status of the procedure will be updated using the following subprogram (also if a preliminary condition makes the launching not necessary, for example, if there is nothing to process: the status then needs to be Completed for the procedure to be considered to be run and for the next procedures to follow). Otherwise, the status will be set to In progress:
        # CURSTAT variable Integermanaging the status (local menu 21)
   # The possible values are Pending, In progress, Completed...
        # If the procedure is launched, CURSTAT=2
        # If it is over, CURSTAT=3
        # In case of error, it will need to be set to 7
        # Variable PROG contains the name of the procedure
        #  (for example, UUMGTRTZMY01)
        Call MIGSTKENDFLG (FOLDER,PROG,CURSTAT,ERR) From TRTMIG
• The presence of tables and prior tables will be tested using subprogram MIGTABNAME, like what is done in the initialization sub-program. In case of error, the status of the task needs to be set to the correct value (7) with subprogram MIGSTKENDFLG.
• Those tables necessary for the migration will then be opened. The internal tables of the migration procedure are opened via:
        Call MIG_OUVRE From TRTMIG
• Logs will be kept to a minimum (no need to add one on each line, there are examples where migrations were interrupted because there was not enough disk space to write the logs). It is on the other hand useful to create a log starting with a message of type "Start of the phase updating xxx", and a time stamp line with a message like "Process started at": These logs will use the following subprograms:
        # Log line if ERR=0, else, error line
        Call ECR_TRACE("message",ERR) From GESECRAN
        # Time stamped log line
        Call ECR_TIME("message") From DOSSUB

• In order to improve the insert time into tables, indices are deleted. Only the first active index is kept at the beginning of the procedure. The indices are recreated at the end of the procedure, using subprograms:
        Call MIGTABINIT(FOLDER,"ZMYTABLE",ERR) From TRTMIG
  This requires that the procedure be the only operating one in the table when it is run. No other procedure must be read, modified, or inserted in the table while the procedure is run. If the table must be accessed by several procedures, this subprogram must not be used.

• The first key to be processed will be found in the processing loop. As a matter of fact, this key is not empty when the migration starts again after having been interrupted. The subprogram described below helps find it:
  
         # TBMKEY(1..NBMKEY) is a table containing the key values
         #   where to resume the read. At a minimum, the components
         #   of the last processed key value are included, but the following can be added
         #   a segment to determine the break level reached
         #   on a multiple key. TBMKEY must be empty before the call
         #   If it is returned empty, it means that there was no current key value
         #   (the procedure is then started at the beginning).
         # NBMKEY is used to define the number of expected values
         # NBL and NBUP count the number of lines read and updated so far.
    Call MIGGETKEY (FOLDER,PROG,NBMKEY,TBMKEY,NBL,NBUP,ERR)
         &    From TRTMIG
• It will then be possible to enter the read loop of the table(s) of origin and the write loop of the destination tables. For performance reasons, it is recommended to avoid the use of isolated read orders on tables and use Link instructions instead, preferably with strict joins (syntax Where CLE~=... ). If a "small" table must be read for initialization or control reasons ("small" means that it contains less than 1,000 lines), it is often better to store the useful content of this table in variables in memory before stating the loop.
• In order to avoid errors linked to the writing of a too large number of lines, the number of written lines will be counted and the loop will be interrupted every N lines (for example, N can be set to 50.000, using the global variable GMAXUPDTRS).
• For performance reasons, the use if the Writeb instruction is recommended (introduced from the engine used in version 6.4). This instruction groups the records and improves the performances, especially but not only in a multi-tier architecture. This instruction can only be used if the table is not reread in the loop (e.g.: reread to know if a record already exists). It implies that the grouping factor is set using a variable named adxwrb (10 seems to be an appropriate value). It is then necessary to stack the values of the written keys in order to be able to retrieve all the keys being currently used (this error is generated with the Flush instruction and not the Writeb instruction). The following subprogram can be used:
          # Stacking of keys.
          # KEY_ARRAY is an alphanumerical array declared by
          # Local Char KEY_ARRAY(N)(adxwrb)  at the beginning of the processing
          # (sizeN is dependent on the size of the key values)
          # NUMBER a variable Shortint that determines the number of stacked keys
          # KEY_VALUE encoded key value if the key is subdivided into several parts
          Call STK (KEY_ARRAY, NUMBER,KEY_VALUE) From TRTMIG
• If the loop is exited (after N loops, or because the processing is completed), a Flush is performed on the table (if Writebis used) by managing a possible error. If an error has been generated, and adxwrbis larger than 1, the keys having caused the error can be found in the KEY_ARRAY array, on the first adxwrbindices: a Rollback is then run before setting the procedure to the status Completed with errors and ending the processing.
• If everything went smoothly, the number of lines read and updated will be updated using the following call:
       Call MIGSTKKEY (FOLDER,PROG,NBMKEY,TBMKEY,NBL,NBUP,ERR)
       &    From TRTMIG
  A Commit is then performed.
• A interruption request test can then be carried out. In the absence of such a request, and all the lines have not been processed, a loop is started again on N lines. The interruption test is carried out using the following subprogram:
        # The STOP variable is equal to 0 if an interruption request has been made
        Call MIGSTKPROGRESS (FOLDER,PROG,STOP,ERR) From TRTMIG
• If an interruption request has been made (or if the processing is completed), the following operations will be conducted:
        # Indexing of the processed table(s)
        # (only if MIGTABINIT has been used)
           Call MIGTABEND(FOLDER,"ZMYTABLE",ERR) From TRTMIG
        # Alert the monitor of the processing end
           Call MIGTRTEND(FOLDER,PROG) From TRTMIG
           Call ECR_TIME(PROG+": Task completed") From DOSSUB

It will then be possible to close the tables and free resources if any, then stop the subprogram using the End instruction.

PATCH subprogram

This subprogram is used to perform a launch by patch. The ADOSSIER table needs to be open, then the record corresponding to the folder to be processed will be read and the migration procedure will be launched. This subprogram could take the following form:

Subprog PATCH(FOLDER)
Value Char FOLDER
# No launch on the supervisor folder
If FOLDER=GDOSX3: End: Endif

#Loading of class [F:ADS]
If clalev([F:ADS])=0  Local File ADOSSIER [ADS]: Endif
Read [ADS] DOSSIER=FOLDER: If fstat: Raz [F:ADS]: Endif
# Launch of the procedure
Call MAJ_UUMGTRTZMY01(FOLDER)
End

Subprogram UTI_MOUL

This subprogram is used to perform a launch on folder validation. It is rather similar to the PATCH subprogram. On the other hand, it is not necessary for table ADOSSIER to be open. and the record corresponding to the folder to be processed does not need to be read. All this is ensured by the calling processing. Its sole purpose is therefore to launch the corresponding procedure. This subprogram could take the following form:

Subprog UTIL_MOUL(FOLDER)
Value Char FOLDER
# No launch on the supervisor folder
If FOLDER=GDOSX3: End: Endif

# Launch of the procedure
Call MAJ_UUMGTRTZMY01(FOLDER)
End

MIGTAB entry point

If specific flow tables need to be managed by the migration processing in a similar way (in other words, they are renamed in the same way at the beginning of the folder validation, then they are created with the new structure), the following phases are necessary:

• Entry point MIGTAB needs to be used to indicate that a specific flow table will have to be processed in the same way as the standard tables during the first phase (renaming and deletion of the unnecessary indices).
• In the supervisor folder (or the reference folder in case of a 3-tier architecture), it is necessary to create the description of the specific tables whose structure varies on version change. Otherwise the new flow tables will not be created empty with the new structure.
• This is a rather particular exception to the rule which states that, usually, no specific table should be created in the supervisor folder. The reason is simple: there is the need for a "reference" dictionary in this particular case. And yet, the only dictionary of the sort that is available, if in a 2-tier architecture (most cases), is the dictionary of the "supervisor" folder (X3 in the case of Sage X3). It should be noted that these specific tables created in the supervisor folder can be overwritten without notice if a new version of the supervisor folder is installed. As a result, the presence of specific tables in X3 is only temporary and the description of these tables must be saved elsewhere.
• Since a specific table can be used to feed specific fields in a standard table, it is not necessary to create a new specific table on version change. In that case, the specific table will be renamed using entry point MIGTAB. Moreover entry points will be used in standard or additional procedures to perform the updates that may be missing without creating new tables in the destination dictionary.

If this entry point is not used, the specific tables, which are protected by a specific activity code, will not be impacted at all. They can be found in the original status. This will often correspond to what must be done by default if the version change has no impact on the structure of the specific tables.

Likewise any specific field present in a standard flow table and having a content that should be transferred as such does not need to follow any specific rules: the standard migration processing routines use class assignments to perform data copy.

The name of the processing where the MIGTAB entry point is located is dependent on the software based on SAFE X3. In the case of Sage X3, this entry point MIGTAB can be found in processing TRTMIGTABX3.

This processing will also contain lines of type:
    Call MIGTABTABADD(DOSSIER,"ZMYTABLE","ZMY","index",
&         NBTAB,WTABLE,WTABLEJ,WABRJ,WTABIDX,ERR) From TRTMIG

The variable names provided below are fixed for most of them:

• FOLDER is the folder code.
• the name of the table to migrate provided as an example is "ZMYTABLE".
• "ZMY" corresponds to the abbreviation of the table of origin after it has been renamed. If this value is empty, a temporary abbreviation is specified, in the form U## or W##, where ## is a number.
  In all cases, this abbreviation is not used in the processing routines. In procedures, another temporary abbreviation such as [XXXM] will be used, with XXX being the abbreviation of the migrated table.
• NBTAB represents the variable that contains the number of flow table defined as to be migrated (each call to TRTMIG increments this variable).
• WTABLE, WTABLEJ, WABRJ and WTABIDX are tables that contain all the elements linked to the flow tables that have already been declared.
• ERR returns a value that is not equal to zero in case of error.

Entry points of the standard migration processing routines

Each standard migration procedure contains entry points used to interact with the switching logic of the standard tables. These entry points are the following:

• CRITSEL; it is used to feed the CRITERESPE variable to filter the data to be processed. More details on the where-used context can be found in the annex describing the migration procedures.
• VERIFSEL; it is used to recalculate fields of the table of origin that is being used during the processing, and possibly exclude a record that will thus not be rewritten by setting ISVALID to 0. More details on the where-used context can be found in the annexe describing the migration procedures.