background image

Adaptive Server IQ 12.4.0

stop_asiq utility

Release Bulletin for Digital UNIX                                                              9

-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. Among 
the most important new features it includes are:

Transactional database capabilities with table-level versioning

The ability to update the database concurrently with queries by multiple 
users

A new, more efficient database format

More intelligent index loading, allowing faster loads for many indexes

Syntactic compatibility with Adaptive Server Anywhere, allowing 
Anywhere users to build on their existing knowledge base as they begin to 
use Adaptive Server IQ

Support for Transact-SQL

Additional query and view support

Improved front end support

See the Adaptive Server IQ Installation and Feature Guide Chapter 3, 
“Migrating Data from Prior Versions,” for important notes about how new 
features change database creation and connection.

7.  Known problems

For a description of known problems in Adaptive Server IQ version 12.4.0, see 
the following sections. If there is a workaround for a problem, it is provided.

See also “Restrictions”  for more information.

“Documentation updates and clarifications”  contains details that were not 
documented in time for this release.

Summary of Contents for ADAPTIVE SERVER IQ 12.4.0

Page 1: ...QL Anywhere SQL Remote SQL Server SQL Server Manager SQL Toolset Sybase Central Sybase IQ Sybase SQL Desktop Sybase SQL Workgroup SyBooks System 10 System 11 Watcom SQL Web SQL WorkGroup SQL Server XA Library XA Server and XP Server are trademarks of Sybase Inc Other product names used herein may be trademarks or registered trademarks of Sybase or other companies 1 99 Release Bulletin Adaptive Ser...

Page 2: ... IQ on Digital UNIX Digital UNIX V4 0d requires the March 1998 patch kit as the absolute minimum Normally it is best to install the latest patch kit However as of Adaptive Server IQ 12 4 0 certification the latest patch kits contain a problem in the pthreads library which causes a one to two times performance slowdown in Adaptive Server IQ most notably in loads and multi user activity For this rea...

Page 3: ...tions Read this section Your system may produce unexpected results if you ignore the restrictions and other instructions listed below If you are upgrading from version 11 x to 12 4 0 see chapter 3 of the Adaptive Server IQ Installation and Feature Guide for further restrictions Converting 12 0 x databases to 12 4 0 Before you can use a database created with earlier versions of Adaptive Server IQ y...

Page 4: ...trictions For this release to obtain the best performance Sybase recommends the following minimum IQ page sizes 32 KB IQ PAGE SIZE 32768 for a database containing up to 10 million rows 64 KB IQ PAGE SIZE 65536 for a database containing up to 100 million rows Note that this is the default IQ page size 128 KB IQ PAGE SIZE 131072 for databases with more than 100 million rows Do not create any databas...

Page 5: ...12 4 0 All documentation for Version 12 4 0 is available in one of two formats indicates document found on the Adaptive Server IQ Technical Library CD Hard copy documentation can be ordered separately indicates document may be provided in hard copy Table 1 Current Adaptive Server IQ documentation If you are also using Adaptive Server IQ Multiplex please refer to the following documentation for fur...

Page 6: ...nt information added after the release of the product CD use the Sybase Technical Library Product Manual Web site To access release bulletins at the Technical Library Product Manual Web site 1 Go to support sybase com 2 Click the Manuals tab 3 From the drop down list choose Adaptive Server IQ 4 In the window on the right under Platform Specific Collections choose the appropriate version link 5 In ...

Page 7: ...ved handling of out of space conditions If you run out of disk space Adaptive Server IQ now provides better assurance that you will be able to add space where you need it Adaptive Server IQ now reserves space so you can issue a create dbspace command You can control the amount of space reserved by setting two new options Reserved_Main_DBSpace_MB and Reserved_Temp_DBSpace_MB For details on these op...

Page 8: ...OIN_SYNTAX option on page 28 Zero length data storage If the length of a CHAR or VARCHAR cell is zero and the cell is not NULL Adaptive Server IQ creates a zero length cell not a NULL This change reverts to the original behavior in Adaptive Server IQ 12 as documented Chapter 5 of the Adaptive Server IQ Administration and Performance Guide 6 2 Changed functionality in Adaptive Server IQ 12 03 1 Thi...

Page 9: ...ries by multiple users A new more efficient database format More intelligent index loading allowing faster loads for many indexes Syntactic compatibility with Adaptive Server Anywhere allowing Anywhere users to build on their existing knowledge base as they begin to use Adaptive Server IQ Support for Transact SQL Additional query and view support Improved front end support See the Adaptive Server ...

Page 10: ...ese tables instances are not dropped until you disconnect This problem will be corrected in a future release As a workaround in this release put an explicit drop table temp_table_name at the end of the procedure For example the following procedure has been corrected so that the automatically created temporary table temp0 is explicitly dropped and will not be replicated create procedure foo begin s...

