View source for Content Manager OnDemand Database Tables

{{TOCright}}

= CMOD Database Tables =

This article is a reference of the contents of database tables inside Content Manager OnDemand.  The data on this page is to be considered informational, and may change from version to version, platform to platform.  There are also constraints on some fields and values, meaning that unless you fully understand how the values in specific fields work, you may combine features or options that are NOT compatible with each other, leading to unexpected, erroneous, or unintended behavior, up to and including problems with stability and/or reliability, and/or data loss.  Use this information at your own risk.

== arsag - Application Group Table ==

This table contains all of the configuration data for Application Group Configuration in Content Manager OnDemand.

{| class="wikitable"
!Field!!Sample Values!!Type!!Description
|-
|NAME||System Log, AccountsPayable||String||Name of the Application Group.  No special characters allowed.
|-
|DESCRIPTION||System Log, Accounts Payable||String||Description of the Application Group.
|-
|AGID||5001,5938,6938||Integer||Unique Numeric Identifier for Application Group.  Used as a relation to ARSAGFLD, ARSAG2FLD, etc.
|-
|AGID_NAME||SL,HGA,IBA||String||Three-character unique Alpha identifier for Application Group.  Used in database ("Segment") names, as a filespace name in TSM, and in the cache filesystem.
|-
|DB_SEG||1,2,3,1000||Integer||Used as a counter for the next database segment (table) number.
|-
|ANN_TYPE||?||?||Determines the Annotation Type for an Application Group.
|-
|DB_EXP_DATE||90,365,2557||Integer||Number of days to retain the documents loaded into this Application Group for.  This field stores the Life of Data and Indexes value from the Administrative Client.
|-
|DB_MGRT_DATE||365,720,2557||Integer||Duration after which the database segement (aka table) is migrated to secondary storage.  Rarely
used.
|-
|SM_CACHE_DOC||90,366,720||Integer||The period for which data should be cached after being loaded.
|-
|RESGRP||1,2,3,40,30000||Integer||Used as a counter for the next Resource Group number.
|-
|LOAD_ID||1,359,497295||Integer||Used as a counter for the next LoadID number.
|-
|LOG||?||?||?
|-
|LOAD_ID_SUFFIX||FAA, GAA, HAA||String||Incremented for every 1 million LOAD_ID values.  Incremented left-to-right from F to Z, A to Z, then A to Z until exhausted -- allowing for over 13.5 trillion loads in total.
|-
|MGRT_CACHE_DATE||30,45,90, 180||Integer||Number of days after loading when the documents are migrated to secondary storage.
|-
|REIMPORT_EXP_DATE||30,90,180||Integer||Number of days after which a re-imported database table is expired.
|-
|SID||1,80,88,837||Integer||The Storage Set ID Number.  A unique integer is defined for each new Storage Set defined in the Administrative Client.
|-
|EXPIRE_TYPE||x'44', x'4C', x'53'||HexChar||The Expiration Type for the Application Group:
* x'44' = "D" = Document
* x'4C' = "L" = Load
* x'53' = "S" = Segment
|-
|SEG_ROWS||2500000,10000000||Integer||Maximum number of rows per database table (aka Segment).
|-
|SEG_ID||1,2,3,1000||Integer||Used as a counter for the next Segement (aka Database Table) number.
|-
|OBJ_SIZE||10000,50000||Integer||Maximum Object Size per compressed object loaded into the Application Group.
See [[ARS0141E]] for information on tuning Compressed Object Sizes in CMOD.
|-
|TYPE
|-
|UPD_USERID||ADMIN||String||The UserID of the last user to perform an update to the Application Group.
|-
|UPD_DATE||06/21/2015 08:15||Date||The date of the last update to the Application Group configuration.
|-
|LAST_DOC_DATE||15727||Integer||The numeric internal date of the oldest document that exists in the Application Group.  Deprecated in CMOD v9.0.
|-
|MIGR_SRVR_STR
|-
|SM_CACHE_DELTA
|-
|DATABASE_NAME
|-
|SM_CACHE_RES||-1,90,365,2557||Integer||Duration to cache Resource Groups in Content Manager OnDemand.
|-
|FTI_SERVER||?||String||String that defines configuration data for Full Text Indexing.
|-
|UPD_DT||2015-06-21 08:15:05.385839||Database Date||Date of last update using the internal database date format, introduced in CMOD v9.0.
|-
|LAST_DOC_DT||2016-04-14 17:38:30.285285||Database Date||Date of oldest document that exists in the Application Group.  Introduced in CMOD v9.0.
|-
|LAST_LOAD_DT||2016-04-15 04:05:48.385921||Database Date||Date of the last load into the Application Group in the internal database date format.
|-
|LAST_QUERY_DT||2016-04-16 10:46:03.958183||Database Date||Date of the last query performed on the Application Group, in the internal database date format.
|-
|LAST_RETR_DT||2016-04-15 04:07:19.727563||Database Date||Date of the last document retrieval from the Application Group in the internal database date format.
|}

