I need to know how many rows are inserted or deleted in each DML.
After making the query, I analyze the table dbc.DBQLStepTbl where I find the data by ProcID + QueryID:
the line of the StepName = MRG is correct, RowCount is 137.455 rows inserted.
Then to find out what the table is, join against dbc.DBQLObjTbl by ProcID + QueryID
The executed query was:
INSERT into D_STAGING.TUTABLAS
SELECT * FROM D_STAGING.TUTABLAS_TEMP
How to know in which of the Tables that appear in dbc.DBQLObjTbl the Inserts were made, the columns show the same data.
Dear TD specialists, any suggestions on how to distinguish in which of the 2 detected tables the inserts are made (TD 15.10)?
I don't think you can get this info from QryLogObjects, do you have QryLogExplain, too?
Do you need the exact count per INSERT?
Or the overall count? This can be returned by DBC.DeleteUseCountV when "Object Use Count" is enabled.
Yes, I want to get the number of inserts and deletes in each table for each executed sentence.
In DBC.DBQLStepTbl there is the number of rows inserted or deleted by the statement, and in DBC.DBQLObjTbl the tables involved, but (strangely) the type of access reported (2) is the same for both tables (for example in a simple Insert). / Select).
The DBC.ObjectUsage table only has the summarized information.
Unfortunately DBC.DBQLogTbl in the column NumResultRows only informs the value for the statements of type Select, not so for Inserts, Updates or Deletes.
Greetings and thanks.
Yes, but I find this information:
"1) First, we do an INSERT into D_WRK.TNJO_JOURNAL_LOG. The estimated time for this step is 0.04 seconds.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 0.04 seconds. "
This is an Explain of a single row INSERT VALUES.
You can apply a RegEx to extract the database.tablename from ExplainText, this should work:
RegExp_Substr(ExplainText,'(1[)] First, we do an INSERT|MERGE step) into\s+\K\S+',1,1,'imn') INSERT VALUES-- 1[)] First, we do an INSERT INSERT SELECT-- MERGE step
OK, I did something similar from DBC.QryLogSql.SqlTextInfo looking for the INSERT INTO statements ... and then joining with DBQLStepTbl with ProcID + QueryID.
But this is very laborious ..., DBQL should provide this data.
Thank you very much for your cooperation.