StarQuest Technical Documents

Binding StarSQL Packages Using StarAdmin

Last Update: 15 October 2009
Product: StarAdmin
Version: 5.5x or later
Article ID: SQV00SQ052

Abstract

This Quick Start Guide describes how to install, configure and use StarAdmin to bind the host SQL packages needed to support StarSQL. StarAdmin is a GUI application that can be installed and operated on a Windows, UNIX, or Linux computer. It does not require an existing installation of StarSQL or a license to operate.

You must create and bind all required packages in the following situations:

• when setting up StarSQL for the first time
• after a DB2 upgrade (such as from DB2 v8 to DB2 v9)
• after a major StarSQL upgrade (such as from StarSQL v4.x to StarSQL v5.x)

If you are rebinding packages after an upgrade, review the considerations below.

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

Background Information

A SQL package is an object that DB2 uses to process a SQL statement. Different packages are required to execute dynamic SQL, static SQL, and ODBC catalog functions. The process of binding packages only needs to be done once per DB2 instance if all StarSQL users share the same set of packages.

The version of StarAdmin determines which versions of StarSQL and StarSQL for Java the packages support. Specifically, a StarAdmin version of 5.51 will bind packages that support StarSQL v5.51 and earlier and all StarSQL for Java versions. To find the version of StarAdmin, select About StarAdmin from the Help menu.

General Requirements

The same individual or a combination of individuals must be capable of performing in the following roles.

• System Administrator is someone with authority to install the StarAdmin on a computer with TCP-IP access to the database host. This user typically must be an administrator (Windows) or root user (UNIX/Linux) on the platform.
• Database Administrator is someone with user credentials suitable for creating collections and binding packages on the database host.

Summary of tasks

1. Request to download StarAdmin.
2. Receive the e-mail with a download link for StarAdmin.
3. System Administrator: Download StarAdmin using the download link from step 2 and install the software.
4. Database Administrator: Bind packages to the database using StarAdmin. This requires knowledge of the database host and sufficient user privilege.
5. Database Administrator: Provide database connectivity information to the StarSQL Client-Platform Administrator to be used for ODBC or JDBC data source configuration.

Step 1: Request StarAdmin Software

Estimated Time: 5 minutes

All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a web browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu. Request to download any version of StarSQL for any type of platform as StarAdmin will automatically be provided when this request is fulfilled.

Step 2: Receive the Download Confirmation E-mail from StarQuest

Estimated Time: Less than 5 minutes

After submitting a request to download StarSQL, you will receive an e-mail containing the download link for StarAdmin. You may disregard the download links for StarSQL and StarLicense as these components are not needed to successfully install and use StarAdmin.

Step 3: (System Administrator task) Download and Install StarAdmin

Estimated Time: 10 minutes

The user who installs StarAdmin must be an administrator (Windows) or root user (UNIX/Linux) on the platform.

Download StarAdmin to a temporary directory using the download link from step 2. For detailed installation instructions, click on the link below for your operating system.

Windows
UNIX/Linux

Windows

1. Log on as an administrative user.
2. Execute setup.exe to launch the installer.
3. If prompted to install the Microsoft Visual C++ 2008 SP1 Redistributable package, click the Install button.
4. On the Welcome dialog, click Next.
5. Accept the License Agreement and click Next.
6. 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.
7. Click Install to being the installation or click the Back button to make any changes.
8. After the installation is complete, click Finish.

UNIX/Linux

1. Log on as the root user.
2. Execute setup<platform>.bin to launch the installer.
3. On the Welcome dialog, click Next.
4. Accept the License Agreement and click Next.
5. Accept the prompt 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.

Step 4: (Database Administrator task) Bind Host Packages Using StarAdmin

Estimated Time: 15 minutes

The database connectivity information collected in this section should be later provided to the StarSQL Client-Platform Administrator for use creating ODBC or JDBC data sources.

1. Collect the DB2 host information and fill in the values for your environment in the space provided in the table below.

Connection Parameter	Description	Your Value
Host	The network Host Name or IP address of the DB2 host.
Port	DB2 for 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).
Database Name	DB2 for 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.
Package Collection	Set this value to STARSQL. DB2/400 users only: create an empty library on the host called STARSQL or set this value to the name of an existing library (e.g., QGPL).
Username/Password	A DB2 user account that has authority to create and bind packages on the database.

2. From the StarAdmin program group (Windows) or /opt/StarQuest/StarAdmin directory (UNIX/Linux), start the StarAdmin GUI application.
3. Enter the database connectivity values in the Connection Settings dialog and click OK.
4. 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 Common Error Messages section below and make any necessary corrections to the values in the connection dialog.
5. Leave the default values for the Package Settings and Grant Options.
6. From the Command menu, select Bind to create and bind the remaining packages.
7. Once the bind package operation has completed, review the summary dialog. 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 Common Error Messages section below, 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.

