GB2294134A - Accessing computer databases - Google Patents

Abstract

A system for accessing computer databases in which instructions for retrieval of information are transmitted directly to any number of source databases for execution on a host machine having first been translated from a common query language. The data thus generated is then translated into a common format before being presented to a user. Thus, the user does not need to specify the location of the data, its format, nor the query language of the host machine. <IMAGE>

Description

ACCESSING COMPUTER DATABASES The present invention relates to accessing of computer databases.

At the present time there is, a rapid growth in the volume of technical, commercial and financial information is being held on computer databases throughout the world.

These databases have many advantages over their conventional, paper-based predecessors. Perhaps their most important advantage is that arrangements can be made to allow users to access these databases from virtually any location in the world where to a telephone line or any other suitable communications system can be used.

However, despite the proliferation of computer databases throughout the world and their ever-increasing complexity, their use within business has not been as widespread as might have been expected. It is believed that a dominant reason for this is that technical difficulties are encountered by users when endeavouring to access information stored in databases of differing and often incompatible design.

At present, databases have been set up throughout the world largely independently of one another. The overwhelming majority of existing computer files and databases have been constructed to meet the specific processing and procedural requirements of their creators.

Despite the impressive efforts of national and international standards authorities, particularly in the area of Electronic Document Interchange (EDI), the administrators of each database typically defines its own standards for storage of its information, frequently modifying these from published standards. This, inevitably, leads to the same data being held in disparate formats in diverse databases. The best known and most widespread example is the storage of dates, for which no fewer than eleven "standards" can be encountered in mundane applications. Similar examples can be found throughout the world in the content, layout and field-length of something as simple as a corporate name and address. Thus, elementary information which is apparently identical and held in similarly named fields on two different databases can actually represent substantially diverse realities.The simple numeric date "011094", for example, is likely to represent "lust October 1994" in the United Kingdom, but "10th January 1994" in the United States. Furthermore, the intimation of the status of individual elements of information or of complete records can vary dramatically between applications. Whilst one commercial database of credit accounts may "mark" an invoice payment as overdue after 30 days, another will not amend its records to reflect this status until 60 days have elapsed.

Another major inhibitor to the full commercial exploitation of the wealth of information stored in the world's databases is the absence of a universal standard command language for its access, interrogation and manipulation. Where there is a requirement to obtain information from dissimilar databases, the user is customarily required to learn new command languages.

Apart from being a costly and time-consuming academic exercise, the inevitable behavioural resistance to change will often lead to a reluctance to seek essential information from the "alien" environment.

Such reluctance is exacerbated by the typical requirement to log on to each database individually, usually to meet the security criteria of the database owners. This mandates the knowledge of a distinct user name and password for each discrete database and network, those typically being of different construction, format and standard. Experience forewarns that, where a user requires regular access to about six or more separate environments, the need to remember the diverse procedures for access almost invariably leads to the maintenance of illicit records of access routes and codes with a consequent compromise of the security of the environment.

One solution to this problem lies in the creation of "federated databases". In a system of federated databases, a number of databases are downloaded onto a single host system and each of these databases can be accessed by an authorised user logged onto that system.

The system allows each of the databases that it holds to be accessed using a single command language defined by the host system and once the user has logged onto the system itself, the system will grant access to all of those databases that the user is entitled to use.

Typically, the user will be charged for the access made to the databases by the database host who will then pass on payment to the database supplier.

Federated databases, however, have their own problems. Firstly, the act of downloading creates difficulties. There is the simple cost and effort involved in sending a large amount of data from where it is compiled and kept to the database host, with no guarantee that any particular item of data will generate revenue from users. Secondly, the copy of the data maintained by the database host will always be out of date as compared with the data at its source. Thirdly, each database of a host will typically be downloaded at different times and frequencies. This causes a lack of currency between the databases which may cause problems where the user wishes to compare data from different database sources. Fourthly, the user must be aware of the different ways in which the data is held in the different databases of the host, and search in these databases accordingly.This can very easily lead to confusion with the potential that information will not be found because the user searches on one database using a strategy suitable for searching in another.

Finally, there is a practical limitation to the number of physical databases capable of being combined to provide a federated database. The norm is less than ten.

It is evident that, whilst the development of a federated database approach provides a more convenient route to multiple databases than that furnished by the conventional method of separate authorised access to each individual source, many real problems remain unresolved.

