Job hangs - Deleting and inserting to same table

chulett · Post by **chulett** » Fri Sep 27, 2002 9:23 pm

Set the commit level on the deletion link to 1, if you can afford to do that, so that it is committed immediately. I've had to do that in the exact same situation with an OCI stage and that worked for us.

-craig

ray.wurlod · Post by **ray.wurlod** » Sun Sep 29, 2002 6:10 pm

Another possibility is that the cursors are getting tangled somehow. Even though it costs an extra connection, using separate ODBC stages sometimes helps in this case.

csimms · Post by **csimms** » Tue Oct 01, 2002 10:49 am

Ray/Craig,

Thanks for the ideas. What I did do to solve the problem was to write a before-job routine responsible for executing the table deletes and truncations. This approach seems to work fine. The reason for the before-job routine was that I need a common solution that would work for various types of database stages. We have containerized our business logic so our jobs contain mainly just connection stages. We have categories of these "wrapper" jobs, one for Oracle, one for SQL Server, and one for ODBC.

Chris

Chris Simms

Senior Systems Engineer
Extended Technologies Corporation
9708 Skillman Road
Dallas, TX 75243
csimms@xtekcorp.com
www.xtekcorp.com
214-540-4108 (Desk)
214-540-4000 (Front Desk)
214-540-4004 (Fax)

ray.wurlod · Post by **ray.wurlod** » Wed Oct 02, 2002 1:00 am

You can run into grief using BCI functions (which I assume you're doing in the before-job subroutine) using ODBC drivers that require a licensing string. This has to be set via SQLSetConnectOption().

ray.wurlod · Post by **ray.wurlod** » Wed Oct 02, 2002 9:07 pm

If you're not seeing the problem you don't have the problem, so smile happily and continue what you're doing.
In general, ODBC drivers supplied by database vendors (such as Microsoft and Oracle) don't have restrictions - they LOVE you to connect to their database products!
However, some third-party suppliers of ODBC drivers, such as Merant, include the requirement for client applications to supply a value for a licensing string, to prove they've paid for the driver. The drivers that DataStage uses (at 4.2, anyway, I haven't checked at 5.x) do exhibit this behaviour, and it's caught a few people out.