SELECT rows from table

sainath · Post by **sainath** » Thu Oct 27, 2011 1:31 pm

Hi

I am selecting 10 million from a DB2 TABLE in Z/Linux .but i am getting error as limit reached time out..

how can i fix this issue ...how can i fine tune this in datastage ...
DBA dont want to increase time ... and the table is not yet partitioned ...

ray.wurlod · Post by **ray.wurlod** » Thu Oct 27, 2011 2:57 pm

Eliminating any GROUP BY or ORDER BY clause in your SELECT statement should allow Oracle to stream data into your job as they are retrieved. Unless, of course, Oracle has to materialize a view or perform some other operation that takes too long before being able to deliver rows.

jwiles · Post by **jwiles** » Thu Oct 27, 2011 9:55 pm

5 CPU seconds and 50000 service units seem pretty small...sounds as if the database may normally be used and tuned for small transaction processing rather than batch extracts.

If you're performing a join within DB2Z you may wish to move it into the datastage job instead and perform just simple selects from the tables.

With the DB2 connector you can perform a Modulus partitioned read based on an integer table column if available, so long as the partitioning won't affect your query results in the end.

Regards,