How to Use This Manual

This manual describes how to use DP4's core utilities to create and maintain DP4 databases. Previous experience of DP4 is not assumed, but a basic knowledge of fields, tables, roles, key fields, indexes and other relational database concepts would be a distinct advantage. The manual is divided into the following sections:

  • Getting Started

    helps new users to become acquainted with DP4. It describes how to log onto DP4, how to use the main system menus, how to use the user interface, how to run DP4 utilities, programs and reports against the SALESORD database (used for examples throughout the manual) and how to test your DP4 printer selection.

  • Database Design

    provides information about DP4's implementation of the relational database model. You should read this before building a DP4 database. It spans a variety of concepts from entity modelling to database design tools.

  • Building a Database

    tells you how to use the DP4 utilities to implement your database design. It covers these tasks: creating and modifying a database; entering, deleting and viewing data; printing the database structure; setting database security levels.

  • Maintaining a Database

    provides information that will enable you to develop a maintenance schedule that will ensure that your database continues to perform at its best. This section covers: setting up transaction and roll-back logging; backing up and restoring a database; recovering a database; checking the status and integrity of a database; counting the records in a database; reorganising a database; shutting down a database; running maintenance records from batch files.

  • Transferring Database Information

    describes the DP4 utilities for transferring information into and out of DP4 databases. You can import data from many different sources, including spreadsheets, word-processed reports, other non-DP4 files and, of course, DP4 databases. This section covers: copying the database structure; copying data between databases; exporting data with Report Writer; importing data using an Importer program; transferring data with the load and unload facility.

  • Editing Text and Maps

    shows you how to use the DP4 Text Editor to edit DP4 print files, how to use the DP4 Map Editor to create screen and print formats and how to use the DP4 Help Editor to create windows of help information for input fields in applications.

  • Managing Users

    explains how to manage the users of a DP4 system, including maintaining the user list, editing a menu, using menu commands, managing menus

  • Creating an Automatic Demonstration or Test with Redirect

    describes how to use the REDn program to create an automatic demonostration or test of your DP4 programs.

  • Supporting National Languages

    provides information for setting up databases that support multiple national languages at the same time. It covers these tasks: developing a multi-language application; translating maps into different languages; selecting the language preferences; specifying the collate characteristics of key fields; changing the database field headings and country-dependent fields for a new language.

  • Architecture of a DP4 System

    explains the underlying file structure of a DP4 runtime system and information how a DP4 application updates the database using the post, commit and checkpoint mechanism and how you can change the database without recompiling your applications. The latter explains the rules of data independence.

  • Database Limits

    gives you maximum limits information for the database

If you would like to point out errors or omissions in this manual please refer to How to Comment on This Help File.


How to Comment on this Help File

Our aim is to provide you with top quality documentation. In order to do that, we need your help. Feedback from you is the best way we have of measuring the quality of our documentation, so comments, criticisms and suggestions on our manuals are greatly appreciated.

If you are familiar with HTML you can use the view source option to get an editable copy of each page. You are welcome to e-mail us with edited versions of pages of this or any other DP4 manual. You can contact us at CustomerCare@itim-ts.com.


1 Compress data file and rebuild index

Compresses the database data file so that all free space is contiguous, and completely rebuilds the index file

Advantages.

  • May improve database performance somewhat.
  • Helps to reduce the rate of growth of the database data file.
  • May take a little less time than a full reorganisation (option 3).
  • Requires only sufficient disk space to write another copy of the database index file

Disadvantages.

  • Does not reorganise the database as well as option 3.
  • Less safe than option 3; in the event of a system crash before the 'Indexing other tables' phase, your only option is to restore the database from a backup.
  • Space reclaimed from deleted records is not released back to the operating system.

User Interrupt

Not available

Crash Recovery

Only in certain circumstances (see Reorganising with Compression )

When to use

Only recommended when it is not possible to run option 3 - Rebuild data and index files


2 Remove holes from data file

This option has been removed in beta releases of 4.526/4.622, but it is hoped to replace it with an improved "Defragment Database" option.

Combines holes in the data file, placing them near to the end of the file. (To count the holes, use the Record Count option of the Database Integrity Checker)

Advantages.

  • Tends to improve update times for the database and helps to reduce the growth rate of the database data file.

  • The fewer holes there are, the less likely it is that new data is added non-sequentially

Disadvantage

  • May adversely affect look up times because some records may be placed far from related records

User Interrupt

Use <Esc> or <Ctrl+S> and <Ctrl+C> or Close on the system menu or <Ctrl+Alt+Del>

Crash Recovery

Yes (see Removing Database Holes )

When to use

When a full reorganisation is not required, but there are many holes in the data file. If there are over 6000 holes in the data file, reorganise the data file with option 3 - Rebuild data and index files


3 Rebuild data and index files

Completely rebuilds the database data and index file, bringing all data up to the latest generation

Advantages

  • May significantly improve database performance
  • Reduces the size of the database data file, possibly dramatically.
  • Provides greater security than option 1 in the event of a system crash
  • The free space is released to the operating system
  • Results in a better organised database than option 1

Disadvantages

  • Whilst in progress, this option requires enough disk space for an additional copy of the data file
  • Requires a little more time than option 1. On large databases this is very unlikely to be a significant proportion of the total time required.

User Interrupt

Not available

Crash Recovery

Yes (see Reorganising with Rebuild )

When to use

Whenever prompted by the system to do a database reorganisation


4 Optimise index file

Rearranges the index file so that its nodes are in the most efficient order for searches. This option requires sufficient space to write a temporary, additional copy of the index file.

Advantages

  • May improve the performance of the database.

  • Runs much quicker than full reorganisation.

  • As of release 4.622, may also significantly decrease the size of the index file

Disadvantages

  • None

User Interrupt

You can interrupt this option, but delete the temporary index file .$$I that is left on your system.

Crash Recovery

Yes (see Optimising the Index File )

When to use.

  • If you rarely perform a full reorganisation, run this option periodically.
  • If after running the database integrity checker, you know that the index nodes are greatly fragmented, or (if you are using release 4.622) that the index utilisation is low.


5 Reorganise an index

This option rebuilds specified table indexes

User Interrupt

Not available

Crash Recovery

Yes (See Reorganising an Index )

When to use

If you make a change such as changing an index link to 'update' with the index link editor


6 Advanced Database Properties

This option, introduced in release 4.620, opens a dialog that allows you to change certain properties of the database files. Changes only become effective when you use options 1 or 3 to rebuild the database afterwards.

The Advanced Database properties dialog is explained in detail in Advanced Database Properties.


1000's separator

Enter the thousands separator, normally "," in the UK and USA and "." in continental Europe


Access Control

See Security Levels.


Action-bar Menu

An action bar menu contains a set of options as a horizontal line across the top of the screen. Each option can have a pull-down menu attached to it.


Add all <F3>

Adds all the tables in the database to your plan in a single column


Add one <F2>

Adds tables to the plan one by one at the current cursor position


Adding a Database Table

  1. On the Database Creation/Changes menu, select option 3 - Table editing.

  2. Enter up to 16 characters for the table name and confirm that you want to add the table to the database.

  3. Define the Table details, including the table heading and security access levels:

    Click on questions on the Table details entry screen for an explanation. The screen shot above is for releases up to 4.621. There are some changes in release 4.622. Please see the remarks below on Data Compression

  4. When you complete the Table Details, the Database Creation/Changes menu is redisplayed.

Data Compression

In the past data compression was not enabled by default. Although enabling data compression can, or perhaps could, cause performance problems in some circumstances, far more commonly we have seen situations where failure to turn compression on for high volume tables has caused significant problems to end users due to databases growing unexpectedly quickly. As of release 4.622 there is a new "Auto Compression" property, which is enabled for all tables by default. For all tables for which this flag remains set REORGDB reserves the right to turn data compression on or off, as it thinks best based on the data currently in the database. For databases where the "Enhanced Data Compression" facility is enabled, REORGDB may,and usually will, choose to use a different, and far more sophisticated compression mechanism, which typically improves on traditional DP4 compression by a factor of 3 or more.

If you don't want REORGDB to manage compression automatically for a particular table, then disable "Auto Compression". Such tables cannot be compressed using the new compression mechanism. This should not be a problem, since the only likely reason for disabling automatic compression management for a table is that you do not want it to be compressed.

Secret Options

The following additional options are displayed on the Table details screen, if you run MAKEDB with the -EXPERT or -BASEDICT command tail.

Do not create tables or fields with -BASEDICT unless you want them to be treated as part of the base dictionary. Such tables are not usually visible to applications and are not data independent.

Local table only

Enable this option if you are going to set up a distributed/networked database and want this table to be read and updated locally rather than on remote databases.

Non-unique keys

The default is 'No'. Enable this option if you want to be able to add several records with the same primary key. This may give improved update performance for tables such as order lines, but you will lose some of DP4's indexing capabilities and cannot use standard DP4 utilities such as BROWSER and PROGMAKE with these tables. This option is no longer supported (and has been completely removed as of release 4.622).

Pack fields

The default is 'No'. Answer 'Yes' to store data records without padding bytes

Only answer 'Yes' if you are confident of the effect of doing so; it may make it impossible for the data to be accessed with programs written in languages that enforce alignment restrictions. For database where enhanced data compression is enabled, enabling this option with the aim of saving disk space will have little or no effect, because padding bytes are normally compressible to a minute fraction of one bit.

Enable PHYS_INDEX

The default is 'No'. Enable this option to allow records to be fetched using the PHYS_INDEX index. While this increases the size of your database file and makes updates slightly slower, the PHYS_INDEX may provides faster access than the prime index and speed up database enquiries. This option has been removed in release 4.622

Additional length

This option is only applicable with the -BASEDICT command tail. It allows you to allocate up to 4000 bytes of data outside the normal DP4 fields. This data cannot be handled by standard DP4 utilities such as QA Build or BROWSER. It can be used to hold user-defined C programs or large character arrays defining bitmaps and so on. The maximum size of a table record is 4096 bytes, which means that the actual amount of additional data you can allocate is not 4000 bytes, but: 4096-<total_DP4_field_size>


Adding a Language

  1. Language information is edited using the DP4 MENUEDIT utility. To access this via the DP4 menu, select Configuration Utilities and then the User and Menu Management option, or for releases up to 4.620, select Other Facilities and then the Menu Facilities option.

  2. Enter the name of any database on your system (the language table is automatically stored on the system database)

  3. Select language 0. The Menu Editing Main menu is displayed

  4. Select option 6 - Language table. The Language Maintenance dialog is displayed:

  5. Enter the new language number. If you are not sure what language numbers are in use, press <Ctrl+P> to view the existing language numbers, then press <Esc> to enter a new language number

  6. Answer 'Yes' to the prompt to add a new language and enter up to 16 characters for the 'Language name'. The remaining fields define the language preferences. Enter these as described in Selecting the Language Preferences

  7. Click the OK button to save the language definition and press <Esc> to return to the Menu Editing main menu

  8. Select option 8 - Exit, then press <Esc> several times to return to the DP4 System menu


Adding a New Record

  1. Select the Keys option

  2. Enter a key for the new record and press <Enter>. Press <Ctrl+P> to display the upper and lower limits for the field. Press <Insert> to insert the database default value. DP4 supplies the next unused number for a numeric field, and the default values for character, date and time fields

  3. If the key you have entered is used by another field, DP4 displays the record with the status: "Existing record"

    If the key is not in use, DP4 displays the status: "No matching record"

  4. Select the Add option. DP4 displays the details of the last record as default values for the record fields

  5. Enter the required values for the fields in the record. These will be validated against the field limits set when the table was created.

  6. When you complete the record entry and try to save the changes, BROWSER will perform any referential integrity checks specified for the table (for example parent checks). If you cannot add the record because of a missing parent you can switch the table you are working on to the problem parent and add the missing parent - when you return to this table your work will still be there). Alternatively you can run BROWSER from a command prompt and specify -ALLOW. This allows you to bypass both range and referential integrity checks. If you find yourself using -ALLOW a lot, then it indicates you should probably make changes to your database design (for example switch off a parent check that needs to be bypassed) using


Adding a New User

  1. Follow the instructions to modify the user list and display the User Set Up dialog box

  2. On the User Set Up dialog box, enter the name of the new user:

  3. Answer 'Yes' to the prompt to confirm the addition of a new user

  4. Enter the user details. The options are described in Setting Up a User

  5. Complete the user details entry. On the Save/Abandon changes menu, select option 1 - Save changes. The user name is added to the user list on the System database

  6. Press <Esc> several times to return to the DP4 System menu


Adding a Table Field

  1. On the Database Creation/Changes menu, select option 3 - Table editing

  2. Enter the table name or press <Ctrl+P> and select a table name from the list of existing tables

  3. On the Database Table menu, select option 2 - Edit fields

    DP4 displays the list of fields in the table. As no fields are set up yet, the option to Add new field is highlighted. Press <Enter> to add a new field. The Field details entry screen is displayed.

    These are the details for the key field of the CUSTOMER table in the example SALESORD database:

    Click on questions on the Table Field details entry screen for an explanation.

  4. When you have finished entering the field details, the list of fields is redisplayed. To add another field, press the <↓> key and highlight the option to Add new field.

    When you have entered all the fields in the table, press the <Esc> key, then <X>, to return to the database table selection.

  5. When you have finished defining tables, press the <Esc> key. The Database Creation/Changes menu is redisplayed.


Adding Numerical Units

You can display numbers with unit symbols, such as "$", "£", "I" and "Kg", that are defined in the Database's Units table. To update the Units table:

  1. On the Database Creation/changes menu, select option 6 - Units Table. A screen similar to the following appears:

  2. Select the New units record or an existing record and specify the required properties. For further information on these click on the appropriate area of the illustration above.


Advanced Database Properties

The properties you can set are as follows:

  • Enable Preallocation

    Normally file space for DP4 databases is allocated only as it is required. On some operating systems, especially if you run REORGDB only rarely, this can cause your database to be fragmented and to perform poorly.

    If you enable preallocation your database will be grown by progressively larger amounts each time more space is required. This may help the database to remain unfragmented.

  • Database Structure

    This option, introduced in release 4.622, enables you to select between various database architectures that are now supported. In case of doubt, then unless you require compatibility with an earlier release, select "Two File Index". The help messages for the various options should help you decide what is best for you. You can also refer to "Configuring the Index Structure" on the DP4UG web site. Depending on the value you choose here, several, or even all, the remaining fields in the dialog may be disabled.

  • Index pointer size

    This option, introduced in release 4.622, and only enabled with the new database structures it introduces, controls the maximum possible size of your database IND file. The actual maximum index size depends both on this value, and the node size. The help message has a table showing how these values relate. The default value, of 3 bytes, is suffient for an index file of at least 8 Gigabytes, and up to 64 Gigabytes with the largest node size. In fact a value of 2 bytes is sufficient for index file up to 256MB if you use the largest node size.

    Although picking a small value for this value will save some space in the index this is not likely to make a very noticeable difference, because probably around 90% of space in the index file is occupied by the keys of the records in the database. Also REORGDB cannot currently correct a value that is too small, as it can with the corresponding parameters for the data file. So err on the side of caution when selecting this value. You should allow a margin of at least 50% if you select a small value, because the index is reduced in size significantly during the "index optimisation" phase. Also your index file is liable to grow swiftly if you have applications that frequently delete and then reinsert large number of records.

  • Index Node Size

    Prior to release 4.620 the index node size was fixed at 512 bytes. With DP4 Enterprise releases it is possible to select the index node size. Based on the number n you enter the actual node size will be 2^n* 512 bytes, i.e. 0 gives 512 bytes, 1 1024 bytes, 2 2048 bytes, and 3 4096 bytes.

    Choose 0 if you require your database to be compatible with earlier releases of DP4 (up to 4.523/4.619), or 16 bit versions of DP4.

    The larger the value you select the smaller the database index file will be. With a size of 3 the index file may be be up to 15% smaller than it would be with index node size 0. Additionally your index will be flatter, and disk cache utilisation may be improved (because most operating systems divide files into 4096 byte pages), so database look up will be faster if performance of your database is disk bound. If your database is processor bound (likely for database index files not much larger than the size of the available memory) increasing the index node size may reduce performance slightly.

  • Enhanced Index Compression

    DP4 Enterprise can compress the index file more efficiently than earlier DP4 releases. It does this by removing trailing spaces from character key fields.

    You must not select this option if any character key fields contain binary data that includes binary 0 (it does not matter if non key fields contain such data). Do not select this option if you require compatibility with earlier releases (though you can revert to the old index compression if required).

    Select this option if you would like to enable the improved compression.

  • Enhanced Data Compression

    Although using this option reduces DAT file size substantially, so might appear to be useful for "program" databases you are distributing via some online upgrade process, the database DAT file will probably not be compressed nearly so well by software such as ZIP programs, so using it for this reason may not be a good idea.

    If this option, introduced in release 4.622, and only available with the new database structures, is enabled, then the "rebuild data and index files" option of REORGDB will add information to the DCI (data compresesion information) file that will most records in the data file to be compressed to a very high degree. This option will cause some minor loss of performance, so should only be enabled where disk space is restricted, or the database is very large.

  • Data Pointer Size

    If you are unsure, what values you need for the data pointer and data sequence size, it is safe to set them both to a low value such as 2 or 3. REORGDB will increase your chosen values if need be.

    This option, introduced in release 4.622, and only available with the new database structures, controls the maximum data file size. A value of 4 bytes, the default, is required for data files larger than !6 Megabytes, and suffices for data files up to 4 Gigabytes. A value of 3 bytes may be enough for "Program databases" and will reduce the size of the IND or PTR file slightly.

  • Data Sequence Size

    This option, introduced in release 4.622, and only available with the new database structures, controls the maximum number of records in the data file. A value of 4 bytes, the default, is required for data files that will contain more than 16 million records, and suffices for data files containing less than 4 billion records. For databases with fewer than 16 million records using a value of 3 bytes will reduce the size of the IND file by a significant amount.

As of release 4.622, specifying the -ADVANCED command tail on the command line to REORGDB -DATA, will upgrade the database to use all the new structure features. On all substantial databases we have tried this with so far the final database has a DAT file at least two thirds smaller than the original database, and the index has usually been reduced in size by around 50%. If you specify the -TIGHT command tail as well, REORGDB will use the smallest safe values for the data pointer and sequence sizes.


All tables

Enable this option to list details for all the tables on the database


Allow block fetches

Enabled by default. Use this option to test the likely performance benefit of using the block fetch feature in other database applications


Alt func

Colours used to display function key areas for pop-up windows


Amending an Existing User

  1. Follow the instructions in Modifying the User List to modify the user list and display the User Set Up dialog box

  2. Use <Ctrl+P>, the <Cursor> keys and <Enter> to select a user from the popup list. The Amend/Delete/Password menu is displayed

  3. Select option 1 - Amend user

  4. Enter the user details as described in Setting Up a User

  5. Save the changes and press <Esc> several times to return to the DP4 System menu


Amending the Keystrokes in a Demonstration

  1. On the operating system command line, load the Redirect utility with this command:

    RED<x> -READ <file 1> -WRITE <file 2> <program name>

    For example: REDW -READ custenq.red -WRITE custupd.red DP4 loads Redirect and the DP4 System menu into memory. The keystrokes are read from the file CUSTENQ.RED in the current directory and played back to the file CUSTUPD.RED

  2. Before the first keystroke is played back from the file, the Redirect file amendment popup is displayed:

    Redirect file amendment

    Map 10200 Field 1

    1 Use existing entry
    2 Change entry
    3 Use entries until interrupted
    4 Use entry but review action

    Enter option required

    The options are explained below:

    Option Description
    Use existing entry

    Uses the keystroke recorded in the file and then redisplays the Redirect file amendment dialog

    Change entry

    Allows you to enter a keystroke from the keyboard. If your input causes the current map or field to diverge from the map or field specified in the file, the Redirection error popup is displayed. Re-synchronise the keystrokes to allow the demonstration to continue as described in Playing Back Demonstrations

    Use entries until interrupted

    Allows you to continue the playback and stop the Redirect file amendment dialog from being displayed for every field. To regain control over the playback at any point, press <Ctrl+B> to redisplay the Redirect file amendment dialog

    Use entry but review action

    Uses the keystroke recorded in the file for the field, but amends the action. The Select action popup is displayed for you to add or remove a comment or pause (see Recording Demonstrations ). The Redirect file amendment dialog is then redisplayed

Once you have finished making the new redirect file, you can play it back as described earlier in Playing Back Demonstrations.


Application

The database and associated programs which meet the requirements of a system.


Application Database

In DP4 this is the database that stores the user's DP4 applications , as distinct from a database storing data.


Application Menu

A menu that is displayed when the user logs onto the system. This menu is stored on the application database.


Architecture of a DP4 System

Architecture of a DP4 Sytem

This appendix tells you about the underlying structure of a DP4 based system. It covers:

  • Data and Index Files

  • Log Files

  • Configuration Files

  • Temporary Files

  • Base Dictionary

  • Database Manager


All lines of the same type?

Answer 'Yes' if there is one type of line in the import file. If the lines contain different fields, you will need to define tagged/untagged field groups. For an explanation of field groups and how to use them to import data, refer to the earlier section Understanding Field Groups


Attached Map

A map that is displayed at the coordinates of a specified field that is already on screen.


Auditing the Transaction Log

Examining the Transaction Log should form part of the testing of almost any program that updates a DP4 database. It can also prove very useful when trying to track down problems in a live system.

The transaction log records each database transaction since the last backup, provided that you have transaction logging switched on in your system setup. You can use the Transaction log auditor utility (AUDITOR) to view the information.

You can also view before-images of amended records, provided you enable the Record "before-images" option in Console/Transaction Logging.

  1. On the DP4 System menu, select the Backup and Recovery option

  2. On the Database Backup, Recovery and Check menu, select the Auditor option. The Transaction Log Audit menu is displayed

  3. Select the File option, and enter the database name. Enter the Log file path, as in this example

    Transaction log audit   Database         SALESORD    Search table
                                         Log file path   D:\DP4\DB\

    The transaction log entries are displayed. For example:

    In this screen, the keys you can use are:

    Keys Action

    <Cursor>

    Moves between the entries

    <End>

    Moves to the bottom of the current screen

    <End> twice

    Moves to the end of the list

    <V>

    Selects the View option to toggle between three modes of viewing the transaction log:

    • Multi-record display showing transaction log entries
    • Single-record display with field headings, which allows you to view the 'amended'/'before image' of a table record
    • Single-record display as previously, but displays fieldnames instead of field headings. (You need to know the fieldnames for formulas in conditional searches)

    <S>

    Selects the Search option to specify the table whose entries you wish to browse using the Last and Next options

    <C>

    Selects the Cond option to apply a condition for searching the search table

    <N>

    Selects the Next option to move to the next entry for the search table

    <L>

    Selects the Last option to move to the previous entry for the search table

    The standard information format is explained here:

    Column Description
    Number

    The number of the action in the log file. You can go directly to a transaction record using the action bar Goto option

    Process number

    The process number of program that initiated the transaction. These are not automatically tied to user names. However if you include the username information in at least one database update you will be able to tie process numbers to particular users.

    Action

    The action description

    Table

    The table affected by the change

    Date and time

    The date and time of the action

  4. Move to a transaction log action line and press <V> to view the amended or 'before-state' of the record. The field headings are displayed on the left, and the record content is displayed on the right

Searching For Log Entries

To find all the transactions made on a particular table:

  1. Select the Search option

  2. Enter the name of the table to be searched and press <Enter>

Use the <Next> and <Last> options to browse through the log entries of the table, relative to your current position

Conditional Searches

You can further refine table searches by applying a condition to the search table:

  1. With a search table selected, select the Cond option and enter the search condition. For example, if the search table is CUSTOMER, to find customers belonging to the East Anglian region, enter:
    region_code._="ANG"

    If you are unsure about the names of fields, use the View option to toggle the display

  2. Use the <Next> and <Last> options to browse the records that meet the condition


Auto-increment Facility

A facility that automatically supplies the next key value for new records that have numeric keys or compound keys ending with a number.


Auto enter dates and times

When this option is enabled you do not have to type the separator character between each part of the date or time or press <Enter> to terminate the field entry


Auto-enter passwords

Enable this option to allow <Enter> to be omitted after typing passwords. (For this to work, the password must of course be equal in size to its entry field)


Auto-enter Yes/No input

Enable this option to allow <Enter> to be omitted for Yes/No field entry


Autoincrement

Enable this option for a table whose last key component is a number if you want DP4 to allocate a unique number to each entry automatically


Automatic Creation of Importer Programs

DP4 generates the import parameter set automatically provided that the export program used to generate the export file is on the same database.

To copy the export program from another database or system, follow the earlier instructions in Transferring Report Writer Programs Between Databases. If the export file is exported from a non-DP4 system, follow the later instructions in Setting Up Importer Programs to set up the import parameters manually.

  1. Automatic Creation of Importer Programs is one of the utility options in IMPSETUP. To access this via the DP4 menu, select Import/Export and Reporting and then Importer Setup and Utilities, or for releases prior to 4.621 select Other facilities , then Importer facilities option and finally select the Setup and utilities option.

  2. Enter the name of the target database. The Importer Facilities screen appears.

  3. On the Edit menu, select option 2 - Automatic program generation. A screen appears showing all the Report Writer Export programs on the database.

    An asterisk (*) after an export program name indicates that an import program already exists.

    You can create an import program from these, as follows:

    • Use the cursor and <Enter> keys to select the export programs
    • When you have finished selecting, press <Tab> to generate the import programs.

    Import programs are created with the same names as the export programs, except that names that begin with "EXP" are changed to begin with "IMP".

    DP4 displays the parameter and table names as the parameters are generated and you are returned to the Edit menu.

  4. Press <Enter> to return to the DP4 System Main menu.


Automatic Increment of Key Values

If the primary key field has the data type 'Number', this key can be incremented automatically by the DP4 system. A multi-part key can also be incremented in this way, provided that the last part of the key is a number. If you use this feature, then each time a user enters a new record, DP4 supplies the next number in the sequence. For example, for the CUSTOMER table in the example Sales Order database, the customer number is allocated automatically.

In a multi-user system, the numbers remain contiguous, even if users abandon half-completed transactions.


Auxiliary Database Controller (ADC)

The ADC processes all the database calls made by your application program. It is a skeleton program, into which you can add whatever code you want executed before the database manager code.


Background

Colours used to clear the screen. For consistency, make these the same as the Text colours


Backing up a Database

This section is divided into the following topics:

  • When to make a Backup of your Database

  • Types of Backup

  • Session Numbers

  • Backing up to Diskette

  • Backing up to Tape Streamer

  • Dynamic Backup

  • Backing up just the Transaction Log

  • Performing a Dummy Backup

  • Modifying the Backup Operation


Backing up just the Transaction Log

The contents of this page apply only up to release 4.525/4.621, as the facility described has been removed in release 4.622. In release 4.622 you can use DYNABACK -SETSESSN -NODELETE to cause the transaction log to be restarted. The previous log file is renamed by changing the file extension from .log to.lbk, and you can use the utility of your choice to copy this file to another location if desired.

When backing up log files only, you must still occasionally make a complete backup of the database. One possible scheme is to make a complete backup once a week and a log-only backup each day. Then, should a database corruption occur, you can use the database restore utility:

  • To restore the complete backup of the database
  • To apply all the log files that are dated later than it
  • To apply the log of the day

Restoring a database by applying log files is both a slow and unreliable process; one bad or incorrectly labelled log file disk can prevent the successful use of DBRESTOR.

  1. On the DP4 System menu, select the Run program option.

  2. Use this command to specify that only the transaction log is to be backed up:
    DBBACKUP −LOGONLY.

  3. The Database Backup utility screen is displayed, and you are asked to enter the database name.

  4. DP4 displays the current session number for the database, and prompts you to confirm that you have backed up the database using an alternative method:

    Has the database been successfully backed up? No

    Answer 'Yes' to continue with the backup operation. Answer 'No' to return to the DP4 System menu

    You are asked whether you want to verify the backup file. Answer 'Yes' to make DP4 check that the file is written to disk correctly. Answer 'No' to skip the backup verification.

  5. Insert a diskette for the log file, as requested, and press <Enter>. DP4 begins copying the transaction log.

  6. Once the transaction log is copied, you are prompted to label the disk with the current session number for the log. The Database Backup utility terminates and you are returned to the DP4 System Main menu.

    The log file will have been deleted from the database directory and the database and log session numbers incremented. A new log is started by carrying out a transaction on the database.


Backing up the Database

Make sure you have diskettes disks available with sufficient free space for a backup of your database:

  1. On the DP4 System Main menu, select option 3 - Backup recovery

  2. Select option 1 - Backup database

  3. Specify the database name, and load the disks as prompted. If 'transaction logging' is selected in your system configuration, you are prompted for a separate disk to store the transaction log.


Backing up to Diskette

The screen shots on this page apply only to DBBACKUP (up to release 4.621). From release 4.526/4.622 DBBACKUP works in the same way as DYNABACK, and the screens are slightly different.

To back up the database files to diskette carry out the following procedure:

  1. Make sure you have a supply of formatted diskettes (or use the -format option)

  2. If you plan to use DBBACKUP ensure that the database is not in use. (Shutting Down a Database tells you how to shut down a database.) (Dynamic Backup describes how to backup a database that is in use.)

  3. On the DP4 System menu, select the Database Backup/Recovery option. The Database Backup, Recovery and Check menu is displayed:

  4. Select the Backup option, followed by the database name, or DYNABACK, followed by the database name. The Database Backup utility screen is displayed:

  5. DP4 displays the current session number for the database, and, for DBBACKUP only and prior to release 4.526/4.622, prompts you to restart a previous backup. Answer 'No' to start a new backup. Answer 'Yes' if you started the backup process earlier but did not complete it (for example, you stopped the backup after the first disk of two, and have not changed the database in any way since). Answer 'Abort' to return to the previous menu. As of release 4.526/4.622 it is no longer possible to restart an interrupted backup.

    You are prompted to verify the backup file:

    Answer 'Yes' to ask DP4 to check that the file is written to disk correctly. Answer 'No' to skip the backup verification. Answer 'Abort' to return to the Database, Backup, Recovery and Check menu.

  6. You are prompted to enter a comment. Enter some text (up to four lines of characters) that describes the database and the changes made to it for this session. This text is displayed when you start the database restore process, so you can confirm that the correct database is restored.

  7. Complete the comment by pressing <Enter> on the last line.

  8. Load a diskette as requested and press <Enter>. DP4 begins copying the database. DP4 updates the display regularly while copying, so that you know how much of the database is copied, and how many Kilobytes have been verified, for example:

  9. If the database is larger than the size available on the first disk, DP4 prompts you to insert another disk. Label each disk with the database name, the session number for the database and the number of disks used so far.

  10. For DBBACKUP only, and for releases prior to 4.526/4.622 DP4 prompts you to enter a separate disk for the transaction log. Once the transaction log is copied, you are prompted to label the disk with the session number for the log. (Note that the session number for the log is different to that for the database). As of release 4.526/4.622 the transaction log is included in the BKP file with both programs.

  11. The Database Backup utility terminates and you are returned to the Database, Backup, Recovery and Check menu. If your system had a log file, it has been deleted and the log session number incremented. The database session number has been incremented. A new log is created by carrying out a transaction on the database.


Backing up to Tape Streamer

The contents of this page apply only up to release 4.525/4.621, and are possibly best ignored for all releases incorporating the DYNABACK −COPY facility. For these releases the best way to back up a database to tape is first to use DYNABACK −COPY to backup and check the database, and then to use tape backup software to backup this copy. You should never use a tape backup utility to backup the real database files while they are in use, as if you restored such a backup it would almost certainly be corrupt.

The tape backup facility is directly available under most of the supported operating systems (on Windows Windows 3.1 you have to use an MS-DOS batch file). Before you use these steps, you will need to know how to write a batch file or shell script and include the commands necessary to back up to the tape streamer.

The advantages of using the DP4 tape backup facility to run the batch file rather than using the operating system are:

  • A partial integrity check is performed on the database.

  • You are prompted to backup the transaction log and it is deleted if you do.

  • The session numbers for the database and transaction log are incremented.

You should create a batch file in the current directory, or in a directory that is included in your path, called:

  • TAPEBKP.BAT for DOS, Windows and FlexOS
  • TAPEBKP.CMD for OS/2
  • tapebkp for UNIX

Include the instructions necessary to back up the database files from your workstation to the tape streamer you are using. To make the batch file generic to any database, use the variable %1 in place of the database name. DP4 will pass the name of your database to your batch file when you run the Database backup utility.

This is an example TAPEBKP.BAT batch file:

echo "Backing up to tape streamer on drive T:"
BACKUP %1.* T:\
echo "Database file copying complete"

  1. On the DP4 System menu, select the Run program option.

  2. Use this command to specify a tape backup:
    DBBACKUP -TAPE
    and press <Enter> to execute it.

  3. Select the database name. The Database Backup utility screen is displayed.

  4. Press <Enter> when you are ready to back up the database.

  5. The Database Backup utility runs the batch file TAPEBKP.BAT. The database name is substituted in place of the variable %1 in the batch file.

  6. DP4 asks you if the backup was successful.

    Answer 'No' to repeat the backup procedure. Answer 'Yes' to continue with the backup process.

  7. If a log file is found, you are prompted to verify it:

    Answer 'Yes' to make sure the transaction log is written to disk correctly. Answer 'No' to skip the backup verification. Answer 'Abort' to return to the Database, Backup, Recovery and Check menu.
    DP4 calculates the database checksum. This is a partial check to help ensure that the database files are valid.

  8. You are prompted to load a new disk for the log file. Once the log file is copied, DP4 prompts you to label the disk with the session number for the log.

The Database Backup utility terminates and you are returned to the DP4 System menu.


Base Dictionary

The base dictionary contains the definition of the underlying database used by DP4 to store the structure of a database. The Database Creation utility copies the base dictionary tables from the BD database to create a new database. You don't need to install the BD database on the end user system.

The base dictionary tables are normally hidden. However, you can view and even update the tables in almost exactly the same way as the application database tables using DP4 facilities such as Browser, QA Build, Report Writer or Importer. To do this, run the DP4 utility program from the command line with the command tail −BASEDICT. Press <Ctrl+P> on the table entry field, and the base dictionary tables are shown with the preface @, such as @NAME_TABLE. Take great care when updating base dictionary records for a database as the tables are critical to the operation of a DP4 system, and an incorrect change might damage your database irreparably.

Table Numbering

Internally Tables, fields and roles are identified by unique numbers. @NAME_TABLE records associate names with thse numbers, so that you will not usually need to know what the internal numbers are. This scheme allows several names to be associated with a single table, field or role.

Numbers are allocated as follows:

0 to 4095 | Reserved for BD fields, roles, tables and reserved words
4096 upwards | Available for application tables, fields and roles


Base Dictionary

The base dictionary is a component of the DP4 environment. It defines the underlying structure for the data dictionary. An application database is initialised by making a copy of the empty base dictionary.


Beep on ignored input

Enable this option to inform the user with a beep that DP4 has ignored a typed character


Box Drawing

The keystrokes that draw boxes and lines are as follows:

Action | Key | Alternative
Selects box drawing mode | <Ctrl+]> |
Size the box by moving the current corner left, right, up or down | <Ctrl+←> <Ctrl+→> <Ctrl+↑> <Ctrl+↓> | <Ctrl+A> <Ctrl+F> <Ctrl+W> <Ctrl+Z>
Make the top left or bottom right corner the current one | <Home> <End> | <Ctrl+Q> <Ctrl+\>
Move the entire box left, right, up or down | <←> <→> <↑> <↓> | <Ctrl+S> <Ctrl+D> <Ctrl+E> <Ctrl+X>
Draw the box, preserving text | <Ctrl+N> |
Draws the box, preserving text, and exits box drawing mode | <Enter> |
Draw the box, overwriting text | <Ctrl+]> |
Draw a raised horizontal line instead of a box | <Ctrl+L> |
Switch box drawing off | <Esc> |
Erase the box. (The outline must be the same shape as the box to be erased) | <Del> |


Building a Database

When you have a clear idea of the database you want to design, DP4 utilities will allow you to implement it very quickly. This section covers:

  • Creating a database

  • Modifying a database

  • Entering, deleting and viewing data

  • Printing the database structure

  • Setting database security levels


Button

A graphical push-button used as a control in a map. Under Windows, map buttons have an associated keystroke that is generated when they are clicked.


C Programming

C is one of the third-generation development supported environments in DP4. The development environment provides all the facilities necessary to develop powerful database applications.


CASE Tools

User-driven tools for helping the developer design and implement software applications.


Centre maps

Enable this option to centre maps, where space permits, on the screen


Change-Verify

The change-verify mechanism ensures that whole transactions are written to a database (with any specified checks being made) in one operation. This means that multiple record updates made by multiple programs are kept in step.


What's New in the DP4 Developer's Manual

This page will tell you about recent changes to this manual. The most recent changes are mentioned first.

New in 4.624

  • 30 Apr 2010 Information in DP4 Limits updated to include information relevant to release 4.624.

New in 4.623

  • 29 May 2008 Various changes to the information about copydb and reorgdb.

  • 1 Jun 2005 Information on dbcheck updated, with information about new −full option.

New in 4.622

  • 13 Aug 2004 Updated in many places, especially the sections on backing and reorganising databases with information about release 4.622.

New in 4.621

  • 14 Feb 2004 Updated in many places with new details of how to access the various DP4 utilities from the 4.621 DP4 menu. Section on translating maps extensively revised.

  • 12 Jan 2004 SETLINK information updated. Information on new Automatically turn off unused active indexes option added.
  • 9 Jan 2004 REORGDB information updated. Information on 4.620 Advanced Database Properties dialog added.
  • 4 Oct 2003 Partially revised for 4.620/4.621

New in 4.620

  • 22 Jan 2003 Information on edit DP4 menu pages updated, and some changes to table of contents.

New in 4.619

16 March 2001 Some of the links only accessible from screen shots were not working properly. Some more information on the SHUTDOWN program has been added. The section on Importer has been slightly reworked, and is, we hope, now arranged more logically.


