When you run SEQUEL ViewPoint on your PC, you can connect to remote databases through SEQUEL on your System i and process SQL requests against them.

SEQUEL on the System i connects to the remote databases; your PC running ViewPoint does not directly connect to the remote system. You define the remote databases that are available in a database file named SEQUELHOST that is stored on the System i.

When you set up SEQUEL for a user, you determine which SEQUELHOST file (or member) SEQUEL should use for remote database access. This lets you give different users access to different remote databases. Use the ViewPoint Administrator Set Defaults option (or the SETDFT command) to select or edit the file or member that should be used.

Two types of access
SEQUEL provides access to two types of remote databases:

• Databases that you can access with the standard SQL Call Level Interface (CLI)
• Databases that use a 100% pure Java (Type 4) JDBC driver

Defining remote database connections
You can define the connection to the remote database by using the SEQUEL Maintain Host File command (MNTHOSTF) or the ViewPoint Administrator SEQUEL Host File option.

The host file gives SEQUEL the information it needs to connect to a remote database. There are two types of entries: platform entries and database server entries.

• Platform entries begin with an asterisk (such as *ISERIES, *ORACLE) and identify characteristics that all connections to that platform have in common.
• Database server entries allow you to refer to the platform entry so that common properties do not need to be specified for each database on the given platform. However, they also let you override those properties when needed. Database server entry properties are:
  • User name and password. When you connect to a remote database, SEQUEL uses the user/password stored with the database entry. SEQUEL encrypts the password in the SEQUELHOST file. If a user accessing the entry doesn’t have SEQUEL administrator authority, SEQUEL asks for the password before it shows any entry details.
  • Server type. Use this property to link the database server entry to the platform entry.
  • Driver. Use the driver properties dialog to specify the path to a Type-4 JDBC driver. The path, defined by the driver manufacturer, is specified in dotted form, as an IFS subdirectory from \QIBM\UserData\OS400\SQLLib\Function\ (for example, com.microsoft.jdbc.sqlserver.SQLServerDriver). Note: Do not specify a path for DRDA databases; but do make sure DRDA databases are listed in the System i remote database directory. You can access this directory by entering the command WRKRDBDIRE on a command line.
  • Connection string. Specify a connection string to provide additional information to the driver, if needed. This may include the address of the target server, port number, database name, or other connection-specific information.

Test the definition
Enter the following SEQUEL command on a command line to test your connection definition:

SQLCONNECT database

where database is the name of the remote database. If the connection fails, SEQUEL sends messages to the job log and command entry display to help you determine the reason for the failure.

Examples of SEQUEL HOSTFILE entries
Two common remote databases you may want to connect to are a System i database and a Microsoft SQL Server database. Here’s a brief overview of each setup.

System i database. To create a connection to a System i database, use the WRKRDBDIRE or ADDRDBDIRE command to define the connection. Test the connection using the SQL/400 CONNECT TO DATABASE statement. Once the database directory entry has been created, you can add the database name to the SEQUEL HOSTFILE. The server name must match the database name used on the WRKRDBDIRE or ADDRDBDIRE command. Specify *ISERIES for the Server Type. Leave the Driver and Connection String entries blank. Connections to the local machine always use the user profile of the current job; you cannot enter user/password information for a local connection. If the connection is to a remote System i, enter a user/password combination.

Microsoft SQL Server database. Use the Microsoft SQL Server 2000 Driver for JDBC to connect to an SQL Server database. When you installed SEQUEL, it put a copy of this driver into the IFS. The SEQUEL HOSTFILE has a *SQLSERVER platform entry that identifies the driver class to be used for these connections. Enter the user and password information, specify *SQLSERVER for the server type, and enter a properly formatted JDBC connection string. The syntax of the connection string is:

jdbc:microsoft:sqlserver://hostname\instancename:portnumber

where hostname\instancename is the network resolvable name or address of the SQLServer host and the SQL Server instance and portnumber is the TCP/IP port.

Refer to the ViewPoint User Guide for more detailed information.

Contributed by Vivian Hall, Technical Consultant

This entry was posted on Wednesday, June 25th, 2008 at 11:32 am and is filed under SEQUEL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.