SQL Procedure parameter limit?

[nshen]

Hi! We are in the process of porting SQL Server stored procedures into DB2 SQL Procedures. One thing that we noticed was some of our SQL Server stored procedures has many parameters, like 250+. What is the limit on DB2 SQL Procedures? I couldn't find it in the stored procedure builder book that I have.
If the limit is smaller than 250, is there a DB2 configuration parameter that we can tune to make it bigger?
We are using DB2 UDB v7.2 on AIX.

Thank you very much!
Regards, Nancy

 

[nshen]

So far we only found the following: Max number of parameters for a stored procedure: 32,767, max number of parameters for an user defined function: 90.
And way back in version 5, DB2/400 had a max parameter limit of 253 for SQL Procedures.

If anybody knows the limit on SQL Procedures for DB2 UDB v7, please share! Thanks!

Regards, Nancy

 

[Shaixpeare]

Hi Nancy, don't sweat it; the parameter limit for SQL procedures in DB2 v7.2 is 32767. You're golden ;-)

Todd
IBM Certified Solutions Expert

 

[nshen]

When we tried to create a SQL procedure with 250+ parameters using DB2 Stored Procedure Builder, we got SQL0442N error:

DB2ADMIN.WRITEPROPERTYRECORDSET - Create stored procedure returns -442.

DB2ADMIN.WRITEPROPERTYRECORDSET: 261: [IBM][CLI Driver][DB2/NT] SQL0442N Error in referring to routine "WRITEPROPERTYRECORDSET". The maximum number of allowable arguments (90) has been exceeded. LINE NUMBER=261. SQLSTATE=54023

DB2ADMIN.WRITEPROPERTYRECORDSET - Build failed.

DB2ADMIN.WRITEPROPERTYRECORDSET - Changes rolled back.

Is there a way to write SQL Procedure without hitting this error, if the max #of parameters is truely 32K?

Thanks!
Regards, Nancy

 

[Shaixpeare]

Nancy, which are you creating, a stored procedure or a user defined function? Also, exactly which version of DB2 are you doing it on? Thanks

 

[nshen]

1) LANGUAGE SQL (a SQL Procedure)
2) DB2 v7.2

It seemed DB2 v7.2 SQL Reference and porting guide from SQL Server 2000 to DB2 UDB 7.2 both agreed with you. But the Porting From Oracle to DB2 UDB 7.2 said otherwise:

http://www7b.boulder.ibm.com/dmdd/library/techarticle/0205wilkins/0205wilkins.pdf

Page 33 of 89

"DB2 SQL procedures have a limit of 90 parameters. When porting PL/SQL procedures with more than 90 parameters, unless some of the parameters are unnecessary and can be eliminated, the original procedure needs to be ported to multiple procedures."

Regards, Nancy