== arsag2fol - Application Group to Folder Mapping ==
This table is used to map individual Application Groups to individual folders.  An Application Group (AGID) may appear multiple times in multiple Folders (FID).  ARSAG2FOL also maps individual fields in a folder to specific fields in Application Groups.

{| class="wikitable"
!Field!!Sample Values!!Type!!Description
|-
|FID||5104, 5319, 5502||Integer||Folder ID field.  Uniquely identifies a specific folder - see the ARSFOL table.
|-
|AGID||5028, 5195, 5425||Integer||Application Group ID field.  Identifies Application Group(s) that are mapped to the folder identified above.
|-
|AID||0||Integer||Unknown/Unused
|-
|Folder_Field||1, 2, 3, 4||Integer||Identifies the field number inside the folder - see the ARSFOLFLD table.
|-
|AppGrp_Field1||1,2,3,4||Integer||Identifies the number of the field in the Application Group that the folder field is mapped to.
|-
|AppGrp_Field2||-1||Integer||Unknown/Unused.
|}

== arsagfld - Application Group Fields Table ==

This table contains a list of all of the fields defined in CMOD Application Groups.

{| class="wikitable"
|-
!Field!!Sample Values!!Type!!Description
|-
|AGID||5001, 5357, 3525||Integer||Specifies the Application Group that the fields are related to.  Relates to ARSAG.AGID.
|-
|Field||0,1,2,3,4,5||Integer||Sequential number representing the field number, starting at 0.
|-
|Name||RPT_ID,CUST_NUM||String||The text name of the field.
|-
|Type||x'49', x'53', x'62'||HexChar||The hex values represent single-byte characters which describe a field type.
* x'49' = "I" = Integer
* x'53' = "S" = String
* x'62' = "b" = Database Internal Date
|-
|Qual||x'42', x'44'||HexChar||x'42 = "B", x'44' = "D"
|-
|Mask||1, 2, 32770||Integer||This is a vectored field that stores the information in the field checkboxes in the Application Group Field Information Tab.
|-
|String_Type||x'00', x'46', x'56'||HexChar||The hex values represent single-byte characters which describe a string type.  
* x'00' = None (Field is not a string type.)
* x'46' = "F" = Fixed
* x'56' = "V" = Variable
|-
|String_Len||0, 1, 2, 5, 10, 60||Integer||The length of the string field.  '0' if the field is a date or numeric field like Integer or Float.
|-
|String_Case||x'4D', x'55'||HexChar||Represents a string field's case.
* x'4D' = "M" = Mixed
* x'55' = "U" = UPPER
* x'4C' = "L" = lower
|-
|Leading||$, #, <space>, - ||String||A list of characters to remove from the FRONT of the string before inserting them into the database.
|-
|Strip||$, #, <space>, - ||String||A list of characters to remove from ANYWHERE in the string before inserting them into the database.
|-
|Trailing||$, #, <space, ->||String||A list of characters to remove from the END of the string before inserting them into the database.
|-
|Map_Field||NULL, -1, 0, 1, 25||NULL or Integer||
|}

=== arsagfld.mask ===
'''CAUTION:'''  Changing these values without knowledge of how they inter-operate or conflict with each other is dangerous and not supported.

Checking the associated checkbox increases the value by the specified value below:

{| class="wikitable"
|-
!Value!!Checkbox Name!!Description
|-
|1||Indexed||The Field Type is set to 'Indexed' when enabled, Filter when disabled.
|-
|2||Updatable||The value for this field can be updated by users or administrators with sufficient privileges.
|-
|4||Segment||Specifies that this field should be used to segment the database tables, for faster access.  All Application Groups should have a Segment Field for maximum performance.
|-
|8||Application ID Field||This field is used to identify different Applications Defined in the Application Group.
|-
|16||Expire Date||This field is used to calculate the expiration date of the document when the Expiration Type is set to 'Document'.
|-
|32||Log||Inserts the value of this field into the System Log for DocGets (retrievals) - message number 66
|-
|64||User Exit||Sends the field name and value to a user exit.
|-
|128||Unknown||Unknown
|-
|256||Partition||Partitions the database based on this field. Only one field can be selected as the partition field.
|-
|512||Cluster||Builds a cluster index if the database engine supports it.
|-
|1024||Reference||Used by the OnDemand Distibution Facility to determine if the document should be distributed.
|-
|2048||Unknown||Unknown
|-
|4096||Page Count||This field contains the number of pages in the document.
|-
|8192||Document Size||This field contains the size of the document in bytes.
|-
|16384||Unknown||Unknown
|-
|32768||Unknown||Unknown
|-
|65536||Lockdown||This field contains the number of holds that exist for a document when CMOD Enhanced Retention Management is enabled.
|-
|131072||CFS-CMOD||Required for interoperation with FileNet if your server is enabled with CFS:OD.
|-
|262144||Full Text Index||Specifies that this field is indexed by the full-text indexing option for Content Manager OnDemand.
|}

== arsann - OnDemand Document Annotation Table ==

{|class="wikitable"
|-
!Value!!Sample Value||Type!!Description
|-
|ID||?||Integer||Annotation ID Number
|-
|USERID||ADMIN, USER||String||The User ID that created the annotation.
|-
|AGID||5229, 5918||Integer||The unique Application Group Identifer that the Annotation belongs to.
|-
|DOC_NAME||123FAAA||String||The name of the object the annotated document is stored in.
|-
|DOC_OFF||0,83852||Integer||The offset from the beginning of the object, in bytes, that the annotated document begins at.
|-
|DOC_LEN||38582,1894||Integer||The length of the document, in bytes, for the annotated object.
|-
|COMP_OFF||478295, 915753||Integer||The offset from the beginning of the compressed object, in bytes, that the annotated document begins at.
|-
|COMP_LEN||35638,2682||Integer||The length of the compressed document, in bytes, for the annotated object.       
|-
|TIME_STAMP||Deprecated||Integer||In versions prior to CMOD v9.0, the timestamp when the annotation was created.
|-
|PAGE||1,2,50,80000||Integer||The page number that the Annotation should appear upon.
|-
|TYPE||?||Integer||Describes the type of annotation.
|-
|X_OFF||?||Integer||The X offset of the annotation.
|-
|Y_OFF||?||Integer||The Y offset of the annotation.
|-
|RESERVED||?||VarChar||Reserved for future use.
|-
|TEXT_BUF||?||LongVarChar||The text of the annotation, up to 32 kilobytes in size.
|-
|IMAGE_BUF||?||Binary Object||An image annotation up to 1 megabyte in size.
|-
|TABLE_NAME||ABC1, DEF2||String||The name of the table that the annotated document is located in.
|-
|DOC_EXP_DATE||Deprecated||Integer||In versions prior to CMOD v9.0, the date when the document (and by extension, the annotation) was due to expire.
|-
|DOC_EXP_DT||2019-05-15 00:00:00.000000||Integer||In versions after CMOD v9.0, the date when the document (and by extension, the annotation) was due to expire, in the database's native timestamp format.
|-
|TIME_STAMP_DT||2019-05-15 00:00:00.000000||Integer||In versions after CMOD v9.0, the date when the annotation was created, stored in the database's native timestamp format. 
|}