Re: Help with SQL Procedure

Hi Greg,

Thanks for reaching out.

Am 12.08.2024 um 16:59 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

My SQL Procedure/Function was basically gone. It still existed within the SQL Plan for that specific SQL View - but I could not run the code to recreate the SQL view because the SQL function no longer exists on our system.
It was not in SYSROUTINES or SYSFUNCS.

These are those that can drive you insane. It’s really strange, that the UDF is still cached in the access plan. But we had such cases too.

The function object is likely gone because it could not be saved or restored. Evidently, it was not saved from our old power7+, and/or it was not restored onto our "new" power9 back in 2021.
https://www.ibm.com/support/pages/stored-procedures-restore-overview

Restoring is one of the really complex processes. It could have been, that the UDF was temporarily created by restoring the program or service program (the SQL source is cached in the *PGM or *SRVPGM object when doing CREATE FUNCTION …) - and after that, the program object moved or was re-compiled, and the SQL function gets lost.

IBM STRONGLY RECOMMENDS saving the SQL Source for creating the procedure or function. For reasons I won't go into, I'm saving it in a source member on the IBM i.

Were doing exactly the same - ever UDF and SP has its source in a member - were still not using IFS sources, because they are somehow inconvenient.

Thanks for closing this thread.

Regards,
Daniel