how to commit 200 rows in procedure

i have this procedure
how can i commit like after 200 rows rite now it runs good for small rows but for 100,000 rows its taking forever
what should i do
thanks
Bobby


CREATE PROCEDURE DEV.Update_dim_product1 (IN COMPCODE varchar(1), IN DIVCODE
varchar(1) )
     LANGUAGE SQL
P1: BEGIN
     DECLARE TYPE1_COUNT   integer default 0;   -- counts type-1 updates
     DECLARE TYPE2_COUNT   integer default 0;   -- counts type-2 updates
     DECLARE NEW_REC_COUNT integer default 0;   -- counts number of new
     DECLARE ROWCOUNT      integer;   -- this counts the number of rowsalready present in dim_product1
                                      -- with corresponding customer_no to the input stg_product
                                      -- It is used to determine whether to  treat the input record as a new record, or an update.
     getcode:  FOR STG AS             -- loop through each record in stg_product.
     SELECT
Company_Code ,
Division_Code ,
Prod_Location ,
Prod_Code  ,
Prod_Desc_Eng  ,
Prod_Desc_Fr  ,
Prod_Group_Code  ,
Prod_Group_Desc_Eng ,
Prod_Group_Desc_Fr ,
Prod_SubGroup_Code ,
Prod_SubGroup_Descr_Eng ,
Prod_SubGroup_Descr_Fr ,
Price_Class_Code ,
Price_Class_Desc_Eng ,
Price_Class_Desc_Fr ,
Price_Sub_Class_Code ,
Price_Sub_Class_Desc_Eng ,
Price_Sub_Class_Descr_Fr ,
Prod_Nomencl_Code  ,
Prod_Nomencl_Prod_fam_Code ,
Prod_Nomencl_Prod_fam_Desc_Eng ,
Prod_Nomencl_Prod_fam_Desc_Fr ,
Prod_Nomencl_Sub_fam_Code ,
Prod_Nomencl_Sub_fam_Desc_Eng ,
Prod_Nomencl_Sub_fam_Desc_Fr ,
Prod_Nomencl_Type_Code  ,
Prod_Nomencl_Type_Desc_Eng ,
Prod_Nomencl_Type_Desc_Fr ,
Prod_Nomencl_Group_Code ,
Prod_Nomencl_Grp_Code_Desc_Eng ,
Prod_Nomencl_Grp_Code_Desc_Fr ,
Prod_Nomencl_SubGroup_Code ,
Prod_Nomencl_SubGroup_Desc_Eng ,
Prod_Nomencl_SubGroup_Desc_Fr,
Supplier_1  ,
Supplier_1_Prod_Number  ,
Supplier_1_UPC_Code  ,
Supplier_2   ,
Supplier_2_Prod_Number  ,
Supplier_2_UPC_Code  ,
Supplier_3   ,
Supplier_3_Prod_Number ,
Supplier_3_UPC_Code  ,
Prod_fam   ,
Prod_fam_Desc   ,
Substitution_Code  ,
Substitution_Prod_Code,
Prod_In_Stock_yn  ,
Prod_Buyers_Cost  ,
Prod_Landed_Cost  ,
Pricing_Factor   ,
Pricing_UOM   ,
Stocking_Unit_of_Measure ,
Prod_Matrix_Code  ,
Prod_Status   ,
dm_create_datetime ,
dm_update_datetime
        FROM STG_PRODUCT
           DO
            SET ROWCOUNT = 0;
GETDIM:    FOR DIM AS    -- for each row in stg_PRODUCT, find record in dim_product1, and determine if it is a type-1 or type-2 update, or is a new record.
              SELECT
