STARSQL for UNIX Release 5.39 Release Notes July 2008 Copyright 1994-2008 StarQuest Ventures, Inc. All Rights Reserved. StarQuest Ventures, Inc. PO Box 1076 Point Reyes Station, CA 94956 Telephone: 415-669-9619 FAX: 415-669-9629 Sales & support: contact@starquest.com URL: http://www.starquest.com StarSQL for UNIX is a powerful ODBC/CLI driver that enables applications running on a UNIX workstation to transparently access, send SQL queries to, and retrieve results from DB2 databases running on remote IBM mainframe, midrange, or Unix systems. It supports the merged ODBC 3.0/CLI specification and is backwards compatible with IBM DB2 v2 CLI. REQUIREMENTS ============ PLATFORM SPECIFIC: Solaris: * SUN Solaris 8 or above (SPARC) * UTF-8 locale ICONV patch (when using ODBC) Solaris 8: Patch-ID# 113261-01 AIX: * IBM AIX 4.3.3 or above * C++ Application Runtime: Be sure that AIX C Set ++ for AIX Application Runtime (xlC.rte package on the AIX 4.x distribution CDROM) is installed. Linux (x86): * Red Hat Linux 9 or above. * SUSE 9 or above Other versions of Linux may work, but have not been tested. HP-UX: * HP-UX 11.11 or later (PA-RISC) FreeBSD: -------- * FreeBSD 6.x or later StarSQL includes a version of libiconv.so (with additional needed conversions) in $STARSQL/lib; make sure that $STARSQL/lib appears before /usr/lib or /usr/local/lib in LD_LIBRARY_PATH. GENERAL: * TCP/IP access to the DB2 system. This can be either -- direct TCP/IP access to a DB2 system with DRDA over TCP/IP support (DB2 for OS/390 v5.x, DB2 UDB v6.x, DB2 for VM v6.x, DB2 for VSE v7.x, OS/400 v4r2 & later) -- connection to a DB2 system through a StarPipes gateway (StarPipes/400 or StarPipes for Windows). StarPipes gateway provides connectivity between TCP/IP and SNA. * Sufficient disk space for installation * Root user authority * StarQuest StarLicense package ============== HOST DATABASES ============== StarSQL can connect to any of the following host databases: * DB2 for OS/390 and z/OS v5.1 and later * SQL/DS (DB2 for VM & VSE) v3.3 and later * DB2/400 (DB2 UDB for iSeries, DB2 for i5/OS) - OS/400 v3r2 and later * DB2 Universal Database (UDB) v6.x and later for UNIX and Windows. For native DRDA over TCP/IP support: * DB2 for OS/390 and z/OS v5.1 and later * DB2 for VM v6.1 and later * DB2 for VSE v7.1 and later * DB2/400 (DB2 UDB for iSeries, DB2 for i5/OS) - OS/400 v4r2 and later * DB2 Universal Database v6.1 and later for UNIX and Windows. For LOB (large object) support: * DB2 for OS/390 v6.1 and later * DB2 UDB for iSeries - OS/400 v5r1 (with appropriate PTF's) and later * DB2 Universal Database v8.1 and later for UNIX and Windows. If you will be accessing Unicode or ASCII LOB data on DB2 for OS/390 or z/OS, it is recommended that you have applied the PTF's described in the IBM APAR PQ85495. For details, refer to the StarSQL documentation on the expert Settings UseSYSDUMMYAEU. ========== WHAT'S NEW ========== * Support for DB2 9 for z/OS and DB2 9 for Linux, UNIX and Windows * Support for i5/OS v6r1 * enhanced international language support, including enhanced support for Group 2 multi-byte and double-byte character sets * enhanced Unicode host support ======================= Upgrade Considerations: ======================= After installing StarSQL 5.30, you should rebind StarSQL packages on the host system. We suggest that you use StarAdmin from StarSQL for Windows to do this. These packages are backwards compatible with StarSQL 5.1x and 5.2x clients. If you are using StarSQL 5.0x or earlier, we recommend that you upgrade those clients or use a different package collection. In addition, if you are upgrading from a previous version of StarSQL 5.3x (prior to 5.38), you should rebind packages if you are connecting to DB2 for z/OS and are changing the value of the new UseDynamicCatalogSQL expert setting from its default of "YES" to "NO". See the StarSQL User's Guide (PDF) for details about package compatibility. QUICK INSTALLATION INSTRUCTIONS =============================== 1. Logon to UNIX as root. 2. cd to the directory containing the StarSQL installer for your UNIX platform. e.g. # cd /mnt/cdrom/starsql/linux For Linux, there are two installers: * the directory "linux" contains a tar-based installer that does not depend on the presence of RPM * the directory "linux.rpm" contains an RPM-based installer created and tested on Red Hat Linux 3. Execute the shell script "setup": # ./setup This will install StarSQL to the default location and run post_install. The default location varies depending on platform: AIX: /usr/lpp/starsql Solaris: /opt/starsql Linux: /usr/share/starsql HP-UX: /opt/starsql FreeBSD 6.x: /usr/local/share/starsql Upgrading from a previous version of StarSQL for UNIX: ====================================================== * Remove the previous version of StarSQL for UNIX: AIX: installp -u starsql Solaris: pkgrm starsql HP-UX: swremove starsql Linux (RPM): rpm -e starsql Linux (tar): rm -rf /usr/share/starsql FreeBSD: rm -rf /usr/local/share/starsql * Install as above. * All users should update the copy of .swodbc.ini in their home directory. This is required to take advantage of new host support (DB2 for z/OS v8). Rather than creating a .swodbc.ini in each user's home directory, You can also creating a symbolic link in each home directory to $STARSQL/etc/swodbc.ini: $ ln -s $STARSQL/etc/swodbc.ini .swodbc.ini However, using this method may cause problems if you wish to use DRDA tracing (see Known Limitations below). USING STARSQL/UNIX ================== Once you have installed StarSQL, you will need to set up your user profile with the appropriate files and environment variables. Add the following environment variables to your .profile or .cshrc: export STARSQL= (e.g. /usr/share/starsql for Linux) export PATH=$PATH:$STARSQL/bin:$STARSQL/odbc/bin export MANPATH=$MANPATH:$STARSQL/man export LD_LIBRARY_PATH=$STARSQL/lib:$STARSQL/odbc/lib:$LD_LIBRARY_PATH (Solaris, Linux, FreeBSD) export LD_32_LIBRARY_PATH=$STARSQL/lib:$STARSQL/odbc/lib:$LD__32_LIBRARY_PATH (FreeBSD on amd64) LIBPATH=$STARSQL/lib:$STARSQL/odbc/lib:$LIBPATH (AIX) SHLIB_PATH=$STARSQL/lib:$STARSQL/odbc/lib:$SHLIB_PATH (HP-UX) Two files, swodbc.ini and odbc.ini, are located in $STARSQL/etc. Copy these to your $HOME directory, and rename them to .odbc.ini and .swodbc.ini, and edit them . The StarSQL for UNIX Users Guide details the contents of these files. Each user accessing StarSQL will need these files in their $HOME directory. To centralize the administration of these files, you can also create symbolic links in each user's $HOME directory, pointing at a shared copy of these files: As an administrator: # cp $STARSQL/etc/odbc.ini /usr/local/etc/odbc.ini Create data sources in /usr/local/etc/odbc.ini. As a user: $ cd $HOME $ ln -s /usr/local/etc/odbc.ini .odbc.ini $ ln -s $STARSQL/etc/swodbc.ini .swodbc.ini Warning: do not edit the copy of odbc.ini in $STARSQL/etc, as this file will be replaced during an update of StarSQL, and your changes will be lost. DOCUMENTATION ============= See the StarSQL for UNIX Users Guide (ssqlunix.pdf) for installation and usage instructions. In addition, UNIX man pages for the StarSQL commands are installed in $STARSQL/man. Information about using StarSQL with the unixODBC driver manager and ODBC-enabled applications can be found in $STARSQL/odbc/readme. Additional documentation can be found in the StarSQL technical documents in the support area at http://www.starquest.com, including the StarSQL for UNIX Quick Start Guide. LICENSING ========= StarSQL can be licensed by one of the following methods: * Using a StarLicense server * install a temporary or permanent license on each workstation StarLicense Licensing (StarLicense Server or local license): ------------------------------------------------------------ Install and configure the StarLicense package. Instructions for configuring StarLicense are included in the StarLicense package. Note that the StarLicense package is used for both a client connecting to a StarLicense server and for local (client) licensing. ================================ ODBC data source default changes ================================ The default values for the expert settings IncludeSynonyms and SpecialColumns have been changed to Yes. Using these settings can enhance the use of StarSQL with some ODBC-enabled applications such as Microsoft Query, as they can help the application identify join columns, etc. If you have not explicitly defined these values in your data source, you will get the behavior of the new defaults. New expert setting UseDynamicCatalogSQL --------------------------------------- StarSQL 5.36.0605 introduces a new expert setting UseDynamicCatalogSQL. Selecting "Yes" will cause StarSQL to favor dynamic over static SQL for catalog functions when connected to DB2/zOS servers. The default for UseDynamicCatalogSQL is Yes. In addition, the default for UseDSCRDBTBL is now Yes if it is not found in the datasource. New SWODBC.INI keyword EXPLAIN ------------------------------ Adding the keyword Explain=All in the [Defaults] section of SWODBC.INI will cause all packages bound by StarSQL to be able to be explained using Visual Explain tools. This includes both dynamic SQL packages bound with StarAdmin (or created when needed by the StarSQL driver) and static SQL packages created with the StarScribe Package Editor. Change in underscore wildcard behavior -------------------------------------- The behavior of the expert setting useDSCRDBTBL = YES (the default) will cause StarSQL to NOT consider an underscore (“low line”) a wildcard for purposes of using the DSCRDBTBL DRDA command. This behavior enhances compatibility with certain third party applications. Users who require the underscore to function as a wildcard may set the expert setting useDSCRDBTBL to NO for purposes of SQLColumns, etc. The interpretation of ‘%” remains unaffected by this change; a “%” is never considered a component of a basic name. =================== LATE BREAKING NOTES =================== Binding catalog package to DB2 UDB v8 FP8 or FP9 fails ------------------------------------------------------ If you have installed fixpak 8 or fixpak 9 on DB2 UDB v8 (Windows and UNIX), you will not be able to bind catalog packages. This is an IBM problem (APAR JR21670). The fix is included in FixPak 10. OS/400 PTF's: ------------- In general, it is a good idea to stay current on PTF packages and the DB2 Group PTF, and use the latest version of StarSQL. The following sections describe more specific issues for different versions of AS/400; check to see that the PTF's listed below are either installed or superceded. v6r1: cum C8064610 or later PTF 5761SS1-SI30581 v5r4: PTF 5722SS1-SI23461 PTF 5722SS1-SI24317 If you are using stored procedures, install PTF 5722SS1-SI22551 If you are working with BLOB data, install PTF's 5722SS1-SI22324 and 5722SS1-SI22335 v5r3: After installing CUM PTF C5298530 or DB2 Group PTF SF99503-8 (November 2005), you may encounter a problem with incorrect precision information being returned for decimal and numeric fields. To resolve this problem, install PTF SI121373 for APAR SE23286. If you are using StarSQL to invoke Java stored procedures on an AS/400, install PTF 5722SS1-SI18139 for APAR SE20127. v5r2: After installing the cum PTF or DB2 Group PTF from December 2005, you may experience extremely high temporary storage usage when an ODBC application (such as SQDR) using StarSQL connects to the iSeries machine. If you encounter this problem, install PTF 5722SS1-SI23370 for APAR SE25005. If you are using StarSQL to invoke Java stored procedures on an AS/400: install PTF 5722SS1-SI18984 for APAR SE21110. v5r1: install PTF 5722SS1-SI09453, which corrects APAR SE10974: Calling a stored procedure over DRDA results in parameters being tagged as char data when the parameters are binary data. v4r4: The following are known issues with StarSQL and OS/400 v4r4 that are resolved by OS/400 PTF's: • function check on certain catalog SQL (also a problem with v4r3): install PTF SF59100 (v4r3) or PTF SF59120 (v4r4) for APAR SA84253. • function check after repeated iterations of certain catalog SQL and SELECT statements: install PTF SF59294 for APAR SA85033. • invoking a stored procedure returns a “DDM code point 2114 not supported” error message: install PTF SF58942 for APAR SA84890. • DRDA problem: SF62126 for APAR SA88584 • DRDA problem: SF62392 for APAR SA88896 ============== CHANGE HISTORY ============== 5.39.0715 10435 review use of locale sensitive functions used by StarSQL - in particular strtod 10436 escaped wildcard doesn't use DSCRDBTBL 10416 Use list of catalog catalog qualifiers and IN Predicate, in favor of LOCATE/RTRIM (DB2 for z/OS V6 and later) 10417 add "FOR FETCH ONLY" to Catalog Queries 10420 provide mechanism for StarSQL packages to be bound with "EXPLAIN" enabled 10349 Cannot get table/view/procedure list in VS.NET 2005/2008 Server Explorer or Data Source Config Wizard 10425 Amend use of RTRIM(CREATOR) LIKE() to use alternative predicate form for MVS 10426 remove surprfluous CREATOR LIKE(%) in Catalog Static SQL for MVS 5.37.0617 10415 SQL_NO_DATA when fetching the first row for the re-executed resultset the 2nd-Nth time 5.36.0606 10413 add new expert setting "UseDynamicCatalogSQL" to "UseDynamic" for Catalog DB2/zOS V6 and later 5.35.0415 10374 Character substitution on assembled late parameter data confuses FSMs 10381 Multithread race condition in sqiconv.dll 5.34.0304 10343 FDOCA parsing error in SQLDTARD 10341 Check for existence of .swodbc.ini (UNIX) 10342 proper behavior for read-only .swodbc.ini (UNIX) 5.33.0215 10331 Interaction between StarSQL and ODBC driver manager's Unicode conversion for long strings 5.32.0111 10319 SQL_MAX_COLUMN_NAME_LEN for UDB V8 10321 Add support for SQL_C_SBIGINT and SQL_C_UBIGINT input parameters 5.31.1212 10288 Char conversion fails for column name if segmented in SQLDARD 10295 Fix return value for SQLGetInfo SQL_MAX_CATALOG_NAME_LEN (BusinessObjects compatibility) 10296 Local result set repeats after SQL_NO_DATA_FOUND (BusinessObjects compatibility) 10279 Cannot record static sql statements containing 'IN' keyword 10284 StarScribe: validation fails with long (and complex) parameterized queries 10289 SQLDescribeParam makes too many internal calls to SQLDescribeCol 10301 thread synchronization issue with the dmobject code. 10304 BusinessObjects Designer crashes while fetching data from SQLColumns call 10305 No table list returned for schema that matches current UserID 10243 update All personalities to specify SQL_OUTER_JOINS=38, 1, "Y" 10251 Incomplete Catalog SQL for UDB 10308 Error calling SQLTables from BO Designer against DB2/400 10309 Parser and four part names ================= KNOWN LIMITATIONS ================= * unixODBC system data sources in /usr/local/etc/odbc.ini are not fully supported; there must also be a .odbc.ini in each user's home directory, but that can be a symbolic link to /usr/local/etc/odbc.ini: $ cd $HOME $ ln -s /usr/local/etc/odbc.ini .odbc.ini * Only 32 bit applications are supported StarSQL and the included unixODBC driver manager are supplied as 32 bit binaries. They can be used in 64 bit environments such as Linux or FreeBSD for x64, but the calling application must also be 32 bit. * Singlethreaded vs. Multithreaded applications StarSQL/UNIX for AIX is distributed as a set of multithreaded shared libraries and should work with multithreaded applications. If the application that you are developing is not multithreaded, we suggest that you compile and link it with the multithreading options anyway. Use the xlc_r compiler instead of the xlc compiler or, for C++, use the xlC_r compiler instead of the xlC compiler. Please contact StarQuest if you need further information or require a singlethreaded version of StarSQL/UNIX for AIX. * Conflict with IBM DB2 CLI/ODBC Driver StarSQL/UNIX is designed to provide compatibility with the IBM DB2 CLI/ODBC driver that is supplied with DB2 Connect & DB2 UDB; it will work with applications that have been compiled to use IBM's CLI driver rather than ODBC. It does this by creating a symbolic link "libdb2.so" (libdb2.sl on HP-UX, libdb2.a on AIX) pointing to libSWODBC.so. However, if you have both StarSQL & DB2 installed, then the order of libraries in LD_LIBRARY_PATH (SHLIB_PATH on HP-UX, LIBPATH on AIX) may affect operation. For example, if StarSQL appears before the IBM DB2 library in LD_LIBRARY_PATH: LD_LIBRARY_PATH=$STARSQL/lib:/home/db2inst1/sqllib/lib then the following error may occur when using the "db2" command: db2: symbol lookup error: db2: undefined symbol: sqltGetCompTraceFlag If you are planning to use both StarSQL and the DB2 CLI/ODBC driver, we recommend that you remove the symbolic link in $STARSQL/lib: # cd $STARSQL/lib # rm libdb2.so * When a Unicode application uses the StarSQL ANSI driver, it cannot send late-bound character data, i.e. character data bound using the SQL_LEN_DATA_AT_EXEC macro, for the C types SQL_C_CHAR, SQL_C_WCHAR, or SQL_C_TCHAR. ================ IF YOU NEED HELP ================ * Read the StarSQL for UNIX Users Guide (ssqlunix.pdf). * Browse the StarSQL technical documents in the support area at http://www.starquest.com, including the StarSQL for UNIX Quick Start Guide. Before you contact technical support, be prepared to provide the following information: Company Information - Address - Phone - Fax Contact Information - Last and First Name - Email Address Product for IBM Host - IBM Host/Version (e.g. AS/400, v5r3) - Gateway/Version (e.g. HIS 2000, TCP/IP) - UNIX Operating System and version (e.g. SUSE Linux Enterprise Server 10, Solaris 9) - StarSQL Version - Where You Got the Product From Problem Information - Application/Version (if applicable) - Exact Error Message (if applicable) - Nature of Your Request -------------------------------------------- -------------------------------------------- --------------------------------------------