Run DB2 export command in loop


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Run DB2 export command in loop
# 1  
Old 08-04-2010
Run DB2 export command in loop

Hi All,

I have list of 100 table names in a file, how to read table name from and pass to DB2 export command and run for all tables in loop.

Please help me with script.

Code:
db2 EXPORT TO ~/data_export/<table name from file>.ixf OF IXF MESSAGES messages.txt "SELECT * FROM ITG.<Table Name from file>"

Thanks
Srimitta

Last edited by Scott; 08-04-2010 at 07:55 PM.. Reason: Added code tags
# 2  
Old 08-04-2010
Code:
$ cat file_with_table_names
table1
table2
table3

$ xargs -I{} echo db2 EXPORT TO \~/data_export/{}.ixf OF IXF MESSAGES messages.txt \"SELECT \* FROM ITG.{}\" < file_with_table_names | sh


Last edited by Scott; 08-04-2010 at 07:26 PM..
# 3  
Old 08-04-2010
Hi Scott,

Thanks for the reply.

I ran script and getting below error message, any idea.

Code:
$ ksh -x export_db2_tables.ksh
+ cd /home/db2inst2/data_export/scripts/
+ cat table_names
table1
table2
table3
+ db2 connect to ITG

   Database Connection Information

 Database server        = DB2/AIX64 9.5.2
 SQL authorization ID   = DB2INST1
 Local database alias   = ITG

+ xargs -I{} db2 EXPORT TO ~/data_export/{}.ixf OF IXF MESSAGES messages.txt "SELECT * FROM ITG.{}"
+ 0< table_names
+ sh
sh: SQL0104N:  not found.
sh[2]: found:  not found.
sh[4]: SQL0104N:  not found.
sh[5]: found:  not found.
sh[7]: SQL0104N:  not found.
sh[8]: was:  not found.

Thanks
Srimitta

Last edited by Scott; 08-04-2010 at 07:56 PM.. Reason: Added code tags
# 4  
Old 08-04-2010
Hi.

Sorry about that Smilie

Code:
while read TABLE; do
  db2 EXPORT TO ~/data_export/$TABLE.ixf OF IXF MESSAGES messages.txt "SELECT * FROM ITG.$TABLE"
done < file_with_table_names

My globbing brain is not engaged! If it doesn't work, try changing * to \*
# 5  
Old 08-05-2010
Thanks Scott, Excellent it's working.

Srimitta
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Homework & Coursework Questions

DB2 Export and Import Oracle

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: is this enough to make the data perfect export into delimited file? there are some posted that i read, they... (9 Replies)
Discussion started by: Sonny_103024
9 Replies

2. Homework & Coursework Questions

DB2 Export and Import Oracle

Hi Guys, I Just wanted your opinion/ suggestion/ Help on my unix script about db2 export data with deli file and import into oracle. db2 connect to Tablename user id using psswrd db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/AAAAA.DEL' OF DEL select * FROM AAAAA" db2 "EXPORT TO... (3 Replies)
Discussion started by: Sonny_103024
3 Replies

3. Shell Programming and Scripting

How to use a loop for multiple files in a folder to run awk command?

Dear folks I have two data set which there names are "final.map" and "1.geno" and look like this structures: final.map: gi|358485511|ref|NC_006088.3| 2044 gi|358485511|ref|NC_006088.3| 2048 gi|358485511|ref|NC_006088.3| 2187 gi|358485511|ref|NC_006088.3| 17654 ... (2 Replies)
Discussion started by: sajmar
2 Replies

4. UNIX for Advanced & Expert Users

Using xml to run db2 commands

Hi, Strange problem I'm having here and not sure what to do. I have here a xml that is supposed to execute the next value in the schema but it isn't do the job it's supposed to do. I'm not a dba or a developer so I'm sorry in advance if there are some things i don't understand. when we run... (5 Replies)
Discussion started by: techy1
5 Replies

5. Shell Programming and Scripting

Script for telnet and run one command kill it and run another command using while loop

( sleep 3 echo ${LOGIN} sleep 2 echo ${PSWD} sleep 2 while read line do echo "$line" PID=$? sleep 2 kill -9 $PID done < temp sleep 5 echo "exit" ) | telnet ${HOST} while is executing only command and exits. (5 Replies)
Discussion started by: sooda
5 Replies

6. Shell Programming and Scripting

Run script to export the data to ixf file in loop

Hi, I am trying to export the data to an .ixf file. I have read the table names from a .dat file and those table name should be passed to the select * from schema.TABLENAME query . I am trying the below loop while read TABLE; do db2 EXPORT TO ~/data_export/$TABLE.ixf OF IXF MESSAGES... (5 Replies)
Discussion started by: vikyalex4
5 Replies

7. Shell Programming and Scripting

How do I run a shell command in a while loop?

The command is: sic -h irc.freenode.net 2>&1 | tee -a irc.log Where sic is an IRC client, and I'm piping the output to tee in order to log my IRC sessions. I'm trying to handle reconnects by running it in a while loop in the shell process and cat the initial commands into sic's stdin. I... (1 Reply)
Discussion started by: guitarscn
1 Replies

8. Shell Programming and Scripting

Error: SQL3100W while export in DB2

Problem has been resolved (7 Replies)
Discussion started by: ustechie
7 Replies

9. Shell Programming and Scripting

Export data from DB2 table to .txt file(space delimited)

Hi I need help on this. Its very urgent for me.. please try to help me out.. I have data in tables in DB2 database. I would like to export the data from DB2 tables into a text file, which has to be space delimited. so that I can carry out awk, grep operations on that file. I tried to export... (2 Replies)
Discussion started by: ss3944
2 Replies

10. Shell Programming and Scripting

for loop with db2 command

Having some trouble with usage of for loop and displaying words. Basically I had 3 words( some times more ) in the variable. I want to get one at a time to process. I am new to unix scripting so please bear with my question and appreciate your reply. I think this can also me done my awk... (1 Reply)
Discussion started by: Vaddadi
1 Replies
Login or Register to Ask a Question