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) 3.
Document History Part Number Product Version 640327-001 HP NonStop MXCS 3.
Legal Notices © Copyright 2011 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 vii HP Encourages Your Comments v xii 1.
Contents 2. Installing, Starting, and Uninstalling MXCS 2.
3. Starting, Configuring, and Managing MXCS (continued) Contents 3. Starting, Configuring, and Managing MXCS (continued) Benefits of MFC 3-32 Limitations of MFC 3-32 Troubleshooting MFC 3-32 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 • • Added the following topics: ° ° ° ° ° ° ° Session Statistics: Connection Information on page 3-20 Session Statistics: Session Summary Information on page 3-20 Statement Statistics: SQL Statement on page 3-21 Statement Statistics: SQLExecute on page 3-22 Statement Statistics: SQLExecDirect on page 3-22 Statement Statistics: SQLPrepare on page 3-23 Statement Statistics: SQLFetch on page 3-24 Modified the title “Format of Event Message 21034” to “Trace Event Message Format o
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 3.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 document can be accessed from the following documentation links available at the HP Business Support Center (BSC), http://www.hp.com/go/nonstop-docs: • • HP Integrity NonStop H-Series HP Integrity NonStop J-Series For more information about downloading online help, see the SQL/MX Installation and Management Guide.
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 • NSM/web: Manages and configures MXCS service. Requires an installed web server. NSM/web is not a server component because it does not run in the host. It is a client component. Figure 1-1.
Client Data Sources Overview of MXCS or MXCI in MXCS mode (MACL) to add, remove, configure, and monitor server data sources. See Managing MXCS Server Data Sources on page 3-3. 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.
Overview of MXCS Connecting a Client to a Data Source association server. ODBC Clients connect to MXCS by connecting via a client data source, which contains the connection-specific information. JDBC clients specify 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.
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.
MXCS 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 Migrating From a NonStop ODBC Server Environment You must migrate your NonStop ODBC Server applications if you plan to take advantage of the compiler and executor features of SQL/MX 3.0 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 and documented by Microsoft in the ODBC 3.
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 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. For systems running on J06.05 and later J-series RVUs or H06.
Installing, Starting, and Uninstalling MXCS Installation Requirements 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 J06.11 and subsequent J-series RVUs or H06.22 or subsequent H-series RVUs.
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 The above example indicates that the process $mxo starts from file $system.zmxodbc.
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 Starting the MXCS Service -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 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. NSODBC64 ZMXODBC File that contains the client 64-bit ODBC components for communicating with the server.
Installing, Starting, and Uninstalling MXCS Uninstalling MXCS from the Server HP NonStop SQL/MX Connectivity Service Manual—640327-001 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-4 Creating and Configuring Server Data Sources 3-4 Reconfiguring Server Data Sources 3-6 Starting MXCS Data Sources 3-7 Stopping MXCS Data Sources 3-7 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.
Starting, Configuring, and Managing MXCS Displaying Data Source Status Displaying Data Source Status 1. Using NSM/web, click MXCS Services. 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.
Configuring MXCS XA Data Source Starting, Configuring, and Managing MXCS OBJ_ID OBJ_TYPE OBJ_NAME LAST_UPDATED 1 1 TDM_Default_ODBCService ? 2 2 TDM_Default_DataSource ? 3 2 XADS 2007-01-24 11:05:26.846 563 The XADS data source is assigned the value ‘3’ in the above table. c. Set the following Control Query Default (CQD) to access and modify the ENVIRONMENTVALUES table: a. Control Query Default ODBC_METADATA_PROCESS 'ON'; b.
Reconfiguring Server Data Sources Starting, Configuring, and Managing MXCS Reconfiguring Server Data Sources You must use a user ID that has OPERATOR permission to perform management tasks. 1. Using NSM/web, click Data Source Configurations to expand the list of data sources. 2. Click on the data source you want to reconfigure. 3. Click the tab associated with the change you want to make.
Starting, Configuring, and Managing MXCS Starting MXCS Data Sources Reconfiguring Character Sets The default character set is determined at SQL/MX installation. ODBC applications may reconfigure the character set for a data source by prefixing character literals in an SQL statement. The text to be inserted into a Unicode column is prefixed with _UCS2 or N, with no spaces between the prefix and the character literal.
Starting, Configuring, and Managing MXCS Deleting MXCS Data Sources 4. Click a data source name. 5. Click Stop. 6. In the dialog box, enter a reason for stopping the data source. 7. Click the appropriate stop option. For more information about a screen, click Help. If the data source you stop is tracing server activity, tracing is automatically enabled when you restart the data source. However, if you stop the Association Server from TACL, tracing information is lost.
Starting, Configuring, and Managing MXCS Resource Statistics 3. Enter the new resource management policy or select an existing policy, then click the appropriate command button. Changes take effect immediately for an available server, and for a connected server when it goes to available state. Table 3-1.
Starting, Configuring, and Managing MXCS Resource Statistics the text of the SQL query for individual SQL statements. See Example resource statistics output for three selected options on page 3-15. Use NSM/web or MXCI to select these options for collecting resource statistics. See Starting Statistics Collection on page 3-17. Statement Statistics - Statements and Values SQL Statement Statement statistics are written to the alternate EMS collector at the time a Prepare statement is received. Table 3-2.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-3. Statement Statistics - SQLExecute Statistic Value ODBCExecutionTi me Total MXCS SQL server CPU process time, in microseconds, from the time the execute started to the time the response is sent back to the client with the results error code. NumberOfRows (ins/upd/del) Number of rows that were inserted, updated, or deleted. ErrorCode Execute error code, if any. A zero (0) indicates that the execute was successful.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-4. Statement Statistics - SQLExecDirect Statistic Value ErrorCode Execdirect error code, if any. A 0 (zero) indicates that the execdirect was successful. RowsAccessed Cumulative number of rows accessed. RowsRetrieved Cumulative number of rows retrieved. DiscReads Cumulative number of disk reads. MsgsToDisc Number of messages sent to the disk process.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-6. Statement Statistics - SQLFetch Statistic Value Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established. Message Attribute STATEMENT:SQLFetch/SQLClose (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-7. Session Statistics - Connection Information Statistic Value Component Name of the MXCS component to which the statistics apply. UserName User name logged into the session (the logical name used at the time of connection). UserID User ID that is logged into the session. ClientID Client workstation TCP/IP name or the NetBios/network name. Application ID Client application name or the main window caption.
Resource Statistics Starting, Configuring, and Managing MXCS Table 3-8. Session Statistics - Session Summary Statistic Value TotalUpdateStmtsExecuted Total number of SQL UPDATE statements sent by the ODBC/MX driver to the MXCS SQL server. Does not include internally generated SQL statements. TotalSelectStmtsExecuted Total number of SQL SELECT statements sent by the ODBC/MX driver to the MXCS SQL server. Does not include internally generated SQL statements.
Resource Statistics Starting, Configuring, and Managing MXCS EstimatedCost:0 StatementType:select SQLCompileTime:1106627 ErrorCode:0 01-10-03 09:27:29 \ODBC Z1782 TANDEM.ODBCMX.G10 Statistics: Session ID: $SS:11:$Z1782:200110392729 Message Attribute: SESSION:SessionSummary Sequence Number: 0 Message Info: StartTime:2001/10/3 9:27:29.45 EndTime:2001/10/3 9:47:36.
Starting, Configuring, and Managing MXCS Collecting Server Statistics Collecting Server Statistics Configuring Statistics Collection You must use a user ID that has OPERATOR permission to perform management tasks. 1. Configure an alternate collector, using the EMSACOLL command. For details on the EMSACOLL command, see the EMS Manual. 2. Start the alternate collector before starting the MX connectivity service. 3.
Starting, Configuring, and Managing MXCS Collecting Server Statistics Viewing Statistics The EMS alternate collector collects the MXCS Server statistics and returns them in event message 21035. See message Resource Statistics Event Message Format for the message format. Each statistics option returns a separate message for every qualified statement, for connection information, and for session summary. See Example resource statistics output for three selected options for an example of statistics returned.
Collecting Server Statistics Starting, Configuring, and Managing MXCS Resource Statistics Event Message Format Message 21035 Parameter Attribute MXCS Statistics Session ID: session-id Message Attribute: message-attribute Sequence Number:sequence-number Message Info:statistics-information messageattribute { { { { { { { session-id Unique session ID generated by the SQL server at the time the connection is established. messageattribute Name of the statistics option you selected.
Starting, Configuring, and Managing MXCS Collecting Server Statistics Session Statistics: Connection Information Session statistics are written to the alternate EMS collector at the time the connection is made (listed in the order written in the event message): Statistic Description Session ID Unique session ID generated by the MXCS server at the time the connection is established. Message Attribute SESSION:ConnectionInformation (the statistics option that was selected).
Starting, Configuring, and Managing MXCS Collecting Server Statistics TotalOdbcElapsedT ime Total MXCS SQL server real time, in microseconds, from the time the connection started to the time the connection closed. TotalInsertStmtsEx ecuted Total number of SQL INSERT statements sent by the HP ODBC driver to the MXCS SQL server; does not include internally generated SQL statements.
Starting, Configuring, and Managing MXCS Collecting Server Statistics Statement Statistics: SQLExecute SQLExecute statistics are written to the alternate EMS collector at the time an EXECUTE statement is received (listed in the order written in the event message): Statistic Description Session ID Unique session ID generated by the MXCS server at the time the connection is established. Message Attribute STATEMENT:SQLExecute (Statistics option that was selected.
Starting, Configuring, and Managing MXCS Collecting Server Statistics StatementID Unique statement ID generated by the MXCS SQL server at the time the PREPARE statement is received. EstimatedCost Estimated query cost returned by the SQL compiler. StatementType Valid types are: Insert, Delete, Update, Select. ODBCElapsedTime Total MXCS SQL server real time, in microseconds, from the time the execdirect started to the time the results are sent back.
Starting, Configuring, and Managing MXCS Tracing Server Activity StatementType Valid types are: Insert, Delete, Update, Select. SQLCompileTime Total MXCS SQL server real time, in microseconds, from the time the prepare started to the time it returns to the client with the compile results. ErrorCode Prepare error code, if any. A zero (0) indicates that the prepare was successful.
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-27.
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-24. See message Trace Event Message Format on page 3-28 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 Tracing Server Activity Trace Event Message Format Message 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 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 and ODBC applications that use complex queries result in lower processor utilization, lower memory consumption, and better response time. Limitations of MFC • • • It does not handle session-specific SQL/MX Control Query Defaults (CQD) and SQL/MX Control Query Shape (CQS). For light weight queries, MFC performs only marginally better than SQL/MX compile. MFC is not supported for rowset operations.
Starting, Configuring, and Managing MXCS Troubleshooting MFC 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 .lst files are the output of the SQL/MX C/C++ preprocessor. In rare cases, these files might contain compiler errors. .sql Files The .
Starting, Configuring, and Managing MXCS Troubleshooting MFC 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. Verify the status of the fileset: info fileset mxc1,detail 5.
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—640327-001 3- 38 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—640327-001 A-28
B Using Microsoft Access with ODBC/MX Microsoft Access applications interact in a special way with NonStop servers. To enable Microsoft Access, 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-6 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 operations 1-7 user authorization 1-6 Access, Microsoft B-1 adding statements for data sources 3-6 alias names, adding A-4 alternate EMS collector configuring for resource statistics 3-17 configuring for server trace 3-25 ANSI adding names 2-3 API functions, ODBC migrating to ODBC/MX A-11 unsupported 1-9 applications, migrating A-10 association server, functions of 1-1, 1-3 authentication, user 1-6 authorization for access to databases 1-6 C character sets, reconfiguring 3-7 cli
Index E disabling MXCS service 3-2 resource statistics 3-17 server traces 3-26 displaying resource statistics data 3-18 server trace data 3-27 E installing MXCS on the server (continued) step 2, add table names 2-3 step 3, start collector for statistics and server trace 2-3 step 4, start MXCS 2-3 IP address, client 1-3 L logging on 3-2 enabling fileset 3-33 MXCS service 3-2 resource statistics 3-17 server traces 3-25 enivronment variable SQL_ATTR_MAP_DATATYPE B-1 environment variable B-1 SQL_ATTR_ACCE
Index N Module File Caching (MFC) (continued) managing 3-31 troubleshooting 3-32, 3-33 MXCS features of 1-9 limitation of 1-9 MXCS permissions 1-8, 3-2 overview 1-7 MXOAS command to start MXCS 2-4 MXOAS file 2-7 MXOCFG file 2-7 MXODSN file 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-3 NonStop ODBC Server features not supported A-14 migrating client side A-10/A-14 migrating server side A-1/A
Index S S Safeguard requirement 2-2 security Event Management Service (EMS) 1-8 permissions 3-2 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-6 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-18 server trace data 3-27 Z ZMXODBC file 2-7 ZMXOTMPL file 2-7 ZTEMPL file 2-7 HP NonStop SQL/MX Connectivity Service Manual—640327-001 Index-5
Index Z HP NonStop SQL/MX Connectivity Service Manual—640327-001 Index-6