Company_Code ,
Division_Code ,
Prod_Location ,
Prod_Code  ,
Prod_Desc_Eng  ,
Prod_Desc_Fr  ,
Prod_Group_Code  ,
Prod_Group_Desc_Eng ,
Prod_Group_Desc_Fr ,
Prod_SubGroup_Code ,
Prod_SubGroup_Descr_Eng ,
Prod_SubGroup_Descr_Fr ,
Price_Class_Code ,
Price_Class_Desc_Eng ,
Price_Class_Desc_Fr ,
Price_Sub_Class_Code ,
Price_Sub_Class_Desc_Eng ,
Price_Sub_Class_Descr_Fr ,
Prod_Nomencl_Code  ,
Prod_Nomencl_Prod_fam_Code ,
Prod_Nomencl_Prod_fam_Desc_Eng ,
Prod_Nomencl_Prod_fam_Desc_Fr ,
Prod_Nomencl_Sub_fam_Code ,
Prod_Nomencl_Sub_fam_Desc_Eng ,
Prod_Nomencl_Sub_fam_Desc_Fr ,
Prod_Nomencl_Type_Code  ,
Prod_Nomencl_Type_Desc_Eng ,
Prod_Nomencl_Type_Desc_Fr ,
Prod_Nomencl_Group_Code ,
Prod_Nomencl_Grp_Code_Desc_Eng ,
Prod_Nomencl_Grp_Code_Desc_Fr ,
Prod_Nomencl_SubGroup_Code ,
Prod_Nomencl_SubGroup_Desc_Eng ,
Prod_Nomencl_SubGroup_Desc_Fr,
Supplier_1  ,
Supplier_1_Prod_Number  ,
Supplier_1_UPC_Code  ,
Supplier_2   ,
Supplier_2_Prod_Number  ,
Supplier_2_UPC_Code  ,
Supplier_3   ,
Supplier_3_Prod_Number ,
Supplier_3_UPC_Code  ,
Prod_fam   ,
Prod_fam_Desc   ,
Substitution_Code  ,
Substitution_Prod_Code,
Prod_In_Stock_yn  ,
Prod_Buyers_Cost  ,
Prod_Landed_Cost  ,
Pricing_Factor   ,
Pricing_UOM   ,
Stocking_Unit_of_Measure ,
Prod_Matrix_Code  ,
Prod_Status   ,
dm_create_datetime ,
dm_update_datetime ,
effective_date,
end_date
        FROM dim_product1
        WHERE Prod_Code = STG.Prod_Code and
       COMPANY_CODE = STG.COMPANY_CODE and
       DIVISION_CODE = STG.DIVISION_CODE and
       end_date is null
        DO
                  --  Detect type-2 changes:
            SET ROWCOUNT = ROWCOUNT +1;
            If COALESCE(STG.Prod_Group_Code, '')   <>
COALESCE(DIM.Prod_Group_Code, '') OR
                COALESCE(STG.Prod_Group_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Group_Desc_Eng, '') OR
                COALESCE(STG.Prod_Group_Desc_Fr, '')   <>
COALESCE(DIM.Prod_Group_Desc_Fr, '') OR
                COALESCE(STG.Prod_SubGroup_Code, '')   <>
COALESCE(DIM.Prod_SubGroup_Code, '') OR
                COALESCE(STG.Prod_SubGroup_Descr_Eng, '')   <>
COALESCE(DIM.Prod_SubGroup_Descr_Eng, '') OR
                COALESCE(STG.Prod_SubGroup_Descr_Fr, '')   <>
COALESCE(DIM.Prod_SubGroup_Descr_Fr, '') OR
                COALESCE(STG.Price_Class_Code, '')   <>
COALESCE(DIM.Price_Class_Code, '') OR
                COALESCE(STG.Price_Class_Desc_Eng, '')   <>
COALESCE(DIM.Price_Class_Desc_Eng, '') OR
                COALESCE(STG.Price_Class_Desc_Fr, '')   <>
COALESCE(DIM.Price_Class_Desc_Fr, '') OR
                COALESCE(STG.Price_Sub_Class_Code, '')   <>
COALESCE(DIM.Price_Sub_Class_Code, '') OR
                COALESCE(STG.Price_Sub_Class_Desc_Eng, '')   <>
COALESCE(DIM.Price_Sub_Class_Desc_Eng, '') OR
                COALESCE(STG.Price_Sub_Class_Descr_Fr, '')   <>
COALESCE(DIM.Price_Sub_Class_Descr_Fr, '') OR
                COALESCE(STG.Prod_Nomencl_Code, '')   <>
COALESCE(DIM.Prod_Nomencl_Code, '') OR
                COALESCE(STG.Prod_Nomencl_Prod_fam_Code, '')   <>
COALESCE(DIM.Prod_Nomencl_Prod_fam_Code, '') OR
               COALESCE(STG.Prod_Nomencl_Prod_fam_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Nomencl_Prod_fam_Desc_Eng, '') OR
               COALESCE(STG.Prod_Nomencl_Prod_fam_Desc_Fr, '')   <>
COALESCE(DIM.Prod_Nomencl_Prod_fam_Desc_Fr, '') OR
               COALESCE(STG.Prod_Nomencl_Sub_fam_Code, '')   <>
COALESCE(DIM.Prod_Nomencl_Sub_fam_Code, '') OR
               COALESCE(STG.Prod_Nomencl_Sub_fam_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Nomencl_Sub_fam_Desc_Eng, '') OR
               COALESCE(STG.Prod_Nomencl_Sub_fam_Desc_Fr , '')   <>
COALESCE(DIM.Prod_Nomencl_Sub_fam_Desc_Fr , '') OR
               COALESCE(STG.Prod_Nomencl_Type_Code, '')   <>
COALESCE(DIM.Prod_Nomencl_Type_Code, '') OR
               COALESCE(STG.Prod_Nomencl_Type_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Nomencl_Type_Desc_Eng, '') OR
               COALESCE(STG.Prod_Nomencl_Type_Desc_Fr, '')   <>
COALESCE(DIM.Prod_Nomencl_Type_Desc_Fr, '') OR
               COALESCE(STG.Prod_Nomencl_Grp_Code_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Nomencl_Grp_Code_Desc_Eng, '') OR
               COALESCE(STG.Prod_Nomencl_Grp_Code_Desc_Fr, '')   <>
COALESCE(DIM.Prod_Nomencl_Grp_Code_Desc_Fr, '') OR
               COALESCE(STG.Prod_Nomencl_SubGroup_Code, '')   <>
COALESCE(DIM.Prod_Nomencl_SubGroup_Code, '') OR
               COALESCE(STG.Prod_Nomencl_SubGroup_Desc_Eng, '')   <>
COALESCE(DIM.Prod_Nomencl_SubGroup_Desc_Eng, '') OR
               COALESCE(STG.Prod_Nomencl_SubGroup_Desc_Fr, '')   <>
COALESCE(DIM.Prod_Nomencl_SubGroup_Desc_Fr, '') OR
               COALESCE(STG.Supplier_1, 0)   <> COALESCE(DIM.Supplier_1,
0) OR
               COALESCE(STG.Supplier_1_Prod_Number, '')   <>
COALESCE(DIM.Supplier_1_Prod_Number, '') OR
               COALESCE(STG.Supplier_1_UPC_Code, '')   <>
COALESCE(DIM.Supplier_1_UPC_Code, '') OR
               COALESCE(STG.Supplier_2, 0)   <> COALESCE(DIM.Supplier_2,
0) OR
               COALESCE(STG.Supplier_2_Prod_Number, '')   <>
COALESCE(DIM.Supplier_2_Prod_Number, '') OR
               COALESCE(STG.Supplier_2_UPC_Code , '')   <>
COALESCE(DIM.Supplier_2_UPC_Code , '') OR
               COALESCE(STG.Supplier_3, 0)   <> COALESCE(DIM.Supplier_3,
0) OR
               COALESCE(STG.Supplier_3_Prod_Number, '')   <>
COALESCE(DIM.Supplier_3_Prod_Number , '') OR
               COALESCE(STG.Supplier_3_UPC_Code , '')   <>
COALESCE(DIM.Supplier_3_UPC_Code, '') OR
               COALESCE(STG.Prod_fam, '')   <> COALESCE(DIM.Prod_fam, '') OR
               COALESCE(STG.Prod_fam_Desc, '')   <>
COALESCE(DIM.Prod_fam_Desc, '') OR
               COALESCE(STG.Substitution_Code, '')   <>
COALESCE(DIM.Substitution_Code, '') OR
               COALESCE(STG.Substitution_Prod_Code, '')   <>
COALESCE(DIM.Substitution_Prod_Code, '') OR
               COALESCE(STG.Prod_Matrix_Code, '')   <>
COALESCE(DIM.Prod_Matrix_Code, '')
               then
                       SET TYPE2_COUNT = TYPE2_COUNT +1;
                UPDATE dim_product1
                   SET END_DATE = CURRENT DATE -1 days,
                          effective_date = NULL,
                       dm_update_datetime      = current timestamp
                 WHERE Prod_Code = STG.Prod_Code and
       COMPANY_CODE = STG.COMPANY_CODE and
       DIVISION_CODE = STG.DIVISION_CODE and
       end_date is null ;
            -- now insert new record into dim_product1
                Insert into dim_product1 (
                    Company_Code ,
Division_Code ,
Prod_Location ,
Prod_Code  ,
Prod_Desc_Eng  ,
Prod_Desc_Fr  ,
Prod_Group_Code  ,
Prod_Group_Desc_Eng ,
Prod_Group_Desc_Fr ,
Prod_SubGroup_Code ,
Prod_SubGroup_Descr_Eng ,
Prod_SubGroup_Descr_Fr ,
Price_Class_Code ,
Price_Class_Desc_Eng ,
Price_Class_Desc_Fr ,
Price_Sub_Class_Code ,
Price_Sub_Class_Desc_Eng ,
Price_Sub_Class_Descr_Fr ,
Prod_Nomencl_Code  ,
Prod_Nomencl_Prod_fam_Code ,
Prod_Nomencl_Prod_fam_Desc_Eng ,
Prod_Nomencl_Prod_fam_Desc_Fr ,
Prod_Nomencl_Sub_fam_Code ,
Prod_Nomencl_Sub_fam_Desc_Eng ,
Prod_Nomencl_Sub_fam_Desc_Fr ,
Prod_Nomencl_Type_Code  ,
Prod_Nomencl_Type_Desc_Eng ,
Prod_Nomencl_Type_Desc_Fr ,
Prod_Nomencl_Group_Code ,
Prod_Nomencl_Grp_Code_Desc_Eng ,
Prod_Nomencl_Grp_Code_Desc_Fr ,
Prod_Nomencl_SubGroup_Code ,
Prod_Nomencl_SubGroup_Desc_Eng ,
Prod_Nomencl_SubGroup_Desc_Fr,
Supplier_1  ,
Supplier_1_Prod_Number  ,
Supplier_1_UPC_Code  ,
Supplier_2   ,
Supplier_2_Prod_Number  ,
Supplier_2_UPC_Code  ,
Supplier_3   ,
Supplier_3_Prod_Number ,
Supplier_3_UPC_Code  ,
Prod_fam   ,
Prod_fam_Desc   ,
Substitution_Code  ,
Substitution_Prod_Code,
Prod_In_Stock_yn  ,
Prod_Buyers_Cost  ,
Prod_Landed_Cost  ,
Pricing_Factor   ,
Pricing_UOM   ,
Stocking_Unit_of_Measure ,
Prod_Matrix_Code  ,
Prod_Status   ,
dm_create_datetime ,
dm_update_datetime ,
effective_date   )
                 VALUES
                     (   STG.Company_Code ,
STG.Division_Code ,
STG.Prod_Location ,
STG.Prod_Code  ,
STG.Prod_Desc_Eng  ,
STG.Prod_Desc_Fr  ,
STG.Prod_Group_Code  ,
STG.Prod_Group_Desc_Eng ,
STG.Prod_Group_Desc_Fr ,
STG.Prod_SubGroup_Code ,
STG.Prod_SubGroup_Descr_Eng ,
STG.Prod_SubGroup_Descr_Fr ,
STG.Price_Class_Code ,
STG.Price_Class_Desc_Eng ,
STG.Price_Class_Desc_Fr ,
STG.Price_Sub_Class_Code ,
STG.Price_Sub_Class_Desc_Eng ,
STG.Price_Sub_Class_Descr_Fr ,
STG.Prod_Nomencl_Code  ,
STG.Prod_Nomencl_Prod_fam_Code ,
STG.Prod_Nomencl_Prod_fam_Desc_Eng ,
STG.Prod_Nomencl_Prod_fam_Desc_Fr ,
STG.Prod_Nomencl_Sub_fam_Code ,
STG.Prod_Nomencl_Sub_fam_Desc_Eng ,
STG.Prod_Nomencl_Sub_fam_Desc_Fr ,
STG.Prod_Nomencl_Type_Code  ,
STG.Prod_Nomencl_Type_Desc_Eng ,
STG.Prod_Nomencl_Type_Desc_Fr ,
STG.Prod_Nomencl_Group_Code ,
STG.Prod_Nomencl_Grp_Code_Desc_Eng ,
STG.Prod_Nomencl_Grp_Code_Desc_Fr ,
STG.Prod_Nomencl_SubGroup_Code ,
STG.Prod_Nomencl_SubGroup_Desc_Eng ,
STG.Prod_Nomencl_SubGroup_Desc_Fr,
STG.Supplier_1  ,
STG.Supplier_1_Prod_Number  ,
STG.Supplier_1_UPC_Code  ,
STG.Supplier_2   ,
STG.Supplier_2_Prod_Number  ,
STG.Supplier_2_UPC_Code  ,
STG.Supplier_3   ,
STG.Supplier_3_Prod_Number ,
STG.Supplier_3_UPC_Code  ,
STG.Prod_fam   ,
STG.Prod_fam_Desc   ,
STG.Substitution_Code  ,
STG.Substitution_Prod_Code,
STG.Prod_In_Stock_yn  ,
STG.Prod_Buyers_Cost  ,
STG.Prod_Landed_Cost  ,
STG.Pricing_Factor   ,
STG.Pricing_UOM   ,
STG.Stocking_Unit_of_Measure ,
STG.Prod_Matrix_Code  ,
STG.Prod_Status   ,
STG.dm_create_datetime ,
STG.dm_update_datetime ,
   current date
                        );
            ELSEIF
              --  Next detect type-1 changes:
              COALESCE(STG.Company_Code, '')  <>  COALESCE(DIM.Company_Code,
'') OR
              COALESCE(STG.Division_Code, '')    <>
