Скачать презентацию IQ 12 4 3 Release Engineering Скачать презентацию IQ 12 4 3 Release Engineering

fec7b78abcc832c582e5f39b567dffba.ppt

  • Количество слайдов: 45

IQ 12. 4. 3 Release – Engineering IQ 12. 4. 3 Release – Engineering

HIGHLIGHTS OF RELEASE 12. 4. 3 • Direct Customers: - Multi-Column Index (Referential Integrity) 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

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

HIGHLIGHTS OF RELEASE 12. 4. 3 (continued) Multiplex: IBM 32 Support HP 64 Support 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

HIGHLIGHTS OF RELEASE 12. 4. 3 (continued) • High Volume Support: VLDB: Intermediate Versioning 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

HIGHLIGHTS OF RELEASE 12. 4. 3 (continued) Other: Veritas Backup/Restore Support New Platform - 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

12. 4. 3 Query Engine 12. 4. 3 Query Engine

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

Referential Integrity - Phase 1 • Phase 1: Unique multi-column High Group –Primary Key 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

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

Update with Joins • Update T 1 From T 1 Inner Join T 2 Update with Joins • Update T 1 From T 1 Inner Join T 2 on ( T 1 x T 2) Where … • T-SQL language extension • Can copy cells from one table to another • Matches T-SQL Delete statement

Word Index • • Treat a varchar, long varchar, char column as a nested Word Index • • Treat a varchar, long varchar, char column as a nested relation Supply delimiter characters Keyword lists URL components CONTAINS predicate Conjunctive: Contains (‘key 1’, ‘key 2’…) Disjunctive Contains (‘key 1’) or Contains (‘key 2’)

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

Union View Performance • Performance improvements for projection • Aggregate pushdown (patent applied for) Union View Performance • Performance improvements for projection • Aggregate pushdown (patent applied for) –Select c 1, Sum(c 2) From (Select T 1 Union All Select T 2) Group By c 1 –Select c 1, Sum(c 2’) From (Select C 1, Sum(c 2) From T 1 Group By c 1 Union All…) • Analogous parallel group by now on by default

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

Range Group By in Indexes • Performance improvements to single-table GB • Select * Range Group By in Indexes • Performance improvements to single-table GB • Select * From T 1 Group By c 1 Where c 1 Between low. Val And high. Val • 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

Miscellaneous Changes Miscellaneous Changes

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

Events and Schedules • You can automate routine tasks in ASIQ 12. 4. 3 Events and Schedules • You can automate routine tasks in ASIQ 12. 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.

