Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2008
    Posts
    9

    Unanswered: Unloading Informix Data

    Hi Gurus,

    I have to unload around 1 million data from informix to DAT file and load it back to SQL through SQL Loader.

    As the data contains all the special characters that a keyboard contains i want to load the data to DAT file using Fixed width format.

    Could any one of you guys let me know how to unload informix data using fixed width format??

    It would be a great help to me if you can provide me solution asap..

    Thanks once again

    Suresh

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Use the HPL ( High Performance Load). I don't believe you can found another way to execute a unload (or load) faster on the Informix.

    With this function you can load and unload to a lot types of formats, include fixed width and custom formats.

    study about the commands:
    onpladm ( IDS version >= 9.40)
    onpload (all versions)
    ipload ( the GUI version of onpladm, but i do not recomend, is not a friendly GUI on my opinion)

    Take a look on the HPL manual , is very easily to use...
    with 2 line command you start your unload.
    You maybe must to use more commands if want to custom the output...

    Simple Example:

    Create a job over a table:
    Code:
    onpladm create job job_table_xyz -d "/tmp/table_xyz.unl" -D my_database -t table_xyz -fu -zFA
    Execute the unload
    Code:
    onpload -j job_table_xyz -fu -l /tmp/table.xyz.log
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jan 2008
    Posts
    9
    Thanks for the info. Could any one give me clear example as i am a novice to infomix? I need to load the data into Oracle Sql after unloading such a large file in informix...

    Thankss

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    more clear that the example above?

    Just look the manuals about the syntax...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Jan 2008
    Posts
    9
    Hi,

    I am taking a backup of data from informix DB to oracle DB.

    For getting data from Informix DB..i am using unload to command using dbaccess.

    I am able to get all the 1 million records with a data limiter '|'.

    I am using this Data file as a input to oracle DB using SQL Loader.

    But I am able to get only 600 thousand records as the data itself has '|' character. So its taking that to bad record.

    So can you tell me how to get all records??

    Thanks

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    You can change the delimiter on the unload:

    unload to "/xyz" delimiter ";"
    select * from xyz

    check what delimiter the SQL loader are expected ...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  7. #7
    Join Date
    Jan 2008
    Posts
    9
    Hi,

    I have tried with all the characters in the keyboard, still i am missing the data while loading thru sql loader.. Please help me out !!

    Thanks

  8. #8
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    well...
    For me, the problem is in SQL Loader...

    My suggestion in this case is split your file and load in small pieces.
    To split file i know 2 ways...
    1) use the "split" command.
    2) with HPL, configure more than 1 device (5 devices for example) and when the unload will generate all data over this 5 files...

    Then you load file by file and discover where is the problem... what record... or if in SQL Loader
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  9. #9
    Join Date
    Jan 2008
    Posts
    9
    Hi,

    Thanks you for your suggestion :-).

    I will use split command..Can you give me an example of using that as i am novice to informix?

    I cant use HPL over here..i need to use only through dbaccess.

    Thanks once again

  10. #10
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Split command is a unix/linux command.

    The output of the "man split"
    SPLIT(1) User Commands SPLIT(1)

    NAME
    split - split a file into pieces

    SYNOPSIS
    split [OPTION] [INPUT [PREFIX]]

    DESCRIPTION
    Output fixed-size pieces of INPUT to PREFIXaa, PREFIXab, ...; default size is 1000 lines, and default
    PREFIX is `x'. With no INPUT, or when INPUT is -, read standard input.

    Mandatory arguments to long options are mandatory for short options too.

    -a, --suffix-length=N
    use suffixes of length N (default 2)

    -b, --bytes=SIZE
    put SIZE bytes per output file

    -C, --line-bytes=SIZE
    put at most SIZE bytes of lines per output file

    -d, --numeric-suffixes
    use numeric suffixes instead of alphabetic

    -l, --lines=NUMBER
    put NUMBER lines per output file

    --verbose
    print a diagnostic to standard error just before each output file is opened

    --help display this help and exit

    --version
    output version information and exit

    SIZE may have a multiplier suffix: b for 512, k for 1K, m for 1 Meg.
    l
    Eg: split -l 100000 my_table.unl my_table.part.
    will create files with 100.000 lines each.
    The name of files in this example is some like this:
    my_table.part.aa
    my_table.part.ab
    my_table.part.ac
    my_table.part.ad
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  11. #11
    Join Date
    Jan 2008
    Posts
    9
    Hi,

    Thank you very much for your detailed examples..It solved my problem..kudos to you

  12. #12
    Join Date
    Jan 2009
    Posts
    3

    Help on Data migration from Informix 11 to Oracle 10g`

    Hello Gurus,
    This is my first post on this forum. We need to migrate data from Informix version 11 to Oracle 10g. There are few millions of data. I dont ahve any idea on Informix.
    Can some one suggestion what would be the best way for extracting data from Informix DB.

    Is IBM Data stage a good option?

    Looking forward for your expertise.

    thanks,
    mm

  13. #13
    Join Date
    Jan 2008
    Posts
    9
    Hi,

    Datastage is a good option if you have that software.

    You can also unload the data in informix to a dat file and then load the data to oracle using sql loader or any other loading utility.

    Thanks

  14. #14
    Join Date
    Jan 2009
    Posts
    3
    Hello,
    Thanks for your feedback. When I do unload and load without any tools, how the data reconcilation can be carried out. Do you ahve any suggestion for data conversion?

    Appreciate your help.

    thanks,
    mm

  15. #15
    Join Date
    Jan 2009
    Posts
    3
    Thanks for all the previous suggestions. I'm gonna use onpladm for unloading the Informix data. For performing this what are all the pre-requisite I should look for? Do I need a DBA access? Thanks in advance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •