InterBase 7.0 Features for Consideration

Integration With Operating System Security

Add options for integration with authentication systems including Microsoft Domains, Microsoft ADS, Novell NDS, NIS, and Hesiod and Kerberos on appropriate platforms.

Given the number of such security services, perhaps it is better to create a plug-in type interface for InterBase security, so that third parties can write their own security bindings. Be careful that this doesn't open a security hole such that hackers can install their own security plug-in and gain access to everything.

Asynchronous Query Cancel

This is a high-demand feature, to allow the DBA to monitor, identify, and forcibly kill runaway queries. Clients should also be able to kill their own queries.

This feature should allow SYSDBA to cancel a query even if the user count is full; don't deny SYSDBA because user attachments have been exceeded.

Client Termination

This is a high-demand feature, to allow the DBA to monitor, identify, and forcibly detach individual client connections.

This feature should allow SYSDBA to terminate a client connection even if the user count is full; don't deny SYSDBA because user attachments have been exceeded.

Services API

Continue developing the Services API. There will undoubtedly be more Services API feature requests, such as:

• Remote server shutdown and restart
• Run multiple tasks on the server simultaneously
• Allow non-SYSDBA users to perform certain tasks, such as allowing users to change their own passwords

Server-level Or Database-level Security Privileges

We've proposed this for allowing control over who can create metadata, who can back up a database, etc. There is an increasing demand for security as people deploy on the Internet.

Server-level Catalog Of Registered Databases

Some users want to be able to contact a server and get a list of all available databases. This is required for JDBC compliance. Users also want some sort of abstraction of the pathname, so they don't have to distribute the physical path information to clients.

We should keep a server-side registry of database locations by which the client app can request a database by its identifier (like ODBC DSNs, or BDE Aliases). One could use a connect string like server: BugDatabase where the path to BugDatabase is defined in /usr/interbase/isc7.gdb

Secure Deployable Databases

This has been called "Database Security II." We should have a mechanism for prevention of database kidnappers.

One suggestion is to create a key pair in a database and its matching isc4.gdb so that the database cannot be moved to another system. Another suggestion is to store passwords in the gdb file, not in isc4.gdb.

This level of security should be optional on per-database basis. There should also be a failsafe, because highly protected databases could become permanently locked if the key is somehow lost.

64-bit File I/O

The limitation of 2GB per database file is a hardship for some users with large databases (for example, there is a user in Brazil with a single 182GB database made up of 91+ files). By using 64-bit file pointers on platforms that support them, InterBase would support files larger than any practical requirement.

Improve Automatic Cleanup Of Terminated Clients

When a client connection terminates, the server cleans up eventually. But it should also terminate any in-progress requests that the client left running. Without this, a runaway query runs to completion before the connection is "garbage collected," demanding a lot of server resources in the meantime.

GBAK Enhancements

• Allow GSPLIT to run on Windows 95/NT; this might be fixed in 6.0.
• Validate backup files during backup; don't give the bad news at restore time.
• Restore as much as possible with a damaged backup file, instead of aborting 100%; report what portion was unrestoreable.
• Permit incremental and partial backups and restores; users want to back up only record versions created in a given time period, or in highly trafficked tables; leave large, static portions of data out of the backup.
• Progress reporting during index builds:
  • Name each index and table/columns before building it, don't just silently build all indexes.
  • Quickly count the data pages in the relation to be indexed to give a comparative estimate for how long each index will take to build.
  • If possible, provide progress reporting during an individual index build.

GFIX Enhancements

• Repair and sweep databases without requirement of exclusive database attachment
• Detect and correct more cases of database corruption

InterBase Server Tray Icon

Some users want to have the convenience of managing the InterBase server from a tray icon. While not recommended by Microsoft style, it is possible to do this whether the server runs as a service or as an application (see Norton Anti-Virus). We should make the IBConsole more easily accessible.

Multi-Threaded Server (Superserver II)

Implement the InterBase Superserver as a fully threaded engine, without a mutex around the engine. This should increase throughput and performance greatly. This is also needed to allow InterBase Superserver to benefit from SMP hardware, which is now very popular.

Query Optimizer Improvement

