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);



4 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