COALESCE(DIM.Division_Code, '')    OR
              COALESCE(STG.Prod_Location, '')    <>
COALESCE(DIM.Prod_Location, '')    OR
              COALESCE(STG.Prod_In_Stock_yn, '') <>
COALESCE(DIM.Prod_In_Stock_yn, '') OR
              COALESCE(STG.Prod_Buyers_Cost, 0) <>
COALESCE(DIM.Prod_Buyers_Cost, 0) OR
              COALESCE(STG.Prod_Landed_Cost, 0) <>
COALESCE(DIM.Prod_Landed_Cost, 0) OR
               COALESCE(STG.Pricing_Factor, 0) <>
COALESCE(DIM.Pricing_Factor, 0) OR
               COALESCE(STG.Pricing_UOM , '') <>  COALESCE(DIM.Pricing_UOM ,
'') OR
               COALESCE(STG.Stocking_Unit_of_Measure , '') <>
COALESCE(DIM.Stocking_Unit_of_Measure , '') OR
               COALESCE(STG.Prod_Status , '') <>  COALESCE(DIM.Prod_Status ,
'')
            Then
              SET TYPE1_COUNT = TYPE1_COUNT +1;
          UPDATE dim_product1 SET

             Company_Code =STG.Company_Code ,
Division_Code = STG.Division_Code  ,
Prod_Location=STG.Prod_Location ,
Prod_In_Stock_yn=STG.Prod_In_Stock_yn  ,
Prod_Buyers_Cost=STG.Prod_Buyers_Cost  ,
Prod_Landed_Cost=STG.Prod_Landed_Cost ,
Pricing_Factor=STG.Pricing_Factor   ,
Pricing_UOM=STG.Pricing_UOM  ,
Stocking_Unit_of_Measure=STG.Stocking_Unit_of_Measure ,
Prod_Status=STG.Prod_Status,
dm_update_datetime = current timestamp
          WHERE Prod_Code = STG.Prod_Code and
       COMPANY_CODE = STG.COMPANY_CODE and
       DIVISION_CODE = STG.DIVISION_CODE;
            END IF;
  END FOR GETDIM;
  IF ROWCOUNT = 0 THEN   -- if there were no records in DIM_CUSTOMER, it is a new customer, so insert it.
     SET NEW_REC_COUNT = NEW_REC_COUNT +1;
            Insert into dim_product1 (
                    Company_Code ,
Division_Code ,
Prod_Location ,
Prod_Code  ,
Prod_Desc_Eng  ,
Prod_Desc_Fr  ,
Prod_Group_Code  ,
Prod_Group_Desc_Eng ,
Prod_Group_Desc_Fr ,
Prod_SubGroup_Code ,
Prod_SubGroup_Descr_Eng ,
Prod_SubGroup_Descr_Fr ,
Price_Class_Code ,
Price_Class_Desc_Eng ,
Price_Class_Desc_Fr ,
Price_Sub_Class_Code ,
Price_Sub_Class_Desc_Eng ,
Price_Sub_Class_Descr_Fr ,
Prod_Nomencl_Code  ,
Prod_Nomencl_Prod_fam_Code ,
Prod_Nomencl_Prod_fam_Desc_Eng ,
Prod_Nomencl_Prod_fam_Desc_Fr ,
Prod_Nomencl_Sub_fam_Code ,
Prod_Nomencl_Sub_fam_Desc_Eng ,
Prod_Nomencl_Sub_fam_Desc_Fr ,
Prod_Nomencl_Type_Code  ,
Prod_Nomencl_Type_Desc_Eng ,
Prod_Nomencl_Type_Desc_Fr ,
Prod_Nomencl_Group_Code ,
Prod_Nomencl_Grp_Code_Desc_Eng ,
Prod_Nomencl_Grp_Code_Desc_Fr ,
Prod_Nomencl_SubGroup_Code ,
Prod_Nomencl_SubGroup_Desc_Eng ,
Prod_Nomencl_SubGroup_Desc_Fr,
Supplier_1  ,
Supplier_1_Prod_Number  ,
Supplier_1_UPC_Code  ,
Supplier_2   ,
Supplier_2_Prod_Number  ,
Supplier_2_UPC_Code  ,
Supplier_3   ,
Supplier_3_Prod_Number ,
Supplier_3_UPC_Code  ,
Prod_fam   ,
Prod_fam_Desc   ,
Substitution_Code  ,
Substitution_Prod_Code,
Prod_In_Stock_yn  ,
Prod_Buyers_Cost  ,
Prod_Landed_Cost  ,
Pricing_Factor   ,
Pricing_UOM   ,
Stocking_Unit_of_Measure ,
Prod_Matrix_Code  ,
Prod_Status   ,
dm_create_datetime ,
dm_update_datetime ,
effective_date   )
                 VALUES
                     (   STG.Company_Code ,
STG.Division_Code ,
STG.Prod_Location ,
STG.Prod_Code  ,
STG.Prod_Desc_Eng  ,
STG.Prod_Desc_Fr  ,
STG.Prod_Group_Code  ,
STG.Prod_Group_Desc_Eng ,
STG.Prod_Group_Desc_Fr ,
STG.Prod_SubGroup_Code ,
STG.Prod_SubGroup_Descr_Eng ,
STG.Prod_SubGroup_Descr_Fr ,
STG.Price_Class_Code ,
STG.Price_Class_Desc_Eng ,
STG.Price_Class_Desc_Fr ,
STG.Price_Sub_Class_Code ,
STG.Price_Sub_Class_Desc_Eng ,
STG.Price_Sub_Class_Descr_Fr ,
STG.Prod_Nomencl_Code  ,
STG.Prod_Nomencl_Prod_fam_Code ,
STG.Prod_Nomencl_Prod_fam_Desc_Eng ,
STG.Prod_Nomencl_Prod_fam_Desc_Fr ,
STG.Prod_Nomencl_Sub_fam_Code ,
STG.Prod_Nomencl_Sub_fam_Desc_Eng ,
STG.Prod_Nomencl_Sub_fam_Desc_Fr ,
STG.Prod_Nomencl_Type_Code  ,
STG.Prod_Nomencl_Type_Desc_Eng ,
STG.Prod_Nomencl_Type_Desc_Fr ,
STG.Prod_Nomencl_Group_Code ,
STG.Prod_Nomencl_Grp_Code_Desc_Eng ,
STG.Prod_Nomencl_Grp_Code_Desc_Fr ,
STG.Prod_Nomencl_SubGroup_Code ,
STG.Prod_Nomencl_SubGroup_Desc_Eng ,
STG.Prod_Nomencl_SubGroup_Desc_Fr,
STG.Supplier_1  ,
STG.Supplier_1_Prod_Number  ,
STG.Supplier_1_UPC_Code  ,
STG.Supplier_2   ,
STG.Supplier_2_Prod_Number  ,
STG.Supplier_2_UPC_Code  ,
STG.Supplier_3   ,
STG.Supplier_3_Prod_Number ,
STG.Supplier_3_UPC_Code  ,
STG.Prod_fam   ,
STG.Prod_fam_Desc   ,
STG.Substitution_Code  ,
STG.Substitution_Prod_Code,
STG.Prod_In_Stock_yn  ,
STG.Prod_Buyers_Cost  ,
STG.Prod_Landed_Cost  ,
STG.Pricing_Factor   ,
STG.Pricing_UOM   ,
STG.Stocking_Unit_of_Measure ,
STG.Prod_Matrix_Code  ,
STG.Prod_Status   ,
STG.dm_create_datetime ,
STG.dm_update_datetime ,
   current date
                        );
            END IF;
    END FOR GETCODE;
    insert into ETL_LOG (COMPANY_CODE,  DIVISION_CODE, TABLENAME,
NEW_RECORD_COUNT, TYPE1_COUNT, TYPE2_COUNT, update_timestamp) values
         (COMPCODE, DIVCODE, 'dim_product1',  NEW_REC_COUNT, TYPE1_COUNT,
TYPE2_COUNT, current timestamp );
commit;
END P1
bobby2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:

Hi Bobby,

DB2 really, really likes data indexes.  It has been my experience that having indexes on the target columns of an update is much more likely to yield performance gains than is intermittent commits.

Try building indexes on the columns that you change with the UPDATE statements.

Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.