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.
- 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.
- 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.
- 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".
- "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.
# 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
- 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 TRTMIGNote: 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 TRTMIGIt is not empty when the upgrade resumes after an interruption. - Enter the
readloop of the original tables and thewriteloop 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 uselinkinstructions instead, preferably with strict joins (syntaxWhere 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
writebinstruction 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 namedadxwrb("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 theflushinstruction and not thewritebinstruction). 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
flushon the table (ifwritebis 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 andadxwrbis larger than "1", the keys that caused the error can be found in the "KEY_ARRAY" array, in the firstadxwrbindices. 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
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:
FOLDERis 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.