DBBUILD

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

Command-Line Syntax

dbbuild { -database db [-Server server] [-User user] [-Password pwd]
          [-save file] [-tablesfirst]}
          [-rebuild | -restruct] [-bcp [directory]] [-[no]insert]
          [-revokeall]
        | -noexec }
          [-config config-file | -VC VC-path] [-subsystem subsys]
          [-label version] [-get | -use_disk]
          [-force] [-log file]
          [-[no]crypt] [-site site-id:s] [-[no]quoterepl] 
          [-Macro &macro=value [...]] [-undef &macro [...]]
          [-environment DEV | TEST | PROD]

Thus, you must specify one of ‑database and ‑noexec, but you cannot specify both.

How DBBUILD Builds the Database

DBBUILD first determines which subsystems to build:

• If you specified ‑config, the config-file determines which subsystems to load and in this order. If you specified ‑subsystem, DBBUILD loads this subsystem only.
• If you specified ‑VC, or ‑VC was set implicitly from TFS mapping this is the single subsystem to build.
• If you specified neither, but there is a SUBSYSTEMS.LIS in the current directory, then this file acts a config-file. If you specified ‑subsystem, DBBUILD loads this subsystem only.
• If you did not specify ‑config or ‑VC and nor SUBSYSTEMS.LIS is present, DBBUILD will act as if there was a SUBSYSTEMS.LIS specifying a single subsystem by the name NAMELESS. You can override this name with the ‑subsystem option.

For each subsystem, DBBUILD determines which files to load in one of these ways:

• If you have specified a version-control directory with ‑config or ‑VC, then the version-control directory alone determines the set of files. When you are building the subsystem without a label and specify ‑use_disk, AbaPerls first looks for copies of the files on disk according to the AbaPerls file-lookup order, but extraneous files on disk with no corresponding files in version-control are ignored. Files for which you have a pending add (posisble in TFS, not in SourceSafe) are included, if and only if you have specified ‑use_disk. (When you are building a subsystem for a label, AbaPerls ignores checkouts and files on disk.)
• If you build from disk, DBBUILD traverses the AbaPerls SQL directory structure for each subsystem, and this alone determines which files DBBUILD loads. This applies no matter whether SS‑FILES.LIS is present or not.

Having determined the list of files to load, DBBUILD proceeds to load the files in the order below. For all directories listed, the traversal includes subdirectories, save for BCP files.

1. A START row is written to the table abainstallhistory, or if does not exist to abainstallinfo (created if needed).
2. DBBUILD loads .sql files in the MESSAGE directory. (General SQL files for creating users, Service Broker queues etc.)
3. DBBUILD loads .syno files in the MESSAGE directory. (Synonyms.)
4. DBBUILD loads .assem files in the ASSEMBLIES directory. (CLR assemblies.)
5. DBBUILD loads .xmlsc files in the TYPE directory. (XML Schema Collections)
6. DBBUILD loads .mty files in the SERVICEBROKER directory. (Service Broker message types.)
7. DBBUILD loads .sb files in the SERVICEBROKER directory for the first time, with CREATE QUEUE commands altered to leave out activation.
8. DBBUILD loads .typ files in the TYPE directory. (User-defined types)
9. DBBUILD loads .seq iles in the TYPE directory. (Sequences)
10. DBBUILD loads .tbltyp files in the TYPE directory. (Table types)
11. DBBUILD loads .tbl files in the TBL directory. (Table definitions.)
12. DBBUILD loads .view files in the VIEW directory. (View definitions.)
13. DBBUILD loads .sqlfun files in the FUNCTIONS directory. (User-defined functions of all kinds.)
14. DBBUILD loads .sp files in SP directory. (Stored procedures.)
15. DBBUILD loads .tri files the TBL directory. (Triggers on tables.)
16. DBBUILD loads .vtri files in the VIEW. (Triggers on views.)
17. DBBUILD loads .sb files in the SERVICEBROKER directory a second time, now without changing CREATE QUEUE commands.
18. If you specified ‑bcp, DBBUILD loads BCP files as detailed under loading BCP files, below. (Not for ABAPERLS).
19. If you specified ‑insert (which is the default unless you specified ‑bcp) or the subsystem is ABAPERLS, DBBUILD loads .ins files in the TBL directory. (INSERT-files.)
20. DBBUILD loads .ix files in the TBL directory. (Indexes and statistics on tables.)
21. DBBUILD loads .vix files in the VIEW directory. (Index and statistics on views.)
22. DBBUILD loads .fkey files in the TBL directory. (Foreign-key definitions.)
23. DBBULID loads .ddltri files in the MESSAGE directory. (DDL triggers.)
24. DBBUILD loads .postsql files in the MESSAGE directory. (Final general SQL scripts.)
25. DBBUILD saves configuration options on database level.
26. DBBUILD saves configuration options for the subsystem.
27. DBBUILD writes a STOP row to abainstallhistory or abainstallinfo.

