Hello everyone from Spain,
I'm a new DataStage user and I have a question:
I have an Oracle table with the following structure and I want to use an 'Aggregator' to gather its data, according to the
type of access, in another table.
The structure of the first table is:
- Application(PK)
- User(PK)
- Date(PK)
- Time(PK)
- Access type: Insert, Delete, Update, Select
The structure of the resulting table is:
- Application (PK)
- User(PK)
- Date(PK)
- Time(PK)
- Number of Inserts
- Number of Deletes
- Number of Selects
- Number of Updates
How would you do this? Is it possible to do it with the 'Aggregator'? Is there a better way or method to do it?
Thaks
Aggregator
Moderators: chulett, rschirm, roy
Hi,
There are several possibilities.
That I prefer is to use, in the transformer, the variables.
- create for each field, a variable and each time the condition is validated, to add 1.
You also have the possibility of creating a flow for each fields (use of the constraint), by using the variables system such as @INROWNUM. In another transformer, you will then reformat your flow.
By using a aggregator, it will be necessary that you separate each flow according to the action (insert...) then for each flow, do the aggregation (in the aggregator) before reformater your flow in another transform.
Personally, I find the first proposal easier.
Hope this Help you,
Pey
There are several possibilities.
That I prefer is to use, in the transformer, the variables.
- create for each field, a variable and each time the condition is validated, to add 1.
You also have the possibility of creating a flow for each fields (use of the constraint), by using the variables system such as @INROWNUM. In another transformer, you will then reformat your flow.
By using a aggregator, it will be necessary that you separate each flow according to the action (insert...) then for each flow, do the aggregation (in the aggregator) before reformater your flow in another transform.
Personally, I find the first proposal easier.
Hope this Help you,
Pey
Greetings! Off the top of my head, you could do this in a couple of different ways:
With the Aggregator, I would build a transform and in it turn the access type field into your four different types - one for insert counting, one for updates, etc. Based on the field, set the value of the appropriate count field to a 1 and all others to 0. Then run them thru the aggregator and sum them.
You could also do this directly from Oracle in the original selection query... but then you really wouldn't 'need' DataStage. [:)]
-craig
With the Aggregator, I would build a transform and in it turn the access type field into your four different types - one for insert counting, one for updates, etc. Based on the field, set the value of the appropriate count field to a 1 and all others to 0. Then run them thru the aggregator and sum them.
You could also do this directly from Oracle in the original selection query... but then you really wouldn't 'need' DataStage. [:)]
-craig
Hi,
Try This : generate 5 parallel OCI stages on the same table
1.select distinct the keys into a sequential file
2,3,4 select count(*) group by the pk where action is "I","S" etc.. each in a hash file
than run the seq looking up in every hash and when not finding a row there it'll be a 0 in the resulting table matching column
Try This : generate 5 parallel OCI stages on the same table
1.select distinct the keys into a sequential file
2,3,4 select count(*) group by the pk where action is "I","S" etc.. each in a hash file
than run the seq looking up in every hash and when not finding a row there it'll be a 0 in the resulting table matching column
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Believe it or not I think you should use an Oracle aggregation and a DataStage aggregation! An Oracle group by will do the counting, the DataStage aggregation will reformat it as one single row.
Running the major group by in the Oracle statement will create miniscule traffic across the network and it should be much faster than aggregating it in DataStage:
SELECT APPLICATION, USER, DATE, TIME, ACCESSTYPE, COUNT(*)
FROM WHATEVER
GROUP BY APPLICATION, USER, DATE, TIME
This will return four rows. There will be 6 fields going into the first transform, add 8 output fields consisting of the primary key and numberofinserts, numberofupdates, numberofselects, numberofdeletes. Use IF statements to check the ACCESSTYPES field and write the COUNT field to the corresponding numberof field. Send the four output rows to an aggregation stage that sums it into one row which you output to the results table.
Vincent McBurney
Data Integration Services
www.intramatix.com
Running the major group by in the Oracle statement will create miniscule traffic across the network and it should be much faster than aggregating it in DataStage:
SELECT APPLICATION, USER, DATE, TIME, ACCESSTYPE, COUNT(*)
FROM WHATEVER
GROUP BY APPLICATION, USER, DATE, TIME
This will return four rows. There will be 6 fields going into the first transform, add 8 output fields consisting of the primary key and numberofinserts, numberofupdates, numberofselects, numberofdeletes. Use IF statements to check the ACCESSTYPES field and write the COUNT field to the corresponding numberof field. Send the four output rows to an aggregation stage that sums it into one row which you output to the results table.
Vincent McBurney
Data Integration Services
www.intramatix.com
Ok, if you want to do it in Oracle - do the whole thing (as I mentioned above):
SELECT
APPLICATION, USER, DATE, TIME,
SUM(DECODE(ACCESSTYPE, 'I', 1, 0)) INSERT_COUNT,
SUM(DECODE(ACCESSTYPE, 'D', 1, 0)) DELETE_COUNT,
SUM(DECODE(ACCESSTYPE, 'U', 1, 0)) UPDATE_COUNT,
SUM(DECODE(ACCESSTYPE, 'S', 1, 0)) SELECT_COUNT
FROM WHATEVER
GROUP BY APPLICATION, USER, DATE, TIME;
[:D]
-craig
SELECT
APPLICATION, USER, DATE, TIME,
SUM(DECODE(ACCESSTYPE, 'I', 1, 0)) INSERT_COUNT,
SUM(DECODE(ACCESSTYPE, 'D', 1, 0)) DELETE_COUNT,
SUM(DECODE(ACCESSTYPE, 'U', 1, 0)) UPDATE_COUNT,
SUM(DECODE(ACCESSTYPE, 'S', 1, 0)) SELECT_COUNT
FROM WHATEVER
GROUP BY APPLICATION, USER, DATE, TIME;
[:D]
-craig