Commit, transactions,... ????

[admin]

Hi,

I use DataStage 4.1 and UDB V7.
Can anyone help me with the following problem?

Im writing a DataStage job in DataStage script, but I have some problems when I run this job. (Compilation and validation are OK) The job runs fine if I dont use Commit or Transactions, but we would like to commit after every n records. The programming logic in this job is very easy, but we need similar commit logic for more complex jobs. Some of our dimensions have a volume of 20 million records (or even more!), so we need to commit after every n records. This is the runtime error message I get when I use Transaction isolation 2,3
or 4:

DataStage Job 488 Phantom 2789
Program "*DataStage*DSR_LOADSTRING": Line 138, FATAL:
The locks necessary for database operations at the current isolation level (2) are not held by this process. Attempting to Cleanup after ABORT raised in stage ADSInitDate.JobControl

When I use Transaction isolation level 0 or 1, I get:

DataStage Job 488 Phantom 2182
Program "DSD.WriteLog": Line 95, FATAL:
The locks necessary for database operations at the current isolation level (1) are not held by this process. Attempting to Cleanup after ABORT raised in stage ADSInitDate.JobControl


Below youll find a part of my job logic. What am I doing wrong?


Set Transaction Isolation Level 1

Loop
While MinDate

[admin]

Set Transaction Isolation Level, Begin Transaction and Commit statements apply to the UniVerse database (DataStage engine), not to the database (UDB) to which you are connected. This is why UDB is reporting that you do not hold the locks. Use SQLSetConnectOptions() to specify that you want to exert manual control of transactions with auto-commit off, and SQLTransact() to commit or rollback. But why are you using BASIC for this? You could do this almost entirely graphically. Looks to me like youre building a date dimension. Why not (for example) generate the dates into a text file with BASIC, then use a "proper" DataStage job to generate a bulk load for UDB?


-----Original Message-----
From: Van Den Eynde, An [mailto:an.vandeneynde@eds.com]
Sent: Monday, 09 July 2001 22:49
To: datastage-users@oliver.com
Subject: Commit, transactions,... ????
Importance: High


Hi,

I use DataStage 4.1 and UDB V7.
Can anyone help me with the following problem?

Im writing a DataStage job in DataStage script, but I have some problems when I run this job. (Compilation and validation are OK) The job runs fine if I dont use Commit or Transactions, but we would like to commit after every n records. The programming logic in this job is very easy, but we need similar commit logic for more complex jobs. Some of our dimensions have a volume of 20 million records (or even more!), so we need to commit after every n records. This is the runtime error message I get when I use Transaction isolation 2,3 or 4:

DataStage Job 488 Phantom 2789
Program "*DataStage*DSR_LOADSTRING": Line 138, FATAL:
The locks necessary for database operations at the current isolation level (2) are not held by this process. Attempting to Cleanup after ABORT raised in stage ADSInitDate.JobControl

When I use Transaction isolation level 0 or 1, I get:

DataStage Job 488 Phantom 2182
Program "DSD.WriteLog": Line 95, FATAL:
The locks necessary for database operations at the current isolation level (1) are not held by this process. Attempting to Cleanup after ABORT raised in stage ADSInitDate.JobControl


Below youll find a part of my job logic. What am I doing wrong?


Set Transaction Isolation Level 1

Loop
While MinDate