SQL0104N on export

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)
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
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
sam2929Author Commented:
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
Gary PattersonVP Technology / Senior Consultant Commented:
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.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

sam2929Author Commented:
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.
Gary PattersonVP Technology / Senior Consultant Commented:
... but i have to so MESSAGES ON SERVER and i don't want that ...

Don't know what you mean.
sam2929Author Commented:
I need to give path name for log as i said in above post and it don't like that
Kent OlsenDBACommented:
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
Gary PattersonVP Technology / Senior Consultant Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam2929Author Commented:
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
Kent OlsenDBACommented:
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')
Gary PattersonVP Technology / Senior Consultant Commented:
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
sam2929Author Commented:
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.
Gary PattersonVP Technology / Senior Consultant Commented:
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');
sam2929Author Commented:
------------------------------------------------------------------------------
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:
Kent OlsenDBACommented:
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')
sam2929Author Commented:
I am on  Database server        = DB2/AIX64 9.5.9.
sam2929Author Commented:
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
sam2929Author Commented:
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
Kent OlsenDBACommented:
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
sam2929Author Commented:
Any other suggestion
Gary PattersonVP Technology / Senior Consultant Commented:
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.
Tomas Helgi JohannssonCommented:
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
sam2929Author Commented:
An unexpected token "db2 EXPORT TO /" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  SQLSTATE=42601
Kent OlsenDBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.