SQL - WCS

Reset Password:

There are multiple ways to do this and there is also a utility to generate password. My favorite way of resetting password for users:

UPDATE UserReg SET status=1,  passwordexpired=0,
logonpassword= (SELECT logonpassword FROM userreg WHERE logonid='wcsadmin'),
salt= (SELECT salt FROM userreg WHERE logonid='wcsadmin')
where logonId=< logonid of user being reset >

Resetting wcsadmin password to wcsadmin:
update userreg set logonpassword = '74434f61354f51593862415655304d5268424e54723865685653356151374a2b353163506c4261363730633d202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020' where logonid='wcsadmin'; 
update userreg set salt = 'hsdbacehyoyn' where logonid='wcsadmin'; 
update userreg set status = 1 where logonid='wcsadmin'; 
update userreg set passwordexpired = 0 where logonid='wcsadmin';

Roles for any given user by organization:

select u.logonid,r.name,org.orgentityname from mbrrole mbr, userreg u,orgentity org,role r where mbr.member_id=u.users_id
and org.orgentity_id=mbr.orgentity_id
and mbr.role_id=r.role_id
and u.logonid ='wcsadmin';

Find contract for a given MBRGRP:

select field1 as token, participnt.trading_id as participntTradingId, contract.contract_id as contractContractId, contract.name,
contract.state as State_Should_Be_A_3, contract.majorversion as majorversion, contract.minorversion as minorVersion
from mbrgrp, participnt, contract where
contract.contract_id=participnt.trading_id and participnt.member_id=mbrgrp.mbrgrp_id and mbrgrp.mbrgrpname=< name >

Attribute Dictionary Related Queries:
--all part numbers with a certain attribute.
select * from catentry where catentry_id in (select catentry_id from catentryattr where attr_id in (select attr_id from attr where identifier='name_of_attribute'))

--all attributes for a part number.
select cat.partnumber,at.identifier
from catentry cat, catentryattr cattr, attr at
where cat.catentry_id=cattr.catentry_id and cattr.attr_id=at.attr_id and cat.partnumber='SKU';

--All attribute and values for a SKU, typically attributes are assigned at the product level
select a.identifier, v.value from attrvaldesc v, attr a, catentryattr c
    where
        v.attrval_id = c.attrval_id and
        a.attr_id = c.attr_id and
        c.catentry_id in (select catentry_id from catentry where partnumber='');

Sample Command Reg Insert:

Insert into CMDREG
   (STOREENT_ID, INTERFACENAME, DESCRIPTION, CLASSNAME, TARGET)
 Values
   (0, 'com.ibm.commerce.tools.catalog.commands.DescriptiveAttributeCreateControllerCmd', 'Attribute Extension', 'com.custom..ExtDescriptiveAttributeCreateControllerCmdImpl', 'Local');

SQL to check commerce version from database:

select * from site;

DB2 Import: db2cmd is the equivalent of sqlplus
Windows:   start -> run -> db2cmd
db2 connect to < db_name>  user < user_name >  using < password >
e.g.: db2 import from .ixf of ixf insert into orgentity

note: if you use squirrel, the passwords are stored in for windows
C:\Documents and Settings\wcs\.squirrel-sql\SQLAliases23.xml

Import from a SQL file:


db2 connect to WCLOCAL user wclocal  using
db2 -vtd# -f import.sql



Offer sample inserts:


insert into offer (offer_id,tradeposcn_id,catentry_id,published,qtyunit_id,flags,identifier)
values((select counter+1 from keys where tablename='offer'),10002,401619,1,'C62',1,2011);

insert into offerprice(offer_id,currency,price)
values((select counter+1 from keys where tablename='offer'),'USD',0)

update keys
set counter=counter+1
where tablename='offer';

Schedule a job: Every 100 seconds

insert into schconfig (sccjobrefnum,scchost,member_id,storeent_id,sccrecdelay,sccrecatt,sccpathinfo,sccquery, sccstart,sccinterval,sccpriority,sccsequence,sccactive,sccapptype,interfacename,optcounter)
values ((select Max(sccjobrefnum)+1 FROM schconfig),null, -1000,0, 100,0,'CustomOrderImport',null, CURRENT_TIMESTAMP, 100, 5,0,'A',default,'com.ibm.commerce.scheduler.commands.CheckForWorkCmd',0);