We have many anecdotal reports that the query optimizer in InterBase 5.x is inferior to the optimizer in 4.x for many types of queries. We'll never create a perfect query optimizer for all cases, but it should be an ongoing task to improve the cost estimation algorithms to serve the commonly used query types. This relieves users of the responsibility to generate PLANs to override the optimizer. There are situations when it is inconvenient for the user to provide a PLAN, such as when using a machine query generator like Crystal Reports. InterBase is supposed to be self-tuning. In every release, InterBase should do a better job of creating its own PLANs.

Throttle Server Resource Use

This is related to the bug of unbounded memory consumption. There should be a configurable ceiling for resource use by the server process, so that ibserver doesn't overburden the server host, or worse, crash after memory is exhausted.

Increase Default Cache Size

The default 256-page cache is chosen for a small footprint. A more appropriate cache for a production database is much larger, even in the tens of thousands of pages. We should make the default be much larger than 256 pages. We could make the number of cache buffers be self-tuning, based on the size of the database and the frequency that the buffers become saturated.

Increase Default Page Size

Most of our "power user" users agree that they see some performance improvement in large databases by increasing the page size. It makes sense to increase the default page size for all databases. Most people feel 4KB is a good choice. Ann Harrison believes that the bigger the page size, the better it is for performance.

Local Database Access On UNIX

There is a significant performance benefit by allowing applications that run on the same host as the database server to connect without using a network interface. It is claimed that this will even speed up the local access method on Win32. This feature has been called "XNET" internally, to distinguish it from "INET" (TCP/IP remote access method) and "WNET" (NetBEUI remote access method).

This feature has been implemented in the code, but ifdef'd out. It needs to be activated, tested, and documented.

Raise Priority Of All Threads

If we give a method to raise the priority of the ibserver, we should do it for all threads in the Superserver engine, not just the server listener thread as we currently do.

There is a built-in method in the Windows NT Task Manager to change the priority of a process; is this safe to do with a running ibserver? Can we document it?

Direct I/O Option

Charlie Caro proposed a technology to bypass the operating system filesystem buffering completely, and instead use direct I/O on database files. This could have a good combination of performance and synchronous I/O.

I/O Read-Ahead

Charlie also proposed an optimization for sequential scans to read data pages and Blob pages asynchronously, so processing doesn't have to be I/O bound. Also, index bitmap pages would be prefetched in the same way.

This is currently partially implemented in the code, but ifdef'd out. It needs to be finished, activated, tested, and documented.

Garbage Collect Duplicate Index Nodes

This is apparently a performance problem. There is inadequate garbage collection of duplicate nodes in non-unique index data structures (for example, foreign keys). It results in larger index trees and slower lookups and updates. We should prioritize some engineering time to enhance the index garbage collection.

Database "Copilot"

We should implement a tool or automatic service to analyze database activity (such as frequently executed queries) and create recommendations for changes to the database to help improve performance.

Examples:

• Increase cache space if needed.
• Create indexes that would benefit the frequently-used queries.
• Rebuild indexes or recompute index selectivity.
• Increase page size.
• Defragment the database.
• Redesign views, stored procedures, and triggers.

Standard Function Library

Most other database products have a large complement of built-in functions. We are definitely behind the curve in this area. We should have many more functions than we currently have, even with the UDF library we provide.

It's a great feature to permit users to write their own UDFs. However, most users don't want to spend the time and effort to design, compile, debug, declare, and deploy their own function libraries. They want these functions to be present by default, especially for commonly used functions that are ubiquitous in other database products.

We should have functions implemented in the engine, not as a UDF library. Making the functions built-in allows them to be used on NetWare and makes them more convenient to use on all platforms. It also simplifies installation and maintenance because no auxiliary UDF library is needed.

See the list of functions in other database products at the end of this document.

GUI Tools Phase II

We have new GUI tools for InterBase V6.0, but that only redesigns existing functionality. We need to improve upon this. There's a long list of possible tasks for which we could build a GUI interface, including:

• Managing users, roles, and privileges.
• Prototyping, analyzing, and tuning queries.
• Reporting database statistics.
• Monitoring OS resources.
• Monitoring lock manager activity.
• Monitoring transactions per second.
• Monitoring I/O per second.
• Automating DBA tasks.
• Accessing multiple databases in query tool; copying tables between databases (like the QLI restructure feature).

