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