How to retrieve or insert data into DB2

kanasai167 · Post by **kanasai167** » Fri Oct 14, 2011 2:48 am

Which job is better for doing this , parallel job or server job ?

And i read in the IBM document on version 8.5 about retrieving/insert data to db2 , there is something called db2 connector but i cant find it in my Quality Stage. But i found something like this .. is this the one so called db2 connector in 8.1 ?

http://imageshack.us/f/195/qualitystagedb2connecto.png

btw im able to view the data in my db2 right now , but how to insert or retrieve data into db2 inside Datastage ?

It would be nice if someone can share me a tutorial document on this , i google out but cant find.

Thank you.

max.madsen · Post by **max.madsen** » Fri Oct 14, 2011 7:35 am

First, Parallel or Server job? its depends on your need.

kanasai167 wrote:btw im able to view the data in my db2 right now , but how to insert or retrieve data into db2 inside Datastage ?

Try use link against DB2 Stage to create input/output.

kanasai167 · Post by **kanasai167** » Sun Oct 16, 2011 1:16 am

i able to get data from db2 and insert into textfile now.

DB2 UDB API ---> Sequential file

But when i cant insert data from textfile into my table in db2 , what im trying to do is something like this :
Sequential file ---> DB2 UDB API

Do i have to add other task after DB2 UDB API in order to insert the data into db2 ??

chulett · Post by **chulett** » Sun Oct 16, 2011 8:05 am

There's no reason you would need to do this in two halves, but you certainly can do it that way if you like. If you are having some kind of a problem getting the data from the flat file into the database, tell us why, what errors or issues you are facing. Simply saying you "can't insert data" doesn't help anyone help you.

kanasai167 · Post by **kanasai167** » Mon Oct 17, 2011 7:24 am

of course i need to know correct job flow to insert data into db2 first then i only can get the "errors/issues" when insert data into db2....

i could not find any tutorial teaching how to get data from flat file into the database....

chulett · Post by **chulett** » Mon Oct 17, 2011 7:32 am

kanasai167 wrote:what im trying to do is something like this :
Sequential file ---> DB2 UDB API

What happened with this? That should be a perfectly valid job design, what issues did you have when you tried this?

ray.wurlod · Post by **ray.wurlod** » Mon Oct 17, 2011 3:31 pm

kanasai167 wrote:i could not find any tutorial teaching how to get data from flat file into the database....

IBM class DataStage Essentials will teach you this knowledge.

BradMiller · Post by **BradMiller** » Mon Oct 17, 2011 4:56 pm

To answer your initial question, the screen-shot you provided is the DB2 UDB API Connector, not the DB2 Connector. The DB2 Connector is available as of 8.1, but I believe you have to install Fix Pack 1 first. I would recommend using the DB2 Connector since it has better error handling.

Both the Connector Stage and the API Stage are fairly straight-forward. If you need assistance in configuring the stages, check out your documentation, especially the Connectivity Guide for DB2 Databases.

kanasai167 · Post by **kanasai167** » Mon Oct 17, 2011 5:45 pm

ok thanks , i try look through for the IBM class DataStage Essentials and install the fixpack.

Keep on seeing people saying DB2 connector...i seriously got no idea what is it , now only i know i had to install the Fix Pack 1 first then only got this thing....thanks again...

kanasai167 · Post by **kanasai167** » Mon Oct 17, 2011 8:47 pm

chulett wrote:
kanasai167 wrote:what im trying to do is something like this :
Sequential file ---> DB2 UDB API
What happened with this? That should be a perfectly valid job design, what issues did you have when you tried this?

http://imageshack.us/f/528/qualitystagedb2002.png/

im stuck here ,
the table i put at the "1.Select tables" is the table already exist in db2 which i already add it into the table definitions earlier.
At the "Insert value" , what is the values i should put over there because the data i want to insert is in the text file.

thanks

chulett · Post by **chulett** » Mon Oct 17, 2011 9:56 pm

That's the SQL Builder, something you should only "need" if for some reason you can't let the stage generate the SQL for you automatically and you have no idea how to write it yourself. And it's for selecting data, not inserting it. No need for that here.

At a high level - build the job like you posted and then set the properties in the DB2 stage that tell it what database to connect to, the table metadata involved and that you want to insert into the table. The stage will (basically) do the rest.

More information can be had in the various guides, including the Connectivity Guide for IBM DB2 Databases pdf, there's a chapter in it for each DB2 stage type. Check the appropriate section "Input" section.

kanasai167 · Post by **kanasai167** » Tue Oct 18, 2011 4:19 am

http://imageshack.us/photo/my-images/25 ... b2004.png/

Im using the "Generate Update Action..." , after i compile and run the job , finally the data is inserted into DB2.

Currently my data is something like this :
Name Amount
test001,50
test002,100
test003,1

But the data inserted into db2 is like this :
Name Amount
???,50
???,100
???,1

Why the data in Name column are all ??? ....

I tried do another job using ODBC_Connector , i got no problem insert data into DB2.

Btw , What is the different between ODBC_Connector and DB2 UDB API?

manoj_23sakthi · Post by **manoj_23sakthi** » Tue Oct 18, 2011 4:39 am

Hi,
DB2 API stage is native stage for Db2 its gives better performance compare to ODBC connector...
Could u conform the metadata data type the corresponing table and u mapped in the stage

Regards,
Manoj Kumar

ray.wurlod · Post by **ray.wurlod** » Tue Oct 18, 2011 3:45 pm

U (who is one of our posters) was not involved.

The second person personal pronoun in English is spelled "you".

Please strive for a professional standard of written English on DSXchange, not least because it helps those whose first language is not English.

How to retrieve or insert data into DB2

How to retrieve or insert data into DB2

Re: How to retrieve or insert data into DB2