SAS Scalable Performance Data Server 4.5 Administrator'S Manual Download

Page: 1 / 271

SAS

Scalable Performance

Data Server

4.5

Administrator’s Guide

TW10909_colortitlepg.indd 1

5/26/09 3:22:24 PM

Summary of Contents for Scalable Performance Data Server 4.5

Page 1: ...SAS Scalable Performance Data Server 4 5 Administrator s Guide...

Page 2: ...S Government Restricted Rights Notice Use duplication or disclosure of this software and related documentation by the U S government is subject to the Agreement with SAS Institute and the restrictions...

Page 3: ...D Server 3 x to SPD Server 4 5 15 Upgrading SPD Server 4 x to SPD Server 4 5 16 Configuring SPD Server Host Software for Your Site 16 Verify That SPD Server 4 5 Is Running 22 Configuring SPD Server Cl...

Page 4: ...sing the SPD Server Manager in SAS Management Console 72 Password Manager 72 ACL Manager 77 Server Manager 81 SPD Process Profiler 83 Proxy Manager 85 Chapter 8 SPD Server SQL Query Rewrite Facility 8...

Page 5: ...e 146 PART 5 Security 147 Chapter 14 ACL Security Overview 149 ACL Security Overview 150 SPD Server ACL Security Model 150 Controlling SPD Server Resources with PROC SPDO and ACL Commands 154 Symbolic...

Page 6: ...ata File 238 Backup Table of Contents File 239 Backup User Messages 240 Spdsrstr the SPD Server Table Restore Utility 240 Using PROC SPDO to Back Up and Restore SPD Server Tables 246 Back Up and Resto...

Page 7: ...Part 1 Product Notes Chapter 1 SPD Server 4 5 Product Notes 3 1...

Page 8: ...2...

Page 9: ...ve changed from the operating system requirements for SPD Server 4 4 For more detailed information about operating system requirements for SPD Server 4 5 see the SPD Server Pre Installation and System...

Page 10: ...SPD Server supports via implicit pass through SQL The installation and delivery of the SPD Server 4 5 client components for SAS is now part of your SAS installation For more detailed information about...

Page 11: ...Part 2 Installation Chapter 2 SPD Server Pre Installation and System Requirements Guide 7 Chapter 3 SPD Server UNIX Installation Guide 11 Chapter 4 SPD Server Windows Installation Guide 37 5...

Page 12: ...6...

Page 13: ...ments and Tuning for 64 bit SPD Server 9 System Requirements 9 Windows Requirements and Tuning for 32 bit SPD Server 10 System Requirements 10 SPD Server 4 5 Client Requirements 10 System Requirements...

Page 14: ...er HP UX kernel parameters that may need to be increased depending on the way you use the SPD Server include ninode Maximum open inodes in memory Adjust for the maximum number of concurrently open SPD...

Page 15: ...Solaris on X64 Requirements and Tuning for 64 bit SPD Server System Requirements The following kernel parameter needs to be adjusted on Solaris server systems where you will run SPD Server rlim_fd_ma...

Page 16: ...ired OS level Windows NT 4 0 Service pack 3 or greater Minimum System Configuration NT server system SPD Server 4 5 Client Requirements System Requirements Required SAS level SPD Server 4 5 requires S...

Page 17: ...y That SPD Server 4 5 Is Running 22 Configuring SPD Server Client Software 23 Testing Your SPD Server Installation Using SAS 24 SPD Server Command Reference 26 SPD Server Name Server Commands 26 SPD S...

Page 18: ...After you correctly configure UNIX directory ownership and you set permissions on your LIBNAME domains there is no real need or benefit for root access to SPD Server For more information and a list of...

Page 19: ...n directory whose path is represented by InstallDir Note InstallDir represents the root directory where SPD Server is installed The bin subdirectory contains the following binary files spdsnsrv is the...

Page 20: ...spds Do not use the killspds script if you have any processes running in UNIX that do not belong to SPD Server but whose executable names begin with the letters spds killrc is a shell script that kill...

Page 21: ...nistrator s Guide and the SAS Scalable Performance Data SPD Server 4 5 User s Guide in PDF format The lic directory contains the SPD Server license file for your installation The spds lic file is used...

Page 22: ...to the InstallDir site directory rc spds pwdb spdsserv parm killrc libnames parm rc perf 3 In the InstallDir site directory edit the pwdb script file Note Depending on the shell that you are running...

Page 23: ...days between successful logins Default infinite Enter the maximum allowed login failures Default infinite Enter admin s performance class 1 LOW 2 MED 3 HIGH carriage return for LOW User admin added E...

Page 24: ...OGFILE Specify whether to create an audit file facility using AUDDIR Specify the audit file cycle time and the file prefix with AUDTIME and AUDFILE Specify the location of your server user password da...

Page 25: ...the installation directory from your site directory INSTDIR bin The PATH LD_LIBRARY_PATH and LIBPATH statements in the default rc spds file refer to the INSTDIR bin directory The PATH LD_LIBRARY_PATH...

Page 26: ...R and the rc spds script will use dev null If you want to keep the logs in another location besides InstallDir log change LOGDIR The DSRVFILE and DSRVTIME spdsserv options NSRVFILE and NSRVTIME spdsns...

Page 27: ...e to specify the time of day HH MM to cycle the audit log file When AUDDIR and AUDFILE are set you enable proxy audit file creation When AUDDIR and AUDFILESQL are set you enable SQL audit file creatio...

Page 28: ...he context of your current UNIX user ID The rc spds script customization is important because it defines UNIX ownership and file access permissions on SPD Server resources Ownership and file permissio...

Page 29: ...ient software is used to make SAS LIBNAME connections and perform user specified operations on the SPD Server host The SPD Server client software is installed with SAS 9 2 and contains the following S...

Page 30: ...Testing your SPD Server installation is simple To verify you make two SAS LIBNAME assignments using the SPD Server LIBNAME engine The examples in this section refer to the SASSPDS engine which is the...

Page 31: ...4 5 while engine version is 3 x ERROR Module TEST not found in search paths ERROR Error in the LIBNAME or FILENAME statement If you receive the following or similar error message describing failures t...

Page 32: ...values from the SPD Server host s LIBNAME configuration file must be unique across all SPD Server hosts connecting to the Name Server Part of the function of the Name Server process is to start an SPD...

