What is the Table updated in dbc.DBQLObjTbl

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
BNA
Enthusiast

What is the Table updated in dbc.DBQLObjTbl

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:

ProcIDStepLev1NumStepLev2NumStepNameIOcountEstRowCountRowCountRowCount2RowCount3IOKBPhysIOPhysIOKB
30.71810MLK   001?????
30.71820MLK   001?????
30.71830MLK   00800????
30.71840RET   1.516,00137.042,00137.455,0000165.004,00??
30.71850MRG   2.154,00137.042,00137.455,000?244.572,002498.440,00
30.71860SplTAB0???????
30.71870Edt   2701?????

 

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

 

ProcIDObjectDatabaseNameObjectTableNameObjectNumObjectTypeFreqofUseTypeofUse
30.718D_STAGINGTUTABLAS_TEMP0Tab12
30.718D_STAGINGTUTABLAS0Tab12

 

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.

 

Regards.

 

7 REPLIES 7
BNA
Enthusiast

Re: What is the Table updated in dbc.DBQLObjTbl

Dear TD specialists, any suggestions on how to distinguish in which of the 2 detected tables the inserts are made (TD 15.10)?

Ambassador

Re: What is the Table updated in dbc.DBQLObjTbl

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.

BNA
Enthusiast

Re: What is the Table updated in dbc.DBQLObjTbl

Hello dnoeth,

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.

Ambassador

Re: What is the Table updated in dbc.DBQLObjTbl

What about dbc.QryLogExplain, is it enabled?

BNA
Enthusiast

Re: What is the Table updated in dbc.DBQLObjTbl

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. "

Ambassador

Re: What is the Table updated in dbc.DBQLObjTbl

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

 

BNA
Enthusiast

Re: What is the Table updated in dbc.DBQLObjTbl

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.
regards