Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Reorg. of Tables

    Friends... a help.

    I have a table "BSIS_CALC" has 272.562.889 rows, I need to accomplish a
    reorg. in this table.

    am I taking 32 hours to accomplish a Load,

    question

    how can I reduce this time?

    would anybody have some suggestion?

    this table this fragmented in 3 dbspaces, different

    I am in the version ifx 7.2, with tru64

    Thank you

    Samuel

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Describe the steps are you doing today... to do this load
    ________________________________________
    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
    2
    Ok...

    step 01
    dbschema -d X03 -t bsis_calc -ss bsis_cal.sql

    step 02
    echo "unload to bsis_calc.uld select * from bsis_calc" | dbaccess -e -X X03

    step 03
    select t.tabname from systables t, sysdepend d
    where t.tabid = d.dtabid and d.dtype = 'V'
    and d.btabid = (select tabid from systables
    where tabname = 'bsis_calc')

    step04
    dbschema -d X03 -t $viewname -ss view_$viewname.sql

    step05
    echo 'drop table bsis_calc' | dbaccess -e -X X03

    step 06
    dbaccess -e -X X03 bsis_calc.sql

    step 07
    foreach viewfile ( `ls view_*.sql` )

    step 08
    dbaccess -e -X X03 $viewfile

    step 09
    echo 'load from bsis_cal.uld insert into bsis_calc' | dbaccess -e -X X03

    Tank's

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Ok... you are using SQL unload/load to do this...
    This command is very simple to use, but is not have a good performance..

    You have a lot of options, you can try tune your load/unload SQL, use dbload, use HPL.
    My suggestion is HPL ( High Performance Load).

    I never work with a version before the 7.31. I Don't know how HPL works exactly in version 7.2

    To get a gain in this process if possible use more devices (physical disks).

    The HPL commands to use are;
    - ipload : Here you create a job to load/unload your table. This is a GUI application , you need a XDMCP client (*) or your must have a graphical enviroment on your server.
    - onpload : Execute the job.

    (*) I know 2 ways to use a "XDMCP client" :
    1) download a XDMCP client for windows on the internet, i recommend XDEEP,
    2) Use a linux station (can be a live linux, bootable), start the KDE/GNOME and active connections with "xhosts +"
    Before execute ipload, on the same session you must set the enviroment DISPLAY=<ip>:0 , eg: DISPLAY=172.30.252.145:0
    When you execute the ipload the application will popup in you client XDMCP

    What you need to do:

    1) check how much physical devices you have available to use, if are mounted on you file system.
    2) start your XDMCP client
    3) Open a new session in your server and set DISPLAY variable to IP of XDMCP client
    4) execute the ipload
    5) On the ipload, create the new "device", you will create a file path to each physical disk. When export occur , the HPL split your data into this files with parallel unload.
    6) create the job. I don't know if have the option "no convertion" on the HPL of IDS 7.2 , if have, set, it's faster.

    7) to unload: onpload -j <job_name> -fu
    8) to load: onpload -j <job_name> -fl


    The format exported by HPL (if you _not_ set the option "no conversion" ) is compatible with SQL load/unload.

    To more information, get manuals on IBM site, is for free...
    Guide to the High Performance Loader, Version 7.21
    http://www-306.ibm.com/software/data.../iodsu_72.html
    Last edited by ceinma; 01-15-08 at 07:59.
    ________________________________________
    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
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    I forgot... to load:
    - Create your table without indexes
    - after the load, create the indexes , include the command "set pdqpriority 100;" on the same session and before the "create index".
    ________________________________________
    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
  •