DB2 mysql export as output

21 January,2011 by Jack Vamvas

I had to export some data from a Mysql database , for the purpose of creating a spreadsheet , which would be used for presentation purposes and also to import into a DB2 database.

Initially I considered using an ODBC connection from my laptop  , I do not have ODBC drivers for mysql on my laptop. The policy of the company is to not allow new drivers installed with centralised authority .

Even without the drivers  it was straightforward.

Logged into mysql  and created a resultset via the following command :

Mysql>select p.page_title from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%';

How can I get that resultset into an output file , which will be useful ?

 

mysql> select p.page_title,t.old_text from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%' INTO OUTFILE '/tmp/jvtest.csv';

ERROR 1045 (28000): Access denied for user 'usertemp'"'localhost' (using password: YES)

I'd seen this error before , and related to the actual Mysql user  not having access to the underlying file system.  Although the Linux user account Id used to log onto the Linux server , had sufficient permissions.

Under the circumstances two methods were available:

Method 1

echo @ select p.page_title,t.old_text from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%'@ > /tmp/test

mysql> -pmypassword -umyuser wikidb < /tmp/test > /tmp/jvtest.csv

 

Method 2

Use tee  - this will save all output from screen

Mysql > tee /tmp/output.txt

Mysql > no teee

I used method 1  as I had more scope to format .

 Ref:Jack Vamvas(http://www.dba-db2.com)

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 mysql export as output

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright