Clarify rules for MESSAGE LOG FILE and ROW LOG FILE

[Breck_Carter]

The rules for the MESSAGE LOG FILE and ROW LOG FILE options of the LOAD TABLE statement in SQL Anywhere 17.0.9.4982 are not entirely clear from the Help.

Please confirm the following statements:

1. The MESSAGE LOG FILE and ROW LOG FILE files are always created even if they are empty.

2. The MESSAGE LOG FILE and ROW LOG FILE files are overwritten if they exist.

3. The phrase "in addition to reporting the row to the user" in the Help for ROW LOG FILE needs an explanation, because I haven't seen any such reporting other than what's in the MESSAGE LOG output. Does it not apply when ALLOW ALL ERRORS is in effect?

Item 1 is just annoying surprising 🙂

Item 2 implies that different file names must be used for different LOAD TABLE statements; i.e., you cannot have all the messages written to one file. ( Yes, that is a candidate for a Characteristic Error. So is forgetting the FILE keyword on MESSAGE LOG FILE and ROW LOG FILE 🙂

Item 3 is just a head-scratcher. The ALLOW topic in the Help also says "The database server reports the last error that was encountered to the user" but I have yet to see any such report. Does it not apply when the LOAD TABLE is inside a stored procedure?

Here is an example where the second LOAD statement failed on every row because it used the wrong input file:

LOAD TABLE DBA.rroad_alert FROM 'C:\\temp\\alert.txt' 
   ALLOW ALL ERRORS 
   MESSAGE LOG FILE 'C:\\temp\\$MESSAGE_LOG_alert.txt' 
   ROW LOG FILE 'C:\\temp\\$ROW_LOG_alert.txt';

LOAD TABLE DBA.rroad_alert_cancelled FROM 'C:\\temp\\alert.txt' 
   ALLOW ALL ERRORS 
   MESSAGE LOG FILE 'C:\\temp\\$MESSAGE_LOG_alert_cancelled.txt' 
   ROW LOG FILE 'C:\\temp\\$ROW_LOG_alert_cancelled.txt';

Contents of $MESSAGE_LOG_alert_cancelled.txt:

Cannot convert Recovery urgency. The Recover to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 1 of the data file
Cannot convert Database unresponsive. Foxhou to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 2 of the data file
Cannot convert Database unresponsive. Foxhou to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 3 of the data file
Cannot convert Connections. The number of co to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 4 of the data file
Cannot convert CPU usage. The CPU time has b to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 5 of the data file
Cannot convert Connection CPU. The approxima to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 6 of the data file
Cannot convert Connections. The number of co to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 7 of the data file
Cannot convert Long sample time. The sample  to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 8 of the data file
Cannot convert I/O operations. There have be to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 9 of the data file
Cannot convert Unscheduled requests. The num to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 10 of the data file
Cannot convert Database unresponsive. Foxhou to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 11 of the data file
Cannot convert Database unresponsive. Foxhou to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 12 of the data file
Cannot convert Database unresponsive. Foxhou to timestamp.  The bad value was supplied for column 'alert_in_effect_at' on row 13 of the data file

Contents of $ROW_LOG_alert_cancelled.txt:

[ exactly the same data as in the FROM 'C:\\temp\\alert.txt' file, which was the wrong file 🙂 ]

[johnsmirnios · Accepted solution]

1. Yes, each log is always created if specified on the LOAD statement. It seems reasonable & consistent. If you UNLOAD T TO 'foo', 'foo' will get created even if 'T' is empty. On a command line, if you run "command_that_has_no_output >foo", foo will be created. If we didn't create the files, you might be asking 'Hey, how can I tell if the UNLOAD statement even honoured my LOG clauses or if there were, in fact, no errors?".

2. Yes, the log files are always truncated. The purpose of these clauses is to support enormous LOADs so that you don't run a 10-hour long LOAD just to find out the last row had an error and rolled the whole thing back. After a LOAD is executed, you are left with a data file that can be edited and then used in another LOAD statement. Mixing data from different LOADs would mean that you would need to split the row log into separate files manually in order to adjust the data and use it in another LOAD statement.

3. It would be more accurate to say that the first unallowed error is returned to the user. If we were to report an error to the client even though all errors were allowed, then the client would incorrectly think the statement failed and was rolled back.

[Breck_Carter · Accepted solution]

OK, I now agree with all the decisions except one (leaving the details out of the Help 🙂