Many organizations are expanding to use multiple platforms in their IT environments. There are many reasons for this: it can be due to company acquisitions or application vendor platform shifts, or when a department requires a certain software package with features that require a different platform. For example, many Customer Relationship Management (CRM) tools employed by sales teams reside on a Microsoft SQL Server, whereas the main system is an IBM i.
Since Sequel Data Access is IBM i-centric, data that does not reside on this system is considered remote. The data contained in a remote database is no less important to an organization than the DB2 data on the IBM i.
Learn More About Using Sequel to Access Remote Databases >
Thankfully, Sequel can easily access data that resides on:
- Microsoft SQL Server
- Oracle
- MySQL
- Other DB2 databases
Most modern databases provide Type-4 JDBC drivers for remote access, which is what gives Sequel this access. Let’s take a look at how to easily define the remote connection, design a View over the remote database, and how to create a data request with data from multiple databases.
Remote Connections
How can this data be accessed using Sequel?
You will need some information in order to make the connection from Sequel to the remote database. You may need your network administrator or remote database administrator or both to provide this information.
- Type of database (MS SQL Server, MySQL, Oracle, other DB2)
- User ID and password to access the remote database
- Address of the target server: port number, database name, other information
- Remote database version—different versions may require different drivers
With this information you can establish a connection between Sequel on the IBM i and the remote database. For users with proper authority, Viewpoint Administrator provides an easy way to monitor your Sequel environment, change user defaults, set and monitor Sequel usage through auditing, setup Viewpoint auto joins and more, including making remote database connections.
In the Viewpoint Administrator, use the Sequel Host Servers option to establish your link from Sequel on your IBM i to the remote database. This link can also be made with the Sequel command MNTHOSTF from the 5250 command line.
Sequel provides platform entries with default driver information for the supported remote databases mentioned above. You will see those listed with an asterisk in the host list on the left hand pane.
NOTE: Your version of SQL Server may require different driver and/or connection information. Please refer to the documentation in the Sequel Viewpoint Users Guide for specific information.
To get started with remote database access:
- Enter a name in the Server box on the right side of the window. You can use a name that is familiar to your organization when referring to the remote database.
- Select the platform using the drop down arrow in the Server Type box.
- Enter a User ID and password for the database being accessed.
- The Driver field is used to specify the path to the Type-4 JDBC driver on the IFS portion of the IBM i. This is defined by the manufacturer. If the driver you need matches the default driver for the platform specified in the Server Type box, you may leave this entry blank.
- The Connection box provides information about the database when the connection is made: the address of the target server may specify port number, database name, or other connection specific information.
Once entered, the connection can be checked with the Test Connection button. A successful connection means you are ready to begin creating Sequel views over the remote data.
For any failures, view the job log.
Design a Sequel View with a Remote Connection
For designers in Sequel who will be making many Views over the remote data, changing your Sequel defaults to point to the remote connection can make a simple step even easier. If you just need to make a small number of Views over the remote data, you can change the properties of a view to point to the remote database.
Start a new View like you normally would. Go to the toolbar and select File then Properties.
In the Properties window, change the Database field to point to the remote database connection you made earlier: it will be in the drop-down box. You have a choice as to the Syntax setting. Syntax of *SEQUEL will allow the creation of Views using SEQUEL syntax and functionality. [Note: There are some exceptions to this. See the Sequel SQL Reference Guide for more specifics.] Syntax of *SERVER indicates you wish to use the syntax and functions of the database to which you are connected. Click OK to accept the properties change.
From this point forward when designing this View, everything will remain the same as a View over IBM i data files. You will have the ability to load remote database tables and click-and-drag fields into the View. Derived (calculated) fields and variables (prompts) are available just as if this View was built over IBM i DB2 data.
How can data be merged with other data without manually moving or downloading the data each time management needs a complete picture of an organization?
Sequel Script View
A Sequel Script View is the easiest, and often the best, method to merge data from IBM i and other platforms (SQL Server, Oracle, MySQL). There is some initial setup, but once it’s completed, management will have the combined data for analysis right at their fingertips.
In this example, we will merge data from IBM i and Microsoft SQL Server. There are a couple ways to accomplish this. This is just one example. Keep in mind, as you are designing your Views that the results will have to be similar in design so they can be merged or joined together.
-
From Viewpoint, design and save a View over the required data files from the IBM i
-
From Viewpoint Explorer, right-click on the view and create a new physical file on the IBM i for use later (FILE_A)
-
-
From Viewpoint, design and save a View over the required tables on the SQL Server
-
From Viewpoint Explorer, right-click on the view and create a new physical file on the IBM i for use later (FILE_B)
-
-
From Viewpoint, design and save the combined View that joins FILE_A and FILE_B
-
Now design the Script View that will re-populate the files each time, ensuring fresh data for analysis.
From ViewPoint Explorer, go to the New tab and select Sequel Script View. [Note: if your version of Viewpoint does not have this option you can use a Sequel Script. However, you should look into upgrading your Sequel and Viewpoint versions to current releases.]
A Script View requires a specific command—SCRETURN. This is used in place of a DISPLAY command in a Script. It also adds more functionality for the user.
- Add a Script View to a dashboard and it acts like a view by displaying the results—even after processing multiple steps or joining across databases like a script.
- Add powerful presentation capabilities to results by designing host or client reports over a Script View.
- View script output in Sequel Web Interface just as you would a view.
Sequel provides an easy-to-use, graphical environment to define and establish a remote connection, while Viewpoint designer makes it simple to create a view definition over this data and include prompts and calculated fields. With Sequel Scripts and Script Views you can easily merge data from multiple databases in a single run-time object, giving the business analyst more information in a faster easier method than they had before.
Do More with Sequel
Sequel Data Access makes it easy to access IBM i and remote databases—like MySQL, SQL Server, and beyond. Whether you need help accessing remote databases or you simply want to build on your Sequel skills, find the training option that's right for you.
