diff between DB2/UDB enterprise stage and DB2/UDB API stage

kavuri · Post by **kavuri** » Fri Jul 13, 2007 11:10 am

Hi ,
can anybody explain me what is the difference between Datastage DB2/UDB Enterprise stage and datastage DB2/UDB API stage and DB2/UDB Load stage. In what situations which need to be utilised.

Thanks in advance

kavuri

bcarlson · Post by **bcarlson** » Fri Jul 13, 2007 11:41 am

I highly recommend searching the forum - this has been addressed multiple times.

My 2 cents - IBM recommended to us that the Enterprise stage is usually our best option. The API and Load stages have more options and may be more flexible, but won't necessarily be as efficient because the data transfer between DataStage and database is one stream of data vs. parallel streams as occurs with the Enterprise stage.

We have yet to find a good reason to use anything but the Enterprise stage and have had extremely good performance.

Brad.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 13, 2007 3:53 pm

There is (at least at version 7.5 and below) an issue with the DB2 Enterprise stage that prevents it from being used to work with data on a different platform. With DataStage on UNIX you must use the DB2 API stage to access DB2 data on AS/400 and mainframe platforms, via the DB2 Connect client software.

Apart from that, the DB2 Enterprise stage is definitely to be preferred. It permits innate parallelism, whereas the default operating mode of the DB2 API stage is sequential.

bcarlson · Post by **bcarlson** » Fri Jul 13, 2007 4:21 pm

One way to get around the issue Ray mentions:

We are using DB2 Enterprise to access mainframe DB2, but we have a Federated connection between the databases to DataStage thinks it is talking to a local DB2 database.

Not sure if that is more or less efficient that using the API stage. It does make it easier to query - you can use the Federated link to run command line queries from Unix, too, without have go MVS.

Brad.

vnspn · Post by **vnspn** » Tue Nov 13, 2007 3:52 pm

Hi,

We have DB2 on the Mainframes and the connection from the DataStage server is using DB2 Connect client software.

Does it mean that we cannot use the DB2 Enterprise stage to conenct to the DB2 database?

Thanks.

bcarlson · Post by **bcarlson** » Tue Nov 13, 2007 3:57 pm

Does the DB2 connect client software allow you to run a query against the DB2 mainframe table from your local DB2 server?

If so, then you can use DB2 Ent to query the table. - it has no idea the ultimate source of the data is the mainframe - all it 'sees' is that it is running a query against your local database.

Brad.

vnspn · Post by **vnspn** » Tue Nov 13, 2007 4:50 pm

bcarlson wrote:Does the DB2 connect client software allow you to run a query against the DB2 mainframe table from your local DB2 server?

Could you please let us know what do you mean by "local DB2 server"

We are able to connect to this DB2 database and query on it from the client software thats on the desktop.

When we use the DB2 API stage, all we need to provide in the stage properties are the Server name, User ID and Password.

When we replace that with a DB2 Enterprise stage, it requests for i) Client Instance Name, ii) User, iii) Password. Also, when the Default Database and the Default Server are set to 'False', it requests for a Database and Server name. So, the uncertainty that we had was, what were the actual values that needs to be given for each of these corresponding properties.

bcarlson · Post by **bcarlson** » Tue Nov 13, 2007 5:05 pm

In our environment, we have our own DB2 database running on AIX. Basically, it runs on the same server as DataStage. That is our local database. We also have a mainframe database that we sometimes reference. We use DB2 Connect to create a federated link between our local database and the mainframe database. This allows us to create a nickname on our local database that is actually pointing to a table on the mainframe.

It is like a file or directory shortcut in Windows, or a link in Unix. I could create a shortcut on my laptop's harddrive that is actually pointing to a file on the network.

I connect to my own local database and write a query against the nickname just like a normal table. Behind the scenes, my local DB2 is talking to the mainframe DB2 passing along the query and returning all the data. By the way, DB2 then takes care of the EBCDIC to ASCII conversion, too.

If you have this kind of setup, then you can use whatever DataStage stage you want. Any stage that works with a DB2 UDB table will work with the nickname. So yes, you can use the Enterprise stage because all DataStage knows is that you are connecting to your own local DB2 database not some mainframe somewhere else.

Brad.

vnspn · Post by **vnspn** » Thu Nov 15, 2007 11:50 pm

Thanks Brad for your detailed information.

We dont have a setup like the one you have mentioned. We have a database only on the Mainframes and nothing on the DataStage server. In this scenario, would it be possible to use the DB2 Enterprise stage?

Our other question is, what would be the value you would be giving to each of these properties - Client Instance name, Database name and the Server name. For one of these, I think you would be providing the name of your local DB2 server; but what would be the values for the other two properties.

vnspn · Post by **vnspn** » Fri Nov 16, 2007 5:13 pm

bcarlson wrote:We have yet to find a good reason to use anything but the Enterprise stage and have had extremely good performance.

Hi Brad,

When you say extremely good performance, what is the approximate rows / sec processed from the DB2 stage?

Thanks.

ray.wurlod · Post by **ray.wurlod** » Sat Nov 17, 2007 1:34 pm

Do you mean single-byte rows or million-byte rows? Rows/sec is an almost totally meaningless throughput metric.