We help IT Professionals succeed at work.

DB2/UDB STORED PROCEDURES

centralmike
centralmike asked
on
I need to have someone help me in writing error checking and commits logic in a stored procedure.  My DBA says I need to commit every 5000 records to into a table.  I dont know how to put a counter into a stored procedure to accomplish this task or commits.  I will Include a copy of the stored procedure if someone can tell me where to put the commits and counters.  Or send me a sample procedure that accomplishes this task.
CREATE PROCEDURE UECI111.ECI_SMASBBBB (IN BEGIN_DATE CHAR(10),
                                       IN END_DATE CHAR(10),
                                       IN GROUP_NUM CHAR(12)
										)

    SPECIFIC ECI_SMASBBBB
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
 
    LANGUAGE SQL
------------------------------------------------------------------------
--Develop by Michael Hughes 
--Date Develop 12/05/2011
--Date Modified    
--Comments
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------
P0: BEGIN
   DECLARE GLOBAL TEMPORARY TABLE TEMP_T_CLAIMS
    (
       T1_POLICY_NUM						CHAR(12),
       T1_COMPANY_CDE						CHAR(3),
   	   T1_PRODUCT_NME						VARCHAR(50),
       T1_SECONDARY_PRODUCT_NME				VARCHAR(50),
       T1_CLAIMANT_ID						VARCHAR(40),
       T1_COMPANY_NME						VARCHAR(40),
       T1_POLICYHOLDER_ID					CHAR(3),
       T1_POLICYHOLDER_NME					VARCHAR(30),
       T1_BENEFIT_NME						VARCHAR(40),
       T1_BENEFIT_PLAN_POLICY_YEAR_NUM      CHAR(2),
       T1_TOTAL_BILLED_AMT					DECIMAL(14,2),
       T1_NOT_COVERED_AMT					DECIMAL(14,2),
       T1_OTHER_INSURANCE_PAYMENT_AMT       DECIMAL(14,2),
       T1_REPRICED_SAVINGS_AMT				DECIMAL(14,2),
  	   T1_OTHER_INSURANCE_WRITE_OFF_AMT     DECIMAL(14,2),
       T1_DEDUCTIBLE_AMT					DECIMAL(14,2),
       T1_TOTAL_PAID_AMT					DECIMAL(14,2),
       T1_TOTAL_PENDED_CLAIMS_AMT           DECIMAL(14,2)

    )
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN TEMP_USR_TS;
    
    
    DECLARE GLOBAL TEMPORARY TABLE TEMP_T_RESERVES
    (
     T2_EMPLOYEE_SSN_NUM					VARCHAR(40),
	 T2_POLICY_NUM							CHAR(12),
     T2_RESERVE_AMT 						DECIMAL(14,2)  
    )
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN TEMP_USR_TS;


