Limiting text file SQL queries

SIHM · Post by **SIHM** » Fri Mar 14, 2014 9:00 am

We have a design rule to ensure that all table extractions run via one job and pass the SQL in as a file.

This is to ensure unity in development.

Addtionally we also impose a limit of 100 rows per node in development so as not to overload the datastage and Oracle server with an unnecessary charge.

However, when we then move our jobs to production, we have to modify by hand all of our SQL statements to remove the ROWNUM < 100.

Given that we can't put parameters in SQL scripts, is there any simple way of restricting the number of rows read by an Oracle stage

thompsonp · Post by **thompsonp** » Fri Mar 14, 2014 9:12 am

It's not the normal use for it, but you could look at the virtual private database functiionality within Oracle and use the predicate applied for a context to add the rownum clause.

It's pretty starighforward, but might be a siginifcant overhead to setup if you have lots of tables on which to add policies.

Looking at the reason behind this, why not approach it from the other direction and instead of limiting the amount of data you can select (which doesn't necessarily stop you placing a large load on the DB - if the query is complex or poorly written but still only returns a few rows), and just cut back on the amount of data stored in the development database.
Presumably you have a performance test environment with proper volumes.

Personally I'd prefer to test the functionality of my jobs in a dev environment with reasonable volumes of data that will show up performance problems and would question the limit that has been imposed.

thompsonp · Post by **thompsonp** » Fri Mar 14, 2014 9:15 am

Forgot to say that changing the code (in this case sql) when you move to production sounds like a recipe for getting typos and defects.

I hope you meant when you move from dev to another test environment, and then no code changes when moving to production.

SIHM · Post by **SIHM** » Fri Mar 14, 2014 9:18 am

In an ideal situation, we would have a low volume environment but unfortunately we have many 3rd party databases to which we have to share access with other non Datastage teams.

I like the idea you have proposed unfortunately it would be unworkable for the environment we have as we run into 100s possibly 1000s of tables.

I deliberately imposed this limitation as we often have developers who run full data migrations on the system which is unnecessary and causes delays for other developers.

We have other environments which allow for volume testing which often go for months without use.