Upgrade - data upgrade process - development side
There are 2 types of data upgrade:
Table of contents
1 - Upgrade for tables with small volumes
2 - Transactions table upgrade
2 -3 - Temporary tables creation
2 -4 - Transactions table upgrade processing
2 -4 -3 - Upgrade procedure development
2 -4 -3 -1 - Preliminary lines
2 -4 -3 -2 -Reset to zero subprogram
1 - Upgrade for tables with small volumes: DOSMAJ.AMAJ and MAJ subprog
The correspondance between upgrade version variables (GMIGX3VERxx) and release is done in TRTMIGTABX3 -$DEF_VERSIONSX3
Remark: Control procedures and pre-upgrade procedures executed on the source folder before upgrading don't work on these tables.
1) The structure of these tables is updated with the new fields. The new index and the removed fields are saved in this phase: MAJDIC from SUPMAJxx
2) Some fields in Supervisor and in the application tables are updated depending on the gap between two successive versions. If a column of a table needs to be removed between two releases and the corresponding data is required to be calculated or to be updated data in another column, then the corresponding update has to be done in this step because the columns will be removed before the second step of updates (MAJ subprog).
If you upgrade from V5 to Update 9, each AMAJ procedure from DOSMAJ30 to DOSMAJ60 is executed sequentially:
DOSMAJ30.AMAJ then DOSMAJ31.AMAJ ...
If the folder validated is an historical folder, the upgrade is done only if the table is an historical table. This set up is done in the normal folder: APARHIS using the ISHISTFOLD function from DOSSUB to check. If the folder is an historical one, use the ISVIEW function from DOSVUE to check if the table is an historical table.
Therefore, there are two situations:
You can refer to the AMAJ procedure in FASMAJ60 process. AMAJ_BUI_604 is never called when the folder to be upgraded is an historical folder whereas AMAJ_LOCATION is called in both cases. But the LOCALIZAT table in historical folder is upgraded only if it is not a view.
# FASMAJ60 processSubprog AMAJ#** Called from DOSMAJ60#*!Local Shortint IS_HISTO_FOLDER : IS_HISTO_FOLDER = func DOSSUB.ISHISTFOLD([F:ADS]DOSSIER) : # FQ108289Call ECR_TRACE("Start AMAJ from FASMAJ60 on" - [F:ADS]DOSSIER, 0) From GESECRANIf !IS_HISTO_FOLDER : # FQ108289# Not to perform in an archive folder (settings data)# Change ATBDIV 604 (building) in an object BUIGosub AMAJ_BUI_604.....Endif# Change the name of the table LOCALIZAT to PHYLCTGosub AMAJ_LOCATION$AMAJ_LOCATION.....If (IS_HISTO_FOLDER) and (func DOSVUE.ISVIEW([F:ADS]DOSSIER,"LOCALIZAT")) : Call ECR_TRACE("LOCALIZAT is a view. Nothing to do",0) From GESECRANElseIf (IS_HISTO_FOLDER) : # FQ108289# In an archive folder, if LOCALIZAT is a table and not a view, we must create the PHYLCT table and upgrade itCall CRE_HISTAB([F:ADS]DOSREF, [F:ADS]DOSSIER, [F:ADS]TYPDBA, "PHYLCT") From CREHISTOEndif3) The table dictionary is updated in the folder to be upgraded according to the new table dictionary definition in the reference folder. Therefore, if a column has been removed in the target version, it is removed in the folder in this step.
4) Post upgrade data updates: Each MAJ subprog launches (basic structure):
- MAJ from SUPMAJxx process
- TRCMAJ30 from TRCMAJxx process (common data)
- xxxMAJxx from xxxMAJxx process (for each module)
The MAJ and xxxMAJxx processes can, for example, populate a new column with the content of an old column that might disappear at the next step, or populate a new table with the content of an old table that might disappear in the next step.
The phases 1 and 3 are managed by the Supervisor and do not require any change when delivering a new version (except if the upgrade process evolves).
2 - Transactions table upgrade: UUMGxxxYYNN procedures
This part describes the different steps of the data upgrade for large/transactions tables.
The transactions table upgrade involves 5 steps:
These steps launch procedures that respects a standard naming convention.
The transactions table upgrade processing launches UUMGTRTxxxNN procedures via the sequencial processing.
Naming standards for control upgrade, pre-upgrade, transactions table upgrade and post upgrade procedures is 12 caracters: UUMGZZZxxxNN with:
| Procedure type | Name |
| Control procedure | UUMGCTLxxxNN |
| Pre-upgrade procedure | UUMGPRExxxNN |
| Upgrade procedure | UUMGTRTxxxNN |
| Post-upgrade procedure | UUMGPOSxxxNN |
2 -1 - Control procedures
The control procedures are launched in the source environment and can be spread out over a period of time, before the actual upgrade. The procedures check that the data to upgrade is consistent. They are delivered in a patch in the versions in production when a change is done for a new version.
The procedure launched is UUMGCTLX3. It launches accounting, purchases, sales and common data procedures.
2 -2 - Pre-upgrade procedures
The pre-upgrade procedures are launched in the source environment and they can take place over a period of time, before the actual upgrade. For example, these can be procedures to compute V6 information in V5 tables which shortens the main upgrade time. They are also delivered in a patch in the versions in production.
The procedure launched is UUMGPREX3.
2 -3 - Temporary tables creation
Temporary tables are created during folder validation in TRTMIGTABX3 - Subprog TRTMIGTABX3.
For each table to be upgraded in transactions tables upgrade, TRTMIGTABX3 calls TRTMIGTABTABADD Subprog from TRTMIG treatment.
For example: Call MIGTABTABADD (DOSSIER,"BUD","","CPY+BUD+ACCNUM",NBTAB,WTABLE,WTABLEJ,WABRJ,WTABIDX,ERR) From TRTMIG
Note that the index is chosen by the developer according to performance aspects.
TRTMIGTABTABADD Subprog fills an array (WTABLEJ) with each temporary table to be created and after all the calls to TRTMIGTABTABADD, the call to TRTMIGTAB_TRT from TRTMIG process creates the tables listed in WTABLEJ in the database.
See the global upgrade documentation for a description of upgrade steps
A temporary table is created only if an upgrade procedure will update columns and depending on the version from which you upgrade. Some tables are not impacted by upgrade procedures. For example, for some of these tables, this is done only if the source folder is earlier than V6 (for example upgrade from V5 to current version). First, these temporary tables are created in the database. Then the data is copied from the source tables to the temporary tables (sometimes the index is modified), and finally the source tables are truncated.
The basic renaming rule 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. There can be exceptions, especially if the name of the table exceeds 12 characters.
The following subprogram determines the name of the table after it has been renamed:
# FOLDER = code of folder# PTABLE = name of the movement table before 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 TRTMIGAn abbreviation is defined for the temporary table.
2 -4 - Transactions table upgrade processing
The folder validation calls the sequencing monitor after the envelope validation (dictionary validation, small tables upgrade, etc ...).
The sequencing monitor launches upgrade procedures according to a plan defined in the "Sequencing Monitor" function (see below). This plan is delivered in Standard but can be updated by the customer before the upgrade.
2 -4 -1 - Upgrade plan
The upgrade plan includes 4 steps:
These steps are executed sequentially: If the procedures of a step are not completed, the procedures of the following step cannot be launched.
In each step, there are phases that are also executed sequentially. The procedures for Phase 2 are not started until the procedures for Phase 1 are completed. Note that all phases of common data need to be completed before starting procedures of Phase 1 for modules. But the procedures for Phase 1 of any module are executed in parrallel. For example, procedures for Phase 1 in the Accounting module will run in the same time as procedures for Phase 1 in the Sales module.
In each phase, there are ranks that determine the launching order.
The sequencing monitor function (in Usage/Upgrade/Sequencing monitor - AMOULIN) advances the progress.
The procedures are launched by batch via the batch server.
The parrallelisation depends on the process numbers and when creating a plan the user can update some parameters (impacted folder, number of procedures that can be run in parallel, task sequencing policy, running status).
For each procedure, it is possible to:
The graphical query: AMOUL3 (in GX3APP and X3DEV) lists the transactions table upgrade procedures with the phase, rank and tables used.
Each team has to update the GESAM3 function in order to have a list up to date with the order and the tables impacted.
When a new procedure is created or updated, the developer needs to take into account the order of the procedures executed (steps/phases). Situations to be taken into account:
2 -4 -2 - Upgrade procedures
The upgrade procedures are defined in the Upgrade process function (in Usage/Upgrade/Upgrade process - GESAM3).
The developer defines in which step/phase/module, the procedure belongs to.
2 -4 -3 - Upgrade Procedure development
The following elements are contained in the upgrade process:
These subprograms are described below.
A skeleton for upgrade procedures exists: "UUMGTRTXXX00" (module XXX) and as example you can use UUMGTRTPUR01.
2 -4 -3 -1 - Preliminary lines
The following lines are executed when the procedure is executed from the parent folder (X3). For example, when testing the procedure.
Preliminary linesThe 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 tableLocal Char DOSSIER(30) : DOSSIER = nomapIf !GSERVEURCall SAIDOSHIS(DOSSIER,"") From SAIDOSHISEndifIf DOSSIER <> ""If clalev([F:ADS])=0 Local File ADOSSIER [ADS] : EndifIf func DOSSUB.FILL_ADS([L]DOSSIER)=0 : End : Endif#Call ACTIVE From TIMING# Opening of the log and display of a timing windowIf !GSERVEURCall TEMPON("O") From GESECRANCall OUVRE_TRACE("UUMGTRTXXX00") From LECFICEndif# Launching of the procedure#Call RAZ_UUMGTRTXXX00(DOSSIER)Call MAJ_UUMGTRTXXX00(DOSSIER)# Closing the logIf !GSERVEURCall TEMPOFF From GESECRANCall FERME_TRACE From LECFICCall LEC_TRACE From LECFICEndif#Call ARRET From TIMINGEndifEnd2 -4 -3 -2 - Reset the subprogram to zero
The subprogram is declared by Subprog RAZ_UUMGTRTXXX00(DOSSIER). It is used to return to the initial situation and to relaunch the procedure, if necessary. This subprogram is called when the user from the sequencing monitor function (AMOULIN) executes the 'Relaunch' action from the procedure (contextual action).
The subprogram must at least include the following basic phases:
Subprog RAZ_UUMGTRTXXX00(DOSSIER)Value Char DOSSIERLocal Integer ERR##1 - verifications#Check that the source and target tables do exist, for example, by means of the following lines####################If [F:ADS]MODULE(N)<>2 : End : Endif: # N= no module#MIGTABNAME gives the temporary table name (WNAMXXXM) , W = "U" usually Local Char WNAMXXXM,WNAMXXDMCall MIGTABNAME (DOSSIER,"XXXXXX",WNAMXXXM,ERR) From TRTMIGCall MIGTABNAME (DOSSIER,"XXXXXXD",WNAMXXDM,ERR) From TRTMIGIf ERR : End : Endif#-----test the presence of the renamed files ... -----#If filinfo(filpath("FIL",WNAMXXXM,"fde",[F:ADS]DOSSIER),0) < 0 : End : EndifIf filinfo(filpath("FIL",WNAMXXDM,"fde",[F:ADS]DOSSIER),0) < 0 : End : Endif##2 - emptying tables depending on the case######################################## If common tables with several processing routines must be partly purged, standard transactions will be used, of type Local File =DOSSIER+".XXXXXX" [XXX]Trbegin [XXX]Delete [XXX] Where ORIMOD<>3Commit#Empty the table(s) where the update procedure inserts data. For that purpose, the following subprogram is available:Call MIGTABRAZ (DOSSIER,"XXXXXXD",ERR) From TRTMIG##3 - emptying AMIGKEY for the transactions table#####################Reset to zero of the indicators of progress of proceedings on the already upgraded transactions for this tableCall MIGRAZKEY (DOSSIER,"UUMGTRTXXX00",ERR) From TRTMIGEndNote: Logs should only be generated for errors (no progress or warning message). The subprog is executed in interactive.
2 -4 -3 -3 -Update subprogram
Subprogram MAJ_UUMGTRTXXX01 is used to transcodify the content of tables "XXXXXX" and "XXXXXXD" to populate table WNAMXXXM and WNAMXXXDM (see UUMGTRTXXX00 process). To create your own MAJ_UUMGTRTXXX01 subprog, first copy the UUMGTRTXXX00.MAJ_UUMGTRTXXX01 subprog in your process and refer to the following steps. The update subprogram must perform the following tasks:
Update subprogram - First phase:
Controls, variables declaration, tables initialisation, dialog with sequencing monitor and reprise management (TABMIGKEY table)
1) 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 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 (DOSSIER,"UUMGTRTXXX00",CURSTAT,ERR) From TRTMIG Call MIG_OUVRE From TRTMIG3) A test if the module is activated is done (otherwise the procedure ends):
If [F:ADS]MODULE(N)<>2 : : # N= no moduleCall MIGSTKENDFLG (DOSSIER,"UUMGTRTXXX00",3,ERR) From TRTMIG : #3=TerminéeEndif4) Logs will be kept to a minimum (no need to add one on each line, there are examples where upgrades 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 like "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 lineCall ECR_TRACE("message",ERR) From GESECRAN#Time stamped log lineCall ECR_TIME("message") From DOSSUB Call MIGTABINIT(FOLDER,"ZMYTABLE",ERR) From TRTMIGThis requires the procedure to be the only one operating in the table when it is run. No other procedure must be read, modified, or inserted in the table while this procedure is running. If the table must be accessed by several procedures, this subprogram must not be used.
Update subprogram - Second phase:
Reading source tables
6) The first key to be processed will be found in the processing loop. This key is not empty when the upgrade starts again after having been interrupted. The subprogram described below helps find it:
# TABMIGKEY(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. TABMIGKEY 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).# NBMIGKEY is used to define the number of expected values# NBLUS and NBCOMMIT count the number of lines read and updated so far.```CODECODE CODECall MIGGETKEY(DOSSIER,"UUMGTRTXXX00",NBMIGKEY,TABMIGKEY,NBLUS,NBCOMMIT,ERR) From TRTMIG
STOP=val(TABMIGKEY(NBMIGKEY))```
7) Reading/writting methods It will be possible to enter the read loop of the source table(s) and the write loop of the target 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 , it is often better to store the useful content of this table in variables in memory before stating the loop. "Small" means that it contains less than 1,000 lines.
To avoid errors due to the writing of a 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 upgrade procedures use the Writeb instruction. This instruction groups the records and improves the performance, especially but not only in a multitier 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 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.# WTAB_XXX is an alphanumerical array declared by# Local Char WTAB_XXX(N)(adxwrb) at the beginning of the processing# (sizeN is dependent on the size of the key values)# WCPT_XXX a variable Shortint that determines the number of stacked keys# [F:XXX]NUM encoded key value if the key is subdivided into several partsCall STK (WTAB_XXX, WCPT_XXX,[F:XXX]NUM) From TRTMIGWriteB [XXX]8) Between the read of the source tables (Link instruction) and the write to target tables, for a new version, you need to add two labels/subprog: reset to zero of new columns for the new version if needed: RAZYY_XXX, data upgrade for the new version (for headers and lines)
#Raz [F:XXX]# version < V6, raz des champs v6If val(VERSION)<GMIGX3VER60Gosub RAZV6_XXXEndif# version < V7, raz des champs v7If val(VERSION)<GMIGX3VER70Gosub RAZV7_XXXEndif[F:XXX] = [F:XXXM]#Appel proc de mise à jour sur entete# Passage en V6If val(VERSION)<GMIGX3VER60Gosub MAJV6_XXXIf GOK<1 : Break : EndifEndif# Passage en V7If val(VERSION)<GMIGX3VER70Gosub MAJV7_XXXIf GOK<1 : Break : EndifEndif9) If the loop is exited (after N loops, or because the processing is completed), a Flush is performed on the table if Writeb is used. The flush writes the buffered records into the table. If an error has been generated and adxwrb is larger than 1, then the keys that have caused the error can be found in the TABMIGKEY array, on the first adxwrb indices. A Rollback is then ran before setting the procedure status to "Completed with errors" and ending the processing.
If everything goes smoothly, the number of lines read and updated will be updated in TABMIGKEY using the following call:
Call MIGSTKKEY (DOSSIER,"UUMGTRTXXX00",NBMIGKEY,TABMIGKEY,NBLUS,NBCOMMIT+NB,ERR) From TRTMIGA Commit is then performed. An interruption request test can then be carried out. In the absence of such a request, and if 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 madeCall MIGSTKPROGRESS (DOSSIER,"UUMGTRTXXX00",STOP,ERR) From TRTMIGAt the end of the update subprog or 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)# add LOGGING attribute to tablesCall MIGTABEND (DOSSIER,"XXXXXX",ERR) From TRTMIGCall MIGTABEND (DOSSIER,"XXXXXXD",ERR) From TRTMIG# Alert the monitor of the processing endCall MIGTRTEND (DOSSIER,"UUMGTRTXXX00") From TRTMIGCall ECR_TIME("UUMGTRTXXX00 : "+num$(NBLUS)+" records read,"-num$(NBCOMMIT)+" records processed.") From DOSSUBIt will then be possible to close the tables and free resources if any, and then stop the subprogram using the End instruction.
10) For an historical folder: in TRTMIGTABX3, the MIGTABTABADD function tests if the table is a view, therefore the historical folders are taken into account.
Specific to historical folder:
- If you need to use data from the ADOSSIER table, use the FILL_ADS function from the DOSSUB process: there are no records for an historical folder in the ADOSSIER table.
- If you need to test the version or release, use the RELEASE/VERSION function from the DOSSUB process.
- If you need to remove or empty a table use the ANNULE/VIDE function from DOSTAB.
Remark: The purge parameters are set in the APARHIS function.