Page 33: ...ious log file is closed and a new log file is opened SPD Server Host Commands The SPD Server LIBNAME engine connects to the SPD Server host to access data in the server environment The SPD Server host...

Page 34: ...ile is required even when running with the NOACL option You must use the SPD Server psmgr utility to create the password file and to populate it with the set of valid SPD Server user IDs noacl Disable...

Page 35: ...irectory provides examples of the command line options When using automatic SNET server log cycling remember to periodically clean the log files Proper log file maintenance includes archiving logs usi...

Page 36: ...nistrative capabilities When you install SAS 9 2 and SPD Server 4 5 you are prompted to install the SAS Management Console utility For more information about SAS Management Console see Introduction on...

Page 37: ...AP server If it is found in the SPD Server parameter file LDAPBINDMETH is a character string whose value is either LDAP_AUTH_SIMPLE or LDAP_AUTH_SASL The default authentication method LDAP_AUTH_SIMPLE...

Page 38: ...word facility without giving them specific login accounts This adds a measure of security and control and SPD Server users are permitted physical access to the SPD Server machine You should add the In...

Page 39: ...s provide the extra layer of access control The SPD Server administrator needs to be familiar with the psmgr utility in SPD Server If you do not use SPD Server user IDs you still need the SPD Server p...

Page 40: ...node name is incorrect 2 NAMESERVERPORT specifies the wrong port number if the SPD Server Name Server is running with a non standard port assignment 3 PARMFILE file specification is invalid or the sp...

Page 41: ...d in the LIBNAME statement In this case node xxx is not accessible in the network 4 libname foo sasspds test server xxx spdsname passwd xxx ERROR Unable to connect to SPDS name server ERROR xxx ERROR...

Page 42: ...eceive a new license to replace your existing license You must restart SPD Server to use the new license Note You should not change the licensing information unless you are logged in with the user ID...

Page 43: ...5 46 Overview of Upgrading 46 Upgrading SPD Server 4 4 to SPD Server 4 5 46 Installing and Configuring SPD Server Clients 46 Testing Your SPD Server Installation Using SAS 47 SPD Server Command Refer...

Page 44: ...etc services file if you want SPD Server clients to connect to the SPD Server host using name services instead of specifying port numbers at invocation Name services require you to define registered...

Page 45: ...bug version of spdsbase spdsengd dll is the debug version of spdseng spdsnlslib dll is the NLS library spdsnlslibd dll is the debug version of spdsnlslib spdsiotest exe is the stand alone SPD Server I...

Page 46: ...in SQL Pass Through scale sas can be used to benchmark the scalability of your SPD Server dynamic_cluster sas shows how to use dynamic clusters with a MIN and MAX variable list minmax sasshows how to...

Page 47: ...NAMESERVERPORT option in spdsserv bat to specify the port number 2 The default SPD Server installation configures port 5401 as the SNET Server port If you want to change the SNET server port edit the...

Page 48: ...vices to start SPD Server Here are some shortcuts Select Start Programs SAS SPD Server 4 5 Install SPD as a Service to install services that start the SPD Server Name Server the SPD Server host and th...

Page 49: ...e Name Server Data Server and SNET server The Automatic setting loads the Name Server Data Server and SNET server without prompting It stops the services without prompting when you close Windows To ch...

Page 50: ...ET server automatically start and stop with Windows Configuring SPD Server Software on Your Windows Host After you validate port and library assignments and start the Name Server and Data Server you c...

Page 51: ...bref and connection scenarios tempwork sas demonstrates temporary LIBNAME domain support Files created in a temporary LIBNAME domain are automatically deleted when the SAS session ends paraload sas sh...

Page 52: ...from SAS 9 2 spds msg is the SAS compatible message file for the SPD Server LIBNAME engine and SPDO operator procedure The SPD Server client software is installed with SAS 9 2 Foundation at SASROOT SA...

Page 53: ...on is relatively simple To verify you only need to make two SAS LIBNAME assignments using the SPD Server LIBNAME engine The examples in this section refer to the SASSPDS engine the engine for SAS 9 2...

Page 54: ...PD Server components is properly set 3 Once the SPD Server host LIBNAMES are assigned you can further verify your installation by running the sample SAS program InstallDir samples verify sas Submit th...

Page 55: ...chine where you license the SPD server and that key must be entered into this license file by the SPD Server administrator The SPD Server will not run on a given machine without first entering a valid...

Page 56: ...wd dir path Specifies the directory path to the SPD Server host password file This option can be omitted if the PASSPATH option is declared in the SPD Server host s parmfile A valid SPD Server passwor...

Page 57: ...he following options listenport listen_port Specifies the listen port number spdssnet will use to accept connections from ODBC JDBC or htmSQL clients If not specified spdssnet will use the named servi...

Page 58: ...client then returns the result to the SPD Server After the client is verified SPD Server uses the client s password database record for all other SPD Server operations To set up LDAP authentication t...

Page 59: ...r the LDAPSERVER can cause SPD Server to fail during start up It is recommended that SPD Server and LDAP Server use the same hosts The user password is sent to the LDAP server in clear text If someone...

Page 60: ...h invalid contents 3 The Name Server port is in use by another process Check for another Name Server process running already on the same node ps ef grep i spdsnsrv SPD Server Host Startup Failed Check...

Page 61: ...s incorrect 3 libname foo sasspds test server stelling spdsname passwd xxx ERROR Unable to connect to SPDS name server ERROR Connection refused ERROR Error in the LIBNAME or FILENAME statement 4 An in...

Page 62: ...e SPD Server software the licensing information is pre initialized When you renew your license you will receive a new license to replace your existing one You must restart SPD Server to use your new l...

Page 63: ...Part 3 Migration Chapter 5 SPD Server 3 x to SPD Server 4 5 Conversion Utility 59 57...

Page 64: ...58...

Page 65: ...ever SPD Server 4 5 provides a conversion utility SPDSCONV that permits SPD Server 3 x customers to convert existing tables for use with SPD Server 4 x The SPDSCONV utility is designed to be run by th...

Page 66: ...the SPD Server 3 x index files for use with SPD Server 4 4 If you choose to create the SAS job file to recreate SPD Server 3 x indexes for use in SPD Server 4 4 the code will resemble the following le...

