1 Reply Latest reply on Apr 24, 2019 3:36 AM by Marco Colombo

    Generic Database extractor for SQL server DB

    Arthur Nigro
      Share This:

      I am looking for documentation on sql examples for using the Database extractor for a SQL server database to extract business driver data.


      The only documentation from BMC is Generic - Database extractor - BMC TrueSight Capacity Optimization 10.7 - BMC Documentation

      SELECT myTsCol AS TS, 3600 AS DURATION, 'sys1' AS ds_sysnm,

             'sys1' AS sysnm, 'CPU_UTIL' AS objnm, 'GLOBAL' AS subobjnm, myValue AS VALUE,

             myMinValue AS MINVALUE, myMaxVAlue AS maxvalue

        FROM TABLE


      How do I declare my variables? I am looking to run the ETL daily to extract a count of a specific table by day.


        • 1. Re: Generic Database extractor for SQL server DB
          Marco Colombo



          I suggest to first create an 'External DB connection' to store DB connection details.


          After that, you can instantiate a new 'Generic - Database extractor (Java)' and configure it.

          In order to import Business Driver metrics, please select WKLDAT as dataset.

          Then query should be formatted as per WKDAT dataset specification.

          As example, let's import a business driver metric TOTAL_EVENTS to report all events happened for application 'myapplication' in one day.


          The output of the query should be like:



          2019-04-26 00:00:00;86400;myapplication;myapplication;TOTAL_EVENTS;12345


          query should be like:


          SELECT '2019-04-26 00:00:00' as TS, 86400 as DURATION, 'myapplication' as DS_WKLDNM, 'myapplication' as WKLDNM, 'TOTAL_EVENTS' as OBJNM, '12345' as VALUE FROM SOURCE_TABLE WHERE TS>:PARL


          Please note:

          • DS_WKLDNM is the field where to store business driver lookup information. WKLDNM the business driver name. Can be the same if there is no need to have a more advanced lookup.
          • Filtering condition 'TS>:PARL' is optional and is required when ETL should run on daily base and source data provide a timestamp. So it is possible to just import newly added samples. If name of TS column is different in source table, it is possible to change it (editing ETL configuration in advanced mode, changing lastcounter column parameter). Latest processed samples will be recorded as lastcounter (see lastcoounter section in ETL page)
          • Please select required metrics among all WKLD datasets