DSXchange

Before you blame DataStage, how long does it take in PL/SQL? And what is the SQL statement you are using? It should be something like
SELECT contract_no FROM table WHERE id = (SELECT MAX(id) FROM table) ;

The index on id should mean that the subquery is executed fast and the outer query is executed fast. It will only ever return one row. Is this what you intend? It sounds like you're seeking to generate the next contract numbers.

In this case, pre-load the result of the above query into a hashed file. It has, for example, a key column whose value is the string "START_CONTRACT_NO" and a non-key column called, say, StartContractNo, whose value is the result of the query.

In a Transformer stage, perform a lookup against this hashed file, with a reference key expression of "START_CONTRACT_NO", and derive the new contract numbers with an expression such as
RefLink.StartContractNo + @OUTROWNUM

PS If you're going to make money out of this reply, can I have some?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Raju

I wonder what your query looks like, do you have a subselect to get the max(id) like

select contract_no, id
from table a
where id=
(select max(id)
from table b
where a.contract_id=b.contract_id)

which i would expect to be slow, or do you

select contract_no, max(id)
from table
group by contract_no

which might be faster. Did you try to read the data into a hash file(key field=contract_id), using a query like:

select contract_no, id
from table
order by id

As there is an index on the id, it should be possible to read the table along it. You would also ensure that you get the max(id) per contract.

Are there additional filters in your sql?

Regards


Stephan