Steps to create a custom table

You can create a custom table.

About this task

To create a custom table:

Procedure

  1. Copy the <runtime_sandbox>/repository/entity/extensions/Extensions.xml.sample file as <runtime_sandbox>/extensions/global/entities/your_custom_filename.xml file OR modify your existing extension XML file. For example, assume that ABC_CUSTOMER_ORDER_LINE is a custom table.
  2. Edit the your_custom_filename.xml file to create custom tables as shown in the following example. For a description of the XML attributes, see the table that follows the example.
    <DBSchema> 
      <Entities> 
         <Entity ApiNeeded="Y/N" AuditRequired="Y" Description="" 
          XMLName="OPTIONAL" Name="OPTIONAL" HasHistory="Y/N" Prefix="ABC" 
          TableName="ABC_CUSTOMER_ORDER_LINE" KeyType="Random" > 
          <!-- table columns --> 
          <Attributes> 
             <Attribute ColumnName="CREATETS" DataType="TimeStamp"  
               DefaultValue="sysdate" Description="Create TimeStamp" /> 
             <Attribute ColumnName="MODIFYTS" DataType="TimeStamp" 
               DefaultValue="sysdate" Description="Modify TimeStamp" /> 
             <Attribute ColumnName="CREATEUSERID" DataType="UserId" 
               DefaultValue="&apos; &apos;" Description="Creating User ID" /> 
             <Attribute ColumnName="MODIFYUSERID" DataType="UserId" 
               DefaultValue="&apos; &apos;" Description="Modifying User ID" /> 
             <Attribute ColumnName="CREATEPROGID" DataType="ProgramID" 
               DefaultValue="&apos; &apos;" Description="Creating Program ID" /> 
             <Attribute ColumnName="MODIFYPROGID" DataType="ProgramID" 
               DefaultValue="&apos; &apos;" Description="Modifying Program ID" /> 
             <Attribute ColumnName="LOCKID" DataType="Lockid" 
               DefaultValue="0" Description="Lock ID" /> 
             <Attribute ColumnName="TABLE_KEY" DataType="Key" DefaultValue=" " 
               Description="" Nullable="True/False" XMLName="TableKey" />        
             . 
              . 
          </Attributes> 
          <!-- PrimaryKey is a mandatory attribute in entity definition. 
            This element can have ONLY ONE attribute element --> 
          <PrimaryKey Name="TABLE_NAME_PK"> 
               <Attribute ColumnName="TABLE_KEY" /> 
          </PrimaryKey> 
          <!-- Indices --> 
          <Indices> 
              <Index Name="INDEX_I1" Unique="True/False"> 
                  <Column Name="Attribute2" /> 
                  . 
                  . 
               </Index> 
               . 
               . 
           </Indices> 
           <!-- Relationship --> 
           <Parent ParentTableName="YFS_ORDER_LINE" XMLName="YFSOrderLine" > 
           <Attribute ColumnName="CUSTOM_ORDER_KEY" 
             ParentColumnName="ORDER_LINE_KEY" /> 
           . 
           . 
           </Parent> 
           <!-- ForeignKeys --> 
           <ForeignKeys> 
             <ForeignKey ParentTableName="PARENT_ORDER_LINE" 
               XMLName="PARENTName1" > 
                 <Attribute ColumnName="CUSTOM_ORDER_KEY" 
                   ParentColumnName="PARENT_COLUMN_KEY" /> 
                 . 
                 . 
             </ForeignKey> 
             . 
             . 
           </ForeignKeys> 
           <!-- AuditReferences --> 
           <AuditReferences> 
             <Reference ColumnName="TABLE_KEY" /> 
              . 
              . 
           </AuditReferences> 
       </Entity> 
      </Entities> 
    </DBSchema>

    Notes:

    • If TableName is specified by using a non-ASCII character set, the attributes XMLName and Name are required for this table or entity.
    • If ColumnName is specified by using a non-ASCII character set, the attributes XMLName and Name are required for this column or attribute.
  3. The following table explains the attributes in the entity XML:

    Attribute

    Description

    Entity

    ApiNeeded

    Indicate whether or not APIs should be generated. Valid values are Y or N. A default set of APIs are generated if Y is passed.

    For example in the ABC_CUSTOMER_ORDER_LINE tables, the application creates the following APIs when the database extension JAR file is generated:
    • getABCCustomerOrderLine()
    • getABCCustomerOrderLineList()
    • createABCCustomerOrderLine()
    • changeABCCustomerOrderLine()
    • deleteABCCustomerOrderLine()

    These APIs can be accessed as services by using the Service Definition Framework.

    AuditRequired

    If set to Y, an audit record for this entity is created.

    HasHistory

    This flag indicates whether the custom table can have a history table that is associated with it.

    The default value is N.

    If the flag is set to Y, the appropriate scripts for generating database scripts for creating and altering the history table is generated by dbverify.

    For a custom table, the HasHistory flag must be set to Y for generating history tables. However, if a Parent relationship is defined in the entity XML, this flag is copied from the parent table definition, and all child entities cannot override this flag.

    Prefix

    The prefix added to your custom tables. It is recommended that you do not use a prefix starting with Y.

    TableName

    The name that is given to your custom table.

    KeyType

    Optional. The valid value is Random. When the value is set to Random, the logic used to generate primary keys for the table results in random primary keys rather than sequential primary keys being generated. That is, when KeyType=”Random”, two random digits are included in the primary keys. For example, the format of generated primary keys is YYYYMMDDHHRRMM(SS)NNNNNNNN, where RR represents the two random digits.

    Entity/Attributes/Attribute

    ColumnName

    The names of the column that comprise the table.

    DataType

    The data type of the column. Valid data types are given in the install_dir/repository/datatypes/datatypes.xml file.

    Note:
    • BLOB datatype is not supported.
    • Do not use the Key datatype because it creates an index in the SYSIBM schema that causes the Update OMS Application UCD Self-serve process to fail and forces manual dropping of the primary key to remove the index. The Key datatype is not required since the <PrimaryKey> achieves the same result without creating the unwanted index.

    CompressionSupported

    Optional. Attribute used to indicate whether or not the data compression is supported for this column. Valid values are True or False. If True compression support is enabled.

    Note: If the data inserted into a column has both CompressionSupported and UseCompression attributes set to True, then this attribute should not be set back to False. Doing so would result in all compressed data being retrieved without any decompression.

    UseCompression

    Optional. Attribute used to compress data for this column. Valid values are True or False. If True data is compressed.

    Note: The value of this attribute should be set to True only if the CompressionSupported attribute is set to True.

    QueryAllowed

    Optional. Attribute used to enable a compressible column to be used for queries in a List API. Valid values are True or False. If True the column can be used for queries in the List APIs.

    Note: If the value of the CompressionSupported attribute is set to true then the value of this attribute should also be set to True.

    DefaultValue

    Default value for the column.

    Description

    A description of the columns that could be used in Javadoc or ERD.

    Nullable

    Optional. Attribute used to describe the nullable value of a field. Default is false. Nullable=true is allowed for all columns except Primary Key Attributes and Entity Relationships.

    XMLName

    Optional. XML name of the attribute, if it is different from the name of the attribute.

    Note: If ColumnName is specified using a non-ASCII character set, this attribute is required.

    Choose a name that does not conflict with the base extension. It is recommended that you use Extn as a prefix. It is also strongly recommended that you use the same convention for arriving at the XMLName as the base product does: Make each letter following the underscore in the column name upper case, and the rest lower case. Then, remove the underscores. Thus, Extn_Item_Id should be: ExtnItemId.

    Name

    Mandatory. The name of the database class.

    Entity/PrimaryKey

    Name

    Name of the unique index created for the primary key.

    Note: The name of the primary key in the extension XML should end with _PK.

    ColumnName

    The name of the table column that is identified as the primary key.

    Entity/Indices/Index

    Name

    The index name. This value cannot exceed 18 characters.

    Unique

    This key is present only for custom entities. Valid values are True or False. If True a unique index is created.

    AllowCompressedColumns

    Optional. If True, the index is allowed to contain columns marked with CompressionSupported attribute set to True.

    Column/ Name

    The table column name associated with the index.

    Entity/Parent

    ParentTableName

    Name of the other table this entity has foreign key relationship.

    XMLName

    The XML name of the parent attribute. It should start with the prefix mentioned in the parent table.

    By default the parent table name is assumed.

    Parent/Attribute Level

    ParentColumnName

    Column name in the parent table.

    Note: To create relationships among entities, the data type of parent column must be of type CHAR or VARCHAR.

    ColumnName

    Column name in this custom entity.

    Entity/ForeignKeys/ForeignKey

    ParentTableName

    The name of the table with which the entity has a foreign key relationship.

    XMLName

    XML representation of the element name.

    By default the parent table name is assumed.

    Entity/ForeignKeys/ForeignKey/Attribute

    ParentColumnName

    Column name of the parent table.

    Note: To create foreign keys among entities, the data type of parent column must be of type CHAR or VARCHAR.

    ColumnName

    Column name in this custom entity.

    Entity/AuditReferences/Reference

    ColumnName

    Reference Column name in the audit table.

    Note: In entity definition, relationship can be defined under Parent and ForeignKey elements.
  4. The relationship defined under the ForeignKey element indicates:
    1. If the foreign table is an application database table, for a single record in the foreign table, zero or many records in this custom table may exist.
    2. This is a read-only relationship, hence deletion of a record from the foreign table does not result in the deletion of a matching record from this custom table.
  5. The relationship defined under the Parent element indicates:
    1. For a single record in the parent table, multiple child records may exist.
    2. Deletion of a record from the parent table results in the deletion of matching records from the child table, if any.
  6. Extend the corresponding API templates (for example, getOrderDetails()API) by following the instructions in Extending API Templates.
    Note: The APIs generated by the application for the custom tables can be invoked as a service and through a multiAPI wrapper component.
  7. Build and deploy your extensions.