IC67641: IFX_REPLCHECK INDEX CAUSES UPDATES TO GO IN AN INFINITE LOOP ANDHANG

APAR status

• Closed as program error.

Error description

• Following is the repro:
  
  drop table PhoneTemplate;
  create table PhoneTemplate (pkid integer, versionstamp char(5),
  cnt integer
  ) with replcheck with crcols;
  insert into PhoneTemplate values (1, 'aa', 1);
  insert into PhoneTemplate values (2, 'aa', 1);
  insert into PhoneTemplate values (3, 'aa', 1);
  insert into PhoneTemplate values (4, 'aa', 1);
  insert into PhoneTemplate values (5, 'aa', 1);
  insert into PhoneTemplate values (6, 'aa', 1);
  insert into PhoneTemplate values (7, 'aa', 1);
  insert into PhoneTemplate values (8, 'aa', 1);
  insert into PhoneTemplate values (9, 'aa', 1);
  insert into PhoneTemplate values (10, 'aa', 1);
  insert into PhoneTemplate values (11, 'aa', 1);
  insert into PhoneTemplate values (12, 'aa', 1);
  create unique index replidx on PhoneTemplate(pkid,
  ifx_replcheck);
  create unique index pkidx on PhoneTemplate(pkid);
  
  drop table PhoneButton;
  create table PhoneButton (pkid integer , fkPhoneTemplate
  integer, name char(5), cnt integer
  -- , FOREIGN KEY(fkPhoneTemplate) REFERENCES
  PhoneTemplate(pkid));
  );
  insert into PhoneButton values (1, 10, 'aa', 1);
  insert into PhoneButton values (2, 10, 'aa', 1);
  insert into PhoneButton values (3, 10, 'aa', 1);
  insert into PhoneButton values (4, 10, 'aa', 1);
  insert into PhoneButton values (5, 10, 'aa', 1);
  insert into PhoneButton values (6, 10, 'aa', 1);
  insert into PhoneButton values (7, 10, 'aa', 1);
  insert into PhoneButton values (8, 10, 'aa', 1);
  insert into PhoneButton values (9, 10, 'aa', 1);
  insert into PhoneButton values (10, 10, 'aa', 1);
  insert into PhoneButton values (11, 10, 'aa', 1);
  insert into PhoneButton values (12, 10, 'aa', 1);
  
  
  -- procedure
  drop function upd_join(integer);
  create function upd_join(use_replcheck integer) returning
  integer
  define pt_id, new_pkid, total integer;
  
  let total = 0;
  let new_pkid = 10;
  
  if (use_replcheck = 1)
  then
  --  using the replcheck index
      FOREACH cursor1 FOR
        SELECT --+ INDEX(PhoneTemplate  replidx )
          T.pkid INTO pt_id FROM PhoneTemplate T
        JOIN PhoneButton B ON B.fkPhoneTemplate = T.pkid
        WHERE B.pkid = new_pkid
          UPDATE  --+ INDEX(PhoneTemplate replidx)
              PhoneTemplate SET versionstamp='zz' WHERE pkid =
  pt_id;
          let total = total + DBINFO('sqlca.sqlerrd2');
      END FOREACH;
  else
  --  using the pkidx index
      FOREACH cursor1 FOR
        SELECT --+ INDEX(PhoneTemplate  pkidx)
          T.pkid INTO pt_id FROM PhoneTemplate T
        JOIN PhoneButton B ON B.fkPhoneTemplate = T.pkid
        WHERE B.pkid = new_pkid
          UPDATE  --+ INDEX(PhoneTemplate pkidx)
              PhoneTemplate SET versionstamp='zz' WHERE pkid =
  pt_id;
          let total = total + DBINFO('sqlca.sqlerrd2');
      END FOREACH;
  end if;
  
  return total;
  end function;
  
  -- update using normal index, works fine
  execute function upd_join(0);
  -- update using replcheck index, hangs ...
  execute function upd_join(1);
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 11.50.xC4 through 11.50.xC6                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When a table is created with replcheck option, and an index  *
  * is created including the replcheck column, and we have a     *
  * nested statement inside a stored procedure where the outer   *
  * statement is an foreach select that might use the index with *
  * the replcheck column, and the inner statement is an update   *
  * statement on the table, then the execution of the stored     *
  * procedure could potentially hang.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC7.                                        *
  ****************************************************************
  

Problem conclusion

• The problem is fixed in 11.50.xC7.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP