5.38.1 What can I use the SQLService for?
The most common use of this service will be for an IBM i application to access data on another server - whether it is a DB2 database on another IBM i, an Oracle database on a Linux box, an SQL Server on a Windows machine, or any other permutation where data can be accessed via a JDBC driver. The data access required may be create, read, update, and or delete.
There are a number of ways to achieve the same objective in terms of accessing remote databases, but JDBC can often be the simplest, hence the SQLService is an option you should consider when assessing how to tackle such issues.
The following are some examples that demonstrate where this service might be used.
Example 1
There are many examples of web applications needing to access information from multiple servers. You may have a LANSA for the Web application running on an IBM i server. The application accesses most of its information from the IBM i, but you may have some screens that need to present data that is gathered from the IBM i plus some data that is residing on an Oracle database on a Windows server. For the data residing on the IBM i, you would FETCH, SELECT, INSERT, UPDATE or DELETE this information in the usual manner you would as for any LANSA IBM i application. For the data residing in the Oracle database though, you should seriously consider using the SQLService to access this database via a JDBC driver.
Now there are other ways that this goal can be achieved using LANSA. You may decide to place your data into dataqueue or a staging file on the IBM i, and have a Visual LANSA application polling this dataqueue or staging file then transferring the data to and from the DB2 and Oracle databases as required. For simple solutions, this can be quite effective. But it does have some drawbacks:
- The approach uses the concept of pulling the data from the IBM i. In this case, the web application itself is reliant on another system to do the updating.
- If the polling of the Visual LANSA application is not regular, then the Web application will possibly suffer from asynchronous and less than real-time data. This problem may be overcome by increasing the polling rate, but of course this would consume more CPU and network resources. Even if no data needs to be update, the polling will still occur to interrogate the data queue or staging file - because this is the only way it can find out if data needs to be moved in one direction or the other.
By using the SQLService with a JDBC driver, you can ensure that data is accessed and updated to and from the web user as and when required. This is because the database access is handled by your LANSA for the Web WebEvent function or WAM itself, and not relying on a polling application to transfer data. So, with this approach, you use CPU and network resources only when needed.
Example 2
You may ask, "Why would an IBM i application need to use a JDBC driver to access IBM i DB2 data?" (as provided for by the two drivers that come with the IBM Toolbox for Java).
Such an approach might be very useful when you want to access a DB2 database that is residing on another remote IBM i. An organisation might, for example, have a number of IBM i machines in different locations around the world. From time to time a LANSA, RPG, or Cobol application running in one country may need to access data from the DB2 database residing on the IBM i sitting in another country. This is a very good example of where the SQLService could be used. The SQLService could be used to access the remote DB2 database real-time via the IBM Toolbox for Java JDBC Driver. This driver comes with all IBM i installations, so it makes for a simple cost effective mechanism for IBM i applications to access remote DB2 databases.