ViewPoint supports the ability to connect to remote databases and process SQL requests against them. The feature is enabled through the Sequel software that runs on the “host” System i to which View- Point is connected. All remote database accesses are performed between this System i and the remote data server; there is no connection between the personal computer running ViewPoint and the remote system.
The available remote databases are defined in a database file named SequelHOST that is stored on the System i. Each user’s Sequel settings indicate the SequelHOST file that will be used for their remote database access. This means that user-based definitions are possible with different users having a different SequelHOST file (or member) specified for their user settings. The file or member to be used can be viewed or changed using the SETDFT command or by using the ViewPoint administrator’s “Set Defaults” option.
Remote database support is provided by Sequel host version R10M04 and ViewPoint version 9.8.172. The System i must be operating at V6R1 or later and any target System i systems must be running V5R1 or later.
Remote database access, including *LOCALSYS connections, and some of the ‘Save As’ functions from the Sequel Web Interface require the Java 5.0 (or higher) SDK licensed program (5761JV1 for V6.1 and V7.1 or 5770JV1 for V7.2, with at least one of the following options: 7, 8, 9, 10, 11 or 12). In addition, on V6R1 the IBM Toolbox for Java licensed program (5761JC1) must also be installed.
For SQLServer connections, Sequel (prior to version R11M03) installs a Type-4 JDBC driver (sqljdbc.jar—version 1.2 dated 10/11/2007 with a size of 570KB) into the rio/lib folder of the IFS. Due to recent changes on both the Systen i and SQL Server, it is recommended this driver should be moved out of the rio/lib folder (not renamed) and replaced with the newer 1.2 version sqljdbc.jar—dated 11/12/2009 with a size of 300KB. This new version can be downloaded from the following link:
Starting with Sequel version R11M03, this newer driver will be installed with the product.
Sequel supports remote databases that are accessible via either the standard SQL Call Level Interface (CLI) or a 100% pure java (Type-4) JDBC driver.
SQL-CLI accesses are made to DRDA databases that are described in the System i remote database directory. The System i commands ADDRDBDIRE and WRKRDBDIRE are used to configure the connections to these remote systems. See the appropriate IBM reference manuals for information about using these commands.
Nearly all modern databases provide Type-4 JDBC drivers for remote SQL access. The Sequel instal- lation process automatically installs drivers for Oracle®, Microsoft SQL Server®, and the MySQL® open source databases. You will be able to use these drivers to access these remote databases without installing additional objects.
If additional Type-4 JDBC drivers are properly installed to the System i Integrated File System, SE- QUEL will be able to access other remote databases as well. These drivers must be restored to the IFS path: \QIBM\UserData\OS400\SQLLib\Function\ in order to be accessible by Sequel. The database vendor will usually supply the driver “.jar” file that can be expanded into the System i IFS.
Defining the connection to a remote database is fairly simple with either the SEQUEL MNTHOSTF (Maintain Sequel Host File) command or the ViewPoint Administrator’s Sequel Host File option.
Entries in the host file provide the information necessary to connect to a remote database. There are two types of entries: platform entries and database server entries. A platform entry begins with an asterisk (i.e. *ISERIES, *ORACLE, etc.) and identifies characteristics that will be common to all the connections for a specific platform. A database server entry references the platform entry; the common proper- ties do not need to be specified for each database on the given platform. The database server entry can override these properties. If they are not overridden by the database server entry, the values specified on the platform entry will be used when the connection occurs. Entry properties are:
User name and password: Connections made to the remote database will be made using the user/password stored with the entry. The password is encrypted in the SEQUELHOST file. If the user accessing the entry does not have Sequel administrator authority**, they will be required to supply the correct password prior to viewing the entry details.
**Sequel Administrator authority is defined as having *CHANGE authority to the SQ#DFT data area object in the SEQUEL product library.
Server type: identifies the platform entry (beginning with an asterisk) that further defines the database server entry.
Driver: specifies the driver class and class path to a Type-4 JDBC driver. The path, defined by the driver manufacturer, is specified in dotted form such as, .com.microsoft.sqlserver.jdbcSQLServerDriver. If no value is specified, then the entry specifies a DRDA database and there must be a corresponding entry matching this server name in the System i remote database directory (WRKRDBDIRE).
Connection string: provides additional information to the driver when the connection is made. Usually this will include the address of the target server and may specify port number, database name, or other connection specific information.
Sequel includes a remote server connection command, SQLCONNECT that can be run from the com- mand line to test a connection definition. Once the database entry has been defined, it can be checked using SQLCONNECT databasename. If the connection fails, messages regarding the failure will be returned to the joblog and command entry display.
System i Database
Create a connection to a System i database by using WRKRDBDIRE or ADDRDBDIRE and defining the connection.
Once the connection has been properly created, it can be tested using SQL/400 and the CONNECT TO DATABASE statement. The database name you must use is determined by the name of the *LOCAL RDBDIRE entry name on the target system.
After the database directory entry has been created, the database name can be defined in the SEQUELHOST file. The server name for the Sequel host entry must match the relational database name used on the ADDRDBDIRE command. Specify *ISERIES for the Server Type and leave the Driver and Connection String entries blank. If the connection is to a remote machine, you must enter a user profile and password combination. Connections to the local machine will always use the user profile of the current job; you cannot enter user/password information for a local connection.
Sequel connections to System i databases will use SQL naming and qualification rules. If qualified, table references in the FROM clause must be in the form ‘library.filename’. Unqualified references will be resolved against a library matching the user’s profile. This can be overridden by specifying LIBRARIES=libraryname in the connection string of the host entry. If the LIBRARIES=libraryname value is specified in the host entry, the single named library will be used to resolve all unqualified table references.
Other DRDA Databases
Define the database as described above using the WRKRDBDIRE/ADDRDBDIRE commands. The name of the database entry that you create must match the name of the DB2 database to which you are connecting. When you create the SEQUELHOST entry, specify an appropriate user/password for the database, use *DB2 as the Server Type and leave the “Driver” and “Connection String” entries blank.
Microsoft SQL Server® Databases
The connection to SQL Server databases occurs through the Type 4 JDBC driver provided by Microsoft Corp. as the “Microsoft SQL Server 2005 (2008, 2012) Driver for JDBC”. The driver is installed auto- matically to the System i IFS by the Sequel installation process. The SEQUELHOST file has a *SQLSERVER platform entry that identifies the driver class to be used for these connections (com. microsoft.sqlserver.jdbc.SQLServerDriver).
When defining a remote database entry in the SEQUELHOST file, you should enter the user and pass- word information, specify *SQLSERVER for the server type, and a properly formatted JDBC connec- tion string. The syntax of the connection string is:
where hostname / instancename is the network resolvable name or address of the SQLServer host and the SQL Server instance to be used. Omit the instancename portion if you are connecting to the default (usually MSSQLServer) instance. The portnumber is the TCP/IP port for the server. The listener port number is usually 1433. To determine or change the listener port number, access the SQL Server, locate the general tab of the properties panel for the server instance, and press network configuration button. The port number is available after selecting the TCP/IP protocol on the Network Utility panel and press- ing the Properties button.
For example, a properly formatted connection string might be as simple as:
If successful, the connection will be made to the user’s default database.
Note: The documentation for the JDBC driver indicates that a driver property (DatabaseName=database) can be specified, but our experience is contradictory; specifying the Databasename=database property on the connection string seems to have no effect.
The connection to ORACLE databases occurs through the Type 4 JDBC driver provided by Oracle Corp. as the “Oracle JDBC Thin Driver”. You can use it to access Oracle databases version 7.2 or higher. The driver is installed automatically to the System i IFS by the Sequel installation process. The SEQUELHOST file has a *ORACLE platform entry that identifies the driver class to be used for these connections (oracle.jdbc.OracleDriver).
When defining a remote database entry in the SEQUELHOST file, you should enter the user and pass- word information, specify *ORACLE for the server type, and a properly formatted JDBC connection string. The syntax of the connection string is:
where hostname is the network resolvable name or address of the Oracle host, portnumber is the TCP/ IP port for the server, and sidname is the name of the service (often ORCL). The listener port number is usually 1521 and is defined in the network\admin\listener.ora file in the oracle product library.
Here is a properly formatted connection string for Oracle:
The connection to MySQL databases occurs through the Type 4 JDBC driver provided under the GPL. The driver is installed automatically to the System i IFS by the Sequel installation process. The SEQUELHOST file has a *MYSQL platform entry that identifies the driver class to be used for these connections (com.mysql.jdbc.Driver).
When defining a remote database entry in the SEQUELHOST file, you should enter the user and pass- word information, specify *MYSQL for the server type, and a properly formatted JDBC connection string. The syntax of the connection string is:
where hostname is the network resolvable name or address of the MySQL host, portnumber is the TCP/ IP port for the server, and databasename is the name of the database to be accessed. The listener port number is usually 3306.
Here is a properly formatted connection string for MySQL:
Using a remote database involves the successful completion of a series of steps. The performance you observe while using Sequel to access remote data will depend on several factors. Because some per- formance enhancement can be realized through regular “refresh” of the connection, it may be advanta- geous to schedule a repeating job to perform Sequel’s SQLCONNECT command at regular intervals.
Java Virtual Machine
Establishing a connection to a remote database that is accessed by a Java driver will always take longer than to a DRDA database that is accessed using the SQL Call Level Interface (CLI). Before a JDBC connection can be made, the System i host job must create the Java Virtual Machine (JVM) environment that will be used by the JDBC connection. A JVM is not required for a CLI connection, so this step is avoided for DRDA databases.
The time it takes to create a JVM is dependent on several factors, among which is the length of time system wide since the last JVM was created. The System i creates JVM objects that are available for use by any job that needs them. If these objects already exist prior to the JDBC connection request, then the JVM will be established in a significantly shorter time.
The first use of a JDBC connection for a job still requires the establishment of the JVM environment. This process can frequently take several seconds to complete, but does not need to be repeated for subse- quent JDBC connections, even if they are made to different remote databases.
Remote Database Connection
Once the overhead associated with establishing the connection environment has been paid, the system will attempt the actual connection to the remote database. To complete this step requires resolution of the name/address, negotiating a route to the target machine, and establishing an authenticated connection with the database server. The time it takes to complete these steps depends on the speed of the connec- tion to the server as well as the speed of the System i and target systems, but also on the length of time system wide since the last successful connection to the server.
There does not seem to be a significant difference between the time required to complete this step for JDBC driven and CLI driven databases.
Request processing and data transfer
All remote database requests are run by the remote system. The System i makes the request to the re- mote host and waits for a response. Once the connection is established, the time required to return results from the remote database will depend on how quickly the remote server can process the request, the amount of data returned, and the speed of the connection between the System i and the remote server.
ViewPoint, and the interactive “green screen” environments for “interactive” processing will acquire re- sults from the remote database in a “block at a time” fashion. Additional blocks of results are requested/ retrieved as the user scrolls through the display.
EXECUTE ALWNULL(*NO) works differently when using a non-Sequel server. Using the Sequel process, records with null values are skipped and not inserted into the outfile. Using a non- Sequel server, records are inserted with zero/blank values in place of the null values. Specifying ALWNULL(*YES) will result in null values inserted into the outfile where null values are returned by the database.
Although Sequel supports both single and double quoted literals, most Sequel users are used to placing double quotation marks around literals. Using double quotes offers a convenient way of avoiding the problems associated with the OS/400 command analyzer handling parameters that are quoted strings. From the OS/400 command line, single quotes within a quoted string must be represented as a pair of single quotes.
Although Sequel was engineered with this in mind and allows users to enclose literals in double quotes, standard SQL does not. Double quotes are used ONLY to surround a case sensitive name (table, column). Values that are surrounded by double quotes will be interpreted by all SQL processors as column names. Consequently, literals must be surrounded by single quotes.
Sequel supports library/file and library.file qualification methods. Standard SQL supports only sche- ma.table qualification. Although the System i SQL supports a “system” naming convention that allows SQL statements to be entered with the System i “library/file” notation, ViewPoint does not accept this notation for non-Sequel database access when using Syntax (*SERVER).
The IBM Distributed Database Programming guide advises that the JDBC connector (and database operations in general) should not be run under a job that uses a CCSID of 65535. This specifies that data conversions are not to be performed, which can cause critical problems in the System i Connect envi- ronment. If you cannot change the system-wide CCSID value to a value other than 65535, you should change the CCSID value of the job under which database operations are being performed.
Remote databases often support data types that are different from those supported by the System i. Sequel will map these data types to native data types automatically. A table of mappings is shown below. Upper-case types match ANSI SQL-99 types and are usually portable between databases. Maximum lengths for each type are shown in parentheses. See the Other Notes section below for additional information.
Column names in the remote database that are greater than 10 characters long will be created on the System i as columns named “RENAMEDxxx” with an alias name matching the original name of the column.
|DB2||SQLServer||Oracle||MySQL||System i DDS||System i SQL||ANSI|
|CHAR (254)||CHAR (8000)||CHAR (2000)||CHAR (255)||Character (32766)||CHAR (32766)||CHAR|
|VARCHAR (32672)||VARCHAR (8000)||VarChar2 (4000)||VARCHAR (255)||Character Vary- ing (32740)||VARCHAR (32740)||VARCHAR|
|LONG VARCHAR (32700)||Long (2GB-1)||Text (65535)
|LONG VARCHAR||LONG VARCHAR|
|CLOB (2GB)||Text (2GB)||CLOB (4GB-1)||Long Text
|CHAR For Bit Data (254)||BINARY (8000)||Raw (2000)||BLOB (65535)||Hex (32766)||BINARY (32766)||BINARY|
For Bit Data
|VARBINARY (8000)||Medium BLOB (16777215)||Hex Varying (32740)||VARBINARY (32740)||VARBINARY|
CHAR For Bit Data (32700)
|LONG VARBINARY (32740)||LONG VARBINARY|
|BLOB (2GB)||Image (2GB)||Long Raw
|Long BLOB (4294967295)||BLOB (2GB)||BLOB|
|Bit||4 byte binary||BOOLEAN|
|TINYINT||TINYINT||2 byte binary||TINYINT||TINYINT|
|SMALLINT||SMALLINT||SMALLINT||2 byte binary||SMALLINT||SMALLINT|
|Mediumint||4 byte binary|
|INT||INT||INT||INT||4 byte binary||INT||INT|
|BIGINT||BIGINT||BIGINT||8 byte binary||BIGINT||BIGINT|
|DECIMAL (31)||DECIMAL (38)||Number (38)||Decimal||Packed||DECIMAL (63)||DECIMAL|
DB2 columns that are identified as “For Bit Data” are retrieved without translation to the job CCSID. Long VarChar data type is not supported, but can be converted to character using the CHAR function. Large object (CLOB/BLOB) fields cannot be retrieved from a remote database table but can be convert- ed using the HEX or CHAR functions.
SQL Server “n-types” (nChar, nText, nVarchar) containing Unicode data are converted to single byte character according to the CCSID of the System i job making the request. “Text” data type
Still have questions? We can help. Submit a case to Technical Support.