Within each group, the load order follows the sort order defined by the Windows regional settings on system level. (That is, if you as a user have Swedish regional settings, but the default setting on the machine is US-English, then DBBUILD will sort according to US-English. It's Perl that works this way, and AbaPerls can't but comply.) There are however a few deviations:

• Site-ids are removed, and sorted on a secondary level, so that the files functions.ins, functions@abn.ins, functions@se.ins are loaded in this order.
• DBBUILD ignores trailing -s when sorting, so instruments.ins is loaded before instrumentnames.tbl. The idea is that main tables ends in a plural -s, which is good practice.
• For stored procedures, see the section Loading Stored Procedures below.

When you specify the switch ‑tablesfirst, DBBUILD traverses the subsystem in two rounds, and in the first round DBBUILD performs steps 1-12 and 25-27 only, and in the second round, DBBUILD performs only the steps 1, 13-27 only. (For the ABAPERLS subsystem, all steps are performed in the first round.)

Occasionally, you may find that you have dependencies that go against the load order. For instance, a function may call another function which comes later alphabetically. Or you may have a view that refers to a user-defined function. In this case, use $REQUIRE to force the referenced object to be loaded first. However, $REQUIRE is not always permitted. If you get the idea to use a scalar UDF in a CHECK constraint in a .tbl file, you will find that AbaPerls does not permit $REQUIRE in .tbl files. For a good reason: having UDF in constraints is rarely a good idea. If you are dead set on it, you can put your constraint in the .fkey file. But it may better to put the check in a trigger. For dependencies between stored procedures, see the next section.

Loading Stored Procedures

Because of the sheer volume, it would not really be practical if you had to use $REQUIRE to control the load order for stored procedures. AbaPerls therefore employs a special strategy for stored procedures. When loading the procedures in a subsystem, AbaPerls first looks through the list of files and buffer all names. (Recall that AbaPerls requires the file name to match the procedure name.) AbaPerls then loads the files in alphabetic order. If AbaPerls finds that a stored procedure (the caller) calls a procedure (the callee) buffered, but not loaded yet, AbaPerls requeues the caller and loads it a second time when all procedures have been loaded. This has two benefits:

• Object Checking can check the parameter profile is checked for all calls.
• You get complete dependency information in sys.sql_dependencies.

Currently, a call to a non-existing stored procedure is never an error with DBBUILD, but a callee could be missing for more than one reason:

• The callee is truly missing. (misspelling, the callee was incorrectly dropped etc.) This is an error with ABASQL and update scripts generated by DBUPDGEN (save update scripts that were generated with AbaPerls L1.0.0281 or earlier) and may eventually become an error with DBBUILD as well. Currently DBBUILD still loads the caller, but when you view the log with LISTERRS, the missing procedure is listed as an error.
• The callee belongs to a subsystem which is later in the build order. As far as DBBUILD is concerned, this is no different from the previous bullet. The same is true for LISTERRS, as long as you don't use the option ‑lax.
• The callee is later in the load order in the same subsystem. DBBUILD issues a warning, and, as discussed above, DBBUILD requeues the caller. Since LISTERRS only lists the last load in the log for a file, you will typically never see this warning if you view the log with LISTERRS.
• The callee failed to load. DBBUILD tracks all load failures, and issues a warning that the caller refers to a callee that failed to load. In difference to when the callee is missing entirely, this warning remains a warning with LISTERRS. This applies also when the callee belongs to a subsystem loaded earlier in the same build.

What is said above, applies also to calls to stored procedures from triggers.

Using the ‑rebuild and ‑restruct options

These two options are the same time both quite similar and quite different. Both reloads all objects in the database and replaces the current set of configuration options. There are several reasons why you may want to do this:

• Restore the database to a known state, because many files have been entered manually. (Only ‑restruct will drop extraneous objects however.)
• You want to change the settings for one or more macros or site-ids, for instance when you copy a test database configured for one customer to be used for testing for another customer.
• You have changed some other configuration option, like ‑crypt and want it to have effect.
• You have changed one or more grant.template files and want the database to align with these changes. In this case, you should also specify ‑revokeall.
• You have installed to a new version of SQL Server, and you have used conditional compilation depending on the SQL Server version. In the ABAPERLS subsystem, this is commonplace, why you should always rebuild the ABAPERLS after an upgrade to a new major release of SQL Server.
• You have made changes to your subsystem structure, for instance consolidated two subsystems into one, or split a subsystem into several or even more dramatic changes. In this case, you must use ‑restruct as ‑rebuild retains the current subsystems.