Page 67: ...NV utility usually an SPD Server Administrator cannot browse the contents of table rows from within the utility During the metadata file conversion no table rows are accessed and there are no options...

Page 68: ...ted prior to execution to ensure that the proper SPD Server 4 4 LIBNAME is used with the PROC DATASETS statement v create verbose output for the conversion process SPDSCONV Utility Examples Suppose yo...

Page 69: ...ant SPDSCONV want to create SAS jobs that you can run to recreate the indexes after the table conversion completes You want the SAS jobs put into the directory HOME salesv9 You also want a verbose out...

Page 70: ...64 Chapter 5 SPD Server 3 x to SPD Server 4 5 Conversion Utility...

Page 71: ...Management Console 71 Chapter 8 SPD Server SQL Query Rewrite Facility 89 Chapter 9 Using SPD Server With Other Clients 93 Chapter 10 Configuring Disk Storage for SPD Server 105 Chapter 11 Setting Up...

Page 72: ...66...

Page 73: ...allows the system administrator to add remove or reallocate disk space and computing power without having to change SAS source code Nor is there the necessity of informing others about changes in res...

Page 74: ...figuring SPD Server on Worldcpu The libname parm file that resides on the worldcpu server contains the following line libname world pathname spds This code instructs SPD Server to register the combina...

Page 75: ...then subreg 1 run The submitted code extracts records from an SPD Server table named alldata which resides in the domain world The world domain is stored on machine worldcpu in the directory spds Beca...

Page 76: ...70 Chapter 6 Using the SPD Server Name Server to Manage Resources...

Page 77: ...mains 81 Refresh Parms 82 Perform Commands 82 SPD Process Profiler 83 Proxy Manager 85 Overview of the Proxy Manager 85 Proxy Refresh 86 Proxy Interrupt 86 Proxy Cancel 87 The SAS Management Console T...

Page 78: ...at are designed to create metadata for a specific type of resource For example administrators can use the SAS Management Console to configure SPD Server user and group passwords and ACLs instead of us...

Page 79: ...lect Connect on the Users tab of the SAS Management Console window to open the Connect to SPD Server window The Connect to SPD Server window contains input fields for the following components The comp...

Page 80: ...sole status indicator in the lower right corner of the SAS Management Console window with the SPD Server connection status at the bottom of the Users tab Users and Groups Overview of Managing Users an...

Page 81: ...the user and then add a new user Auth Level the numeric authorization level ranging from 0 to 7 To change the value select the field and edit it Perf Level the Perf Level setting is not yet implement...

Page 82: ...ime out field represents infinity so a user account with a zero value in the Time out field never times out Allowed Login Failures the number of consecutive login failures that is allowed before the u...

Page 83: ...rd in the Change User Password dialog box and click Change Resetting a Password To reset the password for a selected user using the Users tab of the SAS Management Console window click Reset Pass Spec...

Page 84: ...ement Console window to display the ACL resources that have been defined The ACL Manager display contains the following components Owner the resource owner This field cannot be changed directly To cha...

Page 85: ...user group has permission to read this resource Write if selected the specified user group has permission to write to this resource Alter if selected the specified user group has permission to alter t...

Page 86: ...ermissions that you want to grant and then click Add The user is added and the ACL listing is automatically updated An individual user or group cannot be deleted from an ACL resource To delete a user...

Page 87: ...lities You must connect to an SPD Server host machine before you can use the SPD Management utilities For additional information see Connecting to an SPD Server on page 73 provides detailed instructio...

Page 88: ...le controls server configuration and options Perform Commands To run an SPD Server operator command or utility function click Perform Command in the Server Manager Enter the command or utility in the...

Page 89: ...and to run a spdsbkup command SPD Process Profiler The SPD Management folder in SAS Management Console contains an SPD Process Profiler utility Use the SPD Process Profiler to view server resources th...

Page 90: ...ormance Server application spdsperf must be running for the targeted SPD Server Name Server SAS Management Console must be able to connect to the SPD Server Performance Server s listening port Like th...

Page 91: ...roxy Manager The SAS Management Console tree for SPD Server contains a folder called Proxy Manager You use the Proxy Manager to display tables that list all users that access a specific SPD Server hos...

Page 92: ...te the table with the most current proxy data Refreshing is necessary after an initial connection or after a proxy state has been manipulated Because a proxy s state is dynamic each refresh provides o...

Page 93: ...libref The proxy s activity is immediately halted and any open data connections are immediately closed Clicking Cancel to stop a proxy from accessing a data set or a given domain is recommended when...

Page 94: ...88 Chapter 7 Administering and Configuring SPD Server Using the SAS Management Console...

Page 95: ...fficiently Inserting the derived intermediate data into the original SQL query does not change the quantitative results it only expedites the processing that is required to calculate them The SQL Quer...

Page 96: ...ng RESET command in his or her SQL job stream execute reset tmpdomain ematmp by sasspds Setting TMPDOMAIN EMATMP causes the EMATMP domain to take precedence over the TMPDOMAIN setting that was specifi...

Page 97: ...xecute reset _qrwenable 1 by sasspds Another way to enable _QRW Option Use the _QRW reset option to enable diagnostic debugging and tracing outputs from the SQL Query Rewrite Facility in the log The d...

Page 98: ...92 Chapter 8 SPD Server SQL Query Rewrite Facility...

Page 99: ...er Tables 99 Why Would I Want to Use JDBC 99 How Is JDBC Set Up on the Server 100 How Is JDBC Set Up on the Client 100 How Do I Use JDBC to Make a Query 100 JDBC Code Examples and Tips 101 Limitations...

Page 100: ...is an htmSQL Web page and a client machine with a browser SQL C API This option allows access to SPD Server tables from SQL statements generated by C C language applications This access is provided in...

Page 101: ...s on the Server Instructions for installing the OBDC driver are included in the download package Configuring ODBC on the Client 1 Configure an ODBC data source 2 Make your query using a Windows progra...

Page 102: ...the machine name or IP address of the SPD Server Name Server also called the HOST any secondary LIBNAME domains you want to assign to the ODBC connection Two Types of ODBC Connections With SPD Server...

