Hi Phil,
It sounds like you're just using Optim to purge related data from a set of tables and don't really care about the creation of an archive file. We also use Optim in this capacity frequently and run into *very* slow Selects against tables with large BLOB/CLOB/XML fields. Sadly, Optim won't allow you to deselect the column in the AD when it's an archive (you can do that for an Extract, but you can't delete from an Extract file). I have asked IBM to allow the deselect of columns on archives (or to Delete from and Extract), but that has gone nowhere.
My way around this when the LOB field is in the Start Table is to get creative with the SELECT SQL. Essentially, you use the power of a UNION to select all the columns
except the LOB column. It works like a charm once you learn how to code it. It will look something like so:
SELECT...FROM Table1 WHERE1=0
UNION ALL
SELECT A.[Col1]
,A.[Col2]
,A.[Col3]
,A.[Col4]
,A.[Col6]
,A.[Col7]
,A.[Col8]
,null
FROM [Your_DB_Name].[dbo].[Table1] as A
WHERE A.Col2 <= dateadd(Hour,-2,getdate()) -- or whatever your criteria isThe Lines in
Green are what you normally see for the SQL Select in the Optim GUI, The lines in
blue are what you need to add to the SQL to Select all the rows you actually care about,
but without the LOB column. Please note that in my example the LOB column is Col5, but the
null value I use to replace it with is listed last on the column list in the SELECT statement. If you try to code this with the null listed in the spot where Col5 should go, Optim will throw an error at runtime. It expects LOB data last, regardless of the column order.
Again, this only works for not selecting LOB data in a Start table. LOB data in a child will remain and be a painfully slow select.
Hope this helps your situation. It has definitely been a big performance boost on our jobs that only do purging and not archiving.
------------------------------
Keith Tidball
Progressive Insurance
------------------------------
Original Message:
Sent: Tue September 03, 2019 03:28 PM
From: Phil Neff
Subject: Optim Deletes
Does Optim have the capability to just do the delete without creating the archive file?
------------------------------
[Phil] [Neff]
[Sr. Data Architect]
[J.B. Hunt]
[Lowell] [AR]
Original Message:
Sent: Fri August 30, 2019 09:15 AM
From: Phil Neff
Subject: Optim Deletes
We have tables in SQL Server that have columns defined as varchar(max). When we have them included in an job to archive/delete, the process is agonizinlgy slow. Currently we have 1 job that recently ran for 3 hours 21 minutes and it only removed 26730 rows. This is a fairly simple job. 2 tables. This timeline is fairly consistent. Longer for more rows, less for less rows.
How do we speed this up?
We are getting ready to tackle a group of tables(3) where the parent has the varchar(max) column and we need to remove between 7-10 Million rows every night just from the parent.
Thoughts? Recommendations? Suggestions?
------------------------------
[Phil] [Neff]
[Sr. Data Architect]
[J.B. Hunt]
[Lowell] [AR]
------------------------------
#InfoSphereOptim
#Optim