Has anyone had this issue to happen? I create a report in Web Focus and run it but may need to make changes or something so I kill the report in Web Focus but the program keeps running the process on our Oracle database. This is causing a huge problem that fills up our temp space and causes other programs to crash. Any suggestions?

One of the risks you take with such data base access. We have the same issue with DB2. It's best to not 'kill' at the WebFOCUS end if it is off grinding away on the database end. Your oracle DBA should be able to assist in 'killing' the process I should think.

It is happening even on Sql Server. Even after I kill the agent, I see those process id on the database.

My oracle dba is trying to help with this but he thinks it is in the way I am writing to retrieve the data. I don't think so for instance here is a simple code to retrieve data from two tables.

JOIN TRX.TRX.TYPE IN TRX TO MULTIPLE APPTRX.APPTRX.TREATMENT IN APPTRX AS J1


TABLE FILE TRX
PRINT
'TRX.TRX.PATIENT'
'TRX.TRX.DESCRIPTION'
'TRX.TRX.TREATMENTDATE'
'APPTRX.APPTRX.PROCEDURE'

WHERE TRX.TRX.TREATMENTDATE GE '20080101';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

If I limit the records to 5 everything pulls up fine. To me this is a simple little code nothing fancy and if I do not limit the records the report runs and runs. I have used this type of code in sql or access to pull much more data from five or six tables and never had this problem! So why is the way I am writing my stuff now such a problem? I don't get it. Any Suggestions?

quote:

WHERE TRX.TRX.TREATMENTDATE GE '20080101';

What happens if you put a 'range' on the date? That is GE and TREATMENTDATE LE ' '?
Is this report generated from a Join structure, if so, is the date near the top of the structure?

Seems like you are asking a couple of questions here:

1. Why does the DBMS keep processing after I kill a job in WF?
2. Why is my job taking so long to run in Oracle?

First question - When WF sends a job to the DBMS, by default, it does nothing until an answer set is returned. If you tell it to stop waiting (by killing the job) nothing is sent to the DBMS so it continues processing. There is no way around this. There is, however, a setting for configuring the adapter (can't remember it or find it quickly at the moment) to tell the DBMS to stop processing every so often to check and see it the WF server is still waiting. If it is not, the DBMS then also kills the request. Causes some overhead when enabled (because it has to keep pausing to check), but fixes your problem. If you kill the job, the next time the DBMS checks and sees that WF is no longer waiting for an answer, the job dies.

Second question - there are MANY possible answers. Is treatmentdate indexed? Is treatment in apptrx a key / indexed field? If the answer to either is no, that's a quick explanation of the problem.

You probably want to turn on traces to see the exact SQL that is generated from your request. If there is more than one SQL statement, that's a problem.

Maybe you could create an Oracle view of those tables joined together and let Oracle do the work instead of the WF engine.

That should keep you busy for a few minutes ...
I'll see if I can find the setting I mentioned or someone else who knows it off the top of their head, please feel free.

quote:

JOIN TRX.TRX.TYPE IN TRX TO MULTIPLE APPTRX.APPTRX.TREATMENT IN APPTRX AS J1


TABLE FILE TRX

This should not be grinding on.

Put these in your code and if it improves things put them in EDASPROF so all jobs pick them up.

ENGINE SQLORA SET ORACHAR VAR
ENGINE SQLORA SET OPTIMIZATION ON
ENGINE SQLORA SET OPTIFTHENELSE ON
ENGINE SQLORA SET FETCHSIZE 5000
ENGINE SQLORA SET INSERTSIZE 500
-* never use this setting it causes a 2 second delay after each SQL request
-* ENGINE SQLORA SET NONBLOCK 2
ENGINE SQLORA SET VARCHAR OFF

Also check you are killing the AGENT not the BROWSER as this will not stop the Oracle Process.

As many bear witness to here, it is always a safer option to kill the thread on the RDBMS rather than kill the WF agent process. The WF agent will finish in a controlled manner as a result of the RDBMS actions.

The problem usually is actually pursuading your DBA or Sys Support person to actually kill the thread without having to cite every conceivable reason for them doing so. Then of course there are the ones that insist on having the request pushed through a change control system (they have to cover their backs - aka CYA), by which time the process been using a large CPU% thereby restricting the resources available for other threads etc.

If you are developing reports and just testing the process and the output then remember to use the available tools -

XRETRIEVAL with SQL traces to enable you to check the SQL produced.
WHERE RECORDLIMIT EQ nnnn to restrict the lines in a report
WHERE READLIMIT EQ nnnn to restrict the reads from the data source.

Only once you have the desired, efficient, SQL and the report layout, should you open the report to retrieve data by volume.

T

Thanks All I will try these methods.

Not trying to be critical of John's (hammo1j) advice (you have provided some very usefuleful info on the forum) but I think some lessons can be learned.

1.Without seeing the MFD of the file, you can't determine that the join would not cause the query to "grind on". If the CR field is not indexed, you get problems with the join, especially when it is a one to many.

2. There is probably something in the list of adapter settings that may be helpful, but many are compeltely unrelated to each other or to this issue. Be VERY CAREFUL when "simply adding" settings that change the way an adapter functions to the server profile. If they were always guaranteed to improve things, they would be the defaults. Every installation is different and will use different optimization settings.

3. Neither killing the agent nor killing the browser session will stop the process in the RDBMS. The setting that says "never use" IS the setting that allows the RDBMS to automatically stop a request that has been cancelled by the WF client. From the manual:

This feature allows the adapter to react to a client request to cancel a query while the adapter is waiting on engine processing. This wait state usually occurs during SQL parsing, before the first row of an answer set is ready for delivery to the adapter or while waiting for access to an object that has been locked by another application.

The number at the end is the number of seconds between "wake-up" periods to check and see if the request has been cancelled.

Darin

quote:

1.Without seeing the MFD of the file, you can't determine that the join would not cause the query to "grind on". If the CR field is not indexed, you get problems with the join, especially when it is a one to many.

The guy says it works ok on the raw rdbms. This might imply optimization off and a WF managed join hence the suggestion to turn it on.

quote:

2. There is probably something in the list of adapter settings that may be helpful, but many are compeltely unrelated to each other or to this issue. Be VERY CAREFUL when "simply adding" settings that change the way an adapter functions to the server profile. If they were always guaranteed to improve things, they would be the defaults. Every installation is different and will use different optimization settings.

I did suggested he played with these settings first.

quote:

3. Neither killing the agent nor killing the browser session will stop the process in the RDBMS.

I agree the only guaranteed way to stop the rdbms process is on the rdbms, however this may not always be practicable.

Killing the wf agent will stop it a lot of the time since (without going in to detail) the rdbms may effectively periodically check the requesting process to see if it is still alive. If the requesting process ie wf agent does not exist then it kill the rdbms process since there no point in sending back info with no one to receive it. Not a guarantee but well worth trying whereas closing the browser will have absolutely no effect.

quote:

3. The setting that says "never use" IS the setting that allows the RDBMS to automatically stop a request that has been cancelled by the WF client. From the manual:

If you play with this you will find that it does not work as the manual says and will give you a lot of grief with the wf managed join.