Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: unload data for 3 fields from large informix table

    Hello All Informix Guru,

    I am using informix DB in my DataStage project. I want to unload the three columns from a very big table which having more than 100000000 records.

    Please sugest me some efficient way to unload the data from table.

    I have tried Unload command but it is not working because of large volume of data.

    First column out of those 3 is primary key .

    Thanks

    Singhal

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    Don't know exactly why it can't be done with one statement but I guess the number of logical logs is to small to fit in the complete transaction.
    If that's the case then probably the easiest way, without writing a small application program, is to split the number of rows in the table into an even amount of parts with acceptable results. E.g. if the primary key values range from 1 to 100,000,000 execute 10 unload statements from 1-10,000,000, 10,000,001-20,000,000, etc.
    Another way maybe is to create a temporary table without logging, fill it with a select statement and unload this table within one session, like:
    Code:
    CREATE TEMP TABLE example (                                
        key     INTEGER,                                       
        name    CHAR(10),                                      
        ref     INTEGER) WITH NO LOG;                          
                                                               
    INSERT INTO example SELECT col1, col2, col3 FROM basetable;
                                                               
    UNLOAD TO example.unl SELECT * FROM example;
    Don't know if this works, but this way 'stuffing' and 'stripping' of the temp table will not be logged and therefore could be done with one unload action.

    Regards

  3. #3
    Join Date
    Sep 2005
    Posts
    9
    Thank you veryu much , I resolved the issue ,

    Singhal

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    OR ... use HPL...
    OR... if your IDS is version 10 you can extract from a backup with archecker....
    , without impact your system
    ________________________________________
    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
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi singhald, I'm curious how you solved the problem, can you tell me how? The largest table I have to deal with has about half a million rows, completely different from your stuff...

    Greets

  6. #6
    Join Date
    Sep 2005
    Posts
    9
    Hello

    I am sorry , Now i feel that problem is still there. I am able to unload max 2 GB data from the table . I am using the below command from unix

    Deepak
    Last edited by singhald; 06-27-07 at 06:28.

  7. #7
    Join Date
    Sep 2005
    Posts
    9
    Hello

    Iused the following command from unix prompt

    unload to File
    select coloumn1 , Max(coloumn2) form tablename group by column1 having column1>0;

    But it is unloading only 2 GB data,

    could you please tell me how can i use HPL to unload more then 2 GB data from a table using this query.

    Deepak

  8. #8
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    if you had said before this is the problem...that is easy...

    What is your system? Unix? Windows?

    The limitation of 2GB probably is limitation of your system.
    If is unix, check with comand "ulimit"

    Or if your IDS is version 7.31 and 32bits...

    To workaround in unix, you can use piped files to unload all data, but the performance will degrade...

    to more information , search here for "2gb unload":
    http://groups.google.com/group/comp....pics?lnk=gschg


    sorry my bad english...
    Last edited by ceinma; 07-03-07 at 16:47.
    ________________________________________
    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
    Sep 2005
    Posts
    9
    Yes Cienma,

    I am using Unix HP11,
    I executed 'ulimit' command
    it showing unlimited in the output.

    Deepak

  10. #10
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Lightbulb

    What version is your IDS??


    to more information , search here for "2gb unload":
    http://groups.google.com/group/comp....pics?lnk=gschg
    Last edited by ceinma; 07-03-07 at 16:46.
    ________________________________________
    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).
    ________________________________________

Posting Permissions

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