Data Import/Export Tool

We should provide a user-friendly data import tool or wizard that loads data as quickly as the insert-from-external-table method. This would help customers who need to upsize their Paradox, Visual dBASE, or Access databases.

GDS_REPLAY Debugging Tool

This is a "tape recorder" for users to use. They can record a case where they experience a crash or a bug, and send us the database and the replay log. It existed in previous versions of InterBase, but it needs to be updated in order to work with current InterBase.

Array Datatype Support

An array is a powerful datatype that InterBase can use as a market differentiator, but it's virtually impossible for users to use it in the popular developer tools.

This is a suggested enhancement for IBDAC, ODBC, JDBC, and IBPerl. We should surface the array datatype through commonly used client interfaces. We should also provide more SQL syntax so that DSQL and stored procedure & trigger code can operate on arrays. Currently, a statement can query an element of an array, but there is no syntax to insert or update elements of arrays.

Blob Filter Enhancements

• Document Blob filters better, provide examples.
• Provide standard library of Blob filters for some rich data and complex datatypes.
• Add features to allow complex Blobs to be used in WHERE clause conditions.
• Maintain taxonomy of "official" Blob subtype numbers.
• Maintain library of user-contributed Blob filters.

Scrollable Cursors

This is important for navigating through live datasets, if we want to implement that feature. The alternative is to require navigable datasets to be cached on the client in a TClientDataset for example.

This feature has been implemented in the code, but ifdef'd out. It needs to be activated, tested, and documented.

Event Technology Enhancements

We should make a quantum leap in event technology, to provide a new paradigm in "push" technology. Implement hierarchical events and allow data arguments to be passed with an event. For example:

POST FOO.BAR.BAZ ARG1 ARG2

Allow clients to listen for classes of events. For example:

EVENT_WAIT FOO
EVENT_WAIT FOO.BAR
EVENT_WAIT FOO.BAR.BAZ

These statements would all receive the event posted in the example above. In other words, instead of matching an event based simply on exact string, the event matching algorithm would match based on exact string, i.e. with the regular expression /^FOO$/, but also match with the regular expression /^FOO..*/.

Other event-related feature suggestions:

• Make sure there are components for Delphi/C++Builder/JBuilder that take advantage of the new Event technology.
• Create an "event cursor" mechanism so the client could retrieve the event name/class, info about the event (who posted it, for example), the number of arguments, and the data of the arguments themselves. · Deferred events so a client could log on and get a sequence of events that have been queuing up.
• Permit different asynchronous event handlers for different event classes, i.e. one event handler for event FOO, a different event handler for event FOO.BAR, and a different event handler for event LIME.
• Permit multiple asynchronous event handlers for any given event.
• Allow events within or outside a transaction.
• Add SQL security to GRANT certain users or roles the privilege to receive notification of an event class.

Expression Indexes

Permit indexes to be defined for an arbitrary scalar expression, not just a simple column or set of columns. This would provide a method to perform case-insensitive sorting. It could also be a way to encourage dBASE users to migrate to InterBase without losing functionality.

This feature has been implemented in the code, but ifdef'd out. It needs to be activated, tested, and documented.

Encrypted Network Traffic

Data communication over a network should be secure and resistent to wiretaps. This is especially important when transmitting data over the Internet or other WAN, and when using Replication over phone lines, Internet, or WAN. Users demand a seamlessly integrated network encryption technology that they can use with InterBase.

There are industry indications that operating system vendors are standardizing on IPSec as a strong network encryption technology. IPSec is transparent to applications, implemented in software so it doesn't require extra routers, and it's a cross-platform solution. NT 5.0 and Solaris 7 claim to be adding IPSec as a native component of the respective OSs. There are commercial or free solutions for NT 4.0, other UNIXes, Linux, and NetWare. As an interim solution, we should put on our web site references to resources for IPSec, and an intention to support InterBase on platforms with IPSec technology.

Java UDFs

Customers want to be able to write Java classes and use them as UDFs. This would allow customers to write cross-platform UDFs, compiling once and running anywhere. Many other RDBMS products are adding Java support. Even though there are good reasons why it isn't a good idea technically, we should consider this.