Page 103: ...then select the SAS ODBC driver 4 Enter a data source name and description if desired 5 Select the Servers panel and type in your two part server name 6 Click on the Configure box The TCP Options wind...

Page 104: ...domain on the SPD Server to which you will connect Server User Password Enter the user password as configured for a DBQ SPD Server primary LIBNAME domain on the SPD Server host to which you will conne...

Page 105: ...ODBC Databases 7 Select the data source Using JDBC Java to Access SPD Server Tables Read this information if you do not have Base SAS software on the network client but you want to use the power of th...

Page 106: ...e SPD Server is installed The process is covered in the SPD Server installation manual How Is JDBC Set Up on the Client The client needs a browser set up to accept Java applets such as Netscape Naviga...

Page 107: ...M name lines Line 2 spdssnet_node is the node name of the machine on which the SPD SNET server is running PORT port number of the machine on which the SPD SNET server is running Line 3 value DBQ libna...

Page 108: ...AME AS tname name length AS datatype type length AS prec length length AS scale length AS radix length AS nullable label FORMAT FROM dictionary columns AS tbl WHERE memname your data file AND tbl libn...

Page 109: ...distribute the information over the World Wide Web Your developers are familiar with SQL and HTML How Is htmSQL Set Up on the Server htmSQL is usually set up on the server at the time the SPD Server...

Page 110: ...the network client but you want to provide your network client machines with the capability of accessing SPD Server tables using SQL query methods You must have SPD Server tables available for use SPD...

Page 111: ...his topic if you manage large SPD Server data files that consume gigabytes of disk storage How you configure SPD Server disk storage is important whether you have many SPD Server users or a just a few...

Page 112: ...of the index keys the larger the hbx file The size of the idx file is much more difficult to determine because it depends on how the data for the index keys is distributed across segments An index key...

Page 113: ...to Store SPD Server Table Data and Index Component Files in Other File Systems The following SPD Server code invokes the libnames parm file spdsserv acl acldir InstallDir site nameserver samson libna...

Page 114: ...adding the METAPATH specification to your ROPTIONS value in your LIBNAME parameter file Unfortunately this will not solve your problem Remember the SPD Server restriction mentioned earlier all mdf com...

Page 115: ...se if a user specifies an option the user s setting is implemented If the user omits an option your OPTIONS specification in the libnames parm file is used System Administration recommendation Do not...

Page 116: ...110 Chapter 10 Configuring Disk Storage for SPD Server...

Page 117: ...DAPBINDDN 115 MINPORTNO MAXPORTNO 115 MAXGENNUM 115 MAXSEGRATIO 116 MAXWHTHREADS 116 MINPARTSIZE 116 NO BYINDEX 116 NO COREFILE 116 NO LDAP 117 NO NLSTRANSCODE 117 NO WHERECOSTING 117 RANDOMPLACEDPF 1...

Page 118: ...it maintains options that control the SPD Server processing behavior and use of server resources If you do not specify your SPD Server parameter file with the parmfile option SPD Server assumes that s...

Page 119: ...ere the user defined format is stored To use user defined formats you must create a domain that is named FORMATS FMTDOMAIN is used with FMTNAMENODE and FMTNAMEPORT Usage FMTDOMAIN FORMATS FMTNAMENODE...

Page 120: ...nates the client connection When IDLE_TIMEOUT is specified as a value that is greater than 0 the option is enabled If the value is less than or equal to 0 SPD Server does not enable idle timeouts The...

Page 121: ...RING LDAP bind method string LDAPBINDDN specifies the Relative Distinguished Name RDN or the location in the LDAP server database where the information for the connecting client is stored The SPD Serv...

Page 122: ...llel threads to launch for a WHERE clause evaluation Usage MAXWHRTHREADS ldap_server_binddn_string maximum number of parallel threads MINPARTSIZE ensures that large SPD Server tables cannot be created...

Page 123: ...9 2 however will read the CEI value of the table and performs transcoding for any pertinent character data in the rows returned from SPD Server When NLSTRANSCODE is specified SPD Server reads the tabl...

Page 124: ...g minimum sequential i o buffer size SORTSIZE controls the amount of memory to allocate for sort operations A larger value for this option can increase the paging activity for a file and degrade perfo...

Page 125: ...from the port assigned to LOCAL_HOST To run an LDAP server on the SPD Server host using a port assignment other than LOCAL_HOST add the LDAP option and the LDAPPORT port specification to your SPD Serv...

Page 126: ...nd set the LDAPPORT port specification to TCPIP_PORT in your SPD Server parameter file Then add the LDAPBINDDN specification where the LDAPBINDDN property setting is ou people dc domain dc company dc...

Page 127: ...maximum number of characters in WHERE clause The default value for WHAUDLEN is 512 characters The maximum value for WHAUDLEN is 4 096 characters SQLAUDLEN Specifies the maximum size of the SQL stateme...

Page 128: ...122 Chapter 11 Setting Up SPD Server Parameter Files...

Page 129: ...ns and Data Spaces 133 Overview of Domains and Data Spaces 133 Permanent Table Space 134 Semi Permanent Table Space 135 Temporary Table Space 135 Example Libname parm File Configurations 136 Example 1...

Page 130: ...t the domain creates SPD Server administrators and users can use Domain Path Options on page 124 to enhance computational performance by specifying separate paths for domain data index and work tables...

Page 131: ...in the libnames parm file is identical for options and roptions LIBNAME domain name PATHNAME primary metadata path OPTIONS option 1 option n LIBNAME domain name PATHNAME primary metadata path ROPTION...

Page 132: ...ETAPATH can be specified as an option or as a roption Usage METAPATH meta1 spdsmgr meta meta2 spdsmgr meta Consistency in Nomenclature It is a suggested practice but not a requirement to match or clos...

Page 133: ...ACL on the domain to grant or deny privileges to other SPD Server users When the domain is specified with an owner only the owner can use the TEMP YES LIBNAME option with the domain The owner can use...

Page 134: ...hat inherit WRITE access from writing to updating or deleting resources that were created by the domain owner LIBNAME ALTER access with OWNER and LIBACLINHERIT YES allows the owner to grant privileges...

