SAP Sybase Roadmap 2018 - Dobler Consulting Sybase
SAP Sybase Roadmap2018Presented bySAP & Dobler Consulting
Agenda Welcome Presenter– Anthony Antonello - V.P. HANA Global Centre of Excellence SAP Moderator– Peter Dobler – Founder & CEO Dobler Consulting Agenda––––HousekeepingIntroduction to Dobler ConsultingSAP Sybase Roadmap 2018Q&A
Housekeeping– Everybody is in listen only mode– Please use the “raise hand” feature to gain attention fromthe moderator– Session is being recorded and playback will be available– Please enter all questions through the panel– Questions will be answered at the end of thepresentation
Introduction to DoblerConsultingDobler Consulting is a leading provider of database servicesand information technology support, servicing clients rangingfrom small businesses to FORTUNE 500 companies acrossmultiple industry verticals.Servicing our clients with SAP Sybase upgrade and migrationproject, database managed services and license sales andconsulting.Visit us online at www.doblerconsulting.com, or contact us at813 322 3240, or pdobler@doblerllc.com.
IT Modernization SAP ASE RoadmapAnthony AntonelloV.P. HANA Global Centre of ExcellenceExternal
DisclaimerThis presentation outlines our general product direction and should not be relied on in making a purchasedecision. This presentation is not subject to your license agreement or any other agreement with SAP. SAP has noobligation to pursue any course of business outlined in this presentation or to develop or release any functionalitymentioned in this presentation. This presentation and SAP's strategy and possible future developments aresubject to change and may be changed by SAP at any time for any reason without notice. This document isprovided without a warranty of any kind, either express or implied, including but not limited to, the impliedwarranties of merchantability, fitness for a particular purpose, or non-infringement. SAP assumes no responsibilityfor errors or omissions in this document, except if such damages were caused by SAP intentionally or grosslynegligent. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal2
Modernization and SAP ASE1.Overview and Vision2.ASE and XOLTP Memscale Workload ProfilerASE for Data Centre Operations3. Always-on Security Workload Analyzer4.ASE for Cloud5.ASE Integration with HANA6.ASE Roadmap and Futures 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal3
SAP ASEEditions and their FocusSAP ASE, platformeditionSecure deployment flexibilityby incorporating SAP ASE,SAP IQ software, and SAPReplication Server in onelicensing modelSAP ASE, enterpriseeditionPower mission-criticaldatabase managementsystems for a single-nodeenvironment3-in-1 solutionLarge enterprisesSAP ASE, Edge editionSAP ASE, express editionEnable solutions for smallerdatabase deployments andapplications with a limitationof eight coresStart building transactionalapplications on a free, full uselicense for development anddeploymentMidmarket and ISVs 50 GB disk 4 enginesStartups/small businessSAP ASE, evaluationlicenseTake advantage of a freedownload for developmentenvironments (unlimited withall options available)Developers/educationMemScale optionWorkload analyzer optionAlways-on optionLeverages in-memory and HWoptimizations to achieve linearscalability for high-concurrencyXOLTP workloadsEmploys capture/replay techniquesto allow production workloads to bereplayed in dev/test environments tomitigate upgrade risks and moreaccurate server tuningLeverages streaming replication toprovide HADR clustering to supportboth high availability and disasterrecovery including zero downtimemaintenance and major upgradesXOLTP 2016 SAP SE or an SAP affiliate company. All rights reserved.TCOHigh availabilityInternal4
Evaluation, Packaging and Licensing ChangesNew evaluation license to evaluate ASE EEFor non-production environments onlyPrimarily for personal or independent consultantsNo limits on engines, connections, memory and storage spaceValid for 90 days. Can be extended for an additional se.htmlEnhanced Xpress Edition – free for productionAdded capacity to lower costs for small businesses - 4 cores and 50 GB limit onlyUser Friendly Licensing to Avoid Business DisruptionCustomers can install upgrades/patches even if their support contracts have expired Warning notice to customers allows them to renew support and update their licensesCustomers that need to run on larger machines than what they are licensed will be allowed to do so Warning provided to customers will remind them to be adequately licensed 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal5
2018-2019: ASE Engineering Focus AreasOLTP PerformanceData Center OperationsCloud EnablementASE and HANA 4TB and 64K connections Always-On Enhancements Additional Cloud Services - Common toolingIn-memory only tablesWorkload AnalyzerData MaskingHANA SQL ScriptNon-locking RODev/testTemporal SQLenhancementstables/partitions64bit MDA tablesOptimized data movement 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal6
SAP ASE – Feature-Packed ReleasesFeature Rich Releases in Quick SuccessionASE 15.7Reduce TCOSupport for SAP ERPASE 16Scale, SpeedSecurity2013 Incremental reorgIncremental dump databaseShrink DBPerformance :- Insert, create index- Dump and LoadMonitoring and Diagnosis2014 Scale-up on large SMP systems Partition-level locking Index compression Full database encryption Residual data removal Multi-trigger support 2016 SAP SE or an SAP affiliate company. All rights reserved.ASE 16 sp03:AccelerationHANA CompatibilityASE 16 sp02:AccelerationAvailability, Agility20172015Extreme OLTP support Single HA and DR based on synchronousreplicationWorkload Analyzer foreasier troubleshootingand upgradeExtreme OLTP support(IMRS)Tooling and Packagingenhancements(SQLScript, WebIDE )Internal7
ASE for XOLTPASE MemScale and Extreme Transaction Processing
What is SAP ASE Memscale?ASE Memscale is a suite of ASE features designedto improve XOLTP AccelerationThe Memscale option in ASE 16 SP02, and SP03leverages in-memory computing, intelligent dataplacement, atomic instructions as well as otherhardware and software innovations.The MemScale option is designed to significantlyincreases transaction throughput, concurrency andminimize latency. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal9
What is SAP ASE Memscale?Compiled Queries (SNAP) 2016 SAP SE or an SAP affiliate company. All rights reserved.Latch-Free B-TreeLockless Buffer ManagerInternal10
What is SAP ASE Memscale?Transactional Memory 2016 SAP SE or an SAP affiliate company. All rights reserved.Heat-Based Data PlacementNon Volatile CacheInternal11
What is SAP ASE Memscale?SP03In-memory Row Store (DRC) 2016 SAP SE or an SAP affiliate company. All rights reserved.SP03Multi-Version Concurrency Control (MVCC)SP03Hash Cached B-Tree IndexInternal12
High-level MemScale Feature ApplicabilityMemScale FeatureResource ContentionCacheOS memoryMgmtconcurrencyContention controlsQuery Speed/ LatencyQPOverheadRe-Reading Read / WriteDataConflicts Lockless Data Cache (LLDC) Simplified Native Access Plans(SNAP)Transactional Memory (TSX) Latch-Free B-Tree (LFB) In-memory Database (IMDB)Non-Volatile Cache (NVCache)In-Memory Row Store (IMRS)Hash Cached B-Tree (HCB)Multi-Version Concurrency Control(MVCC) 2016 SAP SE or an SAP affiliate company. All rights reserved.Xact LogSerialization Internal13
A Benchmark - Using Memscale OptimizationsMEMSCALE features tested– Compiled Queries (SNAP)– Lockless Buffer Manager– Latch-free BtreeBenchmark Hardware (SAP Engineering)––––HP DL50 - 80 cores & 512GB memory eachEach ASE configured identically – 80 engines, etc.Host #1 (red lines in graphs) ASE 16 sp01Host #2 (blue lines in graphs) ASE 16 sp02The benchmark Suite– An internal call center/case management systemo Has similar profile to trading systemso Significant cache contention - users after the same data rows (e.g. current ticker prices)– Benchmark scales from 1000 to 5000 users 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal14
ThroughputResponse Time 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal15
E2E Performance SP01, SP02, SP03 with 64 engines & minimal tunings*Common Industry BenchmarkASE versionNew orderCPUtransactions Utilization/ minComments15.7 SP6442K99%Cache manager spinlock contention16.0 SP01 GA42K99%Same as above16.0 SP02 GA282K45%(with LFB LLDC SNAP)(6.5X vs. baseline)Buffer unpinning on data pages, leading to latchconflicts resulting in syslogs semaphore contention16.0 SP03315K44%with same features(7.5X vs. baseline)LFB Improvements; data page latch contentionremains DRC719K95%Datapage latch contention removed96%Further codepath improvements with HCB(17X vs. baseline) HCB771K(18X vs. baseline)That’s 13K tps .*Minimal tunings – in the past when running this benchmark, the system was aggressively tuned using schema techniques such as partitioning, max rows perpage etc. to try to avoid contention. For these tests, only standard memory/proc cache and server configurations were tuned. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal16
ASE Workload Profiler
ASE Workload Profiler Use CaseBUT ASE 16 SP03 has a rich and complementarycollection of features to enable extremetransaction processing Lockless Data Caches Latch Free B-tree Indexes In-Memory Row Storage Data Row Caching MVCC / OD-MVCC Hashed Cached B-tree 2016 SAP SE or an SAP affiliate company. All rights reserved. Feature enablement can be DB-wide or tablewide Indexes may need to be altered to use newfeatures In-memory features need additional memoryresources to be configured New logging mechanism for in-memory featuresneeds additional disk devicesInternal18
ASE Workload Profiler methodologyRun WorkloadGather start metricsBaseline Schema info, MDA Metrics Spaceusage, row counts, configuration, monitorcounters, transaction log countsProcessing PhaseSource ASEProfiling SessionGather start sample metricsSampling intervalGather end sample metricsGenerate Delta-MetricsSampling intervalAnalyze Metrics:Schema Info, Metrics,Counters Growth RatesRun WorkloadTarget ASEIMRS-basedSampling intervalCapture FindingsSampling intervalReport Selection, SizingSampling intervalRecommendationsApply DDL, Configuration 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal19
User interface and commandsInstallwlprofiler: install script which has the DDL & sproc code needed to run ASE WorkloadProfilersp wlprofiler: Main interface used to drive the ASE Workload ProfilerBasic UsageExtended Usagesp wlprofiler “monitor”, dbname, “USING name Workload-name[, interval ‘interval-specifier’]sp wlprofiler “monitor”, dbname[ ]sp wlprofiler “monitor”, dbnamesp wlprofiler “finish” [, Workload-ID ] 2016 SAP SE or an SAP affiliate company. All rights reserved.sp wlprofiler [ “help” ]sp wlprofiler “status”sp wlprofiler “show”sp wlprofiler “report report config”sp wlprofiler “drop metrics”sp wlprofiler “uninstall”Internal20
‘finish’ sample outputEstimated # of rows that will be in IMRS1 sp wlprofiler2 goEstimated IMRS-cache and sysimrslogs requirements per table'finish'Running plan for Workload Name 'TPCC' affecting target db 'tpcc' evaluating feature(s) 'default'Tables which are likely to benefit from feature 'default' in Workload Name 'TPCC' (ID 2) affecting database'tpcc' are listed below:WLP ID DBName Owner TableName NRowsRowsInIMRS PctInIMRS IMRSCache MB IMRSLog MB------ ------ ----- ---------- --------- ---------- --------- ------------ ---------2 tpccdbowarehouse240240100.002.453540.692 tpccdbodistrict24002400100.004.7612668.652 tpccdboitem1000008322283.2232.360.002 tpccdbonew order4097610227312755.47892.768149.222 tpccdbostock2400000012000005.001051.1413954.152 tpccdbohistory3009593815047965.003978.005595.912 tpccdboorder line 305135760152567875.0044880.3566298.89(7 rows affected)Total IMRS Cache and IMRS log sizes needed for the workload:WLP ID DBName IMRSCacheSizeMB IMRSLogSizeMB------ ------ --------------- ------------2 tpcc51142110208(1 row affected)(return status 0) 2016 SAP SE or an SAP affiliate company. All rights reserved.Estimated total IMRS-cache and sysimrslogs requirementsActual configurations may need to be higher to cater to increased throughputRecommendation is to keep IMRS-Log at least 2 – 2.5X IMRS-CacheInternal21
‘report config’ sample output1 sp wlprofiler2 go'report config'List of configuration changes needed to enable feature 'default' for the tables in the database 'tpcc' seen in the workload 'TPCC' (ID 2):/*** BEGIN: ASE Configuration suggested by Workload Profiler for Workload 'TPCC' (ID 2) to enable feature 'default'**** Configuration generated on : May 16 2017 8:44AM**** ASE Version: Adaptive Server Enterprise/16.0 SP03/EBF 27167 SMP/P/x86 64/SLES 11.3/asecoronabtrim/0/64-bit/OPT/Tue May 16 07:43:29 2017**** Installwlprofiler version:**ScriptVersionStatus----------------- --------------------------------------------- -------installwlprofiler 16.0 SP03/EBF 27167 SMP/P/x86 64/SLES 11.3/asecoronabtrim/0/64-bit/OPT/Mon May 15 01:59:27 2017 Complete(1 row affected)*/USE mastergo-- We need an additional 51142 MB of max memory to accomodate-- the In-memory Row Storage Cache.--sp configure 'max memory', 0, '541214M'go-- Create In-memory Row Storage Cache.--sp cacheconfig 'tpcc imrs cache', '51142M', row storagego 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal22
ASE for Data Center OperationsASE 16 Features for Availability, Security & Administration
Always-OnHADR ClusterSingle cluster is limited to 2 nodes Additional standby nodes via external replicationLog-based Logical Replication Based Synchronous, Near-Synchronous, Asynchronous Zero Data Loss in Synch (RPO 0)Fast failover ( 2 minutes normally) Planned failovers 1 minuteGUI (ASE Cockpit – replaces SCC)CapabilitiesAutomated fault detectionAutomated transparent client failover Planned and unplanned failover supportCompanion can be read-only for reportingZero-down time major upgradesSupports In-Memory XOLTP optimizations in ASE 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal24
External Replication (Replication into/out of HADR Cluster)ASE 16sp02 pl05CI mode RepAgent embedded in SRS to read off SPQAppears to external SRS as if a RepAgent from a databaseAllows HADR cluster to be implemented without modifying existing replicationtopology E.g. no need to drop & recreate repdefs & subscriptionsCurrent restrictions on external replication:External SRS version must match HADR SRS versionMSA & Stored Proc/SQLDML replication supportedTable level replication to be supported in later release Use table exclusion in db repdef as work-around for now. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal25
SecurityASE 15.7 added a lot of security featuresASE 16.0 has added moreGranular permissionsFull database encryptionPredicated privilegesResidual data removalLogin profilesOpenSSL SAP CCLNo more null passwords (installs)Restrict owner accessEtc.Granular auditingFull text auditingConfiguration history auditing/trackingResolve as ownerOn Demand EncryptionPassword random 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal26
Security Coming Soon: Secure Password Store (SP03 PL04)What it isLocal secure store for ASE login credentialsStores user names & passwords for serversWhat happensUser connection only supplies login nameCredentials are looked up by API in user storeActual credentials forwarded to ASEAdvantagesNo more –P on command lineLimits exposure of automated process login infoAllows DBA’s to have different passwords for differentservers without having to track in unsecurespreadsheets. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal27
Security Coming Soon: HSM Secure Key Store (SP03 PL06)What it isAllows ASE root keys to be stored in networkedHardware Security Device (HSM)What happensASE master key is encrypted with HSM key instead ofSSO supplied passphraseKey management for HSM key is accomplished viaHSMOne or more ASE’s can use the same HSM keyAdvantagesEliminates risk of single user with passwordAlternative to 2PI (dual control) on master key 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal28
ASE Workload AnalyzerUse the SAP ASE workload analyzer option to: Identify problematic queries, such as queries with a long response time. Identify client activity patterns, such as the number of requests per IP address. Measure the performance of captured workloads in different server configurations. Evaluate database upgrades and understand benefits from new options. Diagnose production problems by replaying functionality in a controlledenvironment. Test new features and run them against a captured workload to verifyperformance. 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal29
Workload Analyzer Architecture OverviewDev/Test(replay)ASE Cockpit(capture)(Dev/Test)Workload AnalysisRepositoryProduction(capture)ASE CockpitPCAP files(production) 2016 SAP SE or an SAP affiliate company. All rights reserved.DBA Workstationw/ Cockpit GUI inbrowserInternal30
ASE for CloudASE support for cloud deployments and services
SAP ASEProduct/portfolio areas of future investmentVision: Market-leading XOLTP DBMS High-performance/low-TCO DBMS for SAP and custom applicationsRecent areas of focus Core XOLTP functionality SAP Business Suite and SAP R/3 development compatibilityNext big steps to reach our vision Provide enterprise-class cloud enablement capabilities Common SAP tooling/framework adoptionAreas of investment and path forward Cloud/cloud enablement Ongoing XOLTP/scalability enhancements Continued support for SAP Business Suite development 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal32
SAP ASE on Cloud TodayVendorProduct EditionKey CapabilitiesAmazonEnterprise Edition on Linux andWindows IaaS with BYOL (Bring Your Own License) Customers can run optionsAmazonRuntime Edition (for SAPBusiness Suite) on all platforms IaaS with BYOL (Bring Your Own License) Runtime Edition itself contains optionsDockerDeveloper, Express andEnterprise Edition CertificationSAP ManagedServices CloudEnterprise Edition Hosted services Fully managed environmentSAP CloudPlatformAdaptive Server Platform Edition(ASP) Subscription Fully managed DbaaS Not all options that are part of the ASPenabled 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal33
SAP ASE Cloud Backup through AWS Storage Gateway to S3Advantages Gives elastic and geographically remote storageRemoves storage purchase and managementOnly pay for what you useData is secure and encrypted at rest and in transitMeets compliance retention requirementsNo changes required to existing backup scripts orschedules except for backup locationHow it works AWS Storage Gateway is installed on premiseAWS S3 storage is mounted on premise as an NFSfile system directory managed by GatewaySAP ASE saves backup files to NFS file systemdirectoryAWS Storage Gateway manages data transfers andretrievals between on premise and AWS S3 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal34
Integration with HANAASE functionality for integration with HANA
ASE and HANA: HANA SQLScript Support Overview Support HANA SQLScript procedureSupport HANA SQL in ODBC, JDBCapplicationsSQLScript and TSQL parser co-existbut mutually exclusiveDatabase level separationPhased implementation SQLScript core (ASE 16.0 SP03) isthe first release followed byenhancements in future releases 2016 SAP SE or an SAP affiliate company. All rights reserved.Use Case 1: Build onASE, deploy on ASEand then HANABuildDeployOLTPAPPHANAASEASEUse Case 2: Workload Management– Offload low-priority or low-marginOLTP apps to ASEHANAHANAASEInternal36
SQLScript Overview - Common language interfaceClientClientT-SQL LanguageT-SQL databasesASE 2016 SAP SE or an SAP affiliate company. All rights reserved.SQLScript LanguageSQLScript databasesHANA SystemInternal37
What is SAP HANA Accelerator for SAP ASE SAP HANA accelerator for SAP ASE provides native access toHANA capabilities from ASE. This provides significant reporting performance improvements bypushing down the query processing to HANA HANA benefits are more than just speed 2016 SAP SE or an SAP affiliate company. All rights reserved.SAP ASE T-SQL queries and stored procedures are executedagainst data in HANACustomers can leverage all of the features of SAP HANA includingHANA’s many specialized engines (spatial, text, predictive etc.) fornew development.Internal38
Accelerating Legacy ASE Apps using HANAExisting AppReporting AppASE OLTPServerOLTP AppReplicationExisting App with A4AExistingOLTP AppReporting AppASEProxy ServerExistingHANA runs Legacy ASE apps Existing ASE reporting apps torun faster in HANA, with nocode changesWhat’s New More ASE-to-HANA pushdown, viatransformation of ASE built-in functionsyntax to HANA (isnull, convert, charLength,charindex,like,contatenation) More pushdowns of SQL, such as CASE,UNION with constants Enable temp table creation in HANA Enable full-push down via configuration Data in HANA can be kept in sync withASE, via replicating from ASE to HANAusing Smart Data IntegrationNEW Reporting AppQueryPush-DownUpgradeASE OLTPServerASEReporting Server NewHANAReplication Server 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal39
What is A4A? (Accelerator for ASE)SAP ASE ClientsRead/WriteASE OLTPServerHostBPhysicaltablesStoredprocs 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal40
What is A4A? (Accelerator for ASE)SAP ASE ClientsRead/WriteRead OnlyASE ReportServerASE OLTPServerHostBHost sReplication Server 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal41
What is A4A? (Accelerator for ASE)SAP ASE ClientsRead/WriteRead OnlyHANA ServerHost APhysicaltablesStoredprocsASE ReportServerASE OLTPServerHostBHost dprocsRead OnlyReplication Server 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal42
What is A4A? (Accelerator for ASE)SAP ASE ClientsRead OnlyASE ReportServerHANA ServerHost AASE OLTPServerHostBHost SRead OnlyReplication Server 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal43
What is A4A? (Accelerator for ASE)SAP HANAClientsSAP HANA StudioSAP HANACockpitHANA a4aSAP ASE ClientsRead OnlyASE ReportServerHANA ServerHost AASE OLTPServerHostBHost ad OnlyReplication Server 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal44
Pushing TSQL from SAP ASE to SAP HanaCREATE PROCEDURE dbo.TICK QRY5 (@SIC NAME VARCHAR(30) 'COMPUTERS',@FROM DATE DATE '2005-11-14', @TO DATE DATE '2005-11-14')ASBEGINCREATE TABLE #TS(TRADING SYMBOL VARCHAR(15),TRADESIZEINTEGER)CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TS 713965909"(TRADING SYMBOL VARCHAR(15) NOT NULL,TRADESIZE INTEGER NOT NULL)CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TRADERANK 729965966"(RANKING INTEGER NOT NULL,TRADESIZE INTEGER NOT NULL)CREATE TABLE #TRADERANK(RANKINGINTEGER IDENTITY,TRADESIZEINTEGER)INSERT INTO #TS (TRADING SYMBOL, TRADESIZE)SELECT ST.TRADING SYMBOL, SUM(TRADE SIZE) AS TRADESIZE-- DENSE RANK() OVER (ORDER BY SUM(TRADE SIZE) DESC) AS RANKINGFROM STOCK TRADE STINNER JOIN INSTRUMENT IION II.INSTRUMENT ID ST.INSTRUMENT IDINNER JOIN SCND IDST CLS SCON II.SCND IDST CLS ID SC.SCND IDST CLS IDAND SC.SIC NAME @SIC NAMEWHERE ST.TRADE DATE BETWEEN @FROM DATE AND @TO DATEGROUP BY ST.TRADING SYMBOLINSERT INTO #TRADERANK (TRADESIZE)SELECT DISTINCT TRADESIZEFROM #TSORDER BY TRADESIZE DESCSELECT TS.TRADING SYMBOL, TS.TRADESIZE, TR.RANKINGFROM #TS TSINNER JOIN #TRADERANK TRON TS.TRADESIZE TR.TRADESIZEORDER BY RANKING ASCDROP TABLE #TSDROP TABLE #TRADERANKENDgo 2016 SAP SE or an SAP affiliate company. All rights reserved.CISPushdownINSERT INTO A4A DEMO."#TS 141824751" (TRADESIZE, TRADING SYMBOL)SELECT SUM(T2.TRADE SIZE ) , T2.TRADING SYMBOLFROM A4A DEMO.STOCK TRADE T2,A4A DEMO.INSTRUMENT T3,A4A DEMO.SCND IDST CLST4WHERE T2.TRADE DATE '2005-11-01' AND T2.TRADE DATE '2005-11-15' ANDT3.SCND IDST CLS ID T4.SCND IDST CLS ID AND T4.SIC NAME 'FINANCIAL' ANDT3.INSTRUMENT ID T2.INSTRUMENT IDGROUP BY T2.TRADING SYMBOLINSERT INTO A4A DEMO."#TRADERANK 157824808" (TRADESIZE, RANKING)SELECT T3.*, A4A DEMO.SEQ #TRADERANK 157824808.NEXTVALFROM (SELECT DISTINCT T2.TRADESIZE AS TRADESIZEFROM A4A DEMO."#TS 141824751" T2ORDER BY 1 DESC ) T3SELECT T1.TRADING SYMBOL , T1.TRADESIZE , T2.RANKINGFROM A4A DEMO."#TS 141824751" T1, A4A DEMO."#TRADERANK 157824808" T2WHERE T1.TRADESIZE T2.TRADESIZE ORDER BY 3DROP TABLE A4A DEMO."#TS 713965909"DROP TABLE A4A DEMO."#TRADERANK 729965966"Internal45
Translation of ASE TSQL into Hana SQL for ExecutionCREATE PROCEDURE dbo.TICK QRY5 (@SIC NAME VARCHAR(30) 'COMPUTERS',@FROM DATE DATE '2005-11-14', @TO DATE DATE '2005-11-14')ASBEGINCREATE TABLE #TS(TRADING SYMBOL VARCHAR(15),TRADESIZEINTEGER)CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TS 713965909"(TRADING SYMBOL VARCHAR(15) NOT NULL,TRADESIZE INTEGER NOT NULL)CREATE TABLE #TRADERANK(RANKINGINTEGER IDENTITY,TRADESIZEINTEGER)CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TRADERANK 729965966"(RANKING INTEGER NOT NULL,TRADESIZE INTEGER NOT NULL)INSERT INTO #TS (TRADING SYMBOL, TRADESIZE)SELECT ST.TRADING SYMBOL, SUM(TRADE SIZE) AS TRADESIZE-- DENSE RANK() OVER (ORDER BY SUM(TRADE SIZE) DESC) AS RANKINGFROM STOCK TRADE STINNER JOIN INSTRUMENT IION II.INSTRUMENT ID ST.INSTRUMENT IDINNER JOIN SCND IDST CLS SCON II.SCND IDST CLS ID SC.SCND IDST CLS IDAND SC.SIC NAME @SIC NAMEWHERE ST.TRADE DATE BETWEEN @FROM DATE AND @TO DATEGROUP BY ST.TRADING SYMBOLINSERT INTO A4A DEMO."#TS 141824751" (TRADESIZE, TRADING SYMBOL)SELECT SUM(T2.TRADE SIZE ) , T2.TRADING SYMBOLFROM A4A DEMO.STOCK TRADE T2,A4A DEMO.INSTRUMENT T3,A4A DEMO.SCND IDST CLST4WHERE T2.TRADE DATE '2005-11-01' AND T2.TRADE DATE '2005-11-15' ANDT3.SCND IDST CLS ID T4.SCND IDST CLS ID AND T4.SIC NAME 'FINANCIAL' ANDT3.INSTRUMENT ID T2.INSTRUMENT IDGROUP BY T2.TRADING SYMBOLINSERT INTO #TRADERANK (TRADESIZE)SELECT DISTINCT TRADESIZEFROM #TSORDER BY TRADESIZE DESCINSERT INTO A4A DEMO."#TRADERANK 157824808" (TRADESIZE, RANKING)SELECT T3.*, A4A DEMO.SEQ #TRADERANK 157824808.NEXTVALFROM (SELECT DISTINCT T2.TRADESIZE AS TRADESIZEFROM A4A DEMO."#TS 141824751" T2ORDER BY 1 DESC ) T3SELECT TS.TRADING SYMBOL, TS.TRADESIZE, TR.RANKINGFROM #TS TSINNER JOIN #TRADERANK TRON TS.TRADESIZE TR.TRADESIZEORDER BY RANKING ASCSELECT T1.TRADING SYMBOL , T1.TRADESIZE , T2.RANKINGFROM A4A DEMO."#TS 141824751" T1, A4A DEMO."#TRADERANK 157824808" T2WHERE T1.TRADESIZE T2.TRADESIZE ORDER BY 3DROP TABLE #TSDROP TABLE #TRADERANKDROP TABLE A4A DEMO."#TS 713965909"DROP TABLE A4A DEMO."#TRADERANK 729965966"ENDgo 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal46
Translation of ASE TSQL into Hana SQL for ExecutionExecution in HANACREATE PROCEDURE dbo.TICK QRY5 (@SIC NAME VARCHAR(30) 'COMPUTERS',@FROM DATE DATE '2005-11-14', @TO DATE DATE '2005-11-14')ASBEGINCREATE TABLE #TS(TRADING SYMBOL VARCHAR(15),Creation ofTRADESIZEINTEGER)temp tableIn HANACREATE TABLE #TRADERANK(RANKINGINTEGER IDENTITY,TRADESIZEINTEGER)INSERT INTO #TS (TRADING SYMBOL, TRADESIZE)SELECT ST.TRADING SYMBOL, SUM(TRADE SIZE) AS TRADESIZE-- DENSE RANK() OVER (ORDER BY SUM(TRADE SIZE) DESC) AS RANKINGFROM STOCK TRADE STINNER JOIN INSTRUMENT IION II.INSTRUMENT ID ST.INSTRUMENT IDINNER JOIN SCND IDST CLS SCON II.SCND IDST CLS ID SC.SCND IDST CLS IDAND SC.SIC NAME @SIC NAMEWHERE ST.TRADE DATE BETWEEN @FROM DATE AND @TO DATEGROUP BY ST.TRADING SYMBOLINSERT INTO #TRADERANK (TRADESIZE)SELECT DISTINCT TRADESIZEFROM #TSORDER BY TRADESIZE DESCSELECT TS.TRADING SYMBOL, TS.TRADESIZE, TR.RANKINGFROM #TS TSINNER JOIN #TRADERANK TRON TS.TRADESIZE TR.TRADESIZEORDER BY RANKING ASCDROP TABLE #TSDROP TABLE #TRADERANKENDgo 2016 SAP SE or an SAP affiliate company. All rights reserved.CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TS 713965909"(TRADING SYMBOL VARCHAR(15) NOT NULL,TRADESIZE INTEGER NOT NULL)CREATE LOCAL TEMPORARY TABLE A4A DEMO."#TRADERANK 729965966"(RANKING INTEGER NOT NULL,TRADESIZE INTEGER NOT NULL)INSERT INTO A4A DEMO."#TS 141824751" (TRADESIZE, TRADING SYMBOL)SELECT SUM(T2.TRADE SIZE ) , T2.TRADING SYMBOLFROM A4A DEMO.STOCK TRADE T2,A4A DEMO.INSTRUMENT T3,A4A DEMO.SCND IDST CLST4WHERE T2.TRADE DATE '2005-11-01' AND T2.TRADE DATE '2005-11-15' ANDT3.SCND IDST CLS ID T4.SCND IDST CLS ID AND T4.SIC NAME 'FINANCIAL' ANDT3.INSTRUMENT ID T2.INSTRUMENT IDGROUP BY T2.TRADING SYMBOLInvoke temp tableIn HANAInvokef temp tableIn HANAINSERT INTO A4A DEMO."#TRADERANK 157824808" (TRADESIZE, RANKING)SELECT T3.*, A4A DEMO.SEQ #TRADERANK 157824808.NEXTVALFROM (SELECT DISTINCT T2.TRADESIZE AS TRADESIZEFROM A4A DEMO."#TS 141824751" T2ORDER BY 1 DESC ) T3SELECT T1.TRADING SYMBOL , T1.TRADESIZE , T2.RANKINGFROM A4A DEMO."#TS 141824751" T1, A4A DEMO."#TRADERANK 157824808" T2WHERE T1.TRADESIZE T2.TRADESIZE ORDER BY 3Drop of temp tableIn HANADROP TABLE A4A DEMO."#TS 713965909"DROP TABLE A4A DEMO."#TRADERANK 729965966"Internal47
Repatriating ASE T-SQL Procedures as HANA SQL Script Stored Procedure 2016 SAP SE or an SAP affiliate company. All rights reserved.Internal48
Jun 06, 2018 · SAP ASE – Feature-Packed Releases . Feature Rich Releases in Quick Succession Incremental reorg Incremental dump database Shrink DB Performance : - Insert, create index - Dump and Load Monitoring and Diagnosis. 2013. 2014. 2015. ASE 15.7. Reduce TCO. Support for SAP ERP. ASE 16. Scale, Speed. Security Scale-up on .
Trusted and unified data environment In-memory/real-timeing SAP HANA SAP Real-time Data Platform SAP Sybase ESP streams Anywhere SAP Sybase SQL mobile and embedded SAP Sybase IQ EDW SAP Sybase ASE transactions SAP Data Services / SAP Information Steward information management Common progra
Difficulty in upgrading from Sybase 12.x to Sybase ASE 15 Many Sybase customers who have not yet upgraded to Sybase ASE 15 are considering migrating away from Sybase and toward Oracle. This is because the expense associated with a Sybase ASE 15 upgrade, in many cases, would cover
Audience This guide is for users of Sybase ETL Development. How to use this book This book contains these chapters: † Chapter 1, "Sybase ETL," is an overview of the Sybase ETL architecture and the feature set of Sybase ETL Development and Sybase ETL Server. † Chapter 2, "Getting Started," describes how to get started using Sybase ETL.
SAP ERP SAP HANA SAP CRM SAP HANA SAP BW SAP HANA SAP Runs SAP Internal HANA adoption roadmap SAP HANA as side-by-side scenario SAP BW powered by SAP HANA SAP Business Suite powered by SAP HANA Simple Finance 1.0 2011 2013 2014 2015 Simple Finance 2.0 S/4 HANA SAP ERP sFin Add-On 2.0
data transformation process by using the unique Sybase ETL "Step and See" technology. How to use this book This book contains the following chapters: Chapter 1, "Sybase ETL" gives you a brief overview of Sybase ETL architecture and the feature set of Sybase ETL Development and Sybase ETL Server.
SAP Certification Material www.SAPmaterials4u.com SAP Certification Material for SAP Aspirants at Low cost Home Home SAP Business Objects SAP BPC CPM SAP BPC 7.0 SAP EWM SAP GTS SAP Public Sector SAP Real Estate SAP FSCM SAP FI/CO SAP AC - FI/CO SAP BI 7.0 SAP CRM 5.0
for Sybase ASE . Developer Edition on Windows 7 box. C: \ Sybase \ C:\ sybase \ase-15_0 corresponds to actual ASE database installation C:\ Sybase \ocs-15_0 corresponds to bundled client software (called OCS in Sybase ASE parlance) development kit. It is interestin
Understanding SYBASE Update Rules 16 Naming Conventions for SYBASE 16 Case Sensitivity in SYBASE 17 Data Types for SYBASE 17 Character Data 17 Numeric Data 18 Abstract Data 18 User-Defined Data Types 19 . Sybase ASE Perfor
