Defining a Procedure

The following listing represents a Procedure in SQL--not necessarily a well structured procedure, but it does illustrate the parameter list and provides an example of calling other procedures. Look for the BEGIN statement to find the point where the procedure functions actually start.


   1600 -- * Description:                                                                                         
   1700 -- *  THE INPUT FOR THIS PROCEDURE IS THE COLUMNS FOR                                                      
   1800 -- *  UPDATING THE PATIENT IMMUNIZATION DATA. THE PROCEDURE WILL                                               
   1900 -- *  CALL THE APPROPRATE PROCEDURE TO UPDATE THE CORRESPONDING                              
   2000 -- *  TABLE.                                                                                              
   2100 -- *                                                                                                           
   2200 -- *  PROCEDURES CALLED TO UPDATE IMMUNZATION TABLES                                                       
   2300 -- *  **********************************************                                                       
   2400 -- *  CLIMSP_PATID_AUD (CLIMSP_FRM_IMAP_CPT)                                                            
   2500 -- *  CLIMSP_PATHDR_AUD (CLIMSP_FRM_HMAP_CPT)                                                          
   2600 -- *  CLIMSP_PATSERIES_AUD (if SERIES_ID is not null) (CLIMSP_FRM_SMAP_CPT)                                     
   2700 -- *  CLIMSP_PATVAC_AUD (CLIMSP_FRM_VMAP_CPT)                                                                   
   2800 -- *  CLIMSP_PATREACT_AUD (for any number of REACTION_CODEs) (CLIMSP_FRM_RMAP_CP                                
   2900 -- *  CLIMSP_PATCMN_AUD (CLIMSP_FRM_CMAP_CPT)                                                                  
   3000 -- *  CLIMSP_AUDPAGE                                                                                            
   3100 -- *                                                                                                            
   3200 -- ****  FACILITY_NUMBER AND HISTORY_NUMBER CAN BE NULL IF                                                      
   3300 -- ****  PERSON_ID IS PROVIDED                                                                                 
   3400 -- ****  PERSON_ID CAN BE NULL IF FACILTIY AND HISTORY NUMBER                                                  
   3500 -- ****  IS PROVIDED                                                                                            
   3600 -- ****  CALCULATE ENTRY_DATETIME AS CURRENT_TIMESTAMP ADJUSTED                                                 
   3700 -- ****  WITH THE APPLICATION OFFSET, IF NOT PROVIDED                                                           
   3800 -- ****                                                                                                         
   3900 -- ****  THE INOUT IDENTITY COLUMNS AT THE END OF THE PARAMETER LIST                                            
   4000 -- ****  ARE REQUIRED FOR THE UPDATE TO A TABLE.  IF THE UPDATE IS                                             
   4100 -- ****  FOR ADD A NEW ROW, PASS NULL.  THE ASSIGNED VALUES ARE RETURNED                                        
   4200 -- ****  TO THE CALLER.                                                                                         
   4300 -- *                                                                                                            
   4400 -- *  PARAMETERS NAME                                   DATA TYPE                                               
   4500 -- *  1) ACTION_CODE (IN)                               CHAR(1)                                                 
   4600 -- *  2) FACILITY_NUMBER (IN)                           NUMERIC(3,0)                                            
   4700 -- *  3) HISTORY_NUMBER (IN)                            NUMERIC(9,0)                                            
   4800 -- *  4) PERSON_ID (IN)                                 BIGINT                                                  
   4900 -- *  5) PT_STATE_REGNBR (IN) (nullable)                VARCHAR(30)                                             
   5000 -- *  6) REVIEW_DATETIME (IN) (nullable)                TIMESTAMP                                               
   5100 -- *  7) REVIEW_USER (IN) (nullable)                    VARCHAR(20)                      
   5200 -- *  8) CVX_CODE (IN)                                  CHAR(5)                               
   5300 -- *  9) ADMINISTER_DATETIME (IN)                       TIMESTAMP                                        
   5400 -- * 10) CONSENT_STS (IN)                               CHAR(1)                                                 
   5500 -- * 11) ENTRY_DATETIME (IN) (nullable)                 TIMESTAMP                                               
   5600 -- * 12) ENTRY_USERID (IN)                              VARCHAR(20)                               
   5700 -- * 13) SERIES_NUMBER (IN) (nullable)                  SMALLINT                                  
   5800 -- * 14) SERIES_ID (IN) (nullable)                      INTEGER                                   
   5900 -- * 15) HEADER_COMMENT (IN) (nullable)                 VARCHAR(2048)                             
   6000 -- * 16) DETAIL_COMMENT (IN) (nullable)                 VARCHAR(2048)                             
   6100 -- * 17) ADMIN_LOCATION_1 (IN) (nullable)               VARCHAR(50)                               
   6200 -- * 18) ADMIN_LOCATION_2 (IN) (nullable)               VARCHAR(50)                               
   6300 -- * 19) ADMIN_LOCATION_3 (IN) (nullable)               VARCHAR(50)                             
   6400 -- * 20) CONSENT_DT (IN) (nullable)                     TIMESTAMP                               
   6500 -- * 21) AUDIT_WRKSTN (IN)                              VARCHAR(20)                             
   6600 -- * 22) RECORD_ID_RC_1 (IN) (nullable)                 CHAR(1)                                 
   6700 -- * 23) REACTION_CODE_1 (IN) (nullable)                CHAR(5)                                 
   6800 -- * 24) RECORD_ID_RC_2 (IN) (nullable)                 CHAR(1)                                 
   6900 -- * 25) REACTION_CODE_2 (IN) (nullable)                CHAR(5)                                 
   7000 -- * 26) RECORD_ID_RC_3 (IN) (nullable)                 CHAR(1)                                 
   7100 -- * 27) REACTION_CODE_3 (IN) (nullable)                CHAR(5)                                 
   7200 -- * 28) RECORD_ID_RC_4 (IN) (nullable)                 CHAR(1)                                 
   7300 -- * 29) REACTION_CODE_4 (IN) (nullable)                CHAR(5)                                 
   7400 -- * 30) RECORD_ID_RC_5 (IN) (nullable)                 CHAR(1)                                 
   7500 -- * 31) REACTION_CODE_5 (IN) (nullable)                CHAR(5)                                 
   7600 -- * 32) TR_ID (IN) (nullable)                          BIGINT                                  
   7700 -- * 33) RECORD_ID_HD (IN)                              CHAR(1)                                 
   7800 -- * 34) RECORD_ID_SR (IN)                              CHAR(1)                                 
   7900 -- * 35) RECORD_ID_VC (IN)                              CHAR(1)                                 
   8000 -- * 36) PATIENT_IMMUN_ID (IN)                          BIGINT                                  
   8100 -- * 37) PTHDR_ID (IN)                                  BIGINT                                  
   8200 -- * 38) PTSERIES_ID (IN) (nullable)                    BIGINT                                  
   8300 -- * 39) PTVAC_ID (IN)                                  BIGINT                                  
   8400 -- * 40) REACTION_ID_1 (IN) (nullable)                  BIGINT                                  
   8500 -- * 41) REACTION_ID_2 (IN) (nullable)                  BIGINT                                  
   8600 -- * 42) REACTION_ID_3 (IN) (nullable)                  BIGINT                                  
   8700 -- * 43) REACTION_ID_4 (IN) (nullable)                  BIGINT                                  
   8800 -- * 44) REACTION_ID_5 (IN) (nullable)                  BIGINT                                  
   8900 -- * 45) SNAP_RECDT (INOUT) (nullable)                  TIMESTAMP                               
   9000 -- * DB80589                                                                                    
   9100 -- * 46) VAC_AUDIT_USERNAME (IN) (nullable)             VARCHAR(25)                             
   9200 -- * 47) VAC_AUDIT_USERTITLE (IN) (nullable)            CHAR(5)                                 
   9300 -- * 48) VAC_MOD_IGN_FLAG (IN) (nullable)               CHAR(1)                                 
   9400 -- * 49) VAC_MOD_IGN_REASON (IN) (nullable)             CHAR(5)                                 
   9500 -- * 50) VAC_MOD_IGN_COMMENT (IN) (nullable)            VARCHAR(256)                            
   9600 -- * 51) CMN_AUDIT_USERNAME (IN) (nullable)             VARCHAR(25)                             
   9700 -- * 52) CMN_AUDIT_USERTITLE (IN) (nullable)            CHAR(5)                                 
   9800 -- * 53) CMN_MOD_IGN_FLAG (IN) (nullable)               CHAR(1)                                 
   9900 -- * 54) CMN_MOD_IGN_REASON (IN) (nullable)             CHAR(5)                                 
  10000 -- * 55) CMN_MOD_IGN_COMMENT (IN) (nullable)            VARCHAR(256)                            
                                                                                                  
  10300 CREATE PROCEDURE &LIB/CLIMSP_PATIENT_FRM (                                                      
  10400   IN ACTION_CODE_IN CHAR(1),                                                                    
  10500   IN FACILITY_NUMBER_IN NUMERIC(3,0),                                                           
  10600   IN HISTORY_NUMBER_IN NUMERIC(9,0),                                                            
  10700   IN PERSON_ID_IN BIGINT,                                                                   
  10800   IN PT_STATE_REGNBR_IN VARCHAR(30),                                                                 
  10900   IN REVIEW_DATETIME_IN TIMESTAMP,                                                                              
  11000   IN REVIEW_USER_IN VARCHAR(20),                                                                                
  11100   IN CVX_CODE_IN CHAR(5),                                                                                       
  11200   IN ADMINISTER_DATETIME_IN TIMESTAMP,                                                                          
  11300   IN CONSENT_STS_IN CHAR(1),                                                                                    
  11400   IN ENTRY_DATETIME_IN TIMESTAMP,                                                                               
  11500   IN ENTRY_USERID_IN VARCHAR(20),                                                                               
  11600   IN SERIES_NUMBER_IN SMALLINT,                                                                                 
  11700   IN SERIES_ID_IN INTEGER,                                                                                      
  11800   IN HEADER_COMMENT_IN VARCHAR(2048),                                                                           
  11900   IN DETAIL_COMMENT_IN VARCHAR(2048),                                                                           
  12000   IN ADMIN_LOCATION_1_IN VARCHAR(50),                                                                           
  12100   IN ADMIN_LOCATION_2_IN VARCHAR(50),                                                                           
  12200   IN ADMIN_LOCATION_3_IN VARCHAR(50),                                                                           
  12300   IN CONSENT_DT_IN TIMESTAMP,                                                                                   
  12400   IN AUDIT_WRKSTN_IN VARCHAR(20),                                                                               
  12500   IN RECORD_ID_RC_1_IN CHAR(1),                                                                                 
  12600   IN REACTION_CODE_1_IN CHAR(5),                                                                                
  12700   IN RECORD_ID_RC_2_IN CHAR(1),                                                                                 
  12800   IN REACTION_CODE_2_IN CHAR(5),                                                                                
  12900   IN RECORD_ID_RC_3_IN CHAR(1),                                                                                 
  13000   IN REACTION_CODE_3_IN CHAR(5),                                                                                
  13100   IN RECORD_ID_RC_4_IN CHAR(1),                                                                                 
  13200   IN REACTION_CODE_4_IN CHAR(5),                                                                                
  13300   IN RECORD_ID_RC_5_IN CHAR(1),                                                                                 
  13400   IN REACTION_CODE_5_IN CHAR(5),                                                                                
  13500   IN TR_ID_IN BIGINT,                                                                                           
  13600   IN RECORD_ID_HD_IN CHAR(1),                                                                                   
  13700   IN RECORD_ID_SR_IN CHAR(1),                                                                                   
  13800   IN RECORD_ID_VC_IN CHAR(1),                                                                                   
  13900   IN PT_IMID_IN BIGINT,                                                                                         
  14000   IN PTHDR_ID_IN BIGINT,                                                                                        
  14100   IN PTSERIES_ID_IN BIGINT,                                                                                     
  14200   IN PTVAC_ID_IN BIGINT,                                                                                        
  14300   IN REACTION_ID_1_IN BIGINT,                                                                                   
  14400   IN REACTION_ID_2_IN BIGINT,                                                                                   
  14500   IN REACTION_ID_3_IN BIGINT,                                                                                   
  14600   IN REACTION_ID_4_IN BIGINT,                                                                                   
  14700   IN REACTION_ID_5_IN BIGINT,                                                                                   
  14800   INOUT SNAP_RECDT_INOUT TIMESTAMP,                                                                             
  14900 --  DB80589                                                                                                     
  15000   IN VAC_AUDIT_USERNAME_IN VARCHAR(25),                                                                         
  15100   IN VAC_AUDIT_USERTITLE_IN CHAR(5),                                                                            
  15200   IN VAC_MOD_IGN_FLAG_IN CHAR(1),                                                                               
  15300   IN VAC_MOD_IGN_REASON_IN CHAR(5),                                                                             
  15400   IN VAC_MOD_IGN_COMMENT_IN VARCHAR(256),                                                                       
  15500   IN CMN_AUDIT_USERNAME_IN VARCHAR(25),                                                                         
  15600   IN CMN_AUDIT_USERTITLE_IN CHAR(5),                                                                            
  15700   IN CMN_MOD_IGN_FLAG_IN CHAR(1),                                                                               
  15800   IN CMN_MOD_IGN_REASON_IN CHAR(5),                                                                             
  15900   IN CMN_MOD_IGN_COMMENT_IN VARCHAR(256))                                                                       
 
  16000 --                                                                                                              
  16100  LANGUAGE SQL                                                                                                   
  16200  SPECIFIC CLIMSP_PATIENT_FRM2                                                                        
  16300  DETERMINISTIC                                                                                       
  16400  MODIFIES SQL DATA                                                                                   
  16500  CALLED ON NULL INPUT                                                                                
  16600  SET OPTION DBGVIEW = *SOURCE, DATFMT = *ISO, TIMFMT = *ISO,                                                    
  16700      TGTRLS = V5R3M0                                                                                            
  16800                                                                                                                 
  16900  BEGIN                                                                                                          
  17000                                                                                                                 
  17100   /* DECLARE VARIABLES */                                                                                       
  17200   DECLARE EDIT_LEVEL CHAR(5) DEFAULT 'H' ;                                                                      
  17300   DECLARE PTCMT_ID BIGINT ;                                                                                     
  17400   DECLARE RECORD_ID_CMT CHAR(1) DEFAULT ' ' ;                                                                   
  17500   DECLARE HDR_COMMENT_SEQ_NBR SMALLINT DEFAULT 1;                                                               
  17600   DECLARE ROUTE_CODE CHAR(10) DEFAULT NULL;                                                                     
  17700   DECLARE SITE_CODE CHAR(5) DEFAULT NULL;                                                                       
  17800   DECLARE ADMINISTER_BY_NAME VARCHAR(25) DEFAULT NULL;                                                          
  17900   DECLARE ADMINISTER_BY_TITLE CHAR(5) DEFAULT NULL;                                                             
  18000   DECLARE MVX_CODE CHAR(5) DEFAULT NULL;                                                                        
  18100   DECLARE LOT_NUMBER VARCHAR(50) DEFAULT NULL;                                                                  
  18200   DECLARE EXPIRATION_DATE DATE DEFAULT NULL;                                                                    
  18300   DECLARE VIS_EDITION CHAR(10) DEFAULT NULL;                                                                    
  18400   DECLARE VIS_PROVIDED_DATE DATE DEFAULT NULL;                                                                  
  18500   DECLARE SOURCE_CODE CHAR(7) DEFAULT NULL;                                                                     
  18600   DECLARE CONSENT_SOURCE_NAME VARCHAR(50) DEFAULT NULL;                                                         
  18700   DECLARE CONTRAINDICATION_CODE CHAR(5) DEFAULT NULL;                                                           
  18800   DECLARE ADMIN_DOSE CHAR(15) DEFAULT NULL;                                                                     
  18900   DECLARE UOM_ABBREV CHAR(3) DEFAULT NULL;                                                                      
  19000   DECLARE ENTRY_DESC VARCHAR(128);                                                                              
  19100   DECLARE PID_AUDDATETIME TIMESTAMP;                                                                            
  19200   DECLARE HDR_AUDDATETIME TIMESTAMP;                                                                            
  19300   DECLARE VAC_AUDDATETIME TIMESTAMP;                                                                            
  19400   DECLARE CMT_AUDDATETIME TIMESTAMP;                                                                            
  19500   DECLARE SER_AUDDATETIME TIMESTAMP;                                                                            
  19600   DECLARE RCT_AUDDATETIME TIMESTAMP;                                                                            
  19700   DECLARE PAG_AUDDATETIME TIMESTAMP;                                                                            
  19800   DECLARE AUDIT_ACTION CHAR(1) DEFAULT 'A' ;                                                                    
  19900   DECLARE SERIES_DATETIME TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000';                                       
  20000   DECLARE VAC_RECDT TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000';                                             
  20100   DECLARE RCT_RECDT TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000';                                             
  20200   DECLARE CMT_RECDT TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000';                                             
  20300   DECLARE HDR_RECDT TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000';                                             
  20400                                                                                                                 

  20500   DECLARE SEQ_1 SMALLINT DEFAULT 1;                                                                             
  20600   DECLARE SEQ_2 SMALLINT DEFAULT 2;                                                                             
  20700   DECLARE SEQ_3 SMALLINT DEFAULT 3;                                                                             
  20800   DECLARE SEQ_4 SMALLINT DEFAULT 4;                                                                             
  20900   DECLARE SEQ_5 SMALLINT DEFAULT 5;                                                                             
  21000                                                                                                                 
  21100                                                                                                                 
  21200   /* OTHER SQL VARIABLES */                                                                                     
  
  21300   DECLARE SQLSTR VARCHAR (4096) ;                                                                               
  21400   DECLARE ROWCOUNT SMALLINT ;                                                                                   
  21500   DECLARE SQLOK SMALLINT DEFAULT 0 ;                                                                            
  21600   DECLARE AT_END INT DEFAULT 0 ;                                                                                
  21700   DECLARE TRANS_STATE_ERR INT DEFAULT 0 ;                                                                       
  21800   DECLARE ACTIVE INT DEFAULT 0 ;                                                                                
  21900                                                                                                                 
  22000   /* DECLARE CONDITIONS */                                                                                      
  22100   DECLARE DUPLICATE_ROW                                                                                         
  22200     CONDITION FOR SQLSTATE '23505' ;                                                                            
  22300   DECLARE INVALID_TRANS_STATE                                                                                   
  22400     CONDITION FOR SQLSTATE '25501' ;                                                                            
  22500                                                                                                                 
  22600   /* DECLARE CURSORS */                                                                                         
  22700                                                                                                                 
  22800   /* DECLARE HANDLERS */                                                                                        
  22900   DECLARE EXIT HANDLER FOR DUPLICATE_ROW                                                                        
  23000   /* ALTHOUGH COULD BE A BEGIN-END BLOCK; IT IS UNSUPPORTED IN V5R1M0 */                                        
  23100     ERRDUPROW: LOOP                                                                                             
  23200       IF TRANS_STATE_ERR <> 1 THEN                                                                              
  23300         ROLLBACK HOLD ;                                                                                         
  23400         SET TRANSACTION ISOLATION LEVEL NC ;                                                                    
  23500       END IF ;                                                                                                  
  23600       RESIGNAL ;                                                                                                
  23700       LEAVE ERRDUPROW ;                                                                                         
  23800     END LOOP ;                                                                                                  
  23900                                                                                                                 
  24000   DECLARE EXIT HANDLER FOR SQLEXCEPTION                                                                         
  24100   /* ALTHOUGH COULD BE A BEGIN-END BLOCK; IT IS UNSUPPORTED IN V5R1M0 */                                        
  24200     EXCEPTHANDLER: LOOP                                                                                         
  24300       IF TRANS_STATE_ERR <> 1 THEN                                                                              
  24400         ROLLBACK HOLD ;                                                                                         
  24500         SET TRANSACTION ISOLATION LEVEL NC ;                                                                    
  24600       END IF ;                                                                                                  
  24700       RESIGNAL ;                                                                                                
  24800       LEAVE EXCEPTHANDLER ;                                                                                     
  24900     END LOOP ;                                                                                                  
  25000                                                                                                                 
  25100   DECLARE CONTINUE HANDLER FOR INVALID_TRANS_STATE                                                              
  25200   /* WHEN CALLED FROM ANOTHER PROCEDURE, THE FOLLOWING SET TRANSACTION */                                       
  25300   /* STATEMENT WILL FAIL.  IGNORE THIS ERROR, SINCE A TRANSACTION WAS */                                        
  25400   /* STARTED IN THE CALLING PROCEDURE. */                                                                       
  25500   /* WHEN THIS ERROR IS THROWN, THE COMMIT IS DONE OUTSIDE OF THIS */                                           
  25600   /* PROCEDURE. */                                                                                              
  25700     SET TRANS_STATE_ERR = 1 ;                                                                                   
  25800                                                                                                                 
  25900   /*  MAIN BODY OF PROCEDURE  */                                                                                
  26000                                                                                                                 
  26100   /*  TRANSACTIONS ARE DONE, SO JOURNALING IS REQUIRED. */                                                      
  26200   GET DIAGNOSTICS ACTIVE = TRANSACTION_ACTIVE;                                                                  
  26300   IF ACTIVE = 0 THEN                                                                                            
  26400       SET TRANSACTION ISOLATION LEVEL CS;                                                                       
  26500   ELSE                                                                                                          
  26600       SET TRANS_STATE_ERR = 1;                                                                                 
  26700   END IF;                                                                                                      
  26800                                                                                                                 
  26900   /*  SET NULLABLE COLUMNS IN INPUT PARAMETERS */                                                               
  27000                                                                                                                 
  27100   IF RECORD_ID_HD_IN IS NULL THEN                                                                               
  27200       SET RECORD_ID_HD_IN = ' ';                                                                                
  27300   END IF ;                                                                                                     
  27400                                                                                                                 
  27500   IF RECORD_ID_VC_IN IS NULL THEN                                                                              
  27600       SET RECORD_ID_VC_IN = ' ';                                                                               
  27700   END IF ;                                                                                                      
  27800                                                                                                                 
  27900   SET PID_AUDDATETIME = TO0P09FN();                                                                            
  28000   SET VAC_AUDDATETIME = TO0P09FN();                                                                             
  28100                                                                                                                 
  28200   IF REVIEW_DATETIME_IN IS NULL OR                                                                              
  28300      REVIEW_DATETIME_IN = '0001-01-01-00.00.00.000000' THEN                                                     
  28400        SET REVIEW_DATETIME_IN = TO0P09FN();                                                                     
  28500   END IF;                                                                                                       
  28600   IF ADMINISTER_DATETIME_IN IS NULL OR                                                                          
  28700      ADMINISTER_DATETIME_IN = '0001-01-01-00.00.00.000000' THEN                                                 
  28800        SET ADMINISTER_DATETIME_IN = TO0P09FN();                                                                 
  28900   END IF;                                                                                                       
  29000   IF ENTRY_DATETIME_IN IS NULL OR                                                                               
  29100      ENTRY_DATETIME_IN = '0001-01-01-00.00.00.000000' THEN                                                     
  29200        SET ENTRY_DATETIME_IN = TO0P09FN();                                                                      
  29300   END IF;                                                                                                       
  29400                                                                                                                 
  29500                                                                                                                 
  29600   /*  CALL PROCEDURE TO INSERT ROW INTO PATID AUDIT TABLE */                                                    
  29700   SET SQLSTR = 'CALL CLIMSP_PATID_AUD(?,?,?,?,?,?,?,?,?)';                                                     
  29800   /* PREPARE CALL */                                                                                            
  29900   PREPARE CALL_CLIMSPCPI FROM SQLSTR ;                                                                          
  30000   /* EXECUTE THE PREPARED STATEMENT */                                                                          
  30100   EXECUTE CALL_CLIMSPCPI USING PT_IMID_IN, FACILITY_NUMBER_IN,                                                  
  30200     HISTORY_NUMBER_IN, PERSON_ID_IN, AUDIT_ACTION,                                                              
  30300     ENTRY_DATETIME_IN, ENTRY_USERID_IN, AUDIT_WRKSTN_IN,                                                        
  30400     PID_AUDDATETIME;                                                                                            
  30500   IF TR_ID_IN IS NOT NULL THEN                                                                                  
  30600     /* THIS CODE UPDATES THE FORM SNAPSHOT TABLE */                                                            
  30700     /* IMAP */                                                                                                  
  30800     SET SQLSTR = 'CALL CLIMSP_FRM_IMAP_CPT(?,?,?,?,?,?,?,?)';                                                   
  30900     /* PREPARE CALL */                                                                                          
  31000     PREPARE CALL_CLIMSPIMP FROM SQLSTR ;                                                                        
  31100     /* EXECUTE THE PREPARED STATEMENT */                                                                        
  31200     EXECUTE CALL_CLIMSPIMP USING ACTION_CODE_IN, PID_AUDDATETIME,                                               
  31300       TR_ID_IN, ENTRY_USERID_IN, ENTRY_DATETIME_IN,                                                             
  31400       ENTRY_USERID_IN, AUDIT_WRKSTN_IN, SNAP_RECDT_INOUT;                                                       
  31500   END IF;                                                                                                       
  31600                                                                                                                 
    
  31900   /*  CALL PROCEDURE TO INSERT ROW INTO PATHDR AUDIT TABLE */                                                   
  32000   SET SQLSTR = 'CALL CLIMSP_PATHDR_AUD(?,?,?,?,?,?,?,?,?,?,?,?)';                                               
  32100   /* PREPARE CALL */                                                                                            
  32200   PREPARE CALL_CLIMSPHDR FROM SQLSTR ;                                                                          
  32300   /* EXECUTE THE PREPARED STATEMENT */                                                                          
  32400   EXECUTE CALL_CLIMSPHDR USING RECORD_ID_HD_IN,                                                                 
  32500     PTHDR_ID_IN, PT_IMID_IN, HDR_RECDT, PT_STATE_REGNBR_IN,                                                     
  32600     REVIEW_DATETIME_IN, REVIEW_USER_IN, AUDIT_ACTION,                                                           
  32700     ENTRY_DATETIME_IN, ENTRY_USERID_IN, AUDIT_WRKSTN_IN,                                                        
  32800     HDR_AUDDATETIME;                                                                                            
  32900   IF TR_ID_IN IS NOT NULL THEN                                                                                  
  33000     /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                            
  33100     /* HMAP */                                                                                                  
  33200     SET SQLSTR = 'CALL CLIMSP_FRM_HMAP_CPT(?,?,?,?,?,?,?)';                                                     
  33300     /* PREPARE CALL */                                                                                          
  33400     PREPARE CALL_CLIMSPHMP FROM SQLSTR ;                                                                        
  33500     /* EXECUTE THE PREPARED STATEMENT */                                                                        
  33600     EXECUTE CALL_CLIMSPHMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                              
  33700       PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                               
  33800       ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                         
  33900   END IF;                                                                                                       
  34000                                                                                                                 
  34100                                                                                                                 
  34200                                                                                                                 
  34300   IF SERIES_ID_IN IS NOT NULL THEN                                                                              
  34400     IF RECORD_ID_SR_IN  IS NULL THEN                                                                            
  34500         SET RECORD_ID_SR_IN  = ' ';                                                                             
  34600     END IF ;                                                                                                    
  34700     /*  CALL PROCEDURE TO INSERT ROW INTO PATSERIES AUDIT TABLE */                                              
  34800     SET SQLSTR = 'CALL CLIMSP_PATSERIES_AUD(?,?,?,?,?,?,?,?,?,?)';                                              
  34900     PREPARE CALL_CLIMSPCSR FROM SQLSTR ;                                                                        
  35000     /*  EXECUTE THE PREPARED STATEMENT */                                                                       
  35100     EXECUTE CALL_CLIMSPCSR USING RECORD_ID_SR_IN,                                                               
  35200       PT_IMID_IN, PTHDR_ID_IN, SERIES_DATETIME,                                                                 
  35300       SERIES_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN,                                                            
  35400       ENTRY_USERID_IN, AUDIT_WRKSTN_IN, SER_AUDDATETIME;                                                        
  35500     IF TR_ID_IN IS NOT NULL THEN                                                                                
  35600       /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                          
  35700       /* SMAP */                                                                                                
  35800       SET SQLSTR = 'CALL CLIMSP_FRM_SMAP_CPT(?,?,?,?,?,?,?,?)';                                                 
  35900       /* PREPARE CALL */                                                                                        
  36000       PREPARE CALL_CLIMSPSMP FROM SQLSTR ;                                                                      
  36100       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  36200       EXECUTE CALL_CLIMSPSMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                            
  36300         PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                             
  36400         ENTRY_USERID_IN, AUDIT_WRKSTN_IN, SER_AUDDATETIME;                                                      
  36500     END IF;                                                                                                     
  36600   END IF;                                                                                                       
  36700                                                                                                                 
  
  37000   /*  CALL PROCEDURE TO INSERT ROW INTO PATVAC AUDIT TABLE */                                                   
  37100   SET SQLSTR = 'CALL CLIMSP_PATVAC_AUD(' ||                                                                     
  37200       '?,?,?,?,?,?,?,?,?,?,' ||                                                                                 
  37300       '?,?,?,?,?,?,?,?,?,?,' ||                                                                                 
  37400       '?,?,?,?,?,?,?,?,?,?,' ||                                                                                 
  37500       '?,?,?,?,?,?,?,?,?)';                                                                                     
  37600   PREPARE CALL_CLIMSPPVC FROM SQLSTR ;                                                                          
  37700   /*  EXECUTE THE PREPARED STATEMENT */                                                                         
  37800   EXECUTE CALL_CLIMSPPVC USING RECORD_ID_VC_IN,                                                                 
  37900     PT_IMID_IN, PTHDR_ID_IN, VAC_RECDT,                                                                         
  38000     CVX_CODE_IN, ROUTE_CODE, SITE_CODE,                                                                         
  38100     ADMINISTER_DATETIME_IN, ADMINISTER_BY_NAME,                                                                 
  38200     ADMINISTER_BY_TITLE, MVX_CODE, LOT_NUMBER,                                                                  
  38300     EXPIRATION_DATE, VIS_EDITION, VIS_PROVIDED_DATE,                                                            
  38400     CONSENT_STS_IN, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                                         
  38500     SOURCE_CODE, CONSENT_SOURCE_NAME,                                                                           
  38600     CONTRAINDICATION_CODE, PTSERIES_ID_IN, SERIES_NUMBER_IN,                                                    
  38700     DETAIL_COMMENT_IN, ADMIN_DOSE, UOM_ABBREV,                                                                  
  38800     ADMIN_LOCATION_1_IN, ADMIN_LOCATION_2_IN, ADMIN_LOCATION_3_IN,                                              
  38900     EDIT_LEVEL, AUDIT_WRKSTN_IN, CONSENT_DT_IN, VAC_AUDDATETIME, AUDIT_ACTION,                                  
  39000 --  DB80589                                                                                                     
  39100     VAC_AUDIT_USERNAME_IN, VAC_AUDIT_USERNAME_IN, VAC_MOD_IGN_FLAG_IN,                                          
  39200     VAC_MOD_IGN_REASON_IN, VAC_MOD_IGN_COMMENT_IN;                                                              
  39300   IF TR_ID_IN IS NOT NULL THEN                                                                                  
  39400     /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                            
  39500     /* VMAP */                                                                                                  
  39600     SET SQLSTR = 'CALL CLIMSP_FRM_VMAP_CPT(?,?,?,?,?,?,?,?)';                                                   
  39700     /* PREPARE CALL */                                                                                          
  39800     PREPARE CALL_CLIMSPVMP FROM SQLSTR ;                                                                        
  39900     /* EXECUTE THE PREPARED STATEMENT */                                                                        
  40000     EXECUTE CALL_CLIMSPVMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                              
  40100       PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                               
  40200       ENTRY_USERID_IN, AUDIT_WRKSTN_IN, VAC_AUDDATETIME;                                                        
  40300   END IF;                                                                                                       
  40400                                                                                                                 
  
  40600                                                                                                                 
  40700   IF REACTION_CODE_1_IN IS NOT NULL OR                                                                          
  40800      REACTION_CODE_2_IN IS NOT NULL OR                                                                          
  40900      REACTION_CODE_3_IN IS NOT NULL OR                                                                          
  41000      REACTION_CODE_4_IN IS NOT NULL OR                                                                          
  41100      REACTION_CODE_5_IN IS NOT NULL THEN                                                                        
  41200                                                                                                                 
  41300 --  SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                             
  41400 --  PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                        
  41500                                                                                                                 
  41600 --  SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                                 
  41700 --  PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                        
  41800                                                                                                                 
  41900                                                                                                                 
  42000     IF REACTION_CODE_1_IN IS NOT NULL THEN                                                                      
  42100       IF RECORD_ID_RC_1_IN IS NULL  THEN                                                                        
  42200         SET RECORD_ID_RC_1_IN = ' ';                                                                            
  42300       END IF;                                                                                                   
  42400       SET RCT_AUDDATETIME = '0001-01-01-00.00.00.000000';                                                       
  42500       SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                           
  42600       PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                      
  42700       /*  CALL PROCEDURE TO INSERT ROW PATREACT INTO AUDIT TABLE */                                             
  42800       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  42900       EXECUTE CALL_CLIMSPPRC USING RECORD_ID_RC_1_IN,                                                           
  43000         PT_IMID_IN, RCT_RECDT, REACTION_CODE_1_IN, SEQ_1,                                                       
  43100         PTVAC_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                          
  43200         AUDIT_WRKSTN_IN, RCT_AUDDATETIME;                                                                       
  43300       IF TR_ID_IN IS NOT NULL THEN                                                                              
  43400         /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                        
  43500         /* RMAP */                                                                                              
  43600         SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                             
  43700         PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                    
  43800         /* EXECUTE THE PREPARED STATEMENT */                                                                    
  43900         EXECUTE CALL_CLIMSPRMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                          
  44000           PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                           
  44100           VAC_AUDDATETIME, RCT_AUDDATETIME,                                                                     
  44200           ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                     
  44300       END IF;                                                                                                   
  44400     END IF;                                                                                                     
  44500                                                                                                                 
  44600     IF REACTION_CODE_2_IN IS NOT NULL THEN                                                                      
  44700       IF RECORD_ID_RC_2_IN IS NULL  THEN                                                                        
  44800         SET RECORD_ID_RC_2_IN = ' ';                                                                            
  44900       END IF;                                                                                                   
  45000       SET RCT_AUDDATETIME = '0001-01-01-00.00.00.000000';                                                       
  45100       SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                           
  45200       PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                      
  45300       /*  CALL PROCEDURE TO INSERT ROW PATREACT INTO AUDIT TABLE */                                             
  45400       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  45500       EXECUTE CALL_CLIMSPPRC USING RECORD_ID_RC_2_IN,                                                           
  45600         PT_IMID_IN, RCT_RECDT, REACTION_CODE_2_IN, SEQ_2,                                                       
  45700         PTVAC_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                          
  45800         AUDIT_WRKSTN_IN, RCT_AUDDATETIME;                                                                       
  45900       IF TR_ID_IN IS NOT NULL THEN                                                                              
  46000         /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                        
  46100         /* RMAP */                                                                                              
  46200         SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                             
  46300         PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                    
  46400         /* EXECUTE THE PREPARED STATEMENT */                                                                    
  46500         EXECUTE CALL_CLIMSPRMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                          
  46600           PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                           
  46700           VAC_AUDDATETIME, RCT_AUDDATETIME,                                                                     
  46800           ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                     
  46900       END IF;                                                                                                   
  47000     END IF;                                                                                                     
  47100                                                                                                                 
  47200     IF REACTION_CODE_3_IN IS NOT NULL THEN                                                                      
  47300       IF RECORD_ID_RC_3_IN IS NULL  THEN                                                                        
  47400         SET RECORD_ID_RC_3_IN = ' ';                                                                            
  47500       END IF;                                                                                                   
  47600       SET RCT_AUDDATETIME = '0001-01-01-00.00.00.000000';                                                       
  47700       SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                           
  47800       PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                      
  47900       /*  CALL PROCEDURE TO INSERT ROW PATREACT INTO AUDIT TABLE */                                             
  48000       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  48100       EXECUTE CALL_CLIMSPPRC USING RECORD_ID_RC_3_IN,                                                           
  48200         PT_IMID_IN, RCT_RECDT, REACTION_CODE_3_IN, SEQ_3,                                                       
  48300         PTVAC_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                          
  48400         AUDIT_WRKSTN_IN, RCT_AUDDATETIME;                                                                       
  48500       IF TR_ID_IN IS NOT NULL THEN                                                                              
  48600         /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                        
  48700         /* RMAP */                                                                                              
  48800         SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                             
  48900         PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                    
  49000         /* EXECUTE THE PREPARED STATEMENT */                                                                    
  49100         EXECUTE CALL_CLIMSPRMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                          
  49200           PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                           
  49300           VAC_AUDDATETIME, RCT_AUDDATETIME,                                                                     
  49400           ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                     
  49500       END IF;                                                                                                   
  49600     END IF;                                                                                                     
  49700                                                                                                                 
  49800     IF REACTION_CODE_4_IN IS NOT NULL THEN                                                                      
  49900       IF RECORD_ID_RC_4_IN IS NULL  THEN                                                                        
  50000         SET RECORD_ID_RC_4_IN = ' ';                                                                            
  50100       END IF;                                                                                                   
  50200       SET RCT_AUDDATETIME = '0001-01-01-00.00.00.000000';                                                       
  50300       SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                           
  50400       PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                      
  50500       /*  CALL PROCEDURE TO INSERT ROW PATREACT INTO AUDIT TABLE */                                             
  50600       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  50700       EXECUTE CALL_CLIMSPPRC USING RECORD_ID_RC_4_IN,                                                           
  50800         PT_IMID_IN, RCT_RECDT, REACTION_CODE_4_IN, SEQ_4,                                                       
  50900         PTVAC_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                          
  51000         AUDIT_WRKSTN_IN, RCT_AUDDATETIME;                                                                       
  51100       IF TR_ID_IN IS NOT NULL THEN                                                                              
  51200         /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                        
  51300         /* RMAP */                                                                                              
  51400         SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                             
  51500         PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                    
  51600         /* EXECUTE THE PREPARED STATEMENT */                                                                    
  51700         EXECUTE CALL_CLIMSPRMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                          
  51800           PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                           
  51900           VAC_AUDDATETIME, RCT_AUDDATETIME,                                                                     
  52000           ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                     
  52100       END IF;                                                                                                   
  52200     END IF;                                                                                                     
  52300                                                                                                                 
  52400     IF REACTION_CODE_5_IN IS NOT NULL THEN                                                                      
  52500       IF RECORD_ID_RC_5_IN IS NULL  THEN                                                                        
  52600         SET RECORD_ID_RC_5_IN = ' ';                                                                            
  52700       END IF;                                                                                                   
  52800       SET RCT_AUDDATETIME = '0001-01-01-00.00.00.000000';                                                       
  52900       SET SQLSTR = 'CALL CLIMSP_PATREACT_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                           
  53000       PREPARE CALL_CLIMSPPRC FROM SQLSTR ;                                                                      
  53100       /*  CALL PROCEDURE TO INSERT ROW PATREACT INTO AUDIT TABLE */                                             
  53200       /* EXECUTE THE PREPARED STATEMENT */                                                                      
  53300       EXECUTE CALL_CLIMSPPRC USING RECORD_ID_RC_5_IN,                                                           
  53400         PT_IMID_IN, RCT_RECDT, REACTION_CODE_5_IN, SEQ_5,                                                       
  53500         PTVAC_ID_IN, AUDIT_ACTION, ENTRY_DATETIME_IN, ENTRY_USERID_IN,                                          
  53600         AUDIT_WRKSTN_IN, RCT_AUDDATETIME;                                                                       
  53700       IF TR_ID_IN IS NOT NULL THEN                                                                              
  53800         /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                        
  53900         /* RMAP */                                                                                              
  54000         SET SQLSTR = 'CALL CLIMSP_FRM_RMAP_CPT(?,?,?,?,?,?,?,?,?)';                                             
  54100         PREPARE CALL_CLIMSPRMP FROM SQLSTR ;                                                                    
  54200         /* EXECUTE THE PREPARED STATEMENT */                                                                    
  54300         EXECUTE CALL_CLIMSPRMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                          
  54400           PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                           
  54500           VAC_AUDDATETIME, RCT_AUDDATETIME,                                                                     
  54600           ENTRY_USERID_IN, AUDIT_WRKSTN_IN;                                                                     
  54700       END IF;                                                                                                   
  54800     END IF;                                                                                                     
  54900                                                                                                                 
  55000   END IF;                                                                                                       
  55300                                                                                                                 
  55400   IF HEADER_COMMENT_IN IS NOT NULL THEN                                                                         
  55500 --  /*  CALL PROCEDURE TO INSERT ROW INTO PATCMN AUDIT TABLE */                                                 
  55600 --  SET SQLSTR = 'CALL CLIMSP_PATCMN_AUD(?,?,?,?,?,?,?,?,?,?,?)';                                               
  55700 --  PREPARE CALL_CLIMSPPCM FROM SQLSTR ;                                                                        
  55800 --  /*  EXECUTE THE PREPARED STATEMENT */                                                                       
  55900 --  EXECUTE CALL_CLIMSPPCM USING RECORD_ID_CMT,                                                                 
  56000 --    PT_IMID_IN, CMT_RECDT, HDR_COMMENT_SEQ_NBR, PTHDR_ID_IN,                                                  
  56100 --    HEADER_COMMENT_IN, AUDIT_ACTION, ENTRY_DATETIME_IN,                                                       
  56200 --    ENTRY_USERID_IN, AUDIT_WRKSTN_IN, CMT_AUDDATETIME;                                                        
  56300     IF TR_ID_IN IS NOT NULL THEN                                                                                
  56400 --    /* THIS CODE UPDATES THE FORM SNAPSHOT TABLES */                                                          
  56500 --    /* CMAP */                                                                                                
  56600 --    SET SQLSTR = 'CALL CLIMSP_FRM_CMAP_CPT(?,?,?,?,?,?,?,?)';                                                 
  56700                                                                                                                 
  56800       SET SQLSTR = 'CALL CLIMSP_PATCMN_FRM (?,?,?,?,?,?,?,?,?,?)';                                              
  56900       PREPARE CALL_PATCMN_FRM FROM SQLSTR ;                                                                     
  57000       EXECUTE CALL_PATCMN_FRM USING ACTION_CODE_IN                                                              
  57100                                   , TR_ID_IN                                                                    
  57200                                   , HDR_COMMENT_SEQ_NBR                                                         
  57300                                   , HEADER_COMMENT_IN                                                           
  57400                                   , CMT_AUDDATETIME                                                             
  57500                                   , HDR_AUDDATETIME                                                             
  57600                                   , ENTRY_DATETIME_IN                                                           
  57700                                   , ENTRY_USERID_IN                                                             
  57800                                   , AUDIT_WRKSTN_IN                                                             
  57900                                   , SNAP_RECDT_INOUT ;                                                          
  58000                                                                                                                 
  58100 --    /* PREPARE CALL */                                                                                        
  58200 --    PREPARE CALL_CLIMSPCMP FROM SQLSTR ;                                                                      
  58300 --    /* EXECUTE THE PREPARED STATEMENT */                                                                      
  58400 --    EXECUTE CALL_CLIMSPCMP USING ACTION_CODE_IN, SNAP_RECDT_INOUT,                                            
  58500 --      PID_AUDDATETIME, HDR_AUDDATETIME, TR_ID_IN,                                                             
  58600 --      ENTRY_USERID_IN, AUDIT_WRKSTN_IN, CMT_AUDDATETIME;                                                      
  58700     END IF;                                                                                                     
  58800   END IF;                                                                                                       
  58900                                                                                                                 
  59000   IF TRANS_STATE_ERR <> 1 THEN                                                                                  
  59100       COMMIT HOLD ;                                                                                             
  59200       SET TRANSACTION ISOLATION LEVEL NC ;                                                                      
  59300   END IF ;                                                                                                      
  59400                                                                                                                 
  59500  END ;                                                                                                          
  

Note the COMMIT HOLD statement--the HOLD clause specifies the resources are on hold--i.e. specifically opened cursors are not closed and all resources acquired during the procedure are held. However, locks on specific rows are released. The COMMIT statement ends the unit of work in which it is executed. It commits all changes made by SQL schema statements (except DROP SCHEMA) and SQL data change statements during the unit of work just completed. IBM recommends that an explicit COMMIT or ROLLBACK statement should be coded at the end of an application process.