calling stored procedure from cobol - bind error

Disco_Stu · #1 (**permalink**) 09-26-05, 16:25

I am trying to call a DB2 stored procedure from a cobol program. Up to this point, I have been calling them from ASP pages. I want to get this to work so that the host programmers can test the stored procedures on their own, in an environment thats comfortable.

I have found the correct syntax to make the call withing my cobol program.

Code:

 
EXEC SQL                                
  CALL  M7476M94(:SEND-RECORD-TYPE-ID,  
                 :SEND-VER-ID,          
                 :RTN-PROCESSING-DATE,  
                 :RTN-DAILY-CYCLE-STS-CD
                 :RTN-RETURN-CODE)      
END-EXEC.

My cobol calling program (M747TEST) compiles fine. Next step is to bind it. Here is where the trouble starts. Here is my jcl.

Code:

 
//STEP01 EXEC PGM=IKJEFT01                           
//STEPLIB  DD DSN=DB2VST.DSNLOAD,DISP=SHR              
//DBRMLIB  DD DSN=ENDH1.B02CL003.SBATDBRM,DISP=SHR   
//SYSTSPRT DD SYSOUT=*                               
//SYSPRINT DD SYSOUT=*                               
//SYSOUT   DD SYSOUT=*                               
//ABENDAID DD SYSOUT=*                               
//SYSUDUMP DD SYSOUT=D                               
//SYSTSIN  DD *                      
  DSN SYSTEM(TDH1) RETRY(5)          
   BIND PACKAGE(C2323LOSSDBT) -      
   MEMBER(M747TEST) -                
   EXPLAIN(NO) -                     
   ACTION(REPLACE) -                 
   OWNER(TD7476ST) -                 
   VALIDATE(BIND) -                  
   ISOLATION(CS) -                   
   FLAG(I)                           
   END                               
/*                                   
//

We keep our stored procedures in a product called Endevor. I am a new programmer, and have only worked in 1 company so I'm not sure how common this is. It is basically a mainframe source code management system. It is made up of several stages, the highest (stage 6)being production, the lower ones being test.

In my bind, if the stored procedure is in stage 6, the bind works fine. I can run the calling program and everything works. However, if the stored procedure is not in stage 6, which will always be the case since we are testing it, then i get a bind error -440. Cannot find program.

So I started looking at the JCL for the bind. And I now wonder how it knows where to look for the stored procedure. I dont see anything pointing to specific libraries. But it is like it is defaulting to our stage 6 (production) libraries.

Does that make sense to anyone? I would like to be able to point it to the right libraries so it can find stored procedures that are lower in the Endevor food chain and bind correctly.

Thanks.

schintala · #2 (**permalink**) 09-27-05, 11:33

SQL Code -440 is not a bind error. Name of SP not found. You need to talk to your sysadmn/DBA in your shop to get the name of library for stored procedures. This should be concatenated in Stored Procedure Address Space JCL. BTW, when you compile your stored procedure program through Endevor what is library Endevor points to?

In our shop we expictly copy the SP loads in to different library which is concated with WLM step lib.

Disco_Stu · #3 (**permalink**) 09-27-05, 11:45

thanks schintala,

Maybe my subject was misleading. I just meant I'm getting an error while trying to do a bind.

In Endevor my stored procedure sits in the following libraries:

DBRM - ENDH1.A0XTIC01.SDB2SPDB
compile - ENDH1.A0XTIC01.SDB2SPLD

So I know where its sitting, I just dont know how to tell my bind JCL where to find it. So are you saying this is something DA sets up?

schintala · #4 (**permalink**) 09-27-05, 12:01

In Bind JCL you would use the DBRM Lib where your SP Program resides. Did you check SP entires were created in Catalogue tables SYSROUTINES and SYSPARM. Before you compile and bind there should be an entry in these tables.

Disco_Stu · #5 (**permalink**) 09-27-05, 12:18

I found entries in both tables.

And in my JCL, I tried concatenating the DBRM lib where my stored procedure is sitting - same error.

I'll mention this again, because I think it could be the key to something. If the stored procedure I call is in production -

DBRM - ENDH1.A06TIC01.SDB2SPDB
compile - ENDH1.A06TIC01.SDB2SPLD

The JCL works fine. I dont even have to point to them. I only point to the DBRM library of the calling program. So to me it appears that it somehow defaults to the production (stage 6) libraries when it looks for the called stored procedure. If I knew how, then I could overrride that somehow. I could be off, but thats where I'm headed with this at this point.

Disco_Stu · #6 (**permalink**) 09-28-05, 11:14

Figured it out. I needed to qualify my program name with the schema ID (if one other than SYSPROC is used). Other wise the default is SYSPROC. Thats why some worked and some didnt. We have currently have some of each.

Internet Services

Web Development

Digital Marketing

Consumer Tech