The APMSQL documentation is in the process of being enhanced. This knowledge document provides additional things to try for certain issues.

      I am not getting all the expected results in APMSQL What should I do?

  Customer environment was at APM 10.5.2 using Squirrel. But this could be for any APMSQL connection method and release.

1)  Seeing only virtual not physical agents in the results.
      - Check Account permissions. Try with an APM administrator account.
      - Increase heap and timeout settings. Set the heap size to 4GB, and the timeout to 3000000 
      - Make sure that the temp area has enough space
     - See https://communities.ca.com/thread/241777432-ca-apmsql-problem-unable-to-access-all-data-agents
     -  Set public.numerical_metric_data in your queries. This is not presently in the  documentation.

2)  Getting Error: TEIID31138 Cannot add batch to invalidated cache group "90" or aomething identical.
- Check prior logs to see if there was an error persisting a batch. 
- APMSQL writes to temp directory, For large queries, it can get filled up. Then a TEIID31138 error will occur
- In APM 10.7 SP 1, there is planned a hotfix that will allow you change the temp directory
location.

For now, increase the temp directory space as a workaround.. If an TEIID31138 errors occurs, when the disk space is low, check the disk space in temp directory
    If linux, run the command : df -h .
    If windows, go to %USERPROFILE%\AppData\Local\Temp and see the disk which holds this has enough space available for buffer to be written.
 
   Note: Inside the temp directory, a directory name buffer is created by APMSQL to store the temporary data, which is valid for one run, so you can delete this folder once you shutdown APMSQL.
3). Queries are bit slow, especially for distinct keyword, so try putting the EM on solid-state drive.
4)  If a TEIID15005  occurs, this is when unauthorized user tries to access APMSQL. Confirm if the correct username/password or token is provided when connecting to APMSQL.
       See if you are getting getting this error while connecting to APMSQL or while making a query request
 

SET TO DEBUG or TRACE in APMSqlServer.properties for helpful logging.
og4j.rootLogger=ERROR,console,logfile
log4j.logger.org.teiid=WARN,teiidlog
log4j.logger.com.ca.apm.server=DEBUG,console,logfile