When you are reading/writing data from/to a database with AIMMS via database table identifiers, internally AIMMS will communicate with the database via SQL statements.

If you have a situation where you expect data to be present in AIMMS after a database read, but it is not, you have the option to instruct AIMMS to output the actual SQL statements that are executed to communicate with the database.

By default, AIMMS will not log this information. You can modify this behavior by changing the two project settings

• Database trace file: indicates to which file the information should be logged,
• Database trace level: indicates how much information should be logged:
  • 0 – No tracing
  • 1 – Only trace the SQL statements without the data
  • 2 – Trace the SQL statements including the data

You can set these project options via the Project Options in the settings menu, or within the AIMMS Code. For example, the code below will switch on statement-only tracing for the first read statement and statement-and-data tracing for the second read statement. Finally, the tracing is switched off again for subsequent database communication.

!Only trace SQL statements, and not the data itself
OptionSetValue("Database trace level", 1) ;
!And store the tracing information to this file
option database_trace_file := "TraceReadLocationTable.txt";
 
read from table LocationTable;
 
!Trace both SQL statements and data transferred 
OptionSetValue("Database trace level", 2) ;
!And store the tracing information to this file
option database_trace_file := "TraceReadDepotTable.txt";
 
read from table DepotTable;
 
!Switch off the tracing again for subsequent communication
OptionSetValue("Database trace level", 0) ;

The above code will create a separate trace file for both read statements. The contents of the TraceReadLocationTable.txt file will be the following:

Starting transaction on datasource "AIMMS Transport Example Database.udl"
Creating SELECT command for AIMMS READ statement: SELECT `Location`, `XCoord`, `YCoord` FROM `Locations`
Start executing READ statement
Reading data with SELECT statement
End executing READ statement (75 row(s) read)
Committing transaction on datasource "AIMMS Transport Example Database.udl"

While the contents of the TraceReadDepotTable.txt file will be the following:

Starting transaction on datasource "AIMMS Transport Example Database.udl"
Creating SELECT command for AIMMS READ statement: SELECT `Depot`, `Supply` FROM `Depots`
Start executing READ statement
Reading data with SELECT statement
	Column 1; Type: WString; Value: "Eindhoven"
	Column 2; Type: Double; Value: 14500.000000
	Column 1; Type: WString; Value: "Haarlem"
	Column 2; Type: Double; Value: 10800.000000
	Column 1; Type: WString; Value: "Heerenveen"
	Column 2; Type: Double; Value: 14300.000000
	Column 1; Type: WString; Value: "Middelburg"
	Column 2; Type: Double; Value: 12400.000000
	Column 1; Type: WString; Value: "Zutphen"
	Column 2; Type: Double; Value: 13000.000000
End executing READ statement (5 row(s) read)
Committing transaction on datasource "AIMMS Transport Example Database.udl"

When using these tracing options, please keep in mind that especially tracing with all data (i.e. database trace level = 2) can result in very large trace files.

Related posts:

1. Using block statement to temporary use other project settings
2. Reading multi-dimensional Excel data with ExcelRetrieveTable
3. Exporting a section and importing it in another AIMMS project
4. Aborting execution of AIMMS