Скачать презентацию New Features in IDS 9 40 Why it Скачать презентацию New Features in IDS 9 40 Why it

00e327ce5e2d5fc5ef65d3b87fb11603.ppt

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

New Features in IDS 9. 40: Why it is Time to Upgrade Jonathan Leffler New Features in IDS 9. 40: Why it is Time to Upgrade Jonathan Leffler STSM, Informix Database Engineering IBM Data Management 30 October 2002 -11 -08 10: 00

Disclosure Information In November 2002, this presentation contained information about an unreleased product and Disclosure Information In November 2002, this presentation contained information about an unreleased product and this slide contained cautionary information about future plans, subject to change. In November 2005, this warning was removed. The content of the presentation was not revalidated (in November 2005) to ensure that its prognostications were actually valid. 1 2002 -11 -08 10: 00

Agenda § Status of IDS 9. 40 § Key New Features • Big Chunks Agenda § Status of IDS 9. 40 § Key New Features • Big Chunks • ER and HDR coexisting • Encrypted communications § Miscellaneous Features § Application Features § Forthcoming Attractions • Fix Packs, IDS 9. 50, IDS 9. 60 § Questions and Answers 2 2002 -11 -08 10: 00

Status of IDS 9. 40 – November 2002 § Feature content frozen • No Status of IDS 9. 40 – November 2002 § Feature content frozen • No new features can be added. • It is always possible something might be removed. § Entered beta in October 2002 § Anticipated release date late Q 1 2003 • Subject to change. 3 2002 -11 -08 10: 00

Key Features § § Big Chunks ER and HDR coexistence Encrypted communications Improved performance Key Features § § Big Chunks ER and HDR coexistence Encrypted communications Improved performance 4 2002 -11 -08 10: 00

Big Chunks § § Disks were small when On. Line was developed Big disks Big Chunks § § Disks were small when On. Line was developed Big disks were about 100 MB in 1988 A 2 GB chunk size didn’t seem too constricting But Moore’s Law and exponential growth apply • • • 1990 1992 1994 1996 1997 1998 1999 2000 2002 — — — — — 320 MB 1 GB 2 GB 4 GB 9 GB 18 GB 36 GB 72 GB 120 GB (still partitionable into 2 GB chunks) (too big to be split into 2 GB chunks) (logical volume managers needed) (available for PC at $200, Fry’s Electronics) 5 2002 -11 -08 10: 00

Big Chunks § The 2 GB limit on chunk size is too painful § Big Chunks § The 2 GB limit on chunk size is too painful § So we’ve removed it • New upper limit per chunk is 4 TB. – For 2 KB pages – Could be doubled to 8 TB later • Good through about 2010. § And increased the number of chunks • New upper limit is 32767 – Could be doubled to 65535 later § The new upper limit on the size of an IDS instance is: • 128 petabytes (128 * 1015 bytes) 6 2002 -11 -08 10: 00

Big Chunks § How big is 128 PB? • • Very big! US Library Big Chunks § How big is 128 PB? • • Very big! US Library of Congress holds about 10 TB printed material. 128 PB is about 12, 800 times the material in the LOC. At current prices, it would cost about $3 B to buy the disks. – Before mirroring or RAID is added in • At a load rate of 1 TB per hour, it would take about 15 years to fill a 128 petabyte database. VERY BIG! 7 2002 -11 -08 10: 00

Big Chunks § How to record extra info in disk pages? • Option 1: Big Chunks § How to record extra info in disk pages? • Option 1: Extend page header – Bad for conversion and reversion • Option 2: Revise meaning of fields in current page header – Good for conversion and reversion – Page timestamp originally used for integrity validation – Disk systems provide that automatically – Enables ‘on the fly’ conversion § The page header now has: • 32 -bit page number • 16 -bit chunk number • 16 -bit checksum – Instead of 32 -bit pg_addr and 32 -bit pg_stamp 8 2002 -11 -08 10: 00

Big Chunks § Corresponding changes in other structures • Physical log • Chunk descriptors Big Chunks § Corresponding changes in other structures • Physical log • Chunk descriptors • Blob spaces § Chunk sizes and offsets now 64 -bit values § Utilities all compiled to handle large files • • • dbimport, dbexport, dbload, dbaccess onunload, onload onspaces oncheck ontape High Performance Loader 9 2002 -11 -08 10: 00

