DSXchange

Can we do Sparse look-up on Mainframe db2 table. As per my understanding DB2 EE stage in 7.5 does not connect to Mainframe and DB2 API does not support sparse look-up.

Do we have any extra features in v8.1 to do a sparse look-up on Mainframe db2

Not directly.

If you have a local DB2 instance, you may be able to map the mainframe DB2 through that.

The DB2 Connector at 8.1 does not support a sparse lookup (doesn't matter where the database lives). We used the ODBC Connector to do a sparse lookup against a mainframe DB2 instance.

Mike

We are planning to use this sparse lookup in a DataStage Job that will be exposed as a web-service. The web-service performance should be very high.

These are the two options I am thinking of

1) use ODBC connector and do a sparse look on Mainframe db2.
2) Load Mainframe db2 data into local aix db2 and do sparse lookup on this database using db2 connector.

Please provide your thoughts which option will be the right way.

For any database,
Connector is best Vs ODBC

For Sparse, same rule Input data Volume Vs lookup data volume.

BUT, I won't encourage option #2 as there is another process of replicating AIX version of Mainframe table.

As Ray mentioned, How about federation?

You have conflicting requirements - sparse lookup and fast performance.

Ray,

The deisgn is for a web service, where we will have one request (one record), which will have to find records in a reference database where we have few million rows.

So I thought Sparse is a best fit here. And the performance I am talking about is firing the query on database and get the results. Will ODBC connector works fine in this scenario.

A few million rows (being selective about the columns - only the ones you need) may well fit in memory and be amenable to normal, local lookup, which is fast. Use an always-on job so that the reference data set remains loaded in memory.

We are planning to use always on job, which will be accessed by concurrent users. The reference data with minimal number of columns is around 2 GB. And when we expose this job as webservice, we might need minimum 3-4 instances of the service running in ISD for supporting concurrent requests.

Since it is consuming lot of memory, we wanted to go for a sparse lookup. The other point we have analyzed is that the normal local lookup data will be loaded into the memory at the start of the service and is not available for any updates. We will have few real time updates everyday on the reference, which should be availble for the reference making the service search near real time.

We are planning to load Mainframe data to a local aix database table and use db2 connector to do sparse lookup.

I'd still look at getting more memory and using local lookup.

Sparse lookup is necessarily slow. Think about what it has to do.