Page 135: ...access for user anonymous PROC SPDO library noinher Specify who owns these ACLs set acluser admin add a LIBNAME ACL to d1 add acl libname Modify LIBNAME ACL Domain d1 Allow users in Group 1 read only...

Page 136: ...es a single record level locking point of failure for the record level proxy If there is a failure in an SPD Server user proxy when dynamic locking is being used only the client that is connected to t...

Page 137: ...r administrator must allocate the correct amount of disk space and I O redundancy to the various paths This section provides functional information about the table spaces that are defined by the DATAP...

Page 138: ...other space categories metadata space is relatively small and usually does not require scalability If compressed data in a given warehouse uses 10 terabytes of disk space there will be approximately 1...

Page 139: ...ardware redundant RAID design RAID 0 configurations are risky to the extent that if the RAID 0 disk goes down the system will also be affected and any process that was running at the time of failure w...

Page 140: ...hat sufficient disk space required for the combination and manipulation of large amounts of data from multiple large warehouse tables is always available For example an organization might call such a...

Page 141: ...production sales or inventory data for a specific period of time longer than a single SPD Server user session The test environment should persist long enough for works in progress to mature to product...

Page 142: ...ain table data metadata index data and intermediate data will reside in the single directory that is referenced in the path specification c data skulist Example 2 Specify Domain Paths for Data Index a...

Page 143: ...rver session temporary tables are automatically deleted Some SPD Server users might use the QRW domain for its temporary table space even if they are not submitting code for an SPD Server SQL query re...

Page 144: ...temporary tables with sufficient disk space to perform large or optimized intermediate table calculations Multiple data and index paths are specified to take advantage of RAID configured disk arrays L...

Page 145: ...mp INDEXPATH idx01 spdtemp idx02 spdtemp idx03 spdtemp idx04 spdtemp Spdsserv parm file code uses the TMPDOMAIN SPDTEMP statement to reference the domain that was created for temporary tables and uses...

Page 146: ...deleted at the end of the SPD Server session LIBNAME PROD sasspds PROD server hostname hostport user user id password password IP YES LIBNAME USERTBLS sasspds USERTBLS server hostname hostport user u...

Page 147: ...er processes spawned by an SPD Server Name Server All SPD Server users must connect to an SPD Server Name Server before their SPD Server session is spawned Each SPD Server Name Server owns a dynamic f...

Page 148: ...n be saved locally on the SPD Server host machine SPD Server ships with a perl utility called process_perf_log that can parse the log that was created by the SPD Server Performance Server Starting the...

Page 149: ...og to SEC is the optional property that specifies the number of seconds that transpire between instances of performance monitoring data captures The permissible range for SEC property values is intege...

Page 150: ...ode below is a typical rc perf script that you can modify for use at your own site Follow the instructions in the section above to customize the script for your SPD Server installation In order to ass...

Page 151: ...ho n Script 0 n Args n set x trace trace echo Script tracing turned on echo Found unknown arg passing on to profiler PARGS PARGS 1 esac shift done echo NSPORT NSPORT echo SNPORT SNPORT echo DEBUG DEBU...

Page 152: ...ile The SPD Server Performance server can also be configured to save the process performance information to a text log file Your SPD Server installation includes a perl utility called process_perf_log...

Page 153: ...Part 5 Security Chapter 14 ACL Security Overview 149 Chapter 15 Managing SPD Server Passwords Users and Table ACLs 193 147...

Page 154: ...148...

Page 155: ...LIST ACL _ALL_ 162 LIST ACL Examples 163 DELETE ACL and DELETE ACL _ALL_ 164 DELETE ACL Examples 164 Symbolic Substitution 165 Symbolic Substitution Row Level Security 165 Symbolic Substitution Examp...

Page 156: ...rsal ALTER access to the resource add rename delete or replace a resource and add delete indexes associated with a table The resource owner can use the following properties to grant ACL permissions to...

Page 157: ...le set up by the system administrator Password expiration can be enforced by the system administrator via the psmgr administration tool for the user ID table or through the SAS Management Console if i...

Page 158: ...arketing and clinical are registered with the name server The disk1 public disk2 qadata disk3 marketing and disk4 clinical directories must exist and the user ID that invokes spdssrv must have read an...

Page 159: ...defined by the system administrator in the libname file UNIX File Level Protection with ACL Security Disabled In UNIX each SPD Server session runs under a UNIX user ID All files created by SPD Server...

Page 160: ...C SPDO runs only on systems where the SAS is installed PROC SPDO Command Set To invoke PROC SPDO submit PROC SPDO LIB libref where libref is a LIBNAME that was previously allocated to the sasspds engi...

Page 161: ...LIBNAME domain When a LIBNAME ACL is created for a specified LIBNAME domain the ACL precedence of permission checks becomes 1 Check user specific permissions first If defined the accessor gets these...

Page 162: ...denied access to the column by default Once a column is protected via ACL security explicit permissions must be granted in order for any user or groups of users to be able to access the column content...

Page 163: ...so on a LIBNAME domain Two Part Resource Name Two part names identify a column entry within a table Use the normal SAS convention of table column when specifying the table and column that you want to...

Page 164: ...ust first issue a SET ACLUSER command before issuing any of the following ACL commands ADD ACL acl1 acl2 C cat T type options Creates new ACL entries acl1 acl2 where ACL entries acl1 acl2 can be one p...

Page 165: ...s universal READ and WRITE access add acl mine_jan1999 read write Add Generic ACL This generic ACL for MINE grants universal READ access add acl mine generic read Add Column ACL This ACL for the colum...

Page 166: ...he table identifier in a two part name modifies all tables for which the given column is matched Specifying _ALL_ as the column identifier in a two part name modifies all columns for which the given t...

Page 167: ...This modifies a LIBNAME domain to set READ and WRITE access for a given user modify acl LIBNAME ralph y y n n Modify ACL MINE This modifies ACL MINE_JAN2003 to deny universal WRITE access and add use...

Page 168: ...or two part table column names Specifying _ALL_ lists all existing resource ACLs for which you have control access Specifying _ALL_ as the table identifier in a two part name lists all tables for whi...

Page 169: ...les This lists all column ACLs for all tables list acl _all _all_ List a Specific Column This lists the column ACL for MINE_JAN2006 SALARY list acl mine_jan2006 salary List All ACL Data for a Table Th...

