| Author |
Message
|
| RJoubert |
 Posted: Fri Jul 12, 2002 11:49 am Post subject: MQSeries Workflow DB2 tables |
 |
|
Apprentice
Joined: 30 May 2002
Posts: 43
Location: Buffalo, NY
|
Does anyone know where I can find documentation on the DB2 tables used in MQSeries Workflow? Or does anyone have any experience in directly querying the DB2 tables used in MQSeries Workflow?
Thanks,
_________________
Rich Joubert
Computer Systems Engineer
Univera HealthCare, an Excellus Company |
|
| Back to top |
|
 |
| steinra |
| Posted: Fri Jul 12, 2002 12:22 pm Post subject: |
|
|
Apprentice
Joined: 23 May 2002
Posts: 28
Location: USA
|
I have queried some of the tables, but in an adhoc way.
To my knowledge IBM has not published the schema for the database. I have not taken the time to reverse engineer it.
The queries I have used are to check how many work items are on a user's list, or the total number of process instances in the system.
select count(*) from work_item where owner = 'USER1'
A thing to keep in mind is that a work item record will stay attached to a user even after it is worked. The state field changes values. The cleanup server will come around eventually and delete the completed items.
Randy |
|
| Back to top |
|
|
| jmac |
| Posted: Sat Jul 13, 2002 4:31 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
The only tables which are published are the Audit_Trail, and if you are running V_332 the new "View Tables". These are documented in the manuals. The View Tables are in the Programming guide, and the Audit trail is either the Admin Guide or the Installation (dont remember at the moment which).
It is my opinion, that you should think LONG AND HARD about going directly at any table other than these three, as, I beleive, it has been IBM's policy in the past to not support you if you have code going against any other table. In other words, if you have a sev 1 problem in production, and they notice you are accessing tables directly, they will have you reproduce the problem in an environment where you are not touching the tables. This is a HUGE risk to take in my opinion.
As Randy points out, you can easily reverse engineer the tables but I would only be doing this on an "AD HOC" basis. I would never make code part of my solution that directly reads tables.
Is there something in particular that you need to go to the tables for?
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| steinra |
| Posted: Sat Jul 13, 2002 10:41 am Post subject: |
|
|
Apprentice
Joined: 23 May 2002
Posts: 28
Location: USA
|
That raises an interesting question.
The reason I go to the tables directly, on an ad hoc basis, is to find out the total number of items on a list.
The reason I did this was because it appears there is a limitation of how many items the worklist interface will return. It seems to be around 1500. If the threshold is not set to that number then I will usually get an API error.
So, for instance, using the thick IBM client I can only see around 1500 in the process instance list. Typically right now I have 9,000+ instances running. So in order to get to this number I have to hit the tables so I can let my users know they are VERY behind  .
Is there a different way, with the API, to get to the total number of process instances, or items on a users work list?
Thanks!
Randy |
|
| Back to top |
|
|
| jmac |
| Posted: Sun Jul 14, 2002 7:24 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Randy:
I would imagine that the reason you are seeing this limitation in number of workitems is due to the default limit of 1,000,000 bytes on the size of the MQSeries message generated. If you go to your Network objects, and look at Domain Properties, I believe on the Messages Tab there is a setting for unlimited message size.
Of course, you should probably NOT be doing this type of a query very often. No one user could possibly process 9000 workitems.
Have you ever considered just keeping track of how many intances are currently running. This is pretty easy to do using the "Audit To MQ" facilitiy combined with Audit Filtering. ALSO, there are monitors out there that can supply this type of information.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| Bobbo |
| Posted: Thu Jul 18, 2002 11:27 am Post subject: |
|
|
Acolyte
Joined: 17 Jun 2002
Posts: 50
Location: Buffalo, New York
|
John:
Could you please expand upon the "Audit To MQ" facility combined with Audit Filtering and the montors you referred to?
Thanks,
Bob |
|
| Back to top |
|
|
| jmac |
| Posted: Thu Jul 18, 2002 11:37 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
As of V3.2 you can have your audit records written to an MQSeries Queue. Also, you can specify filters to indicate exactly which audit records you want to receive.
With these two capabilities it is pretty easy to keep track of how many running instances there are. You simply write all Start instance events, and all Terminate instance events to a Queue, monitor the queue and keep track of the number
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| JohnG |
| Posted: Tue Feb 25, 2003 9:11 am Post subject: |
|
|
Newbie
Joined: 21 Jan 2003
Posts: 7
|
John
Do you have any gut feel for the performance of filters when you put a LIKE qualifier in. We are using ORACLE as the backend DB.
An example would be :-
String filter = "STATE = READY and DESCRIPTION LIKE "+mystring+"*" ;
workitems = service.queryWorkItems( filter, null , null ) ;
Thanks
John |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Feb 25, 2003 10:55 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
| JohnG wrote: |
John
Do you have any gut feel for the performance of filters when you put a LIKE qualifier in. We are using ORACLE as the backend DB. |
Sorry John, I haven't got a clue... You might post this to the IBM newsgroup and get a response.
GOOD LUCK
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| dkrawczynski |
| Posted: Wed Feb 26, 2003 7:53 am Post subject: |
|
|
Apprentice
Joined: 19 Dec 2002
Posts: 26
Location: Dallas, TX
|
The fastest, most efficient way to get a count of workitems or process instances is to directly query the runtime database, but if you have problems with the query do not bother calling IBM for help (IBM will still support your MQWF installation for all supported features).
Using the queryWorkitems API call is a terribly slow way to count the workitems, but its the only supported method. I've also included something from the V3.3.2 Service Pack 4 read me that is related to the subject, see (2.4) below.
My experience with the LIKE clause is that is is very slow for large numbers of rows even if there is an index on the column. You might want to look at using the Global Container feature to achieve your query workitem requirements.
2.4 Configurable message size for query workitems
-------------------------------------------------
A new configuration variable "RTInitialWLMessageSize" has
been introduced. It can be set to the number of bytes that
should be allocated for the response from a query workitems call.
If an execution server trace indicates that a lot of time is
spent copying the workitems - caused by big query results for which
the initial message size is too small, you can use this parameter
to start with a more adequate size.
_________________
Doug Krawczynski
IBM Certified Solutions Expert -
MQSeries Workflow |
|
| Back to top |
|
|
| manoj |
| Posted: Thu Feb 27, 2003 2:13 pm Post subject: |
|
|
Master
Joined: 30 Jan 2002
Posts: 237
Location: Virgina
|
I do query the runtime database frequently. But just a precaution , in the database the value for work_item states may be different from what is exposed in java or activeX.. for getting the count of workitems in ready state for a particular activity use the following query
SELECT COUNT(*) FROM FMC.WORK_ITEM WHERE STATE =1 AND NAME='<ACTIVITY NAME>'
if u want the count of checked out workitems then changE STATE=64
NOTE: There are no foreign key constraints on any of the 61 tables , so u should not delete any thing without thinking "THRICE" even if u want to clean up the workflow runtime database.. i always do a database clean up when i want to start the workflow fresh. I 've identified a list of tables and default data in those tables which should be present to start the workflow server..i can get the list if any body interested in those
thx
_________________
-manoj |
|
| Back to top |
|
|
| dkrawczynski |
| Posted: Fri Feb 28, 2003 7:15 am Post subject: |
|
|
Apprentice
Joined: 19 Dec 2002
Posts: 26
Location: Dallas, TX
|
The workitem states and other "state" numerical values stored in the runtime match the CONSTANTS defined for the C API. Each constant value represent a bit in the state numeric value, which is really not necessary. The MQWF team realized this and when they created the Java API they simply used a sequential numeric value for the constants.
If I want to clean out a workflow instance, I just export all FDL from runtime, delete and recreate the configuration, and reload the FDL. It seems the simplest and safest way.
_________________
Doug Krawczynski
IBM Certified Solutions Expert -
MQSeries Workflow |
|
| Back to top |
|
|
|
|
