HP Neoview Command Interface (NCI) Guide HP Part Number: 531835-001 Published: April 2009 Edition: HP Neoview Release 2.
© Copyright 2009 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.
Table of Contents About This Document.......................................................................................................13 Intended Audience................................................................................................................................13 Document Organization.......................................................................................................................13 New and Changed Information in This Edition...................................
Showing the Session Attributes.......................................................................................................60 Setting and Showing the Idle Timeout Value for the Session.........................................................61 Customizing the Standard Prompt..................................................................................................61 Setting and Showing the SQL Terminator.......................................................................................
Syntax..............................................................................................................................................90 Considerations.................................................................................................................................91 Example...........................................................................................................................................91 CLEAR Command.....................................................
LABEL Command...............................................................................................................................103 Syntax.............................................................................................................................................103 Considerations...............................................................................................................................104 Examples................................................................
Examples........................................................................................................................................119 SET FETCHSIZE Command...............................................................................................................119 Syntax.............................................................................................................................................119 Considerations...............................................................
Examples........................................................................................................................................133 SHOW ALIAS Command...................................................................................................................133 Syntax.............................................................................................................................................134 Considerations..............................................................
Example.........................................................................................................................................142 SHOW PREPARED Command...........................................................................................................142 Syntax.............................................................................................................................................142 Considerations...................................................................
Considerations...............................................................................................................................154 Example.........................................................................................................................................154 SHOW VIEWS Command...................................................................................................................154 Syntax............................................................................
List of Figures 1-1 NCI Within a Neoview Platform Network...................................................................................
List of Tables 2-1 12 Locations of NCI Software Files....................................................................................................
About This Document This guide describes how to use the Neoview Command Interface (NCI) on a client workstation to manage a database on a Neoview data warehousing platform. NCI enables you to perform daily administrative tasks by running SQL statements interactively or from script files. Intended Audience This guide is intended for database administrators and support personnel who are maintaining and monitoring a Neoview database.
Chapter or Appendix New or Changed Information Chapter 4: Running Commands Interactively in the NCI Corrected an error in the example under “Running NCI With -help” (page 58). Chapter 6 (page 79) Removed some information and updated examples. Appendix A: NCI Commands This appendix describes these new or changed interface commands: • “ALIAS Command” (page 89) • “DELAY Command” (page 92) • “DISCONNECT Command” (page 93) • “EXIT Command” (page 96) • “GOTO Command” (page 100) • “IF...
{ } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...} A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Line Spacing If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library.
Neoview Performance Analyzer Online Help Context-sensitive help topics that describe how to use the Neoview Performance Analyzer to analyze and troubleshoot query-related issues on the Neoview data warehousing platform. Neoview Reports Online Help Help topics that describe how to use the HP Neoview Reports Tool. Neoview Repository User Guide Information about using the Repository, including descriptions of Repository views and guidelines for writing Neoview SQL queries against the views.
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to: docsfeedback@hp.com Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Introduction to NCI Neoview Command Interface (NCI) is a command-line interface that you download and install on a client workstation that has the Neoview JDBC Type 4 Driver installed. Operating systems that support the JDBC driver include Windows, Linux, and UNIX. The JDBC driver connects NCI on a client workstation to a Neoview database on a Neoview data warehousing platform.
2 Installing NCI To install NCI, follow these procedures: 1. Preinstallation procedures: • “Installing and Verifying the Java Runtime Environment (JRE)” (page 21) • “Installing and Verifying the Neoview JDBC Type 4 Driver” (page 24) • “Installing Perl or Python” (page 25) 2. Installation procedures: • “Downloading the Installer” (page 25) • “Verifying the Version of the Installer File” (page 26) • “Running the Installer” (page 27) 3.
C:\>java -version java version "1.4.2_10" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03) Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode) C:\> If the returned version is not supported or is unavailable, see: • • “Setting the PATH to a Supported Java Version on Windows” (page 22) “Setting the PATH to a Supported Java Version on Linux or UNIX” (page 24) Setting the PATH to a Supported Java Version on Windows 22 1.
4.
5. Place the cursor at the beginning of the Variable Value field and type the path of the Java bin directory, ending with a semicolon (;): For example: "C:\Program Files\Java\j2re1.4.2_10\bin"; NOTE: Check that no space is 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. 6. 7. 8. Click OK. Verify that the updated Path appears under System variables, and click OK.
1. Change the directory to the lib directory, which contains the JDBC driver JAR file: • On Windows, enter this command: cd jdbc-installation-directory\lib jdbc-installation-directory is the directory where you installed the JDBC driver. • On Linux or UNIX, enter this command: cd jdbc-installation-directory/lib jdbc-installation-directory is the directory where you installed the JDBC driver. 2. Enter this command to return version information: java -jar hpt4jdbc.
3. 5. Enter “Neoview Command Interface” in the search box in the upper right corner of the Software Depot home page. Click the HP Neoview Command Interface link that appears in the search results and download the HP Neoview Command Interface. The installer jar file hpnciInstaller.jar is available within the ZIP file hpnci.zip: 6. . Unzip the hpnci.zip file to the download directory as follows: 4. NOTE: In the File Download dialog box, make sure to select the Save or Save this file to disk option.
2. Enter this command to return version information: java -jar hpnciInstaller.jar v For example: C:\>cd download C:\download>java -jar hpnciInstaller.jar v T0774_N27(R2.4)_31MAR09_HP_NCI_2009_03_10 C:\download> Running the Installer NOTE: Before running the installer, you must install the Neoview JDBC Type 4 Driver on the client workstation. For more information, see “Preinstallation Procedures” (page 21).
1. Click Standard Installation to start the Installer Wizard. The Installer Wizard guides you through installing both the core NCI components and the optional open source extensions. For a quick installation of the core NCI files, click Core Components. If you have already installed the core NCI files, but want to install only the open source extensions, click on Optional Components. 2.
3. To locate the JDBC driver JAR file, click Browse next to HP Neoview JDBC Type 4 Driver JAR file: 4.
5. Select hpt4jdbc.
6. 7. 8. To install in the default location, proceed to Step 9. To install in a user preferred location, proceed to Step 7.
The Installer Wizard displays the directory where NCI will be installed. 9. 32 Click Next to review the open source disclaimer.
10. If you agree to the terms and conditions, click the checkbox and click Next. The Installer Wizard dialog box appears, showing which components are available for you to download and install: 11. Select the optional components to be downloaded and installed. Each optional component is installed if the component box is checked. Select the proxy information as necessary to download the open source extensions.
13. Click Detect Proxy Server(s) to start this process. If NCI detects one or more proxy servers, they are located in a drop-down menu next to the Detect Proxy Server(s) button. 14. Click Install to start the installation. After the core NCI files are installed, the Installation Status dialog box appears indicating how many files were extracted to the installation directory: 15. After the core NCI files are installed, the installer attempts to download and install the components.
After the core NCI files are installed, the Installer Wizard completes.
16. Click Exit. Command-Line Installation Steps 1. At a command prompt, change to the directory where you downloaded the installer: cd installer-directory installer-directory is the directory where you downloaded the installer. 2. Before launching the command-line installer, see the command options below: java -jar hpnciInstaller.
The command-line installer starts and prompts you to enter the type of installation. For a Standard Installation, type Y and then click Enter. To install the Optional Components, type N and then click Enter for the optional components only (proceed to Step 6 for optional components): NOTE: value. 4. All items in square brackets are default values. Press Enter to accept the default Enter the full directory path and file name of the JDBC driver JAR file, hpt4jdbc.
Use a proxy server? [N]: If you do not require a proxy server, proceed to Step 8. 8. NCI can attempt to detect your proxy settings. If proxy servers are found they are displayed: 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 wish to download and install. It also allows you to change the download URL.
Postinstallation Procedures • • • “Verifying the Installed Software Files” (page 39) “Verifying the Installed Version of NCI” (page 40) “Testing the Launch of NCI” (page 46) Verifying the Installed Software Files After downloading and running the installer file, hpnciInstaller.jar, verify that the NCI software files are installed in the correct locations. See Table 2-1 (page 39). Table 2-1 Locations of NCI Software Files Directory Files Description install>nci>bin hpnci.cmd Windows launch file.
Table 2-1 Locations of NCI Software Files (continued) Directory Files Description sampleTables.py Sample python file that lists all tables and respective rowcounts. The file can accepts a command-line wildcard argument. sampleDDL.py Sample python file that uses Jython to execute DDL statements. sampleDML.py Sample python file that uses Jython to execute DML statements. Verifying Code-Signed Files The installer file, hpnciInstaller.jar, and the product JAR file, hpnci.jar, are both code signed.
2. Enter the VERSION command to display information about the build versions: SQL>version Neoview Neoview Neoview Neoview Platform Connectivity Services JDBC Type 4 Driver Command Interface : : : : N02.04.25.0 T7970N27_30SEP2008_SRV_0106 T1249_N27(R2.4)_31MAR09_HP_JDBCT4_2009_03_10 T0774_N27(R2.4)_31MAR09_HP_NCI_2009_03_10 If not connected then NCI displays: SQL>version Neoview Neoview Neoview Neoview SQL> Platform Connectivity Services JDBC Type 4 Driver Command Interface : : : : N02.04.25.
(c) Copyright 2006-2009 Hewlett-Packard Development Company, LP. Connected to DataSource: TDM_Default_DataSource SQL>set schema sch; *** Schema has been set. *** Total elapsed time was 16 second(s). SQL>select * from book; *** Total Query time was 1 second. BOOKID ----------13333 11111 12222 BOOKTITLE BOOKAUTHORID ISCHECKEDOUT ------------------------------ ------------ -----------UML Simplified 93333 0 C++ Internals 91111 0 Object Oriented Design 92222 0 *** Query completed. 3 rows found.
3. If _JAVA_OPTIONS does not appear among the environment variables, click New under System or User variables. If _JAVA_OPTIONS already exists, click Edit.
4. Type _JAVA_OPTIONS for the Variable Name and the -DhpnciLF property value for the Variable Value, and click OK. The Variable Value must include: -DhpnciLF=look-and-feel-type look-and-feel-type is one of the “Supported Look-and-Feel Types” (page 41). For example: -DhpnciLF=SQLPlus 5. 44 Verify that the new or updated _JAVA_OPTIONS appears under System or User variables and click OK.
6. In the System Properties dialog box, click OK to accept the changes. To return to the default look and feel, Neoview SQL, set the -DhpnciLF property value to nci. Setting the Look and Feel in the User Profile on Linux or UNIX 1. Open the user profile (.profile or .bash_profile for the Bash shell) in the /home directory. For example: vi .profile 2. Add this export command (or a setenv command for the C shell) to the user profile.
set _JAVA_OPTIONS=-Dhpnci.enableTrace Testing the Launch of NCI 1. Launch NCI and verify that you can connect to the database. For instructions, see Chapter 3 (page 47). This window should appear: 2. 46 If you cannot connect to the database, verify that: 1. The database platform is available and running, the port number is correct for the database platform, and you are authorized to log in to that database platform.
3 Launching NCI This chapter describes how to launch NCI from the Windows, Linux, or UNIX environment: • • • • • • • “Launching NCI on Windows” (page 47) “Launching NCI on Linux or UNIX” (page 50) “Logging In to the Database Platform” (page 51) “Using Optional Launch Parameters” (page 54) “Launching NCI Without Connecting to the Database” (page 57) “Running NCI With -version” (page 58) “Exiting NCI” For information about launching NCI from Perl or Python, see Chapter 6 (page 79).
1. Right-click the desktop and select New > Shortcut: 2. Type the location of hpnci.cmd within double quotes (“) or click Browse to locate that file, and then click Next: For the location of the NCI software files, see Table 2-1 (page 39). 3.
4. If desired, specify optional launch parameters for the shortcut: a. Right-click the shortcut icon and select Properties: b. c. Click the Shortcut tab. In the Target box, insert a space after "...\Neoview Command Interface\bin\hpnci.
For more information, see “Using Optional Launch Parameters” (page 54). d. 5. Click OK. To launch NCI, double-click the shortcut icon. The Neoview Command Interface appears. If you did not set the optional launch parameters, NCI prompts you to enter the host name or IP address of the database platform, your user name, password, and a data source name. See “Logging In to the Database Platform” (page 51). Launching NCI on Linux or UNIX In the terminal window, enter: .
hpnci-installation-directory is the directory where you installed the NCI software files. For more information, see Table 2-1 (page 39). Check that no space is after the colon (:) in the path. NOTE: 3. In the C shell, use the setenv command instead of export. To activate the changes, either log out and log in again or execute the user profile. For example: . .profile 4. On the command line, execute the hpnci.sh file to launch NCI: hpnci.
4. Enter the name of a data source that is available and running (that is, started). If you do not enter a data source, NCI connects to Admin_Load_DataSource by default. Admin_Load_DataSource is the recommended data source for NCI connections. If Admin_Load_DataSource (or any data source that you specify) is not started, NCI returns an error and prompts you to close the session. If you specify a data source that does not exist, NCI returns a warning and connects to TDM_Default_DataSource instead.
SQL> • This example shows a valid host name. NCI prompts only for the user name and password: hpnci –u super.ser –p serv Welcome to the HP Neoview Command Interface 2.4 (c) Copyright 2006-2009 Hewlett-Packard Development Company, LP. Host Name/IP Address: 172.16.1.1 DataSource Name [Admin_Load_DataSource]: MyDataSource Invalid authorization specification User Name: super.services Password: Connected to DataSource: MyDataSource SQL> • This example shows a valid host name, user name, and data source.
SQL> At the prompt, you can enter an SQL statement or an NCI command. For more information, see Chapter 4 (page 59). Login Parameters Instead of the default method of logging in to the database platform, use the login parameters -h (or -host), -u (or -user), -p (or -password), -dsn, -q (or -sql), -s, (or -script), -noconnect, -version, and -help when launching NCI. For more information, see “Logging In When Launching NCI” (page 55).
Launch Parameter Description -version Displays the build version of NCI and the Neoview JDBC Type 4 Driver. Upon completion of the display, the client exits. If any other parameters are included with the -version parameter, they are ignored. For more information, see “Running NCI With -version” (page 58). -help Displays a list of accepted arguments with descriptions and then exits. For more information, see “Running NCI With -version” (page 58).
hpnci.cmd -q "create schema persnl" • On Linux or UNIX, in the terminal window, enter: cd hpnci-installation-directory/nci/bin ./hpnci.sh -q "create schema persnl" NOTE: Schema names that begin with HP_ are reserved for use by HP. For more information about reserved schema names, see the Neoview SQL Reference Manual.
For more information, see “Creating a Script File” (page 75). Example of Running a Script File With -s or -script • On Windows, in the Command Prompt window, enter: cd hpnci-installation-directory\Neoview Command Interface\bin hpnci.cmd -s settings.txt Specify the full path of the script file if it is outside the directory of hpnci.cmd. • On Linux or UNIX, in the terminal window, enter: cd hpnci-installation-directory/nci/bin ./hpnci.sh -s settings.
Running NCI With -version To display the build version of NCI and the Neoview JDBC Type 4 Driver, use the -version option. If other parameters are included with the -version parameter, they are ignored. Example of Running an NCI File With -version • On Windows, in the Command Prompt window, enter: cd hpnci-installation-directory\Neoview Command Interface\bin hpnci.cmd -version • On Linux or UNIX, in the terminal window, enter: cd hpnci-installation-directory/nci/bin ./hpnci.
4 Running Commands Interactively in the NCI After launching the Neoview Command Interface, you can run SQL statements and NCI commands in the interface.
To terminate an SQL statement that spans multiple lines, use the SQL terminator for the session. You can also include several SQL statements on the same command line provided that each one is terminated by the SQL terminator. For more information, see “Setting and Showing the SQL Terminator” (page 62). Case Sensitivity In the NCI interface, you can enter SQL statements and NCI interface commands in uppercase, lowercase, or mixed-case characters.
TIMING USER OFF role.dba SQL> For more information, see the “ENV Command” (page 94) or “SHOW SESSION Command” (page 146). Setting and Showing the Idle Timeout Value for the Session The idle timeout value of a session determines when the session expires after a period of inactivity. To set the idle timeout value of a session, enter the SET IDLETIMEOUT command.
Setting and Showing the SQL Terminator The SQL terminator symbolizes the end of an SQL statement. By default, the SQL terminator is a semicolon (;). To change the SQL terminator, enter the SET SQLTERMINATOR command. For example, this SET SQLTERMINATOR command sets the SQL terminator to a period (.): SQL>set sqlterminator . SQL>insert into sales.custlist +>(select * from invent.supplier +>where suppnum=8). --- 1 row(s) inserted.
Setting and Showing the Current Schema By default, the schema of the session is USR. The SQL statement, SET SCHEMA, allows you to set the schema for the NCI session. For example, this SET SCHEMA statement 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.
SQL>show schemas SCHEMA NAMES ----------------------------------------------------------------------------DBA001 DBA082 DBMGR DBSCRIPT_SALES DEFINITION_SCHEMA_VERSION_1200 DEMOSCH DEMOSCH1 DEMOSCH2 DEMO_SCH DEV060525 DS_SCH D_SALES HMGR HPNVS HPNVSSCH HPNVS_SAMPLE HPNVS_SAMPLE INVENT ODBC_INVENT ODBC_PERSNL ODBC_SALES ODBC_SCHEMA ODBC_TEST PERSNL PUBLIC_ACCESS_SCHEMA ROLEDBA ROLEMGR ROLEUSER SALES SCH SERVICES T4JDBC_SCHEMA TEST1 USR SQL> For more information, see the “SHOW SCHEMAS Command” (page 145).
Showing the Views in a Schema The SHOW VIEWS command displays the views that exist in the current schema. For example, this SHOW VIEWS command displays all the views in the current schema, INVENT: SQL>set schema invent; --- SQL operation complete. SQL>show schema SCHEMA INVENT SQL>show views VIEW NAMES ---------------------------------------------------------------VIEW207 VIEW207N VIEWCS VIEWCUST SQL> For more information, see the “SHOW VIEWS Command” (page 154).
Displaying Executed Commands To display commands that were recently executed in the NCI session, enter the HISTORY command. The HISTORY command associates each command with a number that you can use to reexecute or edit the command with the FC command. See “Editing and Reexecuting a Command” (page 66).
Running SQL Statements In the NCI, you can run SQL statements interactively. For a list of SQL statements that you can run interactively, see Appendix B (page 159). This subsection shows examples of: • • • • “Executing an SQL Statement” (page 67) “Repeating an SQL Statement” (page 67) “Preparing and Executing SQL Statements” (page 68) “Executing CREATE TRIGGER Statements” (page 71) To run SQL statements from script files in NCI, see Chapter 5 (page 75).
--- 11 row(s) selected. SQL> For more information, see the “/ Command” (page 89), “RUN Command” (page 117), or “REPEAT Command” (page 115). Preparing and Executing SQL Statements You can prepare, or compile, an SQL statement by using the PREPARE statement and later execute the prepared SQL statement by using the EXECUTE statement.
For the syntax of the PREPARE statement, see the Neoview SQL Reference Manual. Setting Parameters In a Neoview session, you can set a parameter of an SQL statement (either prepared or not) by using the SET PARAM command. NOTE: The parameter name is case-sensitive. If you specify it in lowercase in the SET PARAM command, you must specify it in lowercase in other statements, such as DML statements or EXECUTE.
SQL>set param ?sal 80000.00 SQL>show param dn 1500 sal 80000.00 SQL> For the syntax of the RESET PARAM command, see the “RESET PARAM Command” (page 116). Executing a Prepared SQL Statement To execute a prepared SQL statement, use the EXECUTE statement. For example, this EXECUTE statement executes the prepared empsal statement, which does not have any parameters: SQL>execute empsal; SALARY ---------137000.10 90000.00 75000.00 138000.40 56000.00 136000.00 80000.00 70000.00 175500.00 90000.00 118000.
GEORGE OTTO TIM TED PETER MARK HEIDI ROCKY SUE MARTIN HERBERT JESSICA STRICKER SCHNABL WALKER MCDONALD SMITH FOLEY WEIGL LEWIS CRAMER SCHAEFFER KARAJAN CRINER 3100 3200 3000 2000 3300 4000 3200 2000 1000 3200 3200 3500 --- 35 row(s) selected.
Starting the Logging Process To start logging, enter one of these commands: • • SPOOL ON or LOG ON SPOOL log-file or LOG log-file For more information, see the “LOG Command” (page 105) and the “SPOOL Command” (page 155). SPOOL ON or LOG ON Command The SPOOL ON or LOG ON command logs information about a session in the sqlspool.lst file, which NCI stores in the Neoview Command Interfacebin directory: • On Windows: hpnci-installation-directory\Neoview Command Interface\bin\sqlspool.
For example, this SPOOL OFF command stops logging in an NCI session: SQL>spool off Viewing the Contents of a Log File The log file is an ASCII text file that contains all the lines in NCI from the time you start logging to the time you stop logging. The logged lines include prompts, entered commands, output from commands, and diagnostic messages.
5 Running Scripts in NCI In NCI, you can run script files.
Example of a Script File This script file creates tables in the inventory schema: Running a Script File To run a script file in NCI, use the @ or OBEY command. The @ and OBEY commands run one script file at a time in the Neoview Command Interface. To run a script file when launching NCI, see “Running a Script File When Launching NCI” (page 56). For example, this @ command runs a script file, sch_invent.sql, that creates tables in the inventory schema: @C:\ddl_scripts\sch_invent.
--- SQL operation complete. SQL>-- CREATE TABLES/VIEWS in SCHEMA INVENT SQL>SET SCHEMA INVENT; --- SQL operation complete. SQL>CREATE TABLE INVENT.
6 Running NCI From Perl or Python You can execute SQL statements in Perl or Python by invoking the NCI Perl or Python wrapper script. To use the Perl or Python wrapper script, see: • • • “Setting the Login Environment Variables” (page 79) “Perl and Python Wrapper Scripts” (page 82) “Launching NCI From the Perl or Python Command Line” (page 82) These instructions assume that you installed the Neoview Command Interface product. For more information, see Chapter 2 (page 21).
Setting Login Environment Variables in the System Properties 80 1. Right-click the My Computer icon on your desktop, and then select Properties: 2. 3. In the System Properties dialog box, click the Advanced tab. Click the Environment Variables button: 4. In the Environment Variables dialog box, click New under System or User variables, whichever you prefer.
5. In the New User Variable dialog box, type the name of the login environment variable for the Variable Name and the required value for the Variable Value, and then click OK: 6. 7. 8. Verify that the environment variable appears under System or User variables. Repeat Step 4 to Step 6 for each login environment variable. After adding all three environment variables, click OK in the Environment Variables and System Properties dialog boxes to accept the changes.
setenv HPNCI_PERL_SERVER=absolute-path-of-JavaServer.jar setenv HPNCI_PYTHON_JSERVER=absolute-path-of-Jython.jar setenv HPNCI_PERL_JSERVER_PORT=portnumber Setting Login Environment Variables in the User Profile To set the login environment variables in the user profile: 1. Open the user profile (.profile or .bash_profile for the Bash shell) in the /home directory. For example: vi .profile 2. Add these export commands (or setenv commands for the C shell) to the user profile.
Example of a Perl Program (example.pl) use lib 'C:\\neo\\nci\\lib\\perl'; use Session; # create a session object $sess = Session->new(); # connect to the database $sess->connect("super.services","password","neo0101 ","18650","TDM_Default_DataSource"); $retval=$sess->execute(" set schema NEO.
A NCI Commands The Neoview Command Interface supports these commands in the NCI or in script files that you run in the Neoview Command Interface. For a list of NCI commands that are available only to HP support, see the Neoview Database Support Guide. See the README for the HP Neoview Command Interface for syntax, considerations, and examples regarding conditional statements. Command Description Syntax @ Runs the SQL statements and NCI commands contained in a specified script file.
Command Description LABEL Marks a point in the command history See the “LABEL Command” that you can jump to by using the (page 103). GOTO command. LOCALHOST Executes client machine commands. LOG Logs commands and output from NCI See the “LOG Command” (page 105). to a log file. MODE Determines the operating mode of the See the “MODE Command” current session to be either SQL for (page 107). database commands, CS for connectivity service commands, or NS for the Neoview service commands.
Command Description Syntax SET PROMPT Sets the prompt of the current session See the “SET PROMPT Command” to a specified string or to a session (page 127). variable. SET SQLPROMPT Sets the SQL prompt of the current See the “SET SQLPROMPT session to a specified string. The default Command” (page 129). is SQL. SET SQLTERMINATOR Sets the SQL statement terminator of See the “SET SQLTERMINATOR the current session to a specified string. Command” (page 130). The default is a semicolon (;).
Command Description Syntax SHOW PREPARED Displays the prepared statements in the current NCI session. See the “SHOW PREPARED Command” (page 142). SHOW PROCEDURES Displays the procedures (Stored Procedures in Java) in the current schema of the NCI session. See the “SHOW PROCEDURES Command” (page 143). SHOW RECCOUNT Displays the record count of the previous executed SQL statement. See the “SHOW RECCOUNT Command” (page 144).
SQL>@ddl.sql • This @ command runs the script file in the specified directory on a Windows workstation: SQL>@c:\my_files\ddl.sql • This @ command runs the script file in the specified directory on a Linux or UNIX workstation: SQL>@./my_files/ddl.sql / Command The / command executes the previously executed SQL statement. This command does not repeat an NCI command. Syntax / Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
command is an NCI command or Neoview SQL command. SQL-terminator is the default terminator (;) or a string value defined for the statement terminator by the “SET SQLTERMINATOR Command” (page 130). For more information, see “Setting and Showing the SQL Terminator” (page 62). Considerations • • • • • The ALIAS command can be used in all modes. The ALIAS command requires the SQL terminator. You must enter the ALIAS command on one line. The ALIAS command lasts only for the duration of the session.
Considerations • • You must enter the command on one line. The command does not require an SQL terminator. Only ROLE.MGR can execute this command. Example This ALLOW command gives access to the dumpfile. SQL>ALLOW SERVICES ACCESS TO DUMPFILES --- NS operation complete. CLEAR Command The CLEAR command clears the interface window so that only the prompt appears at the top of the window. CLEAR does not clear the log file or reset the settings of the session.
dsnName specifies the name of a data source. If the dsnName is not specified, the value is automatically used from the current NCI session. If NCI was invoked with the -noconnect launch parameter, you are prompted for a dsnName value. Considerations You must enter the command on one line. If NCI was invoked with the -noconnect launch parameter, NCI prompts you for the values.
This DELAY command puts the NCI session to sleep for 5 minutes before executing the next command, which is to exit the session: SQL>delay 5 mins SQL> exit DENY Command The DENY command revokes read access of memory dump files from super.services (HP support personnel). This command controls whether or not HP support personnel can read memory dumps. Syntax DENY [SERVICES] ACCESS TO DUMPFILES Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
ENV Command ENV displays attributes of the current NCI session. You can also use the SESSION and SHOW SESSION commands to perform the same function. Syntax ENV Considerations • • 94 You must enter the command on one line. The command does not require an SQL terminator. ENV displays these attributes: COLSEP Current column separator, which is used to control how query results are displayed. For more information, see “SET COLSEP Command” (page 118).
TIME Current setting (on or off) of the local time as part of the prompt. When this command is set to on, military time is displayed. By default, the local time is off. For more information, see “Customizing the Standard Prompt” (page 61) and “SET TIME Command” (page 131). TIMING Current setting (on or off) of the elapsed time. By default, the elapsed time is off. For more information, see “Displaying the Elapsed Time” (page 62) and “SET TIMING Command” (page 132).
EXIT Command The EXIT command disconnects from and exits NCI. EXIT 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. 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...THEN Command” (page 102).
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 on one line.
SQL> • Correct 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; .... Pressing Enter executes the corrected SELECT statement. • Correct an SQL statement that you entered incorrectly by using more than one editing command: SQL>selt * fromm persnl.
THOMAS SPINNER --- 5 row(s) selected. SQL>fc SQL>select first_name, last_name .... R empnum, SQL>select empnum, last_name .... SQL>from persnl.employee .... SQL>where jobcode=450; .... Pressing Enter lists the employee number and last names of all employees whose job code is 450: EMPNUM LAST_NAME -----180 215 216 225 232 -------------------CONRAD LANCASTER JONES HELMSTED SPINNER --- 5 row(s) selected.
Considerations This command can be executed in SQL mode. The command requires an SQL terminator. Examples SQL>select JOBCODE ------100 1234 450 900 300 500 400 250 420 600 200 * from job; JOBDESC -----------------MANAGER ENGINEER PROGRAMMER SECRETARY SALESREP ACCOUNTANT SYSTEM ANALYST ASSEMBLER ENGINEER ADMINISTRATOR PRODUCTION SUPV --- 11 row(s) selected. SQL> get statistics; Start Time End Time Elapsed Time Compile Time Execution Time Table Name NEO.TOI.JOB 2007/09/18 21:45:34.
SQL> SQL> SQL> SQL> GOTO “View Customers” SELECT * FROM Invoices; LABEL “View Customers” SELECT * FROM Customers; -- skipped HELP Command The HELP command displays help text for the interface commands that are supported in the current operating mode. Syntax HELP [command-name] command-name is the name of an interface command that is supported in the current operating mode. If you do not specify a command, NCI returns a list of all commands that are supported in the current mode.
Example Display the three most recent commands and use FC to redisplay one: SQL>history 3 14> set schema sales; 15> show tables 16> show views SQL>fc 14 SQL>set schema sales .... Now you can use the edit capabilities of FC to modify and execute a different SET SCHEMA statement. IF...THEN Command IF...THEN statements allow for the conditional execution of actions. If the condition is met, the action is executed; otherwise, no action is taken.
SQL Terminator The SQL terminator (SQL-terminator) is the default terminator (;) or a string value defined for the statement terminator by the “SET SQLTERMINATOR Command” (page 130). See “Setting and Showing the SQL Terminator” (page 62). Considerations • • IF...THEN is itself an action. Thus, nested IF...THEN statements are allowed. An action must end with the SQL terminator, even if the action is an interface command. Examples These commands show multiple examples of IF...
Considerations • You must enter the command on one line. Examples This command creates a label using a string of characters: SQL> LABEL MyNewLabel This command creates a label using a quoted string: SQL> LABEL "Neoview Label" LOCALHOST Command The LOCALHOST command allows you to execute client machine commands. Syntax LOCALHOST | LH Considerations • • • • You must enter the command on one line. The command does not require an SQL terminator.
hpnci.py hpnci.sh Elapsed :00:00:00.078 LOG Command The LOG command logs the entered commands and their output from NCI to a log file. If this is an obey script file, then the command text from the obey script file is shown on the console. Syntax LOG { ON [CLEAR, QUIET, CMDTEXT {ON | OFF}] | log-file [CLEAR, QUIET, CMDTEXT {ON | OFF}] | OFF } ON starts the logging process and records information in the sqlspool.lst file in the NCI bin directory. CLEAR instructs NCI to clear the contents of the sqlspool.
• This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Windows workstation: SQL>log c:\log_files\sales_updates.log • This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Linux or UNIX workstation: SQL>log ./log_files/sales_updates.
JOBCODE ------100 450 900 300 500 400 250 420 600 200 JOBDESC -----------------MANAGER PROGRAMMER SECRETARY SALESREP ACCOUNTANT SYSTEM ANALYST ASSEMBLER ENGINEER ADMINISTRATOR PRODUCTION SUPV --- 10 row(s) selected This command stops the logging process: SQL>log off For more information, see “Logging Output” (page 71).
• SHOW ALIAS • SHOW ALIASES • VERSION All other interface commands are disallowed in CS mode. NS specifies Neoview Service (NS) mode and supports the use of Neoview Workload Services (WMS) commands and a subset of the NCI commands. For a complete description of the NS commands, see the Neoview Workload Management Services Guide.
OBEY Command The OBEY command executes the SQL statements and NCI commands of a specified script file or an entire directory. This command accepts a single filename or a filename with a wildcard pattern specified. Executing the OBEY command without optional parameters prompts you to enter a filename. If a filename is not specified, then *.sql is used.
Examples • This OBEY command runs the script file from the local directory (the same directory where you are running NCI): SQL>obey ddl.sql • This OBEY command runs the script file in the specified directory on Windows. SQL>obey c:\my_files\ddl.sql • This OBEY command runs the script file in the specified directory on a Linux or UNIX workstation: SQL>obey ./my_files/ddl.
--- SQL Operation complete. To run only the commands in the insert section, execute the following : SQL>obey C:\Command Interfaces\course.sql (insert) SQL>?section insert SQL>INSERT INTO COURSE VALUES +> ('C11', 'INTRO TO CS','FOR ROOKIES',3, 100, 'CIS'); --- 1 row(s) inserted. SQL>INSERT INTO COURSE VALUES +> ('C22', 'DATA STRUCTURES','VERY USEFUL',3, 50, 'CIS'); --- 1 row(s) inserted.
scriptsdir In this directory, PRUN processes every file with the specified extension. If you do not specify a directory or if you specify an invalid directory, an error message occurs and you are prompted to reenter the directory. Note: Verify that this directory contains valid script files. extension The default is .sql. logsdir In this directory, PRUN creates a log file for each script file by appending the .log extension to the name of the script file.
Total Total Total Total Total Total sqls processed ......................40 errors ...............................4 warnings .............................0 successes ...........................36 connections ..........................5 connection failures...................0 Please verify the error log file c:\log\error\prun.err.log SQL> • This PRUN command initiates a parallel run operation with the -d option: SQL>prun -d SQL> prun -scriptsdir ./prun/sql –e sql –ld .
Syntax QUIT [WITH][status][IF{condition}] status is any 1-byte integer. 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...THEN Command” (page 102). See “Condition Parameter” (page 102). Considerations You must enter the command on one line. The command does not require an SQL terminator.
REPEAT Command The REPEAT command reexecutes a previous command. Syntax REPEAT [ text | [-]number ] text specifies the text of the most recently executed command. The command must have been executed beginning with text, but text need be only as many characters as necessary to identify the command. NCI ignores leading blanks. [-]number is an integer that identifies a command in the history buffer.
SQL> • Re-execute the SHOW TABLES command: SQL>repeat show show tables TABLE NAMES ------------------------------------------------------------DEPT EMPLOYEE JOB PROJECT SQL> RESET LASTERROR Command The RESET LASTERROR command resets the last error code to 0. Syntax RESET LASTERROR Considerations • You must enter the command on one line. The command does not require an SQL terminator. Examples This command resets the last error in the current session: SQL>select * from emp; *** ERROR[4082]Object NEO.
Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. To clear several parameter values but not all, you must use a separate RESET PARAM command for each parameter. Example This command clears the setting of the ?sal (salary) parameter, and the SET PARAM command resets it to a new value: SQL>reset param ?sal SQL>set param ?sal 80000.00 For more information, see “Resetting the Parameters” (page 69).
Syntax SAVEHIST file-name [CLEAR] file-name is the name of a file into which NCI stores the session history. If you want the history file to exist outside the local directory where you launch NCI (by default, the Neoview Command Interface bin directory), specify the full directory path of the history file. The specified directory must exist before you execute the SAVEHIST command. CLEAR instructs NCI to clear the contents of the specified file before adding the session history to the file.
Examples • This command specifies the separator as a “|”(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. SET FETCHSIZE Command The SET FETCHSIZE command allows you to change the default fetchsize used by JDBC.
Syntax SET HISTOPT [ALL|DEFAULT] Considerations You must enter the command on one line. Examples This command shows only the obey commands added to the history buffer. SQL> show histopt HISTOPT DEFAULT [No expansion of script files] SQL> obey e:\scripts\nobey\insert2.sql SQL> ?section insert SQL> set schema neo.sch; --- SQL operation complete. SQL> INSERT INTO COURSE1 VALUES +> ('C11', 'INTRO TO CS','FOR ROOKIES',3, 100,'CIS'); --- 1 row(s) inserted.
6> 7> INSERT INTO COURSE1 VALUES ('C11','INTRO TO CS','FOR ROOKIES',3, 100, 'CIS'); INSERT INTO COURSE1 VALUES ('C55','COMPUTER ARCH.','VON NEUMANN''S MACH.',3,100,'CIS'); 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 30 minutes. Syntax SET IDLETIMEOUT value value is an integer representing the idle timeout value in minutes.
Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. To reset the number of displayed rows, enter this command: SET LIST_COUNT 0 Examples • This 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 format. The default format is RAW. Considerations • • • You must enter the command on one line. If the MARKUP format is CSV or COLSEP, the column header information and status messages are not displayed. For the XML and HTML markup format, the syntax and interface errors have been reformatted so that consistent XML and HTML markup is displayed.
| Error Id | Error Code | Error Message |
| 1 | 4082 | Object NEO.NVS.CUSTOMER does not exist or is inaccessible. |
• To set the application to format output as HTML: SQL>set markup HTML HTML formatted error message example: SQL>set markup John Steinbeck 2323423 Irwin Shaw 93333 Martin Fowler 92222 Grady Booch 84758345 Judy Blume 89832473 Barbara Kingsolver
• Named parameter (represented by ?param-name) in a DML statement or in a prepared SQL statement • Unnamed parameter (represented by ?) in a prepared SQL statement only A prepared statement is one that you SQL compile by using the PREPARE statement. For more information about PREPARE, see the Neoview SQL Reference Manual. After running SET PARAM commands in the session: • You can specify named parameters (?param-name) in a DML statement.
SQL>select * from persnl.employee where salary = ?sal and jobcode = ?job; EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY ------ --------------- -------------------- ------- ------- ---------72 GLENN THOMAS 3300 100 80000.00 --- 1 row(s) selected. SQL> NOTE: The names of 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 69).
SQL>set prompt Enter> ENTER> • This SET PROMPT command sets the CS prompt to #: ENTER>mode cs CS#set prompt # # • To reset the CS prompt to the default, enter this SET PROMPT command: #set prompt CS# • To reset the SQL prompt to the default, enter this SET PROMPT command: CS#mode sql ENTER>set prompt SQL> • This command displays the session user name for the prompt: SQL>set prompt %user> dba1> • This command displays the operating mode of the session for the prompt: SQL>set prompt %mode: SQL: •
SET SQLPROMPT Command The SET SQLPROMPT command sets the SQL prompt of the current session to a specified string. The default is SQL>. Syntax SET SQLPROMPT [string] [%USER] [%MODE] [%SERVER] [%SCHEMA] [%DATASOURCE] string is a string value to be displayed as the SQL prompt. The string may contain any characters. Spaces are allowed if you enclose the string in double quotes. If you do not enclose the string in double quotes, the prompt is displayed in uppercase.
SQL>set sqlprompt %server> neo0101.mylab.mycorp.net:18650> • This command displays the session schema for the prompt: SQL>set sqlprompt "Schema %schema:" Schema USR: • This command displays the session data source for the prompt: SQL>set sqlprompt "%datasource SQL>" TDM_Default_DataSource SQL> • This command displays multiple session variables: SQL>set sqlprompt %USER@%SCHEMA> super.
This query ends with the new terminator, go: SQL>select * from persnl.employee go • To reset the SQL terminator to the default, enter this command: SQL>set sqlterminator ; For more information, see “Setting and Showing the SQL Terminator” (page 62). SET STATISTICS Command The SET STATISTICS command automatically retrieves the statistics information for a query being executed. The results returned are the same as would have been returned if the GET STATISTICS command was executed.
Syntax SET TIME { ON[12H] | OFF } ON specifies that the local time be displayed as part of the prompt. OFF specifies that the local time not be displayed as part of the prompt. OFF is the default. Considerations • • You must enter the command on one line. The command does not require an SQL terminator. The default is a 24–hour military style display. The additional argument of 12h allows the time to be displayed in a 12–hour AM/PM style.
Examples • This command displays the elapsed time of SQL statements: SQL>set timing on • This command turns off the elapsed time: SQL>set timing off For more information, see “Displaying the Elapsed Time” (page 62). SHOW ACCESS Command The SHOW ACCESS command displays the current status of access information to memory dump files. Syntax Syntax -----SHOW ACCESS TO DUMPFILES Considerations • You must enter the command on one line. The command does not require an SQL terminator.
Syntax SHOW ALIAS [alias-name | wild-card-pattern] alias-name is any alias name that is used with the ALIAS command. See “ALIAS Command” (page 89). 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.
Syntax SHOW ALIASES Considerations • You must enter the command on one line. The command does not require an SQL terminator. Examples This command displays all the aliases in the current NCI session: SQL> SHOW ALIASES .OS AS LH .GOTO AS GOTO USE AS SET SCHEMA SHOW COLSEP Command The SHOW COLSEP command displays the value of the column separator for the current NCI session. Syntax SHOW COLSEP Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
Syntax SHOW ERRORCODE Examples This command displays the error of the last SQL statement that was executed: SQL> SHOW ERRORCODE ERRORCODE 29481 SHOW FETCHSIZE Command The SHOW FETCHSIZE command displays the fetch size value for the current NCI session. Syntax Syntax -----SHOW FETCHSIZE Considerations • • You must enter the command on one line. This command is supported in all modes.
SQL>show histopt HISTOPT DEFAULT [No expansion of script files] SQL>set histopt all SQL>show histopt HISTOPT ALL SHOW IDLETIMEOUT Command The SHOW IDLETIMEOUT command displays the idle timeout value of the current NCI session. The idle timeout value of a session determines when the session expires after a period of inactivity. The default is 30 minutes. Syntax SHOW IDLETIMEOUT Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. SHOW LASTERROR can be executed in all modes. If the SET TIMING command is set to ON, the elapsed time information is displayed. Examples This command shows the last error in the current session: SQL>select * from emp; *** ERROR[4082]Object NEO.SCH.EMP does not exist or is inaccessible.
Considerations • • You must enter the command on one line. If the SET TIMING command is set to ON, the elapsed time information is displayed. Examples This command displays the value set for the markup option: SQL>show markup MARKUP RAW Elapsed time:00:00:00:078 SHOW MODE Command The SHOW MODE command displays the operating mode of the current NCI session. The default is SQL. Syntax SHOW MODE Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
_ Use an underscore (_) to indicate any single character. For example, boo_ matches BOOK and BOOT but not BOO or BOOTS. "boo_" matches book and boot but not boo or boots. ? Use a question mark (?) to indicate any single character. For example, boo? matches BOOK and BOOT but not BOO or BOOTS. "boo?" matches book and boot but not boo or boots. Considerations • • • • • You must enter the command on one line. The command does not require an SQL terminator.
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. "%art%" matches smart and Hearts but not SMART, ARTIFICIAL, or PARTICULAR. * Use an asterisk (*) to indicate zero or more characters of any type.
Syntax SHOW PARAM Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. If the SET TIMING command is set to ON, the elapsed time information is displayed. Example • This command shows that parameters that are set for the current session: SQL>show param lastname GREEN dn 1500 sal 40000.
Examples • This command shows all the prepared statements, by default: SQL>show prepared S1 select * from t1 S2 select * from student T1 select * from test123 SQL> show prepared s% S1 select * from t1 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 NCI session. If a pattern is specified, all procedures matching the pattern are displayed.
Considerations • • • • • You must enter the command on one line. The command does not require an SQL terminator. The SHOW PROCEDURES command can be executed in SQL mode only. If you do not specify a wild-card pattern, NCI displays all procedures that exist in the current schema. If you specify a wild-card pattern, NCI displays only the procedure names that match the wild-card pattern. If the SET TIMING command is set to ON, the elapsed time information is displayed.
Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. If the SET TIMING command is set to ON, the elapsed time information is displayed. Example This 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 63).
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> • This 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 SALES SQL>
MODE Current operating mode of the session. The default mode is SQL. The prompt for NS mode is NS%. For more information, see “MODE Command” (page 107). PROMPT Current prompt for the session. In SQL mode, the default is SQL>. In CS mode, the default is CS#. For more information, see “Customizing the Standard Prompt” (page 61) and “SET PROMPT Command” (page 127). ROLE Current role for the session. For more information about user-ids and roles, see the Neoview Database Administrator's Guide.
LOG LOOK AND FEEL MARKUP MODE PROMPT ROLE SCHEMA SERVER SERVICE NAME SQLTERMINATOR STATISTICS TIME TIMING USER OFF NVS RAW SQL SQL > DBA USR neo0101.acme.com:18650 HP_DEFAULT_SERVICE ; OFF OFF OFF role.dba SHOW SQLPROMPT Command The SHOW SQLPROMPT command displays the value of the SQL prompt for the current NCI session. Syntax SHOW SQLPROMPT Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode.
SHOW STATISTICS Command The SHOW STATISTICS command displays if statistics has been enabled or disabled for the current session. Syntax SHOW STATISTICS Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. The SHOW STATISTICS command works in all modes. If the SET TIMING command is set to ON, the elapsed time information is displayed.
• • • If you do not specify a wild-card pattern in a SHOW SYNONYMS command, NCI displays all the synonyms that exist in the current schema. If you specify a wild-card pattern in a SHOW SYNONYMS command, NCI displays only the synonym names that match the wild-card pattern. If the SET TIMING command is set to ON, the elapsed time information is displayed.
INDEXES displays information about the indexes associated with the specified table or materialized view.
SQL> • This command shows information about the synonyms of the CHANNELS table: SQL>show table channels, synonyms SYNONYM NAME --------------MYSCH.
_ Use an underscore (_) to indicate any single character. For example, boo_ matches BOOK and BOOT but not BOO or BOOTS. "boo_" matches book and boot but not boo or boots. ? Use a question mark (?) to indicate any single character. For example, boo? matches BOOK and BOOT but not BOO or BOOTS. "boo?" matches book and boot but not boo or boots. Considerations • • • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode.
SHOW TIMING Command The SHOW TIMING command displays whether the setting for the elapsed time is ON or OFF. Syntax SHOW TIMING Considerations • • You must enter the command on one line. The command does not require an SQL terminator. If the SET TIMING command is set to ON, the elapsed time information is displayed. Example • This 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.
• • If you specify a wild-card pattern in a SHOW VIEWS command, NCI displays only the view names that match the wild-card pattern. If the SET TIMING command is set to ON, the elapsed time information is displayed.
log-file CLEAR instructs NCI to clear the contents of the specified log-file before logging new information to the file. OFF stops the logging process. Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. Use a unique name for each log file to avoid writing information from different NCI sessions into the same log file. This command works in all modes. Examples • This command starts the logging process and records information to the sqlspool.
SQL>select * from neo.toi.job +>; 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. SQL> log off Output of c:\temp\a.
For more information, see “Logging Output” (page 71). VERSION Command The VERSION command displays the build versions of the Neoview platform, Neoview Connectivity Services, Neoview Command Interface and the Neoview JDBC Type 4 Driver. Syntax VERSION Considerations You must enter the command on one line. The command does not require an SQL terminator.
B Supported SQL Statements NCI supports these SQL statements, SQL utilities, and other SQL-related commands. For more information about these statements, see the Neoview SQL Reference Manual. For a list of statements that are available only to HP support, see the Neoview Database Support Guide. SQL Statement Description ALTER MVGROUP Adds or removes a materialized view to or from a materialized view group. ALTER SYNONYM Alters the synonym of a specified table.
SQL Statement Description EXECUTE Executes an SQL statement previously compiled by a PREPARE statement. EXPLAIN Helps you review query execution plans. GRANT Grants access privileges for a table or view to specified users. GRANT EXECUTE Grants privileges for executing a stored procedure in Java (SPJ) to specified roles. GRANT SCHEMA Grants access privileges for a schema to specified roles. INSERT Inserts rows of data into a table or view.
C Connectivity Service Commands NCI supports connectivity service commands in the connectivity service (CS) mode. You can execute connectivity service commands interactively or in script files that you run in CS mode. For information about changing to CS mode, see the “MODE Command” (page 107). Any user can execute the “INFO DS Command” (page 161). Other connectivity service commands are available only to HP support. For information about those commands, see the Neoview Database Support Guide.
AvailSrvrCnt Available servers for this service on this data source (registered minus connected servers) InitSrvrCnt Number of idle servers to start when data source starts SrvrIdleTimeout Number of minutes a server waits in the available state before stopping itself ConnIdleTimeout Number of minutes a client server connection remains idle before the server terminates LastUpdated Date and time of the last update of the component's state, in client's local time.
D Neoview Service (NS) Commands NCI supports Neoview Workload Management Services (WMS) commands and a subset of NCI commands in the Neoview Service (NS) mode. For information about the available WMS commands, see the Neoview Workload Management Services Guide. Some WMS and NCI commands are available only to HP support. For information about those commands, see the Neoview Database Support Guide. You can execute Neoview Service commands interactively or in script files that you run in NS mode.
Index Symbols -DhpnciLF property, 41 -dsn parameter, 54 -h parameter, 54 -help, 55 examples of, 58 -host parameter, 54 -noconnect examples of, 57 -noconnect parameter, 54 -p parameter, 54 -password parameter, 54 -q parameter description of, 54 examples of, 55, 56 -s parameter description of, 54 examples of, 57 -script parameter, 54 -sql parameter, 54 -u parameter, 54 -user parameter, 54 -version, 55 examples of, 58 / command example of, 67 syntax of, 89 @ command example of, 76 syntax of, 88 _JAVA_OPTIONS e
hpnci.jar, location of, 39 hpnci.pl description of, 82 location of, 39 hpnci.py description of, 82 location of, 39 hpnci.
running, 82 Perl wrapper script description of, 82 location of, 39 POPULATE INDEX utility description of, 160 Port number, default, 51 PREPARE statement description of, 160 examples of, 68 Procedures wild-card search, 143 Product banner, 59 Prompts continuation, 59 standard, 59 PRUN syntax, 111 Python command line invoking Neoview Command Interface, 82 Python program running, 82 Python wrapper script description of, 82 location of, 39 Q QUIT command, 113 R RECONNECT command, 114 REORG command description
SHOW SCHEMA command example of, 63 syntax of, 144 SHOW SCHEMAS command example of, 63 syntax of, 145 SHOW SESSION command, 146 SHOW SQLPROMPT command, 148 SHOW SQLTERMINATOR command, 148 SHOW STATISTICS command, 149 SHOW SYNONYMS command example of, 65 syntax of, 149 SHOW TABLE command example of, 64 syntax of, 150 SHOW TABLES command example of, 64 syntax of, 152 SHOW TIME command, 153 SHOW TIMING command, 154 SHOW VIEWS command example of, 65 syntax of, 154 Signature, verifying, 40 SPOOL command examples
U UNIX launch file, location of, 39 UPDATE STATISTICS statement description of, 160 User profile setting _JAVA_OPTIONS, 45 setting login environment variables, 82 setting the PATH, 24, 50 Utilities, 159 V VERSION command, 158 Views showing all views in a schema, 65 wild-card search, 154 W Wild-card characters, 109, 139 Wild-card search materialized views, 140 procedures, 143 schemas, 145 synonyms, 149 tables, 152 views, 154 Windows launch file, location of, 39 169
