StarQuest Technical Documents

Quick Start Guide to Using the StarSQL for Java JDBC Driver

Last Update: 06 April 2011
Product: StarSQL for Java
Version: 2.5x or later
Article ID: SQV00SJ007

Abstract

This document is a guide for installing and configuring the StarSQL for Java software on a Windows, UNIX/Linux, or Mac OS X computer, and is intended for first-time users of StarSQL for Java. Users who are upgrading an existing installation should refer to the Release Notes and the StarSQL for Java User's Guide for upgrade instructions. Read the complete document before starting your installation.

The estimated time to complete all tasks is approximately 30 minutes.

Before You Begin

Before you begin, review the following list to ensure that you have the information and files you need, and that your computer environment provides the minimum requirements.

General Requirements

  • You have requested and downloaded the StarSQL for Java, StarAdmin and StarLicense (UNIX and Mac OS X only) software. All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu if the prerequisite software has not been downloaded. In addition, the download system will provide a registration key used to obtain a temporary license good for 15 days.
  • UNIX/Mac OS X users only: You have installed and configured StarLicense for UNIX or StarLicense for Windows on a computer accessible by the computer that will run StarSQL for Java.
  • You have access to a user ID that has administrative (Windows) or root (UNIX/Mac) authority on the computer where the software will be installed.
  • You have a valid DB2 user ID that provides adequate authority to connect to the database.
  • Ensure that the computer where StarSQL for Java will be installed has the Java Virtual Machine (JVM) or Java Runtime Environment (JRE) v1.4.2 or later installed and specified in the system PATH. You can obtain the Java Runtime Environment free of charge from Sun Microsystems at http://www.java.com/en/download/. From a command prompt, run the java -version command to verify that Java is installed and can be located, as shown in the following example.

C:\>java -version
java version "1.6.0_03"
Java(TM) SE Runtime Environment (build 1.6.0_03-b05)
Java HotSpot(TM) Client VM (build 1.6.0_03-b05, mixed mode)

Prerequisites

In order to connect to a DB2 host, an application must supply StarSQL for Java with basic connectivity information, either in a conection URL or in a data source configuration. This DB2 connectivity information will also be used after the installation to bind host packages using the StarAdmin application.

Collect the required DB2 host information, as described below, or request this information from your network and database administrators.

  1. Locate the Database Name (or Relational Database name) of the database that you want to connect to. In the StarAdmin connection dialog, set the Server value to the database name. You will set the StarSQL for Java configuration parameter databaseName to this value.
    • DB2 for OS/390 or z/OS users: this is the DDF Location Name of the database.
    • DB2/400 users: run the AS/400 command WRKRDBDIRE and locate the entry with a Remote Location value of *LOCAL. If such an entry does not exist, create it with the 1=ADD option.
    • DB2 for Windows, Linux and Unix users: in the left pane of the DB2 Control Center, the databases are shown below the "Database" folder.
    • DB2 Server for VSE & VM: for VSE the database name typically is defined in the DBNAME directory, and for VM the dbname parameter is specified in the CMS Communications Directory in the CMS file of type NAMES.
  2. DB2/400 users only: create an empty library on the host called STARSQL for use as the Package Collection name in StarAdmin and the collection in the StarSQL for Java data source configuration. Alternatively, set the Package Collection or collection parameter value to the name of an existing library (e.g., QGPL) and verify that the AS/400 user who will run StarAdmin to bind packages has read and write authority to this library.
  3. Determine the IP address or network host name of the DB2 host. This value will be used as the Host (StarAdmin) and serverName (StarSQL for Java).
  4. Identify the port number on which DB2 listens for connection requests.
    • DB2 for OS/390 or z/OS users: the port number can be found on the DSNTIP5 panel.
    • DB2/400 users: use the WRKSRVTBLE command and look for the DRDA entry with the port number.
    • DB2 for Windows, Linux and Unix users: in the DB2 Control Center, right-click the DB2 instance, select Setup
      Communications, and select the TCP/IP option. In Properties, locate the port number that is configured for DRDA communications.
    • DB2 Server for VSE & VM: the DBNAME directory contains an entry for each DB2 server that specifies the TCP/IP port number to use (the TCPPORT parameter).

Solution

The following sections guide you through the following major tasks:

