Sterling OMS Adding New Column

By | 07/21/2017

Sterling OMS Adding New Column to table (Extending)

Add Column

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
  1. 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)
  1. Go to <Install Directory>/bin in command prompt
  2. 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/ xml file
  • Update Extensions.xml file as

  • 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

How to test ?

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

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

3 thoughts on “Sterling OMS Adding New Column

  1. Aman

    Very Very clear and good explanation . Thanks a Lot for this topics. also Please provide a Hang off table example.

    Reply
    1. ActiveKite-Admin Post author

      Aman,

      We are working on hang off table creation. Will let you know once we ready with post. Thanks lot for your feedback all time.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *