Copyright Information

StarSQL v5.5
StarQuest Ventures, Inc.

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, people and events depicted herein are fictitious and no association with any real company, organization, product, person or event is intended or should be inferred.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of StarQuest Ventures, Inc.

StarQuest products contain certain trade secrets and confidential and proprietary information of StarQuest Ventures, Inc. StarQuest may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from StarQuest, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. Additionally, any public network IDs or addresses referenced in this document are for demonstration/example purposes only. The inclusion of such IDs and addresses in this document does not grant you any express or implied right to test or otherwise use or access any such network ID or address.

StarQuest and StarSQL are trademarks of StarQuest Ventures, Inc.

Microsoft, Windows, Windows XP, and Windows Vista are registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

StarQuest Ventures logo

©Copyright 1997-2009. StarQuest Ventures, Inc.. All rights reserved.
August 2009 edition


Welcome to StarSQL

StarSQL is available as an ODBC driver for Windows- and UNIX-based computers, and as a JDBC driver for any computer that has the Java Runtime Engine (JRE) or Java Virtual Machine (JVM) installed. The StarQuest floating license allows for you to install any version of the StarSQL driver on any number of client computers and use a license only when a connection to a DB2 host is requested.

The StarSQL for Windows driver is an ANSI driver that is compliant with the ODBC v3.0 specification. The StarSQL driver resides on the Windows computer and enables ODBC applications to transparently access data that resides in a DB2 database on a host computer. See Host Requirements and Network Requirements for details of the host databases and network connections that StarSQL supports.

StarSQL for Windows v5.5 includes a 32-bit driver and a 64-bit ODBC driver that can take advantage of Windows x64 architecture. You can install and run both the 32-bit and 64-bit version of the StarSQL driver on the same 64-bit Windows computer. Install and run only the 32-bit version of the StarSQL driver on a 32-bit Windows computer. The StarSQL for Windows User's Guide provides details for installing one or both versions of the driver software.


New Features

StarSQL Features

StarSQL is a powerful ODBC driver that enables Windows applications to transparently access, send SQL queries to, and retrieve results from databases on remote IBM mainframe, midrange, or UNIX systems.

StarSQL for Windows provides the following major features:

  • support for both 32-bit and 64-bit architectures

  • support for LOB (large object) data types

  • support for global (two-phase) transactions

  • support for encrypted passwords

  • international language support, including enhanced support for East Asian multi-byte and double-byte character sets

  • utilities for recording and viewing DRDA traces

  • support for the latest releases of DB2 and Windows, such as DB2 v9 for UDB and z/OS, i5/OS, and Windows Vista and Server 2008

Support for large object (LOB) data types was added in StarSQL v5. Please see the description of the LongStrParams data source setting if you have applications or hosts that do not support LOBs.


Related Applications

In addition to the StarSQL ODBC driver, the StarSQL product includes some supporting applications. The components that are installed when you install the StarSQL software depend on whether you choose the Typical or Custom installation option.

StarSQL Typical Installation

When you choose the Typical installation option as you install the StarSQL software, the following components are installed.

Application | Description
DSImport | Used by the StarSQL installation program to import a configured data source to multiple desktops throughout the enterprise. See Importing a Data Source for detailed procedures.
License Configuration Utility | Used to obtain and configure a license for using StarSQL. See the StarSQL User's Guide for details about licensing the StarSQL software.
SQImport | Used by the StarSQL installation program to import the license configuration as StarSQL is installed. See the StarSQL User's Guide for details about installing and licensing the StarSQL software.
DRDA Trace Recorder | Creates and stores a DRDA binary trace file. Use the DRDA Trace Viewer, available from the Custom Installation option, to view the trace file.

StarSQL Custom Installation

The default settings for the Custom installation option installs all of the available StarSQL components. You can choose which databases and network interfaces, documentation, and tools are installed by de-selecting any feature you do not want to install. In addition to the applications that are installed with the Typical installation, the following applications are available for installation when you choose the Custom installation option.

Application | Description
StarAdmin | Binds StarSQL ODBC driver static packages on the host database. Creates restricted catalog views on the host. Grants and revokes privileges on packages and restricted views.
StarSQL Resource Manager Administration Tool | An application that runs within the Windows Computer Management console to help administrators manage two-phase commit transactions that have been left in an "Indoubt" status.
StarScribe Package Editor | Allows you to modify recorded SQL statements and bind them into a Static SQL package on the DB2 host. You also can use StarScribe to create and bind Static SQL packages from unrecorded SQL statements that you enter, or to rebind Static SQL packages on the host after you have appended new statements to a previously recorded package file.
DRDA Trace Viewer | Used to decode the binary trace file created with the DRDA Trace Recorder.


Contacting StarQuest

Please use the following methods to contact StarQuest Ventures if you need to obtain a license key, or have suggestions or need information about StarQuest products.

Support

If you do not want to use the online licensing feature of the License Configuration utility, you can obtain a license key for your product by sending an email to contact@starquest.com with the following information:

  • TCP/IP address or Host ID of the computer on which the license will be installed

  • Number of connections purchased

  • Company Name

  • Contact Name

  • Phone Number

  • Email Address

StarQuest Support will send a reply email that provides the license key for your organization's use of the product. Since the license is unique to the computer on which it will be installed, you must contact StarQuest should you need to move the license from one computer to another.

Additional technical support may be available subject to the prices, terms, and conditions specified in your organization's maintenance contract with StarQuest Ventures, Inc.

Sales and Service

If you have ideas for product enhancements or need more information about how StarQuest products can provide solutions for connecting Windows and UNIX applications to IBM host resources, please contact us via any of the following methods.

Address | StarQuest Ventures, Inc. P.O. Box 1076 Point Reyes Station, CA 94956
Telephone | 415-669-9619
Fax | 415-669-9639
Email | contact@starquest.com
World Wide Web | www.starquest.com


System Requirements

This topic describes the systems supported on the desktop and host, and the networks that StarSQL supports.


Desktop Requirements

The StarSQL 32-bit ODBC driver is supported on all Windows platforms, including:

  • Windows 2000

  • Windows XP

  • Windows Vista

  • Windows Server 2003 or 2008

The StarSQL 32-bit and 64-bit ODBC drivers are supported on the following 64-bit platforms, which are based on the AMD64 and EM64T processor architecture (the Itanium processor architecture is not currently supported):

  • Windows XP x64 Edition

  • Windows Vista x64 Edition

  • Windows Server 2003 x64 Edition

  • Windows Server 2008 x64 Edition

For 64-bit OLEDB applications, such as the SQL Server 2008 linked server functionality, you need the 64-bit OLEDB Provider for ODBC (MSDASQL) installed. The OLEDB Provider is included in Windows Server 2008 x64 Edition and Windows Vista x64 Edition SP1, and is available as a download for Windows Server 2003 x64 Edition from http://www.microsoft.com/downloads.


Host Requirements

You can use the StarSQL driver to connect to any of the following host databases:

  • DB2 for OS/390 and z/OS v5.1 and later

  • DB2 Server for VSE & VM v7.1 and later

  • DB2 Server for VSE & VM (formerly known as SQL/DS) v3.3 and later

  • DB2 for i (formerly known as DB2/400, DB2 UDB for iSeries, and DB2 for i5/OS) running OS/400 V3R2 and later

  • DB2 Universal Database (UDB) v6.x and later for Linux, UNIX, and Windows

The following host databases provide native DRDA over TCP/IP support to StarSQL clients:

  • DB2 for OS/390 and z/OS v5.1 and later

  • DB2 Server for VM v6.1 and later

  • DB2 for VSE v7.1 and later

  • DB2 for i

  • DB2 UDB v6.1 and later for Linux, UNIX, and Windows

To take advantage of the StarSQL for Windows support for password encryption, two-phase commit transactions, and LOB data types, the database system also must support those advanced features.

