SQL to find Row Count

Database
Enthusiast

SQL to find Row Count

Hi,

I am trying to find if there is any way we can find the record count of queries that logged into DBQL with Insert or Update statement types. I tring to see the row count by taking the queryid and searching for the final step from the DBQLSTEPTBL. But I cannot do this manually for several ETLs. Is there any way we can find the row count quicky for the SQL statements that logged into DBQL.

Thanks,

Praneeth

Tags (1)
8 REPLIES
Enthusiast

Re: SQL to find Row Count

No. DBQL doesn't seem to log records inserted, deleted, or updated by INSERT, UPDATE, DELETE, or MERGE operations.

Do you have any timestamp or date fields on the table to track when records have been added and/or updated on the table? With these additional fields you can track INSERTS and UPDATES easier. DELETE would be a bit more difficult unless you were applying logical deletes to the table with a flag or expiration timestamp field.

You could collect PARTITION stats on the table before and after the operation. Then using some SQL extract the table cardinality from the statistics before and after any operations against the table that would add or remove records.

Otherwise, you are left with parsing the log files looking for activity statements to determine the records affected by the operation(s) in question and logging those to an audit or log table.

Junior Contributor

Re: SQL to find Row Count

If STEPINFO is enabled then dbc.QryLogSteps.RowCount records the activitycount.

Based on StepName:

- UPD: updates

- DEL: deletes

- MRG: inserts (ins/sel)

- MRM: inserts, RowCount2 -> updates

There are some additional StepNames for variations of ins/upd/del (e.g. FastLoad/MultiLoad/single row ins/merge delete), you should check the list of possible StepNames in the Database Administration manual.

Dieter

Enthusiast

Re: SQL to find Row Count

Learned something new! Thanks Dieter. Didn't even consider StepInfo.

Step Info does generate a significant volume so from a historical basis you'd want to keep 30-60 days tops and maybe build aggregate data from that. 

Enthusiast

Re: SQL to find Row Count

Hi dieter,

could you please explain diff blw                        rowcount|rowcount2|rowcount3

Junior Contributor

Re: SQL to find Row Count

Cut'n'paste from the Database Administration manual:

RowCount

If StepName is MRM (Merge Row Multiple) or EXE (MultiLoad),

RowCount is the number of rows inserted. For all other steps,

RowCount is the actual number of rows returned by the step

(indicating activity count).

RowCount2

If StepName is MRM (Merge Row Multiple) or EXE (MultiLoad),

RowCount2 is the number of updated rows. If StepName is

LFI (FastLoad), RowCount is the number of rows loaded.

RowCount3

If StepName is EXE (MultiLoad), this field is the number of rows

deleted.

Dieter

Enthusiast

Re: SQL to find Row Count

Dieter,

I have been doing the same way you mentioned. We are using  Informatica for data movement within teradata using pushdown optimization. There can be multiple insert/merge/update statements in workflow which will be logged. I am taking the Queryid for each step and checking in StepTBL to see how many rows were processed. But I am curious if it is possible to join DBQLOGTBL, DBQLSQLTBL, and DBQLSTEPTBL to get the query and also number of rows processed by the query.

Thank you all for the reply and the time you have spent replying.

Thanks,

Praneeth

Enthusiast

Re: SQL to find Row Count

The column queryid can be used to join those DSBQL tables/views as well as the DBQLOBJTBL together.  Queryid is a system wide unique value.

Teradata Employee

Re: SQL to find Row Count

Really I  learn great  from this !