SQL Anywhere can extract SQL from log files with the included dbtran command. For example, execute following SQLs
insert into tablea values (1,'aaaaa');
commit;
and then "from the command line" (the OS command line, not Interactive SQL, etc.).
dbtran <path-and-filename of sqlanywhere logfile>
and execute the above command.
A file named "<logfile name>.sql" will be output, so let's take a look inside.
--BEGIN TRANSACTION-1016-0000698840
BEGIN TRANSACTION
go
--INSERT-1016-0000698913
INSERT INTO DBA.tablea(col1,col2)
VALUES (1,'aaaaa')
go
--COMMIT-1016-0000698931-2023-10-31 14:59:14.009561
COMMIT WORK
go
Ver.17 also shows the time of commit.
A connection to the database is not required to run the dbtran command. Only a log file is required.
This command outputs the contents of the log file as SQL. In other words, the output will be what is written in the log.
SELECT statements are not logged, so they are not included. Note that statements that make changes to the database are written to the log, while LOAD statements describe that a LOAD was performed, but do not describe the actual data loaded.
This command can be used for debugging applications (checking whether a command such as INSERT is being executed) or for manual recovery of a database using the logged contents.
It is not a situation that one would like to imagine, but if a disk is damaged in an environment where logs are renamed several times a day and some of the renamed logs are lost, the normal recovery method can only recover the logs before the lost logs from the backup of the database file. However, if you use dbtran However, with dbtran, it is possible to retrieve the executed commands from the lost logs as well. There is a consistency problem, but it may be possible to narrow the scope of the loss.
Now, there is a caveat when using the output of this dbtran command for debugging purposes.
Note : The executed command is not output as is.
In the previous example I wrote this INSERT statement
insert into tablea values (1,'aaaaa');
However, the actual output of the dbtran command is as follows
INSERT INTO DBA.tablea(col1,col2)
VALUES (1,'aaaaa')
The meaning is the same, but the form is different. Thus, the output of the dbtran command is not the exact command executed by the application. It is actually formatted by the optimizer for execution on SQL Anywhere. It is written to the log. Therefore, be careful when searching the contents of the output file. For the same reason, comments written in SQL with /* */, etc. will not be output.
Special values, etc. are recorded with replacement. For example
insert into tablec values(1,current timestamp)
is executed, the log will show
INSERT INTO DBA.tablec(col1,col2)
VALUES (1,'2023-10-31 15:00:52.023127')
The above will be output as SQL with the actual time of execution described.
There is also a pattern where commands that change data on the application in the first place are not logged.
For example
insert into tablea select * from tableb where col1=100;
If the statement "select * from tableb where col1=100" is executed, and none of the items in tableb correspond to any of the items in tableb, it means that there is nothing to insert, so the INSERT statement itself will not be executed and nothing will appear in the log. Also, even if there is a match
INSERT INTO DBA.tablea(col1,col2)
VALUES (1,'aaaaa')
As shown above, the output will be written as a normal INSERT statement for the relevant number of cases.
Therefore, it is not possible to tell from the log that it is an "INSERT INTO ... SELECT" statement.
When using dbtran output for analysis, it is necessary to understand the behavior of the application side. Also, when searching the contents of the logs, care must be taken as described above.
If there is too much log output to search, create a table like the one below and use
create table forsearch(
"comment"varchar (250),
"time"timestamp
)
Before and after the process you want to check from the logs on the application side
INSERT INTO forsearch VALUES("PROCESS START",CURRENT TIMESTAMP);
<Process to be checked>
INSERT INTO forsearch VALUES("PROCESS END",CURRENT TIMESTAMP);
By inserting an INSERT statement as above, the "PROCESS START" string will be included in the output. This will make it easy to search from there to the "PROCESS END".
dbtran's ability to put logs into readable form can be useful in development and as a last resort in case of disk corruption. It is not something that is used very aggressively, but it is a good feature to keep in mind.
