HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.2 HP Part Number: 663856-002 Published: August 2012 Edition: J06.14 and all subsequent J-series RVUs and H06.25 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
© Copyright 2012 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......................................................................................7 Supported Release Version Updates (RVUs)..................................................................................7 Audience.................................................................................................................................7 New and Changed Information in This Edition..............................................................................
Enabling the MXCS Service.................................................................................................27 Disabling the MXCS Service................................................................................................27 Displaying Server Status.....................................................................................................27 Stopping Servers................................................................................................................
Module File Caching (MFC).....................................................................................................48 Design of MFC..................................................................................................................49 Configuring MFC from the MXCS mode................................................................................49 Configuring MFC using NSM/Web.....................................................................................50 Managing MFC.......
SQL_ATTR_ACCESS_MODE for Microsoft Access........................................................................78 Glossary....................................................................................................79 Index.........................................................................................................
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.14 and all subsequent J-series RVUs and H06.
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.Italic computer type letters within text indicate C and Open System Services (OSS) variable items that you supply. Items not enclosed in brackets are required. For example: pathname [ ] Brackets. Brackets enclose optional syntax items.
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.
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.
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. Connections Connecting a Client to a Data Source Figure 2 (page 15) shows the flow of data among the client and server components. The MXCS association server starts and manages the MXCS SQL servers associated with MXCS data sources in the NonStop system.
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.
group.user or safeguard-aliasname Because many client applications use only one-part user identification (such as on Windows and UNIX systems), you might need to add Safeguard aliases and use the safeguard-aliasname format when connecting to MXCS. For more information about user authentication, see the Safeguard User’s Guide. Passwords Safeguard supports multiple alias and password pairs for a single Guardian username. This can improve performance because object caching reuses the underlying username.
There are two permissions: 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 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. • SQL/MX connectivity supports the extended NUMERIC precision data type. The precision of this data type is extended up to 128 digits for signed and unsigned values.
2 Installing, Starting, and Uninstalling MXCS The chapter discusses the following topics: “Components of the MX connectivity service” (page 19) “Starting an MXCS Service manually” (page 21) “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.
-PR port-range specifies the number of ports that can be used simultaneously for connections to the MXCS association server (including the starting port number). The minimum requirement is 3 port numbers; the default is 250. The port range specified must not conflict with port numbers used by other applications. -I specifies the interactive startup mode for the association server.
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. MXODSN ZMXODBC Template file that contains data source options for MXCS connectivity OSS ODBC/MX Driver for NonStop SQL/MX.
NOTE: The fixmxcsmd option also supports the conversion of the SQL/MX Release 3.2 CPU list format to the SQL/MX Release 2.3.x CPU list format in the ENVIRONMENTVALUES MXCS metadata table. To convert the SQL/MX Release 3.2 CPU list format to the SQL/MX Release 2.3.x CPU list format, you must run the mxtool command with the fixmxcsmd option before cold loading with SQL/MX Release 2.3.x objects.
Data source YOUR_DS, translate '14,15' -> '3' ... The previous examples highlight the difference in output when you use the -d flag. Creating the default catalog and schema for MXCS After upgrading to SQL/MX Release 3.2, you must create the default catalog and schema for MXCS. The default catalog is NONSTOP_SYSTEM_NSK. The default schema is PUBLIC_ACCESS_SCHEMA. To create the default catalog and schema, run the following script located at /usr/tandem/ sqlmx/bin: .
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 21)). 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.
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 a plan exists, it is loaded from the disk thereby eliminating the requirement of SQL compilation of the query.
NOTE: 6. • 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. • While upgrading the SQL/MX Release 2.3.x to SQL/MX Release 3.2, delete all the MFC files that were created at the default location or compiled_module_location. To verify that the values are assigned, enter the following command: CS>info evar .
The mgscript is located at the Guardian location, where the MXOSRVR objects are present. The following options can be used with the MFC management script: This option deletes all MFC related files from the specified directory. For example: -A >>mgscript -A
Dir : This must be the directory mentioned in the compiled_module_location This option deletes all MFC related binary files from the specified directory related to a specific table. For example: -T >>mgscript –T .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. If MXCMP passes successfully, the *.err files are removed automatically after the module file is created. Otherwise, the *.err files are stored at the specified location for further analysis of the reported failure. .lock Files The *.
return 0; } Starting with SQL/MX Release 3.2, MFC supports parameterized queries with BIGNUM data type. The following is a sample .sql file: EXEC SQL MODULE "MFCABS1031CAT"."SCH".
4. Verify the status of the fileset: info fileset mxc1,detail 5. Start the fileset: start fileset mxc1 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.
Operation Expected Result Actual Result Remarks Stmt1 = Prepare(" select * from testing") Success Success Compiled plan is retrieved from MFC. Stmt1.execute() Success Success MFC statement works as expected. Stmt1.executeUpdate(" Success drop table testing") Success The table testing is dropped. Stmt1.executeUpdate(" Success create table testing (mycol varchar( 10))") Success The table testing is created with varchar column.
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 21). 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, 19 installing MXCS on the server requirements, 19 step 1, use DSM/SCM to install, 20 step 2, add table names, 31 step 3, start collector for statistics and server trace, 20 step 4, start MXCS, 20 IP address, client , 14 L logging on, 26 M Managing, 26 managing MXCS service, 26, 31 server data sources, 27, 31 mapping names for migration, 57 messages, 17 Microsoft Access, 77 Microsoft ODBC Data Source Administrator, 13 migrating functions, 63 Migrating NonStop ODBC Server applicati
displaying server data sources, 28 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, 19 TDM_Default_DataSource, 13 TDMNSM file, 23 TDMODBC fileDefault Data Source, 23 terminating MXCS, 27 TFDS requirement, 19 tracing data source servers configuring, 46 example trace data, 17, 47 overview, 17 starting, 46 stopping, 47 viewing, 47 U uninstalling serve
