APMSQL Cookbook

In an Agile DevOps organization, many people benefit from APM, but historically not everybody has been able to benefit from APM data; data extraction options were limited. By providing a native SQL interface for CA APM data extraction, analysts have a native and familiar way to export and present reports. Experienced analysts want to know the schema, and we explain it in detail in this guide. For best results, have access to the CA APM user interface as you look for metrics worthy of a report, as described in the section of the CA APMSQL schema. Even though CA APM has its own reporting interfaces, most analysts want to fetch and see the reports in a more familiar presentation format, such as CA Business Intelligence, JasperReports, Tableau, Microstrategy, Crystal Reports, R, Grafana, Periscope Data, or Microsoft Excel.  Whether you use JDBC, ODBC or CSV, CA APMSQL is the easiest way to get CA APM data into your reports.
apmdevops106
In an Agile DevOps organization, many people benefit from APM, but historically not everybody has been able to benefit from APM data; data extraction options were limited. By providing a native SQL interface for CA APM data extraction, analysts have a native and familiar way to export and present reports. Experienced analysts want to know the schema, and we explain it in detail in this guide. For best results, have access to the CA APM user interface as you look for metrics worthy of a report, as described in the section of the CA APMSQL schema. Even though CA APM has its own reporting interfaces, most analysts want to fetch and see the reports in a more familiar presentation format, such as CA Business Intelligence, JasperReports, Tableau, Microstrategy, Crystal Reports, R, Grafana, Periscope Data, or Microsoft Excel.  Whether you use JDBC, ODBC or CSV, CA APMSQL is the easiest way to get CA APM data into your reports.
The CA APMSQL Cookbook expects familiarity with SQL. Many examples are weekly reports on things such as Business Transaction. These reports are a great place to start. For a full list of CA APMSQL use-cases, see APMSQL Recipes.
APMSQL Architecture and Uses
Metric data for CA APM is stored in a highly optimized, proprietary file system named SmartStor. The APMSQL uses an open source JBoss project that is named TEIID to expose this data for query using standard SQL syntax. TEIID is a data virtualization system; multiple, heterogeneous stores of information can be accessed from a uniform API. With CA APMSQL, an analyst can connect to SmartStor as if it were a relational database. Connections are made using a JDBC driver, or an appropriate ODBC driver for connections to tools like Excel. Configuration of both JDBC and ODBC connections are covered in this Cookbook guide.
Customers using the Command Line Workstation (CLW) to extract CSV files for reporting are encouraged use CA APMSQL. CA APMSQL clients can produce CSV by printing commas between the fields of each record of a result set that includes all columns of the metric data table.  For more information, see the recipe for What If I Still Want CSV. Any downstream tooling that was put in place to consume Command Line Workstation CSV works without the need for any further configuration changes.
Earlier versions of CA APM included an Introscope JDBC driver. Do not confuse JDBC with CA APMSQL. The Introscope JDBC driver has been removed. Customers that were using the Introscope JDBC driver can switch to the APMSQL JDBC driver, make minimal changes to their SQL, and use a faster and more flexible data extraction experience.
Recommendations
When writing queries, begin with narrow time ranges, and slowly expand to wider ones. This querying method gives you a better sense of how long it takes to run reports with longer time ranges. Use the WEEK, HOUR, and DAY functions to aggregate data over longer time ranges, as suggested in TEIID documentation. For more information, see the recipe for How to Summarize Performance of My Transactions for the Last Number of Minutes.
The CA APMSQL is memory and disk intense. If you want to run weekly or monthly queries, plan to allocate 8 GB, or more memory to the heap size of your Enterprise Manager. Run the CA APMSQL on a separate server, and give it at least 4GB of memory too. CA Technologies recommend using a solid-state drive for your SmartStor data directory to get a cost effective solution and a faster reporting experience.
Install and Configure APMSQL Server
Installing Enterprise Manager installs the APMSQL by default at the \\<
EM_HOME
>\
APMSQLServer folder.
The configuration file A
PMSQLServer.Properties
is located in the \\config folder. Set the JDBC bind address in the configuration file to the IP address of your CA APMSQL server. Start the server with a script in the
APMSQLServer/bin
directory. For more information, see Install and Configure APMSQL Servers.
Learn how to configure CA APMSQL, and then view the data using Squirrel:

More information: