DB2 - L

 View Only
  • 1.  APPLCOMPAT not updating for Stored Procedure

    Posted 4 days ago
    We have few procedures with APPLCOMPAT(V10R1) and we wanted to move them to APPLCOMPAT(V12R1M500). In order to do that we ran Alter procedure...generate using application compatibility v12r1m500 command. Even though command is successful with CC 00, some of the procedure's APPLCOMPAT value in syspackage is still V10R1.

    Anyone has any information what could be the reason behind that?

    ------------------------------
    SumitSharmaDatavail
    ------------------------------


  • 2.  RE: APPLCOMPAT not updating for Stored Procedure

    Posted 4 days ago
    I can only think that Db2 decided that there was nothing in V12R1M500 (or V11R1) that the
    procedures could use - so didn't bother rebinding their packages.

    What makes you think there was - and hence why did you alter the procedures?

    James Campbell

    On 22 Sep 2022 at 17:14, Sam Kaushik via International wrote:

    > We have few procedures with APPLCOMPAT(V10R1) and we wanted to move them to APPLCOMPAT(V12R1M500). In order to do that we ran Alter procedure...generate using application compatibility v12r1m500 command. Even though command is successful with CC 00, some of the procedure's APPLCOMPAT value in syspackage is still V10R1.
    >
    > Anyone has any information what could be the reason behind that?
    >
    > ------------------------------
    > SumitSharmaDatavail
    > ------------------------------
    >




  • 3.  RE: APPLCOMPAT not updating for Stored Procedure

    Posted 4 days ago

    Hello,

    a few days ago we tried to do the same thing and only for a few procedures the APPLCOMPAT had not changed (e.g. the SYSIBMADM.CREATE_WRAPPED).

    We opened a case with IBM and here is the solution that worked

     

    In order to solve this, you can run the ALTER PROCEDURE APPLCOMPAT, without the REGENERATE option. Here is the explanation given by development in a similar case (in that case the example was for M500, but it's the same for M503):

    .

    According to developer, there are 2 APPLCOMPAT values for DDL/Native SQL PL Proc. There is one that's tied to the 
    Procedure statement (CREATE PROCEDURE SYSTOOLS.REGSP...). And there is another one that is stored in SYSPACKAGE for 
    the procedure itself.
    .
    The one that is tied to the create procedure statement is the one recorded in SYSENVIRONMENT and it governs/limit 
    what the statement can be and what options can be specified. For example, if the APPLCOMPAT value for the CREATE 
    statement is only at M500, it cannot have options that is higher than M500.
    .
    The APPLCOMPAT that is recorded in SYSPACKAGE is tied to the procedure option APPLCOMPAT that is specified in the 
    body itself.
    ...
    The cheapest and most simple way to correct an incorrect/outdated value on column APPLCOMPAT in SYSPACKAGE it is to 
    drop and recreate the procedure after they rebind the tep2 job to M500+. This way, both the environment and the 
    procedure will pick up the M500.
    .
    If dropping and recreate is not an option, then the way to fix it is to run:
    .
    - ALTER PROCEDURE REGENERATE USING APPLICATION COMPATIBILITY V12R1M500 statement. This will fix the environment 
    APPLCOMPAT value in SYSENVIRONMENT.
    .
    - then run an ALTER PROCEDURE APPLCOMPAT V12R1M500 statement. This will update the SYSPACKAGE and SYSPACKSTMT value 
    to V12R1M500

    .

    So the ALTER PROCEDURE REGENERATE USING APPLICATION COMPATIBILITY will update the APPLCOMPAT in SYSENVIRONMENT, but to update the APPLCOMPAT which figures in SYSPACKAGE you will sometimes need to perform an ALTER PROCEDURE with just APPLCOMPAT Keyword as it is detailed by development on the above update.

     

    I know that we were looking into possibly updating the documentation to better reference this behaviour - that in order to "update" the value which appears on syspackage it could be necessary to run, besides the ALTER PROCEDURE REGENERATE USING APPLICATION COMPATIBILITY, an ALTER PROCEDURE APPLCOMPAT).

     

     

     

    Ermanno Bertolotti

     

    Intesa Sanpaolo

    Group Chief IT, Digital and Innovation Officer Area

    CIDIO Direzione Cent Sistemi Informativi

    DC Sist Infrastrutture Tecnologiche

    DC Sist Soluzioni Infrastrutturali Dati – CC Database DB2

     

    Via Langhirano 1, 43125 Parma (PR)

    Cell +39 348.6723316

    Fax  +39 0521.918337

    ermanno.bertolotti@intesasanpaolo.com - st.progettazionemf.db@intesasanpaolo.com

     

     






  • 4.  RE: APPLCOMPAT not updating for Stored Procedure

    Posted 4 days ago
    Fascinating... I do hope they try and clear up the documentation here...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich