Highlighted
ADR Super Contributor.
Super Contributor.
660 views

Transaction info Database

Hi ,

We have a BSM 9.26 IP2 installed. We have another instance in SaaS and HP use a FTP to drop a file with info that we use to fill another application and we’d like to know what is the query HP use in order to extract the info from the database and fill the *.csv file you drop in the FTP. In a previous version, before to have it in SaaS, we used :

SELECT EVENT_METER.EM_DATE_TIME ,TRANSACTIONS.TRANSACTION_NAME ,TRANSACTIONS.RANK_1_FROM ,TRANSACTIONS.RANK_1_TO ,EVENT_METER.EM_STATUS_ID ,EM_RESULT_VALUE.EVENT_METER ,EM_RESULT_VALUE.DBDATE FROM EVENT_METER ,TRANSACTIONS WHERE EVENT_METER_1.EM_TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID AND EVENT_METER_1.EM_LOCATION_ID = LOCATIONS.LOCATION_ID

But as I checked in the current database, this query is not valid anymore. Attached you can find the file with the info.

Anyone know where that info is stored now ? I need to update the query in order to be able to extract the info.

Thanks in advance

Tags (4)
0 Likes
5 Replies
Micro Focus Expert
Micro Focus Expert

Re: Transaction info Database

Hi Adr,

the Query is for pre-BSM 9 times (BAC 8 and before) and yes, it will no longer work in BSM 9,
as the profile database table structures have changed.

if HP provided the query to you, I would expect that you also can get an updated version from them?

You can check KCS document
  KM1165162 - BSM 9.x Database Schema changes
to review some of the changes.

In any case, here is a list of obvious changes:
EVENT_METER is now BPM_TRANS, most of the field names didn't change, some have been added, some other might have been deleted
[SAMPLETIME]
,[TUID]
,[DBDATE]
,[CUSTOMER_ID]
,[INTERNAL_TRANSACTION_ID]
,[INTERNAL_LOCATION_ID]
,[SCRIPT_ID]
,[BPM_AGENT_ID]
,[INTERNAL_SUBNET_ID]
,[SERVER_IP]
,[SERVER_NAME]
,[STATUS_ID]
,[EM_ID]
,[EM_RESULT_VALUE]
,[EM_PAGE_CBD_COUNT]
,[EM_DOWNLOAD_DATA_SIZE]
,[EM_W_CONNECTION_TIME]
,[EM_W_DNS_TIME]
,[EM_W_SSL_TIME]
,[EM_W_NETWORK_TIME]
,[EM_W_NETWORK_FB_TIME]
,[EM_W_SERVER_FB_TIME]
,[EM_W_DOWNLOAD_TIME]
,[EM_W_RETRY_TIME]
,[EM_S_CONNECTION_TIME]
,[EM_S_DNS_TIME]
,[EM_S_SSL_TIME]
,[EM_S_NETWORK_TIME]
,[EM_S_NETWORK_FB_TIME]
,[EM_S_SERVER_FB_TIME]
,[EM_S_DOWNLOAD_TIME]
,[EM_S_RETRY_TIME]
,[BASELINE_RESP_TIME_MEAN]
,[BASELINE_RESP_TIME_STD]
,[BASELINE_RESP_TIME_LOC_MEAN]
,[BASELINE_RESP_TIME_LOC_STD]
,[BASELINE_DOWNLOAD_TIME_MEAN]
,[BASELINE_DOWNLOAD_TIME_STD]
,[BASELINE_SERVER_TIME_MEAN]
,[BASELINE_SERVER_TIME_STD]
,[BASELINE_FIRSTBUF_TIME_MEAN]
,[BASELINE_FIRSTBUF_TIME_STD]
,[BASELINE_NETWORK_TIME_MEAN]
,[BASELINE_NETWORK_TIME_STD]
,[TOT_OK_HITS]
,[TOT_MINOR_HITS]
,[TOT_CRITICAL_HITS]
,[DOWNTIME_STATE]
,[ERROR_COUNT]
,[AVAILABILITY_STATUS]

Hoever, with BSM 9 the concept and the way of storing TRANSACTION and LOCATION information has changed dramatically. The tables don't exist anymore, but have been replaced by multiple others, and the way accessing the data has changed dramatically, but lucky enoguh we do have the *_DIM tables.

