HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.2.1 HP Part Number: 663856-004 Published: August 2013 Edition: J06.15 and all subsequent J-series RVUs and H06.
© Copyright 2013 Hewlett-Packard Development Company, L.P. Legal Notice 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.
Contents About this document......................................................................................6 Supported Release Version Updates (RVUs)..................................................................................6 Audience.................................................................................................................................6 New and Changed Information in This Edition..............................................................................
Stopping Servers................................................................................................................27 Managing Multiple MXCS Services......................................................................................27 Managing MXCS Server Data Sources.................................................................................27 Managing data sources..........................................................................................................
Server Characteristics.........................................................................................................66 System Catalog Names......................................................................................................66 User Catalog Names..........................................................................................................68 Configuration Example for the NonStop ODBC Server............................................................
About this document 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. Supported Release Version Updates (RVUs) This manual supports J06.15 and all subsequent J-series RVUs and H06.
lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name computer type. Computer type letters within text indicate C and Open System Services (OSS) keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.c italic computer type.
"[" repetition-constant-list "]" Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: $process-name.#su-name Line Spacing.
ODBC/MX Driver for Windows Describes how to install and configure HP NonStop ODBC/MX for Microsoft Windows, which enables applications developed with ODBC API to use NonStop SQL/MX. SQL/MX Remote Conversational Interface (RMXCI) Guide Describes how to use SQL/MX Remote Conversational Interface to run the RMXCI commands, and SQL statements interactively or from script files. HP NonStop MXDM User Guide Describes how to use the NonStop SQL/MX Database Manager (MXDM) to for SQL/MX Release 3.2.
NSM/web Help Context-sensitive help topics that describe how to use the NSM/web management tool. Visual Query Planner Help Context-sensitive help topics that describe how to use the Visual Query Planner graphical user interface. The NSM/web, SQL/MX Database Manager, and Visual Query Planner help systems are accessible from their respective applications. You can download the Reference, Messages, and Glossary online help from the HP Software Depot, at http://www.software.hp.com.
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 a 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.
Figure 1 MXCS Architecture - Interactions Among Three Components Data Sources A datasource is a logical grouping of MXOSRVRs. The data source definitions include the basic configuration performed on the data source. MXCS requires data source definitions on the NonStop server, and for ODBC clients on the client workstation. Server Data Sources Server data sources reside on a NonStop server. Each server data source represents a pool of SQL MXCS servers that share the same NonStop SQL context.
Client Data Sources Client data sources are used for ODBC/MX connections and reside on a client workstation. Each client data source is a logical name that identifies the attribute values used to connect to the MXCS service and defines some local behavior for the connection. Use the Microsoft ODBC Data Source Administrator to add, configure, and remove ODBC/MX client data sources.
Figure 2 Connection Data Flow Connection Mapping MXCS maps the client data source name in an incoming connection request to the server data source name (names are case sensitive). If the name matches but the server data source is not started, the connection request is rejected. If the server data source name does not exist, the association server routes the connection to the default data source. If the default data source is not started, the connection request is rejected.
User Authentication MXCS uses a connection model in which users request a connection to a named service, known as the data source. To complete the connection, the user provides a user name and a password, which are passed in a secure, private packet to the MXCS server. Two formats for the username are valid: group.
Controlling who can manage the MXCS service MXCS users have access permissions based on the security values for their Guardian user ID. PUBLIC has USER permission and the super ID has OPERATOR permission by default. Permissions can be assigned and changed using NSM/web only by the super ID. All other users can view permissions but not assign or change them. There are two permissions: MXCS Permission Access USER View status and configuration of MXCS services, data sources, and server. View permissions.
See the MXCS online help that is available on NSM/web for detailed descriptions of the statistics collected. For information on configuring statistics collection, see “Configuring Statistics Collection” (page 40). MXCS Features MXCS supports the following features: • Row size limit is increased to align with maximum block size 32768 for MX tables. • A clustering key length up to 2048 bytes is allowed for range and hash partitioned MX tables.
2 Installing, Starting, and Uninstalling MXCS The chapter discusses the following topics: “Components of the MX connectivity service” (page 18) “Starting an MXCS Service manually” (page 20) “List of Installed Files” (page 23) “Migrating from SQL/MX Release 2.3.4 to SQL/MX Release 3.
Requirement Description In addition, the root fileset must be already added and mounted, as required by SQL/MX. For more information, see the Open System Services Installation Guide. Safeguard (optional) Safeguard must be installed if you want to preserve the NonStop ODBC Server user id names or use name aliases. This product maps the Guardian user IDs to Safeguard alias names. SQL/MP To use SQL/MP tables, SQL/MP must be installed and initialized.
$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.mxoas and uses the port number 35000. Your installation might require different parameters or to configure a different set of CPUs. For more information, see the SCF Manual for the Kernel Subsystem. Starting an MXCS Service manually NOTE: Starting with H06.27 or J06.
specifies the name of the EMS collector to be used for event messages generated by MXCS; the default is $0. -PN port number specifies the starting port number (in decimal) for connections to the MXCS association server; the default is 18650. This port number is used as the single point of contact for all ODBC applications connecting to that IP address; internally MXCS manages the range of port numbers defined by -PR to ensure that each server has a separate port within the port range.
-SSLPWD password specifies the password for the encrypted server key. This option is mandatory when starting MXOAS in SSL Encryption mode. password value is not encrypted. -SSLTRACE trace-level indicates that SSL calls must be traced. The trace-level specifies the granularity for tracing the SSL calls. Value range is 0–9999. -CACERT filename[,filename,filename] specifies the CA certificates for signing. The value is a list of comma separated filenames.
Component Name: ODBC/MX Association Server Object Reference: The port range limits the number of mxosrvrs that can be spawned from all started datasources. This also includes two ports reserved for MXOAS and MXOCFG. HP recommends you to maintain the default configuration of 250.
File Name ISV Description SMXOTMPL ZTEMPL MXCS event messages. T0611PAX ZOSSUTL SQL/MX and MXCS module files Migrating from SQL/MX Release 2.3.4 to SQL/MX Release 3.2 NOTE: The same procedure is applicable when you migrate from SQL/MX 2.3.4 to 3.2.1. The format of the CPU lists stored in the ODBC metadata, in SQL/MX Release 2.3.x is different from the format of the CPU lists stored in SQL/MX Release 3.2. The SQL/MX Release 2.3.x CPU list format is not recognized in SQL/MX Release 3.2.
Executing the following command results in the CPU list 12 and 14, which is wrong if the CPU list is in SQL/MX Release 3.2 format: mxtool fixmxcsmd -v 3 However, executing the following command results in the CPU list value 32, which is wrong if the CPU list is in SQL/MX Release 2.3.x format: mxtool fixmxcsmd -v 2 HP recommends that prior to converting the CPU list format, you test the option using the -d flag to view the conversions.
3 Starting, Configuring, and Managing MXCS “Managing MXCS Services” (page 26) • “Logging on” (page 26) • “Displaying the MXCS Service Status” (page 26) • “Enabling the MXCS Service” (page 27) • “Disabling the MXCS Service” (page 27) • “Stopping Servers” (page 27) • “Managing Multiple MXCS Services” (page 27) “Managing data sources” (page 28) • “Displaying Data Source Status” (page 28) • “Creating and Configuring Server Data Sources” (page 28) • “Reconfiguring Server Data Sources” (page 29)
Enabling the MXCS Service To start the MXCS service, execute the MXOAS command at the TACL prompt (see “Starting an MXCS Service manually” (page 20)). Once the service is started, you can disable and enable it using NSM/web. Requirement: you must use a user ID with OPERATOR permission to perform management tasks. 1. Using NSM/web, expand MXCS Services. 2. Right-click the service name you want to start. 3. Click Start. For more information about a screen, click Help.
matching data source is not available on the server, client applications use the default server-side data source, TDM_Default_DataSource, to connect to the database. In this case, default values cannot be changed using NSM/web. For information on managing data sources using MACL, see the SQL/MX Connectivity Service Administrative Command Reference manual. Managing data sources Only user ID’s with MXCS OPERATOR permission can perform MXCS management tasks.
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: 1) Control Query Default ODBC_METADATA_PROCESS 'ON'; 2) Insert the XA definition of the XADS data source that is created in Step 1.
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.
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. In this case, use NSM/web to enable tracing again. If the data source you stop has statistics collection started, the statistics options are retained. When you restart the data source statistics collection will automatically resume.
Table 1 Attributes and Values for Resource Management Policies (continued) LOG - The user query continues execution. LOG_WITH_INFO - The user query returns a warning message to the client. The query execution continues. STOP - The user query returns an error message to the client. The query execution stops. Limit The maximum value for the attribute. This value is any positive number. The maximum is the value of LARGEINT SQL data type.
Table 2 Statement Statistics - SQL Statement (continued) Statistic Value StatementID Unique statement ID generated by the MXCS SQL server at the time the Prepare command is received. SQL Text Text of the SQL statement. This field can be distributed in multiple messages. SQLExecute SQLExecute statistics are written to the alternate EMS collector at the time an Execute statement is received.
Table 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. StatementID Unique statement ID generated by the MXCS SQL server at the time the Prepare command is received.
Table 5 Statement Statistics - SQLPrepare (continued) Statistic Value EstimatedCost Estimated query cost. StatementType Valid types are: Insert, Delete, Update, Select. SQLCompileTime Total MXCS SQL server real time, in microseconds, from the time the prepare starts 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.
Table 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. Message Attribute SESSION:ConnectionInformation (Statistics option that was selected) SESSION:ConnectionInformation Authentication failed (for unsuccessful connections due to incorrect password) SESSION:ConnectionInformation Authentication failed; password expired (for unsuccessful connections due to password expiry).
Table 8 Session Statistics - Session Summary (continued) Statistic Value TotalDeleteStmtsExecuted Total number of SQL DELETE statements sent by the ODBC/MX driver to the MXCS SQL server. Does not include internally generated SQL statements. 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.
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. Note that no detailed statistics are given for that statement - just the query itself, and the time of execution. • SQLPrepare — The second message contains information for the SQLPrepare option and shows that an SQL SELECT statement was prepared at 9:27.
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 a sample EMS log for SQL SELECT Query: select * from ALLDataType where c1=?,c2=?,c3=?,c4=?,c5=?,c6=? ,c7=?,c8=?,and c9=? All "?" values a
• Logs empty string SQLValues:{} when the total length of the query parameter message exceeds 3.4 KB. The message length of three bytes reserves a byte for data tag and other two bytes for parameter length. The total length of the query parameter message is calculated for 'n' number of parameter value with ni parameter length as: If (n1 + 3) + (n2 + 3) + (n3 + 3) + (n4 + 3)... (Nn + 3) > 3400 , then SQLValues:{} is logged. 11-08-19 17:16:31 \LLSW2.$Y0NN TANDEM.ODBCMX.
4. 5. Clear statistics options. Click Apply. For more information about a screen, click Help. 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” (page 41) for the message format. Each statistics option returns a separate message for every qualified statement, for connection information, and for session summary.
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). Sequence Number Message sequence number generated by MXCS.
Statistic Description TotalDeleteStmtsEx ecuted Total number of SQL DELETE statements sent by the HP ODBC driver to the MXCS SQL server; does not include internally generated SQL statements. TotalUpdateStmtsExecuted Total number of SQL UPDATE statements sent by the HP ODBC driver to the MXCS SQL server; does not include internally generated SQL statements.
Statistic Description StatementID Unique statement ID generated by the MXCS SQL server at the time the PREPARE statement is received. ODBCElapsedTime Total MXCS SQL server real time, in microseconds, from the time the execute started to the time the results are sent back. ODBCExecutionTime 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.
Statistic Description RowsRetrieved Cumulative number of rows retrieved. DiscReads Cumulative number of disk reads. MsgsToDisc Number of messages sent to the disk process. MsgsBytesToDisc Size, in bytes, of the total number of messages sent to the disk process. LockWaits Number of lock waits (this is the concurrency cost). LockEscalation Cumulative number of times record locks are escalated to file locks.
Statistic Description MsgsBytesToDisc Size, in bytes, of the total number of messages sent to the disk process. LockWaits Number of lock waits (this is the concurrency cost). LockEscalation Cumulative number of times record locks are escalated to file locks. TotalOdbcElapsedTime Total MXCS SQL server real time, in microseconds, from the time the fetch was prepared to the time it was closed.
For more information about a screen, click Help. 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. 3. 4. Click the Data Source Status tab. Clear the Tracing column for the data sources you want to stop tracing.
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.
For information on the ODBC/MX driver support for MFC, see the HP NonStop ODBC/MX Driver for Windows for SQL/MX Release 3.2.1. Design of MFC MFC shares static module files created from dynamic queries using SQL/MX tools, across MXOSRVR or SQL/MX connections. MFC uses disk files on the NonStop Open System Services file system for storing the compiled SQL/MX prepared statement plans. When the application uses prepareexecute or execdirect API, MFC checks for an existing plan that matches the query.
If the ‘default’ value is specified then, compiled_module_location directory that is set would be /usr/tandem/sqlmx/TEMPMODULES The directory location specified in the compiled_module_location attribute will include the following files: a. The compiled module plans. b. Temporary files such as *.lst,*.sql*.mdf ; all these files are retained in the directory to enable easy troubleshooting.
d. Click OK. 6. To add the compiled module location: a. Click +. b. In the Name field, enter compiled_module_location. c. In the Value field, enter default or any other location that you want. d. Click OK. 7. 8. Click Apply. Stop and start the data source. Managing MFC Every application must have its own module file directory so that all the modules for an application are grouped together. OSS commands can be used to remove temporary files and modules when required.
Troubleshooting MFC The troubleshooting of MFC includes: • “.err Files” (page 52) • “.lock Files” (page 52) • “.mdf Files” (page 52) • “.lst Files” (page 52) • “.sql Files” (page 52) • “Disk Activity” (page 53) • “Enable Fileset and OSS Caching” (page 53) • “Known Issues” (page 55) .err Files The *.err files are intermediate files available at a location specified in COMPILED_MODULE_LOCATION EVAR, which reports MXCMP errors, if any, generated during module file creation.
# include EXEC SQL MODULE "MFCABS1031CAT"."SCH".
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.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.
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( Success info int); 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.fetch() Success Success Data in the table testing is retrieved.
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.
The following table lists the relationships between the database object names: NonStop ODBC Server 3-part database name based on SQL Corresponding SQL/MX 3-part database name based on Server implementation ANSI SQL 92 standard Database name Catalog Owner Schema Object name Object name In the NonStop ODBC Server, a database name is in the form: system-name_volume-name_subvolume-name The system_name is the name of the NonStop system without the backslash (\).
Figure 3 Mapping NonStop ODBC Server Name Components to ODBC/MX CREATE SQLMP ALIAS Command CREATE SQLMP ALIAS catalog_name.schema_name.
select 'SELECT ''CREATE SQLMP ALIAS '' || T_DBNAME || ''.'' ' || '|| T_UNAME || ''.'' || T_OBJNAME || '' '' || ' || 'N_OBJNAME || '';'' ' || 'from \TEST.$data02.startup.znsdb, ' || trim(N_CATALOG) || '.ZNUOBJ WHERE ' || 'N_CATALOG = ''' || N_CATALOG || ''';' from \TEST.$data02.startup.znsdb; where the ZNSDB table is in the system catalog and contains a list of NonStop ODBC Server databases, and \TEST.$DATA02.STARTUP is the catalog you are migrating to SQL/MX.
NonStop ODBC Server Configuration MXCS Configuration Network configuration The configuration parameters are provided as part of the command line. Map server class names to SCS names Not Applicable. All data source names (and configuration values) are shared between all ODBC/MX service instances in the same system. Map user name aliases to SCS and server class names Not Applicable To configure the MXCS environment, use NSM/web or MXCI.
NonStop ODBC Server Class Attributes Corresponding MXCS Data Source Attributes Max servers Maximum servers Initial heap size Not applicable Maximum heap size Not applicable Idle delete delay timeout Connection idle timeout NOS create options Environment variables NOS debug options Diagnostic information and resource management options provide an alternative debug method. NOS run options Run options are the same as specified for the association server.
Setting MXCS Service Attributes The MXCS service is started differently from NonStop ODBC Server, which uses a configured value for SCS. For detailed information on starting MXCS, see “Starting an MXCS Service manually” (page 20). Server: Preserving User Alias Settings MXCS uses Safeguard aliases or Guardian user names; it does not use NonStop ODBC Server user alias definitions.
You must modify: • SQL statements (to comply with SQL/MX syntax) • NonStop ODBC Server-specific syntax commands 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.
Table 9 ODBC API Mapping Between 2.x and 3.x Versions (continued) 64 ODBC 2.x Calls* ODBC 3.x Calls* 3.x Calls Supported by ODBC/MX? 2.
Table 9 ODBC API Mapping Between 2.x and 3.x Versions (continued) ODBC 2.x Calls* ODBC 3.x Calls* 3.x Calls Supported by ODBC/MX? 2.x Calls Supported by ODBC Server? SQLSetScrollOption SQLSetScrollOption N N SQLSetStmtOption SQLSetStmtAttr Y Y SQLSpecialColumns SQLSpecialColumns Y Y SQLStatistics SQLStatistics Y Y SQLTablePrivileges SQLTablePrivileges Y N SQLTables SQLTables Y Y SQLTransact SQLEndTran Y Y * Function names in italics are converted between both versions.
NonStop ODBC Server Pass-Through Commands Column HeadMXCS Equivalents table logical names using the DROP SQLMP ALIAS command. SET commands for setting environment variables If the SQL/MX language supports the commands, they can be sent directly. CONTROL statements defined in SQL/MX can also be sent (no special syntax is required). Features Not Supported The following NonStop ODBC Server features are not supported: • DBLib messages using the TDS transport.
Database Name Owner Name Object Name Guardian Name MASTER DBO SYSINDEXES \TEST.$DATA02.SYSCAT.ZVUIX MASTER DBO SYSMESSAGES \TEST.$DATA02.SYSCAT.ZVSMSG MASTER DBO SYSOBJECTS \TEST.$DATA02.SYSCAT.ZVUOBJ MASTER DBO SYSPROTECTS \TEST.$DATA02.SYSCAT.ZVUPROT 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.
Database Name Owner Name Object Name Guardian Name MASTER DBO ZONAM2ID \TEST.$DATA02.SYSCAT.ZONAM2ID MASTER DBO ZORES \TEST.$DATA02.SYSCAT.ZORES MASTER DBO ZTEST \TEST.$DATA02.SYSCAT.ZTEST Database Name Owner Name Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL BASETABS \TEST.$DATA07. SAMDBCAT. BASETABS TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL COLUMNS \TEST.$DATA07. SAMDBCAT. COLUMNS TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL COMMENTS \TEST.$DATA07. SAMDBCAT.
Database Name Owner Name Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZNUDT \TEST.$DATA07. SAMDBCAT. ZNUDT TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL ZNUIX \TEST.$DATA07. SAMDBCAT. ZNUIX 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.
Database Name Owner Name Object Name Guardian Name TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL VIEWCS \TEST.$DATA07. INVENT.VIEWCS TEST_DATA07_SAMDBCAT DBAGRP_ PRSNL VIEWCUST \TEST.$DATA07. INVENT. VIEWCUST Configuration Example for the NonStop ODBC Server config $nsoad NSODBC_SYSTEM_CATALOG \TEST.$DATA02.SYSCAT CHECK_INTERVAL_SECS 600 NOS_OBJECT \TEST.$DATA09.NOS7.NOS NOS_LIBRARY_FILE NOSUTIL_OBJECT \TEST.$DATA09.NOS7.
SCS_NAME \TEST.$NSOAD OUT_BUFFER_SIZE_B 2448 DEFAULT_VOLUME SCS_DEBUG_OPTIONS SCS_RUN_OPTIONS NOS_OBJECT \TEST.$DATA09.NOS7.NOS NOSUTIL_LIBRARY_FILE NOSUTIL_PRIORITY 140 NOSUTIL_CPU NOSUTIL_CREATE_OPTIONS NOSUTIL_DEBUG_OPTIONS NOSUTIL_RUN_OPTIONS NET_NAME NSOADMIN NET_PROTOCOL TCP/IP SERVICES_FILENAME 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.
PROFILE_NAME NSOADMINPROF DEFAULT_SECURITY NNNU TRA_MODE_ON TRA_NAME ACC_MODE_ON N ACC_LOGTABLE_NAME ACC_LEVEL SESSION GOV_MODE_ON N GOV_NAME 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
CREATE SQLMP ALIAS MASTER.DBO.ZNSCON \TEST1.$DATA02.SYSCAT.ZNSCON; CREATE SQLMP ALIAS MASTER.DBO.ZNSDB \TEST1.$DATA02.SYSCAT.ZNSDB; CREATE SQLMP ALIAS MASTER.DBO.ZNSDEF \TEST1.$DATA02.SYSCAT.ZNSDEF; CREATE SQLMP ALIAS MASTER.DBO.ZNSDUMMY \TEST1.$DATA02.SYSCAT.ZNSDUMMY; CREATE SQLMP ALIAS MASTER.DBO.ZNSGOV \TEST1.$DATA02.SYSCAT.ZNSGOV; CREATE SQLMP ALIAS MASTER.DBO.ZNSMSG \TEST1.$DATA02.SYSCAT.ZNSMSG; CREATE SQLMP ALIAS MASTER.DBO.ZNSNET \TEST1.$DATA02.SYSCAT.ZNSNET; CREATE SQLMP ALIAS MASTER.DBO.
\TEST.$DATA07.SAMDBCAT.ZVUCOL; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.SYSINDEXES \TEST.$DATA07.SAMDBCAT.ZVUIX; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.SYSOBJECTS \TEST.$DATA07.SAMDBCAT.ZVUOBJ; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.SYSPROTECTS \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.
CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.VIEW207 \TEST.$DATA07.INVENT.VIEW207; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.VIEW207N \TEST.$DATA07.INVENT.VIEW207N; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.VIEWCS \TEST.$DATA07.INVENT.VIEWCS; CREATE SQLMP ALIAS TEST_DATA07_SAMDBCAT.DBAGRP_PRSNL.VIEWCUST \TEST.$DATA07.INVENT.
Client Data Source Configuration On the client workstation, add a new data source and select the ODBC/MX driver name.
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.
SQL_ATTR_ACCESS_MODE for Microsoft Access You can set access mode on the server by defining this environment variable. If you are using the SQL_ATTR_MSACCESS_VERSION variable, you do not have to set this variable. • To set the attribute: ◦ In the NSM/web SET tab, the settings are SQL_MODE_READ_WRITE or SQL_MODE_READ_ONLY. See “Reconfiguring Server Data Sources” (page 29) for details about using NSM/web to change settings.
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 uses a connectivity API to access a HP NonStop SQL database. configuration server. The MXCS component that manages the MXCS configuration data. The association server communicates with the configuration server to retrieve the MXCS configuration values. data source.
Index Symbols .
I installing overview of, 18 installing MXCS on the server requirements, 18 step 1, use DSM/SCM to install, 19 step 2, add table names, 31 step 3, start collector for statistics and server trace, 19 step 4, start MXCS, 19 IP address, client, 13 L logging on, 26 M Managing, 26 managing MXCS service, 26, 31 server data sources, 27, 31 mapping names for migration, 57 messages, 16 Microsoft Access, 77 Microsoft ODBC Data Source Administrator, 13 migrating functions, 65 Migrating NonStop ODBC Server applicatio
displaying servers, 28 displaying service, 26 stopping resource statistics, 40 server data sources, 30 server trace, 47 servers, 27 T table names case sensitive, 30 table names, adding, 31 TCP/IP requirement, 18 TDM_Default_DataSource, 12 TDMNSM file, 23 TDMODBC fileDefault Data Source, 23 terminating MXCS, 27 TFDS requirement, 18 tracing data source servers configuring, 46 example trace data, 16, 47 overview, 16 starting, 46 stopping, 47 viewing, 47 U uninstalling server components, 25 user authenticatio
