When I try to compile a Z/OS procedure in Toad, I get the following error. I can compile using DB2 native tool but not Toad.
ERROR [22001] [IBM][DB2] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001
The issue is with the version of the SYSPROC.DSNTPSMP procedure installed in Z/OS subsystem.
1. Open up the Object Palette
2. Go to the Procedure tab and look in SYSPROC
3. Look for SYSPROC.DSNTPSMP in the Z/OS system
4. Click on the Parameter tab and look at the SQL_ROUTINE_NAME column
If SQL_ROUTINE_NAME is a VARCHAR(27), you have an older version. SQL_ROUTINE_NAME should be a VARCHAR(261). This is the reason why you cannot compile/recompile stored procedures with longer names. You need to upgrade the SYSPROC.DSNTPSMP system stored procedure builder to the appropriate level.
The IBM DB2 Z/OS V8 documentation pointed to by the below link provides information you need such as a description of all the DSNTPSMP parameters including the types and lengths for V8.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/psmprpr.htm
