Archives of the TeradataForum

Message Posted: Mon, 09 May 2011 @ 13:59:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Since you are already familiar with SQL Assistant the best tool to use to clean up your History would be SQL Assistant itself.

If you simply want to delete the old rows (or other specific rows) then it will depend on the version you are using.

SQL Assistant 13.11 contains new functions to Cleanup History. You can specify 1 or more criteria (similar to the Filter dialog) for rows that are to be deleted. There is also a function to delete all duplicate rows.

If you are not using 13.11 you would need to execute your own cleanup queries against the History database.

In this case you should use the File Paths tab of Options to point to a temporary directory for History. This will create a new (empty) History database. Then create a new ODBC Data Source to point to your real History database. (Select the driver type 'Microsoft Access' and then Browse to locate the SQLHistory.mdb file that contains your History.) Connect to this new data source and issue the queries to delete the rows you do not want.

For example:

Delete from History where Int(EndDate) < #2009-01-01# to delete everything older than 2009.

After you have finished:

Disconnect from the data source

Use File Paths to point back to your real History database again

Use Tools > Compact History to reclaim the space from the deleted rows

Delete the temporary History database that was created

If you want to keep the old rows in a separate database it is a little more complex. The simplest method however would be similar to the above.

In this case make a copy of the database and store it in a separate directory. Then follow the above instructions for each of these 2 databases.

In one case use '< #date#' and in the other use '>= #date#'.

You will end up with 2 databases - one with the older rows and another with the more recent rows.

By storing them in separate directories but using the same name (SQLHistory.mdb) you will be able to point to either one as your history.

If you have a large number of duplicate rows it would be a good idea to delete all of those. The SQL for this is a little more complex. (It will take a long time to execute if you have a large History database)

As above, connect to your History database as an ODBC data source.

Create a temporary table:

Create Table Temp (Seq Integer)

Insert the sequence numbers of the most recent row for each query:

Insert Into Temp (Seq)

Select Max(Seq) From History Group by Source,SQL Delete the older duplicate rows:

Delete From History Where Seq Not In (Select Seq from Temp) Drop the temporary table:

Drop table Temp

Use Tools > Compact History to reclaim the space from the deleted rows

Note that 5 minutes is excessive for loading 90 days worth of history. I think you may have another problem than just a large history file. I have tested with a 350MB file containing about 40,000 rows and with a 60 day filter it only takes about 5 seconds to load. (Note that this is the History load time only)

Mike Dempsey
Teradata Client Tools

	<Prev	Next>		<<First	<Prev	Next>	Last>>