Legler Systems Company | DB System | Contact Us | Help | Home |

Database Control System

The Database Control System (DBS) by Legler Systems is based on the client-server model and contains basic support procedures and special functions that identify and maintain the users, codes and system options associated with each online accounting application by Legler Systems. This general-purpose accounting software is designed for small businesses and midsize companies. Generally, access to this Database Control System is reserved for supervisory personnel. The Legler Database Control software utilizes a character-based user interface with mouse support on desktop or laptop computers for fast data entry and optimum end-user productivity in a multi-user environment.

The 2017 DBS software version is now available with recent changes described in the Accounting Software Revision History document. The latest updates include four different real-time transaction dashboards under the DB menu, plus the Report History Dashboard showing the prior runs of a report or inquiry procedure. This Database Control System can function with or without a mouse for greater end-user flexibility and productivity.

After installations on different hardware and operating environments, this legacy database control software has evolved over the last 25 years to an efficient and productive business application for today under Windows 10. Custom versions of the online DBS software support in-house control of confidential database transaction records and have been in day-to-day operation in small businesses in Northern California for many years.

Database Control Menu:

The database control (DB) procedures the end-user can run are identified on the Database Control menu as shown below. Before any online DB procedure is run, the user must first sign-on by entering a valid User-ID and password. To display the Database Control menu, enter "DB" on the main Financial Menu. To start a specific DB procedure, enter the 2-digit number associated with that online procedure or single-click the left mouse button when pointing to the procedure name on the Database Control Menu. The active keyboard function keys are identified at the bottom of all screens, including the DB menu.

 -----------------------------------------------------------------------------
|      DATABASE CONTROL SYSTEM MENU  (2017)              01-09-2014 11:37     |
|=============================================================================|
|                                                                             |
| No     Procedure Name                  No     Procedure Name                |
| --   --------------------------------  --   ------------------------------  |
| 01 - Enter Code Definition Record      10 - Print Company Control Report    |
| 02 - Print Code Definition Report      11 - Load And Print Support Files    |
| 03 - Enter Company Control Record      12 - Print User Defined Report       |
| 04 - Query Global Control Record       13 - Print User Activity Report      |
| 05 - Dump Master File To History       14 - Inquire Active Users            |
| 06 - Restore Master File From History  15 - Print Transaction Audit Report  |
| 07 - Print Master File Records         16 - Inquire Pending Batch Requests  |
| 08 - Enter User Control Record         17 - Enter General Notes Record      |
| 09 - Print User Control Report         18 - Process Import/Export Data      |
|                                        92 - Redefine System Printer         |
|                                        94 - Search DB System                |
|                                        96 - Inquire Latest DB Changes       |
|                                        99 - End DB Menu                     |
|                                                                             |
|                                                                             |
|     Enter DB Procedure Number......[    ]                                   |
|                                                                             |
|                                                                             |
|                  Dashboard 1 - Number of Data Records Changed               |
|             ----------------------------------------------------            |
|              10 |                                               |           |
|               9 |                                               |           |
|               8 |-----------------------------------------------|           |
|               7 |                                               |           |
|               6 |-------------------XX--------------------------|           |
|               5 |                   XX                          |           |
|               4 |-------------------XX--------------------------|           |
|               3 |                XX XX                          |           |
|               2 |----------------XX-XX-XX-----------------------|           |
|               1 |                XX XX XX XX                    |           |
|             ----|---|-------------------------------------------|           |
|              MO | 01 01 01 01 01 01 01 01 01 01 12 12 12 12 12  |           |
|              DA | 10 09 08 07 06 05 04 03 02 01 31 30 29 28 27  |           |
|              YR | 14 14 14 14 14 14 14 14 14 14 13 13 13 13 13  |           |
|                                                                             |
|                   Total transactions = 12 (3 per day)                       |
|                                                                             |
|=============================================================================|
|F1=Help  F2=Enter F9=Print F10=Exit                       Dashboard=F4/F5/F6 |
|                                                                             |
 -----------------------------------------------------------------------------

DBS Software Procedures:

Data entry procedures DB01, DB03 and DB08 display the data fields that comprises each data record and perform the update functions (add, modify and delete) to the online Master File (DBSMST) based on a Transaction Code. The Company Control Record (DB03) defines the current accounting period (YRMO) and prior accounting period (YRMO), sequence number counter for assigning voucher numbers, as well as other important system options. The User Control Record (DB08) defines each user and the computer procedures within an application that user has access to. The Code Definition Record (DB01) defines each type of code and code value (e.g. GL account code) that is entered online by the user. Any notes or comments about vendors, customers, customer orders, products and purchase orders are entered in procedure DB17 (General Notes Record) or from one of the data entry procedures with a Trans-Code of "N".

For all DB inquiry and reporting procedures, a selection screen is displayed so the user can enter various data selection parameters to retrieve specific data records from the online database (DBSMST) and to format the output. Prior data selection parameters are shown in the Report History Dashboard under the selection screen.

Online reporting procedures DB02, DB07, DB09, DB10, DB13 and DB15 print a management reports on a line printer (computer-size paper) or a laser printer (letter-size paper). Management control reports can be displayed in the current window or in a separate report window prior to printing a hardcopy. Each online reporting procedure displays a separate selection screen for the user to enter report selection parameters, such as dates, codes, report format option and report sort option. Procedures DB04, DB05, DB06, DB11 and DB18 print a one-page DBS Control Report showing the number of data records processed for audit documentation along with the elapsed execution time in seconds. The print User Defined Report procedure (DB12) is a general report writer for data mining, where the user can test specific data fields and define the content and format of any management report or display screen.

Inquiry procedure DB14 displays either active users currently on the system or all users that can access the financial systems. Procedure DB16 displays all outstanding batch report requests that were entered online with "Entry Correct" of "B".

Load Support Files procedure DB11 reads three standard text files (that can be changed with most any editor) and creates three indexed-sequential files containing the definitions of (1) all data fields that the user enters on screens (DBSFMT), (2) online data field help text and error messages that are displayed in a separate Help Window whenever keyboard function key F1 is pressed (DBSHLP), and (3) program names and file specifications used to create the Global Control Record and to link a procedure number to a computer program (DBSPGM). Conversion of uppercase letters to lowercase letters for field names and prompt names in DBSFMT is supported under ASCII coding (default) and EBCDIC coding. Procedure DB04 allows the user the capability to change temporarily the file specifications online. Procedure DB11 prints a Data Dictionary for each accounting sub-system, as well as Help Messages Report for management review.

The Process Import/Export Data procedure (DB18) allows the user to select and export data from the online Master File to a PC Text File (a csv file where data fields are separated by commas) for subsequent input to a spreadsheet, such as MS Excel. The import process is generally used to initially establish the beginning values when the system is first installed to avoid manual entry.

Redefine system printer procedure 92 allows the user to redefine where the database reports are to be printed. If a disk file is specified, then the database report is written to disk from which it can be displayed in a pop-up report window (92V) and then printed on paper by pressing function key F9. Procedure 92 (program DBS892) is started from any accounting system menu.

Search DB system procedure 94 allows the user to search for any dollar amount, date, code or text in all or selected data records. The number of database records searched and found is displayed with the search results. Both uppercase letters and lowercase letters are tested. For EBCDIC coding (IBM mainframe computers), System-Option-07 must be set to 6 or 7 in the Company Control Record, otherwise ASCII coding is assumed. Procedure 94 (program DBS894) is started from any accounting menu.

Inquire Latest DB changes procedure 96 allows the user to display the most recent changes to any database record associated with data entry operations. The number of database records displayed depends on the search limit specified by the user. Procedure 96 (program DBS896) is started from any accounting menu.

The online DB end-user procedures identified on the Database Control menu are described in more detail in the DB Menu Procedures document, including a description of the database control transaction dashboards and Report History Dashboard. By clicking on a selected highlighted link in the above Database Control Menu, the associated sample screen is displayed.

Database Control Software Features:

In addition to the operational, security, technical and customization features described in the Accounting Software Overview document, some key functional features of this database control software are highlighted below:

1. Allow the operator to define codes and control options that affect how the various financial modules function.

2. Identify each user and the procedures each user has the authority to execute, as well as reporting on the computer procedures a user performed and how long the procedure took.

3. Monitor changes to the online Master File (database) through transaction logging, so deleted data records are identified and reported.

4. Flexible online reporting capability allows the user to request and print 27 different Database Control Reports, including a general report writer (Print User Defined Report procedure DB12 for data mining) by specifying report selection parameters and a report sort option.

5. For real-time Bar Chart Dashboard (No. 1), a daily (or monthly) bar chart of the number of database transactions is shown under the database control menu. Up to 4 different real-time transaction dashboards for database transactions are displayed: Two real-time Bar Chart Dashboards and two real-time List Dashboards. A Report History Dashboard is displayed under the parameter prompts for a DB report or inquiry procedure showing the last 12 runs.

6. The DBS software supports dynamic linking to data entry procedures (such as DB14 to DB08) with a single keystroke (function key F7). Dynamic linking to existing General Notes Record (DB17) containing special notes about a user is done with one or two keystrokes (F7) or double-click of the left mouse button when the word "NOTES" appears on the DB08 data entry screen.

7. Online processing involves instantly viewing and changing control data in the online Master File based on a Transaction Code (Trans-Code) for added accuracy. One transaction is processed at a time.

8. Support batch job processing (optional), where report selection parameters are entered online for later batch processing by a separate job script named Jobs.bat, so large reports (long running tasks) can be printed offline.

9. Record level locking is performed by all financial programs when updating the online Master File with accounting data to ensure the accuracy and completeness of information in a multi-user environment.

10. Provide special file update edits to prevent the "lost update" problem when two or more users are updating the same accounting record at the same time, so the first change is not overridden by the second change. When this condition occurs an error message is displayed so the user can re-query the changed accounting record to apply his changes, thus solving the "lost update" problem.

11. Allow the financial software by Legler Systems to be run directly from a hard disk, compact disc (CD-ROM) or USB flash drive or thumb drive to support the access to the backup Master File, since all data in the online Master File (LSC directory) should be copied periodically to another hard disk, CD-ROM disc or USB flash drive or thumb drive for complete backup in case of hardware failure and stored off-site for added protection and safety.

12. Source cobol programs have been revised to reference a new copy member (SLP310) for IBM AIX system call to execute the Unix chmod command when an output file is opened and the OPR-SYS-PARM is set to "AIX".

13. Numeric data definitions have been revised to ensure that the storage allocation is either 2-bytes, 4-bytes or 8-bytes regardless of the cobol compiler used. For example, some 32-bit cobol compilers allocate only three bytes of storage for binary data defined as 9(6) COMP, so the Picture clause was revised to 9(8) COMP.

DBS File Specifications:

The online Master File (DBSMST) contains the detail data records for all the financial systems and is an indexed sequential file where each data record is identified by a unique primary key. Up to five alternate keys are also defined to provide alternate paths to quickly access data in the file thereby reducing the number of disk I/O when printing database reports. For example, one alternate key is the system date which is used to query detail data records chronologically in descending date order.

Support files in the financial systems are:

1. Transaction Log File (DBSTRN) contains data records that were deleted or modified online.

2. User Log File (DBSUSR-indexed) contains the date, time and report selection parameters for each procedure a user has executed.

3. Backup History File (DBSHST) is a sequential file containing duplicate data records from the online Master File (indexed) after the dump process by procedure DB05.

4. Transfer File (DBSTFR) is used to pass records between other accounting systems.

5. Format File (DBSFMT-indexed) contains the screen prompts and data field names used by the data entry, inquiry and reporting procedures.

6. Program File (DBSPGM-indexed) contains the program names and disk location associated with each procedure number in an accounting application.

7. Online Help File (DBSHLP-indexed) contains text messages on each data field, report parameter prompt and error message that appear in a pop-up Help Window whenever keyboard function key F1 is pressed. Text records in the online Help File are indexed by System-ID and help message number.

8. Report Work File (DBSRPT) and Report Sort File (DBSSRT) are temporary work files used when sorting records used to print a database report.

Company Control Record:

The Company Control Record contains important dates, accounting periods (YRMO), system options and sequence number counters that apply to the financial accounting modules. Generally, there is only one Company Control Record per System-ID. Procedure DB03 is used to change (modify) accounting periods each month and query Company Control Record. The accounting period number (YRMO) is composed to a 2-digit year and 2-digit month or week number and controls the time period of accounting transactions entered into the computer. Three different sequence number counters are defined in this control record, as Seqno-1, Seqno-2 and Seqno-3. The primary key of the Company Control Record is the Record-Type code, Company-ID and System-ID.