NULL Handling For UDFs

A frequently mentioned shortcoming of our UDF architecture is that there is no method to code a UDF to distinguish between parameters that are NULL and those that have a value. Neither is there a method to allow UDFs to return NULLs. This makes coding UDFs for use in SQL contexts very awkward, because you have to code at the SQL level to restrict what is sent to the UDF. The fact that InterBase has no built in ISNULL or CASE or COALESCE functions, to call functions conditionally or pass parameters conditionally based on the NULL state of the parameters, compounds this awkwardness.

Multi-Database Joins

Users frequently want to join tables that reside in two separate databases. We could do it with GDML, why not SQL ?

DML Syntax For User Operations

In InterBase V6.0, we're introducing the Services API, but users seem to want SQL statements to create and configure users and passwords. This would permit such operations to be coded in stored procedures or triggers.

CAST Varchar <-> Blob

A recurring question is how to get textual Blob data into a Blob? Insert and Update do not support assigning literals to Blobs. Users suggest something like to following:

CAST('string' AS BLOB SUBTYPE TEXT)

This would solve the problem in DSQL statements and Stored Procedures, at least for string literals somewhat shorter than the length limit on SQL statements:

CAST(blob_expr AS VARCHAR(n))

Also, allow casting of Blob data to varchar, as above.

SQL COALESCE() And CASE() Syntax

Users demand this feature (or variations of it) more than any other single SQL feature. It's a convenience function to allow SQL queries to produce conditional results. One could do this with client code, but it'd be much less work to fill a grid with data if we had this feature. It's especially useful because the functionality cannot be duplicated with UDFs, since UDFs can't take nullable parameters.

ISERROR() Function

ISERROR(expression, alternate_expression)

This function would evaluate and return the second operand if and only if the first operand fails and posts an error.

Triggers For Non-Data-Modification Events

Users want to be able to write triggers that are invoked by system events other than INSERT, UPDATE, and DELETE. Here are some suggestions:

• Fire trigger on client connect or disconnect.
• Fire trigger on database shutdown or restart.
• Fire trigger on database backup or restore.
• Fire trigger on a schedule, like cron.
• Fire trigger when the database file exceeds 95% of its specified size limit.
• Fire trigger in response to any named Event.

For example:

sourcecode:: sql

CREATE TRIGGER FOR EVENT

{CONNECT | DISCONNECT | SHUTDOWN | RESTART | BACKUP | RESTORE | SCHEDULE 'schedule-specification' | FILE_FULL | 'event-name'}

AS BEGIN

. . .

END

Domains, Blobs, And Arrays In Stored Procedures, Triggers

Users want to have more datatypes available to them in procedures and triggers. A user reported that he can use Blobs as SP parameters and it works! We need to test, document, and support this feature.

Localized Message Database

Most of the structure in our message database needed to allow the message database to contain text in multiple languages is done. But the data has been neglected and not maintained. We need to revisit this in order to take care of the international needs. Doing this includes the following steps:

• Clean up the message database.
• Perform some editorial review of our message text.
• Outsource translation.
• Distribute localized versions of interbase.msg.
• Provide an Install API option, an ib_config parameter, and a GUI method of switching the message file.

Messages Database Support In IBSERVER

The server should store its messages in interbase.msg. Currently all strings that the ibserver program writes to interbase.log are hardcoded in the server source code. This makes them very difficult to maintain, document, or research. It also makes them impossible to localize.

Flexible Referential Integrity Indexes

Users need to construct PLAN clauses to make use of indexes most efficiently, at least until our optimizer does a better job. Users can't reference indexes that are created by primary or foreign keys, because these indexes are given machine-generated names and are not guaranteed to keep their names after a restore.

There should be a means to give persistent names to indexes that are created implicitly by RI constraints. It might be as simple as automatically naming an index the same as the name of a named RI constraint. Then if a user wants a named RI index, he or she need only name the constraint, something that we already support.

Cursor Syntax In Stored Procedures, Triggers