When you specify ‑rebuild or ‑restruct, DBBUILD takes all configuration options from the config-file and replaces those already present in the database. The options in the database are discarded.

If you specify ‑rebuild together with ‑subsystem to rebuild a single subsystem, DBBUILD retains the database-wide settings in the database and only discards the settings for the specified subsystem. Settings for other subsystems and database-wide settings are left untouched. When you use ‑restruct, you cannot specify ‑subsystem; ‑restruct always operates on the entire database.

To rebuild or restructure a database at a customer site, you should first run with ‑noexec ‑get at home, and then use ‑rebuild or ‑restruct at the customer site, possibly in combination with ‑revokeall.

Specifics for ‑rebuild

When you use ‑rebuild, all subsystems you specify in the config-file or on the command-line with ‑subsystem must already exist in the database; you cannot add a subsystem with ‑rebuild. However, you are not required to rebuild all subsystems, but you can rebuild a subset of the subsystems in the database.

DBBUILD ‑rebuild is intended to refresh a database at its current version; it is not intended for version upgrades. That is, if the current label for subsystem ABC is L8.10.1040, you must rebuild ABC from label L8.10.1040. This is an occasion where it makes sense put explicit labels in your config-file. If there is a label listed for the subsystem in the table abasubsystems, DBBUILD will not accept attempts to rebuild the subsystem from a different label. If there is no label information in abasubsystems, DBBUILD will permit anything, and it is your responsibility to ensure that you rebuild from the correct version.

Why must the versions match? One reason is that DBBUILD ‑rebuild does not remove objects, so if you have version L8.10.1040 and move to L8.10.1500 you may be left with stored procedures that no longer should be there. (Which is bad in a development or a test database, if the production database reaches L8.10.1500 on a different path, and there are calls to these dropped procedures.) Another reason is objects that cannot be changed easily: types and tables. If you have at least version L1.0.0270 of the ABAPERLS system, AbaPerls will attempt to use ALTER TABLE when you load a .tbl file and the definition in the file does not match the database. However, there are many reasons why this will not work out, so it is precarious to count on it. The main reason for the check is to certify that the database agrees with version control.

Normally, DBBUILD ‑rebuild reloads all objects, but there is one exception: if the database is labelled as a production database, and the object in the database has a higher version number than the version number in version control, DBBUILD skips this file, since the later version could be a hotfix or similar. This does not apply to test or development databases, since for these databases you may want a database that aligns exactly with version control (and what you plan to ship to production).

Specifics for ‑restruct

‑restruct is intended for all situations when you want to change the subsystem structure in the database. It could be that you have moved a large number of objects from one subsystem to another, or that you want delete one or more subsystems from the database (for instance because you take copy a test database targeted for one customer to be used for a different customer). A specific scenario which fits in here is when you move your source code from SourceSafe to TFS.

To use DBBUILD ‑restruct, version L1.0.0270 or later of the ABAPERLS subsystem must be present in the database.

‑restruct always operates on the entire database except for the ABAPERLS subsystem. You can't use the ‑VC or ‑subsystem options with ‑restruct, but you must use the ‑config option when you build from version-control. When you create a structure for installation at a remote site with ‑noexec ‑get, you must include all subsystems you intend to have in the new database. DBBUILD ‑restruct never touches the ABAPERLS subsystem. If the ABAPERLS subsystem is present in the config-file, DBBUILD silently ignores it.

DBBUILD ‑restruct adds a couple of preparatory and finalising steps to the build order above. These are the preparatory steps. (To keep it simple I say All although this really means All except the ABAPERLS subsystem.)

1. If you specified ‑revokeall, DBBUILD removes all permissions as detailed above.
2. All triggers, synonyms and Service Broker objects that are listed in abasysobjects are dropped from the database, and the deletion is logged in the same way as the file had been deleted in an update script.
3. All signatures, certificates and users created from the $DBPERM and $SERVERPERM macros are removed and dropped. Note that certificates and logins in the master database are only dropped if you are logged as a member of sysadmin. See the topic Modules with Special Permissions for more details.
4. DBBUILD writes a row to abahistsysobjects for all objects that remains in abasysobjects after the first step. In the scriptname column the value is DBBUILD ‑restruct (pre).
5. DBBUILD writes a REMOVE row to abainstallhistory for all subsystems.
6. DBBUILD deletes all rows in the tables abasysobjects, abaspecialperms, abasubsystems, abasslabels, and abaconfigsettings.

After these steps, the AbaPerls system tables looks just like they would after an initial DBBUILD has completed building the ABAPERLS subsystem, except that the history of the old structure is reflected in abainstallhistory and abahistsysobjects.

Note that if a .tbl fails to load during the rebuild, all child objects for the table – triggers, indexes, foreign keys and insert-files – will also fail to load. The table is still there in the database, but since the table is not present in abasysobjects, AbaPerls cannot add the children to abasysobjects, because of a foreign-key constraint. Stored procedures, functions and views that refer to the table will still load, as long as they are not dependent on the changes in the .tbl file. Keep in mind that as long as you have version 12 of the table in the database and DBBUILD ‑restruct loads version 12 of the file, the load should never fail. If you get an error in this case, this indicates that someone has tampered with table outside of AbaPerls.

When DBBUILD ‑restruct has completed building all subsystems, it retrieves all objects that exist in the database and do not appear in abasysobjects but exists in abahistsysobjects with the most recent row reading isdeletion = 1. That is, these are the objects removed from abasysobjects in steps 4 and 6 above but that were not reloaded by DBBUILD ‑restruct. DBBUILD drops these objects one by one (so that any errors can easily be identified with LISTERRS), and an extra deletion row is written to abahistsysobjects. On this row, the scriptname column will read DBBUILD ‑restruct (post) and the filename column will hold the DROP command. A couple of things to observe here:

• The drop operation also includes objects that for some reason were still around in the database, despite being deleted according to abahistsysobjects prior to the build
• Objects that were loaded entirely outside of AbaPerls and never listed in abahistsysobjects are retained in the database.
• If an object failed to load during the build, the object will not be written back to abasysobjects, and will thus be dropped in the final step with one exception: AbaPerls will not drop a table, nor any child objects of that table, if the .tbl file failed to load. Note however, that all triggers will be missing, since they were dropped in the preparatory steps.
• If a storage setting has been removed from the source file, DBBUILD ‑restruct will consider the setting in the database as set locally, since the table abaobjectstoragesettings is emptied together with abasysobjects.
• If an index definition includes a WITH, ON or FILESTREAM_ON clause, the index will always be rebuilt, as DBBUILD ‑rebuild, no matter which version the ABAPERLS subsystem is at.
• On the other hand, if a table was never included in the build, DBBUILD ‑restruct will drop the table and data will be lost.
• DBBUILD cannot undo the actions of a dropped .sql, .postsql or .ins file, but you will have to cater for this yourself.
• And if you wonder why some objects are dropped in the preparatory steps and others in the final step, this is because when AbaPerls loads trigger, synonym and Service Broker files, AbaPerls drops existing objects loaded from the file as stored in abasysobjects. So DBBUILD must drop these objects while the information is still there in abasysobjects.

DBBUILD ‑restruct performs no checks on labels, so in theory you could use DBBUILD ‑restruct to take your database from L4.20.0010 to L10.30.0450 – or in the reverse direction. However, the chances that you will able to do this successfully are slim. The sore points are types and tables. If a file fails to load during the build, it will not be recorded in abasysobjects, which means that AbaPerls will not have correct information about the objects. For an occasional stored procedure this may be OK, but things quickly get out of hand if you have a couple of types or tables that fail to load.

For this reason, you should plan your ‑restruct operation carefully and make sure that you run the build from labels that are in sync with the labels in the current structure. Particularly, you should make sure that there are no differences in type or table definitions. You should also be careful to take a backup of the database. When you run the restruct on a production database, you should first test the operation on a copy of the database so you know that it works out.

Even if you have all labels in sync, you may still run into errors with .sql, .postsql and .ins files if they are not written so that they can be run more than once. For instance, an .ins file may perform an INSERT without any preceding IF EXISTS. This applies to DBBUILD ‑rebuild as well, but when you use ‑rebuild you can discard the error; since the entry in abasysobjects is retained, there is no real harm.

Loading BCP Files

If you specify a directory as argument to ‑bcp, DBBUILD reads BCP files from the that directory. If you specify ‑bcp without an argument, DBBUILD reads all BCP files from the current directory. In neither case subdirectories are considered. That is, all BCP files should be in one place. This is advantageous when you want to rebuild and reload a database from scripts entirely, for instance because you want to change the collation or you need to migrate from a corrupted database. You can bulk out all data into one place with DOBCP and do not need to sort the files per subsystem.

