1 / 43

New Features in IDS 9.40: Why it is Time to Upgrade

New Features in IDS 9.40: Why it is Time to Upgrade. Jonathan Leffler STSM, Informix Database Engineering IBM Data Management. Disclosure Information.

delphine-manella
Download Presentation

New Features in IDS 9.40: Why it is Time to Upgrade

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. New Features in IDS 9.40:Why it is Time to Upgrade Jonathan Leffler STSM, Informix Database Engineering IBM Data Management

  2. 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.

  3. 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

  4. 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 Q1 2003 • Subject to change.

  5. Key Features • Big Chunks • ER and HDR coexistence • Encrypted communications • Improved performance

  6. Big Chunks • Disks were small when OnLine 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 — 320 MB • 1992 — 1 GB • 1994 — 2 GB • 1996 — 4 GB • 1997 — 9 GB (still partitionable into 2 GB chunks) • 1998 — 18 GB (too big to be split into 2 GB chunks) • 1999 — 36 GB (logical volume managers needed) • 2000 — 72 GB • 2002 — 120 GB (available for PC at $200, Fry’s Electronics)

  7. 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)

  8. 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 $3B 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. VERYBIG!

  9. 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

  10. 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

  11. 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

  12. 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.

  13. 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 DataSync • 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

  14. 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 OpenSSL. • Not using OpenSSL 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.

  15. Improved Performance • During development • Performance testing of most nightly builds. • Aggressively attacking performance issues. • Internal benchmarking shows • 5-10% improvement over 7.3x • Using variety of benchmarks • TPC-C • Vendor benchmarks

  16. 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

  17. 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 ]

  18. Rewritten B-Tree Cleaning • Monitored by onstat • onstat –C prof • onstat –C hot • onstat –C clean • onstat –C 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

  19. Revised Buffer Priority Management • Older system gave priority to • Memory-resident tables • Index pages • Had four tier priority system • High, Medium 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

  20. 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.

  21. 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

  22. 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/v713/sbspace03 • Direct use of device names instead of symbolic links. • Do a level 0 archive after recovery

  23. 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/ipldd09a.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.

  24. 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.

  25. 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

  26. 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

  27. 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.

  28. 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

  29. 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 SomeTable SET SomeColumn = ?; • Yielding a generic SQLHOSTVAR type description otherwise: • DELETE SomeTable WHERE TotherColumn = (ThirdColumn + ?) / SomeFunc(?)

  30. 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)

  31. Sequences • DB2 has them; Oracle does too. • Now IDS 9.40 has them • As well as SERIAL and SERIAL8 • 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

  32. Sequences • Can be used as a DEFAULT in a table: • CREATE TABLE NewTable • (IntCol INTEGER DEFAULT seqname.NEXTVAL NOT NULL, • …) • Can be used in DML too: • UPDATE SomeTable • SET col1 = (SELECT seqname.NEXTVAL FROM dual), • col2 = (SELECT seqname.NEXTVAL FROM dual) • WHERE pkcol = 23; • Same value supplied to col1 and col2. • In each row that is affected by update! • New system catalog table • syssequences

  33. 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.value1, new.value2, new.value3)); • Also for UPDATE or DELETE triggers. • Some minor limitations: • BEFORE or AFTER not permitted • FOR EACH ROW required • SELECT, WHEN, OF clauses not permitted

  34. SQL-99 RIGHT OUTER JOIN • IDS 7.3x and 9.2x or later supports LEFT JOIN: • SELECT * FROM Table01 t1 LEFT OUTER JOIN Table02 t2 • ON (t1.col01 = t2.col02 AND t1.col03 = t2.col04) • IDS 9.40 also supports the RIGHT JOIN: • SELECT * FROM Table01 t1 RIGHT OUTER JOIN Table02 t2 • ON (t1.col01 = t2.col02 AND t1.col03 = t2.col04) • Reverses sense of outer join.

  35. Other Join Types • CROSS JOIN • a.k.a Cartesian product • In IDS 9.40 • SELECT * FROM TableA CROSS JOIN TableB • Equivalent to • SELECT * FROM TableA, TableB • FULL JOIN • Fix pack • NATURAL JOIN • 9.50? • UNION JOIN • Unlikely to be implemented • Deprecated in SQL-99

  36. UNION in Sub-Queries • With IDS 9.40, you can use UNION in many places where a simple SELECT can be used: • SELECT * FROM SomeTable • WHERE col1 IN (SELECT c1 FROM AnotherTable • UNION • SELECT c3 FROM ThirdTable); • Can appear in FROM clause too. • And in collection sub-queries.

  37. 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.

  38. 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 var1 INT, OUT var2 INT, OUT var3 DECIMAL) RETURNING INT AS var4; • The IN keyword is optional and assumed by default. • OUT parameters cannot convey information to function. • And multiple SLVs • Statement local variables • SELECT var2, var3 FROM SomeTable • WHERE col1 > multi_out(col3, var2 # INT, var3 # DECIMAL)

  39. 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

  40. 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) • GB18030 (China) • LVARCHAR(n) notation • Parallel query when using cursors WITH HOLD

  41. Forthcoming Attractions • Fix Packs • Additional features: • Mostly smaller items • Bug Fixes. • Detailed planning for IDS 9.50 • In progress – finish about end Q1 2003. • Probable release date 2H 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.

  42. Questions and Answers YOUR TURN! http://www.ibm.com/software/data/informix Thanks for listening.

  43. Contact Information Jonathan Leffler STSM, Informix Database Engineering IBM Data Management +1 650-926-6921 jleffler@us.ibm.com http://www.ibm.com/software/data/informix

More Related