HP NonStop ODBC Server Reference Manual Abstract This manual describes the HP NonStop™ ODBC Server, a product that allows applications written for the Microsoft ODBC interface, Microsoft SQL Server interface, and Sybase SQL Server interface to access an HP NonStop SQL/MP database.
Document History Part Number Product Version Published 138789 NonStop ODBC Server 2.0 April 1998 424092-001 NonStop ODBC Server 2.0 September 1999 426087-001 NonStop ODBC Server 2.0 May 2000 429151-001 NonStop ODBC Server 2.0 January 2001 429151-002 NonStop ODBC Server 2.
HP NonStop ODBC Server Reference Manual Glossary Index What’s New in This Manual xix Manual Information xix New and Changed Information About This Manual xxi Audience xxi Organization of This Manual Related Manuals xxiii Notation Conventions xxv Examples Figures xix xxi 1.
2. Architecture Overview (continued) Contents 2. Architecture Overview (continued) Server-Side View 2-13 Major Components 2-13 Message Flows 2-16 NonStop ODBC Server Objects and Relationships Name Mapping 2-22 NonStop ODBC Server Catalogs 2-22 NonStop ODBC Server 2-27 SQL Communication Subsystem 2-30 NOSUTIL 2-37 NonStop SQL/MP Execution 2-40 Stored Procedure Execution 2-42 2-19 3.
3. CORE SQL Language (continued) Contents 3.
3. CORE SQL Language (continued) Contents 3. CORE SQL Language (continued) Positioned UPDATE 3-61 Searched UPDATE 3-62 Example 3-63 CORE SQL Compared With NonStop SQL/MP 3-63 4.
4. Transact-SQL Language (continued) Contents 4.
4. Transact-SQL Language (continued) Contents 4.
4. Transact-SQL Language (continued) Contents 4.
5. Stored Procedures (continued) Contents 5.
6. Using Pass-Through Mode (continued) Contents 6. Using Pass-Through Mode (continued) Using SELECT Statements 6-14 Using PREPARE and EXECUTE Statements 6-14 Examples of Using Pass-Through Mode 6-15 Creating or Altering Catalogs 6-15 Creating or Altering Tables 6-16 Creating or Altering Views 6-18 Managing Resource Accounting 6-19 Managing Indexes 6-19 Locking Data 6-20 Using PREPARE, EXECUTE, and RELEASE Statements Managing Traces 6-20 6-20 7.
7. Managing Customized Catalogs (continued) Contents 7.
Contents 8. HP NonStop ODBC Server Mapping Tables (continued) 8.
8. HP NonStop ODBC Server Mapping Tables (continued) Contents 8. HP NonStop ODBC Server Mapping Tables (continued) SYSMESSAGES Catalog View (ZVSMSG) 8-66 SYSOBJECTS Catalog Table (ZVUOBJ) 8-67 SYSPROTECTS Catalog View (ZVUPROT) 8-69 SYSTYPES Catalog View (ZVUDT) 8-70 SYSUSERS Catalog View (ZVUUS) 8-71 Actions That Affect the Mapping Tables 8-72 9. UNIX Notes Building an Executable 9-1 DB-LIBRARY Functions and Macros DBIORDESC 9-2 DBIOWDESC 9-3 DBRBUF 9-4 9-1 A.
B. Summary of Support for SQL Server Features (continued) Contents B. Summary of Support for SQL Server Features (continued) Owner Names B-9 Case Sensitivity B-9 Joins B-9 NULL Values B-10 Parameters B-10 Y2K Implicit Century Conformance B-11 Search Conditions B-11 Statements B-11 Stored Procedures B-17 System Procedures B-17 System Tables B-20 System Tables in All Databases B-20 System Tables in the Master Database B-25 Variables B-26 Wild-Card Characters B-28 C. Summary of Support for ODBC 2.
Figures Contents Figures Figure 1-1. Figure 1-2. Figure 1-3. Figure 1-4. Figure 2-1. Figure 2-2. Figure 2-3. Figure 2-4. Figure 2-5. Figure 2-6. Figure 2-7. Figure 2-8. Figure 2-9. Figure 2-10. Figure 2-11. Figure 2-12. Figure 2-13. Figure 2-14. Figure 2-15. Figure 2-16. Figure 2-17. Figure 2-18. Figure 2-19. Figure 2-20. Figure 2-21. Figure 2-22. Figure 2-23. Figure 2-24. Figure 2-25. Figure 2-26. Figure 2-27. Figure 2-28. Figure 2-29. Figure 2-30.
Figures (continued) Contents Figures (continued) Figure 2-31. Figure 2-32. Figure 2-33. Figure 3-1. Figure 4-1. Figure 5-1. Figure 5-2. Figure 5-3. Figure 5-4. Figure 7-1. Figure 7-2. Figure 8-1. Figure 8-2. Figure E-1.
Tables (continued) Contents Tables (continued) Table 3-9. Table 4-1. Table 4-2. Table 4-3. Table 4-4. Table 4-5. Table 4-6. Table 4-7. Table 4-8. Table 4-9. Table 4-10. Table 4-11. Table 4-12. Table 4-13. Table 4-14. Table 4-15. Table 4-16. Table 4-17. Table 6-1. Table 6-2. Table 6-3. Table 6-4. Table 7-1. Table 7-2. Table 7-3. Table 7-4. Table 7-5. Table 7-6. Table 7-7. Table 7-8. Table 7-9. Table 7-10. Table 7-11. Table 8-1. Table 8-2.
Tables (continued) Contents Tables (continued) Table 8-3. Table 8-4. Table 8-5. Table 8-6. Table 8-7. Table 8-8. Table 8-9. Table 8-10. Table 8-11. Table 8-12. Table 8-13. Table 8-14. Table 8-15. Table 8-16. Table 8-17. Table 8-18. Table 8-19. Table 8-20. Table 8-21. Table 8-22. Table 8-23. Table 8-24. Table 8-25. Table 8-26. Table 8-27. Table 8-28. Table 8-29. Table 8-30. Table 8-31. Table 8-32. Table 8-33. Table 8-34. Table 8-35. Table 8-36. Table 8-37. Table 8-38.
Tables (continued) Contents Tables (continued) Table 8-39. Table 8-40. Table 8-41. Table 9-1. Table A-1. Table A-2. Table A-3. Table A-4. Table A-5. Table B-1. Table B-2. Table B-3. Table B-4. Table B-5. Table B-6. Table B-7. Table B-8. Table B-9. Table B-10. Table C-1. Table D-1.
What’s New in This Manual Manual Information HP NonStop ODBC Server Reference Manual Abstract This manual describes the HP NonStop™ ODBC Server, a product that allows applications written for the Microsoft ODBC interface, Microsoft SQL Server interface, and Sybase SQL Server interface to access an HP NonStop SQL/MP database.
What’s New in This Manual • • • • • • • • • New and Changed Information Under USERCAT INSTALL Statement on page 7-20, the EMPTY option is added. Under Effects of USERCAT INSTALL on page 7-21, a bullet point is added describing what will happen if the EMPTY option is specified while installing the catalog.
About This Manual This manual describes the HP NonStop ODBC Server, which allows applications written for the Microsoft ODBC interface, Microsoft SQL Server interface, and Sybase SQL Server interface to access a HP NonStop SQL/MP database. ODBC clients are supported on Windows workstations; SQL Server clients are supported on DOS, Windows, OS/2, and UNIX workstations. The NonStop ODBC Server runs on the HP server, although one of its components, the HP NonStop ODBC/MP Driver, runs on the client workstation.
About This Manual • • • • • • • • • • • • Organization of This Manual Section 2, Architecture Overview, shows how the NonStop ODBC Server reconciles the architecture of NonStop SQL/MP with the architecture of ODBC or SQL Server.
Related Manuals About This Manual • • • Appendix E, Changing Passwords in a Three-Tier Environment, describes how to change a password in an ODBC configuration in which the ODBC/MP driver is invoked by an application server on behalf of a user application. Appendix F, Creating Partitioned Tables, describes how to create a partitioned table using a Partition Overlay Specification (POS) Template and the CREATE TABLE statement.
Other HP Documentation About This Manual • NonStop SQL/MP Messages Manual—lists the NonStop SQL/MP messages for all NonStop SQL/MP components. It also includes file-system messages that can be issued only on NonStop SQL/MP objects If you are unfamiliar with NonStop SQL/MP, the Introduction to NonStop SQL/MP provides feature, functional, and conceptual overviews.
Notation Conventions About This Manual These manuals are included with a Sun workstation and are available from Sun Microsystems. Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2.
General Syntax Notation About This Manual each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Notation for Messages About This Manual Line Spacing. If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]… !i and !o.
Notation for Management Programming Interfaces About This Manual lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned. For example: p-register process-name [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed.
Change Bar Notation About This Manual UPPERCASE LETTERS. Uppercase letters indicate names from definition files. Type these names exactly as shown. For example: ZCOM-TKN-SUBJ-SERV lowercase letters. Words in lowercase letters are words that are part of the notation, including Data Definition Language (DDL) keywords. For example: token-type !r. The !r notation following a token or field name indicates that the token or field is required. For example: ZCOM-TKN-OBJNAME !o. token-type ZSPI-TYP-STRING.
Change Bar Notation About This Manual HP NonStop ODBC Server Reference Manual—429151-002 xxx
1 Introduction This section introduces the HP NonStop ODBC Server.
Application Interface Introduction Figure 1-1. Workstation-Based Tools Accessing NonStop SQL/MP Using the NonStop ODBC Server LAN Clients Running Workstation-Based Tools NonStop System Running NonStop ODBC Server and NonStop SQL/MP "Off-the-Shelf" Application NonStop ODBC Server NonStop SQL/MP Special-Purpose Application Application-Development Tool VST001.
DB-LIBRARY Interface Introduction Figure 1-2 shows the relationships among an application, the ODBC interface, ODBC drivers, and the servers with which they relate. Figure 1-2. ODBC Interface Application O D B C HP Driver Other Driver NonStop ODBC Server Other Server VST002.vsd A number of different ODBC drivers (code that enables connection to a server), each created for a different server, are supplied by different manufacturers.
How the NonStop ODBC Server Works Introduction How the NonStop ODBC Server Works A workstation-based application program written to run with ODBC contains calls to the ODBC API that the NonStop ODBC/MP Driver packages for transmission to the NonStop ODBC Server. An application written to run with SQL Server contains calls to the DBLIB API to perform similar functions.
Users of the NonStop ODBC Server Introduction Users of the NonStop ODBC Server The people who use the NonStop ODBC Server are system managers, administrators, and end users. Table 1-1 lists the types of NonStop ODBC Server users and describes their primary interaction with the NonStop ODBC Server. Table 1-1.
DOS/Windows Workstation Requirements Introduction DOS/Windows Workstation Requirements Table 1-3 lists the hardware and software required to run the NonStop ODBC Server with a Windows workstation using ODBC; Table 1-4 lists the hardware and software required to run the NonStop ODBC Server with a DOS/Windows workstation using DBLIBRARY. Table 1-3.
UNIX Workstation Requirements Introduction Note. The NonStop ODBC server negotiates to a version 4.6 (or less) driver for clients using DBLIB. Therefore, a DBLIB that requires a driver version greater than 4.6 is not supported. UNIX Workstation Requirements Table 1-5 lists the hardware and software required to run the NonStop ODBC Server with a UNIX workstation. Table 1-5.
Supported and Unsupported ODBC Features Introduction The NonStop ODBC Server supports all of the core-level statements (CORE SQL) and most of the extended syntax. Table 1-6 summarizes the ODBC SQL features and indicates whether the NonStop ODBC Server supports the feature and where to find further information on the feature. For a complete list of NonStop ODBC Server support, see Appendix A, Summary of Support for ODBC Features. Table 1-6.
Supported and Unsupported SQL Server Features Introduction Table 1-6. NonStop ODBC Server Support of ODBC Features (page 2 of 2) Feature Names Description of NonStop ODBC Server Support More Information The following types of names follow ODBC naming rules: Database names on page 1-8 column name correlation name database name index name procedure name table name username view name Usernames in this table Names on page 3-3 All names have special considerations.
Supported and Unsupported SQL Server Features Introduction Table 1-7. NonStop ODBC Server Support of SQL Server Features (page 1 of 4) Description of NonStop ODBC Server Support More Information Aggregate functions All aggregate functions are supported. Aggregates on page 4-31 Alias usernames Alias usernames are supported. ADD ALIAS on page 7-42 COMPUTE BY clause Not supported SELECT on page 4-82 Database names Database names are logical 60character identifiers.
Supported and Unsupported SQL Server Features Introduction Table 1-7.
Supported and Unsupported SQL Server Features Introduction Table 1-7. NonStop ODBC Server Support of SQL Server Features (page 3 of 4) Feature Names Description of NonStop ODBC Server Support More Information The following types of names follow SQL Server naming rules: Database names on page 1-8 column name correlation name index name table name variable name view name Owner names on page 1-12 in this table Names on page 4-5 Database and owner names, however, must follow special rules.
Supported and Unsupported SQL Server Features Introduction Table 1-7. NonStop ODBC Server Support of SQL Server Features (page 4 of 4) Description of NonStop ODBC Server Support Feature Transact-SQL statements Data Definition Language (DDL) statements, such as CREATE DATABASE and DROP TABLE, are supported. More Information Section 4, Transact-SQL Language Data Manipulation Language (DML) statements, such as SELECT and UPDATE, are supported.
Introduction Supported and Unsupported SQL Server Features HP NonStop ODBC Server Reference Manual—429151-002 1- 14
2 Architecture Overview This section gives a high-level architectural overview of the HP NonStop ODBC Server, showing how the components of the NonStop ODBC Server interact to provide connectivity to HP NonStop SQL/MP databases for client applications that issue SQL statements from either Microsoft’s ODBC interface or SQL Server’s DBLIB interface.
Architecture Overview Figure 2-1. NonStop ODBC Server Generalized Architecture PCs/Workstation s Windows Application O D B C HP NonStop System NonStop ODBC/MP Driver NonStop SQL/MP NonStop ODBC Server and Utilities D DOS/ B UNIX L Application I B Pathway Servers DBLIB Legend = NonStop ODBC Server Components VST005.vsd ; Following subsections cover background information, the client-side view, and the server-side view.
Background Architecture Overview Background The following gives background information on the topics of client/server operations, gateways, and NonStop ODBC Server terminology. Client/Server Applications An application program on a NonStop server can use the services of NonStop SQL/MP by means of an embedded SQL interface. In this type of interface, the NonStop SQL/MP statements are written in the source program intermixed with the regular statements of the programming language (COBOL or C, for example).
SQL Gateways Architecture Overview Figure 2-3. Application Program Interface (API) for SQL Server PC or Workstation D PC or B Workstation L Application I B LAN PC, UNIX, HP NonStop or Other System TCP/IP TCP/IP SQL Server DBMS VST007.vsd The NonStop ODBC Server is a HP product that provides connectivity between certain SQL client applications (actually between certain SQL APIs) and NonStop SQL/MP. Figure 2-4.
Client-Side View Architecture Overview Figure 2-5. The NonStop ODBC Server as a SQL Gateway HP Server PC or Workstation O P D C Program B C Library NonStop ODBC Server ODBC SQL Environment SQL Gateway NonStop SQL/MP NonStop SQL/MP Environment VST009.vsd The function of a gateway is to translate all the objects in the SQL environment (SQL syntax, data types, error codes, SQL catalog structures, and so on) between the two sides.
Types of Clients Architecture Overview Types of Clients The NonStop ODBC Server supports two types of clients, as shown in Figure 2-6. Figure 2-6. Two Types of Clients Supported by the NonStop ODBC Server Clients Servers HP Server PC, Workstation, ... 1 SQL Server Application NonStop ODBC Server DBLIB Uses TRANSACT-SQL Supports TRANSACT-SQL PC 2 ODBC Application NonStop SQL/MP HP Server NonStop ODBC Server HP Driver Uses CORE SQL NonStop SQL/MP Supports CORE SQL VST010.
Databases and Datasources Architecture Overview The advantages of supporting both clients with one architecture are simplicity of design and the opportunity to provide shared objects for the various clients. Note that clients of just one type are unaffected by NonStop ODBC Server extensions. For example, if a system has only TSQL clients, they do not recognize names over 32 characters long.
Databases and Datasources Architecture Overview a vendor-neutral term, avoiding the need to distinguish among connecting to a database, connecting to a server that accesses a database, and connecting to anything else. Figure 2-8. Database View for an ODBC Client Datasource CUSTOMERS SQL Catalog PEOPLE CUSTOMERS JOBS ORDERS Client Application VST012.vsd When an ODBC client connects to a datasource, the ODBC driver manager on the PC accesses a special ODBC.
Databases and Datasources Architecture Overview The NonStop ODBC Server provides a database architecture compatible with both ODBC and DBLIB views, as well as being compatible with the NonStop SQL/MP architecture. With NonStop SQL/MP, each node has one NonStop SQL/MP system catalog and an arbitrary set of regular NonStop SQL/MP catalogs. A SQL object on a node can be registered in any catalog on that node. See Figure 2-10. Figure 2-10. SQL Catalog View for a NonStop SQL/MP User Node \WEST Subvol $SYSTEM.
Databases and Datasources Architecture Overview Figure 2-11. Database View for a NonStop ODBC Server Client HP System Client Database View PC Database N_DATA_CUST DBLIB Application SQL Catalog ORDERS \N.$DATA.CUST Customized NonStop SQL/MP Catalog Datasource CUSTOMERS NonStop ODBC Client Application SQL Catalog ORDERS ODBC.INI FIle Datasource CUSTOMERS TCP/IP addr = 34... Database = N_DATA_CUST VST015.
Connections Architecture Overview Connections A client application uses the NonStop ODBC Server by connecting to a particular NonStop ODBC server, issuing SQL statements, and disconnecting, as shown in Figure 2-12. Figure 2-12.
Connections Architecture Overview Figure 2-13. Client Activities in Using the NonStop ODBC Server Configure connections to the NonStop ODBC Server (Run application) Further configure the connections. Connect to the NonStop ODBC Server, issue SQL statements, further configure the connections. • Determine which databases to use. • Determine the type of usage. • Configure the connections (set TCP/IP addresses, and so on). • Alter the configuration – Switch to another database.
Server-Side View Architecture Overview Figure 2-14. NonStop ODBC Server Client Connection Process SQLConnect (SALES, Smith, argh) HP O Client D NonStop Application B ODBC/MP Driver C USERNAME = Smith PASSWORD = argh DATABASE = N_DATA_SALES Datasource SALES SQL Catalog PEOPL E SALESPEOPLE JOB S REGIONS VST0018.vsd In this figure, the client issues a SQLConnect ODBC CLI call that connects the client to the SALES datasource with username SMITH and password “argh.
Major Components Architecture Overview Figure 2-15. Major Components of the NonStop ODBC Server on a NonStop Server NOSCOM NOSUTIL NonStop ODBC Mapping and Configuration Tables Name Mapping and Configuration Data Communication Processes NonStop SQL/MP Execution SCS NonStop ODBC Server SP Execution SPELIB Pathway Server Class SPELIB VST019.vsd Other processes used by NonStop SQL/MP, such as the SQL Compiler, DP2, and TS/MP, are also involved.
Major Components Architecture Overview This subsystem handles all communication with the client workstation and manages the NonStop ODBC servers. To avoid costly process start-ups, SCS maintains server classes of previously started NonStop ODBC servers in an available list. Each client connection results in the creation of an association between the client network ID and an assigned available NonStop ODBC server.
Message Flows Architecture Overview connected to two different databases. Clients 2 and 3 use different SCS processes and are both connected to database N1_V2_S2. Note that in this example the clients all access the same set of databases by the same names, without regard to the SCS used (of course, if they are ODBC clients, they could access different datasource names that lead to the same database). Figure 2-16.
Message Flows Architecture Overview Figure 2-17. Message Flow for a Client Connection 4 NOSUTIL NSSQL 3 1 Client Connecting 9 5 2 Communication Processes 6 NonStop ODBC Server SCS 8 NonStop ODBC Mapping and Configuration Tables 7 VST021.vsd 1, 2 Client connection causes a message to be sent to the designated SCS. The message contains a username, a password, and perhaps a database name. 3 SCS recognizes the connect message and starts a thread for it.
Message Flows Architecture Overview Figure 2-18. Message Flow for Client SQL Execution NonStop ODBC Mapping and Configuration Tables 4 1 2 Communication Processes Client Executing 8 3 NonStop ODBC Server SCS 7 NonStop SQL/MP 6 5 NonStop SQL/MP NonStop SQL/MP Objects VST022.vsd 1, 2 Client executes SQL statement at the API; a message is sent to SCS containing the statement. 3 SCS forwards the message, without examining its contents, to the appropriate NonStop ODBC server.
NonStop ODBC Server Objects and Relationships Architecture Overview body of the procedure. In addition, Step 5 is a ServerClass_Send to the server class. The other steps remain the same. NonStop ODBC Server Objects and Relationships In addition to enabling clients to connect to and use a database, the NonStop ODBC Server also supports configuring many aspects of the connection for performance and management reasons. Clients connect under a username; all the client’s attributes are based on this name.
NonStop ODBC Server Objects and Relationships Architecture Overview NonStop ODBC server configuration A set of attributes that describe the NonStop ODBC server process creation values, default values, program definition, server counts, and performance and diagnostic settings. Resource governing information A set of governing policies that define what tests are to be made on resource usage and what action is to be taken if resource usage exceeds preset limits.
NonStop ODBC Server Objects and Relationships Architecture Overview Figure 2-19.
Name Mapping Architecture Overview The following is an example involving usernames. There are several alias usernames by which a client can connect. The name “SQL_JONES” illustrates using essentially the same name on both the client and server side (which is useful if the named person does some work on a PC and some work on the NonStop server). The other “Joneses” represent two aliases that also map to the logical username “SQL_JONES.” The difference is that each alias is mapped to a different profile.
NonStop ODBC Server Catalogs Architecture Overview function. There may be zero or more NonStop ODBC customized user catalogs on a system. Each customized user catalog is registered in one or more NonStop ODBC system catalogs. The NonStop ODBC Server system catalog, which must exist if there are any NonStop ODBC Server objects on the node, includes a NonStop ODBC Server user catalog. Figure 2-20 shows an example of NonStop ODBC Server catalogs. Subvolume $SYSTEM.
NonStop ODBC Server Catalogs Architecture Overview NonStop ODBC Server Catalog Schema The following are the tables in a NonStop ODBC Server user catalog: Table Usage ZNUDT Map data types ZNUIX Map index names ZNUMTRX Log resource accounting data ZNUOBJ Map table and view names ZNUPCOL Map stored procedure column names ZNUPROC Map stored procedure names ZNUQST Log query status data ZNUTRA* Log trace data * Template only; user can define actual table name.
NonStop ODBC Server Catalogs Architecture Overview Table Usage ZNSUMAP Map login username and SCS to server class ZNSUS Map logical usernames to Guardian usernames ZNSVALUE Help support SYSVALUES for TSQL ZNUx All tables of a NonStop ODBC Server user catalog Client Catalogs A previous description showed how the NonStop ODBC Server catalogs are used by the NonStop ODBC server to map client names to NonStop SQL/MP names.
NonStop ODBC Server Catalogs Architecture Overview Figure 2-21. Support of TSQL Catalog SYSOBJECTS SYSOBJECTS ZVUOBJ CRDATE NAME TYPE ORDERS U USERSTAT 1993-10-11:12:02.23455 ZNUOBJ -1 TABLES T_OBJNAME T_OBJTYPE N_OBJNAME TABLENAME ORDERS U $V.S.MYT $V.S.MYT TYPE TA CREATETIME 8213545332 Join on "=" VST025.vsd Think of ZNUOBJ as an extension of TABLES. ZNUOBJ has a column N_OBJNAME (NonStop SQL/MP object name) that contains the same entries as TABLENAME in TABLES.
NonStop ODBC Server Architecture Overview ODBC Catalogs Unlike database vendors, ODBC does not define a SQL catalog schema. Instead, the ODBC CLI defines functions intended to extract catalog information. The ODBC functions cover tables, columns, index columns, stored procedures, and stored procedure columns (the parameters of the procedures). Each CLI function has parameters that serve to qualify on object names.
NonStop ODBC Server Architecture Overview Figure 2-22. Life Cycle of an NonStop ODBC Server Process Idle Busy Idle Busy Idle Timeline SQL Activity SCS creates server. Client A connects . Initialize and add to free pool of servers. SQL Activity Client A disconnects. Client B connects . NonStop ODBC server retains cache of configuration information and SQL statements. Client B disconnects. SCS stops server. Connection is extra fast if Client B uses same configuration as Client A. VST026.
Architecture Overview NonStop ODBC Server As the NonStop ODBC server obtains results (such as rows fetched for a cursor), it starts sending the results to the client. Based on this behavior, the client library can do a variety of things: it could wait for all results before returning to the application, it could return results as available, it could even support asynchronous calls. The NonStop ODBC/MP driver returns a result row as soon as it receives it from the NonStop ODBC server.
Architecture Overview SQL Communication Subsystem An important translator task is to map certain client names into NonStop SQL/MP names; the translator calls a name mapping module to do this. The name mapping module uses NonStop SQL/MP SELECT statements to read the NonStop ODBC Server catalog tables for the needed map entries. For performance, the name mapping module caches the map entries in memory.
Architecture Overview SQL Communication Subsystem SCS manages server classes, much as Pathway does. SCS starts and stops servers according to configuration information. This task is done solely for performance, to avoid excessive NEWPROCESS commands and SQL compilation for client connections. • SCS provides a management interface. SCS provides interfaces to the Subsystem Control Facility (SCF) and the Event Management Service (EMS) to help administrators manage a NonStop ODBC Server system.
Architecture Overview SQL Communication Subsystem Figure 2-26. Interprocess Communication Between SCS and NonStop ODBC Server SCS NonStop ODBC Server NEWPROCESS Open Open Send Receive Reply WRITEREAD Initiate [ Data ] [ Data ] Reply WRITEREAD . . . Close Initiate Read Write Close VST030.vsd A client request is one logical message, but it can be sent in multiple 512-byte transport messages. As SCS receives messages from the client, it forwards them without change to the NonStop ODBC server.
Architecture Overview SQL Communication Subsystem number of free servers. There is also a special DEFAULT server class to handle clients that are not configured for a specific server class. Figure 2-27 shows two server classes, each with two NonStop ODBC servers. Two clients are connected, one to each server class. Figure 2-27. SCS Management of Server Classes NonStop ODBC Server processor = 1 SC = CLASS1 (Client 1) Client 1 SCS Client 2 SCS Configuration Server Class CLASS1 Guardian Name = G1.
Architecture Overview SQL Communication Subsystem Figure 2-28. Initializing a NonStop ODBC Server NonStop ODBC Server Mapping and Configuration Tables 2 1 Start NonStop ODBC Server Process Use SCS User ID for NonStop ODBC Server Process 3 NonStop ODBC Server SCS Read PROFILE 4 Initialize 5 6 Place NonStop ODBC Server in Free Pool in Server Class "I'm ready" VST032.
Architecture Overview SQL Communication Subsystem Server Connections As stated previously, a connection involves a DBLIB or ODBC client initiating a connection with a NonStop ODBC server. A connection is a significant event in that a number of things happen involving authentication, NonStop ODBC server selection, and attribute setting (much of what happens depends on configuration settings). When connecting, a client application supplies a username, a password, and an optional database name.
Architecture Overview SQL Communication Subsystem Figure 2-30. SCS Determination of Server Class 4 SELECT NOSUTIL USERNAME 3 5 NonStop ODBC Server Catalog ZNSALT ZNSUMAP Server Class Name Guardian username SCS VST034.vsd SCS maintains a cache of username and server class name relationships that it checks before contacting NOSUTIL. If the username is found in the cache, Steps 3, 4, and 5 in Figure 2-30 are omitted.
Architecture Overview NOSUTIL Figure 2-31. NonStop ODBC Server Processes Connection 6 USERNAME PASSWORD DATABASE SCS 7 6A NonStop ODBC Server From Server Class ZNSALT ZNSUS ZNSPROF ZNSTRA ZNSGOV ZNSCON VST035.vsd The NonStop ODBC server caches the connect-username—the Guardian name/profile name relationship it used for the most recent connection to it. If the new connection is under the same username, Step 6A in Figure 2-31 is omitted.
Architecture Overview NOSUTIL Catalog Integrity The NonStop SQL/MP tables that make up the NonStop ODBC Server catalogs include certain integrity constraints, as follows: • • • For example, the NonStop ODBC Server system catalog has a table, ZNSDB, that lists all customized NonStop SQL/MP catalogs supported for a given NonStop ODBC system catalog (that is, all NonStop ODBC Server user catalogs), and only those catalogs, on a NonStop node.
Architecture Overview • • • • • • • • • • • • • • NOSUTIL INSTALL/DEINSTALL/VALIDATE/REFRESH/UPGRADE the NonStop ODBC Server system catalog INSTALL/DEINSTALL/VALIDATE/REFRESH/UPGRADE a NonStop ODBC Server user catalog ADD/REMOVE/MODIFY alias and usernames ADD/REMOVE/MODIFY stored procedure names and attributes ADD/REMOVE/MODIFY profile and trace information ADD/REMOVE/MODIFY SQL Communication Subsystem configuration ADD/REMOVE/MODIFY system configuration defaults ADD/REMOVE/MODIFY server class definition
Architecture Overview NonStop SQL/MP Execution DATABASE, NOSUTIL creates the NonStop SQL/MP catalog (if it does not already exist) and customizes it. • By SCS SCS maintains one private NOSUTIL process that it uses to process connect operations and to get configuration information. It passes the connect message to NOSUTIL, which accesses the NonStop ODBC Server system catalog to map the login name and to determine the server class to use to obtain a NonStop ODBC server.
Architecture Overview NonStop SQL/MP Execution Figure 2-33. NonStop SQL/MP Execution in SQLCI and NonStop ODBC Server SQLCI NonStop ODBC Server NonStop SQL/MP SQLCI2 Extended Dynamic SQL (1600+ max) NonStop SQL/MP Dynamic SQL (20 max.) VST037.vsd SQLCI uses a separate SQLCI2 process for SQL statement execution, so a NonStop SQL/MP statement can be interrupted at any time (by stopping the SQLCI2 process). Therefore, a user can issue a Cancel request at any time.
Architecture Overview Stored Procedure Execution The basic caching algorithm follows: • Prepared statements Client-prepared DML statements are cached as is. • Execute-direct, with parameters Client execute-direct statements with parameters are cached as is. • Execute-direct, without parameters Client execute-direct statements without parameters are first parameterized, then cached.
3 CORE SQL Language Applications used with the HP NonStop ODBC Server can contain CORE SQL statements. The NonStop ODBC Server accepts these CORE SQL statements, translates them to HP NonStop SQL/MP statements, and sends the statements to NonStop SQL/MP. The NonStop ODBC Server can also execute some NonStop SQL/MP statements directly.
CORE SQL Language Unsupported ODBC Features A CORE SQL statement is submitted to NonStop SQL/MP as follows: 1. An application program issues an ODBC call that sends a CORE SQL statement to the NonStop ODBC Server. 2. The NonStop ODBC Server translates the CORE SQL statement to a NonStop SQL/MP statement and sends the statement to NonStop SQL/MP. 3. NonStop SQL/MP processes the statement and returns NonStop SQL/MP data (or a diagnostic message) to the NonStop ODBC Server. 4.
CORE SQL Language Names Names When using the NonStop ODBC Server, the names you specify for objects such as databases, tables, indexes, and columns must be in CORE SQL syntax; however, there are restrictions on some names, because the NonStop ODBC Server maps some names to Guardian names.
CORE SQL Language Names Index-name [ [database.]owner. ] [ database.. ] index-identifier Procedure-name [ [database.]owner. ] [ database.. ] procedure-identifier Column-name [ {table-name }. ] [ {correlation-name} ] column-identifier In the table-name shown above, table-identifier can be either a basetable-identifier or a view-table-identifier. Table 3-1 summarizes name format and how the NonStop ODBC Server handles names. Table 3-1.
CORE SQL Language Names Table 3-2.
CORE SQL Language Names Database Names A logical database name can be any valid NonStop SQL/MP identifier: You must separate the parts of the name with underscores (_). Considerations for database names are as follows: • • Each portion of the name begins with a letter and consists of letters and digits.
CORE SQL Language • Names You specify an owner name as part of an index, table, view, or stored procedure name as follows: [ [database.]owner.] ] [ database.. ] object If you include the database name but omit the owner name, you must include an extra period to show the omission. • All object-name references are fully qualified before the SQL statement is executed. If you do not specify an owner name, the logical username established at connection time is used to qualify the object.
CORE SQL Language Data Types Note. If Safeguard is in effect for the system and the username is an alias, the NonStop ODBC server always uses the alias name for the logon. If the username is not an alias, the NonStop ODBC server uses the Guardian name for the logon. If Safeguard is not in effect, the NonStop ODBC server always uses the Guardian name for the logon.
CORE SQL Language Data Types Creating Objects and Data When you create objects and data, you specify CORE SQL data types and values. The NonStop ODBC Server handles the data type or value in one of two ways: • • Fully supports it—the data type or value is processed as you input it. Converts it—the data type or value is converted to a corresponding NonStop SQL/MP data type or value. Table 3-3 summarizes the CORE SQL data types, the corresponding NonStop SQL/MP data types, and the conversion information.
CORE SQL Language Data Types ODBC describes the minimum limit that must be met by any conformant ODBC implementation, and implements a call interface by which an application can detect the actual implementation-defined limit. The NonStop ODBC Server satisfies each of the ODBC core-level minimums, and in most cases exceeds those minimums. For more information, see the following documents: For Information About See CORE SQL data types Microsoft ODBC 2.
CORE SQL Language Escape Clauses Table 3-4. Conversion of NonStop SQL/MP Data Types to CORE SQL Data Types (page 2 of 2) NonStop SQL/MP Data Type Corresponding CORE SQL Data Type TIMESTAMP TIMESTAMP VARCHAR VARCHAR Others CHAR or VARCHAR, as appropriate DATETIME data can be converted to DATE, TIME, or TIMESTAMP, depending on the range of the qualifier.
CORE SQL Language Escape Clauses Note. The braces ( { and } ) in the preceding syntax do not follow the standard syntax conventions indicating choice; they are actual characters you must enter.
CORE SQL Language Escape Clauses Escape Clauses for Scalar Functions When you include any scalar function (described under Functions on page 3-14) in a CORE SQL statement, the escape clause used has the following format: --*( VENDOR(Microsoft), PRODUCT(ODBC) FN scalar-function )*-or { FN scalar-function } FN specifies that what follows is the invocation of a scalar function.
CORE SQL Language Functions Escape Clauses for Executing Stored Procedures ODBC uses the following form of escape clause for executing stored procedures: --*( VENDOR(Microsoft),PRODUCT(ODBC) [ ?= ] CALL procedure-name [ ( parameter [, ... ] ) ] )*-or { [ ?= ] CALL procedure-name [ ( parameter [, ... ] ) ] } CALL specifies that this is a call to a previously established procedure (a Pathway server class) for execution of that procedure.
CORE SQL Language Functions Table 3-5.
CORE SQL Language • • • Functions TIMESTAMPADD TIMESTAMPDIFF YEAR In all of the syntax descriptions that follow, date-expression or time-expression can be a date or time literal, a column name, or the result of another function. CURDATE The CURDATE function has the following syntax: CURDATE ( ) The CURDATE function returns the current date. CURTIME The CURTIME function has the following syntax: CURTIME ( ) The CURTIME function returns the current time.
CORE SQL Language Functions MINUTE The MINUTE function has the following syntax: MINUTE ( time-expression ) The MINUTE function returns the minute in time-expression as an integer. MONTH The MONTH function has the following syntax: MONTH ( date-expression ) The MONTH function returns the month in date-expression as an integer. NOW The NOW function has the following syntax: NOW ( ) The NOW function returns the current datetime as a timestamp value.
CORE SQL Language Functions interval is the interval to be added to the timestamp. It is one of the following INTERVAL keywords: • • • • • • • SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_MONTH SQL_TSI_YEAR Fractional seconds are expressed in millionths of a second (microseconds). integer-exp is the number of intervals to be added. It is the name of a column, the result of another scalar function, or a numeric literal to be added to timestamp-exp.
CORE SQL Language Functions interval is the interval by which timestamp-exp2 is greater than timestamp-exp1. Valid values of interval are the following keywords: • • • • • • • SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_MONTH SQL_TSI_YEAR Fractional seconds are expressed in millionths of a second (microseconds). timestamp-exp1 is the first timestamp expression in the comparison.
CORE SQL Language Functions For more information, see the following documents: For Information About See CORE SQL date functions Microsoft ODBC 2.
CORE SQL Language String Functions String Functions The NonStop ODBC Server supports the following CORE SQL string functions: • • • • • • • CONCAT LENGTH LOCATE LTRIM RTRIM SUBSTRING UCASE CONCAT CONCAT ( string-expression1, string-expression2 ) The CONCAT function returns a character string that is the result of concatenating string-expression2 to string-expression1.
CORE SQL Language String Functions LTRIM LTRIM ( string-expression ) The LTRIM function returns the characters of string-expression with any leading blanks removed. For example: SELECT * FROM employee WHERE { fn LTRIM(last_name) } = "Wang" RTRIM RTRIM ( string-expression ) The RTRIM function returns the characters of string-expression with any trailing blanks removed. See LTRIM for an example.
CORE SQL Language String Functions The DATABASE function returns the current database name. The current database reflects the user’s current session values. These values are set at the time the user session is first established, using values from the associated profile (ZNSPROF) and the user’s login request. The current values change during the course of a session when the user performs actions to set or reset the current environment.
CORE SQL Language Search Conditions For information about year to fraction data, see the NonStop SQL/MP Reference Manual. Converting a Character Column Value to a Datetime Value When converting a character column value to a datetime value, the format of the data in the column must be year to fraction 3. For example, if the START_DATE column in the following example is a character field, the data must be in the form year to fraction 3 (such as “1994-01-05:12:29:00.123”). select {fn convert (table1.
CORE SQL Language Expressions and Operators For more information about search conditions, see the X/Open CAE Specification. Expressions and Operators Use CORE SQL syntax in expressions when using the NonStop ODBC Server. The syntax of an expression is: operand [ arithmetic-operator operand [ arithmetic-operator operand ] ...
CORE SQL Language Expressions and Operators The syntax of a Boolean expression is as follows: expression comparison-operator [ ANY ] [ ALL ] expression expression [ NOT ] IN expression [ NOT ] EXISTS expression expression [ NOT ] BETWEEN expression AND expression expression [ NOT ] LIKE expression NOT expression LIKE expression expression IS [ NOT ] NULL NOT boolean-expression boolean-expression AND/OR boolean-expression [ NOT ] boolean-function Table 3-6 summarizes the supported CORE SQL operators.
CORE SQL Language Aggregates Aggregates The NonStop ODBC Server supports all of the CORE SQL aggregate functions (AVG, COUNT, MAX, MIN, and SUM). AVG The AVG aggregate function has the following syntax: AVG ( { DISTINCT column-name } { expression } ) The AVG function returns the average of the values in the column, in the same data type as the argument you supply. If you specify DISTINCT, the AVG function calculates the result after excluding duplicate and null values of the argument.
CORE SQL Language Wild-Card Characters The MAX function returns the largest value in the column, in the same data type as the argument you supply. If you specify DISTINCT, the MAX function calculates the result after excluding duplicate and null values of the argument. If you do not specify DISTINCT, duplicate values are retained; expression must contain at least one column-name and must not contain any aggregate functions.
CORE SQL Language CASE Expression Table 3-7. NonStop ODBC Server Support of Wild-Card Characters CORE SQL Wild-Card Character NonStop ODBC Server Support % x _ x Character comparisons are case sensitive. For example, the following comparison would locate the string “sanitary” but not the string “San Francisco.
CORE SQL Language CASE Expression expression-1 ... expression-n specifies a value associated with each result-expression. If the value of an expression in a WHEN clause matches case-expression, then simple CASE returns the associated result-expression value. If there is no match, the CASE expression returns the value expression specified in the ELSE clause, or NULL if the ELSE value is not specified. result-expression-1 ...
CORE SQL Language CORE SQL Statements END FROM employee For more information about the CASE expression, see the NonStop SQL/MP Reference Manual. CORE SQL Statements The SQL statements you can use with the NonStop ODBC Server are a subset of the ODBC CORE SQL statements. This section lists the supported CORE SQL statements and summarizes how each statement differs when used with the NonStop ODBC Server.
CORE SQL Language CORE SQL Statements Table 3-8. NonStop ODBC Server Support of SQL Statements (page 2 of 2) CORE SQL Statement NonStop ODBC Server Support DROP INDEX x Comments The following associated objects must be accessible: • • The underlying table Catalogs containing the description of the index DROP TABLE x Dependent views are automatically dropped, but you should drop them first because they are not dropped from the mapping tables. Use REFRESH afterward.
CORE SQL Language ALTER TABLE Table 3-9. Supported CORE SQL Statements by Type (page 2 of 2) CORE SQL Statement Description Corresponding NonStop SQL/MP Statement DROP INDEX Removes an index. DROP INDEX DROP TABLE Removes a table, along with all data, indexes, dependent views, and permission specifications for that table. DROP TABLE DROP VIEW Removes views. DROP VIEW DELETE Removes rows from a table. DELETE INSERT Adds new rows to a table or view.
CORE SQL Language Example data-type the CORE SQL data type for the column. Data types are converted to NonStop SQL/MP data types. Data types are described under Language Elements on page 3-2. Example The following statement adds two columns to the DEPT table.
CORE SQL Language CALL CALL Use CALL to invoke a previously defined Pathway server class program—a stored procedure—to access the database. Stored procedures are described in detail in Section 5, Stored Procedures. Stored procedure execution is not included in ODBC CORE SQL but it is one of the elements of ODBC Extended SQL that are supported by the NonStop ODBC Server. Because it is an extension, the CALL statement must be entered as an escape clause.
CORE SQL Language Example Considerations • • If the procedure call supplies more values than the number of parameters defined in the procedure declaration, the excess values are ignored. No error or warning is issued. If default values are specified for parameters of a procedure (using the NonStop ODBC Server catalog utility ADD PROCEDURE), you can execute a stored procedure without giving a parameter value. The missing values can appear anywhere in the input parameter list.
CORE SQL Language CREATE INDEX CREATE INDEX Use CREATE INDEX to create indexes. The CREATE INDEX statement has the following syntax: CREATE [UNIQUE] INDEX index-name ON base-table-name ( column-identifier [ ASC | DESC ] [, column-identifier [ ASC | DESC ] ... ) UNIQUE specifies that two or more rows of the table cannot have the same values for the indexed columns. NonStop SQL/MP does not allow unique indexes on nullable columns. index-name specifies the index to create.
CORE SQL Language CORE SQL Compared With NonStop SQL/MP CORE SQL Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is CREATE INDEX. CREATE INDEX in CORE SQL differs from the NonStop SQL/MP implementation in the following ways: Feature In NonStop SQL/MP In CORE SQL Can you create partitioned indexes? Yes No, unless you use passthrough mode. Can you specify partition attributes? Yes No, NonStop SQL/MP defaults are taken unless you use passthrough mode.
CORE SQL Language CREATE TABLE CREATE TABLE Use CREATE TABLE to create new tables. The file security for the table is obtained from the default permissions associated with the user’s logon name. The CREATE TABLE statement has the following syntax: CREATE TABLE base-table-name ( column-element [, column-element ] ...
CORE SQL Language CREATE TABLE column-definition defines the characteristics of a single column in the table being created, as follows: column-identifier specifies a column in the table. The column-identifier must be unique for columns defined in the table. data-type is the CORE SQL data type for the column. Data types are converted to NonStop SQL/MP data types. Data types are described under Language Elements on page 3-2.
CORE SQL Language Examples PRIMARY KEY can appear not more than once in a CREATE TABLE statement. No other table constraints are supported.
CORE SQL Language Creation of Partitioned Tables Creation of Partitioned Tables You can create an SQL/MP partitioned table by using a partition overlay template with the CREATE TABLE statement. For details, see Appendix F, Creating Partitioned Tables. CORE SQL Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is CREATE TABLE.
CORE SQL Language CREATE VIEW CREATE VIEW Use CREATE VIEW to create a view. The file security for the view is obtained from the default permissions associated with the user’s logon name. The CREATE VIEW statement has the following syntax: CREATE VIEW table-name [ ( column-identifier [, column-identifier ] ... ) ] AS query-expression query-expression is: { query-expression UNION [ALL] query-expression } { ( query-expression ) } { query-specification } table-name identifies the view to create.
CORE SQL Language Examples The query-specification cannot include the ORDER BY clause, but it can include the OUTER JOIN and UNION clauses. For more information, see SELECT on page 3-54. Examples The following statement creates a view called MGRLIST: create view mgrlist (first_name, last_name, department) as select first_name, last_name, deptname from dept, employee where dept.manager = employee.empnum This view includes columns from the DEPT table and the EMPLOYEE table of the current database.
CORE SQL Language CORE SQL Compared With NonStop SQL/MP The view definition text indicates whether a view is a shorthand or a protection view. You can list this text by querying the NonStop SQL/MP VIEWS table, which resides on the subvolume of your NonStop SQL/MP catalog. The following SQLCI statement lists the view definition text for a view: select viewtext from views where viewname = "\TEST.$VOL2.PERSNL.EMPLIST"; In this example, both the VIEWS table and the EMPLIST view are in the subvolume \TEST.
CORE SQL Language Searched DELETE DELETE deletes the corresponding row of the base table from which the viewed table is derived. The table-name must be the (single) table referenced by the FROM clause of the query-specification that defines the result table of the cursor. If the specified table is audited, the positioned DELETE statement cannot be used unless a user transaction is in progress (the SQL_AUTOCOMMIT option of the ODBC function SQLSetConnectOption must be off).
CORE SQL Language Examples Examples The following statements delete one record from the EMPLOYEE table and one from the DEPARTMENT table: delete employee where last_name = "Hall" and first_name = "Dana" delete department where deptnum = 1030 CORE SQL Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is DELETE.
CORE SQL Language DROP TABLE DROP TABLE Use DROP TABLE to remove a table from a database, along with all data, indexes, and dependent views for that table. The DROP TABLE statement has the following syntax: DROP TABLE base-table-name [ CASCADE | RESTRICT ] base-table-name specifies the table to be dropped. The table name can be qualified with the database name and owner name. DROP TABLE also drops any indexes based on the base table.
CORE SQL Language Dropping Partitioned Tables The only way to clear the mapping table entries in this situation is to run the catalog utility statement REFRESH. For information about REFRESH, see Section 7, Managing Customized Catalogs. Dropping Partitioned Tables NonStop SQL/MP allows creation of partitioned tables. Each partition has an entry in the mapping table. Dropping a table that is partitioned causes all the partitions to be dropped, but the mapping entries will remain.
CORE SQL Language Dropping Dependent Views and Indexes Dropping Dependent Views and Indexes You should drop dependent views and indexes before dropping a view using the NonStop ODBC Server. When you drop a view using the NonStop ODBC Server, NonStop SQL/MP drops the view and all dependent views and indexes. The NonStop ODBC Server, however, does not clear the mapping table entries for the views or indexes. Note.
CORE SQL Language INSERT INSERT Use INSERT to add new rows to a table or a view. The INSERT statement has the following syntax: INSERT [ INTO ] table-name [ ( column-identifier [, column-identifier ] ... ) ] { VALUES ( insert-value [‚ insert-value ] ... ) } { query-specification } insert value is [ [ [ [ dynamic-parameter literal NULL USER ] ] ] ] table-name is the table into which the rows are inserted. The table-name can be qualified with the database name and owner name.
CORE SQL Language Example insert-value represents a value to be inserted into the corresponding column.
CORE SQL Language POWER POWER Use POWER to retrieve the value of a numeric expression to a specified power. The POWER scalar function has the following syntax: POWER (numeric-exp, integer-exp) numeric-exp is a number. It can be the name of a column, the result of another scalar function, or a numeric literal. integer-exp is the power to which the number is raised. It can be the name of a column, the result of another scalar function, or a numeric literal.
CORE SQL Language SELECT SELECT Use SELECT to retrieve rows from database tables or to access rows for use in updating columns in the same or another table. The SELECT statement has two forms: the standard SELECT and SELECT for update. Standard SELECT The SELECT statement has the following syntax: SELECT [ ALL ] [ DISTINCT ] select-item [, select-item ] ... FROM table-reference-list [ WHERE search-condition ] [ GROUP BY column-identifier | column-alias [,column-identifier | column-alias ] ...
CORE SQL Language Examples table-reference is: table-name [ correlation-name ] | [ outer-join-extension ] outer-join-extension is { OJ outer-join } | outer-join outer-join is: table-name [ correlation-name ] LEFT [OUTER] JOIN oj-table-reference ON search-condition oj-table-reference is: table-name [ correlation-name ] | outer-join sort-specification is: [ unsigned-integer [ column-identifier ] [ ASC | DESC ] ] Note.
CORE SQL Language Examples ALL/DISTINCT Clause The ALL/DISTINCT clause of the SELECT statement specifies the columns to be included in the result of the query. The clause has the following syntax: { ALL } { DISTINCT } select-item [, select-item ] ...
CORE SQL Language Examples FROM Clause The FROM clause of the SELECT statement specifies the tables and views used in the SELECT statement; it has the following syntax: table-reference is table-name [ correlation-name ] | outer-join-extension outer-join-extension is { OJ outer-join } | outer-join outer-join is table-name [ correlation-name ] LEFT [OUTER] JOIN oj-table-reference ON search-condition oj-table-reference is table-name [ correlation-name ] | outer- join table-name identifies a table or view us
CORE SQL Language Examples GROUP BY Clause The GROUP BY clause of the SELECT statement specifies the groups into which the table will be divided; it has the following syntax: GROUP BY column-identifier [,column-identifier ] ... column-identifier is the name of a column from one of the tables in the FROM clause.
CORE SQL Language SELECT for Update specifies that the result table is to be sorted in the same sequence as COL_1. column-name specifies a column in a table in the FROM clause; the sorting order of that column is used as the sorting order of the result table rows. For example: select col_1,col_2 from table_1 order by col_1 specifies that the result table is to be sorted in the same sequence as COL_1.
CORE SQL Language CORE SQL Compared With NonStop SQL/MP SELECT in CORE SQL differs from the NonStop SQL/MP implementation in the following ways: Feature In NonStop SQL/MP In CORE SQL Is BROWSE ACCESS locking mode available? Yes No, unless you use pass-through mode. Is the INTO clause available? Yes No, unless you use pass-through mode. Is the IN EXCLUSIVE MODE clause available? Yes No, unless you use pass-through mode. See the following discussion of “IN EXCLUSIVE MODE Clause.
CORE SQL Language UPDATE UPDATE Use UPDATE to change data in existing rows, either by adding new data or by modifying existing data. UPDATE has two forms: the positioned UPDATE and the searched UPDATE. The positioned UPDATE changes data in the row from which the current row of the active set of a cursor is derived. The searched UPDATE changes data in any rows that satisfy a search condition.
CORE SQL Language Searched UPDATE cursor-name specifies the cursor for which the active row is to be changed. The cursor specification for cursor-name must include the FOR UPDATE clause and the result table of the cursor must be updatable. Searched UPDATE The searched UPDATE statement has the following syntax: UPDATE table-name SET column-identifier = { expression } { NULL } [, column-identifier = { expression } { NULL } ] ...
CORE SQL Language Example Example The following example updates the salary of employee number 1002: update test_disk01_persnl..employee set salary = salary * 1.1 where empnum = 1002 CORE SQL Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is UPDATE. UPDATE in CORE SQL differs from the NonStop SQL/MP implementation in the following ways: Feature In NonStop SQL/MP In CORE SQL Can you specify the locking mode? Yes No, unless you use pass-through mode.
CORE SQL Language CORE SQL Compared With NonStop SQL/MP HP NonStop ODBC Server Reference Manual—429151-002 3- 64
4 Transact-SQL Language Applications used with the HP NonStop ODBC Server can contain Transact-SQL statements. The NonStop ODBC Server accepts these Transact-SQL statements, translates them to HP NonStop SQL/MP statements, and sends the statements to NonStop SQL/MP. The NonStop ODBC Server can also execute some NonStop SQL/MP statements directly.
Transact-SQL Language Using Transact-SQL Through the NonStop ODBC Server Figure 4-1. Relationships Among Transact-SQL, the NonStop ODBC Server, and NonStop SQL/MP Step 2: NonStop SQL/MP Statements Step 1: TRANSACT-SQL Statements Workstation Application With DBLIB Calls NonStop ODBC Server Step 4: Results in DBLIB Format HP Server NonStop SQL/MP Step 3: NonStop SQL/MP Results VST039.vsd A Transact-SQL statement is submitted to NonStop SQL/MP as follows: 1.
Transact-SQL Language Unsupported Features Unsupported Features As stated in “Notation Conventions,” unsupported SQL Server features are underlined in this manual. For example, in the following syntax, the ON DEFAULT clause is unsupported. CREATE DATABASE database-name ON { DEFAULT) } { database-device } [ = size ] Mapping of Table, View, and Index Names When you create tables, views, and indexes, you specify Transact-SQL identifiers for the table, view, or index names.
Transact-SQL Language Specifying the Database Location Table 4-1.
Transact-SQL Language Language Elements Language Elements Language elements differ for Transact-SQL and NonStop SQL/MP.
Transact-SQL Language Names Table 4-2 summarizes name format and how the NonStop ODBC Server handles names. Table 4-2. How the NonStop ODBC Server Handles Transact-SQL Object Names (page 1 of 2) Name Type Name Format How NonStop ODBC Server Handles Name Database name NonStop ODBCdatabase-name Maps it to \node.$volume.subvolume, which corresponds to a NonStop SQL/MP catalog. Owner name logical-username Maps it to group-name.user-name, which corresponds to a Guardian logon name.
Transact-SQL Language Names Table 4-2. How the NonStop ODBC Server Handles Transact-SQL Object Names (page 2 of 2) How NonStop ODBC Server Handles Name Name Type Name Format Correlation name Transact-SQL identifier Maps it to a NonStop SQL/MP simple name. Replaces $ and # with an underscore. Replaces a leading underscore with the letter u. Procedure name Transact-SQL identifier Maps it to a Pathway server class, but the user sees the name as identified to Transact-SQL.
Transact-SQL Language Names Table 4-3.
Transact-SQL Language • • Names Each portion of the name begins with a letter and consists of letters and digits. The maximum number of characters for each portion is as follows: Portion Characters Node 7 Volume 6 Subvolume 8 The NonStop ODBC Server maps the database name to a Guardian identifier as follows: \node.$volume.subvolume For example: corp_vol2_sales maps to \corp1.$vol2.sales For more information about Guardian names, see the Guardian User's Guide.
Transact-SQL Language Names at connection time is used to qualify the object. If the database name is missing, the default database indicated in the ZNSPROF profile record for the current logical username is used. Some additional considerations for alias usernames are as follows: • • • • They can be used in place of their associated logical usernames for logging on. They cannot be used for qualifying object names. They are system-wide, not restricted to a given database.
Transact-SQL Language Names For more information, see the following documents: For Information On See Transact-SQL usernames Microsoft SQL Server System Administrator’s Guide Name mapping Section 7, Managing Customized Catalogs Guardian logon name Guardian User’s Guide Table, View, and Index Names For table, view, and index names, you specify Transact-SQL identifiers. The NonStop ODBC Server maps these identifiers to eight-character Guardian file names.
Transact-SQL Language Data Types Variable Names For variable names, you specify Transact-SQL variable names, which are identifiers of no more than 29 characters preceded by an “at” symbol (@). The NonStop ODBC Server retains the variable name but does not map it because variable names are not used in NonStop SQL/MP. Data Types When creating and manipulating objects and data items, you must specify Transact-SQL data types.
Transact-SQL Language Data Types Table 4-4.
Transact-SQL Language Data Types The date can be any of the following: monthn { . } { - } { / } day { . } { - } { / } year year4 YYYYMMDD YYMMDD day { . } { - } { / } day { .
Transact-SQL Language Data Types Table 4-5.
Transact-SQL Language Data Types Table 4-6.
Transact-SQL Language Functions Table 4-6.
Transact-SQL Language Functions functions, then describes, by the following function types, how to use the supported functions: • • • • Date functions Mathematical functions String functions System functions Summary of Transact-SQL Functions Table 4-7 summarizes the supported Transact-SQL functions. Descriptions of each function follow the table. Table 4-7.
Transact-SQL Language Functions Date Functions The NonStop ODBC Server supports the following Transact-SQL date functions, which manipulate datetime data: • • • • DATEADD DATEDIFF DATEPART GETDATE Dateparts used within the functions have some restrictions, which are described following the individual function descriptions. DATEADD The DATEADD function has the following syntax: DATEADD ( datepart , number , date ) DATEADD returns a datetime value equal to the sum of date plus the number of dateparts.
Transact-SQL Language Functions Dateparts in Date Functions Transact-SQL date functions contain arguments called dateparts. Table 4-8 summarizes the dateparts supported by the NonStop ODBC Server. Table 4-8.
Transact-SQL Language Functions Generating Dates Earlier Than 1753 Transact-SQL and NonStop SQL/MP allow different year ranges: Transact-SQL 1753-9999 NonStop SQL/MP 0001-9999 Although the NonStop ODBC Server allows you to specify dates only in the range allowed by Transact-SQL, you can obtain dates earlier than 1753 by using negative numeric literals in a datetime function.
Transact-SQL Language Functions Variable Expressions in Date Functions The NonStop ODBC Server does not support variable expressions in date functions. For example, the following statement is not allowed: select dateadd ( month, @v1 + @v2, getdate() ) Simple variables, however, are allowed in date functions. For more information, see the following documents: For Information On See Transact-SQL date functions Microsoft SQL Server Transact-SQL Reference Datetime data Data Types on page 4-12.
Transact-SQL Language Functions POWER returns the value of numeric-expression-1 raised to the power of numeric-expression-2. The expressions can be of integer, float, or money type. The result is of the same data type as numeric-expression-1. For more information on Transact-SQL mathematical functions, see the Microsoft SQL Server Transact-SQL Reference.
Transact-SQL Language Functions SUSER_NAME The SUSER_NAME function has the following syntax: SUSER_NAME ( [ server-user-id ] ) server-user-id is not supported. The NonStop ODBC Server ignores this parameter. When executed using the NonStop ODBC Server, SUSER_NAME returns the logical username of the current user. The current user is the user who is logged on. USER_ID The USER_ID function has the following syntax: USER_ID ( [ user-name ] ) user-name is not supported.
Transact-SQL Language Functions data-type is the data type into which the expression is to be converted. It can be any supported data type. expression is the value to be converted. style is not supported. The NonStop ODBC Server ignores this parameter.
Transact-SQL Language Variables Variables You can use both local and global variables when using the NonStop ODBC Server. Local variables, however, have some usage restrictions, and not all global variables are supported. Also, you cannot declare values for global variables. This subsection contains information on both local and global variables. Local Variables You can use local variables when using the NonStop ODBC Server, naming the variables according to Transact-SQL naming rules.
Transact-SQL Language Variables Table 4-9. Global Variables Global Variable NonStop ODBC Server Support Comments or value assigned @@connections x The value is always 1. @@cpu_busy – -1 @@error x No differences. @@idle – -1 @@io_busy – -1 @@max_connections x Depends on the system resources. @@next_level – -1 @@pack_received – -1 @@pack_sent – -1 @@packet_errors – -1 @@procid – -1 @@rowcount – 0 @@textsize x The default is 512. (In Transact-SQL, it is 4058.
Transact-SQL Language Search Conditions Search Conditions Use Transact-SQL syntax in search conditions when using the NonStop ODBC Server. All supported search conditions work the same as in Transact-SQL.
Transact-SQL Language Expressions and Operators Expressions and Operators Use Transact-SQL syntax in expressions when using the NonStop ODBC Server. The syntax of an expression is: operand operator operand [ operator operand ] ... operand is: { { { { { constant column-name function ( subquery ) variable } } } } } operator is: [ arithmetic-operator ] [ bitwise-operator ] [ string-operator ] Operators are summarized on the following pages.
Transact-SQL Language Expressions and Operators Table 4-10.
Transact-SQL Language Aggregates LIKE Operator—Differences From SQL Server When used through the NonStop ODBC Server, the behavior of the LIKE operator differs from its behavior in SQL Server in the following ways: Feature In SQL Server In the NonStop ODBC Server Are trailing blanks significant? No Yes Do character comparisons distinguish between uppercase and lowercase? No Yes For more information, see the following documents: For Information On See Expressions Microsoft SQL Server Transact-S
Transact-SQL Language Aggregates AVG returns a floating point value that represents the average of the values in the column identified by expression; null values are ignored. If you include DISTINCT, AVG eliminates duplicate values before calculating the average. AVG can be used with numeric columns only. If you include DISTINCT when executing AVG using the NonStop ODBC Server, expression cannot be a constant. (The expression can be a constant when using Transact-SQL.
Transact-SQL Language Wild-Card Characters MAX returns a floating-point value for INT expressions. For other types of expressions, the value returned depends on the value of the expression being evaluated. MIN The MIN aggregate function has the following syntax: MIN ( expression ) MIN returns the minimum value in the column identified by expression. MIN can be used with numeric, character, and date columns, but not with bit columns.
Transact-SQL Language NULL Values . Table 4-12.
Transact-SQL Language Comments Table 4-13. The Order in Which NULL Values Are Displayed When Executed Using the NonStop ODBC Server Clause In Transact-SQL GROUP BY NULL values form their own group. Same as Transact-SQL ORDER BY NULL values come before all others. NULL values come after all others. SELECT statement with the DISTINCT keyword NULL values are considered duplicates of each other. Only one NULL is selected, no matter how many are encountered.
Transact-SQL Language Summary of Statements Table 4-14 summarizes NonStop ODBC Server support of SQL statements. Table 4-14. NonStop ODBC Server Support of Transact-SQL Statements (page 1 of 3) Transact-SQL Statement NonStop ODBC Server Support ALTER TABLE x Supports the following syntax: ALTER TABLE ADD (column-identifier data-type [,column-identifier data-type]) BEGIN TRANSACTION x Nested transactions are not allowed. Comments Transaction names can be included but are not meaningful.
Transact-SQL Language Summary of Statements Table 4-14. NonStop ODBC Server Support of Transact-SQL Statements (page 2 of 3) Transact-SQL Statement NonStop ODBC Server Support CREATE VIEW x Comments You can modify data in a view only if the view is derived from one table. The fully expanded view name must be unique in the network. DELETE x The FROM clause is not supported. You cannot specify more than one table name—you cannot delete rows based on data stored in other tables.
Transact-SQL Language Summary of Statements Table 4-14. NonStop ODBC Server Support of Transact-SQL Statements (page 3 of 3) Transact-SQL Statement NonStop ODBC Server Support Comments SELECT x The following clauses are not supported: • • • • INTO GROUP BY ALL COMPUTE BY FOR BROWSE You cannot include expressions in an aggregatefree-expression. Vector aggregates are not supported. You cannot mix aggregate and nonaggregate expressions in the select-list.
Transact-SQL Language Defaults, Rules, and Triggers Table 4-15. NonStop ODBC Server Support of Control-of-Flow Statements Transact-SQL Statement NonStop ODBC Server Support BEGIN...END x These statements are fully supported; however, in SQL Server, BEGIN and END are used primarily to enclose control-of-flow statements (such as IF, THEN, ELSE, and WHILE), which are not supported by the NonStop ODBC Server.
Transact-SQL Language Miscellaneous Transact-SQL Statements Miscellaneous Transact-SQL Statements The NonStop ODBC Server does not support the following Transact-SQL statements: GRANT KILL READTEXT REVOKE SETUSER SHUTDOWN WRITETEXT GRANT and REVOKE can be processed and ignored by setting a user profile option.
Transact-SQL Language Miscellaneous Transact-SQL Statements Table 4-16. Supported Transact-SQL Statements by Type (page 2 of 2) Transact-SQL Statement Description Corresponding NonStop SQL/MP Statement CREATE INDEX Creates indexes. CREATE INDEX CREATE TABLE Creates new tables. CREATE TABLE CREATE VIEW Creates views. CREATE VIEW DROP DATABASE Removes one or more catalogs. DROP CATALOG DROP INDEX Removes an index.
Transact-SQL Language ALTER TABLE None of the Transact-SQL DCL statements are supported. ALTER TABLE Use ALTER TABLE to add new columns to an existing table. The new column appears as the last column of the table. The ALTER TABLE statement has the following syntax: ALTER TABLE [ [database.]owner. ] table-name ADD column-name data-type NULL [ , column-name data-type NULL ] ... table-name is the table to alter. The table name can be qualified with the database name and owner name.
Transact-SQL Language Adding Multiple Columns The DEPT table is registered in the NonStop SQL/MP catalog PERSNL on the disk volume DISK01 on the node TEST. Adding Multiple Columns If you add multiple columns with one ALTER TABLE statement and the statement contains errors, the results can be different than if the statement were executed using SQL Server.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is ALTER TABLE ADD COLUMN. When used through the NonStop ODBC Server, ALTER TABLE differs from the NonStop SQL/MP implementation in the following ways: Feature In NonStop SQL/MP In Programs Used With the NonStop ODBC Server Number of columns you can add with ALTER TABLE One Multiple Can you specify default values? Yes No.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server insert into employee (empnum, first_name, last_name, deptnum, jobcode, salary) values (2001, "Dana", "Hall", 1010, 3001, 40000) insert into employee (empnum, first_name, last_name, deptnum, jobcode, salary) values (2002, "Fiona", "Stenhouse", 1020, 3001, 38000) end NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, BEGIN and END do not differ from the SQL Server implementation.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server insert into employee (empnum, first_name, last_name, deptnum, jobcode, salary) values (2001, "Dana", "Hall", 1010, 3001, 40000) commit transaction NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, BEGIN TRANSACTION differs from the SQL Server implementation in the following ways: Feature In SQL Server In Programs Used With the NonStop ODBC Server Are nested transactions allowed? Yes No Are trans
Transact-SQL Language Examples In SQL Server, transaction names are needed when transactions are nested; however, since transactions cannot be nested in programs used with the NonStop ODBC Server, the transaction-name is not meaningful in these programs. When used through the NonStop ODBC Server, the transaction is committed even if the transaction-name does not match the transaction-name on the previous BEGIN TRANSACTION statement. No error or warning messages are issued.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is COMMIT WORK. The primary difference between NonStop ODBC Server transactions and NonStop SQL/MP transactions is that in programs used with the NonStop ODBC Server, you can specify a transaction name.
Transact-SQL Language Examples ON database-device = size is not supported. In the NonStop ODBC Server, this clause is ignored. No error messages are generated.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP Feature In SQL Server In Programs Used With the NonStop ODBC Server Must the database owner grant access permission to other users? Yes, the owner must grant permission to other users before they can access the database. No. The catalog uses filesystem security. Any user with the appropriate security can access the catalog. For information on file-system security, see the Guardian User’s Guide.
Transact-SQL Language CREATE INDEX CREATE INDEX Use CREATE INDEX to create indexes. The CREATE INDEX statement has the following syntax: CREATE [ UNIQUE ] ON [ } { CLUSTERED { NONCLUSTERED } [database.]owner. ] INDEX index-name table-name ( column-name [ , column-name ] ... [ WITH index-option [ , index-option ] ...
Transact-SQL Language Examples WITH FILLFACTOR = fill is not supported. In the NonStop ODBC Server, this clause is ignored. No error messages are generated. WITH IGNORE DUP KEY is not supported. In the NonStop ODBC Server, this clause is ignored. No error messages are generated. Rows are rejected if they have a duplicate primary key. WITH IGNORE DUP ROW, WITH ALLOW DUP ROW is not supported. In the NonStop ODBC Server, this clause is ignored. No error messages are generated.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP Feature In SQL Server In Programs Used With the NonStop ODBC Server Can you create a unique index on a column that allows null values? Yes No How many columns can be indexed? Up to 16 Depends on the length of the index key. Can you index BIT type columns? No Yes, but the NonStop ODBC Server maps the BIT data type to the NonStop SQL/MP SMALLINT data type.
Transact-SQL Language CREATE TABLE Specifying the Physical Location In NonStop SQL/MP, you can specify the physical location of the index. In programs used with the NonStop ODBC Server, you cannot; you can create an index only on the subvolume that holds the catalog in which the index will be registered. You can, however, specify the physical location by using the pass-through mode.
Transact-SQL Language Examples column-name identifies a column in the table. The column-name must be unique for columns defined in the table. data-type specifies the SQL Server data type for the column. Data types are converted to NonStop SQL/MP data types. Data types are described under Language Elements on page 4-5. If you specify an unsupported data type, an error message is generated and the statement is not executed. NOT NULL, NULL specifies whether the column can contain null values.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, CREATE TABLE differs from the SQL Server implementation in the following ways: Feature In SQL Server In Programs Used With the NonStop ODBC Server Can CREATE TABLE be inside a transaction? No Yes Is auditing of the volume necessary? N.A.
Transact-SQL Language • NonStop ODBC Server Compared With NonStop SQL/MP The ownership and security of the table affect dependent indexes and views. These dependencies are discussed in the CREATE VIEW, CREATE INDEX, and ALTER security statements. For further information on these NonStop SQL/MP features, see the CREATE TABLE statement description in the NonStop SQL/MP Reference Manual. NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is CREATE TABLE.
Transact-SQL Language CREATE VIEW CREATE VIEW Use CREATE VIEW to create a view. The file security for the view is obtained from the default permissions associated with the user’s logon name. The CREATE VIEW statement has the following syntax: CREATE VIEW view-name [ ( column-name [, column-name ] ... ) ] AS query-expression query-expression is: { query-expression UNION [ALL] query-expression } { ( query-expression ) } { query-specification } view-name specifies the name of the view to create.
Transact-SQL Language Examples Tables in the query-specification must be in the current database or be qualified by their database name. If there is only one table referenced by the view, NonStop ODBC automatically creates a protection view. The query-specification cannot include the ORDER BY clause, but it can include the OUTER JOIN and UNION clauses. See SELECT on page 3-54 for further information.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server Feature In SQL Server Is the view definition text visible? Yes, in the SYSCOMMENTS system table. In Programs Used With the NonStop ODBC Server Yes, but not in the SYSCOMMENTS catalog. The text is visible in the NonStop SQL/MP VIEWS catalog table if the user can determine the NonStop SQL/MP name used for the view. See Locating Objects Using the Mapping Tables on page 7-4. Is auditing of the volume necessary? N.A.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP In this example, both the VIEWS table and the EMPLIST view are in the subvolume \TEST.$VOL2.PERSNL. NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is CREATE VIEW.
Transact-SQL Language Examples Examples The following statements declare variables and assign them values.
Transact-SQL Language DELETE DELETE Use DELETE to remove rows from a table. The DELETE statement has the following syntax: DELETE [ FROM ] table-reference [ FROM table-reference [ , table-reference ... ] ] [ WHERE search-condition ] table-reference is: [ [database.]owner. ] {table-name } {view-name) } FROM is not supported. The NonStop ODBC Server ignores this clause. table-name or view-name identifies the table or view from which rows are to be deleted.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, DELETE differs from the SQL Server implementation in the following ways: Feature In SQL Server Is the FROM clause available? Yes In Programs Used With the NonStop ODBC Server No. If you use it, the NonStop ODBC Server generates an error message. See the following discussion of the FROM clause.
Transact-SQL Language DROP DATABASE DROP DATABASE Use DROP DATABASE to remove one or more NonStop SQL/MP catalogs. DROP DATABASE removes the NonStop ODBC Server mapping tables and the NonStop SQL/MP catalog. The DROP DATABASE statement has the following syntax: DROP DATABASE database-name [ , database-name ] ... database-name is the subvolume name that identifies the database. The database-name is a subvolume optionally qualified with a node and volume.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server DROP DATABASE is equivalent to USERCAT DEINSTALL with the CASCADE parameter (described under USERCAT DEINSTALL Statement on page 7-24). Table 4-17 summarizes the objects, mapping tables, and catalogs that are dropped by DROP DATABASE. Table 4-17.
Transact-SQL Language DROP INDEX DROP INDEX Use DROP INDEX to remove an index from the database. The DROP INDEX statement has the following syntax: DROP INDEX table-name.index-name [ , table-name.index-name ] ... table-name identifies the table in which the indexed column is located. The table must be in the current catalog—you cannot drop an index from a table in another catalog. index-name specifies the index to drop.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, DROP INDEX differs from the SQL Server implementation in the following ways: Feature In SQL Server In Programs Used With the NonStop ODBC Server Can DROP INDEX be inside a transaction? No Yes Which objects must be accessible? N.A. See DDL Statements Allowed in User-Defined Transactions on page 4-3.
Transact-SQL Language DROP TABLE DROP TABLE Use DROP TABLE to remove a table from a database, along with all data, indexes, and dependent views for that table. The DROP TABLE statement has the following syntax: DROP TABLE [ [ , [ [database.]owner.] database.. ] [ [database.]owner. ] [ database.. ] table-name table-name ] ... ] table-name identifies the table to be dropped; table-name can be qualified with the database name and owner name.
Transact-SQL Language Dropping Partitioned Tables When you drop a table using the NonStop ODBC Server, NonStop SQL/MP drops the table and all dependent views and indexes. The NonStop ODBC Server, however, does not clear the mapping table entries for the views or indexes. Note. When the mapping table entries are not cleared and you later attempt to create a view or index by the same name, you will receive error messages saying that the object already exists when in fact it does not.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP In Programs Used With the NonStop ODBC Server Feature In SQL Server Are constraints automatically dropped? SQL Server does not have constraints, but any rules on the table lose their binding, and any triggers associated with the table are automatically dropped. Yes Are dependent views and indexes automatically dropped? No Yes, but the mapping entries for the dependent objects are not dropped.
Transact-SQL Language Examples Examples The following statement drops a view owned by PAYROLL_MGR and one owned by PAYROLL_ADMIN: use test_disk01_persnl drop view payroll_mgr.emplist, payroll_admin.mgrlist Dropping Dependent Views and Indexes You should drop dependent views and indexes before dropping a view using the NonStop ODBC Server. When you drop a view using the NonStop ODBC Server, NonStop SQL/MP drops the view and all dependent views and indexes.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP In Programs Used With the NonStop ODBC Server Feature In SQL Server Are dependent views and indexes dropped? No Yes, but the mapping entries for the dependent objects are not dropped. See Dropping Dependent Views and Indexes on page 4-69. Format of an owner name SQL Server identifier A Guardian logon name. For information on specifying owner names, see Language Elements on page 4-5.
Transact-SQL Language EXECUTE EXECUTE Use EXECUTE to invoke a previously defined stored procedure (a Pathway server class program). The EXECUTE statement has the following syntax: [ EXEC[UTE] ] [ @return-status = ] [server.] [ [database.]owner. [ database.. [ [@parameter-name =] ] ] procedure-name[;number] { value, } [, ...] ] { @variable [ OUT[PUT] ] } [ WITH RECOMPILE ] @return-status is a local variable that returns a code indicating the completion status of the stored procedure execution.
Transact-SQL Language EXECUTE @parameter-name is a Transact-SQL identifier. According to SQL Server rules, parameter values without associated parameter names must be given in the order defined in the procedure declaration. If @parameter-name is used, any sequence is allowed, but when this form is used, it must be used for all subsequent parameters in the EXECUTE statement. value is either a literal or a local variable. No other expression is permitted.
Transact-SQL Language • • Example OUTPUT variables are not part of a transaction, so if a parameter is changed in a transaction that is rolled back, the parameter’s value does not revert to its previous value. If the data type of a parameter and its corresponding parameter definition in the procedure declaration are not compatible, a data conversion error occurs. If data conversion results in data truncation, a warning is issued.
Transact-SQL Language Examples view-name identifies the view into which the rows are inserted; view-name can be qualified with the database name and owner name. The view must be an updatable NonStop SQL/MP protection view. For information on protection views, see “Shorthand Views and Protection Views” in the CREATE VIEW on page 4-58. column-name specifies a column for which a value will be supplied. constant-expr is a value (or the keyword NULL) for a column in the row being inserted.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, INSERT differs from the SQL Server implementation in the following ways: In Programs Used With the NonStop ODBC Server Feature In SQL Server Can you insert rows into a view that references more than one table? Yes, if the columns being inserted belong to only one table. No, even if the columns being inserted belong to only one table.
Transact-SQL Language PRINT PRINT Use PRINT to return a user-defined message to the user’s application. The PRINT statement has the following syntax: PRINT { "ascii-string" } { @local-variable } { @@global-variable } "ascii-string" is a character string, up to 255 characters, enclosed in double quotation marks ("). local-variable is the name of a local variable. The variable must be of type CHAR or VARCHAR and must be declared within the batch in which it is used.
Transact-SQL Language Example transaction-name is the name associated with the transaction. In SQL Server, transaction names are needed when transactions are nested; however, because transactions cannot be nested in programs used with the NonStop ODBC Server, the transaction-name is not meaningful in these programs.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, ROLLBACK TRANSACTION differs from the SQL Server implementation in the following ways: Feature In SQL Server In Programs Used With the NonStop ODBC Server Are nested transactions allowed? Yes No Transaction savepoints allowed? Yes Yes, but they are ignored. If you want to roll back a transaction, you must roll back the entire transaction.
Transact-SQL Language SELECT create table employee (empnum smallint, first_name char(15), last_name char(20), deptnum smallint null, jobcode smallint null, salary float null) save transaction create_tab insert into test_disk01_persnl..employee (empnum, first_name, last_name, deptnum, jobcode, salary) values (1234, "Georgia", "Brown", 1001, 3004, 52300) save transaction insert_emp1 insert into test_disk01_persnl..
Transact-SQL Language SELECT The SELECT statement has the following syntax: SELECT [ INTO { ALL } { DISTINCT } select-list [ [database.]owner. ] table-name) ] FROM table-reference [ correlation-name ] [ HOLDLOCK ] [, table-reference [ correlation-name ] [ HOLDLOCK ] ] ... [ WHERE search-condition ] [ GROUP BY [ ALL ] agg-free-expr [ , agg-free-expr ] ... ] [ HAVING search-condition ] [ ORDER BY sort-spec [ , sort-spec ] ... ] [ COMPUTE comp-list BY column-name [ , column-name ] ...
Transact-SQL Language Examples comp-list is: aggregate(column-name) [ , aggregate(column-name) ] ... table-reference is: [ [database.]owner.
Transact-SQL Language ALL/DISTINCT Clause ALL/DISTINCT Clause The ALL/DISTINCT clause of the SELECT statement specifies the columns to be included in the result of the query. The clause has the following syntax: { ALL } { DISTINCT } select-list select-list is: select-item [ , select-item ] ... select-item is: { { { { { { [table-reference.]* [table-reference.]column-name column-heading = [ table-reference.]column-name [table-reference.
Transact-SQL Language INTO Clause [ table-reference.]column-name column-heading retrieves the specified column and replaces the heading of the retrieved column with the specified column-heading. expression is an expression. Expressions are described under Language Elements on page 4-5. aggregate-function is one of the following aggregate functions: AVG COUNT MAX MIN SUM Aggregates are described under Language Elements on page 4-5.
Transact-SQL Language WHERE Clause correlation-name is a name that identifies the table or view for the SELECT statement. Each correlation-name in the FROM clause must be unique. In SQL Server, a correlation name is called an alias. HOLDLOCK in SQL Server, this makes a shared lock on a specified table or view more restrictive by holding it until the completion of a transaction.
Transact-SQL Language HAVING Clause column-name is the name of a column from one of the tables in the FROM clause. expression is not supported. In the NonStop ODBC Server, expressions in the aggregate-free-expression cause an error message. HAVING Clause The HAVING clause of the SELECT statement sets conditions for the GROUP BY clause and has the following syntax: HAVING search-condition Search conditions are described under Language Elements on page 4-5.
Transact-SQL Language COMPUTE BY Clause ASC or DESC specifies whether to sort in ascending or descending order; the default is ascending. COMPUTE BY Clause The COMPUTE BY clause of the SELECT statement is not supported in the NonStop ODBC Server. It has the following syntax: COMPUTE comp-list BY column-name [ , column-name ] ... comp-list is: aggregate(column-name) [, aggregate(column-name) ] ... Inclusion of the COMPUTE BY clause causes an error message to be generated.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP Feature In SQL Server In Programs Used With the NonStop ODBC Server Are vector aggregates available? Yes No Can nonaggregate and aggregate expressions be mixed in the select-list ? Yes No Can the select-list be an asterisk (*)? No Yes INTO Clause In programs used with the NonStop ODBC Server, you cannot specify the SQL Server INTO clause with the SELECT statement.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP When used through the NonStop ODBC Server, SELECT differs from the NonStop SQL/MP implementation in the following ways: In Programs Used With the NonStop ODBC Server Feature In NonStop SQL/MP How to specify the locking mode Use the BROWSE/REPEATABLE/ STABLE ACCESS clause. Use the HOLDLOCK keyword; you cannot use the BROWSE/REPEATABLE/STABLE ACCESS clause.
Transact-SQL Language SET SET Use SET to set query-processing options for the duration of a work session. The SET statement has the following syntax: SET { { { { { { { { { { { { { { { { ALTNAMES } { OFF } } { ARITHABORT } { ON } } { ARITHIGNORE } } { BACKGROUND } } { CONTROL } } { NOCOUNT } } { NOEXEC } } { OFFSETS { { keyword-list } } } { PARSEONLY } } { PROCID } } { SHOWPLAN } } { STATISTICS IO } } { STATISTICS TIME } } ROWCOUNT number } TEXTSIZE number, } ALTNAMES is not supported.
Transact-SQL Language SET NOEXEC is not supported. If included in a program used with the NonStop ODBC Server, an error message is generated. OFFSETS { keyword-list } is not supported. If included in a program used with the NonStop ODBC Server, an error message is generated. PARSEONLY parses subsequent statements but does not execute them (except for other SET statements): ON Statements are parsed but not executed. OFF Statements are executed as they are received.
Transact-SQL Language Examples Examples The following batch sets PARSEONLY to ON and executes several INSERT statements. The INSERT statements are not executed.
Transact-SQL Language TRUNCATE TABLE TRUNCATE TABLE Use TRUNCATE TABLE to remove all rows in a table. The TRUNCATE TABLE statement has the following syntax: TRUNCATE TABLE [ [database.]owner. ] table-name table-name identifies the table to be truncated. The table name can be qualified with the database name and owner name.
Transact-SQL Language UPDATE UPDATE Use UPDATE to change data in existing rows, either by adding new data or by modifying existing data. The UPDATE statement has the following syntax: UPDATE table-reference SET [ table-reference.]column-name = [ column-name = [ { expression } ] { NULL } ] { expression } { NULL } ... [ FROM table-reference [ , table-reference ] ... ] [ WHERE search-condition ] table-reference is: [ [database.]owner.
Transact-SQL Language Examples Examples The following example updates the salary of employee number 1002: update test_disk01_persnl..employee set salary = salary * 1.
Transact-SQL Language NonStop ODBC Server Compared With NonStop SQL/MP NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is UPDATE. When used through the NonStop ODBC Server, UPDATE differs from the NonStop SQL/MP implementation in the following ways: Feature In NonStop SQL/MP In Programs Used With the NonStop ODBC Server Can you specify the locking mode? Yes No, unless you use pass-through mode.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, UPDATE STATISTICS differs from the SQL Server implementation in the following way: Feature In SQL Server In Programs Used With the NonStop ODBC Server Can you specify the index for which to update statistics? Yes No NonStop ODBC Server Compared With NonStop SQL/MP The corresponding NonStop SQL/MP statement is UPDATE STATISTICS.
Transact-SQL Language NonStop ODBC Server Compared With SQL Server NonStop ODBC Server Compared With SQL Server When used through the NonStop ODBC Server, USE differs from the SQL Server implementation in the following way: Feature In SQL Server When does the new database take effect? When the current batch finishes. In Programs Used With the NonStop ODBC Server Immediately (even before the batch finishes).
5 Stored Procedures This section describes the HP NonStop ODBC Server support for execution of stored procedures by an ODBC application or a DB-LIBRARY application connected to a NonStop ODBC server on a HP server. The term “NonStop ODBC Server” is used to distinguish the server process itself from the overall NonStop ODBC Server product.
Stored Procedures Overview of Stored Procedures 2. Invocation of the stored procedure DEBIT_ACCOUNT: execute DEBIT_ACCOUNT ("John Smith", 50.00) Overview of Stored Procedures The NonStop ODBC Server allows the user to develop stored procedures in a familiar 3GL language, such as C or COBOL. Using a 3GL language provides the user with full access to all of the capabilities of the 3GL language, along with the performance and debugging features that come with this approach.
Stored Procedures Installing and Using Stored Procedures 2. The NonStop ODBC server translates the stored procedure invocation to a Pathway server class request, using mapping tables in the NonStop ODBC Server catalogs to look up the Pathway server class program, and sends the request to the Pathway server class. 3. The code in the server class process carries out the stored procedure steps. 4. The server class program returns HP NonStop SQL/MP data (or a diagnostic message) to the NonStop ODBC server. 5.
Stored Procedures Execution of Stored Procedures Execution of Stored Procedures Stored procedures can be invoked through the NonStop ODBC Server using either the CORE SQL syntax or the Transact-SQL syntax. The following conditions must be met for successful execution of a stored procedure: • • The stored procedure must have been created as a Pathway server (see Development of Stored Procedures on page 5-6).
Stored Procedures Invoking Stored Procedures in Transact-SQL The result appears as follows: FIRST_NAME ---------JOE DON DEBBIE • LAST_NAME --------SMITH HUE SMITH If the optional return status (?=) is specified and the stored procedure has been coded and configured to return a status code, the caller receives the execution status in addition to the results. Stored procedure execution can return multiple result sets or counts.
Stored Procedures DON DEBBIE • • Configuration Options HUE SMITH If the procedure returns a status code, the caller also receives the execution status. In the previous example, the execution status is returned in the variable @RETURN_STATUS. If the procedure is defined to have output parameters and the procedure execution requests output values, the caller receives those output values. In the following example, the stored procedure DEBIT_ACOUNT also returns the account balance in the variable BALANCE.
Stored Procedures Server Logic Sequence messages. The SPELIB functions do not perform any actual I/O ($RECEIVE, OPEN, WRITE and REPLY handling) between the NonStop ODBC Server and the Pathway server class program. However, a sample Pathway server program has been provided with the NonStop ODBC Server product that includes a set of routines for handling $RECEIVE and a set of SPELIB shell routines for handling call-back processing.
Stored Procedures • Server Logic Sequence Send the reply or write the output buffer back by utilizing the call-back feature as needed. The following diagram illustrates the calling sequences involved. The SP_SRV_xxx functions are explained in detail under SPELIB Interface on page 5-27. In addition to the SPELIB functions, a set of shell routines has been provided with the sample Pathway server program. The following sequences apply to both the shell routines and the equivalent SPELIB functions.
Stored Procedures Using the Sample Server NonStop ODBC Server Pathway Server . . . . . 12. If any error/warning occurs: . . . . . 13. End every SQL statement with: encode_end_statement [shell routines] or SP_SRV_ENCODE_END_STATEMENT [SPELIB] . . . . . 14. End procedure. encode_sql_diagnostic [shell routines] or SP_SRV_ENCODE_SQL_DIAGNOSTIC [SPELIB] . . encode_end_proc [shell routines] or [SPELIB] SP_SRV_ENCODE_END_PROC . . . /* PATHSEND terminates. */ 15. Reply. send_reply .
Stored Procedures Using the Sample Server DBCREATE An OBEY file for creating the sample database (use this only if the sample database has not already been created). DBLOAD An OBEY file for populating the sample database. DROPSP An OBEY file that contains sample NOSCOM REMOVE PROCEDURE commands to drop the sample stored procedures. PATHIN An example of a Pathway configuration file.
Stored Procedures Using the Sample Server SP23COB The COBOL85 source code for the sample stored procedure DEPT9000. This stored procedure returns employee data for department 9000. SP24C The C source code for the sample stored procedure SELECT_EMP. This procedure returns employee data for the specified employee number. The employee number is a required input parameter. SPMACRO A file that contains TACL macros for the sample stored procedure server.
Stored Procedures Using the Sample Server Figure 5-3.
Stored Procedures Developing a Stored Procedure in C Each of the major functions in Figure 5-3 are described as follows: main The main() routine is very simple and uses a function named REGISTER_SPE for registering all of the stored procedures included within the server. The REGISTER_SPE function is called once for each stored procedure included within the server. The function PROCESS_EVENT_QUEUE is then called for handling any event received from $RECEIVE.
Stored Procedures Developing a Stored Procedure in C The basic steps in developing a stored procedure are described as follows. A more detailed description of how to create the code for the stored procedure appears later in this subsection. 1. Create the stored procedure (SELECT_INPUT_DEPT): a. Provide a routine, if needed, for any initialization that should be performed for this stored procedure when the Pathway server is first started. For example, INITIALIZE_SPE_21. b.
Stored Procedures Developing a Stored Procedure in C The ADD PROCEDURE command registers the stored procedure SELECT_INPUT_DEPT for user SQL.ODBC in the NonStop ODBC Server catalog \TESS.$DATA1.DB, with the Pathmon process name of $EMPSP, server name of EMP-DATA-DML, one result set, and a maximum reply buffer length of 2000 bytes. The ADD PROCEDURE_COLUMNS command specifies one input parameter for the stored procedure. 6.
Stored Procedures Developing a Stored Procedure in C (data) ---------------------------------------------------- */ typedef struct SQLDA_TYPE *sdaptr; sdaptr sdai21; sdaptr sdao21; /* -----------------------------------------------------Set up SQLDA for the column headings ---------------------------------------------------- */ sdaptr sdan21; typedef char (*arrayptr) [1000]; arrayptr colnames21; char temp_stmt21[max_stmt_len + 1]; /* area for null_ind values */ short int NULL_ind[max_columns]; int input_v
Stored Procedures Developing a Stored Procedure in C --------------------------------------------------- */ sdan21 = allocate_SQLDA ( SPE_21_max_entries, FALSE); colnames21 = (arrayptr) malloc (500); strncpy (sdan21->eye_catcher, SQLDA_EYE_CATCHER, 2); sdan21->num_entries = SPE_21_max_entries; /* -----------------------------------------------------Separate SQLDA for column definitions needs to be allocated. This area is used by ENCODE_ROW_DATA.
Stored Procedures Developing a Stored Procedure in C b. Encode initialization. /* -----------------------------------------------------Encode_init must be called to set up each reply buffer before writing any data into the buffer for return to the NSODBC server. ---------------------------------------------------- */ rc = encode_init( env , max_len , output_buffer , reply_err ); if (rc != SPELIB_OK) { strncpy(errtext, "Error attempting encode_init", rc); goto Liberr; } c. Main logic.
Stored Procedures Developing a Stored Procedure in C Fetch the data and encode the resulting rows: /* -----------------------------------------------------SQL FETCH loop ---------------------------------------------------- */ do { EXEC SQL FETCH emp21_cursor into :emp_rec.empnum, :emp_rec.first_name, :emp_rec.last_name, :emp_rec.
Stored Procedures Developing a Stored Procedure in COBOL /* -----------------------------------------------------Every stored procedure should end with an END_PROC message ---------------------------------------------------- */ rc = encode_end_proc( env); if (rc != SPELIB_OK) *reply_err = rc; 3. Create Clean-Up Routine This is the routine that will be called when the stored procedure server process is stopping.
Stored Procedures Developing a Stored Procedure in COBOL a. For the main sample server code to be able to call a stored procedure, it must be given the name of a function to call for that procedure. This function name is set up when the stored procedure is configured into the sample server. For COBOL procedures, the function name is the PROGRAM-ID of the module for the procedure.
Stored Procedures Developing a Stored Procedure in COBOL b. Send in a request from the client to execute the stored procedure. Coding a Stored Procedure in COBOL This subsection covers Steps 1 and 2 from “Developing a Stored Procedure in COBOL” in greater detail, using the sample code in the file SP23COB for the stored procedure DEPT9000 and the file SAMPLEC for the main routine of the sample server. 1. Set up the stored procedure name and point to the shell routines.
Stored Procedures Developing a Stored Procedure in COBOL "SELECT EMPNUM,FIRST_NAME,LAST_NAME,". 05 FILLER PIC X(40) VALUE "DEPTNUM,SALARY FROM =EMPLOYEE ". 05 FILLER PIC X(40) VALUE "WHERE DEPTNUM = 9000 BROWSE ACCESS ". EXEC SQL SOURCE CODECOB (ERRWS) END-EXEC. EXEC SQL END DECLARE SECTION END-EXEC. / ********************************************************* * COBOL PROGRAM WORKING-STORAGE * ********************************************************* 01 05 88 05 05 05 88 01 SP-VARIABLES.
Stored Procedures Developing a Stored Procedure in COBOL SP-BUFFER, SP-REPLY, SP-RC. 3. Set up the initialization code. This code is called when the stored procedure is first executed. In this example, an SQLDA is set up that will be used for returning the row descriptor and data, and the SQL statement to be executed for querying the employee table is SQL prepared. 1000-INITIALIZE. MOVE "00" TO SQL-STATUS-SW. MOVE "D1" TO EYE-CATCHER OF SDAO23. MOVE SPE-23-MAX-ENTRIES TO NUM-ENTRIES OF SDAO23.
Stored Procedures • Developing a Stored Procedure in COBOL Call initialization, if needed, then execute the main logic. ********************************************************* * MAIN PROGRAM ********************************************************* 0000-MAIN. IF SP-FIRST-TIME = "Y" PERFORM 1000-INITIALIZE. PERFORM 2000-PROCESS-REQUEST. PERFORM 3000-END-PROCESS. MOVE WS-RC TO SP-RC. MOVE WS-MAX-LEN TO SP-MAX-LEN. EXIT PROGRAM. • Encode initialization. Call the shell function ENCODE_INIT.
Stored Procedures Developing a Stored Procedure in COBOL Before looking at the code to open the cursor and return the row data, it is helpful to examine the code to set up the varptrs in the SQLDA structure for the column data to be returned.
Stored Procedures • SPELIB Interface Encode “end statement” and “end procedure” messages. After all the rows have been returned, the stored procedure must encode an “end statement” message to indicate the SQL statement has been completed. As this is the end of the stored procedure, an “end procedure” message must also be encoded.
Stored Procedures SPELIB Interface Programmers allocate the environment structure by simply calling “malloc” in C (or a similar function in a different language), using a DEFINE that specifies the size of the structure. For example: malloc (SPEIPC_OBJECT_SIZE); The DEFINE, SPEIPC_OBJECT_SIZE, is provided in the C header file (SPELIBH) containing SPELIB function prototypes. The environment object must be allocated and initialized before other SPELIB functions can be used.
Stored Procedures SPELIB Interface SP_SRV_DECODE_INIT This function must be called before any attempt is made to decode the stored procedure request. This function sets up the environment object that allows decoding of stored procedure requests in “server mode.” Note. If you are using the shell routines supplied with the sample Pathway server program, this function is called on your behalf by the routine PROCESS_REQUEST; it must not be called again.
Stored Procedures SPELIB Interface by the server. If this attempt fails, the NonStop ODBC Server issues an error message to the client. The following example illustrates this action. NonStop ODBC Server Pathway Server PATHSEND(...) Read from $RECEIVE ... rc = SP_SRV_DECODE_INIT (senv,... ) If (rc==SPELIB_VERSION_NOT_SUPPORTED) { /* Allocate reply buffer with */ /* length SPELIB_MIN_BUFFER_SIZE */ SP_SRV_ENCODE_INIT (senv,replybuf,...
Stored Procedures SPELIB Interface SP_SRV_GET_SERVICE_NAME This function returns the Pathway service name associated with the stored procedure name. short int SP_SRV_GET_SERVICE_NAME ( void * env , char * service-name , short int * service-name-len ) env input is a pointer to the environment object STRUCT. service-name output is a pointer to the Pathway service name. service-name-len output is a pointer to the length of the Pathway service name. The maximum length is 60 characters.
Stored Procedures SPELIB Interface SP_SRV_GET_REQ_PROCESS_NAME This function returns the name of the requester process that sent the stored procedure execution request. The name is used if the server must open the requester to invoke the call-back capability. short int SP_SRV_GET_REQ_PROCESS_NAME ( void * env , char * req-process-name , short int * req-process-name-len ) env input is a pointer to the environment object STRUCT.
Stored Procedures SPELIB Interface logical-user-name-len output is a pointer to the length of the logical username; the maximum length is 60 characters. guardian-user-id output is a pointer to the two-character Guardian user ID in internal format. SP_SRV_GET_NUM_INPUT_PARAMS This function returns the number of input parameters that accompany the execution request. The caller should use the num-params value returned to allocate the SQLDA structure and space for the input parameter values.
Stored Procedures SPELIB Interface sqlda output is a pointer to a SQL descriptor area. to be used for the input parameters. The Pathway programmer must have allocated sufficient space for the SQLDA. truncated output indicates whether the input parameter has been truncated. SP_SRV_GET_INPUT_PARAMS This function is used to extract the input parameters associated with a stored procedure request.
Stored Procedures SPELIB Interface SP_SRV_ENCODE_INIT This function must be called before attempting to format each buffer that will be returned to the NonStop ODBC server. It prepares the base buffer format. The environment object must have been previously used to decode a stored procedure request from a NonStop ODBC Server process. short int SP_SRV_ENCODE_INIT ( void * env , char * buffer , unsigned long int max-buffer-len ) env input is a pointer to the environment object STRUCT.
Stored Procedures SPELIB Interface SP_SRV_ENCODE_ROW_DATA This function encodes a row of data of the result set generated by the execution of the stored procedure. This function cannot be used until a call to SP_SRV_ENCODE_ROW_DESCR has been successfully completed for that result set. short int SP_SRV_ENCODE_ROW_DATA ( void * env , void * sqlda ) env input is a pointer to the environment object STRUCT. sqlda output is a pointer to a SQL descriptor area containing the row data to be returned.
Stored Procedures SPELIB Interface The NonStop ODBC Server does not abort the transaction or terminate processing the remainder of the messages coming back from server class. It is recommended, however, that this message be the last message before END_STMT or END_PROC. SP_SRV_ENCODE_OUTPUT_PARAMS This function enables the server to encode any output parameters using one function call.
Stored Procedures SPELIB Interface SP_SRV_ENCODE_END_STATEMENT This function enables the server to encode an “end-statement” message. It signals that a stored procedure operation has finished, but that the stored procedure execution is still in progress. This function should be called after each SQL statement has been executed in the stored procedure. short int SP_SRV_ENCODE_END_STATEMENT ( void * env , void * sqlca ) env input is a pointer to the environment object STRUCT.
Stored Procedures SPELIB Interface env input is a pointer to the environment object STRUCT. message input is a pointer to the message to be returned to the client. message-len input specifies the length, in bytes, of the message. The maximum is 256 bytes. SP_SRV_ENCODE_RAISERROR This function enables the server to encode a “raise error” message. The NonStop ODBC server sends this message to the client.
Stored Procedures SPELIB Interface SP_SRV_ENCODE_SPELIB_ERROR This function enables the server to encode an SPELIB error. short int SP_SRV_ENCODE_SPELIB_ERROR ( void * env , long int error-num ) env input is a pointer to the environment object STRUCT. error-num input specifies the error number to be returned to the client. SP_SRV_GET_BUFFERLEN This function enables the server programmer to determine how much of the IPC buffer is currently in use.
Stored Procedures SPELIB Interface SPELIB Sequence Rules This subsection specifies the sequence considerations for the Pathway server and for the SPELIB functions (and the equivalent SPELIB shell routines provided with the sample Pathway server). SP_SRV_ENCODE_INIT must be the first function called in the encoding process; SP_SRV_ENCODE_END_PROC is the last. In between, a prescribed logical sequence must be followed (for example, SP_SRV_ENCODE_ROW_DATA cannot precede SP_SRV_ENCODE_ROW_DESCR).
Stored Procedures SPELIB Interface Figure 5-4. SPELIB Sequence for Server Encoding Idle State ENCODE_INIT 1 2 ENCODE_END_STMT 2 ENCODE_SPELIB_ERROR ENCODE_ROW_DESCR 1 ENCODE_END_PROC 2 ENCODE_ROW_DATA 1 ENCODE_RETURN_STATUS 2 ENCODE_OUTPUT_PARAMS 1 ENCODE_RAISERROR ENCODE_SQL_DIAGNOSTIC ENCODE_PRINTMSG VST043.vsd 1. represents the path taken if a non-SQL error message, an SQL diagnostic message, or an advisory message is to be returned. 2.
Stored Procedures SPELIB Interface SPELIB Errors For error-handling purpose, a Pathway server class program that emulates a stored procedure is considered to have two sets of errors: SPELIB errors and other errors. The latter might be NonStop SQL/MP execution errors, Pathway program errors, and so on. SPELIB provides SP_SRV_ENCODE_SQL_DIAGNOSTIC for encoding SQL errors or warnings, SP_SRV_ENCODE_RAISERROR for encoding non-SQL errors and SP_SRV_ENCODE_PRINTMSG for reporting informational messages.
Stored Procedures SPELIB Interface SPELIB Return Codes The following describes the error codes returned by the SPELIB interface; they can be used for decision-making in subsequent SPELIB function calls. • SPELIB_OK The operation was completed successfully. • SPELIB_BUFFER_OVERFLOW The reply buffer became full during SPEIPC encoding. The server program should use the call-back mechanism to send the result back to the requester. For example: SP_SRV_ENCODE_INIT (senv, replybuf, maxlen); ...
Stored Procedures • SPELIB Interface SPELIB_EXCEEDS_MAX_BUFFER The reply buffer size exceeds the 32K bytes limit. The server class program can readjust the buffer size or encode the SPELIB error and issue a REPLY. • SPELIB_VERSION_NOT_SUPPORTED The SPELIB version does not match between the requester and the server. The server class program should issue a REPLY with the currently supported SPELIB version.
Stored Procedures Shell Routines Interface An SPELIB environment object is not recognized. The server class program should encode the SPELIB error and issue a REPLY. • SPELIB_INVALID_IPC_KEY An SPELIB key value is not recognized. This is an internal error. The server class program should encode the SPELIB error and issue a REPLY. • SPELIB_INVALID_SEQ_NUM The SPELIB sequence number is out of range. This could be an internal error or a user error.
Stored Procedures Shell Routines Interface void *allocate_SQLDA( short int num_entries , boolean need_names_buffer ); assign_names_buffer extern /*_____________________________________________________________________ This function assigns the names_buffer pointer to the address immediately following the SQLDA.
Stored Procedures Shell Routines Interface , , ); char *buffer short int *reply_err encode_init extern /*_____________________________________________________________________ This function initializes the output buffer for server encode.
Stored Procedures reply_err: Shell Routines Interface IN/OUT: pointer to the reply error Returns: if successful -- SPELIB_OK else -- an SPELIB error ___________________________________________________________________*/ short int encode_printmsg( void , unsigned long int , short int , char , char , short int ); *env max_len msg_len *msg_text *buffer *reply_err encode_raiserror extern /*_____________________________________________________________________ This function encodes a raiserror message int
Stored Procedures Shell Routines Interface , ); short int *reply_err encode_row_data extern /*_____________________________________________________________________ This function encodes a row of data into the output buffer. If it detects an SPELIB_BUFFER_OVERFLOW condition, it will invoke the CALL-BACK facility.
Stored Procedures Shell Routines Interface err_num: buffer: reply_err: IN: IN/OUT: IN/OUT: error number associated with spelib_error text pointer to the output buffer pointer to the reply error Returns: if successful -- SPELIB_OK else -- an SPELIB error ___________________________________________________________________*/ short int encode_spelib_error( void *env , unsigned long int max_len , short int err_num , char *buffer , short int *reply_err ); encode_sql_diagnostic extern /*___________________
Stored Procedures Shell Routines Interface output_buffer: input_sqlda: IN/OUT: IN/OUT: pointer to the output buffer pointer to the input parameter sqlda Returns: if successful -- SPELIB_OK else -- an SPELIB error ___________________________________________________________________*/ short int get_input_params( void , short int , unsigned long int , char , struct SQLDA_TYPE ); *env *reply_err max_len *output_buffer *input_sqlda get_scale_qualifier extern /*____________________________________________
Stored Procedures Shell Routines Interface process_event_queue extern /*_____________________________________________________________________ This function performs waited I/O, $RECEIVE handling. ___________________________________________________________________*/ void process_event_queue(void); register_spe extern /*_____________________________________________________________________ This function registers an spe with its Pathway Server class.
Stored Procedures Installation of Stored Procedures Returns: if successful -- SPELIB_OK else -- an SPELIB error ___________________________________________________________________*/ short int TRACEON( void *env , unsigned long int max_len , char *output_buffer , short int *reply_err ); Installation of Stored Procedures The following describes the procedures and mechanisms for installing stored procedures and for removing them when they are no longer needed.
Stored Procedures Catalog Access ADD PROCEDURE The ADD PROCEDURE statement adds one entry in the ZNUPROC table for procedure name mapping, and adds one entry in the ZNUPCOL table for each parameter definition. ADD PROCEDURE logical-procedure-name PAT[HMON-NAME] pathmon-name SER[VERCLASS] server-class-name [ SERVICE service-name ] [ NUM_RESULT_SETS result-set-count ] [ ( parameter-declaration [, ...
Stored Procedures • • Other Considerations Use the ODBC functions SQLProcedures and SQLProcedureColumns. These are described in the Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide. CORE/ODBC Access The CORE SQL user calls the ODBC functions SQLProcedures and SQLProcedureColumns, as described in the Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide, to obtain catalog information. Other Considerations The following describes additional topics related to stored procedures.
Stored Procedures Fault-Tolerant Programming A Pathway server class program can explicitly abort a transaction or cause a transaction to be aborted. As a result, it is necessary for the NonStop ODBC Server to check transaction status after the server class program replies to a stored procedure request.
Stored Procedures Security Security The NonStop ODBC server issues the ServerClass_Send in such a way that the Guardian user ID with which the client is connected participates in the call. As described in the NonStop TS/MP Pathsend and Server Programming Manual, LINKMON performs authorization checks to ensure that the user ID conforms to the OWNER and SECURITY attributes configured for the server class.
Stored Procedures Sharing Procedures Because there is so much similarity between the TSQL-mode and the CORE-mode stored procedure models and because both kinds of procedures are implemented in server class processes, it is possible for any client to execute any procedure. For clean sharing of procedures, the following guidelines are suggested: • • • • Use procedure names that are valid names for both Transact-SQL and CORE SQL.
Stored Procedures Sharing Procedures HP NonStop ODBC Server Reference Manual—429151-002 5- 60
6 Using Pass-Through Mode The HP NonStop ODBC Server provides a pass-through mode that you use to execute HP NonStop SQL/MP statements, catalog utility statements, and trace statements, and to specify various ODBC options and configurations.
Using Pass-Through Mode Setting Server Options Setting Server Options You can enter commands in pass-through mode to specify the configuration of certain server options dealing with the SQL access mode (read/write or read only), the cursor mode (update or read only), the maximum number of rows returned in response to a query, and the transaction isolation level (browse, repeatable, or stable).
Using Pass-Through Mode Using Pass-Through Syntax Using Pass-Through Syntax You use pass-through mode by embedding keywords within a SELECT statement. Pass-through statements use the following syntax: SELECT "TDM: [ SET server-option-specification ] " [ SQL nonstop-sql-statement ] [ UTIL catalog-utility-statement ] TDM: is a required keyword that identifies a pass-through statement. SET server-option-specification specifies the server option specification to be applied.
Using Pass-Through Mode Example Server Option Specification Example Server Option Specification The following pass-through statement executes the server option specification SET SQL_ACCESS_MODE: select "tdm: set sql_access_mode ro" Example UNLOCK Statement The following pass-through statement executes the NonStop SQL/MP UNLOCK statement: select "tdm: sql unlock table \test.$disk01.persnl.
Using Pass-Through Mode Setting the Cursor Default Mode Setting the Cursor Default Mode The following command sets the server option SQL_CURSOR_DEFAULT_MODE, which controls the degree of database access given by default to SQL cursors. SELECT "TDM: SET SQL_CURSOR_DEFAULT_MODE { RO } { RW } " RO specifies that cursors not otherwise defined default to the read-only mode. RW specifies that cursors not otherwise defined default to the read/write mode. This is the default for this option.
Using Pass-Through Mode Executing a NonStop SQL/MP Statement level specifies the isolation level.
Using Pass-Through Mode Transactions and Pass-Through Mode Examples The following pass-through statement creates a key-sequenced table: select "tdm: sql create table $vol3.prsntabs.customer (custnum numeric (4) unsigned no default, custname character (25) no default, city character (14) no default, primary key custnum) catalog $vol3.persnl organization key sequenced" select "tdm: util usercat refresh $vol3.
Using Pass-Through Mode Available NonStop SQL/MP Statements Available NonStop SQL/MP Statements A subset of NonStop SQL/MP statements is available in pass-through mode. Table 6-1 through Table 6-4 summarize the NonStop SQL/MP DDL, DCL, DML, and Dynamic SQL statements that you can use. After executing some statements, you might need to update the NonStop ODBC Server mapping tables to make an object available through the NonStop ODBC Server. For details, see Updating the Mapping Tables on page 6-12.
Using Pass-Through Mode Available NonStop SQL/MP Statements Table 6-1. DDL Statements Available in Pass-Through Mode (page 2 of 3) Statement Description ALTER VIEW Alters security attributes for a view or renames a view. Mapping Table Action Needed USERCAT REFRESH If you change the owner of a mapped view, the new owner name is not reflected in the NonStop ODBC Server mapping tables. Mapping Table Action Description A mapping table update is needed only if you rename a mapped view.
Using Pass-Through Mode Available NonStop SQL/MP Statements Table 6-1. DDL Statements Available in Pass-Through Mode (page 3 of 3) Statement Description Mapping Table Action Needed CREATE TABLE Defines and creates a table. USERCAT REFRESH Mapping Table Action Description The table is registered in the NonStop SQL/MP catalog but not in the NonStop ODBC Server mapping tables. You cannot access the table using the NonStop ODBC Server unless you refresh the mapping tables. CREATE VIEW Creates a view.
Using Pass-Through Mode Available NonStop SQL/MP Statements Table 6-2. DCL Statements Available in Pass-Through Mode Mapping Table Action Needed Mapping Table Action Description Statement Description CONTROL EXECUTOR Allows or prohibits parallel evaluation of a query by multiple SQL executors. None – CONTROL QUERY Specifies whether SQL should optimize the speed of processing either for returning the first few rows found or for returning all rows found.
Using Pass-Through Mode Updating the Mapping Tables Table 6-3. DML Statements Available in Pass-Through Mode (page 2 of 2) Mapping Table Action Needed Mapping Table Action Description Statement Description INSERT Inserts rows into a table or protection view. None – SELECT Retrieves data from tables and views. None – UPDATE Updates values in columns of a table or protection view. None – Table 6-4.
Using Pass-Through Mode • • • • Updating the Mapping Tables You create an object in a customized catalog and need to access it using the NonStop ODBC Server. You drop an object from a customized catalog and need to create another object by the same name, or you want the object to be removed from the NonStop ODBC Server mapping table. You create a catalog and want to access it using the NonStop ODBC Server. You rename an object.
Using Pass-Through Mode Using SELECT Statements Using SELECT Statements When you execute a SELECT statement in pass-through mode, the NonStop ODBC Server receives NonStop SQL/MP data and translates it to ODBC/SQL Server format. If the data you select is of the NonStop SQL/MP INTERVAL data type, you will receive an error message, because the NonStop ODBC Server does not support the INTERVAL data type.
Using Pass-Through Mode Examples of Using Pass-Through Mode EXECUTE Statement When used through the NonStop ODBC Server, the implementation of the EXECUTE statement differs from the NonStop SQL implementation in the following ways: • • Named parameters are not allowed. You must use the SQLCI version of the statement (as opposed to the programmatic SQL version).
Using Pass-Through Mode Creating or Altering Tables To make the new catalog available through the NonStop ODBC Server, customize it using the USERCAT INSTALL statement: select "tdm: util usercat install \test01.$users.addres" Creating or Altering Tables The following examples show how to use pass-through mode to create or alter tables. Note.
Using Pass-Through Mode Creating or Altering Tables Creating Columns With Default Values When you create a table with the CREATE TABLE statement, you cannot specify default values for columns. You can specify default values, however, by creating the table in pass-through mode: select "tdm: sql create (custnum numeric custname character city character catalog $vol3.sales” table $vol3.saletabs.
Using Pass-Through Mode Creating or Altering Views Altering Table Attributes When you create a table using the CREATE TABLE statement, the table is created with NonStop SQL/MP default attributes. You can change the attributes, however, by altering the table in pass-through mode: select "tdm: sql alter table $vol3.prsntabs.employee secure 'cc-u' " select "tdm: sql alter table $vol3.prsntabs.dept nopurgeuntil jan 01 1995 no audit" You do not need to update the mapping tables after altering table attributes.
Using Pass-Through Mode Managing Resource Accounting After creating a view in pass-through mode, you can update the mapping tables using the REFRESH statement: select "tdm: util usercat refresh $vol3.
Using Pass-Through Mode Locking Data Locking Data The following examples show how to use pass-through mode to lock data. Locking or Unlocking a Table You can obtain an exclusive lock by using the LOCK statement in pass-through mode: select "tdm: sql lock table $vol3.prsntabs.employee in exclusive mode" update $vol3_pertabs..employee set salary = salary * 1.15 where deptnum = 2500 select "tdm: sql unlock table $vol3.prsntabs.
7 Managing Customized Catalogs A customized catalog is a HP NonStop SQL/MP catalog available through the HP NonStop ODBC Server. To make the customized catalog available, the NonStop ODBC Server creates special tables to map ODBC/SQL Server objects and data types to NonStop SQL/MP objects and data types or to Pathway objects. You will occasionally need to access these mapping tables to locate objects or to perform maintenance tasks. A customized catalog corresponds to an ODBC/SQL Server database.
Managing Customized Catalogs Summary of Tasks Figure 7-1. A NonStop ODBC Server Mapping Table NonStop SQL/MP Catalog \test.$vol2.persnl NonStopODBC Server View of test_vol2_persnl Mapping Tables System Tables ZVUCOL syscolumns Sample Mapping Table sysindexes ZVUIX ••• Owner dbo dbo User Tables employee department employee_list SQL Server / NonStop ODBC Name SQL/MP Name syscolumns \test.$vol2.persnl.zvucol sysindexes \test.$vol2.persnl.zvuix dbo sysobjects \test.$vol2.persnl.
Managing Customized Catalogs Summary of Tasks Table 7-1. Tasks That Involve Customization and Catalog Utilities (page 1 of 2) Where to Find Further Information Task What You Need to Do Access a NonStop SQL/MP catalog using the NonStop ODBC Server First, customize the catalog. SYSCAT INSTALL Statement on page 7-18 and USERCAT INSTALL Statement on page 7-20 Create a database using the NonStop ODBC Server Log on to the NonStop ODBC Server and execute the Transact-SQL statement CREATE DATABASE.
Managing Customized Catalogs Locating Objects Using the Mapping Tables Table 7-1. Tasks That Involve Customization and Catalog Utilities (page 2 of 2) Where to Find Further Information Task What You Need to Do List all NonStop SQL/MP catalogs available through the NonStop ODBC Server Check the mapping table that corresponds to the ODBC/SQL Server system table SYSDATABASES.
Managing Customized Catalogs Listing Customized Catalogs Listing Customized Catalogs Although you can list customized catalogs using the NonStop ODBC Server by selecting from the table SYSDATABASES, you might also want to list customized catalogs using SQLCI so you can see both the NonStop SQL/MP name and the ODBC/SQL Server name of the catalog. You can list customized catalogs by accessing the mapping table ZNSDB, which is in the same subvolume as your NonStop SQL/MP system catalog.
Managing Customized Catalogs Locating an Object Using the NonStop SQL/MP Object Name A row is displayed listing the column N_OBJNAME, which contains the NonStop SQL/MP name of the table. For details about the mapping table ZNUOBJ, see ZNUOBJ (For Logical Object Names Mapping) on page 8-51. Locating an Object Using the NonStop SQL/MP Object Name Sometimes you will know the NonStop SQL/MP name of an object but not the corresponding ODBC/SQL Server name.
Managing Customized Catalogs Renaming ODBC or SQL Server Objects Renaming ODBC or SQL Server Objects When you customize or refresh a catalog, the NonStop ODBC Server assigns ODBC/SQL Server names to the objects in the catalog not already registered in the NonStop ODBC Server catalog. How Names Are Assigned When assigning ODBC/SQL Server object names, the NonStop ODBC Server usually assigns the Guardian file name. For example, a table named \TESS.$NOSS.PERSNL.
Managing Customized Catalogs Listing the Mapped Object Names The NonStop ODBC Server assigns simple names as shown in Example 7-1 only if the tables are not partitioned. If a table is partitioned, new names are not assigned. Instead, the NonStop ODBC Server chooses one partition per object to map in ZNUOBJ. Even if primary and secondary partitions of the same object are registered in different catalogs, ZNUOBJ has only one entry for the object.
Managing Customized Catalogs Listing the Mapped Object Names Table 7-2. Description of ZNUOBJ (page 2 of 2) Column Name Data Type Description N_OBJTYPE CHAR (2) The NonStop SQL/MP object type: TA Table VI View N_ORIGIN CHAR (1) A code indicating the type of statement that created the object: T N N_SESSIONID LARGEINT Transact-SQL or CORE SQL NonStop SQL/MP A session ID used internally by the NonStop ODBC Server.
Managing Customized Catalogs Changing ODBC/SQL Server Object Names Example 7-2. ODBC/SQL Server Object Names Generated by USERCAT INSTALL >>select t_objname, n_objname +>from \tess.$noss.persnl.
Managing Customized Catalogs Changing ODBC/SQL Server Object Names To change an ODBC/SQL Server object name using INSERT and DELETE statements, list all columns of the record containing that name and insert a new record that is identical except for the ODBC/SQL Server name, which is column T_OBJNAME. Example 7-3 shows NonStop SQL/MP statements that list all columns of ZNUOBJ for the tables mapped in Example 7-1. Example 7-3. Listing All Columns of ZNUOBJ >>select * from $noss.persnl.
Managing Customized Catalogs Changing ODBC/SQL Server Object Names Example 7-4. Changing the ODBC/SQL Server Object Names in ZNUOBJ >>delete from persnl.znuobj where +>t_objname like '%T_EMP%' or +>t_objname like '%T_T1%'; --- 5 row(s) deleted. >> >>insert into persnl.znuobj (*) values +>(44812, 'T_EMPL178234', 'SQL_LYNNR', 'U', 35906, +>'\TESS.$NOSS.PERSNX.EMPLOYEE', 'TA', 'N', 0); --- 1 row(s) inserted. >> >>insert into persnl.
Managing Customized Catalogs Running the Catalog Utility Statements Running the Catalog Utility Statements The NonStop ODBC Server provides utilities for managing the NonStop ODBC Server mapping tables.
Managing Customized Catalogs Executing the Statements From a TACL Prompt The catalog utility statements are described in the following subsections, but to use them you need the information presented in this subsection. Executing the Statements From a TACL Prompt You execute the statements by running the NOSCOM program from the TACL prompt. The general syntax of NOSCOM is: NOSCOM statement statement specifies a catalog utility statement listed in Table 7-3. Specify object names in Guardian format.
Managing Customized Catalogs Executing the Statements From a TACL Prompt text, enter “r” on the input line at that point, immediately followed by the text to substitute. Hit return to display the modified command string. Hit return again to execute the modified command. To exit FC input mode, enter only “//” on the input line. HELP The HELP command displays help text for NOSCOM commands.
Managing Customized Catalogs Running the Statements Using Pass-Through Mode The following INFO command displays all tables in a single ODBC catalog on all volumes and all subvolumes: INFO TABLE $*.*.* [ FROM odbc-catalog] If the wild card pattern is not provided, the current volume and subvolume values are used. If the ODBC catalog name is not provided, the default is the current volume and subvolume.
Managing Customized Catalogs Privileged Users TDM: is a required keyword that indicates this is a pass-through statement. UTIL is a required keyword that indicates this is a catalog utility statement. catalog-utility-statement specifies one of the catalog utility statements listed in Table 7-3. The syntax of the individual statements is described under Customizing Catalogs. Specify object names using the Guardian format. For details about pass-through mode, see Section 6, Using Pass-Through Mode.
Managing Customized Catalogs Customizing Catalogs reported as file-system errors 48 and 49. It is possible to set the security access list on base tables so that even a privileged user might not be able to complete a configuration statement.
Managing Customized Catalogs SYSCAT INSTALL Statement Example The following statement customizes the NonStop SQL/MP system catalog in the $SYSYEM.SQL subvolume. ADD DEFINE =_NSODBC_SYSTEM_CATALOG, CLASS CATALOG, SUBVOL $system.
Managing Customized Catalogs USERCAT INSTALL Statement USERCAT INSTALL Statement The USERCAT INSTALL statement customizes a NonStop SQL/MP user catalog. To use the NonStop ODBC Server to access a catalog created with NonStop SQL/MP, you must first customize the catalog. Note. If you create the catalog through the NonStop ODBC Server using the CREATE DATABASE statement, you do not need to customize the catalog. For information about the CREATE DATABASE statement, see Section 4, Transact-SQL Language.
Managing Customized Catalogs USERCAT INSTALL Statement EMPTY installs the user catalog and populates the mapping tables without installing the entries for the user-created NonStop SQL/MP objects in the current catalog. Example The following statement customizes the NonStop SQL/MP catalog named \TEST.$VOL2.PERSNL: USERCAT INSTALL \test.$vol2.
Managing Customized Catalogs Decustomizing Catalogs If an error occurs during USERCAT INSTALL, then one of the following occurs: • • • If the original catalog was a user catalog customized for the NonStop ODBC Server, then after USERCAT REFRESH fails, the original NonStop ODBC Server customized user catalog is restored. If the original catalog was a NonStop SQL/MP user catalog, then after USERCAT INSTALL fails, the NonStop SQL/MP user catalog is restored.
Managing Customized Catalogs SYSCAT DEINSTALL Statement You can run SYSCAT DEINSTALL only from a NOSCOM prompt; it cannot be run as a NonStop ODBC Server pass-through command. SYSCAT DEINSTALL has the following syntax: SYSCAT DEINSTALL [ RESTRICT | CASCADE ] RESTRICT empties the catalog mapping tables before the tables are dropped. This option is the default.
Managing Customized Catalogs USERCAT DEINSTALL Statement If SYSCAT DEINSTALL succeeds, you must run SYSCAT INSTALL before the system catalog can be run again against the NonStop ODBC Server. If SYSCAT DEINSTALL fails for any reason, the NonStop ODBC Server customized system catalog is unchanged. If Catalogs Are Still Customized If your system contains a customized user catalog, you will receive an error message, and the SYSCAT DEINSTALL statement will not be executed.
Managing Customized Catalogs USERCAT DEINSTALL Statement CASCADE drops all NonStop ODBC Server objects registered in catalog-name before dropping the mapping tables. A NonStop ODBC Server object is an object created through the NonStop ODBC Server using CORE SQL or Transact-SQL statements. The underlying NonStop SQL/MP table is unaffected. If you specify CASCADE, you must also specify catalog-name.
Managing Customized Catalogs USERCAT DEINSTALL Statement If the catalog contains objects created by using NonStop SQL/MP, the mapping tables and NonStop ODBC Server objects are dropped, but the catalog is not dropped. A warning is displayed indicating that the catalog is not empty and was not dropped. Effects of USERCAT DEINSTALL USERCAT DEINSTALL does the following: • • • • • • • Drops all the NonStop ODBC Server mapping tables from your catalog subvolume.
Managing Customized Catalogs Maintaining Catalogs Maintaining Catalogs The following statements are used to maintain catalogs: • • • • • • • SYSCAT REFRESH—ensures that objects in the system catalog and corresponding entries in the mapping tables balance. USERCAT REFRESH—ensures that objects in user catalogs and corresponding entries in the mapping tables balance. SYSCAT VALIDATE—reports inconsistencies in references between the system catalog and the mapping tables.
Managing Customized Catalogs SYSCAT REFRESH Statement creates a mapping entry. If a table with the same name as output-filename already exists, NOSUTIL returns an error and the SYSCAT REFRESH statement terminates. Example The following statement refreshes the NonStop SQL/MP system catalog: syscat refresh Use this statement to verify that all objects registered in the system catalog are listed in the mapping tables. Objects not listed are added to the mapping tables.
Managing Customized Catalogs USERCAT REFRESH Statement If An Object Is Partitioned For each object with multiple partitions, SYSCAT REFRESH adds only one entry to the ZNUOBJ mapping table. Even if primary and secondary partitions of the same object are registered in different catalogs, ZNUOBJ has only one entry for the object.
Managing Customized Catalogs • • USERCAT REFRESH Statement You create the object using NonStop SQL/MP when the catalog is already customized. You delete a row from the NonStop ODBC Server mapping table ZNUOBJ. You can refresh the system catalog if you delete a row from the ZNSDB mapping table. USERCAT REFRESH re-creates the mapping entry for the customized catalog and refreshes the object mapping table for the system catalog.
Managing Customized Catalogs USERCAT REFRESH Statement Effects of USERCAT REFRESH USERCAT REFRESH does the following: • • • • • • • Checks whether each table, view, and index in the specified catalog has an entry in the mapping tables. If the object is not entered in the mapping tables, USERCAT REFRESH enters it. Re-creates any NonStop ODBC Server catalog table, index, or view not found. Adds TABLE, or VIEW, or INDEX, as a logical username to ZNSUS for any object not found in the mapping table.
Managing Customized Catalogs SYSCAT VALIDATE Statement For information about viewing and changing the mapped names, see Renaming ODBC or SQL Server Objects on page 7-7. Note. USERCAT REFRESH truncates object names that are more than 8 characters long. To preserve a longer object name, manually add the object to ZNUOBJ by executing ADD TABLE, INDEX, or VIEW. USERCAT REFRESH also changes an object name in ZNUOBJ if you change the owner of the object from NonStop SQL/MP.
Managing Customized Catalogs USERCAT VALIDATE Statement Example The following statement validates the NonStop SQL/MP system catalog: syscat validate Effects of SYSCAT VALIDATE SYSCAT VALIDATE reports all object reference inconsistencies for the system catalog. SYSCAT VALIDATE verifies that: • • • • • • • Mapping tables and views exist. The name of the database is registered in the NonStop ODBC Server system catalog.
Managing Customized Catalogs USERCAT VALIDATE Statement USERCAT VALIDATE has the following syntax: USERCAT VALIDATE [ catalog-name [ LOG output-filename ] ] catalog-name specifies the name of the subvolume containing the NonStop SQL/MP catalog to be validated. The catalog-name appears in Guardian format and can include the node and volume. If you omit catalog-name, the current subvolume is used.
Managing Customized Catalogs CLEANUP Statement USERCAT VALIDATE also produces a list of all exceptions detected. CLEANUP Statement Use the CLEANUP statement to drop temporary objects. Although the NonStop ODBC Server drops temporary objects at the end of each session, if the objects are not dropped, you must drop them manually.
Managing Customized Catalogs SYSCAT UPGRADE Statement If you omit session-id, all temporary objects that belong to the current user are dropped. Examples The following examples show uses of CLEANUP. Drop All Temporary Objects The following statement drops all temporary objects in the PERSNL catalog: cleanup $oook.persnl All temporary objects are dropped regardless of who owns them.
Managing Customized Catalogs USERCAT UPGRADE Statement Effects of SYSCAT UPGRADE SYSCAT UPGRADE makes the following changes to the system catalog: • • • • • • Alters the definition of the following tables and supplies a default value for each new column: ZNSUS, ZNSDB, and ZNSPROF Renames ZNSTRC to ZNSTRA and ZNSDT to ZNUDT Adds the following tables: ZNSCON ZNSGOV ZNSSER ZNSSCS ZNSDEF ZNSNET ZNSSCFG ZNSSMAP ZNSUMAP Adds mapping entries for these objects Updates the database version value in ZNSD
Managing Customized Catalogs • Maintaining Mapping Tables Generates an error if the specified catalog is not a NonStop ODBC Server version 100 or 110 catalog Maintaining Mapping Tables Table 7-4 lists the NonStop ODBC Server mapping table statements by type. You can execute these statements either from the TACL prompt by running NOSCOM or from the NonStop ODBC Server Configuration Manager. Use these statements to add items to, change existing items in, and delete items from the mapping tables.
Managing Customized Catalogs Maintaining Mapping Tables Table 7-4.
Managing Customized Catalogs Accounting Log (ACC_LOG) Statements Table 7-4.
Managing Customized Catalogs ALIAS Statements Example CREATE ACC_LOG IN persnl SECURE COOO DROP ACC_LOG The DROP ACC_LOG statement drops an existing accounting log table and removes the mapping entry for the log table in the specified database. To execute DROP ACC_LOG, you must be a privileged user. DROP ACC_LOG FROM database FROM database specifies the name of the database from which to drop the accounting log table.
Managing Customized Catalogs ALIAS Statements ADD ALIAS The ADD ALIAS statement associates an alias username with a logical username and, optionally, with a profile. An alias username cannot refer to another alias username. To execute ADD ALIAS, you must be authorized to modify the system catalog or be the user associated with logical-username.
Managing Customized Catalogs ALIAS Statements UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle. Considerations – ADD ALIAS The ADD ALIAS statement: • • • • • • • Creates a new entry in the ZNSALT table that allows the client to use the alias username to log in or to qualify objects. An alias username is translated only once.
Managing Customized Catalogs ALIAS Statements alias-username specifies the alias username you want to modify. NOS_USERNAME logical-username specifies the logical username associated with alias-username. logical-username must begin with an alphabetic character, followed by alphabetic, numeric, and underscore characters. CHANGE_PASSWORD_OPTION { 0 | 1 | 2 } specifies the level of notification sent to users for Safeguard passwords that are expired or are about to expire.
Managing Customized Catalogs ALIAS Statements Example MODIFY ALIAS janet PROFILE mgr UPDATE_SYSTEM_CONFIG REMOVE ALIAS The REMOVE ALIAS statement removes the existing association between an alias username and a logical username. To execute REMOVE ALIAS, you must be a privileged user. REMOVE ALIAS alias-username [ UPDATE_SYSTEM_CONFIG ] alias-username specifies the alias username. UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time.
Managing Customized Catalogs CONTROL Statements CONTROL Statements The following statements allow you to control various aspects of query execution using NonStop SQL/MP CONTROL statements: • • ADD CONTROL REMOVE CONTROL ADD CONTROL The ADD CONTROL statement adds a new NonStop ODBC Server control statement entity to execute at start up time and re-initialization boundaries.
Managing Customized Catalogs • • CONTROL Statements Generates an error at run time if control-text is not a valid NonStop SQL/MP CONTROL statement Generates an error if you are not authorized to modify the system catalog Example ADD CONTROL late_bind "CONTROL QUERY BIND NAMES AT EXECUTION" REMOVE CONTROL The REMOVE CONTROL statement deletes an existing control statement entity in the ZNSCON table. To execute REMOVE CONTROL, you must be authorized to modify the system catalog.
Managing Customized Catalogs DEFINE Statements DEFINE Statements The following statements allow you to use DEFINEs with the SQL Communications Subsystem (SCS): • • ADD DEFINE REMOVE DEFINE A DEFINE is a named set of attribute-value pairs associated with a process. You can use DEFINEs to pass information to a process when you start the process. For the NonStop ODBC Server, the DEFINE is linked to an SCS configuration entity and is propagated when the SCS starts a new process.
Managing Customized Catalogs DEFINE Statements FOR scs-processname is an alphabetic string up to 15 characters in length in the following format that specifies the name of the SCS process to associate with this DEFINE statement: [\node.]$process-name All processes dependent on scs-processname are also affected by the new DEFINE. If you omit scs-processname, the DEFINE statement applies to all SCS processes and their dependent processes.
Managing Customized Catalogs INDEX Statements All processes dependent on scs-processname are also affected. If you omit scs-processname, the DEFINE statement applies to all SCS processes and their dependent processes. UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs INDEX Statements AS logical-objectname specifies the logical name for the index; logical-objectname can be in any of the following formats: object-name logical-user-name.object-name database-name.logical-user-name.object-name database-name..object-name Any value you specify for database-name must be a customized NonStop ODBC Server database. If you omit database-name, the default is the default database defined in the current profile.
Managing Customized Catalogs INDEX Statements Example ADD INDEX \sales.$db1.region1.ix AS db.o.ix REMOVE INDEX The REMOVE INDEX statement deletes an existing mapping entry for the specified index. The underlying NonStop SQL/MP index is unaffected. To execute REMOVE INDEX, you must be a privileged user.
Managing Customized Catalogs Network Services (NET_SERVICE) Statements Network Services (NET_SERVICE) Statements Use the following statements to configure and manage network services: • • • ADD NET_SERVICE MODIFY NET_SERVICE REMOVE NET_SERVICE ADD NET_SERVICE The ADD NET_SERVICE statement creates a new network service entity for a specified SCS process. To execute ADD NET_SERVICE, you must be a privileged user.
Managing Customized Catalogs Network Services (NET_SERVICE) Statements NET_PROTOCOL { TCP/IP | SPX/IPX } specifies TCP/IP or SPX/IPX as the network protocol to use for this service. NET_NAME net-name identifies the network service; net-name is a string up to 16 characters in length that must match the name of the service in your system SERVICES file. Caution. You can enter names for the NET_PROTCOL and NET_NAME names using lowercase letters because NOSUTIL upshifts each name.
Managing Customized Catalogs Network Services (NET_SERVICE) Statements SO_LINGER { 0 | 1 } specifies whether TCP/IP closes all connections gracefully and wait for data transfer to complete: 0 Do not require connections to close gracefully. 1 Always close connections gracefully. The SO_LINGER option is provided only for compatibility. All C TCP/IP connections close gracefully.
Managing Customized Catalogs Network Services (NET_SERVICE) Statements IOP_NAME iop-name is an alphabetic string up to 15 characters in length that identifies a valid network process in the following format: [\node.]$process-name You set the default for this parameter when you install the NonStop ODBC Server. Each network protocol uses a different default name for a network process; the value you specify depends on your protocol and whether your node uses the default name.
Managing Customized Catalogs Network Services (NET_SERVICE) Statements Example ADD NET_SERVICE $svc4 NET_PROTOCOL TCP/IP NET_NAME odbctst1 & IOP_NAME \netsys.$lam1 MODIFY NET_SERVICE The MODIFY NET_SERVICE statement modifies one or more of the attributes of an existing system network service for a specified SCS process in the ZNSNET table. To execute MODIFY NET_SERVICE, you must be authorized to modify the system catalog.
Managing Customized Catalogs PROCEDURE Statements Caution. If you specify a new value for NET_PROTOCOL, NET_NAME, or IOP_NAME for an SCS process that is currently running, the SCS process will terminate abnormally. Example MODIFY NET_SERVICE $svc4 services_filename $ntwrk.odbc.srvc REMOVE NET_SERVICE The REMOVE NET_SERVICE statement removes the specified system network service. To execute REMOVE NET_SERVICE, you must be a privileged user.
Managing Customized Catalogs PROCEDURE Statements To execute ADD PROCEDURE, you must be authorized to modify the current user catalog. ADD PROCEDURE logical-procedure-name PATHMON_NAME pathmon-processname SERVERCLASS pathway-server-class-name [ SERVICE service-name ] [ NUM_RESULT_SETS result-set-count ] [ RETURN_STATUS { N | Y } ] [ MAX-BUFFER-LEN ipc-message-buffer-len ] [ REMARKS "remarks-text" ] logical-procedure-name specifies the name of the new stored procedure in the following format: [ [database.
Managing Customized Catalogs PROCEDURE Statements PATHMON_NAME pathmon-processname specifies the external Guardian name of the Pathmon process; it is an alphanumeric string up to 15 characters in length in the following format: [\node-name.]$pathmon-processname For example, a pathmon-processname might be $PM or \TESS.$PMN. node specifies the node where the process is running. If node is omitted, the process location defaults to the current node.
Managing Customized Catalogs PROCEDURE Statements The Pathway server class program should also use MAX_BUFFER_LEN to allocate the message buffer for I/O. If the maximum buffer size is different between the requester (the NonStop ODBC Server) and the Pathway server class program, the lesser of the two sizes is used. REMARKS remarks-text specifies arbitrary comments for this stored procedure; it is a string up to 254 characters in length enclosed in double quotation (") marks.
Managing Customized Catalogs PROCEDURE Statements parameter-name specifies the parameter name; parameter-name is an alphanumeric string up to 60 characters in length that must begin with an alphabetic character, followed by alphabetic, numeric, and underscore characters.
Managing Customized Catalogs Data Type PROCEDURE Statements Parameters Value Range Restrictions REAL precision = 22 DOUBLE PRECISION precision = 54 NUMERIC precision scale 0 < precision <= 18 0 <= scale < precision DECIMAL precision scale 0 < precision <= 18 0 <= scale < precision DATE Format: YEAR to DAY Julian restrictions TIME TIMESTAMP Format: HOURS to SECOND Fractional seconds 6 digits microseconds All of the preceding data types can be NULLABLE.
Managing Customized Catalogs PROCEDURE Statements Example The following ADD PROCEDURE_COLUMNS statement adds a parameter definition to the stored procedure named SELECT_DEPT3000, owned by user SQL_DAVE, in the NonStop ODBC Server catalog \TESS.$DATA1.DB: ADD PROCEDURE_COLUMNS tess_data1_db.sql_dave.
Managing Customized Catalogs PROFILE Statements REMOVE PROCEDURE The REMOVE PROCEDURE deletes the entry for a stored procedure from the ZNUPROC table and all entries associated with the procedure from the ZNUPCOL table. To execute REMOVE PROCEDURE, you must be a privileged user. REMOVE PROCEDURE logical-procedure-name logical-procedure-name specifies the name of the stored procedure you want to delete.
Managing Customized Catalogs PROFILE Statements ADD PROFILE The ADD PROFILE statement defines the configuration of a new named profile. To execute ADD PROFILE, you must be a privileged user.
Managing Customized Catalogs PROFILE Statements schema-name is an alphanumeric string up to 60 characters in length. It must begin with an alphabetic character, followed by alphabetic, numeric, and underscore characters. The default for this parameter is the logical username of the current user.
Managing Customized Catalogs PROFILE Statements ACC_MODE_ON { Y | N } specifies whether to include query accounting in this profile: Y Perform accounting and use the existing accounting log table you specify in acc-name. N Do not perform accounting for this profile. ACC_LOGTABLE_NAME acc-name specifies the name of the existing accounting log table to use for this profile; acc-name is an alphanumeric string up to 182 characters in length in the following format: database.owner.
Managing Customized Catalogs PROFILE Statements QST_MODE_ON { Y | N } specifies whether to log query status for each statement executed: Y Log query status for each statement executed. N Do not log query status for each statement executed.
Managing Customized Catalogs PROFILE Statements 1 SQL_TXN_READ_COMMITTED STABLE 2 SQL_TXN_REPEATABLE_READ REPEATABLE 3 SQL_TXN_SERIALIZABLE REPEATABLE (serializable) SQL_UNSUPPORTED { E | W | I } specifies the action taken by the parser/translator when it detects a syntax error for dialect conflicts or nonconforming syntax: E Exceptions are to be reported as errors. W Exceptions are to be reported as warnings.
Managing Customized Catalogs PROFILE Statements CLOSE_TABLES_PER_SESSION { Y | N } specifies whether NonStop SQL/MP tables are closed at the end of a session: Y Close tables at the end of the session. N Do not close tables at the end of the session. N is the default. UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs PROFILE Statements To execute MODIFY PROFILE, you must be authorized to modify the system catalog.
Managing Customized Catalogs Query Status Log (QST_LOG) Statements REMOVE PROFILE The REMOVE PROFILE statement removes a profile entry from the ZNSPROF table. To execute REMOVE PROFILE, you must be a privileged user. REMOVE PROFILE profile-name [ UPDATE_SYSTEM_CONFIG ] profile-name specifies the profile you want to remove.
Managing Customized Catalogs Query Status Log (QST_LOG) Statements SECURE security-string is a four-character alphabetic string that specifies the Guardian read, write, execute, and purge (RWEP) access for the query status log table. For more information about security for NonStop SQL/MP objects, see the “Security” entry in the NonStop SQL/MP Reference Manual.
Managing Customized Catalogs Resource Governing Statements Resource Governing Statements Use the following statements to configure and manage resource governing policy entities that control various aspects of SQL statement execution: • • • ADD GOVERNING MODIFY GOVERNING REMOVE GOVERNING ADD GOVERNING The ADD GOVERNING statement creates a new governing policy entity or adds one or more attribute-value pairs to an existing entity. To execute ADD GOVERNING, you must be a privileged user.
Managing Customized Catalogs Resource Governing Statements GOV_ATTRIBUTE gov-attribute specifies one of the following aspects of query execution to apply to gov-action: ELAPSED_TIME Maximum number of seconds in wall-clock time to wait for an SQL statement to complete, measured as follows: • • For an EXECUTE IMMEDIATE statement, the elapsed time begins when the statement executes and ends when the statement completes execution.
Managing Customized Catalogs Resource Governing Statements GOV_ACTION gov-action specifies the action you want the NonStop ODBC Server to take when a query exceeds the limit-value. gov-action can be only one of the following: COMMIT Stop and commit the transaction. CONTINUE Continue without any changes.
Managing Customized Catalogs Resource Governing Statements Examples The following ADD GOVERNING statement creates a governing policy entity named q3 that lowers the execution priority to 50 if an SQL statement takes more than 60 minutes to complete: ADD GOVERNING q3 GOV_ATTRIBUTE EXECUTION_TIME & LIMIT_VALUE 60 GOV_ACTION "PRIORITY 50" The following statement adds a new gov-attribute, limit-value, and gov-action combination to the existing governing policy entity named q3: ADD GOVERNING q3 GOV_ATTRIBUTE E
Managing Customized Catalogs Resource Governing Statements For a description of the MODIFY GOVERNING statement parameters, see ADD GOVERNING on page 7-75.
Managing Customized Catalogs SERVERCLASS Statements UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs SERVERCLASS Statements ADD SERVERCLASS The ADD SERVERCLASS statement creates a new server class configuration entity in the ZNSSER table. To execute ADD SERVERCLASS, you must be authorized to modify the system catalog.
Managing Customized Catalogs SERVERCLASS Statements AVAILABLE_SERVERS available-servers is an unsigned integer number in the range 0 through 254 that specifies the number of server processes to start for this server class when SCS is started; available-servers must be less than or equal to the value you specify for max-servers.
Managing Customized Catalogs SERVERCLASS Statements NOS_CREATE_OPTIONS nos-create-options is an INT(2) number that specifies create options for the NOS process.
Managing Customized Catalogs PRIMARY_EXTENT=n SERVERCLASS Statements SECONDARY_EXTENT=n MAX_EXTENTS=n specifies values for the primary and secondary extent sizes and the maximum number of extents used in the creation of SQL tables and indexes. The same values are used for both tables and indexes. You can specify none, one, two, or all options.
Managing Customized Catalogs SERVERCLASS Statements LOGIN_TIMEOUT_SEC seconds is an INT(4) value that specifies the time in seconds SCS waits before sending a logon denied message to a client who is trying to logon to a NonStop ODBC Server. This option applies only when the MAX_SERVERS option has reached its limit. SCS measures seconds from when it first receives the client’s logon request. A value less than or equal to zero (0) disables the option, The default is -1.
Managing Customized Catalogs SERVERCLASS Statements UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle. Considerations – ADD SERVERCLASS The ADD SERVERCLASS statement also creates a new entry in the ZNSMAP table that maps the new server class entity to scs-processname.
Managing Customized Catalogs SERVERCLASS Statements To execute MODIFY SERVERCLASS, you must be a privileged user.
Managing Customized Catalogs SERVERCLASS Statements Considerations – REMOVE SERVERCLASS The REMOVE SERVERCLASS statement generates an error if you are not a privileged user. Example REMOVE SERVERCLASS alpha START SERVERCLASS The START SERVERCLASS statement starts a specified server class. To execute START SERVERCLASS, you must be a privileged user. START SERVERCLASS server-name OF scs-processname [ UPDATE_SYSTEM_CONFIG ] server-name specifies the name of the server class definition you want to start.
Managing Customized Catalogs Server Class Mapping (SMAP) Statements Server Class Mapping (SMAP) Statements Use the following statements to map a server class to an SCS configuration entity: • • • ADD SMAP MODIFY SMAP REMOVE SMAP ADD SMAP The ADD SMAP creates a new mapping between an existing server class and an SCS configuration entity. To execute ADD SMAP, you must be authorized to modify the system catalog.
Managing Customized Catalogs • • • Server Class Mapping (SMAP) Statements Generates a warning if scs-processname is not a valid NonStop ODBC Server SCS configuration entity found in ZNSSCS Generates a warning if server-name is not a valid NonStop ODBC Server server class configuration entity found in ZNSSER Generates an error if you are not a privileged user Example ADD SMAP myscs alpha USE_AS_DEFAULT Y UPDATE_SYSTEM_CONFIG MODIFY SMAP The MODIFY SMAP statement changes an existing mapping between a ser
Managing Customized Catalogs Server Class Mapping (SMAP) Statements UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements Considerations – REMOVE SMAP The REMOVE SMAP statement: • • • • • Deletes the entry in ZNSMAP that maps scs-processname to server-name Generates an error if scs-processname is not a valid NonStop ODBC Server SCS configuration entity found in ZNSSCS Generates an error if server-name is not a valid NonStop ODBC Server server class configuration entity found in ZNSSER Generates an error if scs-processname and server-name are not map
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements ADD SCS The ADD SCS statement creates a new SCS named configuration with a set of attribute values in the ZNSSCS table. To execute ADD SCS, you must be a privileged user.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements SCS_OBJECT scs-object-file-name specifies the physical location of the SCS object file; it is an alphabetic string up to 35 characters in length in the following format: [\node.]$volume.subvolume.filename SCS_LIBRARY_FILE scs-library-file-name specifies the name of a user library for the new process; it is an alphabetic string up to 35 characters in length. Use this parameter to specify a library other than the default library.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements SCS_CREATE_OPTIONS scs-create-options is an INT(2) number that determines the environment of the SCS process : 6 Inherit DEFINEs from the calling process (NOSUTIL) but ignore DEFINEs stored in ZNSDEF. 22 Inherit DEFINEs from the calling process (NOSUTIL) and include DEFINEs from ZNSDEF. 23 Inherit DEFINEs from the calling process (NOSUTIL), include DEFINEs from ZNSDEF, and run the SCS process at a low PIN.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements ERR_FILE error-file-name specifies the location of the error log file for the SCS process; it is an alphabetic string up to 35 characters in length in the following format: [\node.]$volume.subvolume.filename IN_BUFFER_SIZE_B input-buffer-size is an integer in the range 2200 through 32000 that specifies the size of the communication buffer in bytes between NonStop TCP/IP or NonStop IPX/SPX and an SCS process.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements NOSUTIL_OBJECT nosutil-object-file-name is a 34-character NonStop SQL/MP program. NOSUTIL_LIBRARY_FILE nosutil-library-file-name is an alphabetic string up to 35 characters in length that specifies the name of a user library for the NOSUTIL process. Use this parameter to specify a library other than the default library.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements NOSUTIL_RUN_OPTIONS nosutil-run-options is an alphanumeric string value, internally formatted, up to 240 characters in length that specifies run options for the NOSUTIL process. UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements MODIFY SCS The MODIFY SCS statement modifies one or more of the attribute values or adds a new attribute-value pair in an existing SCS named configuration in the ZNSSCS table. To execute MODIFY SCS, you must be a privileged user.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements Considerations – MODIFY SCS The MODIFY SCS statement generates an error if you are not authorized to modify the system catalog or a warning if network-service-name cannot be found in the ZNSNET table. Example MODIFY SCS $scs02 scs_library_file bugfix REMOVE SCS The REMOVE SCS statement removes an SCS named configuration from the ZNSSCS table. To execute REMOVE SCS, you must be a privileged user.
Managing Customized Catalogs • • • • • SQL Communications Subsystem (SCS) Statements Sets any defines stored in ZNSDEF for scs-processname Creates a new SCS process Generates an error, if scs-processname does not exist in ZNSSCS Generates an error, if NOSUTIL cannot create the SCS process Fails to start NOS processes, if the user is not a registered ODBC user Example START SCS $myscs STATUS SCS The STATUS SCS statement returns information about an existing SCS process.
Managing Customized Catalogs SQL Communications Subsystem (SCS) Statements STOP SCS The STOP SCS statement stops an SCS process. To execute STOP SCS, you must be a privileged user. STOP SCS scs-processname [ NOWAIT ] scs-processname specifies the process name of the SCS process you want to stop. The SCS process stops when system configuration values are next updated.
Managing Customized Catalogs System Configuration Default (SCFG) Statements System Configuration Default (SCFG) Statements Use the following statements to configure and manage system configuration default values: • • • ADD SCFG MODIFY SCFG REMOVE SCFG ADD SCFG The ADD SCFG statement adds a new system configuration default value in the system configuration table (ZNSSCFG). To execute ADD SCFG, you must be a privileged user.
Managing Customized Catalogs TABLE Statements scfg-name specifies the attribute for which you are modifying a default value; scfg-name is an alphanumeric string up to 60 characters in length and must be the name of a column in the ZNSSCFG table. For a list of the ZNSSCFG column names, see Appendix D, Summary of System Installation Defaults, The value you specify must be alphanumeric; there is no further check for validity. scfg-value specifies the new default value for scfg-name.
Managing Customized Catalogs TABLE Statements ADD TABLE The ADD TABLE statement makes a NonStop SQL/MP base table visible to a NOSCOM user. No REFRESH of the entire database is necessary. To execute ADD TABLE, you must be a privileged user. However, the NonStop ODBC Server does not verify privileges on the underlying NonStop SQL/MP table. ADD TABLE nssql-filename [ AS logical-objectname ] nssql-filename is a Guardian file name that represents the physical location of the NonStop SQL/MP table.
Managing Customized Catalogs • • • • TABLE Statements Generates an error if the table you specify is not registered in the NonStop SQL/MP database Generates an error if you are not authorized to modify the system catalog Generates an error if database-name is not the name of a customized NonStop ODBC Server database Generates an error if logical-object-name is already mapped to a different Guardian file name Example ADD TABLE \persnl.$db1.region1.tblA AS persnl_db1_region1.dbo.
Managing Customized Catalogs • • • Trace Log (TRA_LOG) Statements Deletes a mapping entry in ZNUOBJ for the NonStop SQL/MP table indicated by nssql-filename Deletes primary key and index entries for the table in ZNUIX (if they exist). Generates an error if you are not a privileged user Example REMOVE TABLE \persnl.$db1.region1.
Managing Customized Catalogs TRACE Statements DROP TRA_LOG The DROP TRA_LOG statement drops an existing trace log table. To execute DROP TRA_LOG, you must be a privileged user. DROP TRA_LOG table-name table-name specifies the qualified name of the trace log table to drop.
Managing Customized Catalogs TRACE Statements ADD TRACE The ADD TRACE statement adds and configures a new trace entity. To execute ADD TRACE, you must be a privileged user.
Managing Customized Catalogs TRACE Statements CACHE_STATISTICS { Y | N } specifies whether cache statistics should be logged, with a log record being written out at the end of the user session. SP_WRITE { Y | N } specifies whether all PATHSEND write messages generated by stored procedures should be logged. SP_READ { Y | N } specifies whether all NonStop SQL/MP results returned from the invocation of stored procedures should be logged.
Managing Customized Catalogs TRACE Statements MODIFY TRACE The MODIFY TRACE statement changes the configuration of an existing trace entity. To execute MODIFY TRACE, you must be a privileged user.
Managing Customized Catalogs • • • User Mapping (UMAP) Statements Removes the entry for the specified trace from ZNSTRA Generates an error if the system catalog is not customized for the NonStop ODBC Server Generates an error if you are not a privileged user Example REMOVE TRACE trace4 User Mapping (UMAP) Statements Use the following statements to map a user and SCS process to a server class: • • • ADD UMAP MODIFY UMAP REMOVE UMAP ADD UMAP The ADD UMAP statement associates an existing alias username
Managing Customized Catalogs User Mapping (UMAP) Statements UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs User Mapping (UMAP) Statements SER_NAME server-name is an alphanumeric string up to 32 characters in length that specifies the name of the server class configuration entity to associate with a user and profile; server-name must already be mapped to scs-processname in ZNSMAP. UPDATE_SYSTEM_CONFIG resets the time to update system configuration values to the current time. This option causes running system components to reread their configuration values on the next polling cycle.
Managing Customized Catalogs • USER Statements Generates an error if scs-processname and alias-username are not mapped to each other and represented by an entry in ZNSUMAP Example REMOVE UMAP $myscs sql_dave USER Statements Use the following statements to map logical usernames to Guardian usernames: • • • ADD USER MODIFY USER REMOVE USER ADD USER The ADD USER statement associates a logical username with a Guardian username and, optionally, with a profile.
Managing Customized Catalogs USER Statements CHANGE_PASSWORD_OPTION { 0 | 1 | 2 } specifies the level of notification sent to users for Safeguard passwords that are expired or are about to expire: 0 No notification is sent. A user can log in only if the password is correct and has not expired, including the grace period. This is the default. 1 Notification is sent if the password is still in the grace period. A user can then change the password and log in.
Managing Customized Catalogs USER Statements Example In the following example, ADD USER defines a user whose logical username is SQL_Jones and whose Guardian username is SQL.Jones. Jones uses the profile record PERSNL. ADD USER SQL_Jones SQL.Jones PROFILE persnl MODIFY USER The MODIFY USER statement changes the logical username associated with a particular Guardian username. Note. To change the profile name associated with a logical username, use MODIFY ALIAS.
Managing Customized Catalogs USER Statements Example MODIFY USER SQL_Jones SQL.ODBC REMOVE USER The REMOVE USER statement removes the mapping between a logical username and a Guardian username. Note. REMOVE USER does not remove associated alias usernames. Deleting the logical username has no effect on object attributes. To execute REMOVE USER, you must be a privileged user.
Managing Customized Catalogs VIEW Statements VIEW Statements Use the following statements to control whether a NOSCOM user can access a specified view of a NonStop SQL/MP base table: • • ADD VIEW REMOVE VIEW ADD VIEW The ADD VIEW statement makes the specified view of a NonStop SQL/MP base table visible to a NOSCOM user. No REFRESH of the entire database is necessary. To execute ADD VIEW, you must be a privileged user.
Managing Customized Catalogs VIEW Statements Considerations – ADD VIEW The ADD VIEW statement: • • • • • • • • • Creates a new mapping entry in ZNUOBJ for the specified view Adds a mapping entry to ZNSUS if logical-username is not currently mapped to the owner of nssql-filename Generates an error if you do not have read access to nssql-filename, or if you do not have write access to the mapping tables associated with logical-objectname Generates an error if nssql-filename is already mapped in the approp
Managing Customized Catalogs Catalog Support Procedures \node.subvolume.filename \node.$volume.subvolume.filename The NonStop ODBC Server fully qualifies nssql-filename before performing the REMOVE VIEW statement. Any optional values you omit are determined by the user profile of the current process.
Managing Customized Catalogs SQLColumns SQLColumns SQLColumns is a procedure that returns a list of user-requested column names and column information from user-requested tables. The view ZVUOCOL provides support for SQLColumns. ZVUOCOL is a view over COLUMNS, ZNUOBJ, and ZNUDT. Input parameters for SQLColumns are: • • • • TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME Table 7-5.
Managing Customized Catalogs SQLPrimaryKeys SQLPrimaryKeys SQLPrimaryKeys is a procedure that lists keys for base tables. It does not report keys for views. ODBC does not require views to have independent indexes. SQLPrimaryKeys is a view on ZNOBJ, KEYS, COLUMNS, and INDEXES. A view specific to the NonStop ODBC Server is not provided for this result set. Input parameters for SQLPrimaryKeys are: • • • TABLE_QUALIFIER TABLE_OWNER TABLE_NAME Table 7-6.
Managing Customized Catalogs SQLProcedures Description Table 7-7. Result Set for the SQLProcedureColumns Procedure Result Column Data Type Description PROCEDURE_QUALIFIER VARCHAR (60) Assigned null. PROCEDURE_OWNER VARCHAR (60) The owner of the procedure. PROCEDURE_NAME VARCHAR (60) The name of the procedure. COLUMN_NAME VARCHAR (60) The name of the column or parameter.
Managing Customized Catalogs SQLSpecialColumns Table 7-8. Result Set for the SQLProcedures Procedure Result Column Data Type Description PROCEDURE_QUALIFIER VARCHAR (60) Assigned null. PROCEDURE_OWNER VARCHAR (60) The owner of the procedure. PROCEDURE_NAME VARCHAR (60) The name of the procedure. NUM_INPUT_PARAMS INT (2) The number of input parameters defined for the procedure. NUM_OUTPUT_PARAMS INT (2) The number of output parameters defined for the procedure.
Managing Customized Catalogs SQLStatistics Table 7-9. Result Set for the SQLSpecialColumns Procedure (page 2 of 2) Result Column Data Type Description PRECISION INT (4) The number of digits or bits of precision for numeric data types. LENGTH INT (4) The number of bytes for nonnumeric data types. SCALE INT (2) The scale of numeric data types. SQLStatistics SQLStatistics is a procedure that lists index information for base tables.
Managing Customized Catalogs SQLTables Table 7-10. Result Set for SQLProcedureColumns Procedure (page 2 of 2) Result Column Data Type Description COLLATION CHAR (1) The collating sequence of the keys: A D Ascending Descending CARDINALITY INT (2) Assigned null. PAGES INT (2) Assigned null. SQLTables SQLTables lists database, schema name, and table information. Input parameters for SQLTables are: • • • • TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE Table 7-11.
Managing Customized Catalogs HP NonStop ODBC Server Reference Manual—429151-002 7 -128 SQLTables
8 HP NonStop ODBC Server Mapping Tables The HP NonStop ODBC Server acts as a gateway to HP NonStop SQL/MP from external entities. As such, it must provide translation from external concepts and protocols to NonStop SQL/MP concepts and protocols. The NonStop ODBC Server creates and maintains a set of tables, views, and indexes called the NonStop ODBC Server mapping tables.
HP NonStop ODBC Server Mapping Tables Types of Mapping Figure 8-1. A NonStop ODBC Server Mapping Table NonStop SQL/MP Catalog \test.$vol2.persnl NonStop ODBC Server View of test_vol2_persnl Mapping Tables System Tables ZVUCOL syscolumns Sample Mapping Table ZVUIX sysobjects ••• Owner dbo dbo User Tables employee department employee_list SQL Server / ODBC Name syscolumns NonStop SQL/MP Name \test.$vol2.persnl.zvucol sysindexes \test.$vol2.persnl.
HP NonStop ODBC Server Mapping Tables System Table Mapping System Table Mapping The NonStop ODBC Server maintains tables and views that correspond to the SQL Server system tables. When you specify a system table name, the NonStop ODBC Server searches the object mapping table, ZNUOBJ, and locates the view name that corresponds to the system table. For more information, see System Table Mapping on page 8-60.
HP NonStop ODBC Server Mapping Tables Default Values for NonStop ODBC Server Attributes Table 8-2. Naming Conventions for the Mapping Table Columns Prefix Column Value ACC_ Relates to the resource accounting description. G_ Relates to the HP NonStop Kernel operating system. GOV_ Relates to the resource governing description. N_ Relates to NonStop SQL/MP. NOS_ Relates to the NonStop ODBC Server. SCS_ Relates to or references an SQL Communication Subsystem (SCS) process.
HP NonStop ODBC Server Mapping Tables Table Mapping Security By taking advantage of the changeable default values, you receive the following benefits: • • • The flexibility to change attributes without requiring changes to program code Visibility of the results More control over the system You change the default values throughout the system by using the ADD, MODIFY, or REMOVE statement on the ZNSSCFG table.
HP NonStop ODBC Server Mapping Tables Table Relationships Table Relationships Figure 8-2 shows the relationships among the NonStop ODBC Server mapping tables: Figure 8-2.
HP NonStop ODBC Server Mapping Tables Data Types Data Types The NonStop ODBC Server logical data types described in this section are as follows: Data Type Meaning INT (2) 2-byte integer INT (4) 4-byte integer INT (8) 8-byte integer CHAR (n) Fixed-length character string of n bytes VARCHAR (n) Variable-length character string of n bytes preceded by a 2-byte integer indicating the length of the string TIMESTAMP NonStop SQL/MP data type DATETIME YEAR TO FRACTION Restoring Tables A NonStop ODBC
HP NonStop ODBC Server Mapping Tables Database and Object Mapping Database and Object Mapping Table 8-3 and Table 8-4 are summary tables that provide the location and description of each database and object mapping table. For a summary of system mapping tables, see System Table Mapping on page 8-60. Table 8-3. Mapping Tables Residing Only With the System Catalog (page 1 of 2) File Name Object Type Description ZNSALT Table Maps alternate usernames to logical usernames.
HP NonStop ODBC Server Mapping Tables Database and Object Mapping Table 8-3. Mapping Tables Residing Only With the System Catalog (page 2 of 2) File Name Object Type Description ZNSUSI1 Index Ensures that the logical username is unique for this system. ZNSUSI2 Index Ensures that the Guardian username is unique for all mappings from logical to system usernames. ZNSVALUE Table Used internally by the NonStop ODBC Server to support the ODBC or SQL Server spt_values table.
HP NonStop ODBC Server Mapping Tables Database and Object Mapping The following subsections list the database and object mapping tables alphabetically and describe each table’s purpose and its columns. Many of the tables have a LAST_UPDATED column as the last column on the table. This datetime column is a timestamp that represents the time of the last add or modify executed on the table. For tables modified in batch, each row modified in the same batch of commands receives the same timestamp value.
HP NonStop ODBC Server Mapping Tables ZNSALT (For Alternate Usernames) ZNSALT (For Alternate Usernames) The ZNSALT table maps alternate usernames to ODBC or SQL Server logical username. A user can designate different profile descriptions for use at session initiation for the same NonStop ODBC Server logical use name. More than one alias can point to the same profile and to the same logical username. A ZNSALT record is automatically added for each record added to the ZNSUS table.
HP NonStop ODBC Server Mapping Tables ZNSCON (For Control Statements) ZNSCON (For Control Statements) The ZNSCON table describes the NonStop SQL CONTROL directives that must be executed by the process at initialization or reinitialization time. For the syntax of NonStop SQL/MP CONTROL directives, see the NonStop SQL/MP Reference Manual. ZNSCON is created during INSTALL, REFRESH, and UPGRADE operations. The table may not contain any rows.
HP NonStop ODBC Server Mapping Tables ZNSDB (For NonStop ODBC Server Databases) ZNSDB (For NonStop ODBC Server Databases) The table ZNSDB maps ODBC or SQL Server database names to NonStop SQL/MP catalog names. Each row in ZNSDB represents one NonStop ODBC Server customized catalog. ZNSDB is populated by the INSTALL command. ZNSDB resides only in the subvolume of the system catalog. See also SYSDATABASES Catalog View (ZVSDB) on page 8-63. Primary Key Unique Index Table 8-7.
HP NonStop ODBC Server Mapping Tables ZNSDEF (For NonStop ODBC Server DEFINEs) ZNSDEF (For NonStop ODBC Server DEFINEs) The table ZNSDEF stores the names of the NonStop ODBC Server DEFINEs and values to be set prior to process initiation. ZNSDEF stores only attribute settings defined by the NonStop Kernel (as opposed to the ZNSSCFG table, which stores attribute settings defined by the ODBC server). No values are loaded into ZNSDEF during system installation.
HP NonStop ODBC Server Mapping Tables ZNSDUMMY (For Use With DLIB Applications) ZNSDUMMY (For Use With DLIB Applications) The table ZNSDUMMY is used internally by the NonStop ODBC Server. ZNSDUMMY resides only in the subvolume of the system catalog. ZNSDUMMY contains one column, COL1, of data type INT. The column is used internally by the NonStop ODBC Server.
HP NonStop ODBC Server Mapping Tables ZNSGOV (For Governing Policies) ZNSGOV (For Governing Policies) The table ZNSGOV describes resource usage limits and server actions that occur when the limits are exceeded. The limits and actions are called governing profiles. For information on using the ZNSGOV table to log query status, see the HP NonStop ODBC Server Installation and Management Manual. ZNSGOV resides only in the subvolume of the system catalog. Primary Key Unique Index Table 8-9.
HP NonStop ODBC Server Mapping Tables ZNSGOV (For Governing Policies) GOV_ACTION – Unique Index Column Name Primary Key Table 8-9. Description of ZNSGOV (page 2 of 2) Data Type Description – VARCHAR (60) Action to be taken if the process attribute exceeds the threshold value (see definitions following table): COMMIT CONTINUE PRIORITY [ ++ | -- ] n ROLLBACK STOP LOG_QST_ON – – CHAR (1) Flag indicating whether governing actions are logged.
HP NonStop ODBC Server Mapping Tables ZNSMSG (For Error Messages) ZNSMSG (For Error Messages) The table ZNSMSG contains all error messages that can be generated by the NonStop ODBC Server. The table maps a NonStop ODBC Server error code to its corresponding ODBC or SQL Server error code (where possible). It also contains error text and the ODBC or SQL Server severity level. For details on the error messages, see the HP NonStop ODBC Server Messages Manual.
HP NonStop ODBC Server Mapping Tables ZNSMSG (For Error Messages) T_CLASS – Unique Index Column Name Primary Key Table 8-10. Description of ZNSMSG (page 2 of 2) Data Type Description – INT (2) ODBC or SQL Server error severity level: 10 11–16 17–18 19+ Warning User-generated Nonfatal error Fatal error T_STATE – – INT (2) ODBC or SQL Server error detail level. This is not supported and is always –1.
HP NonStop ODBC Server Mapping Tables ZNSNET (For Network Service Specifications) ZNSNET (For Network Service Specifications) The table ZNSNET contains the data used by the SCS process to initiate a service. Attributes are mutually exclusive according to the network protocol. This means that the table contains certain values if the protocol is TCP/IP and certain other values if the protocol is NETBIOS. ZNSNET resides only in the subvolume of the system catalog. Primary Key Unique Index Table 8-11.
HP NonStop ODBC Server Mapping Tables ZNSNET (For Network Service Specifications) Primary Key Unique Index Table 8-11. Description of ZNSNET (page 2 of 2) Data Type Description _ _ INT (4) Startup value for a socket option. (See the TCP/IP Applications and Utilities User Guide.) _ _ INT (4) Startup value for a socket option. (See the TCP/IP Applications and Utilities User Guide.) SO_REUSEADDR _ _ INT (4) Startup value for a socket option.
HP NonStop ODBC Server Mapping Tables ZNSPROF (For Profile Values) ZNSPROF (For Profile Values) The table ZNSPROF contains the user profile information used by the server to establish session-based defaults. There is one ZNSPROF table per system, and it resides only in the subvolume of the system catalog. ZNSPROF contains one row per user profile. At system installation time, it is loaded with a special profile, named DEFAULT, that has set in it the default values for each profile attribute.
HP NonStop ODBC Server Mapping Tables ZNSPROF (For Profile Values) Primary Key Unique Index Table 8-12. Description of ZNSPROF (page 1 of 4) Data Type Description x – VARCHAR (60) User-defined profile name that is unique in the system in which it is defined. – VARCHAR (60) Default database for object qualification in the NonStop ODBC server or in NOSUTIL for the user who indicates this profile.
HP NonStop ODBC Server Mapping Tables ZNSPROF (For Profile Values) DEFAULT_LOCATION – Unique Index Column Name Primary Key Table 8-12. Description of ZNSPROF (page 2 of 4) Data Type Description – VARCHAR (60) Default location for the creation of objects. When a user creates an object, it appears by default in the same subvolume as the NonStop ODBC Server customized catalog. The DEFAULT_LOCATION entry causes the object to be created on the specified volume instead.
HP NonStop ODBC Server Mapping Tables ZNSPROF (For Profile Values) SQL_ACCESS_MODE – Unique Index Column Name Primary Key Table 8-12. Description of ZNSPROF (page 3 of 4) Data Type Description – CHAR (2) Default mode in the current session: RW RO SQL_CURSOR_MODE – – CHAR (2) Read-write Read-only SQL cursor mode.
HP NonStop ODBC Server Mapping Tables ZNSPROF (For Profile Values) CON_NAME CLOSE_TABLES_PER_ SESSION LAST_UPDATED Unique Index Column Name Primary Key Table 8-12. Description of ZNSPROF (page 4 of 4) Data Type Description – – VARCHAR (60) CONTROL statement list identifier. – – CHAR (1) Flag (Y or N) indicating whether NonStop SQL/MP tables are closed at the end of a session – – DATETIME YEAR TO FRACTION (6) Date and time this row was added or last modified.
HP NonStop ODBC Server Mapping Tables ZNSPROT (For User Permission Data) ZNSPROT (For User Permission Data) The table ZNSPROT contains user permission information. It has one row for each combination of T_ACTION and T_PROTECTTYPE. The table is implemented to provide compatibility with the SQL Server system table SYSPROTECTS. The NonStop ODBC Server, however, does not support the statements that affect SYSPROTECTS, so ZNSPROT grants all users authority to use all statements.
HP NonStop ODBC Server Mapping Tables ZNSSCFG (For System Configuration Values) ZNSSCFG (For System Configuration Values) The table ZNSSCFG stores configuration values and bookkeeping data on changes made to these values. ZNSSCFG stores only attribute settings defined by the ODBC server (as opposed to ZNSDEF, which stores attribute settings defined by the NonStop Kernel), When NOSUTIL installs the NonStop ODBC Server system catalogs, it loads the system default configuration values into the ZNSSCFG table.
HP NonStop ODBC Server Mapping Tables ZNSSCFG (For System Configuration Values) Storage of System Information ZNSSCFG contains two special rows that represent data that pertains to the entire collection of data stored on ZNSSCFG. These are the rows that contain the values “LAST_UPDATE_SYSTEM_CONFIG” and “CHECK_INTERVAL_SECONDS” in the ITEM column.
HP NonStop ODBC Server Mapping Tables ZNSSCFG (For System Configuration Values) If the default value for the attribute on the ZNSSCFG table is “None,” the process reports an error. If the process is SCS or NOS, the process stops. If the process is NOSUTIL, the command fails. For more information on default values, see Default Values for NonStop ODBC Server Attributes on page 8-4.
HP NonStop ODBC Server Mapping Tables ZNSSCFG (For System Configuration Values) The attributes COLLECT_SQL_ERROR_INFO, SQL_ERROR_sqlcode, and SQL_WARNING_sqlcode are not available in the ZNSSCFG table by default; they are only present when added using the ADD SCFG command.
HP NonStop ODBC Server Mapping Tables ZNSSCS (for SQL Communication Subsystem Specifications) ZNSSCS (for SQL Communication Subsystem Specifications) The ZNSSCS table stores the definitions of the SCS process configuration. (It replaces the user-specified edit files used in previous versions of NonStop ODBC.) You can specify any value in the configuration that is used by the PROCESS_CREATE_ procedure or can be passed in the startup message to the SCS process.
HP NonStop ODBC Server Mapping Tables ZNSSCS (for SQL Communication Subsystem Specifications) Primary Key Unique Index Table 8-15. Description of ZNSSCS (page 2 of 2) Data Type Description _ _ INT (2) Byte count in the IPC send buffer between SCS and NOS and between NOS and NOSUTIL. _ _ INT (2) Byte count in the IPC return buffer between SCS and NOS and between NOS and NOSUTIL. DEFAULT_VOLUME – – VARCHAR (26) PROCESS_CREATE_ option.
HP NonStop ODBC Server Mapping Tables ZNSSER (For NonStop ODBC Server Process Definitions) ZNSSER (For NonStop ODBC Server Process Definitions) The ZNSSER table stores the definitions of the NonStop ODBC server process configuration by server class. It replaces the user-specified edit files used in previous versions. You can specify any value that is used by PROCESS_CREATE_ or any value that can be passed in the startup message to the SCS process.
HP NonStop ODBC Server Mapping Tables ZNSSER (For NonStop ODBC Server Process Definitions) Primary Key Unique Index Table 8-16. Description of ZNSSER (page 2 of 2) Data Type IDLE_DELETE_ DELAY_SEC – – INT (4) Number of seconds a process can remain idle before being deleted, if more than the minimum number of servers exist (as defined by the value for MIN_SERVERS). If 0 seconds, the process is not deleted. NOS_CREATE_ OPTIONS – – INT (2) PROCESS_CREATE_ option.
HP NonStop ODBC Server Mapping Tables ZNSSMAP (For Server Class to SCS Description Mapping) ZNSSMAP (For Server Class to SCS Description Mapping) The table ZNSSMAP links the server class description with an SCS description. ZNSSMAP resides only in the subvolume of the system catalog. Column Name Primary Key Unique Index Table 8-17. Description of ZNSSMAP Data Type Description SCS_NAME x – CHAR (16) SCS name. SER_NAME x – CHAR (32) Server class name.
HP NonStop ODBC Server Mapping Tables ZNSTRA (For Trace Data) ZNSTRA (For Trace Data) The table ZNSTRA contains the trace description pointed to by a user profile. . Primary Key Unique Index Table 8-18. Description of ZNSTRA (page 1 of 2) Data Type Description x _ VARCHAR (60) A unique key for associating a trace definition with a profile. _ _ VARCHAR (182) NonStop ODBC Server table name to which the log is written. TRA_LEVEL _ _ VARCHAR (60) Reserved for future use.
HP NonStop ODBC Server Mapping Tables ZNSTRA (For Trace Data) TRA_ERROR _ Unique Index Column Name Primary Key Table 8-18.
HP NonStop ODBC Server Mapping Tables ZNSUMAP (For User and Profile Name to Server Class Mapping) ZNSUMAP (For User and Profile Name to Server Class Mapping) The ZNSUMAP table links the username and profile name in the login record (determined from the ZNSALT table) and the server class (SER_NAME) to which the login message is routed. ZNSUMAP resides only in the subvolume of the system catalog. Primary Key Unique Index Table 8-19.
HP NonStop ODBC Server Mapping Tables ZNSUS (For Logical Username to Guardian Username Mapping) ZNSUS (For Logical Username to Guardian Username Mapping) The ZNSUS table maps logical ODBC or SQL Server usernames to their Guardian username counterparts. ZNSUS resides only in the subvolume of the system catalog. Primary Key Unique Index Table 8-20. Description of ZNSUS Data Type Description x – VARCHAR (60) Logical username.
HP NonStop ODBC Server Mapping Tables ZNSVALUE (For ODBC or SQL Server spt_values Support) ZNSVALUE (For ODBC or SQL Server spt_values Support) The table ZNSVALUE is used internally by the NonStop ODBC Server to support the ODBC or SQL Server spt_values table. The table is used by DBLIB applications. The table contains one column called NUMBER, of data type INT (2). The table has 16 rows. The NUMBER column contains values from 1 to 16. The column is used internally by the NonStop ODBC Server.
HP NonStop ODBC Server Mapping Tables ZNUDT (For Data Types Mapping) ZNUDT (For Data Types Mapping) The static table ZNUDT maps ODBC or SQL Server data types to NonStop SQL/MP data types and vice versa. ZNUDT, TABLES, COLUMNS, and ZNUOBJ are joined to form the view ZVUCOL, which corresponds to the system table SYSCOLUMNS. ZNUDT is used only for this purpose. ZNUDT is loaded during SYSCAT INSTALL or USERCAT INSTALL. It describes the mapping between NonStop SQL and the SQL Server API and the ODBC API.
HP NonStop ODBC Server Mapping Tables ZNUDT (For Data Types Mapping) Primary Key Unique Index Table 8-21. Description of ZNUDT (page 2 of 2) Data Type Description – – INT (2) Indicates whether the ODBC or SQL Server data type allows null values. x – CHAR (1) Indicates whether the NonStop SQL/MP data type allows null values.
HP NonStop ODBC Server Mapping Tables ZNUIX (For Logical Index Names to Guardian Names Mapping) ZNUIX (For Logical Index Names to Guardian Names Mapping) The table ZNUIX maps ODBC or SQL Server index names to NonStop SQL/MP index names. This mapping is needed because ODBC or SQL Server index names are bound to their table names and need not be unique, whereas NonStop SQL/MP index names must be unique. ZNUIX resides in both the subvolume of the system catalog and a customized user catalog.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) ZNUMTRX (For Resource Accounting Log Data) The ZNUMTRX table is the default simple file name used as the log for resource accounting. It is the log table name ACC_LOGTABLE_NAME found in the ZNSPROF record, pointed to by the current user profile. When creating the resource accounting log, you can specify any other simple file name, however the definition of the table is fixed.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) Primary Key Unique Index Table 8-23. Description of ZNUMTRX (page 2 of 6) Data Type Description – – VARCHAR (30) Application provided by the user, stored in the login record; used to determine the environment settings for the current user session. – – CHAR (17) Guardian username determined from the LOGON_USERNAME at the start of the session. PROFILE_NAME – – VARCHAR (60) Profile name for the current session.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) Primary Key Unique Index Table 8-23. Description of ZNUMTRX (page 3 of 6) Data Type SCS_EXECUTION_ TIME – – INT (8) Time in microseconds that the SCS spends managing the session between the accounting start time and end time. NSODBC_ EXECUTION_TIME – – INT (8) Amount of NonStop ODBC server execution time, in microseconds, spent executing in the server process, excluding NSSQL_EXECUTION_TIME.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) NSSQL_EXECUTION_ TIME Unique Index Column Name Primary Key Table 8-23. Description of ZNUMTRX (page 4 of 6) Data Type – – INT (8) Description Amount of NonStop SQL/MP execution time, in microseconds, between the accounting start time and end time. This value can be greater than NSODBC_ELAPSED_TIME due to parallelism in the processing.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) STMT_TYPE – Unique Index Column Name Primary Key Table 8-23.
HP NonStop ODBC Server Mapping Tables ZNUMTRX (For Resource Accounting Log Data) Primary Key Unique Index Table 8-23. Description of ZNUMTRX (page 6 of 6) Data Type Description – – INT (8) Number of messages sent for this OPEN, including the OPEN message. – – INT (8) Number of message bytes sent and received for this file OPEN. LOCK_WAITS – – INT (8) Number of times the executor waited for a lock request to complete.
HP NonStop ODBC Server Mapping Tables ZNUOBJ (For Logical Object Names Mapping) ZNUOBJ (For Logical Object Names Mapping) The table ZNUOBJ maps ODBC or SQL Server table and view names to NonStop SQL/MP object names. ZNUOBJ resides in both the subvolume of the system catalog and a customized user catalog. Primary Key Unique Index Table 8-24. Description of ZNUOBJ (page 1 of 2) Data Type Description x – INT (2) UNSIGNED Numeric user ID of the owner.
HP NonStop ODBC Server Mapping Tables ZNUOBJ (For Logical Object Names Mapping) N_OBJTYPE – Unique Index Column Name Primary Key Table 8-24. Description of ZNUOBJ (page 2 of 2) Data Type Description – CHAR (2) NonStop SQL/MP object type: TA VI N_ORIGIN – – CHAR (1) Code indicating the type of statement that created the object: T N N_SESSIONID – – INT (8) Table View Transact-SQL or CORE SQL NonStop SQL/MP Session ID used internally by the NonStop ODBC Server.
HP NonStop ODBC Server Mapping Tables ZNUPCOL (For Stored Procedure Parameters and Results) ZNUPCOL (For Stored Procedure Parameters and Results) The table ZNUPCOL contains information about the parameters and result sets of stored procedures. The table contains one row for each input parameter, each input/output parameter, and, optionally, each column in the result set. ZNUPCOL also provides support for the ODBC catalog function SQLProcedureColumns.
HP NonStop ODBC Server Mapping Tables ZNUPCOL (For Stored Procedure Parameters and Results) N_ DATETIMESTARTFIELD Unique Index Column Name Primary Key Table 8-25. Description of ZNUPCOL (page 2 of 2) Data Type – – INT (2) Description Most significant field to be used in stored DATETIME values: 1 2 3 4 5 6 7 0 Year Month Day Hour Minute Second Fraction Not a DATETIME value N_ DATETIMEENDFIELD – – INT (2) Least significant field (1–7) to be used in stored DATETIME values.
HP NonStop ODBC Server Mapping Tables ZNUPROC (For Stored Procedure Attributes) ZNUPROC (For Stored Procedure Attributes) The table ZNUPROC contains the combined attributes required to support execution of stored procedures for both CORE SQL users and Transact-SQL users. The table contains one row for each stored procedure supported by the NonStop ODBC Server. ZNUPROC also provides support for the ODBC catalog function SQLProcedures and the SQL Server catalog SYSOBJECTS.
HP NonStop ODBC Server Mapping Tables ZNUPROC (For Stored Procedure Attributes) N_CREATETIME N_MAX_BUFFER_ LEN REMARKS Unique Index Column Name Primary Key Table 8-26. Description of ZNUPROC (page 2 of 2) Data Type Description – – TIMESTAMP Date and time the procedure was added. – – INT (2) Maximum length of the IPC buffer. – – VARCHAR (254) Explanatory comments. Has a static value of zero length; comments can be entered through SQLCI, but not through the API.
HP NonStop ODBC Server Mapping Tables ZNUQST (For Query Status Data) ZNUQST (For Query Status Data) The table ZNUQST is the simple file name used for logging current activity. It is the log table name in the ZNSPROF record of the current user profile. The NonStop ODBC server process enters the values on this table. The ZNUQST table is created as an unaudited table so that concurrency is maximized among multiple users. Log entry rows only append to the table during the time the log is active.
HP NonStop ODBC Server Mapping Tables ZNUQST (For Query Status Data) START_PRIORITY TRANSACTION_ID STMT_TYPE Unique Index Column Name Primary Key Table 8-27. Description of ZNUQST (page 2 of 2) Data Type Description – – INT (2) UNSIGNED Priority at which execution is being performed. – – CHAR (22) Current transaction ID.
HP NonStop ODBC Server Mapping Tables ZNUTRA (For Trace Records Logging) ZNUTRA (For Trace Records Logging) The name ZNUTRA is a template file name for the table used for logging trace records. The actual name of the table is one chosen by the user. It is the log table name found in the ZNSTRA record of the current user profile. ZNUTRA resides in both the subvolume of the system catalog and a customized user catalog.
HP NonStop ODBC Server Mapping Tables System Table Mapping System Table Mapping The NonStop ODBC Server maintains tables and views that correspond to the SQL Server system tables. The prefix “SYS” indicates a view specific to TSQL that is provided by the NonStop ODBC Server and corresponds to a TSQL system catalog table or view. When you specify a system table name, the NonStop ODBC Server searches the object mapping table, ZNUOBJ, and locates the view name that corresponds to the system table.
HP NonStop ODBC Server Mapping Tables SQLProcedureColumns Procedure (ZVUPCOL) SQLProcedureColumns Procedure (ZVUPCOL) The view ZVUPCOL satisfies the SQLProcedureColumns query for ODBC access. It translates the parameter information into the NonStop ODBC Server parameter description form. ZVUPCOL resides in both the subvolume of the system catalog and a customized user catalog.
HP NonStop ODBC Server Mapping Tables SYSCOLUMNS Catalog View (ZVUCOL) SYSCOLUMNS Catalog View (ZVUCOL) The catalog view SYSCOLUMNS lists the columns of tables and views in a NonStop SQL/MP catalog. SYSCOLUMNS is represented by a join view over COLUMNS, TABLES, ZNSOBJ (the object mapping table), and ZNUDT (the data types mapping table). The view and mapping tables reside on each customized subvolume. Sequence of rows is ascending by ID.
HP NonStop ODBC Server Mapping Tables SYSDATABASES Catalog View (ZVSDB) SYSDATABASES Catalog View (ZVSDB) The catalog view SYSDATABASES represents the customized NonStop SQL/MP catalogs on a node. It is supported by a view over a mapping table. It is updated when a NonStop ODBC Server user issues a CREATE or DROP DATABASE statement or the equivalent statement for USERCAT INSTALL or USERCAT DEINSTALL. SYSDATABASES exists only with the NonStop SQL/MP system catalog that corresponds to the MASTER database.
HP NonStop ODBC Server Mapping Tables SYSINDEXES Catalog View (ZVUIX) SYSINDEXES Catalog View (ZVUIX) The catalog view SYSINDEXES is defined for TSQL mode only. It lists the indexes in a NonStop SQL/MP catalog that are created by NonStop ODBC Server users. Indexes created by HP server users that are on tables in other catalogs are not visible through SYSINDEXES. SYSINDEXES is represented by a view over INDEXES and a mapping table. The view and mapping tables reside on each customized subvolume.
HP NonStop ODBC Server Mapping Tables SYSINDEXES Catalog View (ZVUIX) Table 8-33. Description of SYSINDEXES (page 2 of 2) Column Name Column Name in ZNUIX or INDEXES STATUS INDEXES.UNIQUEVALUE A number used internally by the NonStop ODBC Server to record whether an index is unique. ROWPAGE None No corresponding column name. Assigned –1. MINLEN INDEXES.ROWSIZE Length of the packed index record. MAXLEN INDEXES.ROWSIZE No corresponding column name. Assigned –1.
HP NonStop ODBC Server Mapping Tables SYSMESSAGES Catalog View (ZVSMSG) SYSMESSAGES Catalog View (ZVSMSG) The catalog view SYSMESSAGES contains error codes and error text. SYSMESSAGES is represented as a view over a mapping table. The view and mapping table reside only in the subvolume of the system catalog. Sequence of rows is ascending by ERROR. SYSMESSAGES is created when the NonStop ODBC Server is installed.
HP NonStop ODBC Server Mapping Tables SYSOBJECTS Catalog Table (ZVUOBJ) SYSOBJECTS Catalog Table (ZVUOBJ) The catalog view SYSOBJECTS lists the tables and views in a NonStop SQL/MP Catalog. It includes user and SQL Server system tables but does not include stored procedures. SYSOBJECTS is represented by a join view over TABLES and mapping tables. The view and mapping tables reside on each customized subvolume. Sequence of rows is ascending by UID and NAME.
HP NonStop ODBC Server Mapping Tables SYSOBJECTS Catalog Table (ZVUOBJ) Table 8-35. Description of SYSOBJECTS (page 2 of 2) Column Name Column Name in ZNUOBJ, ZNSUS, or TABLES REFDATE None No corresponding column name. Assigned “ ” (blank). CRDATE TABLES.CREATETIME Date and time the object was created. EXPDATE None No corresponding column name. Assigned “ ” (blank). DELTRIG None No corresponding column name. Assigned –1. INSTRIG None No corresponding column name. Assigned –1.
HP NonStop ODBC Server Mapping Tables SYSPROTECTS Catalog View (ZVUPROT) SYSPROTECTS Catalog View (ZVUPROT) The catalog view SYSPROTECTS is created when the NonStop ODBC Server is installed. It is provided for DBLIB users only. It reports all rights against all objects registered in the local ZNOBJ objects table. SYSPROTECTS is represented by a join view over ZNSPROT and ZNUOBJ. The view and mapping table reside on each customized subvolume. Table 8-36.
HP NonStop ODBC Server Mapping Tables SYSTYPES Catalog View (ZVUDT) SYSTYPES Catalog View (ZVUDT) The catalog view SYSTYPES contains each system-supplied data type. The NonStop ODBC Server does not support user-defined data types. SYSTYPES is represented by a view over a data type mapping table. The mapping table resides on each customized subvolume. It contains the same constant values in every catalog. Table 8-37.
HP NonStop ODBC Server Mapping Tables SYSUSERS Catalog View (ZVUUS) SYSUSERS Catalog View (ZVUUS) The catalog view SYSUSERS contains usernames used only to qualify object names. SYSUSERS is represented by a view over the mapping table ZNSUS. The view and mapping table reside on each customized subvolume. SYSUSERS is created when a catalog is customized. You can add users with the NOSUTIL ADD USER statement. For information on this statement, see Running the Catalog Utility Statements on page 7-13.
HP NonStop ODBC Server Mapping Tables Actions That Affect the Mapping Tables Actions That Affect the Mapping Tables When you use the NonStop ODBC Server, some of the actions you take or statements you execute cause the NonStop ODBC Server to create, drop, query, or modify the mapping tables. Table 8-39.
HP NonStop ODBC Server Mapping Tables Actions That Affect the Mapping Tables Table 8-40. Actions That Cause the NonStop ODBC Server to Query the Mapping Tables Action Mapping Tables Queried Description Select from a table or view ZNUOBJ in the subvolume of the catalog in which the table or view is registered NonStop ODBC server searches the object mapping table, ZNUOBJ, and finds the NonStop SQL/MP name corresponding to the specified table or view name.
HP NonStop ODBC Server Mapping Tables Actions That Affect the Mapping Tables Table 8-41. Actions That Cause the NonStop ODBC Server to Modify the Mapping Tables Action Mapping Tables Affected Description Create a database using the NonStop ODBC Server or customize a NonStop SQL/MP catalog ZNSDB in the subvolume of the system catalog NonStop ODBC server adds a row for the database in ZNSDB.
9 UNIX Notes This section contains the environment-specific information on using DB-LIBRARY in the UNIX environment. The Microsoft SQL Server Programmer’s Reference describes DB-LIBRARY and lists DB-LIBRARY commands and routines for DOS-based and OS/2-based SQL Server. This section contains more information for UNIX users. It covers the following topics: • • Building an executable DB-LIBRARY functions and macros Note.
UNIX Notes DBIORDESC Table 9-1. DB-LIBRARY Functions and Macros for the UNIX Environment Function or Macro Description DBIORDESC Provides program access to the UNIX file descriptor used by a DBPROCESS to read data coming from SQL Server. DBIOWDESC Provides program access to the UNIX file descriptor used by a DBPROCESS to write data to SQL Server. DBRBUF Determines whether the DB-LIBRARY network buffer contains any unread bytes.
UNIX Notes • • DBIOWDESC An application can use the DB-LIBRARY DBRBUF() routine, in addition to the UNIX select() function, to help determine whether any more data from SQL Server is available for reading. A companion routine, DBIOWDESC(), provides access to the file descriptor used to write data to SQL Server. Returns DBIORDESC returns an integer file descriptor used by the specified DBPROCESS to read data coming from SQL Server.
UNIX Notes • • DBRBUF The file descriptor returned by this routine can be used only with operating system facilities that do not read data from the incoming data stream. If data is read from this stream by any means other than through a DB-LIBRARY routine, communications between the front end and SQL Server will become scrambled. A companion routine, DBIORDESC(), provides access to the file descriptor used to read data coming from SQL Server.
UNIX Notes DBRBUF Returns DBRBUF returns TRUE or FALSE: TRUE Bytes remain in the buffer FALSE No bytes remain in the buffer See Also For further information, see DBIORDESC on page 9-2 or see dbsqlok, or dbresults in the Microsoft SQL Server Programmer’s Reference.
UNIX Notes DBRBUF HP NonStop ODBC Server Reference Manual—429151-002 9 -6
A Summary of Support for ODBC Features Not all ODBC features are supported by the HP NonStop ODBC Server.
Summary of Support for ODBC Features Data Types Data Types The NonStop ODBC Server supports all ODBC data types, at both the Core level and the Extended level. When you create objects and data, however, the NonStop ODBC Server maps the data to a HP NonStop SQL/MP data type. Table A-1 summarizes the ODBC data types and the corresponding NonStop SQL/MP data types. Table A-1.
Summary of Support for ODBC Features Expressions and Operators Expressions and Operators The NonStop ODBC Server supports all of the operators used in ODBC expression syntax. Table A-2 summarizes the ODBC operators. Table A-2.
Summary of Support for ODBC Features Functions Table A-3. ODBC Functions (page 2 of 3) Function NonStop ODBC Server Support HOUR x Fully supported. Date and time expressions used within the function, however, have some restrictions. MINUTE x Fully supported. Date and time expressions used within the function, however, have some restrictions. MONTH x Fully supported. Date and time expressions used within the function, however, have some restrictions. NOW x Fully supported.
Summary of Support for ODBC Features Functions Table A-3. ODBC Functions (page 3 of 3) Function NonStop ODBC Server Support Comments SQRT – – TAN – – ASCII – – CHAR – – CONCAT x Fully supported. INSERT – – LEFT – – LTRIM x Fully supported. LENGTH x Fully supported. LOCATE – Fully supported. LCASE – – REPEAT – – REPLACE – – RIGHT – – RTRIM x Fully supported. SUBSTRING x Fully supported. UCASE x Fully supported. DATABASE x Fully supported.
Summary of Support for ODBC Features Identifiers Identifiers The NonStop ODBC Server supports ODBC identifiers for naming all objects except databases and owners. Database Names The format for a database name is: node_volume_subvolume You must separate the parts of the name with underscores (_). Each portion of the name begins with a letter and consists of letters and numerals.
Summary of Support for ODBC Features Statements Statements The NonStop ODBC Server supports all of the ODBC CORE SQL statements, although some have restrictions. Table A-4 lists the CORE SQL statements and summarizes how each supported statement differs when used with the NonStop ODBC Server. Table A-4. NonStop ODBC Server Support of CORE SQL Statements (page 1 of 2) CORE SQL Statement NonStop ODBC Server Support Comments ALTER TABLE x Fully supported.
Summary of Support for ODBC Features Stored Procedures Table A-4. NonStop ODBC Server Support of CORE SQL Statements (page 2 of 2) CORE SQL Statement NonStop ODBC Server Support REVOKE x* The NonStop ODBC Server recognizes the syntax, but does not execute the statement. SELECT x Fully supported. UPDATE x The NonStop ODBC Server supports both the positioned UPDATE and the searched UPDATE.
B Summary of Support for SQL Server Features Not all SQL Server features are supported by the HP NonStop ODBC Server.
Summary of Support for SQL Server Features Aggregates Aggregates The NonStop ODBC Server supports all of the SQL Server aggregate functions: • • • • • AVG COUNT MAX MIN SUM The primary differences between aggregate functions in SQL Server and in the NonStop ODBC Server are the following: Feature In SQL Server In the NonStop ODBC Server Data type of the results of AVG, COUNT, and SUM Depends on the expression being evaluated.
Summary of Support for SQL Server Features Browse Mode Browse Mode The NonStop ODBC Server does not support browse mode. The FOR BROWSE clause is an unsupported clause of the SELECT statement. For detailed information on the SELECT statement, see SELECT on page 4-82. Comments The NonStop ODBC Server supports user-written comments. The syntax is the same as in SQL Server. Nested comments, however, are not supported. For detailed information on comments, see Comments on page 4-35.
Summary of Support for SQL Server Features Expressions and Operators Table B-1.
Summary of Support for SQL Server Features Functions Table B-2. NonStop ODBC Server Support of SQL Server Operators (page 2 of 2) SQL Server Operator Type Other Symbol NonStop ODBC Server Support != x !> x !< x *= x =* x ALL x AND x ANY x BETWEEN x EXISTS x IN x IS NULL x LIKE x NOT x OR x xIndicates a supported operator –Indicates an unsupported operator For detailed information on expressions and operators, see Expressions and Operators on page 4-29.
Summary of Support for SQL Server Features Functions Table B-3. SQL Server Functions (page 2 of 4) Function NonStop ODBC Server Support DATEPART x Fully supported. Dateparts used within the function, however, have some restrictions. GETDATE x Fully supported. Dateparts used within the function, however, have some restrictions. Comments Mathetical Functions ABS – – ACOS – – ASIN – – ATAN – – ATN2 – – CEILING – – COS – – COT – – DEGREES – – EXP x Fully supported.
Summary of Support for SQL Server Features Functions Table B-3. SQL Server Functions (page 3 of 4) Function NonStop ODBC Server Support Comments LTRIM – – REPLICATE – – RIGHT – – RTRIM – – SOUNDEX – – SPACE – – STR – – STUFF – – SUBSTRING – – UPPER x Fully supported. + – – COL_LENGTH – – COL_NAME – – DATALENGTH – – DB_ID – – DB_NAME x All parameters are ignored, and the function always returns the current database name.
Summary of Support for SQL Server Features Identifiers Table B-3. SQL Server Functions (page 4 of 4) Function NonStop ODBC Server Support Comments TEXTVALID – – Type Conversion Function CONVERT x The style parameter is ignored, and there are restrictions when converting to or from a datetime value. x Indicates a supported function – Indicates an unsupported function SQL Server date functions contain arguments called dateparts.
Summary of Support for SQL Server Features Database Names Database Names The format for a database name is: node_volume_subvolume You must separate the parts of the name with underscores (_). Each portion of the name begins with a letter and consists of letters and numerals. The maximum number of characters for each portion is as follows: Portion Characters node 7 volume 6 subvolume 8 The NonStop ODBC Server maps the database name to a Guardian identifier as follows: \node.$volume.
Summary of Support for SQL Server Features NULL Values The following table summarizes NonStop ODBC Server support of the SQL Server join operators. Operator NonStop ODBC Server Support = x > x < x >= x <= x != x !> x !< x *= x =* x x Indicates a supported operator NULL Values The NonStop ODBC Server supports NULL values. The only difference between NULL values in SQL Server and NULL values in the NonStop ODBC Server is the order in which data is displayed.
Summary of Support for SQL Server Features Y2K Implicit Century Conformance Y2K Implicit Century Conformance The NonStop ODBC Server supports an implicit century DATE value with the same semantics as found in NonStop SQL Server. When NonStop ODBC/MP can determine by context that an input buffer value is a two-digit year value, it prefixes the year value with a century value of either “00” or “20” before the statement is executed.
Summary of Support for SQL Server Features Statements Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 2 of 7) Transact-SQL Statement NonStop ODBC Server Support Comments BEGIN TRANSACTION x Nested transactions are not allowed. Transaction names can be included but are not meaningful. DDL operations are allowed within a transaction. BREAK – – CHECKPOINT – – COMMIT TRANSACTION x Transaction savepoints are ignored.
Summary of Support for SQL Server Features Statements Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 3 of 7) Transact-SQL Statement NonStop ODBC Server Support CREATE RULE – Use the NonStop SQL/MP CREATE CONSTRAINT statement in pass-through mode. CREATE TABLE x Some data types are unsupported, and the NonStop ODBC Server maps some data types to NonStop SQL/MP data types. See Data Types earlier in this appendix.
Summary of Support for SQL Server Features Statements Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 4 of 7) Transact-SQL Statement NonStop ODBC Server Support DROP INDEX x Comments The following associated objects must be accessible: • • • The underlying table All object program files using the underlying table (if any) Catalogs containing the description of the index DROP PROCEDURE – – DROP RULE – Use the NonStop SQL/MP DROP CONSTRAINT statement in pass-through mode.
Summary of Support for SQL Server Features Statements Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 5 of 7) Transact-SQL Statement NonStop ODBC Server Support INSERT x Comments You cannot insert rows into a view that references more than one table, even if the columns being inserted belong to only one table. INSERT must be inside a transaction if the table is audited.
Summary of Support for SQL Server Features Statements Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 6 of 7) Transact-SQL Statement NonStop ODBC Server Support Comments SELECT x The following clauses are not supported: • • • • INTO GROUP BY ALL COMPUTE BY FOR BROWSE You cannot include expressions in an aggregate-free expression. SELECT must be inside a transaction if it queries audited objects. Vector aggregates are not supported.
Summary of Support for SQL Server Features Stored Procedures Table B-5. NonStop ODBC Server Support of Transact-SQL Statements (page 7 of 7) Transact-SQL Statement NonStop ODBC Server Support Comments WAITFOR – – WHILE – – WRITETEXT – – x Indicates that the statement is supported – Indicates that the statement is not supported For detailed information on supported statements, see Section 4, Transact-SQL Language.
Summary of Support for SQL Server Features System Procedures Table B-6. SQL Server System Procedures (page 2 of 3) System Procedure Alternatives Available Using NonStop SQL/MP or the HP NonStop Kernel Operating System sp_changedbowner Must be done with Guardian permissions. You can alter file permissions in NonStop SQL/MP using the ALTER TABLE or SECURE statements. sp_commonkey None sp_configure You can configure all tables or specific tables in NonStop SQL/MP using the CONTROL TABLE statement.
Summary of Support for SQL Server Features System Procedures Table B-6. SQL Server System Procedures (page 3 of 3) System Procedure Alternatives Available Using NonStop SQL/MP or the HP NonStop Kernel Operating System sp_helpprotect Use Guardian commands to view file security. sp_helpsql You can use SQLCI to get help on NonStop SQL/MP statements. You must use SQLCI interactively, however; you cannot get help using the NonStop ODBC Server.
Summary of Support for SQL Server Features System Tables System Tables The NonStop ODBC Server supports some of the SQL Server system tables. The following tables summarize NonStop ODBC Server support of the system tables. For detailed information on how the NonStop ODBC Server supports the system tables, see Section 8, HP NonStop ODBC Server Mapping Tables. System Tables in All Databases Table B-7.
Summary of Support for SQL Server Features System Tables in All Databases Table B-7. NonStop ODBC Server Support of System Tables in All Databases (page 2 of 5) Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns printfmt – – Assigned “ ” (blank) syscomments – – – – – sysdepends – – – – – sysindexes x name x VARCHAR (60) The fully qualified SQL Server index name in uppercase letters.
Summary of Support for SQL Server Features System Tables in All Databases Table B-7. NonStop ODBC Server Support of System Tables in All Databases (page 3 of 5) Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns syslogs – – – – – sysobjects x name x VARCHAR (60) The fully qualified SQL Server object name in uppercase letters. id x INT (4) An object ID used internally by the NonStop ODBC Server.
Summary of Support for SQL Server Features System Tables in All Databases Table B-7. NonStop ODBC Server Support of System Tables in All Databases (page 4 of 5) Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns cache – – Assigned -1 sysprocedure s – – – – – sysprotects x id x INT (4) An object ID used internally by the NonStop ODBC Server.
Summary of Support for SQL Server Features System Tables in All Databases Table B-7. NonStop ODBC Server Support of System Tables in All Databases (page 5 of 5) sysusers x Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns domain – – Assigned 0 name x VARCHAR (30) The SQL Server data type name.
Summary of Support for SQL Server Features System Tables in the Master Database System Tables in the Master Database Table B-8. NonStop ODBC Server Support of System Tables in the Master Database Only (page 1 of 2) Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns sysconfigures – – – – – syscurconfigs – – – – – sysdatabases x name x VARCHAR (60) The fully qualified SQL Server database name (database.
Summary of Support for SQL Server Features Variables Table B-8. NonStop ODBC Server Support of System Tables in the Master Database Only (page 2 of 2) sysmessages x Comments or Value Assigned Data Type NonStop ODBC Server Support Column Name NonStop ODBC Server Support Table Name Columns error x INT (4) The SQL Server error code that corresponds to this message. If there is no corresponding SQL Server error code, the code is 18001.
Summary of Support for SQL Server Features Variables Not all global variables are supported. Table B-9 summarizes NonStop ODBC Server support of global variables. Table B-9. Global Variables Global Variable NonStop ODBC Server Support Comments or Value Assigned @@connections x The value is always 1. @@cpu_busy – -1 @@error x No differences. @@idle – -1 @@io_busy – -1 @@max_ connections x Depends on the system resources.
Summary of Support for SQL Server Features Wild-Card Characters Wild-Card Characters The NonStop ODBC Server supports two of the SQL Server wild-card characters. Table B-10.
C Summary of Support for ODBC 2.10 Functions Table C-1 summarizes the ODBC 2.10 functions and shows which are supported by the HP NonStop ODBC Server. Table C-1. NonStop ODBC Server Support of ODBC 2.
Summary of Support for ODBC 2.10 Functions Table C-1. NonStop ODBC Server Support of ODBC 2.
Summary of Support for ODBC 2.10 Functions Table C-1. NonStop ODBC Server Support of ODBC 2.
Summary of Support for ODBC 2.
D Summary of System Installation Defaults Table D-1 lists the values stored in the ZNSSCFG table during system installation. Each value in ZNSSCFG is stored in ASCII VARCHAR format in the VALUE column. When a system default value is used in other tables (such as ZNSPROF, ZNSSER), the value is stored as the data type shown in the Table Data Type column.
Summary of System Installation Defaults Table D-1.
Summary of System Installation Defaults Table D-1.
Summary of System Installation Defaults Table D-1.
Summary of System Installation Defaults Table D-1.
Summary of System Installation Defaults HP NonStop ODBC Server Reference Manual—429151-002 D- 6
E Changing Passwords in a Three-Tier Environment A three-tier environment is a configuration of ODBC where the HP NonStop ODBC/MP driver is invoked by an application server on behalf of a user application. Figure E-1 shows a typical three-tier configuration. Figure E-1. A Typical Three-Tier Configuration CLIENT SERVER 1 SERVER 2 data Application calling NonStop ODBC driver on SERVER 1 Application server, NonStopODBC driver manager, NonStopODBC driver NonStop ODBC Server VST048.
Changing Passwords in a Three-Tier Environment Possible values for the Suppress CHANGEPASSWORD View attribute are:. NO Specifies that detection of an expired password by the NonStop ODBC Server is reported to the user on the same platform where the NonStop ODBC/MP driver is being executed. The user is asked if they wish to change the password by using a popup menu. YES Specifies that detection of an expired password by the NonStop Server is returned as a SQLSTATE error or warning.
F Creating Partitioned Tables To create partitioned tables, follow these steps: 1. Create a Partition Overlay Specification (POS) template 2. Configure your environment 3. Create tables Creating POS Templates A template is composed of one or more NonStop SQL/MP DDL partition specifications. Each template is terminated using a semicolon (;) in the first column of the next line.
Creating Partitioned Tables Configuring for Partitioned Tables Example Template KEY (C1) PARTITION ($volume1.SSSSSSSS.FFFFFFFF EXTENT 64 MAXEXTENTS 160 FIRST KEY -1000 ,$volume2.SSSSSSSS.FFFFFFFF EXTENT 32 MAXEXTENTS 128 FIRST KEY 1000 ,$volume3.SSSSSSSS.FFFFFFFF EXTENT 64 MAXEXTENTS 160 FIRST KEY 2222) ; KEY (C1,C2) PARTITION ($volume1.SSSSSSSS.FFFFFFFF CATALOG \N1.$DATA.SQL2CAT FIRST KEY (-50,”AAAAA”) ,\N2.$volume1.SSSSSSSS.FFFFFFFF CATALOG \N2.$DATA.
Creating Partitioned Tables Example CREATE TABLE Statement Example CREATE TABLE Statement Execute the following CREATE TABLE statement from an ODBC client that is connected to ODBC service configured to use the previous example template. CREATE TABLE db1.sql_odbc. T1 (C1 int, C2 char(5), C3 char(5), PRIMARY KEY (C1,C3)) If TRACE is on, you can see the CREATE TABLE translated into the following SQL/MP statement: CREATE TABLE \N1.$ODBC.SV1.
Creating Partitioned Tables Example CREATE TABLE Statement HP NonStop ODBC Server Reference Manual—429151-002 F-4
Glossary This glossary defines terms used in this manual. Because this manual discusses several products, the glossary indicates which of the following products or categories are associated with each term: • • • • • • • • NonStop ODBC Server NonStop SQL/MP ODBC SQL Server Both NonStop SQL/MP and ODBC/SQL Server Standard SQL HP Industry-standard term aggregate function [standard SQL]. A function that generates a summary value from a group of values in a specified column.
Glossary browse mode [SQL Server] a lock on the data it accesses and does not test for existing locks before reading data. Browse access can be specified only for reading data. Of the access options, this access provides the lowest consistency but the highest concurrency. In the HP NonStop ODBC Server, browse access is available only in pass-through mode. See also browse mode [SQL Server], repeatable access [NonStop SQL/MP], and stable access [NonStop SQL/MP]. browse mode [SQL Server].
Glossary client, DBLIB/TSQL [NonStop ODBC Server] architecture enables users to access shared data and resources. Clients and servers run on a local area network. client, DBLIB/TSQL [NonStop ODBC Server]. A type of client that uses the Microsoft/Sybase TRANSACT-SQL (TSQL) dialect and makes DBLIB function calls. It is a client for SQL Server and the HP SQL Server Gateway product. client, ODBC/CORE [NonStop ODBC Server]. A type of client that uses the ODBC CORE SQL dialect and makes ODBC CLI function calls.
Glossary control-of-flow language [standard SQL] control-of-flow language [standard SQL]. The statements that enable the user to control the flow of execution of statements. Control-of-flow statements include statements such as IF, ELSE, WHILE, and GOTO. Most SQL Server control-of-flow statements are not supported by the NonStop ODBC Server. core services [HP].
Glossary database [industry-standard term] See also Data Control Language (DCL) [standard SQL] and Data Definition Language (DDL) [standard SQL]. database [industry-standard term]. A set of data tables and other database objects organized and presented to serve a specific purpose. See also catalog [NonStop SQL/MP]. database device [SQL Server]. A file in which databases and transaction logs are stored. A database device has both a physical name and a logical name. database object.
Glossary dependent object [standard SQL] dependent object [standard SQL]. An object whose definition depends on a base or underlying object; for example, an index or protection view is dependent on a table. device. See database device [SQL Server]. DLL. See dynamic link library (DLL) [ODBC]. DML. See Data Manipulation Language (DML) [standard SQL]. DOS workstation [industry-standard term]. An IBM personal computer or a compatible computer running DOS. dynamic link library (DLL) [ODBC].
Glossary local area network [industry-standard term] local area network [industry-standard term]. A data communications system that enables PCs to have access to common data and peripherals. LANs typically consist of PCs with adapter cards, file servers, a network operating system, printers, and gateways to departmental or corporate computers. local variable [SQL Server]. A variable declared with the DECLARE statement and assigned an initial value with the SELECT statement.
Glossary NonStop Kernel operating system [HP] NonStop Kernel operating system [HP]. The operating system, which consists of the core and system services. The operating system does not include any application program interfaces. NonStop ODBC Server. A HP product that lets programs developed for ODBC or SQL Server access databases using NonStop SQL/MP. NonStop ODBC Server mapping tables [NonStop ODBC Server].
Glossary object [standard SQL] object [standard SQL]. A database entity created, manipulated, or dropped by means of SQL statements. In NonStop SQL/MP, objects are described in an SQL catalog and include tables, views, columns, partitions, constraints, indexes, and files. In SQL Server, objects are tables, columns, views, indexes, defaults, rules, triggers, and procedures. The NonStop ODBC Server does not support these SQL Server objects: defaults, triggers, rules, and procedures.
Glossary pass-through mode [NonStop ODBC Server] pass-through mode [NonStop ODBC Server]. A mode the NonStop ODBC Server provides for executing statements other than TRANSACT-SQL statements. Pass-through mode allows NonStop SQL/MP statements, catalog utility statements, and trace statements. procedure [SQL Server]. See stored procedure [both ODBC and SQL Server] or system procedures [SQL Server]. profile table [NonStop ODBC Server].
Glossary scalar aggregate function [ODBC/SQL Server] scalar aggregate function [ODBC/SQL Server]. An aggregate function that produces a single value from a SELECT statement that does not include a GROUP BY clause. This result is true whether the aggregate function is operating on all the rows in a table or on a subset of rows defined by a WHERE clause. See also vector aggregate [SQL Server]. SCS (SQL Communications Subsystem) [NonStop ODBC Server].
Glossary SQL Server SQL Server. An SQL-based relational database management system. The server maintains the database and handles queries to and responses from the database. Through the server, client applications such as NExpert Object, Paradox, and SQL File can access the database. SQL Server Gateway [HP]. A HP product available with the C30.00 RVU that provides connectivity between DBLIB clients on PCs or UNIX workstations to NonStop SQL/MP. NonStop ODBC Server supports HP SQL Server Gateway clients.
Glossary system-defined transaction [standard SQL] See also catalog [NonStop SQL/MP], CATALOGS table [NonStop SQL/MP], and user catalog [NonStop SQL/MP]. system-defined transaction [standard SQL]. A transaction initiated by the system to protect the consistency of the database. See also transaction [standard SQL] and user-defined transaction [standard SQL]. system procedures [SQL Server].
Glossary trace statements [NonStop ODBC Server] trace statements [NonStop ODBC Server]. The statements provided with the trace feature. The trace statements are ALTER, CLEAR_LOG, DISPLAY_LOG, DROP_LOG, SAVE, SET, SHOW, and SHOW_LOGS. transaction [standard SQL]. A series of changes to one or more database tables that transforms the data from one consistent state to another. TRANSACT-SQL supports transactions; CORE SQL does not.
Glossary workstation [industry-standard term] The NonStop ODBC Server supports both wild-card characters. workstation [industry-standard term]. A microcomputer or minicomputer attached to other computers through a local-area network. See also DOS workstation [industry-standard term] and OS/2 workstation [industrystandard term]. workstation-based tool [industry-standard term]. A client application such as Excel, Paradox, or SQL File. See also client application [industry-standard term].
Glossary workstation-based tool [industry-standard term] HP NonStop ODBC Server Reference Manual—429151-002 Glossary -16
Index A Access mode See Locking mode Activities, client 2-11 ADD ALIAS statement 7-42 ADD CONTROLstatement 7-46 ADD DEFINE statement 7-49 ADD GOVERNING statement 7-75 ADD INDEX statement 7-50 ADD NET_SERVICE statement 7-53 ADD PROCEDURE statement 7-59 ADD PROCEDURE_COLUMNS statement 7-61 ADD PROFILE statement 7-66 ADD SCFG statement 7-103 ADD SCS statement 7-93 ADD SERVERCLASS statement 7-81 ADD SMAP statement 7-89 ADD TABLE statement 7-105 ADD TRACE statement 7-109 ADD UMAP statement 7-112 ADD VIEW stateme
C Index Catalog access 5-55 customized 7-1 objects dropped with DROP DATABASE 4-65 tasks for customizing 7-2 decustomizing 7-22 integrity 2-38 maintaining 7-27 NonStop ODBC Server 2-22 ODBC 2-27 operations 2-38 specifying security for 6-15 statements in pass-through mode 7-16 TSQL 2-25 utilities See Catalog utilities Catalog utilities and pass-through mode 6-1, 6-3, 6-12 statements ADD ALIAS 7-42 ADD CONTROL 7-46 ADD DEFINE 7-48 ADD GOVERNING 7-75 ADD INDEX 7-50 ADD NET_SERVICE 7-53, 7-55 ADD PROCEDURE 5-
C Index statements (continued) REMOVE SERVERCLASS 7-87 REMOVE SMAP 7-91 REMOVE TABLE 7-106 REMOVE TRACE 7-111 REMOVE UMAP 7-114 REMOVE USER 7-118 REMOVE VIEW 7-120 SCFG 7-103 SCS 7-92 SERVERCLASS 7-80 SMAP 7-89 START SCS 7-100 START SERVERCLASS 7-88 STATUS SCS 7-101 STOP SCS 7-102 STOP SERVERCLASS 7-88 SYSCAT DEINSTALL 7-22 SYSCAT INSTALL 7-18 SYSCAT REFRESH 7-27 SYSCAT UPGRADE 7-36 SYSCAT VALIDATE 7-32 TABLE 7-104 TRACE 7-108 TRA_LOG 7-107 UMAP 7-112 USER 7-115 USERCAT DEINSTALL 7-24 USERCAT INSTALL 7-20
D Index CONTROL QUERY statement, in passthrough mode 6-11 CONTROL TABLE statement, in passthrough mode 6-11 Control-of-flow statements 4-38 CONVERT function 3-23, 4-24 CORE SQL data types 3-8 CORE SQL statements ALTER TABLE 3-33 CALL 3-35 CREATE INDEX 3-37 CREATE TABLE 3-39 CREATE VIEW 3-43 DELETE 3-45 DROP INDEX 3-47 DROP TABLE 3-48 DROP VIEW 3-49 INSERT 3-51 REVOKE 3-53 See also individual statements SELECT 3-54 summary 3-31, A-7 support of 3-31, A-7 translated to NonStop SQL 3-1 UPDATE 3-61 Correlation
D Index DATABASE function 3-22 Datasources 2-7 DATEADD function 4-19 DATEDIFF function 4-19 DATEPART function 4-19 Dateparts day 4-20 dayofyear 4-20 hour 4-20 millisecond 4-20 minute 4-20 month 4-20 quarter 4-20 second 4-20 support of 1-9, 4-20, B-8 week 4-20 weekday 4-20 year 4-20 DATETIME data type format 4-13 Day datepart 4-20 DAYOFMONTH function 3-16 DAYOFWEEK function 3-16 Dayofyear datepart 4-20 DBIORDESC function 9-2 DBIOWDESC function 9-3 DBLIB/TSQL client 2-6 DBRBUF function 9-4 DB-LIBRARY DBIORD
E Index DROP TABLE statement (continued) in user-defined transactions 4-3 syntax 3-48, 4-69 DROP TRA_LOG statement 7-108 DROP VIEW statement dropping dependent objects 3-50, 4-72 in user-defined transactions 4-3 syntax 3-49, 4-71 Dropping dependent indexes 3-48, 3-50, 4-69, 4-72 dependent views 3-48, 3-50, 4-69, 4-72 multiple tables 4-69 partitioned tables 3-49, 4-70 views 3-49, 4-71 Dynamic link library (DLL) 1-3 Dynamic SQL statements, in pass-through mode 6-12 E Embedded SQL 2-3 EMS interface 2-34 Esc
G Index data type conversion (continued) string 3-21 CONCAT 3-21 LENGTH 3-21 LOCATE 3-21 LTRIM 3-22 RTRIM 3-22 SUSBSTRING 3-22 UCASE 3-22 UPPER 4-23 support of 1-7, 3-14, 4-17, A-3, B-5 system 3-22, 4-23 DATABASE 3-22 DB_NAME 4-23 SUSER_NAME 4-24 USER 3-23 USER_ID 4-24 USER_NAME 4-24 G Gateway, function of 2-4 GETDATE function 4-19 Global variables See Variables, global GOVERNING statements 7-75 Governing, resource 2-20 GROUP BY clause 3-58, 4-34, 4-87 Group names See Names, owner Guardian username 2-19
M Index Local variables See Variables, local LOCATE function 3-21 Locating object with ODBC/SQL server name 7-5 Locating object with SQL/MP object name 7-6 Locating objects with mapping tables 7-4 LOCK TABLE statement, in pass-through mode 6-11, 6-20 Locking mode, exclusive 3-60, 4-91, 6-20 LOG command, NOSCOM 7-16 Logical username 2-19 Logon name, Guardian 3-6, 4-9 Lowercase comparisons 4-31 LTRIM function 3-22 M Maintaining catalogs 7-27 Maintaining mapping tables 7-38 Manuals Microsoft ODBC manuals -x
N Index MODIFY NET_SERVICE statement 7-57 MODIFY PROCEDURE statement 7-64 MODIFY PROFILE statement 7-71 MODIFY SCFG statement 7-103 MODIFY SCS statement 7-99 MODIFY SERVERCLASS statement 7-86 MODIFY SMAP statement 7-90 MODIFY TRACE statement 7-111 MODIFY UMAP statement 7-113 MODIFY USER statement 7-117 Month datepart 4-20 MONTH function 3-17 Multiple SCS processes 2-15 N Name mapping 2-22 column names 4-11 correlation names 4-11 database names 3-6, 4-9 index names 3-8, 4-11 object mapping tables 8-2 owne
O Index NOW function 3-17 NSODBC server 2-27/2-30 definition 2-14 NSODBC server configuration 2-20 NULL values and order in which data is displayed 4-34 support of 4-34, A-6, B-10 O OBEY command, NOSCOM 7-16 Object dropping with DROP DATABASE 4-65 locating with mapping tables 7-4 locating with ODBC/SQL server name 7-5 locating with SQL/MP object name 7-6 mapping See Name mapping See also Indexes See also Tables See also Views Object Names listing 7-8 ODBC catalog support 7-121 drivers 1-3, 2-1 interface
Q Index Positioned UPDATE statement 3-61 POWER function 4-22 PREPARE statement, in pass-through mode 6-12, 6-14, 6-20 Primary key, creating 6-16 PRINT statement 4-79 Privileged users and customized catalogs 7-17 PROCEDURE statement 7-58 Procedures names 3-8, 4-11 processes, multiple SCS 2-15 Profile 2-19 PROFILE statement 7-65 Protection views 3-44, 4-60, 6-18 REMOVE UMAP statement 7-114 REMOVE USER statement 7-118 REMOVE VIEW statement 7-120 Renaming Server Objects 7-7 Resource accounting 6-19 Resource
S Index SELECT statement (continued) IN EXCLUSIVE MODE clause 3-60, 4-91 in pass-through mode 6-11, 6-14 INTO clause 4-86, 4-90 ORDER BY clause 3-58, 4-88 syntax 3-54, 4-83 UNION clause 3-58 WHERE clause 3-57, 4-87 Server classes 2-19, 2-32 Server I/O protocols 5-56 Server objects generated names 7-10 renaming 7-7 Server option specifications, pass-through mode and 6-2, 6-3 SERVERCLASS statement 7-80 Server-side view of the NonStop ODBC Server 2-13 Sessions See Connections SET keyword 6-3, 6-4 SET stateme
S Index SP_SRV_ENCODE_RETURN_STATUS 537 SP_SRV_ENCODE_ROW_DATA 5-36 SP_SRV_ENCODE_ROW_DESCR 5-35 SP_SRV_ENCODE_SPELIB_ERROR 5-4 0 SP_SRV_ENCODE_SQL_DIAGNOSTIC 5 -36 SP_SRV_GET_BUFFERLEN 5-40 SP_SRV_GET_INPUT_PARAMS 5-34 SP_SRV_GET_INPUT_PARAM_INFO 5-3 3 SP_SRV_GET_NUM_INPUT_PARAMS 533 SP_SRV_GET_REQ_PROCESS_NAME 5 -32 SP_SRV_GET_SERVICE_NAME 5-31 SP_SRV_GET_SP_NAME 5-31 SP_SRV_GET_USER_NAMES 5-32 SP_SRV_SET_CURRENT_VERSION 5-30 SQL Communication Subsystem client communication 2-31 definition 2-14 descript
T Index SUM function 3-28, 4-33 SUSER_NAME function 4-24 SYSCAT DEINSTALL statement 7-22 SYSCAT INSTALL statement 7-18 SYSCAT REFRESH statement 7-27 SYSCAT UPGRADE statement 7-36 SYSCAT VALIDATE statement 7-32 SYSCOLUMNS system table 8-62 SYSDATABASES and USERCAT DEINSTALL 7-26 SYSDATABASES system table 8-63 SYSINDEXES system table 8-64 SYSMESSAGES system table 8-66 SYSOBJECTS system table 8-67 SYSPROTECTS system table 8-69 System naming rules 3-6, 4-8 procedures, support of B-17 table SYSCOLUMNS 8-62, 8-
U Index Transactions (continued) in pass-through mode 6-7 names 4-11 Transact-SQL statements ALTER TABLE 4-42 BEGIN TRANSACTION 4-45 BEGIN...
V Index Usernames (continued) Guardian 2-19 logical 2-19 Users, types of 2-2 USER_ID function 4-24 USER_NAME function 4-24 UTIL keyword 6-3 Utility statements See Catalog utilities, statements V Variables global declaring values for 4-27 in batches 4-27 support of 4-26 local and aggregates 4-26 creating 4-61 declaring 4-61 referencing when null 4-26, 4-62 support of 4-26 names 4-12 support of 4-26, B-26 View definitions ZVSDB 8-63 ZVSMSG 8-66 ZVUCOL 8-62 ZVUDT 8-70 ZVUIX 8-64 ZVUOBJ 8-67 ZVUOCOL 8-60 ZVU
Special Characters Index ZNSMSG mapping table 8-18 ZNSNET mapping table 8-20 ZNSPROF mapping table 8-22 ZNSPROT mapping table 8-27 ZNSSCFG mapping table 8-28 ZNSSCS mapping table 8-32 ZNSSER mapping table 8-34 ZNSSMAP mapping table 8-36 ZNSTRA mapping table 8-37 ZNSUMAP mapping table 8-39 ZNSUS mapping table 8-40 ZNSUSI1 index 8-40 ZNSUSI2 index 8-40 ZNSVALUE mapping table 8-41 ZNUDT mapping table 8-42 ZNUIX mapping table 8-44 ZNUIXI1 index 8-44 ZNUMTRX mapping table 8-45 ZNUOBJ mapping table 8-51 ZNUOBJI
Special Characters Index HP NonStop ODBC Server Reference Manual—429151-002 Index -18
