DBC.QRYLOG Iteration count

Database
Enthusiast

DBC.QRYLOG Iteration count

We have a SQOOP/OOZIE process and inserting data into Teradata using JDBC.

Initially all their inserts were coming as sinlge row inserts. Each insert was creating a record in qrylog/sqllog (and other log tables).

Each isert was coming as INSERT INTO TABLE VALUES (?,?,?,?)

So, a million reocrds inserted in the data table, created a million records (or more) in each of the log tables.

 

Big Data team tweaked their process and now, I see only 1 row in log table. They changed this param - sqoop.export.records.per.statement=10000

The insert query still says:

INSERT INTO TABLE VALUES (?,?,?,?)

But the IternationCount column on dbc.qrylog is equal to the number of rows inserted in the target table.

 

Can someone please try and explain what could be happening here? 

Big data team says they cannot use loader utilties (TPT - mload/fastload or tpump).

 

Is this an optimal way to insert data into TD from Hadoop? Any other suggestions?

 

As a sidenote: we are exploring QueryGrid as an alternative.

 


Accepted Solutions
Teradata Employee

Re: DBC.QRYLOG Iteration count

>>> Can someone please try and explain what could be happening here?


The Teradata Database Reference / Data Dictionary manual says the following about the DBC.QueryLogV.IterationCount column:

"This column logs the iteration count for the data parcel associated with a request. This value is NULL for requests with a single using data row and no using data row."

 

With respect to the Teradata JDBC Driver, when an application uses PreparedStatement batch insert, then the DBC.QueryLogV.IterationCount column indicates the number of rows in the batch.

 

PreparedStatement batch insert is more efficient than single-row insert. We recommend the use of PreparedStatement batch insert.

https://community.teradata.com/t5/Connectivity/Speed-up-your-JDBC-ODBC-applications/td-p/14131

 

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: DBC.QRYLOG Iteration count

>>> Can someone please try and explain what could be happening here?


The Teradata Database Reference / Data Dictionary manual says the following about the DBC.QueryLogV.IterationCount column:

"This column logs the iteration count for the data parcel associated with a request. This value is NULL for requests with a single using data row and no using data row."

 

With respect to the Teradata JDBC Driver, when an application uses PreparedStatement batch insert, then the DBC.QueryLogV.IterationCount column indicates the number of rows in the batch.

 

PreparedStatement batch insert is more efficient than single-row insert. We recommend the use of PreparedStatement batch insert.

https://community.teradata.com/t5/Connectivity/Speed-up-your-JDBC-ODBC-applications/td-p/14131

 

Enthusiast

Re: DBC.QRYLOG Iteration count

Thanks. Very helpful link!