Restricting rows retried when viewing data

ewartpm · Post by **ewartpm** » Tue May 24, 2005 5:01 am

I want to restrict developers from viewing large files from within the DS Designer module. Why, because they press Ctrl Alt Del when they do not get a reponse and it is causing problems.

Typically, I would only like to have the query return 100 rows.

Is there anyway to set this in DS via, for example, the config file.

Many thanks

chulett · Post by **chulett** » Tue May 24, 2005 6:47 am

Depends on what you mean by "files". For sequential files, the answer in Server is no. And yes, that does imply that in EE/PX jobs the answer is yes.

For "queries" via a DBMS type stage, the answer is you need to handle that yourself in the query - you need to do the limiting in the database. Otherwise a large query might still take a great deal of time to run and then DataStage would say "Fine, show me only the first 100 rows".

We generally add a bogus WHERE clause fragment parameter for testing. For production it is set to "1=1" so it doesn't have any adverse effects but in testing (especially when Viewing data) it can be used to restrict the query to produce a smaller result set by adding something appropriate for the data being queried. Date = something, key field = something - as long as it is syntactically correct and will actually narrow your results down. Obviously, some things could narrow the result but do so in such a way that the query actually takes longer - so be aware of that.

I want to restrict developers from viewing large files from within the DS Designer module. Why, because they press Ctrl Alt Del when they do not get a reponse and it is causing problems.

Tell them to knock if off. Counsel patience, that or get a bigger stick.

ray.wurlod · Post by **ray.wurlod** » Tue May 24, 2005 6:56 am

Or...

get a bigger stick then counsel patience

(It's no coincidence that SAP also means South African Police!)

vmcburney · Post by **vmcburney** » Tue May 24, 2005 5:47 pm

Your DataStage Designer only brings across a couple hundred rows of data in any view data statement. Your problem is the duration of the SQL select itself. It could be your SQL is not properly tuned, or you are missing database indexes, or your SQL statement is too complex and some tables need to be taken out of the join and moved into a lookup.

If you have easy access to DBA support you could tell developers that instead of killing the job to stop a long running SQL they should contact the DBA and get the query killed.

If developers want to check the SQL through view data and they know it is a long running SQL then they should follow Craig's advice and add a very narrow filter to the statement such as an employee id. If you modify the SQL and view data and then cancel out of the stage you revert back to the original SQL.

ewartpm · Post by **ewartpm** » Thu May 26, 2005 2:29 am

Thanks everyone for the replies.

You will be please to know, I have bought a stun gun, to &&#% with a big stick