Page 170: ...ies that acl1 acl2 are generic ACLs LIBNAME Identifies the special LIBNAM ACL C cat Identifies the selected ACLs as names of catalog entries from the catalog cat This value must be paired with the T o...

Page 171: ...t be in all upper case text Consider the example SPDSUSR SOMEUSER Symbolic Substitution Row Level Security A powerful use of symbolic substitution is deploying row level security on sensitive tables t...

Page 172: ...n to sasspds select from mytable where SPDSSPEC TRUE queries based on column values will only select appropriate columns select from connection to sasspds select from mytable where usercol SPDSUSR sel...

Page 173: ...LIBACLINHERIT YES the following precedence of permissions checks on the ACL resource If user specific permissions are defined on the object for the accessor the accessor gets these permissions If grou...

Page 174: ...ER6 0 user ID GROUP2 USER7 0 user ID GROUP3 USER8 0 user ID GROUP4 BOSS 7 user ID ADMINGRP EMPLOYEE 0 user ID Domain Security When the libname parm option OWNER is specified no other user can access t...

Page 175: ...1 still remains the owner of the LIBNAME ACL LIBNAME prod2d2 sasspds d2 server zztop 5162 user prod1 password spds123 IP YES PROC SPDO library prod2d2 Set user ID as user1 who owns the ACL to be modif...

Page 176: ...ets these permissions 2 If a resource is owned by the same ACL group as the accessor the accessor gets the resource s GROUP permissions 3 LIBNAME ACL permissions are used for domains where LIBACLINHER...

Page 177: ...odify acl LIBNAME read list acl _all_ quit Set up libname access for user anonymous PROC SPDO library noinher Specify who owns these ACLs set acluser admin add a libname ACL to d1 add acl LIBNAME Modi...

Page 178: ...o the password database and keep the password secret Any table that is created by the anonymous user ID can be viewed by all users who have access to that table s domain The anonymous ID does have the...

Page 179: ...ACl so only user ID anonymous can read the table add ACL anonymous_table modify ACL anonymous_table anonymous y n n n list ACL _all_ quit Now only user ID anonymous can read the table LIBNAME user1 sa...

Page 180: ...l provides backup The following example demonstrates how to allow different groups access to the domain tables and how different groups can control resources in the domain LIBNAME d1 sasspds d1 server...

Page 181: ...in This ACL is a good example for data marts and warehouses which DO NOT contain sensitive data A GENERIC ACL gives broad access to tables in a domain Generic ACLs must be used correctly or not at all...

Page 182: ...over the generic ACL The ACL in the code below performs the following Gives read access of admin1_table2 to group1 Gives the admingrp read and control of admin1_table2 Takes precedence over the generi...

Page 183: ...dividual users The example below shows how to provide the different groups of users access to the domain owned by the user ID Admin and then extends the access to the tables This makes administration...

Page 184: ...1 to 100 output end run Generic ACL allows all users to read tables created by admin1 PROC SPDO library d1 assign who owns the ACLs set acluser admin1 Modify LIBNAME for groupread and groupwrite The A...

Page 185: ...list ACL _all_ run admin2 has write privileges to the domain data admin2d1 admin2_table do i 1 to 100 output end run Admin2 must use PROC SPDO to allow users read access to the table The PROC SPDO ex...

Page 186: ...me group read access to the table PROC SPDO library admin2d1 assign who owns the ACLs set acluser user3 Modify LIBNAME ACL for groupread and groupwrite The ACL MUST include groupread if other users in...

Page 187: ...le s read privileges before the refresh occurs new SPD Server jobs cannot access the table Existing jobs will continue running and can finish under the lock You can also use the special PROC SPDO oper...

Page 188: ...es of users and groups who will not be involved in the refreshing of tables in the domain This example assumes that the tables are already loaded in the domain and that the groups who use them have ac...

Page 189: ...uring table refreshes modify ACL prod1_table prodgrp n n n n Modify table ACL to allow the prod1 user ID to refresh the table modify ACL prod1_table prod1 y y y y list ACL _all_ refresh warehouse tabl...

Page 190: ...sers do you want Limit the number in order to maintain control access SPD Server super users must be knowledgeable about the data and the database users needs Assume the table user1_table1 is loaded a...

Page 191: ...group2 generate some dummy data data user1 t id 1 salary 2000 run Example of only user2 in group2 being allowed to read column salary PROC SPDO library user1 Assign who owns the ACLs set acluser Clea...

Page 192: ...r2 n n n n quit Let both users print the table Only user6 can access column salary proc print data user2 t run proc print data user6 t run quit DICTIONARY PWDB and DICTIONARY ACLS Overview of Dictiona...

Page 193: ...to SPD Server To list all the users in the password database submit the following select from connection to sasspds select from dictionary pwdb To select only the user name and last log in date submi...

Page 194: ...es multiple ports a port to receive data commands from the client a port to receive operator commands from the client and a port for each open table to send and receive data between client and server...

Page 195: ...or commands Each SPD Server host table that is opened also creates its own port Each SPD Server table port becomes a dedicated data transfer connection that is used to stream data transfers to and fro...

Page 196: ...ource the LIBNAME of the domain the user ID of the SPD Server user that is accessing the resource the resource name the resource type the SPD User ID of the resource the SPD Group ID of the resource t...

Page 197: ...he SQL queries that were submitted to the SPD Server server The SQL audit record contains the following information the SQL query timestamp the type of SQL query SELECT DROP ALTER CREATE DESCRIBE UPDA...

Page 198: ...192 Chapter 14 ACL Security Overview...

Page 199: ...rd table Each record in the password table describes the specific attributes and capabilities that are associated with an individual user SPD Server uses two types of user authentication The first typ...

Page 200: ...no longer enabled Until an administrator resets the user ID the user will not be able to connect to the SPD Server host If you are upgrading to SPD Server 4 5 from SPD Server 3 x the SPD Server 4 5 ps...

Page 201: ...er Add a New User Who Creates His Own Password This two part method requires that the new user knows how to use one of the following LIBNAME options to change his password CHNGPASS or NEWPASSWD The fi...

Page 202: ...t a Password for the User The second method adds a new user to the password table using the psmgr utility The password expires immediately after the user is created The SPD Server administrator create...

