|
|
|
|
| SQL Reference | Describes SQL syntax, semantics, and the rules of the language. Chapters 3 and 6 contain details about user-defined functions. | |
| Embedded SQL Programming Guide | Explains how to develop applications that access DB2 databases using embedded SQL. Chapters 6 and 7 contain details about user-defined functions. | |
| Administration Guide | Contains information required to design, implement, and maintain a database. Chapter 4 contains details about user-defined functions. | |
| Building Applications for UNIX Environments | Provides instructions to compile, link, and run user-defined functions on a UNIX system. | |
| Building Applications for Windows and OS/2 Environments | Provides instructions to compile, link, and run user-defined functions on a Windows or OS/2 system. | |
|
|
|
 |
|
|
|
|
|
|
Embedded SQL
|
DB2 CLI
|
| |
| calludf | X |
| Uses the library of UDFs created by the sample program udf. | |
| sampudf | X |
| Demonstrates the use of UDTs and UDFs. | |
| udf | X |
| Creates a library of UDFs made specifically for the SAMPLE tables, but can be used with tables of compatible column types. | |
| order.c |
| X | UDF library code. | |
|
|
|
|
|
The built-in capabilities supplied through SQL may not satisfy all of your application needs. To allow you to extend those capabilties, DB2 supports user-defined functions (UDFs). You can write your own code in C, C++, Java, and Basic to perform operations within any SQL statement that returns a single scalar value or a table.
This gives you significant flexibility. Your applications can return single scalar values such as select lists from databases, or they can return whole tables from non-database sources such as spreadsheets.
Figure 11. UDFs extend DB2 SQL
 |
UDFs provide a way to standardize your applications. By implementing a common set of functions in UDFs, many applications can use the UDFs to process data in the same way ensuring consistent results.
User-defined functions also support object-oriented programming in your applications. UDFs provide for abstraction, allowing you to define the methods that can be used to perform operations on data objects. And UDFs provide for encapsulation, allowing you to control access to the underlying data of an object, protecting it from direct manipulation and possible corruption.
OLE (Object Linking and Embedding) automation is part of the OLE 2.0 architecture from Microsoft Corporation. With OLE automation, applications can expose their properties and methods in OLE automation objects. Other applications can then instantiate those objects, use their properties, and invoke their methods.
DB2 for Windows NT provides access to OLE automation objects using UDFs. To access OLE automation objects and invoke their methods, you must register the methods of the objects as UDFs. DB2 applications can then invoke the methods by calling the UDFs. The UDFs can be scalar or table functions.
For example, you can develop an application that queries data in a spreadsheet created using a product such as Microsoft Excel. To do this, you would develop an OLE automation table function that retrieves data from the worksheet, and returns it to DB2. DB2 can then process the data, perform online analytical processing (OLAP), and return the query result to your application.