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

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
    ACCEPTED ANSWER

    Re: Use of EXPORT

    ‏2012-11-27T07:03:15Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Use of EXPORT

      ‏2012-11-27T11:27:24Z  in response to mwandishi
      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
        ACCEPTED ANSWER

        Re: Use of EXPORT

        ‏2012-11-27T13:00:09Z  in response to SystemAdmin
        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
      ACCEPTED ANSWER

      Re: Use of EXPORT

      ‏2012-11-27T13:02:15Z  in response to mwandishi
      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
        ACCEPTED ANSWER

        Re: Use of EXPORT

        ‏2012-11-27T15:33:43Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Use of EXPORT

          ‏2012-11-28T10:38:21Z  in response to mwandishi
          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."