1 / 45

IQ 12.4.3 Release – Engineering

11. IQ 12.4.3 Release – Engineering. HIGHLIGHTS OF RELEASE 12.4.3. Direct Customers: - Multi-Column Index (Referential Integrity) - Compare Index - OLAP Extensions: Cube, Ranges in Group By, Standard Deviation, Variance

dyami
Download Presentation

IQ 12.4.3 Release – Engineering

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 11 IQ 12.4.3 Release – Engineering

  2. HIGHLIGHTS OF RELEASE 12.4.3 • Direct Customers: - Multi-Column Index (Referential Integrity) - Compare Index - OLAP Extensions: Cube, Ranges in Group By, Standard Deviation, Variance - Performance: Data Loads, Parallel Group by Hash, Prefetching - Update Command for Joins - CIS Support on Sun Platform

  3. HIGHLIGHTS OF RELEASE 12.4.3 • Web Support: - XML - Java Stored Procedures - Word Index - GUID Index (Binary Datatype)

  4. HIGHLIGHTS OF RELEASE 12.4.3 (continued) Multiplex: IBM 32 Support HP 64 Support Migration Simplification Elimination of small temp space set-up DBRemote Versioning Simplification of Converting a Reader to Writer Removal of 26 Drive Limit (NT) Simplification of moving writer to different machine Documented Failover Procedure Sybase Central - offline node support, browse buttons

  5. HIGHLIGHTS OF RELEASE 12.4.3 (continued) • High Volume Support: VLDB: Intermediate Versioning High Group Incremental Load Performance Aggregate Union Pushdown Query Performance for Views across Multiple Tables

  6. HIGHLIGHTS OF RELEASE 12.4.3 (continued) Other: Veritas Backup/Restore Support New Platform - HP 64 bit “Silent” Install for Client Machines OLE DB Connectivity Support Automatic printout of settings at start-up SA version 7.0.2 support

  7. 12.4.3 Query Engine

  8. Contents • Referential Integrity - Phase 1 • OLAP - Cube, StdDev, Variance • Update with joins • Word Index • Binary Datatype • Union view performance • IN Predicate improvements • Range Group By in Indexes

  9. Referential Integrity - Phase 1 • Phase 1: Unique multi-column High Group • Primary Key • Candidate Key • Still need to index each component column • Limit 255 bytes • Join optimizer users for arity and cardinality • Phase 2 will be non-unique MCHG • Phase 3 will be enforce RI

  10. OLAP • Group By Cube • Can uses all 3 algorithms for initial grouping • Limit 1e6 rows (Hash based secondary grouping) • Standard Deviation • Variance

  11. Update with Joins • Update T1From T1 Inner Join T2 on ( T1 x T2)Where … • T-SQL language extension • Can copy cells from one table to another • Matches T-SQL Delete statement

  12. Word Index • Treat a varchar, long varchar, char column as a nested relation • Supply delimiter characters • Keyword lists • URL components • CONTAINS predicate • Conjunctive: <base col> Contains (‘key1’, ‘key2’…) • Disjunctive <base col> Contains (‘key1’) or <base col> Contains (‘key2’)

  13. BINARY, VARBINARY datatypes • ASE compatible (Not ASA compatible) • stored as 2 nibbles per byte (use even lengths) • character like not integer like • HexToInt() and IntToHex() • High Group index only • Compatibility switch

  14. Union View Performance • Performance improvements for projection • Aggregate pushdown (patent applied for) • Select c1, Sum(c2) From (Select T1 Union All Select T2) Group By c1 • Select c1, Sum(c2’) From (Select C1, Sum(c2) From T1 Group By c1 Union All…) • Analogous parallel group by now on by default

  15. IN Predicate Improvements • Sort-based IN subqueries • above calculated/set option Max_Hash_Rows • Large IN-lists tested to 250k values • First round performance improvements to parser • Second round in 12.5

  16. Range Group By in Indexes • Performance improvements to single-table GB • Select * From T1Group By c1Where c1 Between lowVal And highVal • Scans btree evaluating predicate • char/varchar (ISO case respect only) • Cutoff to vertical based on groups meet restriction • Previously based on cardinality of GB column • Most predicates can be used • More queries executed in indexes in 12.4.3

  17. Miscellaneous Changes

  18. Other New Functionality • Events and Schedules • New stored procedures: sp_iqspaceused, sp_iqconnection, sp_iqtransaction • Extended store procedures • Intermediate Versioning • Prefetch • Out of Space

  19. Events and Schedules • You can automate routine tasks in ASIQ12.4.3 by adding an event to a database, and providing a schedule for the event. • Whenever one of the times in the schedule passes, a sequence of actions called an event handler is executed by the database server.

  20. The Create Event Command CREATE EVENT event-name ... [ TYPE event-type [ WHERE trigger-condition [ AND trigger-condition ], ... ] | SCHEDULE schedule-spec, ... ] ... [ ENABLE | DISABLE ] ... [ AT { CONSOLIDATED | REMOTE | ALL } ] ...[ HANDLER BEGIN ... END ] event-type BackupEnd | "Connect" | ConnectFailed | DatabaseStart | DBDiskSpace | "Disconnect" | GlobalAutoincrement | GrowDB | GrowLog | GrowTemp | LogDiskSpace | "RAISERROR" | ServerIdle | TempDiskSpace trigger-condition: event_condition( condition-name ) { = | < | > | != | <= | >= } value schedule-spec: [ schedule-name ] { START TIME start-time | BETWEEN start-time AND end-time } [ EVERY period { HOURS | MINUTES | SECONDS } ] [ ON { ( day-of-week, ... ) | ( day-of-month, ... ) } ] [ START DATE start-date ]

  21. An Example create table mysummary(dt datetime, users int, mainKB unsigned bigint, mainPC unsigned int, tempKB unsigned bigint, tempPC unsigned int) ; create event mysummary schedule sched_mysummary start time '00:01 AM' every 10 minutes handler begin declare mt unsigned bigint; declare mu unsigned bigint; declare tt unsigned bigint; declare tu unsigned bigint; declare conncount unsigned int; set conncount = db_property('ConnCount'); call sp_iqspaceused(mt,mu,tt,tu); insert into mysummary values( now(), conncount, mu, (mu*100)/mt, tu, (tu*100)/tt ); end ;

  22. New Stored Procedures • sp_iqspaceused Returns four out parameters containing the total and used dpspace for the main and temp stores, in Kbytes. • sp_iqconnection Returns a row of information for each active connection. • sp_iqtransaction Returns a row for each transaction control block in the IQ transaction manager. Rows are ordered by TxnID.

  23. sp_iqconnection ConnHandle Name Userid LastReqTime ReqType IQCmdType ========== ======== ====== ========================= ==================== ==================== 419740283 red2 DBA 2001-04-02 15:54:54.605 STMT_EXECUTE_IMM INSERT 640038605 blue1 DBA 2001-04-02 13:32:42.505 CURSOR_PREFETCH NONE 2094200996 DBA 2001-04-02 13:30:27.486 STMT_EXECUTE_ANY_IMM NONE 954498130 fromSCJ DBA 2001-04-02 15:55:02.787752 STMT_DROP NONE 167015670 blue2 DBA 2001-04-02 13:45:50.232752 STMT_DROP NONE 1306718536 DBA 2001-04-02 15:08:36.716 STMT_EXECUTE_ANY_IMM NONE 1779741471 ntJava2 DBA 2001-04-02 15:54:58.558752 STMT_DROP NONE 710225777 nt1 DBA 2001-04-02 15:56:02.729 CURSOR_OPEN IQUTILITYOPENCURSOR … LastIQCmdTime IQCursors LowestIQCursorState IQthreads TxnID ConnCreateTime … ======================= ========= =================== ========= ===== ======================== … 2001-04-02 15:54:54.630 1 EXECUTED 7 10701 2001-04-02 13:17:27.599 … 2001-04-02 13:32:42.295 1 FETCHING 2 10568 2001-04-02 13:21:19.953 … 2001-04-02 13:30:27.548 0 NONE 1 10604 2001-04-02 13:24:35.145 … 2001-04-02 15:55:02.590 0 NONE 1 10619 2001-04-02 13:31:26.001 … 2001-04-02 13:45:50.225 0 NONE 1 10678 2001-04-02 13:35:01.160 … 2001-04-02 15:09:30.320 0 NONE 1 16687 2001-04-02 13:37:50.814 … 2001-04-02 15:54:58.553 0 NONE 1 10676 2001-04-02 13:43:57.907 … 2001-04-02 15:56:02.755 0 NONE 1 10699 2001-04-02 14:05:15.748 … TempTableSpaceKB TempWorkSpaceKB IQconnID satoiq_count iqtosa_count CommLink NodeAddr LastIdle … ================ =============== ======== ============ ============ ======== ============= ====== … 68736 680 14 82 2031 TCPIP 157.133.82.17 9905 … 0 102592 17 76 360 local 606 … 0 0 18 397 688 TCPIP 157.133.83.151 8322 … 0 0 20 709 1541 TCPIP 157.133.83.151 5378 … 0 128 21 131 2082 local 5122 … 0 0 23 18313 821 TCPIP 157.133.83.151 10000 … 0 0 24 994 1667 TCPIP 157.133.83.151 1467 … 0 0 28 900 478 TCPIP 157.133.83.151 5473

  24. sp_iqtransaction Name Userid TxnID CmtID VersionID State ConnHandle IQConnID ======= ===== ====== ====== ========= ========== =========== ======== red2 DBA 10058 10700 10058 COMMITTED 419740283 14 blue1 DBA 10568 0 10568 ACTIVE 640038605 17 DBA 10604 0 10604 ACTIVE 2094200996 18 fromSCJ DBA 10619 0 10619 ACTIVE 954498130 20 blue2 DBA 10634 10677 10634 COMMITTED 167015670 21 ntJava2 DBA 10676 0 10676 ACTIVE 1779741471 24 blue2 DBA 10678 0 10678 ACTIVE 167015670 21 nt1 DBA 10699 0 10699 ACTIVE 710225777 28 red2 DBA 10701 0 10701 ACTIVE 419740283 14 DBA 16687 0 16687 ACTIVE 1306718536 23 … MainTableKBCreated MainTableKBDropped TempTableKBCreated TempTableKBDropped … ================== ================== ================== ================== … 0 0 65824 0 … 0 0 0 0 … 0 0 0 0 … 0 0 0 0 … 3960 152 0 0 … 0 0 0 0 … 2440 1992 0 0 … 0 0 0 0 … 0 0 2912 22096 … 0 0 0 0 … TempWorkSpaceKB TxnCreateTime Dbremote CursorCount SpCount SpNumber … =============== ======================== ======== =========== ======= ======== … 0 2001-04-02 13:17:27.612 0 1 3 2 … 102592 2001-04-02 13:27:28.491 0 1 1 0 … 0 2001-04-02 13:30:27.548 0 0 1 0 … 0 2001-04-02 13:31:27.151 0 0 24 262 … 0 2001-04-02 13:35:02.128 0 0 0 0 … 0 2001-04-02 13:43:58.805 0 0 39 408 … 128 2001-04-02 13:45:28.379 0 0 1 0 … 0 2001-04-02 14:05:15.759 0 0 42 413 … 680 2001-04-02 14:57:51.104 0 1 2 20 … 0 2001-04-02 15:09:30.319 0 0 1 0

  25. Extended Stored Procedures • The extended stored procedures are: • xp_cmdshell Executes a system command. • xp_msver Returns a string containing version information • xp_read_file Returns the contents of a file as a LONG BINARY variable • xp_write_file Writes data to a file from a SQL statement. • xp_sprintf Builds a string from a format string and a set of input strings. • xp_scanf Extracts substrings from an input string and a format string. • MAPI functions • xp_startmail Starts a mail session in a specified mail account by logging on the MAPI message system • xp_sendmail Sends a mail message to specified users • xp_stopmail Closes the mail session

  26. Intermediate Versions • ASIQ12.4.3 Simplex databases drop intermediate versions at the earliest possible time • Mpx databases use the old algorithm: versions are only dropped when the oldest version is no longer in use • sp_iqtransaction provides detailed version information (some interpretation required)

  27. Prefetch • All ASIQ12.4.3 database pages, except blockmaps, are prefetched when accessed sequentially • Data is almost always accessed sequentially • One central Prefetch Manager per iq store • Each application (e.g. sort, garray, a row of fp indexes) has a fixed read-ahead quota • Prefetch statistics returned by the cache_by_type and debug performance monitors

  28. Out of Space Handling • Main and Temp Reserved Space • should be set to 100MB or so • sp_iqconnection and sp_iqtransaction show who is using what, which versions exist, and why • User defined events may be written to monitor and manage space usage

  29. Multiplex

  30. IQ Engine Changes • Start without IQ Temp Store • limited functions; for use by Sybase Central • Multiplex features already loaded • iq.sql: stored procedures, schema • available via ALTER DATABASE UPGRADE • Treat dbremote connections properly for TLV • Windows NT: access more than 26 drives • use \\.\PhysicalDriveN naming scheme

  31. Sybase Central Changes • Integrate support for various actions: • Create Database • Start Database Server • Standardize on use of params.cfg file • from$ASDIR/scripts/default.cfg • Place database files anywhere • Browse button for local files

  32. Sybase Central Changes 2 • New Convert to Multiplex Wizard • replaces upgrade script, manual procedure • Create Query Server Wizard • specify IQ Temp characteristics • synchronizes and starts new server • New Replace Write Server Wizard • Stop Multiplex Wizard • checkbox to start write server in simplex • Off-line nodes support

  33. Off-line nodes support

  34. Documented procedures Truncate transaction log Fail-over after write server failure

  35. DDL Processing

  36. RELEASE 12.4.3 - DDL • Compare (CMP) Index • Enforced Multi-Column Primary Key • Multi-Column Unique HG Index • Incremental HG Load Performance • More Aggressive TLV

  37. Information Required by Engineering

  38. General Information • Hardware, memory and # of CPUs • Operating System and version • IQ version and EBF level • can get the version from the message file (*.iqmsg), sp_iqstatus or from ‘select @@version’ • the time the problem or situation occurred • to help match the log files with the problem

  39. General Information - cont. • Indicate if the problem can be reproduced in house • if so, provide all steps on how to reproduce • If sp_iqcheckdb was run, indicate what the dbcc_option option was set to and if any errors were detected • provide the output from sp_iqcheckdb if errors were encountered

  40. General Information - cont. • Output from sp_iqstatus • All IQ log files: • server logs • UNIX: • asiq12/logfiles/<servername>.00n.stderr • asiq12/logfiles/<servername>.00n.srvlog • NT: • copy of the console window • Multiplex environment: • message.log file for each reader and writer

  41. General Information - cont. • All IQ log files - cont: • <database_name>.iqmsg - IQ Message file • found in same directory as the .db file • stktrc.iq - stack trace file • found in the $SYBASE directory • Configuration file, if used • sometime named <database_name>.cfg • remember to get logs for each server in a Multiplex environment

  42. Query Related Problems • Run the problem query with the following settings: • set temporary option.query_plan=‘on’; • set temporary option.query_detail=‘on’; • set temporary option.query_plan_after_run =‘on’;

  43. Application problems • Operating System and version of client machine • application name and version • If Open Client and/or ODBC is being used, indicated what version • provide ODBC trace output of it is a connection problem or wrong results with ODBC application

  44. Application Problems - cont. • Indicate if problem happens when running query through dbisql or dbisqlc

  45. 12.4.3 End

More Related