CODEWITHSUNDEEP
sqlibm-midrangedb2-400
Abhishek Tiwari
Abhishek Tiwari

Reputation: 332

Output for DSPPGMREF is incorrect for some SQL objects

I am using DB2 i for writing SQL functions and procedures. CTE's (Common Table Expressions) are also used inside the body of these functions and procedures.

These SQL members are converted into objects using RUNSQLSTM command.

But if I am running DSPPGMREF on these objects to see program references, it is showing CTE as referred file whereas it is not an actual file.

Body of my function is as:

BEGIN
WITH SAMCTE AS
(SELECT COL1 FROM TABL1)
SELECT COL1 INTO ABC FROM SAMCTE;
RETURN ABC;
END;

Here actual file involved in TABL1, but DSPPGMREF is showing TABL1 and SAMCTE both as FILES, which is incorrect.

I have few sources from client in which also CTE is used but on their objects CTE are not shown as file. I am not getting what is wrong in my example.

Upvotes: 1

Views: 318

Answers (1)

Abhishek Tiwari
Abhishek Tiwari

Reputation: 332

When creating object for procedure and function using RUNSQLSTM make sure all the File/Table objects which are used in the Procedure and function are in library list or qualified.

If these are not in library list then DSPPGMREF will report some incorrect FILE references as in this case CTEs are reported as referred file.

Upvotes: 1

Related Questions