HP NonStop SQL/MX Remote Conversational Interface (RMXCI) Guide HP Part Number: 691129-001 Published: August 2012 Edition: HP NonStop SQL/MX Release 3.
© 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....................................................................................10 Intended audience..................................................................................................................10 Document organization...........................................................................................................10 New and changed information in this edition.............................................................................
Showing information about SQL database objects..................................................................59 Displaying run commands...................................................................................................60 Editing and rerun a command.............................................................................................60 Clearing the interface window.............................................................................................60 Obtaining help..........
Examples..........................................................................................................................83 DISCONNECT command........................................................................................................83 Syntax..............................................................................................................................83 Considerations.........................................................................................................
Example.........................................................................................................................100 QUIT command....................................................................................................................101 Syntax............................................................................................................................101 Considerations....................................................................................................
Considerations................................................................................................................112 Examples........................................................................................................................112 SET PARAM command..........................................................................................................114 Syntax.....................................................................................................................
Examples........................................................................................................................124 SHOW FETCHSIZE command................................................................................................125 Syntax............................................................................................................................125 Considerations..............................................................................................................
Considerations................................................................................................................134 Example.........................................................................................................................134 SHOW SQLTERMINATOR command.......................................................................................134 Syntax............................................................................................................................
About this document This guide describes how to install and use the HP NonStop SQL/MX Remote Conversational Interface (RMXCI), installed on a client workstation, to manage SQL/MX 3.x database on a NonStop platform. RMXCI enables you to perform daily administrative tasks by running SQL statements interactively or from script files. In this release, RMXCI is supported on Linux and Windows platforms.
Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.sh Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. [ ] Brackets Brackets enclose optional syntax items.
expression-n… Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: DAY (datetime-expression) @script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
• • Reference manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements. SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Connectivity manuals SQL/MX Connectivity Service Manual Describes how to install and manage SQL/MX Connectivity Service (MXCS), which enables ODBC and other connectivity APIs to use NonStop SQL/MX.
• Online help Reference Help Overview and reference entries from the SQL/MX Reference Manual. Messages Help Individual messages grouped by source from the SQL/MX Messages Manual. Glossary Help Terms and definitions from the SQL/MX Glossary. 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.
1 Introduction to RMXCI HP NonStop SQL/MX Remote Conversational Interface (RMXCI) is a command-line interface, which you can download and install on a client workstation that has a NonStop JDBC Type 4 Driver. The NonStop JDBC Type 4 Driver connects RMXCI on a Linux or Windows client workstation to an SQL/MX 3.x database on a NonStop platform. Figure 1 RMXCI connected to the NonStop environment NOTE: RMXCI interacts only with NonStop SQL/MX 3.x databases.
2 Installing RMXCI The hardware and software requirements for RMXCI are described in the softdoc file delivered with the product, through the Scout website for NonStop servers through HP NonStop eServices portal (https://onepoint.nonstop.compaq.com/). Read the softdoc file before installing the product. RMXCI installation requirements include: • SQL/MX Release 3.x on the NonStop system • The following softwares on the client workstation: ◦ Java 1.5 or later ◦ HP NonStop JDBC Type 4 Driver Release 3.
Verifying the Java version To display the Java version of the client workstation, enter the following command: java -version For example, • On Windows, enter the following command: C:\java -version The following is a sample output: java version "1.5.0_10" Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_10-b03) Java HotSpot(TM) Client VM (build 1.5.
4. Select the Path variable and then click Edit. 5. Place the cursor at the beginning of the Variable value: field. Enter the path of the Java bin directory, ending with a semicolon (;).
NOTE: Ensure that there is no space after the semicolon (;) in the path. If there are spaces in the directory name, delimit the entire directory path in double quotes (") before the semicolon. For example, "C:\Program Files\Java\jdk1.5.0_10\bin";. 6. 7. 8. Click OK. Verify that the updated Path appears in System variables, and click OK. Click OK to accept the changes in the System Properties dialog box. Setting the PATH for a supported Java version on Linux 1.
2. To view the version information, enter the following command: java -jar t4sqlmx.jar For example, • On Linux, enter the following command: home:cd /usr/home/install/t4jdbc/lib/java -jar t4sqlmx.jar /usr/home/install/t4jdbc/lib/:java -jar t4sqlmx.jar The following is a sample output: T1249_V32_30AUG12_HP_NONSTOP(TM)_JDBCT4_2012_04_18 /usr/home/install/t4jdbc/lib/: • On Windows, enter the following command: C:\>cd install\t4sqlmx\lib C:\install\t4sqlmx\lib>java -jar t4sqlmx.
3. Locate the file with the name T0774ZIP by using the following command: $SYSTEM.ZMXODBC> fileinfo T0774ZIP 4. 5. 6. To transfer the file to your client workstation by using a binary mode, use any of the available tool or use ftp command on the command prompt. You must rename the file T0774ZIP to rmxci.zip. The file rmxciInstaller.jar is available within the rmxci zip file. To extract the Installer on a Linux system, use the following command on a Linux command window: /usr/home/rmxci:unzip rmxci.
Verifying the installer file version To display the version of the installer file, complete the following steps: 1. Change to the directory, which contains the RMXCI installer file: cd installer-directory where, installer-directory is the directory where you downloaded the installer. 2. Enter the following command to get the version information: java -jar rmxciInstaller.jar v For example, C:\>cd RMXCI C:\RMXCI>java -jar rmxciInstaller.
6. Launch the Installer Wizard with the following command: java -jar rmxciInstaller.jar • Running the Installer Wizard When you run rmxciInstaller.jar, the Installer Wizard appears. 1. 2. Select Standard Installation to start the Installer Wizard. The Installer Wizard guides you through installing both the core RMXCI components and the optional open source extensions. For a quick installation of the core RMXCI files, choose Core Components.
3. To locate the NonStop SQL/MX JDBC Driver JAR file, click Browse. 4. Select t4sqlmx.jar and click Select. The Installer Wizard displays the path of the NonStop JDBC Type 4 Driver JAR file.
5. 6. 7. To install in the default location, skip the next two steps. To install in a different location, proceed to the next step. To find an installation location for RMXCI, click Browse. Select the folder where you want to install RMXCI so that the directory path appears in the File Name box, and click Select.
The Installer Wizard displays the directory where RMXCI will be installed. 8. Click Next to review the open source disclaimer. 9. If you agree to the terms and conditions, select the check box and click Next. The Installer Wizard dialog box appears, showing which components are available for you to download and install. 10. Select the optional components to be downloaded and installed. 11.
NOTE: Perl and Python must be installed for the corresponding extensions to work. Select the proxy information as required to download the open source extensions. If you do not require a proxy server, skip the next two steps. 12. To configure the proxy settings, select Use the following proxy settings:. RMXCI installer attempts to auto-detect your proxy settings. 13. Click Detect Proxy Server(s) to list the available proxy servers. If RMXCI installer detects one or more proxy servers, they are displayed.
The following screen appears when the installation completes. 16. Click Exit. • Command-line installation procedure The command-line installation steps are common to Linux and Windows platforms.
1. At the command prompt, change to the directory where you downloaded the installer:cd installer-directory where, installer-directory is the directory where you have downloaded the installer. 2. Before launching the command-line installer, see the command options by using the -help option: java —jar rmxciInstaller.
4. Enter the full directory path and file name of the NonStop JDBC Type 4 Driver JAR file, t4sqlmx.jar which is located in the NonStop JDBC Type 4 Driver lib directory. The following is a sample display for Windows installation: NonStop(TM) SQLMX JDBC Type 4 Driver JAR File ---------------------------------------------Enter the location and file name [C:\t4sqlmx.jar]: C:\install\t4sqlmx\lib\t4sqlmx.
If you do not require a proxy server, skip the step for detecting the proxy server, and proceed with the installation. 8. RMXCI can attempt to detect your proxy settings. If proxy servers are found, they are displayed as follows: Use a proxy server? [Y]: Y Attempt to auto-detect proxy server(s)? [Y]: N Enter the proxy server (do not include the port): myproxyserver.com Enter the proxy port: 8080 9. The install prompts you to indicate which optional components you want to download and install.
Post-installation procedures This section describes the steps to verify the installation: • “Verifying the installed software files” (page 32) • “Verifying the installed version of RMXCI” (page 32) • “Testing the RMXCI launch” (page 39) Verifying the installed software files After downloading and running the installer file, rmxciInstaller.jar, verify that the RMXCI software files are installed in the correct locations as described in Table 2 (page 32).
1. Go to the lib directory, which contains the RMXCI JAR file: • On Windows, enter the following command: C:\>cd rmxci-installation-directory\rmxci\lib • On Linux, enter the following command: /home:cd rmxci-installation-directory/rmxci/lib where, rmxci-installation-directory is where you installed the RMXCI software files. 2. To obtain the version information, enter the following command: java -jar rmxci.jar For example, C:\>cd install/rmxci/lib C:\install\rmxci\lib>java -jar rmxci.
On Linux, use the following command: export _JAVA_OPTIONS=-DrmxciLF=SQLPLUS Supported Look-and-Feel Types In addition to the default look-and-feel of RMXCI, SQLPlus and Teradata types are also supported.
Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. SQL>SET SCHEMA NONSTOP.RMXCI_SAMPLE; --- SQL operation complete. SQL> To display the SQL status messages in SQLPlus look-and-feel, set the -DrmxciLF property value to SQLPlus.
Setting the Look-and-Feel Type You can set the look-and-feel option either from a command-line prompt or in the user profile on Linux or in the system properties on Windows. NOTE: • The images shown in this section vary depending on the specific Windows version.
3. If you do not see _JAVA_OPTIONS in the environment variables, click New.... If you see _JAVA_OPTIONS, click Edit....
4. Enter _JAVA_OPTIONS for the Variable name: and -DrmxciLF property value for the Variable value:, and click OK. The Variable value: must include -DrmxciLF=look-and-feel-type. where, look-and-feel-type is one of the “Supported Look-and-Feel Types” (page 34). For example, -DrmxciLF=SQLPlus • 5. Verify that the new or updated _JAVA_OPTIONS is set and click OK. 6. In the System Properties dialog box, click OK to accept the changes.
3. To activate the changes, either log out and log in again or run the user profile command. For example: /home:. .profile To return to the default look-and-feel, set the -DrmxciLF property value to RMXCI. NOTE: If you do not set a look-and-feel type, the default is RMXCI. Setting the trace option You can set the _JAVA_OPTIONS environment variable -Drmxci.enableTrace to enable the display of stacktrace output on the console for exceptions.
Picked up _JAVA_OPTIONS: -DrmxciLF=BTEQ Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Host Name/IP Address: nsksys.ind.hp.com:18650 User Name: super.super Password: Data Source Name [TDM_Default_DataSource]: Connected to Data Source: TDM_Default_DataSource SQL> • 40 If you cannot connect to the database, verify whether: 1.
3 Launching RMXCI This chapter describes how to launch RMXCI : • “Launching RMXCI on Windows using graphic tools” (page 41) • “Launching RMXCI on Linux” (page 43) • “Logging in to the database platform” (page 44) • “Using optional launch parameters” (page 47) • “Launching RMXCI without connecting to the database” (page 52) • “Running RMXCI with -version” (page 52) • “Running RMXCI with -help” (page 53) • “Exiting RMXCI” (page 53) For information about launching RMXCI from Perl or Python, see
4. If required, specify optional launch parameters for the shortcut: 1. Right-click the shortcut icon and select Properties. 2. Select the Shortcut tab. 3. In the Target:, insert a space after rmxci.cmd and add the optional launch parameters. For more information, see “Using optional launch parameters” (page 47). 4. 42 Launching RMXCI Click OK.
5. To launch RMXCI, double-click the shortcut icon. The RMXCI appears. If you have not set the optional launch parameters, RMXCI prompts you to enter the host name or IP address and port number of the service you want to connect, the user name, password and a data source name. For details, see “Logging in to the database platform” (page 44). • Launching RMXCI on Windows from command prompt: You can specify the path to rmxci.cmd or go to that location as shown below to invoke rmxci.
3. To activate the changes, either log out and log in again or run the user profile command. For example: /home:. .profile 4. To launch RMXCI, from the client terminal window, run rmxci.sh: /home:rmxci.sh Ensure that rmxci.sh has execute permissions with ls -l command. You can set the permissions with chmod command. RMXCI appears, prompting you to enter the host name or IP address of the NonStop database system, user name, password and a data source name.
Host Name/IP Address: Enter a host name: host-name[:port-number] • Enter a port-number: host-name[:port-number] • If you do not specify a port number, RMXCI uses the default port number18650. Ensure that a service has started on port 18650. Or enter an IP address: IP-address[:port-number] 2. 3. 4. Enter your user name. Enter your password. Enter the name of a data source that is available and started. If you do not enter a data source, by default, RMXCI connects to TDM_Default_DataSource.
Any sessions using redirected or piped input are not prompted for retries. • This example shows an invalid host name. RMXCI then prompts for the host name, followed by the user name and password, as shown: rmxci –h dd Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Unknown Host: dd Host Name/IP Address: 172.16.1.1 User Name: super.
Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Invalid authorization specification User Name: super.super Password:abc Connected to DataSource: TDM_Default_DataSource SQL> After you finish logging in to the NonStop SQL/MX database, the SQL prompt appears, as shown: Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Host Name/IP Address: nsksys.
Launch Parameter Description {-q | -sql} "command" Specifies that an SQL statement or a RMXCI command be run when launching the SQL/MX Remote Conversational Interface. You cannot specify this parameter at the same time as the -s or -script parameter. For more information, see “Running a command when launching RMXCI” (page 49). {-s | -script} script-file-name Specifies that a script file be run when launching RMXCI in interactive mode.
Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Connected to DataSource: TDM_Default_DataSource SQL> Running a command when launching RMXCI To run an SQL statement or an RMXCI command when launching the SQL/MX Remote Conversational Interface, use the -q or -sql command line parameter. This parameter enables you to run a single command on the command-line without having to enter commands in RMXCI.
Host Name/IP Address: nsksys.ind.hp.com:18650 User Name: super.super Password: DataSource Name [TDM_Default_DataSource]: --- SQL operation complete. Example of running an RMXCI command with -q or -sql To run multiple script files simultaneously from the command line, use -q or -sql options with the prun command. For more information, see “PRUN command” (page 99). On Linux, enter the following commands: /home:cd rmxci-installation-directory/rmxci/bin /home/usr/rmxci/bin:./rmxci.
/home:cd rmxci-installation-directory/rmxci/bin /home/usr/rmxci/bin:./rmxci.sh -s settings.txt On Windows, enter the following commands: C:\cd rmxci-installation-directory\rmxci\bin C:\rmxci\bin>rmxci.cmd -s settings.txt where, rmxci-installation-directory is the location where you have installed the RMXCI software files. Specify the full path of the script file if it is outside the directory of rmxci.sh or rmxci.cmd.
Welcome to the NonStop(TM) SQL/MX Remote Conversational Interface (c) Copyright 2006-2012 Hewlett-Packard Development Company, LP. Host Name/IP Address: nsksys.ind.hp.com:18650 User Name: super.super Password: DataSource Name [TDM_Default_DataSource]: Connected to DataSource: TDM_Default_DataSource SQL>SET IDLETIMEOUT 0 SQL>SET SQLPROMPT * *SET TIME ON * 14:14:57 *SET TIMING ON 2:14:57 PM *SET SQLTERMINATOR .
NonStop JDBC Type 4 Driver : T1249_V32_30AUG12_HP_NONSTOP(TM)_JDBCT4_2012_04_18 RMXCI Version : T0774_V32_30AUG12_HP_NONSTOP(TM)_RMXCI_2011_12_12 Running RMXCI with -help To display a list of acceptable parameters, including proper usage information, use the -help option. After displaying this information, the application exits. Example of running RMXCI with -help On Linux, enter the following commands: /home:cd rmxci-installation-directory/rmxci/bin /home/usr/rmxci/bin:./rmxci.
4 Interactively running commands in RMXCI After launching the HP NonStop SQL/MX Remote Conversational Interface, you can run SQL statements and RMXCI commands in the interface.
For more information, see the concatenation operator in the SQL/MX Release 3.x Reference Manual. To terminate an SQL statement that spans multiple lines, use the SQL terminator for the session. A syntax error is returned, if more than one SQL statement is included in the same line. For more information, see “Setting and showing the SQL terminator” (page 57). Case sensitivity In the RMXCI interface, you can enter SQL statements and RMXCI interface commands in uppercase, lowercase, or mixed-case characters.
SQL>session COLSEP " " DATASOURCE TDM_Default_DataSource HISTOPT DEFAULT [No expansion of script files] IDLETIMEOUT 30 min(s) LIST_COUNT 0 [All Rows] LOG OFF LOOK AND FEEL RMXCI MARKUP RAW PROMPT SQL> CATALOG NONSTOP_SYSTEM_NSK SCHEMA PUBLIC_ACCESS_SCHEMA SERVER nsksys.ind.hp.com:18650 SQLTERMINATOR ; STATISTICS OFF TIME OFF TIMING OFF USER super.super SQL> For more information, see the “ENV command” (page 83) or “SHOW SESSION command” (page 132).
SQL ENTER> For more information, see the “SET PROMPT command” (page 116). SET TIME command The set time on command causes the current time of the client workstation to appear in the prompt: SQL ENTER>set time on 20:32:26 SQL ENTER> The set time off command removes the current time from the prompt: 20:32:26 SQL ENTER>set time off SQL ENTER> For more information, see the “SET TIME command” (page 120). Setting and showing the SQL terminator The SQL terminator symbolizes the end of an SQL statement.
Setting and showing the current schema By default, the schema of the session is PUBLIC_ACCESS_SCHEMA and catalog is NONSTOP_SYSTEM_NSK. The SQL statement set schema, allows you to set the schema for the RMXCI session. For example, the following set schema command changes the default schema to PERSNL for the session: SQL>set schema persnl; --- SQL operation complete. SQL>delete from employee where first_name='TIM' and last_name='WALKER'; --- 1 row(s) deleted.
For more information, see the “SET LIST_COUNT command” (page 110) and the “SHOW LIST_COUNT command” (page 127). Showing information about SQL database objects • “Showing the schemas” (page 59) • “Showing the tables in a schema” (page 59) • “Showing the dependent objects of a table” (page 59) • “Showing the views in a schema” (page 59) Showing the schemas The show schemas command displays the schemas that exist in the current catalog.
SQL>set catalog CAT1; -- SQL operation complete. SQL>set schema invent; --- SQL operation complete. SQL>show schema SCHEMA INVENT SQL>show views VIEW NAMES ---------------------------------------------------------------VIEW207 VIEW207N VIEWCS VIEWCUST For more information, see the “SHOW VIEWS command” (page 137). Displaying run commands To display commands that were recently run in the RMXCI session, run the history command.
Obtaining help To display help text for a command that is supported in RMXCI, run the help command. For example, the following command displays syntax and examples for the fc command: SQL>help fc For more information, see the “HELP command” (page 90). Running SQL statements In RMXCI, you can run SQL statements interactively. For a list of these statements, see Appendix B (page 142).
SQL>/ SALARY ---------175500.00 137000.10 139400.00 138000.40 75000.00 90000.00 118000.00 80000.00 70000.00 90000.00 56000.00 --- 11 row(s) selected. For more information, see the “/ command” (page 79), “RUN command” (page 105), or “REPEAT command” (page 102). Preparing and executing SQL Statements You can prepare or compile an SQL statement by using the prepare statement, and then you can run the prepared SQL statement later by using the execute statement.
To specify a parameter to be supplied later, either for a set param statement or for a using clause for an execute statement, use one of these types of parameters in the SQL statement: • Named parameter, which is represented by ?param-name • Unnamed parameter, which is represented by a question mark (?) character For example, the following prepared SELECT statement specifies unnamed parameters for salary and job code: SQL>prepare findemp from +>select * from persnl.
Resetting the parameters To change the value of a parameter, specify the name of the parameter in the reset param command, and then use the set param command to change the setting. For example, suppose that you want to change the salary parameter to 80000.00: SQL>reset param ?sal SQL>set param ?sal 80000.00 Running the reset param command without specifying a parameter name clears all parameter settings in the session, for example: SQL>reset param SQL>show param *** ERROR[29428] No parameters found.
SQL>execute empcom; FIRST_NAME LAST_NAME DEPTNUM --------------- -------------------- ------ALAN TERRY 3000 DAVID TERRY 2000 PETE WELLINGTON 3100 JOHN CHOU 3500 MANFRED CONRAD 4000 DINAH CLARK 9000 DAVE FISHER 3200 GEORGE FRENCHMAN 4000 KARL HELMSTED 4000 JOHN HUGHES 3200 WALTER LANCASTER 4000 MARLENE BONNY 4000 BILL WINN 2000 MIRIAM KING 2500 GINNY FOSTER 3300 MARIA JOSEF 4000 HERB ALBERT 3300 RICHARD BARTON 1000 XAVIER SEDLEMEYER 3300 DONALD TAYLOR 3100 LARRY CLARK 1000 JIM HERMAN 3000 GEORGE STRICKER 310
SQL>create trigger handlecheckout +> after insert on checkout +> referencing new as newr +> for each row +> update member set dues = dues + 10 +> where memberid = newr.memberid; +> / For the syntax of the create trigger statement, see the SQL/MX Release 3.x Reference Manual. Logging output To log a RMXCI session, use the spool or log command. These commands record into a log file the commands that you enter in the RMXCI interface and the output of those commands.
Using the CLEAR option The clear option clears the contents of an existing log file before logging new information in the file. If you omit clear, RMXCI appends new information to existing information in the log file.
For information about error messages that might appear in the log file, see the “Error messages” (page 144).
5 Running scripts in RMXCI In RMXCI, you can perform the following tasks with script files: • “Creating a script file” (page 69) • “Running a script file” (page 70) • “Logging output” (page 71) • “Running scripts in parallel” (page 71) Creating a script file A script file which you run in RMXCI must be an ASCII text file, which contains only the following elements: • “Supported SQL statements in script files” (page 69) • “Permitted RMXCI commands in script files” (page 69) • “Comments” (page 69
in the @ or obey command. For more information, see the“@ command” (page 79) or the “OBEY command” (page 96). Example of a script file This script file creates tables in the inventory schema. Running a script file To run a script file in RMXCI, use the @ or obey command. These commands run one script file at a time in the SQL/MX Remote Conversational Interface. To run a script file when launching RMXCI, see “Running a script file when launching RMXCI” (page 50).
SQL>CREATE SCHEMA INVENT; --- SQL operation complete. SQL>-- CREATE TABLES/VIEWS in SCHEMA INVENT SQL>SET SCHEMA INVENT; --- SQL operation complete. SQL>CREATE TABLE INVENT.
6 Running RMXCI from Perl or Python You can run SQL statements in Perl or Python by invoking the RMXCI Perl or Python wrapper script. To use the wrapper scripts, see: • “Login environment variables” (page 72) • “Perl and Python wrapper scripts” (page 74) • “Launching RMXCI from the Perl or Python command-line” (page 74) These instructions assume that you have installed the RMXCI product. For more information, see Chapter 2 (page 16).
3. 4. 5. Click Environment Variables. Click New in system or user variables in the Environment Variables dialog box. Enter the name of the login environment variable for the Variable Name: and the required value for the Variable value: in the New User Variable dialog box, and then click OK. 6. 7. 8. Verify that the environment variable appears in System or User variables. Repeat the above steps for each login environment variable.
3. To activate the changes, either log out and log in again or run the user profile command, for example: /home:. .profile Perl and Python wrapper scripts The wrapper scripts enable you to run SQL statements and script files using a single connection or multiple connections within Perl or Python programs. The Perl wrapper script is rmxci.pl, and the Python wrapper script is rmxci.py.
import os import sys ## Modify this path sys.path.append("/usr/home/rmxci/lib/python") import Session # create a session object sess = Session.Session() # Connect to the database x=sess.__connect__("super.services","password", "nsksys.ind.hp.com", "18650","TDM_Default_DataSource") # Execute sample queries # __execute takes the query string as parameter setSchema selectTable getStats = "set schema RMXCI.
A RMXCI commands The NonStop SQL/MX Remote Conversational Interface supports these commands in RMXCI or in script files. See the README for the HP NonStop SQL/MX Remote Conversational Interface for syntax, considerations, and examples regarding conditional statements. 76 Command Description @ Runs the SQL statements and RMXCI See the “@ command” (page 79). commands contained in a specified script file. This command is disallowed from Perl and Python programs. / Runs the previously run SQL statement.
Command Description Syntax OBEY Runs the SQL statements and RMXCI interface commands contained in a specified script file. See the “OBEY command” (page 96). PRUN Runs script files in parallel. See the “PRUN command” (page 99). QUIT Disconnects from and exits RMXCI. See the “QUIT command” (page 101). RECONNECT Creates a new connection to the SQL/MX database using the login credentials of the last successful connection. See the “RECONNECT command” (page 102). REPEAT Reruns a command.
Command Description Syntax SET TIME Causes the local time of the client workstation to be displayed as part of the interface prompt. See the “SET TIME command” (page 120). SET TIMING Causes the elapsed time to be displayed See the“SET TIMING command” after each SQL statement is run. (page 120). SHOW ACTIVITYCOUNT Functions as an alias of “SHOW RECCOUNT command” (page 130). SHOW ALIAS Displays all or a set of aliases available See the “SHOW ALIAS command” in the current RMXCI session.
Command Description Syntax SHOW TABLES Displays all or a set of tables that exist See the “SHOW SQLPROMPT in the current catalog and schema for command” (page 134). the RMXCI session. SHOW SQLTERMINATOR Displays the SQL statement terminator of the current session. See the “SHOW SQLTERMINATOR command” (page 134).
Considerations You must enter the command in one line. Example The following / command runs the previously run SELECT statement: SQL>select count(*) from persnl.employee; (EXPR) -------------------62 --- 1 row(s) selected. SQL>/ (EXPR) -------------------62 --- 1 row(s) selected. ALIAS command The alias command allows you to map a string to any RMXCI or SQL command. The syntax of the RMXCI or SQL command is checked when you run the mapped string.
rmxci-python.py rmxci.pl rmxci.py settings.pl settings.py The following command creates an alias named .GOTO for the goto command: SQL>alias .GOTO AS GOTO; SQL> .GOTO mylabel GOTO statement executed, ignoring all commands until a 'LABEL MYLABEL' command is encountered. The following command creates an alias named USE to perform the set schema operation, uses the alias to set the schema to MX.
password specifies the password of the user to log in to the database platform. If the password is not specified, RMXCI prompts for the password. hostname specifies the host name or IP address of the database platform for the client connection. If the hostname is not specified, the value is automatically used from the current RMXCI session. For specifying a port other than the default 18650, add : port number to hostname.
Considerations • If seconds or minutes is not specified, the default is seconds. • The maximum delay limit is 3600 seconds. You can override this value by setting rmxci.maxDelayLimit in _JAVA_OPTIONS. The unit is seconds for rmxci.maxDelayLimit.
Considerations 84 • You must enter the command in one line. • The env command displays the following attributes: COLSEP Current column separator, which controls the display of query results. For more information, see “SET COLSEP command” (page 107). DATASOURCE Name of the data source, which you entered when logging in to the database platform. For more information, see “Logging in to the database platform” (page 44).
Examples • The following command displays the attributes of the current session: SQL>env COLSEP " " DATASOURCE TDM_Default_DataSource HISTOPT DEFAULT [No expansion of script files] IDLETIMEOUT 30 min(s) LIST_COUNT 0 [All Rows] LOG OFF LOOK AND FEEL RMXCI MARKUP RAW PROMPT SQL> CATALOG NONSTOP_SYSTEM_NSK SCHEMA PUBLIC_ACCESS_SCHEMA SERVER nsksys.ind.hp.com:18650 SQLTERMINATOR ; STATISTICS OFF TIME OFF TIMING OFF USER super.
EXIT command The exit command disconnects from the database and exits the RMXCI session. The exit command can return a status code. If no status code is specified, zero is returned by default. In addition, a conditional statement can be appended to the command. Syntax EXIT [[WITH]{status}] [IF{condition}] status is any 1-byte integer. The status is a shell return value, and the range of allowable values is platform dependent. condition is the same as the condition parameter defined for the “IF...
Syntax FC [text | [-]number] text is the beginning text of a command in the history buffer. Case is not significant in matching the text to a command. [-]number is either a positive integer that is the ordinal number of a command in the history buffer or a negative integer that indicates the position of a command relative to the most recent command. Without text or number, fc retrieves the most recent command. Considerations • You must enter the command in one line.
SQL> • The following example corrects an SQL statement that you entered incorrectly by using the delete (D) editing command: SQL>select * from persnl.employee; *** ERROR[15001] A syntax error occurred at or before: selecct * from persnl.employee; ^ SQL>fc SQL>selecct * from persnl.employee; .... d SQL>select * from persnl.employee; .... Press Enter to run the corrected SELECT statement.
216 JONES 225 HELMSTED 232 SPINNER --- 5 row(s) selected. SQL> GET STATISTICS command The get statistics command returns formatted statistics for the last run SQL statement. Syntax GET STATISTICS Description of Returned Values: Records Accessed number of rows returned by disk process to EID (Executor In Disk process). Records Used number of rows returned by EID after selection. Disk IOs number of actual disk IOs performed by disk process.
--- SQL operation complete. GOTO command The goto command allows you to jump to a designated point later in the command list. The point in the command list is indicated by a label. All commands run after a goto command are ignored until the specified label is set. To set a label, use the “LABEL command” (page 93). The goto command is typically used with the IF...THEN command, for conditional execution of other commands. For IF...THEN command usage, see “IF...THEN command” (page 91) .
SQL>help • The following command lists all the set commands: SQL>help set • The following command shows help text for set idletimeout: SQL>help set idletimeout HISTORY command The history command displays the recently run commands, identifying each command by a number that you can use to rerun or edit the command. Syntax HISTORY [number] number is the number of commands to display. The default number is 10. The maximum number is 100. Considerations • You must enter the command in one line.
variable-name is one of: {LASTERROR|RECCOUNT|ACTIVITYCOUNT|ERRORCODE|[%]any ENV variable|any SQL parameter} value is any integer or a quoted string, where the quoted string is any nonquote character. \ is the optional escape character.
SQL> EXIT SQL> LABEL insertAlice SQL> INSERT INTO Employees(SSN, FName, LName) VALUES(987654321, 'Alice', 'Smith'); SQL> EXIT SQL> LABEL insertBob SQL> INSERT INTO Employees(SSN, FName, LName) VALUES(123456789, 'Bob', 'Smith'); SQL> EXIT LABEL command The label command marks a point in the command list that you can jump to by using the goto command. For more information, see the “GOTO command” (page 90). Syntax LABEL {label} label is a string of characters without quotes and spaces, or a quoted string.
rmxci.pl rmxci.py rmxci.sh SQL> LH mkdir /usr/home/rmxci -> Will create a directory /usr/home/rmxci On Linux, you can enter the following command to create a directory /usr/home/rmxci: SQL>LH mkdir /usr/home/rmxci On Windows, you can enter the following command to create a directory c:\rmxci: SQL>LH mkdir c:\rmxci • The following command displays the elapsed time information because the set timing command is set to on: SQL>set timing on SQL>localhost ls rmxci-perl.pl rmxci-python.py rmxci.cmd rmxci.
OFF stops the logging process. Considerations • You must enter the command in one line. • Use a unique name for each log file to avoid writing information from different RMXCI sessions into the same log file. Examples • The following command starts the logging process and records information to the sqlspool.lst file in the RMXCI install directory: SQL>log on • The following command starts the logging process and appends new information to an existing log file, persnl_updates.
900 300 500 400 250 420 600 200 SECRETARY SALESREP ACCOUNTANT SYSTEM ANALYST ASSEMBLER ENGINEER ADMINISTRATOR PRODUCTION SUPV --- 10 row(s) selected • The following command starts the logging process, clears existing information from the log file, specifies that no output appears on the console window, and the quiet option is enabled: SQL>log /usr/home/b.txt clear, cmdtext off, quiet SQL>select * +>from sch.toi.job; SQL> log off Output of /usr/home/b.
line for the next section (or the end of the script file). If you omit section-name, the obey command runs the entire script file. For more information, see “Section headers” (page 69). wild-card-pattern is a character string used to search for and display groups with names that match the character string. wild-card-pattern matches a string (depends on the OS for case-sensitivity) unless you enclose it within double quotes.
CNAME VARCHAR(22) CDESCP VARCHAR(25) CRED INT, CLABFEE NUMERIC(5,2), CDEPT VARCHAR(4) primary key (cno) ) ; NOT NULL, NOT NULL, NOT NULL, ?section insert INSERT INTO COURSE VALUES ('C11', 'INTRO TO CS','FOR ROOKIES',3, 100, 'CIS'); INSERT INTO COURSE VALUES ('C22', 'DATA STRUCTURES','VERY USEFUL',3, 50, 'CIS'); INSERT INTO COURSE VALUES ('C33', 'DISCRETE MATHEMATICS', 'ABSOLUTELY NECESSARY',3, 0,'CIS'); ?section select SELECT * FROM course; ?section delete purgedata course; To run only the commands in s
--- 1 row(s) inserted. The following command runs all files with .sql extension: SQL>obey /usr/home/rmxci/*.sql; SQL> obey /usr/home/rmxci The following command runs all files beginning with the word script and contains one character after the word script and ends with .sql extension. For example: script1.sql,script2.sql,scriptZ.sql and so on: SQL>obey /usr/home/rmxci/script?.sql The following command runs all files that contain the word test. This includes the files that do not end with .sql extension.
connections Enter a number for the maximum number of connections for the data sources. The data source must support the specified number of connections. Considerations • You must enter the command in one line. • If the prun command is run without any parameters, RMXCI prompts for the prun parameters. If one or more options are specified, the prun command runs without prompting for more input. In the noninteractive mode, if any options are not specified then the default value is used.
Total Total Total Total Total Total Total Total files present ......................99 files processed ....................99 sqls processed ....................198 errors ..............................0 warnings ............................0 warnings ............................0 connections .........................5 connection failures..................
condition is the same as the condition parameter defined for the “IF...THEN command” (page 91). See “Condition Parameter” (page 91). Considerations You must enter the command in one line. Examples • The following command disconnects from the database platform and exits RMXCI: SQL>quit • If the previously run command returns the error code 4082, the conditional exit command stops running the script file and then disconnects and exits from RMXCI. log /usr/home/rmxci/log/errorCode.
text specifies the text of the most recently run command. The command must have been run beginning with text, but text need be only as many characters as necessary to identify the command. RMXCI ignores leading blanks. [-]number is an integer that identifies a command in the history buffer. If number is negative, it indicates the position of the command in the history buffer relative to the current command; if number is positive, it is the ordinal number of a command in the history buffer.
------------------------------------------------------------EMPLIST MGRLIST SQL> RESET LASTERROR command The reset lasterror command resets the last error code to zero. Syntax RESET LASTERROR Considerations You must enter the command in one line. Examples The following command resets the last error in the current session: SQL>select * from emp; *** ERROR[4082]Object CAT.SCH.EMP does not exist or is inaccessible.
SQL>reset param ?sal SQL>set param ?sal 80000.00 For more information, see “Resetting the parameters” (page 64). RUN command The run command runs the previously run SQL statement. This command does not run a previously run RMXCI command. Syntax RUN Considerations You must enter the command in one line. Example The following command runs the previously run SELECT statement: SQL>select count(*) from persnl.employee; (EXPR) -------------------62 --- 1 row(s) selected.
Considerations • You must enter the command in one line. • If the specified file already exists, RMXCI appends new session history information to the file. Examples • The following command clears the contents of an existing file named history.txt in the local directory, and saves the session history in the file: SQL>savehist history.txt clear SQL> • The following command saves the session history in a file named hist.txt in the specified directory on a Linux client workstation: SQL>savehist .
Examples • The following example illustrates the usage of this command: SQL>create catalog cat11; --- SQL operation complete SQL> fc; SQL>create catalog cat12; --- SQL operation complete SQL>create schema cat1.
Examples • The following command specifies the separator as |(pipe): SQL>set colsep | SQL>show colsep COLSEP "|" SQL>select * from employee; EMPNUM|EMPNAME |REGNUM|BRANCHNUM|JOB ------|--------------|------|---------|-------1|ROGER GREEN | 99| 1|MANAGER 23|JERRY HOWARD | 2| 1|MANAGER 29|JACK RAYMOND | 1| 1|MANAGER 32|THOMAS RUDLOFF| 5| 3|MANAGER 39|KLAUS SAFFERT | 5| 2|MANAGER --- 5 row(s) selected.
Syntax SET HISTOPT [ALL|DEFAULT] Considerations You must enter the command in one line. Examples The following command shows only the obey command added to the history buffer. SQL> show histopt HISTOPT DEFAULT [No expansion of script files] SQL> obey /usr/home/scripts/nobey/insert2.sql SQL> ?section insert SQL> set schema cat1.sch; --- SQL operation complete. SQL> INSERT INTO COURSE1 VALUES +> ('C11', 'INTRO TO CS','FOR ROOKIES',3, 100,'CIS'); --- 1 row(s) inserted.
7> INSERT INTO COURSE1 VALUES ('C55','COMPUTER ARCH.','VON NEUMANN''S MACH.',3,100,'C IS'); SET IDLETIMEOUT command The set idletimeout command sets the idle timeout value for the current session. The idle timeout value of a session determines when the session expires after a period of inactivity. The default is thirty minutes. Syntax SET IDLETIMEOUT value value is a positive integer representing the idle timeout value in minutes.
Considerations • You must enter the command in one line. • To reset the number of displayed rows, enter the following command: set list_count 0 Examples • The following command specifies that the number of rows to be displayed by SELECT statements is five: SQL>set list_count 5 SQL>select empnum, first_name, last_name +>from persnl.
The supported options enable results to be displayed in XML,HTML,CSV(Comma Separated Values) and COLSEP formats. The default format is RAW. Considerations • You must enter the command in one line. • If the markup format is CSV or COLSEP, the column header information and status messages are not displayed. • If markup format is not specified, RMXCI selects the default format, RAW.
| 4082 | Object CAT.NVS.CUSTOMER does not exist or is inaccessible.93333 Martin Fowler 92222 Grady Booch 84758345 Judy Blume 89832473 Barbara Kingsolver After running set param commands in the session: • You can specify named parameters (?param-name) in a DML statement. • You can run a prepared statement with named parameters by using the execute statement without a using clause. • You can run a prepared statement with unnamed parameters by using the execute command with a using clause that contains either literal values or a list of the named parameters set by set param command or both. ------ --------------- -------------------- ------- ------- ---------72 GLENN THOMAS 3300 100 80000.00 --- 1 row(s) selected. SQL> NOTE: The named parameters, ?sal and ?job, in the SELECT statement are identical to the parameter names, sal and job, in the set param command. For more information, see “Setting parameters” (page 63). SET PROMPT command The set prompt command sets the prompt of the current RMXCI session to a specified string or to these session variables:%USER, %SERVER, %SCHEMA, %DATASOURCE. SQL>set prompt %server> nsksys.ind.hp. Considerations • You must enter the command in one line. string is a string value for the SQL terminator. The string may contain any characters except spaces. Spaces are disallowed even if you enclose the string in double quotes. Lowercase and uppercase characters are accepted, but the SQL terminator is always shown in uppercase. Considerations • You must enter the command in one line. • Do not include SQL or RMXCI reserved word as an SQL terminator. 420 ENGINEER 600 ADMINISTRATOR 200 PRODUCTION SUPV --- 11 row(s) selected. Start Time End Time Elapsed Time Compile Time Execution Time Table Name CAT.TOI.JOB 2007/09/18 21:45:34.082329 2007/09/18 21:45:34.300265 00:00:00.217936 00:00:00.002423 00:00:00.218750 Records Accessed 2 Records Used 2 Disk I/Os 0 Message Count 4 Message Bytes 15232 Lock Escl 0 Lock Wait 0 Disk Process Busy Time 363 SQL> For more information on STATISTICS, see the SQL/MX Release 3.x Reference Manual. Syntax SET TIMING [ ON | OFF ] ON specifies the elapsed time be displayed after each SQL statement is run. For a list of these statements, see Appendix B (page 142). OFF specifies that the elapsed time must not be displayed after each SQL statement is run. OFF is the default. Considerations • You must enter the command in one line. • The elapsed time value includes compile and execution time along with network I/O time and client-side processing time. wild-card-pattern is a character string used to search for and display aliases with names that match the character string. wild-card-pattern matches an uppercase string unless you enclose it within double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters. % Use a percent sign (%) to indicate zero or more characters of any type. For example, %art% matches SMART, ARTIFICIAL, and PARTICULAR but not smart or Hearts. SQL> show aliases .OS AS LH .GOTO AS GOTO USE AS SET SCHEMA SHOW CATALOG command The show catalog command displays the current catalog. Syntax SHOW CATALOG Considerations You must enter the command in one line. Examples The following command displays the current catalog: SQL> set catalog cat11 --- SQL operation complete. SQL> show catalog CATALOG CAT11 SHOW CATALOGS command The show catalogs command displays all the catalogs in the database, to which the RMXCI session is connected . Examples The following command displays all the catalogs: SQL> set catalog cat11 --- SQL operation complete. SQL> show catalogs SQL> SQL> CATALOG NAMES ----------------------------------------------------------------------------ACCMXCAT CAT CAT1 CAT11 The following command lists all the catalog names starting with the letter C. SQL> show errorcode ERRORCODE 29481 SHOW FETCHSIZE command The show fetchsize command displays the fetch size value for the current RMXCI session. Syntax SHOW FETCHSIZE Considerations You must enter the command in one line. Syntax SHOW IDLETIMEOUT Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. SQL>show lasterror LASTERROR 4082 SHOW LIST_COUNT command The show list_count command displays the maximum number of rows to be returned by SELECT statements in the current RMXCI session. The default is zero, which means that all rows are returned. Syntax SHOW LIST_COUNT Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. Syntax SHOW PARAM [param name] Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. • The command does not accept wildcard in param name. Example • The following command shows the parameters that are set for the current session: SQL>show param lastname GREEN dn 1500 sal 40000. Examples • The following commands show all the prepared statements, by default and, the tables that match the pattern s% and t%: SQL>show prepared S1 select * from s1 S2 select * from student T1 select * from test123 SQL> show prepared s% S1 select * from s1 S2 select * from student SQL> show prepared t% T1 select * from test123 SHOW PROCEDURES command The show procedures command displays the procedures (Stored Procedures in Java) in the current schema of the RMXCI session. Considerations • You must enter the command in one line. • If you do not specify a wildcard pattern, RMXCI displays all procedures that exist in the current schema. • If you specify a wildcard pattern, RMXCI displays only the procedure names that match the wildcard pattern. • If the set timing command is set to on, the elapsed time information appears. Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. Example The following command shows that the current schema of the session is PERSNL: SQL>show schema SCHEMA PERSNL For more information, see “Setting and showing the current schema” (page 58). SHOW SCHEMAS command The show schemas command displays all or a set of schemas that exist in the default catalog of the current RMXCI session. DEMOSCH1 DEV060525 HMGR HPNVS_SAMPLE ODBC_INVENT ODBC_SCHEMA PUBLIC_ACCESS_SCHEMA ROLEUSER SERVICES USR DEMOSCH2 DS_SCH HPNVS HPNVS_SAMPLE ODBC_PERSNL ODBC_TEST ROLEDBA SALES T4JDBC_SCHEMA DEMO_SCH D_SALES HPNVSSCH INVENT ODBC_SALES PERSNL ROLEMGR SCH TEST1 SQL> • The following command shows the schemas in the default catalog that have SALES in their names: SQL>show schemas %sales% SCHEMA NAMES -----------------------------------------------------------------------DBSCRIPT_SALES D_SALES ODBC_SALES SALE MARKUP Current markup option selected for the session. The default option is RAW. For more information, see “SET MARKUP command” (page 111). PROMPT Current prompt for the session. The default is SQL>. For more information, see “Customizing the standard prompt” (page 56) and “SET PROMPT command” (page 116). CATALOG Current catalog for the session. The default is NONSTOP_SYSTEM_NSK. For more information, see“SET CATALOG command” (page 106) . SCHEMA Current schema. The default is PUBLIC_ACCESS_SCHEMA. CATALOG CAT1 SCHEMA SCH1 SERVER test.acme.com:18650 SQLTERMINATOR ; STATISTICS OFF TIME OFF TIMING OFF USER sch1.usr SHOW SQLPROMPT command The show sqlprompt command displays the value of the SQL prompt for the current RMXCI session. Syntax SHOW SQLPROMPT Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. Syntax SHOW STATISTICS Considerations • You must enter the command in one line. • If the set timing command is set to on, the elapsed time information appears. Example The following commands show show statistics disabling and enabling: SQL>show statistics STATISTICS OFF SQL>set statistics on SQL>show statistics STATISTICS ON SHOW TABLES command The show tables command displays all or a set of tables that exist in the current catalog and schema for the RMXCI session. Examples • The following command shows all the tables in the current schema, PERSNL: SQL>show schema SCHEMA PERSNL SQL>show tables TABLE NAMES ------------------------------------------------------------DEPT EMPLOYEE JOB PROJECT SQL> • The following command shows the tables in the current schema, invent, that have part at the beginning of their names: SQL>show tables part% TABLE NAMES ------------------------------------------------------------PARTLOC PARTSUPP SQL> For more information, see “Showing the Example • The following command displays the elapsed time information because the set timing command is enabled: SQL>set timing on SQL>show time TIME OFF Elapsed :00:00:00.000 SHOW VIEWS command The show views command displays all or a set of views that exist in the current catalog and schema for the RMXCI session. Syntax SHOW VIEWS [wild-card-pattern] wild-card-pattern is a character string used to search for and display views with names that match the character string. SQL> • The following command shows the views in the current schema, invent, in which their names begin with, VIEW: SQL>show views view% VIEW NAMES ------------------------------------------------------------VIEW207 VIEW207N VIEWCS VIEWCUST SQL> For more information, see “Showing the views in a schema” (page 59). SPOOL command The spool command logs the entered commands and their output from RMXCI to a log file. This command is an alternative to the log command. Examples • The following command starts the logging process and records information in the sqlspool.lst file in the directory where RMXCI is running: SQL>spool on • The following command starts the logging process and appends new information to an existing log file, persnl_updates.log, in the directory where RMXCI is running: SQL>spool persnl_updates.log • The following command starts the logging process and appends new information to a log file, sales_updates. Output of /usr/home/tmp/a.txt ==================== JOBCODE JOBDESC ------- -----------------100 MANAGER 450 PROGRAMMER 900 SECRETARY 300 SALESREP 500 ACCOUNTANT 400 SYSTEM ANALYST 250 ASSEMBLER 420 ENGINEER 600 ADMINISTRATOR 200 PRODUCTION SUPV --- 10 row(s) selected • The following command starts the logging process, clears existing information from the log file, and instructs that no output appear on the console window: SQL>log /usr/home/tmp/b.txt clear, cmdtext off, quiet SQL>select * +>from cat.toi. Example • The following command shows versions of the database platform, NonStop JDBC Type 4 Driver and RMXCI: SQL>version NonStop SQLMX Version: 3. B Supported SQL statements RMXCI supports these SQL statements, SQL utilities, and other SQL-related commands. For more information about these statements, see the SQL/MX Release 3.x Reference Manual. 142 SQL Statement Description ALTER INDEX Modifies an SQL/MX index by changing one or more file attributes of the index. ALTER SQLMP ALIAS Changes the physical name of an SQL/MP table to which an existing alias is mapped. ALTER TABLE Adds a column to a table or renames a table. SQL Statement Description POPULATE INDEX Loads a specified index with data from a specified table. PREPARE Compiles an SQL statement for later use with the EXECUTE statement. PURGEDATA Deletes all data from a table and its related indexes. REVOKE Revokes access privileges for a table or view from specified users. REVOKE EXECUTE Revokes privileges for executing a stored procedure from specified roles. C Error messages RMXCI commands can return the following error messages. By default, the error messages are displayed in the RMXCI window. They are logged to a log file, if log command is used to log the output. 29400 Could not initialize the console reader. Cause: An Input Output Exception was received while initializing the Input Stream. Effect: RMXCI does not launch. Recovery: Retry launching RMXCI. Recovery: Verify that there is a valid NonStop JDBC Type 4 Driver in the location specified during RMXCI installation or reinstall RMXCI by selecting a valid driver. 29407 Failed to connect to the database. Connection limit exceeded. Cause: Database connection limit was exceeded. Effect: RMXCI fails to connect to the database and start a new session. Recovery: Re-configure the client data sources to increase the connection limit and retry the command. 29408 SET command must have an option specified. 29419 No views found. Cause: There were no views present in the current catalog and schema or there were no views that matched the specified pattern. Effect: The show views command does not return any entries. Recovery: Verify that you are in the correct catalog and schema by issuing an env or a show catalog and show schema commands. Set the correct catalog and schema. 29423 No procedures found. Effect: The statement fails to run. Recovery: To view the list of the prepared statements in the current session, run the show prepared command. 29432 Invalid number of parameters for prepared statement stmt name . Where stmt name is the statement name used in the prepare statement. Cause: An incorrect number of parameters were specified for executing a prepared statement. Effect: The statement fails to run. Recovery: Specify the correct number of parameters and retry the command. Recovery: Not applicable. 29439 The query buffer is empty. Cause: An attempt was made to repeat a previously run SQL statement using /,repeat or run commands, when there were no SQL statements in history buffer. Effect: None. Recovery: Not applicable. 29440 Directory not found or does not have READ/WRITE permissions. Cause: An invalid directory was specified for script files or log file in prun command. Effect: : The command fails to run. 29446 Number of connections entered is not within the allowable range. Cause: The number of connections specified for prun command was not within the range supported by the data source. Effect: The command fails to run. Recovery: Specify a valid value for the number of connections and retry the command. 29447 Incorrect value specified for overwrite option. Cause: An incorrect value was specified for -o or -overwrite option in prun command. Effect: The command fails to run. Cause: An invalid directory was specified for script files in prun command. Effect: The command fails to run. Recovery: Specify a valid directory and retry the command. 29459 Scripts Directory not found or does not have READ/WRITE permissions. Cause: An invalid directory was specified for the script files in prun command. Effect: The command fails to run. Recovery: Specify a valid directory and retry the command. Cause: An obey file with an infinite loop was run. This loop was caused by a nested obey either directly by obeying itself or indirectly (for example, by obeying another file, which in turn obeys the original). Effect: The obey command is halted. No other obey files are run and control returns to the session prompt. Recovery: Reduce the depth of nested obey and then run the script. Ensure that there are no loops in your nested obey calls. For example: Consider files A.sql, B.sql, and C.sql. You obey A. 29475 Invalid delay time specified. The value must be in the range 0-3600 seconds. Cause: An out of range value was specified for time in delay command. Effect: The command fails to run. Recovery: Specify a valid value for time and retry the command. 29478 Skipping command until a matching 'LABEL ' command is encountered. Cause: A goto command was run and all commands will be skipped until the matching label is found. Effect: This warning appears for all skipped commands. Recovery: Not applicable. 29484 Invalid fetch size value. The value must be in the range 0-2147483647. Cause: An invalid value was specified for the size in set fetchsize command. Effect: The command fails to run. Recovery: Specify a valid fetch size and retry the command. 29488 Invalid parameter value. Cause: An invalid parameter value was specified in set param command. Effect: The command fails to run. Recovery: Specify a valid parameter value and retry the command. Index Symbols D -DrmxciLF property, 33 -dsn parameter, 47 -h parameter, 47 -help, 48 -host parameter, 47 -noconnect parameter, 48 -p parameter, 47 -password parameter, 47 -q parameter description of, 48 examples of, 49, 50 -s parameter description of, 48 -script parameter, 48 -sql parameter, 48 -u parameter, 47 -user parameter, 47 -version, 48 / command example of, 61 syntax of, 79 @ command example of, 70 syntax of , 79 _JAVA_OPTIONS environment variable setting at a command-line prompt, 36 setting in th J JDBC driver installation, 19 verifying the version, 19 JDBC Type 4 driver see JDBC driver L LABEL command, 93 Launch files, location of, 32 Launch parameters descriptions of, 47 presetting on Linux, 44 lib directory, 32 Linux launch file, location of, 32 LOCALHOST command, 93 LOG command, 94 Log files, 67 PRUN operation, 99 Logging in default method, 44 using login parameters, 48 Logging output concurrent sessions, 67 script file execution, 71 starting, 66 stopping, 67 viewing a log file, 67 Login enviro running multiple files in parallel, 71 running one file at a time, 70 running when launching SQL/MX Remote Conversational Interface, 50 SELECT statement description of, 143 example of, 61 Session, 56 SESSION command, 132 SET COLSEP command, 106, 107 SET commands, in a script file, 50 SET FETCHSIZE command, 108 SET HISTOPT command, 108 SET IDLETIMEOUT command syntax of, 110 SET LIST_COUNT command, 110 SET MARKUP command, 111 SET PARAM command examples of, 63, 64 syntax of, 114 SET PROMPT command example of, DROP TRIGGER statement, 142 DROP VIEW statement, 142 EXECUTE statement, 142 EXPLAIN statement, 142 GRANT EXECUTE statement, 142 GRANT statement, 142 INSERT statement, 142 LOCK TABLE statement, 142 POPULATE INDEX utility, 143 PREPARE statement, 143 PURGEDATA utility, 143 REVOKE EXECUTE statement, 143 REVOKE statement, 143 ROLLBACK WORK statement, 143 SELECT ROW COUNT statement, 143 SELECT statement, 143 SET CATALOG statement, 143 SET MPLOC statement, 143 SET NAMETYPE statement, 143 SET SCHEMA, 143 SET TABLE |
