Data Load mechanism with ODBC Enterprise Stage
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
Data Load mechanism with ODBC Enterprise Stage
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.
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.
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.DSguru2B wrote:I think the OP is referring to the db2 bulk loader.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
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?
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
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.
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.
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. ![Laughing :lol:](./images/smilies/icon_lol.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle