How to Select All records at once?

[DCSD]

I am reading 600 million records from the database and I don't want it to read 2000 records at a time because that would be too many hits against the database. I want to read all records and store it into a sequential file so I don't have to keep going back to the database.

How do I get it to 'Select All' rather than chunks at a time?

Thanks.

[ray.wurlod]

Assuming your database server supports it (which I doubt) you could declare an array size of 600000000.

There's nothing wrong with array size of 2000, it's quite efficient, particularly if the database server handles arrays correctly.

[djbarham]

I am reading 600 million records from the database and I don't want it to read 2000 records at a time because that would be too many hits against the database.

I think you misunderstand what ths assay size is doing.

It is NOT generating 300,000 separate queries against the database where each retrieves 2000 rows.

It is one query against the database.

What the array size does is bundle the results of the query into packets of 2000 records for transmission from the database server to the DataStage server.

As you increase the array size, your throughput will increase. However, I think you will find that past about 2000 rows, are further performance gains will be barely measureable ... and there will be physical limitations that you run into.

Keep in mind too that ALL software that interacts with a database must interact in this same way. (Although some primative ODBC drivers have an effective array size of 1).

As Ray said, 2000 is fine.