DSXchange

Using HASH is better off... you are gaurenteed of perfect results. You have to sort the input on all input links (if not previously sorted) in the same order as your join key, but partition on one/two columns that you think would provide a good partitioning of your data and which will also group you...

Hi Write a post-job DataStage basic subroutine that counts the number of records from the source link and the database write link. Then match the counts and if they do not match conclude that there is some error. I don't think reject capture will help you in this regard because the records are not ...

Hi, I have developed a parallel job in datastage that loads the target table and assigns an oracle sequence for every record inserted. During update, it only updated the record without incrementing the surrogate key sequence. Earlier the oracle sequence was created with a cache=20 but then the DBA ...

I loaded a sequential data set into a db2 table. Three of the records did not load since a date field had invalid data ('**********'). I have corrected the date problem. My concern is that the job did not abort, but had a successful completion. The director did say that the job had a binary load er...

Hi all, I want to get the next sequence for x records coming from the I/P source . I need to get X new Sequence values from oracle. But for a given record I have to Put in two tables,same set of records with the same sequence number. this is easily possible in Server by using oracle as refernce lin...

Hi, I have a DataStage Server routine which i need to call from a PX job. I tried using everything including using transform for the same but nothing worked. can there be a round about way to call a routine from the PX job. Currently i am using a server shared container for the purpose but would li...

Hi I am running a job which updates a table using the below sql. Sql is from "Generated sql" in the Oracle8i oci stage Update table test a set a.date1 =sysdate where a.pk1=:1 and a.Pk2=:2 Paramters which are both composite primary key: :1 has null value from the input stage(not always nul...

Hi, I have to load data from a range partitioned oracle table, partitioned by date on weely basis. My target table is a non partitioned oracle table. I need to know what oracle stage settings do i need to make to load the data from this partitioned table successfully. The source table contains arou...

An Oracle index becomes UNUSABLE when you load data, but the index is not updated with the new records. Here is a sequence of events... 1. Disable the PK (with a KEEP clause), this keeps the associated INDEX 2. When fresh data is loaded, the index still is in UNUSABLE state 3. So, we need to rebuild...

[quote="aruna_peri"]One row is missing in every load. If I load 101 rows then only 100 rows are loaded. The other one record is neither in error log/rejected file nor in Final Table. ( Initially thought the first row of the file is treated as header line so its not reading but I have not m...

We have ben trying to come up with a solution to trap rejects... our requirements were... 1. Abort the job on 1st record reject, but trap the rejected record before aborting (on couple of jobs) 2. Trap all types of rejects (not only NOT NULLs, but even referential etc) but don't abort the job (on co...

You are right, Ray, I should have specified that the APTs work on parallel jobs (but, chaitanyavm, I thought was referring to both types of jobs) and also, vmcburney had already given a way of dealing with server jobs commit frequency (transaction size), so summarizing...... For server jobs, Use Tra...

Hi This is my first post. We have interfaces which have multiple targets like MQ, Database (Oracle,DB2 etc). Our interfaces (datastage load jobs) read multiple files one per each target and write to the MQ, Database or file. There are several instances when there should not be duplicate processing ...

ray.wurlod wrote:Not so easy where the sub-query is a UNION or INTERSECTION etc.

I think Raju was specifically asking for count(*) "coming from specific join condition" and it DOES work for any kind of join even if one of the tables within the main select is a subquery itself!!

You can give a count(*) in the original statement itself, instead of in a sub-query