Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

152 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

-- Deleting an overloaded procedure

DROP PROCEDURE UpdEmployee(Dec(5, 2), Char(10));

DROP SPECIFIC PROCEDURE UPDEM00004;

8.2.6 Getting information about stored procedures

Information about external and SQL stored procedures is saved in the following system

tables:

SYSPROCS Provides all information about stored procedures. The program body

for SQL stored procedures is saved in the ROUTINE_DEFINITON

column.

SYSPARMS Delivers the description of the parameters, including the order,

parameter mode, data type, and length definition.

SYSROUTINES The SYSROUTINES table contains one row for each procedure

created by the CREATE PROCEDURE statement and each function

created by the CREATE FUNCTION statement.

For more information refer to the redbook Stored Procedures, Triggers and User Defined

Functions on DB2 UDB for iSeries, SG24-6309.

8.3 User defined functions

User defined functions (UDFs) are host - language functions for performing customized,

frequently used tasks in applications. UDFs allows the programmers to modularize a

database application, creating a function that can be used in SQL.

DB2 Universal Database for iSeries comes with a rich set of built-in functions, but users and

programmers may have different particular requirements not covered by them. UDFs comes

to play a very important role by allowing users and programmers to enrich the database

manager by providing their own functions.

Some of the advantages of UDFs are:

򐂰 Customization

Functions specifically required by your application not existing in the set of DB2 built-in

functions can be created. Whether the function is a simple transformation, a trivial

calculation, or a complex multivariate analysis, you may choose a UDF to do the job.

򐂰 Flexibility

You can use functions with the same name in the same library that accepts different sets

of parameters.

򐂰 Standardization

Many of the programs that you implement use the same basic set of functions, but there

are minor differences in all the implementations. If you correctly implement your business

logic as an UDF, you can reuse those UDFs in your other applications using SQL.

򐂰 Object-relational support

UDF also provides additional functions for User-defined Distinct Type (UDT) created in the

database. UDFs act as methods for UDTs. More information on UDTs and how UDFs are

used to encapsulate methods for them are in DB2 UDB for AS/400 Object Relational

Support, SG24-5409.