iq 12 4 3 release engineering
Download
Skip this Video
Download Presentation
IQ 12.4.3 Release – Engineering

Loading in 2 Seconds...

play fullscreen
1 / 45

IQ 12.4.3 Release – Engineering - PowerPoint PPT Presentation


  • 133 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'IQ 12.4.3 Release – Engineering' - dyami


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
highlights of release 12 4 3
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 31
HIGHLIGHTS OF RELEASE 12.4.3
  • Web Support:

- XML - Java Stored Procedures

- Word Index - GUID Index (Binary Datatype)

highlights of release 12 4 3 continued
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 continued1
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 continued2
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

contents
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
referential integrity phase 1
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
slide10
OLAP
  • Group By Cube
    • Can uses all 3 algorithms for initial grouping
    • Limit 1e6 rows (Hash based secondary grouping)
  • Standard Deviation
  • Variance
update with joins
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
word index
Word Index
  • Treat a varchar, long varchar, char column as a nested relation
  • Supply delimiter characters
  • Keyword lists
  • URL components
  • CONTAINS predicate
  • Conjunctive: Contains (‘key1’, ‘key2’…)
  • Disjunctive Contains (‘key1’) or Contains (‘key2’)
binary varbinary datatypes
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
union view performance
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
in predicate improvements
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
range group by in indexes
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
other new functionality
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
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.
the create event command
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 ]

an example
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 ;

new stored procedures
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.
sp iqconnection
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

sp iqtransaction
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

extended stored procedures
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
intermediate versions
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)
prefetch
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
out of space handling
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
iq engine changes
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
sybase central changes
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
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
documented procedures
Documented procedures

Truncate transaction log

Fail-over after write server failure

release 12 4 3 ddl
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
general information
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
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
general information cont1
General Information - cont.
  • Output from sp_iqstatus
  • All IQ log files:
    • server logs
      • UNIX:
        • asiq12/logfiles/.00n.stderr
        • asiq12/logfiles/.00n.srvlog
      • NT:
        • copy of the console window
      • Multiplex environment:
        • message.log file for each reader and writer
general information cont2
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
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
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
Application Problems - cont.
  • Indicate if problem happens when running query through dbisql or dbisqlc
ad