This page explains how to identify SQL queries associated to an action done on TCC.
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
| 8.1.x | |
| 8.0.x | |
| 7.3.x | |
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server | |
| CSS2 | |
First, you need to activate SQL queries. For that please refer to the page: Transaction Configuration Center - Information - How to launch TCC in SHOW SQL mode in order to get the SQL traces.
Then locate TCC logs. For this, please follow the page CAST Management Studio - Information - How to find logs. If you want to modify the location of TCC log, you may refer to Transaction Configuration Center - Information - How to change the default location of TCC log file.
Once SQL traces are activated, You can launch TCC and connect to your management DB. You should see the following on your TCC log file:
2016-05-17 10:17:37.247 Using arguments: -ShowSQL
2016-05-17 10:17:49.388 Connected to css2sup -> //css2sup:2280/postgres, m735_4792_nbe
You can see that SQL traces are activated. You can then empty the log file before performing any action in TCC. This way once the action is launched, only SQL queries related to this action will be available in the log.
For example, after emptying the TCC log file and launching the "load current results" action, here is the new content of the log:
2016-05-23 09:44:40.298 Calibration results
2016-05-23 09:44:40.689 Connection...
2016-05-23 09:44:41.173 INF: [SQL] select Object_Name, Server_ID from m735_4792_nbe.CMS_INF_CSS_LocalDB where Object_ID = 44058
2016-05-23 09:44:43.064 INF: [SQL] select Object_Name, UserName, Password, Host, Port from m735_4792_nbe.CMS_INF_STORE_CSS where Object_ID = 966
2016-05-23 09:44:44.548 INF: [SQL] set search_path = l735_4792_nbe, pg_catalog
2016-05-23 09:44:44.548 INF: [SQL] set standard_conforming_strings = on
2016-05-23 09:44:44.548 Loading data functions...
2016-05-23 09:44:44.564 INF: [SQL] select DF.Object_ID, DF.MainTable_ID, KE.Object_Name, K.KeyNam, K.ObjTyp, DF.DET, DF.RET, DF.ILF_Ex, DF.IsInternal, DF.User_FP_Value, DF.User_IsInternal, DF.Cal_Flags, DF.Cal_MergeRoot_ID, O.OBJECT_FULLNAME from l735_4792_nbe.CDT_OBJECTS O, l735_4792_nbe.Keys K, l735_4792_nbe.DSS_KeysExtra KE, l735_4792_nbe.DSS_DataFunction DF where DF.Appli_ID = ? and DF.Object_ID = KE.Object_ID and DF.MainTable_ID = K.IdKey and O.OBJECT_ID = DF.MainTable_ID
2016-05-23 09:44:44.564 INF: [SQL] 1: 1440
2016-05-23 09:45:22.549 Loading data function details...
2016-05-23 09:45:22.580 INF: [SQL] select DFD.Object_ID, DFD.Table_ID, K.KeyNam, K.ObjTyp from l735_4792_nbe.DSS_DataFunction DF, l735_4792_nbe.DSS_DataFunctionDetails DFD, l735_4792_nbe.Keys K where DF.Appli_ID = ? and DF.Object_ID = DFD.Object_ID and K.IdKey = DFD.Table_ID and DFD.MergeFlags = 0
2016-05-23 09:45:22.580 INF: [SQL] 1: 1440
2016-05-23 09:45:25.018 Connection...
2016-05-23 09:45:25.049 Loading transactions...
2016-05-23 09:45:25.049 INF: [SQL] select T.Object_ID, T.Form_ID, KE.Object_Name, K.KeyNam, K.ObjTyp, T.DET, T.FTR, T.TF_Ex, T.IsInput, T.User_FP_Value, T.User_IsInput, T.Cal_Flags, T.Cal_MergeRoot_ID, O.OBJECT_FULLNAME from l735_4792_nbe.CDT_OBJECTS O, l735_4792_nbe.Keys K, l735_4792_nbe.DSS_KeysExtra KE, l735_4792_nbe.DSS_Transaction T where T.Appli_ID = ? and T.Object_ID = KE.Object_ID and T.Form_ID = K.IdKey and O.OBJECT_ID = T.Form_ID
2016-05-23 09:45:25.049 INF: [SQL] 1: 1440
2016-05-23 09:46:16.081 Loading transaction details...
2016-05-23 09:46:16.081 INF: [SQL] select T.Object_ID, TR.Root_ID, K.KeyNam, K.ObjTyp, TR.MergeFlags from l735_4792_nbe.Keys K, l735_4792_nbe.DSS_TransactionRoots TR, l735_4792_nbe.DSS_Transaction T where T.Appli_ID = ? and T.Object_ID = TR.Object_ID and TR.MergeFlags = 0 and K.IdKey = TR.Root_ID
2016-05-23 09:46:16.081 INF: [SQL] 1: 1440
To run the same set of SQL queries on pgadmin:
For the queries that are complete, you can run them directly on pgadmin. For example, first query of above trace can be run without any modification
select Object_Name, Server_ID from m735_4792_nbe.CMS_INF_CSS_LocalDB where Object_ID = 44058
For the queries that have some missing parameters. For example, the 5th query of the above trace:
2016-05-23 09:44:40.298 Calibration results
2016-05-23 09:44:40.689 Connection...
2016-05-23 09:44:41.173 INF: [SQL] select Object_Name, Server_ID from m735_4792_nbe.CMS_INF_CSS_LocalDB where Object_ID = 44058
2016-05-23 09:44:43.064 INF: [SQL] select Object_Name, UserName, Password, Host, Port from m735_4792_nbe.CMS_INF_STORE_CSS where Object_ID = 966
2016-05-23 09:44:44.548 INF: [SQL] set search_path = l735_4792_nbe, pg_catalog
2016-05-23 09:44:44.548 INF: [SQL] set standard_conforming_strings = on
2016-05-23 09:44:44.548 Loading data functions...
2016-05-23 09:44:44.564 INF: [SQL] select DF.Object_ID, DF.MainTable_ID, KE.Object_Name, K.KeyNam, K.ObjTyp, DF.DET, DF.RET, DF.ILF_Ex, DF.IsInternal, DF.User_FP_Value, DF.User_IsInternal, DF.Cal_Flags, DF.Cal_MergeRoot_ID, O.OBJECT_FULLNAME from l735_4792_nbe.CDT_OBJECTS O, l735_4792_nbe.Keys K, l735_4792_nbe.DSS_KeysExtra KE, l735_4792_nbe.DSS_DataFunction DF where DF.Appli_ID = ? and DF.Object_ID = KE.Object_ID and DF.MainTable_ID = K.IdKey and O.OBJECT_ID = DF.MainTable_ID
2016-05-23 09:44:44.564 INF: [SQL] 1: 1440
2016-05-23 09:45:22.549 Loading data function details...
2016-05-23 09:45:22.580 INF: [SQL] select DFD.Object_ID, DFD.Table_ID, K.KeyNam, K.ObjTyp from l735_4792_nbe.DSS_DataFunction DF, l735_4792_nbe.DSS_DataFunctionDetails DFD, l735_4792_nbe.Keys K where DF.Appli_ID = ? and DF.Object_ID = DFD.Object_ID and K.IdKey = DFD.Table_ID and DFD.MergeFlags = 0
2016-05-23 09:45:22.580 INF: [SQL] 1: 1440
2016-05-23 09:45:25.018 Connection...
2016-05-23 09:45:25.049 Loading transactions...
2016-05-23 09:45:25.049 INF: [SQL] select T.Object_ID, T.Form_ID, KE.Object_Name, K.KeyNam, K.ObjTyp, T.DET, T.FTR, T.TF_Ex, T.IsInput, T.User_FP_Value, T.User_IsInput, T.Cal_Flags, T.Cal_MergeRoot_ID, O.OBJECT_FULLNAME from l735_4792_nbe.CDT_OBJECTS O, l735_4792_nbe.Keys K, l735_4792_nbe.DSS_KeysExtra KE, l735_4792_nbe.DSS_Transaction T where T.Appli_ID = ? and T.Object_ID = KE.Object_ID and T.Form_ID = K.IdKey and O.OBJECT_ID = T.Form_ID
2016-05-23 09:45:25.049 INF: [SQL] 1: 1440
2016-05-23 09:46:16.081 Loading transaction details...
2016-05-23 09:46:16.081 INF: [SQL] select T.Object_ID, TR.Root_ID, K.KeyNam, K.ObjTyp, TR.MergeFlags from l735_4792_nbe.Keys K, l735_4792_nbe.DSS_TransactionRoots TR, l735_4792_nbe.DSS_Transaction T where T.Appli_ID = ? and T.Object_ID = TR.Object_ID and TR.MergeFlags = 0 and K.IdKey = TR.Root_ID
2016-05-23 09:46:16.081 INF: [SQL] 1: 1440
As you can see above the query has one missing parameter: DF.Appli_ID
The value of this parameter is given in the next line:
2016-05-23 09:44:44.564 INF: [SQL] 1: 1440
Hence, to run the query you have to replace the ? with the value given above.
select DF.Object_ID, DF.MainTable_ID, KE.Object_Name, K.KeyNam, K.ObjTyp, DF.DET, DF.RET, DF.ILF_Ex, DF.IsInternal, DF.User_FP_Value, DF.User_IsInternal, DF.Cal_Flags, DF.Cal_MergeRoot_ID, O.OBJECT_FULLNAME from l735_4792_nbe.CDT_OBJECTS O, l735_4792_nbe.Keys K, l735_4792_nbe.DSS_KeysExtra KE, l735_4792_nbe.DSS_DataFunction DF where DF.Appli_ID = 1440 and DF.Object_ID = KE.Object_ID and DF.MainTable_ID = K.IdKey and O.OBJECT_ID = DF.MainTable_ID
