Oracle 9i Stage issues

yaminids · Post by **yaminids** » Thu Oct 20, 2005 11:30 pm

Hello friends,

I am getting a strange problem in one of the jobs of our project. The job has 9 steps (each step loads data into one table).

Step 1:
TABLE ==>TRANSFORMER==>ORA9i Stage==>SquentialFileStage

At the end of each step, we are updating the "CurrentExecuteDate" by using the following SQL:

select to_char((to_date(#CurrentExecuteDate#, 'yyyymmdd') + 1), 'yyyymmdd') from dual

The problem is sometimes the last step (updating the date) is failing with the error
"MERCHDMART1..TRANS1: ORA-12535: TNS:operation timed out"

Can anyone help me with this?

Thanks a lot in advance
Yamini

ray.wurlod · Post by **ray.wurlod** » Fri Oct 21, 2005 2:21 am

The SQL you posted won't update anything.

Try the oerr command (oerr ORA 12535) to get recommendations for this particular error.

I suspect it's something to do with locks on the table; you may still have a lock from the earlier cursor not released when you come to do the update. If you do the update in a separate job (controlled by the same controller) this may ameliorate the problem.

srinagesh · Post by **srinagesh** » Fri Oct 21, 2005 5:21 am

I think "DUAL" is the problem here.

Dual is not a proper Oracle table, so to speak..
I have never tried using "Dual" in my sql query.. but chances are that Datastage will not have any metadata for that table and it might fail.

chulett · Post by **chulett** » Fri Oct 21, 2005 7:16 am

No, dual is a perfectly valid 'pseudo' table and can be used in the manner that the OP showed. However...

I don't understand what 'at the end of each step' means and where exactly this SQL is being executed. And as Ray notes, by itself this SQL doesn't update anything. This seems like an awfully clunky way to accomplish something, even if I'm not quite sure what that something is yet.

Can you explain what it is you are trying to accomplish rather than how you are trying to accomplish it? That would help people here to be able to provide a better, more 'DataStage-centric' methodology for you.

lstsaur · Post by **lstsaur** » Fri Oct 21, 2005 11:41 am

Hi,
This is getting ridiculous. How can you claim that you have coded a "select to_char((to_date(#CurrentExecuteDate#, 'yyyymmdd') + 1), 'yyyymmdd') from dual" SQL to update the table???

How can you not know that "DUAL" is a pseudeo table and never used it?

Some people really need to take the Oracle SQL 101 class!

yaminids · Post by **yaminids** » Fri Oct 21, 2005 12:06 pm

Hello friends,

I might have lead you guys in a wrong way. By update I mean, at the end of each step (ORA9i Stage==>SquentialFileStage) I am executing the query to get a 'Date' and insert that value into the Sequential file. My intension is when I run the job the next time it extracts data based on the data present in the Sequential file
Am I doing anything wrong?

Thanks a lot in advance
Yamini

By the way, my dear friend lstsaur, I know what 'Dual' is

ketfos · Post by **ketfos** » Fri Oct 21, 2005 3:06 pm

Hi,
How much time does normally the job takes to complete?

2. Have you tried ping command with a)hostname b)ip address.
This will make it sure that DNS is not screwing up. Sometime it works with ip address but fails with host name.

Ketfos