StarQuest Technical Documents

Binding StarSQL Packages Using StarAdmin

Last Update: 12 July 2021
Product: StarAdmin
Version: 5.5x or later
Article ID: SQV00SQ052

Note: This document describes the platform-independent (Java-based), standalone StarAdmin application. In some cases, StarQuest support may recommend using the original Visual Basic-based StarAdmin application (StarAdmin Classic); refer to the technical document Binding StarSQL Packages Using StarAdmin Classic.

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 v9 to Db2 v10)
• after a major StarSQL upgrade (such as from StarSQL v5.x to StarSQL v6.x)

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

the Jumbo Package option allows StarSQL to set the maximum active statement handle limit to 1,314 rather than the default of 64. For more information see StarSQL Jumbo Packages.

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. 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. Click Install to being the installation or click the Back button to make any changes.
7. After the installation is complete, click Finish.

UNIX/Linux

1. Log on as the root user.
2. Execute setup to launch the installer.
3. StarAdmin will be installed in the default directory of /opt/StarQuest/staradmin.
4. To run StarAdmin, make sure you have a GUI environment (i.e. set the environment variable DISPLAY to an appropriate value) and execute /opt/StarQuest/staradmin/staradmin.

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 for i (Db2/400) users:: use the WRKSRVTBLE command and look for the DRDA entry with the port number. 446 is typical. Db2 for Linux, UNIX, Windows users: identify the port number that is configured for DRDA communications e.g. db2 get dbm cfg | findstr SVCENAME (Windows) db2 get dbm cfg | grep SVCENAME (UNIX) The result is likely to be a symbolic name e.g. TCP/IP Service name (SVCENAME) = db2c_DB2 Look up the symbolic name db2c_DB2 in C:\Windows\System32\drivers\etc\networks (Windows) or /etc/networks (UNIX). 50000 is typical.
Database Name	Db2 for z/OS users: this is the DDF Location Name of the database. Db2 for i (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 Linux, UNIX, Windows users: db2 list database directory
Package Collection	Set this value as desired (typical is SQDR when using StarQuest Data Replicator; STARSQL otherwise) Db2 for i (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 http://support.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

Refer to the release notes for StarSQL for details about backwards compatibility. In many cases, newer StarSQL packages are backwards compatible with older versions of StarSQL.

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. The "Local Security Service retryable error" can also occur if DRDA on the host system has been configured with a minimum encryption level of *AES rather than *DES. See Issues with AES password encryption requirement below for details and workarounds.

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

Issues with AES password encryption requirement

Connection issues can occur if DRDA on the host system has been configured with a minimum encryption level of *AES rather than *DES. See Issues with AES password encryption requirement in StarSQL Common Errors for details.

Symptoms

The symptoms vary depending on the host type:

Connecting to Db2 for i fails with :
[StarSQL][DB2] A local security server retryable error has occurred

Connecting to Db2 for z/OS fails with:
The password for user 'myuser' is invalid.

Connecting to Db2 LUW fails with:
No message text for host error.
SQLCODE = '-030,082' SQLSTATE='08001'

A connection from odbctest to Db2 LUW fails with:
szSqlState = "08001", *pfNativeError = -30082, *pcbErrorMsg = 288, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]Attempt to establish connection failed with security reason <17> (<UNSUPPORTED FUNCTION>)."

Solution (recommended)

Connecting via SSL to Db2 for i or Db2 for z/OS will override password encryption requirements:

• On the Advanced dialog, set the parameters:
  Netlib SQSSL.DLL
  SSL TLS
  useEncryption No
  
• On the connection dialog, enter the host's SSL listening port (e.g. 448).

On Db2 for i, if CHGDDMTCPA has been configured as PWDRQD(*USRIDPWD) ENCALG(*AES), it is only necessary to set useEncryption to No.

There is no solution for connecting to a Db2 LUW system that has been configured to require AES encryption.

Other Workarounds

Create a StarSQL 32-bit ODBC data source with UseEncryption=No and connecting via SSL, and use StarAdmin Classic.

OR

Temporarily set the minimum encryption level of the DDM server to *DES. On IBM i,

1. Issue the command:
   CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRIDPWD) ENCALG(*DES).
2. Bind packages with StarAdmin.
3. Change the level back with:
   CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRIDPWD) ENCALG(*AES)

OR

Use a StarSQL 64-bit ODBC data source as follows:

1. Using ODBC Administrator, set UseEncryption=No in Expert Settings, or uncheck the checkbox for Enable User ID/Password Encryption on the Security/Accounting panel.
2. Connect with ODBC Test or another ODBC application and run a query - this creates the catalog package (the name will vary based on the host type - e.g. QSYS2 for Db2 for i) and the default isolation level (SWNC0000 for Db2 for i; SWRC5000 for Db2 for z/OS; SWRC0000 for Db2 for LUW).
3. If desired, Change the DSN to use other settings of interest for isolation level, held cursor, DYNAMICRULES and KeepDynamic. For example, it is typical to set Isolation Level=Read Committed for accessing journaled collections on Db2 for i. Disconnect and reconnect with odbctest and run a query - this creates SWRC0000.
4. Repeat for all other combinations of isolation level and held cursor settings. See Binding Packages in the StarSQL for Windows User Guide for details about package names and their relation to the settings for isolation level, held cursor, DYNAMICRULES and KeepDynamic.
5. Grant public access to the packages. The following is a typical SQL statement for Db2 for i:

GRANT EXECUTE ON PACKAGE SQDR.QSYS2, SQDR.SWNC0000, SQDR.SWRC0000 TO PUBLIC

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 evaluation software, please open a problem report with StarQuest Customer Support at http://support.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)