Page 203: ...s can be either an apha numeric or underscore The SPD Server user ID does not have to correspond to any system user ID passwd the user s password which is restricted to 8 characters The psmgr table re...

Page 204: ...led can be re enabled by the psmgr administrator using the reset command class the performance class of the user This field is currently not being used AUTHORIZE authorizes a user to modify the passwo...

Page 205: ...umber of days from today the current day that the password is valid CHGIP changes the IP address from which the user must connect to the SPD Server The IP address on which the SAS ODBC JDBC or SQL cli...

Page 206: ...in to re enter it for accuracy The new password must be different from the last 6 passwords The new password must also contain at least 6 characters with at least one numeric character and with at lea...

Page 207: ...oupname argument verifies that the groups that are specified with the GROUPMEM command are valid GROUPDEL deletes an ACL group entry Syntax groupdel groupname Arguments groupname the name of a group v...

Page 208: ...the password table Syntax import textfile Arguments textfile the name of the flat file to import that contains the user definitions to add to the password table Description The IMPORT command reads th...

Page 209: ...assword must contain at least 6 characters at least one character must be numeric and at least one character must be alphabetic The argument is repeated to verify the password for accuracy Note The ne...

Page 210: ...ing LDAP or the psmgr utility LDAP Authentication is performed by an LDAP server that runs on the SPD Server machine When you use LDAP authentication the operating system handles password maintenance...

Page 211: ...firm with your LDAP server administrator that the host logon information exists in the LDAP database If you are using LDAP user authentication and you create a user connection that uses the NEWPASSWOR...

Page 212: ...206 Chapter 15 Managing SPD Server Passwords Users and Table ACLs...

Page 213: ...dure PROC SPDO 209 Chapter 17 SPD Server Index Utility Ixutil 219 Chapter 18 SPD Server Table List Utility Spdsls 227 Chapter 19 SPD Server Backup and Restore Utilities 231 Chapter 20 SPD Server Direc...

Page 214: ...208...

Page 215: ...ver Resources with PROC SPDO and ACL Commands The following SPDO commands require that you have ACLSPECIAL enabled for your SPDO LIBNAME connection To enable ACLSPECIAL you must first grant the SPD Se...

Page 216: ...ies and proxies that are owned by the LIBNAME owner If the LIBNAME owner has ACLSPECIAL privileges then all user locking proxies will be listed For each user locking proxy thread SPD Server returns th...

Page 217: ...id 17610 set user anonymou 17610 NOTE User ANONYMOU connected to proxy operator port with pid 17610 3 Show every LIBNAME for user ANONYMOU for this proxy showlibname _all_ LIBREF FOO Pathname assigned...

Page 218: ...s03 test qabig1_dev bigx dpf _bigdisk_test_qabig1_dev 1 1 spds9 spds04 test qabig1_dev bigx dpf _bigdisk_test_qabig1_dev 2 1 spds9 spds01 test qabig1_dev bigx dpf _bigdisk_test_qabig1_dev 3 1 spds9 sp...

Page 219: ...erator of the user proxy you get the following message ERROR Operator mode owned by another connection Cannot grant this request After you have successfully set yourself as the operator the following...

Page 220: ...e user receives a message in the SAS log that states that the job has been interrupted If the job did not finish then the results might be incomplete However the user LIBNAME will be intact and the us...

Page 221: ...for SPD Server you can use the REFRESH command to avoid restarting the server Submitting the REFRESH command refreshes the specified SPD Server file without restarting the server When you submit the...

Page 222: ...L privileges to refresh domains LIBNAME reftest sasspds reftest server d8488 5180 user admin password spds123 aclspecial YES PROC SPDO library reftest SET acluser admin REFRESH DOMAINS quit Domains th...

Page 223: ...ERROR Specified locking user no longer exists If the disconnected user used a non locking user proxy and you submit an OPER command you get the following message ERROR Specified user userID with pid P...

Page 224: ...218 Chapter 16 SPD Server Operator Interface Procedure PROC SPDO...

Page 225: ...mance and minimize disk space The utility also prints the disk usage statistics or the contents of indexes Ixutil Usage ixutil crejidx data set1 column1 data set2 column2 data set_n column_n libpath p...

Page 226: ...f a particular index are sorted in relation to their observation numbers By default ixutil run stats displays the ten longest runs in the data set A run is defined as the number of successive observat...

Page 227: ...ently and then merge their partial results into the final result dsn data set name The SPD Server table that contains the index libpath physical path The physical path of the domain containing the tab...

Page 228: ...cs for Index X segment_size 8192 n_segments_in_tbl 5 n_values_in_index 2 n_vdeleted_values 1 percent_vdeleted 33 33 n_seglist_values 2 n_seglist_chunks 3 avg_chunks_per_list 1 00 idx_file_bytes 13304...

Page 229: ...ace consider reorganizing the index Reorganizing the index frees up disk space when the garbage content is high Retrieve Index Distribution Statistics Use the dist option of ixutil stats to get the in...

Page 230: ...pleted successfully Running the index utility program again to get the statistics shows that the segment lists for all of the values have been aggregated the avg_chunks_per_list is 1 0 and the unused...

Page 231: ...Statistics Now get statistics on the join index that you created using the statjidx option of the ixutil command The statistics are printed for each join range of the index as well as for the overall...

Page 232: ...delete the join index ixutil deljidx Table1 ID Table2 ID libpath tmp SAS Scalable Performance Data Server 4 5 TS M0 Build Feb 26 2009 11 50 08 Index File Utility Copyright c 1996 2009 by SAS Institute...

Page 233: ...onent file a list of all other component files for the table that might be affected to provide information about the size of SPD Server tables Usage spdsls l i o a s v v8 v6 aonly libpath Table spdsls...

Page 234: ...fied SPD Server table in the LIBNAME domain lists all component files for the table If there is no table specified lists all tables in the LIBNAME domain The output list can be used with any system fu...

Page 235: ...s data public trx dpf _bigdisk_sas_data_public 2 1 spds9 16774912 bigdisk sas data public trx dpf _bigdisk_sas_data_public 3 1 spds9 16774912 bigdisk sas data public trx dpf _bigdisk_sas_data_public 4...

Page 236: ...230 Chapter 18 SPD Server Table List Utility Spdsls...