Some of the problems associated with federated databases have been addressed by the so-called "open systems" approach. In such systems, a multiplicity of computers are interlinked to allow users to access a multiplicity of databases on each computer. Each of these computers must be capable of executing a common command language and must react to commands given to them by a user in that language in a closely defined manner. No computer which fails to meet these standards may be linked to the open systems network.

The open systems approach also has many disadvantages. Most obviously, for an existing system to join an open systems network, it must be reconfigured to comply with the open systems rules. This may be a far from straightforward task since it may involve restructuring of much of the computer's software from its operating system upwards and will, in most circumstances, require reformatting of the data within its databases.

Additionally, the open systems approach lacks flexibility. As is well known, within computing change typically occurs very rapidly and standards can rarely, if ever, be considered to be permanent. Thus, while the standards laid down for an open system may be appropriate at the time that they are made, it is likely that developments will allow them to be improved upon very shortly afterwards. As technology improves and becomes accessible to an ever widening community of commercial interests, its requirements and standards will evolve to meet the continually growing complexity of interrelationships between trading partners However, with the open systems approach, it is not possible for individual computers to change their mode of operation with the result that many potential gains in performance will not be available, until such time as circumstances have changed so far as to justify a complete re-writing of the standards.

It is an aim of the present invention to provide a system which allows users to access a multiplicity of computer databases in a consistent and seamless manner and which avoids or ameliorates at least some of the disadvantages of federated databases and open systems.

According to a first of its aspects, the invention provides a method of accessing computer databases comprising the steps of first transmitting instructions for the retrieval of information directly to any number of source databases, irrespective of their location, construction or standards and subsequently implementing any required on-line transactions directly on the chosen source computers.

A significant departure from existing solutions facilitated by this fundamental principle is the ability to transmit commands from the a parallel processor carrying out the method to any number of object computer databases simultaneously, rather than accumulating information from each source sequentially. The concurrent processing of the interrogation, search and retrieval of information is consequently afforded by the object computers. The communications hardware, software and technology to achieve this solution are commercially available at present.

According to a second aspect of the present invention, there is provided method of obtaining information from one or more computer databases comprising receiving a request for information from a user; ascertaining from a predefined list from which database or databases the requested information may be obtained; generating from predetermined rules for the or each selected database a command to obtain the information and submitting it for execution; receiving information from the or each database and converting it to a form suitable for the user; and outputting the converted data.

By generating commands for each database from commands input by a user, the user is detached from the database and the user need not learn the command languages used by the individual databases. Additionally, by conversion of the data received from each database to a common format, the user is presented with data in an expected form, rather than in the form used by a particular database. It will be seen that access to a new database requires only the creation of suitable information concerning data available in the new database, rules defining its command language, and procedures to convert its data, these being possible substantially irrespective of the command language or data format used by the database.

From a third of its aspects, the invention provides a system through which a user can gain access to one or more computer databases, the system comprising input means for receiving a request for data from a user, a data dictionary comprising a list of databases and details of data stored thereon, a router operative to submit commands to the databases known to the data dictionary, the system, in use, on receiving a command through the input means, selecting, by means of the data dictionary, one or more data, one or more commands to be sent by the router for execution on one or more remote database systems, the information generated by those systems being then output to the user.

The computer hosting the system is preferably a mainframe parallel processor. Existing examples include nCUBE 2 and ICL's Goldrush.

The system's host machine preferably supports a standard data dictionary, such as ICL's DDS (Data Dictionary System), containing detailed information relating to all subscribers to the system, including databases, fields and transactions available to users. In this context the term 'subscribers to the system' being provided. The subscribers being those Users accessing the system, and those systems who are connected to the service allowing their data to be made available. As far as the DDS is concerned, the use will be expanded from that of a Dictionary to that of a Thesaurus, in that it will hold information about data held on all other systems networks and routes being accessed by the service.The system host will itself manage subsequent passwords for access to all networks, databases and transactions needed to effect the requests or commands generated by the user.

The system will maintain a full audit trail in order to provide both security and a financial charging system.

An embodiment of the invention will now be described in detail, by way of example, with reference to the accompanying drawing in which the sole figure is a block diagram of a system embodying the invention.

The system comprises a terminal 10 being input means at which a user may enter typed commands and view data on a display. The terminal 10 is connected to a main computer 12 which controls operation of the system. The computer may be conventional or, more preferably, may have multiple parallel processors.

The computer 12 is connected to a wide area network through a router 14. Connected to the wide area network are several database systems A, B, C, D. Each database system is connected to the wide area network through a respective application interface 16A..16B, as will be described below.

Ten principal steps are carried out by the system when a user wishes to obtain data from a database.

