HP NonStop SQL/MX Release 3.2 Installation and Upgrade Guide HP Part Number: 691118-003 Published: May 2013 Edition: J06.14 and subsequent J-series RVUs; 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 manual.........................................................................................6 Supported release version updates (RVUs)....................................................................................6 Audience.................................................................................................................................6 Related documentation.............................................................................................................
5 Considerations for upgrading the system to SQL/MX Release 3.2...................33 System upgrade path..............................................................................................................33 Compatibility of SQL/MX Release 3.2 systems...........................................................................33 Considerations for mixed-node network.....................................................................................
Version identification of SQL/MX software components...........................................................63 SQL/MX system software version (MXV)................................................................................64 SQL/MX database object versions.......................................................................................64 SQL/MX query plan and module versions.............................................................................65 Interoperability across schema versions..
About this manual This manual explains how to install SQL/MX Release 3.2 on an HP NonStop server, upgrade metadata, and migrate applications from SQL/MX Release 2.3.x, SQL/MX Release 3.0, SQL/MX Release 3.1 to SQL/MX Release 3.2, and how to manage different versions of SQL/MX. SQL/MX is a relational database management system for the HP NonStop server and is based on the ANSI/ISO/IEC 9075:1999 SQL standard, commonly referred to as SQL:1999. Throughout this manual, references to SQL/MX Release 2.
SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. Specialized Guides SQL/MX Installation and Upgrade Guide Describes how to plan to install, create, and upgrade a SQL/MX database. SQL/MX Management Manual Describes how to manage a SQL/MX database.
These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
migrations. If you or your customer deems this functionality to be critical to your project, send a note and background on the project to harry.scott@carrscott.com. • Migration to format 2 SQL/MP tables. For more information, see the SQL/MP Installation and Management Guide. Document organization This document is organized as follows: “Preparing to install SQL/MX” (page 12) Describes preinstallation requirements. “Installing SQL/MX” (page 14) Describes the steps for installing SQL/MX.
pathname [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces.
by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]… Publishing history This guide supports HP NonStop SQL/MX Release 3.2 until otherwise indicated by its replacement publication. The publication date and part number indicate the current edition of the document. Part Number Product Version Publication Date 691118–003 HP NonStop SQL/MX Release 3.
1 Preparing to install SQL/MX Before you install SQL/MX Release 3.2, check that you have met the following installation requirements : • “Hardware requirements” (page 12) • “Software requirements” (page 12) • “Verifying correct versions of independent software products” (page 13) • “Starting TMF” (page 13) Hardware requirements Before you install SQL/MX Release 3.2, your HP Integrity NonStop server must meet these prerequisites. You must install SQL/MX Release 3.
Software Requirements Installation Manual TMF TMF Planning and Configuration Guide HP NonStop TCP/IP TCP/IP Configuration and Management Manual *For installation instructions and the latest version requirements, see the Softdoc file delivered with the product, either on the product CD or on the website for Scout for NonStop Servers, at HP NonStop eServices portal: https://onepoint.nonstop.compaq.com/.
2 Installing SQL/MX Summary of installation tasks If this is the first installation of SQL/MX on your NonStop system, read “Using the InstallSqlmx script” (page 14) to acquaint yourself with the InstallSqlmx script and its functions during the installation of SQL/MX. If any version of SQL/MX already exists on your NonStop system, see “Overview of SQL/MX system upgrade” (page 30). To install SQL/MX Release 3.2: 1.
• After creating the anchor file, InstallSqlmx assigns a security of “N---” to the anchor file. CAUTION: After InstallSqlmx assigns a security of “N---” to the anchor file, do not change this setting. If the anchor file is modified, SQL/MX ceases to operate on the system. • Initializes SQL/MX after successfully creating the anchor file. • Implements the national character set specified with the -n option. • Initializes the SQL/MX Connectivity Service (MXCS) after successfully initializing SQL/MX.
Table 1 The Phases of InstallSqlmx (continued) Phase Phase Activities NOTE: If a large number of disk drives are attached to the local node, the FCHECK portion of InstallSqlmx can take a long time to complete. To avoid this delay, run FCHECK before you run InstallSqlmx.
Installing SQL/MX 1. 2. 3. Log on to the NonStop server using SUPER.SUPER (user ID 255,255). Install the required H-series or J-series SUT. Follow all the instructions provided in the associated software release document and any instructions that come with the SUT. For more information about installing the SUT, see the H06.25 and J06.14 Software Installation and Upgrade Guide. As needed, use DSM/SCM to install SPRs to obtain the functionality and defect repair for SQL/MX Release 3.2.
in the appropriate case as the OSS environment is case-sensitive. You must be logged on as the super ID user to execute the InstallSqlmx script. CAUTION: InstallSqlmx creates the anchor file and assigns it a security of “N----.” Do not attempt to change this setting. If you change the security setting, and the anchor file is modified, SQL/MX ceases to operate on the system. 8. Check if this is the first installation of SQL/MX on your system.
1. 2. 3. The H06.25 or J06.14 or later H-series or J-series RVU includes SQL/MX executables and libraries. When you install the SUT, the installation automatically copies these files to their default locations: • To $SYSTEM.SYSTEM, the files IMPORT,MXTOOL, MXIMPDDL, MXAUDSRV, MXCMP, MXESP, MXOMSG, MXRTDSRV, MXUDR, MXUTP, SQLCLIO, and ESQLCLIO. • To $SYSTEM.ZMXTOOLS, the files MXGNAMES, MXVQP, T0607SET,and T0610SET. • To $SYSTEM.ZMXODBC, the files MXOAS, MXOCFG, MXODSN, MXOSRVR,and TDMODBC.
4. 5. The SUT installation automatically installs the EMS events file, ZMXSTMPL, to $SYSTEM.ZTEMPL. In addition, DSM/SCM automatically uses the COPYOSS macro to install these OSS files to these OSS directories: a. COPYOSS installs these files in the OSS directory /usr/tandem/sqlmx/bin: b. c. • InstallSqlmx • mxci • mxcierrors.cat • mxCompileUserModule • mxexportddl • mxsqlc • mxsqlco • setmxdb • ansp.
• mxlangman.policy • mxudr.mak • mxt_install.ksh • mxtool_install_procs • mxtool_verify.jar CAUTION: Do not move the mxlangman.jar file to a location outside the installation directory, /usr/tandem/sqlmx/udr. If you do, you will encounter problems invoking SPJs. For more information, see the SQL/MX Guide to Stored Procedures in Java. Verify that files are licensed Make sure that all these files are licensed in $SYSTEM.
You are now in the OSS environment. 2. Enter the mxci command to start MXCI. Enter the command in lowercase as the OSS environment is case-sensitive. The MXCI session begins, and the MXCI prompt (>>) appears. For more information about MXCI commands, see the SQL/MX Reference Manual. Summary of installed SQL/MX files After the SUT is installed, these SQL/MX files are installed on your system as described next. You must run all OSS files from OSS.
File Name Description MXIMPDDL An OSS command-line utility, which is a licensed Guardian $SYSTEM.SYSTEM code 800 file, replicates the DDL definition and statistics of SQL/MX objects. MXGNAMES The Guardian code 800 file for the MXGNAMES utility. $SYSTEM.ZMXTOOLS import The symbolic OSS link to the IMPORT utility that resides in Guardian file space. /usr/tandem/sqlmx/bin mxci The OSS file for the SQL/MX conversational interface /usr/tandem/sqlmx/bin (MXCI).
Run-Time libraries File Name Description Where Installed ZCLIPDLL Privileged layer of the CLI and the SQL/MX file $SYSTEM.ZDLLnnn system code is a licensed Guardian code 800 file. ZCLIDLL A library containing CLI calls is a Guardian code $SYSTEM.ZDLLnnn 800 file. CLI is an interface between a host language and an SQL implementation that allows host language programs to execute SQL statements and to perform related operations, such as the retrieval of diagnostics information.
• NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.SQLJ • NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.ANSINAMES These user module files are installed in the directory /usr/tandem/sqlmx/USERMODULES: NONSTOP_SQLMX_NSK.SCH. MXGNAMES The user module file that SQL/MX uses to read metadata information for MXGNAMES. CAUTION: SQL/MX uses these files to read information from and perform updates to its internal tables.
For example, to find the version of MXCMP installed, at a TACL prompt, enter: $SYSTEM SYSTEM 4> vproc mxcmp Information similar to the following will be displayed: VPROC - T9617H01 - (01 FEB 2009) SYSTEM \SNEEZY Date 10 JUN 2012, 22:18:58 Copyright 2004 Hewlett-Packard Development Company, L.P. $SYSTEM.SYSTEM.
3. in the C:\Program Files\Hewlett Packard\NonStop SQL-MX folder. If you prefer, you can select a different installation folder. Delete SETUP.EXE if you want. Visual Query Planner requires the Microsoft ODBC DataSource Administrator (client), and MXCS to be configured and started to connect to SQL/MX. For more information about installing the Microsoft ODBC Data Source Administrator, see the ODBC/MX Driver for Windows Manual.
3 Installing a sample database You can find the readme file for the sample database at /usr/tandem/sqlmx/bin. The setmxdb installation script for the SQL/MX sample database allows you to create and insert data in a SQL/MX database. You can then use the database to learn about SQL/MX, and to test your applications. The steps to install a sample database (T0517) are: 1. The setmxdb installation script is distributed as a PAX file named T0517PAX in the $SYSTEM.ZOSSUTL subvolume.
Options Description All tables in the Data Mining database have three partitions. The -vol option is used to specify the location of the first (or primary) partition, while the -part option is used to specify the locations of the second and third partitions. Only one table in the Pub/Sub sample database, the INBOX table, is partitioned. This table has three partitions.
4 Overview of SQL/MX system upgrade This section introduces the process of upgrading the system to SQL/MX Release 3.2 and discusses the following topics: • “Terminology” (page 30) • “Upgrading SQL/MX system” (page 30) • “Guidelines for conducting the system upgrade” (page 31) • “Assistance with upgrade” (page 31) Terminology You must be familiar with the following terms, which are used throughout the manual: Metadata Downgrade or DOWNGRADE.
Guidelines for conducting the system upgrade To ensure a successful system upgrade, use the following guidelines: • “Conducting the initial upgrade in a test environment” (page 31) • “Running baseline tests and gathering data at each upgrade phase” (page 31) • “Assessing the success of the upgrade” (page 31) Conducting the initial upgrade in a test environment To avoid disrupting the production database and daily business operations, isolate the key applications and database objects to upgrade and con
you implement the project plan. For more information about this service, contact NonStop-SDI-Services@hp.com.
5 Considerations for upgrading the system to SQL/MX Release 3.2 If you are currently using SQL/MX, to obtain new functionalities, performance enhancements, and bug fixes, you might want to upgrade to a newer version. This section discusses the factors that you must consider before upgrading to SQL/MX Release 3.2, and addresses the following topics: • “System upgrade path” (page 33) • “Compatibility of SQL/MX Release 3.
Figure 2 Compatibility of SQL/MX releases in a network R2.3.x OR R3.0 OR R3.1 R3.2 R3.2 R3.2 If your network includes multiple versions of SQL/MX, such as SQL/MX Release 2.3.3 and SQL/MX Release 2.3.4, you must upgrade all of them to SQL/MX Release 3.2 to ensure compatibility, as shown in Figure 3 (page 34). Figure 3 Compatibility of SQL/MX releases in a network after upgrade R2.3.x R2.3.y R3.2 R3.
nonstop_sqlmx_.system_schema.cat_references cr where cs.replication_rule = 'A' and cs.cat_uid = cr.cat_uid and cr.replication_rule = 'M'; Unregister these catalogs from the corresponding systems using the UNREGISTER CATALOG command. For UNREGISTER CATALOG considerations, see the SQL/MX Reference Manual. • Remote catalog registrations — Check if the catalogs on the systems that must remain at lower release are registered on the systems that need to be upgraded.
Considerations for using the Similarity Check feature You must consider the following factors before using the Similarity Check feature: • The DDL_VIEW_SIMILARITY_CHECK defaults attribute is set to ENABLE, by default. In SQL/MX Release 3.2, if you create a view on a schema with version 1200, 3000, or 3100, a version-related error is returned. You must set the default attribute to OFF before creating a view on schemas with a version earlier than 3200.
6 Upgrading the system to SQL/MX Release 3.2 This section addresses the following topics: • “Planning for release downgrade” (page 37) • “Upgrading the system from SQL/MX Release 2.3.x to SQL/MX Release 3.2” (page 37) • “Upgrading the system from SQL/MX Release 3.0 or Release 3.1 to SQL/MX Release 3.2” (page 41) • “Upgrading the metadata and using new features on existing and new databases” (page 44) Planning for release downgrade Before you install SQL/MX Release 3.
Figure 4 Sequence to upgrade from SQL/MX Release 2.3.x to SQL/MX Release 3.2 Start Stop Applications App1 cat1 Stop TMF, Start, Stop TMF Sch1 v1200 Starting Version Install R3.2 and cold load App1 cat1 Sch1 v3200 Start TMF Resulting Version Run mxtool fixrcb Recompile Applications Take new TMF dumps Preinstallation Complete the following prerequisites: 1. Stop all applications which access the database. 2.
Installing SQL/MX Release 3.2 1. 2. Install the Site Update Tape (SUT) for the required RVU. Determine the required RVU for a SQL/MX Release 3. See Table 4 (page 60). To install an RVU, see the H06.25 or J06.14 Software Installation and Upgrade Guide. Install any necessary Independent Software Products, such as the NonStop Server for Java, the JDBC/MX Driver for SQL/MX, and RDF. Accessing the database After the initial system load on the target release, RCBs are still incompatible.
For more information, see “Converting the CPU list format from SQL/MX Release 3.2 to SQL/MX Release 2.3.x” (page 53). The following is the syntax: mxtool fixmxcsmd -v target [-d] Where target is the major SQL/MX release that corresponds to the required target format. The values allowed are 2 and 3. The -d flag enables mxtool to display the source and target CPU list values without performing any conversion. Using the -d flag is optional. NOTE: state.
modules and query plans from SQL/MX Release 2.3.x or earlier versions. You must recompile all the existing embedded modules so they are compatible with the enhanced SQL/MX Release 3.2 executor. NOTE: Recompiling programs might result in different execution plans, causing a change in performance. Upgrading the system from SQL/MX Release 3.0 or Release 3.1 to SQL/MX Release 3.2 This section describes the sequence and the procedures to upgrade the system from SQL/MX Release 3.0 or Release 3.
Preinstallation Before upgrading, complete the following steps: 1. Stop all applications that access the database. 2. Stop the HP NonStop Transaction Monitoring Facility (TMF). NOTE: When upgrading from SQL/MX Release 3.0 or 3.1 to SQL/MX Release 3.2, you do not have to stop TMF twice before the installation. Installing SQL/MX Release 3.2 For more information about installing SQL/MX Release 3.2, see “Installing SQL/MX” (page 14) .
NOTE: Perform the following steps after upgrading to SQL/MX Release 3.2 or later: • Recompile the user modules, which are compiled with releases prior to SQL/MX Release 3.0 (H30^AOX SPR) or 3.1 (H31^AOW SPR). You need not recompile the user modules, which are compiled with SQL/MX Release 3.0 (H30^AOX) or 3.1 (H31^AOW). For more information, see the Support Note S12027. • If your application uses the Module File Caching feature and the modules were compiled with releases prior to SQL/MX Release 3.
Upgrading the metadata and using new features on existing and new databases After upgrading the metadata to SQL/MX Release 3.2 using the relevant upgrade sequence, you can use the SQL/MX Release 3.2 features on existing and new databases. The scenarios in this section describe how you can use the new features. • Scenario 1 - New features offered in SQL/MX Release 3.2 on existing database and applications: To use new features offered in SQL/MX Release 3.
Figure 7 Coexistence of new and old databases App1 cat1 App2 Sch1 cat2 Sch2 App3 v1200 OR v3000 OR v3100 • v3200 Scenario 3 - New features offered by SQL/MX Release 3.2 are not required: If you do not want to use any of the new features offered by SQL/MX Release 3.2 on existing databases, no further changes are required after upgrading the system. NOTE: Scenario 3 does not involve a metadata upgrade. Figure 8 New features of SQL/MX Release 3.
7 Falling back from SQL/MX Release 3.2 This section describes falling back from SQL/MX Release 3.2 to SQL/MX Release 3.1, SQL/MX Release 3.0, and to SQL/MX Release 2.x. NOTE: When using new features, if you encounter an issue that is specific to SQL/MX Release 3.2, discontinue the use of those features instead of falling back from that version.
Figure 10 Falling back to a SQL/MX Release 2.x version earlier than SQL/MX Release 2.3.4 SQL/MX SQL/MX SQL/MX R3.2 R2.3.4 R2.
Fallback sequence Based on your requirements, you can fall back in one of the following ways: • If you have not upgraded the metadata to SQL/MX Release 3.2 and have not created any new databases, you can fall back as shown in Figure 11 (page 48). Figure 11 The v3100, v3000 or v1200 objects are not upgraded and new objects are not created on SQL/MX Release 3.
• If you have upgraded your metadata from v1200, v3000, v3100 to v3200 but not created objects with new features, you can fall back as shown in Figure 12 (page 49).
Figure 13 New database objects are created on SQL/MX Release 3.2 Start Stop Applications App1 cat1 Run downgrade command Sch1 v3200 Starting Version Stop TMF, Start, Stop TMF Install desired fallback release, including required fallback SPRs if any App1 cat1 Run mxtool fixrcb Sch1 v3100 OR v3000 OR v1200 Resulting Version Recompile Applications Install 2.
NOTE: Recompiling programs might result in different execution plans, causing a change in performance. • The online dumps taken on SQL/MX Release 3.2 cannot be used by TMF recovery on SQL/MX Release 3.1, SQL/MX Release 3.0 or on SQL/MX Release 2.x. HP recommends that you take new full TMF online dumps immediately after fallback. • If security administrators have granted any privileges on database objects after upgrading to SQL/MX Release 3.
Consider the following scenarios for system fallback: • • Executing the DOWNGRADE command is not necessary in the following scenarios: ◦ The fallback version is SQL/MX Release 3.1 and the database contains v3100, v3000 and v1200 schemas only. ◦ The fallback version is SQL/MX Release 3.0 and the database contains v3000 and v1200 schemas only. ◦ The fallback version is SQL/MX Release 2.3.x and the database contains v1200 schemas only.
4. 5. Perform the operations described in “Accessing the database” (page 53). Perform the operations described in “Migrating applications after system fallback” (page 54). Installing SQL/MX Release 2.3.4 To fall back to SQL/MX Release 2.3.4, complete the following steps: 1. Install SQL/MX Release 2.3.4. For more information on installing SQL/MX Release 2.3.4, see “Installing SQL/MX” (page 14). 2. Install the fallback SPRs referred to in the SQL/MX Release 3.2 softdoc, if any. 3.
Errors are generated under the following circumstances: • If you assign a wrong value for number • If a normal user executes the command (Only a SUPER ID user can convert the MXCS metadata.) Warnings are generated under the following circumstances: • If a qualifying row from ENVIRONMENTVALUES has an inconsistent value, the row is skipped and a warning is issued.
A Removing security administrator grants In the following steps, queries create a command file containing SQL REVOKE statements targeting all grants that were performed by security administrators. You can then use the command file as input to mxci to remove the grants. To effectively use these queries, carefully read and follow the directions embedded within the comments as some variables must be modified prior to execution. These queries must be run against version 3100 or later metadata.
?CATALOG_NAME from definition_schema_version_3100.col_privileges as col_privileges where col_privileges.grantor != -2 and not exists ( select col_privileges2.table_uid,col_privileges2.grantor,col_privileges2.grantee, col_privileges2.privilege_type,col_privileges.column_number from definition_schema_version_3100.col_privileges as col_privileges2 where (col_privileges.grantor = col_privileges2.grantee or col_privileges2.grantee = -1 ) and col_privileges2.table_uid = col_privileges.
log; --------- Exit mxci and logon as a security administrator. Reenter mxci and obey the output file ($$COMMANDS_FILE$$) created above to revoke the security administrator grants. For example (and assuming $$COMMANDS_FILE$$ was set to “obey.txt”): mxci Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 3.1 (c) Copyright 2003, 2004-2011 Hewlett-Packard Development Company, LP. >>obey obey.
B Identifying tables that contain an IDENTITY Column The following steps use queries to extract names of tables with an IDENTITY column. These queries must be run against version 3100 or later metadata. -- Execute all of the following from within mxci. -- Set pattern $$SYSTEM$$ to the name of the system on which the queries are -- executing.
C Identifying tables where the ownership has changed If the ownership of database objects is changed after upgrading to SQL/MX Release 3.2, you must revert the changes before fallback, because earlier releases do not support this feature. To revert the ownership, the schema owner must be an existing Guardian user. To revert the changes prior to fallback, complete the following steps for each user catalog on the system: 1.
D Version management and interoperability This section covers these topics: • “SQL/MX releases” (page 60) • “Interoperability of SQL/MX releases” (page 63) • “Versions of SQL/MX software components” (page 63) SQL/MX releases A SQL/MX release identifies a set of SQL/MX products as belonging to a given version of SQL/MX.
1. The first number indicates a major release, which introduces major new functionality and a new SQL version. 2. The second number indicates an incremental release, which introduces an incremental change and some new functionality. 3. The third number indicates a maintenance release, which introduces bug fixes and minimal new functionality.
MMDD is the final build date for the product, including the month and day (for example, 1130 for November 30). For example, this VPROC command displays information for the T1051 product that is associated with SQL/MX Release 3.0.0 for H-series RVUs: T1051H30_14FEB2011_H30_300_1130 The SQL/MX Master Softdoc (T0650) for each SQL/MX release refers to the release name and VPROC information.
Table 5 (page 63) lists the current supported RVUs of NonStop systems and the SQL/MX release that they support. You can install only one SQL/MX release on a system at a time. Table 5 RVUs and Supported SQL/MX Releases RVUs Supported SQL/MX Releases H06.25 and later J06.14 and later SQL/MX Release 3.2 nl Interoperability of SQL/MX releases SQL/MX Release 3.2 does not interoperate with other versions of SQL/MX in an EXPAND network.
Table 6 Software version identifiers for SQL/MX Release 3.2 Version Item SQL/MX release version identifier 3.2 For more information, see...
SQL/MX Release version Schema version SQL/MX Release 3.0 3000 SQL/MX Release 2.x 1200 A database object can be accessed if all involved systems and schemas are version compatible. Displaying the schema version The following example displays the schema version of a specified schema, where sch is the schema name: >>get version of schema sch; VERSION: 3200 --- SQL operation complete. >> For more information about SQL/MX Language Elements, see the SQL/MX Reference Manual.
SQL/MX successfully compiles and executes a query execution plan if the plan version is higher than or equal to the earliest supported plan version and lower than or equal to the MXV of the node where the plan is being executed. Otherwise, SQL/MX returns a versioning error. Module versions When you compile modules of embedded SQL/MX applications, the version assigned to a module is the same as the current plan version of the node where you compiled the module. The SQL/MX Release 3.
E Conversions Conversions from numeric to char or varchar SQL/MX Release 3.x reports 8402 errors during conversions from numeric to char or varchar types with truncation. In SQL/MX Release 2.x, such conversions truncate the data without any errors. The following examples illustrate overflow errors generated in SQL/MX Release 3.x: • Example 1 >>create table tnumeric (a numeric (18,6)); --- SQL operation complete. >> >>insert into tnumeric values (123456789012.345678); --- 1 row(s) inserted.
--- 1 row(s) selected. >> >>update tnumeric set a = a + a +>where cast( a * 20 as char (10)) > '5'; *** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:DECIMAL SIGNED(REC_DECIMAL_LSE) Source Value:0x2B0002040609010305070800020406090103050600 to Target Type:CHAR(REC_BYTE_F_ASCII). --- 0 row(s) updated.
Index A O Access the database, 39 Object versions, 64 OFV, 65 OSS, 17 C Changes required after falling back, 54 Compatibility, 33, 34 COPYOSS, 14 R F S Fallback, 46 Considerations, 50 Sequence, 48 Stopping TMF, 52 Schema version, 65 Security Administrator Grants, 55 SPR, 62 SQL/MX Hardware, 12 Software, 12 Starting TMF, 13 Starting MXCI, 21 System Defaults, 26 System upgrade, 30 3.0, 3.1 to 3.