Page 237: ...234 Backup Requirements 235 Client Access to an SPD Server Domain 235 LIBNAME Created with BACKUP Option 235 Example Libnames parm Statements 235 Backup Usage 236 Backup Options 236 Backup Return Val...

Page 238: ...by backing up the entire table If the table is very large the backup time can be lengthy In addition the processing can consume considerable machine resources For this reason administrators frequentl...

Page 239: ...ilities You must use your SPD Server 3 x utilities to restore SPD Server 3 x backup files and then archive the restored files using the SPD Server 4 5 utilities Privileged Access Protection Running th...

Page 240: ...restored ACL files must be in the same physical directory as the domain If any ACL file does not meet this requirement the ACLs will not be backed up and a warning message will be sent to the log The...

Page 241: ...e entry for the LIBNAME domain canbackup which also creates tables in the directory usr foo test has the BACKUP YES option As a consequence tables created through this domain are eligible for backup W...

Page 242: ...for an incremental is unavailable it prints a warning message and the table is not backed up If a full backup is available it performs an incremental backup of the table using the later of the two dat...

Page 243: ...rforms only full backups of SPD Server tables All of the table observations and attributes indexes definitions partition size compression and sorted are backed up After each full table backup it reset...

Page 244: ...mpleted If spdsbkup encounters a table that was created before the specified date time that is it is in the last full system backup spdsbkup sets the last full backup date for the table to the specifi...

Page 245: ...ontents file is a normal system file and cannot be extended The table of contents file size is constrained only by the native operating system s file size limit The table of contents file contains the...

Page 246: ...some notes to stdout unless the quiet q option is specified The notes include useful summary information such as the name of the table that was backed up the number of observations that were backed up...

Page 247: ...ot meet all of the criteria spdsrstr prints a warning message to stdout and does not restore the table If spdsrstr is restoring multiple tables it will restore only the tables that meet the restore cr...

Page 248: ...e the full filename including its extension created by the SPD Server backup utility h The host SPD Server to use for the backup hash Prints a hash sign to stdout for each 256K compressed block that i...

Page 249: ...mation such as the name of the table that was restored the number of rows that were restored and whether the restore that was performed was a full restore or an incremental restore Warning Table Canno...

Page 250: ...backups on data sets that you have previously archived as well as to perform full backups on new data sets that have never been backed up You can also back up your SPD Server data sets using a system...

Page 251: ...able of contents file into a SAS table of contents table Example 3 Restoring a Single SPD Server Table Use the following steps to restore a table that was accidentally deleted from the domain test on...

Page 252: ...resulting in the latest backup of the ACLs being restored Using PROC SPDO to Back Up and Restore SPD Server Tables You can use the SAS PROC SPDO spdscmd to run the SPD Server backup and restore utili...

Page 253: ...in to its last full backup state spdscmd spdsrstr aforce d tstdomn h sunny s 5150 e spdsadm bkup test Back Up and Restore Table Indexes using SPD Server Full Backups When you perform an SPD Server ful...

Page 254: ...w or modified records In summary the first method trades the additional resources required for full backup of the table index files which can be very large against the potentially short time that migh...

Page 255: ...esources that you save by not fully backing up the index files against the greater amount of time it can take to recreate the indexes fully if the table must be restored Method 2 Recreate the Index af...

Page 256: ...250 Chapter 19 SPD Server Backup and Restore Utilities...

Page 257: ...up utility spdsclean to perform routine maintenance functions on directories that you use to configure SPD Server storage directories that SPD Server uses for working storage various system specific d...

Page 258: ...E domain name in the libname file For example the domains pattern test will match the domains ATEST1 ATEST123 ATESTXYZ CTEST1 and so on from a libname file The domains pattern test will match only the...

Page 259: ...r log files SPD Server searches the specified log path directories for spdslog files When spdslog files are found SPD Server checks them for aging criteria You specify the aging criteria which tells S...

Page 260: ...vdomain setting disables logging for resource cleanup from LIBNAME domain directories This is the default setting for this variable domains dompat1 dompat2 Use the domains option to specify a domain...

Page 261: ...ile The following command will do this spdsclean libnamefile opt spds45 site libnames parm domains trial9 ujoe To log the domains processed and the files deleted from each just add the verbose option...

Page 262: ...rmfile opt spds45 site spdsserv parm libnamefile opt spds45 site libnames parm logdir opt spds45 log verbose Glossary ACL Files When you create SPD Server Access Control Lists ACLs hidden ACL files ar...

Page 263: ...ess terminates unexpectedly while you are creating a new file the residual temporary files remain in the LIBNAME domain directories These temporary files are named with a leading character which preve...

Page 264: ...258 Chapter 20 SPD Server Directory Cleanup Utility...

Page 265: ...test images that will not interfere with a pre existing production SPD Server environment The debugging tools are for use with SPD Server 4 5 running on SAS 9 2 The debugging tools are organized into...

Page 266: ...onment variable If ALTPATH does not find the ALTBINPATH option specified in the file a login failure error is issued The ALTPATH option is useful for SPD Server administrators who want to load a non p...

Page 267: ...ORDFLAGS 1 2 4 1 launches a debug image for the SPD Server record locking process If RECORDFLAGS 1 is not specified the default setting launches the optimized image 2 loads the image that is specified...

Page 268: ...xpected exception is encountered while running bit flag 4 To perform all three actions submit the sum of the bit flags 1 2 4 7 as the argument for your RECORDFLAGS option statement RECORDFLAGS 7 262 C...

Page 269: ...e your feedback If you have comments about this book please send them to yourturn sas com Include the full title and page numbers if applicable If you have comments about the software please send them...

Page 270: ......

Page 271: ...one source for accurate timely and reliable information SAS documentation We currently produce the following types of reference documentation to improve your work experience Online help that is built...

Search results

Summary of Contents for Scalable Performance Data Server 4.5

Reviews:

Related manuals for Scalable Performance Data Server 4.5

Brands by name

Popular brands

SAS Scalable Performance Data Server 4.5, Administrator'S Manual

Search results

Summary of Contents for Scalable Performance Data Server 4.5

Reviews:

Related manuals for Scalable Performance Data Server 4.5

Brands by name

Popular brands