Hi everyone,

In many of my fexes, things can be split up into independent functional components that can be executed at the same time without any impact on the rest of the report. For example see the following PSUEDOCODE



SQL Passthrough 1



SQL Passthrough 2



JOIN DATA FROM SQL PASSTHROUGH 1 and PASSTHROUGH 2


PRINT REPORT.

Normally, you would have to wait until the database returns SQL 1 before performing SQL 2. Are there constructs within FOCUS which let you specify blocks that can be executed concurrently? For example, if you have three tags (SEGSTART, SEGEND, BLOCK) where

SEGSTART = specifies the beginning of an independent block
SEGEND = specifies the end of a independent block
BLOCK = specifies a point in the fex where all threads must be completed before moving forward

This would make the preceeding pseudocode look like this:

SEGSTART NAME1

SQL Passthrough 1

SEGEND NAME1

SEGSTART NAME2

SQL Passthrough 2

SEQEND NAME2

BLOCK

JOIN DATA FROM SQL PASSTHROUGH 1 and PASSTHROUGH 2


PRINT REPORT.


This would allow the you to run these processes concurrently and increase response time.

Is there anything like this currently in Webfocus and if not are there are plans to include such functionality?

-Jose Deleon

This message has been edited. Last edited by: Kerry,

IBI's ETL tool, DataMigrator, provides parallel processing functionality and you can probably accomplish something similar in the reporting environment with ReportCaster. But WebFOCUS itself does not. You should open a case with IBI to determine any future plans in this regard.

I see what you mean but it is not exactly the same. The functionality you have specified would require scheduling the jobs to update the stageing files. What I propose above is runtime solution. Would you find the proposed solution I posted above helpful as a new feature within the language itself?


-Jose Deleon

Searching the IBI Tech Support site for "parallel processing", I discovered that parallel processing can be turned on with DB2 databases - although I am unsure how one would code a WebFOCUS request to intentionally achieve concurrent processing. It is probably left strictly to the discretion of the DB2 execution planner.

DataMigrator uses an iWay API procedure named CMASAP to execute concurrent requests, along with a WAIT function to halt further processing until designated parallel processes have completed. The syntax for these API procedure calls is fairly unfriendly, which is why the GUI is the preferred way to setup parallel processing in DataMigrator. It would certainly be nice to provide similar functionality in WebFOCUS - but hopefully with a syntax that could be coded relatively easily by hand.

I suspect one of the obstacles with parallel processing in the reporting environment is how to make temporary datasets (HOLD files, etc.) created by different iWay agents (one for each concurrent process) available to downstream processing. Use of FOCCACHE, TEMPPATH, or APP HOLD might eliminate this problem. In addition, I'm sure performance issues come into play when parallel processing would allow a single report user to engage multiple iWay agents at one time.

With a moderate dose of JavaScript, you can have an html page launch the several prep steps in parallel (each placing its output in foccache); when the last prep fex is done, the page calls the fex that produces the formatted report

Thanks for the Comments Dan and J-Gross.

Couple of comments on your posting Dan,

1. Temporary datasets (correct me if I'm wrong) are currently stored in a temporary folder which is generated for each agent when you run a report. If so, then it is only a matter of making sure that the threads each have a knowledge of what the temp folder is when it is started. Having an APP HOLD shouldn't behave any differently than it does in a single threaded environment or implementation.

2. The syntax doesn't have to be anymore complicated than the example above. Although, I must admit that it would require careful attention by the developer. Definitely more care than Webfocus developers are accustomed in this regard. And some special thoughts need to be placed on any &var that is used in the block of code.

3. Benefits to building in language support for parallel processing are in no particular order.

a. Gives developer ability to build in parallelism in the obvious blocks of code.

b. Keeps Webfocus platform relevant in an increasingly multi-core ecosystem.

c. Allows for run-time parallelism which, if used with SQL Passthrough, increases the throughput of the reporting server and takes advantage of the built in multithreading support built into databases.

d. Provides more bang for the buck on hardware upgrades at the database side while decrease the need to upgrade the hardware on the reporting server. (if sql passthrough is used.)

Difficulties with apply to language.

a. Would require a rework of the fex compiler/interpreter to account for the &vars, error checking, and threading.


I would be concerned about the overhead of the iWay API as you have noted in your post. As well as the maintainability aspect.


J-Gross,

I like your approach. It does allow for the parallelism to be done at run time. But, by it's very nature it is a "jerry-rigging". Inclusion of this capability within the language would be a cleaner implementation. But, your suggestion is very interesting. But how does this affect maintainability?


-Jose Deleon

quote:

Are there constructs within FOCUS which let you specify blocks that can be executed concurrently?

Not out of the box as far as I know.

However, why don't you just extend the two SQL statements to perform the join within the PASSTHRU as a single SQL statement? You wouldn't get the parallel processing that you expressly want but you would be getting the RDBMS to do the majority of the work instead of WF which is always the goal in my book.

Of course, if the sources are in different RDBMS then you may have a problem unless you use SQL Cross Over using "OpenQuery".

T

Hi Tony,

Point well taken. In practice I place as much as possible into the same SQL statement as possible. But, many times the following scenarios require separate pass through statements.

1. Pointing to different data sources. (which you stated.)

2. Code re-usability. Many of portions of our logic is specified in various -INCLUDES which help us make sure that other members of the team conform to coding standards and helps with maintainability.

3. Some times, even when you're pointing at the same database, creating a really BIG SQL statement results in a slower execution plan than just splitting it up into two SQL statements.

While I totally agree with you that the goal is to have the RDBMS perform the bulk of the data manipulation, it still does not preclude focus from providing more performance benefit by way of the above language constructs.

With respect to the OPENQUERY suggestion, I have not had any experience with that functionality. A quick search on Google resulted in references to SQL Server and some vague mention in Oracle but not really the same linguistic mechanism. It seams to me that this would result in maintainability issues and would require significant work on already establish code. What is your experience with it?

-Jose Deleon

As a recent situation, I have an update SQL passthru pulling and manipulating data from Oracle tables and inserting it into an MS SQL table. I don't have access the other way but the same process should work in principle.

T