HP NonStop SQL/MX Connectivity Service Manual Abstract This manual describes how to install and manage the HP NonStop™ SQL/MX connectivity service. This product enables applications developed for the Microsoft® Open Database Connectivity (ODBC) application programming interface and the Java Database Connectivity (JDBC) API to use HP NonStop SQL/MX to access HP NonStop SQL databases on an HP NonStop system. Product Version HP NonStop SQL/MX Connectivity Service (MXCS) 2.
Document History Part Number Product Version Published 520944-004 HP NonStop MXCS, Version 2.0 November 2008 524944-005 HP NonStop MXCS, Version 2.0 May 2009 524944-006 HP NonStop MXCS, Version 2.0 November 2009 524944-007 HP NonStop MXCS, Version 2.0 February 2010 524944-008 HP NonStop MXCS, Version 2.
Legal Notices © Copyright 2010 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MX Connectivity Service Manual Glossary Index Figures Tables Legal Notices What’s New in This Manual v Manual Information v New and Changed Information About This Manual vii Related Documentation v vii 1.
Contents 2. Installing, Starting, and Uninstalling MXCS (continued) 2. Installing, Starting, and Uninstalling MXCS (continued) Step 3: Configure for Statistics Collection and Server Trace (optional) Step 4: Start the MXCS Service 2-3 Configuring MXCS as a Persistent Process 2-3 Starting the MXCS Service 2-4 Upgrading to MXCS 2.0 from a Previous Version of ODBC/MX 2-6 List of Installed Files 2-7 Server Files 2-7 Uninstalling MXCS from the Server 2-7 3.
3. Starting, Configuring, and Managing MXCS (continued) Contents 3. Starting, Configuring, and Managing MXCS (continued) Troubleshooting MFC 3-30 A.
B. Using Microsoft Access with ODBC/MX Contents B. Using Microsoft Access with ODBC/MX SQL_ATTR_ACCESS_VERSION for Microsoft Access B-1 SQL_ATTR_MAP_DATATYPE for Microsoft Access B-1 SQL_ATTR_ACCESS_MODE for Microsoft Access B-2 Glossary Index Figures Figure i. Figure 1-1. Figure 1-2. Figure A-1. Manuals in the SQL/MX Library x MXCS Architecture - Interactions Among Three Components 1-2 Connection Data Flow 1-5 Mapping NonStop ODBC Server Name Components to ODBC/MX Tables Table 3-1. Table 3-2.
What’s New in This Manual Manual Information HP NonStop SQL/MX Connectivity Service Manual Abstract This manual describes how to install and manage the HP NonStop™ SQL/MX connectivity service. This product enables applications developed for the Microsoft® Open Database Connectivity (ODBC) application programming interface and the Java Database Connectivity (JDBC) API to use HP NonStop SQL/MX to access HP NonStop SQL databases on an HP NonStop system.
What’s New in This Manual • • Changes to the H06.20/J06.09 Manual Updated the Managing MFC section on page 3-29. Updated the Troubleshooting MFC section on page 3-31. Changes to the H06.20/J06.
About This Manual Audience This manual is intended for all MXCS users, including system administrators and database administrators who install, configure, and manage MXCS. You should be familiar with: • • • Microsoft ODBC 3.5 API SQL/MX 2.0 Microsoft Windows 98, Windows NT 4.
Related Documentation About This Manual Specialized Guides SQL/MX Installation and Management Guide Describes how to plan for, install, create, and manage an SQL/MX database. Explains how to use installation and management commands and utilities. SQL/MX Query Guide Describes how to understand query execution plans and write optimal queries for an SQL/MX database. SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process.
Related Documentation About This Manual These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
Related Documentation About This Manual Figure i.
Hypertext Links About This Manual 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. Notation Conventions The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
Change Bar Notation About This Manual An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be entered as shown. Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
1 Overview of MXCS HP NonStop SQL/MX Connectivity Services (MXCS) enables applications developed for the Microsoft® Open Database Connectivity (ODBC) application programming interface (API) and the Java Database Connectivity (JDBC) API to use SQL/MX to access a NonStop SQL database on an HP NonStop server. MXCS is managed by NSM/web, which is a web-based graphical user interface (GUI) management tool for configuring and monitoring the server components and data sources.
Data Sources Overview of MXCS Figure 1-1.
Client Data Sources Overview of MXCS Default Data Source MXCS provides a preconfigured, default server data source on the server called TDM_Default_DataSource. You can stop and start the TDM_Default_DataSource using NSM/web, but you cannot delete it. The TDM_Default_DataSource has these functions: • • Clients can connect to TDM_Default_DataSource and access a NonStop SQL database with no special configuration required.
Overview of MXCS Connecting a Client to a Data Source the name of the Server Data source and other connection details as part of the connect API call. 2. The association server grants the connection request and associates the client application with an available MXCS SQL server for the data source. (If the association server does not find the requested data source name, it assigns a server associated with the default data source.) Note.
Connection Mapping Overview of MXCS Figure 1-2. Connection Data Flow Client Application Client API Client Driver Manager 1. Request connection to DS1 Client Driver MXCS Association Server 2. Connection to DS1 granted 3. Client connects to server 4. Server accepts connection MXCS SQL Server MXCS SQL Server Other Data Source: DS1 Tdm_Default_DataSource VST002.
Security Overview of MXCS Security Access authorization Authorization for access to NonStop SQL databases uses authenticated Guardian user names. The MXCS SQL server program (MXOSRVR) follows SQL/MX rules for accessing SQL/MX objects (and SQL/MP rules for SQL/MP objects), and as such the SQL objects are subject to the normal SQL rules that govern access to those objects. For example, to read from an MX table, the user must have SELECT privilege.
Operation Access Controls Overview of MXCS This setting directs SQLConnect, SQLBrowseConnect and SQLDriverConnect to return SQL_SUCCESS_WITH_INFO with one of these warnings: • [WARNING 8857] Password to Expire in n days, n hours, n minutes. (If the password expires in less than two days.) or • [WARNING 8837] CLI Authorization failure: Error 48 with status 11 was returned by Safecom. (If the password is expired and in the grace period.) 2.
Errors, Events, and Warnings Overview of MXCS MXCS Permission Access USER View status and configuration of MXCS services, data sources, and server. View permissions. OPERATOR Enable, disable, manage, and configure MXCS. View permissions. Errors, Events, and Warnings All exceptions that occur during the MXCS service are written to the Event Management Service (EMS) operator collector $0 or to the alternate EMS collector if you configured it when you started the MXCS service.
Unsupported NonStop ODBC Server Features Overview of MXCS Connection information Session summary • Statement collection is typically used for monitoring query performance or to identify usage patterns. Statement statistics report data about the following SQL statements executed during statistics collection. SQLExecDirect SQLPrepare SQLExecute SQLFetch See the MXCS online help that is available on NSM/web for detailed descriptions of the statistics collected.
Overview of MXCS Migrating From a NonStop ODBC Server Environment HP NonStop SQL/MX Connectivity Service Manual—524944-008 1-10
2 Installing, Starting, and Uninstalling MXCS Topic Page Components of the MX connectivity service 2-1 MXCS Configuration and Metadata Tables 2-1 Starting the MXCS Service 2-4 Upgrading to MXCS 2.0 from a Previous Version of ODBC/MX 2-6 List of Installed Files 2-7 Uninstalling MXCS from the Server 2-7 Components of the MX connectivity service To fully install all components of connectivity service, you must install the following: • MXCS server components on a NonStop system Note.
Installing, Starting, and Uninstalling MXCS Installation Requirements For more information about these tables, see the SQL/MX Reference Manual. Installation Requirements Before MXCS is installed and configured, you must verify that the following are valid and present in your environment, in addition to the installation requirements specified for SQL/MX: Requirement Description Operating system The server components must be installed on a NonStop system running using an operating system version G06.
Installing, Starting, and Uninstalling MXCS Step 1: Install the MXCS Server Components Step 1: Install the MXCS Server Components Use DSM/SCM to install the MXCS server components from the Software Update Tape (SUT). This installs the server components and the ODBC/MX Client installation program in the ZMXODBC installation subvolume.
Installing, Starting, and Uninstalling MXCS Starting the MXCS Service The following example commands configure MXCS as a persistent process, and then start it. TACL> scf 1-> assume process $zzkrn 2-> add #mxoas, cpu firstof(01,00), AutoRestart 10, hometerm $zhome, name $mxo, StartMode 2-> application 2-> startupmsg “-pn 35000”, program $system.zmxodbc.mxoas 3-> start #mxoas 4-> status #mxoas Your installation might require different parameters or to configure a different set of CPUs.
Installing, Starting, and Uninstalling MXCS Starting the MXCS Service -TCP tcp-process specifies the Guardian name of the TCP/IP process. If TCPIP^process^name is defined before starting MXOAS, that name is used. If TCPIP^process^name is not defined, the default name is $ZTC0. -EMS collector-name specifies the name of the EMS collector to be used for event messages generated by MXCS; the default is $0.
Installing, Starting, and Uninstalling MXCS Upgrading to MXCS 2.0 from a Previous Version of ODBC/MX -TMC alternate-collector-name specifies the name of the alternate EMS collector to be used for server tracing generated by MXCS. If option [-TMC alternate-collector-name] is not provided, MXCS Server Trace sends trace messages to the primary EMS collector. You must start the alternate collector before starting MXCS.
List of Installed Files Installing, Starting, and Uninstalling MXCS For information about the MIGRATE utility, see the SQL/MX Installation and Management Guide. List of Installed Files Server Files File Name ISV Description MXOAS ZMXODBC Association server MXOCFG ZMXODBC Configuration server MXOSRVR ZMXODBC MXCS SQL server TDMODBC ZMXODBC File that contains the client ODBC components for communicating with the server.
Installing, Starting, and Uninstalling MXCS Uninstalling MXCS from the Server HP NonStop SQL/MX Connectivity Service Manual—524944-008 2 -8
3 Starting, Configuring, and Managing MXCS The MXCS service Logging on 3-2 Displaying the MXCS Service Status 3-2 Enabling the MXCS Service 3-2 Disabling the MXCS Service 3-2 Displaying Server Status 3-3 Stopping Servers 3-3 Managing Multiple MXCS Services 3-3 MXCS server data sources Displaying Data Source Status 3-3 Creating and Configuring Server Data Sources 3-4 Reconfiguring Server Data Sources 3-4 Starting MXCS Data Sources 3-5 Stopping MXCS Data Sources 3-6 Deleting MXCS Data
Starting, Configuring, and Managing MXCS Managing the MXCS Service Managing the MXCS Service Only user ID’s with MXCS OPERATOR permission can perform MXCS management tasks. See Operation Access Controls on page 1-7 for information about MXCS permissions. Logging on To make changes to the MXCS configuration, you must be logged on with a user ID that has OPERATOR permission. User IDs with USER permission can only view the configuration. Use NSM/web to view the user permissions assigned.
Starting, Configuring, and Managing MXCS Displaying Server Status 4. In the dialog box, enter a reason for disabling the service. 5. Click the appropriate stop option. For more information about a screen, click Help. Displaying Server Status 1. Using NSM/web, expand MXCS Services. 2. Click the name of the service. 3. Click the MXCS Server Status tab. For more information about a screen, click Help. Stopping Servers You must use a user ID that has OPERATOR permission to perform management tasks. 1.
Creating and Configuring Server Data Sources Starting, Configuring, and Managing MXCS 2. Click the Data Source Status tab. For more information about a screen, click Help Creating and Configuring Server Data Sources You can modify the configuration of a data source while the data source is running. You must use a user ID that has OPERATOR permission to perform management tasks. 1. Using NSM/web, right-click Data Source Configurations. 2. Click Create New Data Source to start the data source wizard. 3.
Starting, Configuring, and Managing MXCS Starting MXCS Data Sources Using Case-Sensitive table names To handle the system metadata table names that are case-sensitive, use NSM/web to set the SQL_ATTR_METADATA_ID environment variable for the data source. 1. Using NSM/web, expand Data Source Configurations. 2. Click a data source name. 3. Click the Sets tab. 4. Click + to add a new Set. 5. Enter SQL_ATTR_METADATA_ID for Name and SQL_TRUE for Value. 6. Click Ok when finished.
Starting, Configuring, and Managing MXCS Stopping MXCS Data Sources 4. Click Start. MXCS starts the data source and its associated MXCS servers. The MXCS association server must be started before you can start a data source. To determine if the association server is started, in the MXCS Services list, click the service name and check the Status tab to see if the state of the association server is STARTED. To use MACL, see the SQL/MX Connectivity Service Administrative Command Reference manual.
Starting, Configuring, and Managing MXCS Resource Management For more information about a screen, click Help. Note. You cannot delete the MXCS default data source (TDM_Default_DataSource). Resource Management You can assign a resource management policy to a data source by adding the policy using NSM/web. You can also delete the policy that is currently in effect. Adding or Removing a Resource Management Policy Requirement: You must have OPERATOR permission to perform management tasks.
Starting, Configuring, and Managing MXCS Resource Statistics MXCS Server can collect these statistics: • Session Statistics Connection information Session summary • Statement Statistics ° SQL statement — shows the text of the SQL query for individual SQL statements executed but does not include other statistics ° Statistical data about individual statements SQLExecDirect SQLExecute SQLPrepare SQLFetch You may choose any combination of options.
Starting, Configuring, and Managing MXCS Resource Statistics SQLExecute SQLExecute statistics are written to the alternate EMS collector at the time an Execute statement is received. Table 3-3. Statement Statistics - SQLExecute Statistic Value Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established. Message Attribute STATEMENT:SQLExecute (the Statistics option that was selected.) Sequence Number Message sequence number generated by MXCS.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-4. Statement Statistics - SQLExecDirect Statistic Value Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established. Message Attribute STATEMENT:SQLExecDirect (Statistics option that was selected) Sequence Number Message sequence number generated by MXCS. The number begins with 0 (zero) and is incremented by 1 with each new record.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-5. Statement Statistics - SQLPrepare Statistic Value Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established. Message Attribute STATEMENT:SQLPrepare (Statistics option that was selected) Sequence Number Message sequence number generated by MXCS. The number begins with 0 (zero) and is incremented by 1 with each new record.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-6. Statement Statistics - SQLFetch Statistic Value LockEscalation Cumulative number of times record locks are escalated to file locks. ODBCElapsedTime Total MXCS SQL server real time, in microseconds, from the time the fetch was prepared to the time it was closed.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-8. Session Statistics - Session Summary Statistic Value Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established. Message Attribute SESSION:Summary (Statistics option that was selected). Sequence Number Message sequence number generated by MXCS. The number begins with zero (0) and is incremented by 1 with each new record. StartTime Time when the connection request was received.
Resource Statistics Starting, Configuring, and Managing MXCS Table 3-8. Session Statistics - Session Summary Statistic Value TotalExecDirects Total number of SQLExecDirect calls. TotalErrors Total number of SQL statement execution errors detected by the MXCS SQL server. TotalWarnings Total number of SQL statement execution warnings detected by the MXCS SQL server.
Starting, Configuring, and Managing MXCS Collecting Server Statistics TotalInsertStmtsExecuted:2 TotalDeleteStmtsExecuted:0 TotalUpdateStmtsExecuted:0 TotalSelectStmtsExecuted:2 TotalCatalogStmts:0 TotalPrepares:2 TotalExecutes:2 TotalFetches:4 TotalCloses:3 TotalExecDirects:3 TotalErrors:1 TotalWarnings:2 • • • SQL Statement — The first message contains information for the SQL Statement option and shows that an SQL SELECT statement was prepared for execution.
Starting, Configuring, and Managing MXCS Collecting Server Statistics 1. Using NSM/web, expand Data Source Configurations. 2. Click a data source name. 3. Click on the Resource Statistics tab. 4. Check one or more statistics options. 5. Click Apply. Statistics collection begins for all available servers and connected servers. For more information about a screen, click Help. Stopping Statistics Collection You must use a user ID that has OPERATOR permission to perform management tasks. 1.
Tracing Server Activity Starting, Configuring, and Managing MXCS Format of Event Message 21035 21035 MXCS Statistics: Session ID: session-id Message Attribute: message-attribute Sequence Number:sequence-number Message Info:statistics-information message-attribute is: { SESSION:ConnectionInformation { SESSION: SessionSummary { STATEMENT: SQLStatement { STATEMENT: SQLExecDirect { STATEMENT: SQLExecute { STATEMENT: SQLPrepare { STATEMENT: SQLFetch } } } } } } } session-id Unique session ID generated by the
Starting, Configuring, and Managing MXCS Tracing Server Activity Session ID Function Name Sequence Number Function information Every trace event generates a pair of messages. The first message is written on entry to a function, and contains the input parameters to the function. The second is written on exit, and contains the exit parameters for the function. See Example Server Trace Log for the Connect Function on page 3-20.
Starting, Configuring, and Managing MXCS Tracing Server Activity Using NSM/web, you can see the enabled or disabled status of traces on the Data Sources Status tab. Stopping Server Tracing You must use a user ID that has OPERATOR permission to perform management tasks. 1. Do one of the following: a. Using NSM/web, click MXCS Services. b. Expand MXCS Services and click a service name. 2. Click the Data Source Status tab. 3. Clear the Tracing column for the data sources you want to stop tracing. 4.
Starting, Configuring, and Managing MXCS Tracing Server Activity Viewing Trace Information The EMS alternate collector collects the ODBC/MX server trace information and stores it in EMS log, message 21034. For a list of data collected in a server trace, see Server data collected: on page 3-17. See message Format of Event Message 21034 on page 3-21 for the message format. Example Server Trace Log for the Connect Function The following example shows the trace messages returned for the connect function.
Starting, Configuring, and Managing MXCS MXCS Support for SequeLink Servers Format of Event Message 21034 21034 MXCS Trace: Session ID: session-id Function: Enterfunction Sequence Number: sequence-number function-input-parameters OR Session ID: session-id Function: Exitfunction Sequence Number: sequence-number function-exit-parameters session-id unique session ID generated by the MXCS SQL server at the time the connection is established. function The MXCS SQL server function that is being traced.
Starting, Configuring, and Managing MXCS Configuring Data Source Attributes for SequeLink Configuring Data Source Attributes for SequeLink The MXCS version 2.0 installation includes the template file MXODSN, which contains the information necessary for SequeLink servers that connect to MXCS server data sources. 1. Edit the MXODSN file. The MXODSN file is installed in the ZMXODBC subvolume.
Configuring Data Source Attributes for SequeLink Starting, Configuring, and Managing MXCS Table 3-9. Format of the MXODSN file (page 1 of 3) File Section Contents and Format [ODBC] Trace information Trace Attribute Description TraceStart Trace off = 0 Trace on = 1 If TraceStart keyword is omitted, TraceStart = 0 TraceFile Guardian subvolume name where trace files are created.
Configuring Data Source Attributes for SequeLink Starting, Configuring, and Managing MXCS Table 3-9. Format of the MXODSN file (page 2 of 3) File Section Contents and Format FetchBufferSize [data-source-name] continued Size of buffer in Kb used to fetch rows • • • Range: 0 to 256 Kb SYSTEM_DEFAULT = 8Kb Single row = 0 (use to get update on current operation or when exercising Publish/Subscribe) Data Source Attribute Description Server FILE:.
Starting, Configuring, and Managing MXCS Configuring Data Source Attributes for SequeLink Table 3-9. Format of the MXODSN file (page 3 of 3) File Section Contents and Format CATALOG Name of the catalog to be used. If not specified, the value is taken from those defined by the MX connectivity service. Default: NONSTOP_SYSTEM_NSK. SCHEMA Name of the schema to be used. If not specified, the value is taken from those defined by the MX connectivity service. Default: PUBLIC_ACCESS_SCHEMA.
Configuring Data Source Attributes for SequeLink Starting, Configuring, and Managing MXCS In this example, the attributes CATALOG and SCHEMA for data source ATC_TESTS are defined as “cat” and “sch”, which are case sensitive names.
Starting, Configuring, and Managing MXCS Module File Caching (MFC) Module File Caching (MFC) Before the H06.18/J06.07 RVU, the ODBC/MX driver did not cache the NonStop SQL/MX prepared statements. As a result, applications recompiled the same SQL/MX statements, causing higher host utilization, which could lead to worse perceived performance of the ODBC client application. The Module File Caching (MFC) feature shares the SQL/MX prepared statement plans among the SQL/MX database connections.
Starting, Configuring, and Managing MXCS Configuring MFC 2. Configure a data source for the user-specified MXOAS service: add ds $MXOAS.”MCBS_datasource”; Note. For instructions to add a data source, see the SQL/MX Connectivity Service Administrative Command Reference manual. 3. Stop the data source: stop ds “MCBS_datasource”, reason ‘test’; Note. Perform step 3 only if the configured data source is in the started state. 4.
Starting, Configuring, and Managing MXCS Managing MFC 2. Temporary files such as *.lst,*.sql*.mdf ; all these files are retained in the directory to enable easy troubleshooting. Note. If you specify the compiled_module_location evar to a value other than the default, such as /usr/temp, ensure that the directory is created before specifying it. 1. Start the data source: start ds $MXOAS.
Starting, Configuring, and Managing MXCS Benefits of MFC Benefits of MFC JDBC applications that use the java.sql.PreparedStatement object and ODBC applications that use complex queries through the path SQLPrepare() ->SQLExecute() -> SQLFetch() calls result in lower processor utilization, lower memory consumption, and better response time. A query that uses the java.sql.Statement object from JDBC and the execdirect () API from ODBC will not benefit from the MFC feature.
Starting, Configuring, and Managing MXCS Troubleshooting MFC .mdf Files The .mdf temporary files are generated during preprocessing. These .mdf files are retained for easier support and troubleshooting. The preprocessor creates an SQL module definition file in the location that contains the embedded SQL statements. Note. For more information on module definition files, see the HP NonStop SQL/MX Programming Manual for C and COBOL. .lst Files The .
Starting, Configuring, and Managing MXCS Troubleshooting MFC 2. At an SCF prompt, enter the SCF command: add server , cpu 1, backupcpu 2 3. Add a fileset: add fileset mxc1,nameserver #(server name),catalog $(volume other than $oss), pool mxc1pool, mntpoint "/usr/mymodules" Note. Copy the ‘POOL’ template file located in the $SYSTEM.ZXOSSMON volume and modify the default volume setting to point to the location of the newly defined fileset mountpoint. 4.
Starting, Configuring, and Managing MXCS Troubleshooting MFC To enable OSS caching, perform the following steps: 1. At a TACL prompt, enter: SCF and then enter: assume $zpmon 2. At an SCF prompt, enter the following SCF command to stop all filesets on your system: STOP FILESET $ZPMON.* This command begins with the last fileset mounted and stops the filesets in the reverse order in which they were last started. 3.
Troubleshooting MFC Starting, Configuring, and Managing MXCS Known Issues Scenario 1 MFC plans become obsolete when the base table is altered or dropped. The following sequence of operations illustrates the issue. Operation Expected Result Actual Result Remarks Create table testing(info int); Success Success Table testing is created. Stmt1 = Prepare("select * from testing") Success Success Stmt1 is prepared with MXCMP. Stmt1.execute() Success Success Stmt1 is executed. Stmt1.
Troubleshooting MFC Starting, Configuring, and Managing MXCS Stmt1.execute() Success Failure MXOSRVR turned the SQL/MX CQD recompilation_warnings ON. SQL/MX throws SQL exception upon similarity check failure and MXOSRVR drops the invalid module file from the location. Stmt1 = Prepare("select * from testing") Success Success A new plan is created in the MFC location. Stmt1.execute() Success Success MFC statement works as expected.
Starting, Configuring, and Managing MXCS HP NonStop SQL/MX Connectivity Service Manual—524944-008 3- 36 Troubleshooting MFC
A Migrating Applications from NonStop ODBC Server You must migrate your NonStop ODBC Server applications if you plan to take advantage of the compiler and executor features of SQL/MX for your applications. By migrating your applications, you also take advantage of the ODBC 3.5 compliant API driver. Like SQL/MX (BSR X3.135-1992), ODBC/MX is based on the ISO/IEC 9075-3 standard, documented by Microsoft in the ODBC 3.5 Programmer's Guide.
Migrating Applications from NonStop ODBC Server Server: Duplicating NonStop ODBC Server Logical Database Object Names 3. Duplicate the NonStop ODBC Server logical database object names to the SQL/MX environment. For detailed information, see Server: Duplicating NonStop ODBC Server Logical Database Object Names on page A-2 4. Migrate the NonStop ODBC Server configuration settings to MXCS. For detailed information, see Server: Migrating NonStop ODBC Server Configuration Settings on page A-5. 5.
Migrating Applications from NonStop ODBC Server ANSI Names information returned by ODBC/MX for those names will match the metadata information defined for NonStop ODBC Server. Note. You do not need to duplicate names if the application does not rely on the NonStop ODBC Server logical names. However, if you want to use long object names, use the CREATE SQLMP ALIAS command in MXCI to add the long names to SQL/MX. The catalog and schema names must exist before you create the alias.
CREATE SQLMP ALIAS Command Migrating Applications from NonStop ODBC Server Figure A-1. Mapping NonStop ODBC Server Name Components to ODBC/MX NonStop(TM) MXCS CREATE SQL/MP ALIAS MXCS_DATA02_CAT.DBO.EMPLOYEE \MXCS.$DATA02.PERSNL.EMPLOYEE; NonStop(TM) ODBC Server ZNSDB T_DBNAME ZNUOBJ T_UNAME ODBC_DATA02_CAT DBO T_OBJNAME N_OBJNAME EMPLOYEE \ODBC.$DATA02.PERSNL.EMPLOYEE VST003.vsd CREATE SQLMP ALIAS Command CREATE SQLMP ALIAS catalog_name.schema_name.
Migrating Applications from NonStop ODBC Server Accessing Migrated Names For example, the following command generates an initial script containing SQL commands: log mvscript; select 'SELECT ''CREATE SQLMP ALIAS ''', '||'T_DBNAME || ''.'' || T_UNAME || ''.'' || T_OBJNAME || '' '' ||N_OBJNAME', '|| '';''', 'FROM ZNSDB, ', TRIM(N_CATALOG) || '.
Migrating Applications from NonStop ODBC Server Comparison of Configuration Terms While NonStop ODBC Server configuration is performed through the NOSCOM program on the NonStop system, MXCS uses MXCI or NSM/web (a management console) to enter the required configuration parameters. Note. If both products are used at the same time (co-exist) in the same system, the port number defined for both products must be different, and the MXCS port range should not overlap the NonStop ODBC Server port number.
Migrating Applications from NonStop ODBC Server Resource Governing Resource Governing Both products provide resource governing capabilities. MXCS supports resource governing based on cost. When configured, MXCS sends an event message (EMS) to register any attempt to exceed the cost limit. The query can be stopped or executed. Resource governing policies are configured through the Resource Management tab in NSM/web.
Migrating Applications from NonStop ODBC Server Configuring MXCS Data Sources Configuring MXCS Data Sources A server class in an NonStop ODBC Server environment corresponds to a data source in an MXCS environment. The following table compares the terminology between NonStop ODBC Server and MXCS. NonStop ODBC Server Class Attributes Corresponding MXCS Data Source Attributes Guardian user name User name is based on the login name for the connecting user ID; there is no default.
Migrating Applications from NonStop ODBC Server • • • • Setting MXCS Service Attributes protocol name IOP process name SCS process name Multilan specific parameters MXCS supports only the TCP/IP protocol for remote clients and file system for local clients on NonStop servers. All parameters required by the network are defined in the command line when you start the association server.
Migrating Applications from NonStop ODBC Server • • Client-Side Migration Steps User profile information (default database name, default schema name, default location for the creation of objects, default security, trace configuration, accounting configuration, resource governing configuration, SQL access mode, cursor mode, SQL dialect, maximum statement caching, control statements). The mapping between SCS and Server class names.
Migrating Applications from NonStop ODBC Server ODBC API calls ODBC API calls ODBC 2.x compliant applications do not need to be migrated to support the new 3.x functions. The Microsoft ODBC driver manager allows version 2.x applications to run using version 3.x-compliant drivers and vice versa. It automatically maps version 2.x application functions to version 3.x-compliant drivers, version 3.x application functions to version 2.-compliant drivers, and it performs mapping between 2.x and 3.
Migrating Applications from NonStop ODBC Server ODBC API calls Table A-1. ODBC API Mapping Between 2.x and 3.x Versions (page 2 of 3) ODBC 2.x Calls* ODBC 3.x Calls* 3.
Migrating Applications from NonStop ODBC Server Modifying SQL Statements Table A-1. ODBC API Mapping Between 2.x and 3.x Versions (page 3 of 3) 3.x Calls Supported by ODBC/MX? 2.x Calls Supported by ODBC Server? ODBC 2.x Calls* ODBC 3.
Migrating Applications from NonStop ODBC Server SYSINDEXES SYSMESSAGES Modifying Pass-Through Commands SYSTYPES SYSUSERS Note. HP recommends that an ODBC-compliant application use the ODBC Catalog APIs to retrieve catalog information instead of selecting data from Microsoft SQL Server system tables. If NonStop ODBC Server is running, you can migrate the system tables objects by adding the logical name mappings for those tables into the MP_PARTITIONS table; use the ANSI names to select the tables.
Migrating Applications from NonStop ODBC Server NonStop ODBC Server Migration Example NonStop ODBC Server Migration Example Server Characteristics In this example, the server has the following characteristics: System Catalog: \TEST.$DATA02.SYSCAT User Catalog: \TEST.$DATA07.SAMDBCAT User Tables: \TEST.$DATA04.PERSNL.EMPLOYEE \TEST.$DATA04.PERSNL.JOB \TEST.$DATA04.PERSNL.DEPT \TEST.$DATA04.PERSNL.MGRLIST Table owner DBAGR.
Migrating Applications from NonStop ODBC Server System Catalog Names Database Name Owner Name Object Name Guardian Name MASTER DBO SYSTYPES \TEST.$DATA02.SYSCAT.ZVUDT MASTER DBO SYSUSERS \TEST.$DATA02.SYSCAT.ZVUUS MASTER DBO TABLES \TEST.$DATA02.SYSCAT.TABLES MASTER DBO TRANSIDS \TEST.$DATA02.SYSCAT.TRANSIDS MASTER DBO USAGES \TEST.$DATA02.SYSCAT.USAGES MASTER DBO VERSIONS \TEST.$DATA02.SYSCAT.VERSIONS MASTER DBO VIEWS \TEST.$DATA02.SYSCAT.VIEWS MASTER DBO ZNSALT \TEST.
Migrating Applications from NonStop ODBC Server User Catalog Names Database Name Owner Name Object Name Guardian Name MASTER DBO ZODS \TEST.$DATA02.SYSCAT.ZODS MASTER DBO ZODT \TEST.$DATA02.SYSCAT.ZODT MASTER DBO ZOENV \TEST.$DATA02.SYSCAT.ZOENV MASTER DBO ZONAM2ID \TEST.$DATA02.SYSCAT.ZONAM2ID MASTER DBO ZORES \TEST.$DATA02.SYSCAT.ZORES MASTER DBO ZTEST \TEST.$DATA02.SYSCAT.
Migrating Applications from NonStop ODBC Server Database Name Owner Name User Catalog Names Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL PROGRAMS \TEST.$DATA07. SAMDBCAT. PROGRAMS TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL SYSCOLUMNS \TEST.$DATA07. SAMDBCAT. ZVUCOL TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL SYSINDEXES \TEST.$DATA07. SAMDBCAT. ZVUIX TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL SYSOBJECTS \TEST.$DATA07. SAMDBCAT. ZVUOBJ TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL SYSPROTECTS \TEST.
Migrating Applications from NonStop ODBC Server Database Name Owner Name User Catalog Names Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZNUOBJ \TEST.$DATA07. SAMDBCAT. ZNUOBJ TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZNUPCOL \TEST.$DATA07. SAMDBCAT. ZNUPCOL TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZNUPROC \TEST.$DATA07. SAMDBCAT. ZNUPROC TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZVUOCOL \TEST.$DATA07. SAMDBCAT. ZVUOCOL TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZVUPCOL \TEST.$DATA07. SAMDBCAT.
Migrating Applications from NonStop ODBC Server Database Name Configuration Example for the NonStop ODBC Server Owner Name Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL PARTS \TEST.$DATA07. SALES.PARTS TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL PARTSUPP \TEST.$DATA07. INVENT. PARTSUPP TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL SUPPLIER \TEST.$DATA07. SALES.SUPPLIER TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL VIEW207 \TEST.$DATA07. INVENT.
Migrating Applications from NonStop ODBC Server Configuration Example for the NonStop ODBC Server EMIT_EVENTS N MEMORY_CHECK 0 SERVERCLASSES_CONFIGURED 1 SCS_NAME \TEST.$NSOAD JOB_ID 1 SCS_OBJECT \TEST.$DATA09.NOS7.
Migrating Applications from NonStop ODBC Server Configuration Example for the NonStop ODBC Server IOP_NAME \TEST.$ZTC0 SO_KEEPALIVE 1 SO_OOBINLINE 1 SO_LINGER 0 SO_REUSEADDR 1 NSOADMIN is defined as port number 2400 in the Services file. SERVERCLASS NSOADMIN INITIALIZATION PRIORITY: 140 CPU_LIST: (0,1) AVAILABLE_SERVERS 1 MAX_SERVERS 5 INITIALIZATION G_USERNAME SUPER.
Migrating Applications from NonStop ODBC Server Adding Aliases for System Catalog Object Names PROFILE_NAME NSOADMINPROF QST_MODE_ON N SQL_ACCESS_MODE RW SQL_CURSOR_MODE RW SQL_DIALECT TDM_CORE SQL_MAX_STATEMENT_CACHE 0 SQL_TXN_ISOLATION 1 SQL_UNSUPPORTED E OBJ_NAME_CACHE Y STMT_CACHE_LEVEL 1 CON_MODE_ON N CON_NAME CLOSE_TABLES_PER_SESSION N Adding Aliases for System Catalog Object Names To add NonStop ODBC Server system catalog object names to the SQL/MX environment, use MXCI to e
Migrating Applications from NonStop ODBC Server Adding Aliases for User Catalog Names CREATE SQLMP ALIAS MASTER.DBO.ZNSPROF \TEST1.$DATA02.SYSCAT.ZNSPROF; CREATE SQLMP ALIAS MASTER.DBO.ZNSPROT \TEST1.$DATA02.SYSCAT.ZNSPROT; CREATE SQLMP ALIAS MASTER.DBO.ZNSSCFG \TEST1.$DATA02.SYSCAT.ZNSSCFG; CREATE SQLMP ALIAS MASTER.DBO.ZNSSCS \TEST1.$DATA02.SYSCAT.ZNSSCS; CREATE SQLMP ALIAS MASTER.DBO.ZNSSER \TEST1.$DATA02.SYSCAT.ZNSSER; CREATE SQLMP ALIAS MASTER.DBO.ZNSSMAP \TEST1.$DATA02.SYSCAT.
Migrating Applications from NonStop ODBC Server Adding Aliases for User Catalog Names \TEST.$DATA07.SAMDBCAT.ZVUPROT; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.SYSTYPES \TEST.$DATA07.SAMDBCAT.ZVUDT; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.SYSUSERS \TEST.$DATA07.SAMDBCAT.ZVUUS; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.TABLES \TEST.$DATA07.SAMDBCAT.TABLES; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.TRANSIDS \TEST.$DATA07.SAMDBCAT.
Migrating Applications from NonStop ODBC Server Adding User Alias Names To Safeguard CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.VIEWCUST \TEST.$DATA07.INVENT.VIEWCUST; Adding User Alias Names To Safeguard To preserve the NonStop ODBC Server user alias names, map the following user alias and Guardian user names to Safeguard aliases: User Alias Guardian User Name Guardian User ID DBO SUPER.SUPER 255,255 DBAGRP_PERSNL DBAGRP.PERSNL 100,100 ODBCUSR1 SQLUSR.USER1 50,1 ODBCUSR2 SQLUSR.
Migrating Applications from NonStop ODBC Server Client Data Source Configuration Client Data Source Configuration On the client workstation, add a new data source and select the ODBC/MX driver name.
Migrating Applications from NonStop ODBC Server Client Data Source Configuration HP NonStop SQL/MX Connectivity Service Manual—524944-008 A-28
B Using Microsoft Access with ODBC/MX Microsoft Access applications interact in a special way with NonStop servers. To enable Microsoft Access behavior use the NSM/web SET property sheet tab or MXCI to set the environment variables SQL_ATTR_ACCESS_VERSION, and SQL_ATTR_MAP_DATATYPE for the data source to be used by Microsoft Access. If these environment variables are not set, you may receive errors when linking tables or retrieving data from tables.
Using Microsoft Access with ODBC/MX SQL_ATTR_ACCESS_MODE for Microsoft Access SQL_ATTR_MSACCESS_VERSION must be set to 2000 for this mapping to take effect. See Reconfiguring Server Data Sources on page 3-4 for details about using NSM/web to change settings. • Using MXCI in MXCS mode, the syntax is: CS> add evar .
Glossary association server. The HP NonStop MXCS component that starts and manages MXCS SQL servers and associates a client connection request with a specific MXCS SQL server in a data source. client application. An application that usesa connectivity API to access a HP NonStop SQL database. HP NonStop SQL/MX. The relational database system (RDMS) that is available on HP NonStop Kernel operating systems. configuration server. The MXCS component that manages the MXCS configuration data.
Glossary resource management policy limit resource management policy limit. The maximum value for a governing attribute. If the limit is exceeded, MXCS takes the governing action specified for the governing attribute. MXCS SQL server. The MXCS component that provides access to a NonStop SQL database through the NonStop SQL/MX engine. service. The MXCS components running in an HP NonStop system, including the association server, configuration server,, and MXCS SQL servers. SQL.
Index A access controls for objects 1-6 controls for operations 1-7 user authorization 1-5 Access, Microsoft B-1 adding statements for data sources 3-4 alias names, adding A-4 alternate EMS collector configuring for resource statistics 3-15 configuring for server trace 3-18 ANSI adding names 2-3 API functions, ODBC migrating to ODBC/MX A-10 unsupported 1-8 applications, migrating A-10 association server, functions of 1-1, 1-3 authentication, user 1-5 authorization for access to databases 1-5 C character se
Index E deleting server data sources 3-6 statements for data sources 3-4 disabling MXCS service 3-2 resource statistics 3-16 server traces 3-19 displaying resource statistics data 3-16 server trace data 3-20 installing MXCS on the server requirements 2-2 step 1, use DSM/SCM to install 2-3 step 2, add table names 2-3 step 3, start collector for statistics and server trace 2-3 step 4, start MXCS 2-4 IP address, client 1-3 L logging on 3-2 E enabling fileset 3-31 MXCS service 3-2 resource statistics 3-15
Index N Module File Caching (MFC) 3-27 benefits of 3-30 configuration of 3-27 design of 3-27 limitations of 3-29 managing 3-29 troubleshooting 3-29, 3-31 MXCS permissions 1-7, 3-2 overview 1-7 MXOAS command to start MXCS 2-4 MXOAS file 2-7 MXOCFG file 2-7 MXODSN file description 3-22 example 3-25 format 3-23 installed file list 2-7 MXOSRVR file 2-7 N name mapping for migration A-3 name types See object name types NonStop MXCS managing the service 3-2/3-3 starting 2-4 NonStop ODBC Server features not supp
Index T security Event Management Service (EMS) 1-7 permissions 3-2 SequeLink, support for 3-21 servers displaying status 3-3 stopping 3-3 server, MXCS components of 1-1 data sources for 1-2 description of 1-1 mapping to client data source names 1-5 uninstalling server components 2-7 service, MXCS disabling 3-2 displaying status 3-2 enabling 3-2 managing 3-2/3-3 SET statements, adding or deleting 3-4 SHORTANSI -vi shutting down MXCS 3-2 SMXOTMPL file 2-7 SQL server objects, migrating to ODBC/MX A-13 SQL s
Index V V viewing resource statistics data 3-16 server trace data 3-20 Z ZMXODBC file 2-7 ZMXOTMPL file 2-7 ZTEMPL file 2-7 HP NonStop SQL/MX Connectivity Service Manual—524944-008 Index-5
Index Z HP NonStop SQL/MX Connectivity Service Manual—524944-008 Index-6
Content Feedback First Name: __________________ Phone: _____________________ Company: ___________________ Last Name: _________________ e-mail address: ______________ (All contact information fields are required.) If you’re reporting an error or omission, is your issue: Minor: I can continue to work, but eventual resolution is requested. Major: I can continue to work, but prompt resolution is requested. Critical: I cannot continue to work without immediate response.
