Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 18 Nov 2005 @ 10:40:04 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019