Code Definition Record:

There a number of codes that the user can define by a Code Definition Record. For example, each GL account code is defined by separate Code Definition Records. The user initiates procedure DB01 to add, change (modify), query and delete a specific Code Definition Record by entering the Code-Type and Code-No fields. The primary key of the Code Definition Record is the Record-Type code, Code-Type and Code-No.

User Control Record:

Each user that is to have access to one or more accounting system procedures must be defined by a User Control Record. Procedure DB08 is used to add, change (modify), query and delete a User Control Record. The primary key of the User Control Record is the Record-Type code and User-ID. The User Control Record contains other user options, such as the screen color code for background color and foreground color, display real-time dashboards, show dollar sign ($) in all dollar amount fields and Help Window screen colors.

General Notes Record:

The General Notes Record contains special notes, comments or instructions on different data fields, such as customer number, vendor number, order number, product code, contact name, and PO number. Procedure DB17 is used to add, change (modify), query and delete a General Notes Record. Dynamic linking to these special notes or comments is easily done by pressing function key F7 on the data entry screen where the word "NOTES" appears. The primary key of the General Notes Record is the Record-Type code, Note-ID and Code-No.

DBS Software and Hardware Requirements:

The Database Control System operates on any x86 personal computer (desktop or laptop) in the Command Prompt window using any version of Microsoft Windows operating system from MS Windows 3.1 to Windows XP and now Windows 10. The source programs are compatible with 32-bit cobol compilers (such as Micro Focus Net Express) that generate executable code for MS Windows 10 (64-bit version). Personal computers can be connected in a local area network (LAN) with complete flexibility in specifying the disk location of master files on the desktop or laptop client computer or the file server. Additionally, the DBS software can be run on a single stand-alone personal computer or on a Unix based computer, such as the IBM RS/6000 with IBM 3151 terminals using the AIX operating system. This Database Control software has been ported to the HP-3000 COBOL environment. In addition to the standard screen size (80x25), a larger character-based window of 80 characters wide by 50 rows (80x50) is now supported for greater usefulness and allows real-time dashboards to be displayed under the DB menu. The newest release of the database control software supports 32-bit code (.dll) or 16-bit code (.exe) depending on the OPR-SYS-PARM setting ("WIN" or "DOS") in the DBSPGM file. Additional screen color codes are available with the 32-bit code.

The Run Time Environment for Micro Focus COBOL support is included at no extra cost with the DBS executable programs. The MS Windows database control programs (32-bit code) are the same as the DOS database control programs (16-bit code) for compatibility. For financial data archiving and historical reporting, the same DBS executable programs can be run offline directly from a removable device, such as a CD-ROM disc or USB flash drive or thumb drive; thereby eliminating the need to restore old financial data and programs to a separate hard disk drive in order to view or print management reports.

This Database Control System is required to support any of the online accounting modules by Legler Systems in a multi-user environment. The DBS source programs are written in structured ANSI COBOL (COmmon Business Oriented Language), the most widely used business programming language, for easy maintenance in a client-server (ASCII) or IBM mainframe (EBCDIC) environment. A Micro Focus cobol compiler is used to generate the DBS executable programs for personal computers. Each DB end-user procedure identified on the Database Control menu corresponds to a separate cobol application program.

More Information on the Database Control Software:

The DB System software cost is identified in the Software Price Sheet and includes: (1) DBS executable programs for Windows XP or Windows 10, (2) DBS Procedure Manual and (3) online Help File. The technical DBS Systems Manual and cobol source programs can be purchased separately.

To request sample database reports and screen formats, General Information Manual or a free working demo for Windows 10 on a CD-ROM disc of this DB System, e-mail Bill Legler in the San Francisco Bay Area, California.

Legler Systems Company, developer of the online Database Control software, is located in the East Bay of the SF Bay Area in Northern California and provides local on-site DBS software support in Alameda County (major cities are Oakland and Berkeley), Contra Costa County (major cities are Concord, Walnut Creek and Orinda) and other counties in California (CA). E-mail support is always available regarding any DBS software questions or concerns.

Version 11.7 (2017). All rights reserved by Legler Systems Company.