ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

UDF In Select Statement That Uses Same File in Join Causes CPF5032

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • UDF In Select Statement That Uses Same File in Join Causes CPF5032

    I have the following query:

    Code:
    select 
        table1.id, table2.name, feetable.feedesc, getFee(id) as fee
    
    from
        table1
            inner join table2
                on table1.id = table2.id
            inner join feetable 
                on table1.foreignkey = feetable.key 
    where
        somefield = somevalue
    ;
    The getFee() UDF executes a procedure within a service program that performs several reads and calculations to determine a fee for an account. One of the tables that the procedure uses is "feetable". When I run this udf in the select statement of an sql that has a join to one of the tables used by the service program procedure, I get the following:

    Code:
    CPF5032
    Record 123456 member FEETABLE already locked to this job.
    Is there something I'm supposed to specify in the function definition to avoid the record lock generated by the function call?








  • #2
    I wouldn't expect that SQL statement to generate any locks, if you call getFee() with a literal id without a reference to feetable does it work properly?

    Does getFee() update any tables? If not, how is it reading feetable, it should be doing it without trying to obtain a lock - if using RPG op-codes use the (n) suffix.

    Comment


    • #3
      Could you please post the source code of the UDF?
      Birgitta

      Comment


      • #4
        Hi Birgitta. Thanks for your response. I am copying/pasting from the source generated in iSeries Navigator so that it shows "our" defaults:

        Code:
        CREATE FUNCTION MYLIB.GETFEE ( 
            ID NUMERIC(10, 0)
        ) 
        RETURNS NUMERIC(11, 2)   
        LANGUAGE SQL 
        SPECIFIC MYLIB.GETFEE
        NOT DETERMINISTIC 
        MODIFIES SQL DATA 
        CALLED ON NULL INPUT 
        SET OPTION  
            ALWBLK = *ALLREAD , 
            ALWCPYDTA = *OPTIMIZE , 
            COMMIT = *NONE , 
            DECRESULT = (31, 31, 00) , 
            DFTRDBCOL = MYLIB , 
            DYNDFTCOL = *NO , 
            DYNUSRPRF = *USER , 
            SRTSEQ = *HEX
        ;

        Comment


        • #5
          Your defaults will not help! Please show us the (complete) source code where you access your table(s).
          ... and Scott already asked you: Are you doing some updates in your UDF?

          Birgitta

          Comment


          • TheZenbudda
            TheZenbudda commented
            Editing a comment
            This is interesting. So, my source doesn't contain all of that since I do not specify defaults. Are you saying that the source generated from iSeries Nav is wrong?

            No updates in UDF or the RPG procedure being called. Here is the actual source (but i'm not sure how that changes anything):

            Code:
            create function mylib.getFee(
                id numeric(9,0)
            )
            returns numeric(11,2)
            language sql
            specific mylib.getFee
            not deterministic
            modifies sql data
            ;

          • TheZenbudda
            TheZenbudda commented
            Editing a comment
            I just realized you want the source for the RPG procedure! That will take some time to refactor to avoid disclosure. But before I do that, I can tell you that the files are coded as *input only.

        • #6
          I assume then these are F-spec files. Are they USROPN? If yes: Are you always closing them at the end; or are you testing if they are already open before you try and open them again (I don't know but I assume that would error)
          Have you tried debugging the procedure to see which RPG statement throws the error?

          Comment

          Working...
          X