IBM Support

Setting up High-Performance Loader (HPL) in the Informix® command line.

Question & Answer


Question

How do I set up the High-Performance Loader (HPL) Informix® utility?

Cause

Informix DBAs and System Administrators usually 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, through 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 will not 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, issue the following command, changing the -fu flag by the -fl flag: 

   $ onpload -j j_items -fl -l items.log -i 100000 -R $PWD/a
 
4. 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 to your database name, for
example: 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                                   

Notice: For tables that has BLOB columns only Deluxe Mode can be used.

5. 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 is an example of 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.

6. One important notice that should be taken into account is the following HPL environment variables:
 
  • PLOAD_SHMAT:

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.hpl.doc/hpl011066502.htm
    When you set the PLOAD_SHMAT environment variable, the pload converter
    calculates the address using a global attached segment list that is
    maintained across pload virtual processors. The pload converter attaches
    at the next available address after the highest address on the list,
    ensuring that the converter always attaches to an unused shared memory
    segment.

    Error messages like this can be seen in the online.log file when PLOAD_SHMAT is not set:
    15:29:46  PLOAD aborting: cpu VP 15 could not attach shared memory at the required address. Set the environment variable PLOAD_SHMAT to 1 and try again.
  • IFX_XFER_SHMBASE

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_255.htm
    After the database server allocates shared memory, the database server
    might allocate multiple contiguous OS shared memory segments. The client
    utility that connects to shared memory must attach all those OS segments
    contiguously also. The utility might have some other shared objects (for
    example, the xbsa library in onbar) loaded at the address where the
    server has shared memory segment attached. To work around this situation,
    you can specify a different base address in the environment variable
    IFX_XFER_SHMBASE for the utility to attach the shared memory segments.

    The following error message can be seen in the online.log file:
    15:29:46  shmat: [22]: operating system error
    15:29:46  Client could not attach server shared memory segment, use IFX_XFER_SHMBASE.
  • PLOAD_SHMBASE

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_297.htm
    Lets you specify the shared-memory address at which the High-Performance Loader (HPL) onpload processes will attach.
  • DBONPLOAD

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_220.htm
    Lets you specify the name of the database that the onpload utility of the High-Performance Loader (HPL) will use. If DBONPLOAD is set, onpload uses the specified name as the name of the database; otherwise, the default name of the database is onpload.
  • PLCONFIG

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_295.htm
    Lets you specify the name of the configuration file that the High-Performance Loader (HPL) should use. This file must be located in the $INFORMIXDIR/etc directory. If the PLCONFIG environment variable is not set, then $INFORMIXDIR/etc/plconfig.std is the default configuration file.
  • PLOAD_LO_PATH

    • http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_296.htm
    Lets you specify the pathname for smart-large-object handles (which identify the location of smart large objects such as BLOB and CLOB data types). If not set, the /tmp pathname will be used.

    [{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Informix Internet Foundation","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"10.0;11.1;11.5;11.7;12.1","Edition":"Enterprise;Workgroup;Ultimate;Growth","Line of Business":{"code":"","label":""}}]

    Document Information

    Modified date:
    16 June 2018

    UID

    swg21587169

    Page Feedback