DBUPDGEN

DBUPDGEN generates a Perl script that updates one or more subsystems in a database by comparing two different labels of the subsystem(s) in the version-control system and includes all changed files in the update script. If a file with a table has changed, DBUPDGEN generates SQL code to move data from the old definition of the table to the new definition.

You can also feed DBUPDGEN a list of selected files, if you want to script to deploy a patch for a subsystem.

DBUPDGEN can read an existing update script and regenerate it with recent changes while preserving manual changes you have made to the script.

To get an overview of the overall process where DBUPDGEN fits in, see the page Life-cycle of a database.

Contents:
   Command-Line Syntax
   Command-Line Syntax for the Update Script
   Output from an Update Script
   Notes on Regular Update Scripts
   Checking Subsystems and Their Versions
   Inside the Update Script and How it is Updated
      The Setup Part
      The Subsystem Parts
   Table Updates
   Patch Scripts
   Advanced Topics
      Raising Messages and Errors
      Referring to Objects in the Side Schema
      Final Epilogue
      Loading Files from Other Subsystems
      Datatype Changes
      Dropping a Data Type
      Dropping an Indexed Column
      Forcing ALTER TABLE in abasqlfile
      Using ALTER TABLE and sp_rename
      Checking if a Table-Update Has Already Been Run in a Database
      Getting Data Back from SQL Server to the Update Script
      Printing Messages to the Log
      Moving Objects between Subsystems
      Forcing the Install of a Lower Version in a Production Database
      Checking the SQL Server Version
      The $X Object

Command-Line Syntax

To create a regular update script:
dbupdgen {-config config-file [-subsystem subsys] | -VC VC-path -subsystem subsys}
-from from-version [-to to-version | -checkedout] [-listonly] script-file
To update an existing script:
dbupdgen [-to to-version | -checkedout] [-listonly] script-file

To create a patch script:

dbupdgen -patch patch-file [-reverse] {-config config-file] [-listonly] script-file
‑config Instructs DBUPDGEN to build a update script for the subsystems in config-file. DBUPDGEN ignores any configuration options in the config-file and only cares about the VC-paths of the subsystems. If the VC-path for a subsystem does not end in /sql, DBUPDGEN tacks on /sql to the path. If for a subsystem there is no SQL directory, DBUPDGEN emits a warning and ignores the subsystem.
   When you create a new script, you must specify one of ‑config and ‑VC. When you update an existing script, DBUPDGEN reads config-file from the script.
   For patch scripts, ‑config has a different meaning see below under Patch Scripts.
‑VC Instructs DBUPDGEN to build a update script for the a single subsystem, located in VC-path. When you specify ‑VC, the option ‑subsystem is mandatory.
   If you specify neither ‑VC nor ‑config, DBUPDGEN attempts to map your Windows directory to a TFS directory and set the ‑VC option implicitly.
   When you create a new script, one of ‑config and ‑VC must be set. When you update an existing script, DBUPDGEN reads VC-path from the script. If you also specify ‑VC in this case, or if it set implicitly, it must match the script.
‑subsystem With ‑config, you can use ‑subsystem to restrict the script to only include this subsystem. With ‑VC, you must use ‑subsystem to specify the name of the subsystem, unless DBUPDGEN is able to map the VC-path to a subsystem through the system-definition file.
   When you update an existing script, DBUPDGEN reads subsys from the script. If you specify ‑subsystem, or DBDUPDGEN determines the subsystem name from the sysdef-file, the name must match the script. A consequence of this is that if you use a different name for the subsystem from the sysdef-file (because of legacy), you will need to specify it explicitly with ‑subsystem every time you regenerate the script.
‑from Specifies the starting point for the update script. You cannot specify NULL or LATEST for from_version.
   When you use ‑config, from-version specifies a version of the config-file and you can specify a date, label or a explicit version number of the config-file. AbaPerls then determines which label to use of each subsystem from the config-file as described on the config-file page. (Since the config-file may include explicit version specifications, the starting point for the subsystem does not have to be a label, but in most cases it is, so let's call it a label to keep it simple.)
   When you specify a single subsystem with ‑VC, ‑label normally specifies a label of VC-path on the on the standard AbaPerls format LetterMajor.Middle.Minor. It is permitted to specify a date or a label on some other format, but doing this must be considered exceptional, particularly if the ABAPERLS subsystem is installed in the database. (So again, let's call it a label.)
   When you create a new script, you must specify ‑from. When you update an existing script, DBUPDGEN reads this information from the script.
    Bear in mind that the exact interpretation of from-version depends on the version-control system.
‑to

Specifies the version the update script is to update the database to.
   For a config-file, to-version is a version number, date or label for the config-file, which in its turn determines which label each subsystem is be updated to. If you omit to-version, DBUPDGEN reads the most recently checked-in version of the config-file. Unless the config-file includes explicit version specifications, DBUPDGEN will use the most recent AbaPerls label for each subsystem. You can also specify to-version as LATEST, in which case DBUPDGEN will work with the most recently checked-in version of each file in all subsystems.
   When you create a script for a single subsystem with ‑VC, to-version normally specifies a label of VC-path on the standard AbaPerls format LetterMajor.Middle.Minor. This format is required in databases where the ABAPERLS subsystem is installed; you can only use dates or labels on some other format in databases without the ABAPERLS subsystem. You can leave out to-version or specify LATEST to tell DBUPDGEN to work with the most recently checked-in version of all files. In this case you will be able to run the script in a database where the ABAPERLS subsystem is installed, as long as the database is not marked as test or production.
   You can specify ‑to both when you generate a new script and when you update an old script. DBUPDGEN never reads to-version from the update script. When you update an existing script, you typically provide a later label than the original label for the script.

‑checkedout

Requests DBUPDGEN to create a bleeding-edge script by including all files you have checked out in the update script, even if there are no changes since from-version. This is handy if you work with a big update script with table changes, and you want to test the script before you check in your changes.
    When you specify ‑checkedout, you cannot specify ‑to and DBUPDGEN sets to-version to LATEST. The assumption is that once you have concluded your testing, you will set a new label.
     When you use TFS, DBUPDGEN includes all your pending changes when you specify ‑checkedout. That is, if you have added new files they will appear. Pending deletes will appear the section for deleted files, and files that have pending renames will appear in the script under the new name. (For SourceSafe, there are never any other pending changes than plain checkouts.)
    DBUPDGEN never considers other users' checkouts or pending changes.

‑patch Creates an update script that only includes the file specified in patch-file. See further below under Patch Scripts about the format.
   When you update an existing script, DBUPDGEN reads patch-file from the script.
‑reverse This option applies only to patch scripts, and instructs DBUPDGEN to generate script for a reverse patch. See further below under Patch Scripts.
   When you update an existing script, DBUPDGEN reads the setting for ‑reverse from the script.
‑listonly Instructs DBUPDGEN to list which version-control directories that will be processed and then terminate, without searching the version-control system for files and without generating any script.
script-file Determines the name of the script. Typically the name ends in .pl, but DBUPDGEN does not provide this as a default.

Command-Line Syntax for the Update Script

perl script-file  {-database db [-Server server] [-Password pwd] [-save file]}
                  | -noexec
                  [-get | -noVC] [-[no]use_disk] [-log file] [-force]
                  [-[no]crypt] [-[no]quoterepl] [-site site-id:s]
                  [-Macro &macro=value [...]] [-undef &macro [...]]

As with DBBUILD, you must specify one of ‑db and ‑noexec, and you cannot specify both of them. Note that the update script does not take any actual arguments, but only options.

script-file The name of the script that you generated with DBUPDGEN. Typically the name ends in .pl.
Database switches
‑database Name of the database to update.
‑Server The server on which the database resides. Default is the local server. The environment variable ABAPERLS_CTIMEOUT determines how log to wait for a connection. The default is 5 seconds.
   Note that if you specify the server instance by port number, you must enclose the server specification in quotes, for instance ‑S "127.0.0.1,2093".
‑User
‑Password
Which login and password to use with SQL authentication. If you leave out both username and password, AbaPerls will use Windows authentication. If you specify ‑password only, AbaPerls will login as sa. It's illegal to specify a user without a password. If you have a blank password, you need to change this before you can use AbaPerls.
   No matter if you use SQL authentication or Windows authentication, the login you use must map to a database user with dbo as the default schema, or AbaPerls will not let you proceed. Obviously, the user needs to have permission to perform all actions implied by AbaPerls and the script you run.
‑save Name of the file into which the update script is to save the SQL code it generates. If you do not provide an extension, the script adds .sql. If you do not specify ‑save, the update script saves the SQL code in script.sql. There is no way to specify that you do not want an SQL file to be generated. No SQL file is generated when you specify ‑noexec.
   The SQL file can be useful if some part of the update script fails, typically a table update. Rather than restoring a backup and run the entire script again, you may be able repair what is broken and the extract the problematic part from the SQL file to rerun only that part. (Obviously such a manoeuvre requires that you are fully confident with it.)
   Note: the generated SQL code depends on the database you run the script against. Two different databases could very well result in different code being generated, and the code generated for one database, may fail in another database.
‑noexec Instructs the update script to get the files and run them through Preppis to retrieve all included files to and compile any C# or VB files, without proceeding with further processing. You normally use ‑noexec with ‑get to create kits for installation at customer sites.
   When you specify ‑noexec, you cannot specify ‑database, ‑Server, ‑Password or ‑save.
Version-control switches
Update scripts do not accept the switches ‑config, ‑VC, ‑subsystem or ‑label, as the value for these were determined by DBUPDGEN and are hard-wired into the update script.
‑get

With ‑get, the update script creates a directory for each subsystem included in the update script. For each subsystem directory, the script gets the files and places them in an AbaPerls SQL directory structure. Files that have been deleted between from-version and to-version placed in a separate AbaPerls SQL directory structure under OBSOLETE-FILES in the subsystem directory. With ‑get, the update script also creates SS‑FILES.LIS in the top directory with version-control information about the files.
   Normally you use ‑get together with ‑noexec to create kits for installation at customer sites.
   You cannot specify ‑get together with ‑use_disk or ‑noVC. If you current Windows directory maps to a directory in TFS, this is an error.

‑noVC

Instructs the update script to not access the version-control system, but read all files from disk. You use this when installing at a customer site. ‑noVC implies ‑use_disk.

‑use_disk

Without ‑noVC, ‑use_disk instructs the update script to first look for the files on disk, before it looks in version-control, according to AbaPerls file-lookup order.
   With ‑noVC, ‑use_disk is implied, and the update script looks for the file in the structure created with ‑get.
   The default is ‑nouse_disk, unless you specified the switch ‑checkedout to DBUPDGEN when you generated the script; this flips the default to ‑use_disk, so that the changes you have not yet checked in are installed into to your development database.

Miscellaneous
‑log Specifies the name for the log file, to which all output from the update script is written. If you do not specify ‑log, the update script will log to a file named db.log. Normally, an existing file is overwritten. If you prepend the file name with ++, the update script appends to the log file.. For more information about the log, see the section Output from Update Scripts.
‑force Specifies that if there is a conflict between the configuration options saved in the database and the configuration options on the command-line, the command-line takes precedence.
   Only use ‑force when you really need to override the configuration saved in the database.
Configuration options
‑crypt
‑Macro
‑quoterepl
‑site
‑undef
These switches are configuration options. The setting of a configuration option is primarily taken from the database according to the precedence rules for configuration options. If the database defines a setting for an option and you specify a different setting, you must use ‑force to override.

Output from an Update Script

An update script produces two forms of output: it prints to the command-line window, and it produces a log file.

The output to the command-line window only serves the purpose of giving progress information and there is no need to monitor it. You see the name of each file being processed. In case of batches of SQL code for table-updates and similar, you see the first line of the SQL batch. (Often this is a descriptive comment.) Notice that a table-update may take several hours to run and during this time, you will see no output in the command-line window.

On the other hand, it is essential that you check the log file, because if a file or a batch produces errors when the update script runs it, the errors are written to the log only. The log file includes a full list of files that the update script has loaded.

First in the log file, you find a header which reports who ran the script when and with which command-line options. For each subsystem, there is a header with the configuration options that the update script used for that subsystem. AbaPerls reports only the configuration options that were set explicitly, by command-line switches or settings read from the database. Implicit settings from defaults are not reported.

You can use LISTERRS to read the log to only see the problematic parts. However, when you develop a script and add complex table-updates, you should probably review the complete log for the table-updates to verify that the updates work as you intended.

Note: There is one category of messages that are only printed to the command-line window: warnings and errors from Perl itself. Warnings or errors from Perl could be an indication of a problem in your update script, if you have edited the output from DBUPDGEN. They could also be an indication of a bug in AbaPerls (or Perl itself). A Perl error is usually fatal and terminates the script.

Notes on Regular Update Scripts

These are update scripts generated without the ‑patch switch.

Version of Subsystems

DBUPDGEN has two labels to play with for each subsystem. If you specified ‑VC, this is from-version and to-version that you specified on the command line. They will always be in the same version directory (since you can only specify one VC-path.) If both labels are on the standard AbaPerls format LetterMajor.Middle.Minor, DBUPDGEN verifies that the to-label is after from-version, and aborts if they are not. If you use other version specifications, DBUPDGEN merely verifies that from-version and to-version are different.

If you specified ‑config, then from-version and to-version on the command line gives the from- and to-labels for each subsystem through the config-file, according to the rules for version specifications on the config-file page. In this case, there are a few more possibilities:

Determining whether a File Has Changed

A core task of DBUPDGEN is to determine whether a file has changed between the from-version and to-version of the subsystem. The rules here are quite different between SourceSafe and TFS and I describe them separately.

SourceSafe: DBUPDGEN compares the version numbers of the file at the two SourceSafe labels. In the normal case, when the labels are in the same version directory, DBUPDGEN considers the file as changed if the version numbers are different and at least one of the new versions has been created through check-in or a rollback. (SourceSafe can create new versions of a file, despite that there are no changes. The most typical example is when you branch a file.) When the labels are in different version directories, DBUPDGEN only considers the file to be unchanged, if the version number is the same and the file is linked between the two projects. In all other cases, DBUPDGEN considers the file as changed.

TFS: DBUPDGEN first compares the changeset ids. If they are different, DBUPDGEN also checks the length, encoding and the hashes saved in TFS. This check is also performed for any checkins between from-version and to-version. The file is included in the script, only if there is at least one difference. The purpose of this activity is to avoid that about all files are included is some performs a large merge operation which changes nothing.

The reason DBUPDGEN checks the intermediate versions is that databases where the current label is between to-version and from-version may have this intermediate version. Note that this is not foolproof when you use a config-file to generate a update script between two subsystem versions. For instance, at the time the script is generated, the file abc.sp is the same in the two versions, because it was merged from the upper version of the subsystem to the lower version. However, later it is realised that the stored procedure needs to be changed in the lower version, because it depends on something only available in the higher version. This means when the update script is executed, the correct version of abc.sp will not be deployed. Note also that even if the later version of abc.sp exists in the lower version of the subsystem, when the script is generated, DBUPDGEN only checks versions in the higher version.

Note: Under rare circumstances, TFS does not generate hashes, and if DBUPDGEN finds that the hash is missing for both versions, DBUPDGEN performs a binary comparison on the contents. (If the hash is missing for one version only, the files are presumably different.)

Added, Deleted and Renamed Files

If a file exists only at the to-label for the subsystem, DBUPDGEN adds the file to the ‑update script. If a file exists only at the from-label for the subsystem, DBUPDGEN adds the file to a special section, OBSOLETE-FILES, in the update script. All files in this section are run only to make sure that the objects in these files are dropped from the database, and the file itself is deleted from the AbaPerls system tables.

If a file has been renamed, DBUPDGEN sees this as one file added and one dropped. The exception is .tbl files, see below under Table Files. There is one situation where this can go wrong with SourceSafe. Assume that at the from-label you had version 1 of alpha_sp.sp. This procedure was renamed to beta_sp.sp. By the time you arrive to the to-label, someone has added a new alpha_sp.sp again with version number 1. DBUPDGEN will now think that alpha_sp.sp has not changed between the labels. (Since version numbers in TFS are changeset ids, this cannot happen with TFS.)

Include-Files and Similar

DBUPDGEN scans all changed files that may contain a $USEDBY directive and adds all files listed for $USEDBY (provided that the file exists in the version-control directory). These files presumably perform $INCLUDE,  $REQUIRE or $DEPENDSON on the changed file, and may thus be affected by the change. DBUPDGEN perform this scan recursively. That is, if a file added in this may include $USEDBY, DBUPDGEN scans this file in this way and adds these files and so on.

Note that this includes files from other subsystems. That is, a line like:

$USEDBY BETA!some_sp.sp

will result in BETA!some_sp.sp to be included in the update script.

Assembly Files

If DBUPDGEN finds that a .dll, .cs or .vb file has changed, DBUPDGEN includes the corresponding .assem file in the update script, even if the .assem file itself is unchanged. If there is no .assem file in version-control, DBUPDGEN produces a warning and does not include the missing file. The .dll, .cs or .vb file is also included in the script, but it is commented out. DBUPDGEN scans the .assem files in the update script for $USEDBY directives, and adds all files referring to the assembly to the update script, even if they are unchanged, in case the assembly must be dropped and all dependent objects with it. For more information about the CLR in AbaPerls, see the CLR page.

Site-specific Files

If there are site-specific versions of a file, and the main file has changed, then all site-specific versions are also included in the update script. The reverse does not apply. Example: there are three files: some_sp.sp, some_sp@abc.sp and some_sp@xyz.sp. If some_sp.sp has changed, DBUPDGEN includes some_sp@abc.sp and some_sp@xyz.sp in the update script, no matter whether they have changed. On the other hand, if some_sp@abc.sp is the only file of the three that has changed, it will also be the only one included in the update script.

Table Files

If a file with the extension .tbl has changed, DBUPDGEN creates SQL code to implement the change, by moving the data from the old schema of the table to the new schema. DBUPDGEN also includes other files in the subsystem related to the table, no matter whether these files have changed or not. See further the section Table Updates for a complete discussion.

If DBUPDGEN finds a .tbl file that exists only at the to-label, DBUPDGEN unwinds the history for the file, to see if the file – and thus the table – has existed under an earlier name, so that data in the table can be retained. However, this is only fully functional for SourceSafe. For TFS there is a restriction: DBUPDGEN is only able to detect a rename if rename occurred at the most recent checked-in, or if the rename is pending and you use the ‑checkout switch.

If DBUPDGEN finds a file with the extension .tri, .ix or .fkey and there is no matching .tbl file in the subsystem, and nor does the file include a $DEPENDSON directive on a matching .tbl file from another subsystem, DBUPDGEN adds this file to the update script, even if they it is unchanged. This is the only way that DBUPDGEN can ensure that triggers, indexes and foreign keys in outer subsystems are reloaded, in case an update script in the inner subsystem needs to reload and drop the table in case of a change. (Since $DEPENDSON is mandatory for new update scripts, these loads will typically result in an error why you will need to add the missing directive.)

View Files

If a file with the extension .view has changed, DBUPDGEN also includes any files with indexes and triggers on the view, so they can be re-applied. DBUPDGEN does not consider the possibility that there are views or triggers on the view in other subsystems.

Misplaced Files

If DBUPDGEN finds a file of which the suffix does not agree with the subdirectory where the file was found, DBUPDGEN prints a warning and skips the file.

Performance Considerations for SourceSafe

Traversing SourceSafe projects is a slow procedure, so DBUPDGEN may need to run for considerable time for a large subsystem. To keep you awake, DBUPDGEN occasionally reports which file it is processing. Since it traverses each subsystem twice, you are likely to see the same file mentioned twice.

Note, there are actions that you could take to decrease the execution time for DBUPDGEN:

For TFS performance is less of an issue, and you can expect to get an update script even for a large subsystem in a minute or two. With TFS, DBUPDGEN does not print any progress information.

Checking Subsystems and Their Versions

This section discusses how the update script checks subsystems and their versions. The section applies to both regular update scripts and patch scripts. The section only applies if the ABAPERLS subsystem is installed in the database. If you have not installed the ABAPERLS subsystem, you will have to carry these checks on your own.

Which Subsystems are Updated?

The update script checks which of the subsystems in the script that are present in the database, and only runs the updates for these subsystems and ignores other subsystems. Likewise, the update script ignores subsystems in the database not present in the script.

Checking the Subsystem Labels

The update script performs a number of checks for the labels of the subsystems. There are three labels to check against each other: the label of the database (abasubsystems.ss_label) and the from- and to-labels in the script. The purpose is to determine which subsystems that needs to be updated and to verify that no update is missing in a chain of updates.

If there is no label in the database, this means "anything goes", and no further tests are performed for this subsystem. If there is a label in the database, this is always on the standard AbaPerls format LetterMajor.Middle.Minor; the database does not permit anything else. In this case the from- and to-labels must also be on this format, with one exception: as long as the database is not marked as a test or production database, the to-label may also be blank or LATEST. If not all these conditions are fulfilled, the script aborts.

The From-Version and the Database Version

DBUPDGEN compares the from-label with the label in the database, to verify that the script in sync with the database. This question is answered in the affirmative, if any of the following are true:

If neither of these conditions are true, this is an error and execution is aborted, and no subsystems are updated.

Some examples:

Database versionFrom-version OK? Comment
L4.40.0120L4.40.0120 Yes The standard case. The script starts on the database version.
L4.40.0120L4.40.0100 Yes The script starts a few versions before the database version. AbaPerls assumes that the changes between L4.40.0100 and L4.40.0120 can be re-run.
L4.40.0120L4.40.0140 No The script starts on a later version than the database version. AbaPerls cannot permit the changes between L4.40.0120 and L4.40.0140 to be lost.
L4.40.0120L4.50.0001 No The database version has not yet arrived at L4.40.1000, which, by AbaPerls label conventions, is the point where 4.50 is assumed to have been created from 4.40 with new NEWSUBSYSVER.
L4.40.1200L4.50.0001 Yes The database version is later than the divisive point at L4.40.1000, and also includes the changes from L4.40.1000 to L4.40.1200. The script presumably includes these changes too, but AbaPerls assumes that it is OK to run these changes twice.
L4.40.1000L4.50.0010 No In this case the changes from L4.50.0001 and L4.50.0010 are missing.
L4.40.0120L4.30.1200 No You can never go back to an earlier version. (And DBDUPGEN will not let you generate this script.)
L4.90.1000L7.20.0001 Yes One can assume that many versions are being lost here, but when performing this check, AbaPerls has no idea of which version directories there have been along the way, so it have no other choice than to accept that this is OK.

The To-Version

The update script checks the to-label against the database version, and if the to-label is equal or earlier than the subsystem, the subsystem is apparently already up-to-date, and the update script prints an informational message without touching the subsystem. This permits you to first update a certain subsystem separately, and yet later run a multi-subsystem script which includes the subsystem.

If the to-version is blank, AbaPerls does not check it against anything, but as noted, this is only permitted in development databases.

Updating the AbaPerls System Tables

When the update script begins processing a subsystem, it writes a START row to abainstallhistory.

For each file that is loaded, the update script updates abasysobjects as part of the regular AbaPerls file-loading process.

When the update script has processed all files in the subsystem, it writes a STOP row to abainstallhistory. Furthermore, the script sets abasubsystems.ss_label to the to-version (which may be blank).

For the rare case that you are removing a subsystem, the update script will write a REMOVE row, rather than a STOP row to abainstallhistory. The subsystem will not be deleted from abasubsystems (as there may be historic references to the subsystem), but the update scripts sets the column deregdate to mark that the subsystem is no longer present in the database.

Inside the Update Script and How it is Updated

This section describes how the update script looks on the inside, and how DBUPDGEN updates it. Recall that the script is not intended to be a one-size-fits-all solution, but cover the plain-vanilla database changes like adding new stored procedures and simple table changes. The wilder the changes are, the more likely is it that you have to edit the script. Particularly this is true if you change many tables, and the table updates depends on each other.

The Setup Part

The first part of the update script, up to the first subsystem header (see below), is something that is regenerated each time you run DBUPDGEN. This part contains initialisation, definition of which subsystems that the script covers and other internal business. Obviously, since this part is always regenerated, you are not supposed to meddle here. The one exception would be if you find that you have misspelled the subsystem name, in which case you fix that with a find/replace through the whole file.

Very first in the script, you find a header which looks like this:

#---------------------------------------------------------------------
# $Header: $
#
# DBUPDGENver:    <3.6>
# Dirpath:        <TFS::http://ww018378:8080/tfs/defaultcollection/$/AbaPerls/Kati/2.63/SQL>
# Startlabel:     <L2.63.0260>
# AbaPerls Level: <3>
# Last generated: 2013-08-14 15:45:45.
#
# Handled subsystems: <KATI>
#
# $History: $
#
# $NoKeywords: $
#----------------------------------------------------------------------
Header:, History: and NoKeywords: are SourceSafe macros that are useful when you check in the script. (NoKeywords prevents SourceSafe performing expansion of SourceSafe macros further down in the file, would they occur.) TFS users will have less use for these macros, but you will have to live with them.

When updating an existing script, DBUPDGEN reads the rows labelled Dirpath, Config, Startlabel and Handled subsystems to determine the values of the switches ‑config, ‑VC, ‑from and ‑subsystem respectively. Dirpath and Config are mutually exclusive; Dirpath appears when you use the ‑VC switch and Config when you use the ‑config switch.

The value for AbaPerls Level is assigned to an AbaPerls attribute in the start-up section of the script, and is then used by the AbaPerls file-loading process to determine whether certain checks are to be carried out. The value is set by DBUPDGEN for new scripts, and then carried over if the script is regenerated. This is to permit new checks to be added to AbaPerls without affecting old scripts.

DBUPDGENver gives the version of the format for the script. DBUPDGEN use this information to determine whether it is able to read the script at all. The version of DBUPDGEN that creates scripts in format 3.4, also knows how to read scripts in format 3.3 and 3.0, but not in 2.x, 3.5 or 4.x. When the changes in the script format are that radical so that the first part of the version number must be incremented, the old version of DBUPDGEN may be available as DBUPDGEN.OLD or similar.

After the header there is a setup part where the update script sets command-line options, which subsystems to work with, performs the checks of subsystem and labels discussed in the previous section. The script proceeds to loop through the subsystems and calls a Perl procedure for each subsystem to perform the update for the subsystem. After this loop the update script terminates.

The update script creates a special schema AbaPerls$SideSchema. This schema is used for table-updates: the current definition of the table is moved to the side schema to give place for the new. If the script already exists when the script begins running, this is alright only if the schema is empty. If there are objects in the schema, these may come from a previous table-update that failed, and the script aborts with an error that sets the DOS variable %ERRORLEVEL%. The update script attempts to drop the side schema before terminating, and if there are objects in the schema at this point, the script aborts with an error that sets %ERRRORLEVEL%. The update script also checks that there are no objects with a name starting with old_ in the database, since older update scripts did not use the side schema but renamed tables and related objects by prepending old_ to the name.

The Subsystem Parts

After the setup part, there is one Perl sub for each subsystem. Notice that it does not matter in which order the subsystems appear in the script. This is order is determined by a Perl array, which is set up in the first part of the script.

The first thing you see of a subsystem, is the subsystem header. It looks like this:

#**********************************************************************
#                              PAYMENT
#**********************************************************************

Don't touch it.

For each subsystem, there are a number of sections. Every section has a name. All sections generated by DBUPDGEN, with exception of the table-updates, have a predefined name. Here are the sections listed in the order that DBUPDGEN generates them:

When not otherwise noted, the order within a section is alphabetic. Files from other subsystem are sorted last in alphabetic order of the subsystem. (Occasionally this can result in errors when the subsystem dependency order is different, why you may need to rearrange the files manually.)

The sections are delimited with lines like the one below.

pr "#=========== TYPE ===================";

The pr command will also print the header to the log of the update script, to make it easier to see the various sections. It may also perform other actions to initiate or finalise a section.

You can change the order of the sections, and DBUPDGEN will respect this if you regenerate a script. However, you should not move the predefined sections around at whim. It's mainly the table-update sections you may need to move, as they may be dependent of each other. You can also insert your own sections to include code for special fixes. If you remove an empty predefined section, DBUPDGEN will put it back next time you regenerate the script. (DBUPDGEN does suppress some sections if they are empty, but not all.)

In most of the sections you find calls to the Perl routine abasqlfile for each changed file. abasqlfile is the core routine of AbaPerls that implements the AbaPerls file-loading process, and is also used by ABASQL and DBBUILD.

DBUPDGEN prepends ;; to all calls to abasqlfile it generates, as well as to a few more lines. Semicolon is a statement terminator in Perl, and the double semicolons do not affect the execution of the update-script as such. When you regenerate an existing script, DBUPDGEN deletes all lines that start with ;; while retaining other lines. The retained lines will appear before the newly generated lines in each section.

When DBUPDGEN regenerates a script it does not generate any new calls to abasqlfile for files that still are there after the deletion of the ;; lines. This includes calls to abasqlfile that are commented out with #, the comment character in Perl. That is, you can add calls to abasqlfile manually or remove the leading semicolons to force a certain load order, and your manual tweaks will survive the regeneration. If you find that DBUPDGEN has included a file in the update script, which you for some reason want to exclude, you should not remove the file from the script, but put a # before the call to abasqlfile.

Sometimes you may feel compelled to move a file from one section to another. This is a fair game, and DBUPDGEN will respect your change, as long as you remove the double semicolons. Just watch out that you don't move into files in or out of OBSOLETE-FILES – it's unlikely that this is the right thing to do.

Table Updates

For each changed and new .tbl file, there is a separate section in the update script. There are two cases: a new table and a changed table. For new tables, it's simply a matter of loading the files related to the table. Here is an example how it looks like:

pr "#=========== TABLE REPORTINSTRUMENTSELECTIONS ===================";
abasqlfile('reportinstrumentselections.tbl');
abasqlfile('ris_install_sp.sp');
# abasqlfile('reportinstrumentselections.xls');
abasqlfile('reportinstrumentselections.ins');
abasqlfile('reportinstrumentselections.fkey');

This particular table is a small pre-loaded table with an INSERT-file generated from an Excel book with INSFILGEN, why you see both an .xls/.srcdata and an .ins file here. The INSERT-file includes a number of calls to the stored procedure ris_install_sp. Normally, stored procedures appears in the SP section, which comes after the tables in the script. However, DBUPDGEN reads the INSERT-file and extracts all stored procedure calls in the file, and adds all procedures it finds in the file to the section for the table before the INSERT-file. (Since else the INSERT-file would most likely fail. This example does not include any trigger or index files, but had there been .tri or .ix files for this table, they would have appeared in this section.

You may note that there are no double semicolons here. If you add a table under one name and later change the name, you will need to delete the old name manually from the script.

When an existing file for a table has changed, it's a completely different level of complexity. And it should said be immediately: while DBUPDGEN generates lot of code for you, this is a situation which requires your active involvement in the script that DBUPDGEN produces. There certainly are situations where the code that DBUPDGEN produces will work just fine, but just as many times you will have to make manual tweaks to the code. The more complex and far-reaching your change, the more likely that you will have to work with the code.

In SQL Server there are two way you can change a table: ALTER TABLE and the long way. That is, create a new version of the table, copy data over, and make sure that all objects related to the table (triggers, indexes etc) are restored, referencing foreign keys are retargeted to the new table and finally the old version of the table can be dropped. In many cases, ALTER TABLE is to prefer for simplicity and performance, but there are many restrictions with ALTER TABLE, for instance you cannot add a column but as the last column in the table.

For this reason, the update script that DBUPDGEN has two alternatives: first attempt to load the table definition with abasqlfile, and if this fails it employs a fallback which takes the long way. When abasqlfile loads a .tbl file, and AbaPerls cannot drop the table because it has data or it referenced by a foreign key, AbaPerls loads the table to a temporary schema to then compares the two definitions to see if the differences can be resolved with ALTER TABLE, as discussed in detail on the file-load page. If this fails, the update script enters the fallback code.

Here follows a couple of example scenarios and what actions that AbaPerls will take and what actions they require from you. Please observe that none of the lists are exhaustive.

Here are some examples of changes that AbaPerls can implement with ALTER TABLE (and evading the need to execute to the fallback code). For more details on when AbaPerls attempts to use ALTER TABLE, see the section Pre-SQL Analysis: Reloading a Table on the file-load page.

Here are some scenarios when the fallback code is good as-is:

Note that when the sole problem is an index, you can opt to add a DROP of that index before the initial abasqlfile which then will succeed.

Some scenarios where you will have to modify the SQL code that copies the data from the old definition to the new table:

And then there are the difficult cases where you don't only need to change the SQL code, but you will also need to rearrange the Perl code. Here is one such example:

Note also that if you make changes to related tables, you may have to change the order of the table sections; DBUPDGEN makes no attempt to figure this out for you. It will place tables with no .fkey files first, but that is as smart as it gets.

It cannot be enough stressed that you must always test the update scripts on a copy of the actual database before you run live. Even if the update script ran fine in your test environment, the live database may contain data that causes the fallback code to fail, or simply takes too long time to execute. There may also be anomalies in the live database which causes ALTER TABLE to fail, even if it succeeded in your test environment. And always take a backup of the database before you start; there are no transactions in the scripts!

The fallback code that DBUPDGEN generates follows a basic theme, but there are some variations due to the conclusions that DBUPDGEN is able to draw from old version the .tbl file. Sometimes the generated code is overly complex, and if you feel like simplifying it, feel free to do so. You know the data, DBUPDGEN does not. Particularly, DBUPDGEN does not know whether it deals with a five-row lookup table or a table with hundreds of millions of rows.

To describe how a generated table-update looks like, let's look at an annotated example that follows below. The numbers in the right margin refers to the comments that follow the code. Beware that depending on the table, DBUPDGEN will not generate some of the code.

pr "#=========== TABLE EVENTPOSITIONS ===================";
if (not abasqlfile('eventpositions.tbl'))               <--- 1 ----
{                                                       <--- 2 ----
my($sql, $tbl, $old_tbl);
my $stat = 1;                                           <--- 2 ----
$tbl = 'eventpositions';                                <--- 3 ----
$old_tbl = aba_move_aside($tbl);
abasqlfile('eventpositions.tbl');                       <--- 3 ----
abasqlfile('eventpositions.ix');                        <--- 4 ----
abasqlfile('eventpositions.tri');                       <--- 5, 6 ----
#----------- Data shuffling starts here -------------
my $fkey = aba_tblfkey($tbl, $old_tbl, "evpid");        <--- 7 ----

my $batchsize = 5000000;                                <--- 8 ----
my $batchcol  = 'eveid';                                <--- 8 ----

abasql("EXEC sp_unbindrule '$tbl.moduser'");            <--- 9 ----

setup_for_datamove($tbl);                               <--- 10 ----

$sql = <<SQLEND;                                        <--- 11 ----
DECLARE \@startkey int,
        \@stopkey  int,
        \@err      int,
        \@rowc     int,
        \@rowctot  int,
        \@msg      varchar(255),
        \@time     char(8),
        \@rowcold  int                                 <--- 11 ----

SELECT \@err = 0, \@rowctot = 0                        <--- 12 ----
SELECT \@rowcold = COUNT(*) FROM $old_tbl
SELECT \@msg = 'Copying of ' + ltrim(str(\@rowcold)) + ' rows in $tbl started ' + convert(char(8), getdate(), 8)
RAISERROR('%s', 8, 1, \@msg) WITH NOWAIT               <--- 12 ----

SELECT \@startkey = MIN($batchcol) FROM $old_tbl       <--- 13 ----
WHILE \@err = 0 AND \@startkey IS NOT NULL
BEGIN
   SELECT \@stopkey = MAX($batchcol)
   FROM   (SELECT TOP $batchsize $batchcol
           FROM   $old_tbl
           WHERE  $batchcol >= \@startkey
           ORDER  BY $batchcol) AS x                   <--- 13 ----

   INSERT $tbl WITH (TABLOCK) (                        <--- 14 ----
             evpid, eveid, accno, depno, insid, poseffect,
             cmpcode, cmpaccno, qty, loanqty, newcmpcode,
             newcmpaccno, isownerreg, execno, trgid, conid,
             statustext, regdate, moddate, moduser)
      SELECT old.evpid, old.eveid, old.accno, old.depno, old.insid, old.poseffect,
             old.cmpcode, old.cmpaccno, old.qty, old.loanqty, old.newcmpcode,
             old.newcmpaccno, old.isownerreg, old.execno, old.trgid, old.conid,
             old.statustext, old.regdate, old.moddate, old.moduser
      FROM   $old_tbl old
      WHERE  old.$batchcol BETWEEN \@startkey AND \@stopkey
   SELECT \@err = \@\@error, \@rowc = \@\@rowcount
   SELECT \@rowctot = \@rowctot + \@rowc, \@time = convert(char(8), getdate(), 8)
   SELECT \@msg = str(\@rowctot) + ' row(s) copied. ' + \@time
   RAISERROR('%s', 0, 1, \@msg) WITH NOWAIT           <--- 14 ----

   SELECT \@startkey = MIN($batchcol)                 <--- 15 ----
   FROM   $old_tbl
   WHERE  $batchcol > \@stopkey                       <--- 15 ----
END

IF \@err = 0 AND \@rowctot <> \@rowcold               <--- 16 ----
   RAISERROR('There are %d rows in "$old_tbl", but %d rows in "$tbl"!', 16, 1, \@rowcold, \@rowctot)
SQLEND
$stat = abasql($sql);                                 <--- 17 ----

$stat *= wind_down_datamove($tbl, $old_tbl);          <--- 18 ----
                                                      V--- 19 ----
$stat *= abasql("EXEC sp_bindrule 'aba_upduser_rule', '$tbl.moduser'");
                                                       
$stat *= abasql($fkey) if $stat;                       <--- 20 ----
#----------- End of data shuffling -------------

$stat *= abasqlfile('eventpositions.fkey');            <--- 21 ----
drop_table_after_datamove($tbl, $old_tbl) if $stat;    <--- 22 ----
}   # End of update of eventpositions
else {
   abasqlfile('eventpositions.ix');                    <--- 23 ----
   abasqlfile('eventpositions.tri');
   abasqlfile('eventpositions.fkey');                  <--- 23 ----
}
  1. This is the point where the update script attempts to use abasqlfile directly, and if abasqlfile succeeds the following block as defined by the braces will not be entered, but execution will jump to step 23. There may be situations where AbaPerls is able to implement the change with ALTER TABLE, but you nevertheless want to make some manual initialisation. In such case, you need to remove this first line or replace the subsequent block with code that performs your task.
       If abasqlfile fails, there will be error messages in the error log. However, if you use LISTERRS to read the log, you will not see these errors, since the file will be a loaded a second time in step 3. If you expect ALTER TABLE to work, read the log with LISTERRS with severity 8 and make sure that you don't see a reload entry for the table.
       Note that if the ABAPERLS subsystem is not present in the database or is below version L1.0.0270, abasqlfile will always fail, unless the table is empty and not referenced by foreign keys, since the stored procedure that implements ALTER TABLE is absent.
  2. Here we enter the fallback code. The first part is just general Perl initialisation. The left brace defines a block in Perl, and all variables declared in the block are only visible in that block. This block encompasses the entire fallback, steps 2 to 22 in this description. (That is, the closing brace is before the final else. In Perl, variables are declared with my. Scalar variables always have a leading $ in the name. But apart from that there is no data type listed in the declaration, since Perl is a dynamically typed language. The variables declared here – $sql, $tbl, $old_tbl – are always used in table updates. Other variables declared as needed later on. The variable $X is declared in the beginning of the script. $X is the general AbaSql object which encompasses all the internals of AbaPerls.
  3. This step defines the table to be updated and loads the new definition of the table after first having moved the old definition of the table to the side schema, AbaPerls$SideSchema with aba_move_aside which returns name the two-part name of the table after the move, that is, AbaPerls$SideSchema.eventpositions in this example.
        If the table has been renamed in version-control, the call to aba_move_aside will have the old name of the table as the third parameter. The table is moved to the side schema under the new name. That is, say that the script reads:
    $tbl = 'mynewtablename';
    $old_tbl = aba_move_aside($tbl, undef, 'myoldtablename');
    $old_table will be set to AbaPerls$SideSchema.mynewtablename.
        If the table is referred to by a view defined WITH SCHEMABINDING, the view and all objects referring to the view will be dropped at this point, and aba_move_aside produces message ‑1001 to inform you of all dropped objects. (If these objects are later reloaded, LISTERRS will not display this message.)
  4. Next any file with non-key indexes is loaded. See further under steps 10 and 18 about indexes.
  5. At this place DBUPDGEN inserts a call to abasqlfile to load any .tri file here to restore triggers in the same subsystem. The idea is that the trigger perform data-integrity checks that may detect error in the data-move. However, there are situations where you need to move this line to after the data-move (at step 21): For the first two points, it's better to write the trigger to exclude such code by putting it inside this IF statement:
    IF object_id('tempdb..#update$script') IS NULL
    The update script creates the table #update$script when it starts up for the sole purpose to permit you to disable parts of triggers. When performance is the problem, I recommend that you wait with moving the trigger until you have run the update script in test databases, and you feel sure that there are no data-integrity issues.
       As for triggers in other subsystems, there will be a warning in the log file reminding you that these need be to reloaded.
  6. (Not shown in the example.) At this place, DBUPDGEN loads stored procedures invoked by any INSERT-file for the table. (The INSERT-file itself is loaded at step 21.)
  7. The routine aba_tblfkey returns SQL code that moves the foreign keys referencing the old table to refer to the new table. This SQL code is executed at step 20. (aba_tblfkey itself does not access the database.) DBUPDGEN generates this line when it finds a PRIMARY KEY constraint in the table definition; it does investigate whether there actually are any referencing FKs. The third parameter is the PK columns according to the old definition of the table.
       Internally, this routine has two forks, a main fork and an alternative fork which you force by adding a fourth parameter. You need to do this, if you have changed the definition of any of the primary or unique keys in the table.
       When you use the main fork, the third parameter is ignored, and the assumption is that the key definition is the same in both versions of the table. The main fork handles all foreign-key actions: NO ACTION, CASCADE, SET NULL and SET DEFAULT.
       When you use the alternate fork, you must make sure that the third parameter agrees with the new definition of the primary key. If the table has a multi-column key, separate the columns with comma, for instance "keycol1, keycol2". The alternative fork does not support all foreign-key actions, but only NO ACTION and CASCADE
  8. These two lines appear if DBUPDGEN deems that it is suitable to move the data in batches. DBUPDGEN generates code for batching, unless any of the following conditions is true: The variable $batchsize defines the minimum size of a batch. DBUPDGEN always set the variable to the value 5000000 (five million), but you can change this if you find reason to. The variable $batchcol defines the column which is used to control the batch. DBUPDGEN always sets $batchcol to the first column of the clustered index. From experience, batching not based on the clustered index can be painfully slow (and this is why DBUPDGEN does not generate batching code for tables without a clustered index).
  9. If a column in the table has a datatype that ends in _upduser, DBUPDGEN assumes that this column has a rule that requires the value of the column be the name of the current user. To make it possible to keep the existing values, DBUPDGEN inserts a call to sp_unbindrule for this column. It is restored at step 19.
  10. The routine setup_for_datamove, performs a number of actions to prepare for the data move: Occasionaly, if you want to run the data-move with the triggers loaded, you may need one of the non-clustered indexes to be enabled. Then you can reenable the index after the call to setup_for_datamove. The same applies if you need to add a temporary index which you drop after the data-move.   
  11. Here starts the SQL-batch that performs the data-move. Section 11 is just a declaration of variables; some of these variables will be absent if there is no batching. Because @ has a special meaning to Perl, the @ has to be escaped to be interpreted literally, whence all the backslashes. As you see, there are Perl variables in the SQL text; Perl expands these variables to their actual values. The syntax
    $sql = <<SQLEND;
    means that the variable $sql is set to everything that follows to the line that starts with SQLEND. Note that the closing SQLEND must be in the first column; it cannot be indented.   .
  12. These statements initialise some T‑SQL variables and print a message of how many rows that will be copied. This and subsequent messages are printed with RAISERROR WITH NOWAIT so that you at any time can look at the log from the update script to see how far it has come.
  13. This is where batching is controlled. Before the loop starts, the smallest value of the batch column is retrieved, and the first statement in the loop sets @stopkey to the value that is $batchsize rows away from the "first" row. If there is no batching, there is no WHILE loop.
  14. This is the data-move itself. Here you often need to supplement the generated code, depending on what changes you have made to the table.  DBUPDGEN includes all columns in the old definition with three exceptions: 1) Computed columns. 2) Columns of the timestamp (a.k.a rowversion) data type. 3) Columns not present in the new definition of the table. Note that the latter means that if you have renamed a column, it will not be included in the script! The WHERE clause defines the batch. Since the batch includes all rows where the batch column equals @stopkey, the actual batch size can be considerable larger than $batchsize.
       After the INSERT statement, there are checks for error status and row count, and the code prints a message on how many rows were copied, so you can see in the log how fast (or slow) the INSERT-loop proceeds.
  15. This step determines the starting value for the next batch.
  16. At the end of the batch for the data-move there is a check of how many rows that were copied to make sure that no rows were lost. Very occasionally, you make a table change where you rearrange the data in such away that the number of rows is expected to change. In this case, you will need to remove this check – or find a way to modify it to keep this assertion. (Which is probably even more important if you are making really advanced changes.)
  17. Here is where the data-move batch is actually executed. The Perl variable $stat will be set to 1 if the data-move completes without error, else 0.
       The SQL script is executed by abasql, which runs the SQL batch without the bells and whistles of abasqlfile but one: it honours the setting of the configuration option ‑quoterepl, and replaces double quotes with single quotes if this option is active.
  18. At this point, the update script calls wind_down_datamove that perform these actions: The purpose of the *= operator (with the same meaning as in T‑SQL), is to make sure that $stat is set to 0 when something fails and then retains this value.
  19. The rule for any _upduser column is restored.
  20. This is where the commands to move the referencing foreign keys generated in step 7 to the new table are executed. The commands are printed to the log file. Note that if the data-move or any of the subsequent steps failed, AbaPerls will not try this step.
  21. Once the data move has completed, the update script loads any .ins and .fkey files for the table in that order. The script captures the status only for the foreign-key file.
  22. Finally, the old version of the table is dropped, but only if $stat is > 0. The DROP is packaged into the helper routine drop_table_after_datamove which also produces the warning message ‑1001 if there are triggers, indexes or foreign keys on $old_tbl in other subsystems to inform that these have been dropped and needs to be reloaded.
  23. Thus, if something goes wrong, the old version of table is left around so that you can investigate the problem. Any existence of tables in AbaPerls$SideSchema when the script completes generates an error message in itself.
  24. When abasqlfile of the .tbl file succeeds, the update script enters the else branch to load all files directly related to the table to make sure that they are reloaded, no matter if they have changed or not. This is because if they would be changed later, and you would regenerate the update script, DBUPDGEN would be content with finding these files in the fallback code. Thus, don't remove the files listed here.

As you can see there are no ;; in this code, thus everything will remain if DBUPDGEN regenerates the script. Occasionally you may want to delete the data-move, because you don't want to preserve old data at all. In this case, you can delete everything between these two lines, but preserve the lines themselves.

#----------- Data shuffling starts here -------------
#----------- End of data shuffling -------------

If you delete these lines, DBUPDGEN will regenerate the data-move. Also be careful with this line:

$tbl = 'eventpositions';

If you remove it or change it, and then regenerate the script, DBUPDGEN may put the line back together with the calls to aba_move_aside.

If DBUPDGEN generated batching, and you feel that you don't want it for whatever reason, delete steps 8, 13 and 15, and the WHERE clause in step 14. It's important to delete step 8, because then Perl will produce compilation errors if you left $batchcol or $batchsize around somewhere. It's also a good idea to delete the declaration of the variables @startkey and @stopkey.

If DBUPGEN did not generate batching, you will have to add it by hand. One thing to keep in mind: the scheme used by DBUPDGEN does not handle nullable columns; you would need to add a separate INSERT statement to handle any NULL values in the batch column.

Patch Scripts

The option ‑patch permits you construct a patch script to deploy a selected set of files from one or more subsystems. This can be useful when you don't want to deploy an entire new version, but only the fix for a specific problem.

When you work with patch scripts, the only options for regular update scripts that are applicable are ‑listonly and ‑config, of which the later only serves to define the path for the subsystems. To this comes ‑reverse that helps you construct a reverse patch, more about this later.

With ‑patch, you specify which files that are to be included in the script in patch-file. In a patch file you first specify a header for the subsystem with the subsystem and switches for the subsystem. This is followed by lines of files to include from this subsystem. Optionally you can specify an explicit version for the file. Text after -- up to end-of-line is a comment. Blank lines are ignored.

The Subsystem Header

The syntax for the subsystem header is as follows:

[subystem] -base_version base-version [-to_version to-version] [-path path]

Note! The first pair of brackets here does not represent optional syntax, but the brackets here stand for themselves. (See the example below.) The switches are interpreted by the same module that handles command-line switches. However abbreviations are not permitted; they must be spelled out in full. If an argument includes spaces, you must enclose it in quotes. Here is a description of the switches:

‑base_versionbase-version is akin to from-version for a regular update script. The patch script will fail  if the version for the subsystem is before base-version or for a different major.middle as described in the section Checking Subsystems and their Versions. For ‑base_version you should specify the lowest version you think the patch will work with. For instance, if one of the changed files refers to a table change added in version L2.20.0070, you should specify L2.20.0070 or later as the base version. Note that this option is mandatory. base_version must be an AbaPerls label on the format LetterMajor.Middle.Minor.
‑to_versionThis option permits you to set a default version for the files listed for the subsystem. If you don't provide ‑to_version, you have to specify the version for each file explicitly. to-version can be a standard AbaPerls label, some other label, a date, an explicit version number or LATEST.
‑pathSpecifies the path for the subsystem. If you don't provide ‑path, but you specify the ‑config switch on the command line, DBUPDGEN takes the path from the config-file. When reading a config-file for a patch script, DBUPDGEN only cares about the path and ignores all other settings for the subsystem in the file.  As with regular update scripts, the config-file must reside in version-control; it cannot be on disk. (The only reason you would specify a config-file is that you have one around; you would not compose one just for a patch script.)
   If you specify neither ‑path, nor ‑config on the command line, DBUPDGEN attempts to determine the path from the sysdef-file. Beware that if DBUPDGEN cannot find SYSTEM.DEFINITION, the error message will be cryptic: AbaPerls::SysDefFile::get_file_contents was passed empty file path.
   Note also that if you specify ‑config on the command line, DBUPDGEN will not consider the sysdef-file.

Note that in the generated patch script, the subsystems will be loaded in the same order as they appear in the patch-file.

The File Entries

When you list the files, you specify the file names, as you specify file names to ABASQL; that is you don't include SP or any of other special directory names. If the file is in a subdirectory, you must specify the subdirectory. If you did not specify ‑to_version for the subsystem, you must specify a version for the file. If you specified ‑to_version, you can still provide a version to override to-version. The version you specify for a file can be an explicit version number, a label on any format, a date or LATEST. Note the following:

Example

Here is an example of a patch file:

-- This is a comment
[TEST] -base_version L2.1.0200 -to_version L2.1.0320 -path "data6/$/blank test"
sub/at_install_sp.sp  
ael_get_sp.sp  
ael_insert_sp.sp 
some_new_sp.sp 1

[KATI] -base_version L2.71.0070 
cps_listig_sp.sp 2013-08-20
kat_load_sp.sp 18

Here I have specified ‑path for and ‑to_version for the subsystem TEST, but left them out for KATI, where I rely on ‑config or the sysdef-file and I specify explicit version for each file. (When it comes to ‑path, this is an odd thing to do. Normally, you would specify ‑path for all subsystems or for none of them.)

Running the Patch Script

The execution of a patch script is very similar to the execution of a regular update script. Particularly, when loading the a file, AbaPerls will perform the same version checks, and in a production database, AbaPerls will not override a later version in a database with the version from the patch script. Patch scripts writes information about start and stop to abainstallhistory, but they do not update the label in abasubsystems.

Rules for Special files

For patch scripts, DBUPDGEN performs some of the special file actions, but not all. The following applies:

Regenerating Patch Scripts

You can regenerate a patch script just like you can regenerate an update script and this works in the same way. That is, DBUPDGEN will delete files no longer listed in the patch file, unless you removed the double semicolon before the load of the file. If you don't specify ‑patch or ‑reverse on the command line, DBUPDGEN will read this information from the script itself. If you specify command-line switches, they must agree with the settings saved in the script. That is, you cannot turn a patch script into a regular update script or vice versa. Note for patch scripts, DBUPDGEN does not save the setting for ‑config in the update script, but you must specify it every time.

Reverse Patch Script

It may happen that you have delivered a patch, but you decide that you want to undo it by shipping the versions of the files that were previously loaded in the database. To this end, you should specify the option ‑reverse on the command line. This causes DBUPDGEN to set the AbaPerls property Low_version_force in the beginning of the script to suppress the version-check in production databases. Apart from that, reverse scripts works exactly as regular patch scripts. Specifically, note that you cannot include versions earlier than the specified base version for the subsystem.

Advanced Topics

In this section I cover various advanced tricks you may need in your update scripts. Please be aware of that I may refer to some Perl constructs that you are not acquainted with. In interest of brevity, I am explaining them very briefly or not at all. Please see suitable Perl documentation if you need to know any details.

Raising Messages and Errors

As shown in the example in the previous section, you can freely use RAISERROR and PRINT to produce errors, warning and informational messages. There is one thing to keep in mind: do not specify severity level 10 if you want to emit a warning. SQL Server changes this to severity level 0, so in practice this is the same as a PRINT statement. This matters if you want to use LISTERRS to read the log. LISTERRS looks for the severity level of the messages, but if the severity level is 0, AbaPerls takes this as an informational message, and does not print the severity level, why LISTERRS will not find it. Use severity level 9 instead. LISTERRS includes messages with level 9 by default for update scripts. You could also use 8, if you only want to see the messages when you make a closer inspection of the log.

Referring to Objects in the Side Schema

If you need to refer to an object in the side schema, the best is to use the return value from aba_move_aside. If you want to refer to the side schema explicitly, you should use the variable $ABAPERLS_SIDE_SCHEMA which is set in the beginning of the update script.

Final Epilogue

If you add a Perl sub by the name of final_epilogue, the update script will call this procedure. This can be useful when you have an update script that encompasses several subsystems, and you need to perform some clean-up at the end of the script and which can not be put into the epilogue of a certain subsystem. See below under Dropping a Data Type, for cases where you may need this.

Loading Files from Other Subsystems

As noted above, DBUPDGEN may add a file from a different subsystem, if this file is listed in $USEDBY in the current subsystem. For instance, if the file compute.sqlinc in the subsystem ALPHA includes the line

$USEDBY BETA!caller_sp.sp

DBUPDGEN will include this line in an update script for ALPHA:

;; abasqlfile('BETA!caller_sp.sp');

BETA!caller_sp.sp will then be loaded according to the rules for that subsystem. (That is, if ‑nocrypt applies for ALPHA, but BETA has ‑crypt, caller_sp will be loaded WITH ENCRYPTION.)

When loading a "foreign" file like this in a database labelled TEST or PROD, the load will only succeed if the MD5 hash for the matches the MD5 hash for the file in abasysobjects. Else the load will result in an error message. In the latter case, there will supposedly be an update script running for the other subsystem (BETA in this example) later, and if both update scripts write to the same log file (using the ++ prefix with the ‑log option), LISTERRS will not display the first error if the second load went well. That is, the purpose of including the foreign file is to make sure that it is refreshed in the database, even if the foriegn file is unchanged.

If you wish to add a file from a different subsystem to the update script yourself, you can do so. However, be sure to regenerate the script with DBUPDGEN unless there are other files from that subsystem in the script already. This is because the setup part of the script includes a part where it defines all subsystems concerned. If you neglect to regenerate the update script, the load of the file will fail.

Datatype Changes

If a scalar datatype changes between from-version and to-version this is what the upgrade script needs to do:

DBUPDGEN does not generate this for you! Type changes simply occur too rarely (at least in my world) to be worth the effort. Besides, if a .typ file has changed it could be that only the rule or the default for the type has changed, in which case loading the .typ file is all that needs to be done. No need to reload tables etc.

Thus, if you have changed a data type, you need take care of the above yourself. Moving the type to the side schema is not difficult, but you need to use the second parameter to state the object type:

my $old_kati_upduser = aba_move_aside('kati_upduser', 'TYP');
abasqlfile('kati_upduser.typ')

For a table type, you should specify TTY and for an XML schema collection you should use XSC.

The best approach for the affected tables is probably to make a dummy check-in or just check them out and use the ‑checkedout switch, and then let DBUPDGEN pick up the tables. As for stored procedures, functions and views, you could do the same or add them manually to the update script. Remove any ;; so that the files stay in the script if it is regenerated.

If the type is used for a primary key in one table and foreign keys in one or more table, you cannot move the foreign keys referencing the main table directly, as foreign-key constraints requires the columns to have the same type. If you simply skip the fkey-move and reload all referencing tables, you should be able to drop the old definition of the table in the side schema at the end of the script, either in the epilogue of the subsystem, or in a final epilogue of the script. To drop the type, use the variable you got from aba_move_aside.

abasql("DROP TYPE $old_kati_upduser");

Note that you need double quotes; if you use single quotes Perl will not expand the variable value.

What is said in this section also applies to XML schema collections. For table types, this should be less of concern since AbaPerls drops all functions and procedures when uses the table type – but only if they are listed in abasysobjects, why there could be problems if there are stray objects.

Dropping a Data Type

When you drop a data type, you may not be able to drop it in OBSOLETE-FILES, because there are views, stored procedures or functions that refers to the type in columns/parameters (local variables does not matter), and the views/procedures/functions appear later in the update script. In this case, you may prefer to move the call to abasqlfile to drop the type to the EPILOGUE. In this case, you also need to copy the preamble and the conclusion of the OBSOLETE-FILES to the EPILOGUE.

In the case that the type is referenced by other subsystems, you could build an update script that covers all subsystems, and use a final epilogue where you drop the type. As above you would copy parts of OBSOLETE-FILES, but you would have to set the subsystem and the paths accordingly.

Dropping an Indexed Column

If you drop an indexed column from the table, abasqlfile will fail when it runs ALTER TABLE and thus the fallback code will be entered. There is a simple workaround for this. Move the load of the index file to come before the initial attempt to load the .tbl file:

pr "#=========== MYTABLE ===================";
abasqlfile('mytable.ix');
if (not abasqlfile('mytble.tbl'))                
{

Under the assumption that you have also changed the .ix file to not include the index on the dropped column, the index will be dropped before the table is reloaded, and thus ALTER TABLE will succeed.

Forcing ALTER TABLE in abasqlfile

The default behaviour of abasqlfile when you load a .tbl file is drop and recreate the table if possible. This happens when there is no data in the table and there are no referencing foreign keys. There are situations when this is not what you want. Say that you are considering a non-essential change to a table. You don't want to make this change if this leads to an expensive table reload, but in your test environment, the table may be empty and you don't want to add data just to be sure. Therefore, you would like to force abasqlfile to attempt ALTER TABLE. You can achieve this by adding this line after the SUBSYSTEM-INIT section:

$X->{Force_alter_table} = 1;

Note that if you have multiple subsystems in the update script, and you only want to force ALTER TABLE for one subsystem, you need to include a line to set the property to 0 for the other subsystems as $X is a global object.

If you force ALTER TABLE for the reason given above, you may also want to make sure that you get an explicit error in case ALTER TABLE fails:

if (not abasqlfile('delayedacqcalculations.tbl'))
{
my($sql, $tbl, $old_tbl);
my $stat = 1;
$tbl = 'delayedacqcalculations';
$old_tbl = aba_move_aside($tbl);
abasql($sql);
abasqlfile('delayedacqcalculations.tbl');
abasql("RAISERROR('$tbl is not supposed to be reloaded.', 16, 1)");
#----------- Data shuffling starts here -------------
#----------- End of data shuffling -------------

$stat *= abasqlfile('delayedacqcalculations.fkey');
abasql("DROP TABLE $old_tbl") if $stat;
}   # End of update of delayedacqcalculations
else {
   abasqlfile('delayedacqcalculations.fkey');
}

Removing everything but the call to abasqlfile is not a good idea, since DBUPDGEN will re-add the code if you regenerate the script.

Using ALTER TABLE and sp_rename

There are situations where AbaPerls gives up on ALTER TABLE, or attempts in such a way that it fails. An example of the former is when you both drop and add a column at the end of the table (AbaPerls cannot say for sure that the column has been renamed). An example of the later is when you change the data type of an indexed column. In the latter case, the index must be dropped and reloaded.

If you want to implement a table-update with ALTER TABLE in such situation to save execution time, your table-update could look as in this example:

pr "#=========== ACCOUNTVALUES ===================";
if (not abasqlfile('accountvalues.tbl'))                 <-- 1 ---
{
my($sql, $tbl, $old_tbl);
my $stat = 1;
$tbl = 'accountvalues';                                  <-- 2 ---
#$old_tbl = aba_move_aside($tbl);                        <-- 3 ---
#----------- Data shuffling starts here -------------
abasql(<<SQLEND);                                        <-- 4 ---
ALTER TABLE $tbl DROP COLUMN some_old_column float 
ALTER TABLE $tbl ADD settleday_owncapital float NULL
SQLEND
#----------- End of data shuffling -------------

abasqlfile('accountvalues.tbl');                         <-- 5 ---
abasqlfile('accountvalues.tri');
abasqlfile('accountvalues.ix');                          <-- 6 ---
$stat *= abasqlfile('accountvalues.fkey');               
  
} # End of update of accountvalues
else {
   abasqlfile('accountvalues.tri');
   abasqlfile('accountvalues.ix');
   abasqlfile('accountvalues.fkey');
}                                                        <-- 6 ---
  1. This line you can retain or remove. It doesn't matter.
  2. Don't touch this line; if you remove it, DBUPDGEN will re-add it if you re-generate the script.
  3. Comment out the call to aba_move_aside to prevent the move to the side schema.
  4. Rip out all generated code in the data-shuffling section in favour of your ALTER TABLE commands. The same applies if you want to use sp_rename because you have renamed a column.
  5. Move the loading of the .tbl and .tri files to after the data-shuffling section. Provided that the target database has version L1.0.0270 or later of the ABAPERLS subsystem, AbaPerls will compare the definition in the .tbl file with the result of your ALTER TABLE operations. This way you can get an acknowledgement that your ALTER statements are correct.
  6. Retain the loading of other tables related to the file, just in case. Particularly, you need the index file, if you had to drop an index to perform ALTER TABLE ALTER COLUMN.

If you need to run the update script on a database which does not have ABAPERLS L1.0.0270, you can load the .tbl file this way:

abasqlfile('accountvalues.tbl', undef, undef, 1);

This instructs AbaPerls to only load metadata for the table. It goes without saying that in this case, it is your responsibility to make sure that you have changed the .tbl file so it agrees with the result of the ALTER TABLE commands.

Tip: if you change the data type of a column in a non-clustered index, you can use the pattern above, and include a DROP INDEX statement before your ALTER TABLE statement. The index will be re-added when the .ix file is loaded. If you change the data type in your clustered index, you can do the same – but you probably shouldn't. Dropping and rebuilding the clustered index is likely to be more expensive than reloading the table.

Checking if a Table-Update Has Already Been Run in a Database

An update script includes a table-update that may already have been carried out in some databases in which the script is to be run. In the very most cases the initial check with abasqlfile takes care of this.

But in rare situations this may not be what you want, for instance because you don't want abasqlfile to use ALTER TABLE and you want to enter the fallback code. However, you don't want to enter the fallback code if it has already been executed in the database in question. To make this situation easier to handle, AbaPerls offers the utility function aba_check_column. This function takes five parameters:

$tblName of table, mandatory.
$colName of column, mandatory.
$usertype Name of user-defined type, for instance ap_subsystem.
$phystype Name of physical datatype, for instance float, varchar(16).
$property A property accepted by the SQL function objectproperty, and for which the value may be 0 or 1.

The function returns 1 if the column $col exists in the table $tbl. If you have specified $usertype or $phystype, then $col must have this datatype for aba_check_column to return 1. You can also pass a property for aba_check_column to check for. Examples of such properties are AllowsNull and IsIdentity. The idea is that you can conclude whether a table-update already has been run, by checking a column affected by the update.

The simplest way to use this check is simply to replace the test at point 1 (see the example above) in the table-update:

pr "#=========== TABLE EVENTPOSITIONS ===================";
if (not aba_check_column('eventpositions', 'newcol')) 
{
my($sql, $tbl, $old_tbl);
....
}   # End of update of eventpositions
else {
   abasqlfile('eventpositions.ix');
   abasqlfile('eventpositions.tri');
   abasqlfile('eventpositions.fkey');
}

aba_check_column always returns 0 when ‑noexec is in effect, as in this case you always want all files to be extracted.

Getting Data Back from SQL Server to the Update Script

You have seen that you can execute a loose batch of SQL statements with the routine abasql and abasql will return 0 if the batch completes without errors. If the batch produces a result set, the result set will be printed in a very simple way.

But what if you need to get data back from the database to use in the Perl script? Here is an example:

use vars qw($ost_ityid, $hasactive);
abasqlfile('zz_ins_from_postype_t_sp.sp');
if ($opt_exec) {
   my $mssql = $X->{MSSQL_handle};
   $mssql->sql_sp('zz_ins_from_postype_t_sp', [\$ost_ityid, \$hasactive]);
   if ($hasactive) {
      print $LOG "Oops! There are active postype-t in this database!\n";
   }
}

The example calls a stored procedure that returns data in two output parameters. The data is placed in the parameters $ost_ityid and $hasactive. They are declared with use vars, as the update script may need to refer to them further down the track.

The check for $opt_exec is an important matter, so we don't try to call the procedure when we are running with ‑noexec.

The line

my $mssql = $X->{MSSQL_handle};

retrieves a database handle of the type Win32::SqlServer, which is a general library for accessing SQL Server from Perl. We then use the function sql_sp to call the procedure. For further details on Win32::SqlServer, please see the documentation for this library which you can find at this external link. (This documentation is available at your site as well, since Win32::SqlServer is a prerequisite for running AbaPerls.) Win32::SqlServer offers many ways to get data back, but I leave to the reader to explore those possibilities on your own. Here is one more example, which I leave uncommented:

my $has_i2c = 0;
if ($opt_exec) {
    my $mssql = $X->{MSSQL_handle};
    $has_i2c = $mssql->sql_one(<<SQLEND, Win32::SqlServer::SCALAR);
    SELECT COUNT(*)
    FROM   sys.columns
    WHERE  object_id = object_id('$old_tbl')
      AND  name = 'i2ccode'
SQLEND
}
if ($has_i2c) {
   # Do something

Note: you could do all sorts of wild things once you have gotten hold of the $mssql object, but don't expect everything you do be supported by AbaPerls. Handle with care.

Printing Messages to the Log

In the previous section you saw a reference to a variable $LOG. This is the log that the update script produces. You can print to this log as shown in the example above. If you want to print an error message, so that it is displayed by LISTERRS, then format beginning of the message as an SQL message, for instance:

print $LOG "Msg 0, Severity 11, dbupdate-7.10.pl\n";
print $LOG "There's too much coffee in this database to be healthy.\n";

Moving Objects between Subsystems

Normally, AbaPerls does not permit you to create an object with a name that is already in use in another subsystem. This is a protection against unintended name clashes. However, occasionally you may want to move an object from one subsystem to another, in which case you may need to turn of this check. Typically, you will always need to do this when you move an object from an outer subsystem to an inner. If you move an object to an outer system, it may not be necessary depending on which order you run the update scripts.

As long as you are not moving stored procedures, you simply set the AbaSql attribute Subsys_check_disabled around the objects to move:

$X->{Subsys_check_disabled} = 1;
abasqlfile('filetomove1.sqlfun');
abasqlfile('filetomove2.sqlfun');
...
$X->{Subsys_check_disabled} = 0;

When you have moved stored procedures, you cannot set the Subsys_check_disabled attribute in the SP section, so you need to put the moved procedures in a different section than SP. For instance, you could move them to FUNCTIONS which is just above:

pr "#=========== FUNCTIONS ===================";
$X->{Subsys_check_disabled} = 1;
abasqlfile('one_moved_sp.sp');
abasqlfile('another_moved_sp.sp');
$X->{Subsys_check_disabled} = 0;

If you attempt to set Subsys_check_disabled (or any other AbaSql attribute) in the SP section, the update script will abort. When the update script runs the SP section, it buffers the files and don't load them until it comes to the next section. Thus, if you would set and reset Subsys_check_disabled in the section, the load would be performed with the check enabled. To prevent this from happening, AbaPerls does not permit changing any AbaSql attributes while buffering is in force.

Keep in mind that since you are outside the SP section, you need to arrange the procedures so that there will not be any errors due to missing procedures. For instance, if the moved procedures calls a new procedure in the new subsystem, you will need put the load of the moved procedures in a section after SP.

It is worth pointing out that you should lower the guard only around the very objects you have moved to avoid unwanted accidents. Particularly, you want the subsystem check to be in force in OBSOLETE-FILES as this is where the files end up in the script for the subsystem. That is, the check prevents the objects from being dropped if they have been loaded for the new subsystem.

Note that if you are moving tables, you may want to comment them out from the OBSOLETE-FILES section of the old subsystem, to avoid they are dropped and you lose data.

Forcing the Install of a Lower Version in a Production Database

If the database has been labelled as a production database with the config-option ‑environment, an update script will normally skip to load a file, if the version that already is in the database has a higher version number than the version that update script would load. You can override this by setting the AbaPerls property Low_version_force:

$X->{Low_version_force} = 1;

Set it back to 0, if you only want to do this for some files.

Observe that you cannot change this attribute in the SP section of the update script as discussed in the previous section.

Checking the SQL Server Version

To check the version of SQL Server, do like this:

if (AbaPerls::VersionCmp::version_ge($X->{SQL_version}, 10))

The value of $X->{SQL_version} is the same as the Preppis macro &SQL_version, and the same comparison rules apply as do for the macro. However, you don't have access to predefined macros like &SQL8 etc available, but you need to use hard version numbers. You cannot use >, >= etc for comparison, but you have to use version_gt, version_ge etc.

The $X Object

In the previous sections you have seen $X appear in a lot of places. $X is declared in the first part of the update script, and is an AbaPerls::AbaSql object. As you might guess, $X gives you access to the very core of AbaPerls. And an update script is codewise just as much part of AbaPerls as are DBBUILD and ABASQL.

That does not mean that any usage of $X is permissible. Consider usage which you have not seen described on this page, or elsewhere in the AbaPerls documentation, as undocumented, even if it appears in a script that DBUPDGEN has generated