Step 1: Install StarSQL for Java (estimated time: 5 minutes)
Step 2: License the Software (estimated time: 5 minutes)
Step 3: Install StarAdmin and Bind StarSQL Host Packages (estimated time: 10 minutes)
Step 4: Verify Connectivity (estimated time: 5 minutes)

Step 1: Install StarSQL for Java

Estimated Time: 5 minutes

To successfully complete this section, you will need a user ID that has administrative (Windows) or root (UNIX/Mac) authority on the computer where the software will be installed. For installation instructions, click on the link for your operating system.

Windows
UNIX/Linux
Mac OS X

Windows

  1. Log on to Windows as a user with administrative authority.
  2. Extract the contents of the software file that you downloaded into a temporary directory.
  3. From the temporary directory, run the setup.exe application to start the installation.

UNIX/Linux

StarSQL for Java is distributed as an .rpm file for installation by the RPM Package Manager and as a .tar file that uses a setup script for installation on UNIX or Linux computers that do not support RPM. To use either of these methods you run the setup shell script. The setup script extracts the contents of the starjdbc.tar file or invokes rpm when using the RPM installer.

  1. Log on to the UNIX/Linux computer as root user.
  2. Extract the downloaded package to a temporary directory using the tar -options <filename> command, as shown in the following example.

    # tar -xvf starsql-java-rpm.tar

  3. From the directory where you extracted the files, enter the following command to run the setup shell script.

# ./setup

The installation process installs the StarSQL for Java software into the default directory shown in the following table for each supported UNIX platform.

UNIX Platform Default StarSQL for Java Installation Directory
AIX /usr/lpp/starsql_java
FreeBSD /usr/local/share/starsql_java
HP-UX /opt/starsql_java
Linux /usr/share/starsql_java
Solaris /opt/starsql_java

 

Mac OS X

To install StarSQL for Java on a Mac OS X computer:

  1. Use the Finder to open the folder that contains the installation source files.
  2. Double-click setup-macos.command, you will be prompted for your password.

The default directory for installing StarSQL for Java on a Mac OS X computer is /Applications/starsql_java.

Return to the top

Step 2: License the Software

Estimated Time: 5 minutes

For licensing instructions, click on the link for your operating system.

Windows
UNIX/Linux/Mac OS X

Windows

To license the software, you need the Download Instructions that were sent to the email address specified in the StarQuest Product Download Request form. You need these instructions and Internet access to license the software.

If the computer running StarSQL for Java has access to the Internet, follow the Online Licensing Instructions below. Otherwise, follow the Alternate Licensing Instructions.

Online Licensing Instructions
  1. Open the StarLicense Configuration utility from Start --> Programs --> StarSQL for Java --> StarLicense Configuration.
  2. Click on the License Online tab. Select a License Lock Type, enter in the Registration Key provided in the download confirmation email, and click Get License.

When the request successfully completes, the license(s) for the software you are registered to use appear in the License Keys list of the Licenses tab. The Registration Key may produce several License Keys, depending on the products you are registered to use. Subsequent attempts to use the same Registration Key will result in the identical License Key(s) being retrieved.

Alternate Licensing Instructions
  1. On the computer where StarSQL for Java is installed, open the StarLicense Configuration utility from Start --> Programs --> StarSQL for Java --> StarLicense Configuration. Record the Host ID displayed on the License Online tab.
  2. From a computer that has access to the Internet, click on or browse to the following URL:

http://starcust.starquest.com/Registration/index.html#license

  1. On the StarQuest Online Licensing Form web page, enter in the email address used for the original download request and the Registration Key you received in the download confirmation email.
  2. Enter the Host ID value recorded previously and select the "Node-locked" license option. Click Next.
  3. Review the information provided. If any changes are required, click Previous and modify the values as needed. Otherwise, click the Accept button.
  4. Copy the license key displayed on the web page. You will also receive an e-mail with the license key.
  5. On the computer running StarSQL for Java, open the StarLicense Configuration utility from Start --> Programs --> StarSQL for Java --> StarLicense Configuration. Under the Licenses tab, click the Add button and enter in the license key.

UNIX/Linux/Mac OS X

