DSXchange

Our group is attempting to use the STP stage to invoke mainframe DB2 stored procedures. Our DBA is telling us that we somehow need to pass a "Collection ID" as part of our stored procedure call in order to properly invoke it.

So far, the best solution we have is for the DBA to hard-code the collection ID as part of the stored procedure definition within DB2 itself. When he does that, we can use the "Generate Procedure Call" option on the "Syntax" tab to successfully execute it:

CALL SYSPROC.<procedure name>(?, ?, ?);

Not being a DB2 DBA, I can't tell you how exactly how he's doing the hard-code workaround, but it appears to work.

To simulate this in DataStage, The DBA has suggested the following variations on the call statement in order to pass in the collection ID:

CALL <Collection ID>.SYSPROC.<procedure name>(?, ?, ?);
CALL SYSPROC.<Collection ID>.<procedure name>(?, ?, ?);
CALL SYSPROC.<procedure name>.<Collection ID>(?, ?, ?);

SET CURRENT PACKAGESET = '<Collection ID>'; CALL SYSPROC.<procedure name>(?, ?, ?);

The last variation is written as two statements separated by a space (the STP syntax editor does not allow carriage returns).

I've tried each of these as custom-written calls, but the first three cause the job to abort with the following error message:

APT_CombinedOperatorController(0),0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL1097N The node name was not found in the node directory. SQLSTATE=42705

The last variation aborts with this message:

APT_CombinedOperatorController(0),0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES statement. SQLSTATE=42612

In advance, thanks for any insights that anyone can share.