| Author |
Message
|
| RJoubert |
 Posted: Thu May 30, 2002 3:31 am Post subject: Filtering for workitems |
 |
|
Apprentice
Joined: 30 May 2002
Posts: 43
Location: Buffalo, NY
|
I would like to know if there is a way to filter for workitems based on a member of the data container. For example, if I have a member in the data container called "Customer_ID" can I filter for workitems with Customer_ID = "12345"???
Thanks.
_________________
Rich Joubert
Computer Systems Engineer
Univera HealthCare, an Excellus Company |
|
| Back to top |
|
 |
| jmac |
| Posted: Thu May 30, 2002 5:05 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Rich:
The only way to do this is thru trickery. You must put the container item into the Description (%Customrer_ID%), then filter on the description. If you are using the Thin client, you can easily modify the ListViewer.jsp to remove your container item from the description prior to display. If you are writting your own client (Thick or Thin) you can simply not display the Description or if you need it, you can remove any portions that you don't want to display.
GOOD LUCK
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| vedbhat |
| Posted: Thu May 30, 2002 5:58 pm Post subject: |
|
|
Disciple
Joined: 19 Mar 2002
Posts: 186
Location: Singapore
|
Hi,
If you are using the default thick client, I think the simple way is as mentioned by John. You can move the required valud in the description field and then sort/filter according to them.
Cheers
Ved
_________________
IBM Certified Solutions Expert - MQSeries Workflow
IBM Certified Specialist - MQSeries |
|
| Back to top |
|
|
| amittalekar |
| Posted: Sun Jun 02, 2002 8:08 am Post subject: |
|
|
Disciple
Joined: 03 Apr 2002
Posts: 166
Location: VA, USA
|
Hi ,
What is the maximum length of description? Is it 256 characters?...
What if at run time the length of descrption exceeds this limit?...
In this case how workflow server will behave?
-- Amit |
|
| Back to top |
|
|
| jmac |
| Posted: Sun Jun 02, 2002 8:57 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Limit is 256 chars as far as I know.
If it is exceeded truncation will be done by the Execution Server.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| manoj |
| Posted: Wed Jan 15, 2003 12:58 pm Post subject: |
|
|
Master
Joined: 30 Jan 2002
Posts: 237
Location: Virgina
|
Has any body came across problems with DESCRIPTION in the filter query?
i am facing some performance problems with filter "DESCRIPTION LIKE '<some string>*'.
i believe such a query will cause a tablesan.
any thoughts?
_________________
-manoj |
|
| Back to top |
|
|
| yaakovd |
| Posted: Mon Jan 20, 2003 10:35 pm Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Thank you!!! It is interesting point. Our Web Client is very slowly since I started to use this kind of filtering:
1. Operation "LIKE" is very difficult for every DB
2. Member "Description" is not a primary key in table WORK_ITEM
BTW: maximum length is 254 (you can see in DB)
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| jet |
| Posted: Tue Jan 21, 2003 11:07 am Post subject: |
|
|
Apprentice
Joined: 04 Dec 2002
Posts: 49
Location: New York
|
| What is your purpose of filtering? One option is to use Global container. It will pass those info as BLOB in the container_content column....You should get the value by playing around byte array and string... |
|
| Back to top |
|
|
| yaakovd |
| Posted: Tue Jan 21, 2003 11:26 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Good idea for somebody, but I am still working with WF 3.3 
I am going to test this issue with several thousands of work items.
I will update you after.
May be I need change Roles structure or to use Levels…
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| yaakovd |
| Posted: Wed Jan 22, 2003 11:43 am Post subject: Performance test for Client |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Some update about result of testing with Description:
1. We created index by DESCRIPTION on WORK_ITEM table what reduced number of reads from table.
2. I still sure that using LIKE in filter has impact on performance.
3. I suggest using PRIORITY instead (with index by this column).
Somebody have expirience with indexes on WF DB (DB2) ???
Special thanks to "leongor" for his help in testing 
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| dkrawczynski |
| Posted: Wed Jan 22, 2003 5:57 pm Post subject: |
|
|
Apprentice
Joined: 19 Dec 2002
Posts: 26
Location: Dallas, TX
|
If at all possible I suggest upgrading to V3.3.2 and using the filtering capability against the global container data elements. Performance of the LIKE clause will be a killer if you have thousands of rows. In the mean time try this:
Add an index to the WORK_ITEM table using the following SQL statement.
CREATE INDEX FMC.WI_DESC ON FMC.WORK_ITEM (DESCRIPTION ASC);
Then run statistics against the WORK_ITEM table with this SQL:
RUNSTATS ON TABLE FMC.WORK_ITEM WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE;
You can run the SQL commands against the runtime database using the DB2 Command Center program on your desktop if you have it installed or use the DB2 command prompt on your workflow server machine.
Then run your test again to see if there is a performance gain.
Also, if the Customer ID is the only field you will be placing in the Description field and you are truly only looking for an exact match to one customer, then you can use the "=" operator instead of the LIKE which should make the performance even better.
Let us know how it works.
_________________
Doug Krawczynski
IBM Certified Solutions Expert -
MQSeries Workflow |
|
| Back to top |
|
|
| yaakovd |
| Posted: Thu Jan 23, 2003 1:59 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
You explaned exactly what I did in my test and described in previous message, even index name WI_DESC is the same  it works!
Additional point: owner = CURRENT_USER in filter is very helpful option, special in system with big number of users with same outhorization.
But the question: for user, which not outhorized to see workitems of all users, if WF adds "owner = CURRENT_USER" to query?
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| dkrawczynski |
| Posted: Thu Jan 23, 2003 11:13 am Post subject: |
|
|
Apprentice
Joined: 19 Dec 2002
Posts: 26
Location: Dallas, TX
|
I guess I need to stop posting replies late at night after a few glasses of red wine. 
Did you run statistics when you did your previous test? Sometimes the SQL optimizer will not properly use a new index until run statistics is done against the table.
What about using "=" instead of LIKE?
You are correct in assuming that the Workflow server will tack on OWNER = CURRENT_USER to the filter string if the user does not have authority for all workitems.
If you set the tracing level to highest level on the server you can see the SQL commands that are executed. You should see the queryWorkItems SQL when you use global container variables, it is a union of 3 separate selects which each join to the global container table.
_________________
Doug Krawczynski
IBM Certified Solutions Expert -
MQSeries Workflow |
|
| Back to top |
|
|
| yaakovd |
| Posted: Thu Jan 23, 2003 11:31 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
| dkrawczynski wrote: |
| I guess I need to stop posting replies late at night after a few glasses of red wine. . |
Actually I am currently with Jin & Tonic
| dkrawczynski wrote: |
Did you run statistics when you did your previous test? Sometimes the SQL optimizer will not properly use a new index until run statistics is done against the table.. |
I run regular statistics commands, but may be I need create special command for new index? Any way it working.
I cannot use "=" instead of LIKE. Description has business functionality
| dkrawczynski wrote: |
If you set the tracing level to highest level on the server you can see the SQL commands that are executed. You should see the queryWorkItems SQL when you use global container variables, it is a union of 3 separate selects which each join to the global container table. |
Thanks, I forget this option 
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
|
|
