Sql Queries

Start mysql --user=om_user --password --protocol=tcp --host=dbapp pdh_r5_perf User and account info select u.user_login_id,a.account_id,u.user_id from ac_user u, am_person p,am_account a where u.user_id=p.user_id and p.default_account_id = a.account_id and a.account_number is not null; select u.user_login_id from ac_user u,am_person p,am_account a where u.user_id=p.user_id and p.default_account_id=a.account_id and a.account_number is not null; select u.user_login_id, u.user_id,a.account_number from ac_user u, am_person p, am_account a where u.user_id = p.user_id and p.default_account_id = a.account_id and a.account_number is not null; select u.user_login_id from ac_user u, am_person p,am_account a where u.user_id=p.user_id and p.default_account_id=a.account_id and a.account_number is not null; select * from ac_user where user_login_id like 'nithya_010'; Symbol info select i.symbol from sm_instrument i ,sm_pricing p where i.instrument_id=p.instrument_id and p.BID_PRICE is not null and p.ASK_PRICE is not null and p.OPEN_PRICE is not null and p.CLOSE_PRICE is not null and p.52WEEK_HIGH is not null and p.52WEEK_LOW is not null Order by i.symbol select i.symbol,i.instrument_type from sm_instrument i ,sm_pricing p where i.instrument_id=p.instrument_id and i.instrument_type like 'Equity' and p.BID_PRICE is not null and p.ASK_PRICE is not null and p.OPEN_PRICE is not null and p.CLOSE_PRICE is not null Order by i.symbol select i.symbol,i.instrument_type, i.status from sm_instrument i ,sm_pricing p where i.instrument_id=p.instrument_id and i.instrument_type like 'Equity' and p.BID_PRICE is not null and p.ASK_PRICE is not null and p.OPEN_PRICE is not null and p.CLOSE_PRICE is not null and p.BID_PRICE!=0 and p.ASK_PRICE!=0 and i.status='Active' and p.OPEN_PRICE!=0 and p.CLOSE_PRICE!=0 and i.symbol not like '% %' group by i.symbol having count(i.symbol)=1 select i.symbol,i.instrument_type, i.status,o.expiration_date from sm_instrument i ,sm_pricing p, sm_option o where i.instrument_id=p.instrument_id and p.instrument_id=o.instrument_id and i.instrument_type like 'Option' and p.BID_PRICE is not null and p.ASK_PRICE is not null and p.OPEN_PRICE is not null and p.CLOSE_PRICE is not null and p.BID_PRICE!=0 and p.ASK_PRICE!=0 and i.status='Active' and p.OPEN_PRICE!=0 and p.CLOSE_PRICE!=0 and i.symbol not like '% %' and o.expiration_date < curdate() group by i.symbol having count(i.symbol)=1; Eric's Tunnel ./nsshdbback1.sh Order info select u.user_login_id,o.QUANTITY,o.order_status_code from om_order o, ac_user u where u.user_id=o.account_id;

!./

select u.user_login_id, count(o.order_id) from om_order o, am_person p, ac_user u where o.account_id = p.default_account_id and p.user_id = u.user_id and u.user_login_id like 'ooo-001%' group by u.user_id select u.user_login_id, count(o.order_id) from om_order o, am_person p, ac_user u where o.account_id = p.default_account_id and p.user_id = u.user_id and u.user_login_id like 'nith-001%' and date(o.create_date)='2008-08-17' group by u.user_id desc select u.user_login_id, o.account_id,count(o.order_id) from om_order o, am_person p, ac_user u where o.account_id = p.default_account_id and p.user_id = u.user_id and ( u.user_login_id like 'ooo-002%' or u.user_login_id like 'ooo-003%') and o.create_date > '2008-08-19 14:40:08' group by u.user_login_id desc order by count(o.order_id) desc

select count(o.order_id), date_format(o.create_date, '%Y-%m-%d %H:%i') as cdate from om_order o, am_person p, ac_user u where o.account_id = p.default_account_id and p.user_id = u.user_id and ( u.user_login_id like 'ooo-006%' or u.user_login_id like 'ooo-007%') and o.create_date > '2008-08-20 15:59:00' group by cdate MultiLegged Orders SELECT DISTINCT I.SYMBOL optionSymbol,I.INSTRUMENT_ID, E.EXCHANGE_CODE exchange, SOR.ADJUSTED_OPTION_FLAG, O.OPRA_ROOT root, O.STRIKE_PRICE strikePrice, DATE_FORMAT(O.EXPIRATION_DATE, "%m") month,DATE_FORMAT(O.EXPIRATION_DATE, "%Y") year, OPTION_TYPE , DATEDIFF(O.`EXPIRATION_DATE`,CURRENT_DATE()) AS daysToExpire, (SELECT (SP.BID_PRICE+SP.ASK_PRICE)/2 FROM SM_PRICING SP WHERE SP.INSTRUMENT_ID=I.INSTRUMENT_ID ORDER BY LAST_TRADE_DATE DESC LIMIT 1)AS closingMarkPrice, E.EXCHANGE_TYPE_CODE, SOR.MULTIPLIER , SOR.SHARES_PER_CONTRACT , I.REUTERS_INSTRUMENT_CODE FROM SM_INSTRUMENT I LEFT JOIN SM_OPTION O ON (I.INSTRUMENT_ID = O.INSTRUMENT_ID ) LEFT JOIN SM_OPTION_ROOT SOR ON (SOR.OPRA_ROOT=O.OPRA_ROOT) LEFT JOIN SM_INSTRUMENT UI ON (UI.INSTRUMENT_ID = SOR.UNDERLYING_INSTRUMENT_ID ), LK_EXCHANGE E WHERE I.EXCHANGE_CODE = E.EXCHANGE_CODE AND I.STATUS='Active' AND I.INSTRUMENT_TYPE ='Option' AND SOR.ADJUSTED_OPTION_FLAG = false AND UI.SYMBOL in ('A','AA','AET','AAI','ONNN','QCOM','AAP','AAPL','ATI','AAV','AB','ABB','ABC','ABD','ABH','ABI','VOX','ABK' ,'ABM','ABP','ADVNB','ABT','ABV','ABX','ACC','ACE','ACF','ACH','ACI','ACL','ACM','ACN','ACO','ACS','AC V','ACW','ADI','ADM','ADP','ADSK','ADS','AACC','APA','AEA','AEE','AEG','AEL','AEM','AEO','AEP','ADBE',' AER','AES','AEZ','AF','AFG','AGU','AFL','AFN','AKRX','FORM','ARLP','AG','AMCC','AGG','AGN','AGO','AG P','ARCC','AHG','AMAT','AHL','AHR','AHS','AHT','AU','AIG','AIR','AIV','CTV','AIZ','AJG','CYBS','EWZ','AMD

','ANAD','AKS','ALB','ALD','ALE','ALJ','ALK','ALL','ALO','ALU','ALV','ALY','AM','AME','AMG','AMN','AMP','A MGN','AMR','AMT','LTON','AN','ANF','ANH','MVV','ANN','ANR','GKK','ANW','AOB','AOC','TWX','AOI','ATLS ','ARMH','AOS','WB','APC','APD','APH','APL','APP','ADPT','APU','ANDE','ARRS','HHGP','AUTH','AVID','A RQL','AMLN','ARGN','APPX','AHGP','ATML','ATVI','DSCO','ALNY','ACAD','HSII','ARA','ARD','ARE','ARG',' ARM','ARO','ARTC','ARW','ASA','ASF','ASH','ATMI','ATG','ATK','ATN','ATO','ASFI','ATU','ATV','ATW','AGIX' ,'RDWR','CRDN','LKQX','ANPI','AUO','ARRY','AUS','UCBH','AUX','AUY','AVA','AVB','AVD','AMAG','AVP','A VR','AVT','JAVA','AVX','AVY','AW','AWH','AWI','AWK','PARL','AXA','AXE','AXL','AXO','AXP','ISRG','AXS','A YE','AYI','AKNS','AYR','TSS','AZN','AZO','KNXA','AZZ','B','BA','BAC','BPX','BAM','BAS','BAX','BBD','BBG',' BBH','BBI','BBT','BBV','BBW','BBX','BBY','BC','BCE','BCO','RIMG','BCR','BCS','ABTL','BSC','BDC','BDE',' BDH','BDK','BDN','BIDU','BLK','BDX','BE','BEC','BEE','BEN','DENN','PNSN','BEZ','ATCO','BG','BGC','BGG ','BGP','BHE','WFC','BHI','PXP','BLC','BHP','BBBY','BHS','UHAL','IYR','BID','BIG','BKX','MBI','BIK','BIL','BI O','BIP','BCRX','BIV','BJ','SOX','BRLI','BJS','ISSC','BK','BKC','BKD','BKH','BKI','OCNW','BKS','BLG','BLL',' UBB','BORL','BLT','BLV','BMC','BMI','XLB','ULBI','BMR','BMS','SMRT','BMY','BNT','BND','BNE','BNI','RMB S','BOH','VASC','BP','BPAX','BPL','SPWR','BPO','BRNC','BPT','BBBB','BRCD','KNOT','DIA','BRKS','BLTI',' BWTR','BQI','BBEP','EZCH','DBTK','BWLD','BEAV','BR','BRC','BRE','BRL','BRO','BRP','BRS','BRY','BSDM ','MNST','BSV','BSX','BSZ','BTE','BTH','BTU','JPM','BUD','BUF','BVF','BVN','BVZ','BW','BWA','BWK','BWP' ,'IPGP','BWS','TOMO','BX','BXC','BXG','RKH','BXP','BXS','X','BYD','BYI','LYTS','BYT','PSYS','BZH','BZP',' C','CA','CAB','CAG','CAH','CAI','CAL','CAM','CAO','CAR','CAS','CAT','CB','CBB','CBE','CBG','CBI','CBK','C BL','CBM','CBRL','CBR','CBS','CBT','CBY','CBZ','CC','CCC','CCE','CCI','CCJ','CCK','CCL','CCO','CMCSA', 'CCU','VMC','CDD','CDE','CDL','CMI','CDNS','CDS','CMG','CE','CEA','CEC','CEG','CEL','CEM','CEO','CEP ','CF','CFC','MICC','CAKE','CFR','CFW','CFX','CLF','CG','CGI','CAMD','CGV','CGW','CGX','CHA','CHB','CH D','CHE','CHH','CHK','CHL','CHS','CHT','CHU','CI','CIB','CIG','RXII','CIM','CIT','TD','CHRW','MCCC','PWE', 'CKH','CROX','CKP','YUK','CKR','CL','CLB','CLI','CLR','CLS','CLX','CM','CMA','CMC','CME','CMN','CMO',' CMP','CMQ','CMS','CN','CNA','CNB','CNC','CNH','CNI','CNK','CNL','CNO','CNP','CNQ','CNS','CNW','CNX' ,'COF','COG','COH','CCOI','COL','COO','COP','COT','COV','CP','CPB','CPE','CPF','CPN','CPO','CPS','CPX ','COSI','CQB','CPNO','CEPH','CNTF','CYMI','CMTL','CAMP','CMCSK','CENX','CBON','CERN','CASY','CQ P','CREE','CMOS','CTDC','AMED','CPLA','CSGP','LCUT','CR','CRA','CRI','CRK','CRL','CRM','CRR','CRS',' CRX','CRY','CRZ','CS','CSC','CSD','CSE','CSH','CSK','CSL','CSR','CSX','CTB','CTC','CY','CTL','CTS','CTX ','ALGN','CUB','CCRT','CMCO','CPKI','CFFN','CRUS','CPWM','CEDC','GRRF','CYCL','CECO','CUZ','CVA', 'CVC','CVD','CVG','CVH','CVI','CVO','CVP','CVS','CVX','CTIC','CW','CPWR','CXG','CXO','CXR','CXW','PA LM','CYD','CYH','CYN','CSCO','CYT','SPY','CZN','CPST','HOLX','CZZ','D','DRYS','DAL','DAN','IDCC','DAR ','DAX','DBA','DBB','DBC','DBD','DBE','DBO','DBP','DBS','DBV','DCI','DCO','DCP','DCQ','DCT','DD','DDE',' DDM','DRRX','DDR','DDS','DIOD','DDX','DE','DEF','DEI','DEO','DES','DF','DFG','DFII','DFX','DFR','DFS','D GL','SGEN','DGT','DUG','DGX','DHI','DHR','DHS','DHT','DIG','DIN','DIS','PICO','IWM','MDY','DJX','DK','DK S','DLB','DLM','DLN','LDK','DELL','DLR','DLX','DMA','PSPT','QID','DVN','DNA','DNB','DND','DNN','DNR','D NZ','DO','DOC','DOG','DON','DOT','PDCO','DOV','DOW','DOX','DPL','DPM','DSPG','DPS','XLE','DPZ','DY AX','DCOM','ALTI','NNDS','RGNC','WBSN','DRIV','IPSU','DMLP','DEPO','DLTR','ACAS','CVGI','DLLR','WR LD','DRC','DRE','DRH','DRI','DRQ','DRS','DSI','DSL','DST','DSW','DSX','DTD','DTE','DTG','WLT','DTN','DB RN','DTV','DTX','SYNT','BLDP','DUK','LIOX','LDIS','DUX','VPHM','DV','DVA','DWSN','DVR','DVY','DWA','D XD','DXL','HYTM','DY','DYN','PTR','AVZA','EAC','MESA','EAS','EAT','EROC','EPX','EPEX','EXBD','ECA','E CL','ED','ONEQ','ELN','EDR','EDS','EDU','EDV','EEB','EEE','EEM','EEP','EEQ','CENT','EPAY','EEV','EUI',' EFA','EFH','EFII','EFU','EFV','EFX','EPI','EGN','EGO','EGP','EGT','EGY','OSX','HELE','EWY','EIG','EGOV',' EIX','EJ','WIRE','EWW','EK','EXM','XRE','EL','ELP','ELS','ELX','ELY','EMC','EME','EMG','EMN','EMR','EMV ','ENB','ENG','ENH','ENI','ESI','ENP','ENR','ENS','BGCP','ENZ','EOG','EWBC','MSTR','VWO','EP','EPB','E PD','EPE','EPL','EPP','ENER','EPR','EFJI','EQ','SAIA','EMKR','EXAR','SLGN','RJET','XTEX','ENOC','OES X','NICE','RAME','EMAG','ESIO','EQR','EQT','ENWV','ESLT','EQY','REXX','ERF','ERJ','SEPR','VXF','ES',' ESE','ESL','ESS','ESV','ETE','ETH','ETM','ETN','ETP','ETR','LNET','EPIC','ASIA','ELON','TCLP','CIEN','ET FC','EXTR','JUPM','EV','ANEN','EVC','IESC','CETV','EVX','EW','EWA','EWC','EWD','EWG','EWH','EWJ','E WL','EWM','EWP','EWT','EWU','EXB','EXC','XME','EXH','EXP','EXQ','EXR','EYE','UNFI','SAW','EZA','ERT S','EZU','F','FAF','FXI','MFX','FBC','FBN','DSTI','FBR','FBT','SONE','FCF','FCG','FCH','FCL','FCN','FCS','E PIX','FCX','FDG','FDL','FDM','FDN','FDO','FDP','ABFS','FDS','FDX','FE','FED','FLR','SBNY','FEZ','FFH','FX B','LFC','FLIR','FXA','FXC','FGP','FMER','AMMD','FRX','FHN','FXP','PACW','FIC','FIG','FII','AFFX','FIS','FI W','FIX','FIZZ','FULT','FRO','VSE','KCAP','SIGA','FL','FLE','FFIV','FXE','FLO','FFIN','FLS','FLML','FMC','FM D','WFMI','FRED','FNF','FRE','FNI','FNM','FO','FOE','TMTA','CISG','FOR','FPL','FPX','FAST','FTBK','FSTR' ,'FEIC','FTEK','FCEL','CLMT','ARTG','CACH','MMLP','FSIN','FCFS','FNSR','PARD','EQIX','EPIQ','FISV','FL OW','FWRD','FEED','FRC','FRG','FRN','FRP','XIDE','FRZ','FCSX','FSP','FSS','FST','FTD','FTI','FTK','FTO',

'FITB','CSUN','FOSL','SCRX','TZIX','UTHR','FUL','FUM','GTIV','MFLX','FVE','FVX','TOPS','SPSN','FXD','F XF','FXG','FXL','FXM','FXN','FXS','FXU','FXY','FXZ','FCTR','HRLY','CVLT','G','GA','GENZ','GAF','SWKS','G AP','GAS','GB','GDX','ABAT','GIGM','GBX','GCA','GCC','GCI','WCG','GCO','GLD','GD','GDI','GSG','GDP',' GILD','GE','GEF','GEL','GEO','GES','GET','GFA','GFF','GFG','GFI','LFUS','GG','GGB','GGC','GOOG','GGG ','GGP','GGR','GHL','GHS','OSIP','GII','GIL','GIS','GKD','GKE','VGK','GLF','GLG','GLS','GLT','GLW','GM','G ME','GMF','GMM','GMO','IMOS','GMR','GMT','IMGN','GNA','GNK','GNW','GS','GOL','GOX','WM','GPC','GP I','GPN','GPS','CSIQ','GLBC','GERN','GAIA','GFIG','GTXI','GIFI','GRMN','GHDX','GLBL','GPOR','GYMB','G LRE','IDIX','GR','GRA','GRS','GRZ','GSH','GSI','GSK','MYGN','GSS','GSX','MA','GT','GTE','GTI','GU','PGN X','GOLD','GMXR','GBCI','WGOV','GVA','DECK','GW','MVSN','RIG','ESEA','GWR','GWW','GWX','GTOP',' GXC','USL','GXP','GNTX','GY','GYI','ICGE','NOV','ZGEN','HA','HAE','HAI','HAL','HAR','HAS','OMTR','HRC' ,'HBC','HBI','BUCY','ABII','HCC','HCN','HCP','HILL','HD','HDB','HE','HEM','HUBG','HES','HEW','HIFN','MIV A','VMW','HGX','HGR','HGT','PMP','HHD','HHH','HHJ','HHO','HIG','HERO','APSG','HIW','HI','FSLR','HK','H KG','PMTI','HKT','ATPG','HL','HLF','HLS','HLX','HMA','HME','HMX','HMY','HNI','HNP','HNR','HNT','HNZ','H OC','HOG','HUI','HON','HOS','HOT','HOV','HP','HPC','HPQ','HPT','HPY','HBAN','LHCG','CNSL','HSIC','HO KU','HGSI','GSIG','HAYZ','HALO','TXRH','HAYN','CHTT','UEIC','VOLC','HRB','HRH','HRL','HRP','COHR',' HRS','HRZ','HS','HSC','HSP','EEFT','HST','DISH','HSX','HSY','HTE','HTH','HTX','HTZ','HLEX','LECO','HU M','HUN','PFCB','HLTH','TRID','HVY','HW','GVHR','HXL','RCCC','VTAL','HYC','HYG','SNP','HZO','IAG','IAI' ,'IJH','SIAL','IAR','IAT','IBB','IBI','XAU','IBM','IBN','ABMD','IWB','ICE','ICF','ICO','ACCL','ID','IDA','ESMK','IW D','BIIB','IDU','UTH','IEF','IJT','IEV','IEX','ILF','IFF','ACTG','IGE','IGM','IGN','IGT','IGW','PWR','IYY','IHP','M TXX','IIG','INAP','IIX','IJK','IJJ','IJR','IJS','IKN','IVAC','ILA','IVV','ILE','TLT','IWV','IM','IMA','IMB','IMM','IMN','I MO','IMAX','IMMR','MI','IN','ING','INTC','INT','IO','IWF','IOC','CHRD','IOM','IOO','ITU','INSP','OIX','IP','IPE',' IPG','IPI','INCY','IGV','ILMN','ASMI','IIIN','ISBC','PRGO','VSAT','INTU','ECLP','ITMN','IR','IRC','IRF','OOG','I RM','IRBT','ARBA','IRX','INSU','IT','ITB','ITC','ITE','ITF','ITG','IDTI','ITT','IWN','ITW','VARI','ISSI','ENDP','FI NL','INFY','IDSY','ITRI','ASGN','IVGN','IWOV','INGN','IVE','IVN','SIGI','IVW','IVZ','IWA','IWC','IWO','IWP','F XEN','IWR','IWS','IAH','IXC','IXG','IXJ','IXN','IXP','IXX','IXZ','IYC','IYE','IYF','IYG','IYH','IYJ','IYK','IYM','SWI R','USO','IYT','BCSI','IYW','IYZ','SSRI','JAH','NILE','JCP','LBTYK','JAS','JBL','JBX','SFC','JCG','JCI','CNQR ','JEC','JEF','XSP','RNIN','JPP','JBLU','IRIS','JBHT','IIVI','EVVV','SSCC','JKHY','JLL','JLO','SSO','TWTC',' MER','QLD','JNJ','JNK','JNS','JNY','JOE','JASO','ISPH','SPW','ITWO','INWK','PDGI','JCOM','JRJC','JRCC', 'CLDA','SBGI','FSYS','SNTA','JOYG','JRN','JSC','VTI','JTX','LBTYA','RECN','SMH','JNPR','OIH','LCAV','E MM','JWN','SPX','SDY','STMP','CRXL','XES','K','TNP','JMBA','VSEA','TSRA','TER','VGR','KCE','SMTC','K BE','KBH','NCR','AATI','SAY','BLUS','ASYT','ZRAN','KBR','ASEI','SWK','KBW','URS','KRX','MOS','KCI','NA TI','ACOR','KCP','KLAC','OTEX','CELG','KDE','KDN','NAFC','TRW','SHLD','UNTD','NTRI','KEG','KEM','CH KP','PTV','NVDA','KEX','KEY','ZEUS','XEO','VTIV','TWB','KFN','KFT','NDAQ','KFY','KG','KGC','NOBL','CH RS','VCLK','SSP','KHD','XHB','SKX','DAKT','HCBK','MAA','SAI','PPC','KIE','KIM','WMS','WBMD','VRNM','N XY','PKD','COGT','PENN','LULU','ALTH','KKD','OC','AMSG','ALTR','KLD','TITN','FMCN','NUVA','TXT','VND A','KMB','TRA','TYC','KMP','KMR','MS','KMT','PMC','TT','KMX','MOH','NSTK','KND','NTGR','SVVS','XRAY', 'SOHU','KNL','KNX','KO','HITT','UBS','TIVO','MPEL','KOL','KOP','DDUP','KNDL','RRC','PAY','SSW','SWC',' TKLC','CNMD','CPRT','SONO','KOPN','CRAY','KLIC','MATK','VRGY','SPNC','KR','KRE','RCNI','KRY','LFG' ,'KSS','KSU','BONT','PBY','SNCR','LNY','CPTS','EXEL','BKUNA','BEBE','NS','XTO','EMIS','AGII','AVCT','Q SII','KWK','RRD','CWST','KWT','NTRS','OSG','MERX','TTEC','ASML','LAB','DPTR','RRGB','WSO','TIBX','P SUN','LAZ','LAVA','RPM','OREX','LBY','CEGE','LCC','OXPS','POT','LDG','UDR','LDN','ARNA','LEA','LEE',' LEG','LEH','LEN','SLXP','RSYS','LF','NBIX','ISIS','SWIM','LFL','LIFC','SPF','SVU','PHG','CMRG','LFT','CBS T','SA','V','TSO','PNCL','LGF','THRX','PNRA','ACGY','VMED','MIR','LH','IART','RAS','AUDC','PSEM','TDC', 'LHO','MDU','PTIE','LVS','PGR','MINI','SCMR','MANH','WRSP','JDSU','LINTA','MLM','RYN','LII','TEN','NCX' ,'ENCY','LIV','SYNA','LIZ','COGO','AERG','SYY','COLM','CYBX','ACMR','NYT','MCHP','SPIL','ZOLT','LAM R','CERS','RGC','OSTK','SRDX','PM','PWAV','OPWV','NETL','AUXL','UFS','NAL','SINA','LLL','LLTC','COR S','MT','WU','LLY','CLWR','LM','BLUD','SIGM','LMC','ICON','ASPM','XLY','LRCX','LMT','OCR','VAR','RACK' ,'LNC','VNO','LNG','BMRN','NLC','LNN','VCI','LVLT','LNT','TMK','LO','HLIT','RTH','LOW','MNTA','LPA','PDS' ,'MFC','EXPE','RIGL','LPX','PTRY','ACTL','LQD','GPRO','LOGI','LOJN','LNCR','NAT','LGND','LANC','LSCC ','SONS','LOOP','RDC','MMR','ADLR','LRY','NURO','LSI','LTD','URBN','SHFL','WIND','LTM','LTR','OIS','XJ T','UAUA','CSTR','OMCL','LUK','KERX','SNUS','ARIA','LUV','LUX','SCON','NMTI','STAA','RATE','MRGE','S WN','PSS','SD','PLAB','MNKD','WCC','RIMM','FWLT','PSSI','TLB','UGI','LMDIA','PH','NUVO','TRLG','TPX',' TASR','TXI','CSGS','LXK','NMX','NVTL','LXU','PCX','SKS','MDC','XING','GSIC','PLCE','LYV','RGLD','NVS',' LZ','XEC','LZB','ESLR','RVBD','OI','ROK','VECO','TCB','M','MAC','MAN','MAR','MAS','MAT','MNX','MCD','M CF','MCK','MCO','MCY','MDP','MDR','MDS','MDT','ME','MEA','MED','MEE','MEG','MEI','POWR','MET','MF', 'MFA','MFE','MCRS','MFN','MON','MGA','MGG','MGI','MGM','MENT','MHK','MHO','MHP','MLHR','MHS','MI

C','MID','MIG','MIL','MCRL','MGLN','MKC','MLI','MMC','MMM','MMP','MMS','MDTL','MNC','MNG','MNI','MN T','MO','MOO','MOT','MOV','MPG','MPS','MPW','ELNK','LOCM','MSFT','MSCS','SIMO','MGPI','MDCO','CN IC','MAXY','MASI','MRVC','MR','MRH','MRK','MRN','MRO','MRX','MSA','MSM','MSO','MTB','MTD','MTG','M TH','MTN','MTW','MTX','MTZ','MU','MUR','MV','MVL','MVR','MW','MWA','MWE','MEDX','MWV','MWY','MXB ','MDRX','SMS','MYL','MYP','MYY','MZZ','N','NAK','NOK','NBL','NBR','NBI','NVAX','NX','NCC','NCI','NCS',' NCT','NDX','NDN','NGLS','NE','NED','NEM','NEU','LCAPA','NFG','NFO','NFP','NFS','NFX','NG','NTES','NG S','NHP','REDF','NI','NABI','SDA','NKE','NOVL','NL','NVLS','NLS','NUE','NLY','NM','NNI','NNN','NOA','NOC' ,'NOG','NOOF','NP','NPD','NPO','NOVN','NDSN','NGPC','NHWK','CTAS','NGAS','NCTY','ININ','NR','NRF',' NRG','NRP','NSC','NSH','NSM','NSR','NST','NANX','NT','NTE','NTG','NTY','NU','PRXI','NTAP','NUS','NVT', 'NWA','NWE','NWL','NWN','NWS','NWY','NXG','NXT','NY','NYB','NYC','NYX','PTEN','NEWP','O','RCL','RH D','SIRI','PAYX','ALXN','APOL','SO','OSUR','OMC','IDXX','JAKK','SYMC','POZN','APCC','PRAA','XEL','OC N','TBI','SNDA','STZ','PDLI','PPL','ODP','WFT','TCO','XOM','OEX','OEH','AEIS','UTSI','HNSN','ZMH','PAL',' SMMX','USU','IMCL','OGE','WMG','PPCO','TEG','PWER','SIL','TSN','SNV','RHT','RTN','VSH','PMCS','TAP' ,'OII','SYK','ZION','ONXX','PCAR','PBCT','ELOS','CHTR','ZLC','SPPI','TELK','OKE','ORBK','OKS','CTXS',' TRMS','TSM','STT','RFMD','OLN','RES','ADCT','VRTX','PPDI','OMG','AMSC','OMI','PNW','AKAM','OMX','A MKR','WY','ONB','RYL','VVTV','SANM','HEPH','THC','WAG','QQQQ','NFLX','PSTI','RF','PEIX','RX','PRX','P LUG','PGN','PHM','COST','UPS','PHTN','SIX','OTTR','PPG','WOOF','OPMR','ONAV','AGYS','RDN','ENZN', 'OSTE','ORLY','TECD','TEVA','BIOS','SPLS','ORA','ORB','XLP','DNDN','ORH','ORI','ORCL','SRP','XLU','U RI','SMG','IMH','PCYC','OSK','WLP','TTWO','NITE','OTP','THOR','SCI','PHH','TOL','TWC','SEE','FDRY','SI VB','SFD','PKI','AMZN','ABAX','SPG','VRSN','UNM','WCI','SGR','KNSY','PLCM','RAI','SNDK','WAT','OWW', 'TRV','MELI','TLAB','SU','TK','TLCV','OXM','SEIC','MOLX','OXY','FLEX','SNY','EBAY','PPH','PRU','UN','VR X','UPL','MRVL','OZM','XMSR','VC','PAA','PAG','PMTC','PBE','PBG','PBH','PBI','PBR','PBS','PBT','BJRI','P BW','PCG','PCH','PCL','PCP','PCR','PCS','PCU','PCZ','PDC','PDE','PDP','PDQ','PDX','PEC','PEG','PEI','P EJ','PEP','OPLK','PER','PEZ','PFE','PFG','PFI','PFM','PFP','PFS','STP','PG','PGH','PGI','PGJ','PFGC','PGZ ','PHJ','PHO','PETD','WPPGY','PHW','PIC','PID','PII','PIN','PIO','PIR','PLXT','PIV','PJB','PJC','PJF','PJM','P JP','PKB','PKE','PKG','PDII','PL','PLA','PLD','PLL','PLT','PMI','TRMP','PMR','PNC','PCLN','PNK','PNM','NP BC','PNR','PNX','POL','POM','POR','POW','PPA','PPD','PPS','PQ','PANL','PBKS','PCBC','PLNR','PRST','IP HS','PRSP','PSEC','VICL','PVTB','PRE','PRF','PRN','PSA','PSD','PSI','PSJ','PSP','PTF','PTH','PTI','PTJ','P TP','TWGP','PUF','PUI','LPNT','PUW','PVA','PVH','PVM','PVR','PVX','PWB','PWC','PWJ','UWM','PWO','PW T','PWV','PWY','PX','PXD','PXE','PXH','PXI','PXJ','PXN','PYZ','PZD','PZE','PZI','PZJ','Q','ALXA','SKIL','JDA S','ACLI','ALKS','AMRI','NARA','MCHX','MBRK','BBND','BEXP','TRGL','BPHX','CBAK','IBKR','BECN','ARU N','UEPS','BABY','BBOX','ALVR','CKXE','BOOM','TSFG','CGNX','COHU','CCRN','POOL','CTRP','CUTR',' CMED','CRBC','ADRA','DCGN','WRC','ADRE','FLDR','BLKB','CVBF','ASTSF','DGIT','GSAT','DTLK','DIVX', 'DVAX','AAUK','VDE','REFR','LGCY','TGIC','FARO','EGLE','EVEP','ORCT','ISLE','PRKR','SYMM','TESO',' CYTR','APKT','FBTX','WTFC','KFRC','FREE','AFSI','SOLF','AVAN','SLAB','CLNE','VDSI','FTWR','TRMA',' UCTT','SFLY','FRPT','PGIC','ORCC','NRGY','SPTN','LINE','GLNG','GMCR','MALL','LACO','GROW','NYNY ','CYPB','BIDZ','HSTX','VLCCF','HMIN','THQI','HTLD','AGEN','HANS','CPLP','NIHD','HMSY','CPHD','ISLN' ,'AIRV','IFLO','NAVR','NINE','MCRI','INFN','MIPS','ICLR','SIRF','TWTI','OSIR','APOG','JSDA','ROLL','VISN' ,'ATHN','SQNM','VVUS','TINY','ANTP','SBLK','NPSP','CALM','KONG','ALDN','CNET','QLGC','PAET','SDX C','VLY','SMCI','CRNT','MDVN','MGAM','HWAY','EMMS','KALU','MCGC','MORN','MSCC','MXWL','LLNW',' VNBC','DAVE','SNIC','NANO','NEXC','NSTR','CYNO','DTPI','NSIT','NKTR','OMNI','NEOG','OMEX','OPXT', 'PWRD','TXCO','CRZO','LWSN','CLHB','ICUI','PLLL','GIVN','LMRA','PURE','SMSI','MEOH','PTNR','AINV',' UMPQ','MHGC','ODFL','PETM','MPWR','VPRT','MKSI','COMV','WINN','SSYS','HAIN','LJPC','MTSN','SPA R','WRES','HIBB','ATHR','VIMC','RADS','UCBI','REXI','SCOR','RNWK','RSTI','RYAAY','LSTR','QCOR','SIM G','SPSS','SCSS','MOSY','SEED','ALTU','TTES','EXLS','TSCO','IACI','QLTI','ULTA','STAR','USTR','CPSL',' TZOO','TSTR','PLXS','QSFT','IDEV','IMMU','SUNH','ANSS','SMTS','ZUMZ','LONG','WCRX','ASBC','VIGN' ,'ARAY','RNOW','TLEO','WMGI','NTLS','OMPI','QDEL','CFSG','HURN','ISCA','CAVM','XNPT','QGEN','FUQ I','CINF','ACWX','ACWI','QXM','SIRT','APPY','CMGI','AVRX','SSRX','PRGN','MTRX','GCOM','ASTE','GTLS ','CBEY','OXGN','MRCY','SBAC','VLTR','AMCN','DITC','MGRC','IPAS','JOSB','PACR','OCNF','NCMI','GEO Y','R','RAD','RAH','TRIN','RBC','RBI','RBN','RCH','RCI','BRCM','RDY','RE','REG','REP','ROST','RIO','RWX' ,'RFV','PRGS','RGR','RGS','RHI','RIS','RJF','RVSN','RUI','RKT','RL','RTP','ORCH','RMD','RMG','RMN','MM SI','RND','RNR','RNT','ROC','ROG','ROH','ROP','RPV','ADTN','RGEN','ERIC','DEIX','RCII','SPRT','RTIX','S TLD','RAIL','REGN','RMIX','TROW','RRI','RS','ROSE','RSG','RSH','RSO','RSP','RSX','RT','RTI','RUF','ROC K','RUT','RVI','WERN','RVX','RWC','RWR','RWT','RXS','LCRD','NAPS','RY','MTEX','RZ','RZP','S','SAF','SA H','SAM','SBH','SDS','SBS','SCA','SCG','SCHW','SCS','SLB','STRA','SE','SEH','SHOO','SF','SFG','SFI','SF L','SFY','SVO','SGP','SGU','SGY','SH','SHI','SHO','SHW','SHX','SHY','SID','SII','SIN','SJM','SJR','SJT','SST

I','SKF','SKT','SLE','SLF','SLG','SQY','SML','SLM','SLX','SLT','SLW','SM','SMA','SMI','SMN','SMP','SMSC',' SNA','SNH','SNS','SNX','SOA','SOL','SON','SMTL','SOV','SPC','SPH','SPN','SPP','SPR','SPECTRA12','SN HY','WFSL','SAPE','SORL','SORC','CRXX','SQM','SUPX','SCHN','STEC','SAFM','SCHS','SBUX','SRE','SR S','SRT','SNTS','SRX','SRZ','SSD','SSI','SSL','NUAN','STD','STE','STH','STI','STJ','STM','STO','STR','STV' ,'STX','SUF','SUG','ASTI','SUN','SUP','SIFY','SVA','SVR','SWH','SWS','KSWS','SWX','SWY','SXL','SXT','S Y','SYT','SYX','SPSX','T','TAM','TAN','TBA','TBH','TBL','TMB','OVEN','TCK','TCL','TCM','TDG','TDS','TDW', 'TDY','TE','TEF','TEL','TEX','TFI','TMCV','TFX','TG','TGA','TGB','TGI','TGP','TECH','TGT','TLGD','THG','TH I','THO','COMS','THS','TIE','TIF','TIN','TIP','TIV','TJX','TKC','TKR','TLG','TLK','TLM','TLO','TMA','TMO','TM S','TMW','TNB','TNC','TNE','TNH','TNL','TNS','TNX','TNY','TXN','TOT','ACIW','TPP','AMTD','BRKR','STST', 'SDTH','TBSI','NETC','TTEK','TQNT','TCBI','XRTX','TUNE','TAXI','CATS','TWLL','TRE','TRI','TRK','TRN','P ETS','TRR','TRX','TRY','TS','TSB','TSL','TSU','ULTI','TTC','TTH','TTI','TTM','TACT','TRMB','SGMS','TUP','T RAK','TVL','TWI','TWM','TWP','TMRK','SATS','TX','YTEC','TYL','TYX','UA','UFPI','BPFH','UAM','LEAP','UB' ,'CTHR','ALOG','ULTR','CWTR','OVTI','COCO','CHRT','ACTU','ERES','CBLI','UDN','SIRO','UNG','AIRM','C OWN','USMO','SEAC','BRLC','URRE','LNCE','DNEX','ISIL','ROIAK','ENTG','LUFK','CRYP','SHLM','UGA',' SGMO','SHPGY','TSCM','DGII','GILT','UNH','FLWS','SCSC','MOLXA','HOTT','UNP','FMBI','UHS','IUSA','N UCO','CHINA','POWI','SYKE','DISCA','WYNN','UIS','UBSI','ADVS','XXIA','TFSL','TTMI','NXTM','HEES','H WCC','PEET','CCMP','ARII','CLZR','OMRI','CELL','ALSK','EZPW','TRAD','ACLS','ANGO','UNS','UNT','US NA','JADE','CLDN','OSIS','CONN','RPRX','FNFG','ACGL','ODSY','CTSH','ACXM','ACTS','IFSIA','SOAP','S UPG','EHTH','MBLX','LDSH','APWR','STXS','UTEK','SCUR','LMNX','VLCM','NSTC','ORBC','URE','RSTO',' SRCL','EXPD','BARE','URZ','USB','SCHL','USG','SNMX','USM','PAAS','USS','UST','IXYS','UTI','PRFT','PF WD','HTCH','UTY','UTX','CMLS','XRIT','FVRL','TUES','JOBS','RUTH','MANT','UUP','MATW','VIRL','PACT',' STKL','WTNY','SVNT','UVV','MVIS','BLDR','SWHC','SNWL','RDEN','TUTR','SKYW','ASCA','CVTX','UXG',' RTEC','DXCM','IFON','LTXX','HIMX','VOXX','CRME','CHIC','HLYS','INFA','UYG','IPCC','ANST','IPCR','ZIG O','SOLD','WYE','VAL','VAW','VB','VBK','PRXL','VBR','VZ','VCP','VCR','VDC','IGOI','VE','VEA','VEU','VFC',' VFH','VGT','NFLD','VGZ','VLO','GBNK','VHT','VIA','VIG','VIS','VIV','VIX','YGE','SBIB','KVHI','XLF','SBCF',' VM','VMI','XLV','NAVZ','VNQ','VO','VOD','VOE','VOL','WHR','YMI','VOT','VPL','VPU','VQ','CNCT','INTV','XL K','AVAV','VICR','YUM','VRS','XLI','VTR','VTV','ESRX','VUG','WMI','VV','WEN','VVI','WMB','WPI','WMT','XL NX','VXN','XRX','YHOO','VYM','XL','YRCW','WAB','WAL','WBC','WBS','WCN','WDC','WDR','WEC','WES',' WFR','WG','WGL','WGO','WHQ','WIN','WIT','WL','WLK','WLL','WMX','WNC','WNR','WNS','WON','WOR','W PL','WPP','WPZ','WABC','WOLF','WR','WRE','WRI','WSH','WSI','WSM','WST','WTI','WTSLA','WTR','WTS',' WTW','WW','WWE','WWW','WWY','WX','WXS','WYN','XBI','XCI','XCO','XOI','RDNT','XIN','XLG','XMI','FAL C','MLNX','XOP','PKTR','XPH','CNVR','ALEX','OIIM','XRM','XRO','XRT','XSD','ZIXI','XFML','WMAR','SYNO ','ZEP','ZINC','SNPS','LAYN','RBCN','YZC','ZBRA','NEOL','TMR','UMC','AVNR','ZNH','ANX','ZNT','ZQK','Z OLL','NTMD','SONC','VIVO','AVII','ZZ','MECA','PZZA') AND DATEDIFF(o.`EXPIRATION_DATE`,CURRENT_DATE()) >=0 ORDER BY YEAR ASC ,MONTH ASC , O.STRIKE_PRICE ASC;

SELECT iu.* FROM sm_instrument i, sm_instrument iu, sm_option o, sm_option_root sor where i.instrument_id = o.instrument_id and i.symbol = 'JAHID' and o.opra_root = sor.opra_root and sor.underlying_instrument_id = iu.instrument_id Watchlist info select s.watchlist_symbol, u.user_login_id from qt_watchlist w,ac_user u, qt_watchlist_symbols s where u.user_login_id like 'nith-0006%'; select u.user_login_id,s.watchlist_name from ac_user u,qt_watchlist s where s.user_id=u.user_id and u.user_login_id like 'nith-00311'; select u.user_login_id,w.watchlist_name,s.watchlist_symbol from ac_user u, qt_watchlist w,qt_watchlist_symbols s where u.user_id=w.user_id and w.watchlist_id=s.watchlist_id and u.user_login_id like 'nith-001%';

SELECT u.user_login_id, w.watchlist_name, count(*) FROM qt_watchlist_symbols ws, qt_watchlist w, ac_user u where ws.watchlist_id = w.watchlist_id and w.user_id = u.user_id and w.watchlist_name like 'Watchlist1' group by w.watchlist_id order by u.user_login_id Delete Watchlist delete ws from qt_watchlist w, qt_watchlist_symbols ws where w.watchlist_id = ws.watchlist_id and w.user_id >= 1000000000000; user_id >= 1000000000000;

Admin priviledge select * from ac_user where user_login_id = 'nithya'; (5:04:17 PM) Shailesh Joshi: update ac_user set USER_ROLE_TYPE_ID = 1 where USER_ID = 1000000060051; (5:04:25 PM) Shailesh Joshi: update ac_user_role_map set role_id = 1 where USER_ID = 1000000060051;

Eric batch queries For all steps executing for longer than 5 minutes: select ji.job_name, se.step_name, unix_timestamp(se.end_time)-unix_timestamp(se.start_time) as elapsed from batch_job_instance ji, batch_job_execution je, batch_step_execution se where ji.job_instance_id = je.job_instance_id and je.job_execution_id = se.job_execution_id and timediff(se.end_time, se.start_time) >= '00:05:00' order by elapsed desc For all job executions running for longer than 5 minutes: select ji.job_name, unix_timestamp(je.end_time)-unix_timestamp(je.start_time) as elapsed from batch_job_instance ji, batch_job_execution je where ji.job_instance_id = je.job_instance_id and timediff(je.end_time, je.start_time) >= '00:05:00' order by elapsed desc; For all steps: select ji.job_name, se.step_name, unix_timestamp(se.end_time)-unix_timestamp(se.start_time) as elapsed from batch_job_instance ji, batch_job_execution je, batch_step_execution se where ji.job_instance_id = je.job_instance_id and je.job_execution_id = se.job_execution_id Restart server Go to the server to be restarted

sudo su - jboss