insert into schactive (scsinstrefnum,scsjobnbr,scsactlstart,scsattleft,scsend, scsinstrecov,scsprefstart,scsqueue,scsresult,scssequence,scsstate,scspriority,optcounter)
values (10001,(select max(sccjobrefnum) FROM schconfig), CURRENT_TIMESTAMP,1,null,null,CURRENT_TIMESTAMP,null,null,0,'I',default,0);



7 comments:

  1. You have furnished a worth able content in here. I find this article useful to read and share.Keep sharing like this.
    Regards,
    Java Training in chennai | Java course in chennai

    ReplyDelete
  2. Excellent post. I wish to be a regular contributor of your blog.Thanks for sharing such an informative post and keep update your blog.
    Regards,
    Best JAVA Training in Chennai | JAVA Training | JAVA Training institutes in chennai

    ReplyDelete
  3. I have read your blog its very attractive and impressive. I like it your blog.

    Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

    Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

    ReplyDelete

  4. Hello there, I discovered your website by means of Google at the same time as searching for a related matter, your web site came up, it looks good. I have bookmarked it in my google bookmarks.
    Hello there, simply become aware of your weblog through Google, and found that it is truly informative. I'm going to be careful for brussels. I will appreciate for those who proceed this in future. Many people will be benefited from your writing. Cheers! gmail login email

    ReplyDelete
  5. WCS Questions

    53. What are the tables used for Order Management ?
    ORDERS, XORDERS, ORDERITEMS, XORDERITEMS, ORDADJUST, ORDIADJUST

    54. What are the tables used for Catalog Management ?
    CATALOG , CATALOGDSC , CATENTRY, CATENTREL, CATENTATTR, ATTRIBUTE, ATTR , ATTRVAL , ATTRDESC , ATTRVALDESC , CATENTTYPE , CATGPENREL.

    55. What are the tables used for Member Management ?
     USERS, MEMBER, USERREG, USERSDEMO, USERPROF, ADDRESS, ADDRESSBOOK 


    65. Explain about INVENTORY table ? 
    The INVENTORY contains information like CATENTRY_ID, STORE_ID, FFMCENTER_ID, QUANTITY and INVENTORYFLAGS. INVENTORYFLAGS values can be as below:- 1 = noUpdate. The default UpdateInventory task command does not update QUANTITY. 2 = noCheck. The default CheckInventory and UpdateInventory task commands do not check QUANTITY. 


    95. Name few WCS controller commands used in your application for Order subsystem ?
    OrderCreateCmd
    OrderItemAddCmd
    OrderItemDeleteCmd
    OrderItemUpdateCmd
    OrderProcessCmd
    ProcessOrderCmd
    PrepareOrderCmd
    PreProcessOrderCmd
    OrderCancelCmd
    OrderDisplayCmd


    96. Name few WCS controller commands used in your application for Member subsystem ?
    PostOrgEntityAddCmd
    PostOrgEntityUpdateCmd
    CheckUserInMemberGroupCmd
    LogonCmdImpl
    DBAuthenticationCmd
    LDAPAuthenticationCmd
    LogoffCmd

    97. Name few WCS controller commands used in your application for Catalog subsystem ?
    AttributeCreateControllerCmd
    ProductPricingControllerCmd
    CategoryDisplayControllerCmd
    ProductDisplayControllerCmd
    GetContractUnitPriceCmd
     
    98. Name few WCS controller commands used in your application for Marketing subsystem ? 
    PromotionCodeAddRemoveControllerCmd
    AddOrderItemWithPromotionCodeOrCouponCmd
    CouponAddRemoveControllerCmd





    62. Explain relationship between CATENTRY and CATENTREL?
    The CATENTRY_ID is the foreign key in the ORDERITEMS table and OICOMPLIST table. The CATENTRY_ID is the primary key in the CATENTRY table. The relationship of the parent and child CATENTRY_ID can be defined in CATENTREL table. The CATENTRYTYPE_ID can be Product Bean, Item Bean, Package Bean, Bundle Bean, DynamicKitBean and CatalogEntryBean. The CATENTDESC contains all the descriptive information for the CATENTRY_ID and the CATENTRY_ID is the primary key in the table.

    The LISTPRICE contains the pricing information of the CATENTRY_ID. The OFFER table contains the OFFER_ID of the CATENTRY_ID. The OFFERPRICE table contains the offer price of the CATENTRY_ID and the OFFER_ID is the primary key. 

    ReplyDelete