Compiled a pgm that is a Stored Proc, now not working
Last Post 23 Feb 2013 10:55 PM by ibmiiam. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
Frank
New Member
New Member
Posts:2

--
15 Feb 2013 04:22 PM

Hello,

We have a series of programs that were created in 2003 and then they were created as stored procedures in SYSPROCS via:

create procedure *Library/Program
result set 1                    
external name Library/Program  
language RPG general variant

This week a change was needed to one of the programs. after the program was compiled we used SQL to DROP the above procedure and used the same above command to recreate the procedure. The program is called from an outside application:

Case "PROGRAM" 'Monthly Formula

Set rs_LIBRARY_PROGRAM = cnIBM_MACHINE_NAME.Execute("{CALL LIBRARY/PROGRAM}", Rcds, adCmdText)

 

When the process is run the program is submitted to batch and dumps later in the program on a blank value,  because on a widely used JDE routine 'X0028' to convert *MDY to *JUL, the conversion is not executed and the return parameter is blank. When I submit this same program to batch it runs perfectly. But for some reason when it is called as a procedure it keeps dumping? I am not well versed on calling programs from outside applications as stored procedures and believe that it cannot be the newly compiled program, but something associated with it.

 

Any help or input is appreciated. If you need additional info please let me know.

 

Thanks,

Frank

Katty
New Member
New Member
Posts:11

--
18 Feb 2013 10:31 AM
If you use a stored procedure you have to check that all the library list are loaded at first time, before the JDE routine execute. Also check the permissions of the user that you used to connect to the AS
Ringer
Veteran Member
Veteran Member
Posts:1763
Avatar

--
18 Feb 2013 12:16 PM
Run the stored proc from iSeries Navigator. The backend connection job info is displayed there (QZDASOINIT). Put that job/program in debug (STRSRVJOB, STRDBG, etc) and walk through it...

Chris Ringer
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
19 Feb 2013 12:29 PM
Some issues I've had:

1) outside programs often use SQL naming rather than system naming. SQL naming does not use the library list except for the library list defined on the data source, which is just for locating non-database objects. The version that is listed as belonging to the default data base is the version of the procedure that gets run. Thus, if you have a library CpyData with all your files in it, and the procedure was registered in that library (procedures are normally registered in a data library), that will be the one that gets run, not the one in your test versions of programs library, regardless of library list. (This can be affected by paths, but I am assuming no special paths.) Thus TESTPGM might be where your new program is, but the procedure associated with CPYDATA may be explicitly running the program in PRODPGM. When you run the procedure in your own environment, whether interactive SQL, Navigator, or a program, it may run fine.

2) Output result set arrays and parameters need to be explicitly cleared to blanks and zeros. You can't let it default, because then you wind up with hex00, and this causes issues with the database logic and causes various errors, often referring to bigends. If yo;u've added a new output parameter, make sure it is getting initialized.







Lynne Noll
Senior Member
Senior Member
Posts:6567

--
19 Feb 2013 12:30 PM

Find out where procedure is registered: Select * from sysprocs where routine_name ='MYPROCEDURE' will give you the libraries where the procedure is registered. This will also let you know if it is overloaded.

select external_name from sysprocs where routine_name='MYPROCEDURE' and routine_Schema='MYLIB' will let you know where the program that is getting run exists.

 

If there is something wrong, and it is not finding the appropriate procedure, it may attempt to run the underlying program, if it can find that without its being defined.  (I know people who don't like having the procedure and program name being the same for this reason.)  It will then not be able to adjust the parameters to match, because it lacks meta data.  This can lead to decimal data errors.  Try renaming the program something different from the procedure, and adjusting the procedure definition to the new name.  This way, if the procedure does not resolve, you will get a not found rather than having the program itself called.

 

 

 

 

Viking
Basic Member
Basic Member
Posts:345

--
19 Feb 2013 01:38 PM
This doesn't address whatever technical issue you've got that is causing X0028 to not be found, but how about cutting X0028 out of the picture alltogether? Wherever your program currently calls X0028 to convert an MDY 6,0 field to the JDE CYYDDD format (not true Julian), replace that call with:

myDate = %date(MMDDYY:*MDY);
jdeDate = %int(%char(myDate:*longjul0)) - 1900000;

(or combine these two into one line if you want)

To convert a JDE date back to a date (and then back to MMDDYY if you want), do this:

myDate = %date(jdeDate + 1900000 : *longjul);
MMDDYY = %dec(myDate:*MDY);

Obviously you'd like to know the answer to your original question, but in cases of calling X0028, maybe you can just avoid the call completely and maybe it will even perform better since you'll be eliminating a dynamic program call.

My $.02.
Frank
New Member
New Member
Posts:2

--
19 Feb 2013 03:15 PM

Thanks for all of your input!

After checking, all of your suggestions, the programmer had a call in the program to load the library list, but put the call to object X0028 before loading the library list. This hasn't been tested but from reading your responses this make sense now. I'll let you know if this was indeed the problem.

 

Update: The library list was indeed the issue. The pgmr moved his code that calls another object below where the library list gets set and it worked!

Thanks again for all your help!

ibmiiam
New Member
New Member
Posts:38

--
23 Feb 2013 10:55 PM
Frank,
I see you corrected the problem in the stored procedures but let me add some info about a potential gotcha.

One thing to consider is that IBM changed the default handling of result sets since 2003 and, in my experience, it became necessary to code the SQL in the RPG program differently and add FOR RETURN TO CLIENT in the result set declaration


c/exec sql                                                         
c+   SET RESULT SETS FOR RETURN TO CLIENT ARRAY :product FOR :x ROWS
c/end-exec 


Maybe someone else has a better alternative?

Greg
You are not authorized to post a reply.

Acceptable Use Policy