Sparse lookup on Mainframe db2 using Db2 connector

raghav_ds · Post by **raghav_ds** » Wed Aug 24, 2011 11:52 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Aug 25, 2011 1:26 am

Not directly.

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

Mike · Post by **Mike** » Thu Aug 25, 2011 6:16 am

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

raghav_ds · Post by **raghav_ds** » Thu Aug 25, 2011 8:44 am

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.

kommven · Post by **kommven** » Thu Aug 25, 2011 10:40 am

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?

ray.wurlod · Post by **ray.wurlod** » Thu Aug 25, 2011 3:32 pm

You have conflicting requirements - sparse lookup and fast performance.

raghav_ds · Post by **raghav_ds** » Mon Aug 29, 2011 11:12 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 29, 2011 3:23 pm

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.

raghav_ds · Post by **raghav_ds** » Tue Aug 30, 2011 6:08 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 30, 2011 10:16 pm

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

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