Identifying SQL function dependencies

25 pts.
Tags:
SQL
Hello,

Our Iseries site is a heavy user of embedded SQL. We have over the past few months started using SQL UDFs and have recently been found out when the dependent UDFs have not been deployed along with the main deployed code. 
Would anyone know if there is an easy way of identifying dependent SQL UDFs used by deployed programs and if they were used, whether these UDFs were installed in the deployed environment.

Many thanks

Malik


Software/Hardware used:
Power Systems running IBMi / SQLRPGLE

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Since SQL functions can be dynamically prepared there can be no way to guarantee that any function is used by inspecting a program from the outside. However, running PRTSQLINF over a program object will print a list of SQL statements that are available from the program. (Keep in mind that one major purpose of encapsulating program logic in a *PGM object is to keep it hidden and even inaccessible, not to make it easy to examine.) The SYSROUTINE table in the target system catalog will provide a list of functions defined in any schema on that system. -- Tom
    125,585 pointsBadges:
    report
  • IHDCSL
    Hi Tom,

    thanks for your help. Yes, I was thinking of the PRTSQLINF command. Is there a way of examining an SQL without actually running it (similar to Visual Explain) that attempts to create an access plan for the sql? This would actually give me what I am looking for because if the Access Plan does not get created then I would know that there is an issue with the embedded sql hence the program that uses it. Ideally if the PRTSQLINF attempts to create the access plan itself in the process of analysing the sql, that would be the ideal. Many thanks. Malik
    25 pointsBadges:
    report
  • TomLiotta

    ...if the Access Plan does not get created then I would know that there is an issue with the embedded sql hence the program that uses it.

    An access plan is only possible and meaningful for static SQL, so it still comes under "no guarantee". Unless the program itself runs, there is no way to know if it will construct a statement that uses any given SQL element. PRTSQLINF prints everything that can be known.

    How would the construction of an access plan help beyond that? If PRTSQLINF can't list it, then nothing short of program execution can do any better. And if it's a dynamic statement, there will not be an access plan to look at.

    Tom


    125,585 pointsBadges:
    report
  • IHDCSL
    Hi Tom,

    thanks for this. 

    Kind regards

    Malik
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following