quick "check for data" query before other queries run?

david_lock
david_lock
edited February 11, 2022 in Analytics #1

Greetings, long-time lurker, first time asker.

I'm using:
OpenText Analytics Designer
Version: 4.6.0
Build id: v20160406

I am working on a report that runs some complex queries against a few tables in a DB2 Z/OS dbase. These queries can take a long time to run, which is fine if the user gets the data they need at the end. However I am finding that they also take a long time to run even if there is no data returned for the user-entered parameters.

I am wondering if there is a way to run a quick little query to find out if the user parameters are going to return anything, and then either stop everything if there's no data, or keep going if there is. I imagine I would do this in something like an "initialize" or "beforeFactory" script, but I don't know how to work up an SQL query against one of my data sources in Javascript. If I could do this and then set a "check for data" report parameter to Y or N based on the results, I can figure out the rest...

Any thoughts? Is this a terrible idea? Do I just need to ask my enterprise architect to get this database off a mainframe and onto linux servers where it belongs?

Answers

  • jfranken
    jfranken E
    edited June 19, 2018 #2

    I don't know how much it will improve performance, but I've included a sample report that shows a relatively simple implementation. Here are the steps I followed:

    Create a data set called "test" to set the flag.
    Drag the data set to the layout to create a table. This is necessary to make the data set run its query. Turn off the visibility of the table because it is used only to set the flag.
    Create a data set called "optional". Drag it to the layout. This will display data based on the flag setting.
    Add code in beforeOpen of "test" to set the default flag value.
    Add code in onFetch of "test" to change the flag value when data is retrieved.
    Add code in beforeOpen of "conditional" to return no data if the flag is set to false.

    Manually change the query in "test" to return data. When you run the report, the table containing the data from "optional" will display all of its rows. Change the query in "test" so that it returns no data. The table will be empty.

    The best solution would be to buy an iHub so that you can use data object data caching (not that I'm trying to sell you our products :) )

    Warning No formatter is installed for the format ipb

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs