ApplicationXtender - 8 - Data Dictionary

Owned by Chad Nissen
Last updated: Jul 09, 2021
13 min read

ApplicationXtender 8.0 Data Dictionary

The current DB schema version is 8.02.000.

The following tables comprise a summary of all the tables created by ApplicationXtender when a new application is created in Admin Web. Each table lists the fields in the table and gives a brief description of the fields and purpose. Table names followed by a # sign indicate a table that is unique for each application in the system. The # designates the corresponding application id value from the AE_APPS table. For example, AE_DT1 table is the index data table for application id # 1. 

ApplicationXtender may keep backups of the tables listed below. The backup tables begin with an XE_ or YE_ designation (e.g. XE_APPS) and will be located in the same directory or database as the AE_ tables. If these tables need to be dropped, make a full backup of the database before dropping them.

 



AE_ADEFS

The AE_ADEFS table contains the field definitions for each application created in the specified data source.

Column name

Data Type

Size

Description

appid

smallint

2

application id

colnum

smallint

2

application field number

coldesc

varchar

64

application field description

colwidth

smallint

2

width of data type

datatypeid

smallint

2

Data type id  of this application field 

subid

smallint

2

sub id for field type

required

smallint

2

required flag (0=not required, 1=required)

readonly

smallint

2

read-only flag (0=not read only, 1=read only)

flags

smallint

2

flag settings for a field

colname

varchar

64

database column name used

listtable

varchar

32

list-user defined table name

editpic

varchar

254

field validation mask

 Indexes

Index Name

Index Columns

ae_adefs

appid, colnum

Flags

flags

Description

0x0001

Field is searchable

0x0002

Document Level security field

0x0004

Auto-index field

0x0008

Part of unique key

0x0010

Field value required; cannot be null

0x0020

Read-only field

0x0040

Reference key field

0x0080

Reference data field

0x0100

Dual data entry

0x0200

Value mask

0x0400

Leading zero

0x0800

Indexed by

0x1000

Timestamp


Datatype ID

0 Text

6 Ssn

1 Integer

7 Telephone

2 Decimal

8 Zip code

3 Date

9 currency

4 Time

10 Boolean

5 Timestamp

11 User defined


DATATYPE SUBIDS

Integer

1 nnnn

2 n,nnn

3 (nnnn)

4 (n,nnn)


Decimal

1 nnnn

7 n,nnn

13 (nnnn)

19 (n,nnn)

2 nnnn.n

8 n,nnn.n

14 (nnnn.n)

20 (n,nnn.n)

3 nnnn.nn

9 n,nnn.nn

15 (nnnn.nn)

21 (n,nnn.nn)

4 nnnn.nnn

10 n,nnn.nnn

16 (nnnn.nnn)

22 (n,nnn.nnn)

5 nnnn.nnnn

11 n,nnn.nnnn

17 (nnnn.nnnn)

23 (n,nnn.nnnn)

6 nnnn.nnnnn

12 n,nnn.nnnnn

18 (nnnn.nnnnn)

24 (n,nnn.nnnnn)

Date

1 dd-mm-yy

11 mm-dd-yyyy

21 yyyy-mm-dd

31 dd/yyyy/mm

41 yy/mm/dd

2 dd-mmm-yy

12 mm-yyyy-dd

22 yyyy-dd-mm

32 dd/yyyy/mmm

42 yy/ddmm

3 dd-mm-yyyy

13 mmm-dd-yy

23 yyyy-mmm-dd

33 mm/dd/yy

43 yy/mmm/dd

4 dd-mmm-yyyy

14 mmm-yy-dd

24 yyyy-dd-mmmm

34 mm/yy/dd

44 yy/dd/mmm

5 dd-yy-mm

15 mmm-dd-yyyy

25 dd/mm/yy

35 mm/dd/yyyy

45 yyyy/mm/dd

6 dd-yy-mmm

16 mmm-yyyy-dd

26 dd/mmm/yy

36 mm/yyyy/dd

46 yyyy/dd/mm

7 dd-yyyy-mm

17 yy-mm-dd

27 dd/mm/yyyy

37 mmm/dd/yy

47 yyyy/mmm/dd

8 dd-yyyy-mmm

18 yy-dd-mm

28 dd/mmm/yyyy

38 mmm/yy/dd

48 yyyy/dd/mmmm

9 mm-dd-yy

19 yy-mmm-dd

29 dd/yy/mm

39 mmm/dd/yyyy

49 mmm dd, yyyy

10 mm-yy-dd

20 yy-dd-mmm

30 dd/yy/mmm

40 mmm/yyyy/dd

50 dd mmmm, yyy


SSN

1 nnn-nn-nnnn

2 nnnnnnnnn

3 ddd-dd-nnnn

4 dddddnnnn


Telephone

1 nnn-nnnn

2 nnn-nnn-nnnn

3 (nnn)nnn-nnnn

4 (nnn) nnn-nnnn

5 nnn-ddd-dddd

6 (nnn)ddd-dddd

7 (nnn) ddd-dddd



Zip Code

1 nnnnn

2 nnnnn-nnnn


Currency

1 $ nnnn.nn

2 $ n,nnn.nn

3 $ nnnn

4 $ n,nnn

5 $ (nnnn.nn)

6 $ (n,nnn.nn

7 $ (nnnn)

8 $ (n,nnn)


Boolean

1 Yes/No

2 True/False

4 On/Off

8 In/Out

16 Male/Female

32 Exempt/Non-Exempt

64 Asset/Liability

128 Income/Expense

256 Receivable/Payable

AE_AG

The AE_AG table contains the annotation groups.

Column Name

Data Type

Size

Description

grpid

int

4

Annotation group id

grpname

varchar

64

Annotation group name

tsdatetime8timestamp
Indexes

Index Name

Index Columns

ae_ag

grpid

index1

grpname

AE_AGS

The AE_AGS table contains the annotation permission for each AX user or group in the annotation group.

Column Name

Data Type

Size

Description

grpid

int

4

Annotation group id

usrid

int

4

User id ( > 0) or group id ( < 0)

Perms

Int

4

Annotation group permissions

Indexes

Index Name

Index Columns

ae_ags

grpid, usrid

index1

usrid

AE_AI#

The AE_AI# table contains the imported auto index information for an application. Each value in the table gets deleted as it is used in the AE application.

Column Name

Data Type

Size

Description

field(n)

variable

variable

auto index field in application

loaddate

varchar

20

import date of this auto index record

docid

int

4

document id

Indexes

Index Name

Index Columns

ae_ai#

docid

index(n)

If field(n) is searchable

AE_ALS

The AE_ALS table stores the alias (user group) names and their respective alias id values.

Column Name

Data Type

Size

Description

aliasid

int

4

alias id

aliasname

varchar

64

alias name

aliasdesc

varchar

133

alias description

domnam

varchar

255

group's domain name

securid

varchar

254

Security Id

altname

varchar

64

Alternative security alias name

Altdesc

varchar

132

Alternative security alias description

providerid

guid

16identify the security provider for the user
tstimestamp8Time stamp
Indexes

Index Name

Index Columns

ae_als

aliasid

Index1aliasname
Index2securid

AE_AMAP

The AE_AMAP table stores the pointers that map alias id values to user accounts.

Column Name

Data Type

Size

Description

aliasid

int

4

alias id

usrid

int

4

user id

 
Indexes

Index Name

Index Columns

ae_amap

aliasid, usrid

AE_APPS

The AE_APPS table contains the application definition for each application created in the specified data source.

Column name

Data Type

Size

Description

appid

smallint

2

application id

appname

varchar

64

application name

appdesc

varchar

128

application description

dtname

varchar

32

document (DT) table name

dlname

varchar

32

document (DL) page table name

tsstamp

varchar

19

timestamp for application creation

flags

int

4

application flag settings

pathid

smallint

2

current path id for the object files

annpath

smallint

2

current path id for the annotation files

txtpath

smallint

2

current path id for the OCR text

ftpath

smallint

2

ProIndex full-text database file path.

rfname

varchar

32

Key reference (RF) table name

ftsvrid

int

4

Verity K2 server id. The server settings are located in AE_SVRLST table.

ftcolname

varchar

64

ftcolname column is Verity full-text collection name.

sdname

varchar

40

Centera cluster name when using Centera

sdcercid

varchar

82

Certificate file's clip id when using Centera

Indexes

Index Name

Index Columns

ae_apps

appid

Index1

appname

Flags

flag

Description

0x00001

Multiple indexes per document

0x00002

App has Document Level security

0x00004

App has auto-index fields

0x00008

App contains key reference fields

0x00010

App has unique index

0x00020

App use cluster index

0x00040

Can delete/purge application

0x00080

Prompt for checkout before opening document

0x00100

Must provide checkout comment

0x00200

Must provide check in comment

0x00400

Must provide HIPAA comment

0x00800

Document signing has been turned on

0x01000

application supports document retention administration

0x02000

application supports records management classify and transfer

0x04000

application supports Centera storage device

0x08000

(reserved)

0x10000

application supports Software retention

0x20000

application supports EDB Events

AE_ARCH

The AE_ARCH table contains information using by Archive Wizard.

Column Name

Data Type

Size

Description

archid

int

4

archive id

volid

varchar

67

archive volume identifier.

volsize

int

4

volume size

Indexes

Index Name

Index Columns

ae_arch

archid

  AE_ARCHW

The AE_ARCHW table is a working table using by Archive Wizard to update the document location information and remove source files after the associated document page files was archived.

Column Name

Data Type

Size

Description

archid

int

4

archive id

recnum

int

4

record number

appid

smallint

2

application id

docid

int

4

document id

pagenum

int

4

page number of document

pagever

tinyint

1

page version

subpagenum

smallint

2

sub page number

pathid

smallint

2

path id of the object file

objectid

int

4

object id of page version

annote

smallint

2

path id of the document annotation file

newpath

smallint

2

new path id of document object file

annoteid

int

4

Annotation object id

Indexes

Index Name

Index Columns

ae_archw

archid

index1

recnum


AE_AUDIT

The AE_AUDIT table contains audit event information.

Column Name

Data Type

Size

Description

usrid

int

4

User id

wsid

Int

4

Workstation id

wsname

varchar

250

Workstation name

tsstamp

varchar

20

Time stamp of this audit event

eventid

int

4

Audit event id

appid

smallint

2

Application id

docid

int

4

Docucument id

atdesc

varchar

255

Audit description

usrname

varchar

132

User name

sessionid

varchar

40

Session id

Indexes

Index Name

Index Columns

ae_audit

appid

index2

usrid

index3tsstamp
index4wsid
index5eventid

aAudit Events

Audit Event Id

IDL Event Id

Description

0

axAuditID_LOGIN

Login in

1

axAuditID_LOGOUT

Logout ApplicationXtender

2

axAuditID_APPCREATE

Create an AX Application

3

axAuditID_APPDELETE

Delete an AX Application

4

axAuditID_APPMODIFT

The application is modified

5

axAuditID_GROUPCREATE

Group creation

6

axAuditID_GROUPDELETE

Group Deletion

7

axAuditID_GROUPMODIFY

Group Modification

8

axAuditID_USERCREATE

User creation

9

axAuditID_USERDELETE

User deletion

10

axAuditID_USERMODIFY

User modification

11

axAuditID_QUEUECREATE

Queue creation

12

axAuditID_QUEUEDELETE

Queue deletion

13

axAuditID_DOCNEW

New document creation

14

axAuditID_DOCDELETE

Document deletion

15

axAuditID_DOCINDEXMOD

Document index modified

16

axAuditID_DOCINDEXNEW

New document index creation

17

axAuditID_DOCINDEXDELETE

Document index deletion

18

axAuditID_PAGEVIEW

View a document page

19

axAuditID_PAGEINSERT

Insert a document page

20

axAuditID_PAGEDELETE

Document page deletion

21

axAuditID_VERSIONINSERT

Document page version insertion

22

axAuditID_VERSIONDELETE

Document page version deletion

23

axAuditID_ANNOTATIONMOD

Document annotation modification

24

axAuditID_TEXTVIEWCHG

Text view changed

25

axAuditID_BATCHCREATE

Batch creation

26

axAuditID_BATCHDELETE

Batch deletion

27

axAuditID_BATCHIMPORT

Batch importing

28

axAuditID_BATCHINDEX

Batch indexing

29

axAuditID_BATCHPAGEADD

Add page to a batch

30

axAuditID_BATCHPAGEDELETE

Delete a page from a batch

31

axAuditID_BATCHPAGEATTACH

Attach a page to a batch

32

axAuditID_QUERYEXEC

Execute a query

33

axAuditID_QUERYSAVE

Save a query to the AX repository

34

axAuditID_QUERYDELETE

Delete a query object

35

axAuditID_QUERYMODIFY

Modified a existing query object

36

axAuditID_ODMAOP

ODMA operation

37

axAuditID_IMPORTUTIL

Import utility operation

38

axAuditID_ARCHIVE

Archive Wizard operation

39

axAuditID_MIGRATION

Migration Wizard operation

40

axAuditID_GENERICIMP

Generic Importing

500

axAuditID_ID_HIPAA_ACCESS

Accessing HIPAA compliant documents

AE_BS

The AE_BS table contains information concerning batch scan jobs.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

batchid

int

4

batch id

usrid

int

4

user id

tsstamp

varchar

20

timestamp of batch creation

numpages

int

4

number of pages in the batch job

lastpage

int

4

last processed page in batch job

name

varchar

128

name of batch job

bgroup

varchar

255

Batch group

curstate

smallint

2

current state of batch job

memo

varchar

255

description of batch job

owner

int

4

Owner user id

classid

int

4

Batch class id

Indexes

Index Name

Index Columns

ae_bs

appid, batchid

index1

appid, name, bgroup

AE_BSDAT

The AE_BSDAT table contains page pointers for batch scan jobs.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

batchid

int

4

batch id

docid

int

4

document id

pagenum

int

4

number of page in batch

objectid

int

4

object id

pathid

smallint

2

path id

annote

smallint

2

current path id number for the annotations

subpagenum

smallint

2

subpage number

anoteid

int

4

Annotation object id

data1

int

4

ArchiveXtender information

data2

int

4

ArchiveXtender information

data3

int

4

ArchiveXtender information

Indexes

Index Name

Index Columns

ae_bsdat

appid, batchid, docid, pagenum,objectid, subpagenum

AE_BSEXT

The AE_BSEXT table contains page pointers for batch scan jobs.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

classid

int

4

Batch class id (not use)

fieldname

varchar

64

App field name

paramid

int

4

Parameter id

paramval

varchar

254

Parameter value

Indexes

Index Name

Index Columns

ae_bsext

appid, classid, fieldname, paramid

  AE_CFG

The AE_CFG table contains system configuration information including the latest AX version being run on the current data source.

Column Name

Data Type

Size

Description

cfgid

smallint

2

configuration id

cfgvalue

varchar

128

configuration value

Indexes

Index Name

Index Columns

ae_cfg

cfgid


cfgid

Description

Sample Value

1

AX database version

4.20.18

2

Simultaneous users. Used by early AX 3.x

255

3

last assigned workstation id

4

4

creator user name. Used by early version AX


5

last used print job id

 1

6

path semaphore. used by early version AX.


7

last archive id. Used by Archive Wizard

 1

8

data types. Used by AX 3.x only.

 VERSION 3.2

9

Authoring software for the database


22

machine name of AX License Server

 AXSERVER

23

Local number


24

COLD report id

1

25

License Server 5.00 RPC Info (AX 4.20)


26

License Server 5.00 Group Name (AX 4.20)


27

Read-Only repository indicator


30

Security provider 1- CM Security; 2- Windows Security; 3- EAI Security; 5- Directory (LDAP) Security 
For SCT integration which uses EAI security, the value is ‘OtgSctHk.ExtSec’.

1

31

Last assigned user id

2

32

Last assigned group id (reserved)


33

Last annotation group id

1

100

last global id for queues

1

101

last queue id

1

102

last job id

1

103

Last batch class id

1

104

Query Pre-Execution Hook


105

Document Object Creation Hook


106

Document Object Index Hook


107

Document Object Delete Hook


108

Disable autoindex (AI) delete from AI dialog
0x00 - Normal AI operation (delete entry after use)
0x01 - Hide delete button
0x02 - Hide delete all button
0x04 - Hide select button
0x08 - leave entry
0x10 - auto-index fields are read-only during document index
The value should be encoded as hex decimal, so to "Hide delete button' and make 'auto-index fields read-only' the value should be '11'.

1F

109

This string contains CM feature codes
Show underscore application

1

110

Security provider setting in XSAdmin: 1- CM Security; 2- Windows Security; 3- EAI Security; 5- Directory (LDAP)
This value should be the same as the value defined in cfgid 30


111

Generated unique AX database identifier

GUID

112

AppGen Import User hook; works with CM Security only


1000~1255

Reserved for audit


AE_CFG2

The AE_CFG2 table stores the definitions of Record Management mapping between AX Applications and RM system. The mapping information is stored in XML document format.

Column Name

Data Type

Size

Description

idname

varchar

200

Configuration name

seqnum

smallint

2

Segment sequence number

datastream

varchar

1024

Configuration data

Indexes

Index Name

Index Columns

ae_cfg2

idname,seqnum

AE_CFMT

The AE_CFMT table contains formatid information for ODMA (Open Document Management API) Compliance.

Column Name

Data Type

Size

description

formatid

int

4

content format id

contentfrmt

varchar

254

ODMA-aware application name

Indexes

Index Name

Index Columns

ae_cfmt

formatid

AE_CO

The AE_CO table contains document check out information.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

docid

int

4

document id which was checked out

orgdocid

int

4

document id of initial revision

revdocid

int

4

document id reserved for check-in later

revnum

int

4

revision number of checked out document

usrid

int

4

user id who checked out the document

wsname

varchar

250

Workstation name

checkoutby

varchar

64

user who checked out the document

checkotdt

varchar

20

checkout timestamp

cocomment

varchar

254

checkout comment

Indexes

Index Name

Index Columns

ae_co

appid, docid

index1

checkoutby

index2

appid, orgdocid

index3

appid, revdocid

AE_CXRPT

The AE_CXRPT table contains COLD extraction report information.

Column Name

Data Type

Size

Description

reportid

int

4

report id

rptdesc

varchar

254

report description

ts

varchar

20

Timestamp

appid

smallint

2

application id

pathid

smallint

2

path id

objectid

int

4

object id

seqnum

int

4

report segment sequence number

pagecnt

int

4

number of pages in the report segment

rpttype

varchar

32

Report type

clipid

varchar

82

Clip id

Indexes

Index Name

Index Columns

ae_csrpt

reportid, appid, seqnum

index1

appid, pathid, objected

Index2

clipid

AE_DA#

The AE_DA# table contains ODMA compliant attribute settings for each document in an application.

Column Name

Data Type

Size

Description

docid

int

4

document id

docname

varchar

132

document name

author

varchar

132

document author

subject

varchar

132

document subject

comments

varchar

254

comments of document

createdby

varchar

64

document's creator

createddt

varchar

20

document's creation timestamp

modifyby

varchar

64

document's last modified by

modifydate

varchar

20

document's last modified timestamp

Indexes

Index Name

Index Columns

ae_da#

docid

  AE_DFMT

The AE_DFMT table contains custom data formats for default and custom AX data types.

Column Name

Data Type

Size

Description

datatypeid

int

4

Data type id

fmtid

int

4

Data format id begin with 300

fmtname

varchar

128

Data format name

lcid

int

4

Locale id

editpic

varchar

254

Edit picture for the input value

varexpr

varchar

254

Regular expression for data validation

rawexpr

varchar

254

Regular expression for raw data extraction into database

fmtexpr1

varchar

254

Regular expression for display format

fmtexpr2

varchar

254

Regular expression for display format

defval

varchar

254

Default value

flags

int

4

Reserved

scale

smallint

2

Scale for numeric value

dbwidth

smallint

2

Database field size

fmtwith

smallint

2

Max input format length

tstimestamp8timestamp
Indexes

Index Name

Index Columns

ae_dfmt

datatypeid, fmtid

AE_DK#

The AE_DK# contains ODMA compliant keyword search attributes for each document in an application.

Column name

Data Type

Size

Description

docid

int

4

document id

keyword

varchar

132

keyword associated with this document

Indexes

Index Name

Index Columns

ae_dk#

docid

index1

keyword

  AE_DL#

The AE_DL# table contains the page pointers to images for each page in an non-Centera enabled application.

Column Name

Data Type

Size

Description

docid

int

4

document id

pagenum

int

4

page number of document

subpagenum

smallint

2

sub page number of document

pagever

tinyint

1

page version

objectid

int

4

object id

pathid

smallint

2

path id for the object file

annote

smallint

2

path id for the annotations file

formatid

int

4

ODMA client app id defined in ae_cfmt

annoteid

int

4

annotation id

data1

int

4

ArchiveXtender information

data2

int

4

ArchiveXtender information

data3

int

4

ArchiveXtender information

ftoffset

int

4

Full-text offset

ftcount

int

4

Full-text page count

Indexes

Index Name

Index Columns

ae_dl#

docid

index1

pathid, objected

AE_DLSD#

The AE_DL# table contains the page pointers to images for each page in an Centera enabled application.

Column Name

Data Type

Size

Description

docid

int

4

document id

pagenum

int

4

page number of document

subpagenum

smallint

2

sub page number of document

pagever

tinyint

1

page version

objectid

int

4

object id

pathid

smallint

2

path id for the object file

clipid

varchar

82

Clip id for the object file in Centera

filets

varchar

16

UNC file create time stamp

annote

smallint

2

path id for the annotations file

formatid

int

4

ODMA client app id defined in ae_cfmt

ftoffset

int

4

Full-text offset

ftcount

int

4

Full-text page count

Indexes

Index Name

Index Columns

ae_dlsd#

docid

index1

pathid, objectid

Index2

clipid

Index3

filets

AE_DR

The AE_DR table contains the applications and documents, which have been reintegrated to this database by ReportXtender Database Reintegration Wizard.

Column Name

Data Type

Size

Description

mediapath

varchar

133

ReportXtender CD-ROM's media path. Paths begin $ use volume label to identify the media. Paths begin with # uses file path.

appname

varchar

9

Application name

appid

smallint

2

Application id

docmin

int

4

The minimum document id generated by DBR process to associate the first document in the media.

docmax

int

4

The maximum document id generated by DBR process to associate the last document in the media.

newapp

smallint

2

1: means this application was created during the integration process. 0: means the application already exists during integration process.

tsstamp

varchar

20

The timestamp of specified documents in the application was integrated. The timestamp has YYYY/MM/DD HH:MM:SS format.

Indexes

Index Name

Index Columns

ae_dr

mediapath

AE_DT#

The AE_DT# table contains the index data for images in a particular application.

Column Name

Data Type

Size

Description

field(n)

variable

variable

application field

docid

int

4

document id

numobjects

int

4

number of objects in a document

iscom

tinyint

1

cold data flag setting (0=not cold, 1=cold)

idxidsmallint2index id
modtsTimestamp8Modified time stamp
fttstimestamp8Full texted time stamp
Indexes

Index Name

Index Columns

ae_dt#

docid

dtidx1docid, idxid
dtidx2modts
dtidx3ftts

index(n)

If field(n) is searchable


Iscom

Description

0x01

COLD compressed document file

0x02

The document can only be accessed through Workflow integration.

0x04

Document is versioned.

0x08

Reserved for checkin – Document is on Retention

0x10

Document is being checked out

0x20

Document is on retention hold

0x40

Previous revision indicator

0x80

Document is the working revision

  AE_DTYPE

The AE_DTYPE table contains the user-defined data type information.

Column name

Data Type

Size

Description

datatypeid

int

4

Custom data type id begin with 300

typename

varchar

128

Data type name

region

varchar

64

Region code

flags

int

4

Flags

dbtype

smallit

2

OLEDB data type id

maxszie

smallint

2

Max size

minsize

smallit

2

Min size

attrib

int

4

Data type attributes used by AppGen

defattrrib

int

4

Default attributes

tstimestamp8timestamp
Indexes

Index Name

Index Columns

ae_dtype

datatypeid

AE_JOB

The AE_JOB table lists the properties of a job in the queue. See AE_QUEUE table.

Column name

Data Type

Size

Description

qid

int

4

queue id

jid

int

4

job id

jdesc

varchar

133

job description

usrid

int

4

user id that submitted the job

jprty

int

4

job priority

jstat

int

4

job status
(0 – WAITING; 1 –PROCESSING; 2 – FAILED; 3 – FINISHED)

submitted

varchar

19

timestamp of job submission

started

varchar

19

timestamp of job start

finished

varchar

19

timestamp of job finishing

jstatdesc

varchar

133

job status description

Indexes

Index Name

Index Columns

ae_job

qid, jid

AE_JOBEL

The AE_JOBEL contains the elements for each job. This table is the child of AE_JOB. For Example each document submitted for a full text search will appear here.

Column Name

Data Type

Size

Description

qid

int

4

queue id

jid

int

4

job id

elnum

int

4

element number

appid

int

4

application id

docid

int

4

document id

pagenum

int

4

page number

pagever

int

4

page version

elstat

int

4

element status
(0 – PENDING; 1 – DONE)

eldesc

varchar

133

element description

Indexes

Index Name

Index Columns

ae_jobel

qid, jid, elnum

index1

jid

  AE_LOCK

The AE_LOCK contains locks of document object and query object by applications.

column name

Data Type

Size

Description

usrid

int

4

user id of the login

sessionid

int

4

process id in AE_PID table

keytype

smallint

2

application key type: 0 = No Key, 1 = App Key, 2 = Document Key, 3 = Query Key. This the object that is locked

keyid1

int

4

application id

keyid2

int

4

document object id or query id

keyid3

int

4

reserved for future use

keyid4

int

4

workstation id

seqnum

int

4

sequence number generated by the lock

ts

varchar

20

timestamp of lock creation

locktype

smallint

2

Two type of locks. 0 = Read Only and 1 = Read / Write.

flags

int

4

constant value = 0

Indexes

Index Name

Index Columns

ae_lock

sessionid, keyid4, seqnum

index1

keyid1, keyid2, keytype

index2

usrid, keyid4

index3

ts

  AE_LOGIN

The AE_LOGIN table stores all user names, passwords, and unique user id values for a specified data source.

Column Name

Data Type

Size

Description

usrid

int

4

user id

usrname

varchar

64

user name

paswrd

varchar

64

login password

fullname

varchar

133

user's full name

domnam

varchar

255

domain name

secureid

varchar

254

Security id

licgroup

varchar

128

OTG license group

altusrname

varchar

64

Alternative security user name

altpaswrd

varchar

64

Alternative security login password

altfullname

varchar

132

Alternative security user's full name

provideridguid16To identify the security provider for the user
tstimestamp8timestamp
polidvarchar64Password Policy id
laststimestamp8Last login timestamp
pwdtstimestamp8Lass password change timestamp
suststimestamp8Account suspension time stamp
acctsttinyint1

Account state: 0-Active, 1-MustChangePassword, 2-Suspend,3-Disabled.

Indexes

Index Name

Index Columns

ae_login

usrid

index2

usrnam

index3

securid

index4providerid
index5ts
index6polid
index7lasts
index8pwdts
index9susts
index10acctst

AE_NAMES

The AE_NAMES table stores the name and characteristics of a data import specification (e.g. comma delimited ASCII files).

Column Name

Data Type

Size

 Description

spectype

smallint

2

import specification type

appid

smallint

2

application id

specid

smallint

2

specification id

specname

varchar

128

specification name

fdelim

tinyint

1

field delimiter

Indexes

Index Name

Index Columns

ae_names

spectype, appid, sepcid

AE_PATHS

The AE_PATHS table stores all valid path information for the specified data source.

Column Name

Data Type

Size

Description

pathid

smallint

2

path id

path

varchar

250

file path

Indexes

Index Name

Index Columns

ae_paths

pathid

AE_PERMS

The AE_PERMS table contains user permissions for application specific and global security profiles. Records with negative user IDs contain user group permissions. The User ID field for this record matches the Alias ID field from the AE_ALS table.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

usrid

int

4

user id; usrid < 0 is used for group permissions; usrid = 0 is used for audit event setting

access1

int

4

access privileges

mask1

int

4

user access privileges mask

access2

int

4

access privileges

mask2

int

4

user access privileges mask

Indexes

Index Name

Index Columns

ae_perms

appid, usrid


AE_PID

The AE_PID table contains currently active logins on the system and what application state they are in. It is the process control table.

Column Name

Data Type

Size

Description

usrid

int

4

user id

wsid

Int

4

workstation id

pid

int

4

process id

appid

smallint

2

application id

state

smallint

2

state of this process

tsstamp

varchar

20

timestamp of this entry creation

otherinfo

varchar

254

other information

wsname

varchar

250

workstation name

lictypeint4license type
licgrpvarchar254license group
lsttsstampdatetime8timestamp
sessionstateint4session state
Indexes

Index Name

Index Columns

ae_pid

usrid, wsname, pid

index1

appid, state

AE_PQ

The AE_PQ table stores the print queue information for the AE print server.

Column Name

Data Type

Size

Description

jid

int

4

print job id

qname

varchar

32

print job queue name

jstat

smallint

2

print job statistics

numdocs

smallint

2

number of documents in print job

jdesc

varchar

251

job description

Indexes

Index Name

Index Columns

ae_pq

jid

index1

qname

index2

jstat

AE_PQD

The AE_PQD table stores the details of document information of a print job.

Column Name

Data Type

Size

Description

jid

int

4

job id

docnum

int

4

sequential number of documents in this print job

appid

smallint

2

application id

docid

int

4

document id

docdesc

varchar

251

document description

Indexes

Index Name

Index Columns

ae_pqd

jid, docnum, appid, docid

AE_PQN

The AE_PQN table stores the print queue name information for the AE print server.

Column Name

Data Type

Size

Description

qname

varchar

32

queue name

qstat

int

4

queue statistics

qdesc

varchar

251

queue description

Indexes

Index Name

Index Columns

ae_pqn

qname

AE_QRY

The AE_QRY stores the query name and id for each user's query.

Column name

data type

Size

Description

queryid

int

4

query id

name

varchar

133

query name

creatorid

int

4

creator's user id

flags

int

4

query flag

appid

smallint

2

application id associated with the query

Indexes

Index Name

Index Columns

ae_qry

queryid, appid

AE_QSEG

The AE_QESG contains the actual text of the query. Large Queries are broken into pieces. This is the child table of AE_QRY.

Column name

Data Type

Size

Description

queryid

int

4

query id

type

int

4

query type

part

int

4

segment number

segment

varchar

133

query segment text

Indexes

Index Name

Index Columns

ae_qseg

queryid, type, part

AE_QUEUE

The AE_QUEUE stores jobs in order for processing. Presently, only the full text database uses the AE_QUEUE and associated tables.

Column name

Data Type

Size

Description

qid

int

4

queue id

qtype

int

4

queue type

qname

varchar

32

queue name

qdesc

varchar

133

queue description

qstat

int

4

queue status

Indexes

Index Name

Index Columns

ae_queue

qid

AE_RF#

The AE_RF# table contains reference file data for applications configured as key reference file apps.

Column name

Data Type

Size

Description

field(n)

variable

variable

Reference key field of application

field(n)

variable

variable

reference data field of application

Indexes

Index Name

Index Columns

ae_rf#

Field(n) of key reference field

Index#

Other searchable fields

  AE_RH#

The AE_RH# tables contains revision history of documents.

Column name

Data Type

Size

Description

docid

int

4

document id

orgdocid

int

4

document id of initial revision

revnum

int

4

document revision number

checkinby

varchar

64

user who checkin the document

checkindt

varchar

20

check in timestamp

cicomment

varchar

254

checkin comment

Indexes

Index Name

Index Columns

ae_rh#

docid

index1

orgdocid

AE_SEQ

The AE_SEQ table stores the next available sequencing numbers for an application. These include the next available document id, page/object id, and batch id.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

docid

int

4

latest document id

objectid

int

4

latest object id

batchid

int

4

latest batch id

bsdocid

int

4

latest batch document id

formatid

int

4

latest format id

Indexes

Index Name

Index Columns

ae_seq

appid

AE_SPECS

The AE_SPECS table stores data import specifications for a particular data source.

Column Name

Data Type

Size

Description

spectype

smallint

2

specification type

appid

smallint

2

application id

specid

smallint

2

specification id

filecol

smallint

2

field number within the file

appcol

smallint

2

field number within application

maxwidth

smallint

2

maximum field length within the file

fmtstring

varchar

255

format string

Indexes

Index Name

Index Columns

ae_specs

spectype, appid, specid, filecol, appcol

AE_STAT

The AE_STAT table stores the statistical reporting information used in the AE Statistics program. This table is not used in 4.0. It is included for 3.x compatibility.

Column Name

Data Type

Size

Description

usrid

int

4

user id

appid

smallint

2

application id

docadd

int

4

number of documents added by user

docdel

int

4

number of documents deleted by user

docmod

int

4

number of document indexes modified by user

docret

int

4

number of documents retrieved by user

docprt

int

4

number of documents printed by user

docfax

int

4

number of documents faxed by user

docbi

int

4

number of documents batch indexed by user

docmi

int

4

number of documents migrated by user

pagadd

int

4

number of pages added by user

pagdel

int

4

number of pages deleted by user

pagmod

int

4

number of pages modified by user

pagret

int

4

number of pages retrieved by user

pagprt

int

4

number of pages printed by user

pagfax

int

4

number of pages faxed by user

pagbs

int

4

number of pages batch scanned by user

Indexes

Index Name

Index Columns

ae_stat

usrid, appid

AE_STICK

The AE_STICK table stores AE display and configuration information for applications.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

stickyid

smallint

2

sticky id

usrid

int

4

user id

stickyinfo

varchar

255

sticky settings for application

Indexes

Index Name

Index Columns

ae_stick

appid, stickyid, usrid

AE_SVRLST

The AE_SVRLST table stores Full-text server and configuration information for applications.

Column Name

Data Type

Size

Description

serverid

int

4

Server id

typeid

int

4

Server type id

servername

varchar

255

Server name

port

int

4

Port number

attributes

varchar

254

Server attributes

Indexes

Index Name

Index Columns

ae_svrlst

serverid

Index1

typeid

AE_USRPRO

The AE_USRPRO table stores CM user profile information.

Column Name

Data Type

Size

Description

usrid

int

4

User id

profid

int

4

Profile id

appid

smallint

2

Application id

profset

varchar

250

Profile setting

Indexes

Index Name

Index Columns

ae_usrpro

usrid,profid,appid

Index1

Profid

AE_USRPROID

The AE_USRPROID table stores the mapping between the profile id and name.

Column Name

Data Type

Size

Description

profid

int

4

Profile id

profname

varchar

50

Profile name

Indexes

Index Name

Index Columns

Index1profname

ae_usrproid

profid

  AE_WSNAME

The AE_WSNAME table stores the unique workstation id assigned to workstation accessing the CM database. The workstation id start at 100001.

Column Name

Data Type

Size

Description

Wsid

int

4

Workstation id

wsname

varchar

250

Workstation name

Indexes

Index Name

Index Columns

Index1wsname

ae_wsname

wsid

CM_AUDIT

The CM_AUDIT table stores the audit event id, name and description. This table can be used in conjuction with the AE_AUDIT table to generate reports. The addesc column is provided for end user IT to an additional value for the Event name.

Column Name

Data Type

Size

Description

eventid

int

4

Event Id

adname

varchar

33

Event Name

addesc

varchar

255

Event Description (user provided)

Indexes

Index Name

Index Columns

cm_audit

eventid

CM_CLPHIST

The CM_CLPHIST table deleted clip ids

Column Name

Data Type

Size

Description

sdname

varchar

40

Centera cluster's name

Clipid

varchar

82

Clip ids

Movets

varchar

16

Time stamp of the operation

restoken

varchar

80

Resource string, most likely object reference string

jobtype

int

4

PING_LIVE = 1,
PAUSE_SERVICE =2,
OBJECT_DEL=3,
OBJECT_REF=4,
SIGN_CERT=5,
CENTERA_INFO=6,
SET_RETENTION=7,
RETENTION_HOLD=8,
ACTIVATE_GC=9,
CLEAN_CLIPHIST=10,
Sync jobs >= 1000, Async jobs < 1000

Indexes

Index Name

Index Columns

cm_clphist

Sdname, clipid

index1

movets

  CM_CONFIG

The CM_CONFIG table stores the definitions of CM configuration.

Column Name

Data Type

Size

Description

idname

varchar

200

Configuration name

seqnum

smallint

2

Segment sequence number

datastream

varchar

1024

Configuration data

tstimestamp8timestamp
Indexes

Index Name

Index Columns

cm_config

idname,seqnum

  CM_CSJOBQUEUE

The CM_CSJOBQUEUE table stores the jobs submitted to Centera service.

Column Name

Data Type

Size

Description

jobid1

int

4

Jobid1

jobid2

int

4

Jobid2

jobid3

int

4

Jobid3

jobid4

int

4

Jobid4

jobtype

int

4

PING_LIVE = 1,
PAUSE_SERVICE =2,
OBJECT_DEL=3,
OBJECT_REF=4,
SIGN_CERT=5,
CENTERA_INFO=6,
SET_RETENTION=7,
RETENTION_HOLD=8,
ACTIVATE_GC=9,
CLEAN_CLIPHIST=10,
Sync jobs >= 1000, Async jobs < 1000

jobstate

int

4

1 = new; 2 = pending; 3 = done; 4 = failure

jobstatets

varchar

16

Job state change time stamp

jobstatus

int

4

Not used

jobowner

varchar

250

Job owner

requestdata

varchar

1024

Request data in the format of xml

responsedata

varchar

1024

Response data in the format of xml

Indexes

Index Name

Index Columns

cm_csjobqueue

jobid1,jobid2,jobid3,jobid4

index1

jobtype

Index2

Jobstate, jobstatets

Index3

jobstatets

CM_CSJOBQUEUE_LOG

The CM_CSJOBQUEUE_LOG table stores the processed jobs submitted to Centera service.

Column Name

Data Type

Size

Description

jobid1

int

4

Jobid1

jobid2

int

4

Jobid2

jobid3

int

4

Jobid3

jobid4

int

4

Jobid4

jobtype

int

4

PING_LIVE = 1,
PAUSE_SERVICE =2,
OBJECT_DEL=3,
OBJECT_REF=4,
SIGN_CERT=5,
CENTERA_INFO=6,
SET_RETENTION=7,
RETENTION_HOLD=8,
ACTIVATE_GC=9,
CLEAN_CLIPHIST=10,
Sync jobs >= 1000, Async jobs < 1000

jobstate

int

4

1 = new; 2 = pending; 3 = done; 4 = failure

jobstatets

varchar

16

Job state change time stamp

jobstatus

int

4

Not used

jobowner

varchar

250

Job owner

requestdata

varchar

1024

Request data in the format of xml

responsedata

varchar

1024

Response data in the format of xml

Indexes

Index Name

Index Columns

cm_csjobqueue_log

jobstatets

  CM_RSCACHE

The CM_RSCACHE table stores the file cache information generated by CM rendering server for each job queue.

Column Name

Data Type

Size

Description

jobid1

int

4

Job id 1

jobid2

int

4

Job id 2

jobid3

int

4

Job id 3

jobid4

int

4

Job id 4

accesstime

varchar

16

Last access timestamp

accesscnt

int

4

Number of time being accessed

filesize

int

4

filesize

createtime

Varchar

16

Timestamp for file creation

cedata

Ivarchar

196

Cache file path

Indexes

Index Name

Index Columns

cm_rscache

jobid1,jobid2,jobid3,jobid4

index1

accesstime

  CM_RSJOBQUEUE

The CM_RSJOBQUEUE table stores the definitions of CM rendering server job queue.

Column Name

Data Type

Size

Description

jobid1

int

4

Job id 1

jobid2

int

4

Job id 2

jobid3

int

4

Job id 3

jobid4

int

4

Job id 4

jobstate

int

4

Job state

jobstatets

varchar

16

Job state timestamp

jobstatus

int

4

Job status

requestdata

varchar

1024

Request data

responsedata

varchar

1024

Response data

Indexes

Index Name

Index Columns

cm_rsjobqueue

jobid1,jobid2,jobid3,jobid4

index1

jobstate

H#_#

The h#_# table stores document level security information. The first number refers to the application id for the list-user defined field. The second number refers to the field number for the application.

Column Name

Data Type

Size

Description

aliasid

Int

4

alias id for group

dlstype

tinyint

1

value for inclusive or exclusive values

data

variable

variable

contains the actual values to secure or display

Indexes

Index Name

Index Columns

h#_#

aliasid, dltype

  RM_DL

The RM_DL table contains the page pointers to images for each page in an application where the documents are managed by Record Management system.

Column Name

Data Type

Size

Description

appid

smallint

2

Application id

docid

int

4

document id

pagenum

int

4

page number of document

subpagenum

smallint

2

sub page number of document

pagever

tinyint

1

page version

objectid

int

4

object id

pathid

smallint

2

path id for the object file

annote

smallint

2

path id for the annotations file

formatid

int

4

ODMA client app id defined in ae_cfmt

annoteid

int

4

annotation id

data1

int

4

ArchiveXtender information

data2

int

4

ArchiveXtender information

data3

int

4

ArchiveXtender information

Indexes

Index Name

Index Columns

rm_dl

appid, docid

index1

appid, pathid, objectid

RM_DT

The RM_DT table contains the document information which the document is managed by Record Management system.

Column Name

Data Type

Size

Description

appid

smallint

2

application id

docid

int

4

document id

numobjects

int

4

number of objects in a document

iscom

tinyint

1

cold data flag setting (0=not cold, 1=cold)

Indexes

Index Name

Index Columns

rm_dt

appid, docid

RM_QRY

The AE_QRY stores the query name and id for RM classification and retention rules.

Column name

data type

Size

Description

appid

smallint

2

application id associated with the query

queryid

int

4

query id

name

varchar

270

query name

creatorid

int

4

creator's user id

flags

int

4

query flag

Indexes

Index Name

Index Columns

rm_qry

queryid, appid

RM_QSEG

The AE_QESG contains the actual text of the query for Record Management classification and retention rules. Large Queries are broken into pieces. This is the child table of AE_QRY.

Column name

Data Type

Size

Description

appidsmallint2application id

queryid

int

4

query id

type

int

4

query type

part

int

4

segment number

segment

varchar

133

query segment text

Indexes

Index Name

Index Columns

rm_qseg

queryid, type, part

UL#_#

The UL#_# table stores the list-user defined field information. The first number refers to the application id for the list-user defined field. The second number refers to the field number for the application.

Column Name

Data Type

Size

Description

itemid

smallint

2

item id

item

varchar

132

list-user defined item

Indexes

Index Name

Index Columns

primeind

itemid

AE_LIC

The AE_LIC contains the deinitions of license pool table

Column name

Data Type

Size

Description

wsnamevarchar250workstation name
pidint4pid
licservervarchar254license server
licgrpvarchar254license group
lictypeint4license type
activenumint4active number
reservednumint4reserved number
poolstatusint4license pool status
tsstamptimestamp8time stamp
Indexes

Index Name

Index Columns

ae_lic

wsname, pid, liceserver, lictype

AE_APPCFG

The AE_APPCFG contains the definitions of CM Configuration table

Column name

Data Type

Size

Description

idnamevarchar200id name
seqnumsmallint2seq number
datastreamvarchar1024configuration data
usridint4user id
appidsmallint2application id
tstimestamp8timestamp
Indexes

Index Name

Index Columns

ae_appcfg

idname, seqnum, usrid, appid

AE_LOCK2

The AE_LOCK2 contains the deinitions of the new lock table

Column name

Data Type

Size

Description

locktypeint4lock type
keyid1int4key id field
keyid2int4key id field
keyid3int4key id field
keyid4int4key id field
usridint4user id
sessionidvarchar40session id
seqnumint4seq number
tstimestamp8timestamp
Indexes

Index Name

Index Columns

ae_lock2

locktype, keyid1, keyid2, keyid3, keyid4

AE_JOBQUEUE

The AE_JOBQUEUE contains the deinitions of the full text queue table

Column name

Data Type

Size

Description

qidint4queue id
appidsmallint2application id
qnamevarchar64queue name
qdescvarchar133queue description
Indexes

Index Name

Index Columns

ae_jobqueue

appid

index2qname

AE_JOBENTRY

The AE_JOBENTRY contains the deinitions of the full text job table

Column name

Data Type

Size

Description

jobid1int4jobid1
jobid2int4jobid2
jobid3int4jobid3
jobid4int4jobid4
jobtypeint4job type
qidint4queue id
jobstateint4job state
jobstatesvarchar16job states
dependentint4dependent
jobownervarchar250job owner
requestdatavarchar1024request data of full text job
responsedatavarchar1024response data of full text job
Indexes

Index Name

Index Columns

ae_jobentry

jobtype

index2jobstate, jobstates
index3jobstates
index4jobid1, jobid2, jobid3, jobid4


CASO Knowledge Base