Leave feedback
  • Monitoring jobs in the runtime database

Write an Article
Friday 14 February, 2014
David Svennevid David Svennevid StreamServe Employee
8 likes 3668 views

How to find out what is going on inside the processing queue of a streamserve job.

There is two main options when monitoring StreamServe, either  you can use the DBAT Jobs tab:





You can search for top jobs in the runtime repository, and  examine them in the Database Administration Tool. You can use the document type as search criteria, and also add more specific search criter ia, for example to only find jobs with processing status Cancelled.

The DBAT will search in the Part tree structure to find out what the server is doing to the jobs in the database:

Part tree

Tracker_Part [PartType=Top]

----Part_firstInput [PartType=InputData]

--------ContainerPart? [PartType=Container] (Typically an inqueue processing)

------------Part_A [PartType=Data]


------------Part_N (Below structure will exist for each Part_[A..N])

----------------ContainerPart? [PartType=Container] (Typically an outqueue processing)

--------------------Part_A [PartType=Data]



----------------ContainerPart? [PartType=Container] (Created if we do other processing with the same input)

--------------------Part_A [PartType=Data]



Part state

In the release of SP5 another option to examine the StreamServe jobs where introduced, udf_partstate function. This is a function available in all databases since SP6 (before that you needed to install it from the maintenance folder). With partstate you can answer questions like:

Retrieving a summary of the current top job statuses

• Retrieving a summary of events waiting for processing
• Retrieving the queues that hold events waiting for processing
• Retrieving the total number of failed top jobs
• Retrieving all error messages
• Retrieving a summary of each type of error message
• Retrieving failed jobs with error messages
• Retrieving expired top jobs w ith error codes and statuses

.. and many more. Since the result from the partstate function is just a resultset you can combine the information with any other aspect of the runtime database.

The main reason for why you want to use partstate is that it decodes all state and status codes into their meaning. This is very handy when examining the database.

udf_partstate is available for SQLServer, Oracle and DB2. SQLServer and DB2 work the same way whereas the Oracle version is limited to only show the overview of the processing and cannot be combined within other queries since it lacks TrackerID and PartID so there is no way to join the result with other tables.

The best way to get to know partstate is to open up the function in the database tool (SQLDeveloper, SQL Server Management Studio or IBM Data Studio). In the definition of the function there are some simple examples on how to use the function. When a new hotfix of StreamServe is delivered it is a good idea to check that function again since the functionality may have improved..


These examples are grouping over the different columns and therefore you will have the same structuretypes several times if they have different state, status and if the are expired or not. So you need to add the different count together if you want to compare for example the finished jobs with the number of StrsID completed in the platform.log


SELECT * FROM TABLE(udf_partstate) ORDER BY 1,2,3,4,5,6,7; 



SELECT count(*) #,StructureTypeName,ProcessingStatus,ProcessingState,PersistingStatus,PersistingState,ExpiringDateTime,FailedExpiringDateTime,ErrorCode,QStatus,Archiv e
FROM udf_partstate(NULL) ps
GROUP BY StructureTypeName,ProcessingStatus,ProcessingState,PersistingStatus,PersistingState,ExpiringDateTime,FailedExpiringDateTime,ErrorCode,QStatus,Archive


select * from table(partState(x'20110505140638616366000000ffffff'));

PartID show only this row, if part is also a tracker - all parts belonging to the tracker is listed, default is null and null means all parts in the part table are returned.

To be able to interpret the result from the partstate function it is necessary to understand the different part categories that is the StructureTypeName in the result from the function.


  1. PartType_Top_Empty
  2. PartType_Inputdata_Entity
  3. PartType_Inputdata_Fragment
  4. PartType_Inputdata_Empty
  5. PartType_Container_Empty
  6. PartType_Data_Entity
  7. PartType_Data_Fragment
  8. PartType_Data_Empty
  9. PartType_Static_Resource
PartType_Static_Resource - Only used for DB+ resources never in processing.

StructureTypeName Category

TOP type category. Created when a trackerId is created. Describes a top node:
  1. PartType_Top_Empty
INPUTDATA type category. Raw format unprocessed by streamserve but stored by inconnector, e.g flatfile such as SAP RDI,xml etc The related blob will be input data and will be attached to a queueitem. The part will show status etc for this blob:
  1. PartType_Inputdata_Entity (normally inputdata is entity)
INPUTDATA_FRAGMENT (if data is delivered to streamserve it can be flagged as fragment so streamserve know that it is not a complete entity. e.g. multipart, mime mail/http, message queue):
  1. PartType_Inputdata_Empty
CONTAINER type category. Does not hold a blob but is instead used to group e.g inputdata. The container is what we in 4.x call a "job", could also be the "document broker job":
  1. PartType_Container_Empty
DATA type category. Processed data that might be input to other processing, or finished:
  1. PartType_Data_Entity (selfcontained unit, possibly built up of subunit, document, complete order, docbroker doc)
  2. PartType_Data_Fragment (not selfcontained, part of an entity e.g. a page, orderline,orderhead ,docbroker page)
  3. PartType_Data_Empty

Lets go check out the state of things!



Comments (0)


Post comment