DBBUILD

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

Contents:
    Command-Line Syntax
    Current Directory when Building
    How DBBUILD Builds the Database
    Loading BCP Files
    Output from DBBUILD
    Help Tables

Command-Line Syntax

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

Thus, you must specify either of -database and -noexec, but you cannot specify both.

Current Directory when Building

When you build from SourceSafe and specify -use_disk, your current directory must be one from which DBBUILD can find the files you want to load from disk, rather than from SourceSafe. One approach is to put all files you want load from outside SourceSafe in one directory and run DBBUILD from there. But beware that files that are in subdirectories with regards to the special directories in the AbaPerls SQL directory structure, must also be in subdirectories to the current directory. See the examples for the AbaPerls file-lookup order.

You could also have the files in the working directories that mirror the AbaPerls SQL directory structure in SourceSafe. In this case, while you could run DBBUILD from any directory in the structure, I recommend using the top directory or SCRIPTS. If you want to override files from different subsystems, you can create one directory for each subsystem (with the same name as the subsystem), and each such directory have an AbaPerls SQL directory structure where you place the files you want to override.

When you build from disk without using SourceSafe, your current directory should have on directory for each subsystem, and in each subsystem directory there should be an SQL directory under which there is an AbaPerls SQL directory structure. Be careful so that the path to the current directory does not include any of the directory names from the AbaPerls SQL directory structure, because in such case DBBUILD will assume that you are inside the structure – not above the top of another. If you are toying out an entirely new database, you may have not defined any subsystems, but you still need to use -subsystem to give the name of the directory above the SQL directory. Or call this directory NAMELESS which is the default for -subsystem.

When you build from SourceSafe without specifying -use_disk, the current directory can be whatever you like.

How DBBUILD Builds the Database

The first thing DBBUILD does, is to check whether there are any tables in the database (not counting the system tables of SQL Server). If DBBUILD finds no tables, DBBUILD removes all types, rules and defaults (created with CREATE DEFAULT) from the database, assuming that these are inherited from the model database. (And assuming that no one in his sane mind puts user tables in model.)

DBBUILD then determines which subsystems to build:

• If you specified -config, the config-file determines which subsystems to load and in this order. If you specify -subsystem, DBBUILD will only load this subsystem.
• If you specified -VSS, this is the single subsystem to build.
• If you specified neither, but there is a SUBSYSTEMS.LIS in the current directory, then this files acts a config-file. If you specify -subsystem, DBBUILD loads this subsystem only.
• If you did not specify -config or -VSS and there neither is a SUBSYSTEMS.LIS, 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 have specified a SourceSafe project with -config or -VSS, then the SourceSafe project alone determines the set of files. If you have specified -use_disk, AbaPerls will first look for copies of the files on disk according to the AbaPerls file-lookup order, but extraneous files on disk with no corresponding entry in SourceSafe will be ignored.
• If you are building from disk, DBBUILD will traverse the AbaPerls SQL directory structure for each subsystem, and this alone will determine which files that DBBUILD will load. Note that when loading files for a subsystem, the current directory (which affects the AbaPerls file-lookup order, will be the top directory for the AbaPerls SQL directory structure for each subsystem.)

Having determined the list of files to load, DBBUILD then proceeds to loads the files in this order: (Note that when we say "directory" this could also refer to a project in SourceSafe. And recall that all subdirectories are included.)

