The Life-Cycle of a Database

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.

Contents:
    Creating the Database
    Adding Subsystems
    Changing Configuration Options
    Loading Single Objects
    Updating Your Database
    Removing Subsystems
    Upgrading to a New Version of SQL Server
    Auditing Your Database

Creating the Database

The very first step is to create the database, which you do with the SQL command CREATE DATABASE, or from the GUI in Enterprise Manager. There is no command in AbaPerls for this.

Adding Subsystems

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.)

Typically you use a config-file for building the database, and in this config-file you specify the subsystems, and configuration options for the database, either global or on subsystem level. If you later need to add another subsystem, you add the new subsystem 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 -VSS and -subsystem switches and add the subsystems one by one. When you do this, you can still save the configuration options in the database, by specifying them on the command line. Keep in mind, that the precendence rules for configuration options says 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 only 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.)

Changing Configuration Options

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 -rebuild 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 config-file. An example on when you want to do this, is if you decide to save stored procedures etc encrypted, by enabling the -crypt option. You can also use -VSS and -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.

Without affect existing objects: You can update the table abaconfigsettings directly. You can use the SP ap_cos_insert_sp to add a setting not previously defined. Specify NULL for @subsystem to add a global setting. One case when this is useful is when you plan to start to use a new macro in some of the files, and this macro should be set for this database. Since this macro has not been in use before, there is no need to run DBBUILD -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 -rebuild.)

Loading Single Objects

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 encrypted. ABASQL permits you to specify which subsystem the object belongs to, and will then use settings from 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.

Updating Your Database

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 tracks of what you have updated in the database and you have not. In the table abasubsystems AbaPerls keeps track of the SourceSafe label for the subsystem.

DBUPDGEN works by comparing two SourceSafe versions 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 senses which subsystems and skip the parts of that pertains to the subsystem not present in the database – 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. While you could do this with ABASQL, a better way is to use DBUPDGEN with the -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 encrypt stored procedures, or have a certain macro set. But you can never change the configuration options with an update script, and neither add any. To that end you use DBBUILD.

Removing a Subsystem

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.

Upgrading to a New Version of SQL Server

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 makes genererous 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.

Auditing Your Database

The AbaPerls system tables provides 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 AbaPerls, for instance through SQL Server Management Studio. However, you can find out whether such changes have occurred.

Audit on object level

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 procedures 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 SourceSafe label.

Audit on subsystem level

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.

General disclaimer

The auditing features of AbaPerls are intended to catch cases where developers or administrators bypasses 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 will be able to cover his tracks.