IJ07972: LOADING DATA FOR PURCHASE REQUISTIONS, THE BUSINESS RULES ARE OPENING AN EXCESSIVE NUMBER OF DATABASE CURSORS.

APAR status

• Closed as program error.

Error description

• PROBLEM:
  When loading data for Purchase Requistions, the verification
  for a valid company is opening an excessive number of Database
  Cursors.
  PERFORMANCE ISSUE: Yes
  STEPS TO REPRODUCE:
  Load high number (20000 records) of new PR records using MXPR
  Object Strucure.
  The flat file below uses MAXDEMO as example.
  Example of flat file:
  BILLTO;BILLTOATTN;BUYAHEAD;CHANGEBY;CHANGEDATE;CONTACT;CONTRACTR
  EFID;CONTRACTREFNUM;CONTRACTREFREV;CURRENCYCODE;CUSTOMERNUM;PRDE
  SCRIPTION;PRLONGDESC;EXCHANGEDATE;EXCHANGERATE;EXCHANGERATE2;EXT
  ERNALREFID;FOB;FREIGHTTERMS;FREIGHTTERMS_LD;HISTORYFLAG;INCLUSIV
  E1;INCLUSIVE2;INCLUSIVE3;INCLUSIVE4;INCLUSIVE5;PR_INSPREQ;INTERN
  AL;ISSUEDATE;NOVENDOR;MEMO;ORGID;OWNERSYSID;PAYMENTTERMS;PAYONRE
  CEIPT;PCARDEXPDATE;PCARDNUM;PCARDTYPE;PCARDVERIFICATION;PR1;PR10
  ;PR2;PR3;PR4;PR5;PR6;PR7;PR8;PR9;PRID;PRIORITY;PRLA1;PRLA2;PRLA3
  ;PRLA4;PRLA5;PRNUM;REQUESTEDBY;REQUIREDDATE;SENDERSYSID;SHIPTO;S
  HIPTOATTN;SHIPVIA;SITEID;SOURCESYSID;STATUS;STATUSDATE;STATUSIFA
  CE;PR_STORELOC;STORELOCSITEID;SUPERVISOR;TOTALCOST;TOTALTAX1;TOT
  ALTAX2;TOTALTAX3;TOTALTAX4;TOTALTAX5;VENDOR;ASSETNUM;CATALOGCODE
  ;CATEGORY;CHARGESTORE;CLASSSTRUCTUREID;COMMODITY;COMMODITYGROUP;
  CONDITIONCODE;CONSIGNMENT;CONTRACTID;CONTRACTLINEID;CONTRACTLINE
  NUM;CONTRACTNUM;PRL_CONTRACTREFID;PRL_CONTRACTREFNUM;PRL_CONTRAC
  TREFREV;CONTRACTREV;CONTREFLINEID;CONVERSION;CONVERTTOCONTRACT;C
  ONVERTTORFQ;PRLDESCRIPTION;PRLINELONGDESC;ENTERBY;ENTERDATE;ENTE
  REDASTASK;FCPROJECTID;FCTASKID;PRC_FINCNTRLID;GLCREDITACCT;GLDEB
  ITACCT;INSPECTIONREQUIRED;ISDISTRIBUTED;ISSUE;ITEMNUM;ITEMSETID;
  LINECOST;LINECOST1;LINECOST2;LINETYPE;LOADEDCOST;LOADEDCOST1;LOC
  ATION;MANUFACTURER;MKTPLCITEM;MODELNUM;MRLINENUM;MRNUM;ORDERQTY;
  ORDERUNIT;PRL_ORGID;PRLPCARDEXPDATE;PRLPRPCARDNUM;PRLPRPCARDTYPE
  ;PRL_CARDVERIF;POLINEID;POLINENUM;PONUM;POREVISIONNUM;POSITEID;P
  RLALN1;PRLALN2;PRLALN3;PRLALN4;PRLALN5;PRLINEID;PRLINENUM;PRORAT
  ESERVICE;RECEIPTREQD;REFWO;REMARK;REMARK_LD;REQDELIVERYDATE;PRLR
  EQUESTEDBY;RESTYPE;RFQLINEID;RFQLINENUM;RFQNUM;RL1;RL10;RL2;RL3;
  RL4;RL5;RL6;RL7;RL8;RL9;RLIN1;RLIN2;RLIN3;RLIN4;RLIN5;RLIN6;RLIN
  7;RLIN8;RLIN9;SCHEDULEID;STORELOC;TAX1;TAX1CODE;TAX2;TAX2CODE;TA
  X3;TAX3CODE;TAX4;TAX4CODE;TAX5;TAX5CODE;TAXEXEMPT;UNITCOST;VENDE
  LIVERYDATE;VENDORPACKCODE;VENDORPACKQUANTITY;VENDORWAREHOUSE;COS
  TLINENUM;COSTFINCNTRLID;PRC_FCTASKID;FINCNTRLID;COSTGLCREDITACCT
  ;COSTGLDEBITACCT;COSTLINECOST;COSTLOADEDCOST;PRC_ORGID;PERCENTAG
  E;PRCOSTID;PRCOSTLINEID;QUANTITY;CANEDIT;PRT_DESCRIPTION;PRT_ORG
  ID;PLUSITEMNUM;PLUSITEMSETID;PLUSREV;PLUSTYPE;PRTERMID;SENDTOVEN
  DOR;SEQNUM;TERMID
  BEDFORDMAIN;KATHYB;0;MAXADMIN;2018-07-13T11:38:24-03:00;;;;;USD;
  ;MPC TEST
  PR;;2018-07-13T00:00:00-03:00;1.0;;;;;;0;1;1;1;1;1;0;0;2018-07-1
  3T11:37:42-03:00;0;;EAGLENA;;;0;;;;;;0;;;;;;;;;;0;;;;;;MPC1;MAXA
  DMIN;;MX;BEDFORDMAIN;;;BEDFORD;;APPR;2018-07-13T11:38:22-03:00;0
  ;;;;0.0;0.0;0.0;0.0;0.0;0.0;DODGE;;;;0;;;;;0;;;;;;;;;;1.0;0;0;MA
  T
  1;;MAXADMIN;2018-07-13T11:37:58-03:00;0;;;;6800-930-610;6000-200
  -000;0;0;1;;;0.0;0.0;0.0;MATERIAL;0.0;0.0;;;0;;;;1.0;;EAGLENA;;;
  ;;;;;;;;;;;;42985;1;0;1;;;;;MAXADMIN;AUTOMATIC;;;;;;;;;;;;;;;;;;
  ;;;;;;;0.0;;0.0;;0.0;;0.0;;0.0;;0;0.0;;;;;1;;;;6800-930-610;6000
  -200-000;0.0;0.0;EAGLENA;100.0;;;1.0;;;;;;;;;;;
  BEDFORDMAIN;KATHYB;0;MAXADMIN;2018-07-13T11:38:24-03:00;;;;;USD;
  ;MPC TEST
  PR;;2018-07-13T00:00:00-03:00;1.0;;;;;;0;1;1;1;1;1;0;0;2018-07-1
  3T11:37:42-03:00;0;;EAGLENA;;;0;;;;;;0;;;;;;;;;;0;;;;;;MPC2;MAXA
  DMIN;;MX;BEDFORDMAIN;;;BEDFORD;;APPR;2018-07-13T11:38:22-03:00;0
  ;;;;0.0;0.0;0.0;0.0;0.0;0.0;DODGE;;;;0;;;;;0;;;;;;;;;;1.0;0;0;MA
  T
  1;;MAXADMIN;2018-07-13T11:37:58-03:00;0;;;;6800-930-610;6000-200
  -000;0;0;1;;;0.0;0.0;0.0;MATERIAL;0.0;0.0;;;0;;;;1.0;;EAGLENA;;;
  ;;;;;;;;;;;;42985;1;0;1;;;;;MAXADMIN;AUTOMATIC;;;;;;;;;;;;;;;;;;
  ;;;;;;;0.0;;0.0;;0.0;;0.0;;0.0;;0;0.0;;;;;1;;;;6800-930-610;6000
  -200-000;0.0;0.0;EAGLENA;100.0;;;1.0;;;;;;;;;;;
  CURRENT ERRONEOUS RESULT:
  Following sql has opened 28,571 cursors
  select * from pluscompaudit where company = :"SYS_B_0" and
  orgid = :"SYS_B_1" and nextauditdate >= TO_DATE (:"SYS_B_2" ,
  :"SYS_B_3")
  2nd sql is and it has opened 15423 cursors
  select * from plusitemterm where itemnum = :"SYS_B_0" and
  itemsetid = :"SYS_B_1"
  EXPECTED RESULT:
  MAximo should not leave so many cursors opened during PR data
  load process.
  ADDITIONAL INFO:
  This issue is related with Addon IBM Maximo for Nuclear Power.
  ENVIRONMENT (SYSTEM INFO):
  IBM Maximo for Nuclear Power 7.6.1.0-20180614-0819 Build
  20171110-1308 DB Build V7610-45 HFDB Build HF7610-34
  Tivoli's process automation engine 7.6.0.8-IFIX20171025-1353
  Build 20170512-0100 DB Build V7608-63 HFDB Build HF7608-07
  SECURITY IMPACT (Y/N): N
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All IBM Maximo for Nuclear Power users.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Loading data for purchase requisitions, the business rules   *
  * are opening an excessive number of database cursors.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• A fix was created to prevent the excessive number of database
  open cursors.
  

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

  MAXIMO FOR NUCL

• Fixed component ID

  5724R5300

Applicable component levels