[suportebasis]

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

[ceinma]

Describe the steps are you doing today... to do this load

[suportebasis]

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

[ceinma]

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

[ceinma]

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".