IT06799: ANR0162W WITH SQL0545N [...] ROW DOES NOT SATISFY THE CHECK "TSMDB1.ACTIVITY_SUMMARY.ADAS_21"

7.1.3.000-TIV-TSMSRV-WIN
7.1.3.000-TIV-TSMSRV-SolarisSPARC
7.1.3.000-TIV-TSMSRV-Linuxx86_64
7.1.3.000-TIV-TSMSRV-Linuxs390x
7.1.3.000-TIV-TSMSRV-Linuxppc64
7.1.3.000-TIV-TSMSRV-HP-UX
7.1.3.000-TIV-TSMSRV-AIX

APAR status

• Closed as program error.

Error description

• The Tivoli Storage Manager Server may display the following
  message in the activity log after a client backup completes and
  an attempt to insert a record into the summary table is being
  made :
  
  ANR0162W Supplemental database diagnostic information:
  -1:23513:-545 ([IBM][CLI Driver][DB2/AIX64] SQL0545N  The
  requested operation is not allowed because a row does not
  satisfy the check constraint
  "TSMDB1.ACTIVITY_SUMMARY.ADAS_21". SQLSTATE=23513)
  
  This does not affect the client backup at all, purely the
  insertion of the summary record for that specific client
  session. The message can be seen from any version and operating
  system of the client.
  
  The above constraint relates to the "comm wait time" of the
  client session which is calculated on the server and needs to be
  >= 0. On this occasion, we were calculating this as a negative
  value because the system clock was being changed automatically
  to an earlier time on the Tivoli Storage Manager Server during
  the client session connection by the xntpd daemon (Network Time
  Protocol Daemon).
  Note that this only applies to actual time changes, not timezone
  or DST changes. Timezone and DST changes do not affect the UTC
  time (Coordinated Universal Time), but changes to the system
  time do affect the UTC.
  
  Although the Tivoli Storage Manager Server is not causing the
  error, this APAR will address the calculation of a negative
  value and not attempt to insert this, thus will avoid the error.
  Instead, a value of zero will be entered when encountering this.
  
  Tivoli Storage Manager Versions Affected:
  All supported versions and all supported operating systems
  
  Customer/L2 Diagnostics (If Applicable)
  
  Check whether there is any software running on the Tivoli
  Storage Manager Server that can automatically make time
  adjustments.
  
  A server trace using DBEXEC and ACCNT classes will show :
  
  21:39:17.549
  [38910][smutil.c][17715][SmAddAffinityStatistics]:(17435)
  commWaitTime to be added: -19552, total: -19552
  ..
  [38926][imgroup.c][2238][RdbPrepareAndExecuteStmt]:Preparing
  statement based on isPrepared True (prepLen 214 vs stmtLen 214),
  (STMT='SELECT
  NODEID,FSID,LEADERID,GROUPTYPE,MEMBERID,MEMBEROBJTYPE FROM
  "TSMDB1"."GROUP_LEADERS"  WHERE (NODEID=? AND FSID=? AND
  MEMBERID=?) ORDER BY LEADERID, GROUPTYPE FOR READ ONLY OPTIMIZE
  FOR 10 ROWS WITH UR --38926').
  21:39:19.241 [38816][output.c][7227][PutConsoleMsg]:ANR0162W
  Supplemental database diagnostic information:  -1:23513:-545
  ([IBM][CLI Driver][DB2/AIX64] SQL0545N  The requested operation
  is not allowed because a row does not satisfy the check
  constraint "TSMDB1.ACTIVITY_SUMMARY.ADAS_21".  SQLSTATE=23513
  ).~
  
  The output from "Q NODE <nodename> F=D" shows this as :
  
       Bytes Received Last Session: 234.29 M
            Bytes Sent Last Session: 151.01 M
           Duration of Last Session: 2,331.18
        Pct. Idle Wait Last Session: 47.17
       Pct. Comm. Wait Last Session: 184,239.29 <===
       Pct. Media Wait Last Session: 0.00
  
  During the trace, we see that the time was being changed. These
  lines are consecutive :
  
  21:37:33.951
  [38926][imgroup.c][2238][RdbPrepareAndExecuteStmt]:Preparing
  statement based on isPrepared True (prepLen 214 vs stmtLen 214),
  (STMT='SELECT
  NODEID,FSID,LEADERID,GROUPTYPE,MEMBERID,MEMBEROBJTYPE FROM
  "TSMDB1"."GROUP_LEADERS"  WHERE (NODEID=? AND FSID=? AND
  MEMBERID=?) ORDER BY LEADERID, GROUPTYPE FOR READ ONLY OPTIMIZE
  FOR 10 ROWS WITH UR --38926').
  21:37:06.388
  [38926][tbcli.c][4234][RdbPrepareAndExecuteStmt]:Executing
  statement 'SELECT
  NODEID,FSID,LEADERID,GROUPTYPE,MEMBERID,MEMBEROBJTYPE FROM
  "TSMDB1"."GROUP_LEADERS"  WHERE (NODEID=? AND FSID=? AND
  MEMBERID=?) ORDER BY LEADERID, GROUPTYPE FOR READ ONLY OPTIMIZE
  FOR 10 ROWS WITH UR --38926' submitted from imgroup.c(2238).
  
  So for this instance, a large time change was occurring to an
  earlier time, causing the calculation to become negative.
  
  Initial Impact: Low|Medium|High
  Low
  
  Additional Keywords:
  TSM summary xntpd
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Tivoli Storage Manager server users                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fixing level when available. This problem is currently *
  * projected to be fixed in                                     *
  * level 7.1.3. Note that this is subject to change at the      *
  * discretion of IBM.                                           *
  ****************************************************************
  

Problem conclusion

• This problem was fixed.
  Affected platforms:  AIX, HP-UX, Linux, Solaris, and Windows.
  

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

  TSM SERVER

• Fixed component ID

  5698ISMSV

Applicable component levels

• R71A PSY

     UP

• R71H PSY

     UP

• R71L PSY

     UP

• R71S PSY

     UP

• R71W PSY

     UP