Search code examples
sqltibcoojdbctibco-business-works

how to get row count from Tibco Direct SQL or JDBC Query activity

In my Tibco process I have logic that map SQL query output only if the query returns less than 1000 records from Tibco "Direct SQL" or Tibco "JDBC Query" activity.

For now I am just running same query 2 times: 

Select count(*) AS Count 
FROM my_table  
WHERE my_table.foo = 'bar'

if first query result less than 1000 I call same query for getting all the rows 

Select my_table.* 
FROM my_table  
WHERE my_table.foo = 'bar'

The query is pretty heavy and I want to run it only once for performance purpose.

I found the solution from SQL side in the Need a row count after SELECT statement: what's the optimal SQL approach?

I can use query like:

SELECT my_table.*, count(*) OVER() AS Count
  FROM my_table
 WHERE my_table.foo = 'bar'

The problem is that adding count(*) to the query is also affecting performance.

I can map the query result to "Map Data" activity and then use count($Map-Data/pfx:my_element/) but I prefer to avoid additional not required maping for performance purpose.

The Tibco "Direct SQL" and Tibco "JDBC Query" are using Oracle (ojdbc7.jar) and DB2 (jt400.jar) drivers.

Is there any way to get the query output row count from tibco side without adding count to the query output?

Solution

  • I finally got what I need. I can just use XPATH "count" function directly from "SQL Direct" or "JDBC Query" activities output

    "JDBC Query": count($JDBC-Query/resultSet/Record) < 1000

    "SQL Direct": count($SQL-Direct/jdbcGeneralActivityOutput/unknownResultset/row) <1000

    JDBC Query row count