STARSQL Release 2.5.2 Release Notes May 1, 1998 Copyright 1996,1998 StarQuest Software, Inc. StarQuest Software, Inc. 1288 Ninth Street Berkeley, CA 94710 Phone: 510-528-2900 FAX: 510-528-2986 Internet: http://www.starquest.com ===================================================== StarSQL: The Affordable PC Connectivity Solution! ===================================================== StarSQL is a powerful ODBC driver that enables Windows and OS/2 applications on your PC workstation to transparently access, send SQL queries to, and retrieve results from databases on remote IBM mainframe, midrange, or Unix systems. Using your favorite Open Database Connectivity (ODBC)-enabled PC applications, your PC acts as a client to an existing SNA server or router which establishes the connection over SNA to access databases stored on the remote host. This allows you to open a database on the host as if the database were stored on your PC. This file contains important & last minute information about StarSQL. Be sure to read Late Breaking Notes for important information. For more information about StarSQL, read the StarSQL Help file. ======== CONTENTS ======== Host Connectivity Host Databases Late Breaking Notes Configuring StarSQL for DB2 Common Server - IMPORTANT NOTE Stored Procedures General Information DB2 MVS DB2 Common Server New Package Binding Behavior AutoBind Option for Data Sources AS/400 Pre-V3.10 Users Held Cursors Data Source Setup Limitations Change History Installation Notes Documentation addendum: Using StarSQL with ODBC Enabled Applications: Microsoft Access Lotus Approach (32 bit) SQLCancel() Enhancements SQLProcedures() Enhancements Language Support Enhancements If You Need Help ================= HOST CONNECTIVITY ================= StarSQL can use the following networks for host connectivity: * Microsoft SNA Server v2.11 with Service Pack 2 * Microsoft SNA Server v3.0 * Microsoft SNA Server v4.0 * TCP/IP (when installed with Microsoft Host Data Replicator) ============== HOST DATABASES ============== StarSQL can connect to any of the following host databases: * DB2 MVS (DB2 for OS/390) 2.3 and later * SQL/DS (DB2 for VM & VSE) v3.3 and later * DB2/400 (OS/400 v2r2 and later) * DB2 Common Server (Universal Database) for UNIX, OS/2 & Windows NT v2.1 and later =================== LATE BREAKING NOTES =================== Data Source Setup: ------------------ When configuring a data source, you will see a checkbox labeled "Microsoft Host Security - Single Sign-on". This checkbox is to enable StarSQL's use of a new feature of Microsoft SNA Server 3.0; if you are using SNA Server 2.11, this checkbox is not supported and should be left disabled. Configuring StarSQL for DB2 Common Server - IMPORTANT NOTE ---------------------------------------------------------- You must create the table DB2CLI.PROCEDURES before using StarSQL with DB2 Common Server (StarSQL 1.2 and later). You must do this whether or not you plan to use stored procedures. If this table has not been previously created, the catalog package bind will fail. For instructions on how to create this table, please see Setting Up DB2 Common Server for Use with StarSQL in the StarSQL Help file. Stored Procedures ----------------- General Information * SQLProcedures and SQLProcedureColumns are synchronous. * The ProcedureName used in CALL SQL statements must be uppercase. DB2 MVS * Before using SQLProcedures and SQLPRocduresColumns, DB2 MVS must be configured to use stored procedures. This includes setting up a DB2 stored procedure address space and creating a table called SYSIBM.SYSPROCEDURES. * Although the column in the list of parameters to the stored procedure specified in SYSIBM.SYSPROCEDURES.PARMLIST is defined as 3000 characters, StarSQL only reads the first 254 characters. Therefore the list of parameters must not exceed 254 characters. * The parameters specified in SYSIBM.SYSPROCEDURES.PARMLIST must include Procedure Name and IN/OUT/INOUT specifications. DB2 Common Server * You must create the table DB2CLI.PROCEDURES before using StarSQL with DB2 Common Server. You can use the sample DDL provided by IBM in the sqllib\misc\storproc.ddl file in order to do this. This is necessary because of a DB2 Common Server IBM bug that causes the catalog package bind to fail unless all tables referenced in the package exist on the server. Please see instructions on how to create the DB2CLI.PROCEDURES table in Setting Up DB2 Common Server for Use with StarSQL in the StarSQL Help file. * Although the column in the list of parameters to the stored procedure specified in DB2CLI.PROCEDURES.PARM_LIST is defined as 3000 characters, StarSQL only reads the first 254 characters. Therefore the list of parameters must not exceed 254 characters. * The parameters specified in DB2CLI.PROCEDURES.PARM_LIST must include Procedure Name and IN/OUT/INOUT specifications. New Package Binding Behavior ---------------------------- * If you are upgrading from StarSQL v1.x, you must drop the previously created SQL packages (using DROP PACKAGE) and re-bind new packages with StarSQL v2.x. * Beginning with StarSQL v1.2, SQL packages on the server are no longer bound automatically at connect time. Instead, StarSQL assumes that the required packages already exist on the server. This saves prepare time for catalog functions on subsequent connects and greatly enhances StarSQL's connect time performance. If the package StarSQL needs to use does not already exist on the server, it automatically binds the package "on the fly." To do this, StarSQL must have the proper Authorization Identifier (for DB2 MVS) or a valid user ID and password (for the AS/400). You must grant users this authority. If the bind is successful, the ODBC API call that caused the bind returns SQL_SUCCESS_WITH_INFO, indicating that a package was bound. Any warnings or errors reported by the server during the bind are reported for the ODBC API call that caused the package to be bound; warnings are reported as SQL_SUCCESS_WITH_INFO and errors are reported as SQL_ERROR. If the bind fails, the ODBC API call that caused the bind attempt returns SQL_ERROR. If you try to connect again using the same ODBC API call, StarSQL attempts to bind again. StarSQL always attempts to bind a missing package, even when the bind has previously failed. Autobind Option for Data Source ------------------------------- AutoBind causes SQL packages to be bound at connect time (SQLConnect or SQLDriverConnect). AutoBind may be set directly in either the ODBC.INI text file or in the ODBC.INI registry, or may be passed in the connect string to SQLDriverConnect. Please see Advanced Options in the StarSQL Help file for more information. AS/400 pre-v3r1 Users --------------------- * If you have not previously set the system value for CCSID, use the Advanced Options dialog (accessed through the Advanced Options radio button in the Data Sources Setup dialog), check Treat Binary Data as Character Data, and set the CCSID field to 37. Held Cursors ------------ * If you enable HeldCursors by checking it in the Advanced Options dialog, SQLFetch repeatedly returns NO_MORE_DATA when the cursor reaches the end of query data until you explicitly close the cursor with SQLFreeStmt(CLOSE). Data Sources Setup ------------------ * There is a new Driver Settings section in the Data Sources Setup dialog that provides the following new option: - Advanced Options - This option accesses the Advanced Options dialog, which allows you to add certain keywords to your StarSQL data sources. You still must specify some keywords manually. See the Advanced Options topic in the StarSQL Help file for more information. =========== LIMITATIONS =========== * There is a problem for OS/400 v3r1 where column names that have aliases longer than 18 characters are not returned correctly. The fix is to install PTF SF22104 for product 5763SS1 (APAR SA41485). * If you are using OS/400 v3r2, you must install PTF SF36813 for product 5763SS1. This fixes an OS/400 permission problem that causes SQLTables() to fail (all versions of StarSQL). * There is a DB2 MVS problem where secondary Authorization Identifiers are not recognized. You must install APAR PN 63305 to fix this problem. * Do not use parentheses in data source names; if you do so, you will have to manually edit ODBC.INI or the Registry in order to be able to delete the names. * When an application issues the SQLTables call to an AS/400, it returns only a single Owner-ID. * If the AS/400 system value QCCSID or the CCSID field of the user's profile is not the install default of 65535 (0xFFFF) - e.g. it has been changed to 37 - but a field in a table has a CCSID of 65535, StarSQL makes the assumption that it is dealing with an older table that was created when the system or user CCSID was 65535 and treats that field as character data rather than binary data. If you want a field with CCSID=65535 to be interpreted as binary data (for AS/400), set BinaryCCSID=0 in the data source. NOTE: You do not need to do this for DB2 MVS; fields with CCSID=65535 are interpreted as binary data unless you add the BinaryCCSID keyword with a non-zero value to the data source. See BinaryCCSID in Advanced Options for more information. StarSQL binds the Catalog Query SQL to a catalog package at connect time to save prepare time on subsequent use. This action results in a delay and high CPU utilization on the host whenever the StarSQL driver needs to bind a new Catalog package. StarSQL binds a new Catalog package when a user connects for the first time using the driver (unless that user is using a shared library for packages), and whenever the SQL Catalog Qualifier is changed to a previously unused collection. ============== CHANGE HISTORY ============== StarSQL 2.5 * StarSQL now provides a choice of strict or relaxed SQL parsing. * The connection dialog now contains a test button for verifying host connectivity. * Support for extended syntax for DB2 Universal Database (UDB) 5.x StarSQL 2.4.5 * Support in the Connection Setup dialog for SNA Server to display APPC parameters (local LU, remote LU, and mode) obtained from the SNA Server. * In DB2 for OS/390 v5.1 environments, StarSQL can now return result sets from stored procedures. * StarSQL now provides a choice of bind options for DB2/MVS environments, allowing a package to be bound with either the authorization ID of the connection or of the package creator. StarSQL 2.4.1 * Support for SQLDescribeParam Version 2.41 of StarSQL now supports this ODBC Ver 2.x, Level 2 function. This new feature will allow for additional capabilities when using RDO (remote data objects - a VB4.0 Enterprise Edition feature), ADO (OLE DB objects), and PowerBuilder. * Support for Default Qualifier Allows the user to specify a qualifier to be prepended to unqualified table and view references. * Improved performance for the catalog calls: SQLStatistics, SQLPrimaryKeys and SQLForeignKeys. StarSQL 2.4 * Improved compatability with: Microsoft Internet Information Server 3.0 Microsoft Visual InterDev ODBC 3.0 system datasources * support for SQLProcedures() and SQLProcedureColumns() for OS/400 v3r2 & v3r7 (and OS/400 v3r1 & v3r6 with relevant PTF's applied) - see "SQLProcedures() Enhancements" below. * Support for held cursors on AS/400 * Commit on SQLFreeStmt(SQL_CLOSE) in auto commit mode * Support for translation from the Greek Windows Code Page 1253 to and from CCSID 875 * Support for Hebrew & Arabic codepages using Microsoft SNA Server 3.0 SNANLS API (32 bit only) See "Language Support Enhancements" below. StarSQL 2.3 * enhancements to SQLColumns() for SQL/DS & Common Server * enhancements to SQLCancel() * improved compatibility with Microsoft Access and Visual Basic when using timestamp fields * support for Lotus Approach (32 bit) StarSQL 2.2.5 * improved support for Korean double-byte environments * improved compatibility with ODBC 3.0 StarSQL 2.2.2 * Support for additional ODBC Level 2 functions: - SQLPrimaryKeys() - SQLForeignKeys() - SQLProcedures() - SQLProceduresColumns() - SQLStatistics() * Stored Procedure Support for DB2 Common Server and DB2/MVS 4.1 * Support for SQL/DS 3.3 * Support for creating custom installation directories that create ODBC data sources during user installations. * Advanced Options dialog * Support for OS/400 v3r6 * New Help Files * enhanced support for held cursors (DB2 MVS) INSTALLATION NOTES FOR WINDOWS * Installed Files In addition to the ODBC DLL modules, the following StarSQL DLL modules are installed into \SYSTEM during the 16-bit StarSQL install: ARI0304.DLL QSQ0203.DLL SWRNTM16.DLL QSQ0301.DLL SWODBC16.DLL DSN0203.DLL SWWAPPC.DLL DSN0301.DLL SQL0201.DLL SWGEST16.DLL SWHCNV16.DLL SWRSRC16.DLL DSN0401.DLL SWPRO16.DLL QSQ0302.DLL SWTCP16.DLL SQLPRS16.DLL DSN0500.DLL SQL0500.DLL Also the files SWODBC16.HLP, SWODBC16.HBR, HBROWSER.DLL, and HSEARCHR.DLL are installed in installed into \SYSTEM and SWODBC.INI is installed in \. * General Protection Fault Related to WIN87EM.DLL: In rare cases, you may receive a a General Protection Fault in module KRNL386.EXE when using StarSQL multiple times. This is due to the StarSQL DLL SWODBC16 being reloaded when WIN87EM.DLL is not already loaded. In most cases this will not occur because WIN87EM.DLL is usually loaded. If this does occur, the workaround is to rename WIN87EM.DLL to WIN87EM.EXE and place WIN87EM.EXE on the LOAD= line of the WIN.INI file. This allows Program Manager to always keep WIN87EM.DLL in memory. INSTALLATION NOTES FOR WINDOWS NT and WINDOWS 95 * Installed Files In addition to the ODBC DLL modules, the following StarSQL NT DLL modules are installed into \SYSTEM32 during the 32-bit StarSQL install: ARI34NT.DLL SWGEST32.DLL QSQ23NT.DLL SWRNTM32.DLL QSQ31NT.DLL SWODBC32.DLL DSN23NT.DLL SQL21NT.DLL DSN31NT.DLL SWHCNV32.DLL SWAPPCNT.DLL SWRSRC32.DLL SWLMGR32.DLL SWPRO32.DLL DSN41NT.DLL QSQ32NT.DLL SWTCP32.DLL SQLPSR32.DLL DSN50NT.DLL SQL50NT.DLL Also the files SWODBC32.HLP & SWODBC32.CNT are installed in \SYSTEM32 and SWODBC.INI is installed in \. * StarSQL appears as "StarSQL NT" in Windows NT v1.03 and earlier; StarSQL appears as "StarSQL 32" in Windows NT and Windows 95 v1.04 and later. The name "StarSQL NT" was changed to "StarSQL 32" with the addition of Windows 95 support in StarSQL v1.04. * IMPORTANT NOTE: If you have previously installed StarSQL NT v1.03 or earlier, we recommend that you remove the old version before installation of StarSQL 32 v1.04 or later. If you do not remove the old version of StarSQL NT, both names ("StarSQL NT" and "StarSQL 32") appear in the list of available ODBC drivers when you configure ODBC data sources. Either name will work correctly, but the display of two names may cause confusion. In addition, if you remove the old version of StarSQL NT after installation of the new version of StarSQL 32 v1.04 or later, the new version of StarSQL ceases to function. To remove the old version of StarSQL NT v1.03 or earlier: 1. Record the names and contents of all your StarSQL data sources, as these will be deleted and you will need to recreate them. 2. Start the 32-bit ODBC Administrator (in the "ODBC (Common)" program group and in the Control Panels). 3. Select Drivers. A list appears of all installed ODBC drivers. 4. Select "StarSQL NT" and click the "Delete" button. * Setup installs MFC30.DLL for use by the ODBC Driver Manager. We recommend that you close all applications before beginning installation, to avoid the situation where MFC30.DLL is in use by another application and cannot be replaced. * If you have previously installed CTL3D32.DLL, you may receive a warning that it is currently in use & cannot be replaced; Click "ignore" to continue installing this product. ACCESSING THE 32-BIT STARSQL ODBC DRIVER FROM A 16-BIT APPLICATION: Setup installs everything you need to access the 32-bit StarSQL ODBC Driver from a 32-bit application. If you would like to access the 32-bit driver from a 16-bit application, you must install the 16-bit ODBC components: 1. From the 16-bit StarSQL Install Driver screen, do not select any drivers. 2. Click OK. The correct version of 16-bit ODBC components your 16-bit application needs to access the 32-bit driver is now installed. The use of 32 bit ODBC data sources by 16 bit Windows applications is supported only on WindowsNT; we recommend that users of 16 bit applications on Windows95 install the 16 bit version of StarSQL (and a corresponding 16 bit SNA connection). ======================= DOCUMENTATION ADDENDUM: ======================= The following information supplements information contained in the Help file. ============================================ USING STARSQL WITH ODBC ENABLED APPLICATIONS ============================================ Microsoft Access: StarSQL 2.3 adds support for the following Microsoft Access scenarios: 1) Microsoft Access Problem 1 - The Update Problem When using Microsoft Access to update a record on DB2/MVS or DB2/400, in a table which contains a TIMESTAMP column, a user may get the following error: "This record has been changed by another user since you started reading it, if you save the record you will overwrite the changes made by the other user, copying the changes to the clipboard will let you look at the values the other user entered and then paste the changes back if you decide to use them" The user is then presented with two dialog boxes. One says "Save" (which is greyed out), and the other says, "Save to clipboard, drop changes." Our solution is a new option to present timestamps to the calling ODBC application as CHAR(26) strings. This is turned off by default, and can be turned on by a setting in the [Defaults] section of the SWODBC.INI file. [Defaults] TimeStampAsChar=Y See the "Advanced Options" section of the Help file for details on modifying SWODBC.INI. After changing SWODBC.INI, re-attach the table in Microsoft Access. 2) Microsoft Access problem 2 - The Query Problem A user has attached a table in Access, and the table has a date field. The user then executes a select statement using a Date/Time value in a comparison (WHERE clause). Example: Select * from wsw004.buglog where wsw004.buglog_date < date() The user then gets an "ODBC - Call Failed" error. Upon clicking OK, they then get the error dialog. [StarQuest][StarSQL ODBC Driver][DB2] THE STRING RESPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATE TIME VALUE(#181) Note: this only occurs for DB2/MVS and SQL/DS Our solution: when a query from Microsoft Access passes in a timestamp value as a parameter, StarSQL will try to guess the target column data type. This is the default behavior; it can be disabled by specifying AccessFix1=N in the [Defaults] section of the SWODBC.INI file. [Defaults] ProductPath16=C:\starsql\programs; ProductPath32=C:\starsql\programs; AccessFix1=no Lotus Approach (32 bit): StarSQL 2.3 adds support for a problem encountered with the 32 bit version of Lotus Approach. Approach calls SQLTablePrivileges(), even though StarSQL does not support this function. StarSQL now returns 'Y' for SQLGetInfo(SQL_ACCESSIBLE_TABLES). This should not affect the operation of other applications, but if a problem is encountered, the behavior can be overridden by specifying ACCESSIBLE_TABLES=N in the [Defaults] section of SWODBC.INI. [Defaults] ACCESSIBLE_TABLES=N ======================== SQLCancel() Enhancements ======================== SQLCancel() has been enhanced for StarSQL connections using Microsoft SNA Server. SQLCancel() will now cause the host to terminate the DB2 query immediately. Previously, SQLCancel() would have no affect until the host had finished processing the query, but would stop the transmission of all data. In some situations (if SQLCancel() is invoked while StarSQL is waiting for a network response from the host), the connection to the host will be broken. The ODBC call that was waiting will receive a "Communication link failure" message, but SQLCancel() will receive an SQL_SUCCESS response. ============================ SQLProcedures() Enhancements ============================ StarSQL now supports SQLProcedures() and SQLProcedureColumns() for connections to OS/400 v3r2 & v3r7. Previously, these functions were supported only for connections to DB2 MVS 4.1 and DB2 Common Server platforms. To take advantage of this new function, you will need to rebind SQL packages on the AS/400. The new support also works with OS/400 v3r1 with cum PTF package C6060310 or later and OS/400 v3r6 with cum PTF package C6044360 or later installed. The support depends on the existence of the system file QSYS2.SYSPROCS, which will be created if a user runs the CREATE PROCEDURE SQL command (e.g. from STRSQL). The CREATE PROCEDURE command is documented in Chapter 7 - Stored Procedures of "DB2 for OS/400 SQL Programming" SC41-4611-01. In addition, after installation, edit the SWODBC.INI file (in your Windows directory) to use the v3r2/v3r7 personality for v3r1 and/or v3r6 hosts: Under: [Customize] change QSQ0301=QSQ0301.DLL QSQ0306=QSQ0301.DLL to QSQ0301=QSQ0302.DLL QSQ0306=QSQ0302.DLL Under: [CustomizeNT] change QSQ0301=QSQ31NT.DLL QSQ0306=QSQ31NT.DLL to QSQ0301=QSQ32NT.DLL QSQ0306=QSQ32NT.DLL ============================= Language Support Enhancements ============================= * Support for Greek translation tables StarSQL supports translation from the Greek Windows Code Page 1253 to and from the following CCSIDS: CCSID Host 875 Greek * Support for Hebrew & Arabic codepages using Microsoft SNA Server 3.0 SNANLS API StarSQL now supports Hebrew and Arabic if the Microsoft SNA Server 3.0 client is installed (32-bit environments only). StarSQL supports translation from the Hebrew Windows Code Page 1255 to and from the following CCSIDS: CCSID Host 424 Hebrew StarSQL supports translation from the Arabic Windows Code Page 1256 to and from the following CCSIDS: CCSID Host 420 Arabic SNANLS.DLL and appropriate tables (*.NLS) are installed when you install the SNA Server 3.0 client. To complete the installation, you must add the following registry entries with REGEDT32 or REGEDIT: 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". ================ IF YOU NEED HELP ================ This copy of StarSQL has been provided to you by Microsoft. If you need technical support please contact the appropriate Microsoft support organization. StarQuest does not provide direct technical support for this copy of StarSQL. You may also find the answer to your problem in one of the following sources: the readme.txt file, which contains late-breaking release notes and hints Error Messages the FAQs on http://www.starquest.com StarQuest Software, Inc.