DB2 and Stored Procedures

amcgreal

Member
Has anyone utilized stored procedures with DB2? An article from our AS400 Adminisrtator discsses the positives for using them but I can not find anything on the JDE knowledge garden or here that is specific to DB2 and the use of stored procedures. Anyone have some experience or insight?

Thanks!
Amy McGreal
Sr. Programming Analyst
The Martin-Brower Company
 
Amy,

The reason you won't find anything on stored procedures is because OneWorld doesn't make use of them nor does it provide a way for you to easily use them in your code. OneWorld is pretty much plain SQL - understandable given that it deals with multiple versions of 3 different RDBMS's.

Regards,
 
Hello Amy,

We just went live with custom application that uses DB2 stored procedures to supply grid with open demands (from service and sales). I did use native database’s stored procedures before (MS SQL) to improve speed for OW applications and UBEs. Since there is no data conversion and row security overhead, stored procedures are way faster than anything I was able to make in OW, up to 60 times. DB2 stored procedures recalculate demand for whole branch in about 1 min. Also, it is easier to use commitment control (at least for me) than in OW.

Best regards,

Bojan.
 
We are exploring the use of DB2 stored procedures (or more likely, functions) in conjunction with EnterpriseOne and Cognos.

From what I've read so far, it looks like DB2 User-defined functions (UDF) are the most likely route. Unless you are on DB2 v8.1 or later, stored procedures swap processes each time you change from control statements to SQL statements, which seems to invoke a great deal of overhead.

The UDFs, on the other hand, reside in memory, but are quite different from SPs on Oracle or SQLServer. With the UDFs, you seem to have three choices - output a single variable, output a table (to be used as a datasource in a FROM clause), or output a row. Thus, the parameters are not designated as IN or OUT, since they are all IN.

DB2 does have the capability of overlaying UDFs with the same name, so long as they are different in the first 30 parameters.

The SQL I've written to convert JDE-style dates in DB2 is:
date(right(digits(SDTRDJ+1900000),7))
- where SDTRDJ is the Transaction Date - Julian from F4211. The code should result in a date-type value which could be used in all the of standard DB2 date functions.

As we make progress, I will try to post some example UDFs.
 
Back
Top