Changing a User Password

  1. Follow the instructions in Modifying the User List to modify the user list. The User Set up dialog box is displayed, as described in Setting Up a User

  2. Press <Ctrl+P> to pop up a list of users already set up on the database. Select a user to amend. The Amend/Delete/Password menu is displayed

  3. Select option 3 - Password. The Change password dialog is displayed

  4. Enter the new password twice. You are returned to the User Set up dialog box

  5. Press <Esc> several times to return to the DP4 System menu


Changing a User's Preferences

When users log on, their user preferences are read from the system database. A new user inherits the default set of user preferences on the system database, derived from USERDATA.SYS. You can change a user's preferences as follows:

  1. Log onto the DP4 System menu with the username. On the DP4 System Main menu, select the Configuration Options option.

  2. On the System & User Setup Options menu, select the User preferences option. The User Preferences menu is displayed.

  3. Select option 1 to define the date and time formats:

    Click on the appropriate areas of the illustration above for further details.

  4. On the User Preferences menu, select option 2 to set up the number format, keying conventions, words for 'Yes' and 'No' and menu input/output defaults:

    Click on the appropriate areas of the illustration above for further details.

  5. On the User Preferences menu, select option 3 to change the colour and display preferences:

    Click on the appropriate areas of the illustration above for further details.

    Some options define colours for different categories of screen text and maps. These options only apply when running 4.5xx programs Text Painting describes how to use the colour specification dialog. For messages, warnings and errors, you can enable a beep to sound on display. As well as field headings, all 4.5xx maps may be given a 3D effect with the addition of a shadow.

  6. When you complete each screen, the User Preferences menu is re-displayed. If you have completed making changes, select option 8 - Exit. The Save/Abandon Changes menu is displayed. Select option 1 - Save changes. You are returned to the System and User Setup Options menu.


Changing Table, Field and Role Names

  1. On the Database Structure Management menu, select the Create/modify database option and select the database

  2. On the Database Creation/Changes menu, select option 2 - Name changes

  3. Press <Ctrl+P> to pop up a list of the names on the database

  4. Select the name to change. Information about the name is shown in this format:

  5. Enter a new name. For example:

    If you are developing a single-language application, leave the New language number as it is. To add a name in a new language, change the number to a language you have already set up using the Menu Setup and Amendment utility

  6. Press <Tab> to complete the name change

  7. When you have completed the name changes, press <Esc> several times to return to the DP4 System Main menu


Changing Table and Field Headings

Table and field headings consist of 4 lines of text, 16 characters wide. The DP4 Data Entry and Enquiry, Report Writer and Automatic Program Generation utilities use the headings for generating maps, and they are also used in programs such as BROWSER and AUDITOR.

You already set up headings for all your tables and fields when you create the table in the first place. The Headings option in MAKEDB is provided for two main reasons:

  • The Headings option can be made available to developers or users who may be creating QAB or You can use the Headings option if

    To add headings in other languages, follow the instructions in Supporting National Languages.

    1. On the Database Structure Management menu, select the Create/modify database option and select the database

    2. On the Database Creation/Changes menu, select Option 1 - Headings

    3. Enter the language number. Unless you are developing a multi-language application, set the language number to 0

    4. Enter the table name, or press <Ctrl+P> to pop up a list of tables on the database. The Table/Field Headings menu appears:

    5. Select option 1 - Table heading. Enter the new table heading, and press <Enter> to complete the entry

    6. On the Table/Field Headings menu, select option 2 - Field headings. A popup list of fields in the table appears. Use the cursor keys to move through the list. Highlight the field you wish to edit, and press the <Enter> key, and enter the new field heading.

    7. Repeat the last step as often as requiredEnter the new field heading, and press <Enter> to complete the entry (SAA users press <Tab>)

    8. When you complete the changes, press <Esc> several times to return to the DP4 System Main menu


Changing the Database

It is not normally necessary to change the database for a new language. However, there are two possible exceptions:

  • Field headings

    These are used by the DP4 utilities such as Report Writer, the Data Entry and Enquiry utility and the Automatic Program Generation utility.

  • Country-dependent fields

    For example, currency fields (Lire, Francs), description fields such as ITEM in a POS system.


Changing the Database Headings

To change the names of table and field headings on the database:

  1. On the DP4 System menu, select the Database Management option.

  2. On the Database Structure Management menu, select the Create/modify database option.

  3. Enter the database name and press <Enter>. The Database Creation/Changes menu is displayed.

  4. Select option 1 - Headings.

  5. Enter the number of the new language (other than language 0) into which you wish to translate the headings.

  6. Enter the name of the table to which the headings relate. The Table/Field Heading menu is displayed.

  7. Select option 1 - Table heading to amend the table heading. The table heading is shown for language 0 and the language you are amending. The Edit/Use Language Heading menu is also displayed.

  8. Select option 1 - Edit heading to move to the heading field for the language. The language 0 heading is displayed as a default.

    .

  9. Press <Tab> to complete the field (SAA users press <Enter>). You are returned to the Table/Field Heading menu.

  10. Select option 2 - Field headings. A list of field headings is displayed for language 0 and the language you are amending.

  11. Move the cursor keys to select the field heading you want to amend, and press <Enter>. The Edit/Use Language menu is displayed.

  12. Select option 1 - Edit heading to move to the heading field for the language. The language 0 heading is displayed as a default.

  13. Enter the new heading.

  14. Press <Tab> to complete the field (SAA users press <Enter>).

  15. Press <Esc> when you have finished amending the field headings.

  16. On the Table/Field Heading menu, select option 8 - Exit.

  17. On the Save/Abandon menu, select option 1 - Save Changes.

  18. Press <Esc> several times to return to the Main menu.


Changing the Default User Preferences

To change the default user preferences held in the USERDATA.SYS file, run the DP4 System Setup utility from a command prompt (and not the DP4 menu), as follows:

DFSETUP

On the DP4 System Setup menu, select the User Preferences option and change the preferences as described in Changing a User's Preferences. Please note the following points:

  • DFSETUP will save the new USERDATA.SYS settings in its working directory, and therefore not necessarily in the directory where USERDATA.SYS was read from. If necessary, and when you are satisfied with your changes, copy the modified userdata.sys into the appropriate location. It is a good idea to keep a backup of both your original and your final userdata.sys files.

  • If you run DFSETUP from inside the DP4 menu, or at the same time as the DP4 menu is being run on the same computer, or from within or at the same time as QAB programs that use the DP4 menu facilities, then you may not be modifying the default preferences at all. An easy way to check whether you are modifying the default user settings is to go to the Colours and miscellanous cosmetics screen. If the default user settings are being modified you should have access to the Read Authority Level and Write Authority level entry fields. If the cursor skips past these fields when you navigate round the dialog then you are not modifying the correct settings. If you are sure the DP4 menu is not active, it may be that a menudata.sys file has been left on the machine from the previous time DP4 menu was in use. Quit DFSETUP, delete the rogue menudata.sys file and try running DFSETUP again. If the problem persists, then the USERDATA.SYS file has been corrupted. Run USERDATA, which should clean up the USERDATA.SYS file. If the problem still persists, then you need to create a completely fresh USERDATA file by running USERDATA -RESET.


Changing the Language Table Preferences

The language table preferences are stored on the System database. They affect all users who have the language specified in their setup.

  1. Language information is edited using the DP4 MENUEDIT utility. To access this via the DP4 menu, select Configuration Utilities and then the User and Menu Management option, or for releases up to 4.620, select Other Facilities and then the Menu Facilities option.

  2. Enter the name of any database on your system (the language information is automatically stored on the system database)

  3. Press <Enter> to accept the default language number

  4. On the Menu Utilities Main menu, select option 6 - Language table. The Language Maintenance dialog is displayed:

  5. Press <Ctrl+P> to pop up a list of existing languages on the system database. Use the <Cursor> keys and <Enter> to select a language. The following dialog appears:

  6. Select the Amend language option. This example shows the language maintenance dialog for French:

    For details of the options, click on the appropriate area in the illustration above.

  7. When you have completed the screen, select the OK button

  8. Press <Esc> to return to the Menu Editing menu. Press <Esc> several times to return to the DP4 System menu


Changing the Number System

  1. On the DP4 System Main menu, select the Run program option. You are prompted to enter an operating system command

  2. Enter the command to run the DP4 number system utility:
    NUMBSYS

  3. The Number system utility screen is displayed. Select the database

  4. A list of number systems is displayed, as shown in Selecting the Database Number System. Use the cursor key to highlight the new number system, and press <Enter>


Changing the Record Search Index

DP4 shows the records in the sequence determined by the key of the table.

This search sequence is called the Main Index. To change the sequence, follow these instructions:

  1. Select the Index option on the menu bar. DP4 displays the list of available indexes:

  2. Use the cursor keys to highlight the new search index, and press <Enter>. DP4 displays the selected search index underneath the table name

  3. Select the Scan option. Using the cursor keys to move through the records, notice that the records are displayed in the sequence determined by the new search index

  4. Press <Esc> to return to the menu bar


Changing the System Language

This section tells you how to change the language for a system. All utility and application programs run from the command line will use the maps in the selected language.

  1. At the command line, run the DP4 System Setup utility: DFSETUP
    The System Setup menu is displayed

  2. Select option 3 - User Preferences. The User Preferences menu is displayed

  3. Select option 3 - Colours and miscellaneous. The Colours and Miscellaneous screen is displayed. The first field in this screen specifies the default language number

  4. Enter the number of the new default language. If you choose a language other than 0 and some maps are missing, DP4 will display the language 0 equivalents

  5. Press <Enter> or <Tab> to complete the dialog. The User Preferences menu is redisplayed. You may also wish to select the default language preferences, as described in Selecting the Language Preferences

  6. Select option 8 - Exit. On the Save/Exit menu, select option 1 - Save changes. The changes are saved to the user configuration file userdata.sys. You are returned to the DP4 System Setup Main menu. Select option 8 - Exit to leave the System Setup program

All applications run on the system are automatically displayed in the selected language.

The system default only applies to programs run from the command line. It does not apply to menus and application programs with user logon, because those programs will use the user's language.


Changing the System Language Preferences

Changes to the system language preferences are stored in the User file USERDATA.SYS. Any program that is run from the command line (without the logon facility) uses these language preferences.

  1. Run the DP4 System Setup utility from the command line by entering the command:
    DFSETUP

  2. On the DP4 System Setup menu, select option 3 - User preferences. The User Preferences menu is displayed

  3. Select option 1 - Date and time input/display. The Date and Time input/display screen is displayed. This screen is described in Changing a User's Preferences. The language-dependent questions are:

    • Show year first
    • Show month first
    • Show century
    • Show month name
    • Separator between date parts
    • Month names

    When you complete the changes, the User Preferences menu is redisplayed

  4. Select option 2 - Number/character/menu input/display. The Number, character and menu input/output preferences screen is displayed. This screen is described in Changing a User's Preferences. The language-dependent questions are:

    • Decimal point
    • 1000's separator
    • Word for Yes
    • Word for No

    When you complete the changes, the User Preferences menu is redisplayed

  5. Select option 3 - Colours and miscellaneous. The Colours and Miscellaneous screen is displayed. This screen is described in Changing a User's Preferences.

  6. Enter the 'Default language' number and click the OK button to exit

  7. On the User Preferences menu, select option 8 - Exit

  8. On the Save/Abandon menu, select option 1 - Save changes

  9. To exit the System setup utility, select option 8 - Exit on the main menu. You are returned to the operating system


Changing the User Language Preferences

The user language preferences initially default to those on the language table. If you wish to vary the language preferences for a specific user, follow the steps in this section.

The user language preferences are stored in the user list on the System database. When users log onto menu systems or run application programs that use the logon facility, their user preferences are read, and the language preferences take effect.

Either:

  • Log onto the application menu as the user and run the option that executes the %PREFERENCES command. The User Preferences menu is displayed

    Or, to change the language preferences from within the DP4 System menus:

    • Log onto the DP4 System menu as the user
    • Select Configuration Options. The System and User Setup Options menu is displayed
    • Select option 3 - User preferences. The User Preferences menu is displayed

    • Select option 1 - Date and time input/display and modify the language-dependent parts

    • Select option 2 - Number/character/menu input/display to select the 'Yes' and 'No' words for the user

      Note
      Further details on the language-dependent options within the user preferences are given earlier in Changing the System Language Preferences.


Changing your Password

If a username is set up to use the DP4 System menu, you can add or change the password as follows:

  1. Log onto the DP4 System menu with the username. If no password has been set up yet, press <Enter> on the password field

  2. On the DP4 System Main menu, select Configuration Options

  3. On the System and User Setup menu, select the Password option

  4. Enter a new password as described in Changing a User Password. The Change password utility terminates and you are returned to the System and User Setup menu

  5. Exit from DP4, log on to the DP4 System menu with the appropriate username and test whether the new password is accepted


Character collate sequence

The collate sequence indicates whether you have customised the sequence of characters used for U-type fields (for example, to add extra characters, or customise for a national language)


Character for NULL

Enter the character that represents NULL values in the import file. This is normally an exclamation mark (!)


Check all tables

Enabled by default. Disable this option if you want to check an individual table


Check Box

Under Windows, check boxes in maps enable or disable a particular command or answer a question. The option is enabled when X appears inside the check box; it is disabled when the check box is empty.


Check Change

This is a check that may be applied when updating a record. A posted record will not be written to the database if it is an amendment of an existing record, or if there are changes to any of the keys in secondary indexes that are marked for change prevention in the database. SETLINK sets up an index for change prevention.


Check foreign keys are the same?

Answer 'Yes' to check that secondary key fields are unchanged. Answer 'No' to post records without checking


Check Parent

This is a check that may be applied when updating a record. It prevents a record that has been posted to the queue from being updated if it does not have a corresponding parent record.

This check is applied only if the parent is missing in any of the indexes that are marked for parent checking in the database. SETLINK sets up an index for parent checking.


Checking a Database

The Database Checking utility (DBCHECK) displays the database status and checks the database and index files for logical integrity, as follows:

  • It checks the header record on the index file.
  • It checks that all records in the data file are for valid tables and have valid lengths.
  • It checks the data file checksum.

  • It checks that the index file is a valid one, and (unless the -QUICK command tail is applied) that each index entry references a record of the correct type. From release 4.623 DBCHECK can also check that the index entry correctly indexes the data, something that only DYNACHEK did in earlier releases.

You can specify the 'Maximum period between integrity checks (in days)' within the Console/Transaction logging screen of the DP4 system setup utility. For example, specifying a value of 1 for this option forces a daily integrity check, while 7 forces a weekly check. If you enter a NULL (!) value, you will never be forced to carry out an integrity check. When the specified number of days has elapsed and you attempt to use a DP4 utility, such as the database browser, or other program that can update the database, a warning message informs you that an integrity check must be run first.

You MUST run the database integrity checking utility often enough to ensure that you have at least one good database backup. For example, if you back up your database every week and keep the last four backups before recycling the disks, do a full integrity check at least every four weeks. If at all possible check database integrity before avery backup so that you can be sure never to have a bad backup.

It is also a good idea to run an integrity check after restoring your system from a backup. This is especially important where you need to guard against a partial backup. For example, if you have backed up from tape or used the facilities of the operating system to copy database files, running a database integrity check with the -QUICK command tail is sufficient, providing you ran a full integrity check on the backup from which you are restoring.

If uninterrupted operation of the database is required, there are two possibilities for checking database integrity

  • You can use DYNABACK -COPY to create a backup of the database while it is in use. As part of the backup process DBCHECK is run against the backed up database, which is identical to the original database at the time the backup completes.
  • You can use check database integrity dynamically with (DYNACHEK). However, this utility degrades performance and may report spurious errors if another application updates a database record that is being checked. The dynamic integrity checker does not perform all the checks of the standard integrity checker but it does check that index records match the records on the database. For example, it checks whether the index entry for ORDER record 10 actually references ORDER 10. The dynamic integrity checker is a useful diagnostic tool because you can run it against specified database tables in which you suspect errors have occurred.

The following topics are covered in this section:

  • Checking the Database Integrity
  • Checking the Database Status
  • Improving Performance for Local Databases
  • Checking the Database Integrity Dynamically
  • Modifying the Operation of DYNACHEK


Checking the Database Integrity

Checking the integrity of a database can take some time. For a database containing several gigabytes of data, you may wish to run the check overnight. (If you want to check the database while it is on line, see Checking the Database Integrity Dynamically )

  1. On the Database Integrity Checking menu, select option 3 - Integrity check. DP4 displays status messages as it checks the database integrity:

    The number of records and index nodes counted is also displayed

    At any time, you may interrupt the integrity check with <Ctrl+S> and <Ctrl+C> or <Ctrl+Alt+Del> without corrupting the database

  2. If the database passes the integrity check, the following message is displayed:

    If the database fails the integrity check, a message is displayed to identify the problem area:

    • If the message indicates a problem with the index, then you may be able to re-generate the index file using the database reorganisation utility (see Reorganising with Compression). However, if you have followed proper backup procedures you will probably find it quicker, and less risky to restore a backup copy of the database and apply the transaction log.
    • If the message indicates a problem with the data file, then you will need to retrieve a backup copy of the database and apply the transaction log to roll-forward the database, as described in Restoring a Database.

    In the event of a database being corrupt, and a good backup not being available technical staff at Itim Technology Solutions will attempt to repair the database if it is supplied on Tape or CD-ROM. This is not covered by your support agreement, and we reserve the right to charge for this work.

Performing Additional Checks on Consistency Using -full

Up to release 4.622 DBCHECK performs only rudimentary tests of the relative consistency of the data and index files - it only checks that each index entry points to a record of the correct type, not that the index entry actually matches the data in the record. Rarely a database might have a corruption of this type. As of release 4.623, by specifying the command fail -full you can force DBCHECK to perform this type of checking, which was previously only available in DYNACHEK.

When you use this option DBCHECK calculates a 32 bit CRC for every index entry, and when the data is accessed compares this CRC with the CRC of the corresponding data. This increases the time needed for checking the database by a considerable amount, though exactly how much will depend on the number of records in each table and the number of secondary indexes. The increase is greater for a database using advanced compression (and for these databases corruptions of this type caused by a low level error in the data would almost always show up as a data file corruption). However, even with full checking the time taken to check a database is not that great - perhaps 12-15 minutes per gigabyte of data, or twice that if the database has advanced compression, compared with around 5 minutes per giagabyte for the less thorough check performed when you do not specify -full.

The checking performed by DBCHECK -full is not quite as exhaustive as the checking in DYNACHEK: because the validation of the index entry is done using a CRC, there is a very slight possibility that a corruption might not be detected.


Checking the Database Integrity Dynamically

Unlike DBCHECK, which directly checks the low level structure of the database, DYNACHEK works in the same way as an application program. DYNACHEK can therefore also be useful for testing things like the speed of database access over a network, as well as for integrity checking. DYNACHEK and DBCHECK are to some extent complementary programs: DBCHECK does not check consistency between the data and index files, only that each file is correct in its own right (though the -full option added in release 4.623 largely remedies this defect). DYNACHEK tests precisely this aspect of a database.

You use DYNACHEK like this:

  1. On the Database Backup, Recovery & Check menu, select the Dynachek option.

  2. Select the database. The Dynachek Facilities menu appears.

    Click on an option in the above menu for further information.

  3. To perform the integrity check on all tables, including those derived from the base dictionary, select option 1 - Integrity check. Starting with the base dictionary tables, DP4 checks all the tables in the database. It displays status messages, similar to the following ones, as it goes:

    A counter indicates the number of errors found during the integrity check.

    At any time, you may interrupt the integrity check with <Esc> or <Ctrl+S> and <Ctrl+C>.

  4. If the database passes the integrity check, the following message is displayed:

    and you can press <Enter> to return to the DYNACHEK Facilities menu.

    If the database fails the integrity check, a message is displayed to identify the problem area. However, it is possible for DYNACHEK to report spurious errors. DYNACHEK works by fetching each record twice, once using the NO_DATA flag so that only keyfield information is available. Occasionally a record may be updated between the two fetches, resulting in a problem appearing to exist, when there isn't one. You may wish to interrupt the checking and restart it on a table where a problem is reported.

    If DYNACHEK generates a system error 87 then there is certainly a database corruption, and you need to restore a backup of the database as described in Restoring a Database.

    Like most DP4 utilities DYNACHEK has command tail options that can be used to modify its operation. Click here for details.


Checking the Database Status

  1. On the Database Backup and Recovery menu, select the Check option. The Database Integrity Checking screen is displayed. Enter the database name

  2. The Integrity Checking menu is displayed:

  3. Select option 1 - Status display, or option 2 - Data file usage and status display. The last option also checks the percentage usage of the data file.

    The Status and Integrity Check display is shown in this format:

    Click on the relevant areas of the illustration above for further information.

  4. Press a key to return to the Integrity Checking menu.


Checkpoint

A checkpoint operation attempts to write the whole transaction to the database. If it is successful, it flushes the transaction log file and it unlocks any records that are locked.


Child Delete

This is a check that may be applied when deleting a record. It automatically deletes any children of a record that is to be deleted.


Child delete

Answer 'Yes' to delete child records automatically when their associated parent is deleted. For example, delete all the orders for a customer if the customer is deleted. 'Orphan check' and 'Child delete' are mutually exclusive


Child Table

A table that includes the key fields of another table, the parent .


Choosing a Reorganisation Option

  1. On the DP4 System menu, select option 2 - Database Management. The Database Structure Management menu is displayed

  2. Select the Reorganise database option

  3. Enter the database name. The Database Reorganisation menu is displayed:

    If you are not sure which option to choose, select option 3 - Rebuild data and index files

    For further information on the database reorganisation options, including their relative merits, click on the appropriate items in the illustration above.


Client

An application program running on a workstation that issues requests to another computer, the server.


Client/Server Environment

An architecture in which a program (the client) issues requests to a master program (the server) which can reside on a separate machine.


Clock date/time input

This is normally "t". Enter the character that the user can input to force the DP4 application to take the date/time from the machine's built-in calendar/clock


Close a database immediately

Close the database immediately, without waiting for users to log off. Programs using the database will fail. The database can be re-started with no ill-effects

A batch equivalent of this option is:

SHUTDOWN -DB database_name -NOWAIT


Collate Sequence

The sequence in which characters are sorted in the user national language. The COLLATE utility is used to define the sequence.


Colour

Enables you to select the colour used for new text


Commit

A commit operation attempts to write the transaction held in the transaction log file to the database, performing any specified checks.


Compound Key

See Multi-part Key.


Contention Control

A scheme to ensure that data integrity is preserved when two users attempt to update the database at the same time.


Context-sensitive Help

Specific information on the currently selected field or option on the screen.


Continuous checking

Disabled by default. Enable this option to do the integrity check twice


Control

Any component in a map which provides the user with information or allows the user to enter data or instructions.


Copy all tables

Copy completely the structure of the source database. Any tables with the same name on your database are overwritten


Copy selected tables

Copy an individual table on the source database. Press <Ctrl+P> to select a table from a popup list. When you select a table you can give it a different name at the destination database by setting the 'Rename to' field. This defaults to the original name of the table


Copying a Menu from Another Database

This option overwrites the existing menu on the destination database. Copy the menu pages individually as in Copying a Menu Page earlier if there is a menu on the database already.

  1. Start up the Menu editing utility, and enter the name of the destination database

  2. The Menu Editing Main menu is displayed

  3. Select option 3 - Copy another menu. The Menu Copying dialog box is displayed

  4. Enter the name of the source database

  5. Enter 0 for the language number, unless you are developing a multi-language application

The menu is copied, and you are returned to the Menu Editing Main menu


Copying a Menu Page

  1. Follow the steps in Selecting a Menu Page to select the menu page

  2. Select option 3 - Copy page. The Menu Copying dialog box appears. Its options are as follows:

    Option

    Description

    Database

    Enter the name of the source database. (This can be the same as the target)

    Language

    Enter 0 unless you are creating a multi-language application

    Page

    Enter the page number to be copied or press <Ctrl+P> to pop up a list. As you move round the list the currently highlighted menu page is displayed. Press <Enter> to select the menu page to be copied

  3. Press <Enter> to copy the menu page. If you have entered the number of an existing menu page, DP4 prompts you to confirm that you want to overwrite the menu

    DP4 copies the menu page and you are returned to the Menu Editing Main menu


Copying and Pasting Text

  1. This section describes how to copy and paste text using the block edit feature in the DP4 text editor:

  2. Use the cursor keys or the mouse to move the cursor to the first character in the block.

  3. Press <Ctrl+K> to switch block mode on. There are two ways to block an area: line select mode and column select mode.

    • Line select mode is the default. It blocks all characters from the start to the finish of the block.

    • Column select mode allows you to block a box-shaped area in the file.

      You can press <Ctrl+]> while you are in block mode to toggle between Line Select and Column Select mode, or enter Column mode from Box drawing mode by pressing <Ctrl+K>.

      Column select mode is especially useful if you want to move a table in the Table Planner, and the following trick will allow you quickly to select the entire table:

      1. Position the edit cursor (caret) at the top left corner of the table you want to move.
      2. Press <Ctrl+]> to enter box drawing mode. The whole table should be selected.

      3. Press <Ctrl+K> to switch to column mode.

  4. Use the cursor keys or mouse to size the block.

  5. You now have two options:

    • To cut the marked block to the buffer, use <Ctrl+Y> or <Delete>

    • To copy the marked block to the buffer, use <Ctrl+K>.

  6. Move the cursor to where you want to insert the block.

  7. Press <Ctrl+L> to paste the block into the text file.

  8. If you have finished copying and pasting, press the <Esc> key to switch off block selection mode.


Copying Data between Databases

p>DP4 provides three facilities for copying data between databases:

  • Data Transfer

    The Data Transfer utility (RECASTDB) copies data from the source database to the target. This facility is described in Transferring Data

    • You can transfer data by name where fields and tables may have non-matching internal numbers

    • You can choose to delete data that is not on the source database

    Use the Data Transfer utility if you wish to transfer data between databases that have the same structure and are on the same system

  • Data Export and Import

    The Report Writer and Importer utilities (REPSETUP, REPPRINT, IMPSETUP, IMPPOST) are used to transfer the data. This facility is described later in Exporting Data with Report Writer and Importing Data using a Parameter Set.

    The Data Export and Import facility uses the internal reference numbers of tables and fields to transfer data. If this proves unsuccessful, use the Data Unload and Load facility which transfers data by name

    The Data Export facility will only create lines in the export file up to 500 characters in length, and to creaet longer lines you need to set yo a print device that does not put mew liens between lines. If you have created an export file by other means, you can use an Importer program to import it as long as it conforms to the format described in Understanding Field Groups later in this section. In this case, there is no practical limit on the length of lines; they may hold up to 8190 characters

    The data export and import process may be summarised as follows:

    1. On the source database, create the export program with Report Writer

    2. Export the data from the source database to an ASCII file by running the export program

    3. Extract the export program to a file with Report Writer

    4. Post the export program to the target database with Report Writer

    5. Automatically generate the importer program, from the export program, on the target database

    6. Import the data from the ASCII file to the target database by running the Importer program

    Automatic facilities are provided to help you produce the export and import programs quickly and easily

    On export, you can choose to extract records, depending on conditions.

    On import, you can choose whether:

    • To allow records to be inserted that are not already on the target database
    • To allow records to be updated that are already on the target database
    • To force parent checking or parent verification to prevent a record from being posted to the target database when it fails the check. These checks are rarely, if ever, needed
    • To check that foreign keys are the same, and prevent posting if not

    You can also specify processing for import fields.

    Use the data export and import facility if you wish:

    • To copy data between databases with a different structure
    • To transfer the data to a target database on another system
    • To exchange data with another database system, or application, such as a spreadsheet or mail merge.

  • Data Load and Unload

    The data Unload utility (UNLOAD) exports data from selected tables to an export file. The data Load utility loads data from the export file into the target database. This is a simple two stage process without the line length limitation of data exporting and importing

    The data transfer is by name. This allows the data to be loaded where tables and fields have the same names but different internal numbers

    As with the Data Export and Import facility, use the data Load and Unload facility if you wish to copy data between databases with a different structure. This new facility is provided as an alternative to the Data Import and Export facility

    You can also use the Load utility to import data that originates on a non-DP4 system, if you format the ASCII file exactly as explained in Transferring Data with the Load and Unload Facility


Copying Table Structures

You can copy table structures from another database using the procedure described on this page. However, where you want the databases to have matching internal numbers you should follow the procedures descrined in Copying the Database Structure.

  1. On the Database Structure Management menu, select the Create/modify database option.

  2. Select the destination database.

  3. On the Database Creation/Changes menu, select option 4 - Copy tables and enter the name of the source database.

    If you select a database other than the database you selected in step 2, the Copy All/Selected Tables menu appears:

    DP4 prevents an existing table with the same name from being overwritten. If the table name already exists, DP4 prompts you for a new table name.

    DP4 displays the name of the fields in the table as it is copied.

  4. You can repeat step 3 as often as required. When you have finished copying tables you can select Option 5 to perform the necessary Index Analysis. Press <Esc> several times to return to the DP4 System Main menu.


Copying the Database Structure

There are two utilities for copying the database structure:

  • The Data Structure and Transfer Utility, RECASTDB

    This utility copies the database structure from the Data Transfer utility source database to the target database, completely overwriting the target's structure. The source and target databases must be on the same system. You can specify whether maps, help messages, Report Writer, Message Manager, QA Build and Importer programs are also copied. This utility is described in Transferring the Database Structure.

    Use the Data Structure and Data Transfer utility in these circumstances:

    • When you have used the database creation utility to create a new, empty database and want to copy the structure of an existing database onto it.

    • When you have modified the structure of a database and want to over write older versions of it with the new structure, while retaining their existing data.

  • The Data Dictionary Extract and Post utility, COPYDB

    This utility copies the database structure in two stages:

    • First you extract the database structure of the source database to a file.

    • Then you post the database structure from the file to the target database.

    Use the Data Dictionary Extract and Post utility if you wish to merge two databases, or if you wish to transfer the structure to a target database on another system.


Count only

Disabled by default. Enable this option if you just want counts of the records in each table


Counting the Records in the Database

To count the records in tables, use the following procedure. If you want to count records while the database is on line, refer to Checking the Database Integrity Dynamically

  1. On DBCHECK's Database Integrity Checking menu, select option 5 - Count records on database

  2. DP4 counts the records and prompts you to select the output destination. The record count is displayed in this format:

    Record count for database SALESORD

    18 Oct 1994

    Table name

    Record count

    Record size

    Total size (Kilobytes)

    COMPANY

    3

    140

    1

    CUSTOMER

    182

    162

    29

    EMPLOYEET

    42

    52

    3

    MATERIAL

    54

    58

    4

    ORDER_HEADER

    215

    56

    12

    ORDER_LINE

    805

    36

    29

    ORDER_PAYMENT

    294

    52

    15

    PAYMENT_MEDIA

    9

    34

    1

    REGION

    47

    48T

    3

    SUPPLIER

    6

    134

    1

  3. Press any key to return to the Database Integrity Checking menu


Creating a Database

This section tells you how to create a database structure. It is divided into the following topics:

  • Running the Database Creation Utility (MAKEDB)

  • Selecting the Database Number System

  • Adding a Database Table

  • Adding a Table Field

  • Index Link Analysis

  • Index Link Scrutiny

  • Backing up the Database


Creating a New Text File

Using the DP4 Text Editor

  1. The DP4 Text Editor is called DP4ED and can be accessed from the DP4 menu by selecting Other Facilities and then the ColorWrite Text Editor option (DP4ED is only available in the "real" DP4 menu, and not the Start Menu work-alike). The DP4 File Editor (DP4ED) appears:

    The cursor is positioned in the first character of the first column. DP4 displays the name of the current file in the Title Bar. Initially the file is called "Untitled". You can save it under a more suitable name on exit.

    The picture above shows the 4.621 version of DP4ED. In this version the size of the editing window changes when you resize the application. In earlier releases the screeen looks slightly different and the edit window is limited to at most 80*25 characters, and scroll bars appear if the application is resized too small.

  2. Type the text into the Editing area. You do not need to press <Enter> at the end of the line; the editor wraps the text automatically at the wrap column. This is normally column 80, but you can change it if you like.
    If you press <Esc> while you are in the Editing area, the menus become active. To return to the editing screen, press the <Esc> key twice.

    The text editing facilities, for which Keys Help is displayed, are fully described in Text Editing Facilities

    The File menu is accessed by pressing <Esc> or <Alt+F>. It provides these menu options:

    Menu Option Purpose

    New

    Opens an empty file called "Untitled"

    Open

    Allows you to open an existing file. Enter the drive, path and the name of the file. If you only enter the name, DP4ED looks for the file in the current or working directory

    Save

    Saves your edits in the file. If the file is untitled, you are prompted to enter its name. (Name the file as above)

    Save As

    Allows you to save the file under a different name. (Name the file as above)

    Reformat F4

    Wraps the existing text to the new wrap column

    Print F5

    Prints the file at the selected output destinations

    Exit F8

    Exits from DP4ED

    The Options menu is accessed by pressing <Esc+→> or <Alt+O>. It provides these options:

    Menu Option Purpose

    Status line

    Toggles the Status line on and off

    Keys help

    Toggles the help for editing keys on and off

    Colour F7

    Pops up a window that allows you to change the text colour

    Wordwrap column F3

    Changes the number of characters that are allowed before the text is wrapped onto the next line. This is set to 80 characters as a default

  3. Select the Exit, <F8>, option when you are ready to save the file. DP4 prompts you to Save file, Abandon changes or Continue editing

  4. Select the Save file option, type the name of the file and press <Enter> to leave the editor


Creating an Automatic Demonstration or Test with Redirect

Redirect is a memory-resident utility that records keystrokes while a DP4 program is running. The keystrokes are recorded in an ASCII file. Redirect can play back the file to perform the recorded actions automatically. These are the two main functions of Redirect:

  • User demonstrations

    Create an automatic demonstration that shows the features of your application.

  • Test scripts

    Record test scripts for validating an application. This type of validation ensures that changes to an application do not have unexpected results

You can include comments and pauses in the playback. You define the relative pause speed for each pause when recording the demonstration. However, most of the factors affecting pauses are defined within the colour and display preferences of the DP4 System Setup utility, including:

  • The duration of a standard pause. (This is divided by the pause speed, a number from 1 through 9, to determine the actual duration of a pause).

  • The duration of the pause increment. This is the amount by which the pause is increased or reduced, depending on whether the <+> or <−> key is pressed during the playback.

  • The duration of a 'wait'. This allows you to turn a pause into a long wait by pressing the <Enter> key during the playback

These options are fully described in Changing a User's Preferences.

This section is divided into the following topics:

  • Recording Demonstrations

  • Playing Back Demonstrations

  • Amending the Keystrokes in a Demonstration

  • Interpreting the Keystrokes in a Redirect File

  • Problems with Demonstrations


Creating an Export Program

This section describes how to create export programs with Report Writer. An export program can be used to transfer data from an application database table to an ASCII file, or used to generate an Import program automatically.

  1. Start REPSETUP. You can start it from the DP4 main menu like this:

    • Select Import/Export and Reporting and then Report Writer Setup and Utilities, or prior to 4.621 select Other facilities, then select Report Writer facilities, and finally Setup and utilties.

  2. Enter the database name.

  3. On the Edit menu, select the Automatic program generation option.

  4. On the Automatic Report Program Generation screen, enter Export as the program type to generate.

    For export programs, the other fields of importance are:

    • Create programs for all tables

      Disable this option if you want to select the tables containing data to export

    • Page width

      Specify the length, in characters, that you want every line in the export file to be. (Lines may be shorter than this if you choose to remove trailing spaces in the Output Devices screen). 0 is often entered and prints lines up to the last character that is not blank.

    By default, the maximum line length possible is 500 characters. If you have tables that are wider than this, the options are:

    • This facility is only available from 4.523/4.619 onwards. Use the -maxwidth width commmand line option when you start REPSETUP, to increase the limit to a higher value (the maximum allowed is 9999), and specify that your program uses a print device with a suitably high page width. . The System database includes a print device named DP4_Export, which is set to allow a page width of 5000 characters, (you can change this using the Printer charactersitcs option of DFSETUP if required).

    • Reduce the number of fields to be exported by amending the export program after generating it (see Field Processing) in the Report Writer Developer's Manual.

    • Define a printer on your system database with a zero length string for Carriage Return. Then a folded report will end up lines joined together. You will need to use a formula to insert an explicit line feed after the last field (e.g. CHR(13)+CHR(10)).

    • Use multiple linetypes and export the keyfields and a tag on each line. The DP4 Importer is able to import a file in this format.

    • Use the Data Unload and Load facility to transfer the data.

    The Automatic Program Generator automatically generates report programs for exporting data in a standard format from the specified tables.

  5. Once you have created all the export programs you need, press <Esc> to return to the Edit menu.

    You now have a choice of actions:

    • If you want to exit from the Setup & Utilities option, select Exit from the menu bar
    • If you want to change a program's details, such as the export condition or the format, you can edit it. To do so, select the Edit option on the Report Writer menu bar, and select option 1 - Program Editing. Press <Ctrl+P> and select the Report Writer program from the popup list. Changes you might make include:

      Change | Method
      Change the delimiter character. | Select option 1 - Table details and type. Edit the Field delimiter.
      Make the fields variable-width rather than fixed-width. | Select option 6 - Output devices and format. Enable Remove Trailing trailing blanks
      Change the condition to include a record.

      Select option 4 - List/delete condition. Edit the condition.

      Save your changes and select Exit from the menu bar of the REPSETUP screen.


CUSTOMER

Stores the customer's name, address and region code, credit arrangements and balance. The primary key is a unique number that identifies the customer


Cutover year for next century

This allows DP4 to make an intelligent guess about what century a date belongs to when it only has the last two digits of the year, for example '89. If the year is less than the cutover year, the next century is assumed. Otherwise, the current century is assumed. So, for example, when the current century is 19, the cutover 80 and the year '83, the full date is assumed to be 1983. For the same, but a year of '5, 2005 is assumed


Data and Index Files

The data file contains the tables in the database, and all the records stored in them. The data includes the data dictionary, maps and any programs written in QA Build or Report Writer.

The data file contains all the data in a continuous stream. When a record is deleted, a hole the size of a deleted record is left in the file. The database manager dynamically re-uses the space when records are inserted.

You do not need to know the structure of any of the database files, as they are only ever accessed by the database manager. However, in case you are interested, all DP4 database files (including the transaction log and rollback files) contain a stream of records up to 64K, with the convention that the first 2 bytes comprise an integer specifying the length of record. In practice, there is no enforced alignment with the blocks in the physical file. A record 2 bytes in length is normally used as an end-of-file marker. The DP4 utility FDUMPM can be used to inspect DP4 files.

The index file defines the access mechanism for data retrieval. This file contains the keys together with pointers to the records in the data file. The index file is organised in the dynamic B-Tree structure described in B-Trees in the Introduction to DP4.


Data compression

Enable this to compress zeroes and blanks in the database. The database takes up less room on the disk, but there is potentially a performance penalty. This comes not so much from the compression itself, but from the fact that records may need to be moved when they are updated, which can be expensive if there are many secondary indexes, and which also increases data fragmentation.

Except for records in tables where compression is unlikely to save much space, or which will be updated frequently, you are recommended to always enable compression.


Data De-normalisation

Defining entities and their structure in other than third normal form.


Data Denormalisation

There are three common reasons for holding data in other than third normal form:

  • If a parent table has to be frequently accessed to retrieve some field, it may be advantageous to keep a copy of the field with the child record. As an example, you might choose to hold a copy of the material description field on the ORDER_LINE table, although it belongs properly in the MATERIAL table, to save an access to the MATERIAL table during an operation on order lines. The penalty for this is that you must take special steps in your application to maintain the material description field on the ORDER_LINE records. This technique is used quite heavily in the base dictionary tables used by the DBMS Database Manager itself.

  • If you have a table with many fields, of which only a few are of interest to your key application, it may be useful to split the table. DP4 makes this easy to do, as you are allowed multiple tables with the same primary key. As an example, a product table might have 2KBs worth of data, including statistics on usage. Your key POS (Point Of Sale) application requires only the price and short description - perhaps 50 bytes. You could have 2 tables, a PLU (Price Look Up) extract table for use by the POS application, and a full product table, for use elsewhere, both with the product code as primary key. The only penalty for this is that you must ensure these tables are only updated by a purpose-designed application program that always manages both tables in synchronisation

    Another reason for splitting a table is to avoid contention. If two programs concurrently update different fields in the same table, splitting the table prevents contention from locks or checkpoint verification.

  • It may be beneficial to hold data that should be spread over several records in one. For instance, the classical structure for an order is an order header record followed by multiple order line records. You could instead have a compound record with space for, say, header data and data for 5 order lines in one record. The penalty for this is that the construction of the record usually requires some relatively difficult programming in C, and the record cannot be examined by any of the standard utilities such as QA Build or the Report Writer. DP4 use this technique for storing compiled QA Build programs, where efficient access is crucial, and no-one could reasonably want to use the utilities to examine the contents

    A special case of combining data into one is supported by the DP4 occurs facility. For instance, if you have a budget system with 12 monthly budget figures to be held for each department, you have a choice. You could have a department header table and a budget table keyed on department number and month number, or you could just have a department table, with a field 'budget' set up as an array of 12 'occurs' elements. In general, if you can use this technique, it gives faster processing.


Data Dictionary

The set of table and field definitions (including names, types, roles and relationships) that define the database.


Data Dictionary Plan

The DP4 Data Dictionary Planner can be used to print out a plan of the tables on the database:

In this example, lines show the relationship between the tables. DP4 automatically shows the data type and size of each field in a table. Key fields are marked with a number to indicate the key precedence, or an asterisk (*) to indicate a secondary key that indexes another table.


Data Encryption

Encoding data to protect it from unauthorised access.


Data encryption

Enable this to help safeguard data from unauthorised access.


Data File

A DP4 database comprises at least a data file (.DAT) and an index file (.IND). The data file contains all the tables in your database and their records. As of release 4.622 the data file may be compressed (and effectively encrypted against) a separate data compression information file (.DCI).

If a DCI file is used, the data file cannot be read without it.


Data in

Colours used for indicating the input field


Data Independence

This section describes how you can change the database without recompiling the programs that access the database.

If the structure of a table is changed in a way that does not affect the indexing, then the data independence mechanism allows records with the old and new structure to co-exist on the database. The application program 'sees' the old or new structure of the database as appropriate.

This section is divided into the following topics:

  • Use of Generation Numbers

  • Preserving Data Independence

  • Data Type Conversion


Data Independence

A scheme to ensure that the program accessing data does not need to know the physical details of data storage.


Data Normalisation

Data normalisation is a method widely employed for defining entities and their structure in a way that minimises storage space requirements and database maintenance. For example, the following customer order line entity or record could be defined:

Customer order line record
1 Customer number
Name
Order number
Line number
Material Code
Quantity
Description

The 1 indicates that the customer number is the key to the record. The first stage in normalisation is to take out the item that repeats most frequently and treat it as a separate record, but include a key to it in the original record. A customer may place several orders, so the first normal form is represented as follows:

Customer record | Order line record
1 Customer number | 1 Customer number
Name | 2 Order number
 | Line number
 | Material Code
 | Quantity
 | Description

1 and 2 indicate that the order record now has a compound key, that is a key with more than one part, comprising the customer number plus the order number. These partial key dependencies are removed when going from first normal form to second normal form. This is done by examining records having a compound key and checking to see whether each data field in the record relates to the whole key. If a field relates to part of the key only, it is removed with its key to form another record. In our example Line number, Material code, Quantity and Description relate to the Order number but not the Customer number. So they can be extracted into a separate Order line record, as follows:

Customer record | Order record
1 Customer number | 1 Customer number
Name | 2 Order number
Order line record |
1 Order number |
2 Line number |
Material Code |
Quantity |
Description |

The final stage of the analysis, the reduction to third normal form, involves examining each record to see whether any items are mutually dependent. If there are any, they are removed to a separate record, leaving one of the items behind in the original record and using that as the key in the newly extracted record. In the example, Material code and Description are mutually dependent because Material code will always have the same Description. Here is the final definition:

Customer record | Order record
1 Customer number | 1 Customer number
Name | 2 Order number
Order line record | Material record
1 Order number | 1 Material code
2 Line number | Description
Material Code |
Quantity |

A test can now be applied to ensure that the records are in third normal form. The following questions are asked:

  • Given a key value, is there just one possible value for the data?

  • Is the data directly dependent on the key?

If the answer is 'Yes' in both cases, the records are in third normal form.


Data Normalisation

A three-stage, analytical method for defining entities and their structure.


Data out

Ink colour used for displaying field output. The paper is taken from the displayed map


Data present

DP4 enables this automatically if there is data in the table. For new tables this is always set to 'No'


Data Type Conversion

These are the rules for data conversion:

Conversion | Change
U to C | No change
C to U | No change
I, L or N to C or R | The number is converted to a left-justified decimal representation The minus sign (−) and decimal point (.) characters are included if necessary. If the resulting character does not fit, it is truncated
C or U to I, L or N | The character string is read as a number. If there is a minus sign anywhere in the string, it is read as a negative number. The first occurrence of a full stop (.) or comma (,) is interpreted as a decimal point Characters other than these are ignored: ,.-0123456789
D to I, L or N | The date is converted to the number of days since 01.03.1980
I, L or N to D | The number is interpreted as the number of days since 01.03.1980
D to C or U | The date is converted to a number (D to I as above), and then the number to a string (I to C as above)
C to U or D | The character string is first converted to a number (C to I as above), and then the number to a date (I to D as above)
Y to I, L or N | TRUE is converted to 1, and FALSE to 0
I, L or N to Y | 0 is converted to a FALSE value, and any other number to a TRUE value
Y to C or U | The value is converted to a number (Y to I as above), then to a number character string (I to C as above)
C or U to Y | The character string is converted to a number (C to I as above), and the number to a Yes/No value (I to Y as above)

If a field contains a null value, it is converted to the null value of the new data type.


Data type

Select a data type from:

C | Character
D | Date
I | Integer (16 bit)
L | Long integer (32 bit)
M | Mixed case character
N | Number (floating point)
P | Pseudo-numeric (digit only characters)
S | System character (reserved for DP4 use)
T | Time
U | Upper case character
Y | Boolean (Yes/no)


Data Types

The standard data types supported by DP4 are:

Type | Code | Description
Character | C | A string of ASCII characters
Date | D | A field used to store the year, month and day
Integer | I | Standard 2-byte signed integer, with the low-order byte first
Long integer | L | A 4-byte signed integer with the low word first. This is the format used by most compilers which support 32-bit integers
Mixed case | M | A field like the C character type, except that this type is not case sensitive in indexes, and so "SMITH" and "smith" appear next to each other in an index
Number | N | DP4 implements this type according to the language format you choose for your database. For example, for the Microsoft C programming language, the IEEE real number system is used
Pseudo numeric | P | A character field composed only of the characters '0..9' and '.'
Time | T | A field composed of hours, minutes, seconds and hundredths of seconds
Boolean (Yes/No) | Y | A field for which only two values are allowed, for example "Yes" and "No"
Upper case | U | A character field suited for use as a key field. You can change the collate sequence of U type characters. This facility is used for national language support and to indicate the precedence of non-alphabetic characters such as @ and #

You can change the way each field type is entered and displayed on your database. Although the fields are always stored in the same way, the entry and display is user-specific. For example, for the date field, you can specify the order of the day, month and year parts, and whether the month is shown as a name or a number.

For each data type, your program may need to test whether the user has entered a value. You do this by testing for a Null value.


Database

A file of structured data that can be accessed for different purposes by a range of programs.


Database Design

We cannot stress too strongly how important it is to invest sufficient time and resources in the design stage. A poorly designed database is likely to deliver reduced performance and may not be able to do what you want. This section is not an exhaustive treatise on how to design a database; plenty of weighty reference books have already been written on the subject. It provides insights into DP4's implementation of the relational database model that will influence your design decisions.

The following topics are covered:

  • Database Model

  • Entity Modelling
  • Data Normalisation
  • Entity Relationships
  • Table Definition
  • Field Definition
  • Primary Key
  • Relationships Between Tables
  • Referential Integrity
  • Field Roles
  • Data Types
  • Range of Occurrences
  • Data Dictionary Plan
  • Automatic Increment of Key Values
  • Indexes

  • Record Sequence and Key Precedence

  • Design Tools


Database Design Tools

See CASE Tools.


Database Integrity

The correctness of data values on the database. The DBCHECK and DYNACHEK utilities are used to check the database integrity.


DP4 Limits

Operating System and Network software may impose limits of its own.

For example Unix limits the amount of memory a single process can have and the number of files it can open (sometimes to as little as 64). You need to make sure you have configured appropriate limits for the kernel. SRV3 will open one file for every user, as well as all the regular database files. TCPMGR will "fork" another process for every connection.

Database Manager

Maximum database data file size | 4096 MB (up to 4.622). From release 4.622 there are no file size limits other than those imposed by the underlying operating system
Maximum number of tables on a database | 32K
Maximum number of records per table | Limited only by max data file size
Maximum record size | 4K
Maximum field size | 4K-12 bytes
Maximum number of key fields per table | 99
Aggregate key size | 200 bytes
Maximum length of table and field names | 16 charcters
Maximum number of indexes per table | 32767

Maximum Number of Connections

DP4 limits the number of connections that a server can accept (there is one connection per client machine, if multiple programs run on on a client there is only one connection) to 256. Individual implementations of TCP/IP may limit this figure further. A client can connect to 99 servers simultaneously using AUXDISTR, but individual tables can only use 32 of these.

The maximum number of connections supported by the network manager depends on the platform.

