All Forums Tools
ananthaisin 5 posts Joined 12/10
11 May 2011
Record count from worktable in mload

Hi All,

I need some clarification on mload worktable. While Mload process is still going on how to check the record count in worktable?

If i put select Count(*) from wt_tabl1;

its not showing any records.


Jimm 298 posts Joined 09/07
13 May 2011

Multiload locks the work table for Write.
If you want to look at it during the load, use an Access Lock.

Locking wt_tabl1 For Access
select Count(*) from wt_tabl1;


abhi_insignia 13 posts Joined 06/11
28 Jun 2011

A doubt regarding the Work tables. we say it to be a table created during the Preliminary Phase used to store intermediate data acquired from the host during an MLOAD task. These data is eventually applied to a target table. Does work table takes data in blocks or at one go from the host? Does work table has an exact replica of data from the host or it does some data conversion too. If yes, what kind of conversion it do? Also, as explained in the post above, we can use an access lock to read the count in work table while Mload is in process, won't it led to a deadlock situation since work table is already in use by Mload task and at the same time we r trying to read from it. or is it like, the access lock will be a Read only and so, we wouldn't come across this problem.

abhishank18 12 posts Joined 03/16
02 Aug 2016

I am follwoing this post, Can anybody pls answer this question ?


Ivyuan 63 posts Joined 01/10
04 Aug 2016

Here is Locking summary during a MultiLoad job:

  •  - Begin MLOAD Step
       . Target table
          - write lock
       . Work table
          - read/exclusive in the restart case (base work table only)
     - MLOAD Step
       . Target table
          - write lock
       . Work table
          - exclusive lock
       . Error tables
          - exclusive lock
     - DML Step
       . Work table
          - write lock on appropriate work table
  •  - Data Sort Step
       . Target table
          - access lock
       . Work table
          - exclusive lock
     - Execute MLOAD Step
       . Target table
          - write lock
       . Work table
          - exclusive lock
     - End MLOAD Step
       . Target table
          - write lock
       . Work table
          - exclusive lock
  •    . Error table
          - exclusive lock
  • Row Hash Level Write Locks
     - Used while inserting errors into error tables (primary/fallback)
     - An error insert runs as a separate transaction and has a short life
     - Write locks prevent inserts from different AMP worker tasks for same target
       table or by the user accessing the table to run into concurrency problems

During MultiLoad Acquisition Phase:

  • Single explicit transaction with multiple requests
  • Data sent to MLOAD sessions and not rolled back if the
    transaction fails
  • Responsible for populating the primary data subtables of the
    work tables
  • There are two steps:
     - Data Transport Step (stpesd)
     - Data Sort Step (stpesr)

In Data Transport Step,

  • Load Control Task forwards data to local deblocker task
    which then converts the data into internal row format for each
    DML of the associated APPLY clause and redistributes the
    rows to their primary and backup hash owners
  • Receiver tasks collect the rows and store them in the appropriate work table

During Application phase:

  • Turn rows from a work table into updates, inserts, and
    deletes modifying a single target table
  • Up to five stpexe’s may operate in parallel under the same
  • Changes to target table data are not rolled back if transaction
  • No interaction with Host during this phase and until the end of the phase
You must sign in to leave a comment.