I had been researching just this problem recently. I tried running your script and learned how to set commit correctly. I still have errors but they must related to our work environment, SQL 42704. I would hope the official version would be more forgiving.
Original Message:
Sent: Thu December 19, 2024 01:37 AM
From: Jérôme CLEMENT
Subject: QSYS2 exported procedure
Hello,
Thank you Vincent.
The idea number is IBMI-I-4317
I created it yesterday, I hope it will be accepted...
------------------------------
Jerome CLEMENT
IBMi Modernization Manager
OCSI
France
Original Message:
Sent: Wed December 18, 2024 07:24 PM
From: Vincent Greene
Subject: QSYS2 exported procedure
I think that SQL gives all of the procedures that COULD be used by your program, not all of the procedures that ARE used by your programs. To get that detail you would also need to match the module procedure imports to the service program procedure exports.
You can get module imports from DSPMOD to an outfile with DETAIL(*IMPORT), or the List Module Information (QBNLMODI) API format MDL0200. As far as I can tell, there is no SQL service currently to access this. This operates on module objects because by definition, programs will have no unresolved imports once they are fully bound.
I completely agree that having this information -- procedures imported at a module level within a program -- would be extremely valuable. To be truly useful it would be an attribute of the program that is saved with the program so it can be retrieved by dsppgm or the program APIs, and even better if available to a SQL service. There are too many use cases where *MODULE objects are not retained or distributed.
Please post the idea number when you've created it so we can vote on it.
------------------------------
Vincent Greene
IT Consultant
Technology Expert labs
IBM
Vincent.Greene@ibm.com
The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Original Message:
Sent: Tue December 17, 2024 05:17 AM
From: Jérôme CLEMENT
Subject: QSYS2 exported procedure
Hi,
I can find the information with the next sql sentence :
SELECT A.PROGRAM_LIBRARY AS "SERVICE_PROGRAM_LIBRARY",
A.PROGRAM_NAME AS "SERVICE_PROGRAM",
A.OBJECT_TYPE AS "OBJECT_TYPE",
A.SYMBOL_NAME AS "EXPORTED_PROCEDURE",
B.PROGRAM_LIBRARY AS "PROGRAM_USE_EXPORTED_PROCEDURE_LIBRARY",
B.PROGRAM_NAME AS "PROGRAM_USE_EXPORTED_PROCEDURE",
B.OBJECT_TYPE AS "PROGRAM_USE_EXPORTED_PROCEDURE_TYPE",
TRIM(C.SOURCE_FILE_LIBRARY) CONCAT '/' CONCAT TRIM(C.SOURCE_FILE)
CONCAT '(' CONCAT TRIM(C.SOURCE_FILE_MEMBER) CONCAT ')' AS PATH_SOURCE
FROM QSYS2.PROGRAM_EXPORT_IMPORT_INFO A
LEFT JOIN QSYS2.BOUND_SRVPGM_INFO B
ON B.BOUND_SERVICE_PROGRAM = A.PROGRAM_NAME
LEFT JOIN QSYS2.BOUND_MODULE_INFO C
ON C.PROGRAM_LIBRARY = B.PROGRAM_LIBRARY
AND C.PROGRAM_NAME = B.PROGRAM_NAME
WHERE A.PROGRAM_LIBRARY = 'my_program_library'
AND A.PROGRAM_NAME = 'my_service_program'
AND B.PROGRAM_LIBRARY = 'my_program_library'
AND LOCATE_IN_STRING(GET_CLOB_FROM_FILE(TRIM(C.SOURCE_FILE_LIBRARY) CONCAT '/' CONCAT TRIM(C.SOURCE_FILE)
CONCAT '(' CONCAT TRIM(C.SOURCE_FILE_MEMBER) CONCAT ')', 1), A.SYMBOL_NAME) > 0;
With commit control active for the GET_CLOB_FROM_FILE...
So i know how retrieve the programs who use my exported procedure but i wanted to know if it'll be possible to have a futur QSYS2 Table with this information directely...
Thank you
------------------------------
Jérôme CLEMENT
IBMi Modernization Manager
OCSI
Paris
Original Message:
Sent: Tue December 17, 2024 12:52 AM
From: Daniel Gross
Subject: QSYS2 exported procedure
In fact - What procedures are exported by our service programs? - can be answered with the view PROGRAM_EXPORT_IMPORT_INFO .
But we still have no information about which programs use which procedure.
Tools like CMOne have their own solutions for that problem, and mostly use BOUND_SRVPGM_INFO and source code analyses for that purpose.
HTH
Daniel
Original Message:
Sent: 12/16/2024 8:59:00 PM
From: Satid S
Subject: RE: QSYS2 exported procedure
Dear Jerome
I find that what you described as information on "What programs use our service programs." is BOUND_SRVPGM_INFO and BOUND_MODULE_INFO views. But I cannot find what it is that gives you information on "What procedures are exported by our service programs.". Is this also another IBM i service view? If so, you should be able to do a query joining these two to get want you asked for. If it is an API, you may try creating a table function from it so you can join it with BOUND_SRVPGM_INFO.
------------------------------
Satid S
Original Message:
Sent: Fri December 13, 2024 10:59 AM
From: Jérôme CLEMENT
Subject: QSYS2 exported procedure
Hello
QSYS2 functions and tables allow you to know:
- What procedures are exported by our service programs.
- What programs use our service programs.
But it is not possible to know which programs use which exported procedure.
This is a pity because it involves developing to look in the sources of the programs that use the service programs to find out which exported procedures are used.
Do you think it would be possible to have a new table in SQYS2 that would store this information?
Thank you
------------------------------
Jérôme CLEMENT
------------------------------