DTS Date Ranges w/IBMDA400

We have several DTS packages that copy over sales history information into tables setup on the SQL server, & from there we do our reporting. The problem is that everytime the packages run we clear out the current tables, for instance (TRUNCATE TABLE Tickets), next the DTS' copy over the updated information as well as the history, again. It takes hours for these things to run. I'd like to be able to copy over just latest ticket history and not clear the table and start over each night. I'm importing the information from an IBM AS 400 (IBM DB2 for iSeries IBMDA400 OLE DB Provider) into the SQL Tables.
Here's the query:

SELECT     TH_CUSTNO, TH_TICKNO, TH_TRANYR, TH_TRANMO,
TH_TRANDA, TH_PRIMRY_ROUTNO, TH_CUSTOMER_BRANCH, TH_TKTOTL,
6 as LocationId,TH_CSHCHG
FROM         RAP011901.TICHIS
WHERE  TH_TRANYR = 05 or TH_TRANYR = 06

Any ideas will be greatly appreciated.
DFCRJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You may reduce the scope of the writing to the day by modifying the query so that you don't take the whole thing everyday...For instance, take off TRUNCATE TABLE/CREATE TABLE and replace them with a statement deleting all the SQL server records inserted for the month then reduce the scope of your select statement pulling the data from the DB2 so that it only pumps data for the month -->should reduce the pumping time...Ex(you nedd to check SQL syntax for DB2 --this is SQL):

SELECT     TH_CUSTNO, TH_TICKNO, TH_TRANYR, TH_TRANMO,
TH_TRANDA, TH_PRIMRY_ROUTNO, TH_CUSTOMER_BRANCH, TH_TKTOTL,
6 as LocationId,TH_CSHCHG
FROM         RAP011901.TICHIS
WHERE  month(dates) = month(getdate())
and year(dates) = year(getdate())

  --> month(getdate()) is current month

Check also if you can set some kind of Transactional Replication (never tried it ) between your DB2 and SQL Server// Hope this helps...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DFCRJAuthor Commented:
i didnt quite do it that why, but it did give me an idea. I changed the Truncate Table and replaced it with

DELETE    
FROM         Tickets
WHERE  
   (TransactionDate > CONVERT(DATETIME, '2006-01-01 00:00:00'))

This allows me to keep year 04/05 and not erase and reload the data nightly, it also gave me additional time to look at your suggestions. Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.