RE: Help with SQL Procedure

Daniel,
I continued this discussion on midrange list.

But, just in case someone finds this in a search...

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.

I had to recreate it from scratch because I had lost the source.

How did this happen?
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

Maybe when I created it, I missed the SQL7909 Routine SIMPLE was created, but cannot be saved and restored.
Maybe it was because I created the Function in a library different from the library where the Service Program existed?

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.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Friday, August 9, 2024 4:34 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL Procedure

What does

SELECT *
FROM SYSROUTINES
WHERE ROUTINE_NAME LIKE '%TBF_SHIPBYDATE%'

show?

A SQL function which is implemented as external is only an entry in SYSROUTINES - no additional object is created.

HTH
Daniel

Am 09.08.2024 um 16:27 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

Charles... thanks.

The view still works... And regenerating the SQL source for the view is where I got the syntax below.
My user profile has *ALLOBJ.
I tried RCLDBXREF OPTION(*FIX) for that library.

I will post the question in midrange.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Friday, August 9, 2024 10:04 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL Procedure

One of the columns selected in my view is
ASTHHOBJ.TBF_SHIPBYDATE(PIK_DATE, PIK_TIME,PTY_CODE),

That show's it's definitely being used as a Function, so I'd be looking at the function node.

Does the view still work? When you generate the source for the view, does it show the above invocation ?

(WAG)
Does your user ID have *ALLOBJ? If not, maybe you can't see it?
Could try issuing GRANT or ALTER FUNCTION commands to see if you get a NOT ALLOWED or NOT FOUND.

Otherwise, I seem to recall an old issue where objects would fail to show in iNav..
Might try a RCLDBXREF OPTION(*FIX) for just that library.

This question is probably more appropriate for MIDRANGE-L rather than RPG-L.

Charles
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231