Blog

Troubleshoot Redshift / Paraccel COPY command load errors

Posted by Irtaza Hassan on November 16, 2014 at 12:20 AM

The scripts below can be used on both Amazon Redshift and Actian Matrix (Paraccel) for operations related to COPY commands.

 

Number of rows inserted by each succesful COPY command  

SELECT starttime, querytxt, lines_scanneD 
FROM stl_query NATURAL JOIN stl_load_commits
WHERE xid IN (
	SELECT xid FROM stl_utilitytext WHERE RTRIM("text")='COMMIT'
)
ORDER BY starttime DESC;

 


Troubleshooting load errors

The following four tables can come in handy while troubleshooting load errors via the COPY command:

• STL_LOAD_ERRORS

• STL_LOADERROR_DETAIL

• STL_LOAD_ERROR_INFO

• STL_FILE_SCAN


 Some useful queries that make use of the above tables are:


1. Errors in unsuccessful COPY commands for the current day 

SELECT *
FROM stl_load_errors 
WHERE starttime > current_date
ORDER BY starttime DESC, line_number;

 

 2. Combine stl_load_error with stl_tbl_perm to get the table name

SELECT DISTINCT tbl, TRIM(name) AS table_name, query, starttime, 
TRIM(filename) AS input, line_number, colname, -- Use "field" for "colname" in Paraccel
err_code, TRIM(err_reason) AS reason
FROM stl_load_errors sl, stv_tbl_perm sp 
WHERE sl.tbl = sp.id

Categories: Amazon Redshift, Actian Matrix (Paraccel)

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

Already a member? Sign In

0 Comments