June 11, 2004 at 10:10 am
I have an existing SQL Server 2000 DTS job that does the following:
1. TRUNCATE TABLE1 (Execute SQL Task)
2. INSERT data into TABLE1 from DB2 Database on external server (Transform Data Task)
This works just fine, but if the DB2 database is down or TABLE1 on DB2 is empty (only when it's being updated), I do not want to run #1 and #2. There should always be data in TABLE1. TABLE1 is sometimes empty on DB2 because it's in the middle of getting data from another source, and isn't ready to be used.
Right now, if the DTS job is ran when the DB2 server is down or TABLE1 is in the middle of being updated, TABLE1 on our SQL2K is empty. This is very bad. I rather have old data, than no data. The DB2 Server is a Linked Server to the SQL2K box, so I can run direct queries from there as well.
I need help on how to implement something like this:
1. Check if DB2 Server is Online AND DB2.TABLE1 is not empty
2. If Step1 is FALSE, stop DTS job
3. If Step2 is TRUE, continue with next step (Step4)
4. TRUNCATE TABLE1
5. INSERT data ....
Does anyone have any suggestions? Thanks.
June 11, 2004 at 11:02 am
Make the first step a datapump into a #temp table on SQL.
Second Step:
DECLARE @THECOUNT INT
SELECT @THECOUNT = COUNT(*) FROM #TEMPTABLE
IF @THECOUNT = 0
BEGIN
RAISERROR ('NO DB2 DATA', 16, 1)
END
Then run your truncate table on success of step 2, and pump the data into the permanent table from the #temp table on the final step.
June 11, 2004 at 12:03 pm
I forgot to mention that I really can not use a temp table to store a second copy of TABLE1. There are several DTS jobs that are doing something similar. Each of these DB2 tables contains millions of records that can be up to 30GB. I can not afford to have these DTS jobs creating and storing temp tables of these magnitude.
Creating the temp table and then comparing it before moving it into the real table is very costly for me in terms of space, extra time needed for the job to complete, and the extra I/O needed to move all those data back and forth. I usually do this for other smaller tables, but I can't use this approach on this time...
June 11, 2004 at 12:09 pm
How about creating a temp table, insert into the temp table a count of the number of rows on the DB2 db and if that count is more than 0 then run the import?
June 11, 2004 at 12:13 pm
That sounds like it might work. Let me go try it out and see what happens. Thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply