DSXchange

Now that you've clarified things a bit, all that takes is something to check for the last day and a custom trigger expression to bypass the second job when it comes up true. And a Sequencer to stitch the flow back together again.

As Paul noted, you can do it all in one SQL although it will basically emulate exactly what you are doing in the job. Rather quickly and off the top of my head, something like: select e.EMPID, e.EMPNAME, e.DEPTCD, e.SAL, da.AVG_SAL from EMPLOYEE e, (select DEPTCD, AVG(SAL) as AVG_SAL from EMPLOYEE g...

Also note that what you posted is not a script but rather the response file (probably leveraged via a 'here document' approach) for the FTP session itself. Any script, such as you'll see when you do the search Paul mentioned, will need to include the actual FTP command itself. Also realize that to l...

However, in my opionion there's nothing wrong with how you've done it... I assume it is working for you, yes?

You certainly can. Have you been through Chapter 6 of the Connectivity Guide for Oracle Databases pdf? There's lots of information there on using PL/SQL in the connector and the requirements around that topic.

Agreed so off you go to your own post but linked back to the original. Please come back and let us know your resolution.

Once we did force compile, we cannot able to compile any parallel job with Transformer. Earlier it was working. So this was something that worked in the past but then stopped working? The obvious next question is what changed? I can't imagine that this is something that simply doing a 'force compil...

Normally I would say by setting the 'Transaction record count' property to zero but according to this post that option may not work, or at least doesn't seem to be working for them. It also mentions the $APT variables that can control commits for Oracle. You may need to experiment and/or worst case ...

Have you verified that you are running a supported compiler for your DataStage version and OS / platform?

It's also important to let us know if you have this problem with every Parallel job with a transformer in it or if others work and this error is only for this particular job.

You need to ensure no intermediate commits occur. What database?

Ack, nevermind - it's in the subject. What exact version of DataStage?

Hmmm... rkashyap, have you used it in that manner? The DTS or Distributed Transaction Stage is all about a "two-phase commit" and is generally used for the latter scenario I outlined. That along with the discussion of the End Of Wave Generator or a bookmark line (etc.) to help control rest...

Clarify something. Do you want ALL of the deletes to succeed and ALL of the inserts to succeed before you commit anything? Or are they individual transactions, matching pairs, meaning one delete then an insert for the same key such that once complete those individual transactions can be committed?

Have you tried setting up a reject link from the connector?

Oracle doesn't use ODBC. Can you connect to Oracle (or at least tnsping your instance) from the ETL server? Is your tnsnames.ora file setup correctly? Suggest you verify your native sqlnet connectivity first.

Just want to clarify something. The output you are getting is correct, dot and all. If you want the dot gone for some reason, then use the solution srinivasu posted to remove it from the string.