Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: Access to the Load Utility Statistics / data

    I am on DB2 9.5 LUW - on windows

    i have run the LOAD utility with replace.

    Can i get access to the load statistics for a job using sql.

    I have run the load utility which has given me this data/info at the end

    Number of rows read = 101
    Number of rows skipped = 0
    Number of rows loaded = 96
    Number of rows rejected = 5
    Number of rows deleted = 0
    Number of rows committed = 101

    is this data written to a table in db2 anywhere.

    can i write this data to table using sql if it does not get written to a table.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you can route the output to a message file - not to a table
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2009
    Posts
    20
    I have generated a message file

    this is text which displays the same data.

    I would need a program to extract the number of records read, skipped, loaded etc.

    there is nowhere in DB2 where this data is held.

  4. #4
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    Using a shell and sed/awk you could probably parse out that output, convert it to a csv format and load it in yourself

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to load the message file into a work table, then edit the rows in the work table and insert them into statistics table.

    Here is my trial.....

    Create tables:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE load_messages
    ( msg_load_time  TIMESTAMP    NOT NULL
                     DEFAULT CURRENT TIMESTAMP
    , line_id        INTEGER      NOT NULL
                     GENERATED ALWAYS AS IDENTITY
    , line_seq       INTEGER
    , text           VARCHAR(80)
    , comment        VARCHAR(80)
    , PRIMARY KEY (msg_load_time, line_id)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE load_statistics
    ( msg_load_time  TIMESTAMP    NOT NULL
    , data_file      VARCHAR(80)
    , beginning_time TIMESTAMP
    , finished_time  TIMESTAMP
    , rows_read      INTEGER
    , rows_skipped   INTEGER
    , rows_loaded    INTEGER
    , rows_rejected  INTEGER
    , rows_deleted   INTEGER
    , rows_committed INTEGER
    , PRIMARY KEY (msg_load_time)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Create test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE employee_load LIKE employee;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    EXPORT TO expot_employee.del OF del MESSAGES expot_employee.msg SELECT * FROM employee;
    ------------------------------------------------------------------------------
    
    Number of rows exported: 42
    
    
    ------------------------------ Commands Entered ------------------------------
    LOAD FROM expot_employee.del OF del MESSAGES load_employee.msg REPLACE INTO employee_load;
    ------------------------------------------------------------------------------
    
    Number of rows read         = 42
    Number of rows skipped      = 0
    Number of rows loaded       = 42
    Number of rows rejected     = 0
    Number of rows deleted      = 0
    Number of rows committed    = 42
    Load the message file into the work table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    LOAD FROM load_employee.msg OF del INSERT INTO load_messages(text);
    ------------------------------------------------------------------------------
    SQL3501W  The table space(s) in which the table resides will not be placed in 
    backup pending state since forward recovery is disabled for the database.
    
    SQL3109N  The utility is beginning to load data from file 
    "D:\IBM\SQLLIB_V97\tools\load_employee.msg".
    
    SQL3500W  The utility is beginning the "LOAD" phase at time "2009-12-24 
    02:42:17.385313".
    
    *** Snipped ***
    
    SQL3107W  There is at least one warning message in the message file.
    
    
    Number of rows read         = 31
    Number of rows skipped      = 0
    Number of rows loaded       = 31
    Number of rows rejected     = 0
    Number of rows deleted      = 0
    Number of rows committed    = 31
    INSERT a row into load_statistics table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO load_statistics
    SELECT msg_load_time
         , MAX(data_file)
         , MAX(beginning_time)
         , MAX(finished_time)
         , MAX(rows_read)
         , MAX(rows_skipped)
         , MAX(rows_loaded)
         , MAX(rows_rejected)
         , MAX(rows_deleted) 
         , MAX(rows_committed)
    FROM (
    SELECT msg_load_time
         , CASE
           WHEN POSSTR(text, 'SQL3109N') > 0 THEN
                LEAD(text) OVER(PARTITION BY msg_load_time ORDER BY line_id)
           END
         , CASE
           WHEN POSSTR(text, 'SQL3500W') > 0 THEN
                SUBSTR( text || ' ' ||
                        LEAD(text) OVER(PARTITION BY msg_load_time ORDER BY line_id)
                      , POSSTR(text, 'at time "') + 9, 26)
           END
         , CASE
           WHEN POSSTR(text, 'SQL3515W') > 0 THEN
                SUBSTR( text || ' ' ||
                        LEAD(text) OVER(PARTITION BY msg_load_time ORDER BY line_id)
                      , POSSTR(text, 'at time "') + 9, 26)
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows read') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows skipped') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows loaded') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows rejected') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows deleted') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
         , CASE
           WHEN POSSTR(text, 'Number of rows committed') > 0 THEN
                INTEGER( SUBSTR(text, POSSTR(text, '=') + 2) )
           END
      FROM load_messages m
     WHERE NOT EXISTS
           (SELECT *
              FROM load_statistics s
             WHERE s.msg_load_time = m.msg_load_time
           )
    ) m
    ( msg_load_time
    , data_file
    , beginning_time
    , finished_time
    , rows_read
    , rows_skipped
    , rows_loaded
    , rows_rejected
    , rows_deleted   
    , rows_committed )
     GROUP BY
           msg_load_time;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Result statistics in the load_statistics table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM load_statistics;
    ------------------------------------------------------------------------------
    
    MSG_LOAD_TIME              DATA_FILE                                                                        BEGINNING_TIME             FINISHED_TIME              ROWS_READ   ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED
    -------------------------- -------------------------------------------------------------------------------- -------------------------- -------------------------- ----------- ------------ ----------- ------------- ------------ --------------
    2009-12-24-02.42.17.178000 D:\IBM\SQLLIB_V97\tools\expot_employee.del                                       2009-12-24-01.20.16.573016 2009-12-24-01.20.17.200132          42            0          42             0            0             42
    
      1 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •