 |
 |
Archives of the TeradataForum
Message Posted: Fri, 18 Nov 2005 @ 10:40:04 GMT
| Subj: | | Re: Stored procedures results |
| |
| From: | | Eric Friedman |
| | "SP's or macros are difficult, if not impossible, to model and can't be generated by a CASE tool. The key to reusability is not small
packaging or elegant solutions hidden in SPs and macros. The key is to model both data and processes and build everything in CASE tools and
portable languages, that way the human investment is reusable in other platforms and makes it easier for the organization to drop one technology
and adopt a new one on the fly. | |
| | Embedding business logic in SPs or macros may be practical from the developer's standpoint, but from the "big picture" point of view they
are simply not portable and thus unacceptable as vehicles to implement business logic. | |
| | You have at your disposal both SQLCODE and SQLSTATE for checking the outcome of SQL operations. Plus you can DECLARE either and EXIT or
CONTINUE HANDLER routine to take whatever action you deem appropriate." | |
People who advocate that SPs are ONLY appropriate to automate DBA tasks are focusing on their own responsibilities and ignoring the main reason
that the database exists in the first place--to address business needs. While SQL is admittedly a very powerful DML (Data Manipulation Language),
no one would reasonably argue that SQL is a programming language sufficient for creating business applications. And being able to determine the
return state of a SQL command is hardly sufficient when what you actually need are the data returned by the select statement, not whether or not
it succeeded.
Although the programming languages provided by each RDBMS vendor to create SPs and UDFs are unfortunately still proprietary to each RDBMS and
therefore not portable, conversion is typically more tedious than difficult, and each vendor provides conversion tools to automate perhaps 90% of
the process.
The only way to achieve true portability is to use third party application middleware (whether CASE tools or not) external to the RDBMS, with
plugable device drivers written to accommodate the idiosyncrasies of each RDBMS back end. MicroStrategy and Business Objects have successfully
pushed this architecture for years, and MS ADO through ODBC offers much the same functionality (if you isolate and modularize the system-dependent
SQL that references system tables into callable functions that utilize CASE logic to execute the appropriate system table queries for the target
RDBMS).
Unfortunately, these portable middleware solutions require you to dynamically move potentially massive amounts of data out of the RDBMS to the
application server where the independent business logic can operate upon it. This totally defeats the parallel processing power of Teradata or any
other MPP RDBMS installation, forcing both serialization plus the additional overhead of data transfer (in both directions if the results are then
used to update tables in the database). Properly written UDFs and SPs offer the significant advantage of running in parallel within the database
environment, thereby extending the already powerful capabilities of the SQL engines.
Eric Friedman
Analytic Consultants, Inc.
| |
