Topic
  • 6 replies
  • Latest Post - ‏2012-11-28T10:38:21Z by mwandishi
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic Use of EXPORT

‏2012-11-27T06:21:03Z |
Hi,

I am Shubha from Bangalore, India.

I am using Export utility to unload. The query given down is the one am trying..
EXPORT T0 DATA_FILE OF DEL MODIFIED BY COLDEL ${delimiter} STRIPLZEROS DECPT.SELECT * FROM <TABLE_NAME>
EXPORT T0 DATA_FILE OF DEL MODIFIED BY COLDEL ${delimiter} DECPT STRIPLZEROS.SELECT * FROM <TABLE_NAME>
Both the above queries failed for some kind of syntax errors. I am working on AIX OS.We also dont know how to get the version of DB2 used.

Please check if the above 2 queries are wrong in using and send us the correct query.
And also we wanted to know how to get the version of the DB2 used in the project.
Please respond ASAP.

Thanks in advance,
Shubha Shivanna
Updated on 2012-11-28T10:38:21Z at 2012-11-28T10:38:21Z by mwandishi
  • mwandishi
    mwandishi
    47 Posts

    Re: Use of EXPORT

    ‏2012-11-27T07:03:15Z  
    Hi Shubha,

    Firstly, there is no space after the: 'decpt.select...' clause, it should look like this: '...modified by coldel; striplzeros decpt. select * from xxxx'. I note too you are using a . ( period ) for the decpt which is the default decpt char so specifying it is unnecessary but that is not the issue.

    I assume this is being run from a script, which means I can't determine precisely what is being passed to: ${delimiter} but again something like: '...modified by coldel; striplzeros decpt. select ' should work.

    Is DATA_FILE supposed to be parameterised too or is that the real output file name?

    Perhaps you could share the error?

    If you issue: db2level
    It will report the code level of db2.

    Lastly, please refer to the db2 info centre for help with export, this is the v9.7 URL:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Use of EXPORT

    ‏2012-11-27T11:27:24Z  
    • mwandishi
    • ‏2012-11-27T07:03:15Z
    Hi Shubha,

    Firstly, there is no space after the: 'decpt.select...' clause, it should look like this: '...modified by coldel; striplzeros decpt. select * from xxxx'. I note too you are using a . ( period ) for the decpt which is the default decpt char so specifying it is unnecessary but that is not the issue.

    I assume this is being run from a script, which means I can't determine precisely what is being passed to: ${delimiter} but again something like: '...modified by coldel; striplzeros decpt. select ' should work.

    Is DATA_FILE supposed to be parameterised too or is that the real output file name?

    Perhaps you could share the error?

    If you issue: db2level
    It will report the code level of db2.

    Lastly, please refer to the db2 info centre for help with export, this is the v9.7 URL:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html
    Hi,

    This is Shubha again here

    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL${DELIMITER}; STRIPLZEROS DECPT.SELECT * FROM TABLE_NAME
    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL${DELIMITER}; STRIPLZEROS DECPT. SELECT * FROM TABLE_NAME
    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL'${DELIMITER}'; STRIPLZEROS DECPT.SELECT * FROM TABLE_NAME

    THE ABOVE QUERIES AGAIN DID NOT WORK.

    THE ERROR MSG IS : FAILED TO EXPORT DATA. THERE IS NO ANY SPECIFIC ERROR MSG THROW OUT
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Use of EXPORT

    ‏2012-11-27T13:00:09Z  
    Hi,

    This is Shubha again here

    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL${DELIMITER}; STRIPLZEROS DECPT.SELECT * FROM TABLE_NAME
    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL${DELIMITER}; STRIPLZEROS DECPT. SELECT * FROM TABLE_NAME
    EXPORT TO $DATAFILE OF DEL MODIFIED BY COLDEL'${DELIMITER}'; STRIPLZEROS DECPT.SELECT * FROM TABLE_NAME

    THE ABOVE QUERIES AGAIN DID NOT WORK.

    THE ERROR MSG IS : FAILED TO EXPORT DATA. THERE IS NO ANY SPECIFIC ERROR MSG THROW OUT
    Hi,,

    The delimiter used is '^A'.

    EXPORT TO $data_file OF DEL MODIFIED BY COLDEL ${delimiter} STRIPLZEROS DECPT. SELECT * FROM TABLE_NAME
    EXPORT TO $data_file OF DEL MODIFIED BY COLDEL${delimiter} STRIPLZEROS DECPT. SELECT * FROM TABLE_NAME
    IF THE DELIMITER IS NOT '^A' (CTRL-V AND CTRL-A), SOMETHING LIKE * , IT WORKS FINE BUT WITH THE DELIMITER WHERE OUR PROCESS WANTS..

    PLEASE RESPOND ASAP
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Use of EXPORT

    ‏2012-11-27T13:02:15Z  
    • mwandishi
    • ‏2012-11-27T07:03:15Z
    Hi Shubha,

    Firstly, there is no space after the: 'decpt.select...' clause, it should look like this: '...modified by coldel; striplzeros decpt. select * from xxxx'. I note too you are using a . ( period ) for the decpt which is the default decpt char so specifying it is unnecessary but that is not the issue.

    I assume this is being run from a script, which means I can't determine precisely what is being passed to: ${delimiter} but again something like: '...modified by coldel; striplzeros decpt. select ' should work.

    Is DATA_FILE supposed to be parameterised too or is that the real output file name?

    Perhaps you could share the error?

    If you issue: db2level
    It will report the code level of db2.

    Lastly, please refer to the db2 info centre for help with export, this is the v9.7 URL:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html
    HI,

    CAN I GIVE YOU A CALL IF YOU CAN SHARE YOUR CONTACT DETAILS
    OR YOU CAN REACH OUT TO ME ON 9886624522 . I LEAVE IN BANGALORE INDIA
  • mwandishi
    mwandishi
    47 Posts

    Re: Use of EXPORT

    ‏2012-11-27T15:33:43Z  
    HI,

    CAN I GIVE YOU A CALL IF YOU CAN SHARE YOUR CONTACT DETAILS
    OR YOU CAN REACH OUT TO ME ON 9886624522 . I LEAVE IN BANGALORE INDIA
    Firstly, with no coldel modifier data looks like this:

    cat test.del| head -1
    10,"Head Office",160,"Corporate","New York"
    coldel; test:
    export data_file=/home/db2970/test/test.del

    db2 "export to $data_file of del modified by coldel; STRIPLZEROS DECPT. select * from org"
    SQL3104N The Export utility is beginning to export data to file "/home/db2970/test/test.del".

    SQL3105N The Export utility has finished exporting "8" rows.

    cat test.del| head -1
    10;"Head Office";160;"Corporate";"New York"

    coldel caret (^):
    db2 "export to $data_file of del modified by coldel0x5E STRIPLZEROS DECPT. select * from org"
    SQL3104N The Export utility is beginning to export data to file "/home/db2970/test/test.del".

    SQL3105N The Export utility has finished exporting "8" rows.

    Number of rows exported: 8

    cat test.del| head -1
    10^"Head Office"160"Corporate"^"New York"

    I can't find an appropriate hex coldel string to delimit with ^A (0x5E caret + 0x41 for A ), perhaps this isn't a supported delimiter:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0011047.html

    However, I will continue to experiment and update the thread ASAP.

    Does this help?

    Stephen.
  • mwandishi
    mwandishi
    47 Posts

    Re: Use of EXPORT

    ‏2012-11-28T10:38:21Z  
    • mwandishi
    • ‏2012-11-27T15:33:43Z
    Firstly, with no coldel modifier data looks like this:

    cat test.del| head -1
    10,"Head Office",160,"Corporate","New York"
    coldel; test:
    export data_file=/home/db2970/test/test.del

    db2 "export to $data_file of del modified by coldel; STRIPLZEROS DECPT. select * from org"
    SQL3104N The Export utility is beginning to export data to file "/home/db2970/test/test.del".

    SQL3105N The Export utility has finished exporting "8" rows.

    cat test.del| head -1
    10;"Head Office";160;"Corporate";"New York"

    coldel caret (^):
    db2 "export to $data_file of del modified by coldel0x5E STRIPLZEROS DECPT. select * from org"
    SQL3104N The Export utility is beginning to export data to file "/home/db2970/test/test.del".

    SQL3105N The Export utility has finished exporting "8" rows.

    Number of rows exported: 8

    cat test.del| head -1
    10^"Head Office"160"Corporate"^"New York"

    I can't find an appropriate hex coldel string to delimit with ^A (0x5E caret + 0x41 for A ), perhaps this isn't a supported delimiter:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0011047.html

    However, I will continue to experiment and update the thread ASAP.

    Does this help?

    Stephen.
    Hi,

    Of course A is an LF in ascii:

    $ od -a od.in
    0000000 ^ A lf lf
    0000004

    $ cat od.in
    ^A

    From: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dm.doc%2Fdoc%2Fr0011047.html

    "Delimiter restrictions

    There are a number of restrictions in place that help prevent the chosen delimiter character from being treated as a part of the data being moved. First, delimiters are mutually exclusive. Second, a delimiter cannot be binary zero, a line-feed character, a carriage-return, or a blank space. As well, the default decimal point (.) cannot be a string delimiter. Finally, in a DBCS environment, the pipe (|) character delimiter is not supported."