Follow

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:
C:\ProgramData\TimeXtender\ODX\<version>\BACKLOG\Backlog.sqlite

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)

mceclip0.png

 

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

SELECT
e.ID As TaskRunID,
CASE
WHEN e.TaskType = 1 THEN '--OUTBOUND--'
WHEN e.TaskType = 2 THEN '--OUTBOUND--'
WHEN ds.isTombStoned = 1 THEN ds.DataSourceName || ' (Deleted)'
ELSE ds.DataSourceName
END
AS DataSourceName,
CASE
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))
ELSE 'N/A'
END
AS DataSourceProvider,
ds.isTombStoned as DataSourceIsDeleted,
CASE
WHEN e.TaskType = 1 THEN '--OUTBOUND--'
WHEN e.TaskType = 2 THEN '--OUTBOUND--'
WHEN t.isTombStoned = 1 THEN t.TaskName || ' (Deleted)'
ELSE t.TaskName
END
AS TaskName,
t.isTombStoned as TaskIsDeleted,
CASE e.TaskType
WHEN 0 THEN 'Source -> ODX'
WHEN 1 THEN 'ODX -> DWH'
WHEN 2 THEN 'Preview'
WHEN 5 THEN 'Synchronize'
ELSE 'Other'
END
AS TaskType,
CASE e.isScheduled
WHEN 0 THEN e.UserName
WHEN 1 THEN 'Schedule'
END
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
END
as Status,
CASE
WHEN l.Message IS NULL THEN ' ---- '
WHEN e.State = 4 THEN 'Completed Successfully'
ELSE l.Message
END
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(t.id) as TaskID, t.Name as TaskName, t.isTombstoned
FROM TaskVersions t
WHERE t.VersionNumber =
(SELECT max(VersionNumber) FROM TaskVersions b WHERE b.id = t.id)) as t
ON hex(e.TaskId) = t.TaskID

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

Left Join
(SELECT hex(ds.id) as DataSourceID, ds.Name as DataSourceName, ds.isTombStoned, ds.Settings
FROM DataSourceVersions ds
WHERE ds.VersionNumber =
(SELECT max(VersionNumber) FROM DataSourceVersions b WHERE b.id = ds.id)) 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

SELECT
ds.DataSourceName,
lower(hex(t.DataSourceId)) as DataSourceID,
t.SchemaName,
t.TableName,
lower(hex(t.TableId))as TableID
FROM DataSourceMetaStructures t
Left Join
(SELECT hex(ds.id) as DataSourceID, ds.Name as DataSourceName
FROM DataSourceVersions ds
WHERE ds.VersionNumber =
(SELECT max(VersionNumber) FROM DataSourceVersions b WHERE b.id = ds.id)
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

0 Comments

Please sign in to leave a comment.