7. Copy the entire Summary text to the clipboard as this information will be used in step 5.
8. Click the Finished button to return to the main dialog.
9. From the File menu, choose Close Database and then Exit.

Step 5: (Database Administrator) Provide Connectivity Information to the StarSQL Client-Platform Administrator

Estimated Time: Less than 5 minutes

The StarAdmin summary dialog contains the database connectivity parameters for your DB2 host and the name of the package collection in which StarAdmin created and bound StarSQL packages. This information should be used by the StarSQL Client-Platform Administrator during the configuration of an ODBC data source or a JDBC connection URL.

• Provide the ODBC information to StarSQL for Windows/UNIX/Linux users:

  HostName=<your DB2 host name or IP address>
  Port=<your DB2 port>
  Server=<your DB2 database name>
  PkgColID=<your package collection>

• Provide the JDBC connection URL to StarSQL for Java users:

  jdbc:StarSQL_JDBC://<hostname>:<port>/<database_name>;collection=<package_collection>

Considerations when Re-binding Packages after a DB2 or StarSQL upgrade

The version of StarAdmin determines which versions of StarSQL and StarSQL for Java the packages support. Specifically, a StarAdmin version of 5.51 will bind packages that support StarSQL 5.51 and earlier and all StarSQL for Java versions. StarSQL packages are backwards compatible as far as StarSQL v5.38, i.e. StarSQL 5.38 and 5.4x users and StarSQL 5.5x users can use the same set of packages created with the StarAdmin version 5.5x application. Users of StarSQL prior to v5.38 should use a set of packages created with an earlier version of StarAdmin.

In upgrade scenarios where not all clients will upgrade to StarSQL 5.5x (i.e., other clients will remain at an earlier release), you may want to bind a separate set of packages for the StarSQL 5.5x users. This can be accomplished by setting the value of the Package Collection in the StarAdmin connection dialog to a unique collection name, such as STARSQL55, to distinguish these new packages from those created with older versions of StarSQL.

Important note: no matter which package collection value is used when binding packages with StarAdmin, it is important to specify this value as the Package Collection Name (StarSQL), PkgColID (StarSQL for UNIX), or collection (StarSQL for Java) in the client data source configuration.

Return to the top

Common Error Messages

Connection Errors

Error Message	Suggested Resolution
Reason: Unable to resolve hostname '<host>'	Verify that the HostName is correct or, alternatively, specify the IP address in the Host field.
Reason: Unable to connect. Database stopped or incorrect port number	Verify that the port number is correct and that DB2 is running and listening on that port. If the Host value specified is the IP address, verify that this address is correct and that it can be reached on the network from this computer using the 'ping' command from a Windows command prompt.
Reason: connect timed out	Verify that the HostName value is correct and that it can be reached on the network from this computer using the 'ping' command from a Windows command prompt. Alternatively, specify the IP address as the HostName.
The database server '<Server>' was not found.	Verify that the Database Name is correct as described in the instructions in the table in step 4.
Reason: [StarSQL][JDBC Driver] Communications error - the input stream ended abnormally while receiving data.	It is likely that the port number is valid on the host system but it is not serving a DB2 DRDA server. Verify that the DRDA port number is correct using the instructions in the table in step 4.
The user ID was missing or invalid.	The user ID entered is not valid on the DB2 host system. Verify that you have the correct user ID and that you have typed it correctly.
The password for user '<Username>' was missing or invalid. or [StarSQL JDBC Driver][DB2] A Local Security Service retryable error has occurred.	The password entered for this user is not correct. Verify that you have the correct password and are typing the password in the correct case.

Return to the top

Bind Errors

Error Message	Suggested Resolution
Object QSYS.<Package Collection> type *COLLECTION not found. SQLSTATE=42704	The library or collection specified as the Package Collection does not exist on the host. Either create an empty library on the host called STARSQL and set the Package Collection parameter value to STARSQL, or specify the name of an existing library (e.g., QGPL) as the Package Collection.
Not authorized to object 'PACKAGE.<Package Collection> .SWRCxxxx ' type '*'. SQLSTATE=42501	The user does not have the necessary authority to create objects in the Package Collection specified. Either request the permission for this user or connect with a user account with elevated permissions.

Return to the top

Contacting StarQuest

As you use the StarAdmin software, refer to the product documentation for more information. StarAdmin includes Release Notes and online Help that provide more information about the advanced settings and optional configuration parameters.

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

Additional References

StarAdmin Release Notes
StarAdmin Online Help (accessible by clicking the "?" icon in the StarAdmin application)

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2009 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.