By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Data load in DB2

P: 8
Hi,

my job is converting informix queries to db2.
There is a load function in informix to load the data from flat file to sql file.
Can you please any body help how load function works in db2.

i am using db2 v8.1 version.

please help anybody, its urgent.

Thanks in advance.
Aug 17 '07 #1
Share this Question
Share on Google+
10 Replies


docdiesel
Expert 100+
P: 297
Hi sai1001,

if you're working on DB2 LUW, you'll find the docs to db2 load at http://publib.boulder.ibm.com/infoce...e/r0008305.htm .
Basically its

Expand|Select|Wrap|Line Numbers
  1. db2 load from myfile.xxx of filetype insert into schema.table
  2.  
where filetype maybe ASC (Text, fixed length), DEL (Text, delimited) or IXF (DB2 interchange format, binary).

Regards,

Bernd
Aug 17 '07 #2

P: 8
Hi Bernd,
Thanks for your reply. But here I am using db2 command line editor for converting queries from informix to db2.

For below command what you have given me I was trying in db2 command line processor.

Before executing the command , i created one txt file and I created one temp table and I am trying to load the data from txt file to temp table using
the command " db2 load from myfile.xxx of filetype insert into schema.table"

But here I am getting error like ' the remote database does not support the command or one of the command options.
And what is DB2 LUW i don't understand. If you understand my problem please give me a reply.

Thanks



Hi sai1001,

if you're working on DB2 LUW, you'll find the docs to db2 load at http://publib.boulder.ibm.com/infoce...e/r0008305.htm .
Basically its

Expand|Select|Wrap|Line Numbers
  1. db2 load from myfile.xxx of filetype insert into schema.table
  2.  
where filetype maybe ASC (Text, fixed length), DEL (Text, delimited) or IXF (DB2 interchange format, binary).

Regards,

Bernd
Aug 17 '07 #3

docdiesel
Expert 100+
P: 297
Hi sai1001,

well, let's get a fresh start:
  • What version of DB2 are your going to use
  • on what platform (Btw., LUW=Linux, Unixes, Window$)
  • Are you working directly on the server or remotely
  • Where do you place the text data
  • What delimiters do you use
  • Do you have admin access to DB2

If you're working on Linux, put a small amount of rows (up to 10) of your data in a temporary file like /tmp/testdata.txt - in the beginning you'd better work with absolute path and keep time for test cycles short and size of output small.

Regards,

Bernd
Aug 18 '07 #4

P: 8
Hi Brend,
Thanks for your reply.
Here are my answers to your questions...
[*] What version of DB2 are your going to use ( DB2 v 8.1)[*] on what platform (Btw., LUW=Linux, Unixes, Window$)( On windows)[*] Are you working directly on the server or remotely (working remotely)[*] Where do you place the text data (in our local system)[*] What delimiters do you use ( we use delimiters comma and pipe)[*] Do you have admin access to DB2 (no)[/list]
Note:I want to load the data from text file to sql file.

I will be waiting for your reply.

Thanks
Sai.
Aug 20 '07 #5

P: 8
can you please anybody help me....
Thanks
sai



Hi,

my job is converting informix queries to db2.
There is a load function in informix to load the data from flat file to sql file.
Can you please any body help how load function works in db2.

i am using db2 v8.1 version.

please help anybody, its urgent.

Thanks in advance.
Aug 20 '07 #6

docdiesel
Expert 100+
P: 297
Hi Sai,

if working remotely, I guess you already have cataloged the DB on your local machine? You can do this using the graphical interface or the db2 command line.

In the following lines I assume that
  • the database is cataloged as REMOTEDB
  • target table is MYSCHEM.MYTABLE
  • the data is placed in c:\temp as file data.txt

Then yout load command in db2cmd command line window should be
Expand|Select|Wrap|Line Numbers
  1. db2 connect to REMOTEDB
  2. db2 load client from c:\temp\data.txt modified by coldel0x7c replace into MYSCHEM.MYTABLE
  3.  
If you're adding data to the table, replace the 'replace' by an 'insert'. The 'client' keyword tells db2 to load from your local client into a remote db.

The coldel0x7c is for pipe ('|', ascii 0x7c) as column delimiter. Maybe you'll have to add a chardel0x2c for the comma (','), but this should be default, as far as I recall.

Hint: If something goes wrong and the content of the table is all in a mess, do a load from an empty file. That's a smart, proper and real quick cleanup.

Good luck,

Bernd
Aug 20 '07 #7

P: 8
Hi,
Thanks for your reply,
But here I am trying to load the data into a temporary table. Here I am creating the temporary table like this..
ex:
declare global temporary table temp (name char(10)........);

I have a doubt like can i load the data into a temporary table or not?
why am I asking is because I don't have a privilages to create a new table.
and i want to use this temp table in select statement by using join conditions.

Please help


Thanks
Sai


Hi Sai,

if working remotely, I guess you already have cataloged the DB on your local machine? You can do this using the graphical interface or the db2 command line.

In the following lines I assume that
  • the database is cataloged as REMOTEDB
  • target table is MYSCHEM.MYTABLE
  • the data is placed in c:\temp as file data.txt

Then yout load command in db2cmd command line window should be
Expand|Select|Wrap|Line Numbers
  1. db2 connect to REMOTEDB
  2. db2 load client from c:\temp\data.txt modified by coldel0x7c replace into MYSCHEM.MYTABLE
  3.  
If you're adding data to the table, replace the 'replace' by an 'insert'. The 'client' keyword tells db2 to load from your local client into a remote db.

The coldel0x7c is for pipe ('|', ascii 0x7c) as column delimiter. Maybe you'll have to add a chardel0x2c for the comma (','), but this should be default, as far as I recall.

Hint: If something goes wrong and the content of the table is all in a mess, do a load from an empty file. That's a smart, proper and real quick cleanup.

Good luck,

Bernd
Aug 21 '07 #8

docdiesel
Expert 100+
P: 297
Hi Sai,

...
I have a doubt like can i load the data into a temporary table or not?
...
never worked with temp. tables so far. Sorry, can't answer you that one.

Regards,

Bernd
Aug 21 '07 #9

P: 8
Thanks for your reply.

I have one more question. I am working on windows environment, is the load functions works in windows environment (using command line editor) to load the data in mainframe db2 or not?

I will be waiting for your reply.

Thanks
Sai



Hi Sai,


never worked with temp. tables so far. Sorry, can't answer you that one.

Regards,

Bernd
Aug 22 '07 #10

docdiesel
Expert 100+
P: 297
Hi Sai,

so you're working with DB2 Connect? Hm, as far as I recall it's working the same way as a standard runtime client. The main difference is the ablility to connect to mainframe DB2.

The client side is the same. Therefore I bet it's loading into whatever remote DB you like, whether situated on a Windows, Linux or Mainframe system. Imho there's no reason why it shouldn't.

Regards,

Bernd
Aug 23 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.