The Create Event Command CREATE EVENT event-name. . . [ TYPE event-type [ WHERE 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 Backup. End | "Connect" | Connect. Failed | Database. Start | DBDisk. Space | "Disconnect" | Global. Autoincrement | Grow. DB | Grow. Log | Grow. Temp | Log. Disk. Space | "RAISERROR" | Server. Idle | Temp. Disk. Space 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 ]

An Example create table mysummary(dt datetime, users int, main. KB unsigned bigint, main. PC An Example create table mysummary(dt datetime, users int, main. KB unsigned bigint, main. PC unsigned int, temp. KB unsigned bigint, temp. PC 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('Conn. Count'); call sp_iqspaceused(mt, mu, tt, tu); insert into mysummary values( now(), conncount, mu, (mu*100)/mt, tu, (tu*100)/tt ); end ;

New Stored Procedures • sp_iqspaceused Returns four out parameters containing the total and used 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 Txn. ID.

sp_iqconnection Conn. Handle Name Userid Last. Req. Time Req. Type IQCmd. Type ===== ========================= sp_iqconnection Conn. Handle Name Userid Last. Req. Time Req. Type IQCmd. Type ===== ========================= 419740283 red 2 DBA 2001 -04 -02 15: 54. 605 STMT_EXECUTE_IMM INSERT 640038605 blue 1 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 from. SCJ DBA 2001 -04 -02 15: 55: 02. 787752 STMT_DROP NONE 167015670 blue 2 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 nt. Java 2 DBA 2001 -04 -02 15: 54: 58. 558752 STMT_DROP NONE 710225777 nt 1 DBA 2001 -04 -02 15: 56: 02. 729 CURSOR_OPEN IQUTILITYOPENCURSOR … … … … … Last. IQCmd. Time IQCursors Lowest. IQCursor. State IQthreads Txn. ID Conn. Create. Time ============== ============== 2001 -04 -02 15: 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 … Temp. Table. Space. KB Temp. Work. Space. KB IQconn. ID satoiq_count iqtosa_count Comm. Link Node. Addr Last. Idle … ======== ============ ======= … 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

sp_iqtransaction Name Userid Txn. ID Cmt. ID Version. ID State Conn. Handle IQConn. ID sp_iqtransaction Name Userid Txn. ID Cmt. ID Version. ID State Conn. Handle IQConn. ID ======= ========= ==== red 2 DBA 10058 10700 10058 COMMITTED 419740283 14 blue 1 DBA 10568 0 10568 ACTIVE 640038605 17 DBA 10604 0 10604 ACTIVE 2094200996 18 from. SCJ DBA 10619 0 10619 ACTIVE 954498130 20 blue 2 DBA 10634 10677 10634 COMMITTED 167015670 21 nt. Java 2 DBA 10676 0 10676 ACTIVE 1779741471 24 blue 2 DBA 10678 0 10678 ACTIVE 167015670 21 nt 1 DBA 10699 0 10699 ACTIVE 710225777 28 red 2 DBA 10701 0 10701 ACTIVE 419740283 14 DBA 16687 0 16687 ACTIVE 1306718536 23 … Main. Table. KBCreated Main. Table. KBDropped Temp. Table. KBCreated Temp. Table. KBDropped … ================== … 0 0 65824 0 … 0 0 0 0 … 3960 152 0 0 … 2440 1992 0 0 … 0 0 2912 22096 … 0 0 … Temp. Work. Space. KB Txn. Create. Time Dbremote Cursor. Count Sp. Number … ==================== ======== … 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 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

Extended Stored Procedures • The extended stored procedures are: –xp_cmdshell –xp_msver –xp_read_file –xp_write_file –xp_sprintf Extended Stored Procedures • The extended stored procedures are: –xp_cmdshell –xp_msver –xp_read_file –xp_write_file –xp_sprintf –xp_scanf Executes a system command. Returns a string containing version information Returns the contents of a file as a LONG BINARY variable Writes data to a file from a SQL statement. Builds a string from a format string and a set of input strings. 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

Intermediate Versions • ASIQ 12. 4. 3 Simplex databases drop intermediate versions at the Intermediate Versions • ASIQ 12. 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)

Prefetch • All ASIQ 12. 4. 3 database pages, except blockmaps, are prefetched when Prefetch • All ASIQ 12. 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

Out of Space Handling • Main and Temp Reserved Space – should be set Out of Space Handling • Main and Temp Reserved Space – should be set to 100 MB 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

Multiplex Multiplex

IQ Engine Changes Start without IQ Temp Store • limited functions; for use by 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 \. Physical. Drive. N naming scheme

Sybase Central Changes Integrate support for various actions: • Create Database • Start Database 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

Sybase Central Changes 2 New Convert to Multiplex Wizard • replaces upgrade script, manual 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

Off-line nodes support Off-line nodes support

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

DDL Processing DDL Processing

RELEASE 12. 4. 3 - DDL • Compare (CMP) Index • Enforced Multi-Column Primary 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

Information Required by Engineering Information Required by Engineering

General Information • Hardware, memory and # of CPUs • Operating System and version 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

General Information - cont. • Indicate if the problem can be reproduced in house 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 was set to and if any errors were detected – provide the output from sp_iqcheckdb if errors were encountered

General Information - cont. • Output from sp_iqstatus • All IQ log files: – General Information - cont. • Output from sp_iqstatus • All IQ log files: – server logs § UNIX: vasiq 12/logfiles/. 00 n. stderr vasiq 12/logfiles/. 00 n. srvlog § NT: vcopy of the console window § Multiplex environment: vmessage. log file for each reader and writer

General Information - cont. • All IQ log files - cont: – <database_name>. iqmsg General Information - cont. • All IQ log files - cont: – . 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 . cfg • remember to get logs for each server in a Multiplex environment

Query Related Problems • Run the problem query with the following settings: – set 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’;

Application problems • Operating System and version of client machine • application name and 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

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

12. 4. 3 End 12. 4. 3 End