1. A START row is written to the table abainstallhistory, or if does not exist in abainstallinfo (created if needed).
2. DBBUILD loads the .SQL files in the MESSAGE directory.
3. DBBUILD loads synonym files in the MESSAGE directory.
4. DBBUILD loads the .ASSEM files in the ASSEMBLIES directory.
5. DBBUILD loads the .XMLSC files in the TYPE directory.
6. DBBUILD loads the .TYP files in the TYPE directory.
7. DBBUILD loads the .TBLTYP files in the TYPE directory.
8. DBBUILD loads the files TBL/*.TBL, including files that is in a sub-directory of TBL.)
9. If there is any VIEW directory, DBBUILD runs the *.VIEW files there.
10. DBBUILD loads the files in FUNCTIONS.
11. DBBUILD loads the files in SP.
12. If you specified -sptwice, DBBUILD loads the files in SP once more.
13. DBBUILD loads triggers on tables in TBL/*.TRI.
14. DBBUILD loads triggers on views in VIEW/*.VTRI.
15. If you specified -bcp, DBBUILD loads BCP files as detailed under loading BCP files, below. (Not for ABAPERLS).
16. If you specified -insert (which is the default unless you specified -bcp) or the subsystem is ABAPERLS, DBBUILD loads TBL/*.INS.
17. DBBUILD loads indexes and statistics on tables in TBL/*.IX.
18. DBBUILD loads indexes and statistics on views in VIEW/*.VIX.
19. DBBUILD loads TBL/*.FKEY
20. DBBUILD loads MESSAGE/*.POSTSQL.
21. DBBUILD saves configuration options on database level.
22. DBBUILD saves configuration options for the subsystem.
23. 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 in the file name sorting, so that INSTRUMENTS.TBL is loaded before INSTRUMENTNAMES.TBL. The idea is that main tables ends in a plural -s, which is good practice.

With the switch -rebuild you can rebuild a database, without affecting the data in the tables. In this case DBBUILD performs only steps 1, 3, 4, 9-14 and 16-23 above. That is, types and tables are not rebuilt, while synonyms, assemblies, views, functions, procedures, triggers, indexes, INSERT-files, and foreign keys are reloaded. Note that even if you specify -bcp with -rebuild, no BCP files will ever be loaded.

When you specify the switch -tablesfirst, DBBUILD traverses the subsystem in two rounds, and in the first round DBBUILD performs steps 1-9 and 21-23 only, and in the second round, DBBUILD performs steps 1, 8-21 only. (For the ABAPERLS subsystem, all steps are performed in the first round.)

Note: the build order makes some assumption how things refer to each other that are not always true. The most typical case is user-defined functions that may refer to tables and views, but tables and views may also refer to user-defined functions. With the build order of DBBUILD the creation of a table of a view fails if there is a reference to a user-defined function. A workaround is to include a $REQUIRE directive in the file that defines the table or view to have the function created before the table/view. (While off-topic for this manual, a word of caution for scalar user-defined functions: they almost always turn a query from being truly set-based to be iterative like a cursor. Thus, while you can use a scalar UDF in a constraint, it may have serious performance impacts.)

Loading BCP Files

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

The files should be named table.BCP, that is the same name as the table, and with the file extension .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 strings @!@ 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.

Note that DBBUILD is not able to detect if the 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 the constraint checks, SQL Server would mark the constraints as "not trusted", and the optimizer would not ignore the constraints when building query plans, which in some cases can lead to less effecient plans.

There is one exception to this: if there are types with the name ending in _upduser, DBBUILD assumes that for each such type there is a rule bound to it that requires the value to be equal to SYSTEM_USER or similar. DBBUILD unbinds these rules prior to bulk-loading, assuming that then name of the rules is the type name with _rule tacked on to it. When DBBUILD has finished bulk-loading the subsystem, it rebinds the rules. If you use some other mechanish, for instance a constraint to enforce the value of an auditing column, I'm afraid that it's not going to work with DBBUILD.

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 the 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 run the script, 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 were set explicitly, by command-line switches, or settings read from the database.

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: There is one category of messages that are only printed to the command-line window: warnings and errors from Perl itself. Such messages should not occur, and are likely to be indications of bugs in AbaPerls (or Perl). A Perl error is usually fatal and terminates DBBUILD.

Help Table

Even if you opt to not install the subsystem ABAPERLS in your database, DBBUILD one help tables: abainstallinfo. Older versions of DBBUILD also created ababuildaid in tempdb, but this has been dropped.

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-2010, Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 10-09-02 13:25