PH24515: BIG SQL START UP FAILS OR STATEMENTS FAIL WITH SQLCODE -1225 ON LINUX REDHAT 7.8 OR LATER

APAR status

• Closed as fixed if next.

Error description

• On Linux RedHat version 7.8 or later, Big SQL may fail during
  start up, or
  statements may fail with sqlcode -1225 indicating a lack of
  resources.  For
  example:
  
  drop table smoke_hadoop_1554305901 PURGE
  DB21034E The command was processed as an SQL statement because
  it was not a
  valid Command Line Processor command. During SQL processing it
  returned:
  SQL1225N The request failed because an operating system process,
  thread, or
  swap space limit was reached. SQLSTATE=57049
  
  In the db2diag.log you will see the following message:
  
  2020-04-07-14.20.44.283722+000 E189555E507           LEVEL:
  Severe
  PID     : 12714                TID : 140443249534720 PROC :
  db2sysc 43
  INSTANCE: bigsql               NODE : 043
  HOSTNAME: BIGSQL-WORKER-HOST
  FUNCTION: DB2 UDB, oper system services, sqloEDUEntry, probe:10
  CALLED  : DB2 UDB, oper system services, sqloGetShrEDUWaitElem
  RETCODE : ZRC=0x850F0081=-2062614399=SQLO_SSEM_EXCEED_MAX
            "Requesting too many semaphores"
            DIA8336C Requested too many semaphores.
  
  The problem occurs because of a change in behaviour in the
  latest Linux levels.
  On start up, Db2 (and therefore Big SQL) automatically adjusts
  the number of semaphore
  arrays (SEMMNI) based on the amount of memory available.  The
  target value is calculated
  as 256 * (memory in GB).  On servers with greater than 128 GB of
  memory, this results
  in a value greater than the internal SEMMNI limit (32768).  On
  older versions of
  Linux, this resulted in the number of arrays being set to the
  SEMMNI limit.
  As of Linux kernel version 3.10.0-1127.el7.x86_64 the limit is
  now being strictly enforced,
  resulting in the setting adjustment failing silently and the
  number of semaphore arrays
  remaining at the default value of 128, which is too low.
  
  
  This can be confirmed by checking the start up message recorded
  in the db2diag.log.
  
  DATA #2 : System Info, 664 bytes
  System: Linux hdp-worker-29.datalake-hdp.zc2.ibm.com 10 3 x86_64
  CPU: total:28 online:28 Cores per socket:1 Threading degree per
  core:1 SIMD:Y CP
  U Features:SSE2,SSSE3,SSE4,AVX2
  CPU binding: num cores:26.000 processor list: 0 1 2 3 4 5 6 7 8
  9 10 11 12 13 14
   15 16 17 18 19 20 21 22 23 24 25
  Physical Memory(MB): total:354549 free:325995
  Virtual  Memory(MB): total:442549 free:413995
  Swap     Memory(MB): total:88000 free:88000
  Kernel   Params: msgMaxMessageSize:65536 msgMsgMap:65536
  msgMaxQueueIDs:32000
                   msgNumberOfHeaders:65536 msgMaxQueueSize:65536
                   msgMaxSegmentSize:16 shmMax:9223372036854775807
  shmMin:1
                   shmIDs:4096 shmSegments:4096 semMap:256000
  semIDs:128
                   semNum:256000 semUndo:256000 semNumPerID:250
  semOps:32
                   semUndoSize:20 semMaxVal:32767
  semAdjustOnExit:32767
  
  The important part is: "semIDs:128".  This is the default value.
  
  This can also be confirmed by checking the value in
  /proc/sys/kernel/sem as root.
  
      [root@bigsql-worker-host ~]#? cat /proc/sys/kernel/sem
      250 256000 32 128
  

Local fix

• The solution is to manually set the semaphore values with the
  following command (as root):
  
  sysctl -w kernel.sem="250 256000 32 32768"
  

Problem summary

• Please see problem description.
  

Problem conclusion

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

Applicable component levels