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

Database Control System

The Database Control System (DBS) developed by Legler Systems in California (CA) is based on the client-server model and contains basic support procedures and special functions that identify and maintain the users, codes, disk files and system options associated with each online accounting application. 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 latest 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 database control menu, plus the Report History Dashboard showing the prior runs for each report or inquiry procedure. This Database Control System is easy to use and can function with or without a mouse for greater end-user flexibility and productivity.

After installations on different hardware and operating systems, this reliable database control software has evolved over the last 25 years to an efficient, proven and productive business application for today under Microsoft 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 System Menu (screen image 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 from the main Financial Menu, enter "DB" to the System-ID prompt. To start a specific DB end-user 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 System Menu screen. The active keyboard function keys are identified at the bottom of all screens, including the database control menu.

DBS Software End-User Procedures:

Data entry procedures DB01 (screen image), DB03 (screen image), DB08 (screen image) and DB17 (screen image) 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 directly in data entry procedure DB17 (General Notes Record) or from one of the application data entry procedures with a Trans-Code of "N".

For all DB inquiry procedures 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 basic application 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. Update 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 the management report or display screen.

Online inquiry procedure DB14 displays either active users currently on the system or all users that can access the financial systems. Inquiry 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 fixed length text files (that can be changed with most any text 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 Message 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 IBM EBCDIC coding. Update procedure DB04 allows the user the capability to change temporarily the file specifications online. Reporting 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 values or fields are separated by a comma) for subsequent input to a spreadsheet, such as MS Excel. The import process is generally used to initially establish the beginning data values when an accounting application is first installed to avoid manual entry. The input Text File (a CSV File) is processed by DB18 to reformat the variable length records (csv file format) to fixed length data records and write them to a Transfer File. Individual accounting applications process this Transfer File to edit and update the online master database.

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 a dollar amount, transaction date, code or text in all data records or specific data records. The number of database records searched and found is displayed along with the actual search results. Both uppercase letters and lowercase letters are tested. For EBCDIC coding used with mainframe computers (IBM Zsystem), System-Option-07 must be set to 6 or 7 in the Company Control Record (DB03), otherwise the ASCII coding structure is assumed (primarily used with personal computers and file servers). Procedure 94 (program DBS894) is started from any accounting system menu.

Inquire Latest DB changes procedure 96 allows the user to display the most recent changes to 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 system menu.

Each online DB end-user procedure identified on the Database Control System Menu is described in more detail in the DB Procedure Menu document, including a description of the database control transaction dashboards and Report History Dashboard.

When 99 is entered as the DB procedure number, the main Financial Menu is displayed.

Database Control Software Features:

In addition to the operational, security, technical and customization features described in the Accounting Software Overview document, important functional features of this proven 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. One-time ad-hoc database reports are easily printed.

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. This user-friendly DBS software supports dynamic linking between data entry procedures (such as DB14 to DB08) with a single keystroke (function key F7) or double-click of the left mouse button. The linking data field is marked by an up arrowhead. 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 database 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 (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 for complete backup in case of hardware failure and stored off-site for added protection and safety. The financial applications can be started directly from these backup devices on any computer.

12. Cobol source programs have been revised to reference a new common Copy member (SLP310) for the IBM AIX system call to execute the Unix chmod command, when an output file is opened and the OPR-SYS-PARM field 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 data records (fixed format) 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 Message Window whenever keyboard function key F1 is pressed. Text records in the online Help File are indexed by System-ID and help message number. A drop-down list of valid codes for a field (marked by a down arrowhead) is also shown in the Help Message Window when the F2 key is pressed after the F1 key. The Help Message Window temporarily overlays the bottom half of the basic application window (Command Prompt window).

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. Code-Types beginning with "Z" are reserved to identify those codes that support a drop-down list. Each individual code value (Code-No) in a drop-down list is defined by a separate Code Definition Record.

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. Data entry 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 Message Window screen color codes.

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. Data entry 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 Windows 3.1 to Windows XP (16-bit) and Windows 7 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 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 client laptop or desktop 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 computer and IBM mainframe computer (Zsystem). In addition to the standard application window size (80x25), a larger Command Prompt 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 database control menu.

The 2017 version 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 exe programs run under Windows XP and the dll programs are used with Windows 10. AIX programs use the .gnt code. The DB application programs (32-bit code) run on 64-bit hardware.

The Run Time Environment for Micro Focus COBOL support is included at no extra cost with the DBS executable programs. The Windows 10 database control programs (32-bit code) are the same programs 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; 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 the other 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 code) or IBM mainframe (EBCDIC code) environment. A Micro Focus cobol compiler is used to generate the DBS executable programs for personal computers using 32-bit or 64-bit addressing. Each DB end-user procedure shown on the Database Control System Menu screen corresponds to a separate 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 the Microsoft Windows environment, (2) DBS Procedure Manual and (3) online Help File. The technical DBS Systems Manual and DBS source programs with common Copy members in the ANSI COBOL language can be purchased separately.

To request sample database reports and screen formats or a free working demo of this user-friendly DB System on a USB flash drive or CD-ROM disc for any version of Microsoft Windows, e-mail Bill Legler in the San Francisco Bay Area, CA.

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. E-mail support is always available regarding DBS software questions or concerns.

Version 11.8 (2020). All rights reserved by Legler Systems Company.