One important goal we have at VantagePoint Software is to integrate the software design of the Distributed Application model with existing database services that provide access to Relational and MultiValue (PICK) database systems.

Today there are number of avenues you can use to implement the FetchData, SaveData, and DeleteData methods of your Data-Centric Business objects. There are vendor specific and vendor neutral technologies available to connecting to a MultiValue PICK database. Most of these products and solutions revolve around the use of the Open Database Connectivity (ODBC) standard application programming interface (API) or the newer OLEDB provider technology that makes PICK data available through ActiveX Data Objects (ADO).

ODBC has its own problems with the number of layers it must go through and mechanisms of making and maintaining connections. OLEDB and ADO are much newer technologies and provide a much more robust object model to deal with. But the current implementations of OLEDB that support ADO objects are somewhat spotty by the different vendors. The very first sets provide for very fast connections and data throughput but are limited to generating read-only data at this time.

At VantagePoint Software we developed our own database object (VPDb20.dll) similar to the DAO and ADO object models from Microsoft but it has been modified to deal with MultiValue data in addition to relational data. We have also developed a vendor neutral protocol mechanism for accessing a MultiValued Pick-based system known as the Vantage mvServer protocol. This protocol is based on establishing a telnet session on top of a TCPIP connection and functions through terminal input/output. This protocol also supports Serial connections as well as older Netware network connections based on SPX/IPX. We have found this protocol is very robust and is as fast or faster than any of the current OLEDB implementations and orders of magnitude faster than the ODBC standard.

Irrespective of the communication and transport mechanism there are different data accessing methods available. These include:

	Structured Query Language
	Stored Procedures

With the dbObjects Database component (VPDb20.dll), or with OLEDB providers along with ADO you can choose to access and manipulate your data either through the Structured Query Language (SQL) or through calls to Stored Procedures. SQL is the only language available for requesting and updating data from traditional Relational Database systems. And for Relational Database Systems, stored procedures are nothing more than precompiled SQL Statements with some limited additional procedural language enhancements. SQL can also be used against a MultiValued Pick-based database system, but any data it refers to must be made to look like normalized data. In other words, any embedded multivalues or multi-subvalues would have to be broken out into real or virtual secondary tables so that the SQL grammar could be used to access the data. Client-side software that is part of the mvServer product from VantagePoint Software provides mechanisms to map out a MultiValue database to look like a normalized relational database for use with SQL.

The one advantage to this approach is that the same language you use to access and manipulate other Relational Database Systems can be used with MultiValue Systems. This makes it easier to build database service components that can work with multiple data sources. Using this approach, all data processing logic is pulled from the database server itself and is incorporated in the SQL grammar found in the Data-Centric business objects. The database system just becomes another data source.

The other approach is to use precompiled procedures that are located on the database server. These precompiled procedures are referred to as "Stored Procedures". In the case of a MultiValued Pick-based system, these would be Pick/Basic subroutines that can be called via the connection and transport software of Vantage mvServer protocol and server software. These Pick/Basic subroutines can take any number of parameters and provide logic to retrieve or update data to the data files of the system. One advantage of this approach is that you can take advantage of existing application logic by reengineering them into these “stored procedures”. Another clear advantage to Stored Procedures is that both for Relational Database Systems and MultiValue Pick-based Database Systems, these stored procedures will operate significantly faster than manipulation through dynamic SQL.

No matter which data access method you use there are a number of issues that you must address when designing your software for a Distributed client/server approach. These include:

	Concurrency issues
	Connection management
	Database logic locations

Concurrency issues

Concurrency has to do with allowing multiple users access to the same set of records in a real-time situation. Traditionally this has been managed through the use of Database row or record locks. In today’s world of highly scalable application design with the eye to stateless operations, how we deal with concurrency must change. No longer can you assume that a connection to database is opened and maintained during the entire operation of the application. Especially with Web Services or Web-based applications a connection pool is maintained where a connection is allocated for a single process and then reassigned to the next user’s request. In these situations a traditional database lock cannot be used.

What has to be built into either the object that calls an SQL operation or within the Stored Procedures is the ability to identify when an update request has been made and the underlying data has been changed since the data was originally fetched. In these circumstances the database application logic will need to analyze what changes there are, whether they are relevant, and either go ahead with the update or raise an error back to the Data-Centric and UI-Centric objects that the update can not take place.

How you deal with it at this point is part of the application design and may change depending on the circumstances. The user at the UI may be informed of the concurrency problem and be given the option to override with an update anyway or rollback the transaction and start over. There are a number of options in handling these types of situations that don’t require traditional record locking or even a persistent connection to the database system. The base classes that compose your Data-Centric business objects, the main database operations component, and the several support stored procedures subroutine that are part of the mvServer software all have code to deal with concurrency issues, both on a persistent-connection basis with traditional record locking, as well as with non-persistent-connections in a stateless environment.

Connection management

We already have touched on the issue of persistent connections. Using the Distributed Application model you must use a non-persistent approach to connection management. This is the most scalable approach and makes the best use of resources. Using a MultiValue Pick-based system within a client/server approach can significantly reduce the overhead associated with any Pick port when compared with a terminal environment. In a terminal environment the Pick system is handling all input/output, screen display, program logic, and data retrieval and update. In a client/server environment, the Pick port is left to only handle data retrieval and update and optionally a portion of the program logic.

One important approach to connection management is the multiplexing of client connections into a single port to take advantage of all the idle time within the port. At VantagePoint Software we have developed a multithreaded Gateway service that multiplexes client connections to our Vantage mvServer protocol services. This allows multiple client requests to share a given Pick communication port. Even with a persistent connection approach we have found that we can support 10, 20, 30 or more users per port. The savings in port licensing alone has covered the cost of all our tools and components for many customers. This same Gateway multiplexing service provides for multiple concurrent Web application data requests to be handle by a given Pick communication port. This is very important when dealing with the scaling issues associated with Web-based applications.

Database logic location

The last design concern we need to discuss is a determination of where the database logic should reside. There are no hard fast rules for deciding this issue. It some cases it depends on where the customer is coming from and their current investment in the business logic already residing in the MultiValue Pick-based system. Ideally, we might want to move as much logic to a set of business objects at the Data-Centric application server or servers, but that might not be practical if what has made a business so successful to this point is tied up in the Pick/Basic programs that are on the database server itself. It might be OK to move some of this logic into the Data-Centric set of objects, but a large portion could be constructed as Stored Procedures and stay on the Database Server. And many ways, with the large processing power associated with many Pick-based computer systems, this is the appropriate place for this logic. Plus, the calling and execution of Stored Procedures on the database server is one of the most efficient methods of accessing data - far more efficient than SQL.

These are all issues that need to be thought through when designing a Distributed Application. At VantagePoint Software we have spent years dealing with these issues and can help any company in choosing a proper design that fits their particular needs. All of our components and tools have been designed with these issues in mind.

support@vpsoft.com