Data load in DB2

[sai1001]

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.

[docdiesel]

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

db2 load from myfile.xxx of filetype insert into schema.table
 

where filetype maybe ASC (Text, fixed length), DEL (Text, delimited) or IXF (DB2 interchange format, binary).

Regards,

Bernd

[sai1001]

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

db2 load from myfile.xxx of filetype insert into schema.table
 

where filetype maybe ASC (Text, fixed length), DEL (Text, delimited) or IXF (DB2 interchange format, binary).

Regards,

Bernd

[docdiesel]

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

[sai1001]

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.

[sai1001]

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.

[docdiesel]

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

db2 connect to REMOTEDB
db2 load client from c:\temp\data.txt modified by coldel0x7c replace into MYSCHEM.MYTABLE
 

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

[sai1001]

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

db2 connect to REMOTEDB
db2 load client from c:\temp\data.txt modified by coldel0x7c replace into MYSCHEM.MYTABLE
 

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

[docdiesel]

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

[sai1001]

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

[docdiesel]

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