This page gives an overview on how DBBUILD, ABASQL and the update scripts generated by DBUPDGEN operate on a database during its life. Please see pages for the concerned commands and concepts, mentioned tables and stored procedures for full details.
Defining the System and Creating the Database
Changing Configuration Options
Loading Single Objects
Updating Your Database
Removing a Subsystem
Upgrading to a New Version of SQL Server
Auditing Your Database
Audit on object level
Audit on subsystem level
When you plan to start a new database application, the first step is to set up the system-definition file where you list the subsystem you plan to have. There is no requirement to have a sysdef-file, but it can make things easier for developers, particularly if you are using TFS, since this permits AbaPerls to automatically determine the subsystem from the current Windows directory.
If you plan to have several databases in your application, you should add the subsystems for all database to the same sysdef-file.
As your system evolves, you can add more subsystems to the sysdef-file. Be careful to add them in build-order.
There is no support for creating the database as such in AbaPerls, but you use the regular CREATE DATABASE command or use the GUI in SQL Server Management Studio.
Once you have created a database, the next step is to add one or more subsystems with DBBUILD. To fully use the features of AbaPerls, the first subsystem you add should be ABAPERLS, which contains the AbaPerls system tables and associated stored procedures. You can still use the AbaPerls tools, even if the ABAPERLS subsystem is not present in the database, but some features will not be available. The life-cycle detailed here presumes that the AbaPerls system tables are present from the very start. (See however the very end of this page about abainstallinfo, a table that AbaPerls creates when you do not install ABAPERLS.)
To bulid the a database you typically use a config-file where you specify the subsystems, and configuration options for the database, either global or on subsystem level. If you use a sysdef-file, the subsystems are drawn from this file. If you later need to add another subsystem, you add it to the config-file and run DBBUILD anew. DBBUILD will ignore the subsystems in the config-file that already are in the database, and build only the new subsystem.
An alternative method is to use DBBUILD with the
add the subsystems one by one. When you do this, you can still save the
options in the database, by specifying them on the command line. Keep in
mind, that the precedence rules for configuration options
say that these options will always apply to the database as a whole, and
never per individual subsystem.
How would you divide your database into subsystems, you may ask? In AbaSec, we introduced subsystems because we have components that are only relevant to some customers, and our database had grown too large in number of SQL objects to make it convenient to always ship everything. This should give you a clue. If your database only has some fifty-some tables, or you always install all tables and procedures (for instance, because your database is specific to solve the needs of your enterprise), then you probably only need to have two subsystems: ABAPERLS and your own database. And in this case, maintaining a config-file is probably not worth the effort.
(You may wonder how subsystems are difference from schemas in SQL Server, and the answer is that they are to a great extent the same idea. Subsystems were added in AbaPerls long before schemas became useful in SQL Server. Alas, AbaPerls does not really understand schemas in SQL Server, but assumes that you always everything in dbo.)
If you want to change the settings for a database, either on global level or for a specific subsystem, there are basically two ways to go, depending on whether you want existing objects to be affected or not:
To change existing
objects: Use DBBUILD with the
switch. This will reload all objects,
excepting types and
tables, in the subsystems specified in the config-file. The settings saved
in the database will be scrapped in favour of those in the
can also use
‑subsystem in place of a
config-file, which will
rebuild that particular subsystem and replace its settings, but leave the
settings on global level unchanged.
‑rebuild. (On the other hand, if you later decide to change the setting of the macro for a database, then you should probably use DBBUILD
When you want to load a single object you use ABASQL.
ABASQL reads the
settings from the database, so if you used
‑crypt when you created the database,
the objects that you change or add with ABASQL will be automatically obfuscated.
ABASQL permits you to specify which subsystem the object belongs to, and will
then use settings for that subsystem.
Note however, that ABASQL is mainly a tool for developers to be used in development databases. That is, in a perfectly maintained production database, you would only load SQL objects with DBBUILD and the update scripts generated by DBUPDGEN. In real life, every now and then, the need for an immediate fix arises, and you may even need to test various variants of the fix on the production data (because that's the only place where you can reproduce the problem prompting the fix). Thus, here you would need to use ABASQL.
The regular way to update the database with new/changed tables and stored procedures is to build an update script with DBUPDGEN. The big advantage of this is you can keep track of what you have updated in the database and you have not. In the table abasubsystems AbaPerls keeps track of the version-control label for the subsystem.
DBUPDGEN works by comparing two versions in the version-control system against each other, and it includes all changed files in the update script, and, in the case of tables, files that needs to be reloaded when the table is rebuilt. When you run the script, the script updates the database with the current version of the changed scripts, and the script also updates the labels for the subsystem in abasubsystems.
An update script updates the subsystem from one version to another. To make your updates safer, the update script verifies that it fits with the subsystem. Say that you have a database where a certain subsystem is at version L5.30.1200, and you have an update script that updates the subsystem from L5.30.1300 to L5.30.1500. This update script will refuse to run in this database, because you first need to run a script that updates the subsystem to L5.30.1300. On the other hand, if the update script would find that the subsystem is already on L5.30.1500, the update script would tell you that there is no need to run the script again.
When you generate an update script, you can start from two versions of a config-file and get a script that comprises many subsystems. When you run the script, the script examines which subsystems that are present in the database and skips the parts of that pertains to the absent subsystems as well as subsystems that are already up to date.
Occasionally you may want to ship a small handful of files without changing
the labels of the subsystem. You can do this with ABASQL, but you can also use DBUPDGEN
‑patch option. There are several advantages with this: 1) You get a
single Perl script that has all the changes. 2) The script verifies its start
label fits with the database. 3) The execution of the scripts is logged to
abainstallhistory, more about
this table in the section on auditing below.
Just like ABASQL, the update script reads the configuration options from the database, so when you run a script on a database, you never have to remember whether this was a database you should obfuscate stored procedures, or have a certain macro set. But you can never change the configuration options with an update script and nor can you add any. To that end you use DBBUILD.
The way to remove a subsystem is to generate an update script with DBUPDGEN from a config-file. The subsystem to be removed should be present in the earlier version of the config-file, but not in the later. DBUPDGEN will then include all objects of this subsystem in the update script, but for deletion only. The update script will not only drop all objects, but also deactivate the subsystem in abasubsystems.
When you upgrade you database to a new major version of SQL Server, you
should always rebuild the ABAPERLS subsystem with DBBUILD
‑rebuild. This is
because the stored procedures in AbaPerls make generous use of conditional
compilation depending on SQL Server version. Thus, you need to rebuild ABAPERLS
to get full support for new features in SQL Server.
The AbaPerls system tables provide information that permits you to see what subsystems and objects that are installed in the database through AbaPerls. Obviously, AbaPerls cannot track objects that are loaded to the database outside of AbaPerls, for instance through SQL Server Management Studio. However, you can find out whether such changes have occurred.
The table abasysobjects holds all objects that currently are loaded in the database according to AbaPerls. The table abahistsysobjects holds the complete installation history for each object. The stored procedure ap_sob_report_suspects_sp reports all deviations from the normal installation procedures. This procedure list objects in the database not in abasysobjects and vice versa. It also lists objects for which the entry in abasysobjects does not reflect the most recent CREATE/ALTER of the object in the database; that is, objects that have been loaded outside AbaPerls. You can also see objects that have been loaded directly from disc without a proper version-control label.
The table abainstallhistory includes all executions of DBBUILD and update scripts generated by DBUPDGEN (both regular update scripts and patch scripts). For all normal executions there is both a START and a STOP row. The entries are per subsystem, so if you run DBBUILD in a new database with a config-file that includes five subsystems you get ten rows in abainstallhistory.
This functionality is available to you, even if you do not install the ABAPERLS subsystem. In this case AbaPerls silently creates the table abainstallinfo to which DBBUILD and the update scripts generated by DBUPDGEN write START and STOP rows. If you later install the ABAPERLS subsystem, AbaPerls automatically converts abainstallinfo to abainstallhistory.
The auditing features of AbaPerls are intended to catch cases where developers or administrators bypass the regular procedures because of ignorance, laziness or uttermost emergency. A malicious user who has the rights to create objects will of course have the rights to manipulate the AbaPerls system tables and be able to cover his tracks.
Copyright © 1996-2012,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 12-06-13 11:14