OCI Stage "Abnormal Termination"

dadabhoy · Post by **dadabhoy** » Wed Apr 16, 2008 10:03 am

I have a job that looks something like this:


         OCI_LU
            |
            |
            |
          HASH
            |
                      
            |
OCI_IN-----TFM----OCI_OUT

Its basicly getting some records from a table (OCI_IN), performing a lookup against a hash file (populated from another database table) and then inserting the records into the final table (OCI_OUT).

The OCI_OUT is set to insert rows without clearing.
The job runs through and then aborts with an "Abnormal Termination" but does however insert the required rows into the target table.

Dont undesrstand why the "Abnormal Termination" occurs.

Ive included log entries for the termination and pre and post termination log entries.

Pre Warning Log
X0001_Create_Initial_PL_Table..IQ_DIM_COMPANY: INSERT INTO PL_ENTRY_HISTORY(PL_ENTRY_HISTORY_SEQ_NO, PL_FILENAME, PL_FILE_QTR, PL_FILE_YEAR, PL_ACTIVE, PL_KEY, COMPANY_CODE, COMPANY_DESCRIPTION, TREATY_CATEGORY_CODE, TREATY_CATEGORY_DESCRIPTION, CORPORATE_ADJUSTMENT, TREATY_NATURE, TREATY_NATURE_DESCRIPTION, COUNTRY_CODE_ISO, COUNTRY_DESCRIPTION, FAS_CODE, FAS_DESCRIPTION, CLASS_OF_BUSINESS_CODE, CLASS_OF_BUSINESS_L3_CODE, CLASS_OF_BUSINESS_DESCRIPTION, RESULT_RESP_ID_ECON, RESULT_RESP_DESCRIPTION, TREATY_NUMBER, TREATY_DESCRIPTION, TREATY_SECTION, TREATY_SECTION_DESCRIPTION, STATUS_CODE, STATUS_DESCRIPTION, ACTUARIAL_SEGMENT, ACTUARIAL_SEGMENT_DESCRIPTION, PERIOD_START_DATE, PERIOD_END_DATE, INVOLVEMENT, INVOLVEMENT_DESCRIPTION, UNDERWRITING_YEAR, OCCURRENCE_YEAR, ACCOUNTING_YEAR, ENTRY_CODE_4, ENTRY_CODE_5, ENTRY_DESCRIPTION, GL_ACCOUNT, GL_ACCOUNT_DESCRIPTION, ESTIMATE_CODE, ESTIMATE_DESCRIPTION, REPORTING_ENTRY_CODE, PREV_CURR_YEAR, TRANSACTION_CURRENCY, ACCOUNTING_QUARTER, TC, GBP) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50)

Warning Log
Attempting to Cleanup after ABORT raised in stage X0001_Create_Initial_PL_Table..Transformer_163

Post Warning Log
Job X0001_Create_Initial_PL_Table aborted.

chulett · Post by **chulett** » Wed Apr 16, 2008 11:21 am

Reset the aborted job and let us know the contents of any 'From previous run...' log entry.

dadabhoy · Post by **dadabhoy** » Wed Apr 16, 2008 11:44 am

Unfortunately, I had since recompiled the job as part of the a multiple job recomplile, so i cant reset it.

In replicating the issue I emptied (using "delete from.." and then "commit") the target table and run the job again but this time got a different issue in that no rows were loaded and the job aborted. The following log messages generated :

Info
B001_Load_PL_History..IQ_DIM_COMPANY: INSERT INTO PL_ENTRY_HISTORY(PL_ENTRY_HISTORY_SEQ_NO, PL_FILENAME, PL_FILE_QTR, PL_FILE_YEAR, PL_ACTIVE, PL_KEY, COMPANY_CODE, COMPANY_DESCRIPTION, TREATY_CATEGORY_CODE, TREATY_CATEGORY_DESCRIPTION, CORPORATE_ADJUSTMENT, TREATY_NATURE, TREATY_NATURE_DESCRIPTION, COUNTRY_CODE_ISO, COUNTRY_DESCRIPTION, FAS_CODE, FAS_DESCRIPTION, CLASS_OF_BUSINESS_CODE, CLASS_OF_BUSINESS_L3_CODE, CLASS_OF_BUSINESS_DESCRIPTION, RESULT_RESP_ID_ECON, RESULT_RESP_DESCRIPTION, TREATY_NUMBER, TREATY_DESCRIPTION, TREATY_SECTION, TREATY_SECTION_DESCRIPTION, STATUS_CODE, STATUS_DESCRIPTION, ACTUARIAL_SEGMENT, ACTUARIAL_SEGMENT_DESCRIPTION, PERIOD_START_DATE, PERIOD_END_DATE, INVOLVEMENT, INVOLVEMENT_DESCRIPTION, UNDERWRITING_YEAR, OCCURRENCE_YEAR, ACCOUNTING_YEAR, ENTRY_CODE_4, ENTRY_CODE_5, ENTRY_DESCRIPTION, GL_ACCOUNT, GL_ACCOUNT_DESCRIPTION, ESTIMATE_CODE, ESTIMATE_DESCRIPTION, REPORTING_ENTRY_CODE, PREV_CURR_YEAR, TRANSACTION_CURRENCY, ACCOUNTING_QUARTER, TC, GBP) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50)

Warning
Attempting to Cleanup after ABORT raised in stage B001_Load_PL_History..IQ_DIM_COMPANY

Info
B001_Load_PL_History..Transformer_163: DSD.INTClose Active stage finishing.

Warning
DataStage Job 86 Phantom 5980
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage B001_Load_PL_History..IQ_DIM_COMPANY
DataStage Phantom Aborting with @ABORT.CODE = 3

Info
Job B001_Load_PL_History aborted.

I believe the access violation is reffereing to the target table, which I know by fact that I or no one else has a lock on the table.

Have reset the job but no "From previous run" entries.

Any idea's

John Smith · Post by **John Smith** » Wed Apr 16, 2008 8:15 pm

In your logs do you see any SQL errors?

chulett · Post by **chulett** » Wed Apr 16, 2008 9:51 pm

No, the 'access violation' has nothing to do with the database per se or any lock on the table, that would generate a normal ORA error. It's typically a code issue, a bug in the Oracle drivers / client code or DataStage. Let's start with Oracle.

What Oracle versions are at work here - the database itself and the Oracle client installed on your DataStage server? As precise as you can get, for example 9.2.0.5 for the client, 10.2.0.1 for the database.

dadabhoy · Post by **dadabhoy** » Thu Apr 17, 2008 2:30 am

As, I understand it, logging into SQL+ from the datastage server I get

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 17 09:19:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>

Im not a dba but I translate the above to mean the client is 9.2.0.1.0 and the database is 9.2.0.4.0. I have confirmed the database version using :

Code: Select all

select * from product_component_version;

Wasnt sure how else I could confirm the client install version.

Do you belive the subtle difference in version could be causing this problem ?

dadabhoy · Post by **dadabhoy** » Thu Apr 17, 2008 2:34 am

John, I dont see any sql errors in the logs.

chulett · Post by **chulett** » Thu Apr 17, 2008 6:25 am

dadabhoy wrote:Do you belive the subtle difference in version could be causing this problem ?

Yes, indeedy, that is your primary suspect right now in my mind. I've been down this path before, 9.2.0.1 is notoriously buggy. It can work fine for the vast majority of your jobs and then find one certain something in a query it doesn't like and blow sky high.

Have your DBA install the 9.2.0.4 client. Or better yet, get closer to the latest release of 9iR2 - I think it's more like 9.2.0.8 now... but that would mean both client and database would need to be upgraded and that may be more of an undertaking then they are willing to commit to. Getting just the client updated should be a non-issue, however.

dadabhoy · Post by **dadabhoy** » Thu Apr 17, 2008 1:29 pm

:D Upgraded client and it worked perfect ! Thanks for the help on this one chulett !

This ones getting marked resolved !