1. The user enters a request for data at the terminal 10 and this is transmitted to the computer 12.

2. The computer 12 consults a data dictionary to ascertain which databases contain the information requested by the user, and consults an internal register to establish which of these databases the user may access. For each suitable database, the system generates a command or commands for obtaining from the database the data requested by the user.

3. The generated commands are each fed to the router 14 which passes them over the wide area network to the database for which it is intended. In cases where the computer 12 is capable of parallel processing, several or all of the commands are preferably handled simultaneously.

4. Each application interface 16A. .16D receives a command from the wide area network.

5. The application interfaces 16A. . 16D each converts its respective command to a form in which it may be fed to the respective database A..D so as to mimic a search command entered at a terminal connected directly to the database A..D.

6. The database executes the received command and outputs its results to the respective application interface 16A..16D.

7. Each application interface 16A..16D transmits the data over the wide area network from which it is received by the router 14, and transmitted to the computer 12.

8. The computer 12 monitors the received data to verify its integrity until all of the expected data has been received.

9. The data is processed for output into the format expected by the user.

10. The processed data is output to the terminal 10.

The above ten steps will now be expanded upon.

The request entered by the user comprises one or more statements in a command language which is defined by the system. The commands entered are independent of the database or databases on which searching will actually take place. Indeed, the user may not know at this stage what databases will be used, since this is something which is primarily determined by the system. The particular command language selected for use by the system is largely determined by convenience. The design of such languages is a task well known to those skilled in the art.

In step two, the system decides on which database or databases the information requested by the user may be found.

Information regarding the data held on all databases of which the system is aware is stored in a data dictionary. Additionally, the data dictionary contains details of the command language used by a particular database and instructions to the system to specify the manner in which commands entered in the system's own language may be translated into equivalent commands for execution on each database system.

Thus, by means of the data dictionary, following entry of the command, the system first determines those databases on which the information requested by the user is available. Then, for each database, a set of commands to access the information stored thereon is constructed.

In systems where parallel processing capability is available, the generation of commands for all databases takes place concurrently, so that the execution time for step two does not increase linearly as additional databases are added to those selected for searching.

In step 3, each of the commands generated in step two is submitted to the router 14. Again, where multiple processors are available, the commands for all of the databases may be submitted substantially simultaneously.

The router, which may be of a substantially conventional type, applies the command data to a local or wide area network. The router attaches to the command data information according to the particular protocol used by the network concerning the intended destination of the command, and any other data required by the protocol. The data is then handled by the network and delivered at its intended address.

Each database A..D communicates with the network through an application interface 16A..16D. The application interface receives the command data from the network and converts it to a form which may be processed directly by the respect of database systems, as if it were a command entered by a user at a terminal. This command is then transmitted to the database system via an input line, which is substantially identical to a terminal line.

When the database system receives the command from its application interface, it executes the command and generates output data as a result. This is output from the system on a communication line also substantially identical to a line for connection to a terminal. In the present invention, the output data is received by the application's interface.

The application's interface processes the output data and applies it to the network for transmission back to the main control computer 12. The computer 12 verifies the data received from the network to ensure that it accords with the definitions laid down in the data dictionary. The data is then reformatted into the standard format for the system and output to the user at the terminal 10.

The system can handle security at a number of levels.

Firstly, a user is required to log into the system itself, and to provide a user name and password.

Thereafter, the user is not required to perform any further logging in procedure. For each user known to the system, there is kept a list of databases to which that user has access, together with user names and passwords to be used. Also, for each database which the system as a whole can access, details of the procedures required to log into that database are stored. Whenever access to a database is required by the system and prior to attempting to execute a searching command, the required logging in procedures are generated by the computer 12, and sent over the network by the router 14, to be translated into the equivalent of a logging in command by the appropriate application interface 16A..16D.

A further level of security may also be provided. It may be decided that some users have only restricted access to a particular database. Where a particular field of a database record is not available to all users, a note may be made of this in the data dictionary.

Additionally, there is provided a register which lists all users, and specifies, for each restricted field, whether they are or are not entitled to access that field. When formatting information received from a database, the system checks this register whenever a selectively available field is to be processed, and includes data from that field in the formatted output only where the user has access to it.

There follows an additional technical description of the elements required to complete each stage of the end-to-end transaction, specifying - where appropriate existing hardware and software.

STAGE 1 - USER ACCESS TO THE SYSTEM Access is gained to the system's host computer from any terminal using a standard formatted screen for password control. Access from the terminal to the system may be through UNIX protocol TCP/IP or OSI protocol.

