ASE 16/Sybase Administration
JeffreyGarbusSAP* ASE16/Sybase ASE Administration9RheinwerkPublishingBonn Boston
ContentsAcknowledgments23Preface25Introduction to SAP ASESystem Administration271.1Placement within the SAP Landscape1.2Architecture Overview381.3SAP ASE 16:411.4Increased1.3.2Security1.3.3SimplicitySAP ASE2.44546Key 31.4.4Data Federation (via1.4.5Graphical Monitoring and AdministrationEncryptionReplicationASE In-Memory Database52Component Integration AP ASE Cluster Edition1.4.82.2Speed and 4.61.5Key3858Logical PagePhysical Devices592.1.22.1.3Server Names61Size602.1.4Networking Information612.1.5Sybase Software61AssetManagerFile TransferRunning62Installation622.3.1Device ationStartupInstallation Files732.4.173Interfaces File7
Contents2.52.6Runserver File2.4.3Error2.4.4Server2.4.5SSL in SAP ASE792.4.6Environment g the Server83842.6.1Normal UNIX2.6.22.6.3Normal Windows StartupManual UNIX Startup2.6.4AutomaticStartupVerifyingShutting the Server88DownGraceful Shutdowns892.7.2Immediate Shutdowns892.7.3Maintenance Shutdowns8990SummarySAP ASE-Mirrored Devices8.Creating and DroppingDevices9192Master Device Creation3.1.2Raw Devices3.1.3Create Devices3.1.4Create Devices:3.1.5Default DevicesimIU3.1.6Dropping1023.1.7Dsync OptionSAP ASEversusFileSystem9496ExamplesDatabase Devices100102Mirroring1033.2.1Disk Mirror Syntax1043.2.2DecidingDisable Mirroring1073.2.33.4862.7.13.1.13.386System BootRunningto the Server3.28485the Server IsDefining Physical and3.178servername.cfgFrontend Installation2.6.52.7762.4.2What to t11 *l3.3.1Maintenance and Ease of Use 3.3.2Load111SummaryandBalancingHardware-Level Mirroring109
Contents4.1Database Structures1234.2System sybsystemprocs1264.2.44.41281284.3.1Create Database4.3.2Database4.3.3Creating Database Logs4.3.4Sizing4.3.5Alter Database4.3.6Find Database4.3.7Dropping the Database4.3.8Setting Database Optionsa132OwnershiponSeparate Devices132134Database135137Description140141System ions5.1.15.1.2The5.2.2147149Transaction Modes150Transaction Control Statements152Log156Commit 7Other SystemWorking with the Database4.2.54.3127DatabasesData158to DiskIs Full5.2.3When the Transaction5.2.4Automatically Truncating the LogFree Space Thresholds5.2.55.3Summary6.1SAP ASELogSecurity LevelsSystem-Level Security6.1.1Operating6.1.2Server-Level Security1621621671691721741759
Contents6.1.36.26.2.26.2.36.2.46.46.3.26.3.3System Role Functionssyslogins, sysloginroles, and syssrvroles6.3.4Turning System1g9Roles OffUser-Defined Roles6.4.1Creating signing Roles to UsersDropping RolesMutually Exclusive RolesAssigning Passwords to Roles6.4.8Default Roles for Logins196.4.91 956.4.10Granting Permissions to RolesDisplaying Information about Roles6.4.11Show Active Roles1966.4.12Display Permissions1976.4.13Groups1971Login Activities1996.5.11996.5.2Dropping LoginsThe syslogins Table2006.5.3Show Login Information2006.5.4Change Existing Login 6.6.3The Kill Command2046.6.4Command2056.6.5SystemServer Connectionsthe SetProxy CommandSecurityBuilt-in Functions2 2903205Object Permissions6.7.1With Grant6.7.2Revoking Object AccessGranting Vast PermissionsDisplaying Permissions6.7.36.7.410Role DefinitionsSystem6.4.66.7182The Operator RoleThe System Security Officer Role6.3.16.4.56.6The System Administrator RoleThe sysusers TableServer Roles6.4.46.5176Standard Roles6.2.16.3Database-Level SecurityOption208209209210
Contents6.7.56.7.66.8210Ownership ChainsTest/Change213Permissions213Access Rules6.8.1Access RulesUsingJava Function andApplication213Contexts6.92146.8.2Syntax for6.8.3DisableAccess Rules6.8.4Access Rules and6.8.5Fine-Grained Access Control218Access Rules218bcp(FGAC)219219Column Encryption6.9.1Enabling s TableNew Tables withEncryption223224Existing Tables6.9.4Altering6.9.52246.9.6Select Into Syntax with EncryptionCipher Text Randomization6.9.7Column Encryption Performance .10Summary2277.1Installation Overview2307.2The sybsecurity Database and 2367.2.3sybsecurity Stored ProceduresSet Auditing Options7.2.4Display Enabled Auditing Options2387.2.5Write User-Defined Comment to Audit Trail2397.2.6Add Audit Table to Audit Trail240236Audit Storage2417.3.1Queue2417.3.2Audit Database7.3.3Archiving AuditStorage RequirementsRecords242244Creating Audit TablesQuerying the Audit Trail2452497.6Applications and Third-Party SoftwareAdditional Tips on How to Effectively Audit7.7Summary2507.3.47.47.5External24625011
ContentsBacking Up8.18.28.3andResponsibilitiesBackup TypesBackup8.2.2Restore ScenarioBackup2542558.2.1Scenario?C5,S2 Server8.3.1Remote8.3.2Server Identification257Starting a Backup ServerMedia Changes during Dump and Load2588.3.4DumpingBackupDumpDump Database CommandDump CompressionDump Database SummaryDevices2 1264266266Loading the Database8.5.1Loading a Corrupted8.5.2Online Database8.5.3Load Database )fClZD/Database2682 8Summary8.5.4Creating a Database forMonitoring the Transaction Loga269Restore27 8.6.1Last-Chance Threshold2728.6.2Thresholds273Free-SpaceAborting versus Suspending TransactionsDumping Transactions8.6.38.78.7.1Truncate Transaction Log without8.7.2Truncate ngTransactions2778.8.1Up-to-the-Minute Recovery278Point-in-TimeRestoringRecoverythe Master Database8.9.1Steps8.9.2TheCumulativesybdumptran UtilityDumpsLoadingDumping8.12Quiesce s PlatformsRecovery Scenarios:Scenario 1Server279to Restore8.118.13.112DumpLog without CheckpointTruncate Transaction Log in the Case of Media Failure274Dump Transaction Log from PrimaryDump Transaction Activities8.8.28.92587*59the s and8.3.38.4Restoring283283Q&A28 286
Contents8.148.13.2Scenario 22878.13.3Scenario 32878.13.4Scenario 42888.13.5Scenario g2929.2TimeAdd Time9.2.2Simplify the Creation of Time RangesModify Time RangesRemove Time Ranges9.2.49.49.5293Ranges9.2.19.2.39.3Resource e ypeTypeof Enforcement298Action299299ScopeLimit Hierarchies3009.4.1Examples ofLimit Hierarchies9.4.2View Limits9.4.3Change9.4.4Remove Limits300301Limits301302System Tables3029.5.1spt limit Purpose of the Logical Process ManagerLogical Process Manager Execution gine Affinity30710.2.3Timeslice (Quantum)307LogicalProcessManager Procedures30810.3.1Add Execution Class30910.3.2Remove Execution Class31010.3.3Bind10.3.4RemoveExecution ClassObjectsObject Bindingstofroman310Execution Class31113
Contents10.410.3.5Set10.3.6ResetThread amic Execution Classesand Engine Groups313Create Engine GroupDrop Engine from s317Manager Conflicts and Precedence318Memory Configuration11.1312Execution AttributesandTuning.321SAP ASE Releases11.1.1Pre-SAP ASE 12.511.1.2SAP ASE 12.5 and LaterSAP emory Configuration322versus on SystemConfiguration File Parameter FormatSummary Table of Memory-Related VariablesRecommended Configuration .211.4.311.4.411.4.511.5123593 0ConfigurationMemoryCalculating Procedure CacheSample Server Configuration3* 2Statement Cache364Guidelines foraCache3 23 3Summary364Data Cache12.112.212.1.1Transaction Performance and Named Caches12.1.2Creating12.1.3Guidelines foraBuffer PoolsNamed CacheConfiguring aNamed Cache.3713/137212.2.3Creating a Buffer PoolRemoving a Buffer PoolUsing Buffer Pools12.2.4Wash Area37412.2.212.3365Named anObjectto a Named Cache377
Contents12.412.3.2Dropping Cache Bindings37712.3.3Information37712.3.4fred cache after PartitioningonBindings378How to Tune ing Cache for In-MemoryIdeas385orRelaxed DurabilityDatabases12.4.4MRU tainingDatabases Using Data itioningRange tioningPartitioning397How and When to Use DataPartitioning39713.3.1Range Partitioning39813.3.2List39913.3.3Hash PartitioningLocalversus13.5.2Partitioning400Global Indexes400with PartitionsWorking13.5.113.6the Cost ofIndex .2387389Use Data13.1.1386Replacement StrategyConfiguringGetting403PartitionsPartition InformationSome Uses for SemanticPartitioning40340340413.6.1Data Loads40413.6.2Data Truncation40513.6.3Updating PartitionStatistics40513.7Summary40614.1Remote Procedure Call40814.1.1ServerNaming40914.1.2Remote Access41015
Contents14.214.1.3Login Mapping14.1.4Example:14.3Remote AccessComponent Integration14.2.1Adding14.2.2Local Storage14.2.3Proxy15.2a Remote414415417DatabasesTables fromSystem Files14.2.5Enhanced Mapping of ExternalLogins41841942114.2.6File Access14.2.7SAP ASE VariablePageSize Issues421423SummaryRegimenServer-Level Maintenance42515.1.1System42615.1.2MDA Tables42915.1.3Locking Contention Monitoring43015.1.4System Parameter MonitoringMonitoring the System Error Log43515.1.515.1.6Resource Verification4 415.1.7Software Maintenance4 15.1.8RecordingUse Information439456Runtime DataDatabase-Level Maintenance4 15.2.1Scheduling45715.2.2Run dbcc Commands15.2.3General-Purpose ChecksUnderstanding the OutputDatabase Maintenance4594 from dbcc Commands47115.2.5Errors Generated bydbcc14 215.2.6Planning ResourcesMaintaining dbccdbGenerating Reports from very49315.2.11Log Management49315.2.12Space ManagementScript Maintenance49415.2.815.2.1316Server for CIS rvices15 Preventative Maintenance15.1410482485494Table-Level Maintenance49415.3.1Update Statistics49515.3.2Indexes495Summary495
Contents16497High Availability and Disaster Recovery16.1Definitions and Causes49716.1.1Uptime49816.1.2Data Loss abilityBroad Approaches to High Availability16.1.4and DisasterRecovery501Planning16.2.1Hardening:Reduce the Chance of FailureReduce theImpactof Failure16.2.2Redundancy:16.2.3Recovery Planning: Reduce the Cost of Recovery16.3503StandbyAvailability and Recoverability50416.3.2Operating SystemStorage/Disk506DBA and User507513Activity51316.4.1SAP ASE Patches16.4.2Multiple16.4.3Multiple tempdbs16.4.4SAP ASE Boot Time514SAP ASE Listener Ports516516HousekeepingInadequateOffload DBA Housekeeping from Production16.4.616.4.7Key Person DependenciesBackup Strategies16.5.116.5.2DBACosts ofIncremental/Transaction Database BackupsNative 16.5.6TestCold16.6.216.6.3TapeversusDumpDumps by Loading ElsewhereDumps by ValidatingDump and Load Databasedump and load tranDump with standby access, load,standby accessStandby: SAP Replication ServerReplicate Transactions, Not DataforWarm16.7.1520520521523to Disk524525525525Standby: Dump 116.3.316.4Hot, Warm, and ColdArchitecting the502503Post-Failure16.2.4501526526online database52752852817
Contents16.8Warm16.7.3Reduce Downtime forHotversusUpgrades and MigrationsStandby: Clustering16.8.1Vanilla SAP ASE with OS16.8.2SAP ASE HA with OS16.8.3SAP ASE Cluster ummary17.1Advantages of SAP Replication ServerNew Features in SAP Replication Server (Support 17.4Components and17.4.1System Tables17.5Partitions and Stable17.4.3Data Servers17.4.4Replication AgentReplicationObtaining17.5.2Installation of SAP17.6.417.6.517.6.618.1547License at SPDCforPreparationSAP Replication547Serveror548SMPServer SoftwareReplicationInstalling and Configuring554Perform Post-Installation TasksWarmStandby556Setting Up557558ConsiderationsWarm559Standby Replicationthe Warm549550ServerSetting Up Warm Standby17.6.1Consistency and Latency17.6.317.854717.5.1a546QueuesInstallation of 8Standby52yMulti-Site Availability16.7.2StandbySetting UpAdding the Standby Database to the maryat the Databasebetween the Active andOverviewStandbyDatabases563565566Changesin SAP ASE 1657018.1.1Showplan Utility57118.1.2Relaxed Query Limits572
ContentsImprovements18.1.4 Improvements18.1.3572in the Hash Joinsin theQuery Plan and Execution572Statistics in HTMLQuery Optimization18.3Layers of SAP ASE 1618.3.1Application LayerDatabase Layer57618.3.3Network Layer Loads57718.3.4Hardware Device577Query578Search Engine579Display580Access Method CostsQuery Optimizationand the PerformanceImpactsonTuningStatistics in18.5.2Storing18.5.3Parallel Query ProcessingControlling the Query Optimization583Statistics in Tables585586587StatisticsAutomatically UpdatingThe datachange18.6.1588Functionthe Processor StatisticsUsing Recommendations for Adding Statistics for Unindexed589How Often to 7.2581582Query Optimization18.5.118.7.118.8578Processor and the I/O SizeDetermine Total Actual I/O Cost Value18.6.518.757718.4.218.5.418.6LayerOperating System ess18.2InputRules for LRU and MRU inQuery Optimization595596DefaultStrategy (LRU)Fetch-and-Discard (MRU) Strategy596598Summary19 SAP ASE In-Memory Database59919.1Use Cases for SAP ASE IMDBs60019.2Difference between SAP ASE IMDB and Traditional SAP ility60319.2.3Database Cache604Operations Supported By19.3.1Different DatabaseIn-Memory TemporaryDatabasesConfigurations60560619
Contents19.3.2to Create an60619.3.4In-Memory DatabaseAdministering In-Memory DatabasesUsing Minimally Logged .3.3Managing Relaxed Durability Databasesbcp Modes61620.1.220.220.320.4bcp Command ExamplesCopy Definitions (defncopy)20.2.1defncopy Command Examples20.2.2defncopy Command TipsExtract CreationScript (ddlgen)20.3.1ddlgen Command Examples20.3.2 ddlgen Command TipsCommand Parser (isql)20.4.1isql .7ATipsA.1isql TipsDisplay System Table Information (optdiag)625626Summary630and Tricks633System-Specific RecommendationsA.1.2A.2625Interactive SQLA.1.1UNIX Systems BackupSolaris: Total PhysicalDatabase Maintenance633633Memory633634A.2.1Index MaintenanceA.2.2DeadlockingA.2.3Find Source Procedure CodeA.2.4Moving tempdb Off the Master DeviceLoading Production Databases into a DevelopmentA.2.5634635EnvironmentA.320611610Copy Program20.1.1609Performance TuningA.3.1Tune I (Before636637641You GetA.3.2Tune IV (Free DB)A.3.3Tune V641There)(After You're There)6421.II"642642
ContentsA.3.4A.3.5A.4643ProcessMonitoring, Scripts,Tools648A.4.1Unique Users by Database648A.4.2Kill User Connections to the Database655A.4.3Database Device Utilization Report658A.4.4ActiveversusA.4.5SimpleDataInactive Connections663669MiningA.5Security685A.6DBA Commands: Reference Sheet687A. 7Acknowledgements693695TroubleshootingB644Common Problems and SolutionsB. 1The Server Won't Come UpB.2Some Users Claim the ServerB.3Server IsUp,695StoppedSome Users Can't Gain AccessB.4Processing SlowsB.5Some Database(s) Can't Be AccessedB.6Users Can't AccessDownorObjectsStops696696697697698COther Resources699DThe Authors701Index70521
11.1.1 Pre-SAP ASE 12.5 Memory 321 11.1.2 SAPASE12.5and LaterMemoryUse 322 11.2 SAP ASE Configuration Basics 322 11.2.1 CurrentMemoryConfiguration 323 11.2.2 Dynamic versus Static Options 326 11.2.3 Configuration System Tables 327 11.2.4 Configuration File Parameter Format 354 11.3 Summa
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
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
"active/passive" support. For "active/active" support for ASE Enterprise Edition, contact Sybase for their agent. Supported software for the VCS agent for Sybase The VCS for Sybase agent for Sybase supports the following software versions: Sybase Adaptive Server Enterprise (ASE) Enterprise Edition 1
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.
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
all" for the Sybase dataserver by connecting to isql session. Supported software for Sybase The Veritas agent for Sybase supports the following software versions: Sybase Sybase Adaptive Server Enterprise (ASE) 12.5.x and 15.x Veritas Cluster Server VCS 5.1 on Solaris SPARC: Sola
May 16, 2012 · Sybase ASE Password Strength . 16 Sybase ASE Patches . 17 Sybase ASE Roles and Groups . 17 Contents. 8 Contents. Baseline Policy Manual for CIS Benchmark for Sybase A
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.