Users want to have full-featured fetch commands (NEXT, PREVIOUS, FIRST, LAST, GOTO) in stored procedures. The current FOR SELECT… DO syntax effectively implements only the NEXT cursor command. Users want to declare cursor(s) and be able to FETCH from the cursor(s).

UPDATE WHERE CURRENT OF CURSOR In Stored Procedures, Triggers

A user reports that this works! We should test, document, and support this feature.

Dynamic Queries In Stored Procedures, Triggers

Users want to parameterize tables and columns, not just constants. The best way to deal with this is to allow procedures and triggers to prepare and execute fully dynamic queries. Obviously it isn't fast, but it opens up a whole bunch of features that previously weren't available.

Cascading Parameter Changes For Chained Procedures

If I SP1 calls SP2 and passes params to it, if I change the params in SP2, IB won't compile SP1 after changing the params it uses to call SP2. What I seem to need to do is drop both and recreate.

Shadowing To A Mapped Drive On Wintel

Users want to write their shadow files to a mapped drive in a Wintel environment. We can write a shadow file to an NFS-mounted filesystem in a UNIX environment. Without this feature, some users criticize the shadowing feature, calling it "useless." They are willing to accept the immense performance penalty for performing I/O over a LAN, but they want a "hot backup" on a separate host for the case of a complete server failure.

Another shadow feature request: allow clients to connect to a shadow database as though it were a read-only database.

Cluster Support For Failover & Load-Balancing

Users want failover support in an NT cluster environment. If one server goes down, the second server should take over the database connection transparently. It is not necessary to preserve a request or even a transaction, but the connection should continue uninterrupted from the client's perspective.

Windows NT Enterprise contains some clustering technology. Microsoft Cluster Service (MSCS, ) provides failover technology. Microsoft Windows NT Load Balancing Service (WLBS) balances and distributes TCP/IP client connections in a three-tier model. We should track these technologies, because users are already asking us to support them.

Create Table Based On A Query Or Table

This is a SQL feature request to make it easier to copy a table.

CREATE TABLE FOO AS SELECT * FROM BAR;

The table FOO would be created automatically with datatypes matching the fields of the query, and fill the new table with the data in the query result set.

Cascading Constraint Drop

When one drops a table, the dependant constraints (e.g. foreign key references) should also drop. Oracle has this and it makes it really easy to drop and recreate tables. One doesn't have to waste the time dropping every view, constraint, etc.

Multithreaded Client Interface

Users often demand to be able to share database connection handles and transaction handles between multiple threads in an application.

Allow One NULL In UNIQUE Column

Oracle and MS SQL Server conform to the intermediate/full SQL92 rule that permits up to one NULL entry in a column that has a UNIQUE constraint. Users periodically complain that InterBase doesn't behave this way (though we are compliant with the entry level of SQL92 in this case).

Evaluate Constraints According To ANSI

Currently, we evaluate constraints after each row change in a multi-row update. According to ANSI SQL, we should defer constraint checking until after all rows have been changed. This affects situations like:

UPDATE MYTABLE SET PRIM_KEY = PRIM_KEY+1;

This returns an error for the violation of the unique constraint, but it should not.

PURGE Table Command

It's so common for users to delete all rows in a table, that we should consider adding a special command to optimize this operation. Charlie Caro proposes implementing a new statement, PURGE, as exists in some other database products. PURGE would behave similar to DROP table except that it would preserve all table-related metadata. Like the DROP of the table, PURGE:

• would be a deferred operation that doesn't happen until COMMIT.
• would require no foreign key dependencies by other tables on it.
• would require no (or active) DELETE triggers on the table.
• would require exclusive access to the table.
• would return all data pages and index pages to the free list.

Performance-wise, PURGE:

• would run orders of magnitude faster than a DELETE.
• would cause zero database growth.
• would cause no deferred garbage collection.

PURGE could be called from stored procedures but the COMMIT to execute it would have to be executed from the application since stored procedures can't commit.

Jim Starkey suggests that one could alter the semantics of the special case DELETE FROM TABLE (with no row restriction clause) to accomplish the same thing.

XA And MTS Support

These two standards are becoming more important. Users ask for MTS support on a regular basis. In order to support MTS, we must be conformant to the XA interface.

Rework System Metadata

We should "eat our own caviar" by establishing more explicit referential integrity between system relations. This will also serve to document the relationships between system relations. Additionally, cascading references can make it more automatic to update system relations for engine work and for user applications that want to modify the system relations directly.

Support The GDML Column Alias

GDML allowed columns to have an alternative name, an "alias." We don't support this feature in SQL, but it is supported in the engine.

Support The GDML First n Query

GDML allowed users to request the first n rows of a more lengthy query. This is a handy feature that users have requested, even without knowing that we have the capability in the engine. Surface this functionality through SQL.

Support CAST(expr AS domain-name)

This expands the usefulness of domains as user-defined datatypes.

Cascading ALTER TABLE Operations

This builds upon the 6.0 feature of altering datatype or name of a column; any referent column in another table should also be updated in a cascading manner.

Permit UNION In Subqueries

SELECT … FROM (SELECT … UNION SELECT …)

Unions reportedly perform better than IN (…) predicates, so presumably if IN executed faster that would satisfy this feature request.

GROUP BY expr

Currently GROUP BY takes only a list of column. We should permit ordinal column (like ORDER BY 2) and expressions, so we could write queries to group by an expression, including calls to UDFs.

Temporary Tables

Users frequently demand the use of temporary tables that are visible only in a session, and which evaporate automatically when the session terminates.

Database Sweep Causes Crash, Corruption

Engineering plans to fix this in the InterBase V6.0 project, but it should be verified fixed in 6.0 and subsequent releases.

SET TERM In ISQL Scripts

This is a design flaw in the ISQL parser. It makes it impossible to clearly document writing scripts to create procedures and triggers. Fixing this is not a major piece of functionality, but it would go a long way toward making the product easier to use.

Views With UNIONs And Column Aliases

Many users get frustrated at our lack of support for Views defined as UNION queries. The engine supports this, and gpre allows you to create such a view, but there is no way in DSQL to create a view defined as a union query. Also, apparently you cannot use column aliases in views. Both of these are seemingly trivial problems, but fixing them will go a long way toward making the product easier to use.

Memory Management Issues

There are issues with memory management of the InterBase server. Certain operations can use an unbounded amount of memory resources, depending on the number of concurrent users or metadata complexity. Also, when the InterBase server is unable to allocate memory from the operating system, the server crashes. In order to be a true self-maintaining database server and not require a DBA, we should improve the server memory usage and its ability to recover from exhausted memory errors.

Online Foreign Key Create/Drop

Currently, creating or dropping a foreign key requires exclusive access to the database. We should allow this sort of metadata change to occur on a live database.

Functions From ANSI SQL3

String functions:

• SUBSTRING(expr FROM num FOR num)
• CONVERT(expr USING conversion-name)
• POSITION(expr IN expr)
• TRANSLATE(expr USING translation-name)
• TRIM([ [ LEADING | TRAILING | BOTH ] [character] FROM ] expr)
• UPPER( expr )
• LOWER( expr )
• OVERLAY(expr PLACING expr FROM position [FOR length ] [REMOVING length] )

Date/time functions:

• EXTRACT(field FROM expr) Planned for 6.0
• CURRENT_DATE, CURRENT_TIME(precision), LOCALTIME(precision), CURRENT_TIMESTAMP(precision), LOCALTIMESTAMP(precision)

Flow control functions:

• NULLIF( expr, expr )
• COALESCE( expr, expr, …)
• CASE operand when-clause … else-clause END

Functions In InterBase 5.x ib_udf Library

Math functions:

abs, acos, asin, atan, atan2, ceiling, cos, cosh, cot, div, floor, ln, log, log10, mod, pi, rand, sign, sin, sinh, sqrt, tan, tanh

Binary boolean functions:

bin_and, bin_or, bin_xor

Character functions:

ascii_char, ascii_val, lower, strlen

Functions Proposed For InterBase ib_udf Library But Postponed

base, bin_comp, cond (like ?: operator in C), degrees, is_null, radians, round, charindex (like SQL position), difference (between two soundex values), insert (string in string), match (regular expression), proper (case), replicate, reverse, soundex, space, current_time, datepart, datediff, dateadd, timepart

Functions From Visual dBASE 7

String functions:

asc, at, center, chr, difference, left, len, length, like, lower, ltrim, proper, rat, replicate, right, rtrim, soundex, space, str, stuff, substr, substring, transform, trim, upper, val

Math/money functions:

abs, acos, asin, atan, atan2, atn2, ceiling, cos, dtor, exp, floor, fv, int, log, log10, max, min, mod, payment, pi, pv, random, round, rtod, set currency, set point, set precision, set separator, sign, sin, sqrt, tan

Date and time functions:

cdow, cmonth, ctod, date, day, dmy, dow, dtoc, dtos, elapsed, mdy, month, parse, seconds, set century, set date, set date to, set epoch, set mark, set time, time, utc, year

Bitwise functions:

bitand, bitlshift, bitnot, bitor, bitrshift, bitset, bitxor, bitzrshift, htoi, itoh

SQL functions:

avg, count, max, min, sum, lower, substring, trim, upper, extract

Functions From Greg Deatz's FreeUDFLib

Math functions:

DollarVal, IsDivisibleBy, RoundFloat

Character functions:

CRLF, FindFirstWord, FindNextWord, FindNthWord, FindWordStartingAt, GenerateFormattedName,Mid, PadLeft, PadRight, ProperCase, QPushQueue, Right, Strip, ValidateNameFormat

Date functions:

AddMonth, AddYear, AgeInDays, AgeInDaysThreshold, AgeInMonths, AgeInMonthsThreshold, AgeInWeeks, AgeInWeeksThreshold, IsLeapYear, MaxDate, MinDate

Blob functions:

BlobMaxSegmentLength, BlobSegmentCount, BlobSize, BlobAsPChar, BlobLeft, BlobLine, BlobMid, BlobRight, StrBlob

Functions In Robert Shieck's UDF Library

Cstring functions:

alltrim, alltrimc, center, centre, cstradd, cstrdelete, cstr_plus_int, int_plus_cstr, lefts, len, lower, lpad, ltrim, pad, parse, pos, proper, quarter, replicate, reverse, rights, rtrim, rtrimc, substring

Conversion functions:

cstr_to_dbl, cstr_to_vchar, ascii, chr, vchar_to_cstr

Date/Time functions:

addtime, day, day_of_week, diffdate, firstday, hour, julian, lastday, makedate, maxtime, minute, month, month_of_year, msec, quarter, sec, subtime, year, week, zerotime

Double precision functions:

absdbl, acos, asin, atan, atan2, cos, cstr_to_vchar, dabs, degrees, exp, fact, floor, log, log10, maxdbl, mindbl, PI, pow, pow10, radians, round, sin, sqrt, tan, truncate

Float functions:

absflt, maxflt, minflt · Integer functions: absint, modquot, modrem, maxint, minint

Smallint functions:

abssml, maxsml, minsml

Varchar functions:

valltrim, valltrimc, vcenter, vcentre, vcharadd, vchardelete, vlefts, vlen, vlower, vlpad, vltrim, vltrimc, vpad, vparse, vpos, vproper, vreplicate, vrights, vrtrim, vrtrimc, vsubstring

Functions In Sybase ASE

Datatype functions:

convert, hextoint, inttohex

Date functions:

dateadd, datediff, datename, datepart, getdate

Math functions:

abs, acos, asin, atan, atn2, ceiling, cos, cot, degrees, exp, floor, log, log10, pi, power, radians, rand, round, sign, sin, sqrt, tan

Row aggregate functions:

sum, avg, min, max, count

String functions:

ascii, char, charindex, char_length, difference, lower, ltrim, patindex, replicate, reverse, right, rtrim, soundex, space, str, stuff, substring, upper

System functions:

col_name, col_length, curunreservedpgs, data_pgs, datalength, db_id, db_name, host_id, host_name, index_col, isnull, lct_admin, object_id, object_name, proc_role, reserved_pgs, rowcnt, show_role, show_role, suser_id, suser_name, used_pgs, tsequal, user, user_id, user_name, valid_name, valid_user

Text and image functions:

patindex, textptr, textvalid

Functions In Pervasive.SQL V7

Aggregate functions:

avg, count, max, min, sum

Character functions:

length, substr, ltrim, rtrim

