How can I find out where certain data is stored in the database?

Document ID : KB000016853
Last Modified Date : 06/09/2018
Show Technical Document Details
Question:

In Service Desk Manager, data is referenced by "objects" but not by "tables", however the data is stored in tables within the MDB.  How can I find out which table is used to store certain data.

Environment:
CA Service Desk Manager - ALL VERSIONS
Answer:

To see where data is written to the database, turn on logging:

For SQL Server use the command:  pdm_logstat sqlclass.c MILESTONE
For Oracle Database use the command: pdm_logstat -f orclclass.c MILESTONE

Update one of the records for which you are looking for the table in which it is stored, then turn the logging off with the command:

For SQL Server use the command:  pdm_logstat sqlclass.c
For Oracle Database use the command: pdm_logstat -f orclclass.c

The milestone entries in the logs will show you the queries in which you can see what tables the data is being queried, and the insert statements or update statements showing where the data is coming from and written to.

Example:

In Organizations within Service Desk, there is an "Environment" tab which stores some additional data tied to that organization.  You want to know where that data is stored - in which table.

What you can do is run the logging as decribed above, and then attempt to add an additional record to the Environment tab on that organization.   

Looking in the log you would see the following:

Select Statement:

12/13 12:23:57.27 SDM-SERVER  sqlagt:select1       9164 MILESTONE    sqlclass.c            1057 The following statement took 89 milliseconds: Clause (SELECT ca_owned_resource.own_resource_uuid, ca_owned_resource.resource_name, ca_resource_family.table_extension_name, ca_resource_class.name, ca_resource_family.name, ca_owned_resource.serial_number, ca_contact.last_name+CASE WHEN ( ca_contact.first_name IS NULL OR ca_contact.first_name = ? ) AND ( ca_contact.middle_name IS NULL OR ca_contact.middle_name = ? ) THEN N'' ELSE N', '+isnull(ca_contact.first_name,N'')+N' '+isnull(ca_contact.middle_name,N'') END AS "combo_name", ca_owned_resource.last_update_date, ca_owned_resource.product_version, cn01_ca_owned_resource.resource_name, bool_tab.sym, cn02_Boolean_Table.sym FROM (ca_owned_resource LEFT JOIN ca_resource_family ON ca_owned_resource.resource_family = ca_resource_family.id LEFT JOIN ca_contact ON ca_owned_resource.resource_contact_uuid = ca_contact.contact_uuid) LEFT JOIN (usp_owned_resource LEFT JOIN ca_owned_resource cn01_ca_owned_resource ON usp_owned_resource.baseline_uuid = cn01_ca_owned_resource.own_resource_uuid) ON ca_owned_resource.own_resource_uuid = usp_owned_resource.owned_resource_uuid, bool_tab cn02_Boolean_Table, bool_tab, ca_resource_class WHERE ( ( ( ca_owned_resource.own_resource_uuid IN (?) ) AND ca_owned_resource.resource_class = ca_resource_class.id ) AND ca_owned_resource.is_asset = bool_tab.enum ) AND ca_owned_resource.is_ci = cn02_Boolean_Table.enum) Input (<string>|<string>|<uuid>2771EF32B58474438C4C7AD36568DAA9)

 

Insert Statement:

12/13 12:23:56.59 isrjo02-E2726  sqlagt:upinde        8648 MILESTONE    sqlclass.c            1057 The following statement took 31 milliseconds: Clause (INSERT INTO usp_lrel_oenv_orgref ( last_mod_by, last_mod_dt, nr, org, id ) VALUES ( ? , ? , ? , ? , ? )) Input (<last_mod_by:uuid>BCC279AA74E6F8438F632FB47B5BD83C|<last_mod_dt:time>12/13/2017 12:23:56|<nr:uuid>2771EF32B58474438C4C7AD36568DAA9|<org:uuid>8748A81A2EEE2347B2DCAE63BC33572C|<id:int>400001)

 

So in this example you can see that its pulling the available data from the usp_owned_resource table, and its storing the relationship for that record to the organization record in the usp_lrel_oenv_orgref table.