Solved

SQL0104N on export

Posted on 2014-03-23
24
1,710 Views
Last Modified: 2014-04-21
Hi,
i am running below query and getting error:

EXPORT TO
"/userin/dev/test.ixf" OF IXF
MESSAGES
"/userin/MAT_Integration_dev/test.log"
select 'x' as id from SYSIBM.SYSDUMMY1;

i am getting below error any ideas.

Msg = [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token "EXPORT TO" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_view>".  SQLSTATE=42601 (CC_DB2Adapter::postRun, file CC_DB2Adapter.cpp, line 726)
0
Comment
Question by:sam2929
  • 11
  • 7
  • 5
  • +1
24 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39950769
EXPORT is an administrative command - not an SQL command.  You'll need to use the SQL command CALL ADMIN_CMD stored procedure to run EXPORT through an ODBC interface.

CALL ADMIN_CMD (cmd)

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0012547.html
0
 

Author Comment

by:sam2929
ID: 39952884
CALL ADMIN_CMD (EXPORT TO "C:\Datastageexports\emea\test.ixf" OF IXF MESSAGES "C:\Datastageexports\emea\test.log" select 'x' as id from SYSIBM.SYSDUMMY1)
SQL0104N  An unexpected token "TO "C:\Datastageexports\emea\test.ixf" OF" was
found following "LL ADMIN_CMD (EXPORT".  Expected tokens may include:  
"<space>".  SQLSTATE=42601
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39954277
You'll need to get that command string (('EXPORT .... ') properly quoted.  Try putting single quotes around the command - you'll need to probably double up any single quites insude or escape them, or substring chr() in or something.

Bottom line, you need to get your command string formatted right.
0
 

Author Comment

by:sam2929
ID: 39954773
Below worked with warning but i have to so MESSAGES ON SERVER and i don't want that


CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES ON SERVER  select * from SYSIBM.SYSDUMMY1')


  Result set 1
  --------------

  ROWS_EXPORTED        MSG_RETRIEVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    MSG_REMOVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('32239_')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                                          CALL SYSPROC.ADMIN_REMOVE_MSGS('32239_')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

  1 record(s) selected.

  Return Status = 0

SQL20397W  Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL3001", was encountered during the execution. More information
is available.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39956163
... but i have to so MESSAGES ON SERVER and i don't want that ...

Don't know what you mean.
0
 

Author Comment

by:sam2929
ID: 39957190
I need to give path name for log as i said in above post and it don't like that
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39958900
Hi Sam,

Just change MESSAGES ON SERVER to:

  MESSAGES c:\temp\export_messages.txt

Or even leave off the MESSAGES clause.  Either should work just fine.


Kent
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 39959073
According to the documentation, it doesn't look like you can specify a path for messages when you run EXPORT TO from ADMIN_CMD:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023573.htm

Only option is shows is MESSAGES TO SERVER.
0
 

Author Comment

by:sam2929
ID: 39960429
Hi Kent,
Thats what i tried earlier but no luck, I am on  Database server        = DB2/AIX64 9.5.9.

Can you please try on your side is possible.

Thanks a lot

CALL ADMIN_CMD (EXPORT TO "C:\Datastageexports\emea\test.ixf" OF IXF MESSAGES "C:\Datastageexports\emea\test.log" select * from SYSIBM.SYSDUMMY1)

SQL0104N  An unexpected token "TO "C:\Datastageexports\emea\test.ixf" OF" was
found following "LL ADMIN_CMD (EXPORT".  Expected tokens may include:  
"<space>".  SQLSTATE=42601
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39960433
Hi Sam,

You still need to quote the entire string that you're passing to ADMIN_CMD

CALL ADMIN_CMD ('EXPORT TO "C:\Datastageexports\emea\test.ixf" OF IXF MESSAGES "C:\Datastageexports\emea\test.log" select * from SYSIBM.SYSDUMMY1')

or

CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1')
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39960440
I don't think it likes that quoted pathname.  Didn't you say this worked for you?

CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES ON SERVER  select * from SYSIBM.SYSDUMMY1')


Based on the documentation, that looks like the right format.  I don't think you can specify a file for the MESSAGES when you use ADMIN_CMD.

Using this interface, it looks like you are supposed to retrieve and clean up messages using the calls to  ADMIN_GET_MSGS and ADMIN_REMOVE_MSGS.

Personally, I'd just follow the documentation and do what it tells me to do - saves a lot of headaches trying to make something work differently than how the designer meant for it to work.  

Who knows, though - maybe the documentation is wrong or incomplete.

- Gary Patterson
0
 

Author Comment

by:sam2929
ID: 39961182
Hi Kent,

i get below error

CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1');
------------------------------------------------------------------------------
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1')
SQL0007N  The character "\" following "OF IXF MESSAGES 'C:" is not valid.  
SQLSTATE=42601


Garry i do getting warning using below export also as i mentioned i do need logs too

------------------------------ Commands Entered ------------------------------
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES ON SERVER  select * from SYSIBM.SYSDUMMY1');
------------------------------------------------------------------------------
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES ON SERVER  select * from SYSIBM.SYSDUMMY1')


  Result set 1
  --------------

  ROWS_EXPORTED        MSG_RETRIEVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    MSG_REMOVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('31258_19003_MKT_DBO ')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                            CALL SYSPROC.ADMIN_REMOVE_MSGS('31258_19003_MKT_DBO ')                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

  1 record(s) selected.

  Return Status = 0

SQL20397W  Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL3001", was encountered during the execution. More information
is available.

Can you please try on your side if possible.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39961376
Count your single quotes.  Three is never a good number.  Suggest you get rid of the middle one.

CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1');
0
 

Author Comment

by:sam2929
ID: 39961813
------------------------------------------------------------------------------
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1')


SQL0007N  The character "\" following "OF IXF MESSAGES 'C:" is not valid.  
SQLSTATE=42601

SQL0007N  The character "1" following "1208" is not valid.

Explanation:
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39961836
What client are you running?

You may need to escape the backslash.  The client may be interpreting the backslash as an escape of the NEXT character.


CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\\emea\\test.ixf OF IXF MESSAGES C:\\Datastageexports\\emea\\test.log select * from SYSIBM.SYSDUMMY1')
0
 

Author Comment

by:sam2929
ID: 39962012
I am on  Database server        = DB2/AIX64 9.5.9.
0
 

Author Comment

by:sam2929
ID: 39962021
step back Kent

if i just run export runs with no issues.

EXPORT TO C:\Datastageexports\emea\test.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\test.log select * from SYSIBM.SYSDUMMY1

But if i run via call function i got issues
0
 

Author Comment

by:sam2929
ID: 39962022
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\\emea\\test.ixf OF IXF MESSAGES C:\\Datastageexports\\emea\\test.log select * from SYSIBM.SYSDUMMY1')
SQL0104N  An unexpected token "C:\\Datastageexports\\emea\\test.log" was found
following "MESSAGES".  Expected tokens may include:  "ON".  SQLSTATE=42601
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39962040
Hi Sam,

I understand that.  :)

My confusion (if that's the right adjective) is twofold.  Most of your sample statements have an odd number of single quotes.  I can't picture anywhere in DB2 that that's accepted.  And the error message about the backslash suggests that the processor is treating the string much like the C compiler would and using the backslash-character combination as an escaped character. (\t = tab; \n = newline, etc.)

It would help to know what the client program is that you need to call the ADMIN_CMD function instead of just executing the EXPORT from the CLP.


Kent
0
 

Author Comment

by:sam2929
ID: 39962781
Any other suggestion
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39971149
Yes - I suggest that you answer Kent's questions so he can help you.  

I've already explained that I don't think that you can specify an output file for the messages  when calling EXPORT from ADMIN_CMD, but you seem to be ignoring that input, so I'm just going to drop out of the conversation.

Best of luck.
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 39975026
Hi!

If you are running the export command in an AIX shell then the command is like this
db2 EXPORT TO /userin/dev/test.ixf OF IXF MESSAGES /userin/MAT_Integration_dev/test.log select 'x' as id from SYSIBM.SYSDUMMY1

Open in new window

Notice that the "" is not surronding the paths.
Make sure that both paths have rw access for the user executing the cmd.

Regards,
    Tomas Helgi
0
 

Author Comment

by:sam2929
ID: 40000407
An unexpected token "db2 EXPORT TO /" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  SQLSTATE=42601
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40000570
Hi Sam,

You probably need to quote the command parameter.

db2 "EXPORT TO /userin/dev/test.ixf OF IXF MESSAGES /userin/MAT_Integration_dev/test.log select 'x' as id from SYSIBM.SYSDUMMY1"

Open in new window


Kent
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now