Writing a specific upgrade procedure

Upgrade from an earlier version is performed using a job monitor that launches upgrade procedures in an automated way. Specific upgrade procedures can be added but you have to:

Generate an upgrade processing.
Reference this processing as an upgrade procedure in the Supervisor folder using the corresponding function (Usage > Upgrades > Upgrade process).
This should be done before creating any upgrade plan and starting any folder revalidation.

This document describes the structure of such an upgrade processing. Refer to processing "UUMGTRTPUR01", if necessary. It is used to carry out the upgrade of the purchase requests.

Caution: Its source code is provided as an example. Do not modify it as it might make the upgrade procedure invalid.

Basic principles

During folder validation:

Each transactions table is renamed. By default, the letter "U" is added before its name.
Activity code "MIG" is assigned to each table to make it possible to identify them when the temporary tables are deleted after the upgrade validation.
The content of these transactions tables is kept strictly identical to the original transactions tables.
Their indices are deleted (except for the first index, and possibly an additional one that can speed up the upgrade processing).
One or more empty transactions tables are created according to the dictionary of the new version (the specific fields or modifications to the standard fields made in the original table are included).
The empty table usually has the same name as the original table, unless the table no longer exists in the new version.

The upgrade of specific tables can be managed using the same mechanism. This is described in MIGTAB entry point.

The following subprogram can determine the name of the table after it has been renamed:

     # FOLDER = code of folder
     # PTABLE = name of the movement table before the upgrade
     # 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 is to add the letter "U" before the name of the table. However, there are exceptions, for example if the name of the table exceeds 12 characters. Entry point "TABNAME" in processing "TRTMIG" is used to set entry point "MTABLE" by defining "PTABLE" with a rule that can be different. Each unitary procedure is defined using a program that upgrades one or several tables in the base. This processing must be called "UUMGSPExxxnn", with "xxx" being the root identifying the table to upgrade, and "nn" being two numbers (to enable the use of several processing routines). It functions as follows:

The original tables are read but not updated (their names are found using the subprogram above).
The processing writes the modified, completed or transcoded data in the final tables.
The processing makes it possible to divide the upgrade into parallelizable procedures.

In order to obtain the best performances possible:

The final table is delivered empty, and then is indexed (single index). The other indices are created at the end of the unit procedure.
It is possible to partially process the original information, as well as process non-upgraded data at a later stage (for example if you only want to upgrade the last two fiscal years).
The table is preferably written in grouped mode (using syntax writeb), running commit on the table for each stack of N entries (N being limited to a reasonable number).
The upgrade log is updated.

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

A pointer is written in a dedicated table after each commit to specify what data has already been processed and provide additional information.
A test is run to check if an interruption request was made after each commit.
The procedure is written in such a way that it can be rerun starting from the empty destination tables.

## Description of the upgraden processing The upgrade processing contains the following elements:

Preliminary lines that enable the direct triggering of the procedure.
Subprogram "RAZ_UUMGSPExxxnn" which makes it possible to return to the initial situation to restart the procedure.
Subprogram "MAJ_UUMGSPExxxnn" which contains the upgrade procedure.
Subprogram "PATCH".
Subprogram "UTI_MOUL".

These subprograms are described in this document. In the given examples, processing "UUMGTRTZMY01" corresponds to the unit upgrade phase of a single table called "ZMYTABLE". After the preliminary folder revalidation phase:

"ZMYTABLE" has been renamed according to the default rule ("UZMYTABLE").
The new "ZMYTABLE" table has been created with the new structure but it is empty.

The upgrade 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:

# Definition of the current folder, and read of the folder table
Local Char FOLDER(30): FOLDER = nomap
# Name of the upgrade 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)" allows you to revert to the initial situation, if necessary, in order to relaunch the procedure. It must at least include the following basic phases:

Make sure the original and destination tables exist using 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

Generate logs for errors only (no progress or warning messages).

Empty the tables where the update procedure inserts data using the following subprogram:

      # Sudden and irreversible dump of table ZMYTABLE
      Call MIGTABRAZ(FOLDER,"ZMYTABLE",ERR)
      If ERR: End: Endif

Use standard transactions such as the ones listed below if common tables with several processing routines must be partially purged:
```
       Trbegin [...]: Delete [...] Where ... : Commit
```
Reset the information regarding the already upgraded flow for this table to zero using the following subprogram:
```
      # No upgraded 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 performs the following tasks:

Define the name of the upgrade procedure at the beginning:
```
Local Char PROG(20): PROG="UUMGTRTZMY01"
```

Update the status of the procedure using the following subprogram:

      # CURSTAT variable Integer managing 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

Note: If a preliminary condition makes the launch unnecessary, the status needs to be "Completed" for the procedure to be considered as running and for the next procedures to follow. Otherwise, the status is set to "In progress"

Test the presence of the current and previous tables using subprogram "MIGTABNAME". In case of error, the status of the task has to be set to the correct value ("7") with subprogram "MIGSTKENDFLG".
Open the tables necessary for the upgrade. The internal tables of the upgrade procedure are opened using:
```
  Call MIG_OUVRE From TRTMIG
```
Keep logs to a minimum. Adding a log to each line can cause a lack of disk space, which can interrupt the upgrades. However, it is useful to create a log starting with "Start of the phase updating xxx", and a time stamp starting with "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
```
Delete indices to improve the insertion time in tables. Only the first active index is kept at the beginning of the procedure. The indices are recreated at the end of the procedure, using the following subprograms:
```
  Call MIGTABINIT(FOLDER,"ZMYTABLE",ERR) From TRTMIG
```
Caution: The procedure has to be the only operating procedure 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 has to be accessed by several procedures, this subprogram must not be used.

Find the first key to be processed in the processing loop using the following subprogram:

       # 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 is not empty when the upgrade resumes after an interruption.

Enter the read loop of the original tables and the write loop of the destination tables. For performance reasons, it is recommended to avoid the use of isolated read orders on tables. It is also recommended to use link instructions instead, preferably with strict joins (syntax Where CLE~=... ). If a "smaller" table (containing less than 1,000 lines) must be read for initialization or control reasons, it is usually better to store the useful content of this table in variables before starting the loop.
Avoid writing an overly large number of lines which can cause errors. To do this, the number of lines is counted and the loop is interrupted every N lines. For example, N can be set to 50,000, using global variable GMAXUPDTRS.
Use the writeb instruction for performance reasons. This instruction groups the records and improves performances, especially for multi-tier architectures. It can only be used if the table is not re-read in the loop. It implies that a grouping factor is set using a variable named adxwrb ("10" is an appropriate value). It is then necessary to stack the values of the written keys to be able to retrieve all the keys currently being used in case of error (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
```
Perform a flush on the table (if writeb is used) by managing a possible error if the loop is exited (after N loops, or because the processing is complete). If an error has been generated and adxwrb is larger than "1", the keys that caused the error can be found in the "KEY_ARRAY" array, in the first adxwrb indices. A rollback is then run before setting the procedure to "Completed with errors" and ending the processing.
Update the number of lines read and updated using the following call:
```
  Call MIGSTKKEY (FOLDER,PROG,NBMKEY,TBMKEY,NBL,NBUP,ERR)
  &    From TRTMIG
```
A commit is then performed.
Carry out an interruption request test, if necessary. In the absence of such a request, and if not all the lines have 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
```

Conduct the following operations if an interruption request has been made (or if the processing is completed):

      # 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 is then possible to close the tables and free the 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. The record corresponding to the folder to be processed is read, and the upgrade procedure is launched. This subprogram can be organized as follows:

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 launch folder validation. It is similar to the PATCH subprogram, except that table "ADOSSIER" does not need 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 to launch the corresponding procedure. This subprogram can be organized as follows:

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 transactions tables have to be managed in a similar way by the upgrade processing, the following phases are necessary:

Use entry point MIGTAB to indicate that a specific transactions table has 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), create the description of the specific tables whose structure varies after version change. If not, the new transactions tables will not be empty with the new structure.
This is an exception to the rule stating that no specific table should be created in the Supervisor folder. It is due to the fact that a "reference" dictionary is needed in this particular case. The only "reference" dictionary available for a 2-tier architecture (usual case) is the Supervisor folder dictionary. Note that the 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 Sage 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 after the version change. In this case, the specific table is renamed using entry point "MIGTAB", and entry points are 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, and will remain in their original state. It usually corresponds to what must be done by default if the version change has no impact on the structure of the specific tables.

Similarly, any specific field present in a standard transactions table that has content that should be transferred as such does not need to follow any specific rule. The standard upgrade processing routines use class assignments to copy data.

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

This processing will also contain lines such as:

    Call MIGTABTABADD(FOLDER,"ZMYTABLE","ZMY","index",
&         NBTAB,WTABLE,WTABLEJ,WABRJ,WTABIDX,ERR) From TRTMIG

With most of the variable names provided below being fixed:

FOLDER is the folder code.
The name of the table to upgrade provided as an example is "ZMYTABLE".
"ZMY" is the abbreviation of the original table 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]" is used, with "XXX" being the abbreviation of the upgraded table.
"NBTAB" contains the number of transactions tables to be upgraded. Each call to "TRTMIG" increments this variable.
"WTABLE", "WTABLEJ", "WABRJ" and "WTABIDX" are tables that contain all the elements linked to the transactions tables that have already been declared.
"ERR" returns "0" unless there is an error.

Entry points of the standard upgrade processing routines

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

"CRITSEL", which 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 upgrade procedures.
"VERIFSEL", which is used to recalculate fields of the original table being used during the processing, and possibly exclude a record that will not be rewritten by setting "ISVALID" to "0".
More details on the where-used context can be found in the annex describing the upgrade procedures.