Page 11: ...his server limitation is addressed 7 3 Adaptive Server IQ Operations This section reports problems with Adaptive Server IQ operations 7 3 1 Output to file in DBISQL There are known problems in using the output to file feature from a DBISQL session that will cause a server to abort Do not use this feature in this release Instead put filename on the end of a select statement The cases that cause the...

Page 12: ...ory segment To resolve this issue subsequent servers should be started with the parameter hs which will turn off shared memory For example start_asiq kent cfg kent db hs All servers can be started with shared memory off Note that if shared memory is turned off then you will have to connect to the server using TCP IP instead of using the default shared memory connection 7 3 5 Unsupported terminal t...

Page 13: ...icrosoft Windows 95 or 98 client systems running the Directory Services Editor DSE utility from Sybase Central returns the following error Error Executing dsedit Check that the applications location is in your Search path The Directory Services Editor is not installed with the Adaptive Server IQ Client Components on Windows 95 or Windows 98 systems If you do not install Open Client you cannot run ...

Page 14: ... and Feature Guide for Digital UNIX for a list of client application tools that have been certified with Adaptive Server IQ version 12 4 0 9 Documentation updates and clarifications This section contains information omitted from documentation and new information that needs emphasis It is organized into the following categories Startup shutdown and connection Data definition DDL Data manipulation D...

Page 15: ...IX or the asiqsrv12 command line override the database cache parameters that are set using the SET OPTION command If the user has not set the parameters the defaults are 8MB for the main cache size and 4MB for the temporary cache size The following new server switches override values of the database options Two other new command line options iqfrec and iqdroplks let you force database recovery and...

Page 16: ...te the number of users expected to connect to the server 9 1 5 gn command line option The gn server parameter should be added to Chapter 2 of the Adaptive Server IQ Reference Manual and to Controlling performance from the command line in Chapter 2 of the Adaptive Server IQ Administration and Performance Guide Function Set the number of execution threads for the Catalog Store Syntax asiqsrv12 gn in...

Page 17: ...ection Introduction to connections the second paragraph incorrectly states that select db name can be used to confirm a connection The correct syntax is select db_name to display the current database or select db_name database_id to display any database you specify 9 1 8 Using ODBC connectivity with UNIX On UNIX systems Adaptive Server IQ installation installs only the ODBC driver and not the driv...

Page 18: ...ks for the odbc ini file in 1 The directory specified by the ODBCHOME environment variable 2 The directory specified by the HOME environment variables 3 The path The database server ignores the ODBC_HOME ODBC_INI and ODBCINI environment variables 9 1 10 stop_asiq utility The stop_asiq utility is new for version 12 03 1 Use this command to shut down an Adaptive Server IQ server and close all user c...

Page 19: ...SIQ 12 processes s found UID PID PPID C STIME TTY TIME CMD aharring 13870 13869 0 Jun 07 1 20 asiqsrv12 c 16m gc 6000 gd all gr 6000 gm 10 gp 4096 ti 4400 tl 300 asi teds 16255 5843 0 Jun 07 pts 27 0 03 asiqsrv12 gd all tl 120 gm 10 n writer gp 4096 x tcpip port 6907 jamesfay 3683 1 0 23 32 14 14 33 asiqsrv12 gc 6000 gr 6000 gm 10 n express_daily_49765 c 16M gp 4096 gd a redisch 5486 1 4 10 10 40 ...

Page 20: ...en you stop the server with the DBSTOP command you need to specify the same parameters as when you started the server Using a configuration file to start the server ensures that you will be able to find these parameters when you need them 9 1 13 Disconnect details omitted The following information was omitted from the Introduction to Adaptive Server IQ It should be added in Chapter 4 after the sec...

Page 21: ...pecified uppercase 9 2 Data definition DDL 9 2 1 Change to CREATE DATABASE statement The defaults and minimums for the IQ SIZE and TEMPORARY SIZE parameters of CREATE DATABASE for operating system files only were stated incorrectly in the Adaptive Server IQ Reference The default and minimum value depend on IQ PAGE SIZE Table 2 Default and minimum sizes of IQ and Temporary Stores IQ PAGE SIZE IQ SI...

Page 22: ...oks like a regular table it has a name columns rows and indexes Unlike a regular table however a JVT cannot be created modified or deleted by the user It is created by Adaptive Server IQ as a result of a Create Join Index for internal processing purposes It is deleted when the user does a Drop Join Index Error messages relating to join virtual tables appear only when a user tries to use or modify ...

Page 23: ...his and also does not mention that file extensions are needed when operating system files are used 9 2 7 Error documenting IQ PATH In Chapter 3 of the Adaptive Server IQ Administration and Performance Guide the example shown in the section titled Specifying an IQ PATH should be corrected The example shows where Adaptive Server IQ puts the message and log files when you do not specify the Temporary...

Page 24: ...b Adaptive Server IQ TM is no longer waiting for more dbspace 1999 02 05 16 14 52 0002 20902 Insert completed Index CDR DBA CDR_FE_FP_lf These messages are only informational This is normal behavior as the server verifies that the dbspace does not yet exist and no action is required Such messages will be suppressed in a future release 9 2 10 SIZE clause of CREATE DBSPACE This clarification affects...

Page 25: ...eral errors described below Privileges needed to create a join index The book states that you must be the owner of a table or the DBA to create alter or synchronize a join index that includes that table If you are not the DBA you need to be owner of the table and have RESOURCE authority in order to create a join index One to many relationship The following changes apply to the subsection One to ma...

Page 26: ...ou can see in the following example for every row in customer there are potentially many matching rows in sales_order select sales_order id sales_order cust_id customer lname from sales_order customer where sales_order cust_id customer id cust_id id lname 2583 101 101 Devlin 2001 101 101 Devlin 2005 101 101 Devlin 2125 101 101 Devlin 2206 101 101 Devlin 2279 101 101 Devlin 2295 101 101 Devlin 2002...

Page 27: ...fter 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 customer id UNENFORCED order_date datetime NOT NULL fin_code_id char 2 NULL REFERENCES DBA fin_code code UNENFORCED region char 7 NULL sales_rep integer NOT NULL REFERENCES DBA employee emp_id...

Page 28: ...r 1 Introduction to Adaptive Server IQ should read Intelligent query processing Adaptive Server IQ uses index only access plans to process only the data needed to satisfy any type of query 9 4 3 STRIP load option clarification The following clarification should be added to the Adaptive Server IQ Administration and Performance Guide The STRIP load option does not apply to ASCII fixed width inserts ...

Page 29: ... Support for joins between stores or databases This section clarifies current support for joins between stores or between databases Any joins within a given IQ database are supported This means that you can join any system or user tables in the Catalog Store with any tables in the IQ Store in any order Joins of IQ tables with tables in an Adaptive Server Enterprise database are supported under the...

Page 30: ...with any remote table the local IQ table must appear first in the FROM clause This means that the local IQ table is the outermost table in the join The CHAR data type is incompatible between Adaptive Server Anywhere and Adaptive Server IQ when the database is built with BLANK PADDING OFF If you want to perform cross database joins between ASA and ASIQ tables using character data as the join key us...

Page 31: ...ure that the load can succeed even if it means incurring degradation in load performance However every load requires some minimal amount of heap memory If the user specified limit is lower than this amount the user will receive the following error The load user approximately x MB but only y MB was specified where x is a rough approximation of the minimum amount of heap memory needed and y is the u...

Page 32: ...ir action 9 4 7 Corrections to INSERT LOCATION These corrections apply to the Adaptive Server IQ Reference Manual and the Adaptive Server IQ Administration and Performance Guide If you plan to use the INSERT LOCATION command syntax 3 of the INSERT command the Adaptive Server Enterprise server you are connecting to must exist in the interfaces file on the local machine Also the syntax in the Adapti...

Page 33: ... Syntax REPEAT string expression numeric expression Parameters string expression The string expression to be repeated integer expression The number of times the string expression will be repeated Examples The statement SELECT REPEAT repeat 3 returns the value repeatrepeatrepeat Standards and compatibility SQL 92 Vendor extension Sybase REPEAT is not supported in Adaptive Server Enterprise but REPL...

Page 34: ...ng space Two new options let you control the amount of space reserved for adding more disk space RESERVED_MAIN_DBSPACE_MB Function Controls the amount of space Adaptive Server IQ reserves for adding dbspaces to the main IQ Store Allowed Values Integer greater than zero in megabytes Default 1 Description This option lets you control the amount of space Adaptive Server IQ sets aside space in your ma...

Page 35: ...nd and add a dbspace of at least 4 MB 9 5 3 Insufficient disk space The following changes apply to the section Insufficient disk space in Appendix A Troubleshooting Hints of the Adaptive Server IQ Administration and Performance Guide Replace the second and third Action items with the following text Actions Try to connect to the database from a new connection If this works you know that the databas...

Page 36: ...ection and create the needed temporary dbspace Once this is done the other create dbspace for main completes and all waiting connections resume running 9 5 4 Forced recovery and leaked space recovery This section describes parameters options and procedures that allow forced recovery and leaked space recovery This information will be added to the Adaptive Server IQ Administration and Performance Gu...

Page 37: ...t restricted access gives the DBA greater control over inadvertent opens of the database Leaked space recovery Either through system failure or as a result of opening a database with forced recovery a database s allocation map may not reflect the true allocation of its usage When this occurs we say that it has leaked storage Adaptive Server IQ 12 4 0 resolves this problem by adding the ability to ...

Page 38: ...ied during server startup runs CheckStorage Fix Checks for leaked blocks and corrupt database pages by walking all the block maps in the system and reading every database page If no error is detected resets database free list to calculated allocation map If an error is found it is reported in iqmsg file but free list is not altered If iqdroplks was not specified during server startup runs GatherSt...

Page 39: ...ing the various block maps or object directories that make up the database The underlying database pages that make up the actual tables and indexes are not read Therefore successful completion of sp_iqcheckdb using option 1 or 3 does not guarantee absolutely that the database is not corrupt 2 Runs CheckStorage Checks for leaked blocks and corrupt database pages by walking all the block maps in the...

Page 40: ...e database To recover leaked space In the event that the default option dbcc_option 0 cannot recover the free list and a previous backup is not available use the following procedure to try to recover the database 1 Start the server with the iqdroplks switch in the start_asiq command on UNIX or asiqsrv12 command 2 Set dbcc_option to 3 as a temporary option SET TEMPORARY OPTION dbcc_option 3 3 Run t...

Page 41: ...ure to include iqfrec in the dbstop command 4 Start up the server again with the iqdroplks switch asiqsrv12 iqdroplks foo gd dba foo db 5 Connect to the database It will be fully allocated 6 To correct the leaks created by the forced recovery open run sp_iqcheckdb on foo 9 5 5 Improved output in stored procedures Several stored procedures now display output in units that are easier to understand T...

Page 42: ...e required to set this option Default 0 characters Description This option allows the database administrator to impose a minimum length on all new passwords for greater security Existing passwords are not affected Example Set the minimum length for new passwords to 6 bytes SET OPTION PUBLIC MIN_PASSWORD_LENGTH 6 9 5 8 Transaction Log utility The following information on the Transaction Log utility...

Page 43: ...ction Log utility from the system command line using the DBLOG command line utility The DBLOG command line utility Syntax dblog switches database file Switches Description The DBLOG command line utility allows you to display or change the name of the transaction log or transaction log mirror associated with a database You can also stop a database from maintaining a transaction log or mirror or sta...

Page 44: ...w be used to open the database and reset the defaults 9 5 10 Setting Prefetch_Buffer_Limit option The SET option PREFETCH_BUFFER_LIMIT defines the number of cache pages available to Adaptive Server IQ for use in prefetching the read ahead of database pages This option has a default value of 20 which can degrade multi user performance Sybase recommends that you set this option to 0 for multi user a...

Page 45: ...user s connect privileges any database objects owned by that user are deleted without warning Likewise if you use the stored procedure sp_dropuser to drop a user database objects owned by that user are dropped without warning To avoid this problem remove objects owned by a user or assign them to another user before issuing REVOKE CONNECT or sp_dropuser Note Procedures like sp_dropuser provide mini...

Page 46: ... is OFF and STACKER is ON Adaptive Server IQ will wait for a pre determined amount of time waiting for the next tape to be auto loaded The number of tapes supplied along with the SIZE option will be used to determine whether there is enough space to store the backed up data Do not use this option with 3rd part media management devices 9 5 15 Cleaning up after abnormal exit Killing processes may re...

Page 47: ... choose the log with the largest number for the desired server Issue a tail f command to view the log contents For example tail f demo 002 srvlog When you run start_asiq specify the Z option to enhance the log file with additional information about connections This will help new users or those troubleshooting connection problems To check if a particular server is running log into the machine where...

Page 48: ... the connection to Adaptive Server IQ is closed If you leave multiple queries open you could consume more connections than you realize eventually preventing other users from connecting to Adaptive Server IQ since the number of configured connections would be exceeded With Business Objects you should change Password_Encryption 1 to Password_Encryption 0 9 6 3 Creating attribute tables for PowerBuil...

Page 49: ...the Sybase subsidiary in your area Before you contact Technical Support Before contacting Technical Support collect the following operating system platform For example Sun Solaris 2 6 SPARC front end tool or connectivity protocol used For example Brio Query configuration type single user or multi user message log file File named dbname iqmsg in the directory located by default where you started th...

Page 50: ...Technical Library CD allows you to access technical information about your product in an easy to use format Refer to the Technical Library Installation Guide in your documentation package for instructions on installing and starting Technical Library Technical Library Web site includes the Product Manuals site which is an HTML version of the Technical Library CD that you can access using a standard...

Page 51: ...w the instructions associated with the SupportPlusSM Online Services entries If you are not a registered SupportPlus user and you want to become one You can register by following the instructions on the Web To use SupportPlus you need 1 A Web browser that supports the Secure Sockets Layer SSL such as Netscape Navigator 1 2 or later 2 An active support license 3 A named technical support contact 4 ...

Page 52: ...52 ...

Reviews: