RE: Help with SQL Procedure

OK... So IBM got back to me.

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

Maybe when I created it, I missed the SQL7909 Routine SIMPLE was created, but cannot be saved and restored.

The View still works because the "Access Plan" is stored with the view.
As of right now, I cannot recreate the view or even run the select statement within the view.

IBM suggested connecting to the old box to regenerate the SQL and saving that in a source or IFS file. I'm fairly certain I don't have it.

Throwing this out here in case someone else ends up with the same issue.

YUCK!


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Javier Sanchez
Sent: Friday, August 9, 2024 12:31 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL Procedure

Re-reading your messages, I believe you are talking about the other function that is internal in your SRVPGM (??). If so, I believe you won't find it in sysprocs/sysroutines because you may not have registered it as an SQL Public function, but kept it as an internal, utility function within your SRVPGM. In that case, yes, it still works because it is IN the SRVPGM itself. I think you may be confident that if you recompile/regenerate everything you won't have any issues.

HTH
JS

El vie, 9 ago 2024 a las 10:10, Greg Wilburn (<
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>) escribió:

Thanks..

I scoured sysprocs and sysroutines for any references to my function.
I even downloaded it to Excel to really look through it.
I can see other functions I had created, but nothing referencing that
library or even "TBF" which I can clearly see in the function name.

I will try Charles suggestion to generate the SQL.

Feels like some kind of sorcery (or source-ery ha ha)



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Javier Sanchez
Sent: Friday, August 9, 2024 11:43 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL Procedure

The sysprocs and sysroutines tables/views have a column that stores
the original sql creation statement.
Use a select statement with the where routine_name = 'myroutine_name'.


El vie, 9 ago 2024 a las 9:33, Greg Wilburn (<
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>) escribió:
Greg Wilburn
Director of IT
301.895.3792 ext. 1231
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231