Experiences with database extensibility:

The existing server extensibilities in modern DBMSs have been critical in our company’s development of software products that improve database performance for certain scientific computing applications. We are planning to develop other products that will utilize an extensible database engine, and so we would be grateful for feedback on the following questions as they pertain to your experience in developing IDS applications: 1. What types of extensibility (i.e., data types, functions/methods, indexing, etc.) are available and convenient to use in practice? 2. What types of extensibility (possibly already available in other DBMSs) are currently missing in IDS? 3. To what extent was your choice of IDS as a development platform based primarily on its extensibility features?

ERIC DAVIES said: > --===============1407085584== > > The existing server extensibilities in modern DBMSs have been critical in > our > company’s development of software products that improve database > performance > for certain scientific computing applications. We are planning to develop > other products that will utilize an extensible database engine, and so we > would be grateful for feedback on the following questions as they pertain > to > your experience in developing IDS applications: > > 1. What types of extensibility (i.e., data types, functions/methods, > indexing, > etc.) are available and convenient to use in practice? User Defined Types User Defined Routines / Functions Indexes on the above User Defined Aggregates Virtual Table Interface Virtual Index Interface > 2. What types of extensibility (possibly already available in other DBMSs) > are > currently missing in IDS? Nothing I have needed to date. > 3. To what extent was your choice of IDS as a development platform based > primarily on its extensibility features? Not at all. However, it was very easy to solve an awkward business problem by bolting in a UDT and solve other problems by implementing functional indexes. I have also used non-SPL UDRs to address some requirements. All of these problems COULD have been solved less elegantly by using pre-extensibility IDS, but at great cost, both in terms of effort and performance. -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent." -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

ERIC DAVIES wrote: > The existing server extensibilities in modern DBMSs have been critical in our > company’s development of software products that improve database performance > for certain scientific computing applications. We are planning to develop > other products that will utilize an extensible database engine, and so we > would be grateful for feedback on the following questions as they pertain to > your experience in developing IDS applications: > > 1. What types of extensibility (i.e., data types, functions/methods, indexing, > etc.) are available and convenient to use in practice? > I prepared this for a client recently. Since I didn't charge for compiling the list, here it is for posterity. There are a rather long list of new features that were introduced in 9 and later in IDS 10 that one is no able to take advantage of while constrained to the features offered in IDS 7.3x. The more significant of these include: * _Opaque types_ Ability to create business specific data types that the engine does not need to interpret, but which are meaningful to applications or to users. For example an warehouse application could define a type wh_locator type as a structure containing several fields including an item's RFID tag id, bin and shelf. IDS does not need to access the contents of the structure, but an application can load the column's contents directly into a binary data structure and access it. * _Distinct types_ Ability to define new business specific types which are range restrictions on existing types. Distinct types can be based on built-in or user defined types including row types and opaque types. Distinct types are strongly typed so that one must define type type casts to convert to other standard data types. An example might be a dept code type which is defined as a 9 digit character string which only accepts numeric digits and whose length must be exactly 9 characters long. * _User defined types (UDTs)_ Ability to create business specific data types with built-in rules controlling the range of values and conversions to other types. An example might include wh_cost_usd, wh_curr_gbp, wh_cost_eur, etc. types that include an understanding of their inherent currency codes and conversion functions between each other that reference a table of conversion factors by date to permit comparisons within the engine and automated conversions between customer currencies and USD. Extending the example, an invoice detail line table could use this bh_cost_usd type for storage while the application can request an automatic conversion to type wh_cost_eur for European customers' invoice display and printing. * _User defined casts_ Related to UDTs are UDCs or explicit and implicit casts for converting values of one type to another. * _User defined routines (UDRs)_ The ability to write more efficient and/or more capable procedures in the engine using C and/or Java. These 'host language' routines have access to all server facilities and can be far more complex and therefore more efficient than SPL routines. Along with this came the differentiation of STORED PROCEDURES and STORED FUNCTIONS as SPL routines that do not return inline data and those that do. * _User defined aggregate functions_ Define a set of UDRs that can be used to initialize, iterate, combine, and complete a aggregation calculation on a set of data. The resulting aggregate 'function' can be used like the built-in SUM(), AVG(), MIN(), and MAX() functions in GROUP BY queries, etc. * _Virtual Table Interface (VTI)_ This is the ability to define files and databases external to IDS as tables in the database where they are treated as native including defining access methods for searching, reading, inserting, updating, and deleting data from these 'tables'. The example is a flat file that is pre-processed into a temp table so that its contents can be filtered and processed into multiple result tables. By defining an external table for the flat file IDS can index the 'table' directly and search it or even include its contents in a INSERT INTO ... SELECT ... FROM ...; statement. This can save the overhead of reading the file into a temporary or permanent staging table and deleting the table and/or its contents later. * _Virtual Index Interface (VII)_ A set of definitions and function hooks that permit one to define new index methods to fit application requirements. The new RTREE built-in index method for geographic/graphical data is built on this interface and the default BTREE index method has been reimplemented using the VII interface to simplify server code. * _Datablades and bladelets_ Datablades are Informix's method of extending the capabilities of the IDS instance with business logic, new indexing methods, or even whole new data manipulation capabilities. Bladelets are smaller packages containing a small number of UDRs that improve the usability of the IDS instance. There are already many useful datablades that are available including the CopperEye datablade that uses the VII interface to define a new index type which is highly efficient for highly volatile tables, several full text search datablades, the time-series datablade which permits efficient tables of time-dependent data, the node datablade which defines hierarchical relationships between rows in a table, and others. Many bladelets are also available like the Dynamic SQL Bladelet which permits SPL routines to define SQL on the fly and execute it, a regular expression bladelet, etc. * _Functional indexes_ This is a favorite of mine. This extends the usability of IDS indexes in many ways. IDS indexes are otherwise limited to indexing 32 columns and certain data types cannot be included. With a functional index based on a Java language UDR you can pass up to 255 columns as arguments to the UDR and the functions return is the value which becomes the key in the index Btree. The return can be a compressed version of the original columns or some calculation based on them. As long as the function is invariant and deterministic it can be used. * _Row types_ The ability to create a collection of datatypes which can be used to define a table column or even an entire table. When you have several tables which will have wholly or partially identical lists of columns row types can be used to simplify administration. The classic example is to define an address row type as two address lines plus city, state, postal code, and country columns and use that as a column in any table that needs to store addresses. This guarantees that the address columns are always the same length, etc. *@