MVSFORUMS.com

Abhi · Posted: Sat Jan 17, 2004 1:40 am Post subject: Nested stored procedures

I have 2 SPs say SP1 & SP2, one being called in the other. The problem is that I am not able to call the second SP (SP2) from the main SP (SP1)which itself is called from a batch COBOL program.

It fails with a SQLCODE of -471 and a dump of the error says that -
[code:1:0783b24e3d] SQLCA h

kolusu · Posted: Sat Jan 17, 2004 11:48 am Post subject:

Abhi,

A couple of questions.

1.How are you calling your SP2 from SP1 ? Are you calling it using EXEC SQL CALL SPNAME(SP2) ?.

2. If your using regular CALL statement to invoke SP2, then just make sure that you have DDF(Distributed Data Facility ) was started and active.

3. Also let us know as to how you are passing the parameters and call statment.

4. What level of cobol are your using COBOL II , COBOL390 ...? If your are using COBOL-II then make sure that your are link editing with language interface modules

You can have Cobol as your language for SP's

I would recommend the following Redbook on building and debugging Stored procedures.

http://www.redbooks.ibm.com/redbooks/pdfs/sg245485.pdf

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Abhi · Posted: Mon Jan 19, 2004 1:59 am Post subject:

Hi,

We are using

EXEC SQL
CALL SP2 ( :PARM1 :IPARM1,:PARM2 :IPARM2)
END-EXEC.

to call the SP2 from SP1.

Both batch program as well as SP's are coded using COBOL370 as language.

If i am running the command DISPLAY DDF its giving

DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I USNETDB2THFC USNET.DB2THFC -NONE

Along with this it also displays IPADDR,TCPPORT and RESPORT details.

Does this means that DDF is started and active? In that case what could be the reason for getting the error message?

In SYSIBM.SYSROUTINES table we tried with 'S' for PROGRAM_TYPE for SP2. Even then it didn't work

We have WLM_ENVIRONMENT for both the SP definitions. The parameter WLM_ENV_FOR_NESTED is specified as 'N'. COMMIT_ON_RETURN is also 'N'.

Advice on this issue will be of great help,

Thanks,
Abhi

kolusu · Posted: Mon Jan 19, 2004 9:42 am Post subject:

Abhi,

Try pre-compiling the stored procedure with parameter 'ATTACH(CAF)' adn see if it works.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Bithead · Posted: Mon Jan 19, 2004 12:37 pm Post subject:

Try increasing the ASUTIME Limit on SP1 to account for the extra time taken in SP2. This may work.

Abhi · Posted: Wed Jan 21, 2004 1:18 am Post subject:

Hi Kolusu, Bithead,
I tried with both ur suggestions but nothing seems to work. We are increasingly beginning to belive that this has something to do with the WLM environment not being properly set up. The trouble is we are unable to push this with the DBA and so have decided to merge the 2 SPs into one (tight delivery dates Confused

).

Thank u all the same. We fortunately have access to create SPs in our development region so we can explore more.

However Kolusu, we use Xpeditor in our shop as the debugger, and the precompiler accepts the 'ATTACH(CAF)' parameter only if the Xpeditor option is set. This might be a installation setting but I don't quite get why it would be necessary.