Oracle Connection Dead
Posted: Mon Dec 11, 2006 12:18 am
Hi Everyone,
I have one job in which i am reading the data from a Sybase table and after doing the transformations on the data i am putting them in an Oracle table. Now the requirement is like this that i need to read all the Sybase table records before processing them, this is because the processing of each record depends on the last record that was processed. I mean suppose i have 2 records A & B, now the processing of record B depends on the outcome of record A's processing.
In the Sybase table i have nearly 10 million records and i need to read all these records before processing any. For source Sybase table i am using the ODBC stage and it is taking nearly 2 hours to read all these records and by the time i read & process the records, the target Oracle table's connection becomes dead and the job aborts. So the job runs for 2 hours and then aborts.For Oracle target i am using an ODBC stage.
Teh log entries for the job are as follows:
-------------------------------------------------------------------------------
Occurred: 2:33:56 AM On date: 12/11/2006 Type: Control
Event: Starting Job Build_FO_Load_job. (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: Environment variable settings: (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: Parallel job initiated (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: Ascential DataStage(tm) Enterprise Edition 7.5.1A (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: The open files limit is 2000; raising to 2147483647.
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: orchgeneral: loaded (...)
Occurred: 2:33:58 AM On date: 12/11/2006 Type: Info
Event: main_program: APT configuration file: /opt/Ascential/DataStage/Configurations/default.apt (...)
Occurred: 2:34:05 AM On date: 12/11/2006 Type: Warning
Event: footnotesttext_od: When checking operator: When binding output interface field "company_cd" to field "company_cd": Converting a nullable source to a non-nullable result; (...)
Occurred: 2:34:05 AM On date: 12/11/2006 Type: Warning
Event: ProcessError_FOData_xf: When checking operator: When binding input interface field "sequence_nr" to field "sequence_nr": Implicit conversion from source type "string" to result type "string[6]": Possi (...)
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Warning
Event: ProcessError_FOData_xf: When checking operator: When binding input interface field "data_desc" to field "data_desc": Implicit conversion from source type "string" to result type "string[max=1024]": Po (...)
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: APT_CombinedOperatorController(1),0: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: BuildRTTagcount_od,0: Failure during execution of operator logic.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Info
Event: BuildRTTagcount_od,0: Input 0 consumed 1 records.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: APT_CombinedOperatorController(1),0: Fatal Error: {0}
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: node_node1: Player 12 terminated unexpectedly.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Fatal
Event: main_program: Unexpected exit status 1 (...)
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Fatal
Event: main_program: Step execution finished with status = FAILED.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Info
Event: main_program: Startup time, 0:19; production run time, 2:40:28.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Control
Event: Job Build_FO_Load_job aborted.
Occurred: 5:14:46 AM On date: 12/11/2006 Type: RunJob
Event: (Build_Main_Seq) <- Build_FO_Load_job: Job under control finished.
End of report.
------------------------------------------------------------------------------
If anyone is having any information about this problem please help me out in this.
Thanks a lot for all the help you have been providing.
Thanks in advance,
I have one job in which i am reading the data from a Sybase table and after doing the transformations on the data i am putting them in an Oracle table. Now the requirement is like this that i need to read all the Sybase table records before processing them, this is because the processing of each record depends on the last record that was processed. I mean suppose i have 2 records A & B, now the processing of record B depends on the outcome of record A's processing.
In the Sybase table i have nearly 10 million records and i need to read all these records before processing any. For source Sybase table i am using the ODBC stage and it is taking nearly 2 hours to read all these records and by the time i read & process the records, the target Oracle table's connection becomes dead and the job aborts. So the job runs for 2 hours and then aborts.For Oracle target i am using an ODBC stage.
Teh log entries for the job are as follows:
-------------------------------------------------------------------------------
Occurred: 2:33:56 AM On date: 12/11/2006 Type: Control
Event: Starting Job Build_FO_Load_job. (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: Environment variable settings: (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: Parallel job initiated (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: Ascential DataStage(tm) Enterprise Edition 7.5.1A (...)
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: The open files limit is 2000; raising to 2147483647.
Occurred: 2:33:57 AM On date: 12/11/2006 Type: Info
Event: main_program: orchgeneral: loaded (...)
Occurred: 2:33:58 AM On date: 12/11/2006 Type: Info
Event: main_program: APT configuration file: /opt/Ascential/DataStage/Configurations/default.apt (...)
Occurred: 2:34:05 AM On date: 12/11/2006 Type: Warning
Event: footnotesttext_od: When checking operator: When binding output interface field "company_cd" to field "company_cd": Converting a nullable source to a non-nullable result; (...)
Occurred: 2:34:05 AM On date: 12/11/2006 Type: Warning
Event: ProcessError_FOData_xf: When checking operator: When binding input interface field "sequence_nr" to field "sequence_nr": Implicit conversion from source type "string" to result type "string[6]": Possi (...)
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Warning
Event: ProcessError_FOData_xf: When checking operator: When binding input interface field "data_desc" to field "data_desc": Implicit conversion from source type "string" to result type "string[max=1024]": Po (...)
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: APT_CombinedOperatorController(1),0: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: BuildRTTagcount_od,0: Failure during execution of operator logic.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Info
Event: BuildRTTagcount_od,0: Input 0 consumed 1 records.
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: APT_CombinedOperatorController(1),0: Fatal Error: {0}
Occurred: 5:14:40 AM On date: 12/11/2006 Type: Fatal
Event: node_node1: Player 12 terminated unexpectedly.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Fatal
Event: main_program: Unexpected exit status 1 (...)
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Fatal
Event: main_program: Step execution finished with status = FAILED.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Info
Event: main_program: Startup time, 0:19; production run time, 2:40:28.
Occurred: 5:14:45 AM On date: 12/11/2006 Type: Control
Event: Job Build_FO_Load_job aborted.
Occurred: 5:14:46 AM On date: 12/11/2006 Type: RunJob
Event: (Build_Main_Seq) <- Build_FO_Load_job: Job under control finished.
End of report.
------------------------------------------------------------------------------
If anyone is having any information about this problem please help me out in this.
Thanks a lot for all the help you have been providing.
Thanks in advance,