| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-23-09, 08:16
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 18
|
|
|
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.
|
|
12-23-09, 08:23
|
|
Registered User
|
|
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
|
|
you can route the output to a message file - not to a table
__________________
Best Regards, Guy Przytula
Database Software Consultant
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
|
|
12-23-09, 08:34
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 18
|
|
|
|
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.
|
|
12-23-09, 09:33
|
|
Registered User
|
|
Join Date: Oct 2009
Location: Calgary, AB Canada
Posts: 37
|
|
Using a shell and sed/awk you could probably parse out that output, convert it to a csv format and load it in yourself
|
|
12-23-09, 13:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Access to the Load Utility Statistics / data