Functions In Solid Server 2.3

String functions:

ascii, char, concat, insert, lcase, left, length, locate, ltrim, repeat, replace, right, rtrim, space, substring, ucase

Numeric functions:

abs, acos, asin, atan, atan2, ceiling, cos, cot, degrees, exp, floor, log, log10, mod, pi, power, radians, sign, sin, sqrt, tan, truncate

Time and date functions:

curdate, curtime, dayname, dayofmonth, dayofweek, dayofyear, hour, minute, month, monthname, now, quarter, second, timestampadd, timestampdiff, week, year

System functions:

ifnull, user · Datatype conversion: convert, convert_char, convert_smallint

Functions In Oracle 8

Character functions:

ascii, chr, concat, initcap, instr, instrb, length, lengthb, lower, lpad, ltrim, nls_initcap, nls_lower, nls_upper, nlssort, replace, rpad, rtrim, soundex, substr, substrb, translate, upper

Conversion functions:

chartorowid, convert, hextoraw, rawtohex, rowidtochar, to_char(date), to_char(number), to_char(label), to_date, to_label, to_multi_byte, to_number, to_single_byte

Date functions:

add_months, last_day, months_between, new_time, next_day, round, sysdate, trunc

Miscellaneous functions:

dump, greatest, greatest_lb, least, least_ub, nvl, uid, user, userenv, vsize

Number functions:

abs, ceil, cos, cosh, exp, floor, ln, log, mod, power, round, sign, sin, sinh, sqrt, tan, tanh, trunc

Functions In Informix Dynamic Server 7.3

Algebraic functions:

abs, mod, pow, root, round, sqrt, trunc

DBINFO functions:

DBINFO({'DBSPACE' | 'sessionid' | 'dbhostname' | 'version' {'major'| 'level' | 'full'}})

Exponential and logarithmic functions:

exp, logn, log10

HEX functions:

hex

Length functions:

length, char_length, character_length, octet_length

Time functions:

date, day, month, weekday, year, extend, mdy, to_char, to_date

Trigonometric functions:

cos, sin, tan, asin, acos, atan, atan2

String-manipulation functions:

trim, substring, substr, replace, lpad, rpad, upper, lower, initcap

Aggregate functions:

count, avg, max, min, sum, range, stdev, variance

Functions In DB2 Universal Database 5.2

Aggregate functions:

avg, count, count_big, grouping, max, min, sum, var, variance

Character functions:

ascii, chr, concat, difference, insert, lcase, left, length, locate, ltrim, posstr, repeat, replace, right, rtrim, soundex, space, substr, translate, ucase

Datalink functions:

dlcomment, dllinktype, dlurlcomplete, dlurlpath, dlurlpathonly, dlurlscheme, dlurlserver, dlvalue

Datatype & conversion functions:

bigint, blob, char, clob, dec, decimal, dbclob, digits, double, double_precision, float, graphic, hex, int, integer, long_varchar, long_vargraphic, real, varchar, vargraphic

Date/time functions:

date, day, days, dayname, dayofweek, dayofyear, hour, julian_day, microsecond, midnight_seconds, minute, month, monthname, second, quarter, time, timestamp, timestamp_iso, timestampdiff, week, year

Math functions:

abs, absval, acos, asin, atan, atan2, ceil, ceiling, cos, cot, degrees, exp, floor, ln, log, log10, mod, power, radians, rand, round, sign, sin, sqrt, stddev, tan

SQL functions:

coalesce, nullif, value

System functions:

deref, event_mon_state, generate_unique, nodenumber, partition, raise_error, table_name, table_schema, type_id, type_name, type_schema

Other Function Suggestions From Users

• TO_DATE(stringfield, format) converts strings to date entities using a format string
• TO_CHAR(datefield, format) converts dates to strings using a format string
• DECODE(field, test1, result, test2, result, …) nonstandard equivalent of ANSI SQL COALESCE()
• DATEPART() MS SQL Server's nonstandard equivalent of ANSI SQL EXTRACT()
• Blob_add(), Blob_delete(), Blob_retrieve(), Blob_replace(), Blob_insert(), Int_to_blob(), Str_to_blob(), Str_type()
• Matrix math on arrays