Month: November 2020

Oracle GoldenGate – Conditional replication based on the value of a column

A colleague had a requirement to replicate rows from a table only when a column was updated to a specific value and came up with this solution. The table in question was a configuration table which contained parameters and values that was loaded in advance of code deployment. After the code was deployed, the parameter was activated by updating the column REPLICATE_FLAG to a value of “y”.

On the source side, the table is defined in the .prm file as follows:

TABLE TABLE_OWNER.CONFIG_TABLE, WHERE (REPLICATE_FLAG = 'y' OR REPLICATE ='Y');

This causes GoldenGate to replicate only after the row is updated to a “y”. On the target side, the row can either exist in which case GoldenGate performs an update or the row does not exist and GoldenGate performs an insert. In order to handle both conditions, the *.prm file has the parameter insertmissingupdates.

insertmissingupdates
MAP TABLE_OWNER_PROD.CONFIG_TABLE , TARGET TABLE_OWNER_TARGET.CONFIG_TABLE;
noinsertupdates
MAP TABLE_OWNER_PROD.OTHER_TABLE , TARGET TABLE_OWNER_TARGET.OTHER_TABLE

Since you do not want this parameter (i.e. missing updates converted to inserts behavior) to be applied to other tables being replicated, you can either turn it off with the noinsertupdates or put the table that needs the special handling at the end.

This was tested on Oracle GoldenGate Version 19.1.0.0.4 and Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

Left aligning DSNTEP2 (aka batch SPUFI) output

A short blog about a trivial but annoying item. By default, the DSNTEP2 (aka batch SPUFI) output is center aligned. As I prefer the output to be left aligned for readability and parsing of output, I code the JCL as described below.

By default, the below code will center align the output:

//SYSTSIN   DD *                                         
 DSN SYSTEM(PRN3)                                        
 RUN  PROGRAM(DSNTEP2) PLAN(DSNTEP2)
//*                                                      
//SYSIN    DD  *                                         
  SELECT    COUNT(*)                                     
  FROM      Q.OBJECT_DATA                                
  ;

And the output looks like:
(Actual display is further to the right.I moved it to the left for readability.) 

PAGE    1                                                                     
***INPUT STATEMENT:                                                           
   SELECT    COUNT(*)                                                         
   FROM      Q.OBJECT_DATA                                                    
   ;                                                                          
                                                 +----------------+ 
                                                 |                | 
                                                 +----------------+ 
                                               1_|           4042 | 
                                                 +----------------+ 
SUCCESSFUL RETRIEVAL OF          1 ROW(S)

To left align, code as:

Default (i.e. center) alignment:

//SYSTSIN   DD *                                         
 DSN SYSTEM(PRN3)                                        
 RUN  PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARMS('/ALIGN(LHS)')
//*                                                      
//SYSIN    DD  *                                         
  SELECT    COUNT(*)                                     
  FROM      Q.OBJECT_DATA                                
  ;                                                      



And the output looks like:


PAGE    1                                
***INPUT STATEMENT:                      
   SELECT    COUNT(*)                    
   FROM      Q.OBJECT_DATA               
   ;                                     
       +----------------+                
       |                |                
       +----------------+                
     1_|           4042 |                
       +----------------+                
SUCCESSFUL RETRIEVAL OF          1 ROW(S)

REORG and contention – Forcibly terminating threads?

One of the challenges in performing online REORGs (i.e. SHRLEVEL CHANGE) is successfully completing the switch phase of the process. This problem seems to compound in direct relation to the number of distributed threads that connect to the database objects. I often see a pattern wherein a distributed thread acquires a lock or claim on an object and then does not perform a commit to release the lock or claim. This results in the REORG failing with a page out to the DBA.

I initially coded the REROG jobs with 

REORG TABLESPACE   LIST CLUSTTS                            
      LOG           NO                        
      KEEPDICTIONARY                          
      ROWFORMAT BRF                           
      SORTDATA                                
      SORTKEYS                                
      NOSYSREC                                
      COPYDDN      (LPCOPY)                   
      RECOVERYDDN  (RBCOPY)                   
      SHRLEVEL      CHANGE                    
      DRAIN_WAIT    10 RETRY 3  RETRY_DELAY 10
      DEADLINE      NONE                      
      MAXRO         60                        
      DRAIN         WRITERS   <---
      LONGLOG       CONTINUE                  
      DELAY         0                         
      TIMEOUT       TERM                      
      FASTSWITCH    YES                       
      UNLOAD        CONTINUE                  
      STATISTICS    TABLE INDEX ALL           
      UPDATE ALL

Assuming that this would be the least invasive approach. This was based on the below information from the Utility Guide:

WRITERS
Specifies that Db2 drains only the writers during the 
log phase after the MAXRO threshold is reached and 
then issues DRAIN ALL on entering the switch phase.

I changed the options to include DRAIN ALL FORCE ALL:

REORG TABLESPACE   LIST CLUSTTS                            
      LOG           NO                        
      KEEPDICTIONARY                          
      ROWFORMAT BRF                           
      SORTDATA                                
      SORTKEYS                                
      NOSYSREC                                
      COPYDDN      (LPCOPY)                   
      RECOVERYDDN  (RBCOPY)                   
      SHRLEVEL      CHANGE                    
      DRAIN_WAIT    10 RETRY 3  RETRY_DELAY 10
      DEADLINE      NONE                      
      MAXRO         60 
      DRAIN         ALL    <---                   
      FORCE         ALL    <---           
      LONGLOG       CONTINUE                  
      DELAY         0                         
      TIMEOUT       TERM                      
      FASTSWITCH    YES                       
      UNLOAD        CONTINUE                  
      STATISTICS    TABLE INDEX ALL           
      UPDATE ALL

The results were as follow:

If the DDF thread had only performed a select without a commit, it was cancelled with the below message in the DB2 master address space:

DSNL027I  -PR01 SERVER DISTRIBUTED AGENT WITH  828                   
           LUWID=JF0C80D3.DC4E.D8C6D35F5B78=129996                   
                                                                     
THREAD-INFO=ABC123Y:67.19.138.241:ABC123y:db2jcc_application:DYNAMIC:
5719:*:<::67.19.138.241.55378.D8C6D35F5B78>                          
           RECEIVED ABEND=04E                                        
           FOR REASON=00E50013

If the DDF thread had performed an update without a commit, it was not cancelled. Instead, the REORG fails with the below message in the job output:

DSNURDRN - RESOURCE NOT AVAILABLE, REASON=X'00C200EA', ON 
DBNAME.TSNAME PROHIBITS PROCESSING

I would have preferred that the DRAIN ALL FORCE ALL options would have cancelled the thread in both cases.