Data Load mechanism with ODBC Enterprise Stage

Inquisitive · Post by **Inquisitive** » Tue Jan 09, 2007 9:15 am

Hi,

We are planning to use ODBC Enterprise stage to load data into Db2 databse.
Can any of you please clarify what data loading mechanism does ODBC enterprise stage uses?

Is it SQL loader or Database Insert (in which chase Db2 fires inert query for each row).

Due to OS Version mismatch on ETL server (RHEL 3.0) and Db2 Server (RHEL 4.0) we were not able to set up Db2 Enterprise stage connectivity.

IS there any option to use SQL loader with ODBC enterprise stage? If not, is there any way how we can avoid db2 from firing insert queries for each input row from Datastage (in which case it will take pre-load image and post load image for rollback and would be a great performance hit).

Thanks in advance for inputs.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 9:44 am

Why not use a command-line load? The ODBC stage uses DML, so you're limited to that. As for SQL loader, I'm not familiar with that terminology outside of Oracle. What about a stored procedure?

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 9:46 am

I think the OP is referring to the db2 bulk loader.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 9:56 am

DSguru2B wrote:I think the OP is referring to the db2 bulk loader.

That's what I thought as well, using sql*loader as generic slang for bulk loading. But, since they said DB2 Enterprise connectivity wouldn't work because of version compatibility, I assumed this rules out API and Load stages.

Inquisitive · Post by **Inquisitive** » Tue Jan 09, 2007 9:58 am

Thanks For your replies,

I was referring Db2 Bulk loader.

Now, can I trigger Command line load from Database stages? (ODBC Ent Stage) Can I use Db2 bulk load mechanism with Command line load?

Inquisitive · Post by **Inquisitive** » Tue Jan 09, 2007 10:07 am

I have Db2 Client installed on the ETL server and I am able to connect to Db2 database through Db2 Client on ETL server.

Now, this should be sufficient condition to use API or load stages right?

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 10:10 am

Test it out and let us know what you find. See if you are actually able to connect through the DB2 API stage.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 10:14 am

You can ALWAYS use command line. You may have to move files off the DataStage server and local to the database server and then remote shell (ssh, rsh) execute the bulk load command line.

If you have the DB2 Client on the DataStage server and have no version compatibility issues, aren't you still in the same situation of wanting to avoid using DML based loading?

Inquisitive · Post by **Inquisitive** » Tue Jan 09, 2007 10:29 am

Version compatability issue is stopping me from installing PX component on Db2 servers which is required to have Native stage connectivity.

But having Db2 clinet installed on ETL servers I am using Db2 Clint to connect and talk to Db2 servers and my ETL server acts as a pure Client.

So my understnading is I can API stage just by having Db2 client installed on ETL server ( Do we need any additional set up??) but for Db2 Entperprise stage connection we need to have PX components installed on Db2 servers and we need to run Db2set.sh.. db2env.sh and some more set up done.

Is my understanding right?

Also, do you know additional requiremtns for having API connection?

Thanks.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 10:34 am

I believe your understanding is correct. Have you tried importing metadata using the DB2 API plugin? Importing metadata uses Server technology and will verify your Client installation works, but the real test is from the DB2 API stage, which uses PX technology. You're most of the way there if you can import metadata. As for requirements, you're going to probably make changes to the dsenv file per the configuration notes. I'm not an installation expert, so others need to contribute. Of course, you are paying IBM Tech Support to help you out as well.