When doing an @execsqlproc, a function error is being thrown when trying to access a Stored Procedure in DB2

Document ID : KB000008043
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

We are tyring to call a stored procedure on DB2 (mainframe) through Datamaker Data Painter and are getting an error. 

This is the stored procedure 
@execsqlproc(PSSMOM,SPUDM01U,TBLNM,IN,'CLUBVNLS01',OLDKEYZN,IN,'Z15220',ULTRATCD,OUT,0,PMTR1,OUT,0,PMTR2,OUT,0,PMTR3,OUT,0,PMTR4,OUT,0,PMTR5,OUT,0,PMTR6,OUT,0,PMTR7,OUT,0,PMTR8,OUT,0,PMTR9,OUT,0,PMTR10,OUT,0,PMTR1)@ 
and it is producing this error: 

CA Test Data Manager - Datamaker Error Message - 2017/08/18 - 13:19:34 

Exe dyn 

Profile: SOSME4 
Title: MF SQL Conn#7 - SOSME4 

Database Warning Message: Can not find parameters of SP SPUDM01S 

Transaction Error Code: -1 
Database Error Code: 999 

CA Test Data Manager - Datamaker Details: 

GUI Version: 4.1.0.46 (3/29/2017 10:57:14) 
REP Version: 3.2H 

Environment:
TDM 4.1DB2
Cause:

First problem resolved by appending the schema name (MNFMTEST.SPUDM01U) 

Query Used - @execsqlproc(PSSMOM,SPUDM01U,TBLNM,IN,'CLUBVNLS01',OLDKEYZN,IN,'Z15220',ULTRATCD,OUT,0,PMTR1,OUT,0,PMTR2,OUT,0,PMTR3,OUT,0,PMTR4,OUT,0,PMTR5,OUT,0,PMTR6,OUT,0,PMTR7,OUT,0,PMTR8,OUT,0,PMTR9,OUT,0,PMTR10,OUT,0,PMTR1)@ 

Was altered to be: 

Query Used adding MNFMTEST: 
@execsqlproc(PSSMOM,MNFMTEST.SPUDM01U,TBLNM,IN,'CLUBVNLS01',OLDKEYZN,IN,'Z15220',ULTRATCD,OUT,0,PMTR1,OUT,0,PMTR2,OUT,0,PMTR3,OUT,0,PMTR4,OUT,0,PMTR5,OUT,0,PMTR6,OUT,0,PMTR7,OUT,0,PMTR8,OUT,0,PMTR9,OUT,0,PMTR10,OUT,0,PMTR1)@

This uncovered a hidden error:

"String too long Go back and edit the data." 

The stored procedure was returning data of type char(255) and the column where the sqlexecproc was running was of type char(6).

Resolution:

The first error was that the schema name was not fully qualified by pre-pending MNFMTEST.  Once MNFMTEST was pre-pended, this resolved the query error message.  

This uncovered the next error - "String too long Go back and edit the data." 

To resolve this, the stored procedure was returning data of type char(255), and the column where the sqlexecproc was running was of type char(6). It was changed from 6 to a 600, saved, re-tested, and it worked.