Transcript No Slide Title

New Features of
IDS.2000
Paul Brown,
INFORMIX Software
(The DBMS Company)
Where are we? How did we get here?
8.1
8.2
7.2
7.3
9.1
1996
2
8.3
1997
9.21
1998
1999
9.3
2000Informix
user.conference
OK. So What Have We Got Then ?
• What are the differences between 7.X and 9.X?
• Features and Functionality
• Product Quality and Performance Changes
• What are the Differences between 9.x and 9.21?
• Lots of stuff now works, so we can talk about it
• And What is going on in 9.3 (IDS.2001)
• Lots of cool stuff
• And By The Way, What’s IDS.2000 and IIF.2000?
• Oh. Yes. Let’s talk about that
Informix
user.conference
3
Differences: 7.X and IDS.2000
• Nothing is Lost by Moving to IDS.2000
• IDS.2000 (9.21) functionality is super-set of 7.31
• IDS.2000 (9.21) is at least as fast as 7.31
• Upgrade does not require unload/reload
• There are Some Gotchas
• Changed indexing strategy: bigger indices
• Slightly more memory per connection
• and Overall Quality is Good
• Lots of Bug Fixes
Informix
user.conference
4
New Features: OODLES of ‘em
• New features, and new functionality
• User-defined Types and Functions (Extend SQL)
• Java for database procedures, types (SQL-J)
• In 9.21, can use JDK 1.2 inside the server.
• All of the DataBlades™ work for you
• Data Management Improvements and Innovations
• 128 character identifiers
• Fuzzy check-pointing
• ON SELECT Triggers
• Java Classes as Database Stored Procedures
Informix
user.conference
5
Slightly Technical Example . . .
• Extensions that can be
bought or are bundled
with the product:
DataBlades, BladeLets,
etc.
• Build customized
extensions that bring
business logic into the
SQL processor.
7
-- Object-Relational System
-CREATE TABLE HospitalRooms (
Name
VARCHAR(128)
NOT NULL,
Equipment Document
NOT NULL
Where
GeoPoint
NOT NULL,
Occupied SET( Period NOT NULL )
);
-- “Show me all rooms available for the next five
-- days within 45 miles of the patient’s
-- location equipped with a defilibrator?”
-SELECT H.Name
FROM HospitalRooms H, Patients P
WHERE Contains ( H.Where,
Circle( P.Location, ‘45 Miles’ ) )
AND P.Name = ‘Fred Flintstone’
AND DocContains ( H.Equipment,“defilibrator” )
AND NOT Booked ( H.Bookings,
Period (TODAY,TODAY+5));
--- Key value: Simply by adding extra parts to the
Informix
-- query you can ask different questions.
The
-- alternative is to write, compile anduser.conference
debug ‘C’.
How is it Built?
Queries
Logic
Response
Data
Informix
user.conference
UDTs & UDRs Example - 1/2
CREATE ROW TYPE BirthDay (
MMonth
INTEGER NOT NULL,
DDay
INTEGER NOT NULL,
FromLeapYear
BOOLEAN NOT NULL
);
CREATE FUNCTION BirthDay ( Arg1 date )
RETURNING BirthDay
RETURN BirthDay ( MONTH(Arg1),
DAY(Arg1),
FromLeapYear(YEAR(Arg1));
END FUNCTION;
Informix
user.conference
UDTs & UDRs Example - 2/2
CREATE TABLE Customers (
Name
PersonName
NOT NULL,
BirthDate
DATE
NOT NULL
);
INSERT INTO Customers VALUES
( PersonName(‘McDoogal’,’Fred’), ‘02/28/1970’);
INSERT INTO Customers VALUES
( PersonName(‘Lo’,’Anne’), ‘02/29/1968’);
SELECT Print(C.Name)
FROM Customers C
WHERE BirthDay(C.BirthDate)=BirthDay(‘02/28/1999’);
SELECT Print(C.Name)
FROM Customers C
WHERE BirthDay(C.BirthDate) =BirthDay(‘02/29/2000’);
Informix
user.conference
What are DataBlade™ Modules?
• Custom Extensions Created by Partner
• Market leaders, or Technology leaders
• Informix’s Customers can Buy/Use
• Text - What customers said “help”?
• GIS - Where are my customers?
• ESRI, MapInfo, Geodetic.
• ‘The Little Things’
• T-SQL, Bill-of-Materials Processing, SQL extras
• Extend Existing SQL-92 Database
Informix
user.conference
Shared Statement Cache
• New Functionality for 9.2
• Query plans shared between connections
• Big benefits for sites with lots of users and only
one or two applications
SELECT F.First,
B.Second
FROM Foo F, Bar B
WHERE F.A = B.A
AND F.C = :C1
AND B.C = :C2;
SELECT F.First,
B.Second
FROM Foo F, Bar B
WHERE F.A = B.A
AND F.C = :C1
AND B.C = :C2;
CPU-VP
Plan ( C1, C2 )
{
}
Informix
user.conference
ON SELECT Triggers
• Unique to Informix
IDS.2000
• Audit and monitor
READs, in addition to
writes
• Like all TRIGGERS,
imposes a overhead on
normal processing. Will
be a multiple ( >2X)
depending on what you
do in triggered action.
CREATE TABLE Articles (
Id
INTEGER
Title
Article_Title
.
Text
Document
.
);
NOT NULL,
NOT NULL,
NOT NULL,
CREATE TRIGGER Article_Billing
SELECT OF Text ON Articles
REFERENCING OLD AS Read_Row
FOR EACH ROW (
( INSERT INTO Billing
VALUES
( USER, NOW, Read_Row.Id)
);
Informix
user.conference
User-defined Aggregates
Two Kinds of UDA
• Overloading of Builtin SQL Aggregates.
SUM, MIN, MAX, AVG,
VARIANCE.
• Requires that you
supply appropriate
low-level functions
• CREATED
Aggregates
Compute a result
from a single pass
over a large number
of data values
SELECT O.Customer,
SUM( O.Physical_Quantity )
FROM Orders O
GROUP BY O.Customer;
CREATE AGGREGATE regression
WITH (
INIT
= regression_init,
ITER
= regression_iter,
COMBINE = regression_combine,
FINAL
= regression_final
)
SELECT E.Dept_Name
regression ( ROW (E.salary,
(TODAY - E.DOB) )
)
FROM Employees E
GROUP BY E.Dept_Name;
Informix
user.conference
Java-in-the-Server
Java VM in Engine
• Can use any one of
several, of which the
SunSoft JDK1.2 is
best
• Fully SQL-J
compliant
• Includes JDBC
callbacks in UDR
• Big Advantage: Java
Class logic inside, or
outside the DBMS.
execute procedure install_jar(
"file:/UDO/extend/name/person_name.jar",
”person_name_jar");
create function person_name (
first_name lvarchar,
second_name lvarchar )
returns person_name
with ( not variant, parallelizable )
external name
‘person_name_jar:p_name.new( String,String)’
language java;
grant usage on function
person_name ( lvarchar, lvarchar) to public;
Informix
user.conference
Quick Review of 9.3 (Due, 2001)
• Continues the O-ing of the RDBMS
• ER Replication for All UDTs (Just in-row in 9.21)
• Fragment by UDR, CHECK UDR, etc
• New Java Implementation
• Krak-2 Architecture - Change in JNI Bindings
• Chunk Size Change - 2 Terabyte Chunks
• Change page header to permit more pages
• Truncate Table
• No more “UNLOAD/DROP/CREATE/LOAD”
• More features in future releases
• Other stuff in the code, just not QA’d yet
Informix
user.conference
16
Summary and Conclusions
• IDS.2000 is Road Ahead from 7.X for OLTP
• Code and Skills Investment is Preserved
• 9.21 performance is as good or better than 7.X
• Lots of New Features
• Extensibility (Object-Relational DBMS)
• User-defined Types and User-defined Functions
• DataBlade Extensions
• Query-Language Innovation
• Long Identifiers
• ON SELECT TRIGGER
• Data Management Innovation
• Fuzzy Checkpointing
• Shared Statement Cache
17
Informix
user.conference