Pessoal,
conforme havia prometido, material técnico sobre HPL, e que esta em revisão para virar um Technote, ou
se tudo der certo um artigo na DeveloperWorks Area. Esse material é um apanhado de experiências pessoais minhas em migrações de clientes. Agradeço o apoio nas informações enviadas pelo Carlos Eduardo Boccio e Dirceu Tirado, ambos especialistas Informix da Porto Seguro.
O material é rico em detalhes, e focado em ambientes grandes, que se beneficiam do poder de processamento do HPL.
Easely setting up High-Performance Loader (aka HPL) in the command line.
Technote (FAQ)
Question
How to set the High-Performance Loader utility.
Cause
Users (Informix DBAs and System Administrators) usually intend to find HPL a little bit tricky, due to its own shared memory management engine and specific command line instructions to handle the load and unload jobs control.
This document gathers a compilation of useful links and ease-of-use instructions to quickly set HPL ready to run.
Answer
Eventually, some may find the GUI option the best path, but this document intends to prepare the reader to set HPL in just a few steps, thru the command line interface.
1. First of all, you should choose a table to create the HPL job (and the onpload database):
$ onpladm create job j_items -d "items.unl" -D stores -t items
Successfully created Job j_items
where: - j_items -> job name for the table
- items.unl -> flat file to receive the unloaded table rows
- stores -> database name
- items -> table name
As soon as the first job is created, the onpload database is created too, if it doesn't exist already. If it does exist, you should rename it so it won't cause any trouble.
2. To execute the j_items job as an unload job, just issue the command below, using the -fu flag:
$ onpload -j j_items -fu -l items.log -i 100000 -R $PWD/a
where: - $PWD -> environment variable created to store the current directory value
- j_items -> job name for the table
- items.log -> error log file name
3. To use the same job as a load job, just issue the command below, changing the -fu flag by the -fl flag:
$ onpload -j j_items -fl -l items.log -i 100000 -R $PWD/a
Obviously, no one would use HPL facility to unload/load a single table. The sections bellow show some SQL queries to generate the onpload commands for each table. You can use the DBAccess utility to execute these commands.
Don't forget to change the <dbname> variable for your database name, i.e.: stores_demo.
- This query, generates the CREATE JOB command thru the onpladm facility for all tables in the database, in Express Mode:
select tabname from systables where tabid > 99 and tabtype = "T" into temp a; output to hpl_create_job.sh without headings select "onpladm create job job_"||trim(tabname)||" -d '/tmp/"||trim(tabname)||".unl' -D <dbname> -t "||trim(tabname) from a
- This query, generates the CREATE JOB command thru the onpladm facility for all tables in the database, in Deluxe Mode:
select tabname from systables where tabid > 99 and tabtype = "T" into temp a; output to hpl_create_job_delux.sh without headings select "onpladm create job job_"||trim(tabname)||" -d '/tmp/"||trim(tabname)||".unl' -D <dbname> -t "||trim(tabname)||" -fc" from a
- This query, generates the job load command thru the onpload facility for all tables in the database:
select tabname from systables where tabid > 99 and tabtype = "T" into temp a; output to hpl_load_job.sh without headings select "onpload -j job_"||trim(tabname)||" -fl ","-l /tmp/"||trim(tabname)||".log -i 100000 -R /tmp/"||trim(tabname) from a
- This query, generates the job unload command thru the onpload facility for all tables in the database:
select tabname from systables where tabid > 99 and tabtype = "T" into temp a;
output to hpl_unload_job.sh without headings
select "onpload -j job_"||trim(tabname)||" -fu ","-l /tmp/"||trim(tabname)||".log -i 100000 -R /tmp/"||trim(tabname) from a
For all the tables that has BLOB columns only Deluxe Mode can be used.
When all the shell scripts are done, you can clean them up, avoiding line feeds and weird characters which may cause a failure at execution time.
Follows attached, two scripts that might be used as cleaning tools:
a. Use this tool to remove line feeds, name it
join.sh:
:join
/\\$/{N
s/\\\n/ /
b join
}
b. Use this tool to remove blank lines, name it
del_blank.sh:
sed '/^$/d' $1
Here's an example on its use:
cat hpl_load_job.sh | sed -f join.sh > zz
sh del_blank.sh zz > zzz
mv zzz hpl_create_job.sh
And the final hpl_create_job.sh script is clean.
Related information
John Carlson's document on HPL usage
Environment variables needed for HPL proper execution
HPL tuning tips
Appendix B link. High-Performance Loader configuration
Vou corrigindo o conteúdo antes da publicação oficial.
FFO
