Run script to export the data to ixf file in loop

Shell Programming and Scripting
1

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 messages.txt SELECT * FROM schema.$TABLE 
done <file_which _has_tableNAmes

I am getting the following error..

SQL0104N  An unexpected token ""TABLENAME"" was found following 
"<identifier>".  Expected tokens may include:  "OF".  SQLSTATE=42601

Please help

2

Though I'm not familiar with this db2 command it looks unlikely.
Does the db2 command work when typed at the command line?
I have no idea where "TABLENAME" came from unless it is in your input file. I do hope that your input file has not got headings!

From a shell scripting point of view, the asterisk needs protecting from Shell filename globbing.

while read TABLE; do

  db2 EXPORT TO ~/data_export/$TABLE.ixf OF IXF MESSAGES messages.txt SELECT \* FROM schema.$TABLE 
done <file_which _has_tablenames
3

Hi Methyl,

The TABLENAMES are in the input file ..
the input file looks like this

"tablename1"
"tablename2"
"tablename3"

etc...
4

It think one of the problems the problem is the quotes round the table names in you input file, but still have no idea where the string "TABLENAME" came from in the error message you posted.

Ignoring Shell script, do you have a sample command which works from the unix command line?

5

Hi Methyl,

I am using the below script to load the data into tables and it is working fine...but its creating db connection eveytime because its in the while loop..
and idea how can i create the connection globally and read the sql in the while loop..

while read TABLE; do
  echo "$TABLE"
cat> $loadsql << EOF

connect to $DB_CAT_PPSTAGE user $PSTGUSERID using $PSTGPASSWORD 
@
 LOAD CLIENT FROM $data_dir/$TABLE.ixf OF ixf REPLACE INTO $TABLE NONRECOVERABLE INDEXING MODE REBUILD ALLOW NO ACCESS
@
EOF
sh " db2 -td@ -f $loadsql"

 done < $input_file
 exit
6

Untested (and I don't know the rules for db2 command sequences), but my first idea is to try generating the complete load script concatonated before executing the database load. For example:

> ${loadsql}   # Create empty db2 script file

# Append: Connect to database
cat >>${loadsql} << EOF
connect to $DB_CAT_PPSTAGE user $PSTGUSERID using $PSTGPASSWORD 
@
EOF

# Append: Create as many database load lines as needed
while read TABLE; do
echo "$TABLE"
cat >>${loadsql} << EOF
LOAD CLIENT FROM $data_dir/$TABLE.ixf OF ixf REPLACE INTO $TABLE NONRECOVERABLE INDEXING MODE REBUILD ALLOW NO ACCESS
@
EOF
done < $input_file

# Execute the connect and all the load lines
sh " db2 -td@ -f ${loadsql}"

exit

Footnotes:
1) None of your posted samples issue a command to disconnect from the database conection. Is this a feature of db2?
2) The example does not set a value for $data_dir or $input_dir and a lot more variables.