Hi,

We are migrating our report database from DB2 to Netezza and facing some har performance issues. We have some join between FOCUS tables built in run-time with Database tables. The aggregation can't be done efficiently. We have tried to write in run-time the focus tables to database, (FORMAT SQLNEZ) but we have some doubts if this is the best approcah.

Has anyone dealt with this. What do you think could be the best approach?
We have Webfocus 7704.

Thanks in advance.
António Simões.

This message has been edited. Last edited by: <Kathryn Henning>,

Unless the temporary HOLD files created as Netezza tables are on the same server as the permanent Netezza tables, WebFOCUS will still treat any joins as cross-platform joins. Performance may actually be better when using FOCUS HOLD files in the joins to Netezza tables. If possible, instead of using joins write key values to HOLD files and pass them to Netezza as a list of values (e.g., WHERE Netezza.table_key IN FILE HOLD_KEYS). There are limitations on the number of values you can pass in one file so you may need to create several such files of key values, with several WHERE clauses. Of course the number of key values that need to be passed to Netezza may overwhelm this kind of approach. In that case, it is often best to apply as many WHERE conditions as possible to your Netezza query and perform the joins with FOCUS HOLD files after all the necessary data has been extracted from Netezza. Make sure you turn on SQL traces when evaluating your queries against Netezza to ensure they are as efficient as possible (sorting and aggregation performed by Netezza whenever possible). A lot of trial and error will be necessary in the beginning until you become more familiar with how WebFOCUS and Netezza interact.

Hi Dan, In fact the HOLD files will be in the same segment of all other netezza tables.
I understand your idea but my code is quite complex and i have join fiels in "select part" so I think writing focus hold files to netezza would be a better approach.
My only problem seems to be that the table created in run-time is a perssitent (not temporary table), so i'm afraid of what could happen in multi thread scenario.

Thanks for your opinion.

In that case, you may need to add the userid, current time, and/or a random number to the temporary table names to make them unique and avoid conflicts. You should probably also add a step at the end of each job to delete the temporary table/s created during the session.

That's a good ideia put create a variable to store the variable name that should be a unique name.