IBM Support

Slow IGC search for assets type Database Column

Troubleshooting


Problem

IGC Rest API /search for Database Columns is slow.
Sample requests:
  • POST /ibm/iis/igc-rest/v1/search
    {
  "begin":11,"pageSize":200
   ,"properties":["name","short_description", "long_description"]
   ,"types":["database_column"]
}
  • POST /ibm/iis/igc-rest/v1/search
    {
  "pageSize":100
   ,"properties":["name","short_description", "long_description"]
   ,"types":["database_column"]
   ,"where": {"conditions":[{"property": "name","operator": "containsWords","value": "SAMPLE"}],"operator": "and"}
}
  • GET /ibm/iis/igc-rest/v1/search/?types=database_column&properties=name&properties=short_description&properties=long_description&begin=11&pageSize=200

Symptom

  • Long running SQL queries with inner join between metatables ASCLModel_DataField and ASCLModel_DataCollection in Repository database
Signature SQL queries for Repository database on IBM® Db2®
SELECT COUNT(A0.xmeta_repos_object_id_xmeta) FROM XMETA.ASCLModel_DataField A0 INNER JOIN XMETA.ASCLModel_DataCollection A1 ON A0.of_DataCollection_xmeta = A1.xmeta_repos_object_id_xmeta WHERE ( A0.subtype_xmeta IS NULL OR A0.subtype_xmeta NOT IN (?, ?) ) AND A1.of_DataSchema_xmeta IS NOT NULL
;
SELECT X.c0, X.c1, X.c2, X.c3, X.c4 FROM (SELECT A0.xmeta_repos_object_id_xmeta, A0.name_xmeta, A0.name_xmeta, A0.typeCode_xmeta, A0.subtype_xmeta, row_number() over(ORDER BY upper(A0.name_xmeta), A0.xmeta_repos_object_id_xmeta) FROM XMETA.ASCLModel_DataField A0 INNER JOIN XMETA.ASCLModel_DataCollection A1 ON A0.of_DataCollection_xmeta = A1.xmeta_repos_object_id_xmeta WHERE ( A0.subtype_xmeta IS NULL OR A0.subtype_xmeta NOT IN (?, ?) ) AND A1.of_DataSchema_xmeta IS NOT NULL) X(c0, c1, c2, c3, c4, rn) WHERE X.rn >= 12 AND X.rn <= 200 ORDER BY X.rn
;
SELECT A1.xmeta_repos_object_id_xmeta, A1.isView_xmeta, A1.subtype_xmeta, A1.name_xmeta, A0.xmeta_repos_object_id_xmeta FROM XMETA.ASCLModel_DataField A0 INNER JOIN XMETA.ASCLModel_DataCollection A1 ON A0.of_DataCollection_xmeta = A1.xmeta_repos_object_id_xmeta WHERE A0.xmeta_repos_object_id_xmeta IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
;
SELECT COUNT(A0.xmeta_repos_object_id_xmeta) FROM XMETA.ASCLModel_DataField A0 INNER JOIN XMETA.ASCLModel_DataCollection A1 ON A0.of_DataCollection_xmeta = A1.xmeta_repos_object_id_xmeta WHERE ( A0.subtype_xmeta IS NULL OR A0.subtype_xmeta NOT IN (?, ?) ) AND upper(A0.name_xmeta) LIKE '%SAMPLE%' ESCAPE '\' AND A1.of_DataSchema_xmeta IS NOT NULL
;
SELECT X.c0, X.c1, X.c2, X.c3, X.c4 FROM (SELECT A0.xmeta_repos_object_id_xmeta, A0.name_xmeta, A0.name_xmeta, A0.typeCode_xmeta, A0.subtype_xmeta, row_number() over(ORDER BY upper(A0.name_xmeta), A0.xmeta_repos_object_id_xmeta) FROM XMETA.ASCLModel_DataField A0 INNER JOIN XMETA.ASCLModel_DataCollection A1 ON A0.of_DataCollection_xmeta = A1.xmeta_repos_object_id_xmeta WHERE ( A0.subtype_xmeta IS NULL OR A0.subtype_xmeta NOT IN (?, ?) ) AND upper(A0.name_xmeta) LIKE '%SAMPLE%' ESCAPE '\' AND A1.of_DataSchema_xmeta IS NOT NULL) X(c0, c1, c2, c3, c4, rn) WHERE X.rn <= 100 ORDER BY X.rn
;
  • IGC thread waiting on Repository database to respond
Example of a key information for a problematic thread. The information available in Javacore (thread dump).
3XMTHREADINFO      "WebContainer : 9" J9VMThread:0x0000000005FD8D00, omrthread_t:0x00007FDB1009E1C8, java/lang/Thread:0x0000000604A50F50, state:R, prio=5
3XMJAVALTHREAD            (java/lang/Thread getId:0x8D8, isDaemon:true)
3XMTHREADINFO1            (native thread ID:0xC15, native priority:0x5, native policy:UNKNOWN, vmstate:R, vm thread flags:0x00000000)
3XMTHREADINFO2            (native stack address range from:0x00007FDACF726000, to:0x00007FDACF766000, size:0x40000)
3XMCPUTIME               CPU usage total: 9.230114820 secs, current category="Application"
3XMHEAPALLOC             Heap bytes allocated since last GC cycle=0 (0x0)
3XMTHREADINFO3           Java callstack:
4XESTACKTRACE                at java/net/SocketInputStream.socketRead0(Native Method)
4XESTACKTRACE                at java/net/SocketInputStream.socketRead(SocketInputStream.java:127(Compiled Code))
4XESTACKTRACE                at java/net/SocketInputStream.read(SocketInputStream.java:182(Compiled Code))
4XESTACKTRACE                at java/net/SocketInputStream.read(SocketInputStream.java:152(Compiled Code))
4XESTACKTRACE                at com/ibm/db2/jcc/t4/y.b(y.java:222(Compiled Code))
(...)
4XESTACKTRACE                at com/ibm/db2/jcc/am/hp.executeQuery(hp.java:718(Compiled Code))
( ... )
4XESTACKTRACE                at com/ibm/is/bg/services/SearchResource.search(SearchResource.java:581)
4XESTACKTRACE                at com/ibm/is/igc/rest/v1/services/APISearchService.getSearchResultsInternal(APISearchService.java:108)
4XESTACKTRACE                at com/ibm/is/igc/rest/v1/resources/APISearchResource.getSearchResults(APISearchResource.java:158)

Document Location

Worldwide

[{"Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSZJPZ","label":"IBM InfoSphere Information Server"},"ARM Category":[{"code":"a8m500000008g94AAA","label":"Governance Catalog - IGC-\u003EIGC REST API"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
25 April 2025

UID

ibm16421483

Page Feedback