BBS

(1) Dump of BBS is in
‘/Sybase/dump/bbs_sucess.dmp’
load database dump using following command
login using isql -Usa –P
load database vectest from ‘/Sybase/dump/bbs_sucess.dmp’
(2) To run BBS check following point
Do All The step require for vector BOD
Check data in following table
(i) BUY_TRAN
Previous day data should be there
(ii) BBO_BUY_TRAN
This table should be empty. Data will come in this table after Opening session.
(iii) INT_ORDER_MAP
In this table previous day data should be there.
(3)
If database transaction log is full then login with
isql –Usa –P
run following command
dump tran vectest with truncate_only

(4)
For BBS change no of user connection using following command
isql –Usa –P

(i)
sp_configure "connection"
This command will display current number of connection
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
cis idle connection timeout 0 0 0
0 seconds dynamic
max cis remote connections 0 526 0
0 number dynamic
number of remote connections 20 125 20
20 number static
number of user connections 25 45052 150
150 number dynamic

current no of user connection is -150


To change No of connection run following command
sp_configure "number of user connections",600

(5)
If it is fresh IPO then delete following table
DELETE FROM TRADE_TRAN
DELETE FROM BBS_PRICE
DELETE FROM BUY_TRAN
DELETE FROM BBO_BUY_TRAN
DELETE FROM INT_ORDER_MAP

Update
Scrip_mast table minallowprice and maxallowprice
Using follwing command
UPDATE SCRIP_DET SET MinAllowPrice=2400000,MaxAllowPrice=3600000 WHERE ScripId = 5558
update SCRIP_STAT_CURR set ClosePrice=3000000 where ScripId=5558

Ceiling Depth should be 10% of issued share.( Ex. 10000 for 100,000)

To check How many user login today

select * from INT_LOGIN where datediff(dd,LastLoginTime,getdate())=0

To check How many User login in last N minute
For e.g N =30

select * from INT_LOGIN where datediff(mi,LastLoginTime,getdate()) <=30

When any user is abnormal close window and next time try to login
Message will come “You are already logged in at another terminal”
Then run following query
Here Enter appropriate Broker code and Login Name
UPDATE INT_LOGIN SET LoginStatus = 'A' WHERE BrokerCd = 'BBS' AND LoginName='testing4';



Do Normal BOD



(6)
If in any case BBO Details is not display then run following query

(i) first delete record from BBO_BUY_TRAN
delete from BBO_BUY_TRAN
(ii) Then insert using following sql.


INSERT INTO BBO_BUY_TRAN
SELECT ScripId,XactType,Price,TotalQty,BrokerCd,TraderCd,'U'
FROM BUY_TRAN




Aftert 15th July 15, 2009 BBS testing database dump is
/Sybase/dump/bbs_final.dmp

if some problem occur in bbs_sucess.dmp then load this database dump




DELETE FROM TRADE_TRAN
DELETE FROM BBS_PRICE
DELETE FROM BUY_TRAN
DELETE FROM BBO_BUY_TRAN
DELETE FROM INT_ORDER_MAP


SELECT * FROM TRADE_TRAN
SELECT * FROM BBS_PRICE
SELECT * FROM BUY_TRAN
SELECT * FROM BBO_BUY_TRAN
SELECT * FROM INT_ORDER_MAP


SELECT * FROM BUY_TRAN WHERE ScripId = 5558

SELECT * FROM INT_LOGIN WHERE BrokerCd='SALTA' AND LoginName = 'krishnaa'
UPDATE INT_LOGIN SET LoginStatus = 'A' WHERE BrokerCd = 'SALTA' AND LoginName = 'krishnaa'


SELECT * FROM INT_LOGIN WHERE LoginName IN (SELECT LoginName FROM INT_REGISTRATION WHERE BrokerCd = 'BBS')
AND BrokerCd='BBS'


SELECT * FROM SCRIP_MAST WHERE ScripId = 5558
UPDATE SCRIP_MAST SET ScripStatus = 'S' WHERE ScripId <> 5558


SELECT * FROM SCRIP_DET WHERE ScripId = 5558
UPDATE SCRIP_DET SET MinAllowPrice=2400000,MaxAllowPrice=3600000 WHERE ScripId = 5558

UPDATE

SELECT * FROM BUY_TRAN WHERE ClientCd NOT IN (SELECT ClientCd FROM INT_LOGIN
WHERE BrokerCd='BBS' AND LoginName NOT IN (SELECT LoginName FROM INT_REGISTRATION WHERE
BrokerCd='BBS'))

SELECT * FROM INT_LOGIN WHERE LoginName LIKE 'atiq%'
SELECT * FROM INT_REGISTRATION WHERE LoginName = 'kajal'

SELECT * FROM INT_LOGIN WHERE BrokerCd = 'BBS' AND LoginName NOT IN (SELECT
LoginName FROM INT_REGISTRATION WHERE BrokerCd = 'BBS')


SELECT * FROM BUY_TRAN

SELECT * FROM BBS_BIDDER

UPDATE INT_LOGIN SET LoginStatus='A' WHERE BrokerCd='BBS' AND ClientCd LIKE '%BBS28'

UPDATE FINAL

UPDATE INT_LOGIN SET LoginStatus = 'A' WHERE BrokerCd = 'BBS' AND LoginName='check123'
SELECT * FROM SCRIP_STAT_CURR WHERE ScripId=5558

UPDATE SCRIP_STAT_CURR SET ClosePrice = 3000000 WHERE ScripId = 5558
UPDATE SCRIP_DET SET MinAllowPrice=2400000,MaxAllowPrice=3600000 WHERE ScripId = 5558

SELECT * FROM SCRIP_DET WHERE ScripId=5558

sp_configure "number of user connections",700


SELECT * FROM SCRIP_DET WHERE ScripId=5558

SELECT * FROM INT_LOGIN WHERE LoginName = 'korban2' AND BrokerCd='BBS'

UPDATE INT_LOGIN SET BrowPass = (SELECT BrowPass FROM INT_LOGIN WHERE LoginName = 'rassel2' AND BrokerCd='BBS')
WHERE LoginName = 'korban2' AND BrokerCd='BBS'

SELECT * FROM INT_LOGIN WHERE BrokerCd='SALTA' AND LoginName


SELECT * FROM INT_ORDER_MAP

SELECT * FROM BBO_BUY_TRAN

SELECT * FROM TRADE_TRAN

SELECT * FROM BBS_PRICE

SELECT * FROM BUY_TRAN WHERE Price = -2590000

VIEWS

CREATE VIEW BBS_BIDDER_TRADE AS
SELECT INT_REGISTRATION.LoginName,INT_REGISTRATION.ClientLName,
INT_REGISTRATION.Address1,INT_REGISTRATION.Address2,INT_REGISTRATION.Telephone,
TRADE_TRAN.BClientCd,INT_REGISTRATION.BoId,BROKINVLMT_TRAN.GrossLmt,
(BROKINVLMT_TRAN.GrossLmt/convert(INT,SYS_CONFIG_PARS.ParamValue) ) DepositeLmt,
BROKINVLMT_TRAN.CurrGrossLmt,
INT_REGISTRATION.BankAcctNo,INT_REGISTRATION.ClrBankCd,INT_REGISTRATION.ClrBankName,
SCRIP_MAST.ScripName,
TRADE_TRAN.Price,TRADE_TRAN.Qty,TRADE_TRAN.ScripId, TRADE_TRAN.DtTime,TRADE_TRAN.TradeId
,TRADE_TRAN.BXactId
FROM INT_REGISTRATION,TRADE_TRAN,INT_LOGIN,BROKINVLMT_TRAN,SCRIP_MAST,SYS_CONFIG_PARS
WHERE INT_REGISTRATION.LoginName = INT_LOGIN.LoginName AND
INT_LOGIN.ClientCd = TRADE_TRAN.BClientCd AND
INT_LOGIN.BrokerCd = TRADE_TRAN.BBrokerCd AND
INT_LOGIN.TraderCd = TRADE_TRAN.BTraderCd AND
BROKINVLMT_TRAN.BrokerCd = TRADE_TRAN.BBrokerCd AND
BROKINVLMT_TRAN.TraderCd = TRADE_TRAN.BTraderCd AND
BROKINVLMT_TRAN.ClientCd = TRADE_TRAN.BClientCd AND
TRADE_TRAN.ScripId = SCRIP_MAST.ScripId AND
SYS_CONFIG_PARS.ParamMnemonic = 'BBS_GROSS_MULT'




CREATE VIEW BBS_BIDDER AS
SELECT INT_REGISTRATION.LoginName,INT_REGISTRATION.ClientLName,
INT_REGISTRATION.Address1,INT_REGISTRATION.Address2,INT_REGISTRATION.Telephone,
BUY_TRAN.ClientCd,INT_REGISTRATION.BoId,BROKINVLMT_TRAN.GrossLmt,
(BROKINVLMT_TRAN.GrossLmt/convert(INT,SYS_CONFIG_PARS.ParamValue) ) DepositeLmt,
INT_REGISTRATION.BankAcctNo,INT_REGISTRATION.ClrBankCd,INT_REGISTRATION.ClrBankName,
SCRIP_MAST.ScripName,
BUY_TRAN.Price,BUY_TRAN.TotalQty,
BUY_TRAN.ScripId,BUY_TRAN.DtTime,BUY_TRAN.XactId
FROM INT_REGISTRATION,BUY_TRAN,INT_LOGIN,BROKINVLMT_TRAN,SCRIP_MAST,SYS_CONFIG_PARS
WHERE INT_REGISTRATION.LoginName = INT_LOGIN.LoginName AND
INT_LOGIN.ClientCd = BUY_TRAN.ClientCd AND
INT_LOGIN.BrokerCd = BUY_TRAN.BrokerCd AND
INT_LOGIN.TraderCd = BUY_TRAN.TraderCd AND
BROKINVLMT_TRAN.BrokerCd = BUY_TRAN.BrokerCd AND
BROKINVLMT_TRAN.TraderCd = BUY_TRAN.TraderCd AND
BROKINVLMT_TRAN.ClientCd = BUY_TRAN.ClientCd AND
BUY_TRAN.ScripId = SCRIP_MAST.ScripId AND
SYS_CONFIG_PARS.ParamMnemonic = 'BBS_GROSS_MULT'






SERVERS
/sybase/dump >display

======================================================================
No Server Name No of Copies
=====================================================================
1 ACserver 2
2 AMserver 0
3 AUserver 0
4 BCTserver 1
5 BCserver 1
6 BOserver 0
7 CPTserver 0
8 CPserver 0
9 CSserver 3
10 DOserver 0
11 DQserver 1
12 DRSserver 0
13 FIserver 0
14 FTserver 0
17 GQserver 2
16 HMserver 5
17 HSserver 1
18 IHserver 0
19 IXserver 1
20 LMserver 1
21 NDserver 0
22 OLserver 0
23 OMserver 0
24 OPserver 0
25 OSWserver 2
26 OSserver 4
27 PAserver 1
28 POSWserver 1
29 POSserver 1
30 PSserver 0
31 QMserver 0
32 QSserver 0
33 SLserver 0
34 SMSserver 0
35 SMserver 1
36 SPserver 0
37 TSserver 1
38 UMSserver 1
39 UMserver 1
=========================================================================
Total Number Of Servers Running Are : 30




---------------Some Query------------------------------------------------------------------------
->
select LoginName,BrokerCd,TraderCd,ClientCd,LoginStatus from INT_LOGIN where BrokerCd='BBS' order by LoginStatus

->
select ScripId,BrokerCd,TraderCd,ClientCd from BUY_TRAN order by ClientCd;

->
select LoginName,ClientCd from INT_LOGIN where ClientCd in (select ClientCd from ORDER_ADD_LOG);
->Clients who placed order
select LoginName,ClientCd from INT_LOGIN where ClientCd in (select ClientCd from BUY_TRAN)
->
UPDATE INT_LOGIN SET LoginStatus = 'A' WHERE BrokerCd = 'BBS' AND LoginName = 'SHAFIQB6'


?
UPDATE SCRIP_DET SET MinAllowPrice=2400000,MaxAllowPrice=3600000 WHERE ScripId = 5558
update SCRIP_STAT_CURR set ClosePrice=3000000 where ScripId=5558

? [Client –wise Total Bid qty]
select ClientCd,sum(TotalQty),sum(PendQty) from BUY_TRAN group by ClientCd order by 1

? [Clients who didn’t place order]
select LoginName,ClientCd from INT_LOGIN where ClientCd not in (select ClientCd from BUY_TRAN) and ClientCd not like'%BBS
%' order by 2













Exception

(26 rows affected)
1> update BUY_TRAN set TotalQty=10000,PendQty=10000 where ClientCd like '%BS11';
1> go
1> update BUY_TRAN set TotalQty=10000,PendQty=10000 where ClientCd like '%BS35';
1> update BUY_TRAN set TotalQty=10000,PendQty=10000 where ClientCd like '%BS84';
1> select ClientCd,sum(TotalQty),sum(PendQty) from BUY_TRAN group by ClientCd order by 1
2> go



Selecting The client who are not BBS and BrokerCd=BBS
select LoginName,ClientCd from INT_LOGIN where BrokerCd='BBS' and ClientCd not like ('%BBS%') order by 2

Selecting The client whose BrokerCd=BBS and TraderCd=BBS1
select BrokerCd,TraderCd,LoginName,ClientCd,UserStatus,LoginStatus from INT_LOGIN where BrokerCd='BBS' and TraderCd='BBS1' order by 3


If Tic
update SCRIP_MAST set TickPrice=2500 where ScripId=5559


UPDATE SCRIP_DET SET MinAllowPrice=2400000, MaxAllowPrice=3600000 WHERE ScripId = 5558
update SCRIP_STAT_CURR set ClosePrice=3000000 where ScripId=5558