Archives of the TeradataForum

Message Posted: Thu, 20 May 2004 @ 16:15:02 GMT

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

I would not say that it eliminates the need for UNIX scripts or that Stored Procedures can do everything that a UNIX script can do. I don't plan to eliminate all of my scripts. I believe in having many tools to do the job, and then selecting the best one (I hope). My thoughts are:

1. Permissions. For instance if the applications run the script (not the DBA) and you drop and build indices or collect stats, you either have to grant INDEX rights on every object or DROP Table on table databases to the applications. We do the latter, because we have found that granting rights on objects add a thousands of rows to dbc.allrights and are a pain to maintain since tables are changed a lot more often than databases. Now al you need to grant is execute on the stored procedure. This limits what the applications can do. With Macros, you could only have one hardcoded DDL statement per MACRO.

2. We do a lot of building DDL from SQL. It sometimes gets a little hokey to get the proper order of statements and you usually have to write the derived SQL to a file and then execute that file. The stored procedrues can eliminate that intermediate step.

3. Stored procedures allow the DBA to execute a complex procedure manually (when necessary) from queryman or if you don't have a UNIX server (or your scripts) handy. For instance, you may only need to run something on one object whereas the UNIX script handles several databases with hundreds of objects.

4. You can write a single stored procedure to modularly handle a number of cases that may cause you to write several scripts.

5. Stored Procedures are database objects. They are easy to carry with you for DR.

6. You can run the same UNIX script for all your environments (DEV, TEST, and PROD) and have it react differently (be careful).

7. By logging directly to a table, searching for an event can be a lot easier than going through log files.

There are things that they do not hadle well. I really wish that you could build a multistatement request in a stored procedure. Then I could write one to allow the apps to optimally delete nor update a large portion of a large table. Of course, one of my programmers suggests that the optimizer should handle this case.

I will say that so far, I have had great success in the places that I have implemented them.

Hope this helps.

Glen

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