5.38 SQLService
The SQLService is designed to enable users to develop applications than can communicate with any database that can be accessed with a JDBC driver. While the SQLService could be run in a number of different scenarios, the most common usage of it would be to enable IBM i applications (written in LANSA, RPG, Cobol or any other IBM i language) to access data on other platforms via a JDBC driver.
Some examples of its use might be:
- A IBM i (LANSA, RPG, Cobol) application accessing data in an SQL Server database on Windows server.
- A IBM i (LANSA, RPG, Cobol) application accessing data in an Oracle database on a Linux server.
- A IBM i (LANSA, RPG, Cobol) application accessing data in a DB2 database residing on another IBM i machine.
The above describes IBM i applications accessing data on other types of databases on other machines, but this is not a restriction. You may have a requirement that a Windows based application needs to talk to a JDBC driver to access IBM i data for example.
Using this service with a JDBC driver will provide you with a simpler way of reading and updating records in other databases. It also enables real-time updates and access of data on remote servers. Other techniques to achieve the same objective might include the use of dataqueues or staging files which store data until a polling program on another platform comes along to pick up the data and process it into the other database. Such processes are fraught with risk as they consume CPU and network resources, even when information is not being processed. Refer to 5.38.1 What can I use the SQLService for? for further information of how you might use the service.
Related Services
There are no related services.
Technical Specifications
When using the SQLService, a key requirement is to obtain a JDBC driver for the database that you want to connect to. You will have to obtain this yourself from the database vendor. Most vendors will provide the requisite drivers on their installation CDs, as well as allow you to download them from their web site. In many case these drivers will be available at no charge. Most databases will need you to use a driver that is of exactly the same version as the database. Therefore, ensure that you have access to the correct driver for the version and type of database that you wish to connect to. Vendor web sites are the best source for this information. Some useful sites are as follows:
Oracle JDBC Drivers: www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Microsoft SQL Server Drivers: www.microsoft.com/technet/downloads/sqlsrvr.mspx
Adaptive Server Drivers: www.sybase.com/products/informationmanagement/softwaredeveloperkit/jconnect
NOTE: For many databases, drivers can also be obtained from third party sources.
The IBM Toolbox for Java comes with two JDBC drivers for the IBM i, which can be used for accessing DB2 data:
- IBM Toolbox for Java JDBC Driver - this is a Type 4 driver, that makes direct socket connect to the database host server
- IBM Developer Kit for Java JDBC Driver - this is a Type 2 driver, and makes native method calls to the SQL CLI (Client Level Interface).
You would use one of these if you have a requirement to access DB2 data on an IBM i via a JDBC driver. In general, you would use the native driver if your program is intended to only run on an IBM i JVM and the data is on the same machine. You would tend to use the Toolbox driver if your program is intended to run on other JVMs or the Java program is on one IBM i OS system and the data in on a separate IBM i OS system. For more information on the IBM Toolbox for Java please refer to IBM Toolbox for Java.
This service assumes a basic knowledge of SQL. The SET command provides the ability to control numerous commitment control features for your SQL statements. While these are addressed in some detail in this document, a complete explanation of the effect of each type is beyond the scope of this document.
Finally, ensure that your remote server is available on your network and that you have all the connections set up so that you can access the database.