sam2929
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_d ev/test.lo g"
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)
i am running below query and getting error:
EXPORT TO
"/userin/dev/test.ixf" OF IXF
MESSAGES
"/userin/MAT_Integration_d
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)
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
SQL0104N An unexpected token "TO "C:\Datastageexports\emea\
found following "LL ADMIN_CMD (EXPORT". Expected tokens may include:
"<space>". SQLSTATE=42601
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.
Bottom line, you need to get your command string formatted right.
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\t est.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_MS GS('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.
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
Result set 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------------- --------------------------
- SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MS
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.
... but i have to so MESSAGES ON SERVER and i don't want that ...
Don't know what you mean.
Don't know what you mean.
ASKER
I need to give path name for log as i said in above post and it don't like that
Hi Sam,
Just change MESSAGES ON SERVER to:
MESSAGES c:\temp\export_messages.tx t
Or even leave off the MESSAGES clause. Either should work just fine.
Kent
Just change MESSAGES ON SERVER to:
MESSAGES c:\temp\export_messages.tx
Or even leave off the MESSAGES clause. Either should work just fine.
Kent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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\
SQL0104N An unexpected token "TO "C:\Datastageexports\emea\
found following "LL ADMIN_CMD (EXPORT". Expected tokens may include:
"<space>". SQLSTATE=42601
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\t est.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\ test.log select * from SYSIBM.SYSDUMMY1')
You still need to quote the entire string that you're passing to ADMIN_CMD
CALL ADMIN_CMD ('EXPORT TO "C:\Datastageexports\emea\
or
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
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\t est.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
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
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
ASKER
Hi Kent,
i get below error
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t est.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\ test.log select * from SYSIBM.SYSDUMMY1');
-------------------------- ---------- ---------- ---------- ---------- ---------- --
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t est.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\t est.ixf OF IXF MESSAGES ON SERVER select * from SYSIBM.SYSDUMMY1');
-------------------------- ---------- ---------- ---------- ---------- ---------- --
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t est.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_MS GS('31258_ 19003_MKT_ DBO ')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS( '31258_190 03_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.
i get below error
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
--------------------------
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
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
--------------------------
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
--------------------------
CALL SYSPROC.ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
Result set 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------------- --------------------------
- SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MS
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.
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\t est.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\ test.log select * from SYSIBM.SYSDUMMY1');
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
ASKER
-------------------------- ---------- ---------- ---------- ---------- ---------- --
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t est.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:
CALL ADMIN_CMD ('EXPORT TO C:\Datastageexports\emea\t
SQL0007N The character "\" following "OF IXF MESSAGES 'C:" is not valid.
SQLSTATE=42601
SQL0007N The character "1" following "1208" is not valid.
Explanation:
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')
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\
ASKER
I am on Database server = DB2/AIX64 9.5.9.
ASKER
step back Kent
if i just run export runs with no issues.
EXPORT TO C:\Datastageexports\emea\t est.ixf OF IXF MESSAGES 'C:\Datastageexports\emea\ test.log select * from SYSIBM.SYSDUMMY1
But if i run via call function i got issues
if i just run export runs with no issues.
EXPORT TO C:\Datastageexports\emea\t
But if i run via call function i got issues
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\\eme a\\test.lo g" was found
following "MESSAGES". Expected tokens may include: "ON". SQLSTATE=42601
SQL0104N An unexpected token "C:\\Datastageexports\\eme
following "MESSAGES". Expected tokens may include: "ON". SQLSTATE=42601
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
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
ASKER
Any other suggestion
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.
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.
Hi!
If you are running the export command in an AIX shell then the command is like this
Make sure that both paths have rw access for the user executing the cmd.
Regards,
Tomas Helgi
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
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
ASKER
An unexpected token "db2 EXPORT TO /" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601
Hi Sam,
You probably need to quote the command parameter.
Kent
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"
Kent
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