Sybase IQ 12.4.0 manual

Copyright 1989-1999 by Sybase, Inc. All rights reserved. Sy base, the Sybase logo, Data W orkbench, InfoMaker , PowerBuilder , Powersoft, SQL Advantage, SQL Debug, T ransact-SQL, Adaptive Server , Adaptive Server Any- where, Adaptive Server Enterpr i se, Adaptive Server Enterprise Monitor ,AnswerBase, Backup Server , ClearCon nect, Client-Library ,[...]

Requir ed Operati ng Syste m Patches Adaptiv e Server IQ 12.4 .0 2 Release Bu lletin fo r Digital UNIX • Digital Unix V4.0f Note The pr oduct name Digital UNIX has recently been changed to Tru64 UNIX. However the Adaptive Server IQ documen tation still uses the ol d Digital UNIX name. The follo wing op eratin g system command shows the le vel of [...]

Adaptiv e Server IQ 12 .4.0 Converti ng 12.0.x d atabases to 1 2.4.0 Release B ulletin for Dig ital UNIX 3 T o obtain patch es, down load them fr om the website at http://www .service.digit al.com/ or contact your Digital rep resentative. Note These patches r equire that you reb uild your ker nel. Y ou must ha lt your system and then b oot from the[...]

Inser t into ta ble from remote S QL databa se not su pported Adaptiv e Server IQ 12.4 .0 4 Release Bu lletin fo r Digital UNIX Y ou mus t run upgra siq.sql once for each 12.0.x database to upgrade it to 12.4.0. Note For Adap ti ve Server IQ versions 1 2 .0.3, 12.03.1, an d 12.4.0: Due to a timing related issue, th e database server process wil l s[...]

Adaptiv e Server IQ 12.4.0 Setting th e LD_LIB RARY_ PATH Envi ronment V ariabl e Release B ulletin for Dig ital UNIX 5 SUBSTRING(COL2 ...) 3. Inst allatio n Instruc tions For complete instal lation inst ructions, see Adaptive Server IQ Insta llation and Featur e Guide for Compaq Digi tal UNIX . 3.1 Setting the LD_L IBRAR Y_P A TH Env ironment V ar[...]

Acce ssing Cu rrent Rel ease Bul letin Info rmation Adaptiv e Server IQ 12.4.0 6 Release Bu lletin fo r Digital UNIX Dependin g on how you use Adapti ve Server IQ, you may also need to re fer to the documentation for Adaptive Ser ver Anywhere. Ref er to the V ersion 6.02 edition of Adaptive Server Anywhere do cumentation on the Sybase T echnical Li[...]

Adaptiv e Server IQ 12 .4.0 Changed fun ctionali ty in Ad aptive Server IQ 12.4.0 Release B ulletin for Dig ital UNIX 7 In version 1 1.x, you could outpu t the query plan usin g the command IQ SET QUERYINF O 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[...]

Improv ed stor ed proce dure outp ut Adaptiv e Server IQ 12.4 .0 8 Release Bu lletin fo r Digital UNIX • Improved s tored procedure output Stored pr ocedures now disp lay output in unit s that are easier to und erstand. • Minimum password l ength Database administrators can specify a minimum password length, t o discourage eas ily dis covered p[...]

Adaptiv e Server IQ 12.4.0 stop_asi q util ity Release B ulletin for Dig ital UNIX 9 • -iqmc sets the size o f the main buf fer cache • -iqtc sets the size of the temporar y buffer cache See “Additio ns to the start_asi q or asiqsrv1 2 command- line options ” on page 15 for detail s . • stop_asiq u tility Y ou can st op the server u s ing[...]

Data defi nition Adaptiv e Server IQ 12.4 .0 10 Release Bu lletin fo r Digital UNIX 7.1 Dat a definition This section reports problem s with data definitio n. 7.1.1 T emporary t ables in procedures When you include an automatically created tempo rary table in a procedure, the table should be d ropped automatically when the procedure completes. In A[...]

Adaptiv e Server IQ 12.4.0 Large IN subquer ies Release Bul letin for Di gital UNIX 11 •= A L L •! = A L L If you use an uns upported query in this gr oup, Adap tive Server IQ returns an error like the following: Feature, ANY, not yet implemented Queries of this type can always be expr es sed in terms of IN subqueries or scalar subqueri es usin[...]

User -defi ned vari able issu e Adap tive Serv er IQ 12. 4.0 12 Release Bu lletin fo r Digital UNIX T o avoid truncated output, increase the length by setti ng the truncation_length option as fo llo ws: SET OPTION DBO.TRUNCATION_LENGTH = 80 Alternatively , from the DBISQL menu select Co mmand → Options and en ter a higher value for Limit D isplay[...]

Adaptiv e Server IQ 12.4.0 Sybase Central Release Bulleti n for Digital UNIX 13 7.4 Sybase Centra l This secti o n rep ort s pr obl ems wit h t h e Adapti v e Ser ver IQ plug-in for Sy base Centr al. 7.4.1 Pr oblems with Add User-defined Dat a T ype wizard When you create a user-def ined data type, the Adaptive Server IQ plu g-in allows you to spec[...]

Data Type colum n in Table Editor re tains foc us Adaptiv e Server IQ 12.4.0 14 Release Bu lletin fo r Digital UNIX When you use the Add In dex Wizar d to create a new index, the Choose IQ Index T ype screen lets you specify t he number of record s that shoul d be added before sending a notification message. The properties s creen for the index doe[...]

Adaptiv e Server IQ 12.4.0 Additio ns to the s tart_as iq or as iqsrv12 c omman d-line opti ons Release Bulleti n for Digital UNIX 15 • Remove all limits , and then set limits o n the stack size and descrip tors. T o do so, go to the C shell and issue these commands: % unlimit % limit stacksize 8192 % limit descriptors 4096 Note Note that un limi[...]

-gm c ommand li ne option Adaptiv e Server IQ 12.4.0 16 Release Bu lletin fo r Digital UNIX The -iqmt switch is not set by star t_asiq on Digital UNIX s ystems. The setti ng listed in the Ad aptive S erver I Q Adm inistr ation an d Per f or manc e Guid e and the Adaptive Se rver IQ Ins tallation and Featur e Guide is incorrect. The default value is[...]

Adaptiv e Server IQ 12.4.0 Confirmi ng connec tions Release Bulleti n for Digital UNIX 17 The followin g note should be added to Chapter 2, “The Databas e Server ,” after the des cript ion of th e -v server switch. Note In order to d isplay the vers ion on 64–bit platf o rms, you must do the following: •R u n start_asiq -v instead, which wi[...]

Using a .odbc .ini file Adaptiv e Serve r IQ 12.4 .0 18 Release Bu lletin fo r Digital UNIX If Adaptive Server IQ does not detect the presen ce of an ODBC driv er manager , it will u se ~/.o dbc.ini for data source information. Otherwise, it will query the driver manager for data source inform ation. 9.1.9 Using a .odbc .i ni file The following co [...]

Adaptiv e Server IQ 12 .4.0 Additio n to STOP DATA BASE s tatement Release Bulleti n for Digital UNIX 19 Y --------------------------------------------- --------- - If you t ype Y (yes), th e following message displ ays: --------------------------------------------- --------- - Shutting down asiqsrv12 ......... (server shu tdown). -----------------[...]

Erro r in DBST OP exam pl e Adaptiv e Ser ve r IQ 12.4.0 20 Release Bu lletin fo r Digital UNIX The fol lowing inform ation should be added to the STOP DA T ABASE statement in the Adaptive Server IQ Refer ence . When you issue STOP DATABASE database-name the database-na me is the name specified in the -n parameter when the database is started, o r [...]

Adaptiv e Server IQ 12 .4.0 -Z switch must be up percase Release Bulleti n for Digital UNIX 21 The parameter string in the sam ple configuratio n file shown in Chapter 2 of the Adaptive Server IQ Administration a nd Performance Guide should be corrected to: -n Elora -c 16M -x tcpip(port=2367) -gm 10 -gp 4096 pathmydb.db 9.1.15 -Z sw itch must be u[...]

MESSAGE PATH Adap tive Server IQ 12 .4.0 22 Release Bu lletin fo r Digital UNIX 9.2.2 MESSAGE P A TH In the MESSAGE P A TH clause of CREA TE DA T ABASE 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 Refer ence . 9.2.3 Low_Disk functions as High_Group LD (Low_D[...]

Adaptiv e Server IQ 12.4.0 Error doc umenting IQ PATH Release Bulleti n for Digital UNIX 23 • Specify a dif ferent pathname (for ex ample, /iqfiles/main /iq and /iqfiles/temp /iq or dif ferent raw pa rtitions • Omit TEMPORAR Y P A TH when y ou create the d a tabase. In this c ase, the temporary store is created i n the same path as the Catalog [...]

SIZE cl ause of CRE ATE DB SPACE Adaptiv e Server IQ 12.4.0 24 Release Bu lletin fo r Digital UNIX 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 an d checkpoint messages, the following e rror displays: 1999-02-05 16:12:53 0001 Exception [...]

Adaptiv e Server IQ 12.4.0 Rec ommen ded index types Release Bulleti n for Digital UNIX 25 9.2.12 Reco mmended index t ypes In the Adaptive Server I Q Admini s tra t ion and Per f ormance Guide , Chapter 4, “Adaptive Ser ver IQ Indexes,” in T able 1–2, Quer y type/index, the reco mmen ded in dex ty pes fo r COUNT and range pred i cate are inc[...]

Changes to “Usin g join in dexes” Adaptiv e Server IQ 12.4.0 26 Release Bu lletin fo r Digital UNIX If the join colum n is made up of more than o ne column, th e combination of the values must be unique . For example, in the asiqdemo database, the id in th e customer table and the cust_id in the sal es_or der table each contain a customer ID. T[...]

Adaptiv e Server IQ 12 .4.0 Changes to “Usi ng join i ndexe s” Release Bulleti n for Digital UNIX 27 Creating st ar joins The followi ng should be ad ded to Ch apter 4 of the Ad aptive Server IQ Administ ratio n and Perf ormance Guide just after the figure that shows the sale s_or der table in a star join. Y ou can create this tab le using the [...]

Error in DISK_S TRIPING def ault Adaptiv e Server IQ 12.4.0 28 Release Bu lletin fo r Digital UNIX 9.3 Error in DISK_STRIPI NG default The Adaptive Server IQ Refer ence c ontains an error in the General Database Options table in Chapter 5, “Database Options.” Th e DISK_STRIPING o ption defau lt should be ON. 9.4 Dat a manipulation (DM L) 9.4.1 [...]

Adaptiv e Server IQ 12 .4.0 Default Release Bulleti n for Digital UNIX 29 Default ON Description This o ption reports a syntax error for those queries co ntaining outer joins that have ambiguou s syntax due to the p r esence of duplicate correlation name s on a null-suppl ying table. The following join claus e illustrates the kind o f query that is[...]

New and c hanged general database options Adaptiv e Server IQ 12.4 .0 30 Release Bu lletin fo r Digital UNIX • In order to join a local ASE table with a remote IQ 12 table, the ASE version must be 1 1.9.2, and you must use the server class cd ASAnywhere. Note Adaptive Server Enterpr i se 1 1.9.2 introduces new server c l asses to be used for acce[...]

Adaptiv e Server IQ 12 .4.0 Descrip tion Release Bulleti n for Digital UNIX 31 Description This opt i o n s pecifi es an up per b ou nd ( in MB) on the amou nt of heap m e mory subsequent loads can use. Th e default setting, 0 (zero) , means that there is no upper boun d, and Adaptive Serv er IQ can use as much heap memo ry as necessary to perform [...]

Descr ip tio n Adaptiv e Ser ve r IQ 12.4.0 32 Release Bu lletin fo r Digital UNIX Descriptio n Fo r join s with in a query , the IQ optimizer has a choice of several algo rith ms for processing the join . This optio n allows you to override th e optimizer’ s cost—based decision when choosing the algorithm to use. It does not override internal [...]

Adaptiv e Server IQ 12.4.0 String funct ion REPE AT is su pported Release Bulleti n for Digital UNIX 33 { SELECT * FROM CUSTOMERS } Note This syntax is not currentl y supported on Digital UNIX. 9.4.8 S tring function REPEA T is supported The string functio n REPEA T is supported in Adaptiv e Server IQ version 12.x. It is documented as follows: REPE[...]

Using ISNUL L() and COALES CE( ) Adaptiv e Serve r IQ 12.4. 0 34 Release Bu lletin fo r Digital UNIX The NUMBER(*) function i s not sup p orted and shou ld be deleted from the Adaptive S erver IQ Refer ence Manual . 9.4.1 1 Using ISNULL() and COALESCE() ISNULL() and C O ALESCE() can be use d to convert NULL values into something else. If these are [...]

Adaptiv e Server IQ 12 .4.0 Default Release Bulleti n for Digital UNIX 35 Default 1 Description This option lets you control the amount of space Adaptive S erver IQ sets aside space in your temporary I Q Store, so that if you run out of disk space there yo u can add a new db space. Adaptive Server IQ sets aside 1 MB by default. This value is usuall[...]

Effect o f check points Adaptiv e Server IQ 12.4.0 36 Release Bu lletin fo r Digital UNIX Temporary IQ Blocks Used:,163 of 61 44, 2%, Max Block#: 97 If the percen t age of block s used is in the ni neties, you need to add more disk space with the CREA TE DBSP ACE command. In this ex ample, 82% of the Main IQ Blocks and 2% of the T emporary IQ Block[...]

37 • Increme ntal backup s are disabled After t he database is opened in forced recover y mode, incremen tal backups are disabled. The n ext backup must be a full backup . Doing a full backu p reenables incrementals. • Forced recove ry affects al l databa ses opened The forced recovery parameter applies to all op ens of the database wh ile the [...]

38 T o verify that the data is not corrupt and set the d atabase storage to its actua l value, you start the server with the -iqdroplks sw itch and connect to the database. Y ou then s et the option dbcc_option and ru n the sp_iqcheck db stored proce dure . Dependin g on result s, you may need to reset thi s opti on and rerun t h e procedure. See t[...]

39 Runnin g sp_ iqche ckdb In order to recover leaked storage within a database, first star t the server with the -iqdroplks switch in the asiqsrv1 2 command. Next, connect to you r database and i ssue the command: sp_iqcheckdb The stored procedu re reads all storage within the database. On s uccessful completion, it updates the database free list [...]

40 The dbcc_option settings of 0 and 3, w hen combined with the serv er option -iqdroplks , update the free list if no errors are detected. In order to perfor m this function, write tran s actions are prevented before and during the runnin g of sp_iqche ckdb . The stored procedure ensures this by taking the appropriate locks during its ex ecution. [...]

41 Exam ple As sume that the DBA cannot successfully open and con nect to database foo , because of reported IQ errors during database o pen and recovery . T o 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 err or while IQ is tryi ng to op en a dat[...]

42 9.5.6 SP_IQST A TUS now displays IQ Page Size The sp_iqs tat u s st o red pr ocedu re now di s plays the IQ page size in addit io n to the block size. For ex ample, Adaptive Server IQ used to display: Block Size: 512/2bpc It now dis pla ys: Page Size: 1024/512blksz/2bpc 9.5.7 MIN_P ASSWORD_LENG TH option The MIN_P ASSWORD_LENG TH option is new i[...]

43 When you use the RESTORE statement to move and/or rename a database, you can rename all of the files except the transaction log. Tr ansactions continue to be written to th e old log file na me, in the location wh ere the Catalog S tore file (the .db file) is located after the d at abase is restored. When you renam e or move all o t her fi l es i[...]

44 Set the nam e of the trans action log file (-t ) This option sets a filename, including an optio nal directory p ath, for a new transactio n log. If the databas e is not currently using a trans action log, it starts u sin g one. If the database is already using a trans action log, it ch ang es to using th e new file as its transaction lo g. 9.5.[...]

45 The same command can also be used to add a new u ser . 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 . Y ou do not receive a warning becau s e this behavio r is considered normal. T h is behavior differs fr om pre-V ersion [...]

46 9.5.14 Changes to BACKUP st atem ent In th e Adaptive Server IQ Refer ence , the SIZE and ST ACKER option description s in the BACKUP statement should read: SIZE option Specifies maximum tape o r f ile capacity ( so me platf orms do not reliably detect end-of-tape markers) . No volume used on the corresponding device should be sho rter tha n thi[...]

47 ipcrm -m mid1 -m mid2 ... -s sid1 -s sid2 ... For example: % ipcrm -m 40965 -s 5130 -s36682 9.5.16 Monitoring server acti v i t y It may be helpful, especially for new users, to mon itor server activity . When you start a server with the st a rt_ asiq utility , server activity is logged in an ASCII text file placed in the d irectory defined by $[...]

48 9.6 Client applications 9.6.1 ODBC AutoPreCommit omitted The ODBC Auto PreCommit opt ion was om itted from the Adaptive Server IQ Ref erence . T urning this option ON causes each statement to do a C OMMIT before executio n (as opposed to a C OMMIT af ter execution for the AutoCommit opt ion ). The default for AutoPreCommit is OFF . Set the AutoP[...]

49 9.7.1 Adapt ive Server IQ plug-in help r eflects M ultiplex support Adaptive Server IQ Multiplex 12.4.0 is a sep arate product from Adaptive Server IQ 12.4.0. If you have not pu rchased or instal led Adaptive Se rver IQ Multiplex, the functio n ality described in th e online help top ic Managing Multiplexes is not available. 10. T ech nical Supp[...]

50 • Outpu t from sp_iqst atus procedure Y ou may find addi tional h elp from t he Sybase onli ne suppo rt database, MySuppo rt. MySupport l ets you search thr ough closed su pport cases, lates t software bulletins, resolved and known problems, using a view customized for your need s . Y ou can ev en open a technical supp ort case online. MySuppo[...]

51 techinfo.sybase.co m 2 In the Browse section, click on the What’ s Hot entry . 3 Explo re your area o f interest: Hot Docs co vering vario us topics, o r Hot Links to T echnical News, Certificatio n Reports, Partner Certifications, and so on . ❖ If you are a r egistered SupportPlus user: 1 Point yo ur W eb b rowser to T echnical Documents at[...]

52[...]