2 calls to Oracle function in PX
Moderators: chulett, rschirm, roy
2 calls to Oracle function in PX
I have a function call to oracle using a sparse lookup.
The function creates an entry in an "audit trail" table with batch_id and all that info and returns the batch_id.
My default config.apt has 2 nodes. So every time the function gets called it is creating 2 entries for a batch_id in the audit trail table while it should be only one.
How do I control that?
Thanks!
The function creates an entry in an "audit trail" table with batch_id and all that info and returns the batch_id.
My default config.apt has 2 nodes. So every time the function gets called it is creating 2 entries for a batch_id in the audit trail table while it should be only one.
How do I control that?
Thanks!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You do need to re-think your design. Think about it. It's fundamental parallel processing. You certainly would expect a process (i.e. function) to run on every node. Your function manufacturers a data row... it will manufacture a data row on every node.
The reason you don't see duplicates when you are inserting is because the data is partitioned. Each node process only inserts a subset of the total data.
Two quick options:
1) If the stage supports it, run it in sequential mode.
2) Pass the partition number to the function and only manufacture a row for one partition (i.e. one node).
Mike
The reason you don't see duplicates when you are inserting is because the data is partitioned. Each node process only inserts a subset of the total data.
Two quick options:
1) If the stage supports it, run it in sequential mode.
2) Pass the partition number to the function and only manufacture a row for one partition (i.e. one node).
Mike
Nice suggestions!
When we use a oracle function call and join it using a sparse lookup it is by default set to "Sequential mode". Now, if it is set to "sequential mode" how is it creating 2 records for my functionality. My default apt config files has 2 nodes running.
So, going forward if I run this in sequential mode and pass a PARTITIONNUM as an input parameter to the ORA function to manufacture a single row, I suspect it still will return/manufacutre 2 rows due the current run logic explained above.
Thanks!
When we use a oracle function call and join it using a sparse lookup it is by default set to "Sequential mode". Now, if it is set to "sequential mode" how is it creating 2 records for my functionality. My default apt config files has 2 nodes running.
So, going forward if I run this in sequential mode and pass a PARTITIONNUM as an input parameter to the ORA function to manufacture a single row, I suspect it still will return/manufacutre 2 rows due the current run logic explained above.
Thanks!
Check the execution mode of the lookup stage (not the Oracle stage).
I've also made the assumption that there is only 1 row driving the lookup on the stream input.
Regardless of whether the execution mode of the lookup stage is sequential or parallel, adding logic inside your Oracle function that makes it conditional on partition number will work as long as there is only a single driving row on the stream input.
Mike
I've also made the assumption that there is only 1 row driving the lookup on the stream input.
Regardless of whether the execution mode of the lookup stage is sequential or parallel, adding logic inside your Oracle function that makes it conditional on partition number will work as long as there is only a single driving row on the stream input.
Mike
Cool!
So that would mean once the PARTITIONNUM is passed. Manufacture a record only for the first partition number.
Partition numbers usually begin with 0. If 2 partitions then 0,1;if 4 then 0,1,2,3.
So I would manufacture a record only for PARTITIONNUM= 0.
Does that make sense or is that too static if partition numbers could differ?
Thanks!
So that would mean once the PARTITIONNUM is passed. Manufacture a record only for the first partition number.
Partition numbers usually begin with 0. If 2 partitions then 0,1;if 4 then 0,1,2,3.
So I would manufacture a record only for PARTITIONNUM= 0.
Does that make sense or is that too static if partition numbers could differ?
Thanks!
whoops! stuck one last time..
I'm using the below in a ORA Ent stage using a sparse lookup.
I know in transformer stage we can use a system variable @PARTITIONNUM.
How could I use that system variable in my ORA functional query call before.
1. select FNC_UTL_logfilestart(@PARTITIONNUM,sysdate,'Loading for F_FX_RATE','Loads into fact table F_FX_RATE for type T','dsgstadm','GST_F_FX_RATE','dbaxsna915','GLBSALES.F0015') as CRT_LOG_ID from dual?
That doesn't work!!
2. ??
I'm using the below in a ORA Ent stage using a sparse lookup.
I know in transformer stage we can use a system variable @PARTITIONNUM.
How could I use that system variable in my ORA functional query call before.
1. select FNC_UTL_logfilestart(@PARTITIONNUM,sysdate,'Loading for F_FX_RATE','Loads into fact table F_FX_RATE for type T','dsgstadm','GST_F_FX_RATE','dbaxsna915','GLBSALES.F0015') as CRT_LOG_ID from dual?
That doesn't work!!
2. ??