IBM i GYMN – SECOND SECTION: “Data access layer – Functions”

After we have seen PROCEDURES and TRIGGERS, see the following posts:

we could close the panoramic of the SECOND SECTION by illustrating the FUNCTIONS.

The approach is similar: under your schema you could access to the menu able to create a new SQL Function:


The following window will be opened and in the first tabbed you could indicate the name of the function and the schema:


In the Parameters tabbed you could define the parameters for your function, by pressing “add”. By the others button you could change or cancel one parameter or change the parameter order:


If you have a look at the “data type“, you could define every kind of type, from the character, numeric to every kind of data (BLOB, GRAPHIC, XML and so on):



You could also define the “coding method” of the data (CCSID, UTF and so on). I remind you to the help online for all the details about every single meaning:


Similar situation you will find in the “Restituzioni” panel, where you should define the return values:


You could define a scalar value or a return table:

The next panel id the ‘Options panel‘. I don’t want to focus on each detail, only I want to illustrate you the possibility to specify the SET OPTION instruction as I show in the following screens:


The last and most important panel let you define the body of the routine. As the usual you could consult the example list and check for the correctness of your instructions:


So you could build your function and save it:


At the end you could generate the SQL script as we saw in all the previous cases.

Leave a Reply

Your email address will not be published. Required fields are marked *