For both 16- and 32-bit Windows DP4 limits this value to 127 (or 256 in 4.623). If a Windows client is running two DP4 programs that is only one connection but two user processes. Again this may be further limited by the underlying software layers. (The 16 bit Windows TCPMGR is obsolete. It probably won't accept more than about 6 connections, because of memory constraints)

On UNIX servers the limit is the smaller of 256 and the defined value of FD_SETSIZE. This is defined in the system header files. There is only one connection per client machine.

The DOS network manager can accept up to 128 connections. (The DOS network manager is not available since release 4.520. It certainly won't accept 128 connections if it is reinstated)

Networks

Maximum number of servers | 99
Maximum number of databases open | 48. In 4.623 up to 1024 databases may be opened
Maximum number of DP4 programs per server | 414 - Likely to be limited further by number of connections

Notes

The lists above shows the current limits of the DP4 system.

However, the practical limits may be affected by performance issues.

QAB and User Interface Limits

Prior to release 4.520/4.616 there was a limit on the number of QAB procedures of 32767. Now you can raise this limit to 65533 by making some changes to the base dictionary. See release notes for details.

QAB Programs can access a maximum of 128 database tables per program.

Each QAB procedure can process at most 99 input and output fields.You can have additional fields that are neither input nor displayed.

In 4.5xx DP4 programs can display a maximum of 50 maps on screen at the same time. There is no limit in 4.6xx.

Prior to release 4.624 the maximum size of a compiled QAB program is 64K code+data. In 16 bit versions of DP4 the maximum size is 42000, but this can be increased if you link your own version of PROGRUN. In 4.624 the maximum size of a QAB program is limited only by the maximum size of a characer array in a 32-bit program. The default maximum program size is 4MB, but you can increase this is you link your own version of PROGRUN.

Minimum processor requirements

From release 4.521 the minimum processor requirement for all 16 bit versions of DP4 is an 80386 (previously the English MS-DOS version worked on 80286).

32-bit DP4 will work on any 32-bit x86 style processor. DP4 is tested on machines with Intel Pentium 4 class processors, but there are no known compatibility issues with older processors, or with AMD processors.

64-bit DP4 is currently only available for "X64" style processors. The Itanium processor is not supported.


Database Manager

This appendix describes two quite separate aspects of the Database manager:

  • Updating the Database broadly describes how a DP4 application updates the database using the post, commit and checkpoint mechanism.

  • Data Independence details what changes you can make to the database without recompiling your applications. This feature is known as data independence.


Database Manager

A memory-resident program that manages the database and ensures that data can be accessed concurrently and is protected from corruption.


Database Model

The DP4 DBMS (DataBase Management System) is based on the relational database model. In the DP4 DBMS, the entities are represented as tables. A table is a two-dimensional array, in which the columns define the fields in a record, and the rows represent different records.


Database Re-organisation

Reorganising the information in the database to improve performance and reduce the size of the database. If you make a structural change to a database you will need to reorganise it. The REORGDB utility is used to reorganise the database.


Database Recovery

This is the mechanism by which the database ensures that data can be recovered. The DBRECOV utility is used to recover the database after a system crash, if roll-back logging is enabled.


Database status

The status tells you whether any action is required on the database. The possible messages are shown below:

Message | Action Required
Normal | None
Backup program (DBBACKUP) must be run | Back up the database, using DBBACKUP or DYNABACK. You can perform a dummy backup, if required, with SETSESSN or DYNABACK -SETSESSN.
Index analysis (MAKELINK) required | Run MAKELINK. See Index Analysis.
Index analysis (MAKELINK) followed by data reorganisation | Run MAKELINK followed by REORGDB -DATA or -INDEX See Index Analysis.
Index scrutiny (SETLINK) required | Run SETLINK. See Index Scrutiny.
Data reorganisation (REORGDB) required | Run REORGDB -DATA or -INDEX. See Reorganising the Database.


Database

Leave this field blank to enable the user to log onto the DP4 System Main menu. If you want the user to log onto an application menu, enter the name of the database. Editing a Menu explains how to create an application menu


Database version

This tells you which version of the database you are using.


Date and time format options

These options are as described in Formatting a Field


Decimal point

Enter the character used in place of a decimal point (for example , as in 0,01)


Decimal point

Enter the decimal point character, normally "." in the UK and USA and "," in continental Europe


Default database

This question is skipped if you are logged in because it is set in the user list, as described in Maintaining the User List. If running DFSETUP from the command line, enter the name of the database you use most often. It will be offered as the default when running DP4 utilities from the command line


Default language

0 is normally the default language. The standard maps are language 0. If maps have been set up for your language, enter its number here


Default value

Enter the value to offer as a default when the field is first entered


Default when blank

Disable this option and the next to make the field value blank when the field value is blank or missing from the import line. Enable this option, if you want the field to be assigned its dictionary default value when the field value is blank or missing from the import file


Defining Field Groups

This screen willl appear only when you cleared the checkbox or answered no to the question "All lines of the same type" in the File Description dialog.It is only necessary to specify field groups when separate lines of the import file may contain a differing collections of fields.

  1. On the Importer Program Editing menu, select option 2 - Line processing. A screen appears displaying the field groups available. Initially there are none.

    Click on the buttons in the illustration above for a description of their function

  2. Select the Identification option

  3. Fill in the details on the Field Group Properties menu, for example:

    Click on the options in the illustration for further information

  4. Having defined the tag field properties to your liking, press <Enter> to return to the Field groups available screen. There is no need to define the tag format, unless:

    • You specified a date or time type and must describe the format in which it is held in the import file
    • You specified a number type and it is held in the import file in other than string format

  5. Repeat steps 2, 3 and 4 to define as many field groups as you have different groups of fields

    For each field group, select the Tables option and define the table fields to be imported, as described in the next section.


Defining Import Fields

  1. To define the fields to be imported from the import file, with the appropriate table highlighted, select the Fields option of the Tables imported screen. The list of import fields is displayed. The option Insert new field is highlighted, for example:

    Click on the buttons in the illustration above for details of the four options.

  2. Add a field to the list by moving the highlight to Insert new field and selecting the Processing option.

    You are prompted to select a field name from the list of available fields.

  3. Select the field name and fill in the options on the Processing screen. For example:

    1,"Russell R ","34 Church St. ","Pine

    Click on the relevant areas of the illustration above for details of the options.

  4. Having specified the processing of the field to be imported, press <Enter> to return to the Import fields screen. There is no need to define the field format, unless:

    • You specified a date or time type and must describe the format in which it is held in the import file

    • You specified a number type

  5. To define the format of a field, select the Format option of the Import fields screen. The options displayed depend on the data type of the field. The options for a numeric field are explained below:

    Option Description
    Add value to existing field value?

    Answer 'Yes' if you want DP4 to add the value in the import file to the value on the database

    Number data format

    Answer 'Yes' if the number is in a format other than 'string'. You are prompted to select the format from this list:

    • String format
    • Binary type
    • IEEE floating point
    • Cobol COMP-3
    • Turbo-Pascal
    • Packed BCD
    Number stored with bytes reversed?

    Answer 'Yes' if the format stores the low byte of the integer before the high (such as Intel)

    Override scale factor?

    Answer 'Yes' to enter the new scale factor

    Decimal point character

    Enter a new character such as a comma (,) to change the decimal point from the default period (.)

  6. When you have completed the format definition, press <Enter> to return to the Import fields screen.

  7. Repeat steps 2 through 6 to define all the fields to be imported for the table.


Defining Import Tables

  1. To define the tables to be imported, display the Tables imported screen:

    You access this screen from one of two points in the Importer:

    • From option 2 - Line processing of the Importer Program Editing menu if all the lines are the same in your import file
    • From the Tables option of the Field Groups Available menu if different lines of the import file contain different groups of fields

    Click on the buttons in the illustration above for details of the options

  2. Select the Edit option to display the Import Tables dialog

    Click on the relevant areas of the illustration above for details of the options

  3. Fill in the table details and press <Enter> to return to the Tables imported screen. The table you added is now displayed in the list of tables to be imported

  4. Repeat steps 2 and 3 to define all the tables to be imported for the field group or for the Importer program


Defining the Line Processing

Defining the line types involves specifying the data to be imported. This procedure depends on whether each line in the import file has the same fields:

  • If each line in the import file has the same fields, you need to:

    • Define the tables to be imported. (See Defining Import Tables )
    • Define the fields to be imported. (See Defining Import Fields )

  • If each line in the import file has different fields, you need to:

    • Define the field groups. (See Defining Field Groups )
    • Define the tables and the fields to be imported for the field group as above.

    If you are unsure about what is meant by the term field group, refer to Understanding Field Groups


Delay between records

0 by default. Enter the delay you require, in hundredths of a second, between calls to the database manager. Use this option to reduce the impact of Dynachek on other applications that are accessing the database


Delete

Use this option to delete the highlighted field group


Delete

Use this option to delete the highlighted field from the list of fields to be imported


Delete

Use this option to delete the highlighted table from the list of tables to be imported


Deleting a Menu Page

  1. Follow the steps in Selecting a Menu Page earlier to select the menu page

  2. On the Menu Editing menu, select option 2 - Delete page

  3. Confirm the deletion. The menu page is deleted, and you are returned to the Menu Editing menu


Deleting a Record

  1. Locate the record you wish to delete. You can use the Scan or Keys options as described in Locating and Viewing Records

  2. Select the Delete option

  3. Answer 'Yes' to confirm that the record is to be deleted


Deleting a User

  1. Follow the instructions in Modifying the User List to modify the user list and display the User Set up dialog box

  2. Press <Ctrl+P> and select a user from the popup list. The Amend/Delete/Password menu is displayed

  3. Select option 2 - Delete user

  4. Answer 'Yes' to the prompt to confirm the deletion and press <Esc> several times to return to the DP4 System menu


Deleting Application Data

The database reorganisation utility also allows you to delete application data from the database. This option does not delete mapsets, menus or parameter sets.

  1. On the DP4 System menu, select the Run program option, type the command:
    REORGDB -DELETE
    and press <Enter> to execute it

  2. Select the database

  3. Select option 7 - Delete application data from the Database Reorganisation Facilities menu:

    You are asked whether you want to delete the data from all tables:

    Answering 'No' allows you to select particular tables:

    You are asked whether you want all the selected tables to be marked as remote. This question is only applicable when your system has a remote database manager:

    Answering 'Yes' forces database applications to look on remote machines for the data you are going to delete

    You must now confirm whether you want to delete the data after all:

    DP4 reorganises the database omitting data from the selected tables and displays a message for each stage of the reorganisation. There are two main stages:

    • Rebuilding the data file, which includes copying dictionary tables, copying other tables and indexing other tables. The new data file size is displayed at the end of this stage
    • Optimising the index file, during which the number of index nodes to process and the number of nodes processed so far are displayed

  4. When the reorganisation utility terminates, press <Enter> to return to the DP4 System menu. Back up the database, using one of the methods described earlier

Crash Recovery

Your options for recovering from a crash while this option is running are the same as when you use option 3, Rebuild Data and Index files.


Deleting Table Data

A different utility is needed to delete all data from selected tables. To do this, run the Database Reorganisation utility (REORGDB) with the -DELETE command tail, as described in Deleting Application Data .


Demo pause increment

In demonstrations, each press of the <+> key increases, or <−> key decreases, the pause time by this amount, in tenths of seconds


Demo pause time

Specify the duration, in tenths of seconds, for pauses in demonstrations created with the Redirect utility. For example, 120 gives a 12 second pause


Demo wait time

Pressing <Enter> pauses the demonstration for this duration, in tenths of seconds


Design Tools

Designing a database structure for a relational database follows the same pattern, regardless of the application development environment. The systems analyst works out a way of representing a real world problem as a set of entities.

Customer

Material

Order

Order Line

Each entity can have a one-to-one, one-to-many or many-to-one relationship with another entity. Entity pairs with many-to-many relationships are not allowed; they must be eliminated by the introduction of a new intermediary entity offering many-to-one relationship with one of the original entities and a one-to-many relationship with the other. In this way each entity may remain in third normal form. These entities can then be represented as tables on the database.

A database design can be produced using a range of methodologies including Jackson and SSADM. You can produce a design by hand or using a Computer Aided Software Engineering (CASE) tool. The design should include the following characteristics:

  • A description of the data elements in a system.

  • An indication of the relationships between the data elements

Typical modelling techniques for producing a DP4 database design are as follows:

  • Function hierarchy modelling

  • Function dynamics modelling

  • Entity relationship diagram


Developing a Multi-Language Application

DP4 national language support ensures that you can have one application database and one set of application programs that run in several languages.

The approach recommended assumes that you have already developed and tested your application in one language, typically language 0, and you are ready to add additional languages.

This subject is divided into the following topics:

  • Summary of the Translation Process

  • Translating the System Database

  • Adding a Language

  • Selecting the User Language

  • Changing the System Language


Device

An object attached to the workstation that inputs information to or outputs information from the application.


Device Driver Interface (DDI)

A set of instructions for interfacing to a device.


Device Independence

A scheme to ensure that changes to the devices on a system do not affect the application.


Device Integration Pack (DIP)

The DP4 product that provides the application program interface for device handling in DP4. The DIP is a modifiable interface that is used to create Device Driver Interfaces (DDIs).


Disable flashing

For DOS and OS\2, you can disable flashing colours and make light paper colours available instead


Disable option numbers

Enable this to suppress the display, and disable the use of, option numbers in menus


Disallow 'Press any key'

See Key to clear messages


Display status line

Disable this option to suppress the display of the status line in DP4 editors


Displaying Records Conditionally

To search for records that meet a user-defined condition:

  1. Select the required table with the Table option and display any record. If you are unsure about the names of the fields you wish to use in your search condition, use the View option to display field names in place of field headings

  2. Select the Cond option. Enter the condition you require in the Search Condition Entry window that is displayed. For example, if the CUSTOMER table is currently selected, the following condition allows you to scan customers whose names begin with "S":
    NAME [1]="S"

    You are restricted to fields in the current table. See the QAB or Report Writer Developers Manual for how to construct formulas.

    Press <Tab> to save the condition and continue

  3. To start the search, select the Scan option and use the cursor to browse the records in the direction you wish to search. Records which do not meet the condition are quickly scrolled through until one that does is displayed

If the search takes too long or you think you made a mistake in the condition, you can interrupt the search by pressing <Esc>. If you are running on Windows the search will probably be quite a lot quicker if you minimise the application for a while.


Distributed Database

A distributed database is one that is held on two or more machines on the network. This allows you, for example, to put branch tables on the workstation and master tables on the Head Office server.


Do you want a roll-back log

Enable this option to turn on roll-back recovery to ensure a fast restart in the event of a system failure. If roll-back logging is disabled the only means of recovering the database is to restore it from a backup.


Do you want a transaction log

Enable this option to turn on transaction logging for use in roll-forward recovery. Disabling transaction logging is not recommended because all transactions since the last backup will be lost in the event of an unrecoverable database corruption.


DP4 Menu

A menu program named DP4 is provided to run the menus set up for each DP4 user.


DP4 Relational Database Management System (RDBMS)

The DP4 Relational Database Management System is a set of tools for implementing and maintaining a relational database.


Dynamic Backup

DYNABACK has two completely different modes of operation. The first mode of operation creates a backup in the designated backup directory in the format used by DBRESTOR. The second mode of operation creates a copy of the DP4 database files, in exactly the exact format as regular database files, but with different file extensions. The second mode of operation has important benefits, but does require that you have space for at least two and preferably three copies of your database files on the machine at the same time.

DYNABACK does not directly support back up to tape. To back up to tape without interruption you can use the DYNABACK -COPY to create a copy of the database and then use DBBACKUP -TAPE, or a batch/script to back up that copy to tape.

Backup in DBRESTOR format

  1. Make sure you have a supply of formatted diskettes (or use the −format command tail).

  2. On the DP4 System menu, select the Backup/Recovery option. The Database Backup, Recovery and Check menu is displayed.

  3. Select the DynaBack option.

  4. Select the database, specify whether you want to verify the backup, add any comment to be displayed when restoring. Then, insert, remove and label the disks as requested. (The process is more or less to that for DBBACKUP, and is explained in greater detail in Backing up to Diskette.)

    If your system had a log file, it will have been deleted and the log session number incremented. The database session number has been incremented. A new log is created by carrying out a transaction on the database.

Backup in Native DP4 Database Format

  1. On the DP4 System menu, select the Run Program option and enter the following command: DYNABACK −COPY. You may use the −DB database_name command tail to specify the name of the database.

  2. If you did not enter the name from the DP4 menu enter the name of the database to be backed up. The backup process will start, and is completely automatic from now on. The following operations are performed:

    1. DYNABACK copies the database to a temporary database name ($$$0AC or something similar). This copy is written in the normal database directories (hence the need for space for at least one additional copy of the database). The copy is done using special internal functions from the database manager. This allows the database to be backed up while it is still open. The mechanism for doing this depends on DP4 rollback recovery. Please note that while DYNABACK is backing up the database the rollback file cannot be restarted, and it can therefore grow to quite a large size. It is advisable to run DYNABACK at periods of low activity.

    2. When the backup has completed DYNABACK immediately runs DBRECOV against the backed up database to apply the backed up rollback and transaction logs. The copy of the database is then identical to the original database immediately prior to the point the backup completed.

    3. Next DYNABACK runs DBCHECK against this database:

      • If no corruptions are found the backup is good. At this point the backup files are renamed to DBNAME.DB1 (renamed .dat file) and DBNAME.IB1 (renamed .ind file) or to DBNAME.DB2 and DBNAME.IB2. Simililarly, and if used the backed up .DCI and .PTR files are renamed to DBNAME.CBn and DBNAME.PBn. A marker is written to the original database to indicate that a successful backup has completed, and to indicate whether the B1 or B2 name has been used. The names are used alternately, because it is not safe to delete the previous backup until the current backup has completed, verified AND the database has been notified of this. Once this process is completed the previous backup is deleted if it is still present (if it was you must have had space for three copies of the database). If space permits it is advantageous to keep the current backup online, as it will permit a faster recovery if the database is lost (for example if DBRECOV fails following a system crash).

      • If the DBCHECK process discovers a problem on the backed up database the bad backup is renamed instead to .IBB and .DBB. (It is not deleted automatically, as you may wish to run DBCHECK or other utilities interactively to discover more about the problem). However, as the backup is corrupt, it is very likely that there is a problem with the original database. Therefore a special flag is set in the original database. When applications are run against the database they will display a warning message indicating that the database has a probable corruption, and that DBCHECK should be run. This message will continue to be displayed by each new application until such time as DBCHECK is run, and passes, the database is successfully reorganised with REORGDB, or a backup copy of the database is restored. Normal operation is not disabled, as sometimes it is preferable (from a user point of view) to risk continue operating with a database that is corrupt, rather than disrupting the system by immediately restoring a backup. This flag also gets set if the database manager encounters a problem which causes a system error 87 (database corruption).

After a successful backup the backed up database files should be copied to tape, or CD ROM, or a hard drive on another machine. As the files are are in the regular DP4 database format the restore process is a single step operation - there is no need to run DBRESTOR afterwards, except to apply a log file. Alternatively a CD ROM copy of the database can be accessed in the normal way by DP4 programs, which may be useful for archive purposes.

Other options (with -COPY option)

If your database is very large it may take too long or have an unacceptable impact on performance to run DBCHECK against the backup, though it is of course the backup and not the live database that is being checked. It is therefore possible to disable the automatic DBCHECK that takes place after a -COPY backup. You are recommended not to do this unless absolutely necessary. We have recently been able to run DBCHECK against a 3 Gigabyte database in only a few hours, (on a PC with only a 200MHz Pentium MMX) so do not assume that you cannot run DBCHECK. If you do use the option to disable DBCHECK then you should transfer the backed up database to another computer where you can run DBCHECK without impacting performance of the system. If you do not run DBCHECK at least once during the backup cycle you risk losing your database. It is best to run it after every backup, otherwise you may waste time restoring a backup that itself turns out to have a corruption.

If despite the above you want to disable automatic DBCHECK as part of the backup process then specify −NOVERIFY on the command line to DYNABACK.

Additional notes on -COPY option

  • The backup process does not transfer database data across the network, so it may be run on a client. Note however that the DBCHECK phase does transfer data across the network, so it is best to run from the server unless the -NOVERIFY option is used. At the time of writing release 4.622 does not support running DBCHECK over a network, so in this case DYNABACK must be run with -NOVERIFY if run across the network.

  • The backup process only prevents the rollback file from being flushed while the database is actually backing up. Therefore running DBCHECK as part of the backup will not adversely affect the size of the rollback file.

  • From release 4.622 you can use DYNABACK to copy an open database to another name, rather than to backup the database, by specifying the command tail −NEWDB newname. When you use this option the original database will continue to use its existing log file rather than start a new session.


Dynamic Backup

The process of backing up the database whilst it is being accessed by other programs.


Edit

Use this option, first of all, to set up details of the table to be imported


Editing a Menu

This section tells you how to set up a menu that is displayed when the user logs onto the DP4 system. The DP4 Menu editing utility lets you set up pop-up menus that can have several pages.

If you wish to create pull-down menus, refer to the DP4 Map Editor User Guide.

There are two types of menu that you can provide with your system:

  • Application menu

    An application menu provides access to the programs you have developed for the application. This menu is stored on the application database.

    You can provide access to any DP4 utilities that apply to the application. You can also include standard menu commands such as the LOCKUP facility that locks up the system until the user returns.

  • System menu

    A system menu gives the system manager access to the database management utilities. This menu is stored on the system database and is independent of the application programs.

    The DP4 System menu provides access to the utilities, but it is designed for developers rather than end users. You can copy and modify the system menu to create a menu with all the facilities required by the system manager on the customer site.

This section is divided into the following topics:

  • Selecting a Menu Page

  • Editing a Menu Page


Editing a Menu Page

If you wish to edit an existing menu, you may wish follow the instructions in Extracting a Menu Page to print out the menu first. Study the menu options and structure before you make any changes.

Follow the procedure Selecting a Menu Page to select a menu to edit.

The Map Editor's editing screen is displayed. This will look rather different depending on whether you are using a 4.5xx or 4.6xx release of DP4.

Editing 4.6xx DP4 Menu pages is described here.


Editing a Record

  1. Locate the record you wish to edit. You can use the Scan or Keys options as described in Locating and Viewing Records

  2. Select the Edit option. The cursor moves to the first non-primary key field

  3. Use the cursor keys to move between the fields

  4. Edit the fields. <Ctrl+P> displays the valid range for numbers, dates, times and Yes/no fields. <Insert> and <Del> reset the value to the default for the field (as set up with the Database Creation/changes utility)

  5. Press <Enter> (or <Tab> depending on your choice of keying convention) to confirm the changes.


Editing an Existing Table Plan

  1. On the DP4 System Main menu, select the Run program option

  2. Use this command to read in a table plan that is stored in layout form:
    TABLEPLN -LAYOUT <file_name>
    Use this command to read in a table plan that is stored in expanded (full scale) form:
    TABLEPLN -READ <file_name>

  3. DP4 reads in the table plan for further editing


File Description

When using the Importer, we recommend that the file from which you are going to import data is accessible in the current/working directory or the print file directory. If it is, the importer program allows you to scan through lines of the file to help you define the import field groups and their tables and fields.

The File Description option opens the IMPSETUP Main Parameter Settings dialog, used to describe overall properties of your program and the file to be imported:

For further detailes of the options, click on the appropriate part of the illustration above. The checkbox asking you whether all lines are of the same type is of particular importance, as it determines whether or not your program will use Field Groups.

Having defined the main parameter settings, press the OK button (or <Enter>) to close the dialog. You are returned to the Importer Program Editing menu. You must now define the data tables and fields to be imported by selecting option 2 - Line processing, as described in Defining the Line Processing


Editing Area

On entry to the DP4 File Editor, the cursor is positioned in the editing area


Editing Maps

Maps are used to define how information is displayed and printed by your application. They are set up and edited using the Map Editor application (MAPEDIT). Maps are numbered within mapsets which have names. The mapsets are stored on the database, independently of the application. This means that you can change many of the characteristics of the map without recompiling your applications.

The Map Editor application is the only DP4 application which is very different between release 4.5xx and 4.6xx. The 4.6xx Map Editor is described in the 4.600 Map Editor User Guide.


Editing Text and Maps

DP4 provides four editors. This section describes three of them:

  • Text Editor

    The DP4 Text Editor is not intended to be a fully featured word processor; it has no facilities for searching and replacing text or spell checking. The DP4 Text Editor is useful for examining files produced by DP4, displaying boxes and interpreting colours using the DP4 conventions. It may be useful when you are using DP4 in an unfamiliar environment, perhaps visiting a client's site, and it is the only editor you have or the only one you know how to use. Similar editing facilities are built into the DP4 Table Plan program, and into QAB program documentation, so you will probably become want to become familiar with it at some point. There is also a similar file viewing program called VIEW, which is especially useful for examining file output from other DP4 utilities.

  • Map Editor

    The DP4 Map Editor is invoked automatically at various points in QA Build or can be invoked explicitly like any other DP4 utility. It allows you to create screen and print formats rapidly. These are stored locally on the database in objects, called maps, which can be accessed by any DP4 application. The Map Editor for version 4.5xx is completely different from the one in 4.6xx.

  • Help Editor

    The DP4 Help Editor is used to create windows of help information for every point of data entry within applications. All the help messages are invoked by a single keystroke or they can be displayed all the time. This is particularly useful for training new staff and introducing new products. The Help Editor is a cut down version of the Map Editor. It can be used to create help messages while the program is actually running. Run time help editing has always been available in 4.5xx releases, but is only available in 4.6xx releases from release 4.621. The AUTHOR program can be used to configure whether or not this facility is enabled. If it is enabled the help editor is usually invoked by pressing <Ctrl+O>. Depending on your userdata.sys settings you may also be able to invoke it with F9.

    Refer to Editing a Menu Page for details on the Menu Editor.


Emphasis

Colours used for emphasis text


EMPLOYEE

Stores details of Acme employees who manage customer sales regions and customer accounts, including the employee's name, gender, company grade and date of birth. The primary key is a unique number that identifies the employee


Entering, Viewing and Deleting Data

This section tells you how to enter and view data on the database using the DP4 Data Entry and Enquiry utility, BROWSER. It is divided into the following topics:

  • Starting the Data Entry and Enquiry Utility

  • Locating and Viewing Records

  • Displaying Records Conditionally

  • Changing the Record Search Index

  • Editing a Record

  • Adding a New Record

  • Deleting a Record

  • Deleting Table Data

  • Outputting a Record


Entity

A significant business object with properties that are modelled, stored and manipulated in the database system.


Entity Modelling

To define a database, you begin by designing a simplified model of the real world, in which entities represent logical collections of values. For example, you may group the set of details that describe a customer into one entity and the set of details that describe a material into another. The process of designing a database is known as entity modelling. In a sales order system, entities are required to represent customers, orders and materials. At the design stage, you identify the information that is to be stored.

For example, the customer information normally includes the name and address:

Jones Builders, 121 Main Road, Cambridge

A first approximation of the entities in a sales order system is shown here:

CUSTOMER

Name
Address
Credit Limit

MATERIAL

Material Code
Quantity in Stock
Price

ORDER HEADER

Order Number
Customer Name
Date

ORDER LINE

Order Number
Material Code
Quantity


Entity Modelling

The process of identifying objects in the real world that are to be represented in the database.


Entity relationship diagram

Using the Function Hierarchy and Function Dynamics models, you determine the items of data that you intend to process in your application. To start with, the groups of data items that comprise entities are identified.

For example, a name, address and balance of account are all items of data that relate to a customer. These data items can be grouped into an entity called CUSTOMER. The example Sales Order system has the entities CUSTOMER, MATERIAL, ORDER_HEADER and ORDER_LINE.

You need to show the relationships between identities on the diagram, for example:

  • A customer places many orders.

  • An order consists of many order lines.

  • A material is specified in many order lines

The relationships between the tables in the Sales Order database are shown in the next diagram:

In our convention, the split lines or 'crows' feet' at the end of the lines on the diagram indicate the one-to-many relationships between the entities.


Entity Relationship Diagram

The Entity Relationship Diagram shows boxes representing entities, linked by lines that show the relationships (such as: one-to-many, many-to-one) between the entities.


Entity Relationships

Two of the relationships between the entities in the example sales order system are:

  • Customers place orders.
  • Materials are specified in orders.

In order to show the relationships between the entities, an entity relationship diagram is used. This shows the one-to-many relationships between the entities in a system. The design represents the relationships between the entities in third normal form.

In a sales order system, there are three main entities: CUSTOMER, ORDER and MATERIAL. However, as illustrated previously, an ORDER entity would not be in third normal form since an order for a single customer can include many materials. An order can therefore be separated into two parts, ORDER HEADER and ORDER LINE. The ORDER HEADER, in addition to including the customer number, could include the date the order was placed and the total value of the order. The ORDER LINE identifies each of the materials ordered. There is a one-to-many relationship between the ORDER HEADER and the ORDER LINE entities.

Once your entities are represented as tables in a DP4 database, the relationships between the tables are known as parent-child relationships. For example, in the diagram shown above, the CUSTOMER table is the parent of the ORDER HEADER table.


Errors

Colours used to display error messages


Exit Code

User-supplied, C-code functions for handling devices, connected to external systems, and message translation that are incorporated into the DP4 runtime system.


Exit

Returns you to the previous menu or exits from Dynachek


Exit <F8>

Exits from the Table Planner


Exit

Use this option to return to the Importer Program Editing menu


Exit

Use this option to return to the Tables imported screen


Exit

Use this option to return to the previous menu


Expand <F4>

Expands a layout plan to full scale


Exporting Data to an ASCII File

This section describes how to export data to an ASCII file by running an export program created in the Report Writer.

  1. On the Report Writer Facilities menu, select the Run programs option.

  2. Enter the database name.

  3. Select the export program you wish to run. The output destination dialog is displayed.

  4. Fill in the dialog, specifying the name of the export file. This is normally EXP<NNNNN>.PRN, where <nnnnn> is the internal reference number of the table.

    The export file is created in the 'Print files' directory, as specified in the location of files for your DP4 setup.

    The data on the tables is written to the export file, and you are returned to the Report Writer Facilities menu.

    Repeat the above steps until all the required data is exported.

If you want to keep the data in one file, specify the same name for the export file and append the data. You will, however, need to include a field group tag to identify the different groups of fields present in the export file. (See Understanding Field Groups for more information).


Exporting Data with Report Writer

The Report Writer Developers Manual tells you how to use Report Writer. This section tells you about three parts of Report Writer that are relevant to exporting data from the database.

  • Creating an export program

    You use the automatic generation facility to create an export program for exporting data from one or more tables on the database.

  • Exporting data to an ASCII file

    You run the Report Writer data processing program to read the program and produce an export file.

  • Transferring Report Writer programs between databases

    If you intend to distribute the export program (for example for use by the Importer automatic generation facility on the target database), you will need to extract the export program to a file

    If you plan to export data from several tables and you want to keep the data in one file, you can specify the same name for the export file and append the data. If you do so, you will need to include a field group tag to identify the different groups of fields present in the export file. (See Understanding Field Groups for more information).

DP4 has another facility for automatically transferring data between databases, which is suitable for use when the databases have the same or very similar structures. See Transferring Data with the Load and Unload Facility.


Extract File

Report Writer programs, Importer parameter sets, mapsets, table data, and other database information can be exported to ASCII format files. An extract file can be posted to another DP4 database.


Extracting a Database Menu

  1. Start up the Menu Editing utility and enter the name of the database containing the menu. The Menu Editing Main menu is displayed.

  2. Select option 4 - Print menu. The Menu Printing dialog box is displayed. Its options are as follows:

    Option Description
    Print in Mappost format?

    Enable this option to extract the menus in a format that can be posted to another database. Disable it to extract the menus in a format that can be used for documentation.

    Print help messages

    Enable this option to include any help messages you have added to the menu options.

    Print user details

    Enable this option if you are printing the system menu, and you also wish to print out the user list.

    Page cross reference list

    Enable this option if you are printing out the details of a menu with many pages and you want to know where each page is called.

  3. You are prompted to select the output destination. Include the name of the extract file.

    The menus are extracted to the specified file in the Print files directory, and you are returned to the Menu Editing Main menu.


Extracting a Menu Page

  1. Follow the steps in Selecting a Menu Page to select the menu page. The Menu Editing menu is displayed

  2. Select option 4 - Print page. The Menu Printing dialog box is displayed. Its options are as follows:

    Option Description
    Print in Mappost format

    Answer 'Yes' to extract the menus in a form that can be posted to another database. Answer 'No' to extract the menus in a format that can be used for documentation

    Print help messages

    Answer 'Yes' to include any help messages you have added to the menu options

    Print user details
    Print page cross reference list

    These two questions are not available for page printing, but they are available if you print the database menu as described in Extracting a Database Menu

  3. You are prompted to select the output destination. Include the name of the extract file

The menu page is extracted to the specified file in the Print files directory and you are returned to the Menu Editing main menu


Extracting Maps for Translation

In 4.6xx you use the Map Utilities (MAPMAKE) to print out a copy of the maps to be translated. In 4.621 MAPMAKE is accessible in the Screen and Form Utilities menu as Map Utilities.

  1. When you have started MAPMAKE specify the database which contains the mapsets that you want to have translated.

  2. If the mapset already has maps defined for more than one language, you may select which of these languages you want to use as the base for the translation by setting the language number to the required value. This can be done via the Select Language option on the initial MAPMAKE menu.

  3. Select the maps to be extracted:

    • If you want to extract data for all mapsets in the database, choose the Print All Maps option.
    • If you wish to extract data for only one mapset, choose the Select Mapset option and then choose the Print Mapset option.
    • If you wish to extract data for only one map, choose the Select Mapset option and then choose the Select Map option and then choose the Print Map option.

  4. In each case, the Map Printing dialog is didplayed. In the Print Format field, choose Translator Format.
    Enable the "Include Help Messages" option if you also want to translate the help messages.

  5. The standard DP4 Print dialog appears. Select output to file and enter a suitable name for the file to contain the extracted maps.

The process of extracting maps for translation in 4.5xx is almost identical. However, instead of using MAPMAKE you use the 4.5xx release of MAPEDIT.


Extracting the Database Structure to a File

Extracting the database structure to an ASCII file is the first stage in transferring the database structure to a database on a remote system or merging databases. You can include instructions in the extract file to delete or retain tables on the target database that are not contained on the source:

  • If you are merging two databases you should elect to retain such tables.

  • If you wish to replace the structure of the target with that of the source, you should elect to delete such tables. (If the source and target databases are on the same system, you can do this more quickly with the Data Structure and Data Transfer utility - details are given in Transferring the Database Structure.)

  1. On the DP4 System menu, select the Database Management option.

  2. On the Database Structure Management menu, select the Dictionary extract post option

  3. Enter the name of the source database. The Data Dictionary Extract and Post menu appears:

  4. On the Data Dictionary Extract and Post menu, select option 1 - Extract database structure. The COPYDB Extract Options dialog appears:

  5. Answer 'Yes' to the first question if you wish to extract all the tables to the file. Answer 'No' if you wish to specify which tables are to be extracted
    Answer 'No' to the second question if you wish to keep the existing tables on the target database. Answer 'Yes' if you wish to delete the tables on the target database that are not specified in the extract file

  6. If you specified 'No' to the first question to select the tables to extract, a popup list of tables appears. Use the cursor keys to move around the list of tables. Depending on your configuration you can select/deselect a table either with Enter or the space bar. When you have finished selecting tables, press either Tab or Enter.

    If you select a partial export of tables, the index information is only exported if both the child and parent table are in the selected list of tables. This means that if you used SETLINK to set up the index information for the links between a new table and the existing tables, the index settings will not be transferred if you only export information for the new table. As of 29th May 2008 the 4.623 version of COPYDB and MAKEDB accepts the command line option "−either". In this case index information is included in the export if either the child or parent table is included in the list of tables. You are recommended to use this option, as it is much more likely to do what you really want.

  7. DP4 displays the Output Devices dialog box. Answer 'Yes' to the question 'File?'. DP4 supplies the database name and the extension .PRN as the default name for the export file.

    DP4 creates an export file in the DP4 'Print files' directory. The Data Structure and Data Transfer utility terminates, and you are returned to the DP4 System Main menu.

The format of the export file is described in Understanding the Export File .


Fast 'Exit' in menus

We recommend that you enable this option under Windows to make OK buttons behave as you would expect when they are clicked with the mouse. Under other environments, you should disable this option


Field

A single item of data in a record.


Field Definition

For each field in the table, the type and size of the field is selected. For the fields in the MATERIAL table, these are:

P8 | Up to 8 pseudo-numeric characters can be entered for the material code key. This field type is suitable for storing a bar code
C20 | Up to 20 ASCII characters can be entered for the material description, in upper and lower case
N8 | Up to 8 digits can be entered for the price field. This includes the decimal point
I5 | Up to 5 digits of an integer, including the sign, can be entered for the quantity in stock (a number between -9999 and 30000)


Field delimiter

Enter the character used to separate the import fields in the import file. This is normally a comma (,)


Field lexicon

Enable this option to get an alphabetical listing of all the fields on the database. For example:

Field name | In table | Seq | Typ | Len | Occ Prc S/f Low/high/def | R/W
ADDRESS | CUSTOMER | 15 | C | 30 | 3 | 0
 | SUPPLIER | 15 |  |  |  | 0
 | COMPANY | 15 |  |  |  |


Field name

Enter up to 16 characters for the field name


Field Role

A role that is applied to distinguish a field from another field of the same type.


Field Roles

You can specify a role for a field in a table where you wish to store the same type of information as an existing field. For example, on the MATERIAL table, we want to ensure that a material is reordered when there is a specific number left in stock. This means adding another field:

QUANTITY_IN_STOCK I5

REORDER_LEVEL I5

In both cases, the type of information is the same. A better way of identifying the fields is to use the same field name with different roles:

QUANTITY (IN_STOCK) I5

QUANTITY (REORDER_LEVEL) I5

Both fields are QUANTITY fields, and so share the same size and type checking, and the same upper and lower limits.

You can use the role facility to specify two links to a parent table. For example, the MATERIAL table includes two links to the SUPPLIER table:

SUPPLIER_NUMBER (PRINCIPAL)

SUPPLIER_NUMBER (ALTERNATIVE)

In this example, if the principal supplier cannot supply a material, the alternative supplier record is retrieved.


Fields

Use this option to set up details of the fields to be imported for the highlighted table


File to import

Enter the name of the file to import. This allows you to refer to the import file when defining the data to be imported. If the import program was created automatically from an export program, this name has been entered for you. To locate the file, DP4 searches the current or working directory and then the print file directory


Fixed length fields?

Answer 'No' if the fields are delimited by a character


Focus Rectangle

A dotted rectangular line that appears on map fields selected under Windows.


Force option

Enable this option to select the 'Initial option' automatically when the 'Initial page' is displayed


Format/Validation

Use this option to specify the format of the field in the import file. You may need to do this for numbers, dates and times


Formatting a Field

The format flags determine how an individual field is formatted. If you don't specify a format for a field it will display with a default format based on user preferences. These format flags are used directly in programs such as BROWSER and AUDITOR that display database information without using maps. In most cases a field is displayed using a field in a map. DP4 utilities that create maps should copy the database field format into the map field, but pre 4.621 versions of the 4.6xx MAPEDIT program do not.

  1. On the Field Editing menu, select option 2 - Format flags.

  2. Modify the format flags using the dialog that appears.For a Y-type (boolean) field, you can modify the 'yes' and 'no' words used for the field. Select the field on the field list and select option 3 - Yes/no words.

    The format flag questions vary depending on the data type of the field. They are explained in the following table:

    Option | Data Type | Description
    Format | Numbers | DP4 provides a default format for each data type. To override the default, select one of the formats (right justified, left justified, asterisk-padded, zero padded)
    Cash till input | Numbers | Select this option to specify that numbers are entered as on cash registers. For example, 1.00 is entered as 100 and 0.01 is entered as 1
    Separate thousands | Numbers | Set this option to 'Yes' to display thousands with commas between groups of 3 digits. For example, 1000 would appear as 1,000
    Blank if zero | Numbers | Set this option to suppress the display of a 0 value
    Blank leading zeroes | Numbers | Answer 'Yes' to suppress the display of a leading zero. For example, .12 instead of the default 0.12
    Trailing sign | Numbers | Answer 'Yes' to place the minus sign to the right of the number. For example, 10- instead of the default -10
    In parentheses | Numbers | Answer 'Yes' to put negative values in parentheses. For example, (0.37) instead of the default -0.37
    Overtype mode | Char | Answer 'Yes' for text entry in overtype mode. By default this option is set to 'No', to clear the existing text as soon as the user presses a key (unless the user presses a cursor key first)
    Start with capital | Char | Enable this option to convert the first letter of the field to upper case automatically. This is convenient when typing in names
    Show seconds
    Show hundredths     		Time Set these options to determine how a time field is displayed
    Year in full | Date | Enable this flag to output the year in full. For example, '1995' instead of '95'
    Month name | Date | Enable this flag to output the month name. For example, 'Aug' instead of '08'
    Month first | Date | Enable this flag to output the month first. For example, 'Apr 27 1995'
    Year first | Date | Enable this flag to output the year first. For example, '2000 Dec 25'
    Remove separator | Date, Time | Enable this flag to force dates to be input, printed or displayed without any separator characters. For example, '25Dec94' or '0912'
    Force this format | Date, Time | Only applicable when no format flags, such as 'Year in full', 'Show seconds' and so on are enabled. If you enable this option, dates are displayed as <dd> <mm> <yy> and times as <HH>:<MM>
    Function keys
    Page up/Ins/Del
    Auto enter/Forbid null     		All These options are not used in MAKEDB. They are shown because the 4.5xx MAPEDIT program uses the same dialogs to set the format of map fields and the flags are valid there.
    Unit name | Numbers | Select a Unit symbol from the unit table (described in Adding Numerical Units ). The unit is displayed within the existing field length, before and/or after the number. You should, therefore, increase the field length to accommodate the symbols. Specifying a unit here is more flexible than including the symbol in maps, because it allows you to change unit definitions in one place without editing your maps. It also allows leading symbols to appear immediately before numbers, for example "£100", and trailing symbols to appear immediately after numbers, for example "100$"
    Check box field | Y | Select this option if you want the field to take its input from a check box. You can toggle the check box with the <Space> key or by clicking the mouse
    Yes/no words | Y | Select this option to override the Yes/no words that are used to respond to the boolean field. For example, 'Pos' and 'Neg' rather than 'Yes' and 'No'

  3. Complete the changes and press <Esc> several times to return to the DP4 System Main menu


Full scale

In full scale mode, table planner shows the tables on the screen exactly as they appear when they are printed. Use this option for producing a table plan containing a few tables


Function Dynamics Modeller

A CASE tool for producing a model that represents the processes of a business.


Function dynamics modelling

A function dynamics model defines the events, functions, process breaks and results in the system, for example:


Function Hierarchy Modeller

A CASE tool for producing a tree structure that defines the function of a system.


Function hierarchy modelling

This technique involves defining the highest level activity of the business function you are modelling. For example, 'Process Sales Orders' is the highest level function of a Sales Order system:

Process
Sales Orders

The function is then decomposed into the next highest level of functions, like this:

You continue decomposing the functions until you have completely defined all the processes of the business, for example:


Function

Colours used for function key area maps


Generation Number

DP4 uses generation numbers to keep track of modifications made to the application database, records on the database, application programs and Report Writer programs. For example, if you make structural changes to the database, its generation number is incremented. Each time a program accesses the database, the generation number of the application program is compared with that of the database. If they are different, the database manager translates the record correctly.


Getting Started

New users of DP4 may read this section to become acquainted with the DP4 and the way things work. You also find it helpful to refer to the Introduction to DP4 (see Related Documents in the contents.) This section has a brief introduction to the DP4 database, and some simple exercises that show you:

  • How to log onto DP4

  • How to use the DP4 system menus

  • How to use the DP4 user interface

  • How to use the Windows interface

  • How to run DP4 utilities, programs and reports against the sample database


Grey out

Colours used to indicate unavailable menu options


Group applies to all lines

Enable this option when the group of fields are repeated on every line of the import file. In this case, the Importer skips the remaining questions


Hash area size

The hash area size shows how much space is used for tables with a hashed index.
Using hashed index is no longer supported.


Heading

Enter a heading for the table. This is for use in screens generated by DP4 such as the Data Entry and Enquiry and Report Writer utilities


Heading

Enter up to 4 lines, 16 characters wide that can be used as the fieldname. DP4 uses the heading in standard utilities such as Data Entry and Enquiry and Report Writer


Help Authoring

The DP4 authoring utility allows you to add context-sensitive help to your application programs. This utility is pre-loaded into memory and can be invoked from within your application program.


Help

A window of help describing the features of Dynachek


Help Editor

See Help Authoring.


Help

Colours used for help messages


Hide cursor in menus

Enable this option to make the cursor invisible in menus


How to Log On to DP4

To log on to the DP4 System Main menu:

  1. Double-click on the DP4 icon in the DP4 group, or select the Run option from the Windows File menu and type: DP4. The DP4 Logon screen is displayed

  2. Enter the DP4 username:

  3. Press <Enter> to complete the logon entry. You will probably not be asked for a password (unless your system administrator has added one for the DP4 user). The DP4 System Main menu is displayed:

If nothing happens when you press <Enter>, your system no longer has the DP4 user name set up. Consult your system administrator for an alternative user name that gives you access to the DP4 System utilities.


Identification

Use this option, first of all, to set up the properties of a field group. These tell the Importer whether the field group is present on every line of the import file, or if it is not, what value in a fixed position on the line tags the field group as being present


Import from column

Enter the column of the import file in which the tag field is located. To take the import file as your model, press <Ctrl+P> and:

  • Use the <↑> and <↓> cursor keys to display a line containing the field group tag.

  • Use the <←> and <→> cursor keys to highlight the column containing the tag.

  • Press <Enter> to fill in the tag's details from the highlighted column of the import file

If you answered 'Yes' to the question 'Fixed length fields' in the earlier section Editing and Creating Import Programs, the column specified is the starting position, in characters, of the field in the file.


Import from column

Enter the column of the import file in which the field is located. To take the import file as your model, press <Ctrl+P> and:

  • Use the <↑> and <↓> cursor keys to display a line containing the field.

  • Use the <←> and <→> cursor keys to highlight the column containing the field.

  • Press <Enter> to fill in the field's details from the highlighted column of the import file.

If you answered 'Yes' to the question 'Fixed length fields' in the section Editing and Creating Import Programs, the column specified is the starting position, in characters, of the field in the file.


Importing Data from an ASCII File

Importing Data from an External File

An Importer program must be set up on the database to allow you to import the data from the file. The section Setting Up an Import Program tells you what to do.

Alternatively, you may be able to use the DP4 Load utility to import the data. The advantage of this is that an Importer program is not required; the disadvantage is that the data must be held in a fixed format ASCII file. Full details on this means of transferring data are given in Transferring Data with the Load and Unload Facility

  1. If the import file is not in the current/working directory or the 'Print files' directory, copy it there

  2. Importer Programs are executed with IMPPOST. To access this via the DP4 menu, select Import/Export and Reporting and then Run Importer Porgrams, or for releases prior to 4.621 select Other facilities, then Importer facilities option and finally select the Import Data option.

  3. Enter the name of the database.

  4. Press <Ctrl+P> on the Parameter set field to pop up a list of parameters on the database. Press <Enter> to select a parameter set. DP4 identifies and displays the name of the file to import

  5. Select the output destination for any messages during the import sequence (for example, screen and file). Answer the questions relating to the action to take on error:

    Action Description
    List errors only

    Disabled by default. Enable this option, if you want nothing but errors reported

    Test only (do not post)

    Disabled by default. Enable this option to test the import file without posting records

    Continue posting after errors

    Enabled by default. Disable this option to halt record posting if an error is found in the import file

    Continue testing

    Applicable when 'Continue posting after errors' is disabled, in which case you may enable this option to continue testing the import file rather than halting the Importer program

DP4 imports the data to the target database. The Import utility terminates, and you are returned to the DP4 System Main menu


Importing Data using a Parameter Set

This section describes how to use the DP4 utilities, IMPSETUP and IMPPOST, known collectively as the Importer. As with creating an export program in Report Writer, you use the setup utility (IMPSETUP) to create a parameter set and run the post utility (IMPPOST) to read the parameter set and import the data from an external file.

Typically the files are ASCII text files, and each line of the file to be imported contains information corresponding to one or more records in a DP4 database. The fields in each line can be in fixed posiitons on the line, or the fields can be variable length but delimited by a special character (usually a comma). Importer can read files where each line is in the same format, but also files contain lines of different types (see Understanding Field Groups for more information on this topic).

The Importer setup utility,IMPSETUP, allows you to:

  • Automatically create Importer programs, sometimes called parameter sets, from export programs created by the Report Writer. See Automatic Creation of Importer Programs

  • Create or amend parameter sets. See Setting Up Importer Programs

  • Move parameter sets from one database to another. See Managing Importer Programs

  • Rename programs. See Managing Importer Programs

  • Print out details of Importer programsin summary or detail format. See Managing Importer Programs

  • Test programs, usually after making changes to the data dictionary. See Managing Importer Programs

  • Delete Importer programs from the database. See Managing Importer Programs

The data posting utility, IMPPOST, allows you to run the parameter sets created by IMPSETUP to import the data from a data file.

If a change has been made to the database structure, any parameter sets need to be reviewed, unless the data independence facility was used. In this case, the programs use the previous structure of the database.


Improving DBCHECK Performance for Local Databases

Where the database resides on the machine from which you are running the database integrity check, run the utility with the −LOCAL command tail to improve performance.

From release 4.621 DBCHECK automatically determines whether the database to be checked is local or not by examining the network status table. The −LOCAL option will be selected automatically unless the resil_type field indicates that databases are remote. You can still use −LOCAL to force local file handling to be invoked even where the database should be remote, and you can use -REMOTE to force the database to be treated as though it were remote.

Under FlexOS 2.x, use DBCHECK.280, by copying it to DBCHECK.286, rather than the supplied dbcheck.286 because it loads the terminal manager as an overlay rather than a separate process and will considerably improve performance.

On the DP4 System menu, select the Run program option, and type the command:

DBCHECK −LOCAL

and press <Enter> to execute it.


Inactive Title

Colours used for the title line of inactive windows


Including Base Dictionary Tables

To include base dictionary tables in the table plan, run the Table Planner with the -BASEDICT command tail. For example, on the DP4 System menu, select the Run program option and type the command line:

TABLEPLN -BASEDICT


Special Menu Command Tails

The Menu command tails are described in this table:

Command Description

%1, %2 .. %9

Prompts for the values to substitute for these variables at run-time, using the Second map option. On the second map, include the text to prompt the user for the values, and include fields numbered 1 to n that correspond to the variables %1 to %n
%BATCH

This option is not very well named. It instructs the DP4 program to use the shell to run the command if an attempt to run it normally fails. This option is required to run .BAT files on MS-DOS or Windows, or shell scripts on Unix/Linux. It is also needed to run internal commands like dir on MS-DOS/Windows.

Note that the shell is only invoked if the program cannot be run normally. Thus %BATCH would not work as intended if you used iut to do something like running an ordinary executable program with command line input/output redirection.
%CLOSEDB

Closes the database whilst an option is executed. You need to do this if the option requires exclusive access to the database files is a non DP4 program that needs access to the database (for example a tape backup utility).
%DB

Substitutes the current database name at run-time. For DP4 utilities remember to use this format: -DB %DB

%NOCLEAR

(Text based interface only) Suppresses clearing the screen for a command that does not output to the screen

%NOWAIT

(From 4.620) DP4 does not wait for the command to complete before continuing to execute. This option will only work well for programs that run properly as background tasks.

%PAUSE

Pauses and asks the user to press any key once the program has run. This option may not work as intended on Windows
%PREFERENCES

If using a non-DP4 program to change the user preferences, implements the changes in the current session
%SYSTEM

(MS-DOS only) Attempts to remove the database manager and user interface manager from memory to increase the available memory


Built-In Menu Commands

The menu command options that you can provide as options in a menu are described in this table:

Command | Description
%DB database name | Changes to the specified database, for example, %DB SALESORD. It is important to use make sure the Return To page and option are valid when you use this option.
%LOCKUP | Locks up the system until the user re-enters the password
%LOGOFF | Terminates and re-starts the DP4 menu system. The log-on screen is displayed
%LOGON | Prompts the user to log on without terminating and re-starting the system. The logon screen is displayed
%NOSTATUS | Turns off the status line
%PAGE | Goes to the page specified in 'Return to page' field of the option. The option specified in the 'Return to option' field is highlighted.
%PASSWORD | Lets the user change the password
%PREFERENCES | Lets the user change the preferences
%STATUS | Turns on the status line at the top of the menu that displays the database and user names, time, date and menu page while DP4 is running. (This is unrelated to the status line that appears near the bottom of the screen in DP4 editors, such as the Menu editor.) To turn on the status display permanently, use the user preferences option
%SYSTEM | Exits DP4 and returns to the operating system (do not confuse this option with the command tail of the same name)


Index

A retrieval mechanism that allows data to be retrieved in a particular sequence.


Index-only Table

An index-only table does not hold data, but is used to create secondary indexes for its child tables.


Index analysis

Enable this option to list the details of indexes. For example:

Table name | Nr keys |  |  |  |  |  |  |
CUSTOMER | 1 |  |  |  |  |  |  |
Index table/role | Primary | Prevent change | Orphan check | Child delete | Update | Parent check | Parent verify | Needs reorg
REGION |  | No | No | No | Yes | Yes | No | No
INDEX_CUST_NAME |  | No |  |  | Yes |  |  |


Index File

A DP4 database comprises at least a data file (.DAT) and an index file (.IND). The index file provides the access mechanism for data retrieval. It contains the keys together with pointers to the records in the data file.

As of release 4.622 it the index file may use a separate data pointer file (.PTR) to store the actual pointers to records. This usually results in a significant overall reduction in the size of the index, potentially helping to improve performance.


Index Link Scrutiny

You can access SETLINK from within MAKEDB, or directly from the command line or the DP4 menu.

  1. When you enter SETLINK, (and after selecting a database if necessary), you are presented with a menu containing five options (including exit). Both the first two options allow you to set the link properties. The first option displays all the indexes and parents for a particular table. The second option displays all the tables that a particular table is a parent of or index for. The second option will be particularly useful when you have modified a database by adding a new index table. Select either option 1 or option 2.

  2. DP4 automatically displays the first of the tables for which you can set index characteristics.

  3. Press <Enter> to accept the table name, or enter another table name. DP4 displays the current index settings for the table you select. Change the index settings between the tables in your database as required for your application.

    Click on features of the example Index Settings screen above for further details.

  4. When you complete the index link settings, press <Esc> several times to return to the DP4 System Main menu.

    You have completed the database creation process. Before you take any other action, back up your database.

SETLINK contains two more options: the Redundant Index Report, and Automatically turn off unused indexes.These are explained below.

Redundant Index Report

Large DP4 databases often contain a great many tables and indexes, and it is by no means uncommon for it to become very unclear whether a particular index is required or not. SETLINK has been enhanced to generate a report on "redundant" indexes. The indexes are redundant in the sense that there is another index which will return the same data in the same order, and that therefore one of the indexes can be dispensed with provided any programs that use the index to be turned off are updated as appropriate. A database with redundant indexes is larger and slower than it would be otherwise. The report does not identify indexes that are turned on but which are never actually used, as there is currently no way to determine this. It is possible to identify which indexes are not used by QAB or Report Writer, but not indexes that are used by C /C++ programs,or SQL.

For example suppose a database has a table containing these tables and fields:

  1. ORDER containing foreign keys ITEM_NR and ORDER_DATE
  2. ITEM with key fields ITEM_NR
  3. INDEX_ITEM_DATE with key fields ITEM_NR and ORDER_DATE

Both the ITEM and INDEX_ITEM_DATE tables index the ORDER table. However there is probably no need to enable both indexes, because the INDEX_ITEM_DATE will return ORDERS in ITIM_NR sequence in any case. Unfortunately the ITEM index IS required if child deletion or orphan checking from ITEM to ORDER is required.

The Redundant Index Report in SETLINK identifies and lists all indexes for which an alternative index can be used instead. The report contains pairs of lines, the first line lists the redundant index, the second an index which can be used as an alternative. (There may be several such indexes, in which case the one with the most keys is listed). The child delete and orphan check settings for the redundant index are also listed. If both of these set are set to NO then the index is definitely redundant. If either is set to Yes the index is not strictly speaking redundant because it is required for these purposes, but you may be able to live without it if you can dispense with these options for this table.

You may occasionally find that the primary index of a table is listed as being redundant. In this case it is more likely that you will wish to switch off the secondary index, as it cannot add any further ordering information to the records in the table. At worst you may need to add a further test to programs that do EQUAL searches on the secondary index. (For example a CREDIT_CARD table might have a primary key of CARD_NR and a secondary index of CARD_NR and STOPPED, so that a program could check it was not stopped with a view to doing a NODATA search on the secondary index. However, in practice it would be better to just inspect the STOPPED field after accessing the record via the primary index.)

Automatically Turn off Unused Indexes

This option, introduced in releae 4.621, is particularly useful where the number of secondary indexes that are turned on has become excessive and performance of DP4 has been adversely affected.

This option switches off all secondary indexes that are not needed for automatic child deletion and are not used by either QAB or Report Writer. A report is produced detailing the changes made, so you can easily use options 1 or 2 of SETLINK to turn on any indexes you are sure you really want to keep.

After running this option you will normally want to do a full reorganisation of the database to reclaim the space used by indexes that have now been switched off.

Using this option might cause some programs to stop working with system error 24, and might degrade the performance of some SQL queries. Also the operation of RECASTDB using a configuration file may be upset.

You can fix any problems of this nature that arise by turning the problem index back on again. You will have to reorganise the affected index afterwards.


Index table only

The default is 'No'. If you want to index records in a table by a key other than the primary key of the table, you define an index-only table that contains the key. Unlike tables that store records, index-only tables do not have to have a unique primary key

For example, to let you view the records on the CUSTOMER table in order of the customer name, the INDEX_CUST_NAME table has been defined as an index-only table. This table contains one field, the NAME field, defined as the primary key

After creating an index-only table make sure that you only enable index updates for the tables requiring the secondary index (see Index Link Scrutiny). By default MAKEDB enables an index-only table index on all possible tables, so there is a distinct possibility that indexes may be created that are not required. This can result in unnecessarily large database index files, causing performance degradation and difficulty with database administration (e.g. excessive time to backup database). You can change the default behaviour of the index analysis phase of MAKEDB by specifying -nonewindex as a command line option. All new indexes that are created are initially turned off. While this may make running SETLINK slightly more onerous, it may well be worth doing because you can be sure only indexes actually required are turned on.


Indexed Records

Records in a table are indexed by the primary key and any secondary keys in the table.


Indexes

Records in a table are indexed by the primary key of the table. For example, a customer record is indexed by the key CUSTOMER_NUMBER. As a rule, the customers are displayed in the order of the key field.

Records can also be indexed by tables higher in the table structure. For example, the CUSTOMER_NUMBER is included as a secondary key in the ORDER_HEADER table. This allows order records to be indexed in customer sequence.

If you want to index the records in a table by a key other than the primary key of the table, you define an index-only table that contains the key. Unlike tables that store records, index-only tables do not have to have a unique primary key.

For example, to view the records on the CUSTOMER table in order of the customer name, an index-only table can be used. This table defines NAME as the key field on the table:

INDEX_CUST_NAME
___________________________
1 NAME C30

The index-only table is a parent table of the CUSTOMER table, and allows the customer records to be viewed in name order.

Switching off Secondary Indexes for Improved Performance

The DBMS Database Manager has work to do to maintain secondary indexes, so you should switch off any you do not need. On the other hand, you can sometimes save a lot of processing with a secondary index. For instance, if your application needs to know which is the most recent order line in an order, and you average 50 order lines for each order, it may be helpful to have a secondary index on order lines with keys of order number and insertion date. In general, secondary indexes are worth having if you cannot write the application without them (an example would be an index to customers by name rather than region code) or if they substantially reduce the proportion of records that need to be accessed. An example of a secondary index that is unlikely to be worth having is one on a credit/debit indicator, which only reduces the proportion to be selected to a half.

Physical Indexing for Improved Performance

Prior to release 4.622, DP4 allows you to create physical indexes in addition to the indexes that are built from keys. Physical indexes reference the physical position of records on the disk. Although a physical index may provide faster access than the prime index and, therefore, give better performance for enquiries and reports that are run on the database, it may slow down database updates. Physical indexing also slightly increases the size of the index file.

If a physical index is available for a table, the F_PHYS_INDEX flag, as defined in DBFLAGS.H, is set in the @REL_FLAGS field of the @RELDATA record. Using the DP4 C interface, for example, records can be fetched through a physical index like this:

fetch(FIRST,&datarec.1,PHYSREC_TYPE,0);
while (there)
{
  /* process the record ... */
  fetch(NEXT,&datarec.1,PHYSREC_TYPE,0);
}

This code fetches all the table records in the random order in which they are physically arranged in the data file.

A physical index is unlikely to give significantly faster access than the prime index when the data file has been sorted by a database reorganisation. From release 4.622 REORGDB -DATA sorts data into prime index sequence, so there is no advantage to this type of index, and it is therefore no longer supported.


Initial option

Enter the option number highlighted when the menu appears


Initial page

This is the number of the first page presented to the user after logging on. This is normally page 1


Insert new records?

Answer 'Yes' to insert records that are not already on the table on the target database. Answer 'No' to prevent new records being inserted


Inserting and Deleting Text

The keystrokes that insert and delete text are as follows:

Editing Action | Key | Alternative
Delete character at cursor | <Delete> | <Ctrl+G>
Delete character to the left of the cursor | <Backspace> | <Ctrl+H>
Delete from the cursor to the end of the word | <Ctrl+T> |
Delete from the cursor to the end of the line | <Ctrl+Y> |
Undo deletion at the cursor | <Ctrl+U> |
Insert new line at the cursor | <Ctrl+N> |
Typeover/insert toggle | <Insert> |


Installing the Example Database

Skip this section if the files SALESORD.DAT and SALESORD.IND are already in your DP4 database directory.

  1. Make sure you have the Itim Technology Solutions disk labelled "Example Disk" which holds the SALESORD database

  2. From the Run option of the Windows File menu, copy the SALESORD.ZIP file from the Example disk, for example: COPY A:\SALESORD\SALESORD.ZIP C:\DP4\DB

  3. Unzip the SALESORD database, for example: PKUNZIP C:\DP4\DB\SALESORD.ZIP

  4. Run a dummy backup and delete the roll-back log, for example:
    SETSESSN -DB SALESORD -DELETE

    When this operation is complete, return to the DP4 System Main menu


Integrity check

Checks the integrity of the database


Integrity Checking

See Database Integrity .


Interpreting the Keystrokes in a Redirect File

The Redirect file format is an operating system-independent format which allows the creation of a demonstration or test script for a DP4 program. A Redirect file can be re-played to the DP4 program on any of the supported operating systems.

Redirect records a demonstration by adding one line to the file for each field that is accessed. The following information is stored:

<Map number> <Field number> <Option number> <Speed> Field input>

The information format is explained here:

Item | Description
Map number | The number of the map on the system or application database
Field number | The field number of the input field or menu option
Option number | One of three Redirect options: 1 is Continue 2 is Pause 3 is Display message and Pause
 | For option 3, the message is included in the file. Each line of the message starts with a lead-in character +, or * for the last line of the message
Speed | The length of the pause if option 2 or 3 is selected
Field input | The keystrokes entered for the field. Only the ASCII characters in the range 32 to 127 decimal (<Space> to <Del>) are recorded

This is an example Redirect file, run against the DP4 System menu utility:

250 1 1 3:DP4^M
+This Redirect script loads the DP4 System
+menu and runs a QA Build Customer Enquiry program on
*the SALESORD database
10100 1 1 3:q
+Run the QA Build interpreter, enter the database and
*program names
11100 1 1 3:i
801 2 1 3:prog9^M
*Select the Customer Enquiry option
1 1 1 3:e
3 1 1 3:^M
1 1 1 3:^M
*Retrieve the record for customer 20
1 1 1 3:20^M
1 1 1 3:^D
1 1 1 3:^M
*View the customer details
1 1 1 3:^[
1 1 1 3:^[
3 1 1 3:^[
1 2 1 3:x^[
11100 1 1 3:^[
10100 4 1 3:^[
*Escape to the operating system

The control codes are explained in this table:

Action Type | Description | Code
Menu and screen control |  |
 | Escape | ^[
 | Enter (accept field or select option) | ^M
 | Tab (accept screen) | ^I
 | Help toggle | ^J
 | Help authoring mode toggle | ^O
Cursor movement |  |
 | Character right, character left | ^D, ^S
 | Word right, word left | ^F, ^A
 | Start of line, end of line | ^Q, ^\
 | Line up, line down | ^E, ^X
 | Scroll down, scroll up | ^W, ^Z
 | Page up, page down | ^R, ^C
Text editing |  |
 | Enter (carriage return) | ^M
 | Insert toggle | ^V
 | Insert line | ^N
 | Mark block | ^K
 | Retrieve block | ^L
 | Paint mode toggle | ^P
 | Box drawing mode | ^]
Insertion and deletion |  |
 | Backspace | ^H
 | Tab | ^I
 | Delete character | ^G
 | Delete word | ^T
 | Delete line | ^Y
 | Undo deletion | ^U
Function keys and characters |  |
 | Function keys <F1>, <F2> .. <F26> | \001, \002 .. \026
 | The characters \ and ^ | \\, \^


Keep deletions

Enable this if you want to keep deleted data on the database. Normally, space occupied by deleted records is automatically reused when new records are added to the database and deleted data is completely lost. If you plan to use the DP4 Software Release and Data Distribution (SRDD) product, it is advisable to enable this option (for development databases only) and keep the deleted data. (It is not essential because you can opt to keep deleted data within SRDD itself)

This option has been removed as of release 4.622, and the use of this feature of SRDD is not supported.


Key

A field or fields used to locate or identify a record.


Key fields

Enable this option to list the key fields of all database tables. For example:

Table | Key | Field | Role
COMPANY | 1 | COMPANY_ID |
CUSTOMER | 1 | CUSTOMER_NUMBER |
EMPLOYEE | 1 | EMPLOYEE_NUMBER |


Key precedence

For key fields, number the key precedence from 1 to the number of key fields (to a maximum of 99). Give the highest priority field a priority of 1. Enter a key precedence of 0 for non-key fields


Key to clear messages

Applicable when "Disallow 'Press any key'" is enabled. Messages are normally displayed with the text 'Press any key' and you can do that to continue. The NULL value, enables the normal operation. If you require messages to be cleared with a specific keystroke, press the <Spacebar> or <Ctrl+P> followed by the keystroke


Key Translation Table

A table that defines the message that is sent to the application when a key is pressed.


Keys Help

The Keys Help shows key sequences that perform special actions when editing the file. Use the Help key (usually F9) to toggle it on and off


Keys help

The Keys help window is initially displayed above the Editing window. <Enter>, or <Help>, toggles the Keys help on and off


Language Table

This table allows you to set up mapsets for different languages that are appropriately displayed depending on the user's login name.


Last backup number

The backup or session number tells you how many times the database has been backed up.


Last log number

The log number tells you how many transaction logs have been created since the database was created


Layout mode

In layout mode, the table planner shows the tables in one third scale, and field names are not displayed. Use this option for producing a table plan containing many tables. If you start in this mode, you may still expand the plan to full scale at a later stage


Length

Enter the display length of the field. For character-type fields this is also the storage length on the database


Length of field in file

If you answered 'Yes' to the question 'Fixed length fields' in the section Editing and Creating Importer Programs, the length is the number of characters occupied by the field in the file. If using the import file as a model, highlight all the characters of the field to be imported:

  • The <Ins> key increases the width of the highlight.

  • The <Del> key reduces the width of the highlight.


Licence File

The DP4 licence file, DP4.SYS (from 4.620) or DATAFIT.SYS in older releases, holds your DP4 licence number and name, as well as defining the location of the DP4 files, and console/transaction settings. The DP4 licence file is provided on diskette with your first set of DP4 software, and must be kept in a safe place.


Licence Number

The DP4 licence number is stored in the licence file, DP4.SYS or DATAFIT.SYS, and should uniquely identify each installation.


Line length

Enter the line length, if the lines do not end with carriage return and/or linefeed and are all the same length


Lines end in CR/LF

Answer 'Yes' if each line in the import file ends with a carriage return and line feed. If you answer 'No', you are prompted to enter the line length


Lines to skip

If there is a header or comment at the start of the file, enter the number of lines to skip


List of tables

Enable this option to list tables names with storage lengths and internal reference numbers. For example:

Table name | Length | Ref nr
COMPANY | 140 | 4140
CUSTOMER | 162 | 4107
EMPLOYEE | 52 | 4096
... |  |
SUPPLIER | 134 | 4133


Loading Data into the Target Database

To load database data from an ASCII file created by the Unload utility or formatted as explained in Understanding the Data Format:

On the DP4 System Main menu, select the Run Program option and enter:

LOAD

Enter the name of the file, including its .TXT extension, that holds the data. The data is loaded into the database specified at the beginning of the file. The table being updated and a record count is displayed as the data is loaded. Unless you specify the -error option described below it does not matter if some fields containined in the file are not in the database table, or fields on the database are missing in the file. LOAD is very useful for transferring data to databases with slightly different structures.

Modifying the Load Facility

To override the database specified in the data file, use the -DB command tail, for example:

LOAD -DB salesord

To suppress screen output while the data is loading, append the -QUIET command tail:

LOAD -QUIET

Normally the Load facility ignores fields that are in the file but missing from the target database. To force Load to regard this as an error and abort its operation, add the -ERROR command tail:

LOAD -ERROR


Locating and Viewing Records

The View option allows you to change the display so that field names are shown instead of field headings. This is useful when entering formulas for conditional searches with the Cond option.

  1. To scan through the records, select the Scan option on the menu bar:

    Press the <→> and <←> cursor keys to move between the records in the table

    Press <Esc> to return to the menu bar

  2. To move to a record directly, highlight the menu option Keys, and enter the key of the record. Press <Enter>, and DP4 displays the record. If no record is found, DP4 displays the record status: "No matching record"

  3. Press <Esc> to return to the menu bar


Log Files

The log files are as follows:

  • Transaction log - database_name.LOG

    The transaction log stores the changes made to the database since it was last backed up.

    There is normally one transaction log for each backup interval. The log stores the time and the full details of each database transaction. Should data loss occur, the Database Recovery utility uses the transaction log to roll-forward the database.

    If transaction logging is disabled but rollback lofgging is enabled a temporary transaction log is created using the .RLL file extension. This uses the same format file format as an ordinary transaction log but it will only contain the most recent transactions (those completed since the rollback log was last re-started).

    Files with .LBK extension are backup transaction log files created by DYNABACK.

    The information in the transaction log can be viewed using the Transaction Log Auditing utility, as described in Auditing the Transaction Log.

  • Roll-back log - database_name.RLB

    The roll-back log stores images of data before changes are made.

    Whenever a change is made to the index file or to the data file, the pre-image of the record being changed is written to the roll-back file. Following a system failure, the database recovery mechanism reads the roll-back file backwards and applies its contents. This allows the data and index files to be recovered as they were at the time the roll-back file was opened.

    The database is brought fully up to date and the roll-back file is deleted from time to time, and a new roll-back file is created as described in Updating the Database.

  • Console log - SYSLOG.PRN

    The console log records all access to DP4 programs, and also all system and fail errors.

    DP4 maintains the console log in the location specified in the Directory Assignment option of DFSETUP. Each time a program is run, a record is written to the file in this format:

    <user> <read/write authority> <time> <date> <program name>

    You can specify separate log files for each user, by making sure each user works from a separate directory ansd leaving the location for SYSLOG.PRN blank. Alternatively, you can have a single console log for the system, by specifying a location such as c:\dp4. The latter option is usually preferable.

    This is an excerpt from a console log:

             255 255 13:42:09 02 Mar 2001 [MAKEDB  ]. Program running without login.
         255 255 13:42:14 02 Mar 2001 System error 68
DP4 System error 68. Origin: Operator error

       Correct transaction log file not found.
       A frequent cause of this problem is that a log file for
       a previous session of the database has not been deleted.

         255 255 13:42:30 02 Mar 2001 [MAKEDB  ]. Program running without login.
         255 255 18:19:49 02 Mar 2001 [TCPMGR  ]. Program running without login.
         255 255 18:20:00 02 Mar 2001 [PROGDATE]. Program running without login.
         255 255 18:20:03 02 Mar 2001 [PROGDATE]. Program running without login.
         255 255 18:18:37 06 Mar 2001 [IMPSETUP]. Program running without login.
         255 255 10:39:55 07 Mar 2001 [COLLATE ]. Program running without login.
         255 255 11:12:43 07 Mar 2001 [DFSETUP ]. Program running without login.
         255 255 11:13:00 07 Mar 2001 [DP4     ]. Program running without login.
dp4      99  99  11:13:14 07 Mar 2001 DFSETUP
dp4      99  99  11:13:31 07 Mar 2001 DBCHECK
dp4      99  99  11:13:37 07 Mar 2001 %SYSTEM


Low occurs/High occurs

DP4 fills in the occurs values if the field has more than one occurrence, such as an address field


Lower bound/Upper bound

Enter the lowest/highest values permitted for the field. DP4 uses these values to validate the field when a value is entered


Main System Menus

See System Menus.


Maintaining a Database

Database maintenance is a key issue. It is important that you develop a rigorous approach to ensure that the database continues to perform at its optimum. However, DP4 does try to help you, regular back-up and integrity checking of the database throughout the development of DP4 applications or use of DP4 utilities is strictly enforced. Furthermore, security measures such as roll-forward and roll-back recovery, should enable you to recover the database in the event of a system crash. DP4 actively manages the database and will prompt you to perform an appropriate action, like a database reorganisation, whenever work done by you on the database demands this.

This section covers:

  • Setting up transaction and roll-back logging

  • Backing up a database

  • Recovering and Restoring a database

  • Checking the status and integrity of a database

  • Counting the records in a database

  • Reorganising a database

  • Auditing the Transaction Log

  • Shutting down a database

  • Running maintenance programs from batch files


Maintaining the User List

The user list is a list of the names, passwords and preferences of the users who access the DP4 menu system. When you install a new DP4 system, there is a default user with the user name DP4 and a blank password. You use this username to access the DP4 System menu.

The DP4 user has the highest access levels with access to all the DP4 utilities through the DP4 system menu. To safeguard the security of your system, make sure you remove this username, or change its password before you distribute your DP4 application.

This section describes:

  • Modifying the User List
  • Adding a New User
  • Setting Up a User
  • Amending an Existing User
  • Changing a User Password
  • Deleting a User
  • Printing the User List
  • Changing your Password
  • User Preferences
  • Changing a User's Preferences
  • Changing the Default User Preferences

Read Editing a Menu if you wish to provide a customised menu for a user.


Managing Importer Programs

This section tells you how to manage your Importer programs with the Importer setup utility. Among the management tasks are:

  • Testing Importer programs to assess the impact of database changes.

  • Moving Importer programs between databases.

  • Printing details of Importer programs.

  • Deleting Importer programs which are no longer required

  1. All the Importer program management facilities are contain in the IMPSETUP program which can be accessed as follows:

    • Select Import/Export and Reporting and then Importer Setup and Utilities, or for releases prior to 4.621 select Other facilities, then Importer facilities option and finally select the Setup and utilities option.

  2. Enter the name of the target database. The Importer Setup screen appears.

  3. Select the appopriate option from the Menu that is displayed.

Testing Importer Programs

After making changes to the database, use the Test menu to test Importer programs. Its options are as follows:

Option Description
all programs

Validates the Importer program processing against the database. By removing any invalid processing from the Importer program, the validation process attempts to correct any problems that are found, such as:

  • Changes of data type for fields
  • Field length changes on the database
  • Processing more occurrences of a field than exist
  • Changes of a field's scale factor
Test selected programs

As for Test all programs, but on your selection of programs

Options

Selects validation options when testing programs

Moving Importer Programs

The Move menu allows you to transfer Importer programs between databases. Its options are as follows:

Option | Description
Extract all programs | Extracts Importer programs from the database to an ASCII file, for transfer to another database
Extract selected programs | As for Extract all programs, but on your selection of programs
Post | Posts ASCII files created with either of the extract options to the database. The ASCII file is compiled during the posting and Importer programs are created on the database
Options | Selects options for error recovery during program compilation

Printing Importer Programs

The Print menu allows you to print out details of Importer programs. Its options are as follows:

Option | Description
Print all programs | Prints out details of Importer programs to an ASCII file
Print selected programs | As for Print all programs, but for your selection of programs
Options | Selects whether summary or detailed information, including details about the format of fields, is printed

Deleting Importer Programs

The Delete menu allows you to delete all or selected Importer programs from the database.


Managing Menus

There are three types of menu management facility:

  • Copying and deleting

    Use the copy facilities within the Menu Editing menus utility to copy and delete individual menu pages or menus. Menus can be copied to a database on the same system.

  • Extracting and posting

    Use the print options within the Menu Editing utility menus to extract menu pages or menus to a file. Use the Map Posting utility to post the menus to a database on another system.

  • Printing of menus for documentation purposes

    Use the print options within the Menu Editing utility to extract individual menu pages or menus to a file for inclusion in application documentation

Menus are stored in the same format as maps, and can be edited, copied, extracted and posted using the Map Editing facilities (menus are stored in a mapset called MENU).

The following menu management operations are described in this section:

  • Copying a Menu Page
  • Copying a Menu from Another Database
  • Deleting a Menu Page
  • Extracting a Menu Page
  • Extracting a Database Menu
  • Posting a Menu


Managing Users

Because the user interface is independent of the application programs, you can set up as many versions of the user interface as required and run them concurrently. A single DP4 application can, therefore, have an individual rapport with every one of its users - no matter how different their approach may be and irrespective of their nationality, business experience, or familiarity with computers.

This section describes how to manage the users of a DP4 system. It covers:

  • Maintaining the User List

  • Editing a Menu

  • Using Menu Commands

  • Managing Menus


Map

A definition of the layout of text and fields that is used for displaying and printing information.


Map Editor

The DP4 Map Editor allows you to create, edit and manage mapsets and their maps.


Map Field

A map contains map fields that handle data input and output between screens and supported DP4 programming environments such as C and QA Build.


Mapsets

A mapset is a collection of maps under a mapset name. Mapsets are stored on the application database.


Match clock date/time

For date or time tag fields, enable this option if the value of the tag must match the system date or time


MATERIAL

Stores details of each material including its code, description, unit price, quantity in stock and re-order level together with the code of the principal and the alternative supplier of the material. The material code is the primary key to the MATERIAL table


Maximum period between backups

Specify the maximum number of days between backups.


Menu Bar

Use the <Esc> key to move the cursor from the editing area to the menu bar


Messages

Colours used for information messages and Yes/No fields


Mnemonics

Colours used for mnemonics in menus


Modifying the Operation of DYNACHEK

Use option 2 - Options on the Dynachek Facilities menu to modify what Dynachek does. For example, if you just want Dynachek to count the records in tables, enable Count only in the Dynachek Options menu:

Click on the options in the above illustration for further information.


Modifying a Database

This section tells you how to modify the database. It covers the following topics:

  • Utilities for Modifying the Database

  • Structural Changes

  • Formatting a Field

  • Adding Numerical Units

  • Changing Table and Field Headings

  • Changing Table, Field and Role Names

  • Copying Table Structures

  • Changing the Number System


Modifying the Backup Operation

You can use a number of command tails with the DP4 backup utilities to modify their operation. The command tails are described below and applied in this way:

  1. On the DP4 System menu, select the Run program option

  2. Type the command line: DBBACKUP|DYNABACK -<command_tail>
    and press <Enter> to execute it

Turning Verification On and Off

To turn verification on or off and suppress the verification question, run the backup utilities as follows:

DBBACKUP|DYNABACK -VERIFY|NOVERIFY

Leaving Space on the Disks

To set the amount of space in Kilobytes to leave free on each backup disk, run the backup utilities like this:

DBBACKUP|DYNABACK -SPACE <amount>

Formatting the Disk Before Backing Up

To format the diskette as the first operation of the backup, run the backup utilities with the -FORMAT command tail:

DBBACKUP|DYNABACK -FORMAT

You must supply a batch file that contains the appropriate operating system instructions to format the diskette. It must be located in the current directory or one that can be found in the path and named as follows:

  • FORMBKP.BAT for DOS, Windows and FlexOS
  • FORMBKP.CMD for OS/2
  • formbkp for UNIX

Specifying the Backup Location

To override the system configuration and specify the location to be used for the backup, run the standard backup utility like this:

DBBACKUP|DYNABACK -DISK <directory_path>

Examples of <directory_path> are A: and C:\BACKUP\. DYNABACK did not support the -DISK command tail until release 4.522/4.618.

Backing up Quickly

To perform a quick backup, run the dynamic backup utility like this:

DYNABACK -QUICK

This option does slightly less security in the event of a system crash: in this mode DYNABACK works slightly differently, and starts a new log file before the old one has been backed up (the old log file is renamed). If the system crashes before the backup is complete then the restore process will not complete properly unless you manually rename the backed up log file.

Suppressing Questions

To suppress all the questions that DP4 asks during a backup, run the DBBACKUP or DYNABACK utilities with these command tails:

DBBACKUP|DYNABACK -QUIET [-COMMENT "<text>"]

where -COMMENT adds an optional comment, up to 32 characters, displayed when restoring the database. When DYNABACK is used the backup can occupy only one disk in this case.

Backing up Specified Servers

To specify the server to back up from, run the dynamic backup utility as follows:

DYNABACK -SERVER <n>.


Setting the Database Version Information

  1. On the DP4 System Main menu, select Database Management

  2. On the Database Structure Management menu, select the Create/modify database option

  3. Enter the database name

  4. On the Database Creation/changes menu, select option 7 - Version/Security. The Database version control and security levels dialog is displayed

    Fill out the version control options as follows:

    Option Description
    Issue date

    Enter the date to be recorded for issue of the database

    Issuer's Id

    Enter up to 4 characters to identify the issuer

    Version number

    Enter a version number between 0 and 30000 for this issue of the database

    Reset session ?

    Answer 'Yes' to reset the database and transaction log session numbers to 0

  5. Click the OK button to save and exit


Modifying the User List

  1. On the DP4 menu, select Configuration Utilities and then the User and Menu Management option, or for releases up to 4.620, select Other Facilities and then the Menu Facilities option.

  2. Enter the name of any database on your system (the user information is automatically stored on the system database). The Menu Editing Main menu is displayed

  3. Select option 5 - Users table. The User List menu is displayed

  4. Select option 1 - Select user. The User Set Up dialog box is displayed:

  5. Add a new user as described in Adding a New User or press <Ctrl+P> to pop up a list of existing users. When you have completed the changes, press <Esc> several times to return to the DP4 System menu


Month first

Enable this to show the month in the first position, for example Dec 10 1995. Disable it, to show the month in the middle position, for example 10 Dec 1995.


Month name

Enable this to show the first three characters of the month name, for example 10 Dec 95. Disable it, to display the month number, for example 10 12 95


Month names

With 'Month name' enabled, enter the first three characters of each month


Month names

Enter your preferred abbreviations for months


Moving the Cursor

The keystrokes that move the cursor and scroll the file are as follows:

Cursor Movement | Key | Alternative
Move left, right, up or down | <←> | <Ctrl+S>
 | <→> | <Ctrl+D>
 | <↑> | <Ctrl+E>
 | <↓> | <Ctrl+X>
Move one word left or right | <Ctrl+←> <Ctrl+→> | <Ctrl+A> <Ctrl+F>
Move to the start or end of the line | <Home> <End> | <Ctrl+Q> <Ctrl+\>
Move to the start or end of the screen | <Home> twice <End> twice | <Ctrl+Q> twice <Ctrl+\> twice
Move to the start or end of the document | <Home> 3 times <End> 3 times | <Ctrl+Q> 3 times <Ctrl+\> 3 times
Scroll up or down | <Ctrl+↓> <Ctrl+↑> | <Ctrl+Z> <Ctrl+W>


MS-DOS Wildcard Commands

This utility allows a program to be run against a set of files under MS-DOS and MultiuserDOS.

On the command line, enter the wild command and the command to execute:

WILD <command> <filter>

For example:

WILD PROGDATE c:\dp4\dos\*.exe

If the program requires that only one filename be specified at a time, use the command tail -ONE. For example:

WILD EDIT *.txt -ONE

This command edits each filename in turn with the extension .TXT.


Multi-language Application

An application that can display text in more than one national language.


Multi-occurs Field

Fields may have multiple occurrences. For example, an address field with three occurrences can hold a three-line address.


Multi-part Key

A multi-part key is one that is composed of several fields to select table records.


Multi-user System

A system that can be accessed by several users at the same time.


National Language Support

The facilities in DP4 that support the development and creation of applications in other national languages.


Needs reorganisation

DP4 sets this to 'Yes' if the table needs to be reorganised following modifications to the database structure


Non-indexed Table

A table that does not have a primary key.


NULL input/NULL output character

Select a character to represent the NULL input/output character field value. This is normally "!"


Number format/Cash till input/Separate thousands/Blank if zero/Blank leading zeros/Trailing Sign/In Parentheses

These options are as described for Formatting a Field


Number of repairs made

This shows the number of repairs that have been made to the database.


Number system

The number system tells you which option was selected during the database setup (see Selecting the Database Number System for the full set of options)


Occurrences

Enter the number of occurrences of a field. Use this option to specify a single dimension array of numbers or dates. For character fields, this option specifies the number of lines in the field (for example, 4 for an address field)


Occurs Facility

See Multi-occurs Field.


Occurs

Enter the number of occurrences that make up the group's tag field


Operating-System Independence

The ability to transfer applications to different operating systems without modification.


Optimising the Index File

As the index file grows, logically adjacent portions of the index file, termed nodes, are liable to be widely separated on the disk. This fragmentation of the index reduces the performance of programs using the database. To check how much fragmentation has occurred to the index file, you can use the database integrity checking utility and see whether the node number displayed increases steadily or moves up and down erratically.

The optimise index file option rearranges the index so that the index nodes are in the most efficient order for searches. To use this option your system must have enough disk space to write another copy of the index file. The old index is not deleted until the process is completed. This option of REORGDB does not usually take very long to run.

All the options that build an index file call this option automatically, unless the -FAST option was specified.

  1. On the Database Reorganisation menu, select option 4 - Optimise index file. DP4 optimises the index file. It displays the number of index nodes to process and the number processed so far

  2. When the reorganisation utility terminates, press <Enter> to return to the DP4 System menu

Crash Recovery

A machine crash while this option is running does not harm the database. If one happens, just optimise the index again.


Options

Allows you to enable and disable a number of options which modify the integrity checking process (see Modifying the Operation of DYNACHEK ). You can also use this option to restrict Dynachek to just counting records in the database's tables


ORDER_HEADER

Stores the details of the date and time the order was placed, its value and customer payments made against it, delivery details, customer number and code of the Acme employee responsible for the account. The primary key is a unique number that identifies each order. A customer may have several order header records, each of which may have several order lines giving materials and quantities ordered. An order header record may also have several order payment records


ORDER_LINE

Includes one record for each different material in an order. ORDER_LINE contains the order number, a line number that identifies an order line, the material code and quantity ordered, together with the value of the order line. The order number plus line number form the primary key that identifies each record


ORDER_PAYMENT

Includes one record for each payment made against an order. ORDER_PAYMENT records the order number, a payment number that identifies separate payments, the media code (cheque, direct debit, credit card), the amount paid, the card number and payment date and time


Orphan

If a record that has associated child records is deleted, the child records are called orphans because they no longer have a parent.


Orphan Check

This is a check that may be applied when deleting a record. It prevents a record from being deleted if it has one or more child records on the database.

This check is only applied if any children exist in any of the indexes that you marked for orphan checking when you set up the database. It is also made for any children that would be deleted if the child delete check is specified.


Orphan check

Answer 'Yes' to prevent a parent record being deleted that would leave orphaned records. For example, prevent the deletion of a customer for which orders exist


Output Destination

The screen, printer or file selection to which the application sends data.


Outputting a Record

  1. Locate the record you wish to print. You can use the Scan or Keys options as described in Locating and Viewing Records

  2. Select the Print option

  3. You are prompted to select the output devices:

    The details of the record are output to the specified file in the Print files directory and you are returned to the Database Browser's main screen

The output devices are not prompted for the next and subsequent times you select the Print option, but the current record will be appended in the print file. If you want to change the output devices exit from the Database browser, re-enter it and select the record followed by the Print option.


Overtype text fields

Normally, the old contents of text fields are cleared when you start typing; if you want to

edit the contents you have to move the cursor before typing. Enable this option to change this, allowing immediate editing of the old text


Parent-child Link

When the key fields of one table (the parent) appear in another table (the child). The resulting one-to-many relationship between the tables is known as a parent-child link.


Parent Check

See Check Parent.


Parent check?

Answer 'Yes' to check for a parent record before posting. Answer 'No' to post records without checking for a parent. Parent checking enables you to prevent a record being posted to the database if its parent record does not exist on the database. Leave this check disabled if you want to import all the data and you know that some of the records do not have parents


Parent check

Answer 'Yes' to make sure a parent record exists before inserting a child record. For example, check that a customer exists before adding an order for the customer


Parent Table

A table which has a one-to-many relationship with another table (a child table).


Parent Verify

See Verify Parent.


Parent verify?

Answer 'Yes' to check that the parent has not been changed during the course of the import process and only allow posting for records with unchanged parents. Answer 'No' to post records without verifying the parent. This check is rarely required


Parent verify

Answer 'Yes' to check whether another user has updated the parent record since this record was read. For example, check that the material price is unchanged before updating an order line


Performance

The combination of factors relating to the speed and efficiency of a system, including the time to retrieve a record from the disk and the rate at which transactions are processed.


Performing a Dummy Backup

Performing a dummy backup is potentially very dangerous, as if the database has an undetected corruption, and you choose to delete the log file, you will have no reliable way to ensure the transactions contained in the log file are not lost.
As of release 4.621, there are few, if any, DP4 utilities that will cause a backup to be forced without very good reason.

You may want to perform a dummy backup in either of these circumstances:

  • You are backing up the database using other utilities and you want to increment the database and log file session numbers and delete the log.

  • You have been or will be prompted by the system to do a backup, but you do not wish to do one. (Performing a dummy backup in this situation is only really acceptable in a development environment, and where the data is non critical.)

The dummy backup utility performs no database checks at all. So, if using the operating system to back up your system to, for example, a tape streamer, run an integrity check as well. (Note that the standard DP4 backup utility does a partial integrity check when you use the -TAPE command tail.)

Two different utilities can perform a dummy backup:

  • DYNABACK -SETSESSN can perform a dummy backup while the database is in use.

  • SETSESSN is the original dummy backup utility. It requires exclusive access to the database. It also has some special command tails that can modify its operation.

To perform a dummy backup:

  1. On the Database Backup, Recovery & Check menu, select the Dummy backup option. Prior to release 4.621 this option invokes SETSESSN. From 4.621 it invokes DYNABACK −SETSESSN.

  2. Select the database.

  3. You are asked whether you wish to delete the transaction log file. Answer 'Yes' to delete it. Answer 'No' to keep the log.

The database session number has been incremented. If there was a log file, the log file session number for new transactions has been incremented. If you are using SETSESSN this causes a problem where you did not choose to delete the log file, in that you are prevented from updating the database until you have manually renamed or deleted the log file. This problem does not arise when you use DYNABACK -SETSESSN.

Modifying the Dummy Backup Operation

Several command tails exist to modify the dummy backup operation. For example, if the dummy backup is part of a batch process and you want to suppress output to the screen, you should run SETSESSN with the -SILENT command tail. All the SETSESSN command tails are described below:

Command Tail | Description
-DELETE | Deletes the log file
-NODELETE | Retains the log file
-SILENT | Suppresses screen output
-NOINCSEQ | Does not increment the backup sequence number
-DBCHECK | Performs a dummy integrity check

Dynamic Dummy Backup

Alternatively, a dummy backup can be achieved with the dynamic backup utility as follows:

On the DP4 System menu, select the Run program option

Use this command to perform the dummy backup:
DYNABACK -SETSESSN [-DELETE|-NODELETE]
where -DELETE is optional and deletes the transaction log, and -NODELETE is optional and renames the transaction log to <DATABASE_NAME>.LBK

The database session number has been incremented. If there was a log file, the log file session number for new transactions has been incremented.


Index Link Analysis

Index link analysis generates the links between the tables in the data dictionary.

  1. On the Database Creation/Changes menu, select option 5 - Makelink/Setlink:

    DP4 performs an index analysis on your database structure in this order:

    • Tables are checked for missing and duplicate keys.
    • The field positions within tables are set and data independence records created.
    • Old index records are marked for deletion.
    • Index records are created for the primary index of each table.
    • Index records are created for secondary indexing. Secondary indexes are created for fields that are the primary keys of higher tables. For example, the CUSTOMER_NUMBER, which is the primary key of the CUSTOMER table, is a secondary key in the ORDER_HEADER table. This allows ORDER_HEADER records to be indexed in customer sequence.
    • Index records are created for indirect joins. These are links between indirectly related tables: child to grand parent, child to great grandparent and so on.
    • Obsolete index records are removed.
    MAKELINK creates a log file which will list any new links that have been created or old links that have been removed.

  2. When the Index Analysis is complete MAKEDB will display a menu allowing you to view the MAKELINK log, run the Index Scrutiny program, SETLINK, or return to the MAKEDB main menu. You should always run SETLINK when you first create a database, or whenever you have added new tables or indexes later on, or have modified the keys of an existing table.


Physical Index

Physical indexes do not hold keys but reference the physical position of records on the disk. They are not used from release 4.622 as they no longer offer any performance benefit over the primary index.


Playing Back Demonstrations

  1. Restore any files your demonstration updates to their initial state

  2. On the operating system command line, load the Redirect utility with this command:

    RED<x> -READ <drive:path:filename> <program name>

    For <x>, enter the identifying character for your operating system (2 for MS-DOS, 3 for UNIX, 5 for FlexOS, 7 for OS/2 and w for Windows)

    For example: REDW -READ custenq.red DP4 loads Redirect and the DP4 Menu system into memory. The keystrokes are read from the file CUSTENQ.RED in the current directory.

  3. Use the following keystrokes to control the demonstration:

    Key Action

    <Esc>

    Exit the Redirect playback at any time. With 4.520/4.616 or later, if you specify the command tail −safemode when you start the redirect program, a menu appears with options to:

    1. Ignore <Esc> and continue demonstration
    2. Ignore rest of file and continue running program manually
    3. Exit program
    <−> Reduce pause time

    <+>

    Increase pause time

    <Enter>

    Delay for one minute within a pause

    <Space bar>

    Delay as long as the key is held down within a pause

    You can hit the Escape key at any time, but the other keys mentioned above only work when the program is waiting at a pause.

  4. If there is a difference between the maps, fields and options referenced in the Redirect file and the program to which the file is played back, the Input redirection error popup is displayed:

    Input redirection file error at line n
Expected map   10200 field      1
Found    map       6 field      1

Select corrective action

1   Enter data from screen
2   Try next entry in file
3   Ignore rest of file

Enter option required

    This message usually highlights the difference between two systems (for example, where an option or field has been added).

  5. Enter 1 to select the next option manually or 2 to try the next option in the file (usually you should only do this immediately after using option 1 to correct the previous input)- if the redirect program encouters a wrong input field input it will not move onto the next input until you choose to do so. This caters for the situation where an additional input field has been added to a screen since the demonstration was created. If the keystrokes in the Redirect file are re-synchronised with the options and fields in the application, the demonstration continues.

    Playing back demonstrations can be a useful means of testing a new release of a DP4 system. If nothing else it is a good way of identifying parts of the system that have changed. If you use REDn for this purpose you will sometimes not want to know anything except which files can be played back. You can use the −testmode command tail to suppress the Input redirection error popup. With this command tail, if an redirection error occurs the program is terminated with System Error 162 and REDn exits with an error code of 3.

  6. The Redirect playback completes and you are returned to the command line.


Point of Sale (PoS)

An application that processes sales and other transactions on a PoS terminal.


Post

The action of sending information to the database.


Posting a Menu

  1. Edit the extract file and change the database name on the first line to the destination database name, for example:
    #DB salesord

  2. On the Other Standard Facilities page of the DP4 System menu, select the Screen map facilities option

  3. On the Map Facilities menu, select the Post text files option. The Map Posting utility screen is displayed

  4. Enter the name of the extract file. The menu page or pages are posted to the destination database


Posting Maps to a New Language

When all the required changes have been made to the translation file, it can be posted back to the database using MAPPOST. A typical command line to invoke MAPPOST in this context would be something like:
mappost xlat.prn -quiet -file error.prn
(You will almost certainly want to refer to the error log mappost creates, because it will be rare for a translator not to have made at least one or two slips that prevent the file being posted back without errors.). Refer to MAPPOST in the DP4 Quick Reference for more information about the command line options for MAPPOST.

The translation file is posted in the same manner as normal 'mappost' files.

As the translation file is processed, for each map specified, MAPPOST accesses the current map which is defined for the base language (defined by the #translation line) and creates a version of that map for the new language (defined by the #language line), by merging the properties of the base map and the translation. The details are different depending on whether you are using release 4.5xx or 4.6xx

Release 4.6xx

No map properties are taken from the translation file. The new map will use the properties of the base map, including size.

For each control within the base language map, MAPPOST expects to find a corresponding control in the translation file:

  • All values other than the control's text and mnemonic are copied from the base map control, including position and width.

  • If a control is in the base map and not in the translation file, a warning message will appear and that field will appear in the translated map with the base text.

  • If a control is found in the translation file which does not exist in the base map, a warning message will appear and this translated control will be discarded by MAPPOST and not be put in the new map.

Release 4.5xx

  • Map text, field positions, widths and mnemonics are taken from the information contained in the translated file.
  • All other properties of the map are taken from the base language copy of the map.
  • Differences in the size of maps, or the position or size of fields in them are written to the error log.
  • Additional fields in the file are ignored, but errors recording this are written to the log.
  • Missing fields in the file are copied from the base language. Appropriate errors are recorded.

When processing has finished, MAPPOST will terminate as normal.


Posting the Database Structure File

Posting the database structure from an extract file (created by the Data Dictionary Extract and Post utility) to the target database is the last stage in transferring the database structure to a database on a remote system or in merging database structures.

  1. On the DP4 System Main menu, select the Database management option

  2. On the Database Structure Management menu, select the Dictionary extract post option

  3. Enter the name of the target database

  4. On the Data Dictionary Transfer menu, select option 2 - Post database structure file. DP4 prompts you to enter the name of the export file

  5. DP4 posts the extract file to the target database. While the export file is being posted, DP4 displays the export file on screen, and shows the name of the table currently being processed. After doing this, the Data Dictionary Extract and Post utility re-generates the index links and displays these messages:

    Checking fields
    Setting field positions and marking data independence records
    Removing details of changed tables and fields
    Marking old index records for deletion
    Creating index records for prime index
    Creating index records for secondary indexing
    Creating index records for indirect joins
    Removing obsolete index records

    The Data Dictionary Extract and Post utility terminates and you are returned to the Database Structure Management menu

If DP4 indicates that the database independence mechanism has been invoked due to the structure transfer, it is a good idea to run a database reorganisation, and this will be forced if keys used in indexes have changed. To see the action recommended by DP4, run the Database Integrity Checker, select Option 1 - Status display and examine the Database status field. If you run REORGDB select option 3.


Precision

Enter the number of decimal places to display. For example, for monetary values, set this option to 2


Prefix length

The length of the prefix. By default the symbol appears immediately before the number, for example "£10". To separate the symbol from the number make the Prefix length longer than the symbol


Prefix

The unit symbols, e.g. "£"


Preserving Data Independence

The following table describes how changes to the database affect data independence:

Modification Type Independence Rules

Adding tables

Tables can be freely added to the database without affecting existing programs

Deleting tables

Tables can be deleted without affecting existing programs provided that Programs do not access that table, or use it as an index to another table.

The database will need to be reorganised if the table contained data, or indexed a table containing data.

Adding a field

Non-key fields can be added without affecting existing programs. If a key field is added to a table that contains data, the database must be reorganised, and programs that access the table will need to be modified.

Deleting a field

Non-key fields can be deleted. Programs written before the deletion will 'see' a dummy value. For C, D and U fields, the null value is seen.

Changing the field description

The field description is the data type, occurs, length and scale factor of a field. The rules for data type conversion are shown in the section on Data Type Conversion. Character fields reduced in length are truncated. Character fields increased in length are padded with blanks

If the field description of a key field is changed, programs that access the table will probably need to be altered and recompiled.

If the field description of a non-key field is changed, existing programs do not need to be recompiled. DP4 will convert the value to the format accepted by the program.

Changes to the key

In theory programs do not need to be recompiled. In practice programs will need to be altered and recompiled in this situation.

The database will need to be rebuilt if there is data in the table, or the table indexes a table that contains data.


Prevent Change

See Check Change.


Prevent change

Answer 'Yes' to prevent the key fields that identify the parent from being changed. For example, prevent the customer for an existing order from being changed


Primary Key

Each record in a table has a unique primary key. At the database design stage, you select the fields that identify the records. You may need to introduce new fields to ensure record uniqueness. For example, in a customer table, the customer name is not guaranteed unique, but a numeric field CUSTOMER_NUMBER can be introduced to act as the primary key.

You can define a multi-part key, composed of up to 99 fields. The ORDER_LINE table has a two-part key:

The precedence of the key fields is shown by the number on the left. In the ORDER_LINE table, ORDER_NUMBER is the first part of the key and takes precedence over the LINE key field.

The MATERIAL_CODE field is marked with an asterisk. This indicates that this field is a secondary key. Secondary keys define a link to another table.


Primary Key

One or more fields that are used to select the records in a table.


Prime/Primary Parent

A table has a prime parent when the keys of an immediate parent table appear as the first part of the key of the child table. For example, the ORDER_NUMBER table which has the key ORDER_NUMBER is the prime parent of the ORDER_LINE table which has the key ORDER_NUMBER+LINE.


Print <F5>

Prints the table plan


Print options

Brings up the 'Select output devices' dialog, allowing you to control the output of Dynachek. By default the output is only directed to the screen


Printing the Data Dictionary

The DP4 Data Dictionary Print utility (PRINTDB) allows you to print out selected details of database tables.

  1. On the DP4 System Main menu, select the Database management option

  2. Select the Print data dictionary option. Select a database

  3. A popup menu appears from which you select the data dictionary information you require:

    Click on the appropriate areas of the illustration for further details of the options.

  4. Select the combination of output devices to which you wish to print

  5. Press <Enter>. DP4 prints out the Data dictionary information you requested

To cancel the printout before it is complete, press <Ctrl+S>, <Ctrl+C>


Printing the Database Structure

This section tells you how to use the DP4 Table Planner utility (TABLEPLN) and the Database Dictionary Print utility (PRINTDB). It is divided into the following topics:

  • Using the Table Planner

  • Editing an Existing Table Plan

  • Including Base Dictionary Tables

  • Printing the Data Dictionary


Printing the User List

  1. If you are using release 4.621, in the Configuration Utilities menu select the User and Menu Management option. If you are using earlier releases, select Other Facilities, and then the Menu Facilities option.

  2. Enter the name of any database on your system. Enter 0 for the language. The Menu Editing Main menu is displayed.

  3. Select option 5 - Users table.

  4. On the User List menu, select option 2 - Print user list.

  5. Select the print destination. If you print to a file, the information is stored in a format that can be posted to another database (use the method described in Posting a Menu if you want to do this).

  6. Press <Esc> several times to return to the DP4 System menu.


Problems with Demonstrations

Unexpected Playback

Users sometimes encounter a problem where programs attempt to run in demonstration mode even when REDn is not in use, and ask how this situation arises and how to recover from it.

The problem arises when a machine is switched off, rebooted or crashes while a demonstration is running. All the REDn program actually does is to set special flags in USERDATA.SYS which tells the terminal manager to run in demo mode. It sets the flags before running the program, and clears them when the program terminates. If REDn terminates abnormally (usually for one of the reasons mentioned above), then it does not have a chance to clear the flags and so all programs will attempt to run in demo mode.

There are two ways of avoiding the problem:

  • If you ran REDn from a directory other than the one where your master USERDATA.SYS file is stored, simply delete any local USERDATA.SYS or MENUDATA.SYS file.
  • Running USERDATA to recreate USERDATA.SYS will clear the special flags. So if your master USERDATA.SYS file has been updated by REDn, use USERDATA -SAMEKEY to clean it up.


Processing

Use this option, first of all, to specify the name of the field to be imported, its position and width in the import file together with any processing of the field value on import


Prompt character

This is normally left blank but you can display a character to prompt that the system is ready for input. It is intended for systems that have no other means of indicating this


Pull-down Menu

When an option is selected from an action-bar, a pull-down menu may appear. This offers the user a further set of options associated with the action-bar option.


QA Build

A fourth-generation programming environment for use with DP4 applications.


Quick check only

Disabled by default. Enable this option if you do not want every fetched record to be refetched using an exact match search on its primary key


Radio Buttons

Under Windows, radio buttons present two or more mutually exclusive options. You pick one of the options by clicking the associated button to highlight it.


Range of Occurrences

Fields in DP4 can have up to 99 occurrences. Occurrences allow multiple lines of data and lists of numbers to be treated as a single field. For example, for an address field, you may allow four lines for an address:

13 Cherry Lane Wheathampstead Hertfordshire AL10 6BP

The address field is defined as a multi-occurs field with four occurrences.

Numeric multi-occurs fields can be thought of as an array or list of numbers. If you wish to store 12 values representing the budget for each month of a year, you define a field BUDGET with 12 occurrences. To reference the budget for March, the programmer writes:

BUDGET[3]


Read and Write access levels

Enter the maximum value of 99 unless you want to restrict the user from accessing certain databases, tables, fields and programs


Read authority level/Write authority level

This question is skipped if you are logged in because it is set in the user list, as described in Maintaining the User List. If running DFSETUP from the command line, enter the default level of authority you want to set in USERDATA.SYS


Read security level/Write security level

Enter a security level between 0 and 99. A value of 0 means that all users can read from and write records to the table


Read security/Write security

Enter the access level required for the user to read the data in the Write security field and write data to the field


Record

A single set of information, including the key fields and data fields, that is stored in a table. A record is represented as a row in a two-dimensional table.


Record "before-images" in transaction log

The purpose of recording before images is to support software written to analyse the log for audit purposes, such as the DP4 AUDITOR utility


Record Locking

The contention control mechanism that ensures an application program cannot access a record that is currently being processed by another application.


Record Sequence and Key Precedence

If an index has more than one key field, the precedence of the key fields is significant. For example, order lines have the key fields ORDER_NUMBER and LINE. The records can be indexed in two sequences ORDER_NUMBER/LINE or LINE/ORDER_NUMBER.

DP4 asks you what you want the key sequence to be. Here, there is a natural precedence: ORDER_NUMBER comes before LINE. Where there is no natural precedence, it does not matter which precedence you choose, but you have the option to control the choice. If you wish to use both indexes, you can do this by defining a second, empty, table with the alternative key sequence.


Recording Demonstrations

  1. Make sure you have made copies of any files, including databases, that your demonstration will change. You will probably need to restore these before playing back the demonstration, because typically your program will require different responses if the data it manipulates is in a different state

  2. On the operating system command line, load the Redirect utility with this command:

    RED<x> -WRITE <drive:path:filename> <program name>

    For <x>, enter the identifying character for your operating system (2 for MS-DOS, 3 for UNIX, 5 for FlexOS, 7 for OS/2 and w for Windows)

    For example: REDW -WRITE custenq.red DP4 loads Redirect into memory, and starts up the DP4 menu system under Windows. The keystrokes are recorded in the file custenq.red in the current directory. You can use REDn to run any program, including a batch file, as long as the only user input will come from DP4 programs

  3. The DP4 program runs. Select options and enter text as normal, but make sure you only use the keyboard as REDn does not support recording the mouse, at least for 4.6xx versions. All the actions that you take are recorded in the Redirect file

  4. Press <Ctrl+B> if you want to pause a demonstration for a short interval, or add a message that is displayed when the redirect file is played back. The Redirect action popup is displayed:

    Redirection for map     10100 field          8

Select action after this field

1   Continue
2   Pause
3   Display message and pause

Enter option required                         3
Enter pause speed required                    3

    The options available are as follows:

    Option Action

    Continue

    Exits from the menu and continues recording

    Pause

    Inserts a pause interval into the demonstration playback. Enter the 'pause speed' as a number from 1 to 9, where 1 gives the longest pause

    The length of the pause is affected by the 'Demo pause time' in the User Preferences. This is set to 120 by default, giving a 12 second time delay for a pause speed of 1, and a 4 second delay for a pause speed of 3
    Display a message and pause

    Enter the 'pause speed', and press <Enter>. You are prompted to enter a message, which can consist of up to 6 rows, 60-characters in length.

  5. Exit the DP4 program. The Redirect program terminates

Recording Demonstrations for 4.6xx Releases

In 4.6xx there are some limitations with the Redirect facility which can make it a little hard to create redirect files. The two main problems are as follows:

  • Mouse clicks and other navigation using the mouse are not recorded at all. When creating a redirect file you must not use the mouse.
  • There are various places where the space bar does not get recorded either. In particular you cannot use the space bar to set the value of a checkbox or radio button field. However a space bar added to a redirect file manually will be played back correctly at least for a checkbox.
  • If you press a button using the spacebar DP4 records the key corresponding to the button, not the spacebar itself. Usually this does not matter.
The easiest way to set checkboxes for a 4.6xx redirect file is to use the - and + keys. You can use - to clear a checkbox, and + to set it. These keys also have the adavantage that they are not toggles, so that the checkbox will definitely be set to the value you want.


Recovering a Database

If a system failure (such as a power failure) occurs, your database will not be closed down correctly. The next time you run DP4, you will be prompted to use the DP4 Database Recovery utility by a message like this:

DP4 secures changes to the database after several updates. The recovery mechanism rolls the database back to the last intact version of the database. The transaction log is then applied to roll-forward the database to the last update secured before the system failure.

  1. On the Database Backup/Recovery & Check menu, select the Recover option. The Roll-back Recovery utility screen is displayed

  2. Select the database name

  3. DP4 displays the message that it is recovering the database and applying the transaction log. Once the process is complete, you are returned to the Database Backup/Recovery & Check menu

After running the database recovery utility, run the database checking utility. If you are using a version of DP4 prior to 4.520 you may find a checksum error is reported, but if this is the only error it is safe to reset the checksum and carry on. However if you are using 4.520/4.616 or later and get any error at all, or if any other error is reported, the database was either corrupt before the crash or the roll-back recovery has not worked.

The database recovery utility is designed for recovering databases after a crash, not for removing corruption from genuinely corrupt databases. These include databases that have reported DP4 System error 87. Origin: Database Corruption

Recovering the System Database

The system database should not normally require recovery, because it is usually opened in read-only mode. However, certain tasks, such as adding or changing device support, or updating the user list, do update the system database, and if a system failure occurs the system database will need to be recovered. In this case you will not be able to log on to DP4 at all, and in the worst case scenario, your DP4 configuration may not start at all.

To recover the system database proceed as follows:

  1. If your normal DP4 configuration will not start, then start DP4 in standalone mode (just the DP4 database manager running). On Windows platforms, if you have modified the congiration file so as to leave no explicit means of starting up DP4 like this you can do this by specifying dummy section names for preload and startup, for example:

    srvw32 -start -startup=invalidsection -preload=invalidsection

    On other platforms, just load SRVn, for example on Unix/Linux srv3, or allow DP4 to auto-load,

  2. To perform the actual recovery, run DBRECOV from a command prompt like this:

    dbrecov -db system -silent

    If you have renamed the system database, then supply the appropriate name.

  3. If the recovery succeeds you will be able to run DP4 as normal once again. In case of difficulty you will need to restore a backed up copy of your modified system database, or if this is not available, you can try renaming the current system database files, and reinstalling the issued DP4 system database, and then retry the DBRECOV operation as though the renamed system database was an ordinary application database.


Recovering and Restoring a Database

A database is recovered or restored from disk for three purposes:

  • To install a database on another system.
  • To recover a database following a system failure.
  • To restore a database if data corruption has occurred.

DP4 provides these utilities for recovering a database:

  • Database Recovery utility (DBRECOV)

    This utility uses the roll-back and transaction logs to recover your database. From version 4.522/4.618 it can also restore the last backup from hard disk and apply transaction log, in the unusual event of a failure in rollback recovery mechanism, provided the DYNABACK -COPY mechanism is used to backup the database.

    The database recovery utility does not correct databases that that have reported a database corruption or failed a database integrity check. In either case, the most recent non-corrupt backup of the database must be restored. From release 4.522 the DYNABACK program has a secure mechanism for backing up the database which can prevent the problem of good database backups being replaced by bad ones, and which will help you discover any corruptions sooner.

  • Database Restore utility (DBRESTOR)

    This utility restores a database from the backup media, and uses the transaction log to roll-forward the database

The following topics are covered in this section:

  • Recovering a Database

  • Restoring a Database
  • Restoring the System Database
  • Restoring Out-of-Sequence Logs
  • Restoring a Database from Tape Streamer


Referential Integrity

You can apply rules to the relationships between tables. The rules specify how an action on one table affects the other tables in the database. DP4 automatically implements the rules when the table is accessed. This can reduce the amount of development time needed for an application program.

The rules of referential integrity are:

Parent Check | Check a parent record exists before inserting a child. For example, check that a customer exists before an order can be created for the customer
Update | Maintain an index link to another table. For example, make sure DP4 updates the link between order lines and materials when a change is made
Prevent Change | Ensure the key fields that identify the parent table cannot be changed. For example, make sure the customer for an existing order cannot be changed
Orphan Check | Do not allow a parent record to be deleted if there are children. For example, do not allow the deletion of a customer for which orders exist
Child Delete | Delete all the child records of a deleted parent. For example, when a customer is deleted, delete all their outstanding orders (order header and order line records)
Parent Verify | Check whether a parent record has been updated since it was last read. For example, make sure the material price is still the same before updating an order line


Referential Integrity

The facility to include rules in the database to ensure that data integrity is maintained.


REGION

Stores details of the UK regions where customers live, including the name of the region and the company employee managing the region. The primary key is a unique code that identifies each region


Relational Database

A database system that organises data in tables and allows record selection using the keys in a table.


Relationships Between Tables

DP4 derives the structure of your database from an analysis of the tables on your database. A parent-child link is derived where you include the primary key of the parent table in the child table.

For example, customers place orders, and there is a requirement to find the order record for a customer. You create a link by including the key of the CUSTOMER table, CUSTOMER_NUMBER, as a secondary key in the ORDER_HEADER table:

The database manager uses this structural information for indexing and referential integrity, and QA Build and Report Writer use it to provide predetermined access paths between records.


Remarks at bottom

Enable this option to position help messages against the bottom of the screen. Disable it to position them below any displayed text


Removing Database Holes

This option has been removed from release 4.622. It is to be replaced by a new "Defragment database" option, which will give most of the benefit of a full database rebuild, but with a faster running time.

The number of small holes in the database data file increases over time, and when deletions and insertions are made in tables that have data compression enabled.

Option 2 - Remove holes from data file combines many small holes in the data file into fewer larger holes, placing these near to the physical end of the file.

We recommend that you enable roll-back logging when you run this option so that you can recover the database in the event of a system crash. However, if you do so, you should be aware that the reorganisation takes about twice the time it would normally take and that a large roll-back file may be created, depending on how much restructuring is done on the data file.

  1. On the DP4 System menu, select the Run program option. Use this command to run the reorganisation with roll-back recovery enabled:
    REORGDB -ROLLBACK
    and select the database

  2. On the Database Reorganisation menu, select option 2 - Remove holes from the data file. DP4 reorganises the data file

You can interrupt this operation at any time by using <Esc> or <Ctrl+S> and <Ctrl+C> or, on Windows, <Alt+F4>

Among the information displayed are counters to indicate how many holes there are in the data file and how many holes have been removed so far

Once the reorganisation is complete, press <Enter> to return to the DP4 System menu. Back up the database, using one of the methods described earlier

Crash Recovery

Should a system crash occur, you can recover the database with the database recovery utility if you enabled roll-back logging with the -ROLLBACK command tail. Otherwise, your only option is to restore the database from a backup.


Reorganising an Index

Use this option to rebuild specified logical indexes, after making a change that affects the index, such as changing an index link to 'update' with the index link editor.

We recommend that you enable roll-back logging when you run this option so that you can recover the database in the event of a system crash. However, if you do so, you should be aware that the reorganisation takes about twice the time it would normally take and that a large roll-back file may be created. During the reorganisation, the roll-back file can grow up to the size of the index file being rebuilt, depending on the amount of index restructuring.

  1. On the DP4 System menu, select the Run program option. Use this command to run the reorganisation with roll-back recovery enabled:
    REORGDB -ROLLBACK
    and select the database

  2. On the Database Reorganisation menu, select option 5 - Reorganise an index

  3. Enter the name of the table that uses the index, or press <Enter> and select a table from a popup list of tables on the database.

  4. Enter the name of the index or press <Enter> and select an index from a popup list.

  5. Enter the role name (if there is one).

    DP4 now rebuild this index. A message is displayed to indicate how many old index entries have been removed and how many new index entries have been added

  6. When the reorganisation is complete, press <Enter> to return to the main reorganisation menu.


Reorganising the Database

The database reorganisation utility (REORGDB) is used for one of three reasons:

  • A structural change requiring a complete reorganisation has been made to the database.

  • The information in the data or index file has become fragmented and requires rebuilding. If DBCHECK shows a data file usage below 90% or index file usage below 60% the database will benefit from being reorganised. As of release 4.622 REORGDB can improve the performance of very large databases enormously. You should consider running REORGDB frequently, especially on head-office databases.

  • An index for a table requires rebuilding because it has been newly switch on using SETLINK.

This section is divided into the following topics:

  • Speeding Up Reorganisations

  • Advanced Database Properties

  • Choosing a Reorganisation Option

    • Reorganising with Compression

    • Removing Database Holes

    • Reorganising with Rebuild

    • Optimising the Index File

    • Reorganising an Index

    • Deleting Application Data

  • Sorting The Database Data File

  • Sorting the Entire Data File

  • Reorganising to Remove Corruptions

  • Reorganising to Remove Deletions


Reorganising to Remove Corruptions

If your only copy of the database is corrupt, you may be able to recover the situation with the reorganisation utility when only the index file is damaged. In this case, you can try to rebuild the database, by running REORGDB with the -CORRUPT command tail, for example:

REORGDB -DB SALESORD -INDEX -CORRUPT

If the problem is confined to a single logical index, you may be able to remove the corruption by reorganising just that index, for example:

REORGDB -DB SALESORD -BUILD table index_table index_role -CORRUPT

You can do this only where the DYNACHEK utility reports no errors in the primary index of the table.

Should the system crash when reorganising, follow the advice given earlier for the particular reorganisation option.

For more information on rebuilding a corrupt database refer toHelp! My Database is Corrupt (and I don't have a Backup)

As of release 4.623 REORGDB -CORRUPT works in a completely different way, and will attempt (usually successfully) to rebuild a usable database even when the original database is severely damaged.


Reorganising to Remove Deletions

DP4 normally reallocates the space in the data file when records are deleted, unless you enabled 'Keep deletions' in the table details option of the database creation utility. The only reason for doing this would be to facilitate the use of the DP4 Software Release and Data Distribution (SRDD) product.

To remove deleted data from a data file with the 'Keep Deletions' flag set, run the reorganisation utility with the -REUSE command tail, as in:

REORGDB -DB SALESORD -DATA -REUSE

-REUSE is only applicable to reorganisation options 1 and 3, which rebuild the data file.


Reorganising with Compression

This option is not recommended for general use, because if something goes wrong while the data file is being processed the database will neeed to be restored from a backup. Option 3 is much safer, because it builds a new data file and only removes the old data file when the new one is known to be OK and secured.

This option is something of a misnomer - it does not truly compress the data file - it merely removes any holes in the data file by moving later records into the space occupied by the hole. The only option of REORGDB that can genuinely improve the data file compression is option 3: Rebuild data and index file. If you have a database without an index file at all, or a hopelessly corrupted index, this option can be used to rebuild the index file without changing the data file in any way, by running REORGDB like this:
reorgdb -db dbname -nosqueeze -corrupt -index
You will need to create a dummy index file, by copying bd.ind to dbname.ind

  1. On the DP4 System menu, select the Database management option. The Database Management menu is displayed.

  2. Select the Reorganise database option and enter the database name.

  3. On the Database Reorganisation Facilities menu, select option 1 - Compress data file and rebuild index

    DP4 displays a message for each stage of the reorganisation. There are several stages:

    • Compressing the data file, which includes indexing dictionary tables.
    • Indexing of other tables
    • Recalculation of the data file checksum.
    • Optimisation of the index file.

  4. When the reorganisation utility terminates, press <Enter> to return to the DP4 System menu. Back up the database, using one of the methods described earlier.

Crash Recovery

If the machine crashes during the 'Indexing other tables' phase, you can continue the reorganisation by renaming an old index file to the index filename (if you do not have an old index file, use one from another database with the same numbering system and generation number equal to or less than the crashed database), and restarting the reorganisation from the Run program option of the DP4 System menu:
REORGDB -CORRUPT
If the machine crashes before the 'Indexing other tables' phase, your only option is to restore the database from a backup.


Reorganising with Rebuild

  1. On the Database Reorganisation menu, select option 3 - Rebuild data and index files. DP4 displays a message for each stage of the reorganisation. There are three main stages:

    • Rebuilding the data file, and indexing of the dictionary tables. The details of this stage are rather different in release 4.526/4.622 from earlier DP4 releases:

      • Prior to release 4.622, or if the -CORRUPT command tail is specified, REORGDB builds the new data file by reading the original data file and copying records from the old data file to the new file, making any necessary changes due to data independence, or changes to the setting of the encryption/compression flags for the table. If the -ALTSORT command tail was specified the data is partially sorted, so that records that are close together in the primary indexesd are arranged sequentially in the data file. This can dramatically improve the performance of the database. The -SORT command tail arranges records so that records from related tables are grouped (so that, on SALESORD for example, each order would be followed by its order lines). Tests on very large DP4 databases indicate that the -ALTSORT command tail gives all round best performance.
      • From release 4.622, REORGDB builds the new data file by using the existing index to fetch all the data records in order, thus building a perfectly sorted data file. In addition the data in the table is analysed to see if the records would benefit from being being compressed. If enhanced data compression is enabled a more complicated analysis of the data is performed, so that the table can be compressed as efficiently as possible. Unfortunately this means that each table must be read from the existing database twice, which can take a long time if the original data file is badly fragmented.
    • Rebuilding the index file. This may be done in up to four passes: Two to build the primary indexes, and two to build secondary indexes. Creating the primary and secondary indexes separately is actually quicker than doing all the work in a single pass except for small databases. Up to release 4.621 REORGDB uses two passes for each phase of the indexing indexing only part of the data each time because this usually results in a smaller (and hence more efficient) index. This is no longer necessary in 4.622, because the index optimisation has been enhanced and always builds an optimal index file.
    • Optimising the index file, during which the number of index nodes to process and the number of nodes processed so far are displayed. This stage is usually fairly quick, and will not take more than an hour or so for even the largest databases. As already mentioned, as of release 4.622 this phase also ensures that the index is made as small as possible.

  2. When the reorganisation utility terminates, press <Enter> to return to the DP4 System menu. Back up the database, using one of the methods described earlier

Crash Recovery

If the machine crashes before the 'Indexing other tables' phase, you can continue the reorganisation by renaming an old index file to the index filename (if you do not have an old index file, use one from another database with the same numbering system and generation number equal to or less than the crashed database), and restarting the reorganisation from the Run program option of the DP4 System Main menu:
REORGDB -CORRUPT
If the machine crashes in the 'Indexing other tables' phase, the original data file has been replaced by a new file called <DB_NAME>.$$D. Rename this file to <DB_NAME>.DAT, copy an old index file to <DB_NAME>.IND and restart the reorganisation with REORGDB -CORRUPT. By now the data file has been rebuilt, so select option 1 from the Database Reorganisation menu; it will be faster than option 3.


Replace existing records?

Answer 'Yes' to replace records that are already on the table on the target database. Answer 'No' to prevent existing records being updated


Report Writer

A report writing environment for defining and running reports on the data in a DP4 database.


Reset memory-resident option in database manager shutdown

Set the database manager to close down when all DP4 programs terminate. Use this option if you have loaded the database manager to remain in memory, and you want to release the memory back to the operating system


Restoring a Database

You can use the database restore utility to restore backups, held on disks or tape, made with the database backup utilities, DBBACKUP and DYNABACK. This utility also allows you to apply transaction logs to the database for roll-forward recovery.

  1. Make sure you have the disks that contain the database backup

  2. On the Database Backup/Recovery & Check menu, select the Restore option. The Restore and Recovery utility screen is displayed. Select the database

  3. If you are restoring a database that is not already on the system, the Restore Database menu is displayed:

    If the database is already on the system, DP4 displays the Restore Database menu with transaction log options:

  4. Select option 1 - Restore database from backup disks.

  5. You are asked whether you want to verify the database to be restored:

    Answer 'Yes' to make sure the database is written to disk properly. Answer 'No' to skip the database verification. Answer 'Abort' to return to the previous menu.

  6. Load the first backup disk, as requested, and press <Enter>. DP4 locates the database on disk and displays the database information:

    Backup of session 4 made on 21 May 1998

    Comment SALESORD, Session 4, 21/05/98.

  7. Confirm that you have the right backup. If you do, press <Enter> to continue with this disk. If you do not, answer 'No' and try another disk.

  8. If the database already exists on your system, you are asked whether you want to replace it:

    Answer 'Yes' to restore the database. Answer 'No' to return to the previous menu

  9. If the database is stored on more than one disk, DP4 prompts you to enter each disk in turn.

  10. The Transaction Log Recovery menu is displayed:

    If the backup was made in the standard way with DBBACKUP, select option 1 - Transaction log recovery. You are prompted to insert the transaction log disk for a specific session number:

    Load the appropriate disk and press <Enter>
    If the backup was made using DYNABACK, select option 3 - Extract log from dynamic backup.

    You are prompted to insert the disk containing the dynamic backup. Load the appropriate disk and press <Enter>

    If you have mislaid the transaction log that DP4 requests, but have others that you wish to apply, refer to Restoring Out-of-Sequence Logs.

  11. Once the transaction log is applied, you are returned to the Transaction Log menu. If you have further logs to update the database with and they are in sequence, use option 1 - Transaction Log Recovery or option 3 - Extract log from dynamic backup to apply each of them in turn. The last log that you must apply is the current log:

After restoring a database it is recommended that you run the database integrity checking utility against it to ensure that you have a good database and complete working system.


Restoring a Database from Tape Streamer

This section tells you how to restore a database that has been backed up to tape using the standard database backup utility:

  1. Create a batch file in the current directory, or in a directory that is included in your path, named as follows:

    • TAPERST.BAT for DOS, Windows and FlexOS
    • TAPERST.CMD for OS/2
    • taperst for UNIX

    Include the instructions that restore the database files from the tape streamer to your workstation. To make the batch file generic to any database, use the variable %1 in place of the database name.

    DP4 substitutes the name of your database in place of the variable when you run the database restore utility.

  2. On the DP4 System menu, select the Run program option.

  3. Type the command line:
    DBRESTOR -TAPE
    and press <Enter> to execute it.

  4. The Database Restore utility screen is displayed. Select the database. DP4 prompts you to press any key when you are ready to restore the database.

  5. The Database Restore utility runs the batch file TAPERST.BAT. The database name is substituted in place of the variable %1 in the batch file.

  6. DP4 asks you if the database has been successfully restored. If you answer 'No' to this question, DP4 repeats the restore procedure. Answer 'Yes' to continue with the restore process.

  7. The Transaction Log Recovery menu is displayed, and you are prompted to restore the transaction log as described earlier in Restoring a Database.

After restoring a database it is strongly recommended that you run the database integrity checking utility against it to ensure that you have a good database and complete working system.


Restoring Out-of-Sequence Logs

You may need to restore out-of-sequence logs, for example if you have restored an old backup and one or more of the log files is missing. Doing so involves applying a log file different to that requested by DP4.

Using this option creates an inconsistent database. After restoring the out-of-sequence logs, check the database and, if possible, recreate the missing transactions.

Before you begin, make sure you have the disks that contain the log backups.

  1. On the Database Backup/Recovery & Check menu, select the Restore option. The Restore and Recovery utility screen is displayed. Select the database.

  2. The Restore and Recovery menu with transaction log options is displayed.

  3. Select option 4 - Out of sequence transaction log recovery. The Log Recovery dialog is displayed:

  4. Enter the number of the Transaction log to be recovered. For example, if the Next transaction log should be 16, but 16 and 17 are missing, enter 18. If you don't know the number (which really shouldn't happen if you are following proper procedures), enter any number for the time being.

  5. As requested, insert the disk holding the transaction log and press <Enter>. The log is applied to the database and you are returned to the Restore and Recovery menu. If the disk does not contain the expected log a warning is displayed with the log number that it does contain, if any, so you can return to the previous step and enter the the number for the log this disk does contain, assuming you really want to restore this log.

    If you have further logs to update the database with, use option 1 - Transaction Log Recovery or option 4 - Out of sequence transaction log recovery to apply them. Finally, run the database integrity checking utility on your database.

    It should hardly need stating, but you should always apply what log files you have in ascending order, and you should never apply a log file that is older than the backup that has been restored.


Restoring the System Database

The system database cannot be restored from within the DP4 menu system because this application makes use of the system database.

The following procedure shows you how to restore the system database in Windows. In other operating systems, the procedure is more straightforward, in that you can run the DP4 utilities from the command line.

  1. The backup of the system database supplied by Itim Technology Solutions is called SYSTEM. If the SYSTEM database is called another name in your DP4 configuration, use the SYSDB utility to set the name to SYSTEM. For example, in Windows, select the Run option of the Start Menu or the Program Manager's File menu and type, for example:
    C:\DP4\WIN\SYSDB SYSTEM
    When SYSDB terminates, stop the DP4 service.

  2. Use the Run option of the Start menu or the File menu to restore the system database, as in:

    C:\DP4\WIN\DBRESTOR -SYSTEM -DISK A:
    where -DISK A: optionally specifies the drive that holds the backup. If you are installing from the DP4 CD-ROM you can specify the disk like this -DISK D:\616\WIN32\ , replacing the drive letter,version, and operating system as appropriate. Note the trailing back slash character. Alternatively you can rerun the DP4 Install program and choose to install just the system database.


Role

See Field Role.


Role name

Enter up to 16 characters for the role name, or press <Ctrl+P> for a list of the existing roles on the database. See Field Roles for an explanation of roles


Roll-back Log

This stores the images of data before changes are made to the database.


Running a DP4 Utility

Load a DP4 utility by specifying its program name on the command line. For example, this is the command to load the DP4 Data Entry and Enquiry utility:

BROWSER

Use the DP4 utility command tails to restrict access to the database where this is useful in your application. For example, this command loads the Data entry and enquiry utility to update the CUSTOMER table on the Sales Order database:

BROWSER -DB %DB -TABLE customer

Consult the DP4 Quick Reference Guide for a complete list of utilities and their command tails.


Running a Non-DP4 Program

Run a non-DP4 program by entering the command you would normally enter at the operating system command line. Include any paths that are required to locate the program.

Use the Menu command tails to control how a program is run. For example, you might load a non-DP4 program such as a word processor with these command tails:

WORDPROC %1 %SYSTEM

The command tail %1 indicates that the filename is to be supplied at run-time, and, on MS-DOS, %SYSTEM terminates the database manager and user interface manager to increase the available memory.

Remember to use the %CLOSEDB option when running programs such as a tape backup utility, and the %BATCH option when running batch files or shell scripts


Running a QA Build Enquiry Program

  1. On the DP4 System Main menu, select the QAB facilities option

  2. From the QA Build menu that is displayed, select the Interpreter option. The QA Build Interpreter screen is displayed and you are prompted to enter a database name. Type SALESORD, then press <Enter>. You are prompted to enter a program name:

  3. Press <Ctrl+P> or click on the prompt button to pop up a list of QA Build programs on the database

  4. Use the cursor keys to move between the programs. Highlight PROG3, and the program description "Customer number enquiry" is displayed

  5. Press <Enter> to select and run the program PROG3. This is a simple program which displays a selected record on the CUSTOMER table. The first screen of the program is displayed. The cursor is positioned in the CUSTOMER NUMBER field:

  6. Enter the number 1, and press <Enter>. The details for customer 1 are shown. Press any key when you have finished viewing the record. On the customer number field, enter any existing record number (between 1 and 60) and DP4 shows the customer details

  7. Press <Esc> to exit the program, and return to the DP4 System menu


Running a QA Build Menu Program

  1. Run the QA Build interpreter as described in Running a QA Build Enquiry Program. Run the PROG9 program. This is an example DP4 program with drop-down menus that gives access to the example update, enquiry and report programs

  2. Press the cursor keys <←> or <→> to move between the items in the menu bar. Press <Enter> to make a menu drop down, and use the <↑> or <↓> cursor keys to move between the menu options. Press <Enter> to select a menu option. DP4 runs the program associated with the menu action

    Press <Esc> to return to the menu program, and select the Exit option to return to the DP4 System Main menu. Press <Esc> several times to return to the DP4 System Main menu


Running a QA Build Update Program

  1. Load the QA Build interpreter as described in Running a QA Build Enquiry Program

  2. Select and run PROG2. This is an example DP4 program that updates a database table. The program screen is displayed. The cursor is positioned in the first entry field

  3. Enter an existing customer record number (between 1 and 60) and press <Enter>, and DP4 displays the customer record

    You can take one of three actions when a record is displayed:

    • Use the cursor keys to scan through the existing customer records, in customer number sequence
    • Press <Esc> to enter a specific customer number
    • Press <Enter> to display the Amend/Delete/No action Record dialog:

    • Select Amend to modify the customer details. DP4 positions the cursor in the first non-key field, in this case, the customer name field

    • You can navigate round the fields on the screen using the keyboard or mouse.

    • Amend the customer details, then press <Enter> on the last field. When DP4 prompts you to save the changes, select option 1 - Save changes:

    • To delete a customer record, enter an existing customer number and press <Enter>. Select option 2 - Delete. Answer 'Yes' to confirm the changes

    • To add a new customer record, press <Enter> to accept the next free record number. The Insert/Search/Rekey menu is displayed:

    • Select option 1 - Insert, enter a new customer number and address, and fill in the credit and balance fields. For the 'Sales region' field, press <Ctrl+P> to pop up a list of region codes. When you complete the screen, DP4 displays the Save/Rekey/ Abandon Changes menu:

    • Select option 1 - Save changes

    • Press <Esc> to return to the QA Build Program Writer


Running a Report Writer Program

  1. Report Writer programs are executed using REPPRINT. To access this via the DP4 Main menu, select Import/Export and Reporting and then Run Report and Export Programs, or prior to 4.621 select Other facilities, then select Report Writer facilities, and finally Run programs. The Report Printer screen is displayed. Enter SALESORD and press <Enter>. The Report Writer Data Processing screen is displayed:

  2. Press <Ctrl+P> to pop up a list of programs on the database:
    Use the cursor keys to highlight the report called LISTING, and press <Enter>. A list of material codes and their descriptions is displayed

  3. Once the report is complete, the Report Writer menu is re-displayed. Press <Esc> to return to the DP4 System Main menu


Running an Application Program

Load an application program by entering the executable name, or running the DP4 utility such as Report Writer or QA Build that is required to load the program. For example, an executable program to output customer labels to a dot matrix printer might be run as follows:

PRINTLBL -DEVICE DOT_MATRIX

Run a QA Build program using the command tails to specify the database and program name. For example:

PROGRUN -DB %DB -PROG maint


Running Maintenance Programs From Batch Files

You may choose to run some DP4 maintenance programs from batch files. It is useful to know, therefore, that when DP4 programs such as DBCHECK, DBBACKUP and REORGDB terminate, they return an exit code to the operating system. This exit code is a number between 0 and 5 that indicates the completion status. An error code of zero indicates that the DP4 utility completed successfully. Any other value indicates an abnormal termination. The meanings of the return codes are given in the following table:

Return Code | Meaning
0 | The utility completed successfully
1 | The utility terminated abnormally
2 | The user interrupted and terminated the utility abnormally (e.g. with Alt+F4 on Windows)
3 | The utility terminated with a DP4 system error
4 | The utility terminated with a DP4 fail error
5 | Serious configuration problem

These values can be tested in batch/script files, for example in DOS/Windows/OS/2 by using the IF batch command with the ERRORLEVEL parameter. This allows batch file processing to be altered based on the exit code returned from the DP4 utility.


Running the Database Creation Utility (MAKEDB)

  1. On the DP4 System Main menu, select the Database management option. The Database Structure Management menu is displayed:

  2. Select the Create/modify database option

  3. Enter up to 8 characters for the database name, and answer 'Yes' to confirm that you want to create a new database

    The Base Dictionary (BD) is copied to this name after the required number system is selected in the following section.


Configuration Files

The DP4 Cofiguration files are as follows:

  • System database, SYSTEM.*

    The system database defines the information that is common to all the applications on your DP4 system, and is created as part of the installation process. The system database files ( SYSTEM.DAT and SYSTEM.IND, and also SYSTEM.DCI and SYSTEM.PTR from release 4.622) are generally located in the same directory location as your application database. A system database must be present for your DP4 system to operate (except on machines configured as network clients with no local database).

    The system database contains the following system specific information:

    • The user list, including the password and access levels for each user
    • The language table
    • The printer and device characteristics

    The System database also contains all the maps used by the standard DP4 utility programs and the System menu (initiallydisplayed when you log on as DP4)

    You may choose to edit these maps and menus on the end-user copy of the system daabase to change or restrict the faclities available to end users.

  • DP4 Licence file

    The licence file defines your licence details and the location of DP4 files on your system. The licence file, named DP4.sys from 4.620 or DATAFIT.SYS in earlier releases, is supplied on your DP4 licence disk. This file must be present for your system to work properly. It is modified by the DP4 System setup utility and the System database utility. The file stores the following information about your system:

    • Your licence details
    • The system database name
    • The location of files on your system (including the database files, backup files and print files)

    • The transaction, roll-back and console log options for your system

  • The DP4 Configuration file

    The DP4 configuration file contains many important details about your DP4 installation. From version 4.620 it is usually named DP4.INI and is installed in the same directory as your DP4 executables. In earlier releases this file is only used in the Windows version of DP4, is named DATAFIT.INI and is stored in the "Windows" directory.

    For full details of the settings contained in this file refer to the Guide to DP4 Configuration.

  • User data file USERDATA.SYS

    The user data file defines your workstation monitor type, the user preferences and the key translation table on your system. It is created by the USERDATA utility program during the installation process. It is modified by the DP4 System setup utility (DFSETUP). It stores defines the following information about your system:

    • The characteristics of the monitor used by your workstation
    • The key translation table

    • The user preferences, including the date and time and colour preferences

    You can maintain separate copies of USERDATA.SYS by placing one copy in each user directory. Alternatively, you can maintain one copy that is shared by all users by placing the file in the licence directory.

    MENUDATA.SYS is a temporary file that is created when a user logs on to the DP4 system. The MENUDATA.SYS file is deleted when the user exits. Occasionally a MENUDATA.SYS file may be left after a system crash. This file can safely be deleted.


Saving Importer Programs

When you have defined the Importer program, press the <Exit> key repeatedly until the Save/Abandon Changes menu appears.

Select option 1 - Save and the Importer program is posted to the database. You are returned to the main screen of the Importer.


Scale Factor

An integer that determines how the field is stored. The field is stored as 10 to the power of the scale factor times the field value, and is displayed as the field value divided by 10 to the power of the scale factor. The aim is to store the number as an integer to eliminate precision rounding errors. You are advised to make the scale factor equal to precision in normal circumstances.


Scale factor

For a floating point number, enter a scale factor. Unlike the Precision option, the scale factor affects how DP4 stores the data. Make sure you scale the field value to the appropriate power of 10 to avoid rounding errors (normally, you set this to the same value as the Precision). If you are using C, Pascal or COBOL, consult the supplied Developers Manual for further information


Screen refresh

This question is skipped, unless running DFSETUP from the command line. You can choose one of three options to control how the screen is refreshed:

  • Fully buffered.

  • Partly buffered.

  • Immediate update.


Secondary Key

A key other than the primary key that is used for indexing purposes.


Security Levels

In DP4, users are assigned read and write access levels from 1 to 99, where 99 assigns the highest access level. To prevent unauthorised access, minimum read and write access levels can be set for fields, tables and the database itself.


Selecting a Menu Page

  1. Menu Pages are worked on using the MENUEDIT utility. To access this via the DP4 menu, select Configuration Utilities and then the User and Menu Management option, or for releases up to 4.620, select Other Facilities and then the Menu Facilities option.

  2. If you are creating or editing an application menu, enter the name of the database. If you are modifying the System menu, enter the name of the system database

  3. Enter 0 for the language number, unless you are developing a multi-language application. The Main Editing menu is displayed

  4. Select option 1 - Select page. The Menu editor prompts you for the number of the page:

    • To create a new page, enter a new number (1 if you are creating the first menu), and answer 'Yes' to the prompt to add the page to the database
    • To edit an existing page, press <Ctrl+P> to select from a pop up list

    The Map Editor is launched showing the selected menu (which will be blank if it is a new one)


Selecting the Database Number System

  1. DP4 displays the development environment/number system list:

    Select the development environment or number system suited to your development environment

    If you do not see your development environment in the list, select either option 3 - Other language, or option 9. Both store numbers using the IEEE standard for double-precision floating point numbers, but they differ in how data is aligned. Select option 3 if you intend to develop your application programs using the QA Build, Report Writer and SQL/ODBC environments and your database will only be accessed on PCs. Select option 9 if you intend to program using DP4DYN in Visual Basic or another language that enforces alignment restrictions, or if you want to access the database directly on non Intel machines such as an IBM RS6000 or Sun Solaris.

  2. If you chose option 3 or 9 for the last step, you are prompted:

    The default is 'No', which you should accept if you intend to develop in QA Build, Report Writer, Microsoft C or SQL

    Answer 'Yes' if you intend to use a programming language that allows less than 16 unique characters in a name. You are prompted to enter the number of characters that must be unique in table, field and role names

    When you complete the programming language selection, DP4 displays the Database Creation/Changes menu:

If you see the following warning, the database will not be created properly:

To correct this problem, you should run setsessn -db bd, and setsessn -db bd -dbcheck. This warning should not appear with current versions of DP4


Selecting the Language Preferences

There are three ways of specifying the language preferences:

  • System language

    Set up the language preferences that are used for all programs run from the command line without the logon facility. This is described later in Changing the System Language Preferences

  • Language table

    Set up the preferences for all users that have the language selected in their setup. The language preferences are read from the language table when a user logs onto the menu system or application program. The language table preferences are described later in Changing the Language Table Preferences

  • User preferences

    Override the language preferences for a user. The user preferences are read from the user table when the user logs onto the menu system or application program. The user list preferences are described later in Changing the User Language Preferences.

    You will find more information about user preferences in User Preferences.


Selecting the User Language

To select the language for a single user.

  1. On the DP4 System Main menu, select the Configuration Options. The System and User Setup Options menu is displayed

  2. Select the Maintain user list option. The User Set Up dialog box is displayed.

  3. Add a new user as described in Modifying the User List, or press <Ctrl+P> to pop up a list of existing users

  4. For the language number, press <Ctrl+P> to pop up a list of languages on the database and select one

  5. Fill in the remaining details as described in Setting Up a User

    Check that the specified menu page has been added in the selected language. If it has not, the 'missing map' error message will be given when the user logs onto the menu system

  6. Click the OK button to save the changes to the user setup, and press <Esc> several times to return the DP4 System menu


Selection

Colours used to highlight the current menu selection


Sequence number

Enter a sequence number to indicate the order in which fields are processed and displayed. DP4 uses this sequence in the Data Entry and Enquiry and Table Planner utilities


Server

The DP4 environment supports a client/server architecture. The server is the machine on which the database server resides.


Session Numbers

DP4 displays the session numbers of the database and transaction log when you back up or restore the database, indicating:

  • How many backups have been taken of the database.
  • How many backups have been taken of the transaction log.

The database session number is incremented after any type of backup, including transaction-only and dummy backups. The transaction log session number is only incremented when a log is backed up.

Labelling your backup disks with the session number information enables you to ensure that in the event of a non-recoverable database error:

  • The correct database can be restored.

  • The correct transaction log, or sequence of them, can be applied to the database.

To view the session numbers for the last backed up database and transaction log, use option 1 - Status display of the Database Integrity Checking utility.

To reset the database and transaction log session numbers to 1, use option 7 - Version/Security of the MAKEDB utility.

We recommend you run the database integrity check (DBCHECK) as part of the backup process for these reasons:

  • Restoring a corrupt backup wastes a great amount of time.
  • Restoring an older backup than necessary and its associated log files is time consuming and error prone.


Setting Database Security Levels

User access to data can be set at three levels:

  • For individual fields on the Field Details screen, see Adding a Table Field

  • For individual tables on the Table details screen, see Adding a Database Table

  • For the entire database, within option 7 - Version/Security on the Database Creation/Changes menu. See Modifying the Database Version Information.


Setting the Standard Collate Sequence

To reimplement the standard collate sequence for a database:

  1. From the command line, start the Collate utility with this command:

    COLLATE

    The Collate Utility menu is displayed

  2. Select option 2 - Database Upper Case Collate Sequence

  3. Type the database name and press <Enter>. The Collate Options menu is displayed:

  4. Select the Standard Sequence option

  5. Select the Exit option

Alternatively you can simply enter collate -db dbname -standard at the command line.

If the database index is affected by the change, you are prompted to reorganise the database.


Setting Up a User

The User Set Up dialog box defines how a user logs on to an application menu. This is an example:

Click on the appropriate areas of the illustration above for an explanation of the features available


Setting Up Importer Programs

This section describes how to set up an Importer program. To start editing an Importer Program proceed as follows:

  1. Importer programs are created using IMPSETUP. To access this via the DP4 menu, select Import/Export and Reporting and then Importer Setup and Utilities, or for releases prior to 4.621 select Other facilities, then Importer facilities option and finally select the Setup and utilities option.

  2. Enter the name of the target database. The Importer Setup screen appears.

  3. On the Edit menu, select option 1 - Program editing.

  4. Enter the name of the program on which you want to work and press <Enter>. <Ctrl+P> gives a list of existing import programs from which to select. The Importer Program Editing menu appears.

The stages in editing an Importer program are described in the following topics:

  • File Description.

  • Defining the Line Processing. This involves two or three stages as follows, depending on whether you are using field groups:

    • Defining Field Groups

    • Defining Import Tables

    • Defining Import Fields

  • Saving Importer Programs


Setting up Transaction and Roll-back Logging

DP4 maintains a roll-back log and a transaction log to help ensure the security and integrity of the database. The roll-back log stores images of data before changes are made and the transaction log records each database transaction.

Transaction logging is turned on when you first install DP4 to ensure that roll-forward recovery can be used. The transaction log has the same name as your database, and the extension .LOG.

Roll-back logging is also switched on as standard to make it possible to restart without restoring from a backup copy of the database in the event of a system failure. The roll-back log has the same name as your database, and the extension .RLB. The log is deleted when you exit DP4.

To set up roll-back and transaction logging:

  1. On the DP4 System Main menu, select the Configuration Options:

    The System and User Setup Options menu is displayed

  2. Select option 1 - System configuration which will cause the DP4 System Setup menu to be displayed:

  3. On the System Setup menu, select option 2 - Console/Transaction logging and the Console/Transaction Logging screen is then displayed:

    Only the first seven options are relevant to logging; click on them in the illustration above for further information. The very last option is also important for database maintenance. It is a good idea to ensure that regular integrity checks are performed on your DP4 databases.

  4. Complete the entry screen, pressing <Tab> to save your changes. A message is displayed prompting you to re-boot your machine for the changes to take effect. Press a key to remove the message, and <Esc> to return to the DP4 System menu.

  5. The database manager is not aware of the changes until it is shut down and re-started. Re-boot the machine if the database manager is loaded to remain in memory. Windows users re-start Windows, or shutdown and restart DP4.

Auditing the Transaction Log describes how you can view the contents of the transaction log.


Shadow

Colours used for shadowed screen areas


Show century

Enable this to show the two century digits 19 in front of the year number, for example 1995. Disable it, to suppress the century digits, for example, 95


Shutting Down a Database

  1. On the DP4 System menu, select the Run program option

  2. Type this command to shut down the database:
    SHUTDOWN
    Press <Enter>. The DP4 Shutdown menu is displayed:

    For further information on the options click on the appropriate area of the illustration above.

  3. Select the Shutdown option. If you select options 1 or 2, DP4 prompts you for the name of the database to shut down.

When to use the Shutdown Program

In the Windows versions of DP4 there is no real need to use the SHUTDOWN program. At the time the DP4 system is shutdown, (by stopping the DP4 service for example), all open databases are closed properly. You can use SHUTDOWN -stop as an equivalent of srvw32 -stop or srvw32 -unload. It is bad practice to use SHUTDOWN anywhere in the [preload] or [startup] sections of datafit.ini.

On Unix and Linux (and all other non Windows versions of DP4) databases are not closed when srv3 is stopped (for example with the Unix kill) command. Therefore you should use SHUTDOWN -NOWAIT -DB dbname to ensure the database is closed cleanly before unloading srv3.

If you are using SHUTDOWN to force network clients to log off from the database you should first unload the network manager program. Otherwise running SHUTDOWN may be ineffective as new programs might immediately restart. Once the network manager has been unloaded the database should automatically be closed in any case, but you may wish to run SHUTDOWN in any case - for example in case a locally running application has crashed, leaving an open database connection behind it.


Skip dictionary tables

Disabled by default. Enable this option if you do not want to check the tables derived from the base dictionary (@<name>)


Skip when blank

Enable this option if you want the field value on the database to be unchanged when the field value is blank or missing from the import line


Sorting The Database Data File

As of release 4.622, the contents of this page only apply if you reorganise the database with -CORRUPT. From release 4.622 REORGDB uses the existing index file to build a perfectly sorted DAT file.

As with index optimisation, sorting the data file may improve the performance of programs accessing the database.(The improvement can be very subtantial for a large database). You can specify that the data file is sorted when using option 3 - Rebuild data and index files and option 7 - Delete application data. To enable sorting with either of these options, run the reorganisation with the -SORT or -ALTSORT command tail, for example:

REORGDB -DB SALESORD -DATA -ALTSORT

This rebuilds the SALESORD data file and index, sorting the data file in primary key sequence but with the records from related tables grouped together. For example, ORDER_HEADER records are arranged like this:

order_1, order lines for order 1,
order_2, order lines for order 2.

The -ALTSORT option sorts the data file but does not group records from different tables together like this, but sorts each table individually. On large databases -ALTSORT is likely to give the most benefit.

Neither thhe -SORT and -ALTSORT options perform a complete sort of the the whole data file, which would take a prohibitive amount of time in the case where there is no index available, and probably not significantly improve the performance of the database. Rather the data is partially sorted within the limits of the memory available to the program. Using -ALTSORT regularly the amount of order in the data file will gradually increase, and the performance penalty for the reorganisation will decrease.

Except on legacy versions of DP4, you can increase the amount of memory available to REORGDB for sorting by using the -HEAP command tail. For example -HEAP 16384 allows REORGDB to use 16MB of memory for sorting records rather than the default 4MB.

You may opt to sort the whole data file, but you must use a different method, described in Sorting the Entire Data File.

Some points to consider are:

  • -SORT and -ALTSORT marginally slow down the first phase reorganisation, but if the index is large this is usually more than offset by a reduction in the time it takes to build the index.

  • It is never possible to predict what performance benefit will be gained from sorting the data; it depends on how particular applications access the database. However, for programs that using a large database that access large amounts of data sequentially the improvement is likely to be very dramatic: we have seen the times taken to run such programs reduced from the order of 30 to just 1 or 2 minutes.


Sorting the Entire Data File

This page can be ignored as of release 4.622

To sort the database file in one portion, do the following:

  1. As described in Creating a Database, use the database creation utility to create a new database with the appropriate database numbering system. You may also need to use the COLLATE program to set the collate sequence for the database.

  2. As described in Transferring the Database Structure, use the data structure and data transfer utility to transfer the structure and then the data. This creates a sorted data file, but related tables are not grouped together.

  3. To reduce the size of the index file, reorganise the database using option 1 - Compress data file and rebuild index or option 3 - Rebuild data and index files. (The index built by RECASTDB will probably be very far from optimal because of the way data is transferred.)


Changing Character Collate Sequence and Case Conversion

The Collate utility (COLLATE) provides two language definition facilities:

  • Lower to upper case conversion for U-type fields

    Character data entered into a U-type (Upper case) field is automatically translated into upper case

    Initally DP4 converts characters a..z to A..Z and no others. You can specify how additional characters are converted on a system. For example: üÜ éE âÂ.

    This information is stored in USERDATA.SYS.

  • Collate sequence for U- and M-type fields

    Characters in U-type (Upper case) and M-type (Mixed-case) fields are indexed in a default collate sequence, according to the ASCII values of their upper case equivalents.

    You can specify an alternate index sequence for an application. For example, you may wish to make diacritical characters such as é and Ü appear next to their non-diacritical equivalents such as E and U in indexes and lists.

    This information is stored in the database header.


Reserved Words

Programming environments have reserved words that you must avoid using as names in your database, as otherwise you may have problems referring to the table or field that uses the reserved word as a name. For example, in SQL, the words ORDER and SORT are reserved words.

It is recommended that you set up a list of reserved words and post it to the Base Dictionary, or to each database as it is created. This provides a secure method of preventing developers from inadvertently using a reserved word as a name on the database.

From release 4.620, when DP4 is installed it should copy a number of files of type .RWL into the directory with DP4 executables, containing reserved words for the languages most commonly used with DP4. You can use the Reserved Words (W) option on the main menu of MAKEDB to post these words to BD or to your application database.

Specifying Custom Reserved Words

You can also create your own additional lists of reserved words as follows:

  1. Use a text editor to create a list of reserved words, one on each line.

  2. On the command line, enter the command to load the Reserved words utility:

    RESWORDS filename

    The reserved words are posted to the Base Dictionary (or you can use the -DB option to post the reserved words to a specified database).

Whenever you post a list of reserved words to a database, RESWORDS will warn you if one of the new reserved words has already been used as a name on the database.

you can use RESWORDS -DELETE to remove the list of reserved words from a database.


Specifying the Collate Sequence

To specify an alternative upper-case collate sequence for a database:

  1. From the command line, start the Collate utility with this command: COLLATE
    The Collate Utility menu is displayed

  2. Select option 3 - Database Upper Case Collate Sequence

  3. Type the database name and press <Enter>. The Collate Options menu is displayed

  4. Select option 1 - Alternate Sequence. The Collate Sequence table is displayed:

  5. Use the cursor keys to move to the character whose position in the sequence you wish to modify. For example, to change the position of Ü, move the cursor to it

  6. Press <Enter> to select the character. You are prompted to select a new position for the character

  7. Move the cursor to the character's new position in the table

  8. Press <Enter> and the Position in Collate Sequence menu is displayed. The Collate Sequence options are explained as follows:

    Option Description
    Same as preceding value

    Select this option to prevent DP4 from differentiating between the new character and the preceding character for collate purposes. For example, É is inserted after E in the collate table. If you select this option, É is treated the same as the character E for collate purposes
    After preceding value

    Select this option to ensure that the new character is placed after the preceding character for collate purposes. For example, É is inserted after E in the collate table. If you select this option, É always appears after E for collate purposes.

    The character at the insertion point is moved one to the right, and the new character is inserted.

  9. Press <Esc> to complete the changes. The collate sequence is stored in the database header.

  10. Select option 8 - Exit to terminate the Collate utility.

If the database index is affected by the change, you are prompted to reorganise the database.

If you have set an alternate collate sequence and wish to revert to the standard collate sequence see Setting the Standard Collate Sequence.

If you have set up a non standard collate sequence on one database, and wish to use the same collate sequence on another database, then you can use COLLATE -db db1 -EXPORT followed by COLLATE -db db2 -IMPORT db1.col where db1 and db2 are the names of the respective databases.


Specifying the Lower to Upper Case Conversion

  1. From a command prompt, or the "Run Program" option in the DP4 menu, start the Collate utility with this command:

    COLLATE

    The Collate utility menu is displayed

  2. Select option 1 - Lower to upper case translation. The Translation Table is displayed:

  3. Use the cursor keys to move between the pairs of characters. Press <Enter> to select a character pair to amend. DP4 prompts you to enter the ASCII value of the new upper case translation character

  4. Enter the hexadecimal value, for example:

    Enter translated character: Lower case value E2h Upper case value C2h

    and press <Enter>. The upper-case character is displayed next to the lower-case character.

  5. Complete the changes, and then press <Esc>. The changes are stored in the key translation table in the configuration file USERDATA.SYS. This table is always preserved when you run USERDATA again, unless you specify the -reset option.

  6. You are returned to the Collate Utility Main menu. Select option 8 - Exit to terminate the utility.


Speeding Up Reorganisations

Reorganising a large database can take a very long time: times in excess of 48 hours are not unknown. There are various ways in which database reorganisations can be speeded up:

  • Advanced Database Properties

    You can use the Advanced Database Properties dialog to configure certain properties of the index file. You are recommended always to enable Enhanced Index Compression. For a very large database, or a database stored on a solid state device, set the index node size to 4096 bytes.

  • −LOCAL Command Tail

    From release 4.621 REORGDB automatically determines whether the database to be reorganised is local or not by examining the network status table. The −LOCAL option will be selected automatically unless the resil_type field indicates that databases are remote. You can still use −LOCAL to force local file handling to be invoked even where the database should be remote, and you can use −REMOTE to force the database to be treated as though it were remote.

    If the database you want to reorganise is on the local machine, run the reorganisation utility with the −LOCAL command tail, for example:
    REORGDB −DB SALESORD −LOCAL −DATA
    Much of the processing normally done by the database manager is now done by the terminal manager. This provides certain benefits:

    • It is somewhat faster (on some operating systems up to 60% improvement may be gained).
    • It reduces the load on the DBMS, useful when other programs are using it.

  • −HEAP Command Tail

    Up to release 4.621 REORGDB only allocates 4MB of memory for file and temporary index buffering. by default If your machine has plenty of memory allocating a larger amount of memory may dramatically improve performance. For example −heap 16384 would cause REORGDB to allocate 16MB of memory for the heap. In fact this is the default from release 4.622.

  • −ALTSORT Command Tail

    If your database is very large using the −ALTSORT command tail to sort the data file without grouping related tables may improve performance considerably. Although the first pass will be a little slower this is likely to be more than offset by an increase in the efficiency of the file buffering in the phases that rebuild the index. In addition, the resulting database is likely to perform better, particularly if it is very large.

  • −FAST Command Tail

    The −FAST command tail makes REORGDB use a different algorithm for building the index file. It uses fewer passes, and the index optimisation phase is skipped. This may therefore speed reorganisation up. An example command is:

    REORGDB −DB SALESORD −FAST −INDEX

    Note that although −FAST may speed up the reorganisation options that build a completely new index (options 1, 3, and 7), it builds a slightly less efficient and larger index than otherwise.

    If your database is very large the −FAST option may well actually degrade performance, because a degradation in the efficiency of file buffering may more than offset any time saved by the use of fewer passes.

    The −FAST option has been removed in release 4.622, because fewer passes are used during the indexing phase, and the index optimisation phase is never skipped (in 4.622 this phase, which is very quick, typically reduces the index file size by up to 30%).


SRDD

The DP4 Software Release and Data Distribution (SRDD) product is a management control system for the incremental release of data from a master site to its remote sites.


Start in insert mode

Text can be toggled between insert and overtype modes. Enable this option to start in insert mode


Start in training mode

In DP4, help is toggled on and off with the Help key (normally <F9>). Enable this option to display help on entry to DP4


Starting the Data Entry and Enquiry Utility

  1. On the DP4 menu, select the Other facilities option.

  2. Select the Database Browser or Browse Database option, then tner the database name.

  3. Press <Enter> and type a table name, or press <Ctrl+P> to pop up a list of tables on the database. If you do the latter, use the cursor keys to highlight a table name and press <Enter> to select it

    If there are records in the table, DP4 displays the first record in the table, and the message "Record Status: Existing record".

    If there are no records in the table, DP4 shows the field headings and the message "Record Status: Empty Table".

    To scan the records in the table, use the <←> and <→> keys.

    To move to the menu bar without selecting an option press <Esc>.

    To select an option, press its mnemonic letter, move the cursor to it and press <Enter> or click on it with the mouse.

    To exit from the Database Browser, select the Exit option.


Status Line

The status line shows the current ink and paper colours, whether the typing mode is Insert or Overtype, and the row and column position of the cursor


Status line

The Status line is initially displayed at the bottom of the screen as indicated by a tick. <Enter> toggles the status line on and off


Stop on error

Answer 'Yes' if you want the import to stop if there is an error


Structural Changes

If you make a structural change to your database, you may need to reorganise the database. Any of the following changes are structural:

  • Adding an index link to your database

  • Enabling an index on a table that contains data

In both these cases you will have to build this index using the Reorgansise an Index option before you can access data using the new index. If you make any of the changes below to tables containing data, you will have to completely rebuild the database index:

  • Modifying a key field in a table in the following ways:

    • changing the key precedence,
    • changing the data type
    • changing the number of occurrences

    • changing the scale factor

  • Adding an extra key field to a table

  • Deleting a key field in a table

If you make any of these changes, DP4 forces you to reorganise the database structure. Follow the instructions in Reorganising the Database to run the Database Reorganisation utility.


Structure Generation Number

See Generation Number.


Structure generation number

The structure generation number is maintained by DP4 each time you make a structural change, and reorganise the database. You need to know this when you want to copy tables or programs between two databases, because the structure generation number has to be the same


Suffix length

The length of the suffix. By default the symbol appears immediately after the number, for example "10$". To separate the symbol from the number make the Suffix length longer than the symbol and include a leading space character in the suffix


Suffix

The unit symbols, e.g. "Kg"


Summary of the Translation Process

In summary, to produce an application that runs in more than one language:

  1. Define the language

    Add the language to the language table as described in Adding a Language. Select the language preferences as described in Selecting the Language Preferences

  2. Add the maps in the new language

    Export the maps in the Map Editor's 'Translator format' to a text file, translate them into another language using a word processor of your choice, change the value of the #LANGUAGE directive in the file from lang_not_set to <n> to define the language to be updated and use the Map Posting facility to post the maps back to the application database. These steps are described in Extracting Maps for Translation, Translating Extracted Maps and Posting Maps to a New Language

    When posting translator-format files, most of the map properties and fields are forced to match those of the base language maps. The only attributes of fields which can change are their positions and mnemonics, and for local fields their widths. All such differences are, however, written to an error log which you can examine to ensure that no accidental changes have occurred

  3. Select the collate sequence for key fields

    Define the sequence in which the characters are sorted in the user national language, for example, A, Á, B .. Z. This option is described in Specifying Collate Characteristics of Key Fields. You only need to do this if you have defined one or more U type or M-type (upper case or mixed case) character key fields

For many applications, all that is required is to add the new language, define the language preferences and translate the maps. This is an example screen in two languages from the example Sales Order database:

                                   CUSTOMER UPDATE

Customer No.           4  Name       Russell J

Address             20 Spitfire Drive
                          Freshbream
                          Swindon

Credit     Yes  Credit  Limit   200.00         Balance      218.75

Sales Region    SWE       South West England

This is the corresponding screen translated to a new language:

                                 KUNDE INFORMATION

Kunde Nummer        61  Name       Joachim Ziegler

Anschrift            Herrstrasse 21
                          Staaken
                          Berlin

Kredit     Ja   Kreditlimite   1000.00         Konto      50.00
                                                               Stand
Verkaufsregion    BER       Berlin


SUPPLIER

Stores the company's name and address. The primary key is a unique number that identifies the company


Supporting National Languages

Applications can be set up for any chosen national language. All DP4 applications and DP4 itself, use maps to communicate with the user. Because maps from multiple national languages can coexist on the database and you can define the language preferences for users, an application can support multiple languages at runtime.

This section covers these topics:

  • Developing a multi-language application

  • Translating maps into different languages

  • Selecting the language preferences

  • Specifying the collate characteristics of key fields

  • Changing the database field headings


System Database

The System database is a component of the DP4 environment. It contains information for the application databases, the list of users, their passwords and preferences, and the set of printers and devices.


System Menu

A standard menu that provides access to the majority of the DP4 utilities. This is the default menu, if you do not set up a menu page for a user.


Table

A two-dimensional representation of data, in which fields comprise the columns and records the rows.


Table Definition

Entities are represented as tables on your database. You can have up to 32,000 tables on your database. Each instance of an entity is represented as a record in a table. A record consists of one or more fields that define the type of information that is stored in the table. An example of a table representing the details of materials in stock is:

Material Code | Description | Quantity in Stock | Price
12345 | Paint | 200 | 2.56
34567 | Cement | T50 | 4.00
56789 | Hardboard | 100 | 5.00

To define a table, you first need to identify the individual items of information that are to be stored. Each item of information is represented as a field. The field definitions form the columns of the table. In the MATERIAL table above, the column headings are Material Code, Description, Quantity In Stock and Price. The names need to be modified before they can be used as unique field names in a table definition. They are changed to upper-case, and the spaces are removed.

For example, "Material Code" becomes MATERIAL_CODE. The definition of the fields in the MATERIAL table is shown in this example table plan:

In the example, 1 next to the MATERIAL_CODE field indicates that this field is a key field. The codes to the right of the field names indicate the data type and size of the fields.

In addition to the tabular form shown above, records can also be represented as a set of cards:

This way of showing a table matches the way a record is entered by a user, and also illustrates how it is possible to scan and search through a set of records.


Table details

Enable this option to choose the tables for which details are to be presented


Table Link

The inclusion of the fields in one table in the second table.


Table name

Only applicable when 'Check all tables' is disabled. In which case, select the table whose indexes need to be checked


Table to post

Enter the name of the table to be updated on the target system


Tables

Use this option to define the table fields to be imported for the field group, as described in Defining Import Fields


Tag field length

Enter the data length of the field's tag field


Tag field type

Enter the data type of the tag field. For example, N if you have used a number to tag lines on which the field group appears


Tag Format

Use this option for tagged field groups to specify the format of the tag defined in the field group properties. This option is disabled for some tag types, including characters


Terminal Manager

A memory-resident program that manages the screen, keyboard and printers and communicates with the operating system.


Testing your DP4 Printer Selection

Your printer selection is used for formatting all output from DP4 utilities and programs. This section tells you how to make sure you have the correct printer selection set up on your system. Skip this section if you have successfully printed from DP4 using your present configuration.

In the following steps, you are asked to run the DP4 Data Entry and Enquiry utility and print a record from the example SALESORD database.

  1. On the DP4 System Main menu, select the Other facilities option followed by the Database browser option

  2. Select the SALESORD database. The main screen of the Database browser is displayed

  3. From the menu bar, select the Table option. Enter the table name CUSTOMER, and press <Enter>. DP4 displays the first record in the sequence

  4. Select the Print option. DP4 displays the Output Devices menu. Enable output to a printer by clicking on the 'Printer' check box:

    For the 'Print device' field, press <Ctrl+P> to pop up the Print devices list. This is the list of printers provided with the standard DP4 system:

    Select the print device that most closely matches the printer attached to your system

  5. Press <Enter>, and DP4 outputs the record to your printer. Some printers require a page break code, and will only print the page when you exit the utility.

  6. On the Data entry and enquiry action bar, select the Exit option. Press <Esc> twice to return to the DP4 System Main menu

What if your printer does not appear in the output devices list? You can try out the alternative printer selections to see if these work with your printer. This may take some trial and error.
If the alternative printer selections do not work with your printer, follow the instructions in Configuring a Printer to tell DP4 about your printer. You will need to run the System configuration utility (option 1 on the Main menu), and have your printer manual to hand.


Text Editing Facilities

The Keys Help displays the keys that perform special actions when editing the file. These fall into five categories:

  • Moving the Cursor

  • Inserting and Deleting Text

  • Copying and Pasting Text

  • Text Painting

  • Box Drawing


Text Editor

The DP4 Text Editor,DP4ED, also known as ColorWrite, is useful for viewing and editing files produced by DP4 utilities.


Text Painting

Use the <Ctrl+P> keys to paint the character to the right of the cursor with the current paper and ink colours. To paint a block of text, just highlight it with the mouse or mark it as a block before using <Ctrl+P>.

To change the current paper and ink colours:

  1. Use the Colour, <F7>, option to display the colour specification dialog

  2. Use the cursor keys and <Enter> to select options. Pressing <Enter> again deselects an option

    You can specify the paper and ink colours explicitly or opt to use one of the following predefined styles:

    Default specifies that the colours set up for the map type are used. These are not specified in the editor, but in the User Preferences of the DP4 System Setup utility. This option is mainly of use when editing maps

    Emphasised specifies that the colours set up for the Emphasis style are used. These are not specified in the editor, but in the User Preferences of the DP4 System Setup utility

    If you choose your own paper and ink colours you can combine them with these options:

    Flash under non-graphical environments, this option makes the paper flash. Under graphical environments, it makes the paper lighter. For example, blue combined with Flash becomes light blue

    Highlight makes the ink colour lighter

    Reverse swaps the ink and paper assignments with one another

  3. Select the Save & Exit option to leave the popup. The next text you type is displayed in the colours you chose


Text

Colours used to display field headings and other map annotation


The SALESORD Database

The examples in this manual are based upon the dealings of a fictitious company, Acme Sales Order Plc, which sells a number of products throughout the United Kingdom. An example database called SALESORD is supplied on which you may experiment with the database utilities. SALESORD stores information on Acme employees, customers, orders and supplier’s materials. Its structure is illustrated below. See below for further information.

SALESORD Entity Relationship Diagram

The principal tables in the SALESORD database are as follows:

EMPLOYEE

Stores details of Acme employees who manage customer sales regions and customer accounts, including the employee’s name, gender, company grade and date of birth. The primary key is a unique number that identifies the employee

REGION

Stores details of the UK regions where customers live, including the name of the region and the company employee managing the region. The primary key is a unique code that identifies each region

CUSTOMER

Stores the customer’s name, address and region code, credit arrangements and balance. The primary key is a unique number that identifies the customer

ORDER_HEADER

Stores the details of the date and time the order was placed, its value and customer payments made against it, delivery details, customer number and code of the Acme employee responsible for the account. The primary key is a unique number that identifies each order. A customer may have several order header records, each of which may have several order lines giving materials and quantities ordered. An order header record may also have several order payment records

ORDER_LINE

Includes one record for each different material in an order. ORDER_LINE contains the order number, a line number that identifies an order line, the material code and quantity ordered, together with the value of the order line. The order number plus line number form the primary key that identifies each record

ORDER_PAYMENT

Includes one record for each payment made against an order. ORDER_PAYMENT records the order number, a payment number that identifies separate payments, the media code (cheque, direct debit, credit card), the amount paid, the card number and payment date and time

MATERIAL

Stores details of each material including its code, description, unit price, quantity in stock and re-order level together with the code of the principal and the alternative supplier of the material. The material code is the primary key to the MATERIAL table

SUPPLIER

Stores the company’s name and address. The primary key is a unique number that identifies the company


Third Normal Form

The stage of normalisation of an entity in which the key (such as a customer number) uniquely identifies all the other fields of a record (such as the name and address).


Title Bar

The title bar displays the name of the file being edited


Title

Colours used for the title line of active windows


Transaction

A set of changes to the database that are processed as a single unit.


Transaction Log

A log that records all the modifications to the database since the last backup. This file is vital for recovering the database in the event of a system failure.


Transaction log maximum size

Specify the maximum size, in KBytes, that the transaction log can reach before you are forced to make a backup. When storing the transaction log on removable media, you can set this below the maximum disk capacity. If you prefer, DP4 can store transaction logs across more than one disk.


Transaction log on floppy

Enable this option if the designated drive is removable media. DP4 stores the transaction log on the drive specified in your Configuration Options, Location of Files option


Transaction log warning size

Specify the size, in KBytes, that the transaction log can reach before the user is warned to back up the database


Transferring Data

The Data Transfer utility copies data between databases of identical structure on the same system. Before allowing data transfer, DP4 tests whether the databases are identical. Using their internal reference numbers, DP4 compares fields and tables on the source database with the same fields and tables on the target. If the databases fail this test, no data is transferred. It is possible for databases to have identical structures, while their tables have different internal reference numbers. This can happen when tables have been added to a database with either the Database Creation utility or the Data Dictionary Extract and Post utility, rather than with the Data Structure & Data Transfer utility. In this case, we recommend that you transfer the data by name, as described later.

  1. On the DP4 System Main menu, select the Database management option

  2. On the Database Structure Management menu, select the Transfer structure/data option

  3. Enter the name of the source database and then the name of the target database. The Data Structure and Data Transfer menu appears

    Two options on this menu are concerned with data transfer:

    • Option 2 - Data transfer copies data from the source database to the target

    • Option 7 - Options specifies whether data is transferred by name. By default, data is transferred by internal reference numbers

  4. Select option 2 - Data transfer. A popup list of tables on the source database appears. You are prompted to select the tables that contain the data you wish to transfer

  5. Use the cursor keys to move around the list of tables and press <Enter> to select a table. Press <Tab> when you have completed the table selection. DP4 asks you the following question:

  6. Answer 'No' if you want to keep the existing data on the target database. Answer 'Yes' to delete any data on the selected tables of the target database that is not on the source database. DP4 transfers the data in the selected tables to the target database. Messages are displayed indicating the progress of the operation

If you get a warning that the databases have different internal numbers, select option 7 - Options on the transfer menu, and select the option Data Transfer by name. This option allows copying to take place where tables have the same structure but different internal numbers

The transfer completes and the database structure and data transfer menu is redisplayed


Transferring Data with the Load and Unload Facility

The data Load and Unload facility provides a simple two-stage process for copying data by name between databases with a similar, but not necessarily identical structures:

Unload | Exports database tables to an ASCII file, named database_name.txt
Load | Loads table data from an ASCII file, created by the data Unload utility, into the target database

This section is divided into the following topics:

  • Unloading Data from the Source Database

  • Loading Data into the Target Database

  • Understanding the Data Format


Transferring Database Information

A common requirement when you work with databases is to import information into them and export it from them. This information might be data to populate application tables or it might be the tables themselves; that is, structural information to create or update field and table definitions on the database. DP4 supports three different methods for transferring information, although there is some overlap between them. This gives you the flexibility to opt for a means of transfer that exactly matches your purpose.

You can import data from many different sources, including spreadsheets, word-processed reports, non-DP4 files and, of course, other DP4 databases.

This section is divided into the following topics:

  • Copying the database structure

  • Copying data between databases

  • Exporting data with Report Writer

  • Importing Data using a Parameter Set

  • Transferring data with the load and unload facility


Transferring Report Writer Programs between Databases

This section describes how to extract Report Writer export programs, and then post them to another database. Read this section if you intend to generate import programs automatically from Report Writer export programs.

Extracting Export Programs

  1. On the Report Writer Facilities menu, select the Setup and utilities option

  2. Enter the database name. The Report Writer setup screen appears

  3. On the Move menu, select option 2 - Extract selected programs:

  4. Select the program you wish to extract. Remember that automatically generated export programs are named EXP<NNNNN>, where <nnnnn> is the internal reference number of the table. Report Writer extracts the export program to the file

  5. Repeat step 4 if there are any further programs you wish to extract

  6. If the target database is on another system, exit the DP4 system. Transfer the extract files to the destination system by diskette or other means

    Posting Export Programs

    Start up DP4 on the target system.

  7. On the Report Writer Facilities menu, select the Setup and utilities option

  8. On the Move menu, select option 4 - Post program file:

  9. Enter the name of the extract file. The program is transferred, and the Report Writer menu bar is redisplayed

  10. Repeat step 3 if there are any further extract files to post

  11. Select the Exit option to return to the DP4 System Main menu


Transferring the Database Structure

You can only use the Data Structure and Transfer utility (RECASTDB) when the source and target databases reside on the same system.

When you transfer the structure:

  • If the target database is an older version of the source database, the data independence mechanism upgrades the existing data

  • The source database structure completely replaces the structure of the target database

    After transferring the structure, run the database integrity checker against the database to determine what, if any, action you need to take. For example, if the data independence mechanism is invoked by the structure transfer, you will be advised to run a database reorganisation to rebuild the data file.

To transfer the database structure:

  1. On the DP4 System menu, select the Database Management option. The Database Structure Management menu appears

  2. On the Database Structure Management menu, select the Transfer structure/data option

  3. Enter the name of the source database

  4. Enter the name of the target database. The Data Structure and Data Transfer menu appears:

    Two options on this menu are concerned with structure transfer:

    • Option 1 - Structure transfer replaces the structure of the target database with that of the source database

    • Option 7 - Options specifies whether QA Build source code and data dictionary information is transferred. This should normally be transferred unless:

      • You want to deliver a runtime-only system that does not allow users to edit QA Build programs
      • You want to speed up the structure transfer and know that the data dictionary information is identical in both databases

  5. Select option 1 - Structure transfer . The System Data Options screen is displayed:

    These options let you select the additional information you would like the transfer utility to copy to the target database. There are two input columns, as follows

    Transfer? enables the type of additional information to transfer. Disable all the options to transfer just the database structure

    Delete data not on source? enables an option to delete data, for example Report Writer programs, on the target database that is not present on the source. Disable an option to retain existing target data that is not on the source

    As an alternative, be aware that there are specific, more powerful utilities for transferring additional system data. For example, MAPEDIT and MAPPOST transfer maps and QABUTIL transfers QA Build programs and their maps

  6. Make your selections and press the <Tab> key. DP4 transfers the database structure. Messages are displayed which show the progress of the operation. The transfer completes and a message appears advising you of what action to take:

  7. Press <Enter> to return to the Data Structure and Data Transfer menu

If the database independence mechanism has been invoked due to the structure transfer, run a database reorganisation. To see the action recommended by DP4, run the Database Integrity Checker, select Option 1 - Status display and examine the Database status field.


Translate from EBCDIC

Disable this option if the field is to be read as an ASCII value. Enable it if the field is to be read as an EBCDIC value


Translating Extracted Maps

Using an editor program which can process plain text files, the translator should edit the file created by the procedure outlined in Extracting Maps for Translation, replacing each item of text in the initial language with text in the new language. The translator must be careful only to alter text in help messages or between quotes for controls, (or between lines of underscores in the acse of release 4.xx). The translator must be careful not to translate the keywords used by MAPPOST such as Static,Button,Edit etc. The translator should also beware of things like word-wrapping facilities which may cause lines to be split inappropriately.

At the start of the file is a line which begins #language. This will be emitted with the value unset or lang_not_set if you are using 4.5xx. This must be changed to be the number of the DP4 language for which the translation is being made.
Do not change the line marked #translation.

For example, if you are translating to French and it is defined as language 2, change #LANGUAGE as follows: #LANGUAGE 2

On posting, an error is generated if the value unset is left in the file

It may help the translator to have a documentation copy of the maps to hand. You can produce one of these by carrying out the earlier procedure Extracting Maps for Translation, but selecting Documentation instead of Translator format. This is likely to be especially useful in release 4.6xx


Translating Maps into Different Languages

The 4.6xx MAPMAKE utility and the 4.5xx MAPEDIT both have a facility that allows maps to be exported to a text file that you can supply to a translator who will translate the maps into a foreign language. The translator does not require any knowledge of DP4. This is done as follows:

  1. Extract the maps' text in "Translator" format. See Extracting Maps for Translation
  2. Translate the file from the original language to the new language. See Translating Extracted Maps
  3. Post the translated file back into a new language code with MAPPOST.Posting Maps to a New Language

Please refer to Translator Format Limitations to learn about the limitations to what can be achieved using Translator Format, and for important advice on designing maps that you anticipate will be translated later on.

If you are maintaining an application which has maps in several different languages you may find the Multi-Language option in MAPMAKE useful.


Translating the System Database

The system database holds all the maps for the DP4 utilities, including system and fail error messages. As with application databases, it supports multiple national languages and extraction and posting of maps in translator format. Be aware that all the maps for QA Build are held in the PROGMAKE mapset.


Translation needed

If the tag field is coded in EBCDIC, enable this option to translate it to ASCII


Translator Format Limitations

MAPPOST creates the translated version of the map by combining the properties of the maps and controls from the base language with the text in the new language. If you make changes to the base language map you will need to repeat the translation process.

For release 4.6xx the sizes and positions of all the controls in the translated map are the same as in the base language. Therefore you should allow additional space when designing maps that are to be translated. It is suggested that you allow at least 100% extra for controls whose text consists of a short single word, 50-70% extra for short single words, and 30% extra for longer phrases. In future an option may be provided to report on controls that are not large enough for the text they contain.

For release 4.6xx the font, and therefore the character set, for the translated map is assumed to be the same as in the base language. This may well cause problems when translating between languages that typically use different character sets. In future a facility to automatically translate fonts into an appropriate character set will be provided.


Type-ahead

This is a mechanism that allows applications to continue taking input while processing the previous input. The options are:

1 No type ahead

Stops the user answering questions in advance

2 Alphanumeric keys

Allows the user to type one field ahead, but not to edit it before it is displayed.

3 Editing and Alphanumeric keys

Allows the user to type and edit one field ahead.

4 Unrestricted type ahead

Allows the user to type ahead with complete freedom, subject to the size of the keyboard buffer.

5

 OS type ahead

Type ahead is implemented by the operating system and not by DP4 at all. This may be useful in the following situations:

  • Where input arrives very quickly and it is important that no characters are lost, for example, when using a DP4 OSDI to accept input from devices such as scanners.

  • On dumb terminals where XON/XOFF protocol must be used

Using this option prevents batch programs from being interruptable with <Ctrl+S> <Ctrl+C>.


Types of Backup

For most systems, by far the best method of backing up your DP4 database is that described in DYNABACK - Backup in Native DP4 Database Format

You can either make a complete backup of your database including all the files that together make up the database, or you can opt to back up only the .LOG file.

You can back up the database to hard disk or to removable media such as a diskette or tape, or both. If you back up to the local hard disk only you can restore the database quickly, but you are not covered in the event of a hard disk failure. It is a much better idea to back up to a second hard disk, for example a drive on a network server that is automatically backed up each night by a tape streamer. It is also a good idea to keep a backup available on hard disk, as this is likely to permit a faster restart after a system crash. However, this does require extra hard disk space.

There are two different utilities for backing up a database, with a range of facilities:

  • The original backup program is called DBBACKUP. It is geared towards backing up a database to diskettes, which is inconvenient for large databases, at least if you only have a standard diskette drive, though it may be acceptable if you have a Zip drive. Until release 4.622 DBBACKUP also partially supports backing up to tape. A key feature (or major disadvantage) of DBBACKUP is that it cannot be used while the database is in use.

  • The newer backup program is called DYNABACK. It supports backup to diskette in the same format as DBBACKUP but with the difference that the backup can be made while the database is in use. From release 4.522/4.618 it has a new facility to back up the database by creating a copy of the database under a different name. This has two very important benefits:

    • The backed up database can have a full integrity check done on it. The result of this check is the same as running a check on the main database immediately prior to the backup completing. You will therefore have checked your live database for integrity, and can be sure that the backup copy of the database is good.

    • In the event of a system failure and a failure in the usual roll-back recovery process, DBRECOV can use the last backup in this format to automatically restore the database, without the need for user intervention.

    For further details see Dynamic Backup.

When using the original diskette style backup with either DBBACKUP or DYNABACK you can control where the backup is made, using the directory assignment option of DFSETUP to set up the desired default location for backup files. You can over-ride the backup location with the -DISK command tail when running DBBACKUP (or DYNABACK from 4.522). This allows you to arrange, for example, to back up to hard disk on a daily basis, and to disk on a weekly basis. (For details see Specifying the Backup Location.)

If the total size of all database files is very close to or exceeds 4GB, then the only way to back up the database correctly, at least until release 4.622, is:
DYNABACK -COPY -NOSPACE.

As of release 4.622 all the DP4 backup utilties can work with databases of any size, provided the underlying operating system supports sufficiently large files.

Backing up without using DP4 backup utilities

An alternative to using the DP4 backup utilities is to use the facilities provided by the operating system or third party software to copy the database files. Be careful when writing a script/batch file to do this, because:

  • You must make sure the database files are all closed before attempting to copy them. (See Shutting down a database.)

  • You must make sure ALL the database files are copied.

  • You must run the SETSESSN utility with the -DELETE command tail to perform a dummy backup. This increments the database session number and deletes the transaction log. A new transaction log is started in the next session when you carry out a database transaction. (Under Windows 3.1 it is not possible to run DP4 for Windows programs from DOS batch files. So, running utilities like SETSESSN has to be done manually after running the backup utility).


Understanding Field Groups

In 4.500, the Importer program was modified and the concept of field groups introduced. Read this section if you want to understand about field groups and how to set them up to import data from a file containining lines in multiple formats.

The fields belonging to one field group are always held on one line. They cannot be split over several lines. This means that the maximum number of characters that can be held in a field group is governed by the maximum length of an import line, which is 8190 characters. Each line of the import file may contain more than one field group.

There are two types of field group, called untagged and tagged. The fields in an untagged group are repeated on every line of the import file. The fields in a tagged group are only repeated on certain lines. Field groups are named like this because you do not have to identify or tag field groups that repeat on every line of the import file, but you do have to tag field groups that only occur on certain lines of the import file. A tag is a field in the import file that appears on each line of the import file that holds the field group. Tags are normally numbers at the beginning of the line.

Some examples of import files and the field groups that you would set up to describe their import fields follow:

Example 1: All Lines the Same

A comma delimited import file containing lines that are all the same:

<field1>,<field2>,<field3>
<field1>,<field2>,<field3>
...
<field1>,<field2>,<field3>

You do not need to set up a field group in the Importer for the above. If the fields are from the same table, in the Importer just define the table and then that <field1> is in column 1, <field2> in column 2 and <field3> in column 3.

Example 2: Two Kinds of Line

Suppose your import file holds fields from two tables on different lines:

1,<tab1_field1>,<tab1_field2>,<tab1_field3>
1,<tab1_field1>,<tab1_field2>,<tab1_field3>
...
1,<tab1_field1>,<tab1_field2>,<tab1_field3>
2,<tab2_field1>,<tab2_field2>,<tab2_field3>
2,<tab2_field1>,<tab2_field2>,<tab2_field3>
...
2,<tab2_field1>,<tab2_field2>,<tab2_field3>

You need to set up two tagged field groups to import the above:

  • One tagged group to import the TAB1 fields:

    <tab1_field1>,<tab1_field2>,<tab1_field3>

    which appear in columns 2, 3 and 4 on lines tagged by the value 1 in column 1.

  • One tagged group to import the TAB2 fields:

    <tab2_field1>,<tab2_field2>,<tab2_field3>

    which appear in columns 2, 3 and 4 on lines tagged by the value 2 in column 1.

Example 3: Multiple field Groups on Lines

Suppose you wanted to import sales order information. The import file might hold ORDER_LINE fields on every line and ORDER_HEADER fields on some lines, for example:

1,<OH_field1>,<OH_field2>,<OL_field1>,<OL_field2>
,,,<OL_field1>,<OL_field2>
,,,<OL_field1>,<OL_field2>
,,,<OL_field1>,<OL_field2>
...
1,<OH_field1>,<OH_field2>,<OL_field1>,<OL_field2>
,,,<OL_field1>,<OL_field2>
,,,<OL_field1>,<OL_field2>

You need to set up two field groups to import the above: a tagged group to import the ORDER_HEADER fields:

<OH_field1>,<OH_field2>

which appear in columns 2 and 3, on lines tagged by the value 1 in column 1, and an untagged group to import the ORDER_LINE fields:

<OL_field1>,<OL_field2>

which appear on every line in columns 4 and 5.


Understanding the Data Format

If you want to import data from another system, a quick way of doing it is by creating an ASCII file that conforms to the structure explained in this section and using the data Load utility to transfer the data directly into the target database. You can also use conventional flat files, but in this case you will need to write a program to import the data (you can use Importer to avoid having to code the program in C)

The structure of the ASCII file you need to prepare is given below:

DATABASE <database_name>

TABLE <table_name>
FIELDS <field_list>
<field_values>
...
<field_values>

TABLE <table_name>
FIELDS <field_list>

<field_values>
...
<field_values>

The meaning and format of the various items is as follows:

<database_name>

Is the default name of the database into which the data is to be loaded by the data Load utility. For example, SALESORD

<table_name>

Is the name of the table on the target database into which the data specified by the next fields section is to be loaded. For example, CUSTOMER

<field_list>

Is a list of field definitions, separated by commas, that define the fields to be imported, as follows:

<name>.<role>[<occurrence>][<type>],<name>.<role> [<occurrence>][<type>],...

For example:

customer_number[N5],name[C30],address[1][C30],address[2][C30]

The list of field definitions after the Fields keyword must be on one line. Lines may be up to 4096 characters long in the file.

<field_values>

Specifies the field values to be imported in the same order as the field list. Repeating groups of field values must be on their own lines in the file. The field values must be separated by commas, but any trailing spaces may be omitted. Field values are formatted in this way:

Time format | <hrs>.<mins>.<secs>[.<h'dths>] for example: 10.12.30
Date format | <full_year>-<month>-<day> for example: 1995-1-29
Char format | Srings can be enclosed in double or single quotes. If in double quotes only printable characters are permitted, and a double quote can only be included in the data by doubling it. If strings are enclosed in single quotes then you can use C style escapes e.g. \r,\n to represent non-printable characters. Any value can be specified as \nn where n is a hexadecimal digit.
Number format | <number> for example: 1.23

Let's look at a complete example, showing an ASCII file containing data from the CUSTOMER and MATERIAL tables:

Database SALESORD
Table CUSTOMER
Fields CUSTOMER_NUMBER(N),NAME(C30),ADDRESS[1](C30),ADDRESS[2](C30),ADDRESS[3](C30),CREDIT_CUSTOMER(Y),CREDIT_LIMIT(N),BALANCE(N),REGION_CODE(U3)
   1,"Russell R","34 Church St.","Pinead","Spalding",1,1000.00,126.45,"CAM"
   2,"Abrahams P F","154 Greenfields","Earith","Ramsey",1,500.00,26.00,"CAM"
 ...
   60,"Taylor P","Unit 3","Scunthorpe","Yorkshire",1,200.00,0.40,"YRK"
Table MATERIAL
Fields MATERIAL_CODE(U8),DESCRIPTION(C20),PRICE(N),QUANTITY.IN_STOCK(I),QUANTITY.RE_ORDER_LEVEL(I),ON_ORDER(Y),SUPPLIER_NUMBER.PRINCIPAL(I),SUPPLIER_NUMBER.ALTERNATIVE(I)
   "19949819","4 Inch Nails",1.25,120,200,0,1,2
   "19949826","White Gloss Paint",2.56,200,100,0,1,2
...
   "41123232","Unique Sparkling Wat",0.60,300,100,0,1,2


Understanding the Export File

The export file stores the structure for each table and field in this format:

#db          SALESORD
#generation  0
#null        !
#reldata     name = "CUSTOMER"  number = 4107  flags = 8496 ,
             read = 0    write = 0  last_change = 0
#language 0
________________
Customer
________________
#language 1
________________
Kunde
________________
#field_descr name = "CUSTOMER_NUMBER"  number = 4108 ,
             type = N     length = 5  occurs = 1 ,
             read = 0    write = 0  format = 0 ,
             iotype = N ,
             precision = 0 scale =   0 ,
             low  = "0           " ,
             high = "99999           "

#relfield    name = "CUSTOMER_NUMBER"  number = 4108 ,
             key  = 1  sequence = 5

The indexes between tables are stored at the end of the export file, in this format:

#pc_record   child  = "REGION"  child_nr  = 4104 ,
             parent = "INDEX_CUST_NAME"  parent_nr = 4127 ,
             flags  = 3328
#pc_record   child  = "CUSTOMER"  child_nr  = 4107 ,
             parent = "REGION"  parent_nr = 4104 ,
             flags  = 2309

If you answered 'Yes' to the question 'Include instruction to delete tables not in extract from target', DP4 puts the line #all_tables to the export file. This line specifies that tables on the target database that are not on the source database are to be deleted:

#db          SALESORD
#generation  0
#null        !
#all_tables


Unit name

The name of the unit


Unloading Data from the Source Database

To unload database data into an ASCII file:

  1. On the DP4 System Main menu, select the Run Program option and enter:
    UNLOAD

  2. Enter the database name. The Unload menu appears. There are two options:

    • Option 1 - All Tables unloads data from all the tables on the database into an ASCII file

    • Option 2 - Selected Tables unloads data from your choice of database tables into an ASCII file

  3. Select the option you want. If you select option 2 - Selected Tables, you make your choice from a list of the tables which appears. The data is exported to an ASCII file named <DATABASE_NAME>.TXT

Modifying the Unload Facility

To export the data from the tables specified in a file, run the Unload utility like this:

UNLOAD -DB <database_name> -TABLE @<file_name>.lst

where <file_name> lists the tables, separated by spaces, to be unloaded.

To suppress screen output while data is unloading, use the -QUIET command tail, as in:

UNLOAD -QUIET


Update

Answer 'Yes' if you wish to maintain the secondary index link to the parent table. For example, allow the material table to index the order line table. There is a performance penalty in maintaining an index


Updating the Database

A DP4 application program updates the database in three stages:

  • Post or Kill

    Each new or amended record is posted to a buffer, and each deleted record is flagged as 'killed'. The buffer is usually a workfile with the extension .$$$ on disk

    In the event of a system failure, changes that have been posted but not committed are lost.

  • Commit

    The records in the post buffer are checked. If the commit is successful, the records are written to the transaction log in memory. The roll-back log buffer is updated with the 'before image' of the record, and the database files are logically, though not usually physically updated. Occasionally the roll-back file is restarted to prevent it growing too large. The details of this vary from version to version. When it happens you may notice a brief slight drop in performance.

    In the event of a system failure, the last few committed records may be lost. Only complete transactions are recovered (or lost) by a system failure.

    You can force a transaction to be secure by using a checkpoint (Secure changes in QAB).

  • Checkpoint

    This stage secures the updates made by your program. The transaction log file is secured, so that transactions can be guaranteed to be recoverable.

    The database files themselves are NOT secured at every checkpoint. That is, if the system fails immediately following a checkpoint, the database will usually require DBRECOV. When the DBRECOV is run the transactions secured in the log file will be automatically applied to the recovered database. There is no facility, and no need for a facility to force the database files to be physically updated, other than to shut the database down.

    In the event that the database is lost and has to be recovered with DBRESTOR, the transaction log can be applied as part of the restore process.

    The description above assumes that you have the roll-back logging options selected in the Console/Transaction Logging option in the Setup utility. If transaction logging is disabled a temporary transaction log is used instead, and every so soften the database is secured by physically updating all the files, so that the temorary log can be discarded and restarted.

    If rollback logging is disabled (not recommended) then the database is secured by every checkpoint, or every so many commits. This has a severe impact on performance.


Use default value

Disable this option if you want the field value to be read from the import file. Enable it, if you want the field value to be assigned its dictionary default value by DP4 (for example, to allocate an auto-increment key)


Use of Generation Numbers

Each time the database structure is changed, DP4 increments the generation number held in the database index file. New and updated records are written to the database with the current generation number.

When a program reads a record, the generation number for the record is checked to ensure that it is the same as the program generation number. If the two numbers are different, the data independence table is used to convert the record to the structure required by the program.

This means that provided the program logic is not affected by the change, you can change the database structure without needing to recompile programs.


User File

The DP4 user file, USERDATA.SYS, which holds the information defining a user's terminal.


User language

For a single-language application, enter 0. For a multi-language application, press <Ctrl+P> to select an alternative language from the popup list. If the language does not appear in the list, read Supporting National Languages


User List

A list of users of a DP4 system is held on the system database. The information relating to users includes their password, access levels, language and user defaults.


User Menu

See Application Menu.


User Preferences

User preferences allow you to customise the look-and-feel of the user interface to accommodate different conventions for displaying fields and for colouring the screen display. User preferences are tied to usernames so that when a user logs on the right preferences are read from the system database.

The DP4 configuration file, USERDATA.SYS, stores the default user preferences for all users. On installation, the first time you run the USERDATA utility, the initial values for these preferences are copied into USERDATA.SYS from the executable file, USERDATA.EXE. You can subsequently change the default user preferences held in USERDATA.SYS by running the DP4 System Setup utility (DFSETUP) from the command line (not logged in under a username), as described in Changing the Default User Preferences

To restore the USERDATA.EXE preference values to USERDATA.SYS, run the USERDATA utility with the -RESET command tail. And, if doing this for Windows, include the -SAA command tail to impose the IBM SAA convention, fully described in Using the DP4 User Interface. In order to retain any customisation of the keyboard, set up with DFSETUP, also append the -SAMEKEY command tail.

User preferences for individuals are stored on the System database. To set these up for a user, log into the DP4 system menus as that user, and carry out the procedure given later in Changing a User's Preferences. The preferences are initially taken from userdata.sys, but you can change these as you like to conform to different user and user group conventions. For many preferences, you can choose Default to use the current USERDATA.SYS values. The following example illustrates the consequences of this.

Initially, the USERDATA.SYS "Selection" colour is "Blue/White"

Within their respective menus systems, user A specifies "Default" for "Selection" (which gives "Blue/White"), while user B explicitly specifies "Red/White"

By running DFSETUP from the command line, the USERDATA.SYS "Selection" colour is changed from "Blue/White" to "Black/White". This changes user A's "Selection" colour accordingly to "Black/White" because this is its new default. User B is unaffected by this modification to USERDATA.SYS, so his "Selection" colour remains as "Red/White"


Using Menu Commands

There are two types of menu action:

  • Built-in menu commands

    You can use built-in Menu option commands to perform specific actions such as changing to another database, prompting the user to log on and changing the user preferences.

  • Executable commands
    You can run any program including:

    • Your own DP4 application programs (implemented in QA Build, Report Writer, or a language such as C/C++).
    • Standard DP4 utilities such as database backup and recovery tools
    • Non-DP4 programs such as Word Processors

    You can add one or more of the built-in special command tails to affect how the programs are run (for example the %BATCH tail is needed to run batch files and shell scripts), and can supply ordinary command tails such as command line options and filenames.


Using the DP4 System Main Menu

The DP4 System Main menu gives you access to the standard development utilities as follows:

Release 4.621

From Release 4.621 the install program will create a Windows Start Menu for DP4 that is structure almost identically to the DP4 System Menu. You can use either to access the most useful DP4 utilities.

Option | Facilities
Configuration Utilities | Set up the DP4 system, adding its users and devices
Database Design | Create and modify databases
Maintenance Utiltiies | Backup, restore, recover and check your databases
QAB Facilities | Create, compile, debug and run QA Build programs
Import Export and Reporting | setup and run import and export programs, setup and run reports
Screen and Form Utilities | Edit and manage the screens and forms used by your DP4 programs
Troubleshooting | Various Utilties to assist you with tracking down problems with DP4
Other Facilities | Add and browse records with the Database Browser, or use SQL to query or update a database.
Run Program | Run a utility as if from the command line
Other Facilities | Add and browse records, import and export data, produce reports, create menu systems, manage maps
Run Program | Run a utility as if from the command line
Lock Up | Temporarily lock the DP4 session
Log out | End the session, returning to the logon dialog
Exit | End the session, returning to the program manager

Earlier DP4 Releases

Option | Facilities
Configuration Options | Set up the DP4 system, adding its users and devices
Database Management | Create and modify databases
Backup/Recovery | Backup, restore, recover and check your databases
QAB Facilities | Create, compile, debug and run QA Build programs
Other Facilities | Add and browse records, import and export data, produce reports, create menu systems, manage maps
Run Program | Run a utility as if from the command line
Log out | End the session, returning to the logon dialog
Exit | End the session, returning to the program manager

  • To move the highlight around the options, use the cursor keys or mouse.

  • To select an option, highlight it and then do any one of the following:

    • Press the <Enter> key
    • Click on its icon
    • Click on the Select button at the bottom of the window
    • Press the mnemonic key that selects the option (indicated by an underscored letter)

    When you select an option, DP4 either displays a further menu or executes a command, such as the command to run a utility program.

  • To exit from the DP4 System Main menu, do any one of the following:

    • Click on the Exit icon
    • Click on the Cancel icon

    • Press the <Esc> key

In the sections that follow, you are asked to select options on the DP4 System Main menu. If it is not already loaded, you will need to log on to DP4 as described in How to Log on to DP4.


Using the DP4 User Interface

This section describes the standard keystrokes and their functions in DP4. Some of the keystrokes can be accessed using alternative keys as shown in the third column. If your system is set up to use the IBM SAA keyboard standard, the keys differ on data entry screens as shown in the last column.

 | Action | Key/Button | Alternative | IBM SAA Compatible
Menu Actions | Move to the next option | <→> or <↓> | <Ctrl+X> |
 | Move to the previous option | <↑> or <←> | <Ctrl+E> |
 | Select the current option | <Enter> | <Ctrl+M> |
 | Shortcut to an option | Press the mnemonic key |  |
 | Cancel without selecting an option | <Esc> |  |
Data Entry Screen Actions | Move to the next field | <Enter> |  | <Tab>
 | Move to the previous field | <Shift+Tab> | <Ctrl+@> (Serial terminals only) |
 | Toggle Insert/overtype mode | <Insert> | <Ctrl+V> (Not Windows) |
 | Move to the end of the field, line or window | Press <End> repeatedly | Press <Ctrl+\> repeatedly |
 | Move to the start of the field, line or window | Press <Home> repeatedly | Press <Ctrl+Q> repeatedly |
 | Complete the entry screen | <Tab> |  | <Enter>
 | Abandon changes to the entry screen | <Esc> |  | <F3>
Popup List Actions | Move to the next item | <→> or <↓> | <Ctrl+X> |
 | Move to the previous item | <↑> or <←> | <Ctrl+E> |
 | Move to the end of the line or window | Press <End> repeatedly | Press <Ctrl+\> repeatedly |
 | Move to the start of the line or window | Press <Home> repeatedly | Press <Ctrl+Q> repeatedly |
 | Select the current item | <Enter> | <Ctrl+M> |
 | Exit the popup list | <Esc> |  |
Toggling Help On and Off | <F10> | <Ctrl+J> |  | <F1>


Using the Example Database

An example Sales Order database called SALESORD is supplied. You can experiment with it to help you become acquainted with DP4. The structure of this database is described in The SALESORD Database.

Read this section if you wish to install the SALESORD database and run the example programs and reports.

Topics covered are:

  • Installing the Example Database

  • Running a QA Build Enquiry Program

  • Running a QA Build Update Program

  • Running a QA Build Menu Program

  • Running a Report Writer Program

  • Testing your DP4 Printer Selection


Using the Multi-Language Option

The multi-language facility helps you to maintain databases containing maps from multiple languages. You enable this facility, from the command line, like this:

MAPMAKE -MULTILANGUAGE

Alternatively, you can enable the option for Multi-language operation within Option 0 - Options on the first menu of MAPMAKE.

The multi-language facility, extends the scope of MAPEDIT facilities:

  • Deleting, printing, or copying maps is performed for maps from all languages, not just those from the current language.

  • The change file option, which prints out all the maps on the current database which are missing or different on the named database, does the comparison for each language, not just the current language.

In Multi-language mode, it is not possible to copy a map from one language to another. However the Map Editor will allow you to open a map in one language and save it to anothere.


Using the Table Planner

The DP4 Table Planner utility prints out the tables and fields in your database. This is a simplified table plan for the SALESORD database:

  1. On the DP4 System Main menu, select the Database Management option

  2. On the Database Structure Management menu, select the Make table plan option

  3. Select the database. DP4 displays this menu:

    Click on the appropriate areas of the illustration above for further information

  4. Select option 1 - Full scale or option 2 - Layout mode. The Table Planner main screen is displayed and the cursor is positioned in the editing screen:

  5. To move to the menu bar, press the <Esc> key. To move to a menu directly, press the <Alt+mnemonic> keys. <Alt+T> takes you into the Table menu:

    Click on the appropriate areas of the illustration above for further details.

    <Alt+O> takes you into the Options menu:

    Click on the appropriate areas of the illustration above for further details.

  6. To generate a single-column plan of all the tables in the database, use these steps:

    • Select the Add all option from the Table menu. DP4 creates a list of all the tables in the database in a single column
    • Select the Print option from the Table menu and select the output destination. If you want to be able to edit the table plan in future, save the plan to a file (see Editing an Existing Table Plan )

    • Select the Exit option from the Table menu and leave the utility

    To control the appearance and layout of the table plan, follow steps 7 to 11 below.

  7. From the Editing window, use the editing keys to move to the position at which the top left corner of a table is to appear

  8. Press <F2> to pop up a list of tables on the database. Use the cursor keys and <Enter> to add a table to the table plan. You can use the text editing facilities described in Editing a Text File to add text, draw lines and colour your table plan

  9. Having completed the table plan, you can print it at layout size or full scale. (Note that <F4> expands the table plan to full scale when working in Layout mode)

  10. Press <F5> to print the table. DP4 prompts you to select an output destination. Select the combination of output devices to which you wish to print. To retain your table plan for future editing, make sure you include the 'file' output option among your selected destinations:

  11. Press <F8> to leave the Table Planner utility


Using the Windows Interface

In Windows, you can use the mouse to perform actions in place of some keystrokes. For example, single-click on a field to move directly to it and double-click to select an option. Push buttons entitled OK and Cancel have the same action as the <Enter> and <Esc> keys.

DP4 utilities prompt for the database name when you run them. The window below, which lists the databases on your system and allows you to choose one, should be familiar to experienced Windows users.

To select the database, either type its name or click on a name in the database list.

To select a database and enter the application, double-click on a database name.

If there are more databases than can fit in the database list, drag the slider down to view them. (Drag means hold down the left mouse button while moving the mouse.)

To exit to the previous screen, click on the Cancel button.


Utilities for Copying the Database Structure

There are two utilities for copying the database structure:

  • The Data Structure and Transfer Utility, RECASTDB

    This utility copies the database structure from the Data Transfer utility source database to the target database, completely overwriting the target's structure. The source and target databases must be on the same system. You can specify whether maps, help messages, Report Writer, Message Manager, QA Build and Importer programs are also copied. This utility is described in Transferring the Database Structure.

    Use the Data Structure and Data Transfer utility in these circumstances:

    • When you have used the database creation utility to create a new, empty database and want to copy the structure of an existing database onto it.

    • When you have modified the structure of a database and want to over write older versions of it with the new structure, while retaining their existing data.

  • The Data Dictionary Extract and Post utility, COPYDB

    This utility copies the database structure in two stages:

    • First you extract the database structure of the source database to a file.

    • Then you post the database structure from the file to the target database.

    Use the Data Dictionary Extract and Post utility if you wish to merge two databases, or if you wish to transfer the structure to a target database on another system.


Utilities for Modifying the Database

The DP4 utilities for modifying the database are:

  • The Database Setup and Maintenance utility (MAKEDB) which allows you to modify the database data dictionary:

    • Add new tables. (See Adding a Database Table )
    • Change table definitions, including the autoincrement, security level, encryption and compression options. (See Adding a Database Table )
    • Add new fields. (See Adding a Table Field )
    • Change field definitions, including the key precedence, sequence number, data type and length, validation values and security levels. (See Adding a Table Field )
    • Change the field format flags. (See Formatting a Field )
    • Change table headings. (See Changing Table and Field Headings )
    • Change table and field names. (See Changing Table, Field and Role Names )
    • Copy tables. (See Copying Table Structures )

  • The Index Link Editor (SETLINK), which allows you to change the attributes of the index links between tables, including 'Check parent' and 'Child delete'. (See Index Link Analysis and Scrutiny)

  • The Number System Utility (NUMBSYS), which allows you to change the number system, depending on the programming language you intend to use. (See Selecting the Database Number System )

  • The Database Reorganisation utility (REORGDB), which allows you to reorganise the database after making a structural change. (See Reorganising the Database )

  • The National Language Collate setup utility (COLLATE), which allows you to change the collate sequence used for database fields of type U. (See Specifying Collate Characteristics of Key Fields )


Utility Files

Temporary Files

The utility files are temporary files that are created by the database manager and the user interface manager. They are stored in the Work files directory, unless otherwise stated:

POSTxxxx.$$$ | Post files contain records to be posted or deleted, but not committed
STORxxxx.$$$ | Store files contain a private cache of data for use by the database manager and the user interface manager
SPOOxxxx.$$$ | Spooler files are used for print spooling, and are kept in the spooler files directory
PRINxxxx.$$$ | Print files are used as a temporary store for print lines
MAPSxxxx.$$$ | Map files may be created by the user interface manager to store details of information displayed on screen


Validate field

Answer 'Yes' if you want DP4 to validate the field. This is the validation that has been set up in MAKEDB for the field (see Adding a Table Field )


Value to match

Enter the data value that identifies the tag field. If you used the import file as a model, this option already has a value


Verify Parent

This is a check that may be applied when updating a record. It prevents a record from being written to the database if the parent is missing, or has been updated since the start of the transaction in any of the indexes that are marked for parent verification in the database. SETLINK sets up an index for parent verification.


Version Control

DP4 allows you to add and view version information to your databases:

  • You can set the Database Version Information by using the Version/Security option of the MAKEDB utility.

  • You can use the Database Integrity checking utility DBCHECK to View the Database Version Information

  • The DBERA utility can be used:

    • To keep track of the changes you have made to your database during QA Build system development

    • To distribute changes to other developers working at other sites on the same QA Build system

See Version Control in the C/C++ Programmer's Reference for information on version control of applications.


Viewing a Text File

  1. On the DP4 Main System menu, select the Run Program option, and type
    VIEW
    .The View utility is invoked.

  2. Select the File option, and enter the name of the file to be viewed. If the file is not in the current/working directory, include its drive and pathname, as in:
    C:\DP4\INVOICE.PRN.

In 4.621, assuming you are using the officially releases 4.621 DP4 configuration file, VIEW is invoked automatically by most DP4 utilities when you select to send printed output to screen. The 4.621 program also creates a SendTo icon for VIEW. You can of course use any other program you like as a file viewer. One advantage of View is that it can usually correctly detect whether a particular file should be displayed using the MS-DOS or the ANSI character set. It is also intended that it should recognise various other other file formats, rather like the QuickView program included with Windows 95, though this has not been released at the time of writing.


Viewing the Database Version Information

  1. On the DP4 Main System menu, select Backup Recovery

  2. On the Database Backup and Recovery menu, select the Check option

  3. Enter the database name

  4. On the Database Integrity Checking menu, select option 1 - Status display. The Status Display screen is displayed, showing the database version information. For example:


Wait for all users to log off

Close the database as soon as the last user logs off.

A batch equivalent of this option is:

SHUTDOWN -DB database_name -WAIT


Wallpaper background

Enable this option to fill empty parts of the screen with a striped pattern


Warnings

Colours used for warning messages


What is the DP4 RDBMS?

The DP4 Relational Database Management System (RDBMS) includes tools for implementing, distributing and maintaining relational databases.

The DP4 RDBMS supports data independence to shield programs that access DP4 databases from the physical storage details of the data. This means that you can modify the database structure without modifying the application, if the logic of the application is not affected.

The DP4 RDBMS supports operating-system independence. You can develop and test a database on one operating system and then port it to another and not incur extra development time.

In DP4's implementation of the relational database model, entities are represented as tables, instances of entities are represented as records and attributes of entities are represented as fields. You do not need to set up the relationships between tables explicitly; DP4 automatically derives the structure of the database from an analysis of its tables. For example, a parent-child link is derived where you include the primary key of the parent table in the child table.

The DP4 RDBMS supports referential integrity, which allows you to set rules that define how a change on one table affects related tables. This can help to reduce application development time.

The DP4 utilities carry out the tasks you want on the database, depending on your responses to a series of questions, in most cases requiring yes/no answers or option selection from menus. Default responses are suggested where appropriate and context-sensitive help is always at hand.

The DP4 RDBMS provides special facilities for the development of applications in multiple international languages. A multi-language application supports the differing language requirements of its users at runtime.

Significant among the security and integrity features offered by the DP4 RDBMS are:

  • Automatic maintenance of a transaction log and roll-back log for database recovery.

  • Contention control and record locking in multi-user systems.

  • Enforced database backup and integrity checking schemes.

Together these facilities allow you quickly to deliver powerful, robust, DP4 databases upon which applications can be built with confidence for client/server environments.


When to Back Up your Database

DP4 helps you to maintain your database by reminding you to take backups at regular intervals. Back up the database in any of the following circumstances:

  • You make structural changes to a database.

  • DP4 warns you that the database is due to be backed up. (You should set a suitable maximum interval between backups using DFSETUP. See Setting up Transaction and Roll-back logging.

  • DP4 warns you that the transaction log file is approaching its maximum size. If a set limit has been reached, the database manager allows you to read records on the database, but not to update them.

  • You wish to copy or move the database to another system. In this case, back up the database to removable media, and use the database restore utility on the destination system. The procedure is the same if the destination system has a different operating system. (Of course if you have a direct network connection to the other machine it will be much easier to copy the database files directly.)


Windows

Colours used to display pop-up and choice maps


Word for Yes/Word for No

These options are as described in Formatting a Field


Word for Yes/Word for No

Enter up to three characters for the words for 'Yes' and 'No' in the language


Workstation

The client machine, including the operating system, visual display and keyboard used by the application.


Year first

Enable this to show the year as the first date item, for example 1995 10 Dec. Disable it, to show the year in the last position, for example 10 Dec 1995


Editing DP4 Menu Pages

The Map Editor contains options for editing the maps used the DP4 menu program (DP4 itself). These options also allow you to edit the other information associated with the menu, notably the commands executed by the options in the menu, and, for options that require them, secondary maps used for requesting values for parameters in the command.

Usually you will probably use the DP4 menu page options via the MENUEDIT program which contains a number of other facilities you will need to set up a proper DP4 menu, but this is not actually necessary except when you need the additional facilities.

In order to edit DP4 menu pages directly from the Map Editor you first need to use the Open DP4 Menu Pages option. This opens a dialog similar to the Open Mapset dialog, and allows you to select the database and language for which the menu pages are to be edited, and to choose the page to select initially. After you complete the dialog a window similar to the mapset window containing all the menu pages is opened:

Note that the toolbox is slightly different from normal. It is explained here.

In general editing a DP4 Menu Page is just like editing any other map. The major difference is that the Control Property Dialogs are slightly different:

  • The Access Page is removed.
  • Where relevant an Option Action page is added. The Option Action page is explained here.

There are also options to copy and delete one or all menu pages. These work just like the corresponding options for copying maps and mapsets. The only difference is that the menu option information associated with the maps for the menu pages is also copied or deleted.


Option Action Properties

The Option Action Page looks like this:

Click on a feature in the illustration above for further information.


DP4 Menu Page Toolbox

The DP4 menu page toolbox is used exactly like the ordinary Map Editor toolbox as described here. It contains the following tools:

Pick Tool for selecting/manipulating Controls | Button Control | Text Control
Option Control | Group Box Control | Bitmap Control
Icon Control | Line Control | Tab Button Control
Header Control | Hidden Option Control |

Text Controls are used for adding background or informational text, not associated with menu options.

Option controls are numbered text controls which can be selected and clicked to invoke a menu option.

Hidden Option controls are numbered options in the menu which do not have a visible control for selecting them. The most common hidden option is option 0, selected when the user escapes from a menu page, but you can set up other numbered hidden options if desired. Hidden options are displayed at the top left of the page at edit time, but are invisible at run time.