FAST Impulse – SQL Queries to Monitor the JDBC Connector Execution

If you run FAST ESP + Impulse and use the Impulse and the JDBC connectors to load data into FAST you probably need to monitor how the connector is running and processing documents for each of your collections.

I use the following SQL queries to get the current status of the items on the Impulste Items database (ImpulseItems schema).

This query shows all the documents per collection and per different statuses:

SELECT collection_name, update_flag, COUNT(*)
FROM ImpulseItems.status (NOLOCK)
GROUP BY collection_name, update_flag
ORDER BY collection_name

This query shows the statuses fo the documents for a specific collection:

SELECT update_flag, COUNT(*)
FROM ImpulseItems.status (NOLOCK)
WHERE collection_name = '[collection name]'
GROUP BY update_flag

The status is defined by the update_flag field.

The update_flag field value means:

• < -1: document is being deleted by one of the JDBC connector instances. This number represents the number of the JDBC process defined on the NodeConf.xml file.
• -1: document should be deleted when the JDBC connector instance runs.
• 0: no updates on the document / document doesn’t need to be processed.
• 1: document has been update and needs to be processed.
• between 2 and 221: document is being processed by one of the JDBC connector instances. This number represents the number of the JDBC process defined on the NodeConf.xml file.
• 222: document is being loaded by the EXLT connector.
• 333: document is locked by EML server.

It is important for you to run the queries using the NO LOCK query hint in order to avoid interference on the execution of the connector (no blocking on SQL Server processes).

See you,

Amadeu.