Job hanging due to Identity Column

[memrinal]

Hi All,
We have PX job which loads data into a SQL Server2000 DB.

We have an identity column defined in the table, but we have set identity insert as off, because some other processes will also load into the table.
When this job tries to load into empty table, load is successful. But on attempting load into the actual table - which has about 1.2 Million records, the job hangs after 20-30 % execution.

Has anyone else faced a similar situation?
Appreciate if someone can provide a workaround for this.

Can we set IDENTITY_INSERT ON during our jobs execution and then set IDENTITY_INSERT OFF, so other porcesses are able to load properly and we load unqiues sequences into the table?

[memrinal]

Missed a detail.

We are using ODBC Stage for the target table.

[DSguru2B]

You can set Identity Insert On in OPEN statement and OFF in CLOSE statement. But that means that you will have to generate the keys and pass the key column in your insert. And no one else should load anything into that table at that time.
I am not sure why your job is hanging. Get together with your DBA and monitor at the database end. Your dba will be able to tell you whats going on. What is your commit level?

[ray.wurlod]

Perhaps the parallel streams into SQL Server are conflicting with each other. Try running the job with a one-node configuration file, to test this theory.

[memrinal]

Thanks Ray,

I just verified after removing the MS_IDENT. The job is still hanging after 30% processing.
I even ran it on a single node configuration table, but the job still hangs.

But when I load this data to an empty table, it loads well and then a server job is able to append to the existing large table also.

What all settings should I look for in SQL Server 2000 DB to verify if the issue is due to DB.

Please Let me know.

Thanks in Advance.

[ray.wurlod]

Without knowing the current settings it is impossible to advise. That's why you have a DBA. Ask your DBA.