Creating functions

You can create your own user-defined functions.

Procedure

To create functions:

On the DB2 Administration Menu (ADB2) panel, specify Z, and press Enter.
The System Administration (ADB2Z) panel is displayed.

Specify option FM, and press Enter.

The Manage Functions (ADB2ZF) panel is displayed, as shown in the following figure.

Figure 1. Manage Functions (ADB2ZF) panel

 DB2 Admin -------------------- DD1A Manage Functions -------------------- 09:53
 Option ===>                                                                    
                                                                                
                                                          DB2 System: DD1A      
    1 - Display/alter functions                           DB2 SQL ID: ADM001    
    2 - Create functions                                                        
    3 - Display function statistics                                             
    4 - Start all functions                                                     
    5 - Stop all functions                                                      
    6 - Create view on SYSIBM.SYSROUTINES                                       
    7 - Display views on SYSIBM.SYSROUTINES                                     
                                                                                
    Catalog table/view for option 1:                                            
      Owner  ===> SYSIBM           (default is SYSIBM)                          
      Name   ===> SYSROUTINES      (default is SYSROUTINES)                     
                                                                                
  User defined functions can also be managed from option 1.F                      
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE   
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Specify option 2, and press Enter.

The Create Function (ADB26CF) panel is displayed, as shown in the following figure.

Figure 2. Create Function (ADB26CF) panel

 DB2 Admin --------------------- DB2X Create Function -------------------- 18:38
 Command ===>                                                                   
                                                                                
  CREATE FUNCTION                                                               
                                                                                
  Schema      ===>          >            (Default is ISTJE)                     
  Name        ===>                    >  (? to look up existing functions)      
                                                                                
  (                                                                             
  Number of parameters ===>              (0-255)                                
  )                                                                             
                                                                                
  SPECIFIC    ===>                    >  (Specific name)                        
                                                                  (continued...)

Enter the required parameters and press Enter to continue with the create operation, or press End to avoid creating a function.
Db2® Admin Tool issues the SQL CREATE FUNCTION statement with the parameters you specify.
To create a SQL scalar function:
Restriction: When creating SQL scalar functions, the maximum length of the return statement is 2MB (32,767KB).
1. Write the SQL scalar function as part of the CREATE statement.
2. Pre-compile, compile, and link the program.
3. If the program has SQL statements, bind a package.
4. Create the function to register it to Db2 and grant execute to authorize all appropriate users.
5. Use the function in application programs.