Insert hanging against Data warehouse

rmcclure · Post by **rmcclure** » 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?

DSguru2B · Post by **DSguru2B** » Mon Mar 17, 2008 12:28 pm

What is your target transaction size and array size?
Try splitting up your job, for debugging purposes, into two jobs. One that extracts into a flat file and the other, loads this flat file into your target.

rmcclure · Post by **rmcclure** » Mon Mar 17, 2008 1:20 pm

DSguru2B wrote:What is your target transaction size and array size?

Array size and transaction size are both 20

DSguru2B · Post by **DSguru2B** » Mon Mar 17, 2008 1:28 pm

Did you try increasing the array size? Any particular reason they are so low? Keep the transaction size also high, to whatever you please (multiple of array size).

chulett · Post by **chulett** » Mon Mar 17, 2008 1:28 pm

This is repeatable or it only happened once? What stage are you using for the target? What update action? Are you doing anything 'after SQL' in the stage?

rmcclure · Post by **rmcclure** » Mon Mar 17, 2008 1:37 pm

chulett wrote:This is repeatable or it only happened once? What stage are you using for the target? What update action? Are you doing anything 'after SQL' in the stage?

The source is a transaction table and the job picks up any new transactions since the last run time.
The stage for the target is Dynamic_RDBMS.
The update action is "Truncate table then insert rows"
There is no After SQL in the stage

chulett · Post by **chulett** » Mon Mar 17, 2008 1:51 pm

Bummer... was looking for something simple that would explain the delay at the end of processing. What about the first question - is this behaviour repeatable? I wonder if the delay is space management / allocation going on in the database...

rmcclure · Post by **rmcclure** » Tue Mar 18, 2008 7:23 am

chulett wrote:What about the first question - is this behaviour repeatable? I wonder if the delay is space management / allocation going on in the database...

It happens every time I run the job. I have run SQL activity monitor while this job is running and once it stalls there doesn't seem to be any activity against the DB from this job.

chulett · Post by **chulett** » Tue Mar 18, 2008 7:25 am

Have you had your DBA monitor it during the job?

rmcclure · Post by **rmcclure** » Tue Mar 18, 2008 7:39 am

chulett wrote:Have you had your DBA monitor it during the job?

Yes, he was a lot of help. He looks at the logs sees nothing and says it is a Datastage problem not a database problem.

chulett · Post by **chulett** » Tue Mar 18, 2008 7:51 am

</sarcasm off>

Hard to help if you have a DBA that's just going to play that card. [sigh]

DSguru2B · Post by **DSguru2B** » Tue Mar 18, 2008 8:17 am

Did you try splitting up the job and trying to isolate the problem? Did you try altering the array size and the transaction size ???

chulett · Post by **chulett** » Tue Mar 18, 2008 8:56 am

Nothing wrong with either size, though they do seem small. Had to hit the road, but yes the next suggestion is to pursue the 'split the jobs' suggestion and see if that changes the behaviour. Land the file in the first job to a flat file and then load from there.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 18, 2008 3:11 pm

Can you write to a text file, rather than to SQL Server, and at least isolate the hang behaviour to source or target? Then, maybe, a job to read that text file and insert into SQL Server, to eliminate interaction between the databases as a cause? Is there anything that might be locking a row or table in the target and preventing your inserts from working?

chulett · Post by **chulett** » Tue Mar 18, 2008 3:20 pm

Try to keep up, Ray.