How to Query ODX local Backlog SQLite database

Note: Making modifications to the backlog database may lead to inconsistent behavior in ODX.  Do not make any modifications to this file unless TimeXtender Support gives specific instructions to do so.

The ODX server uses a local backlog (SQLite database file) to keep meta data about data sources, storage and tasks etc.  This backlog is continuously synced with the cloud repository.  Analyzing the backlog may help troubleshoot some performance issues.

Note: Only metadata is stored in the backlog or cloud repository. Tasks, data source configuration, incremental load settings, table/field selection etc. The connection strings (user names, passwords etc.) are all encrypted.  

Download SQLite Studio for Windows (note: this is a 3rd party, open source tool)

Decompress the .zip file and run SQLiteStudio.exe

Navigate to the ODX backlog folder

In ODX 20.5 and earlier versions, the file is stored in:
C:\Program Files\TimeXtender\ODX <version>\BACKLOG\Backlog.sqlite

In ODX 20.10 and later versions, the file is stored in:

Make a (backup) copy of Backlog.sqlite file (give it a descriptive name like Copy_Backlog.sqlite)

Note: Do not make extra connection to Backlog.sqlite file in use, since it may interfere with ODX activity in progress.  For analytics, always make a copy of the current backlog file and use the copy for analysis.

From SQLiteStudio UI, Database menu -> Connect to the database (e.g. the Copy_Backlog.sqlite file)



Useful Queries

Once you have successfully connected to the ODX Backlog SQLite file, you can copy, paste, & execute the below queries to retrieve useful info. 

Execution Task Details

e.ID As TaskRunID,
WHEN e.TaskType = 1 THEN '--OUTBOUND--'
WHEN e.TaskType = 2 THEN '--OUTBOUND--'
WHEN ds.isTombStoned = 1 THEN ds.DataSourceName || ' (Deleted)'
ELSE ds.DataSourceName
AS DataSourceName,
WHEN instr(ds.settings,'<ProviderTypeName>')>1 THEN substr(ds.Settings,instr(ds.Settings,'<ProviderTypeName>')+18,instr(ds.Settings,'</ProviderTypeName>')-(instr(ds.Settings,'<ProviderTypeName>')+18))
WHEN instr(ds.Settings,'<ProviderName>')>1 THEN substr(ds.Settings,instr(ds.Settings,'<ProviderName>')+14,instr(ds.Settings,'</ProviderName>')-(instr(ds.Settings,'<ProviderName>')+14))
AS DataSourceProvider,
ds.isTombStoned as DataSourceIsDeleted,
WHEN e.TaskType = 1 THEN '--OUTBOUND--'
WHEN e.TaskType = 2 THEN '--OUTBOUND--'
WHEN t.isTombStoned = 1 THEN t.TaskName || ' (Deleted)'
ELSE t.TaskName
AS TaskName,
t.isTombStoned as TaskIsDeleted,
CASE e.TaskType
WHEN 0 THEN 'Source -> ODX'
WHEN 2 THEN 'Preview'
WHEN 5 THEN 'Synchronize'
ELSE 'Other'
AS TaskType,
CASE e.isScheduled
WHEN 0 THEN e.UserName
WHEN 1 THEN 'Schedule'
AS StartedBy,
strftime('%Y-%m-%d %H:%M:%S',StartTime/10000000 - 62135596800,'unixepoch') StartTime,
strftime('%Y-%m-%d %H:%M:%S',EndTime/10000000 - 62135596800,'unixepoch') EndTime,
strftime('%H:%M:%S',(EndTime-StartTime)/10000000 - 62135596800,'unixepoch') Duration,
(strftime('%s',(EndTime)/10000000 - 62135596800,'unixepoch')-strftime('%s',(StartTime)/10000000 - 62135596800,'unixepoch'))/60.0 DurationInMinutes,
CASE e.State
WHEN 4 Then 'Completed Successfully'
WHEN 6 Then 'Completed with Errors'
WHEN 7 Then 'Failed'
ELSE e.State
as Status,
WHEN l.Message IS NULL THEN ' ---- '
WHEN e.State = 4 THEN 'Completed Successfully'
ELSE l.Message
AS [Details (Preview)]
FROM [ExecutionTasks] e

Left Join
( SELECT l.Id, l.ExecutionTaskId, l.Severity, l.message
FROM ExecutionLogs l
WHERE l.Id =
(SELECT min(Id) FROM ExecutionLogs l1 WHERE l.ExecutionTaskId = l1.ExecutionTaskId)
AND l.Severity > 1) l
ON e.Id = l.ExecutionTaskId

Left Join
(SELECT hex( as TaskID, t.Name as TaskName, t.isTombstoned
FROM TaskVersions t
WHERE t.VersionNumber =
(SELECT max(VersionNumber) FROM TaskVersions b WHERE = as t
ON hex(e.TaskId) = t.TaskID

Left Join Tasks t2
ON hex(e.taskid) = hex(t2.ID)

Left Join
(SELECT hex( as DataSourceID, ds.Name as DataSourceName, ds.isTombStoned, ds.Settings
FROM DataSourceVersions ds
WHERE ds.VersionNumber =
(SELECT max(VersionNumber) FROM DataSourceVersions b WHERE = as ds
ON ds.DataSourceID=hex(t2.DataSourceID)
--WHERE strftime('%Y-%m-%d',StartTime/10000000 - 62135596800,'unixepoch') <'2020-11-30'--StartTime
--WHERE strftime('%Y-%m-%d',EndTime/10000000 - 62135596800,'unixepoch') >'2020-11-01'--EndTime

Order By e.StartTime Desc
Limit 1000

List of Data Source Tables

lower(hex(t.DataSourceId)) as DataSourceID,
lower(hex(t.TableId))as TableID
FROM DataSourceMetaStructures t
Left Join
(SELECT hex( as DataSourceID, ds.Name as DataSourceName
FROM DataSourceVersions ds
WHERE ds.VersionNumber =
(SELECT max(VersionNumber) FROM DataSourceVersions b WHERE =
AND isTombStoned <> 1) as ds
ON ds.DataSourceID=hex(t.DataSourceID)
Order By ds.DataSourceName, t.SchemaName, t.TableName
Was this article helpful?
1 out of 1 found this helpful


Please sign in to leave a comment.