stored procedures 1

[Crox]

Hi,

can anybody explain why en when you want to use stored procedures, when it is good or bad, what is easy and what difficult, how your organization is using it, whom is making them, how they are maintained, etc.

Some FAQ perhaps?

Thanks in advance!

With Friendly Regards,

Crox

 

[dhecht]

For information of creating and maintaining stored procedures for V.6, look at the Application Programming and SQL Guide. Here's some relevant snippets:

"Stored Procedures can encapsulate an applications SQL statements into a single message to the DB2 server, reducing network traffic to a single send and receive operation for a series of SQL statements."

"Stored Procedures remove SQL applications from the workstation, which prevents workstation users from manipulating the contents of sensitive SQL statements and host variables."

Additionally, Stored Procedures separate, to some degree, the database access layer from the business layer, which has distinct maintenance and migration advantages.

Prior to V.6, SYSPROCEDURES was used to maintain Stored Procedures. With V.6, SYSROUTINES was introduced, as well as schemas. Moreover, an option exists in V.6 and above to create SQL procedures or external procedures.

Considerations:

1) Avaiable DDF threads

2) DDF timeout values.

3) WLM vs. SPAS.

4) Block Fetch

5) Commit Frequency

Hope this helps.