Iq 12 4 3 release engineering
Download
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
Iq 12 4 3 release engineering

11

IQ 12.4.3 Release – Engineering


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


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: <base col> Contains (‘key1’, ‘key2’…)

  • Disjunctive <base col> Contains (‘key1’) or <base col> 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/<servername>.00n.stderr

        • asiq12/logfiles/<servername>.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:

    • <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


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