These instructions assume that you have installed and configured StarLicense for UNIX or StarLicense for Windows on a computer accessible by the computer running StarSQL for Java. You will need to know the name or IP address of the StarLicense server, the port on which is listens for licensing requests, and the Product ID of the license installed in StarLicense. If you are unsure of these values, ask the administrator who initially configured StarLicense.

  1. Locate the StarLicense.properties file in the directory where StarSQL for Java was installed.
  2. Edit the [ClientServer0] section of the StarLicense.properties file to specify a StarLicense server to use for licensing StarSQL for Java connections. The StarLicense.properties file must contain the following section to identify the license server.

[ClientServer0]
hostname=<StarLicense_server_hostname.domain.com>
port=4999
PRODUCTID=SQ

  1. Save the StaLicense.properties file when finished.

 

For both Windows and UNIX/Linux/MacOS:

If you are connecting to an IBM System i (iSeries, DB2/400) and received a license for StarSQL for iSeries (Product ID "CA"), edit the StarLicense.properties file located in the directory where StarSQL for Java was installed (on Windows, you can use the "Edit StarLicense.properties" shortcut in the StarSQL for Java program group) and uncomment the following line:

DEFAULT_PRODUCTID=CA

Return to the top

Step 3: Install StarAdmin and Bind StarSQL Host Packages

Estimated Time: 10 minutes

StarAdmin cannot be installed on a Mac OS X computer. Install and run StarAdmin on a Windows or UNIX/Linux computer.

To successfully complete this section you will need the following:

  • a Windows user ID with administrative authority or a UNIX/Linux user ID that has root authority,
  • a DB2 user ID that has authority to create and bind packages on the DB2 host, and
  • the database connectivity information collected in the Before You Begin section.

This step is best performed by the database administrator.

Install StarAdmin

Windows
  1. Log on as an administrative user.
  2. Execute setupwin32.exe to launch the installer.
  3. On the Welcome dialog, click Next.
  4. Accept the License Agreement and click Next.
  5. Accept to install StarAdmin in the default directory of C:\Program Files\StarQuest\StarAdmin or browse to select an alternate directory. Click Next to continue.
  6. Review the summary information. If acceptable, click Install to being the installation. Otherwise, click Back and make any necessary changes.
  7. After the installation is complete, click Finish.
UNIX/Linux
  1. Log on as the root user.
  2. Execute setuplinux.bin to launch the installer.
  3. On the Welcome dialog, click Next.
  4. Accept the License Agreement and click Next.
  5. Accept to install StarAdmin in the default directory of /opt/StarQuest/StarAdmin or browse to select an alternate directory. Click Next to continue.
  6. Review the summary information. If acceptable, click Install to being the installation. Otherwise, click Back and make any necessary changes.
  7. After the installation is complete, click Finish.

Bind DB2 Host Packages

  1. Start StarAdmin.
    • Windows: From Start -> Programs -> StarAdmin, click on the StarAdmin shortcut.
    • UNIX/Linux: From /opt/StarQuest/StarAdmin (or from the directory where you installed StarAdmin), execute staradmin.
  2. Enter the database connectivity values, as collected in the Before You Begin section and explained below, and click OK.
    • Host: the HostName or IP address of the DB2 host
    • Port: the DB2 listening port, in most cases the default value of 446 is correct
    • Database Name: the Database Server name
    • Package Collection: DB2/400 users should specify a valid library, others should use the default value of STARSQL
    • Username/Password: DB2 user account that has authority to create and bind packages
  3. Upon connecting successfully, StarAdmin will immediately bind one package, which will be displayed in the package list. The status bar at the bottom of the dialog will display the Database Name, Package Collection, DB2 Type, and Version. If StarAdmin fails to connect to the database, review the suggested resolutions in the Binding StarSQL Packages Using StarAdmin technical document and make any necessary corrections to the values in the connection dialog.
  4. Leave the default values for the Package Settings and Grant Options.
  5. From the Command menu, select Bind to create and bind the remaining packages.
  6. Once the packages have been created, the Summary dialog will display the results. Record the connection information in the Summary dialog to assist with the data source or connection URL configuration for StarSQL for Java.

If the packages are created and bound successfully, the summary output will look similar to the following:

Package binding starting: Wed Jul 29 14:44:03 PDT 2009
UID=SUPERUSER
HostName=DB2HOST.DOMAIN.COM
Port=446
Server=DB2PROD
PkgColID=STARSQL
AutoTypDefOvr=
BindRules=RUN
CustomizePrdid=No
UseJumboPackages=No
UseEncryption=Any

