asked on

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)

Gary Patterson, CISSP

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

sam2929

ASKER

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 Patterson, CISSP

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.

sam2929

ASKER

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 Patterson, CISSP

... but i have to so MESSAGES ON SERVER and i don't want that ...

Don't know what you mean.

sam2929

ASKER

I need to give path name for log as i said in above post and it don't like that

Kent Olsen

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

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sam2929

ASKER

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 Olsen

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 Patterson, CISSP

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

sam2929

ASKER

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 Patterson, CISSP

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');

sam2929

ASKER

------------------------------------------------------------------------------
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 Olsen

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')

sam2929

ASKER

I am on Database server = DB2/AIX64 9.5.9.

sam2929

ASKER

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

sam2929

ASKER

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 Olsen

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

sam2929

ASKER

Any other suggestion

Gary Patterson, CISSP

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 Johannsson

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

sam2929

ASKER

An unexpected token "db2 EXPORT TO /" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601

Kent Olsen

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