Record count from worktable in mload

Tools & Utilities
Enthusiast

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.

Rgds
Anantha
4 REPLIES
Enthusiast

Re: Record count from worktable in mload

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;

:-)
Enthusiast

Re: Record count from worktable in mload

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

Re: Record count from worktable in mload

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

Teradata Employee

Re: Record count from worktable in mload

Hi,

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

    transaction
  • Changes to target table data are not rolled back if transaction

    fails
  • No interaction with Host during this phase and until the end of the phase