ER and HDR Coexistence § In IDS versions prior to 9. 40 • Use ER and HDR Coexistence § In IDS versions prior to 9. 40 • Use HDR (High availability Data Replication) for automatic failover between two substantially identical systems. • Use ER (Enterprise Replication) for controlled replication of subsets of data between many heterogeneous systems. § But cannot use both at once 10 2002 -11 -08 10: 00

ER and HDR Coexistence § In IDS 9. 40 • HDR systems can participate ER and HDR Coexistence § In IDS 9. 40 • HDR systems can participate in ER systems too. § Typically, one or more of the root nodes in the ER system would also run HDR • Note that systems communicating with such nodes must be running IDS 9. 40. • Hence, all root nodes would have to be running 9. 40 before any of them are converted to HDR too. § ER systems will contact the primary server of the HDR pair in ordinary operation • Will automatically contact the secondary if the primary fails. 11 2002 -11 -08 10: 00

Other ER and HDR Improvements § HDR now supports extensible data types • Replicates Other ER and HDR Improvements § HDR now supports extensible data types • Replicates logged smart blob spaces § ER now supports extensible data types § ER now has faster queue recovery • Dynamic Data. Sync § ER supports larger transactions • Up to 4 TB (from previous 2 GB) • But you shouldn’t have transactions that big! § Using dynamic logs feature of IDS 9. 30 § ER event class identifiers and messages for ALARMPROGRAM 12 2002 -11 -08 10: 00

Encrypted Communications § Previously, password encryption using CSS/CSM technology § Now (optionally) using CSS/CSM Encrypted Communications § Previously, password encryption using CSS/CSM technology § Now (optionally) using CSS/CSM to encrypt all conversations from client to server • Using cryptography libraries from Open. SSL. • Not using Open. SSL directly because of design of ASF. § Optionally encrypt ER traffic • Often sent over long-haul networks. • Protects data sent over Internet. • Not available for HDR in initial release. 13 2002 -11 -08 10: 00

Improved Performance § During development • Performance testing of most nightly builds. • Aggressively Improved Performance § During development • Performance testing of most nightly builds. • Aggressively attacking performance issues. § Internal benchmarking shows • 5 -10% improvement over 7. 3 x • Using variety of benchmarks – TPC-C – Vendor benchmarks 14 2002 -11 -08 10: 00

Miscellaneous Features § Rewritten B-Tree cleaning algorithms § Revised buffer priority management § Fractional Miscellaneous Features § Rewritten B-Tree cleaning algorithms § Revised buffer priority management § Fractional percentages for LRU_MIN_DIRTY and LRU_MAX_DIRTY § Use full size of tape devices § Rename chunk devices during restore § No libraries installed in /usr/lib by default § Add chunks when first chunk full § Restartable fast recovery § More DBSERVERALIASES § New default ALARMPROGRAM 15 2002 -11 -08 10: 00

Rewritten B-Tree Cleaning § Under previous versions • Single-threaded B-Tree cleaner • Could get Rewritten B-Tree Cleaning § Under previous versions • Single-threaded B-Tree cleaner • Could get overwhelmed by amount of work to do § Replaced by B-Tree Scanner • Prioritized workload • Multiple scanner threads possible § Alternative algorithms available • Leaf scan • Light range scan § Controlled by onmode • onmode –C [ start | stop ] {count} – Starts or stops N scanner threads • onmode –C [ threshold | range ] {size} • onmode –C [ low | high ] 16 2002 -11 -08 10: 00

Rewritten B-Tree Cleaning § Monitored by onstat • • onstat –C –C prof hot Rewritten B-Tree Cleaning § Monitored by onstat • • onstat –C –C prof hot clean range § Tuning • By default, B-Tree scanner threads run at low priority • Set priority high if they are falling behind • Increasing number of cleaner threads has little benefit when running at low priority 17 2002 -11 -08 10: 00

Revised Buffer Priority Management § Older system gave priority to • Memory-resident tables • Revised Buffer Priority Management § Older system gave priority to • Memory-resident tables • Index pages § Had four tier priority system • High, Medium Low, Low § New system has two tier priority system • • • High – using FIFO Low – using LRU Maximum amount of High is controllable Moves frequently used pages from low to high automatically Resets the use counters at checkpoints § Dynamically balances use of shared memory • Improves overall performance 18 2002 -11 -08 10: 00

