Oracle sequence issue

rohitchattar · Post by **rohitchattar** » Sat Jan 26, 2008 11:48 pm

Hi,

We have migrated our datastage jobs from one machine to another machine.all the jobs are working fine but the jobs where we are using ORACLE SEQUENCE that job is hanging and finally aborting.

Can you please suggest,what needs to be done.

ray.wurlod · Post by **ray.wurlod** » Sun Jan 27, 2008 12:18 am

1. Demonstrate that you are using the sequence correctly.
2. Prove that the Oracle sequence is not locked as you are trying to use it (you may need to involve your DBA in this).
3. Monitor the SQL that is arriving in Oracle from DataStage (ditto).

chulett · Post by **chulett** » Sun Jan 27, 2008 12:29 am

Post the abort. And to add what Ray asked, how are you using these Sequences - embedded in the target sql, as a reference lookup, what?

rohitchattar · Post by **rohitchattar** » Sun Jan 27, 2008 12:37 am

SELECT BDW_CUST_SID2_SQ.NEXTVAL FROM DUAL

we are calling the above select statement in the oracle OCi stage.We are able to view the data from oracle stage where we are using this sequence.

For any new record ,we are giving the new sequence no from the above statement. But the same job is running fine in our existing box.

chulett · Post by **chulett** » Sun Jan 27, 2008 12:48 am

Post the abort.

rohitchattar · Post by **rohitchattar** » Sun Jan 27, 2008 1:19 am

Job name:BDW_Load_Customers
Message:
BDW_Load_Customers: Set NLS locale to US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH
Project:Premium_Service_DM (etl7.data.corp.sp1)
Job name:BDW_Load_Customers
BDW_Load_Customers..t1xfmLoadSIDS: DSD.StageRun Active stage starting, tracemode = 0.
BDW_Load_Customers..t1xfmLoadSIDS: Set NLS locale to US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH
BDW_Load_Customers..Customers_File: Using project default NLS map ISO8859-1
BDW_Load_Customers..Customer_Lookup: Using NLS map ISO8859-1
BDW_Load_Customers..Customer_Lookup: SELECT BDW_CUSTOMERS_DIM.CUST_SID2, BDW_CUSTOMERS_DIM.CUST_SID1, BDW_CUSTOMERS_DIM.CUST_ID, BDW_CUSTOMERS_DIM.CUST_FIRST_NAME, BDW_CUSTOMERS_DIM.CUST_MIDDLE_NAME, BDW_CUSTOMERS_DIM.CUST_LAST_NAME, BDW_CUSTOMERS_DIM.CUST_YUID, BDW_CUSTOMERS_DIM.CUST_LCOOKIE, BDW_CUSTOMERS_DIM.CUST_RCOOKIE, BDW_CUSTOMERS_DIM.CUST_STREET, BDW_CUSTOMERS_DIM.CUST_CITY, BDW_CUSTOMERS_DIM.CUST_STATE, BDW_CUSTOMERS_DIM.CUST_COUNTRY, BDW_CUSTOMERS_DIM.CUST_ZIPCODE, BDW_CUSTOMERS_DIM.IS_CURRENT, BDW_CUSTOMERS_DIM.CUST_EMAIL FROM BDW_CUSTOMERS_DIM WHERE BDW_CUSTOMERS_DIM.CUST_ID = :3 AND IS_CURRENT = 'Y'
BDW_Load_Customers..Process_Insert_Update: DSD.INTOpen Active stage starting, tracemode = 0.
BDW_Load_Customers..Gen_Cust_SID2: Using NLS map ISO8859-1
BDW_Load_Customers..Gen_Cust_SID2: SELECT BDW_CUST_SID2_SQ.NEXTVAL FROM DUAL
Job BDW_Load_Customers aborted.
(BDW_Driver) <- BDW_Load_Customers: Job under control finished.

chulett · Post by **chulett** » Sun Jan 27, 2008 9:07 am

Reset the aborted job and let us know the contents of the 'From previous run...' log entry that (hopefully) shows up.

rohitchattar · Post by **rohitchattar** » Sun Jan 27, 2008 9:51 am

(BDW_Driver) <- BDW_Load_Customers: Job under control finished.
From previous run
DataStage Job 17 Phantom 22194
*** glibc detected *** free(): invalid pointer: 0xfffd2930 ***
Abnormal termination of DataStage.
Resetting Job BDW_Load_Customers.
Fault type is 6. Layer type is BASIC run machine.
Fault occurred in BASIC program DSP.Open at address 574.
Resetting stage BDW_Load_Customers..t1xfmLoadSIDS.
BDW_Load_Customers..Customers_File: Using project default NLS map ISO8859-1
Resetting stage BDW_Load_Customers..Process_Insert_Update.
Project:Premium_Service_DM (etl7.data.corp.sp1)
Finished Resetting stage BDW_Load_Customers..Process_New_Customers.
Finished Resetting stage BDW_Load_Customers..t1xfmLoadSIDS.
Job BDW_Load_Customers has been reset.

Please let me know what needs to be done to solve the issue.

chulett · Post by **chulett** » Sun Jan 27, 2008 10:06 am

Wish I knew. Invalid pointers in glibc? You'll probably need to involve your official Support provider at this point, but let's see...

What's different about this 'new machine' than the old one? Obviously something or we wouldn't be here having this conversation. I'm wondering about your Oracle client... check the specific version of the Oracle client installed or ask your DBA. Ensure that you are running the exact same version of the client on this new machine as the old one. Hard to believe, I know, but certain versions are known to be buggier than others. I've spent days chasing down the odd 'BASIC run machine' errors (you can search the forums for that phrase and find my postings) only to find out the Oracle DBA needed to upgrade the client.

Let's start there. If that doesn't help, try a search for "Fault type is 6" to see if others have solved that aspect of the problem before.

ray.wurlod · Post by **ray.wurlod** » Sun Jan 27, 2008 1:06 pm

Could this be the 64-bit library being found instead of the 32-bit library? Check the shared library search list environment variable on each of the systems. This is one of LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH, depending on what kind of UNIX you're using.

chulett · Post by **chulett** » Sun Jan 27, 2008 3:03 pm

Probably not, as then nothing Oracle would work. I'm assuming this is one of those odd circumstances like I had where 99.9% of things worked 'fine' and then one job, that didn't look any different than any other other job, would blow.

Well worth checking, of course, though.

rohitchattar · Post by **rohitchattar** » Sun Jan 27, 2008 10:40 pm

surprisingly the same job is working fine in our old machine .but when we are trying to run in our new machine it's hanging. what i found is all the job where we are using oracle sequence through oracle OCi stage is giving the prob.

chulett · Post by **chulett** » Sun Jan 27, 2008 11:04 pm

And?? Did you check anything we mentioned?

ray.wurlod · Post by **ray.wurlod** » Sun Jan 27, 2008 11:57 pm

Track down what's different. Something is.

lstsaur · Post by **lstsaur** » Mon Jan 28, 2008 12:17 am

Check with whoever applied the patch, "glibc" is part of software package, for Oracle on the other machine that jobs aborted.