The myit_sb_ServiceSearchCache table is a 'data denormalized' view of all the revisions (including import/export) done to services. As there are more revisions being created, the more data gets created/stored in this cache table.
BMC Recommends that Catalog Administrators regularly clean-up older Revisions while updating or exporting a service. You may encounter search and export/import issues if the Revisions are not cleaned-up.
As best practice for managing the cache, we recommend that Catalog Administrators perform the following steps:
1) Regular clean-up of service revisions that are not needed
You can find the steps here:
https://docs.bmc.com/docs/digitalworkplaceadvanced/2008/managing-service-versions-and-revisions-948704795.html?src=search#Managingserviceversionsandrevisions-Todeleteoneormorerevisions
Also more information for best practice clean-up on revisions can be found here:
https://docs.bmc.com/docs/digitalworkplaceadvanced/2008/copying-services-from-another-system-948705345.html
2) Regular refresh of the application cache
We recommend bi-monthly or monthly cache refresh (Service Catalog > Application caches, and then click Reset Service Cache)
The above are Administrative functions of the Catalog and part of the Catalog Administrator role.
If after performing the above cache management clean-up, you are still seeing issues with search on DWP for Catalog services or observing display errors behavior on catalog items (such as catalog items not being available or service items not displaying under it's category), then there are additional clean-up steps you can take to address the issue.
1) Perform a truncate on the DB for table myit_sb_servicesearchcache.
For example, login to the DB and run the following queries (this will require CRQ to BMC Helix Database Team)
I) select name,schemaid from arschema where name like '%myit_sb_servicesearchcache%';
II) truncate table T<sechemaid from above query>;
2) Delete all 'Approved' revisions from Revision History tab of DWPC to reduce number of existing revisions.
IMPORTANT: Customers should not delete published revisions from Revision History tab, if a published revision is deleted the services will disappear from DWP Catalog and DWP console.
3) Perform an FTS reindex on myit-sb:ServiceSearchCache form in Mid-Tier (versions 21.3+)
If above operations did not help and still the catalogs is not searchable
This could be due to the defect DRMY1-26510. This will be going to fix in the next HF release which is targeted for Feb 2nd week.
As a workaround, reset the service search cache of the services using POST call.