Fractions for LRU_MIN_DIRTY § If you have 500, 000 buffers • • • 1% Fractions for LRU_MIN_DIRTY § If you have 500, 000 buffers • • • 1% of buffers with dirty pages is 5, 000 buffers. 1 MB must be written to disk at the checkpoint. Checkpoints take too long. Setting LRU_MIN_DIRTY to zero is not advisable. Systems with 1 TB main memory in couple of years. § IDS 9. 40 permits fractional values for • LRU_MIN_DIRTY • LRU_MAX_DIRTY § Helps minimize checkpoint times • Increases writes between checkpoints. • These are ‘wasted’ if a crash occurs. • Decreases writes at checkpoint. 19 2002 -11 -08 10: 00

Use Full Size of Tape Devices § Currently, must specify size of tape • Use Full Size of Tape Devices § Currently, must specify size of tape • Difficult to estimate capacity of tapes with compression • Error if specified size too big – wasted space when too small § A tape size of zero means: • Write until no room left on tape (or disk). • No more guessing how much compression you’ll get • Can use different size tapes during backup § Changed utilities include: • • • dbexport dbimport onload onunload ontape 20 2002 -11 -08 10: 00

Rename Chunks During Restore § Map chunk device names during cold restore • • Rename Chunks During Restore § Map chunk device names during cold restore • • Not available during warm restore. All chunk device names. Mapping on command line or in file. Primary and mirror chunks can be renamed too. § Allows recovery on similar systems • The sets of disk devices do not have to be identical. • Replacement disk devices after a disk crash. • Can fix mistakes in device naming: – Inappropriate use of version in pathname: • /ifmx/v 713/sbspace 03 – Direct use of device names instead of symbolic links. § Do a level 0 archive after recovery 21 2002 -11 -08 10: 00

No Libraries Installed in /usr/lib § IDS 9. 40 does not place any libraries No Libraries Installed in /usr/lib § IDS 9. 40 does not place any libraries in /usr/lib • Except for Java libraries under /usr/lib/informix on HP-UX. § Optical library specified by new parameter • OPTICAL_LIB_PATH in ONCONFIG file § HPL library specified by new parameter • HPL_DYNAMIC_LIB_PATH in … • Default: $INFORMIXDIR/lib/ipldd 09 a. so § ON-Bar library specified by new parameter • XBSA_LIBRARY_PATH in ONCONFIG § Smart Disk support removed § Don’t forget to clean up old Informix libraries • Once you’re sure they won’t be needed again. 22 2002 -11 -08 10: 00

Add Chunks When First Chunk Full § In prior versions of IDS • All Add Chunks When First Chunk Full § In prior versions of IDS • All chunk information had to be in first chunk of root dbspace. • If first chunk full, it was not possible to add new disk space. § With IDS 9. 40 • If there is no space left in first chunk of rootdbs. • Extra chunk information can be added to one of the other chunks in root dbspace. • Helps prevent conversion failures. • Helps support 32, 767 chunks. 23 2002 -11 -08 10: 00

Restartable Fast Recovery § In previous versions of IDS • Crashes during fast recovery Restartable Fast Recovery § In previous versions of IDS • Crashes during fast recovery could corrupt the system: – No fast recovery possible. – Restore from archive necessary. § In IDS 9. 40 • Physical logging is enabled during fast recovery: – Allows recovery to occur multiple times if necessary. – Physical log could overflow during recovery. – PLOG_OVERFLOW_PATH specifies directory where overflow pages are written. • Default: $INFORMIXDIR/tmp 24 2002 -11 -08 10: 00

More DBSERVERALIASES § Previously limited to 10 DBSERVERALIASES • Getting too tight for some More DBSERVERALIASES § Previously limited to 10 DBSERVERALIASES • Getting too tight for some customers • Distinguishing different services by different aliases § Limit increased to 32 • Multiple lines accumulate 25 2002 -11 -08 10: 00

New Default ALARMPROGRAM § When an event occurs • Logged to message log file. New Default ALARMPROGRAM § When an event occurs • Logged to message log file. • Information passed to a program: – Usually, but not necessarily, a shell script. § Previous default program only handled • Logical log complete: – By running an ON-Bar process to back it up. § New default has example handling for all events • Configuration section. • ‘Private’ section with code to analyze and report on events. • Tailor the configuration section to suit: – email addresses. – reporting levels. • Alter the private section if something isn’t done right for you. 26 2002 -11 -08 10: 00

