Monitor Tablemove?

M

maday15

Member
Good morning, I have a 10.2bSP8 database on AIX and I need to move a table to a new storage area, I have tested it and it works fine but I want to be able to monitor the progress of the move so I can estimate when it will complete when I run this in production. I have seen from the progress knowledge base that I should be able to get this info from VST tables but it doesn't seem to be working. Does anyone know why the VST's aren't showing the tablemove?
 
I have tried doing a simple display of _UserStatus as well as this program I found from the knowledgebase, but neither work:
Code: 
/* display _userstatus VST for tablemove, indexmove, idxfix, idxcompact, idxanalys */

def var vName   as char format "x(12)".
def var vFound  as log.

repeat :

    vFound = NO.

    for each _userstatus where _userstatus-operation <> ? :
        vFound = YES.

        /* FIND INFO ABOUT THE PROCESS */
        find first _connect no-lock where _connect-usr = _userstatus-userid.

        /* FIND INFO ABOUT THE OBJECT */
        case _userstatus-objecttype :
            when 1 then do:
                find _file no-lock where _file-number = _userstatus-objectid.
                vName = _file-name.
            end.
            when 2 then do:
                find _index no-lock where _idx-num = _userstatus-objectid
                    no-error.
                vName = if available _index then _index-name else "No Index".
            end.
            otherwise vName = "".
        end.

        display
            _userstatus-operation   label "Operation"   format "x(13)"
            _userstatus-userid      label "Usr#"        format ">>>9"
            _connect-name                               format "x(10)"
            _userstatus-objectid    label "Obj#"        format "->>>9"
            (if _userstatus-objecttype = 1
             then "T" else "I")     label ""            format "!"
            vName                   label "Object"
            _userstatus-state       label "Phase"       format ">>9"
            _userstatus-counter     label "Counter"
            _userstatus-target      label "Target"
            with no-box.

    end. /* for each */
 
Last edited by a moderator:
It hurts my eyes to try to read code that has no code tag formatting. And my brain melts when people use hungarian notation so that isn't helping me see why your code isn't working either.

Why not just use ProTop? Much simpler... ProTop Monitoring and Alerting Service

Is this table non-trivial in size? If the production table is more than around 10 to 50MB you probably do NOT want to use tablemove. It will consume enormous amounts of BI space and take *forever*.
 
Thanks Tom, it is about 6gb. I got the code from the progress knowlegbase. I have OE replication, I don't want to have to re-baseline in order to do a dump and load into the new storage area. I tested it once and it took 30 mins, with a database not set up for replication, then I tested it again after setting up replication and it took almost 4 hours. This is why I have to be able to monitor it, I think I can live with 4 hours if it means I can skip re-baselining but I need to know where I am in the process in case it looks like it will go past 4 hours so I can notify the business.
 
Looking at your code, I can't determine offhand why it doesn't show you what you expect to see. It is missing an END statement for the repeat block, but I assume that is present in your version of the code.

I have a similar program that I got from Dan Foreman's VST book and tweaked a bit, but I haven't run it in years. If I get time, I'll try a tablemove on a table of meaningful size and see if I can get it to work.
 
In my experience with OE 10 an online table move required the BI to grow to 10 times the tablesize because the work is done in a single transaction. I didn't try online tablemoves for any table larger than 200mb due to bi growth and the performance impact to other processes while the tablemove was running.

I would keep an eye on the AI space that it required in a test environment and then just monitor the prod environment AI size to track the progress.

I haven't attempted online moves with OE 11.
 
I'm running a test on my test box, CentOS 10.2B08, and my program is showing me the current status of a tablemove. Example:

Code: 
Operation      Usr# Name        Obj#   Object                              Phase Phase description          Counter      Target
────────────── ──── ────────── ───── ─ ─────────────────────────────────── ───── ────────────────────── ─────────── ───────────
Table Move        6 Tablemove    104 T switch-tran                            24 Records_copy                 81139           0

Here is the code:

Code: 
/* userstatus.p
 *
 * Display _Userstatus for tablemove, indexmove, etc.
 *
 * Original source: Dan Foreman's VST Guide
 * Tweaked by     : Rob Fitzpatrick
 *                  11/30/2011
 */
def var vName  as character format "x(35)" no-undo.
def var vFound as logical                  no-undo.
def var vState as character format "x(22)" no-undo.

repeat:
  vFound = no.
 
  for each _userstatus no-lock where _userstatus._userstatus-operation <> ?:
    vFound = yes.
   
    /* get process info */
    find _connect no-lock where _connect._connect-id = _userstatus._userstatus-userid + 1.
   
    /* get object info */
    case _userstatus._userstatus-objecttype:
      when 1 then
      do:
        find _file no-lock where _file._file-number = _userstatus._userstatus-objectid.
        vName = _file._file-name.
      end.
      when 2 then
      do:
         find _index no-lock where _index._idx-num = _userstatus._userstatus-objectid.
         find _file no-lock where recid( _file ) = _index._file-recid.
        vName = _file._file-name + "." + _index._index-name.
      end.
      otherwise vName = "".
    end. /* case _userstatus-objecttype */
   
    case _userstatus._userstatus-state:
       when 11 then vState = "Lock_wait_table".
       when 12 then vState = "Lock_wait_admin".
       when 13 then vState = "Lock_wait_object".
       when 24 then vState = "Records_copy".
       when 25 then vState = "Index_create_secondary".
       when 26 then vState = "Records_delete".
       when 27 then vState = "Index_delete_all_old".
       when 31 then vState = "Index_create_new".
       when 32 then vState = "Index_delete_old".
       when 41 then vState = "Scan_delete_chain".
       when 42 then vState = "Compact_nonleaf".
       when 43 then vState = "Compact_leaf".
       when 51 then vState = "Scan_RM".
       when 52 then vState = "Scan_indexes".
       when 53 then vState = "IDX_dbkey_compare".
       when 54 then vState = "IDX_build".
       when 55 then vState = "BLOB_enablelargefiles".
       when 60 then vState = "BK_init_area_descript".
       when 61 then vState = "BK_backup_BI_clusters".
       when 62 then vState = "BK_estimate_backup_size".
       when 63 then vState = "BK_flush_bigB_and_bibufs".
       when 64 then vState = "BK_backup_area_obj_blks".
       when 65 then vState = "BK_backup_BI_file".
       when 66 then vState = "BK_backup_DB_blks_per_area".
       when 70 then vState = "AN_examine_RM_chain".
       when 71 then vState = "AN_examine_IX_del_chain".
       when 72 then vState = "AN_examine_free_chain".
       when 73 then vState = "AN_examine_DB_blocks".
       when 74 then vState = "AN_gather_frag_stats".
       when 80 then vState = "DMP_validate_user_input".
       when 81 then vState = "DMP_validate_column_index".
       when 82 then vState = "DMP_validate_col_data_type".
       when 83 then vState = "DMP_dump_recs_from_tbl_by_idx".
       when 84 then vState = "DMP_dump_recs_encr_dumpspecified".
       when 85 then vState = "DMP_encr_get_validate_passphrase".
       when 86 then vState = "DMP_prep_dump_encr_file_header".
       when 87 then vState = "DMP_reservedforfutureuse".
       when 88 then vState = "DMP_validating_input".
       when 89 then vState = "DMP_dump_recs_binary_non-encr".       
       when 90 then vState = "IXMV_validate_user_input".
       when 91 then vState = "IXMV_validate_target_area".
       when 92 then vState = "IXMV_lock_wait_table".
       when 93 then vState = "IDX_build".
       when 100 then vState = "IXFX_schema_index_rebuild".
       when 101 then vState = "IXFX_scan_index_blocks".
       when 102 then vState = "IXFX_scan_IX_or_RM_blocks".
       when 103 then vState = "IXFX_reconstruct_indexes".
       when 104 then vState = "IXFX_rebuild_from_sort_files".
       when 105 then vState = "IXFX_activate_all_rebuilt_IX".
       when 106 then vState = "IXFX_del_old_schema_indexes".
       when 110 then vState = "TBMV_validate_user_input".
       when 111 then vState = "TBMV_validate_target_area".
       when 112 then vState = "TBMV_lock_wait_table".
       when 113 then vState = "TBMV_find_inactive_indexes".
       when 114 then vState = "TBMV_del_templ_rec_from_src".
       when 115 then vState = "TBMV_move_tbl_from_src_to_dst".
       when 116 then vState = "TBMV_create_templ_rec_in_dst".
      otherwise.

    end.  /* case _userstatus-state */
   
    display
            _userstatus._userstatus-operation label "Operation" format "x(14)"
            _userstatus._userstatus-userid    label "Usr#"      format ">>>9"
            _connect._connect-name                              format "x(10)"
            _userstatus._userstatus-objectid  label "Obj#"      format "->>>9"
            (if _userstatus._userstatus-objecttype = 1
             then "T" else "I")               label ""          format "!"
            vName                             label "Object"  
            _userstatus._userstatus-state     label "Phase"     format ">>9"
            vState                            label "Phase description"
            _userstatus._userstatus-counter   label "Counter"  
            _userstatus._userstatus-target    label "Target"
            with no-box.
    pause 0.
  end. /* for each */
 
  if not vFound then
    display "No active online processes."
    with frame none center row 6.
   
  pause 1.
 
end. /* repeat */

I think the case statement is out of date now; I believe more have been added in 11.x. But it should be fine for showing all the possible tablemove/indexmove statuses in 10.2B08.
 
All that said, I agree with CJ and Tom. 6 GB is a lot to tablemove and will leave you with a huge BI file, which in turn will affect the duration of your online backups.
 
Rather than use tablemove for 6GB I would create a new table and indexes in the new areas and then use some 4gl code to copy the data (with the indexes active). Chunk it into transactions of N records at a time (N = 100 is probably good). When you are all done rename the old & new tables. Then recompile. Drop the old table at your convenience. (I would not delete it as a I go -- leaving it in place makes abort and recovery much, much simpler.)

I doubt this would take any longer and it would certainly use much less bi space and be far safer to abort.
 
TomBascom said:
You may benefit from running proutil updatevsts
Click to expand...
I could understand if the code didn't compile, if the VST schema were out of date, but I don't believe its currency would impact whether the table is populated.

@maday15 are you sure that you are running 10.2B08 on the database server? If so, I'd open a support case. There's no indication in the KB of a bug where _UserStatus is not populated when it should be.

Your query code looks similar to mine, apart from the lock strength, so I don't understand why it doesn't work, unless it's an AIX-specific issue which seems unlikely.
 
You must log in or register to reply here.
Back
Top