Last month, I wrote a blog entry about how Mike Stonebreaker had argued that Oracle was a legacy technology, and that for warehouses, at the least, column-oriented databases were the way forward.
As a follow-up to that post, here ‘s a post on AskTom about what Tom Kyte thinks about both the article that Stonebreaker wrote, and also column-oriented databases. As ever, it provides interesting reading.
Whenever I run a training course, one of the most common questions (or a variant of it) is "what should I store in attributes?" or "when should I use attributes?" There tends to be a tendency to store as much information as the workflow process might need in individual item attributes – if we were writing this in PL/SQL then there would be no hesitation to fetch the entire record from a table and store it either in a PL/SQL table or even in individual variables.
This is something that really should be resisted at all costs in a workflow process, unless there is a valid reason for storing it there. Every attribute that you create has to be stored separately within the database, and unless you are creating the attributes dynamically on the fly, then even if you don’t populate the attribute, then there will be a record in the item attribute values table. This is particularly important if you will be instantiating a large number of processes, since each process has a separate record for each attribute within the table. Additionally, if you need to fetch the value back out of the database, then this needs to call the Get API once for each attribute. If you store (for example) ten descriptive flexfield values from a table in separate workflow attributes, this requires the following database access just to store them
If the next activity requires five of these attributes, then you will need to call the API five times, which performs five select operations.
Alternatively, you could store the primary key within the workflow, which can then be used to run further select statements later. This will perform better than denormalizing the data into workflow attributes in every situation where more than one flexfield is required. The best approach is to have the primary key to the table stored within the item key, which means that your code will always have the key to the table. Failing this you should store the key in an attribute, and retrieve data values from the base table by fetching the key from workflow first, then going directly to the table. One of the best places to view this is within the seeded Order Management workflow, which use the header ID or the line ID as the item key for the workflow processes. In this case, you have the primary key to the table and then can access the base table to retrieve the data directly from the order header or order line table.
So here’s my guidelines on what you should store in workflow attributes
When you are creating attributes, you should always bear in mind the potential number of instances of the workflow will be stored in the database at the same time. Bear in mind that there isn’t too much that can be done to tune the tables or queries that the APIs run – this is standard code that if you change you won’t have any further support from Oracle, so the amount of data that is in the table should really be kept to a minimum. Also, if possible, get the customer tied into a sensible purging policy as early as possible – a customer I worked with in the past wanted to retain the data for 7 years! At their anticipated sales volumes, that meant that there will be somewhere in excess of 450 million records in the one table…
The previous post looked at how to launch a workflow from a database trigger, using the CreateProcess and StartProcess (or the LaunchProcess) APIs. However, I’m really against invoking workflows using the APIs any more – this relies on hard-coding in a number of places that can be potentially difficult to change. If you ever wanted your code to communicate with a different workflow, or with more than one workflow, or to run some additional PL/SQL, or just to be able to switch it off for a while, then you need to make changes to either the workflow or to the code that launches it. This logic could be embedded in a number of different places, some of which you might not be aware of – you should raise a business event instead, and then you can easily change the processing logic through event configuration.
But, I digress – this post is meant to be talking about raising a business event from a trigger. As we saw yesterday, there is a restriction which stops you calling Workflow code from a trigger, since a number of savepoints are issued by the workflow engine. If you try to raise a business event directly from a trigger, you end up in the same situation – there are savepoints that are issued which are not allowed. Even if you try to force the event manager to process the subscriptions in a deferred session, you still hit the same problem. So, how can you do this?
The savepoint issue is only an issue if you are trying to raise an event to the manager locally, i.e. via PL/SQL. If you raise the event externally via an AQ, then there is no savepoint issued, so you can do it from the trigger.
There are a few points to be careful of when doing this though:
1. In order to raise the event from a trigger, you will need to manually build up the WF_EVENT_T ADT to enqueue for processing. If you use the RAISE API to raise the event via PL/SQL, this is all done automatically for you (or you can do it manually if need be), but for raising the event externally this needs to be done by your code.
2. In order to process the event, you need to schedule an inbound agent listener to pick it up – if this is not scheduled to run periodically, then the event will never get raised to the event manager for processing. This means that you will have a delay in processing the business event between the trigger firing and the agent listener processing the queue. If you need the event to be fired immediately, then you cannot do it via raising an event, due to this latency.
3. The message is not enqueued onto the queue until the code that fired the trigger does the commit. This ensures transactional integrity throughout – if the controlling session rollsback, then the message is not visible on the queue. [This only applies if you are using the standard seeded queues, or custom queues where the message is only visible on commit. It is not advisable to use queues where the message can be processed before comitting]
So let’s look at exactly how to do it. Here’s a code sample:
10 BEGIN
20 v_event_name := 'xx.matt.searle.test.event';
30 v_parameter_t.name := 'ORG_ID';
40 v_parameter_t.value := v_org_id;
50 v_parameter_list.extend;
60 v_parameter_list(1) := v_parameter_t;
70 v_parameter_t.name := 'ITEM_NO';
80 v_parameter_t.value := v_segment1;
90 v_parameter_list.extend;
100 v_parameter_list(2) := v_parameter_t;
110 v_event_key := :NEW.LAYER_ID||'-'||TO_CHAR(ln_inv_layer_id)||'-'||TO_CHAR(GetNextEventKey);
120 enqueue_event ( p_event_name => v_event_name
130 , p_event_key => v_event_key
140 , p_event_parameters => v_parameter_list
150 , p_msg_id => v_message_id );
160 END;
Lines 30 and 40 sets the name and value of a parameter to ORG_ID and a value in v_org_id variable. This is then stored in a variable of type WF_PARAMETER_T.
Line 50 adds a new record to the table of parameters.
Line 60 then adds the parameter v_parameter_t to the table of parameters v_parameter_list which is defined as a variable of type WF_PARAMETER_LIST_T as record 1 in the table. When the workflow is initiated, the ORG_ID item attribute will be populated with the value from the parameter. If there is no ORG_ID attribute, then one is created on the fly for you.
Lines 70 to 100 repeat this for the second parameter to pass into the workflow.
Line 110 generates a unique event key, using a function called getNextEventKey to pick a number off a sequence
Lines 120 to 150 will call a procedure to enqueue the message onto an Oracle AQ and return the message ID of the new message on the queue.
The full code (including the enqueue procedure) can be found here.
If you want to make the procedure more generic / resuable, you could include passing a list of the parameters into the procedure and then add these to the event payload. The key thing to note when using this sample is that you need to extend the table for each new parameter, and then make sure that you are setting the right record in the table. You could include something like
v_parameter_count := 0; to ensure that you don’t get the count of the records in the table wrong, and that you increment the count whenever you extend the table. I’ve not done it that way, but what you choose to do with the code from here is up to you!
v_parameter_t.name := 'ORG_ID';
v_parameter_t.value := v_org_id;
v_parameter_list.extend;
v_parameter_count := v_parameter_count + 1;
v_parameter_list(v_parameter_count) := v_parameter_t;
Matt
There are certain situations when you may need to launch a workflow from a trigger, which is supported but in a restricted manner. Whenever a workflow activity starts, a savepoint is issued – which is not allowed in a trigger, so if you need to launch your flow (or restart it) from a trigger, you need to ensure that the code doesn’t get as far as issuing a savepoint by deferring it to the background.
In order to determine whether the activity should run in the foreground or the background, the workflow engine checks an externalized constant called WF_ENGINE.THRESHOLD. If the cost of the activity in the workflow is above the threshold, then the activity is deferred to the background and you need to run a background engine to execute it.
In order to force the engine to defer the thread, you need to set the threshold to a value which is going to be lower than the cost of the activity, and then communicate witht= the workflow engine. Once you have called the engine, you MUST ensure that you set the threshold back, or all workflow activities from that point on will defer to the background.
Once you’ve deferred the thread, run a background engine (WF_ENGINE.BACKGROUND or there is a seeded concurrent program in eBS) to start the process moving again. Here’s the code to do it:
begin
save_threshold := wf_engine.threshold;
wf_engine.threshold := -1;
wf_engine.createprocess('<ItemType>','<ItemKey>','<Process>');
wf_engine.startprocess('<ItemType>','<ItemKey>');
wf_engine.threshold := save_threshold;
exception
when others then
-- remember to reset the threshold
if wf_engine.threshold <= 0 then
wf_engine.threshold := save_threshold;
end if;
-- continue with normal error handling
end;
This works fine if you are using the CreateProcess and StartProcess (or LaunchProcess) APIs, but doesn’t work for raising a business event. The next post will look at the only way that you can raise a business event from a trigger.
Some customers have a requirement that for audit purposes, they need to keep the Workflow runtime data for quite some time – I worked with a bank in the UK which wanted to keep everything in the Workflow tables for seven years! So here’s a couple of tips for consideration.
Firstly, if you need to keep runtime data for completed workflows for more than a month or so, then you shouldn’t do it in your Workflow system – archive it somewhere else. If you leave large quantities of data in tables which are being hit frequently during run-time, then there will be a significant performance hit. I’ve seen a number of posts in various lists which say something like “I’ve got 2 million rows in my item attribute values table, we’ve never purged anything and the system is slow – what can I do?”
By the time you have this many records, purging the data will also take a long time (some customers have reported that it takes over a day on some systems!!) – a Catch-22 situation, since you can’t purge to remove the data because it takes too long, and in the meantime lots more data is being written to the Workflow tables…
If you are archiving the data elsewhere, there are two different ways you can approach this – either push the data from the Workflow tables into the archive system, or you can pull it into the archive system from the Workflow system.
For a push, you would need to write triggers to each table that workflow writes to, so that whenever data is inserted or updated, the change is replicated in the archive system. This would be very processor intensive, since you are effectively running the same job twice. I would not recommend this method.
For a pull, you need to write something that can run at the end of the day to copy everything from the Workflow system into the archive. This is significantly better for performance, since it can be scheduled to run when the system workload is low, and so should not impact the operation of the system. Once you have successfully archived the data, you should then purge the Workflow data. If the requirement is just to archive the runtime data (and not to archive changes to the workflow definition), then you can use the queries which are executed in the standard wfstat.sql script for any completed workflows to determine what information you need to keep.
My recommendation would be to take the second option, and pull the data from Workflow into your archive system. The archive can even be kept in the same database as the workflow system, but in completely separate tables which are not used during regular operation, or it could be on a completely separate system using something like a database link to connect to the different database. You could even write something to pull all the information from the runtime tables and create a payload which can be enqueued onto an Oracle Advanced Queue – from there the you can determine what other environments pick up the message, and you could just leave the message on the queue for processing / viewing later.
As ever, any comments or views on my suggestions are more than welcome!
This workaround was originally suggested in the WorkflowFAQ forum by 67_eb_in_619, but I’ve moved it into the blog for more people to look at.
Within eBS, the default definition of the Workflow roles always includes the HZ_PARTY records, as well as those linked to people and users. The problem with this is that if you aren’t using email, then if the notification is sent to the role linked to the party record, then no-one is going to see it!
This is also really bad from a user-friendliness point of view. If you are reassigning a notification, then do you need to transfer it to the user called “SEARLE, Mr. Matthew” or “Mr. Matthew Searle” (in a recent project the first of these was the right one and the latter was for my party record). Potentially, the party record and the user record could have the same display name as well, making it pretty much pot-luck which one the user chooses.
So, how do you remove the records from the view?
There are two ways that you can do this – one supported, one not.
Firstly, you can make the party record inactive. The method that I have been recommended by Oracle to use is:
Navigate: Sales Online Responsibility > Customer Tab > Person Sub Tab
1. Query the person record.
2. Once the person details are displayed, you will see a Status field.
3. Select the status to be Inactive
4. Save
If you don’t use sales, then this is still probably the best place to do it, by all accounts!
Secondly, update the records through SQL:
UPDATE ar.hz_parties
SET status='I'
WHERE party_id IN ( SELECT party_id
FROM hr.per_all_people_f );
As ever, make sure you know what you are doing before you commit the changes – I’d be inclined to use the GUI to make the change, but if there are lots of records to fix, or you aren’t fussed about support anyway, then try the second one. I’m not taking any responsibility for it though!
 |
 |
 |
You are currently browsing the WorkflowFAQ weblog archives for August, 2008.
