With SEQUEL you can retrieve information not just from your local IBM i, but also on other IBM i’s, SQL servers, Oracle, MySQL, and other remote databases. Once you have the remote host definition defined you are ready to convert data on other platforms into meaningful information right from your local IBM i. The ability to design views with SEQUEL SQL and translate them on the fly to native SQL on the IBM i, or to server-specific SQL on a remote database, opens up a world of data access opportunities.
How to define a remote host
The available remote databases are defined in a database file named SEQUELHOST that is stored on the Power Systems server. The definitions can be created from the ViewPoint Administrator or using the MNTHOSTF command on the IBM i. Each user’s SEQUEL settings indicate the SEQUELHOST file that will be used for their remote database access. This means that user access to remote databases can be controlled by specifying different SEQUELHOST files (or members) for their user settings. The file or member to be used can be maintained through the user’s SEQUEL defaults which can easily be set through the Administrator.
Example of an SQL Server connection.
Example of an Oracle Database connection.
See our ViewPoint User Guide for complete system requirements and setup. See our Help/Facts entry on troubleshoot remote databases for additional assistance, or contact our support team.
How to use a remote host definition
When building your SEQUEL View, first define the DATABASE parameter within the View Properties to your remote database.
A view defined to use the ABLCORP2005 SQL Server database.
Tip: When building the SEQUEL View, select the remote database as needed and select *SEQUEL as the Syntax, allowing you to build the view in the SEQUEL syntax you are familiar with. At runtime, the SEQUEL syntax will be converted to native SQL or server-specific SQL on a remote database. Once you have your view defined using your remote database, you can build the view just like any other view that is built over the local IBM i data. Just select or drag and drop the tables (files), columns (fields), and so on to build the SEQUEL View.
Why build remote views and dashboards?
Access current data not just on the IBM i. Gain cross-platform consolidation of data that can be displayed nicely in a dashboard.
SQL Server, Oracle, and DB2 data displayed in a single dashboard.
Building a similar remote database dashboard
The consolidated data shown uses four SEQUEL Views and a SEQUEL Script. The Script runs automatically when the dashboard is opened or can be scheduled to run on a regular basis to collect the information from the databases and consolidate it on one server.
The three views listed above are straightforward and retrieve GL accounts and amounts from the respective databases. The GL_ABLE view is simply:
SELECT COMPANY, DEPT, GLACCT, SUBACCT, ACCTDESC, FYR, PERIOD, AMOUNT
FROM ABLE.GLFILE glfile
The view used in the dashboard displays the consolidated data from the work file created in the script.
SELECT DESC.2 COLHDG(" "), seq.2 COLHDG("seq#"), SUM(CASE WHEN company
CONTAINS "Able" THEN amount*sn ELSE 0 END) NAME(able) LEN(9,0) COLHDG("Able, Inc.")
EDTCDE(O), SUM(CASE WHEN company CONTAINS "Baker" THEN amount*sn ELSE 0 END)
NAME(baker) LEN(9,0) COLHDG("Baker Co.") EDTCDE(O), SUM(CASE WHEN company CONTAINS
"Cook" THEN amount*sn ELSE 0 END) NAME(cook) LEN(9,0) COLHDG("Cook, Inc.") EDTCDE(O),
SUM(amount*sn) NAME(cons) LEN(9,0) COLHDG("Consolidated") EDTCDE(O)
FROM rmthq/congl,rmthq/is_rules
WHERE glacct BETWEEN begacct AND endacct AND fyr=2004
GROUP BY DESC,seq
ORDER BY seq ASC
SEQUEL Script:
/* SQL Server */
EXECUTE VIEW(RMTHQ/GL_ABLE) OUTFILE(RMTHQ/CONGL) MBROPT(*REPLACE)
/* DB2 */
EXECUTE VIEW(RMTHQ/GL_BAKER) OUTFILE(RMTHQ/CONGL) MBROPT(*ADD)
/* Oracle */
EXECUTE VIEW(RMTHQ/GL_COOK) OUTFILE(RMTHQ/CONGL) MBROPT(*ADD)
/* Consolidated */
DISPLAY VIEW(RMTHQ/GL_CONSOL)
The process is seamless to the user, who simply runs the dashboard. The work of consolidating the data is buried in the script that runs automatically behind the scenes. For assistance with building your own consolidated view, contact our excellent SEQUEL Software Support Team. With SEQUEL’s built-in function to connect to many different platforms, what’s stopping you from accessing the data that you need?