The following database systems support or successfully negotiate password encryption:

  • DB2 OS/390 v6.1 and later

  • DB2 UDB for iSeries (OS/400) V4R5 and later, including i5/OS
    To support password encryption on a DB2 UDB for iSeries V5R1 or V5R2 host, you must install the Cryptographic Access Provider 56-bit for iSeries (#5722-AC2) or Cryptographic Access Provider 128-bit for iSeries (#5727-AC3).

  • DB2 UDB for Linux, UNIX, and Windows v9.1 and later

The following database systems support two-phase commit transactions:

  • DB2 for OS/390 v5r1 and later (with PTF SI09453 applied)

  • DB2 UDB for iSeries (OS/400 v5r2 and later)

  • DB2 UDB for Linux, UNIX, and Windows v8.1 and later

The following database versions support LOB data types:

  • DB2 for OS/390 V5R1 and later (with PTF SI09453 applied)

  • DB2 UDB for iSeries (OS/400 V5R2 and later)
    To support LOB data types on an OS/400 V5R1 host, you must edit the SWODBC.INI file, install LOB-related PTFs, and rebind packages. See the StarSQL for Windows User's Guide for details.

  • DB2 UDB for Linux, UNIX, and Windows v8.1 and later

If you will be accessing Unicode or ASCII LOB data on DB2 for OS/390 or z/OS host, be sure to apply the PTFs described in the IBM APAR PQ85495. Refer to the StarSQL Readme file for additional information about other PTFs or fixes that may be required for proper operations.


Network Requirements

The 64-bit version of the StarSQL ODBC driver works within a TCP/IP network. It also can be used in conjunction with the StarPipes gateway, to connect a Windows x64 client to a DB2 host using SNA.

The 32-bit version of the StarSQL ODBC driver can use any of the following networks to connect to the host.

  • TCP/IP

  • Microsoft SNA Server v2.1 or later, or Microsoft Host Integration 2000 Server and later

  • NetManage AS/400 Router

  • An SNA communications product that provides a 32-bit CPI-C interface

Note that future versions of StarSQL for Windows may provide direct support only for TCP/IP connections, requiring StarPipes to be configured for connections to an SNA network.


Configuring Data Sources

A data source describes a collection of data and the information needed to access the data. Typically a data source consists of a database and the network information needed to connect to the database.

You need to configure a data source for each collection of data that will be accessed using the StarSQL driver. You use the Windows ODBC Data Source Administrator to configure data sources. The ODBC Administrator is typically available from the Administrative Tools item of the Windows Control Panel.

If you are running Windows x64, there is a 32-bit ODBC Administrator (located in Windows\SysWOW64) and a 64-bit ODBC Administrator (located in Windows\System32). If you have both versions of StarSQL installed, the ODBC Administrator shortcut in the StarSQL (32-bit) program group starts the 32-bit ODBC Administrator and the shortcut in the StarSQL (64-bit) program group starts the 64-bit version of the ODBC Administrator.

Windows ODBC Data Source Administrator

When you choose StarSQL 32 or StarSQL (64-bit) as the driver for a new ODBC data source, or click Configure to modify a data source that uses the StarSQL driver, the StarSQL Data Source Wizard appears to help you quickly and easily configure the data source. Respond to each Wizard pane and click Next to advance to the next pane, clicking Finish to save the new data source. Click any item in the navigation tree on the left to modify a particular configuration setting.

Start pane of StarSQL Data Source Wizard


Required and Optional Data Source Settings

Required Data Source Settings

Some of the data source settings are required to connect to the host database from your computer. The required settings are:

  • Data Source Name

  • Database Server Name

  • Network Information

To test the DSN connection you also must specify a valid user ID and password for logging into the DB2 host. Other data source settings are used to tune and customize the data source, but typically are not necessary for establishing a connection. When a successful connection is made, the driver gets many of the remaining settings from the host.


Navigating the Data Source Setup Wizard

Use the Data Source Setup Wizard to set and modify your data source settings.

There are several ways to navigate through the Wizard:

Navigation Option | Description
Next/Back Buttons | Click the Next button at the bottom of the panel to move to the next group of settings. Click the Back button to return to a previous setting.
Tab/Enter Keys | Press the Tab key to move to the next field on a panel. Press the Enter key to move to the next group of settings.
Navigation Panel | Click an item in the navigation pane on the left to display the settings that correspond to that item.
Expert Settings | If you know precisely which setting you wish to modify, you can work directly from the Expert Page. Display the Expert Page using one of the preceding methods. Then, from the Expert Page, select the setting that you wish to modify from the Data Source Entry list.

When you are finished configuring data source settings, click the Summary button to show a summary of the configuration. From the Summary page you can click OK to save the data source configuration.


Connecting to a Data Source

When you connect to a StarSQL data source, a Connect dialog appears so you can enter the user ID and password required for connecting to the DB2 database. The User ID and Password fields can each contain up to 64 characters, although the maximum may be further limited by the host you are connecting to. The data source can be configured to cache the user ID for subsequent connection requests (see CacheUID).

If you do not have an account set up on the host system, contact your System or Database Administrator. If you have an account for accessing the host system, follow the steps below to connect to the data source.

  1. In the User ID field, enter your username or authorization identifier on the host DB2 database.

  2. In the Password field, enter your password on the host DB2 database. If you want to be able to connect without having to enter your User ID and password, see Storing the User ID and Password.

  3. To connect to the data source, click Connect. The required settings for the data source name, the database server, and the network must be properly configured for the connection to succeed. If you want to configure StarSQL data source settings or change your host password, click Options.


Importing a Data Source

Sharing Data Source Definitions Among StarSQL Users

After you create one or more data sources, you can use the DSIMPORT.EXE program that is included with StarSQL to replicate data source definitions to computers on which StarSQL is installed.

Important Information Windows x64 Users: Be sure to use the appropriate version of DSIMPORT.EXE to import data sources. Use the 32-bit version of DSIMPORT.EXE, which is located in the C:\Program Files (x86)\StarQuest\StarSQL, to import 32-bit data sources, and the 64-bit version of DSIMPORT.EXE, located in the C:\Program Files\StarQuest\StarSQL, to import 32-bit or 64-bit data sources. The format of the DSIMPORT.TXT file is the same for DSN definitions exported from either a 32-bit or 64-bit environment and can be used with either the 32-bit or 64-bit version of DSIMPORT.EXE.

  1. After you create one or more data source definitions (see Configuring Data Sources), use REGEDIT or another registry editor to access the following ODBC.INI object in the Windows registry. On a 64-bit computer the 64-bit data sources are stored in the standard ODBC object and 32-bit data sources are stored under a mirror ODBC object in the Wow6432Node, as detailed below.

For User data sources (32- and 64-bit):

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

For System data sources:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

or, for 32-bit data sources on a 64-bit computer:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI.

  2. Select the desired ODBC.INI object or specific data source object from the registry, choose File–>Export.

  3. Name the text file DSIMPORT.TXT and be sure the file type is “Save as type Win9x/NT4 Registration Files (REGEDIT4)”. Click Save to export the object.

  4. Move the customized DSIMPORT.TXT, and the appropriate version (32-bit or 64-bit) of the DSIMPORT.EXE file to a shared network directory that all StarSQL users can access.

  5. Double-click the DSIMPORT.EXE file to run the utility and import the StarSQL DSN definitions from the DSIMPORT.TXT file to the registry. The data source entries will appear in either the standard ODBC object or the mirrored Wow6432Node ODBC object, depending on whether you execute the 32-bit or 64-bit version of DSIMPORT.EXE.

Note that data sources are imported from the DSIMPORT.TXT file only when performing a fresh installation of StarSQL on a computer, not during an update of the StarSQL software.


Configuring StarSQL to Match and Record SQL Statements

Configuring StarSQL to Match and
Record SQL Statements

Static SQL packages allow StarSQL users to execute pre-set queries rather than passing dynamic SQL from their local computer to the host. When you use the StarSQL driver to record SQL statements, it saves the statements in a StarSQL package file. When you enable the StarSQL driver to match SQL statements, it matches the SQL statements that are issued with the statements in the StarSQL package file and uses the static SQL on the host database whenever it finds a match.

You can create a StarSQL package file using either of the following methods:

  • Use the StarScribe Package Editor to build the SQL statements and bind them into a StarSQL package file, as described in the StarScribe Package Editor help system.

  • Enable the Recording option of the StarSQL driver to record the SQL statements that are run from an ODBC-enable application.

This topic describes how to use the StarSQL driver to record the SQL statements, which usually is the most efficient way to record the statements. It also describes how to configure the StarSQL driver to match SQL statements to the package file.

If the data source is not already defined, follow the procedure in Configuring Data Sources to define the data source and your network connection.

Recording SQL Statements

To record (capture) your SQL queries as you run them from an ODBC-enabled application, you enable the SQL Recording option for the StarSQL driver.

  1. Select the ODBC data source for which you want to record the SQL queries and click Configure to display the StarSQL Data Source Wizard.

  2. Click the SQL Options page of the Wizard and select Recording from the Static Option drop-down box.

    SQL Options page of StarSQL Data Source Wizard

  3. Select which type of statements you want to record: only those statements with parameter markers, only SELECT statements, and/or whether you want to include DDL statements such as CreateTable and DropTable.

  4. To create a new package file, enter a name in the Static Package File text box. If you want to add the recorded statements to an existing file, enter the name of the existing package file or click Browse to locate the file.

  5. Click the Summary button and then OK to save the data source configuration.

  6. From within an ODBC-enabled application, connect to the host database and execute the desired SQL statements. The StarSQL driver records your statements to the StarSQL package file as they are executed.

  7. After recording your statements, return to the SQL Options page of the Data Source Wizard and change the Static Option to Disable or, if you are ready to use the package file, to Matching.

  8. If you appended new statements to an existing StarSQL package file, you must bind them using the StarScribe Package Editor before you can execute them as static SQL. If you created a new package file, it is bound on the host automatically.

Matching SQL Statements

After you record the desired SQL statements to a package file, you can enable the StarSQL driver to match statements that are executed from client computers to statements that are in the StarSQL package file. When a SQL statement matches one in the package file, the statement is run as static SQL from the host database instead of as dynamic SQL from the client computer.

  1. Select the ODBC data source for which you want to match SQL queries and click Configure to display the StarSQL Data Source Wizard.

  2. Click the SQL Options page of the Wizard and select Matching for the Static Option.

  3. Enter the name of the Static Package File that contains the statements to which you want to match, or click Browse to locate the appropriate file.

  4. Click the Summary button and then OK to save the data source configuration.

When an ODBC-enabled application connects to the host using a StarSQL data source that has Matching enabled, the StarSQL driver matches the application queries with the statements in the package file. If it finds a match, it runs the pre-compiled SQL statements on the host database instead of passing the statements from the client as dynamic SQL.


Configure the Data Source Name

Configuring the Data Source Name

The data source Name field is required. You can enter up to 32 characters for the data source name.

Cautionary Information

Do not use parenthesis in the data source name. The ODBC driver manager cannot resolve names with parentheses included and the DSN will be unusable.

The Data Source Description field is optional. If desired you can enter approximately 1KB of text that describes the data source.


Configure the Database Server Name

Configuring the Database Server Name

In the Database Server Name field, enter the name of the database server that will be accessed through this data source. You may need to obtain the database server name from the Database Administrator.

On DB2 for OS/390, the database server name is the Location Name in the Distributed Data Facility (DDF). You can get this by examining the DDF Communication record on the host.

On DB2/400, the database server name is the RDB or Relational Database Name. To find or create the relational database name, use the AS/400 command WRKRDBIRE on the host.

On DB2 UDB for Linux, UNIX, and Windows, the database server name is the Database Name.

On DB2 for VSE & VM the database server name is the Global Resource Name.

In the Package Collection Name field, enter the location on the DB2 host of the packages required by StarSQL to execute Dynamic SQL. The package collection name is called the Package Collection ID on some hosts.

On DB2/400, this is the name of the library that contains the packages.

On DB2 for OS/390 and DB2 for VSE & VM, the Package Collection Name is the name of the virtual collection associated with these packages. Set the Package Collection Name to SQLDBA for a DB2 for VSE & VM host.


Choose the Network Option

From the Network Options drop-down menu, select the type of connection that you will use to connect to the database.

You need to configure the appropriate information depending on the network option you select. The following network options are available:

  • TCP/IP

  • Microsoft SNA Server

  • CPI-C Interface

  • NetManage AS/400 Router


Configure a TCP IP Connection

Network Information for a TCP/IP Connection

Choosing TCP/IP for the Network option causes the Wizard to prompt for the following information:

TCP/IP Network Options

In the Host Name field, enter either a TCP/IP host name or an IP address in dotted decimal notation. The host name refers to the TCP/IP server, which is the same as the host name of the database server. Dotted decimal notation is four decimal fields separated by periods, in which each decimal field may be between 0 and 255, for example "198.147.235.1".

In the Port Number field, enter the number of the port that DB2 is listening on for DRDA communications. The default DRDA port is 446.

Click the Test Connection button to verify that StarSQL can check out a license and connect to the host using the specified host name and port number.


Configure a Microsoft SNA Server Connection

Network Information for a Microsoft SNA Server Connection

Choosing Microsoft SNA Server for the Network option causes the Wizard to prompt for the following information:

SNA Network Options

In the Local LU (Logical Unit) field, specify the name of the local LU.

In the Remote LU field, specify the name of the remote LU. You may need to obtain the local and remote LU names from your System Administrator.

Specify the mode name in the Mode Name field. The mode contains SNA-specific session information: the highest synchronization level for conversations on the sessions, the class of service for the sessions, and the session routing and delay characteristics. LU6.2-type LUs use this information to carry on a session. Multiple LU pairs can share a mode.

On DB2 for OS/390, the mode name is the same as the logmode name in the logmode table entry. QPCSUPP is the default mode, which is typically used to connect to DB2/400. For DB2 for OS/390, the mode (DLOGMODE) is defined in or inherited by the Local LU as it is defined in VTAM.

If you are connecting to a database on a DB2 for VM & VSE system, check the Use Custom DRDA TP Name option and enter "SYSDBA" in the text field. If you are connecting to any other type of system, leave the Use Custom DRDA TP Name checkbox and field blank.

For more information about using Microsoft SNA Server with StarSQL, see Optimizing Microsoft SNA Server for StarSQL.


Configure a CPI-C Connection

Network Information for a CPI-C Connection

Choosing CPI-C Interface for the Network option causes the Wizard to prompt for a Symbolic Destination name.

CPI-C Interface Network Options

The Symbolic Destination Name is a field in the Side Information Record. The Side Information Record provides session-specific initialization information. It includes the local and remote LU names, local and remote program names, session mode name, security type, User ID, and password.


Configuring a NetManage ViewNow AS/400 Router

Network Information for a NetManage AS/400 Router

Choosing NetManage AS/400 Router for the Network option causes the Wizard to prompt for a Host System name.

The Host System name is the name of the AS/400 system that is defined in the NetManage router configuration.


Set the User ID and Password

Setting the User ID and Password

In the User ID field, enter your username or authorization identifier for connecting to the database. The user account information is used only to test the connection to the host if the Connect to Host Database option is enabled; it is not stored in the DSN. The user ID and password fields can contain up to 64 characters each, although the maximum may be further limited by the host system.

In the Password field, enter your password for connecting to the database.

If you want StarSQL to automatically configure a data source based on the host environment, enable the Connect to Host Database to Obtain Default Configuration Values option.

If the Network option is configured to use Microsoft SNA Server, a checkbox appears for enabling the Microsoft Host Security Single Sign-On feature. This feature allows you to connect to the database without supplying a username and password. If this option is checked, the user ID and password fields are disabled. If you want to require a username and password to connect to the host, clear the Microsoft Host Security Single Sing-On checkbox.

Click the Advanced button to configure the optional data source settings.

Click the Summary button to see a summary of the current configuration. All of the keywords shown in the summary are described in the Expert Settings Overview.


Change Password

Changing a Password

If the host is set up to support password management, as described in the StarSQL User's Guide, you can change your password when you use the StarSQL driver to connect to a data source. Note that the new password is sent in clear text unless the data source is configured to encrypt it (see UseEncryption setting) and the host is running MVS v7 or later.

To change your password when connecting to a data source, follow the steps below.

  1. Click the Options button of the Connect dialog.

    Connect dialog

  2. In the StarSQL Data Source Wizard dialog, select Change Password from the navigation tree.

    Change Password dialog

  3. If it does not already appear, enter your current password in the Password field.

  4. Enter a new password in the New Password field.

  5. Enter the new password again in the Verify New Password field.

  6. Click OK to close the StarSQL Data Source Wizard dialog and return to the Connect dialog.

  7. In the Connect dialog, enter your old password in the Password field and click the Connect button. After you connect to the DB2 data source using the old password, StarSQL sends the new password to the host. Use the new password the next time you connect to the data source.


Configure the DBMS Features

Configuring the DBMS Features

From the Catalog Schema drop-down box of the DBMS Features page, select the catalog schema for the DB2 host. The catalog schema indicates the location of the DB2 catalog tables that StarSQL uses when an application calls an ODBC catalog function.

The default catalog schemas are:

DB2 Host | Schema
DB2 for OS/390 and z/OS | SYSIBM
DB2/400 (v3r2 and later) | QSYS2
DB2 UDB for Linux, UNIX, and Windows | SYSCAT
DB2 for VSE & VM | SYSTEM

For the Catalog Filters you can select one or more (up to ten) qualifiers to restrict the amount of data retrieved by ODBC. This limits what data the catalog function retrieves in the specified libraries or collections.

If you edit the catalog filters list, click Refresh to see the updated catalog filters list from the host. To connect to the host to refresh the catalog filter list there must be a valid user ID and password specified on the User ID/Password page.

The Bind Options setting appears only for applications that use Dynamic SQL on a data source on DB2 for OS/390 v5.1 or later. Set the DYNAMICRULES option to RUN or BIND as desired. See Bind Rules for more information about the differences between the BIND and RUN options.

Click the Next button to configure SQL Options.

Click the Summary button to see a summary of the current configuration.


Configure SQL Options

Configuring SQL Options

In the Default SQL Qualifier field, enter the value that you want to qualify all unqualified SQL statements. On DB2/400 this qualifier refers to an AS/400 library. On other hosts this qualifier refers to an Owner, Authorization Identifier, or Schema.

In the Create Table Directive field, enter any additional text that you want to append to SQL create table statements. This will indicate where you want the table to be created. For example, if you enter "in database DSNTEST" in the Create Table Directive field and the application contains the following statement

create table test (num integer)

the statement is passed to DB2 as

create table test (num integer) in database DSNTEST

See Table Creation Directives for information on the directive syntax supported for the various DB2 platforms.

From the Static Option drop-down box, select Disabled, Matching, or Recording. If you select Matching, also specify the static package file name. If you select Recording, also specify which statements you want to record. These options are further described in the following table. The topic Configuring StarSQL to Match and Record SQL Statements provides step-by-step procedures for recording and matching SQL statements.

Disabled

This option disables both matching and recording of SQL statements.

If you do not want the StarSQL driver to either match statements against those in the StarSQL package file the next time SQL queries are run, or to record SQL statements as they are run from the application, select Disabled for the Static Option of the SQL Options pane.

Matching

Enables matching of Static SQL statements. Enter the name of the package file to match to in the Static Package File field.

If a valid package file name is configured, StarSQL matches SQL statements in the application with SQL statements in the static SQL package on the host. It then executes those statements when it finds a match.

For information about Static SQL packages, see the StarSQL User's Guide and the online help for the StarScribe Package Editor.

Recording

Enables recording of Static SQL statements. Enter the name of the package file to record to in the Static Package File field. The package file is used to bind Static SQL packages on the DB2 host.

If all the checkboxes are clear and recording is enabled, StarSQL records all SQL statements executed by the application except for DDL statements. If you want to record DLL statements, check Include DDL Statements. Under normal circumstances you would not want to include statements such as CreateTable and DropTable, which would be recorded with the DDL Statements option.

To record only SQL statements with parameter markers, check Statements With Parameter Markers Only. Statements with parameter markers are more likely to be re-usable.

To record only SQL statements that are SELECT statements, check the SELECT Statements Only option. If you are creating pre-defined queries that are the only statements you want users to run, recording only the SELECT statements can produce a more manageable package file.

Click the Next button to configure Cursor Options.

Click the Summary button to see a summary of the current configuration.


Configure Cursor Options

Configuring Cursor Options

From the Held Cursor Setting drop-down list, select Yes, No, or <Default>. Selecting Yes enables held cursors. Selecting No disables held cursors.

The behavior of held cursors when the <Default> setting is selected depends on the host platform. On DB2 for OS/390, <Default> enables held cursors (equivalent of Yes). On DB2/400 and DB2/UDB, <Default> disables held cursors (equivalent of No). On platforms that do not support held cursors, this setting is ignored. For more information, see the Held Cursors Overview.

From the Default Isolation Level drop-down list, choose a specific isolation level, None, or <Default>. The default behavior depends on host platform. The default for DB2/400 is None. For all other platforms the default is Read Committed. The topic Isolation Levels describes the choices in more detail.

In the Maximum Result Set Rows field, enter the maximum number of rows that can be returned by a result set. Set this value to limit the number of rows returned in queries that do not call SQLSetStmtOption with the SQL_MAX_ROWS parameter. If you do not want to set an upper limit, enter zero or leave the field blank.

Click the Next button to configure Security or Accounting.

Click the Summary button if you want to see a summary of the current configuration.


Configure Security and Accounting

Configuring Security and Accounting

To help keep user login information secure, StarSQL can send the user ID and/or password to the host encrypted rather than in clear text. If you turn on the Enable User ID/Password Encryption option, select whether you want to always send the login information encrypted, or send it encrypted if the host supports encryption and in clear text if the host does not support encryption.

Check the Use Stored Procedure for Password Management checkbox if you want users to be able to invoke a stored procedure to change their host passwords from StarSQL. The stored procedure option is available on DB2 platforms that support it. If you are using a stored procedure for password management, you must enter the name of the stored procedure that you are using to enable host password changing.

In the Password Expiration Warning field, specify the number of days prior to the password expiration date that you want StarSQL to warn the user that their password will expire. The system will warn the user of pending password expiration from that day and each subsequent day until the password is changed or expires. If you do not specify a value in the Password Expiration Warning field, StarSQL provides no warning before the password expires. See Support for Changing Host Passwords for general information about this feature. See the StarSQL User's Guide for information about installing a stored procedure for password management.

In the Optional Accounting String field, you can enter the suffix of an accounting string that can be used to charge users for mainframe resources consumed when connected to DB2 databases through StarSQL. This option is available on DB2 for OS/390. See Accounting Strings for more detailed information.

Click the Next button to configure Two-Phase Commit.

Click the Summary button if you are finished configuring data source settings.


Two-Phase Commit

Enabling Two-Phase Commit

Two-phase commit protocol allows applications to coordinate units of work across multiple databases so that the units of work are committed as a single unit of work. Two-phase commit guarantees that either all or none of the participating databases are updated.

To use two-phase commit protocols, check the Enable Use of Two-phase Commit checkbox and then select whether the two-phase commit protocol will operate over TCP/IP (the TCP/IP DRDA option) or APPC Syncpoint (for Microsoft SNA Server or Host Integration 2000 Server).

Important Information

You must have the Microsoft Transaction Server (MTS) and Distributed Transaction Coordinator (DTC) components to use the two-phase commit protocol. These components are included in Windows 2000, Windows XP, and Windows Server 2003.


Expert Settings Overview

From the Expert panel, you can quickly access and set all of the data source settings that can be modified by a user. These include all of the settings that can be configured from the other panels in the wizard as well as those that are accessible only from the Expert Page.

To configure a setting from the Expert Page:

  1. From the Data Source Entry list box, select the name of the data source setting that you want to modify .

  2. In the Values list, either choose or type an appropriate value for the corresponding setting.

The following links offer specific information about each data source setting.

AccessFix1 | ExpirationWarning | PkgCollD
Accessible Tables | FetchAhead | PkgOwnID
Accounting | FoldUIDPWD | Port
AllowSynonyms | GetInfoCatalogUsage | QryBufSiz
AutoBind | HeldCursors | ReadOnly
AutoSqlSet | HostName | RemoteLU
AutoTypDefOvr | HostSec | RmtTPName
BinaryCCSID | HostSystemName | Server
BindRules | IncludeSynonyms | SpecialColumns
CacheUID | IsolationLevel | StrictParsing
Capture | KeepDynamic | SymDestName
Capture DDL | LiteralConversion | TimeStampAsChar
CaptureQryOnly | LocalLU | TwoPC
CatFilters | LongStrParams | TypeDefOvr
CatQual | MaxRows | UID
CharacterSubstitution | ModeName | UseDSCRDBTBL
CmdBufSiz | NMFix | UseDynamicCatalogSQL
CreateTable | OverrideCodeset | UseEncryption
CustomizePrdid | PackageFile | UseJumboPackages
DefaultQualifier | ParameterMarkersOnly | UseStaticMatch
Description | PasswordProc | UseSYSDUMMYAEU


AccessFix1

Some database management systems cannot handle the way Microsoft Access compares timestamp and date types. Set AccessFix1 to Yes to have the StarSQL driver treat timestamp types as date types.


Accessible Tables

Set the Accessible Tables variable to Yes if you are calling SQL_ACCESSIBLE_TABLES from Lotus Approach. Otherwise leave this variable set to the default value, No.


Accounting Strings

Accounting

OS/390 allows you to specify an accounting string for charging back the mainframe resources that particular users consume when connected to DB2 through StarSQL.

The Accounting variable can be set to a user-defined suffix of the full accounting record.

An accounting string contains a system-generated prefix and a user-generated suffix. Together these provide the information needed to associate resource usage with a user's access for charge-back accounting. The accounting string is passed in the PRDDTA parameter of the ACCRDB DRDA command. It is stored as an accounting record on the mainframe.

Accounting String Prefix

The prefix consists of 56 bytes and is generated by StarSQL. The fields are right-padded.

Field Name | Field Length | Description
acct_str_len | 1 byte | Length of the accounting string minus 1, in hexadecimal format
client_prdid | 8 bytes | Product ID of the driver, for example "StarSQL2"
client_platform | 18 bytes | Client OS, for example, "Windows"
client_appl_name | 20 bytes | Client application, for example, "mypgm"
client_authid | 8 bytes | Authorization ID of the StarSQL user
suffix_len | 1 byte | Length of the accounting string suffix, in hexadecimal format

Accounting String Suffix

The user-defined suffix further refines the accounting record. The user enters the suffix into the accounting string field in the data source setup wizard. In the following sample accounting string, the user-supplied suffix is "SALES":

x'3C'STARSQL2Windows mypgm BARNES x'05'SALES

The next sample shows an accounting string with a null suffix. A null suffix is passed if the user leaves the accounting string field in the data source setup blank.

x'3C'STARSQL2Windows mypgm BARNES x'00

For information about interpreting the accounting record on DB2, see the "Accounting for Distributed Data" section in IBM's DB2 for OS/390 V5 Reference for Remote DRDA Requesters and Servers and the DB2 for OS/390 V5 Administration Guide.


AllowSynonyms

The default behavior of StarSQL is to support SYNONYMS as input or output in catalog calls. If you enable or disable AllowSynonyms, the setting for IncludeSynonyms is forced to the same value, regardless of whether it was explicitly set.

When AllowSynonyms or IncludeSynonyms is set to Yes, synonyms are supported, as both input and output, in catalog calls.

To disable Synonyms support, set AllowSynonyms to No on the Expert Page of the Data Source Setup Wizard. When AllowSynonyms or IncludeSynonyms is disabled, synonyms are not returned in results and are not found when passed as input parameters.


AutoBind

The AutoBind option forces SQL packages to be bound at connect time.

Normally the use of AutoBind is not recommended. Because this option always causes binding, AutoBind can adversely affect connect time performance.

The AutoBind option settings are defined in the table below:

Value | Definition
0 | No auto-bind at connect time. The driver still binds packages dynamically if it does not find them on the server when it needs them.
1 | Binds up to three packages at connect time, but uses NO REPLACE option on bind. If the packages already exist they will not be replaced. In the current version of StarSQL, AutoBind=1 ignores the NO REPLACE option and functions in the same manner as AutoBind=2.
2 | Binds up to three packages at connect time and always replaces current packages, if there are any.
3 | Binds the Package File (for static SQL) only at connect time, always replacing the current package there is any.
N | Same as 0.
Y | Same as 3.

The three packages that may be bound by 1 and 2 are:

  • the dynamic package for the current transaction isolation level

  • the catalog package

  • the static package file

Regardless of the AutoBind setting, if the driver does not find packages on the host at the time they are needed, it will bind them dynamically.


AutoSqlSet

Some DBMS systems accept information that identifies the client connection---user ID, computer name, and application name. Set AutoSqlSet to No if the host does not support this functionality or you do not want to send this client connection information to the DBMS.

If StarSQL detects that the host supports the client connection information, it automatically sets the AutoSqlSet setting to Yes. Set AutoSqlSet to D (for "disabled") if you do not want StarSQL to change the AutoSqlSet setting to Yes for hosts that accept client connection information.


AutoTypDefOvr

The AutoTypDefOvr setting stores the Coded Character Set Identifiers (CCSIDs) that the DB2 host provides to StarSQL upon connection. After the connection with the host is established, StarSQL uses the CCSID values specified for AutoTypDefOvr to send SQL statement and parameter data to the host unless the TypDefOvr value specifies to use a different CCSID. The TypDefOvr values take precedence over values specified in the AutoTypDefOvr setting. However, if the AutoTypDefOvr CCSID values are appropriate, TypDefOvr values do not need to be sent with each DRDA request.

The AutoTypDefOvr keyword value consists of three comma-separated CCSIDs in the following format.

<single-byte CCSID>,<double-byte CCSID>,<mixed-byte CCSID>

For the complete list of supported CCSIDs, refer to the "StarSQL Character Conversion and National Language Support" document in the StarSQL technical documents of the StarQuest Web site. If the host returns a different CCSID than you want to use for the connection, or you want to override a multi-byte client code page with a MBCS CCSID, set the TypDefOvr to send a particular CCSID value with each DRDA request.


BinaryCCSID

If you are experiencing problems with character data being returned as binary data from DB2/400, try inserting the CCSID for your character set in the Binary CCSID field. This field instructs StarSQL to treat binary data as character data using the specified CCSID.

For the complete list of supported CCSIDs, refer to the "StarSQL Character Conversion and National Language Support" document in the StarSQL technical documents of the StarQuest Web site.


BindRules

The BindRules setting applies only to applications that use Dynamic SQL on a data source on DB2 for OS/390 v5.1 or later. Set this option to RUN or BIND as desired.

On DB2 for OS/390, the permissions required to run applications that use Dynamic SQL depend on the value of the Bind Rules option. If this option is set to RUN, the System Administrator needs to grant the user explicit permissions to read and write the columns and tables accessed by the application. If it is set to BIND, the StarSQL user executes with the permissions of the owner of the dynamic SQL package.

If the option is set to BIND, the following SQL statements cannot be executed, regardless of the actual permissions of the package owner:

  • SET CURRENT SQLID

  • GRANT

  • REVOKE

  • ALTER

  • CREATE

  • DROP

  • Any SQL statement that cannot be prepared as dynamic SQL


CacheUID

The CacheUID setting determines whether a UserID appears as a default in the login dialog. Set CacheUID to Yes if you want StarSQL to display the last successful UserID in the login dialog. Set CacheUID to No if you want the user to enter the UserID every time the login dialog appears.


Capture

Choose Yes to enable Static SQL recording to a package file. Choose No to disable Static SQL recording.

If you set Capture=Yes, you also must specify a value for the PackageFile keyword.

The Capture setting does not enable recording of DLL statements. To record DLL statements, use CaptureDDL.


CaptureDDL

Choose Yes to enable recording of Data Dictionary Language (DLL) statements in an application to a package file.

Choose No to disable recording of DLL statements.

If you set CaptureDDL=Yes, you also must specify a value for the PackageFile keyword.


CaptureQryOnly

Choose Yes to enable Static SQL recording only of SQL select statements to a package file. Choose No to if you do not want to restrict recording to only select statements.

To enable recording of all Static SQL in an application, except for DLL statments, use the Capture setting.

To record DDL statements, use CaptureDDL.

The PackageFile keyword also must be set if CaptureQryOnly is Yes.


Catalog Filters

CatFilter

The CatFilters keyword restricts (filters) the amount of data retrieved by ODBC catalog functions when a NULL or '%' is passed as the schema name.

To invoke the catalog functions, select a particular library or collection from the Catalog Filters list. This limits the data retrieved by catalog functions to data in the specified library or collection.

This feature is supported on the following platforms:

  • DB2 for OS/390 v5.1 and later

  • SQL/DS (DB2 for VM & VSE)

  • DB2/400 (OS/400) v3r2 and later

  • DB2/UDB v6.x and later


Catalog Qualifier

CatQualifier

The CatQualifier keyword indicates the location of the DB2 system tables that StarSQL uses when an application calls an ODBC catalog function.


CharacterSubstitution

If one or more characters cannot be converted between the host and client character sets, StarSQL substitutes a question mark, ?, in place of the character(s). The CharacterSubstitution parameter on the Expert Page settings determines whether a warning is generated when characters are replaced (parameter value of Warning), or the characters are substituted without generating a warning (parameter value of Silent).


Command Buffer Size

CmdBufSize

A large command buffer can improve performance for inserts and updates in which a single row contains more than 400 bytes of data.

Enter the size of the command buffer in the appropriate field. The buffer size can be between 512 bytes and 32KB.


CreateTable Directive

CreateTable

For the CreateTable option, enter any additional text that you want to append to SQL create table statements. This text indicates where you want the table to be created. For example, if you enter "in database DSNTEST" in the Values box for the CreateTable entry and the application contains the statement:

create table test (num integer)

the statement is passed to DB2 as

create table test (num integer) in database DSNTEST

See Table Creation Directives for information on the table creation directive syntax supported for the various DB2 platforms.


CustomizePrdid

The CustomizePrdid parameter customizes the behavior of a DB2 for z/OS host that is using a double-byte character set (DBCS). DB2 for z/OS does not support the ESCAPE clause in SQL syntax when the LIKE argument uses a mixed-byte character set.

When the CustomizePrdid parameter is set to Mixed, StarSQL will strip the backslash escape character (\) from both the catalog SQL that is used to bind catalog packages and from dynamic catalog SQL calls. You must enable the CustomizePrdid parameter prior to binding packages, and it must be enabled in the DSN that is used to connect to the host from the StarSQL client.

The escape character precedes a special character, such as the underscore (_) character, to treat the character as a literal. To work with tables that include an underscore in the name, such as MY_TABLE, create an ALIAS, SYNONYM, or VIEW for the table with a name that includes no special characters instead of working directly with the table.


DefaultQualifier

For the DefaultQualifier variable you can enter a value that you want to use to qualify all unqualified SQL statements. For example, if you set the DefaultQualifier to MYQUAL, the SQL statement "SELECT * FROM MYTABLE" would be converted to "SELECT * FROM MYQUAL.MYTABLE"

On DB2/400 this qualifier refers to an AS/400 library. On all other platforms it refers to an Owner or Authorization Identifier.


Description

For the Description option you can enter a textual description of the data source, such as ACCOUNTS, SALES, or WORKGROUP. This setting is optional.


ExpirationWarning

The password expiration warning (ExpirationWarning) only applies when using a stored procedure for password management. If you are using this method, you can enter an integer to indicate the number of days prior to password expiration that the user will be warned.

Important Note

The user must have his or her password set to expire on the host for this feature to function correctly.


FetchAhead

StarSQL implements a Fetch Ahead mechanism to retrieve data from the host in anticipation of the client application requests. Data is fetched from the host, stored in the driver, and returned to the application on request. For receiving large result sets from the database, enabling the FetchAhead option can help optimize the network connection and improve performance. Performance with smaller result sets is unaffected by the FetchAhead option.

You can enable or disable the FetchAhead option when you set up a data source using the Data Source Setup Wizard. For TCP/IP connections, the default for FetchAhead is Yes (enabled). For all other network protocols, the default for FetchAhead is No (not enabled).


FoldUIDPWD

Set FoldUIDPWD to Yes if you want StarSQL to force the UserID and Password to uppercase letters if the user is connecting using Microsoft SNA. When FoldUIDPWD is set to No, user IDs and passwords are sent in the case they were entered.


GetInfoCatalogUsage

Applications can call SQLGetInfo with the SQL_CATALOG_USAGE option to determine the classes of SQL statements in which catalog and schema names can be used to qualify objects of SQL statements. Setting the GetInfoCatalogUsage parameter of the StarSQL driver allows you to customize the bitmask that is returned by SQLGetInfo() for SQL_CATALOG_USAGE.

Set GetInfoCatalogUsage to 0 to force StarSQL to report that it does not support three-part naming (no Catalog). This may be necessary in some situations, such as when using StarSQL to perform queries to a SQL Server linked server. If GetInfoCatalogUsage contains an empty string or -1, the normal bitmask is returned.


Held Cursors

From the Values drop-down menu, select Yes, No, or <Default>. Setting the option to Yes enables held cursors. Setting it to No disables held cursors.

The behavior of held cursors when the <Default> setting is selected depends on the host platform:

  • On DB2 for OS/390, <Default> enables held cursors (equivalent of Yes ).

  • On DB2/400 and DB2/UDB, <Default> disables held cursors (equivalent of No).

  • On platforms that do not support held cursors, this setting is ignored.

If you are upgrading from a previous version of StarSQL, you may need to use StarAdmin to delete and rebind the SQL catalog package, after changing the held cursors setting.

See Held Cursors Overview for more information about this option.


HostName

The host name refers to the TCP/IP server, which is the same as the host name of the database server.

For the HostName variable you can enter either a TCP/IP host name or an IP address in dotted decimal notation. Dotted decimal notation is four decimal fields separated by periods, in which each decimal field may be between 0 and 255, for example "198.147.235.1".


Host Security

HostSec

The Host Security (HostSec) setting applies only to data sources that use Microsoft SNA Server or Host Integration 2000 Server to connect to DB2.

If you want SNA Server to use Microsoft Host Security Integration, choose Yes. This allows the user to connect to the database without supplying a username and password.

If you want the user to supply a user ID and password to connect, choose No.


HostSystemName

The HostSystemName variable applies only to data sources that use a NetManage AS/400 Router to connect to DB2.

The Host System Name is the name of the AS/400 system that is defined in the NetManage router configuration.


IncludeSynonyms

When you create a data source with v5 or later of StarSQL, the IncludeSynonyms support is enabled by default. Prior versions of StarSQL disabled IncludeSynonyms by default. For any StarSQL data source you can change the setting for the IncludeSynonyms support from the Expert Page of the Data Source Setup Wizard. If you enable or disable IncludeSynonyms, the setting for AllowSynonyms is forced to the same value, regardless of whether it was explicitly set.

When IncludeSynonyms is set to Yes, synonyms are supported, as both input and output, in catalog calls.

To disable Synonym support, set IncludeSynonyms to No on the Expert Page of the Data Source Setup Wizard. When AllowSynonyms or IncludeSynonyms is disabled, synonyms are not returned in results and are not found when passed as input parameters.

Enabling the SpecialColumns and IncludeSynonyms settings can enhance the use of StarSQL with some ODBC-enabled applications, such as Microsoft Query, as they can help the application with operations such as identifying join columns. These settings also can affect performance, depending on the applications and data involved, so you may need to experiment with the settings to determine which provide the best performance in your environment.


Isolation Levels

IsolationLevel

IsolationLevel refers to the degree of concurrency allowed when multiple transactions try to access the same data. In the data source, the isolation level is represented by the number shown after the isolation level name in parentheses. For example, if the currently-configured isolation level is for Repeatable Read, the data source summary will show "IsolationLevel=4".

Below is a list of isolation levels that you can set for DB2.

Isolation Level | Description
Repeatable Read (4) | Ensures that any row read during a unit of work can not be changed by other application processes until the unit of work has completed. Also ensures that any row changed by another application process cannot be read until it has been committed. At this level, a process is completely isolated from the effects of concurrent application processes.
Serializable (8) | Ensures that any row changed by another application process cannot be read until it is committed by that application process. Ensures only that the current row of every updateable cursor is not changed by other application processes. Rows read during a unit of work can be changed by other application processes.
Read Committed (2) | Similar to Repeatable Read in that a unit of work cannot be changed by another process until it has completed and a row changed by another application process cannot be read until it has been committed. However, with Read Committed, an application process that executes the same query more than once may see newly committed rows inserted by another application process that were not present in the original read.
Read Uncommitted (1) | For a SELECT INTO, FETCH with a read-only cursor, subquery, or subselect used in an INSERT statement, allows any row read during the unit of work to be changed by other application processes and any row changed by another application process to be read even if the change has not been committed by that application process. For other operations, behaves like Read Committed.
None (0) | No isolation of concurrent application processes.
<Default> | The default is host platform-dependent. The default for DB2 UDB for iSeries is None so that the driver can access non-journaled files. For all other platforms the default is Read Committed.


KeepDynamic

KeepDynamic enables or disables optimization for prepared statements by tuning the KeepDynamic bind option (DB2 for OS/390 v5 and later and DB2 UDB v6 for Windows and UNIX). With the KeepDynamic option enabled, prepared SQL statements are valid even after a commit for DB2 for OS/390 v5 and later. Enabling the KeepDynamic option can help improve performance since the SQL statements do not need to be prepared again.

The default value for the KeepDynamic setting is Yes.


LiteralConversion

Set LiteralConversion to Yes to enable Literal Conversion. This causes StarSQL to substitute parameter markers for literals when doing static matching.


LocalLU

The LocalLU setting applies only to data sources that use Microsoft SNA Server or Host Integration 2000 Server to connect to DB2.

With the LocalLU variable selected, enter the name of the local LU (Logical Unit) that names the network access point that contains the necessary configuration information. The local LU is a component of an SNA network.

You may need to obtain the local LU name from your SNA System Administrator.


LongStrParams

StarSQL v5 added support for DB2 LOB (large object) data types. The LongStrParams setting provides backwards compatibility to use StarSQL v5 with hosts that do not support LOBs and applications that rely on the previous ODBC data types.

As shown in the following table, StarSQL v5 no longer differentiates DB2 long strings from short strings.

DB2 Data Type | StarSQL v4.1 ODBC Type | StarSQL v5 ODBC Type
VARCHAR | SQL_VARCHAR | SQL_VARCHAR
LONG VARCHAR | SQL_LONGVARCHAR | SQL_VARCHAR
VARGRAPHIC | SQL_VARCHAR | SQL_VARCHAR
LONG VARGRAPHIC | SQL_LONGVARCHAR | SQL_VARCHAR
VARCHAR FOR BIT DATA | SQL_VARBINARY | SQL_VARBINARY
LONG VARCHAR FOR BIT DATA | SQL_LONGVARBINARY | SQL_VARBINARY

The new data type mappings affect ODBC catalog query results. For example, SQLColumns for a DB2 LONG VARCHAR now returns ODBC type SQL_VARCHAR instead of SQL_LONGVARCHAR. The new mappings also affect parameters for SQL statements. If an application binds a parameter as SQL type SQL_LONGVARCHAR, it is sent as a DB2 CLOB instead of as a DB2 VARCHAR string.

When LongStrParams is set to Yes, then SQL_LONGVARCHAR is sent as VARCHAR instead of CLOB, and SQL_LONGVARBINARY is sent as VARCHAR FOR BIT DATA instead of BLOB. This LongStrParams setting does not affect the types returned for result set columns, or for types returned by catalog queries.

When StarSQL v5 connects to an older version of DB2 that does not support LOB data types, the LongStrParams data source setting is forced to Yes and SQL_LONGVARCHAR and SQL_LONGVARBINARY parameters are sent as strings. Catalog queries, however, will not return SQL_LONGVARCHAR or SQL_LONGVARBINARY for long string types.


Maximum Rows

MaxRows

For the MaxRows variable you can specify the maximum number of rows that can be returned by a result set. Set this value to limit the number of rows returned in queries that do not call SQLSetStmtOption with the SQL_MAX_ROWS parameter.

If you do not want to set an upper limit, enter 0 (zero) or leave the Values field blank.


ModeName

The ModeName variable applies only to data sources that use Microsoft SNA Server or Host Integration 2000 Server to connect to DB2.

Enter a value for the mode name. The mode contains SNA-specific session information such as:

  • The highest synchronization level for conversations on the sessions

  • The class of service for the sessions

  • The session routing and delay characteristics

LU6.2-type LUs use this information to carry on a session. Multiple LU pairs can share a mode.

On DB2 for OS/390, the mode name is the same as the logmode name in the logmode table entry.


NMFix

The NMFix setting can help improve performance when you use Microsoft Access with a NetManager router to connect to a database. Set NMFix to Yes to prevent the driver from establishing more than one connection when connecting through a NetManager router with Microsoft Access.


OverrideCodeset

The OverrideCodeset parameter allows you to override the name of the local codeset that StarSQL uses to convert outbound character data. The codeset names are defined in the CCSID.CSV conversion table that is installed with StarSQL.

StarSQL obtains the locale (Linux, UNIX) or code page (Windows) that is set on the client computer and performs a lookup of the corresponding codeset name in the CCSID.CSV table to find the corresponding CCSID value to use. For example, if the code page for a Windows client is set to 1252, StarSQL looks for a codeset name of “WINDOWS-1252” in the CCSID.CSV file.

The OverrideCodeset parameter can be useful if you want to send data encoded using a different codeset, or to match a local codeset name that is not in the CCSID.CSV table to one that is. The value for the OverrideCodeset parameter can be any string.


PackageFile

If you want to enable matching or recording for Static SQL, select the PackageFile variable and enter the package file name in the Values field. The package file is used to bind Static SQL packages on the DB2 host.

If recording is enabled, StarSQL records Static SQL, optionally DDL statements, to the package file. To enable recording, set Capture, CaptureDDL, CaptureQryOnly or ParameterMarkersOnly.

If a valid package file name is entered and matching is enabled, StarSQL matches SQL statements in the application with SQL statements in the static SQL package on the host. It then executes those statements when it finds a match. To enable matching, set UseStaticMatch.

For information about Static SQL packages, see the StarSQL User's Guide and the online help for the StarScribe Package Editor.


ParameterMarkersOnly

Select the ParameterMarkersOnly variable and choose Yes to enable Static SQL recording only of SQL statements with parameter markers to a package file. Choose No if you do not want to record only SQL statements with parameter markers.

You also must set the PackageFile option if you set ParameterMarkersOnly to Yes.

To enable recording of all Static SQL, except DDL, use the Capture setting.


Password Procedure

PasswordProc

If you are using a stored procedure for password management, you must enter the name of the stored procedure as the value of the PasswordProc variable.

See Support for Changing Host Passwords for general information about this feature. See the StarSQL User's Guide for information about installing a stored procedure for password management.


Package Collection ID

PkgColID

The SQL Package Collection ID (PkgColID) indicates the location on the DB2 host of the packages required by StarSQL to execute Dynamic SQL. The default is STARSQL.

On DB2/400, the SQL Package Collection ID is the name of the collection or library that contains the packages.

On DB2 for OS/390 and DB2 for VM and VSE, the Package Collection ID is the name of the virtual collection associated with these packages.

You may need to obtain the Package Collection ID from your System or Database Administrator.


PkgOwnID

The SQL Package Owner ID (PkgOwnID) specifies the owner ID to associate with the StarSQL driver packages that reside on the host. The target DBMS host uses the PkgOwnID value to validate whether the user has authority to perform the functions represented by the SQL statements in the bound package. This option can be useful in situations where an administrator needs to bind the driver packages but does not want to be the owner of the packages.

The default package owner is the user ID that is used to establish the ODBC connection when a package is bound. To specify a different user ID as the package owner, select the PkgOwnID option in the Data Source Entry list and enter up to 8 characters in the Values text box. After you set the PkgOwnID option, run StarAdmin to bind the desired StarSQL packages with the package owner specified for PkgOwnID.

If you are using static SQL with the StarScribe Package Editor, note that specifying a PkgOwnID in the StarSQL driver configuration does not override the owner ID that is specified by StarScribe when binding custom SQL packages.


Port

The Port option specifies the IP port that DB2 is listening on for DRDA connections. The default port for DRDA communications is 446.


Query Buffer Size

QryBufSize

Increasing the size of the query buffer can improve performance for large queries. The buffer size can be between 512 bytes and 32KB.


ReadOnly

Set the ReadOnly variable to Yes if the data source is read only. This prevents users from attempting to update the host database. Choose No if you want the StarSQL user to be able to update the host database, to the extent allowed by the host security.

If you are using MS Access and you choose Yes, performance will be significantly reduced. Use MS Access's SNAPSHOT ONLY feature instead of the Star SQL Read Only setting to prevent updates.


RemoteLU

The RemoteLU variable applies only to data sources that use Microsoft SNA Server or Host Integration 2000 Server to connect to DB2.

With the RemoteLU variable selected, enter the name of the remote partner LU (Logical Unit) that is used by the transaction program on the IBM host where the remote database resides. The remote LU is a component of an SNA network.

You can obtain the remote LU name from your System Administrator.


Remote TP Name

RmtTPName

The Remote Transaction Program Name (RmtTPName) variable applies only to data sources that connect to hosts using SNA stacks that do not support service TP's.

The remote TP Name, also called the APPC Transaction Program Name, is the name of the remote transaction program that StarSQL uses to access the host database using APPC protocol.

You can obtain the remote TP name from your System Administrator.


Server

Enter the relational database name (RDB) or the location name, as it is configured on the remote system, of the database server that will be accessed through this data source.

You may need to obtain the database server name from the Database Administrator.

On DB2 for OS/390, the database server name is called the location name.

On DB2/400, the database server name is called the RDB or relational database name.

On DB2/UDB, the database server name is the relational database name.


SpecialColumns

When you create a data source with the current version of StarSQL, the ODBC SpecialColumns function is enabled by default. Prior versions of StarSQL disabled the SpecialColumns function by default. For any StarSQL data source you can change the setting of the SpecialColumns function from the Expert Page of the Data Source Setup Wizard.

Enabling the SpecialColumns and IncludeSynonyms settings can enhance the use of StarSQL with some ODBC-enabled applications, such as Microsoft Query, as they can help the application with operations such as identifying join columns. These settings also can affect performance, depending on the applications and data involved, so you may need to experiment with the settings to determine which provide the best performance in your environment.

When the SpecialColumns function is enabled (set to Yes), it retrieves the following information about columns in a specific table:

  • the optimal set of columns that uniquely identify a row

  • columns that are automatically updated when any value in the row is updated

When the SpecialColumns function is disabled (set to No), it returns an empty result set.


StrictParsing

Choose Yes to enable Strict Parsing, No to disable it.

When StrictParsing is enabled, the StarSQL driver parses the SQL sent by an application to ensure it meets SQL standards. If an application sends invalid SQL, StarSQL generates an error message and does not send the SQL. Leave the StrictParsing option enabled for normal operation.

If StrictParsing is disabled, StarSQL passes the SQL to the host database. However, some functionality, such as SQLDescribeParam, may not be available to the application.


Symbolic Destination Name

SymDestName

The Symbolic Destination Name (SymDestName) variable applies only to data sources that use a CPI-C connection to connect to DB2.

The Symbolic Destination Name is a field in the Side Information Record.

The Side Information Record provides session-specific initialization information, including:

  • the local and remote LU names

  • local and remote program names

  • session mode name

  • security type

  • User ID and password


TimeStampAsChar

If the TimeStampAsChar option is set to Yes, StarSQL converts timestamps to the char data type. This corrects a Microsoft Access problem with datetime precision loss and is needed only if you are using Microsoft Access. The default value is No.

The TimeStampAsChar keyword can be specified as a global setting (in .swodbc.ini) and for a specific DSN configuration (in .odbc.ini). If the keyword is specified in both and there is a conflict in the value, the DSN setting takes precedence.


Two-Phase

TwoPC

The two-phase commit protocol allows applications to coordinate units of work across multiple databases so that the units of work are committed as a single unit of work. Two-phase commit guarantees that either all or none of the participating databases are updated. You can disable the two-phase commit protocol, or enable the TwoPC variable for use in TCP/IP or APPC networks, as described further in the table below.

Value | Description
No | This means you are not using two-phase commit protocols.
TCP/IP | Select this value to use the two-phase commit protocol over TCP/IP.
APPC | Select this value to use the two-phase commit protocol over SNA.


TypeDefOvr

TypDefOvr

Use the TypDefOvr keyword to explicitly set the SBCS CCSID that StarSQL uses to send character data to the DB2 host. When TypDefOvr is set, StarSQL sends the data using the CCSID specified in the TypDefOvr with each DRDA request. The CCSID specified for TypDefOvr overrides the CCSID values specified for AutoTypDefOvr.

The TypDefOvr keyword value can be any SBCS CCSID that StarSQL supports. (For the complete list of supported CCSIDs, refer to the "StarSQL Character Conversion and National Language Support" document in the StarSQL technical documents of the StarQuest Web site.)

If the CCSID specified for TypDefOvr is for a single-byte character set, StarSQL sends all SQL statements and parameters only as single-byte character strings defined by that CCSID. The topic Customizing StarSQL for National Language Support provides additional information about customizing StarSQL to support specific client and host configurations.


UID

The user ID (UID) variable sets the StarSQL user name, or authorization ID, that is needed to establish a connection to the host database. The user ID can appear automatically if you enable the CacheUID setting.


UseDSCRDBTBL

If the UseDSCRDBTBL setting is Yes, StarSQL uses the DRDA DSCRDBTBL command to implement the SQLColumns ODBC function. Yes is the default. When UseDSCRDBTBL is set to Yes StarSQL does not treat an underscore ("low line") as a wildcard when using the DSCRDBTBL DRDA command. This can improve compatibility with some applications.

For hosts that do not support the DRDA DSCRDBTBL command, such as SQL/DS, DB2/UDB, or any host in a double-byte environment, set UseDSCRDBTBL to No. When UseDSCRDBTBL is set to No, StarSQL implements SQLColumns by preparing and executing a SELECT command. In addition, the underscore character is treated as a wildcard when UseDSCRDBTBL is set to No.


UseDynamicCatalogSQL

Set the UseDynamicCatalogSQL to Yes if you want the StarSQL driver to use dynamic rather than static SQL for catalog functions when connected to DB2/zOS servers. The default value is Yes, causing dynamic SQL to be used when it's available.


UseEncryption

The UseEncryption setting determines whether the login user ID and/or password is sent to the host encrypted or in clear text. When a user logs into the host or changes their host password using a StarSQL connection, StarSQL sends the user ID in clear text and the password encrypted unless the UseEncryption parameter is explicitly set to No or Yes (Any is the default value). To send the user ID and password encrypted, you must set the UseEncryption parameter to Yes and the host must support encryption.

DB2 for OS/390 v6.1 and z/OS v7 or later support or successfully negotiate password encryption. DB2/400 supports password encryption (with the Cryptographic Access Provider described in Host Requirements) but does not support encrypted user IDs, therefore the UseEncryption setting must be Any or No. DB2 for Linux, UNIX, and Windows supports encrypted passwords if you enable the database for encryption as described in the "Host Preparation" chapter of the StarSQL for Windows User's Guide. If the host does not support encrypted passwords you can set UseEncryption to No to send both the user ID and password in clear text.

Specify Any, Yes, and No for the UseEncryption setting, as described in the following table.

UseEncryption Setting | Description
No | StarSQL always sends the user ID and password to the host in clear text.
Yes | StarSQL always sends the user ID and password to the host encrypted. If the host does not support encryption, StarSQL returns an error.
Any (default) | StarSQL sends the user ID in clear text and the password encrypted. If the log fails, StarSQL then sends both the user ID and password in clear text.


UseJumboPackages

The UseJumboPackages variable affects:

  • how dynamic SQL packages are bound, and

  • how many SQL statements handles are available when using the StarSQL driver to connect to a DB2 host.

If you configure the StarSQL DSN to use jumbo packages (UseJumboPackages=Yes), StarSQL sets the maximum active statement handle limit to 1,314. Each handle can each be used by one ODBC statement at a time. If you set UseJumboPackages to No, StarSQL binds the packages with a maximum of 64 statement handles.

To take full advantage of this option you must enable the UseJumboPackages variable before you bind StarSQL packages, and it must be enabled in the StarSQL data source that is used to connect to the database.

If a dynamic package that is bound with 64 sections (UseJumboPackages=No) receives 65 or more active statements because the StarSQL data source is configured to use jumbo packages, DB2 may report an error that the sections cannot be found. If packages are bound with the jumbo option enabled (UseJumboPackages=Yes), users can connect to DB2 using a StarSQL data source that is configured to use either the typical (64 statement handles) or jumbo-sized (1,314 statement handles) packages.


UseStaticMatch

Choose Yes for the UseStaticMatch variable to enable matching of Static SQL in the package file with SQL statements in the static SQL package on the host and an to execute those statements when StarSQL finds a match.

Choose No to disable matching.

You also must set the PackageFile keyword if you specify Yes for UseStaticMatch.


UseSYSDUMMYAEU

The UseSYSDUMMYAEU parameter affects how LOB data is processed on a DB2 for z/OS host. The default setting of Yes indicates that the DB2 for z/OS system catalog contains the system tables SYSDUMMYA, SYSDUMMYE, and SYSDUMMYU, and StarSQL will use these tables instead of the standard SYSDUMMY1 table to avoid unnecessary conversion of LOB data that has a CCSID associated with it.

The SYSDUMMYA, SYSDUMMYE, and SYSDUMMYU tables are created by applying PTFs that IBM made available in APAR PQ85495. You can verify whether these tables exist on the host by entering the following statement:

SELECT * FROM SYSIBM.SYSTABLES WHERE NAME LIKE ‘SYSDUMMY%’

Note that only the SYSDUMMYA and SYSDUMMYE tables are created when applying the PTF to DB2 for z/OS v6—the SYSDUMMYU table is not created for that version. If your system has only the standard SYSDUMMY1 table and your database contains LOB data, the following error may be returned at runtime when users try to access LOB data:

SQLSTATE 42704 (-204) “[StarSQL][StarSQL ODBC Driver][DB2] IS AN UNDEFINED NAME”

To resolve this error you can:

  • set the UseSYSDUMMYAEU keyword to No, or

  • apply the PTFs from IBM APAR PQ85495 (http://www-1.ibm.com/support/docview.wss?uid=swg1PQ85495) to create the new SYSDUMMY tables.

If you set the UseSYSDUMMYAEU keyword to No, any double-byte EBCDIC or ASCII GRAPHIC data will be converted to UNICODE on the host before it is sent to StarSQL. If host performance is an issue, apply the PTFs and set UseSYSDUMMYAEU to Yes to avoid the unnecessary conversion.


Internationalization

National Language Support

StarSQL is a v3.0-compliant ANSI ODBC driver that provides connectivity to any database that is compliant with the Distributed Relational Database Architecture (DRDA). This includes all of IBM’s DB2 family of database products running on mainframe, midrange and workstation-based platforms. StarSQL allows connecting to DB2 from all modern Microsoft Windows and most popular UNIX- and Linux-based computers.

To support a wide range of host systems, StarSQL includes conversion support for character encoding based upon Unicode, IBM EBCDIC (including Extended EBCDIC) and ANSI (ASCII) standards. In addition, StarSQL supports legacy UNIX encoding, such as Extended UNIX Coding (EUC) and the International Standards Organization (ISO) encoding.

This section of the StarSQL help provides information about how StarSQL supports different languages and character encoding schemes. It is intended for any reader who is interested in character conversion, but is especially pertinent for StarSQL users who are:

  • running a client that is configured with a code page for one of the Group-2 languages (Japanese, Chinese, or Korean) that needs to connect to a host that does not support these languages.

  • accessing a DB2 z/OS system that is supporting one of the Group-2 languages using extended EBCDIC.

The topic Customizing StarSQL for National Language Support specifically addresses using StarSQL in the above situations. The topic StarSQL Character Conversion provides further details about how StarSQL converts character data.


StarSQL Character Conversion

StarSQL uses the IBM Coded Character Set Identifier (CCSID) value to identify client and host encoding schemes in a variety of ways, such as for the Expert Settings that modify the behavior of the driver. The CCSID is just a number that identifies a particular code page.

Character data in most Linux, UNIX, and Windows environments is represented only by a code page, which StarSQL treats as a CCSID. For example, the U.S. form of English using ANSI encoding is CCSID 1252.

The default behavior of the StarSQL driver typically involves the following steps when connecting to a DB2 host and exchanging SQL data.

  1. StarSQL connects to DB2 and obtains the CCSID values that the host expects for SBCS, DBCS, and MBCS data. These CCSID values are stored in the AutoTypDefOvr setting of the StarSQL data source for future use.

  2. StarSQL sends SQL statements and input data to DB2, converting SBCS data to the host-specified SBCS CCSID. Mixed-byte data is sent using the client code page, and the host is responsible for converting it to the host-specified CCSID for DBCS and MBCS data.

  3. DB2 converts the data to the host server’s code page, if necessary, and then processes the data.

  4. DB2 sends the result back to the StarSQL client.

  5. StarSQL converts the result to the code page of the client computer.

StarSQL performs inbound data conversion from the host system based upon the mappings that are defined in the ccsid.cvs table that is installed with StarSQL. The ccsid.csv table is platform-specific, and is installed to the \Programs directory of a Windows-based computer.

In order for StarSQL to convert data from one character set to another, there must be a mapping defined in the ccsid.csv table for all the CCSIDs used in the exchange. (For the complete list of supported CCSIDs, refer to the "StarSQL Character Conversion and National Language Support" document in the StarSQL technical documents of the StarQuest Web site.) If there is no mapping defined for a specified CCSID, an error is reported. You can request that additional character conversions be supported by contacting StarQuest Customer Support, and you also can customize the StarSQL data source to use a different CCSID as explained in the topics TypeDefOvr and Customizing StarSQL for National Language Support.

If one or more characters cannot be converted between the host and client character sets, StarSQL substitutes a question mark, ?, in place of the character(s). You can configure whether the substitution generates a warning by setting the CharacterSubstitution parameter of the StarSQL data source.


Determining and Setting the Client Code Page

StarSQL determines the local client’s code page by a variety of system calls—the value is normally returned as part of the “locale” API support.

On a Windows-based computer the system language is specified in the Regional and Language Options of the Control Panel. The code pages that determine how non-Unicode characters are converted are represented by a numeric string, such as 1252 for US-English code page. StarSQL converts the code page number to a string that the conversion routines can process, such as WINDOWS-1252.


Determining the Host Code Page

The code pages that are specified for the host to use are detected as part of the initial exchange between StarSQL and DB2. You can determine the CCSIDs that the host is returning to StarSQL by connecting to DB2 using a StarSQL data source from a Windows computer. Use the StarSQL Data Source Configuration Wizard, as described in the following steps, to see the CCSID values the host is returning to StarSQL.

  1. From the System DSN tab, select a StarSQL data source that connects to the host computer for which you want to determine the CCSIDs being used and click Configure.

  2. Select User ID/Password in the left pane of the StarSQL Data Source Wizard. In the right pane, enter a valid password and user ID for connecting to the host and click the Summary button.

  3. Scroll the summary page as necessary to see the values that appear for the AutoTypDefOvr setting.

The AutoTypDefOvr setting is three comma-separated values of the CCSID for SBCS, DBCS, and MBCS, respectively. For example, the AutoTypDefOvr value that the DB2/400 host returned to StarSQL after connecting with the DSN illustrated below is CCSID 37, with no CCSID value set for DBCS or MBCS.

finding the AutoTypDefOvr values returned from the host

To control the SBCS CCSID that StarSQL uses to send outbound data, you can modify the TypDefOvr value in the Expert Settings of the StarSQL data source as described in Customizing StarSQL for National Language Support.


Customizing StarSQL for National Language Support

This topic addresses situations that may require customizing StarSQL to support a specific client connecting to a specific DB2 host: StarSQL may need to be customized in order to:

  • establish a connection between a client that is configured with a code page for one of the Group 2 languages (Japanese, Chinese, or Korean) and a host that does not support these languages.

  • access a DB2 z/OS system that is supporting one of the Group 2 languages using extended EBCDIC.

Connecting an MBCS Client to an SBCS Host

IBM encoding of the MBCS support recognizes two distinct CCSIDs for SBCS and DBCS encoded characters. Typically only characters encoded using the SBCS component are used in SQL statements, while both DBCS and MBCS data can be the output of the SQL statements. If a host is not configured to support mixed-byte character sets it can receive only SBCS data.

Typically StarSQL sends SBCS data to the host with characters encoded according to the CCSID value that the host indicates it is using for SBCS data when it returns the AutoTypDefOvr values to StarSQL. You can specify a particular CCSID to use for converting SQL data by setting the TypDefOvr parameter of the data source that StarSQL uses to connect to the host.

The TypDefOvr setting overrides the SBCS CCSID value that is specified in the AutoTypDefOvr setting. When converting SQL data to a form the host can use, StarSQL will use the conversion routine that is associated with the CCSID in the TypDefOvr setting, regardless of the client computer’s character encoding scheme (SBCS or MBCS) or the AutoTypDefOvr values.

If a host reports to StarSQL that a CCSID being used by the client is not acceptable, useful connectivity may still be achieved by specifying an SBCS CCSID that is supported by the host, which restricts the SQL character data to only single-byte character strings. For example, when connecting a client computer that is using a Japanese character set to a US-based host that does not support Japanese mixed-byte character set, you can force a specific SBCS CCSID to be used by setting the TypDefOvr setting of the StarSQL data source.

Supporting Asian Languages with Extended EBCDIC

Some multi-byte host environments have restrictions regarding the type of SQL phrases which may be used. Specifically, hosts that are running DB2 for z/OS with support for Group-2 languages may not handle the default SQL statements used by StarSQL to fulfill ODBC catalog calls.

When using Extended EBCDIC client encoding, StarSQL packages must be bound with the CustomizePrdid parameter enabled for “Mixed.” Setting the CustomizPrdid parameter to M (Mixed) disables use of the backslash character, \., as an escape character in arguments passed to catalog functions. The escape character precedes a special character, such as the underscore (_) character, to treat the character as a literal. To work with tables that include an underscore in the name, such as MY_TABLE, create an ALIAS, SYNONYM, or VIEW for the table with a name that includes no special characters instead of working directly with the table.

StarSQL packages are normally bound using the StarAdmin utility that is included with the StarSQL for Windows. Be sure that the DSN has CustomizePrdid set to Mixed before binding packages with the StarAdmin utility. The ODBC DSN that StarSQL clients use to connect to the host also must be configured with CustomizePrdid set to Mixed to use the packages that are bound with CustomizePrdid set to Mixed.


Supported CCSIDs

StarSQL supports converting character data between a wide range of CCSID-to-CCSID pairs and CCSID-to-code-page pairs. It supports Group 1, Group 1A, and Group 2 character sets as defined by CDRA.

  • Group 1 covers the Roman Alphabet Number 1, which includes Australia, Hong Kong, New Zealand, North and South America, and Western Europe.

  • Group 1A covers multilingual scripts Cyrillic, Hebrew, Greek, and Turkish. The Latin 2 character set associated with Central Europe is supported in this group.

  • Group 2 covers double-byte coding for Japan, Korea, the People’s Republic of China, the Republic of China, and Thailand.

For the complete list of supported CCSIDs, refer to the "StarSQL Character Conversion and National Language Support" document in the StarSQL technical documents of the StarQuest Web site.


Optimizing Microsoft SNA Server for StarSQL

Some Microsoft SNA Server or Host Integration 2000 Server settings can be optimized to work better with StarSQL.

SNA Setting

Recommended Value

Max Send RU Size
and
Max Receive RU Size

These SNA settings correspond to entries in the RUSIZES mode table entries on DB2. The range for this value is between 256 and 16384. The default is 1024. The recommended value is 4096.

Max BTU Length

The range is 265 to 16393. The default for token ring is 1929 and for Ethernet 1493.

The recommended maximum BTU length depends on the type of connection, as outlined below.

Host connections | Specify a Max BTU Length less than or equal to VTAM PU definition for the MAXDATA=parameter.
Downstream connections | Specify a Max BTU Length less than or equal to maximum value supported by downstream system.
Token ring adapter at 4 Mbps | Specify a Max BTU Length less than or equal to 4195.
Token ring adapter at 16 Mbps | Specify 16393.
Ethernet adapter | Specify a Max BTU Length less than or equal to 1493.


Printable Glossary

Glossary

Below is a glossary of terms that are important to understand when working with ODBC applications, the StarSQL driver, and host databases.

Term

Definition

Accounting String

Part of an accounting record, which enables administrators to charge back individual users for use of mainframe resources.

APPC

Advance Program-to-Program Communications. A network protocol that contains SNA, but is often informally used as a synonym for SNA

Authorization Identifier

A string that can be verified for connection to DB2 and to which a set of privileges are allowed. It can represent an individual, an organizational group, or a function.

Catalog Function

An ODBC function that returns information from the data source's system tables or catalog.

Catalog Qualifier

Same as catalog schema.

Catalog Schema

Location of the DB2 system tables or catalog. Previously called the Catalog Qualifier.

CCSID

Coded Character Set Identifier. Represents a character set, code page, encoding scheme, and additional coding-related information. Used to support international code sets.

Collection

A location on the host for database objects, such as user tables and catalog tables, which are collected together under a single qualifying name.

Cursor

A SQL object that points to a row in a result set.

Data Source

Information needed to access data on a host database. Typically, database and the network information needed to connect to the database.

Database Server

Host platform specific. On DB2/MVS, the database server name is the location name in the Distributed Data Facility (DDF).On DB2/400, the database server name is the RDB or relational database name. On DB2/UDB, the database server name is the realtional database name.

DDL

(Data Dictionary Language) describes the collection of SQL statements that affect the data dictionary, such as those that create or remove databases, tables, or indices.

DRDA

Distributed Relational Database Architecture. Supports access to distributed DB2 data by which an application can explicitly connect to another location, using an SQL statement, to execute packages that have been previously bound at that location.

DSN

Data Source Name. An ODBC definition that refers to a particular database.

Dynamic SQL

SQL statements that are prepared and executed within an application program while the program is executing. A dynamic SQL statement can change during program execution.

Host Name

In a TCP/IP configuration, the name or IP address of the TCP/IP server.

Held Cursor

A cursor that is not automatically closed when its transaction commits.

Host Security Integration

A feature of Microsoft SNA Server and Host Integration 2000 Server, whereby security is delegated exclusively to the Microsoft Windows client and the user does not have to give a user ID and password to connect to the host.

Host System Name

The name of the host system. Required for configuring a NetManage ViewNow AS/400 Router connection.

Installable Image

An image of a StarSQL installation that can be installed on multiple desktops over the network.

Isolation Level

The degree of concurrency allowed when multiple transactions try to access the same data.

LU

Logical Unit. A component of an SNA connection. Each end of the connection requires an LU name.

Mode

Contains SNA session-specific information, such as the packet size (RU Size), which must be synchronized before an SNA session can be initiated.

ODBC

Open Database Connectivity. A call-level interface developed by Microsoft Corporation that allows a single application to access DBMSs from different vendors using a single interface.

Package

An object on the host containing a set of SQL statements that have been bound statically and are available for processing.

Package File

A file on the desktop that contains Static SQL. Used to bind Static SQL packages on the DB2 host.

Qualifier

The part of a table reference in a SQL statement that indicates the schema in which the table is located.

RDB Name

A unique identifier for an RDBMS within a network.

Regedit

Registry editor supplied by the Windows NT operating system for manually editing the NT Registry.

Result Set

The set of data rows returned by a query.

Side Information Record

Component of an CPI-C connection that includes session-specific information, such as remote LU, session mode name, and TP name.

SNA

Systems Network Architecture, a network protocol developed by IBM. See also APPC.

Static SQL

SQL statements in an application program that are created and bound to the host in a static package before the application executes. After being created, a static SQL statement does not change, although values of variables specified by the statement might change.

To execute Static SQL sent by an ODBC application, DB2 requires a Static SQL package to be bound on the DB2 host. Use the StarScribe Package Editor to bind a Static SQL package.

Stored Procedure

A set of one or more host commands that have been parsed and partially pre-processed on the host.

Symbolic Destination Name

A field in the Side Information Record. Required for configuring a CPI-C connection.

Table Creation Directive

A string appended to a SQL CREATE TABLE statement that indicates the schema or database in which the table is to be created.

TCP/IP

Transmission Control Protocol / Internet Protocol. A commonly used network protocol.

TP Name

APPC Transaction Program Name. Defines the name of the remote transaction program that the database client must use when it issues an allocate request to the database server using the APPC communication protocol.

Trace File

A file that records a history of events that occurred in the system.

Transaction

One or more SQL commands that are committed as a unit. Either the entire transaction or none of it is committed to the database.

Two Phase Commit

Protocol that allows applications to coordinate units of work across multiple databases so that the units of work are committed as a single unit of work.

UID

Unique identifier that enables a user to logon to a host.

VTAM

Virtual Telecommunications Access Method provides network communications on IBM mainframes and AS/400 systems.


Obtaining Licensing Information

The license key for using StarQuest products is based on which product you are licensing, the number of connections you need, and the particular computer on which the license will be installed. The easiest method for obtaining a license key is to use the online licensing feature of the License Configuration utility. (For details about online licensing, start the License Configuration utility and click the Help button.)

If you prefer to obtain a license key from StarQuest via an email request, you need to provide a static IP address or the Host ID of the computer on which you will install the product. If the computer is in a network that uses the Dynamic Host Configuration Protocol (DHCP), provide the Host ID instead of an IP address to StarQuest when you request the license. IP addresses that are assigned by the DHCP service are dynamic and can invalidate the license key when the computer is assigned a different unique IP address.

Displaying the IP Address

Each node in an IP network must be assigned a unique address, such as 172.19.36.72. The IP address can be static (permanently assigned to the computer) or dynamic (assigned by the DHCP service). If the computer is assigned an IP address by the DHCP service, the IP address can change and you should provide the Host ID instead of the IP address when you request the license from StarQuest.

  1. To display the IP Address of a computer, enter the command ipconfig at a Command Prompt.

A display similar to the following shows the IP address that is currently assigned to the computer.

Connection-specific DNS Suffix: company.com
IP Address....................: 172.19.36.72
Subnet Mask...................: 255.255.255.0
Default Gateway...............: 172.19.24.1

Refer to the documentation for your version of the operating system for more information about displaying a command prompt or using the ipconfig command.

Displaying the Host ID

The Host ID, or Disk ID, of a Windows computer identifies the physical disk in a particular computer. You can display the Host ID from the License Configuration utility by clicking Add from the Licenses tab. Or you can display the Host ID by opening a command window and entering the command label at the command prompt.

The label command displays information similar to the following, from which you provide the Volume Serial Number to StarQuest when you request the license key.

Volume in drive C: has no label
Volume Serial Number is C854-CE05
Volume label <ENTER for none>?


Held Cursors Overview

Normally a cursor is closed when its transaction commits. A transaction can involve one or more SQL commands that are committed as a unit---either the entire transaction or none of it is committed to the database. If the held cursors option is enabled, the cursor remains open after the commit. This enables an application to fetch rows from a result set, commit the transaction, and then continue fetching additional rows on the same result set.

Using held cursors, an application can commit immediately after opening the result set to allow locks normally needed to maintain a prepared statement to be freed early. If the application is in auto-commit mode, StarSQL automatically issues a commit at the time the result set is opened and at the time the result set is closed.

Held cursors are supported on DB2 for OS/390, on DB2/400, and on DB2/UDB.

An application that uses held cursors should turn SQL_AUTOCOMMIT_MODE off before executing a prepared statement multiple times. Otherwise, StarSQL prepares the SQL statement after each SQLExecute. This can potentially result in poor performance.


SNA Server Support for CCSID Group 1A

StarSQL supports SNALS API (Microsoft SNA Server 3.0 and later). SNANLS.DLL and appropriate tables (*.NLS) are installed when you install the SNA Server (3.0 or later) client.

StarSQL supports Hebrew and Arabic if the Microsoft SNA Server 3.0 or later client is installed (32-bit environments only).

If you are using the SNA Server 3.0 or 3.0 SP1 client on Windows NT, you must add the following entries to the Windows Registry. Use a registry editor, such as Regedit, to modify the Windows Registry.

Language | Registry Entry
Hebrew | Add a registry entry under HKEY_LOCAL_MACHINE: SYSTEM:CurrentControlSet:Control:Nls:CodePage where the string value name is 20424 and the value data is: "c_20424.nls"
Arabic | Add a registry entry under HKEY_LOCAL_MACHINE: SYSTEM:CurrentControlSet:Control:Nls:CodePage where the string value name is 20420 and the value data is: "c_20420.nls". You may need to make a similar registry entry on Windows 95. On Windows 95 the values are "cp_20424.nls" and "cp_20420.nls".


Storing the User ID and Password

If you do not want to enter a user ID and password every time you connect with StarSQL, edit the following lines in the ODBC.INI file on your PC in the section for the appropriate datasource:

UID=<your host user ID>

PWD=<your host password>


Support for Changing Host Passwords

On DB2 for OS/390 and DB2/400, you can change your password on the host when you connect from StarSQL. The password management feature is supported either by DRDA security flows or by installing a special stored procedure on your DB2 host. An advantage of the DRDA security flow method is that it enables a user to connect to the host with an expired password to change the password. You cannot change an expired password through StarSQL if the password changing feature is implemented by the stored procedure method.

Using the DRDA Security Flow Method

The DRDA security flow method is supported on DB2 for OS/390 (DB2/MVS 5.1) using TCP/IP connections. Refer to the StarSQL User's Guide for details about setting up the host to support password management.

Using the Stored Procedure Method

The stored procedure method is supported on DB2/400 v3r2 and later. The stored procedure included with StarSQL is called DRDAAS.DRDA11DE. For information about obtaining and setting up the stored procedure, see the StarSQL User's Guide. If you are using the stored procedure method, you must configure the data source to supply the name of the stored procedure used to support password management (see Configuring Security and Accounting).


Table Creation Directives

A Table Creation Directive field consists of text indicating where on a DB2 host a table should be created.

The following syntax is supported:

Platform | Syntax
DB2 for OS/390 | IN DATABASE DATABASE_NAME IN DATABASE_NAME . TABLESPACE
DB2/400 | IN NODEGROUP_NAME A nodegroup name is a qualified or unqualified name that designates a nodegroup. A nodegroup is a group of DB2/400's across which a table is distributed. For information about distributed tables and nodegroups, see the DB2 Multisystem for OS/400 book, SC41-3705.
DB2 for VM & VSE | IN DBSPACE
DB2/UDB | IN TABLESPACE_NAME A tablespace name is a long identifier that identifies a distinct table space described in the DB2 catalog.


The Conversion Table

The StarSQL Conversion Table

Type topic text here.