I checked the sample data you attached:
you can get to the transaction name via
<profile_db>.BPM_TRANS.INTERNAL_TRANSACTION_ID = <profile_db>.TRANSACTION_DIM.INTERNAL_TRANSACTION_ID
field name holding the transaction name is TRANSACTION_DIM.TRANSACTION_NAME

you can get to the location name via
<profile_db>.BPM_TRANS.INTERNAL_LOCATION_ID = <profile_db>.LOCATIONS_DIM.INTERNAL_LOCATION_ID
field name holding the location name LOCATIONS_DIM.LOCATION_NAME

Overall I guess your query should work

SELECT BPM_TRANS.SAMPLETIME ,TRANSACTIONS_DIM.TRANSACTION_NAME ,BPM_TRANS.STATUS_ID ,BPM_TRANS.EM_RESULT_VALUE ,BPM_TRANS.DBDATE
FROM BPM_TRANS,TRANSACTIONS_DIM,LOCATIONS_DIM
WHERE BPM_TRANS.INTERNAL_TRANSACTION_ID = TRANSACTIONS_DIM.INTERNAL_TRANSACTION_ID AND BPM_TRANS.INTERNAL_LOCATION_ID = LOCATIONS_DIM.INTERNAL_LOCATION_ID

That's from one of my test systems (I only retrieved top 10 results)

SAMPLETIME TRANSACTION_NAME STATUS_ID EM_RESULT_VALUE DBDATE

1472116260.000000 Step 0 - Google 1 1418 2016-08-25 11:12:29.787
1472116294.000000 Step 0 - Start JPetStore 0 2526 2016-08-25 11:12:29.787
1472116268.000000 Green 0 5000 2016-08-25 11:12:29.787
1472116278.000000 Yellow 0 10007 2016-08-25 11:12:29.787
1472116296.000000 Step 1 - Login 0 1913 2016-08-25 11:12:29.787
1472116296.000000 Step 2 - Order a bird 1 8 2016-08-25 11:12:29.787
1472111267.000000 tx Rand Availability 0 2984 2016-08-25 09:48:39.097
1472116317.000000 Step 0 - Start JPetStore_SOE 0 2554 2016-08-25 11:13:19.803
1472116319.000000 Step 1 - Login_SOE 0 1944 2016-08-25 11:13:19.803
1472116319.000000 Step 2 - Order a bird_SOE 1 5 2016-08-25 11:13:19.803

BTW, I have no idea where to find
TRANSACTIONS.RANK_1_FROM ,TRANSACTIONS.RANK_1_TO

Hope it gives you a start anyway
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Transaction info Database

another approach can be to use Custom Query Report > BPM transaction samples. then select which ever fields you need and filter the data as you want, then publish the report to CSV. you can use Publish Report option which will generate a URL/HTML for you, and the result will be in CSV format, so each time you will run the URL it will give you recent data.

0 Likes
ADR Super Contributor.
Super Contributor.

Re: Transaction info Database

Hi, 

Thank you both. 

I'm checking the option given by Asaf.

Regarding to the "RANK_1_FROM, RANK_1_TO" fields are in a table in the management schema, but I'm not sure if we can create a relation between different tables from different schemas, I mean in the point of view of your data architecture,  would it be possible ?

 

Regards

0 Likes
ADR Super Contributor.
Super Contributor.

Re: Transaction info Database

BTW, RANK_1_FROM, and RANK_1_TO are the values of the threshold established for every transaction. do you know if this values are in another table into the profile schema ?

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Transaction info Database

Hi ADR,

it seems that this informarion moved to the BSM Management database table EUMBPM_TRANS
(so not the profile database!):
..
RANK_1_FROM INT NOT NULL,
RANK_1_TO INT NOT NULL,
..
RANK_1_FROM_ROLL INT NULL,
RANK_2_FROM_ROLL INT NULL,
BASEL_RANK_UPPER_LIMIT NUMERIC(24,6) NULL,
..
AVAIL_THRESHOLD INT DEFAULT 90000 NOT NULL,
..
BASEL_RANK_LOWER_LIMIT NUMERIC(24,6) NULL);
..

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.