| Author |
Message
|
| PisgahMan |
 Posted: Fri Apr 01, 2005 8:39 am Post subject: Runtime database query. |
 |
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
| Hi experts - can someone tell me what DB2 query to run to get all of the active work items from the runtime database? I am not familiar with the table structure. |
|
| Back to top |
|
 |
| vennela |
| Posted: Fri Apr 01, 2005 10:44 am Post subject: |
|
|
Jedi Knight
Joined: 11 Aug 2002
Posts: 4055
Location: Hyderabad, India
|
But why?
If you open the client and login as admin then you should be able to find that out. Are there too many work items? |
|
| Back to top |
|
|
| MQRR |
| Posted: Mon Apr 04, 2005 12:27 pm Post subject: |
|
|
Centurion
Joined: 10 Aug 2003
Posts: 110
|
It is not good idea to play with runtime database but to answer your question,
You can query against work_item table and use the state column in the table to check for the value whatever the state you are looking for. I dont remember on top of my mind but i think for READY it is 1 and RUNNING it is 2.
MQRR |
|
| Back to top |
|
|
| jmac |
| Posted: Mon Apr 04, 2005 1:04 pm Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
| MQRR wrote: |
| It is not good idea to play with runtime database |
My sentiments exactly, BUT there are some views that you can use that are defined by MQWF. See the Programming guide, Chapter 14, for details.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Tue Apr 05, 2005 4:37 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
Hi guys - I appreciate your concerns and I had the same questions. The way our system is set up, we send several of the workflow audit messages to the Audit queue, then a UPES reads these items and updates tables in an Oracle database. Workitems are then updated in the work item table as well as workflow.
The problem is sometimes we have 'ghost items' that exist in Oracle or DB2 but not the other. One proposed solution was to periodically run querys against both DB's and reconcile the differences.
What I need is a query that will include the process instance name with the work item data.
Thanks! |
|
| Back to top |
|
|
| ucbus1 |
| Posted: Tue Apr 05, 2005 8:53 am Post subject: |
|
|
Knight
Joined: 30 Jan 2002
Posts: 560
|
| Quote: |
Hi guys - I appreciate your concerns and I had the same questions. The way our system is set up, we send several of the workflow audit messages to the Audit queue, then a UPES reads these items and updates tables in an Oracle database. Workitems are then updated in the work item table as well as workflow.
|
Which database is showing more ? If Oracle database is having less records, then it could be possible your work items are gettting stuck at ur UPES stop. Create a view on the workitems for the upes and see if anything stuck.
| Quote: |
| Workitems are then updated in the work item table as well as workflow. |
I could not understand this.. |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Tue Apr 05, 2005 9:33 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
I think what happens is this. Certain individuals have a tool to mark work items as complete in the Oracle (audit feed) work item table. This tool just does a DB Update to Oracle so the work items in workflow are still ready. If no one notifies the WF Admin, these process instances are just hanging out in WF with no way for the user to ever complete them.
Our architecture is strange and I was not involved in the design, but we have a work item table that is feed by the WF audit trail. In some cases both the Oracle tables and Workflow are updated, seperatly.
Any one know how to get a list of Active work items from DB2 with the associated process instance name?
 |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Apr 05, 2005 3:17 pm Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
I am sure this can be done going directly against the database, but I would not know the statement to use.
However, you can easily get the list you want using the MQWF API, then use that to go against your Oracle DB.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Wed Apr 06, 2005 5:09 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
| I have Runtime database on my local machine, with some nice GUI tools. Hopefully I convince everyone this is not a good idea. It would be easier to look up items in the web client. |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Wed Apr 06, 2005 7:03 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
| I cannot even figure out the relationship between the Work Item created message on the audit queue and the work item table in WF. |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Wed Apr 06, 2005 7:10 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
| The associated Object tag in the create work item message seems to be the field we are using in Oracle as the work item ID....but where is that in DB2? |
|
| Back to top |
|
|
| jmac |
| Posted: Wed Apr 06, 2005 7:16 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
If I understand what you are doing correctly, the field you are looking for does not exist in DB2. The ID field you are using is an External field, and internally they use a different ID.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Wed Apr 06, 2005 7:25 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
John the field I see in the Audit message is for example
<AssociatedObject>VwAAAAEBlaUqAAAAAAAAAABX</AssociatedObject>
We are using that as they key in the oracle table. Is there anyway to tie the data from that audit message back to the DB2 tables? |
|
| Back to top |
|
|
| PisgahMan |
| Posted: Wed Apr 06, 2005 7:28 am Post subject: |
|
|
Voyager
Joined: 27 Jul 2004
Posts: 93
|
| is there a relationship between the external and internal ids? |
|
| Back to top |
|
|
| jmac |
| Posted: Wed Apr 06, 2005 8:26 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Yes there is, but off the top of my head I do not remember it. There are some metods in the Native Java API that allow for conversion. If you don't have Native Java API, it is a support pac you can download... I am pretty sure if you search this site you will find a reference to it.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
|
|
