DSXchange

I attended the Server to EE transition Class several months back, at Dallas. I think I did thank the organizers and our instructor on a class well conducted. I was reading the handouts again a few days back, and was in full appreciation for the structure, quality and clarity in Ray's approach to tra...

I suspect your session is still open with the database and is trying to insert records and hence encountering the ORA-12899 error. Killing the job from Director always doesn't terminate the connection "immediately" with Oracle. Check with your DBAs and try to kill your open session.

I am sure you looked at ORA-01722 invalid number already. Just in case you didn't: Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in ari...

could it be this part "ERDAT_SLS_ORD_CRTE_DT=:3" ? try ERDAT_SLS_ORD_CRTE_DT=TO_date(:3, 'YYYY-MM-DD')

Hi All, Pls clarify me regarding how to call an oracle sequence inside the data stage? one way would be to use userdefined sql while you either do the select or insert in the Oracle stage .. i.e. use schema_name.SEQuence_name.nextval an alternate way would be to do the same via a OCI lookup stage. ...

Why don't you just use the sequence when inserting the rows with user-defined SQL? For example INSERT INTO table(col1, col2, col3) VALUES (tableseq.NEXTVAL, :1, :2); ray's method above is the more efficient way to do it... We normally do it this way in server jobs too and found it to be way faster ...

[quote="chanthou_2000"]the processing after the OCI is exactly the same. as i mentionned, i execute an explain plan and i tested the 2 queries oustide of Datastage, and i have the same conclusion. If you tested both queries outside of datastage -- and if you returned ALL (not just the FIRS...

Chanthou, After the OCI stage, are both jobs doing a similar kind of processing ? i.e. is one writing to a text file while the other is going through transformations ? One way to find out which query is really faster (outside of datastage) is to do a "create table select * from"... or do a...

kduke wrote:The MINUS command is also powerful.

Use the Minus command with caution. If any of the columns has NULLs, then the minus command willnot work.

Better split the query into DataStage manageable chunks and design the job in DataStage. To add to what the experts have said here, here is a how you could do it in datastage manageable chunks: Take the distinct values of Tab2. col1,col2,col3 and stage it in a hashfile HF2 Take the distinct values ...

dsedi,

can you try with this to eliminate the first line -- sed -e '1d' filename ?

One way you could avoid re-processing all the rows again is to store the records in a flat file before you insert into the Oracle table. This of course gets complicated if you have multiple targets in a sequential processing situation.

Harithay, Very few will disagree with Ken Bland's observation to your latest post on the dsxchange. There are a bunch of very experienced and knowledeable people here who help solve lot of problems others face. But it doesn't serve anybody any good if one repeatedly asks questions without doing any ...

I don't know if its true now, but when I worked with OWB (about 5 yrs back) it only worked with Oracle databases. It would just generate Pl/Sql code and run it on the oracle engine.

Try using DS.TOOLS from the Administrator client.

Bibhu