Report turning blank when table is loaded

shalini11 · Post by **shalini11** » Sat Oct 24, 2009 5:14 am

Hi All,

I have a simple DS job which is extracting data from a join of some tables and loading it into target table. It is truncate and load.

Cognos report fires query on the target table. The table is refreshed every 1/2 an hour. At the time when table is getting truncated and loaded with fresh data, the report turns out blank for 2-3 mins while data is getting loaded.

Update and insert can be one solution but we dont need that. I have to do truncate and load only.

Is there any other way to avoid the reports getting blank?

Your help would be highly appreciated.

Thanks

ArndW · Post by **ArndW** » Sat Oct 24, 2009 5:29 am

Two options that immediately come to mind are:

(a) Lock the table during the load process so that Cognos can't generate reports during that time.

(b) Load to a different table name, then in a closed SQL block lock the original table and delete it, then rename the new table to the old name.

I prefer the first method, as it is simpler and less prone to error.

chulett · Post by **chulett** » Sat Oct 24, 2009 6:57 am

What kind of volume are we talking about in this table? If it's not too large, then a 'Clear' (transactional delete) rather than a truncate would avoid the problem as well. What batabase, btw?