Comparison of built-in and user-defined routines
Understanding the differences between built-in and user-defined routines can help you determine whether you actually need to build your own routines or whether you can re-use existing routines. The ability to determine when to re-use existing routines and when to develop your own routines can save you time and effort as well as ensure that you are maximizing routine performance.
| Characteristic | Built-in routines | User-defined routines |
|---|---|---|
| Feature support | Extensive numerical operator, string manipulation, and administrative functionality available for immediate use. To use these routines, simply invoke the routines from supported interfaces. |
Although not all SQL statements are supported within user-defined routines, a great many are supported. You can also wrap calls to built-in routines within user-defined routines if you want to extend the functionality of the built-in routines. User-defined routines provide a limitless opportunity for routine logic implementation. To use these routines, you must first develop them and then you can invoke them from supported interfaces. |
| Maintenance | No maintenance is required. | External routines require that you manage the associated external routine libraries. |
| Upgrade | No or little upgrade impact. | Release to release upgrades might require you to verify your routines. |
| Performance | Perform better than equivalent user-defined routines. | Generally do not perform as well as equivalent built-in routines. |
| Stability | Error handling. | Error handling must be programmed by the routine developer. |
Whenever it is possible to do so, you should choose to use the built-in routines. These are provided to facilitate SQL statement formulation and application development and are optimized to perform well. User-defined routines give you the flexibility to build your own routines where no built-in routine performs the specific business logic that you want to implement.