Use the steps below to troubleshoot and solve remote database issues related to Sequel or Viewpoint.
- If possible, make sure you have Sequel version R10M14 or higher. Use the following command to verify:
- Sequel installs various .jar files into the /sequel and /rio/lib folders. If newer versions of any of these files are required, do not rename the current file. Instead, move the file out of the folder and replace it with the newer version. Screen shots of these folders may be requested by your support rep to confirm the content and versions of the files. Below is a list of some of the more commonly used Database/Drivers in the rio/lib folder :
Oracle ojdbc14.jar MySQL mysql.jar Microsoft sqljdbc.jar SQL Server7 mssqlsever.jar
- Create a screen shot of the Connection String/Driver path using either ViewPoint Administrator or the command SEQUEL/MNTHOSTF. Use this for reference, or to share with SEQUEL Support. Two examples are shown below:
Example SQL 2000 strings:
Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver Connect: jdbc:microsoft:sqlserver://hostname\instancename:portnumber
Example SQL2005/SQL2008/SQL2012 strings:
Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver Connect: jdbc:sqlserver://hostname:portnumber;instancename
- The specific details of the string syntax is defined by the JDBC driver vendor (Microsoft in this case). SEQUEL simply passes the string specified in the Sequel Host Definition to the JDBC driver.
- 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 included with the product.
- If you are using a driver that is not supplied by SEQUEL Software, place the jar file in the /rio/lib folder.
- Document the installed Java options:
- Issue the command GO LICPGM.
- Select Option 10.
- Scroll to the Java products (xxxxJC1 and xxxxJV1) toolbox, developer kit for java and java developer kits.
- Collect print screens showing options and installed status. V7R1 does not have developer kit. (Also, V7R1 requires 57xxSS1 option 33 - Portable App Solutions Environment)
- Set your environment for maximum logging. The following file—\QIBM\UserData\Java400\ SystemDefault.properties—contains the following additional settings that produce more detailed information:
os400.display.properties=true os400.run.verbose=true javax.net.debug=allNote: For V7R1, add the following job environment variable:
Name Value IBM_JAVA_OPTIONS ‘-verbose’
- Issue the following command to test the connection:
SBMJOB CMD(SEQUEL/SQLCONNECT SERVER(NAME)) LOG(4 00 *SECLVL) LOGCLPGM(*YES)This will create two spooled files—QPRINT (java job/error log) and QPJOBLOG. Print these files to review the test or to share with SEQUEL Support.
Note: If SQLCONNECT is successful but ViewPoint still fails, java logs from the ViewPoint job must be collected. For ViewPoint, these logs are in job QPRTJOB associated with the user id. They can be found using:
WRKSPLF (specify the ViewPoint user id) or
WRKJOB QPRTJOB (scan the list for the recent job of the ViewPoint user)Example error message:
‘Relational database xxxnot in relational database directory’This message indicates SEQUEL is trying to connect using CLI, not JDBC which is an obvious problem if you are trying to use a JDBC driver based connection. This indicates a problem with the host definition. The problem is most likely the connection string or incompatibility between the driver and connection strings.
- Check the Group PTF for Java. It may be necessary to update the GROUP PTF for JAVA. The groups are as follows for the various O/S Levels:
(see http://www-912.ibm.com/s_dir/sline003.nsf/GroupPTFs for current levels)
- Finally: Be sure to check for unwanted jar and class files. Unexpected driver files and class files are a common source of connection and/or operational problems. These files cause problems when they get used instead of the driver installed into the /rio/lib folder. Users are likely to find unex- pected jar files and/or class files in one or more of the following locations:
(for jar files)
(Microsoft class files)
(Oracle class files)
(MySQL class files)
Less likely locations:
(where JJ =40 or 50 or 60 or 70 and KK=32 or 64)
The above paths should be purged of any related JDBC drivers unless there is another product that specifically requires the driver to be in that location.