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

book

Article ID: 8043

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

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 

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).

Environment

TDM 4.1DB2

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.