Release Bulletin Adaptive Server® IQ 12.4.0 for Digital UNIX Document ID: 74950-01-1240-01 Last revised: September 10, 1999 Topic 1. Product summary Page 1 2. Restrictions 3. Installation Instructions 3 5 4. Documentation for this version 5. Special migration instructions 5 6 6. Changed functionality in this version 7. Known problems 7 9 8. Product compatibilities 9. Documentation updates and clarifications 14 14 10. Technical Support 11. Other sources of information 49 50 1.
Required Operating System Patches • Adaptive Server IQ 12.4.0 Digital Unix V4.0f Note The product name Digital UNIX has recently been changed to Tru64 UNIX. However the Adaptive Server IQ documentation still uses the old Digital UNIX name. The following operating system command shows the level of your base system software: % sizer -v Tru64 UNIX V4.0D (Rev. 878); Wed Jul 15 12:31:49 EDT 1998 Adaptive Server IQ 12.4.
Adaptive Server IQ 12.4.0 Converting 12.0.x databases to 12.4.0 To obtain patches, download them from the website at http://www.service.digital.com/ or contact your Digital representative. Note These patches require that you rebuild your kernel. You must halt your system and then boot from the console after the new kernel is put into place. When installing patches, log in as the “root” (superuser) and follow the directions provided with the patch.
Insert into table from remote SQL database not supported Adaptive Server IQ 12.4.0 You must run upgrasiq.sql once for each 12.0.x database to upgrade it to 12.4.0. Note For Adaptive Server IQ versions 12.0.3, 12.03.1, and 12.4.0: Due to a timing related issue, the database server process will sometimes hang if an IQ database is started on the command line. For a workaround, see the readme file included with the software.
Adaptive Server IQ 12.4.0 Setting the LD_LIBRARY_PATH Environment Variable SUBSTRING(COL2 ...) 3. Installation Instructions For complete installation instructions, see Adaptive Server IQ Installation and Feature Guide for Compaq Digital UNIX. 3.1 Setting the LD_LIBRARY_PATH Environment Variable You must set the LD_LIBRARY_PATH environment variable before running Adaptive Server IQ and utilities.
Accessing Current Release Bulletin Information Adaptive Server IQ 12.4.0 Depending on how you use Adaptive Server IQ, you may also need to refer to the documentation for Adaptive Server Anywhere. Refer to the Version 6.02 edition of Adaptive Server Anywhere documentation on the Sybase Technical Library Web site, for the most current information that applies to Adaptive Server IQ.
Adaptive Server IQ 12.4.0 Changed functionality in Adaptive Server IQ 12.4.0 In version 11.x, you could output the query plan using the command IQ SET QUERYINFO ON. In Adaptive Server IQ 12.0, run the following command to output the query plan: SET TEMPORARY OPTION Query_Plan = ’on’ The plan will be in the .IQMSG file. If you want additional detail or are sending the plan to Technical Support you can use: SET TEMPORARY OPTION Query_Detail = ’ON’ 6.
Improved stored procedure output • Adaptive Server IQ 12.4.0 Improved stored procedure output Stored procedures now display output in units that are easier to understand. • Database administrators can specify a minimum password length, to discourage easily discovered passwords. Minimum password length For more information, see “MIN_PASSWORD_LENGTH option” on page 42 • ODBC 3.51 The ODBC driver has been updated to ODBC 3.51. This version of ODBC includes support for Unicode applications.
Adaptive Server IQ 12.4.0 stop_asiq utility •-iqmc sets the size of the main buffer cache •-iqtc sets the size of the temporary buffer cache See “Additions to the start_asiq or asiqsrv12 command-line options” on page 15 for details. • stop_asiq utility You can stop the server using the stop_asiq utility. For more information see “stop_asiq utility” on page 18. 6.3 Changed functionality in Adaptive Server IQ 12 Adaptive Server IQ 12 takes a giant step forward from earlier versions.
Data definition Adaptive Server IQ 12.4.0 7.1 Data definition This section reports problems with data definition. 7.1.1 Temporary tables in procedures When you include an automatically created temporary table in a procedure, the table should be dropped automatically when the procedure completes. In Adaptive Server IQ 12.4.0, however, the table is not dropped. As a result, it becomes visible outside the procedure, and a new instance of the table is created each time the procedure executes.
Adaptive Server IQ 12.4.0 Large IN subqueries • = ALL • != ALL If you use an unsupported query in this group, Adaptive Server IQ returns an error like the following: Feature, ANY, not yet implemented Queries of this type can always be expressed in terms of IN subqueries or scalar subqueries using MIN and MAX set functions. 7.2.
User-defined variable issue Adaptive Server IQ 12.4.0 To avoid truncated output, increase the length by setting the truncation_length option as follows: SET OPTION DBO.TRUNCATION_LENGTH = 80 Alternatively, from the DBISQL menu select Command→Options and enter a higher value for Limit Display Columns and/or Limit Output Columns. 7.3.3 User-defined variable issue User-defined variables will core dump if used in IQ queries. 7.3.
Adaptive Server IQ 12.4.0 Sybase Central 7.4 Sybase Central This section reports problems with the Adaptive Server IQ plug-in for Sybase Central. 7.4.1 Problems with Add User-defined Data Type wizard When you create a user-defined data type, the Adaptive Server IQ plug-in allows you to specify a default value incompatible with the user-defined base data type. For example, if you specify base type integer, you may insert a character string ’test’ as default value.
Data Type column in Table Editor retains focus Adaptive Server IQ 12.4.0 When you use the Add Index Wizard to create a new index, the Choose IQ Index Type screen lets you specify the number of records that should be added before sending a notification message. The properties screen for the index does not display this setting, however. 7.4.
Adaptive Server IQ 12.4.0 • Additions to the start_asiq or asiqsrv12 command-line options Remove all limits, and then set limits on the stack size and descriptors. To do so, go to the C shell and issue these commands: % unlimit % limit stacksize 8192 % limit descriptors 4096 Note Note that unlimit affects soft limits only. If you have any hard limits, you must change them by setting kernel parameters. • Set all server parameters appropriately in the asiqsrv12 command. 9.1.
-gm command line option Adaptive Server IQ 12.4.0 The -iqmt switch is not set by start_asiq on Digital UNIX systems. The setting listed in the Adaptive Server IQ Administration and Performance Guide and the Adaptive Server IQ Installation and Feature Guide is incorrect. The default value is calculated from the number of connections and the number of CPUs, and is usually adequate. 9.1.
Adaptive Server IQ 12.4.0 Confirming connections The following note should be added to Chapter 2, “The Database Server,” after the description of the -v server switch. Note In order to display the version on 64–bit platforms, you must do the following: • Run start_asiq -v instead, which will set up the correct paths, environment, iq parameters, etc... Anything you pass to start_asiq will be passed to asiqsrv12. End users should always use start_asiq.
Using a .odbc.ini file Adaptive Server IQ 12.4.0 If Adaptive Server IQ does not detect the presence of an ODBC driver manager, it will use ~/.odbc.ini for data source information. Otherwise, it will query the driver manager for data source information. 9.1.9 Using a .odbc.ini file The following corrections apply to “Using ODBC data sources on UNIX,” in Chapter 2 of the Adaptive Server IQ Administration and Performance Guide. When creating a .odbc.
Adaptive Server IQ 12.4.0 Addition to STOP DATABASE statement Y ------------------------------------------------------ If you type Y (yes), the following message displays: -----------------------------------------------------Shutting down asiqsrv12 ......... (server shutdown). ------------------------------------------------- If you type N, you are returned to the system prompt and IQ does not shut down the server.
Error in DBSTOP example Adaptive Server IQ 12.4.0 The following information should be added to the STOP DATABASE statement in the Adaptive Server IQ Reference. When you issue STOP DATABASE database-name the database-name is the name specified in the -n parameter when the database is started, or in the DBN (DatabaseName) connection parameter. This name is typically the file name of the database file that holds the Catalog Store, without the .db extension. 9.1.
Adaptive Server IQ 12.4.0 -Z switch must be uppercase The parameter string in the sample configuration file shown in Chapter 2 of the Adaptive Server IQ Administration and Performance Guide should be corrected to: -n Elora -c 16M -x tcpip(port=2367) -gm 10 -gp 4096 path\mydb.db 9.1.
MESSAGE PATH Adaptive Server IQ 12.4.0 9.2.2 MESSAGE PATH In the MESSAGE PATH clause of CREATE DATABASE you must specify an operating system file. The message file cannot be on a raw partition. This is a correction to the Adaptive Server IQ Reference. 9.2.3 Low_Disk functions as High_Group LD (Low_Disk) has been changed to function like HG (High_Group). It is no longer supported by a separate index and therefore does not offer any benefit beyond using HG.
Adaptive Server IQ 12.4.0 Error documenting IQ PATH • Specify a different pathname (for example, /iqfiles/main/iq and /iqfiles/temp/iq or different raw partitions • Omit TEMPORARY PATH when you create the database. In this case, the temporary store is created in the same path as the Catalog Store, with the default name and extension dbname.iqtmp where dbname is the database name. Warning! On UNIX platforms, you must be careful not to specify filenames that are links to the same file.
SIZE clause of CREATE DBSPACE Adaptive Server IQ 12.4.0 in database /tech1/iq/cdrdb.db. In another session, please issue a CREATE DBSPACE ... IQ STORE command and add a dbspace of at least 1000 blocks. After commit and checkpoint messages, the following error displays: 1999-02-05 16:12:53 0001 Exception Thrown from hos_ion.cxx:170, Err# 4, tid 102 origtid 102 1999-02-05 16:12:53 0001 O/S Err#: 2, ErrID: 522 (hos_ioexception) 1999-02-05 16:12:53 0001 File does not exist. File: /tech1/iq/cdrdb.
Adaptive Server IQ 12.4.0 Recommended index types 9.2.12 Recommended index types In the Adaptive Server IQ Administration and Performance Guide, Chapter 4, “Adaptive Server IQ Indexes,” in Table 1–2, Query type/index, the recommended index types for COUNT and range predicate are incorrect.
Changes to “Using join indexes” Adaptive Server IQ 12.4.0 If the join column is made up of more than one column, the combination of the values must be unique. For example, in the asiqdemo database, the id in the customer table and the cust_id in the sales_order table each contain a customer ID. The customer table contains one row for each customer and, therefore, has a unique value in the id column in each row. The sales_order table contains one row for each transaction a customer has made.
Adaptive Server IQ 12.4.0 Changes to “Using join indexes” Creating star joins The following should be added to Chapter 4 of the Adaptive Server IQ Administration and Performance Guide just after the figure that shows the sales_order table in a star join. You can create this table using the following commands: CREATE TABLE "DBA"."sales_order" ( "id" integer NOT NULL, "cust_id" integer NOT NULL REFERENCES "DBA".
Error in DISK_STRIPING default Adaptive Server IQ 12.4.0 9.3 Error in DISK_STRIPING default The Adaptive Server IQ Reference contains an error in the General Database Options table in Chapter 5, “Database Options.” The DISK_STRIPING option default should be ON. 9.4 Data manipulation (DML) 9.4.1 DISK_STRIPING default The Adaptive Server IQ Reference contains an error in the General Database Options table in Chapter 5, “Database Options.
Adaptive Server IQ 12.4.0 Default Default ON Description This option reports a syntax error for those queries containing outer joins that have ambiguous syntax due to the presence of duplicate correlation names on a null-supplying table. The following join clause illustrates the kind of query that is reported. ( R left outer join T , T join S on ( C1 ) ) where C1 is a condition. If the option is set to ON, this query is interpreted as follows.
New and changed general database options • Adaptive Server IQ 12.4.0 In order to join a local ASE table with a remote IQ 12 table, the ASE version must be 11.9.2, and you must use the server class cd ASAnywhere. Note Adaptive Server Enterprise 11.9.2 introduces new server classes to be used for accessing remote databases from the ASE 11.9.2 system. You must use server class ASAnywhere to access Adaptive Server IQ 12.0x or Adaptive Server Anywhere 6.x from ASE 11.9.2.
Adaptive Server IQ 12.4.0 Description Description This option specifies an upper bound (in MB) on the amount of heap memory subsequent loads can use. The default setting, 0 (zero), means that there is no upper bound, and Adaptive Server IQ can use as much heap memory as necessary to perform the load. A non-zero value means that the user has set an upper bound. This option is typically used for LOAD statements, but affects all operations where loads occur, including SYNCHRONIZE and DELETE operations.
Description Description Adaptive Server IQ 12.4.0 For joins within a query, the IQ optimizer has a choice of several algorithms for processing the join. This option allows you to override the optimizer’s cost—based decision when choosing the algorithm to use. It does not override internal rules that determine whether an algorithm is legal within the query engine.
Adaptive Server IQ 12.4.0 String function REPEAT is supported { SELECT * FROM CUSTOMERS } Note This syntax is not currently supported on Digital UNIX. 9.4.8 String function REPEAT is supported The string function REPEAT is supported in Adaptive Server IQ version 12.x. It is documented as follows: REPEAT function [String] Function Returns a string composed of integer-expression instances of stringexpression, concatenated together.
Using ISNULL() and COALESCE() Adaptive Server IQ 12.4.0 The NUMBER(*) function is not supported and should be deleted from the Adaptive Server IQ Reference Manual. 9.4.11 Using ISNULL() and COALESCE() ISNULL() and COALESCE() can be used to convert NULL values into something else. If these are used with an outer join, the production of this expression gets pushed below the outer join.
Adaptive Server IQ 12.4.0 Default Default 1 Description This option lets you control the amount of space Adaptive Server IQ sets aside space in your temporary IQ Store, so that if you run out of disk space there you can add a new dbspace. Adaptive Server IQ sets aside 1 MB by default. This value is usually sufficient to run the DDL commands. You do not need to set aside room to hold the new dbspace. This option only provides space for executing related DDL commands. 9.5.
Effect of checkpoints Adaptive Server IQ 12.4.0 Temporary IQ Blocks Used:,163 of 6144, 2%, Max Block#: 97 If the percentage of blocks used is in the nineties, you need to add more disk space with the CREATE DBSPACE command. In this example, 82% of the Main IQ Blocks and 2% of the Temporary IQ Blocks are used, so more space will soon be needed in the Main IQ Store. Effect of checkpoints Insert the following text at the start of the section “Effect of checkpoints on out of disk space conditions.
Leaked space recovery • Incremental backups are disabled After the database is opened in forced recovery mode, incremental backups are disabled. The next backup must be a full backup. Doing a full backup reenables incrementals. • Forced recovery affects all databases opened The forced recovery parameter applies to all opens of the database while the server is up.
To verify that the data is not corrupt and set the database storage to its actual value, you start the server with the -iqdroplks switch and connect to the database. You then set the option dbcc_option and run the sp_iqcheckdb stored procedure. Depending on results, you may need to reset this option and rerun the procedure. See the discussion below for details. New set option A new set option, dbcc_option, controls the actions performed by sp_iqcheckdb.
Value Action 2 Runs CheckStorage. Checks for leaked blocks and corrupt database pages by walking all the block maps in the system and reading every database page. Runs about 50 times slower than option 1. Produces a report of findings. Does not reset the free list. 3 This option is the same as the default option when -iqdroplks is passed to the server except that it runs in read-only mode. Runs CheckAllocation Fix. Server must have been started with iqdroplks switch.
The dbcc_option settings of 0 and 3, when combined with the server option -iqdroplks, update the free list if no errors are detected. In order to perform this function, write transactions are prevented before and during the running of sp_iqcheckdb. The stored procedure ensures this by taking the appropriate locks during its execution. Any write transactions are blocked while sp_iqcheckdb is running.
Example Assume that the DBA cannot successfully open and connect to database foo, because of reported IQ errors during database open and recovery. To force recovery and correct leaked space, follow the steps below. Note Do not confuse an inability to connect to a database with an IQ server- level error while IQ is trying to open a database. 1 Start the database server with the -iqfrec switch: asiqsrv12 -iqfrec foo ... -gd dba ... foo.db or on UNIX start_asiq -iqfrec foo ... -gd dba ... foo.
Column name Description Nblocks Info Number of IQ blocks Component of the IQ index for which the Kbytes, Pages, and Compressed Pages are being reported. The components vary by index type. For example, the default (FP) index includes BARRAY and Bitmap (BM) components. The Low_Fast (LF) index includes Btree (BT) and Bitmap (BM) components. 9.5.6 SP_IQSTATUS now displays IQ Page Size The sp_iqstatus stored procedure now displays the IQ page size in addition to the block size.
When you use the RESTORE statement to move and/or rename a database, you can rename all of the files except the transaction log. Transactions continue to be written to the old log file name, in the location where the Catalog Store file (the .db file) is located after the database is restored. When you rename or move all other files in the database, it is preferable to do the same for the log file. To move or rename the log file, you use the Transaction Log utility (DBLOG).
This option sets a filename, including an optional directory path, for a new transaction log. If the database is not currently using a transaction log, it starts using one. If the database is already using a transaction log, it changes to using the new file as its transaction log. Set the name of the transaction log file (-t ) 9.5.9 Error message for buffer cache settings The following paragraph describes a change in behavior as of Version 12.4.0.
The same command can also be used to add a new user. For this reason, if you inadvertently enter the user ID of an existing user when you mean to add a new user, you are actually changing the password of the existing user. You do not receive a warning because this behavior is considered normal. This behavior differs from pre-Version 12 Adaptive Server IQ. To avoid this situation, use the system procedures sp_addlogin and sp_adduser to add users.
9.5.14 Changes to BACKUP statement In the Adaptive Server IQ Reference, the SIZE and STACKER option descriptions in the BACKUP statement should read: SIZE option Specifies maximum tape or file capacity (some platforms do not reliably detect end-of-tape markers). No volume used on the corresponding device should be shorter than this value. This value applies to both tape and disk files but not 3rd party devices. Units are KB so, for example, for a 3.5 GB tape you specify 3500000.
ipcrm -m mid1 -m mid2 ... -s sid1 -s sid2 ... For example: % ipcrm -m 40965 -s 5130 -s36682 9.5.16 Monitoring server activity It may be helpful, especially for new users, to monitor server activity. When you start a server with the start_asiq utility, server activity is logged in an ASCII text file placed in the directory defined by $ASLOGDIR. (If $ASLOGDIR is not defined, it defaults to $ASDIR/logfiles.) The log file name has this format: your_server_name.###.
9.6 Client applications 9.6.1 ODBC AutoPreCommit omitted The ODBC AutoPreCommit option was omitted from the Adaptive Server IQ Reference. Turning this option ON causes each statement to do a COMMIT before execution (as opposed to a COMMIT after execution for the AutoCommit option). The default for AutoPreCommit is OFF. Set the AutoPreCommit option in either the Windows (NT/95) registry or the .ini file (Windows 3.1). For example: [Sample DSN] UID=DBA PWD=SQL AutoPreCommmit=y 9.6.
9.7.1 Adaptive Server IQ plug-in help reflects Multiplex support Adaptive Server IQ Multiplex 12.4.0 is a separate product from Adaptive Server IQ 12.4.0. If you have not purchased or installed Adaptive Server IQ Multiplex, the functionality described in the online help topic Managing Multiplexes is not available. 10. Technical Support Each Sybase installation that has purchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support.
• Output from sp_iqstatus procedure You may find additional help from the Sybase online support database, MySupport. MySupport lets you search through closed support cases, latest software bulletins, resolved and known problems, using a view customized for your needs. You can even open a technical support case online. MySupport can be used from most Internet browsers. Open the MySupport home page, mysupport.sybase.com, and follow the instructions provided there to sign up for and use this free service.
techinfo.sybase.com ❖ 2 In the Browse section, click on the What’s Hot entry. 3 Explore your area of interest: Hot Docs covering various topics, or Hot Links to Technical News, Certification Reports, Partner Certifications, and so on. If you are a registered SupportPlus user: 1 Point your Web browser to Technical Documents at the following Web site: techinfo.sybase.com 2 In the Browse section, click on the What’s Hot entry. 3 Click on the EBF Rollups entry.
