Resolve object references for embedded SQL at compile time

IBM does not intend to provide a solution to this request at this time, so it is being closed. SQL services are being developed as an alternate interface for CL commands and other IBM i information. IBM prefers to invest resources in an SQL service rather than in an enhancement to the DSPPGMREF CL command.

An RFE to return DSPPGMREF as an SQL Service that is being considered. SQL module level information could be added to this service:
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=137655

Module level SQL references can be returned today with the following query:

select program_schema, program_name, module_name, statement_text, p.*
from qsys2.sysprogramstmtstat,
table (qsys2.parse_statement(statement_text)) p
where program_schema = 'MYLIB'
and program_type = '*MODULE';

The CAAC has reviewed this requirement and recommends that IBM view this as a high priority requirement that is important to be addressed. Since DSPPGMREF includes *MODULE as an object type, we feel that this information should be available there for consistency.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CAAC, see www.common.org/caac

For more details about CAAC's role with RFEs, see http://www.ibmsystemsmag.com/Blogs/i-Can/May-2017/COMMON-Americas-Advisory-Council-%28CAAC%29-and-RFEs/

Nancy Uthke-Schmucki - CAAC Program Manager

The fact that the bind is able to include the information in the SRVPGM makes me believe it is available in the MODULE object as well but somehow not shown. I hope IBM pays attention to this as it is major drawback for SQL and proper change management.

Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Db2 for i
Operating system - IBM i
Source - None

For recording keeping, the previous attributes were:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Languages - RPG
Operating system - IBM i
Source - None

We have the same problem. When we use a SQL in a *MODULE object and then do DSPPGMREF on the module, the file references DO NOT appear. However, if we subsequently bind the module in a service program, we do see the references when we do DSPPGMREF on the service program.

Apart from the fix, any workarounds would be welcome.

Example module source:

**free
ctl-opt nomain;
/copy QPROTOSRC,SQLREL01

// --------------------------------------------------
// Procedure name: READ
// Purpose: Read a record
// Returns:
// Parameter: SQL => SQL Instruction
// --------------------------------------------------
DCL-PROC ReadRecord EXPORT;
DCL-PI *N;
SQL CHAR(250);
END-PI ;

exec sql
insert into omrel (elobjc)
select 'A'
from omobj
where rjobjc = 'DDD';
return ;
END-PROC ;


### procedure prototype
// --------------------------------------------------
// Prototype for procedure: READ
// --------------------------------------------------
DCL-PR ReadRecord;
SQL CHAR(250);
END-PR ;