P1: BEGIN
	DECLARE cursor1 CURSOR WITH RETURN FOR
	
	SELECT 
	CLM.T1_POLICY_NUM,
    CLM.T1_COMPANY_CDE,
   	CLM.T1_PRODUCT_NME,
    CLM.T1_SECONDARY_PRODUCT_NME,
    CLM.T1_COMPANY_NME,
    CLM.T1_POLICYHOLDER_ID,
    CLM.T1_POLICYHOLDER_NME,
    CLM.T1_BENEFIT_NME,
    CLM.T1_BENEFIT_PLAN_POLICY_YEAR_NUM ,
    SUM(CLM.T1_TOTAL_BILLED_AMT) AS T1_TOTAL_BILLED_AMT,
    SUM(CLM.T1_NOT_COVERED_AMT) AS T1_NOT_COVERED_AMT,
    SUM(CLM.T1_OTHER_INSURANCE_PAYMENT_AMT) AS T1_OTHER_INSURANCE_PAYMENT_AMT,
    SUM(CLM.T1_REPRICED_SAVINGS_AMT) AS T1_REPRICED_SAVINGS_AMT,
  	SUM(CLM.T1_OTHER_INSURANCE_WRITE_OFF_AMT)AS T1_OTHER_INSURANCE_WRITE_OFF_AMT,
    SUM(CLM.T1_DEDUCTIBLE_AMT) AS T1_DEDUCTIBLE_AMT ,
    SUM(CLM.T1_TOTAL_PAID_AMT)AS T1_TOTAL_PAID_AMT,
    SUM(CLM.T1_TOTAL_PENDED_CLAIMS_AMT) AS T1_TOTAL_PENDED_CLAIMS_AMT ,
   
	RES.T2_RESERVE_AMT 
	 FROM 
	SESSION.TEMP_T_CLAIMS CLM
	
    LEFT JOIN
    SESSION.TEMP_T_RESERVES RES
    ON
    CLM.T1_POLICY_NUM = RES.T2_POLICY_NUM
	AND
	CLM.T1_CLAIMANT_ID = RES.T2_EMPLOYEE_SSN_NUM
	GROUP BY
	CLM.T1_POLICY_NUM,
    CLM.T1_COMPANY_CDE,
   	CLM.T1_PRODUCT_NME,
    CLM.T1_SECONDARY_PRODUCT_NME,
    CLM.T1_COMPANY_NME,
    CLM.T1_POLICYHOLDER_ID,
    CLM.T1_POLICYHOLDER_NME,
    CLM.T1_BENEFIT_NME,
    CLM.T1_BENEFIT_PLAN_POLICY_YEAR_NUM ,
    RES.T2_RESERVE_AMT 
	WITH UR;
	
	INSERT INTO SESSION.TEMP_T_CLAIMS
	( 
	 T1_POLICY_NUM,
     T1_COMPANY_CDE,
   	 T1_PRODUCT_NME,
     T1_SECONDARY_PRODUCT_NME,
     T1_CLAIMANT_ID,
     T1_COMPANY_NME,
     T1_POLICYHOLDER_ID,
     T1_POLICYHOLDER_NME,
     T1_BENEFIT_NME,
     T1_BENEFIT_PLAN_POLICY_YEAR_NUM ,
     T1_TOTAL_BILLED_AMT,
     T1_NOT_COVERED_AMT,
     T1_OTHER_INSURANCE_PAYMENT_AMT,
     T1_REPRICED_SAVINGS_AMT,
  	 T1_OTHER_INSURANCE_WRITE_OFF_AMT,
     T1_DEDUCTIBLE_AMT,
     T1_TOTAL_PAID_AMT,
     T1_TOTAL_PENDED_CLAIMS_AMT
       )	
	
	SELECT 
    	POLICY_NUM,
       	COMPANY_CDE,
   		PRODUCT_NME,
    	SECONDARY_PRODUCT_NME,
      	CLAIMANT_ID,
       	COMPANY_NME,
    	POLICYHOLDER_ID,
    	POLICYHOLDER_NME,
    	BENEFIT_NME,
    	BENEFIT_PLAN_POLICY_YEAR_NUM,
    	SUM(TOTAL_BILLED_AMT)AS TOTAL_BILLED_AMT,
    	SUM(NOT_COVERED_AMT) AS NOT_COVERED_AMT,
    	SUM(OTHER_INSURANCE_PAYMENT_AMT)AS OTHER_INSURANCE_PAYMENT_AMT,
    	SUM(REPRICED_SAVINGS_AMT) AS REPRICED_SAVINGS_AMT,
  	    SUM(OTHER_INSURANCE_WRITE_OFF_AMT) AS OTHER_INSURANCE_WRITE_OFF_AMT,
    	SUM(DEDUCTIBLE_AMT) AS DEDUCTIBLE_AMT,
    	SUM(TOTAL_PAID_AMT) AS TOTAL_PAID_AMT,
    	SUM(TOTAL_PENDED_CLAIMS_AMT) AS TOTAL_PENDED_CLAIMS_AMT
      
 FROM 

	 REQ47261.T_CR_SPEC_RISK_CLAIMS
 WHERE
      
     (POLICY_NUM = GROUP_NUM
      OR
     POLICY_NUM LIKE RTRIM(GROUP_NUM)||'%' )
     and
     DRAFT_CLAIM_PAID_DTE BETWEEN BEGIN_DATE AND END_DATE
GROUP BY
      POLICY_NUM,
      COMPANY_CDE,
   	  PRODUCT_NME,
      SECONDARY_PRODUCT_NME,
      BENEFIT_PLAN_POLICY_YEAR_NUM,
      COMPANY_NME,
      POLICYHOLDER_ID,
      POLICYHOLDER_NME,
      BENEFIT_NME
 	  	
WITH UR ;
/************** ***********END OF STEP3*************************************/
   INSERT INTO SESSION.TEMP_T_RESERVES
	  ( 
	     T2_EMPLOYEE_SSN_NUM,
	  	 T2_POLICY_NUM,
         T2_RESERVE_AMT   
       )

SELECT 
	RES.EMPLOYEE_SSN_NUM,
	RES.POLICY_NUM,
	RES.RESERVE_AMT 
FROM
 	UECI000.T_CR_CLAIM_RESERVES RES
 WHERE

 	(RES.POLICY_NUM = GROUP_NUM
      OR
     RES.POLICY_NUM LIKE RTRIM(GROUP_NUM)||'%' )
 AND
	 RES.VALUATION_DTE = '10/31/2011'
 ORDER BY 2,1
 WITH UR;
		
	OPEN cursor1;

END P1;
END P0

Open in new window

Comment
Watch Question

Data Warehouse / Database Architect
Commented:
Hi Mike,

I think that in this case your DBA is mistaken.

Committing updates (inserts, deletes, etc.) has several impacts.  The biggest things is that it keeps the database engine from preserving locks on modified objects and it limits the temp space that is used to maintain the modified objects.

You're only writing to temporary tables.  A commit won't affect the engine's locking mechanism, nor will it affect temp space usage.

I've honestly never used a COMMIT to manage a temporary table so I don't know if it will "preserve" the written rows across jobs steps.  Even if it does though, the returned table may not be complete if the procedure exits with an error condition.


I'd ask for an explanation of what it is that the DBA is trying to accomplish.  Rewriting the procedure to read from a cursor and write the values to a new temporary table just so you can execute a COMMIT at fixed intervals makes the procedure more complicated than it has to be has little or no upside.


Kent

I would agree with Kent. You run the queries against UECI000.T_CR_CLAIM_RESERVES and REQ47261.T_CR_SPEC_RISK_CLAIMS with uncommitted read isolation, meaning they would acquire no locks. Your DGTTs are declared with no logging, so no log overhead should be expected either. Frequent commits would serve no obvious purpose.

What is the problem that your DBA is hoping to solve by frequent commits?