Create routines
You can define public or private user-defined routines that support your DataBlade® module. You can specify if the routine is called by SQL or is an internal routine.
Routines can be functions, which return values, or procedures, which do not return values. Routines can be written in the C or Java™ programming languages or the HCL OneDB™ Stored Procedure Language (SPL).
- Overload existing routines for extended data types.
Existing routines can be built-in or user-defined. Built-in routines include operator and other arithmetic functions, and support routines.
- Overload operators for built-in aggregates for extended data types.
Built-in aggregates include AVG, DISTINCT, MAX, MIN, RANGE, SUM, STDEV, and VARIANCE.
- Create user-defined routines for built-in or extended data types.
| Property | Default value | Description |
|---|---|---|
| Routine name | prefix Routine | The name of the routine. If you are overloading
a routine, the name can be an existing routine name; otherwise, it
must be a unique name. See Routine name for more information. |
| Return type | No return type | The data type that is returned by the routine. Functions return a value, but procedures do not. |
| Statement local variable? (Available for routines that return values only) |
No | Whether the last argument passed to the function
is an OUT parameter for a statement local variable, allowing the function
to return two values. See Statement local variables for more information. |
| Arguments | None | The name, data type, and default value of each
argument passed to the routine. See Routine arguments for more information. |
| Language | C | The programming language in which to write the
routine: C, Java, or SPL. You must set server compatibility to 9.2 or later to generate code for Java projects. You need the J/Foundation upgrade to HCL OneDB to enable Java services. |
| SQL routine body (SPL routines) | None | The SPL statements that define the routine. See the HCL OneDB Guide to SQL: Syntax for more information about SPL. |
| Does not accept null values? (C and Java routines) | Yes | Whether the routine accepts null values. If a routine that does not accept nulls is passed a null value, the database server returns a null value without calling the routine. |
| Is variant? (C and Java routines) | Yes | Variant routines can return different values with
the same input arguments. The database server never caches results
from variant routines. See Variant functions for more information. |
| Is parallelizable? (C and Java routines) | No | Parallelizable routines can be split into subqueries
and processed in parallel. See Parallelizable routines for more information. |
| Is a DBA routine? (C and Java routines) | No | The routine can be created or executed only by a user with DBA permissions. |
| Never called from SQL? (C routines) | No | If a routine cannot be called from SQL, it is an internal routine that can only be called directly by the database server: for example, primary access method routines. |
| An iterator? (C and Java routines) | No | Iterator routines return a set of values, one value
at a time. See the HCL OneDB DataBlade API Programmer's Guide for more information. |
| C routine name (C routines) | prefix Routine | The name of the routine in the shared object file.
Must be unique. See C routine name for more information. |
| Shared object path (C and Java routines) | $ONEDB_HOME/extend/%SYSBLDDIR%/project.bld (C
routines) %JAVAPATH% (Java routines) |
The relative or absolute path and file name of the shared object. The default path and file name is recommended. |
| Well behaved or poorly behaved? (C and Java routines) | Well behaved | Well-behaved routines can run in the CPU virtual
processors; poorly behaved routines run in a user-defined virtual
processor. See Routine behavior for more information. |
| User-defined virtual processor class (C and Java routines) | default_class | The name of the user-defined virtual processor
class in which a poorly behaved routine runs. See User-defined virtual processor class name for more information. |
| Special stack size requirements? (C routines) | No | Whether the routine needs an unusually large amount
of virtual shared memory to execute. See Stack size for more information. |
| Cost of routine (C routines) | 0 | The relative cost of the routine, for query optimization. See Cost of routine for more information. |
| Negator routine? | No | A routine that returns the opposite Boolean result
with the same arguments. Used for query optimization. See Related functions for more information. |
| Commutator routine? (C routines) | No | A routine that returns the same Boolean result
with the arguments in reverse order. Used for query optimization. See Related functions for more information. |
| Selectivity routine? (C routines) | No | A routine that estimates the percentage of rows
returned by the routine. Used for query optimization. See Related functions for more information. |
- Routine name
- Return type
- Statement local variable
- Arguments
- Language
- DBA routine
Using the ALTER FUNCTION statement allows BladeManager to reregister the routine without dropping and recreating it.
The following sections describe properties of routines.