IBM Database Add-Ins for Visual Studio  

IBM Database SQL and SPL Functions

You can use the IBM® Database Add-Ins for Visual Studio to create database functions. A function, also called a user-defined function (UDF), is an extension or addition to the existing built-in functions of SQL in a DB2® database or of SPL in an Informix® Dynamic Server (IDS) database.

For a DB2 database You can register functions to a database in the SYSCAT.FUNCTIONS catalog by using the CREATE FUNCTION statement. For DB2® for z/OS®, functions are registered in the SYSIBM.SYSROUTINES catalog. Functions are never part of the SYSIBM schema. One such set of functions is provided with DB2 in a schema called SYSFUN. After you define a function to DB2, you can refer to it in SQL statements.

With functions, you can extend the function of the database by adding function definitions to be applied in the database engine. By adding functions to the database engine, you can save the effort of retrieving rows from the database and applying similar functions on the retrieved data. Functions provide more synergy between an application and the database. Functions can also contribute to higher productivity for application developers because they encourage code reuse.

The IBM® Database Add-Ins for Visual Studio give you a full development environment for working with functions. You can create the following types of functions with the IBM Database Add-Ins for Visual Studio:

For a DB2 database SQL scalar function
Returns a single scalar value each time that it is called.
For a DB2 database SQL table function
Returns a result set of columns in a table format.
For an IDS database SPL function
Returns one or more variables, expressions, or the result of another function call.

To develop an IBM database function in Visual Studio:

  1. Create an SQL or SPL function by using one of the following tools:
    • IBM Function Designer
    • Visual Studio Code Editor
  2. If you used the wizard or the editor to create the function in the Solution Explorer, build the function on a database server.
  3. Run the function to test the logic and result sets.
  4. Change the function definition as appropriate.
  5. Edit the source code of the function or change the properties of the function as appropriate.
  6. If you edited the source code of the SQL or SPL function or changed its properties, rebuild the function on the database server.
  7. Run the function again.
  8. Call the function with an IBM database procedure or an application.

See Also

Developing IBM Database Functions | IBM Function Designer


.NET Development Forum   developerWorks: Visual Studio .NET   DB2 FAQs   IDS FAQs

© Copyright IBM Corporation 2002, 2019. All Rights Reserved.