jdbc:StarSQL_JDBC://DB2HOST.DOMAIN.COM:446/DB2PROD;collection=STARSQL

SQL package SWNC0000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWNC0000 to PUBLIC
SQL package SWRU0000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRU0000 to PUBLIC
SQL package SWRC0000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRC0000 to PUBLIC
SQL package SWRR0000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRR0000 to PUBLIC
SQL package SWTS0000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWTS0000 to PUBLIC
SQL package SWNC1000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWNC1000 to PUBLIC
SQL package SWRU1000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRU1000 to PUBLIC
SQL package SWRC1000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRC1000 to PUBLIC
SQL package SWRR1000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWRR1000 to PUBLIC
SQL package SWTS1000 in collection STARSQL has been created.
Granted execute privileges on STARSQL.SWTS1000 to PUBLIC
SQL package QSYS2 in collection STARSQL has been created.
Granted execute privileges on STARSQL.QSYS2 to PUBLIC

Package binding completed: Wed Jul 29 14:44:55 PDT 2009

If any errors are reported, review the suggested resolutions in the Binding StarSQL Packages Using StarAdmin technical document, correct the problem and execute the bind operation again. If you are unable to resolve the error condition, copy the entire Summary text and send it to StarQuest Customer Support at contact@starquest.com.

  1. Click the Finished button to return to the main dialog.
  2. From the File menu, choose Close Database and then Exit.

Return to the top

Step 4: Verify Connectivity

Estimated Time: 5 minutes

StarSQL for Java supplies several sample applications that can be used to verify database connectivity. These applications can be found in the \samples subdirectory in the StarSQL for Java installation directory.

To test basic connectivity, use the simpconn application in the \samples\simpconn directory.

  1. Open the simpconn.bat (Windows) or simpconn.sh (UNIX/Linux or Mac OS X) file in a text editor.
  2. Edit the URL, USER, and PASSWORD variables to specify values appropriate for your database environment. Save the file.
  3. To run the simpconn application on:
    • Windows: open a command prompt, change directory to the \samples\simpconn directory and run the simpconn.bat file.
    • UNIX/Linux or Mac OS X: run the simpconn.sh to launch the simpconn application.

    If the connection is successful, the output will look similar to the following:

Connected to jdbc:StarSQL_JDBC://MYHOST:446/MYRDB
Database DB2/400
Version QSQ05040
Driver StarSQL for Java
Version V2.52.0316
pass 1 completed
Press Enter to close the connections

To test the ability to query the database using either QueryApp or CatalogApp, perform the following procedures:

Windows

  1. Start QueryApp or CatalogApp from Start --> Programs --> StarSQL for Java --> Samples.
  2. Enter the Database, Host, Port, UserID and Password for the DB2 system that you want to connect to.
  3. Click the Connect button. If the connection fails, review the error message at the lower portion of the dialog, correct the problem and retry the connection.
  4. Execute a simple SQL query (QueryApp) or view the catalog information for a specific database object (CatalogApp).

UNIX/Linux/Mac OS X

  1. Run the shell script samples.sh (UNIX) or samples.command (for MacOS X).
  2. From the menu of the available sample applications and utilities, as shown below, select either CatalogApp or QueryApp.

    StarSQL for Java Utility & Sample Applications

    1) ShowVersion
    2) CatalogApp
    3) QueryApp
    4) LobTestApp
    5) CreateDS - Create a DataSource
    6) Transaction Log Manager
    7) Exit

  3. Enter the Database, Host, Port, UserID and Password for the DB2 system to which you want to connect.
  4. Click the Connect button. If the connection fails, review the error message at the lower portion of the dialog, correct the problem and retry the connection.
  5. Execute a simple SQL query (QueryApp) or view the catalog information for a specific database object (CatalogApp).

Refer to the StarSQL for Java User's Guide for instructions on running the other sample applications.

Continuing Your Software Evaluation

This Quick Start Guide is intended to help you install the StarQuest software and access DB2 data as quickly as possible.

As you use the StarSQL software, refer to the product documentation for more information. StarSQL for Java includes a User's Guide that provides more information about driver configuration and defining connection URLs and data sources.

If you encounter any problems while using the evaluation software, please send an email to StarQuest Customer Support at contact@starquest.com or call (+1) 415-669-9619 for assistance.

Additional References

StarSQL for Java Release Notes
StarSQL for Java User's Guide


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.