We are using BODI 11.7.3.10 and the underlying DB is SQL2000. All jobs were working fine for months.
We migrated our DB from SQL 2000 to SQL 2005 and after that couple of jobs started hanging.
As part of upgrade to SQL 2005 DB, following steps were executed
1.) a new blank database was created and
2.) new repository was created, which in turn created new set of internal repository tables on SQL 2005.
3.) ATL file was imported into new environment &
4.) after that datastore configurations were changed to point to new DB.
5.) All jobs were validated.
But still we are not able to run the jobs successfully in new enviornment with SQL 2005, but same jobs are running fine with SQL 2000.
Is there anything which was missed?? or is there any way to make it working??
Just to add to it…
Some jobs are running perfectly fine but load is hanging at a particular data flow.
I did try to take out the underlying query from that data flow and ran on the SQL 2005 Database, it gotcompleted in 4 seconds. But the same DF is hanging in BODI. It is doing updates on the same table.
the reason I said could be something to do with DF design, since you mentioned that its updating the same table, is it the source or target table ? can you share the design of your DF, like what transform etc
I can try to reproduce the issue, the problem could be with how locks and tansactions are handled in sql Server 2005 may be something got changed, did you check for table locks in DB when this job is running?
How did you do the database migration for the source/target databases, from 2000 to 2005?
Are you sure no indexes on the source/target tables were dropped during this process?
Also, after upgrading the SQL databases, you should do a full Index Rebuild, and then Column Stats update. (Both are things your SQL DBAs should do, these aren’t BODI things.)
(BOB member since 2004-01-30)