How to increment a column using DB2 MERGE?

by | May 16, 2007

Say for an example, you want to increment a column whenever a user logs in using an application for audit purpose. Normally in a single unit of work, DB2 will guarantee the data and there will not be an issue of concurrency if you use an INSERT and do an UPDATE upon INSERT failure. Instead of having 2 SQL statement and a check on SQL error on INSERT, you can use a single MERGE statement to accomplish same.

Source file showing DB2 MERGE

$ cat merge.sqc 
#include <stdio.h>
#include <string.h>
#include <sqlca.h>

/*
db2 "create table fedsch.term (userid varchar(10) not null, cnt int not null)"
*/

#define NAME_COLUMN_SIZE 27 

int main()
{
  int i = 0;
  EXEC SQL INCLUDE SQLCA;

  EXEC SQL BEGIN DECLARE SECTION; 
     char sqlBuffer[1000];
     char userid[11];
     char name[NAME_COLUMN_SIZE]; 
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR GOTO error_section; 

  strcpy(sqlBuffer, "MERGE INTO FEDSCH.TERM B USING (VALUES(CAST(? AS VARCHAR(10))");
  strcat(sqlBuffer, " ,1)) A (USERID, CNT) ON B.USERID = A.USERID WHEN MATCHED ");
  strcat(sqlBuffer, " THEN UPDATE SET B.CNT = B.CNT + 1 WHEN NOT MATCHED THEN ");
  strcat(sqlBuffer, " INSERT (USERID,CNT) VALUES (A.USERID,A.CNT)");
  strcpy(userid, "DB2UDE");
  EXEC SQL CONNECT TO SAMPLE;
  EXEC SQL VALUES CURRENT TIMESTAMP INTO :name;
  EXEC SQL PREPARE dynstmt FROM :sqlBuffer;
  for (i = 0; i < 10000; ++i)
  {
     EXEC SQL EXECUTE dynstmt USING :userid;
  }
  EXEC SQL CONNECT RESET;

  printf ("Server time is %s\n", name);
  return 0;

  error_section:
  {
      printf ("Error occured SQLCODE = %d\n", sqlca.sqlcode);
      return -1;
  }
}

makefile to compile

$ cat makefile 
DB2PATH = $(HOME)/sqllib

ERASE=rm -f

all : \
        test

clean : 
        $(ERASE) *.o

cleanall : \
        clean
        $(ERASE) *.bnd

test :
        ./prepcmd sample merge

prepcmd command

$ cat prepcmd
PACKAGETOKEN=$(date +"%j%M%S")
db2 -v connect to $1
db2 -v "prep $2.sqc bindfile explain yes explsnap yes package using P$PACKAGETOKEN preprocessor 'gcc -I$HOME/sqllib/include -E -o $2.i'"
gcc $2.c -L$HOME/sqllib/lib -ldb2 -o $2prg
db2 -v connect reset

Note: Thanks to Serge Rielau for sending me the correct syntax for MERGE command.