Application Features § § § § § DESCRIBE INPUT ORDER BY values not in Application Features § § § § § DESCRIBE INPUT ORDER BY values not in select-list Sequence objects Triggers on views SQL-99 RIGHT OUTER JOIN UNION in sub-queries Names for return values Multiple OUT parameters Iterator functions in FROM clause 27 2002 -11 -08 10: 00

DESCRIBE INPUT § SQL-92 provides ESQL/C DESCRIBE statement: • Dynamic SQL • DESCRIBE [OUTPUT] DESCRIBE INPUT § SQL-92 provides ESQL/C DESCRIBE statement: • Dynamic SQL • DESCRIBE [OUTPUT] … – To describe the data returned to the client by the server • DESCRIBE INPUT – To describe the data types that should be sent by client to server § CSDK 2. 81 will (probably) provide DESCRIBE INPUT • Yielding an accurate type description when possible: – UPDATE Some. Table SET Some. Column = ? ; • Yielding a generic SQLHOSTVAR type description otherwise: – DELETE Some. Table WHERE Tother. Column = (Third. Column + ? ) / Some. Func(? ) 28 2002 -11 -08 10: 00

ORDER BY Values Not in Select-list § In SQL-92 • All columns in ORDER ORDER BY Values Not in Select-list § In SQL-92 • All columns in ORDER BY clause must be in select-list. § Most DBMS are less fussy • Result has essential ordering. § IDS 9. 40 allows this too. • Missing columns are added to select-list. • Data is sorted. • Extra columns are projected away before data returned. § Complex example: • • SELECT d. deptnum FROM dept d, emp e WHERE e. deptnum = d. deptnum GROUP BY d. deptnum ORDER BY AVG(e. salary) 29 2002 -11 -08 10: 00

Sequences § DB 2 has them; Oracle does too. § Now IDS 9. 40 Sequences § DB 2 has them; Oracle does too. § Now IDS 9. 40 has them • As well as SERIAL and SERIAL 8 § Syntax: • CREATE SEQUENCE seqname INCREMENT BY 4 START WITH 16 MAXVALUE 50 MINVALUE -10 CYCLE; • SELECT seqname. NEXTVAL FROM dual; – 16, 20, … • ALTER SEQUENCE seqname INCREMENT BY 24; • SELECT seqname. NEXTVAL FROM dual; – 44, 8, … § Not tied to a single table • Unlike a SERIAL column 30 2002 -11 -08 10: 00

Sequences § Can be used as a DEFAULT in a table: • CREATE TABLE Sequences § Can be used as a DEFAULT in a table: • CREATE TABLE New. Table • (Int. Col INTEGER DEFAULT seqname. NEXTVAL NOT NULL, • …) § Can be used in DML too: • • • UPDATE Some. Table SET col 1 = (SELECT seqname. NEXTVAL FROM dual), col 2 = (SELECT seqname. NEXTVAL FROM dual) WHERE pkcol = 23; Same value supplied to col 1 and col 2. In each row that is affected by update! § New system catalog table • syssequences 31 2002 -11 -08 10: 00

Triggers on Views § IDS 9. 40 permits creation of INSTEAD OF triggers on Triggers on Views § IDS 9. 40 permits creation of INSTEAD OF triggers on non-updatable views: • • • CREATE TRIGGER ins_viewname INSTEAD OF INSERT ON viewname REFERENCING NEW AS NEW FOR EACH ROW (EXECUTE PROCEDURE ins_basetables(new. value 1, new. value 2, new. value 3)); • Also for UPDATE or DELETE triggers. • Some minor limitations: – BEFORE or AFTER not permitted – FOR EACH ROW required – SELECT, WHEN, OF clauses not permitted 32 2002 -11 -08 10: 00

SQL-99 RIGHT OUTER JOIN § IDS 7. 3 x and 9. 2 x or SQL-99 RIGHT OUTER JOIN § IDS 7. 3 x and 9. 2 x or later supports LEFT JOIN: • SELECT * FROM Table 01 t 1 LEFT OUTER JOIN Table 02 t 2 • ON (t 1. col 01 = t 2. col 02 AND t 1. col 03 = t 2. col 04) § IDS 9. 40 also supports the RIGHT JOIN: • SELECT * FROM Table 01 t 1 RIGHT OUTER JOIN Table 02 t 2 • ON (t 1. col 01 = t 2. col 02 AND t 1. col 03 = t 2. col 04) § Reverses sense of outer join. 33 2002 -11 -08 10: 00

Other Join Types § CROSS JOIN • • a. k. a Cartesian product In Other Join Types § CROSS JOIN • • a. k. a Cartesian product In IDS 9. 40 SELECT * FROM Table. A CROSS JOIN Table. B Equivalent to – SELECT * FROM Table. A, Table. B § FULL JOIN • Fix pack § NATURAL JOIN • 9. 50? § UNION JOIN • Unlikely to be implemented • Deprecated in SQL-99 34 2002 -11 -08 10: 00

UNION in Sub-Queries § With IDS 9. 40, you can use UNION in many UNION in Sub-Queries § With IDS 9. 40, you can use UNION in many places where a simple SELECT can be used: • SELECT * FROM Some. Table • WHERE col 1 IN (SELECT c 1 FROM Another. Table • UNION • SELECT c 3 FROM Third. Table); § Can appear in FROM clause too. § And in collection sub-queries. 35 2002 -11 -08 10: 00

Names for Return Values § Stored procedures cannot name return values: • DB-Access says Names for Return Values § Stored procedures cannot name return values: • DB-Access says ‘(expression)’ or similar. § IDS 9. 40 permits return values to be named: • • CREATE PROCEDURE named_returns(n INTEGER) RETURNING INTEGER AS x, DECIMAL(24) AS y; … END PROCEDURE; § Notes: • Names have no significance within body of procedure. • Either all returned values have names or none do. § Improves output from DB-Access • More significantly, helps ODBC, JDBC, etc. 36 2002 -11 -08 10: 00

Multiple OUT Parameters § Previous versions of IDS • Only allowed one output parameter. Multiple OUT Parameters § Previous versions of IDS • Only allowed one output parameter. • It had to appear last in parameter list. § IDS 9. 40 allows multiple OUT parameters • CREATE FUNCTION multi_out(IN var 1 INT, OUT var 2 INT, OUT var 3 DECIMAL) RETURNING INT AS var 4; • The IN keyword is optional and assumed by default. • OUT parameters cannot convey information to function. § And multiple SLVs • Statement local variables • SELECT var 2, var 3 FROM Some. Table • WHERE col 1 > multi_out(col 3, var 2 # INT, var 3 # DECIMAL) 37 2002 -11 -08 10: 00

Iterator Functions in FROM Clause § Treat results of function as table: • SELECT Iterator Functions in FROM Clause § Treat results of function as table: • SELECT * • FROM TABLE(FUNCTION fibonacci(10)) AS vtab(vcol); § Function can return multiple columns 38 2002 -11 -08 10: 00

Anything Else? § Of course – lots of other changes § Bug fixes § Anything Else? § Of course – lots of other changes § Bug fixes § Relaxed limits • Number of parameters to stored procedures • Number of parameters to functional indexes • Maximum size of shared memory dumps § Spatial costing § Enhancements in ISA § Better GLS support • Unicode (ICU) • GB 18030 (China) § LVARCHAR(n) notation § Parallel query when using cursors WITH HOLD 39 2002 -11 -08 10: 00

Forthcoming Attractions § Fix Packs • Additional features: – Mostly smaller items • Bug Forthcoming Attractions § Fix Packs • Additional features: – Mostly smaller items • Bug Fixes. § Detailed planning for IDS 9. 50 • In progress – finish about end Q 1 2003. • Probable release date 2 H 2004. • Feature Requests being accepted: – Detailed explanation of why is most important. – Outline explanation of what. § Outline planning for IDS 9. 60 • Material that cannot be fitted into IDS 9. 50. • Possible release date 2005 -6. 40 2002 -11 -08 10: 00

Questions and Answers YOUR TURN! http: //www. ibm. com/software/data/informix Thanks for listening. 41 2002 Questions and Answers YOUR TURN! http: //www. ibm. com/software/data/informix Thanks for listening. 41 2002 -11 -08 10: 00

Contact Information Jonathan Leffler STSM, Informix Database Engineering IBM Data Management +1 650 -926 Contact Information Jonathan Leffler STSM, Informix Database Engineering IBM Data Management +1 650 -926 -6921 [email protected] ibm. com http: //www. ibm. com/software/data/informix 42 2002 -11 -08 10: 00