ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Store procedure keeping lock on PF

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

  • Store procedure keeping lock on PF

    I have a RPGLE PCM5085 which generates a workfile WF1. When I call this from the command line it works fine.
    I also have a wrapper program PCM5085SP which accepts two numeric parametes, converts these two parms into char and calls PCM5085.
    I have created a store procedure as

    CREATE PROCEDURE dhillond/PCM_SP_SelectSizes1 (@store dec (4 ),
    @sku dec (7)) DYNAMIC RESULT
    SETS 1 LANGUAGE RPGLE SPECIFIC Dhillond/PCM_SP_SelectSizes1 NOT
    DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT EXTERNAL NAME
    'DHILLOND/PCM5085SP' PARAMETER STYLE GENERAL

    I run this as CALL PCM_SP_SELECTSIZES1 (0053, 5054978).

    I have two problems - when I finish calling this I do a select on workfile but its empty. If this means it has not worked, then which part has n't?
    Also workfile is being locked by this session.

    Please help

    Devarshi.

  • #2
    Re: Store procedure keeping lock on PF

    The first thing that catches my eye here is that your CREATE PROCEDURE statement states that a result set is being returned when it is not.

    As for your program retaining a lock your file, are you setting on the LR indicator before you exit that program?
    "Time passes, but sometimes it beats the <crap> out of you as it goes."

    Comment


    • #3
      Re: Store procedure keeping lock on PF

      I have removed the result set1 from the store procedure - it now looks like
      CREATE PROCEDURE dhillond/PCM_SP_SelectSizes1 (@store dec (4 ),
      @sku dec (7))
      LANGUAGE RPGLE
      external name Dhillond/pcm5085sp
      reads SQL DATA
      PARAMETER STYLE GENERAL

      I call it as - CALL dhillond/PCM_SP_SELECTSIZES1 (0053, 5054978)

      Once complete I do - select * from pcmsp5wf to see the output buts its empty.

      In program PCM5085SP LR is on before it ends.

      Many thanks
      Devarshi.

      Comment


      • #4
        Re: Store procedure keeping lock on PF

        ur procedure contains

        MODIFIES SQL DATA
        Indicates that the procedure possibly modifies data using SQL. The procedure can contain any SQL statement other than:

        and may be its trying to keep the lock and its expecting some kind of Commit operation to release it.

        Try to have a look at this ...
        Thanks,
        Giri

        Comment


        • #5
          Re: Store procedure keeping lock on PF

          OK, I too have a stored procedure that leaves a lock on a PF after it finishes. Did anyone figure out how to fix this?

          This procedure does modify data.
          Here is my header:

          PHP Code:
          CREATE PROCEDURE ECKODW2.CUSTOMER_UNIQUE_NUM_PROC ( ) 
              
          LANGUAGE SQL 
              SPECIFIC ECKODW2
          .CUSTOMER_UNIQUE_NUM_PROC 
              DETERMINISTIC 
              MODIFIES SQL DATA 
              CALLED ON NULL INPUT 
              BEGIN 
          and the last few lines of code

          PHP Code:
              CLOSE CURS_CUSTOMER 
              
          COMMIT 
              
          END  
          Predictions are usually difficult, especially about the future. ~Yogi Berra

          Vertical Software Systems
          VSS.biz

          Comment


          • #6
            Re: Store procedure keeping lock on PF

            Shouldn't you COMMIT before a CLOSE?
            Jonas Temple
            Got FROG?
            Got Tadpole? No, because it's not done yet! Stay tuned....

            01010111 01100001 01110011 01110011 01110101 01110000 00100000 01100100 01101111 01100111 00111111

            Comment


            • #7
              Re: Store procedure keeping lock on PF

              nah, that didnt do it.
              Predictions are usually difficult, especially about the future. ~Yogi Berra

              Vertical Software Systems
              VSS.biz

              Comment


              • #8
                Re: Store procedure keeping lock on PF

                Where do you call this procedure from ? Strsql, Runsqlstm, other ?
                Philippe

                Comment


                • #9
                  Re: Store procedure keeping lock on PF

                  I get an object lock from every SQL interface I use.

                  I am currently testing the statement using SQL scripts in OPs Navigator.
                  Predictions are usually difficult, especially about the future. ~Yogi Berra

                  Vertical Software Systems
                  VSS.biz

                  Comment


                  • #10
                    Re: Store procedure keeping lock on PF

                    Bueler, bueler. . .
                    Predictions are usually difficult, especially about the future. ~Yogi Berra

                    Vertical Software Systems
                    VSS.biz

                    Comment


                    • #11
                      Re: Store procedure keeping lock on PF

                      When you run the command to create your procedure, use the COMMIT(*NONE) parameter to make sure commitment control is off for the procedure. Then try the call again.
                      "Time passes, but sometimes it beats the <crap> out of you as it goes."

                      Comment


                      • #12
                        Re: Store procedure keeping lock on PF

                        This procedure is puer sql. There is absolutely no RPG or CL . . . involved with this procedure. However, I do have SET OPTION COMMIT = *NONE in the procedure header. Unfortunately, the lock remains.
                        Predictions are usually difficult, especially about the future. ~Yogi Berra

                        Vertical Software Systems
                        VSS.biz

                        Comment


                        • #13
                          Re: Store procedure keeping lock on PF

                          Is it possible to see the entire procedure?
                          Jonas Temple
                          Got FROG?
                          Got Tadpole? No, because it's not done yet! Stay tuned....

                          01010111 01100001 01110011 01110011 01110101 01110000 00100000 01100100 01101111 01100111 00111111

                          Comment


                          • #14
                            Re: Store procedure keeping lock on PF

                            PHP Code:

                            --  Generate SQL 
                            --  Version:                       V5R2M0 020719 
                            --  Generated on:                  03/06/07 08:46:57 
                            --  Relational Database:           **** 
                            --  
                            Standards Option:              DB2 UDB AS/400 


                            /*Drop procedure Eckodw2.customer_u_name_proc;  */

                            CREATE PROCEDURE ECKODW2.CUSTOMER_U_NAME_PROC ( ) 
                                
                            LANGUAGE SQL 
                                SPECIFIC ECKODW2
                            .CUSTOMER_U_NAME_PROC 
                                DETERMINISTIC 
                                    MODIFIES SQL DATA 
                                CALLED ON NULL INPUT 
                                    SET OPTION COMMIT 
                            =*NONE
                            BEGIN 
                              
                            DECLARE V_CO VARCHAR ) ;  -- VVariables from Fetch 
                              
                            DECLARE V_DIV VARCHAR ) ; 
                              DECLARE 
                            V_CUSTOMER CHAR 12 ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_CUSTOMER CHAR 12 ) ;  -- VVVariables from Subsequent Selects 
                              
                            DECLARE V_NAME CHAR 50 ) ; 
                              DECLARE 
                            V_CUS_CLASS CHAR ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_CUS_CLASS CHAR ) ; 
                              DECLARE 
                            V_CORP_GROUP CHAR ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_CORP_GROUP CHAR ) ; 
                              DECLARE 
                            V_SLS_REP CHAR ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_SLS_REP CHAR ) ; 
                              DECLARE 
                            V_FACTORCODE CHAR ) ; 
                              DECLARE 
                            V_CHARGEBACK SMALLINT 
                              DECLARE 
                            V_RETAIL_ALT_AND_ALL_L0 SMALLINT 
                              DECLARE 
                            DER_RETAIL_PRIM_ABOVE_L0 SMALLINT 
                              DECLARE 
                            V_IDENT INTEGER 
                              DECLARE 
                            DER_SIMPLE_WORK VARCHAR 300 ) ;  -- DerFields Derived within the Procedure 
                              
                            DECLARE DER_SIMPLE_NAME CHAR 300 ) ; 
                              DECLARE 
                            DER_SIMPLE_NAME_SOUNDEX CHAR 300 ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE CHAR 35 ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_CO VARCHAR ) ; 
                              DECLARE 
                            VV_CUST_UNIQUE_DIV VARCHAR ) ; 
                              DECLARE 
                            DER_CUST_UNIQUE_REDIRECT SMALLINT 
                              DECLARE 
                            DER_ALT_HIER_CO VARCHAR ) ; 
                              DECLARE 
                            DER_ALT_HIER_FLAG SMALLINT 
                              DECLARE 
                            DER_ALT_HIER_CUST_UNIQUE CHAR 35 ) ; 
                              DECLARE 
                            V_END_TABLE INTEGER DEFAULT 
                              DECLARE 
                            CURS_CUSTOMER CURSOR WITH RETURN FOR 
                                
                            SELECT
                                  CUSTOMER 
                            CO 
                                  
                            CUSTOMER DIV 
                                  
                            CUSTOMER CUSTOMER 
                                  
                            CUSTOMER NAME 
                                  
                            CUSTOMER CUS_CLASS 
                                  
                            CUSTOMER CORP_GROUP 
                                  
                            CUSTOMER SLS_REP 
                                  
                            CUSTOMER FACTORCODE 
                                  
                            CUSTOMER CHARGEBACK 
                                  
                            COALESCE ( ( SELECT 1 FROM SYSIBM SYSDUMMY1 
                                                 WHERE
                                                   EXISTS 
                            SELECT 'X' 
                                                 
                            FROM ECKODW2 RTL_EDI_HDR RETAILCUBEIN 
                                                 WHERE CUSTOMER 
                            CO RETAILCUBEIN CO 
                                                   
                            AND CUSTOMER DIV RETAILCUBEIN DIV 
                                                   
                            AND CUSTOMER CUSTOMER RETAILCUBEIN CUSTOMER 
                                                

                                              ) 
                                  , 
                            ) AS RETAIL_ALT_AND_ALL_L0 
                                 FROM 
                            SELECT CO DIV CUSTOMER NAME CUS_CLASS CORP_GROUP SLS_REP UPD_TIME FACTORCODE CHARGEBACK 
                                        FROM ECKODW2 
                            CUSTOMER 
                                 UNION ALL 
                                      SELECT SUBSTR 
                            CO ) , DIV CUSTOMER NAME CUS_CLASS CORP_GROUP SLS_REP UPD_TIME FACTORCODE CHARGEBACK 
                                     FROM ECKODW2 
                            CUSTOMER_CBACK 
                                    
                            CUSTOMER 
                                    LEFT OUTER JOIN ECKODW2 
                            CODIV_ORDER CODIV_ORDER 
                                      ON CUSTOMER 
                            CO CODIV_ORDER CO AND CUSTOMER DIV CODIV_ORDER DIV 
                                      ORDER BY CHARGEBACK  
                            --  CRITICAL Chargeback specific records (chargeback=1must be processed last or they could become the "Primary" records 
                                 
                            COALESCE CODIV_ORDER SORTORDER DESC 
                                 
                            CUSTOMER CUSTOMER WITH NC 

                              DECLARE CONTINUE 
                            HANDLER FOR NOT FOUND 
                              SET V_END_TABLE 

                              
                              
                            OPEN CURS_CUSTOMER 

                              
                            /* Removed During testing
                              **DELETE FROM ECKODW2 . CUSTOMER_U_NAME WITH NC ; 
                              **CALL QSYS.QCMDEXC('CLRPFM FILE(eckodw2/CUSTO00001)',0000000031.00000) 
                              */

                              
                            ALTER TABLE ECKODW2 CUSTOMER_U_NAME ALTER COLUMN IDENT RESTART  
                              
                            SET V_END_TABLE 

                              WHILE 
                            V_END_TABLE DO 
                                
                            SET DER_SIMPLE_WORK ECKODW2 SIMPLIFY V_NAME V_CUSTOMER ) ; 
                                
                            SET DER_SIMPLE_NAME_SOUNDEX LEFT DER_SIMPLE_WORK POSSTR DER_SIMPLE_WORK ';' ) - ) ; 
                                
                            SET VV_CUST_UNIQUE_CO V_CO 
                                
                            SET VV_CUST_UNIQUE_DIV V_DIV 
                                
                            SET VV_CUST_UNIQUE_CUS_CLASS V_CUS_CLASS 
                                
                            SET VV_CUST_UNIQUE_CORP_GROUP V_CORP_GROUP 
                                
                            SET DER_CUST_UNIQUE_REDIRECT 10 ;  -- redirect/merge based on simple_name 
                                SET DER_ALT_HIER_CO 
                            V_CO 
                                
                            SET DER_ALT_HIER_CUST_UNIQUE '' 
                                
                            SET DER_RETAIL_PRIM_ABOVE_L0 V_RETAIL_ALT_AND_ALL_L0 
                                
                            SET VV_CUST_UNIQUE_SLS_REP NULL ;  --           set V_Sls_Rep    NULL 
                                
                            SET VV_CUST_UNIQUE 'No Merge' 
                                -- 
                            Redirect codes 
                                
                            -- 0  no redirect the original customer# co div 
                                
                            -- 5  no redirect but use the Customer# || Co because the customer# is in use with a non-matching simple name 
                                
                            -- 10 redirect to customer# (may be same customer# or may not) in another co and/or div based on equal simple names (i.e. the 0 code above) 
                                
                            -- 15 redirect to the customer# (may be same customer# or may not) || Co in another co and/or div based on equal simple names (i.e. 5 zero code above) 
                                
                            -- 20 redirect to the original customer# in another co and/or div based on equal simple SOUNDEX names (i.e. the 0 code above) 
                                
                            -- 25 redirect to the original customer# || Co in another co and/or div based on equal simple SOUNDEX names (i.e. 5 zero code above) 
                                
                            -- 
                                -- 
                            NOTE in summary simple name matches are sufficient to group regardless of equal customer numbers 
                                
                            --                 simple SOUNDEX name matches are sufficient when the customer# matches 
                               
                                
                            -- OK see if there is anyone I should be merged with 
                                
                                SELECT CUSTOMER 
                            CUST_UNIQUE CUST_UNIQUE_CO CUST_UNIQUE_DIV CUS_CLASS CORP_GROUP SLS_REP 
                                FROM ECKODW2 
                            CUSTOMER_U_NAME CUSTOMER_U_NAME 
                                WHERE CUSTOMER_U_NAME 
                            SIMPLE_NAME DER_SIMPLE_NAME 
                                  
                            AND CUSTOMER_U_NAME IDENT = ( SELECT MIN IDENT 
                                                                  
                            FROM ECKODW2 CUSTOMER_U_NAME INNNER 
                                                                  WHERE INNNER 
                            SIMPLE_NAME DER_SIMPLE_NAME 
                                                  
                            WITH NC    
                                -- If 
                            unchanged then there is nobody with whom I should have been merged 
                                
                                
                            IF VV_CUST_UNIQUE 'No Merge' THEN 
                                  
                            -- OK now see if anyone is already using that customer Number (or that customer number with a company suffix
                                  
                            SET VV_CUST_UNIQUE 'There is No Conflict' 
                                  
                            SELECT CUSTOMER CUST_UNIQUE CUST_UNIQUE_CO CUST_UNIQUE_DIV CUS_CLASS CORP_GROUP SLS_REP 
                                  FROM ECKODW2 
                            CUSTOMER_U_NAME CUSTOMER_U_NAME 
                                  WHERE TRIM 
                            LEFT CUSTOMER_U_NAME CUST_UNIQUE LENGTH V_CUSTOMER ) ) ) = TRIM V_CUSTOMER 
                                    AND 
                            CUSTOMER_U_NAME IDENT = ( SELECT MIN IDENT 
                                                                    
                            FROM ECKODW2 CUSTOMER_U_NAME INNNER 
                                                    WHERE TRIM 
                            LEFT INNNER CUST_UNIQUE LENGTH V_CUSTOMER ) ) ) = TRIM V_CUSTOMER 
                                                  ) 
                            WITH NC

                                  IF 
                            VV_CUST_UNIQUE 'There is No Conflict' THEN 
                                    
                            IF V_CHARGEBACK 0 THEN 
                                    SET VV_CUST_UNIQUE 
                            TRIM V_CUSTOMER ) ; 
                                ELSE 
                                     
                            SET VV_CUST_UNIQUE TRIM V_CUSTOMER ) || '-CB' 
                                
                            END IF ; 

                                
                            SET DER_CUST_UNIQUE_REDIRECT ;  -- 0  no redirect the original customer# co div 
                                
                            SET DER_ALT_HIER_FLAG 
                                  ELSE 
                                 -- `````
                                             
                                SET VV_CUST_UNIQUE_SLS_REP = NULL ; 
                                    -- IF They Share a Customer number then relax the standards and reuse the number if the simple_name_soundex is equal 
                                SET VV_CUST_UNIQUE = 'The Conflict still a Conflict' ; 
                                SELECT CUSTOMER , CUST_UNIQUE , CUST_UNIQUE_CO , CUST_UNIQUE_DIV , CUS_CLASS , CORP_GROUP , SLS_REP 
                                    FROM ECKODW2 . CUSTOMER_U_NAME CUSTOMER_U_NAME 
                                WHERE TRIM ( LEFT ( CUSTOMER_U_NAME . CUST_UNIQUE , LENGTH ( V_CUSTOMER ) ) ) = TRIM ( V_CUSTOMER ) 
                                    AND CUSTOMER_U_NAME . SIMPLE_NAME_SOUNDEX = DER_SIMPLE_NAME_SOUNDEX 
                                    AND CUSTOMER_U_NAME . IDENT = ( SELECT MIN ( IDENT ) 
                                FROM ECKODW2 . CUSTOMER_U_NAME INNNER 
                                WHERE TRIM ( LEFT ( INNNER . CUST_UNIQUE , LENGTH ( V_CUSTOMER ) ) ) = TRIM ( V_CUSTOMER ) 
                                    AND INNNER . SIMPLE_NAME_SOUNDEX = DER_SIMPLE_NAME_SOUNDEX 
                                ) WITH NC; 
                                  
                                  IF VV_CUST_UNIQUE = 'The Conflict still a Conflict' THEN 
                                    -- It still is so there is no conflict 
                                    SET VV_CUST_UNIQUE_CO = V_CO ; 
                                  SET VV_CUST_UNIQUE_DIV = V_DIV ; 
                                SET VV_CUST_UNIQUE_CUS_CLASS = V_CUS_CLASS ; 
                                SET VV_CUST_UNIQUE_CORP_GROUP = V_CORP_GROUP ; 
                                SET DER_CUST_UNIQUE_REDIRECT = 5 ;  -- 5  no redirect but use the Customer# || Co because the customer# is in use with a non-matching simple name 
                                SET DER_ALT_HIER_FLAG = 0 ; 

                                IF V_CHARGEBACK = 0 THEN 
                                    SET VV_CUST_UNIQUE = TRIM ( V_CUSTOMER ) || TRIM ( V_CO ) ; 
                                ELSE 
                                  -- CRITICAL NOTE The code below is dependent on customer_cback customers coming in with group CB and that group having the lowest (last) sortorder !!!!  Cu    
                                    SET VV_CUST_UNIQUE = TRIM ( V_CUSTOMER ) || TRIM ( V_CO ) || TRIM ( V_DIV ) || '-CB' ; 
                                END IF ; 
                                  ELSE 
                                    -- The Conflict is not a conflict but really a soundex based redirect  
                                IF TRIM ( VV_CUST_UNIQUE ) = TRIM ( VV_CUST_UNIQUE_CUSTOMER ) THEN 
                                    SET DER_CUST_UNIQUE_REDIRECT = 20 ;  -- 20 redirect to the original customer# in another co and/or div based on equal simple SOUNDEX names (i.e. the 0 code     
                                ELSE 
                                   SET DER_CUST_UNIQUE_REDIRECT = 25 ;  -- 25 redirect to the original customer# || Co in another co and/or div based on equal simple SOUNDEX names (i.e. 5 zer    
                                  END IF ; 

                                  IF TRIM ( V_CUS_CLASS ) = TRIM ( VV_CUST_UNIQUE_CUS_CLASS ) 
                                       AND TRIM ( V_CORP_GROUP ) = TRIM ( VV_CUST_UNIQUE_CORP_GROUP ) THEN 
                                    SET DER_ALT_HIER_FLAG = 0 ;  -- Alt Hierarchy is Different from Standard 
                                SET DER_ALT_HIER_CUST_UNIQUE = '' ; 
                                  ELSE 
                                  SET DER_ALT_HIER_FLAG = 1 ;  -- Alt Hierarchy is Different from Standard 
                                SET DER_ALT_HIER_CUST_UNIQUE = TRIM ( V_CUSTOMER ) || ' Alt ' || V_CO || CASE WHEN V_CHARGEBACK = 1 THEN V_DIV || '-CB' ELSE '' END ; 
                                SELECT 1 
                                INTO DER_RETAIL_PRIM_ABOVE_L0 
                                FROM SYSIBM . SYSDUMMY1 
                                WHERE EXISTS ( SELECT 1 
                                           FROM ECKODW2 . RTL_EDI_HDR RETAILCUBEIN  -- change to Effective Date cube 
                                           WHERE VV_CUST_UNIQUE_CO = RETAILCUBEIN . CO 
                                               AND VV_CUST_UNIQUE_DIV = RETAILCUBEIN . DIV 
                                         AND VV_CUST_UNIQUE = RETAILCUBEIN . CUSTOMER 
                                                ) ; 
                                   END IF ; 
                                 END IF ; 
                                END IF ; 
                              
                              ELSE 
                              
                              IF TRIM ( VV_CUST_UNIQUE ) = TRIM ( VV_CUST_UNIQUE_CUSTOMER ) THEN 
                                SET DER_CUST_UNIQUE_REDIRECT = 10 ;  -- 10 redirect to customer# (may be same customer# or may not) in another co and/or div based on equal simple names (i.    
                              ELSE 
                                SET DER_CUST_UNIQUE_REDIRECT = 15 ;  -- 15 redirect to the customer# (may be same customer# or may not) || Co in another co and/or div based on equal simple    
                              END IF ; 
                              IF TRIM ( V_CUS_CLASS ) = TRIM ( VV_CUST_UNIQUE_CUS_CLASS ) 
                                 AND TRIM ( V_CORP_GROUP ) = TRIM ( VV_CUST_UNIQUE_CORP_GROUP ) THEN 
                               
                                SET DER_ALT_HIER_FLAG = 0 ;  -- Alt Hierarchy is Different from Standard 
                                SET DER_ALT_HIER_CUST_UNIQUE = '' ; 
                              ELSE 
                                SET DER_ALT_HIER_FLAG = 1 ;  -- Alt Hierarchy is Different from Standard 
                                SET DER_ALT_HIER_CUST_UNIQUE = TRIM ( V_CUSTOMER ) || ' Alt ' || V_CO || CASE WHEN V_CHARGEBACK = 1 THEN V_DIV || '-CB' ELSE '' END ; 
                                SELECT 1 
                                INTO DER_RETAIL_PRIM_ABOVE_L0 
                                FROM SYSIBM . SYSDUMMY1 
                                WHERE EXISTS ( SELECT 1 
                                                 FROM ECKODW2 . RTL_EDI_HDR RETAILCUBEIN  -- change to Effective Date cube 
                                             WHERE VV_CUST_UNIQUE_CO = RETAILCUBEIN . CO 
                                        AND VV_CUST_UNIQUE_DIV = RETAILCUBEIN . DIV 
                                        AND VV_CUST_UNIQUE = RETAILCUBEIN . CUSTOMER 
                                        ) ; 
                               END IF ; 
                             END IF ; 

                             --                                    All of these fields below from co thru Corp_Group are actually the Alt_Hier Flds                                         
                              INSERT INTO ECKODW2 . CUSTOMER_U_NAME ( CO , DIV , CUSTOMER , NAME , CUS_CLASS , CORP_GROUP 
                                , SLS_REP , FACTORCODE , CHARGEBACK 
                                , RETAIL_ALT_AND_ALL_L0 , RETAIL_PRIM_ABOVE_L0 
                                , ALT_HIER_FLAG , ALT_HIER_CO , ALT_HIER_CUST_UNIQUE 
                                , CUST_UNIQUE , CUST_UNIQUE_CO , CUST_UNIQUE_DIV 
                                , CUST_UNIQUE_CUS_CLASS , CUST_UNIQUE_CORP_GROUP 
                                , CUST_UNIQUE_REDIRECT , CREATE_TS 
                                , SIMPLE_NAME , SIMPLE_NAME_SOUNDEX ) 
                              VALUES ( V_CO , V_DIV , V_CUSTOMER , V_NAME , COALESCE ( V_CUS_CLASS , 'NC' ) , V_CORP_GROUP 
                                , COALESCE ( V_SLS_REP , VV_CUST_UNIQUE_SLS_REP , 'CB' ) , V_FACTORCODE , V_CHARGEBACK 
                                , V_RETAIL_ALT_AND_ALL_L0 , DER_RETAIL_PRIM_ABOVE_L0 
                                , DER_ALT_HIER_FLAG , DER_ALT_HIER_CO , DER_ALT_HIER_CUST_UNIQUE 
                                , VV_CUST_UNIQUE , VV_CUST_UNIQUE_CO , VV_CUST_UNIQUE_DIV 
                                , COALESCE ( VV_CUST_UNIQUE_CUS_CLASS , 'NC' ) , VV_CUST_UNIQUE_CORP_GROUP 
                                , DER_CUST_UNIQUE_REDIRECT , ( CURRENT TIMESTAMP ) 
                                , DER_SIMPLE_NAME , DER_SIMPLE_NAME_SOUNDEX ) WITH NC ; 
                              
                              SET V_END_TABLE = 0 ; 
                            END WHILE ; 
                              CLOSE CURS_CUSTOMER ; 
                              COMMIT ; 

                            END  ; 


                            Predictions are usually difficult, especially about the future. ~Yogi Berra

                            Vertical Software Systems
                            VSS.biz

                            Comment


                            • #15
                              Re: Store procedure keeping lock on PF

                              In your case, which file is still left opened?
                              Jonas Temple
                              Got FROG?
                              Got Tadpole? No, because it's not done yet! Stay tuned....

                              01010111 01100001 01110011 01110011 01110101 01110000 00100000 01100100 01101111 01100111 00111111

                              Comment

                              Working...
                              X