Re: How to create a sql procedure which doesn't propagate a sql package to the remote system?

[Rob Berendt <robertowenberendt@xxxxxxxxx>]

If I do the update from outside of a stored procedure I do not need a
sqlpkg.  Why do I have to have it when I am using a stored procedure?

On Tue, Oct 17, 2023 at 9:02 AM Therrien, Paul via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Maybe don't use 3 part naming in the update command?
>         UPDATE GDI . QUSRBRM . QA1AFS
>         SET FSXCMD = OMIT_LIST
>         WHERE FSNAME = 'QLNKOMT' ;
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
> Berendt
> Sent: Tuesday, October 17, 2023 8:58 AM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: How to create a sql procedure which doesn't propagate a sql
> package to the remote system?
>
> I have a sql stored procedure which basically looks like the following:
>
> CREATE PROCEDURE robxyz.PROPAGATE_QLNKOMT()
>          LANGUAGE SQL MODIFIES SQL DATA
>         SET OPTION DATFMT = *ISO
>
> P1 : BEGIN
> DECLARE OMIT_LIST CHARACTER VARYING ( 32000 ) ;
>
> SELECT FSXCMD INTO OMIT_LIST
> FROM QUSRBRM . QA1AFS
> WHERE FSNAME = 'QLNKOMT'
> ;
> UPDATE GDI . QUSRBRM . QA1AFS
> SET FSXCMD = OMIT_LIST
> WHERE FSNAME = 'QLNKOMT' ;
>
> ...
>
> ...
>
> ...
>
> The problem is, when I make a change to it and call it again the UPDATE
> GDI ..... statement fails to do the desired updating and I get:
>
> SQL0818 - Consistency tokens do not match -
>
> Cause . . . . . : Package PROPA00001 in ROB on application server GDIHQ
> cannot be run with the current application program because either the
> application program has been recompiled without rebuilding the package or
> the package has been restored from a back level version. Recovery . . . :
> Rebuild the package by using the CRTSQLPKG command or by using a CRTSQLxxx
> command specifying the correct relational database. Otherwise, restore the
> package from a version which matches the application program being run.
>
> Which basically forces me to rebuild the sql package on each target
> machine.  Heck I'm not even calling a stored procedure on each target
> system so why do I need the package?  Is there some option on CREATE
> PROCEDURE to it not to need a target sql package?  Like a SET OPTION
> statement or some such thing?
>
> I didn't see an option on the CALL to handle this
> --
> 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.