Determining Stored Procedure Associated With Program
Last Post 15 Mar 2013 01:44 PM by Arthur Blose. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Ron Jones
New Member
New Member
Posts:22

--
01 Mar 2013 09:48 PM

I know you can use System Navigator to list all the stored procedures in a specific schema (library) and in that list you can see the program that each stored procedure calls. Is there anyway you can look at that list from the prospective of the program and see a list of all the stored procedures associated with it? Something like a DSPPGM command/option to show that list?

Thanks,
Ron


Arthur Blose
New Member
New Member
Posts:24

--
02 Mar 2013 04:31 AM

I'm sure there are better ways to do it, but I spend most of my time with SPs, (mainly EXTPRC), so I just mine the data out of SYSPROCS.

We use all service programs, so if you wanted to see all the SPs related to a given program/srvpgm, you could do somethng like this:

 

select  substr(specific_name,1,10),
substr(external_name,1,50),
routine_name
from sysprocs
where external_name like '%FMA01S000%'

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+
SUBSTR      SUBSTR                                              ROUTINE_NAME                  
FMA01Q005U  PRJ4634/FMA01S000(DOCK_UPDATEFACILITYTYPE_SP)       DOCK_UPDATEFACILITYTYPE_SP    
FMA01Q006C  PRJ4634/FMA01S000(DOCK_ADDFACILITYSTATUS_SP)        DOCK_ADDFACILITYSTATUS_SP     
FMA01Q006D  PRJ4634/FMA01S000(DOCK_DELETEFACILITYSTATUS_SP)     DOCK_DELETEFACILITYSTATUS_SP  
FMA01Q006L  PRJ4634/FMA01S000(DOCK_GETFACILITYSTATUSES_SP)      DOCK_GETFACILITYSTATUSES_SP   
FMA01Q006R  PRJ4634/FMA01S000(DOCK_GETFACILITYSTATUS_SP)        DOCK_GETFACILITYSTATUS_SP     
FMA01Q006U  PRJ4634/FMA01S000(DOCK_UPDATEFACILITYSTATUS_SP)     DOCK_UPDATEFACILITYSTATUS_SP  
FMA01Q007C  PRJ4634/FMA01S000(DOCK_ADDREGIONTYPE_SP)            DOCK_ADDREGIONTYPE_SP         
FMA01Q007D  PRJ4634/FMA01S000(DOCK_DELETEREGIONTYPE_SP)         DOCK_DELETEREGIONTYPE_SP

A standard program call, should just have library/object, with no begin paren/procedure name/endparen. The wildcard search should help you find it.

 

Ron Jones
New Member
New Member
Posts:22

--
02 Mar 2013 11:15 PM

Arthur,

Thanks for your reply. I was aware of the QSYS2.SYSPROCS table. That is what system navigator uses in its display. What a lot of people are not aware of is that the actual called program is "stamped" with the stored procedure reference(s) when CREATE or DROP PROCEDURE is run. What I was looking for was a way to query the program object itself to see what stored procedure(s) it is stamped with.

Many people don't realize that when you restore, replace, move a version of a program that is referenced by stored procedure(s) it will actually change the SYSPROCS records. Also the stamping of any one program is limited to 32 stored procedure references. You'll get an SQL warning message (SQL7909 I think) when you attempt to add the 33nd stored procedure reference. This is a limitation we have had to be careful with because we use a generic program that serves many stored procedures. The generic program does common stuff and then passes stored procedure specific parameters to the actual worker bee program associated with the stored procedure. If we are not careful when we monkey with our generic program then things go south in a hurry.

Once again thanks for taking the time to reply.

Ron

Ringer
Veteran Member
Veteran Member
Posts:1748
Avatar

--
03 Mar 2013 01:02 PM
Yes, a restored object can create duplicate entries in SYSPROCS. I've seen it. Sometimes I have to drop them all and recreate the one.

http://www.itjungle.com/mgo/mgo0820...ory02.html

Chris Ringer
Arthur Blose
New Member
New Member
Posts:24

--
03 Mar 2013 01:55 PM

There is some very suspect behavior with service programs and stored procedures at V7R1. I'm not sure this is exactly what you are describing, but here's my issue:

We have service programs (a number of them actually) that each contain 7 or 8 modules. These modules are each related to a table. Each module has exported procedures for get, get list (result set for a client), add, update, delete, and possibly some specialized ones. Also many private procedures. So that means, for one service program, possibly 40 or or more exported procedures that reference SQL procedure definitions. When I promote (through turnover) from dev to test or from test to prod, a large number of SP definitions get deleted. I'm not sure if this is a TO, IBM, or combination of the two that is causing it, but the upshot is that the SP definitions disappear, and the websphere and c-sharp guys start crying, as that is the interface to the system.

I then have to check out all the missing SPs, and run them back up through TO after the SRVPGM to restore everything to it's normal status. 

 

Looking at SYSPROCS, I still see my procs, but with changed library names, usually those TO uses for rollback purposes on a form if it goes into recover mode.

 

My only solution has been a brute-force method of doing an exception join of QSQLSRC in the to environment to SYSPROCS to see what got wiped out. I then check those out, promote them and get back to a matched relationship between SP source and registered procedures.

I'm sure IBM is trying to do something to help those who restore libraries to a remote system without change control, but the 32-procedure limit and the strange behaviors aren't doing us any favors. It would be nice to be able to control the behavior, possibly with a parm on the SRVPGM create, system value, something? Anyone see anything similar?

Art

 

 

Ron Jones
New Member
New Member
Posts:22

--
03 Mar 2013 02:21 PM

Thanks for the link Chris.

As far as I am concerned the whole "stamping" thing is clunky and causes more headaches than it solves. Just let me restore the program/service program called by the stored procedure like any other. Let me worry about making sure the SYSPROCS table is right through appropriate SQL means (DROP, CREATE, LABEL, etc.). I had to do that anyway during development of the stored procedure and associated program. Why should it be any different in production or other environments?

In our shop we create pseudo source that contains the actual SQL statements used to DROP, CREATE and otherwise modify the stored procedure itself. If the procedure's characteristics (typically parameters) change then we'll move the SQL source along with the program through our development cycle. If nothing about the procedure changes, and its just the underlying code that is being modified (the most common scenario), then just the program is taken through development cycle.

Ron

Arthur Blose
New Member
New Member
Posts:24

--
15 Mar 2013 01:44 PM

For those who are on V7R1, and have restored service programs messing with your sysprocs entries, IBM has a solution:

 

ADDENVVAR ENVVAR(QIBM_SQL_NO_CATALOG_UPDATE) LEVEL(*JOB)

 

(or *SYS to make it global). This will revert back to V6R1 behavior. We aren't allowed to do it system-wide, so we put it as a pre-process form command in Turnover. Since using it, our SPs don't get wiped out anymore. The IBM link explaining it is

 

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/IBM%20i%20Technology%20Updates/page/Improved%20catalog%20management%20for%20procedures%20and%20functions?lang=en

You are not authorized to post a reply.

Acceptable Use Policy