Note that AbaPerls system tables are never loaded through BCP, but they will be repopulated with the objects and subsystems that are included in the build.

The files should be named table.bcp. DBBUILD attempts to load non-empty files named *.bcp that fits with the tables in the current subsystem. Empty BCP-files are merely noted in the build log.

DBBUILD determines the format of the BCP files in this way:

1. If there is a tbl.fmt, DBUILD will use this file as a format file. (Typically this file would have been generated by DOBCP).
2. If DBBUILD can find the string @!@ on the first line or the string <-> on the second line, DBBUILD will assume these command-line switches for BCP: ‑c ‑t@!@ ‑r"\n<->\n". (This is the character format used by DOBCP.)
3. If neither applies, DBBUILD assumes native format, that is, the ‑n option to BCP.

Note that DBBUILD is not able to detect whether a BCP file was generated with the ‑unicode option with DOBCP, but you must always use format files in this case.

Overall, the use of format files is recommended. For instance, if you move data from one collation to another and use native format, you need to use format files to describe the BCP files correctly. (Note that sql_variant columns will retain their collation, since for them the collation is part of the data.)

DBBUILD runs BCP with a batch size of 1000, and uses the ‑E switch if required to maintain IDENTITY values.

DBBUILD runs BCP with the option ‑h "CHECK_CONSTRAINTS", meaning that all constraints for the table are checked when the data is loaded. Typically foreign keys will not be present when BCP runs, as DBBUILD loads them later. On the other hand, CHECK constraints are present if they appear in the .tbl file (which is the normal thing). If DBBUILD would load the files without constraint checks, SQL Server would mark the constraints as "not trusted", and the optimiser would not ignore the constraints when building query plans, which in some cases can lead to less efficient plans.

Note: while AbaPerls generally supports the full use of Unicode in file names, this does not apply to BCP files, since AbaPerls invokes Windows Command Line which is not Unicode-aware.

Output from DBBUILD

DBBUILD 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. There is no need to monitor this output.

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

First in the log file you find a header which reports the user that ran DBBUILD, when he did it and with which command-line options. For each subsystem, there is a header with the configuration options that DBBUILD used for that subsystem. DBBUILD reports only the configuration options that somehow were set explicitly. That is, they were specified in the config-file or on the command-line or read from the database. DBBUILD does not report options set to their default values.

For a database with a reasonable number of tables, the log becomes huge and tedious to read directly. To this end there is the command LISTERRS that extracts the interesting messages from the log as well as the header information.

Note: Messages from Perl itself will be printed to the command-line window only. Ideally such messages should not occur, and if they do, it could be construed to be a bug in AbaPerls (or Perl itself). Some warnings like wide character in output are fairly innocent and can be ignored..

Current Directory when Building

You can build a database from files on disk, from version-control or a combination both. Essentially there are four scenarios:

1. From version-control only.
2. From version-control, but letting local disk files override with ‑use_disk.
3. From disk only.
4. From disk from a structure previously created with ‑noexec ‑get.

When you build from version-control only, your current directory does not matter. (Save for BCP files if you did not specify an argument to ‑bcp.)

When you want to override version-control with local disk files (because you are making some tests), you need to ensure that DBBUILD will find the files with through the AbaPerls file-lookup order. The simplest way is to check out these files, and DBBUILD will find the disk files through the version-control system. You can also gather the files in the same AbaPerls SQL directory structure, and run DBBUILD from the Scripts directory of that structure. Note that when you build from version-control, DBBUILD retrieves the list of files from the version-control system, and thus DBBUILD will not pick up new files that have not yet been checked in (including files that have an Add pending in TFS.)

When you build from disk with no version-control whatsoever – maybe because you are toying out a new database, or you want to test with files you have not checked in – you can only build one subsystem at a time. As in the previous scenario, your current directory can either be in the AbaPerls SQL directory structure for the subsystem, or in a directory which has one directory per subsystem. In the latter case, you don't have to change directory if you build several subsystems.

In the last scenario, you always run DBBUILD from the directory where you have SUBSYSTEMS.LIS and SS‑FILES.LIS, and you have one directory per subsystem.

For more information and examples, see AbaPerls file-lookup order and Installation at a Customer Site.

abainstallinfo

Even if you opt to not install the subsystem ABAPERLS in your database, DBBUILD creates one help table: abainstallinfo.

Abainstallinfo gives an installation history for the database, akin to the one in abainstallhistory, and is also maintained by the update scripts generated by DBUPDGEN. If you later opt to add the ABAPERLS subsystem to your database, abainstallinfo will be converted to abainstallhistory.

Copyright © 1996-2017, Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 21-03-15 16:22