HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.1 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 Published 640327-001 HP NonStop MXCS 3.0 February 2011 663856-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 for SQL/MX Release 3.1 Glossary Legal Notices Index Figures Tables 1 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 3. Starting, Configuring, and Managing MXCS Installation Requirements 2-2 Step 1: Install the MXCS Server Components 2-3 Step 2: Add Logical Table Names to SQL/MX 2-3 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 List of Installed Files 2-7 Server Files 2-7 Uninstalling MXCS from the Server 2-7 2-3 3.
A. Migrating Applications from NonStop ODBC Server Contents Limitations of MFC 3-35 Troubleshooting MFC 3-35 A.
Glossary Contents SQL_ATTR_MAP_DATATYPE for Microsoft Access SQL_ATTR_ACCESS_MODE for Microsoft Access B-1 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. Table 3-3. Table 3-4. Table 3-5. Table 3-6. Table 3-7. Table 3-8. Table A-1.
What’s New in This Manual Manual Information HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.1 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 HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.
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.x 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.
About This Manual Hypertext Links 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.
About This Manual Change Bar Notation 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.
Overview of MXCS Client Data Sources 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.
Overview of MXCS Operation Access Controls 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.x 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 for SQL/MX Release 3.
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-20. 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 NumberOfRows (ins/upd/del) Number of rows that were inserted, updated, or deleted. 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.
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 Session ID Unique session ID generated by the MXCS SQL server at the time the connection is established.
Starting, Configuring, and Managing MXCS Resource Statistics Table 3-8. Session Statistics - Session Summary Statistic Value TotalOdbcElapsedTime Total MXCS SQL server real time, in microseconds, from the time the connection started to the time the connection closed. TotalInsertStmtsExecuted Total number of SQL INSERT statements sent by the ODBC/MX driver to the MXCS SQL server. DOes not include internally generated SQL statements.
Starting, Configuring, and Managing MXCS 01-10-03 09:27:50 \ODBC.$Z1782 TANDEM.ODBCMX.G10 Statistics: Session ID: $SS:11:$Z1782:200110392729 Message Attribute: STATEMENT:SQLPrepare Sequence Number: 0 Message Info: StatementId:200110392749 EstimatedCost:0 StatementType:select SQLCompileTime:1106627 ErrorCode:0 01-10-03 09:27:29 \ODBC Z1782 TANDEM.ODBCMX.
Starting, Configuring, and Managing MXCS Param Caching For information on configuring statistics collection, see Configuring Statistics Collection on page 3-20 Param Caching Param Caching is an enhancement of resource statistics which allows logging SQL query parameter value as part of resource statistics information during the start of the SQL Execute operation. Configuring SQLValues To configure SQLValues: 1. Set SQLExecuteStat in Data Source to ON. 2.
Starting, Configuring, and Managing MXCS Param Caching F: Data value for the second parameter value The list of available data tags are: A: Printable ISO88591 B: Base64 encoded ISO88591 C: Base64 encoded UCS2 D: Date I: Interval N: Numeric K: Base64 encoded Kanji S: Base64 encoded KSC560 a: Truncated printable ISO88591 b: Truncated Base64 encoded ISO88591 c: Truncated Base64 encoded UCS2 k: Truncated Base64 encoded Kanji s: Truncated Base64 encoded KSC5601 Sample EMS Log for Select Query The following is
Starting, Configuring, and Managing MXCS Param Caching Message Attribute: STATEMENT:QueryParamValues Sequence Number: 0/0 Message Info: StatementId:SQL_CUR_MFC00000001 QueryId:MXID01223001061212175852220031727 000000000111SUPER.SUPER00_20_SQL_CUR_MFC00 000001 StatementType:SQL_SELECT_NON_UNIQUE SQLValues:{ZACOAAKabcdefghi CAcAGEA3wAwADEAMgAzADQANQA2ACA=AAHa123456 CAQADYANwA4AGEA3w==IAT01 12:23:34.987654 DAK1988-07-25NAE2333NAJ123456789NAM987654s, .
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 Sequence Number Message sequence number generated by MXCS. The number begins with zero (0) and is incremented by 1 with each new record. 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.
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-30.
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-27. See message Trace Event Message Format on page 3-31 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. Note. While upgrading the SQL/MX database from R 2.3.x to R 3.x, delete all the MFC files that were created at the default location or compiled_module_location. 1.
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 with SQL/MX 3.
Starting, Configuring, and Managing MXCS Troubleshooting MFC specified compiled_module_location directory after the binary module is successfully created. The *.lock files are not deleted for the queries that cannot create module files. For example, ODBC application queries using scalar functions, such as SUM or AVG or ABS cannot create module files. .mdf Files The .mdf temporary files are generated during preprocessing. These .mdf files are retained for easier support and troubleshooting.
Starting, Configuring, and Managing MXCS Troubleshooting MFC 1. At a TACL prompt, enter: SCF and then enter: assume $zpmon 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.
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.
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 ODBC_DATA02_CAT ZNUOBJ T_UNAME 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.
Resource Governing Migrating Applications from NonStop ODBC Server 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.
Configuring MXCS Data Sources Migrating Applications from NonStop ODBC Server 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.
Setting MXCS Service Attributes Migrating Applications from NonStop ODBC Server • • • • 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.
ODBC API calls Migrating Applications from NonStop ODBC Server 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.
ODBC API calls Migrating Applications from NonStop ODBC Server 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.
Modifying SQL Statements Migrating Applications from NonStop ODBC Server 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.
Modifying Pass-Through Commands Migrating Applications from NonStop ODBC Server SYSINDEXES SYSMESSAGES 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.
NonStop ODBC Server Migration Example Migrating Applications from NonStop ODBC Server 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.
System Catalog Names Migrating Applications from NonStop ODBC Server 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.
User Catalog Names Migrating Applications from NonStop ODBC Server 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.
User Catalog Names Migrating Applications from NonStop ODBC Server Database Name Owner Name 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.
User Catalog Names Migrating Applications from NonStop ODBC Server Database Name Owner Name 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.
Configuration Example for the NonStop ODBC Server Migrating Applications from NonStop ODBC Server Database Name 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.
Configuration Example for the NonStop ODBC Server Migrating Applications from NonStop ODBC Server EMIT_EVENTS N MEMORY_CHECK 0 SERVERCLASSES_CONFIGURED 1 SCS_NAME \TEST.$NSOAD JOB_ID 1 SCS_OBJECT \TEST.$DATA09.NOS7.
Configuration Example for the NonStop ODBC Server Migrating Applications from 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.
Adding Aliases for System Catalog Object Names Migrating Applications from NonStop ODBC Server 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.
Adding User Alias Names To Safeguard Migrating Applications from NonStop ODBC Server 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.
Client Data Source Configuration Migrating Applications from NonStop ODBC Server 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 for SQL/MX Release 3.
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.
resource management policy limit Glossary 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-20 configuring for server trace 3-28 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
E Index resource statistics 3-20 server traces 3-29 displaying resource statistics data 3-21 server trace data 3-30 E 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-36 MXCS service 3-2 resource statistics 3-20 server traces 3-28 enivronment variable SQL_ATTR_MAP_DATATYPE B-1 environment variable B-1 SQL_ATTR_ACCESS_MODE B-2 SQL_ATTR_ACCESS_VERSION B-1 error messages 1-8 Event Management Service (EMS) alterna
N Index 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-10 migration example A-15/A-27 NonStop ODBC/MX SQL functions not supported 1-9
T Index 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 -v shutting down MXCS 3-2 SMXOTMPL file 2-7 SQL server objects, migrating to ODBC/MX A-13 SQL statements, migrating to ODBC/MX A-13 SQL statements, statistics for 3-9 SQLStatistics B-1 SQL_ATTR_ACCESS_MODE B-2 SQL_
Special Characters Index ZMXOTMPL file 2-7 ZTEMPL file 2-7 Special Characters .err Files 3-35 HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.
Index Special Characters HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.
