Job in running mode for long time

[nagarjuna]

i am having a DS job .Its design is as below :

Seq file ---> column export ---> transformer ---- > column import ---> Oracle enterprise .

Oracle enterprise stage is in upsert mode . The sequential file will have only update records .We separated updates and inserts .Upsert mode is user defined updates only .

we are using schema file option in sequenial file stage . when i am running the job is in running mode for long time .i am getting log as shown below .There is no progress in the datastage log also .

DataStage Report - Summary Log for job: JobName.Invocationid
Produced on: 5/26/2009 2:44:22 PM
Project: ProjectName Host system: HostName
Items: 1 - 20
Sorted on: Date Sorter
Entries are filtered

Occurred: 2:02:36 PM On date: 5/26/2009 Type: Control
Event: Starting Job JobName.Invocationid. (...)

Occurred: 2:02:37 PM On date: 5/26/2009 Type: Info
Event: Environment variable settings: (...)

Occurred: 2:02:37 PM On date: 5/26/2009 Type: Info
Event: Parallel job initiated

Occurred: 2:02:37 PM On date: 5/26/2009 Type: Info
Event: OSH script (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: IBM WebSphere DataStage Enterprise Edition 8.0.1.5246 (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: The open files limit is 2048; raising to 2147483647.

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: conductor uname: -s=AIX; -r=3; -v=5; -n=HostName; -m=00C4C6FD4C00

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: orchgeneral: loaded (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: Echo: (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: Explanation: (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: Dump: (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: APT configuration file: /opt/IBM/InformationServer/Server/Configurations/default4_dpbmdss.apt (...)

Occurred: 2:02:38 PM On date: 5/26/2009 Type: Info
Event: main_program: This step has 2 datasets: (...)

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: main_program: Schemas: (...)

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 10 percent.

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 20 percent.

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 30 percent.

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 40 percent.

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 50 percent.

Occurred: 2:02:40 PM On date: 5/26/2009 Type: Info
Event: SEQF_Load_Updates,0: Progress: 60 percent.

End of report.

Has any body faced like this ? please help me .

Thanks in advance

[vkhandel]

What is the partitioning done on the Oracle Enterprise Stage?

If you have used RoundRobin, could you try with Hash/sort on Target Table Key Columns.

[ArndW]

After displaying 60% the process just hangs forever? Can you monitor system performance from UNIX (I like "vmstat") to see if the machine is still busy?

[nagarjuna]

Thanks for all for your suggestions ...today i will follow your advice and let you know the outcome

[miwinter]

Perhaps a bottleneck downstream (prime suspect being the DB write) is causing a buffer to fill and is throttling the read from the sequential file? If you remove the Oracle stage from the flow and write to a dataset, how does it behave? I'd also take a dump of the score out of your original code.