IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
 View Only

  • 1.  SQLRPGLE not using Overridden file

    Posted Tue February 06, 2024 05:08 PM

    Hello, 

    We have one SQLRPGLE program created on V6R1M0. In DSPPGM we can see these 3 Service Programs and is successfully using the overridden file in CL Program. (call from CL -> SQLRPGLE)

    QRNXIE
    QRNXIO
    QLEAW

    Now we need to change program and upgraded OS to  V7R4M0. After compilation we can see these 4 Service Programs in DSPPGM and that SQLRPGLE is not using data from Overridden file.

    QRNXIE  
    QRNXIO  
    QRNXUTIL
    QLEAWI  

    In both cases Commitment Control *none is used.

    Please help to resolve issue. Thank you!



    ------------------------------
    Ujwala Kavathekar
    ------------------------------


  • 2.  RE: SQLRPGLE not using Overridden file

    Posted Tue February 06, 2024 10:34 PM

    Ujwala

    When you compile the program in IBM i 7.4, have you looked into the result source listing to see if there is any warning messages that can give you the clue to your issue?  Also look into the job log when you run the program and it fails. 



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 09:38 AM

    Hi @Satid Singkorapoom Thanks for the response. Program do not fail, instead it is using First member of that file and in CL we are overriding to other member.  



    ------------------------------
    Ujwala Kavathekar
    ------------------------------

    Original Message


  • 4.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 09:40 AM

    @Satid Singkorapoom Program do not fail. Instead it is using first member instead of the member that override in CL. 



    ------------------------------
    Ujwala Kavathekar
    ------------------------------

    Original Message


  • 5.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 03:04 AM

    Ujwala,

    You may need to look into the scope of substitution.

    In the OVRDBF, you have an OVRSCOPE parameter that, by default, is set to *ACTGRPDFN. Try putting *JOB.



    ------------------------------
    Dominique GAYTE
    ------------------------------

    Original Message


  • 6.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 10:11 AM

    Most likely, your OVRDBF is out of the activation scope for your SQLRPGLE call.

    Adding OVRSCOPE(*JOB) to your OVRDBF will usually solve the problem. Note that I also use OPNSCOPE(*JOB) with this option, although I understand it isn't strictly necessary to use both options I find my overrides are always in scope for where I need them when I use both options.

    Note also that DLTOVR needs to have LVL(*JOB) parameter added [there are other options besides *JOB, your OVRDBF & DLTOVR options must match for cleanup of OVRDBF to be successful.]

    HTH!

    Cheers 



    ------------------------------
    Don Anderson
    ------------------------------

    Original Message


  • 7.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 11:43 AM

    https://www.ibm.com/docs/en/i/7.3?topic=dc-ovrdbf-does-not-apply-create-table-as-select

    This is what we are exactly doing with Create table and it seems 'In IBM® i 7.3 with SI60199, the CREATE TABLE statement with an AS clause is changed such that it is no longer affected by the Override with Data Base File (OVRDBF) command.'

    We tried to use Alias however it's still not working 



    ------------------------------
    Ujwala Kavathekar
    ------------------------------

    Original Message


  • 8.  RE: SQLRPGLE not using Overridden file

    Posted Wed February 07, 2024 11:04 PM
    Edited by Satid Singkorapoom Thu February 08, 2024 07:36 AM

    Dear Ujwala

    >>>> We tried to use Alias however it's still not working  <<<

    When you create an ALIAS, did you make sure to specify the member name of the file ?  This is essential if the file has multiple members and you want to use a member that is not the first member of the file.

    You should post both CREATE ALIAS and CREATE TABLE ... AS for us to see.   Also give us the job log's error message when you encounter failure after switching to ALIAS.  

       CREATE <library>.<alias> FOR <library>.<file>(<member>) 

    And when you refer to the alias in SQL, try to always use fully qualified name <library>.<alias> if you are not proficient in using unqualified name which requires you to know how to deal with library list or current schema.   


    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 9.  RE: SQLRPGLE not using Overridden file

    Posted Thu February 08, 2024 10:43 AM

    Thank you so much for response  @Satid Singkorapoom. We have included member and it worked. 



    ------------------------------
    Ujwala Kavathekar
    ------------------------------

    Original Message


Related Content