STAGE 2 - GENERATING THE REOUEST FOR INFORMATION For the purpose of clarification, we can look at the main two types of transactions to be processed: 1. SQL - SQLs to be Run on a Number of Systems. SQL SQL is aimed primarily where a single query is used to general 4GL type queries on alien machines. Because the alien machines may be on any architecture using any 4GL, provided that the Data Dictionary on the system allows and manages the correct data elements to be interrogated, a series of 4GL's will be generated, to be processed concurrently.

On receipt of the output of these transactions, the system will reconvert the data and consolidate the data for further processing. The initiation of the SQL will not invoke existing transaction on the alien systems.

The User will generate a request for information from the terminal screen, using any one of a number of agreed standard enquiry languages, e.g. SQL*Plus on Oracle7, which will be transmitted to the system.

The system will identify the information bases to which the user has access and will translate the information request, into the query language and using the element names associated with the databases to be searched. This is achieved by using the data dictionary to hold the data element names known to the user and associating these with the particular query language used by the user.

For each system to be accessed, there will be corresponding aliases for these fields and attributes relating to the system on which the enquiry is to be run.

The system will then take this data and create a transaction based on the input query to be invoked and executed on the object systems. It therefore follows that this facility will be made available for each query language which subscribes to the system.

The system will build all the necessary transactions, including all necessary access procedures with network and access passwords. Determination of access routes and subsequent transmission will be handled by a proprietary multiprotocol router, e.g. one of the Cisco 7000 family.

2. SQL - Generated Transactions to be Run on a Number of Systems.

SQL - Generated Transactions is concerned with invoking transactions on alien machines that have been developed by the owners of those systems for their normal day to day business operations. These transactions may be written in any language on any architecture.

The SQL fires off the request, which is reformatted through the system so that the generated transaction will be acceptable to these existing alien transactions. The alien systems will treat this data as normal terminal input, process the data just like any other data, and output the data so that it will be routed back to USA.

The User will generate a request for information from the terminal screen. This will either be in the form of an SQL type query invoking a call directly to the system, which would recognise the call and call up the appropriate transactions for the appropriate systems.

For each transaction and each system, the system would need to hold on the Data Dictionary the screen template of the screen(s) to be used to access the subscribing system, both for the input and the output.

Alternatively, the User can access the system using a personalised menu, and input the parameters necessary to invoke the transactions to be generated.

In order to generate the transactions the following steps are initiated: (a) Find the pseudonyms for the data elements required for each of the transactions (b) Once the pseudonyms have been accessed they will be used to populate the screen input for the transactions. The transactions will be transmitted by the proprietary multiprotocol router, eg one of the Cisco 7000 family.

In order to manage password control the system will maintain information for every password needed to access object systems.

Part of the service to the users by the system will be the facility to manage the amendment, insertion and deletion of password mechanisms as dictated by the object systems. This will be of particular value when the object systems change their password access on a frequent basis.

STAGE 3 - ACCESSING THE REOUESTED INFORMATION Any access failures encountered in logging into the object networks or systems will be notified to the user through the system, or to the system directly if the failure is appropriate to the value added management of the service provided by the system.

On successful access to the object databases, the individual query programs generated by the system will be actioned by the object computer as if an authorised request had been received from a remote terminal.

STAGE 4 - RETURNING RETRIEVED DATA TO THE SYSTEM Information retrieved from object computers, including nil returns, is returned through the multiprotocol network, managed by the Cisco 7000 family, to the system for consolidation and further processing.

Dependent upon the type of query invoked by the user, there will be three broad categories of response: (a) A simple answer (e.g. what is the highest interest rate available in the world?). This would be directed to the user screen.

(b) A large volume of data. In this situation there will be a facility for the user to intercept this data either by his own processing or by the use of proprietary software to achieve consolidation or alternate display, e.g. GUI or EIS.

(c) An update to previous information invoked by software that runs continuously.

STAGE 5 - IMPLEMENTING A DECISION Data retrieved by the system and transmitted to the user will always be associated with system details identifying the source of the information. Using these details, the user can, through the system, now communicate with any database system or systems to which an authorised transaction may be made.

The data dictionary will hold information on all transactions authorised by the owners of the databases to which it has access. This information will include details of all mandatory fields required successfully to initiate a valid transaction on any of those databases.

If, for example, the original search has provided the user with information which leads to a decision to invest money with a specific trader or to purchase goods from a particular supplier, the user will be able to initiate an authorised transaction, registered with the system, to place an investment or a purchase order directly and immediately onto the appropriate database.

AN EXAMPLE OF THE END-TO-END PROCESS A simple example of this invention in practice would be the investment of money in global financial markets.

Let us look at a possible service to be provided to normal individual bank customers. The attributes of the service to be provided would be as follows: 1. Money designated by the account holder would be accessed by the service to be invested on a daily/weekly/monthly basis as agreed between the account holder and the service; 2. The service would have global access to investment services taking account of, for example, risk, amount, interest rates, currency conversion rates, duration of investment; 3. The service would run 24 hours a day, 7 days a week, constantly searching, and taking decisions based on global information; 4. At the end of the agreed time period, the money plus interest would be returned to the account holder or reinvested as agreed; 5.In order to achieve this service, the following infrastructure will be built: (a) An integrated global network; (b) Parallel processors to drive the transactions concurrently on a global basis across hundreds, even thousands of computer systems; (c) A data dictionary to ensure consistency and integrity of data; and (d) A mechanism to navigate through passwords, security and privacy.

This invention is of particular value where a very large number of databases are to be accessed. The more systems being accessed, the more valuable is the invention. If only two or three systems are to be accessed, then this invention may not only not improve the service, but could degrade overall performance.

As the number of users increase towards hundreds and thousands of concurrent users, the value of the invention becomes exponentially more valuable as there is no alternative invention to achieve this business requirement.

Claims (11)

1. A method of accessing computer databases comprising the steps of first transmitting instructions for the retrieval of information directly to any number of source databases, irrespective of their location, construction or standards and subsequently implementing any required on-line transactions directly on the chosen source computers.

2. A method according to claim 1 in which a parallel processor is employed to carry out the transmission of instructions to any number of object computer databases simultaneously.

3. A method of obtaining information from one or more computer databases comprising receiving a request for information from a user; ascertaining from a predefined list from which database or databases the requested information may be obtained; generating from predetermined rules for the or each selected database a command to obtain the information and submitting it for execution; receiving information from the or each database and converting it to a form suitable for the user; and outputting the converted data.

4. A system through which a user can gain access to one or more computer databases, the system comprising input means for receiving a request for data from a user, a data dictionary comprising a list of databases and details of data stored thereon, a router operative to submit commands to the databases known to the data dictionary, the system, in use, on receiving a command through the input means, selecting, by means of the data dictionary, one or more commands to be sent by the router for execution on one or more remote database systems, the information generated by those systems being then output to the user.

5. A system according to claim 4 comprising a host computer being a mainframe parallel processor such as nCUBE 2 and ICL's Goldrush.

6. A system according to claim 5 in which the host computer supports a standard data dictionary, such as ICL's DDS (Data Dictionary System), containing detailed information relating to all subscribers to the system, including databases, fields and transactions available to users, wherein the subscribers are individuals authorised to access the system as well as those databases that allow access to themselves through the system.

7. A system according to claim 6 in which the data dictionary holds information about data held on all other systems, networks and routes being accessed by the system.

8. A system according to any one of claims 5 to 7 in which the host computer will itself manage subsequent passwords for access to all networks, databases and transactions needed to effect the requests or commands generated by the user.

9. A method by which a user can obtain information from one or more databases comprising the steps of: a. the user entering a request for data at the terminal 10 and this being transmitted to the computer 12; b. the computer 12 consulting a data dictionary to ascertain which databases contain the information requested by the user, and consulting an internal register to establish which of these databases the user may access, for each suitable database, the system generating a command or commands for obtaining from the or each database the data requested by the user; c. feeding the generated commands to the router 14 which passes them over a wide area network to the database for which it is intended, in cases where the computer 12 is capable of parallel processing, several or all of the commands being handled simultaneously; d. each application interface 16A. . 16D receiving a command from the wide area network; e. the application interfaces 16A. .16D each converting its respective command to a form in which it may be fed to the respective database A..D so as to mimic a search command entered at a terminal connected directly to the database A..D; f. the database executing the received command and outputing its results to the respective application interface 16A..16D; g. each application interface 16A..16D transmitting the data over the wide area network, from which it is received by the router 14, and transmitted to the computer 12; h. the computer 12 monitoring the received data to verify its integrity until all of the expected data has been received; i. the data being processed for output into the format expected by the user; and j. the processed data being output to the terminal 10.

10. A method of accessing computer databases substantially as herein described with reference to the accompanying drawing.

11. A system through which á user can gain access to one or more computer databases substantially as herein described with reference to the accompanying drawing.