Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I import NULL columns into DB2 timestamp field?

Posted on 2006-07-14
8
Medium Priority
?
5,334 Views
Last Modified: 2008-01-09
Hi All - I am trying to port data from a mysql DB to a DB2 DB(express 8.2). I am very new with DB2 and am having trouble importing rows where one of the timestamp columns is null.  For a simple example, if I use;
 'select * into outfile '/home/jentest.txt' from jentest

on mysql I get a file /home/jentest.txt with;

1      /N
2      2006-06-26 09:21:16

Now I try to import the file into DB2, the table definition in DB2 has column2 defined simply as timestamp which should allow nulls as that is the default. The table def is:

create table jentest (id INTERGER NOT NULL DEFAULT 0, time TIMESTAMP);

The import command I have used, both with and without timestampformat:
import from '/home/jentest.txt' of del modified by timestampformat="yyyy-mm-dd hh:mm:ss" COLDEL0x09 INSERT INTO jentest;

I get an error for the first row and the second row works fine, the result being that row 1 is not imported but row 2 is;
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

I have tried changing the /N to NULL. I have removed timestampformat on the import. I am not sure if there are any options, how do I get DB2 to recognize the time column allows NULLs, by definition it should? The nulls work for other field types defined similiarly, such as INTEGER.

Please let me know if you have any ideas, thanks!

Jennifer
0
Comment
Question by:jennfir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Accepted Solution

by:
ocgstyles earned 1600 total points
ID: 17112892
In your case, just leave that field blank.  So it should look like:

1      
2      2006-06-26 09:21:16

If you are importing more fields than you are listing above, make sure you retain the TAB characters.  Changing the delims to a comma may make it easier to eye...

Good luck

- Keith
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17120345
if using CSV

ID, col2, col3
123,,,

0
 

Author Comment

by:jennfir
ID: 17141033
Thanks, I do see that changing the /N to blank works. I guess I have to write something to go thru all my mysql export files and change /N to blank. Does anyone know if there is a method to have mysql put blanks in the output file if the field is null? I see I can have it put 'null' there, but cannot figure out how to make it put in a blank.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17141230
you can put null keyword also (without single quotes)

e.g.

ID, col2, col3
123,null,null,
0
 

Author Comment

by:jennfir
ID: 17142123
If I put a null character in, and try to import that into DB2 using the same manner noted above, I get the same error as when I had /N, e.g.

SQL0180N. The syntax of the string representation of a datetime value is incorrect.

I cannot use the comma as my delimiter, too many of those in my data. I have been using the tab character instead. If there is a better way to get data out of mysql into db2 other then select into(mysql) and then load/import on the DB2 side, please let me know.

Thank You.


0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 17143232
Can you connect to the databsae using a GUI, like phpMyAdmin.  I was able to export the data very easily using that using the CSV format in the Export section with these options:
 
Fields terminated by:     \t
Fields enclosed by:         "
Fields escaped by:          \
Lines terminated by:       \r\n
Replace NULL by:            <blank>

You can also give this a try:

select id, case when timestamp is null then '' else timstamp end into outfile '/home/jentest.txt' from jentest;

The CASE statement should give you the "blank" value you need.

Good Luck,

- Keith
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17144672
if its a one time import, you can use any text editor to find/replace /n to blank.

if its a script, you can use command line batch utility to find/replace /n to blank
e.g

http://fart-it.sourceforge.net/
OR
http://www.abacre.com/afr/index.htm
0
 

Author Comment

by:jennfir
ID: 17170656
Thanks!  I am doing this as a script, as I have several similiar databases I need to move over. The CASE statement from ocgstyles really made it easy to export the required blanks DB2 wanted. This is working perfectly now, Thank you!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question