01 — System Overview
⌂ Hub 02 Vessel Tables 03 Reference Tables 04 Triggers 05 Functions 06 Procedures 07 Migration

System Overview

Architecture, conventions, and reference codes for the Lloyd's Register ABSD schema

1,653
Tables
1,498
Views
1,029
Procedures
282
Triggers
281
Functions
1
Database Statistics
CategoryCount
Total Tables1,653
Views1,498
Stored Procedures1,029
Triggers282
Functions / UDFs281
ABSD_ core tables~180
SUPPLEMENTAL_ tables~25
VSL_ output tables4
CB* reference codebooks~35
2
Architecture Overview

Two Parallel Table Families

ABSD_ Tables — Mainframe-era Input Layer
  • Named using Lloyd's Register Advanced Bulk Ship Data (ABSD) schema codes
  • Columns follow the X##_FIELDNAME naming pattern where X## is an LR data dictionary section code
  • Every coded attribute carries four companion columns: _EFD (effective date, NVARCHAR YYYYMM or YYYYMMDD), _CC (confidence code), _SRCE (source system, 9-char), _VER (verification flag)
  • Dates stored as NVARCHAR, NOT SQL date types
  • Primary key: LRNO (7-char Lloyd's Register vessel identifier)
  • No enforced foreign key constraints — all referential integrity is handled by triggers
VSL_ Tables — Modern Readable Output Layer
  • Human-readable column names
  • Primary key: IMO_NUM (10-char International Maritime Organization number)
  • Populated by triggers and scheduled jobs from ABSD_ data
  • Consumed by downstream reporting and client-facing products

EDM Integration

The system is in a partial migration to an Enterprise Data Management (EDM) platform. Many views now source from EDM.dbo.T_ENT_* and EDM.dbo.T_REF_* tables with ABSD_ as fallback for unmigrated records.

EDM TablePurpose
EDM.dbo.T_ENT_MASTERMaster entity/owner record
EDM.dbo.T_ENT_ADDRESSBest address per entity
EDM.dbo.T_ENT_OWINOwner/company intermediate
EDM.dbo.T_REF_COUNTRYCountry reference (replaces ABSD_CBCY)
EDM.dbo.T_REF_CBUB3Classification notations
3
Key Identifiers
●
Critical mapping rule: ABSD_OVGE.LRNO = VSL_DESIGNATION.IMO_NUM — these store the same value in different tables under different column names. All cross-table joins in views use this pattern.
IdentifierColumnTypeDescription
LRNOABSD_OVGE.LRNOnvarchar(7)Lloyd's Register vessel number — primary key for all vessel data
IMO NumberVSL_DESIGNATION.IMO_NUMnvarchar(10)International Maritime Organization number — equals LRNO in VSL_ tables
Owner CodeABSD_OWGE.OWCODEvarchar(7)Unique company/owner identifier
Incident NumberABSD_CAGE1.INNOvarchar(7)Casualty/incident identifier
Shipbuilder CodeABSD_CBSB.KEYC+KEYG+KEYN+KEYXvarchar(9)SBLDR_CMP — concatenation of 4 key components
Port CodeABSD_CBPPROOT.KEYvarchar(7)7-char port/place identifier
4
LR Data Dictionary Field Coding System

All ABSD_ table columns follow a systematic code: Section_Code + Field_Number + "_" + Field_Name

B01_CALLSIGN → Section B, Field 01, "Call Sign" D06_PASS_UN → Section D, Field 06, "Passengers Underdeck" H01_OWNER_CODE → Section H, Field 01, "Owner Code"
SectionDomain
A01–A03Vessel/Publication Status
B01–B12Basic Vessel Identifiers (callsign, official no., flag, tonnage)
C01Builder/Construction details
D01–D25Vessel Design (type, capacity, propulsion, deck)
E01–E45Equipment (engines, generators, thrusters)
F01–F14Classification and casualty data
G01Name history
H01–H03Owner/Manager/Company history
J06Last change tracking (author, date, time)

Companion Column Pattern

Every coded field X##_FIELDNAME always has these sibling columns:

SuffixNameDescription
_EFDEffective DateNVARCHAR, YYYYMM or YYYYMMDD
_CCConfidence Codeblank=unverified, V=verified, C=confirmed, E=estimated
_SRCESource System9-char source system code
_VERVerification FlagVerification indicator
5
Sequence (SEQNO) Model — History Pattern
SEQNOMeaning
'00'Current/active record — this is always the live data
'01'Most recent historical version (immediately before current)
'02', '03', …Older historical versions in reverse chronological order

Rules

Tables Using SEQNO History

ABSD_OVNA (names), ABSD_HIOW (registered owner), ABSD_HIMA (manager), ABSD_HISM (ship manager), ABSD_HIOP (operator), ABSD_HIPP (parent company), ABSD_HIFL (flag), ABSD_HITL (tonnage), ABSD_HIST (status), ABSD_HIFC1/2 (classification), ABSD_HIBR (builder), ABSD_HIBBC (bareboat charterer), ABSD_HIGBO (group/beneficial owner), ABSD_STSE (statutory survey), ABSD_OVTY (ship type), ABSD_OVSMC (ISM cert), ABSD_OVISSC (ISSC cert), ABSD_OVDOC (DOC company)

6
Date Storage Conventions
⚠
All dates in ABSD_ tables are stored as NVARCHAR, not SQL date types. Always use fnLRDateToRealDate() or fnLRDateToRealDate2022() for date comparisons.
FormatLengthExampleNotes
YYYYMM6 chars202603Month-level precision — most EFD (effective date) fields
YYYYMMDD8 chars20260315Day-level precision — some history tables
YYMMDD6 chars260315J06_LNCHDATE (last-change tracking)
HHMMSS6 chars143022J06_LNCHTIME (last-change time)

Special Date Values

ValueMeaning
'000000' / '00000000'Unknown / not recorded
'999999' / '99999999'Future / open-ended
'198099'Special sentinel: "no EFD set / use default" (used in GROUP_FLEET procedures)
Month '00'Unknown month — treated as January by fnLRDateToRealDate
Month '99'Open/continuing — treated as December by fnLRDateToRealDate
Day '00'Unknown day — treated as 1st by fnLRDateToRealDate
Day '99'Open day — treated as last day of month by fnLRDateToRealDate

The custom function fnLRDateToRealDate(@LRDATE varchar(8)) converts all these to SQL DATETIME. The 2022 version (fnLRDateToRealDate2022) adds proper leap year handling.

7
Trigger Disable Mechanism

All INSERT/UPDATE triggers check a control table before executing:

IF NOT EXISTS (SELECT * FROM TRIGGER_DISABLE) BEGIN -- trigger logic executes only when table is empty END

Table-Specific Disable Flags

TablePurpose
TRIGGER_DISABLEMaster disable — suppresses all triggers when any row exists
TRIGGER_DISABLE_OVGEDisables vessel general triggers (for batch loads to OVGE)
TRIGGER_DISABLE_OVNADisables name history triggers
trigger_disable_himaDisables manager history triggers — used within HIOW trigger to prevent recursion when calling SPMAKEHIMAHISTORICAL
ⓘ
Anti-recursion pattern: The HIOW trigger inserts its own SPID into trigger_disable_hima before calling SPMAKEHIMAHISTORICAL to prevent infinite recursion when that procedure modifies HIMA records.
8
Change Tracking Architecture

There are four parallel audit/change tracking mechanisms:

8.1 — J06 Fields (Last-Modified Stamp)

Every ABSD_ table trigger updates two fields in ABSD_OVGE:

UPDATE ABSD_OVGE SET J06_AUTHOR = UPPER(LEFT(RIGHT(system_user, LEN(system_user)-CHARINDEX('\\',system_user)), 3)), J06_LNCHDATE = RIGHT(YEAR(GETDATE()),2) + REPLICATE('0',2-LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + REPLICATE('0',2-LEN(DAY(GETDATE()))) + CAST(DAY(GETDATE()) AS VARCHAR(2)) WHERE LRNO IN (SELECT LRNO FROM inserted)

J06_AUTHOR extracts the last 3 characters of the Windows login (e.g., DOMAIN\usr → USR).

8.2 — tblChanges (Field-Level Audit Log)

Written by spAnnotateChange called from triggers. Records: table name, field name (English-readable via spGetEnglishFieldName), old value, new value (up to 1,012 chars each), user initials, source, source reference, filing reference, is-new flag.

8.3 — *_UPDATES Tables (Downstream Staging)

Every major table has a parallel ABSD_*_UPDATES tracking table populated by triggers on every modification. These serve as staging queues for downstream systems (APS, Seaweb, etc.).

8.4 — tblAnnotationLogGeneral (Secondary Groupings)

Logs groups of related field changes together. Used for presenting annotation summaries to users.

9
SUPPLEMENTAL Table Pattern

SUPPLEMENTAL_ tables hold data from external sources that enhances (but does not replace) the core ABSD_ master data:

SUPPLEMENTAL TableEnhancesPurpose
SUPPLEMENTAL_ABSD_OVGEABSD_OVGEExtra vessel attributes from third-party feeds
SUPPLEMENTAL_ABSD_OVTYABSD_OVTYAlternative ship type codes from other sources
SUPPLEMENTAL_ABSD_FUGEABSD_FUGEAdditional capacity data
SUPPLEMENTAL_ABSD_FUDI1ABSD_FUDI1Additional deck equipment data
SUPPLEMENTAL_ABSD_FUDI2ABSD_FUDI2Additional engine data
SUPPLEMENTAL_ABSD_FUUN1ABSD_FUUN1Additional tank unit data
SUPPLEMENTAL_ABSD_HIMAABSD_HIMAManager data from external feeds
SUPPLEMENTAL_ABSD_HITLABSD_HITLTonnage from other measurement sources
SUPPLEMENTAL_ABSD_HIFLABSD_HIFLFlag history from AIS / third-party
SUPPLEMENTAL_ABSD_HIFC1ABSD_HIFC1Classification data from other societies
SUPPLEMENTAL_ABSD_STSEABSD_STSESurvey data from external auditors
SUPPLEMENTAL_ABSD_STDEABSD_STDEDeadweight from alternative sources
SUPPLEMENTAL_ABSD_CADIABSD_CADICasualty detail from external incident reports
SUPPLEMENTAL_ABSD_NCONABSD_NCONNewbuilding contracts from broker feeds
SUPPLEMENTAL_ABSD_CBCOROOTABSD_CBCOROOTCompany short names in proper case
SUPPLEMENTAL_ABSD_CBCYABSD_CBCYCountry attributes from external classification
10
Vessel Status Codes (A02_STS)

The field ABSD_HIST.A02_STS (via ABSD_OVGE for current status) uses single-character codes:

CodeMeaningActive?
SIn Service / CommissionYes
LLaid UpYes
TUnder Construction / On TrialYes
RIn Refit / ConversionYes
BUnder Repair / Broken DownYes
CUndergoing ConversionYes
VUnder SurveyYes
JJumboisedYes
PPlanned / OrderedYes
OOn OrderYes
UUnder ConstructionYes
FFitting OutYes
EEngine TrialsYes
AAfloat — Status UnknownMarginal
HHulked (non-propelled)Marginal
NNot in ExistenceNo
XScrapped / Broken UpNo
ZTotal LossNo
DDemolishedNo
WWreckedNo
ⓘ
Active ships query filter: A02_STS IN ('S','L','R','T','C','V','J','P','O','U','F','E')
11
Publication Status Codes (A03_PUBST)
CodeMeaning
UContinues Publishing
DConfirmed — Will Not Publish
YSuppressed — Will Not Publish
SConfirmed Never Was
XSuppressed Confirmed Never Was
ZSuppressed Confirmed Will Not Publish
12
Ownership Role Codes

Used in ABSD_OWSH.REL1_1/REL1_2/REL1_3 and ABSD_OWXR.REL1_1:

CodeRole
RCRegistered Owner
MRShip Manager (Crewing/Technical)
COCommercial Operator
DODirect Owner (beneficial)
BOBeneficial Owner
ASAssociated / Affiliate
BBCBareboat Charterer
GBOGroup/Beneficial Owner
13
Confidence Codes (_CC fields)
CodeMeaning
(blank)Unverified
VVerified
CConfirmed
UUncertain
EEstimated
← Documentation Hub  ·  Next: Vessel Tables →
⇧