Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!

Nilesh Wattamwar

+ Follow
since Dec 06, 2007
Nilesh likes ...
IBM DB2 Eclipse IDE Spring
Cows and Likes
Total received
In last 30 days
Total given
Total received
Received in last 30 days
Total given
Given in last 30 days
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Nilesh Wattamwar


I want to understand about how first level cache and second level cache and what is difference between these two.
Also, when it comes to distributed application(i.e. multiple application servers and single database servers) how does second level cache works.
4 years ago
Hii Dave,Winston,

Finally my issue got solved

Actual culprit was OpenCSV ..
when i do select * from table_name format coming as YYYY-MM-DD . I was sending that resultset directly to CSVWriter.writeAll() method.When it writing to csv it was changing the date format to DD-MMM-YYYY which was incompatible with DB2.
So I changed the approach and was directly writing each resultset row to BufferedWriter without using opencsv API.
Once csv got generated used below load command to load the csv to db.

db2 load from v10.vakuutus.csv of del modified by nochardel coldel$ dateformat=\"YYYY-MM-DD\" insert into lis.vakuutus > load_dollar_out.txt

And it worked!!!

many thanks for your timely support..
I have tried imp and load both the options.

db2 import from v10.vakuutus.csv of coldel$ messages vakuttus_load.log insert into lis.vakuutus

db2 load from v10.vakuutus.csv of del modified by nochardel coldel$ dateformat=\"YYYY-MM-DD \" insert into lis.vakuutus > load_dollar_out.txt
As of now , I am fetching the data from universe db and writing it to csv using OpenCSV with delimiter as "$".while writing the data to csv , date is coming in 'DD-MMM-YYYY' format(not sure if it is actual database date or java recognised date format).
once csv is written,I am loading this data into db2 using load\imp utility . Unfortunately, db2 doesnt recognise 'DD-MMM-YYYY' format so that utility also not working . thats why need to convert it to 'DD-MM-YYYY' or 'YYYY-DD-MM' format.
I am totally agree with Dave since the table data volume is high i need to find the most efficient way to extract the same.Slow by slow is definitely a good approach but in this case it is not possible.
However,I still didnt find anything at Universe DB front to change the date format in select query itself like "select to_date(to_char(column_name,'DD-MM-YYYY'),'DD.MM.YYYY')) from table name" or any other option except exporting data into csv( like into txt or xml etc) which will be more efficient to extract large volume data?
Hi Winston ,

TBH, if you're required to use openCSV, I'd do this in four stages:
1. Write out the CSV file as you're doing.
2. Read it back into a List <String[]> using a CSVReader with the same settings as (1). (have a look at the readAll() method)
3. Convert the date column yourself.
4. Write lines back out with CSVWriter.writeAll().

As you mentioned above,

first two steps are fine.for 3rd step how can convert the date on my own? can you please explain,it will be better if provided the code for the same.
but i found similar option when i googled it...but nothing is working more over i have only one option to access the universe db i.e. from java ..
it will be very helpful i get some functions to convert the date format in universe database.

i tried the same option SELECT to_char(column_name, 'MM/DD/YYYY') FROM table_name;

I am getting below error msg.
java.sql.SQLException: UniVerse/SQL: syntax error. Unexpected symbol. Token was "to_char". Scanned command was FROM v10.vakuutus SELECT to_char

It seems Universe Database doesnt support to_char function.I could not find any function in Universe DB which can help in solving my problem.
can you please suggest something on this front.
My sql query is ,
rs = select * from table_name;
here i am directly sending the rs to writeAll method of opencsv

I am trying to export the data into CSV using openCSv framework.Database is Universe Db.CSV generates fine with "$" as separator.But when we tried to load the same csv data into IBM DB2 database it is failing because of incompatible date format(i.e DD-MMM-YYYY).While IBM DB2 doesnt support the mentioned date format.

Code to generate the CSV:
private CSVWriter writer;
writer = new CSVWriter(new OutputStreamWriter(new FileOutputStream(savePath + "\\" + tableName+ ".csv"),"UTF-8"), '$',CSVWriter.NO_QUOTE_CHARACTER);
writer.writeAll(rs, false); //rs is the resultset returned for the select query

We are loading the csv using DB2 load & imp command.

Kindly help me in changing the default date format while generating the csv or any other option to do the same .
Also,let me know if there is any other framework that can be used to load the csv data.

Puprose of the activity :
To load the data from Universe db to IBM DB2 database.

Note : Universe DB doesnt support updating the resultset hence that option was not taken into consideration