Sterling OMS Adding New Column

Sterling OMS Adding New Column to table (Extending)

Sterling OMS Adding New Column to table (Extending)

Requirement: Add new column (SELLER_ITEM_DESCRIPTION) in YFS_ITEM table; should be able store 200 characters.

How to check table can be extended ?

1. Open ERD/HTML/index.html
2. Look for YFS_ITEM table name and click on the link
3. Look for String “You can extend this table by adding columns and by creating hang-off table(s)”
4. If above string available; then we should be able to add new column

What is difference between Entitydeployer vs Dbverify?

Entitydeployer: Use this tool to build database extensions. The entitydeployer calls dbverify, applies all the SQLs generated by dbverify, and then rebuilds the entities.jar file.

Command : <Install Directiry>/bin/deployer.cmd -t entitydeployer

Note : Quick Question what does -t means in above line: Ant script target name from <Install Directory>\properties\deployer.xml

DBVerify: Dbverify performs database schema creation, verification, and correction. Dbverify is used to ensure database schema integrity. Dbverify is used during the following tasks:

• Installation
• Upgrade
• Customization

Command : <Install Directiry>/bin/dbverify.cmd

Lets learn about few installation properties values related to database

sandbox.cfg Parameter	What happens when entitydeployer command called	Remarks
NO_DBVERIFY=false REINIT_DB=false	New resources.jar file gets created DB Verify program called and DDL statements gets created New entities.jar gets created DB will NOT be updated with newly added column/changes	From Development point of view this option is good. Make change in XML file get the DDL by running entitydeployer command and validate the changes and manually run the DDL on database.
NO_DBVERIFY=false REINIT_DB=true	New resources.jar file gets created DB Verify program called and DDL statements gets created New entities.jar gets created DB will be directly updated with DDL created part of DB verify program	This option is good with controlled environment (Quality testing, User Acceptance testing)
NO_DBVERIFY=true REINIT_DB=false	New resources.jar file gets created DB Verify program NOT called and DDL statements not created New entities.jar gets created No change to DB because DDL statements not created	Manually need to run dbverify.cmd to generate DDL statements and manually execute in the database.

How to modify database related properties in sandbox.cfg?

1. From <Install Directory>/properties folder take a copy of sandbox.cfg as sandbox.cfg.original
2. Search for following values in sandbox.cfg
   • LOAD_FACTORY_SETUP=true
   • NO_DBVERIFY=false
   • REINIT_DB=true

3. First make change in sandbox.cfg file
   • LOAD_FACTORY_SETUP=true (keep as true)
   • NO_DBVERIFY=false (DDL scripts created automatically)
   • REINIT_DB=false (Database update will not happen automatically; Have to run the DDL script manually)

4. Go to <Install Directory>/bin in command prompt
5. Run <Install Directory>/bin/setupfiles.cmd file

How to add Database Column?

1. Open <Install Directory>\repository\datatypes\datatypes.xml file and find varchar2 field with 200 character support
2. Check for data type which supports 200 characters

<DataType Name=”VARCHAR2-200″ Size=”200″ Type=”NVARCHAR”/>

• Copy the install_dir/repository/entity/extensions/Extensions.xml.sample file as install_dir/extensions/global/entities/Extensions.xml file
• Update Extensions.xml file as

<?xml version="1.0" encoding="UTF-8"?>
<DBSchema>
   <Entities>            
      <Entity TableName="YFS_ITEM" >
        <Attributes>
        <Attribute ColumnName="EXTN_SELLER_ITEM_DESC" 
          DataType="VARCHAR2-200"
          DecimalDigits="" 
          DefaultValue="' '" 
          Description=" "  
          CaseInsensitiveSearch="Y"
          ShadowColumnName="EXTN_SELLER_ITEM_DESC_CI"
          Nullable="true" 
          Size="200" 
          Type="VARCHAR2" 
          XMLName="ExtnSellerItemDesc"
          XMLGroup="Extn" 
          SqlServerDataType=""/>		  
        </Attributes>		
      </Entity>
    </Entities>
</DBSchema>

• Run command from bin folder : deployer.cmd -t entitydeployer
• Query your database to see if column added? (No; because we updated the sandbox properties updated with REINIT_DB=false)
• Open file \repository\scripts\EFrame_TableChanges.sql

ALTER TABLE YFS_ITEM 
 ADD (
EXTN_SELLER_ITEM_DESC VARCHAR2(200)   DEFAULT ' '  NULL 
,EXTN_SELLER_ITEM_DESC_CI VARCHAR2(200)   NULL 
 )
/

• Run the above script in database and make sure column added
• Perform build and deploy the ear

How to test ?

Insert Record : Using HTTP API tester call manageItem API with below input and template xml.

<?xml version="1.0" encoding="UTF-8"?>
<ItemList>
<Item Action="Manage" CanUseAsServiceTool="N" GlobalItemID="" ItemGroupCode="PROD" ItemID="DEMO1" OrganizationCode="Matrix" UnitOfMeasure="EACH">
<PrimaryInformation AssumeInfiniteInventory="N" CostCurrency="USD" CountryOfOrigin="" />
<InventoryParameters ATPRule="" IsFifoTracked="N" IsSerialTracked="N" LeadTime="0" MinNotificationTime="0" NodeLevelInventoryMonitorRule="" ProcessingTime="0" TagControlFlag="N" TimeSensitive="N" />
<ClassificationCodes CommodityCode="" ECCNNo="" HarmonizedCode="" HazmatClass="" NAICSCode="" NMFCClass="" NMFCCode="" OperationalConfigurationComplete="N" PickingType="" PostingClassification="" Schedule_B_Code="" StorageType="" TaxProductCode="" UNSPSC="" VelocityCode="" />
<Extn ExtnSellerItemDesc="Omega 42'' Plasma Television" />
</Item>
</ItemList>

New Item should get created and new column should be updated with seller item description provided above.

Fetch Record : Using HTTP API tester call getItemDetails API with below input and template xml.

Input to getItemDetails API	Template	Output
<Item ItemID=”100013″  OrganizationCode=”Matrix” UnitOfMeasure=”EACH” />	None	<?xml version=”1.0″ encoding=”UTF-8″?> <Item CanUseAsServiceTool=”N” GlobalItemID=”” ItemGroupCode=”PROD” ItemID=”100013″ ItemKey=”2009070804444945478″ OrganizationCode=”Matrix” UnitOfMeasure=”EACH”> <PrimaryInformation AssumeInfiniteInventory=”N” CostCurrency=”USD” CountryOfOrigin=”” /> <InventoryParameters ATPRule=”” IsFifoTracked=”N” IsSerialTracked=”N” LeadTime=”0″ MinNotificationTime=”0″ NodeLevelInventoryMonitorRule=”” ProcessingTime=”0″ TagControlFlag=”N” TimeSensitive=”N” /> <ClassificationCodes CommodityCode=”” ECCNNo=”” HarmonizedCode=”” HazmatClass=”” NAICSCode=”” NMFCClass=”” NMFCCode=”” OperationalConfigurationComplete=”N” PickingType=”” PostingClassification=”” Schedule_B_Code=”” StorageType=”” TaxProductCode=”” UNSPSC=”” VelocityCode=”” /> </Item>
<Item ItemID=”100013″ OrganizationCode=”Matrix” UnitOfMeasure=”EACH” />	<Item> <Extn  /> </Item>	<Item ItemID=”100013″ OrganizationCode=”Matrix” UnitOfMeasure=”EACH”> <Extn ExtnSellerItemDesc=”Omega 42″ Plasma Television”/> </Item>

Conclusion : This post helps to add new column into existing OMS database easily. Please share your feedback at support@activekite.com OR comment in the post.

Reference: https://www.ibm.com/support/knowledgecenter/SS4QMC_9.3.0/com.ibm.help.ext.database.doc/Extending_DB_plugin-gentopic2.html

Register with us to get more OMS updates

OMS Interview Questions