Hi all,
I am using a DB2 Stage as a lookup stage(dont know
why) to load to the same table.
In transformer it looks for Key Columns(Key and
FromDate) and the Criteria to load the table is: if ID
is Null then load.
Table A structure is:
Id Integer
Key char(8)
FromDate Date
Lookup Table A
|
Sourcedata----> Transformer -------> Targer Table A
I have the prefected value of 50 rows.
The problems comes when I run the job, it runs forever
and I have to abort the job.And by looking at the log
file it hangs forever at the lookup state. Is it a
database problem? Has anyone faced similar problem. Is
there a better way to approach this by redisning the
job.
Platform:
DS 4.1
DB2 7.1 on AIX
Thanks in advance.
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com
Prefected rows
Are you using the same DB2 stage for the lookup and for the insert?
DataStage uses the links to sort out sequencing. Inputs to a static stage (your insert) will be processed before outputs from the same static stage (your lookup).
If this is the case in your job, you need to use separate DB2 stages.
Something else to consider. If the input contains a situation where one row would result in a record being loaded and a later row would then match it, you should set the transaction size on your insert to 1. This way, subsequent lookups will find prior rows that have been inserted in this job. Works this way with Oracle, not sure about DB2.
There may also be an issue with database locking. If there is, committing every row should also fix this.
David
-----Original Message-----
From: John Thomas [mailto:datastagehelp@yahoo.com]
Sent: Thursday, 25 October 2001 6:48 AM
To: datastage-users@oliver.com
Subject: Prefected rows
Hi all,
I am using a DB2 Stage as a lookup stage(dont know
why) to load to the same table.
In transformer it looks for Key Columns(Key and
FromDate) and the Criteria to load the table is: if ID
is Null then load.
Table A structure is:
Id Integer
Key char(8)
FromDate Date
Lookup Table A
|
Sourcedata----> Transformer -------> Targer Table A
I have the prefected value of 50 rows.
The problems comes when I run the job, it runs forever
and I have to abort the job.And by looking at the log
file it hangs forever at the lookup state. Is it a
database problem? Has anyone faced similar problem. Is
there a better way to approach this by redisning the
job.
Platform:
DS 4.1
DB2 7.1 on AIX
Thanks in advance.
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com
DataStage uses the links to sort out sequencing. Inputs to a static stage (your insert) will be processed before outputs from the same static stage (your lookup).
If this is the case in your job, you need to use separate DB2 stages.
Something else to consider. If the input contains a situation where one row would result in a record being loaded and a later row would then match it, you should set the transaction size on your insert to 1. This way, subsequent lookups will find prior rows that have been inserted in this job. Works this way with Oracle, not sure about DB2.
There may also be an issue with database locking. If there is, committing every row should also fix this.
David
-----Original Message-----
From: John Thomas [mailto:datastagehelp@yahoo.com]
Sent: Thursday, 25 October 2001 6:48 AM
To: datastage-users@oliver.com
Subject: Prefected rows
Hi all,
I am using a DB2 Stage as a lookup stage(dont know
why) to load to the same table.
In transformer it looks for Key Columns(Key and
FromDate) and the Criteria to load the table is: if ID
is Null then load.
Table A structure is:
Id Integer
Key char(8)
FromDate Date
Lookup Table A
|
Sourcedata----> Transformer -------> Targer Table A
I have the prefected value of 50 rows.
The problems comes when I run the job, it runs forever
and I have to abort the job.And by looking at the log
file it hangs forever at the lookup state. Is it a
database problem? Has anyone faced similar problem. Is
there a better way to approach this by redisning the
job.
Platform:
DS 4.1
DB2 7.1 on AIX
Thanks in advance.
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com