Insert hanging against Data warehouse
Posted: Mon Mar 17, 2008 12:00 pm
We have a strange problem with one of our datastage jobs. We have a job that stages a table with an incremental load.
We use ODBC to pull from an AS/400 source DB to a SQL server target DW. This job is very simple it has 1 source, 1 transformer, and 1 target. Nothing else. I ran this job while monitoring the SQL DB and the DS job.
Here is the problem:
The job starts and DS perfornamce statistics shows it running in the 1000's of rows per second. I watch as the row count grows and the rows per second value is very high. I also do a select count(*) against the DB and I watch it grow as well.
Suddenly about 3 to 5 minutes into the job the performance statistics for the record count stop. The Select count(*) against the target database also stops growing. The only thing that changes is the rows per second shinks. About 1 hour and 20 minutes into the job. The performance statistics change and the Select count(*) changes and the job complete successfully.
An example is the job started and 3 minutes in to the job a select count(*) on the database returned 154000 records. That count stayed the same for the next hour and 17 minutes until finally the job ended and the Select count(*) returned 154069 records.
I also ran SQL servers activity monitor and during the time the job is stalled it shows no activity against this DB.
Any ideas?
We use ODBC to pull from an AS/400 source DB to a SQL server target DW. This job is very simple it has 1 source, 1 transformer, and 1 target. Nothing else. I ran this job while monitoring the SQL DB and the DS job.
Here is the problem:
The job starts and DS perfornamce statistics shows it running in the 1000's of rows per second. I watch as the row count grows and the rows per second value is very high. I also do a select count(*) against the DB and I watch it grow as well.
Suddenly about 3 to 5 minutes into the job the performance statistics for the record count stop. The Select count(*) against the target database also stops growing. The only thing that changes is the rows per second shinks. About 1 hour and 20 minutes into the job. The performance statistics change and the Select count(*) changes and the job complete successfully.
An example is the job started and 3 minutes in to the job a select count(*) on the database returned 154000 records. That count stayed the same for the next hour and 17 minutes until finally the job ended and the Select count(*) returned 154069 records.
I also ran SQL servers activity monitor and during the time the job is stalled it shows no activity against this DB.
Any ideas?