Use of %SQL_Diag logging along with all-new LOAD DATA functionality

In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.

It is recommended to go through LOAD DATA documentation first. 

After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table

Below is the basic command when table is already created and source file does not contain header row. 

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO MyTable

The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.

Loading from File Source: Header

To specify that the data file has a header row, use the header boolean parameter, as shown in the following example:

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO Sample.Employees
USING {"from":{"file":{"header":"1"}}}

I am using same command in my Medical Datasets Application

SET qry = "LOAD DATA FROM FILE  '"_filename_"' INTO "_tableName_ " "_"USING {""from"":{""file"":{""header"":""1""}}}"
SET rset = ##class(%SQL.Statement).%ExecDirect(,qry)

Here filename is a complete file path and tableName is a table name where data needed to be loaded.

%SQL_Diag Logging

Please note that after every successful operation LOAD DATA will insert record into %SQL_Diag.Result and %SQL_Diag.Message tables.
 

%SQL_Diag.Result table

Below is the structure of %SQL_Diag.Result 

In order to get the detail we need to get maximum ID from %SQL_Diag.Result table after LOAD DATA operation.
Below is the SQL command to get the Maximum ID:

SET qry = "SELECT id FROM %SQL_Diag.Result WHERE ID = (SELECT MAX(ID) FROM %SQL_Diag.Result )"

Let us suppose select of MAX(ID) return 5 from %SQL_Diag.Result table which I demonstrated in my open exchange application preview.
 

We can use Management Portal SQL or $SYSTEM.SQL.Shell() to view the details

SELECT * FROM %SQL_Diag.Result WHERE ID = 5

 %SQL_Diag.Message table

In case of errors, system is saving number of errors in errorcount column of %SQL.Diag.Result table. 
These errors can be viewed in %SQL_Diag.Message table by using  %SQL.Diag.Result ID against %SQL_Diag.Message diagResult column.

Below is the structure of %SQL_Diag.Message table

Below is the command to check errors details:

SELECT * FROM %SQL_Diag.Message WHERE severity = 'error' and diagResult = 5

%SQL_Diag.Message shows the details of the errors.
 

Thanks