HP Neoview Command Interface (NCI) Guide HP Part Number: 544808-001 Published: April 2008 Edition: HP Neoview Release 2.
© Copyright 2008 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.......................................................................................................15 Intended Audience................................................................................................................................15 New and Changed Information in This Edition...................................................................................15 Document Organization.......................................................................
Setting and Showing the Idle Timeout Value for the Session.........................................................63 Customizing the Standard Prompt..................................................................................................63 Setting and Showing the SQL Terminator.......................................................................................64 Displaying the Elapsed Time...........................................................................................................
Syntax..............................................................................................................................................93 Considerations.................................................................................................................................93 Example...........................................................................................................................................93 CLEAR Command.....................................................
OBEY Command.................................................................................................................................108 Syntax.............................................................................................................................................108 Considerations...............................................................................................................................108 Examples...............................................................
Examples........................................................................................................................................121 SET PARAM Command......................................................................................................................124 Syntax.............................................................................................................................................124 Considerations............................................................
Considerations...............................................................................................................................134 Examples........................................................................................................................................134 SHOW MODE Command...................................................................................................................135 Syntax.............................................................................
Syntax.............................................................................................................................................145 Considerations...............................................................................................................................146 Examples........................................................................................................................................146 SHOW TABLE Command..................................................
List of Figures 1-1 NCI Within a Neoview Platform Network...................................................................................
List of Tables 2-1 Locations of NCI Software Files....................................................................................................
About This Document This manual 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 manual is intended for database administrators and support personnel who are maintaining and monitoring a Neoview database.
Document Organization Chapter 1: Introduction to NCI Introduces NCI and describes its capabilities. Chapter 2: Installing NCI Describes how to install NCI on the client workstation. Chapter 3: Launching NCI Describes how to launch, log in to, and exit the NCI on a client workstation. Chapter 4: Running Commands Interactively in the NCI Describes how to run commands interactively in the NCI. Chapter 5: Running Scripts in NCI Describes how to run script files in the NCI.
{ } 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 Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
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 23) • “Installing and Verifying the Neoview JDBC Type 4 Driver” (page 26) • “Installing Perl or Python” (page 27) 2. Installation procedures: • “Downloading the Installer” (page 27) • “Verifying the Version of the Installer File” (page 28) • “Running the Installer” (page 29) 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 24) “Setting the PATH to a Supported Java Version on Linux or UNIX” (page 26) Setting the PATH to a Supported Java Version on Windows 24 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_N26_AAD(R2.3)_15FEB08_HP_NCI_2007_12_18 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 23).
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. 34 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 41) “Verifying the Installed Version of NCI” (page 41) “Testing the Launch of NCI” (page 47) 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 41). Table 2-1 Locations of NCI Software Files Directory Files Description install>nci>bin hpnci.cmd Windows launch file.
1. Change to the lib directory, which contains the NCI JAR file: • On Windows, enter: cd hpnci-installation-directory\Neoview Command Interface\lib hpnci-installation-directory is the directory where you installed the NCI software files. • On Linux or UNIX, enter: cd hpnci-installation-directory/nci/lib hpnci-installation-directory is the directory where you installed the NCI software files. 2. Enter this command to return version information: java -jar hpnci.
SQL>create view persnl.salarylist +>as select salary from persnl.employee; --- SQL operation complete. SQL> To display the SQL status messages in SQLPlus look and feel, the -DhpnciLF property value is set to SQLPlus. The SQLPlus look and feel appears as shown below: Picked up _JAVA_OPTIONS: -DhpnciLF=SQLPlus Welcome to the HP Neoview Command Interface 2.3 (c) Copyright 2007, 2008 Hewlett-Packard Development Company, LP. Connected to DataSource: TDM_Default_DataSource SQL>create view persnl.
Setting the Look and Feel at a Command-Line Prompt To set the _JAVA_OPTIONS environment variable for each session at a command-line prompt: • On Windows, enter this command: set _JAVA_OPTIONS=-DhpnciLF=look-and-feel-type look-and-feel-type is one of the “Supported Look-and-Feel Types” (page 42).
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 42). For example: -DhpnciLF=SQLPlus 5.
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 49). This window should appear: 2. 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 49) “Launching NCI on Linux or UNIX” (page 52) “Logging In to the Database Platform” (page 53) “Using Optional Launch Parameters” (page 56) “Launching NCI Without Connecting to the Database” (page 59) “Running NCI With -version” (page 60) “Exiting NCI” For information about launching NCI from Perl or Python, see Chapter 6 (page 81).
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 41). 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 56). 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 53). 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 41). 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.3 (c) Copyright 2007, 2008 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 61). 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 57).
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 60). -help Displays a list of accepted arguments with descriptions and then exits. For more information, see “Running NCI With -version” (page 60).
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" After you enter the SQL statement, NCI launches and prompts you to log in by default (if you did not specify -h, -u, -p, and -dsn on the command line), runs the SQL statement, and then returns to the command prompt: Host Name/IP Address: neo0101.mylab.mycorp.
For more information, see “Creating a Script File” (page 77). 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 64). 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 95) or “SHOW SESSION Command” (page 142). 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 141).
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 150).
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 68).
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 155). This subsection shows examples of: • • • • “Executing an SQL Statement” (page 69) “Repeating an SQL Statement” (page 69) “Preparing and Executing SQL Statements” (page 70) “Executing CREATE TRIGGER Statements” (page 73) To run SQL statements from script files in NCI, see Chapter 5 (page 77).
--- 11 row(s) selected. SQL> For more information, see the “/ Command” (page 92), “RUN Command” (page 116), or “REPEAT Command” (page 114). 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 115). 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 104) and the “SPOOL Command” (page 151). 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 58). 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 an SQL statement 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 81) “Perl and Python Wrapper Scripts” (page 84) “Launching NCI From the Perl or Python Command Line” (page 84) “Launching NCI From a Perl or Python Program” (page 86) These instructions assume that you installed the Neoview Command Interface product.
Setting Login Environment Variables in the System Properties 82 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 four login environment variables, click OK in the Environment Variables and System Properties dialog boxes to accept the changes.
export HPNCI_PASSWORD=password export HPNCI_DATASOURCE=data-source-name At each command prompt in the C shell, enter one of these commands: setenv setenv setenv setenv HPNCI_SERVER=host-name:port-number HPNCI_USER=user-name HPNCI_PASSWORD=password HPNCI_DATASOURCE=data-source-name 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.
You can pass only one SQL statement at a time on the Perl or Python command line. The SQL statement must: • • • Be enclosed in double quotes (") without the SQL terminator (;) Contain fully qualified database object names (for example, neo.schema-name.obj-name) Contain the syntax of one of the supported SQL statements. See Appendix B (page 155). See “Perl and Python Commands on Windows” (page 85) and “Perl and Python Commands on Linux or UNIX” (page 85).
The command returns this output: EMPNUM -----1 23 29 32 ... FIRST_NAME --------------ROGER JERRY JANE THOMAS LAST_NAME DEPTNUM JOBCODE SALARY -------------- ------- ------- -------GREEN 9000 100 175500.00 HOWARD 1000 100 137000.10 RAYMOND 3000 100 136000.00 RUDLOFF 2000 100 138000.40 --- 61 row(s) selected. Launching NCI From a Perl or Python Program You can execute an SQL statement by invoking the NCI Perl or Python wrapper script in a Perl or Python program.
#-------------Display the results-------------------------------------------foreach $rowvalue (@resultrows) { print $rowvalue; } Example of a Python Program (example.py) import os import sys import string if __name__ == '__main__': #Define SQL statements reorgtable="REORG TABLE neo.persnl.employee"; updatestats="UPDATE STATISTICS FOR TABLE neo.persnl.employee " updatestats=updatestats + "ON EVERY COLUMN"; selecttable="SELECT COUNT(*) FROM neo.persnl.
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 Syntax QUIT Disconnects from and exits NCI. See the “QUIT Command” (page 113). RECONNECT Creates a new connection to the Neoview platform using the login credentials of the last successful connection. See the “RECONNECT Command”. REPEAT Re-executes a command. See the “REPEAT Command” (page 114). RESET LASTERROR Resets the last error code to 0. See the “RESET LASTERROR Command” (page 115).
Command Description Syntax SHOW HISTOPT Displays the value that has been set for See the “SHOW HISTOPT Command” the history option of the current setting. (page 132). SHOW IDLETIMEOUT Displays the idle timeout value of the See the “SHOW IDLETIMEOUT current session. Command” (page 133). SHOW LASTERROR Displays the last error of the statement See the “SHOW LASTERROR that was executed. Command” (page 133).
Command Description Syntax SHOW TIMING Displays the setting for the elapsed time. See the “SHOW TIMING Command” (page 150). SHOW VIEWS Displays all or a set of the views that See the “SHOW VIEWS Command” exist in the current schema of the NCI (page 150). session. SPOOL Logs commands and output from NCI See the “SPOOL Command” to a log file. (page 151). VERSION Displays the build versions of See the “VERSION Command” Connectivity Services, Platform, Type (page 154). 4 Driver, and NCI.
(EXPR) -------------------62 --- 1 row(s) selected. SQL> ALLOW Command The ALLOW command allows you to grant read access for dumpfiles to super.services (HP support personnel), which they can use to diagnose a problem. This command controls whether or not HP support can read memory dumps. Syntax ALLOW [SERVICES] ACCESS TO DUMPFILES Considerations • • You must enter the command on one line. The command does not require an SQL terminator. Only ROLE.MGR can execute this command.
Syntax CONNECT [username [/password][@hostname][,dsnName]] username specifies the user name to log in to the database platform. If the user name is not specified, NCI prompts for the user name. password specifies the password of the user to log in to the database platform. If the password is not specified, NCI prompts for the password. hostname specifies the host name or IP address of the database platform to which you want the client to connect.
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 DENY command revokes access to the dumpfile. SQL>DENY SERVICES ACCESS TO DUMPFILES --- NS operation complete. DISCONNECT Command The DISCONNECT command terminates the connection from the Neoview platform, not from NCI.
Considerations • • 96 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 117). DATASOURCE Name of the data source that you entered when logging in to the database platform. For more information, see “Logging In to the Database Platform” (page 53).
Examples • This ENV command displays the attributes of the current session: SQL>env COLSEP DATASOURCE HISTOPT IDLETIMEOUT LIST COUNT LOG LOOK AND FEEL MARKUP MODE PROMPT ROLE SCHEMA SERVER SERVICE NAME SQLTERMINATOR STATISTICS TIME TIMING USER • " " TDM_Default_DataSource DEFAULT [No expansion of script files] 30 min(s) 0 [All Rows] OFF NCI RAW SQL SQL> DBA USR neo0101.acme.com:18650 HP_DEFAULT_SERVICE ; OFF OFF OFF role.
SQL operation has a specific SQL error code associated with the error condition. For more information about SQL error messages, see the Neoview Messages Manual. 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. You cannot execute this command in a script file. You can execute this command only at a command prompt. As each line of the command is displayed, you can modify the line by entering these editing commands (in uppercase or lowercase letters) on the line below the displayed command line: D Deletes the character immediately above the letter D. Repeat to delete more characters.
• Correct an SQL statement that you entered incorrectly by using more than one editing command: SQL>selt * fromm persnl.employee; *** ERROR[15001] A syntax error occurred at or before: selt * fromm persnl.employee; ^ SQL>fc SQL>selt * fromm persnl.employee; .... iec// d SQL>select * from persnl.employee; .... Pressing Enter executes the corrected SELECT statement.
EMPNUM LAST_NAME -----180 215 216 225 232 -------------------CONRAD LANCASTER JONES HELMSTED SPINNER --- 5 row(s) selected. SQL> GET STATISTICS Command The GET STATISTICS command returns formatted statistics for the last executed 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.
SQL> get statistics; Start Time End Time Elapsed Time Compile Time Execution Time Table Name NEO.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 operation complete. HELP Command The HELP command displays help text for the interface commands that are supported in the current operating mode.
number is the number of commands to display. The default number is 10. The maximum number is 100. Considerations • • You must enter the command on one line. The command does not require an SQL terminator. You can use the FC command to edit and re-execute a command in the history buffer, or use the REPEAT command to re-execute a command without modifying it. See the “FC Command” (page 98) or the “REPEAT Command” (page 114).
2 Dir (s) 57,686,646,784 bytes free SQL> LH mkdir c:\neo -> Will create a directory c:\neo on your local machine For more information, see Appendix C (page 157). • This command displays the elapsed time information because the SET TIMING command is set to ON: SQL>set timing on SQL>localhost ls hpnci-perl.pl hpnci-python.py hpnci.cmd hpnci.pl 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.
Examples • This command starts the logging process and records information to the sqlspool.lst file in the NCI bin directory: SQL>log on • This command starts the logging process and appends new information to an existing log file, persnl_updates.log, in the local directory (the same directory where you are running NCI): SQL>log persnl_updates.log • This command starts the logging process and appends new information to a log file, sales_updates.
--- 10 row(s) selected • This command start 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 c:\temp\b.txt clear, cmdtext off, quiet SQL>select * +>from neo.toi.job; SQL> log off Output of c:\temp\b.
• MODE and SHOW MODE • RESET LASTERROR and SHOW LASTERROR • SET COLSEP and SHOW COLSEP • SET HISTOPT and SHOW HISTOPT • SET IDLETIMEOUT and SHOW IDLETIMEOUT • SET MARKUP and SHOW MARKUP • SET PROMPT • SET TIME and SHOW TIME • SET TIMING and SHOW TIMING • 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.
CS#mode sql SQL> 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.
the JVM, the default depth is set to 10. To change this depth (for example to a value of 20), pass a java environment variable as follows: -Dhpnci.obeydepth=20 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.
+> CDEPT VARCHAR(4) +> primary key (cno) +>) ; NOT NULL, --- 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.
Syntax PRUN [-d | -defaults] | PRUN [-sd [-e [-ld [-o [-c | | | | | -scriptsdir directoryName] -extension extension] -logsdir logDirectory] -overwrite {y | n}] -connections num] 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.
Total Total Total Total Total Total Total Total files present ........................3 files processed ......................3 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.
QUIT Command The QUIT command disconnects from and exits the NCI. Syntax QUIT [IF ERRORCODE{=|<|>|<=|>=|<>}error-code] error-code is an integer that represents an error condition of the previously executed command. If the previously executed command returns this error code, NCI disconnects and exits. Commands that execute successfully in NCI have an error code of zero (0). Interface commands that do not perform SQL operations and that fail to execute have an error code of -1.
Examples This command creates a new connection to the Neoview platform using the login credentials of the last successful connection: SQL>reconnect Connected to DataSource TDM_Default_DataSource 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.
SQL> • Re-execute the fifth command in the history buffer: SQL>repeat 5 show tables TABLE NAMES ------------------------------------------------------------DEPT EMPLOYEE JOB PROJECT 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.
param-name is the name of the parameter for which you specified a value. Parameter names are case-sensitive. For example, the parameter ?pn is not equivalent to the parameter ?PN. param-name can be preceded by a question mark (?), such as ?param-name. If you do not specify a parameter name, all of the parameter values in the current session are cleared. Considerations • • • You must enter the command on one line. The command does not require an SQL terminator.
SAVEHIST Command The SAVEHIST command saves the session history in a user-specified file. The session history consists of a list of the commands that were executed in the NCI session before the SAVEHIST command. Syntax SAVEHIST file-name [CLEAR] file-name is the name of a file into which NCI stores the session history.
Considerations You must enter the command on one line. The SET COLSEP command can be executed only in SQL mode. The SET COLSEP command has no effect if the markup is set to HTML, XML, or CSV.
--- 1 row(s) inserted. SQL> history; 1> show histopt 2> obey e:\scripts\nobey\insert2.sql This command shows all the commands added to the history buffer. SQL> set histopt all SQL> obey e:\scripts\nobey\insert2.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. SQL> INSERT INTO COURSE1 VALUES +> ('C55','COMPUTER ARCH.','VON NEUMANN''S MACH.',3,100,'CIS'); ---1 row(s) inserted.
Examples • This command sets the idle timeout value to four hours: SQL>set idletimeout 240 • This command sets the idle timeout value to an infinite amount of time so that the session never expires: SQL>set idletimeout 0 • To reset the idle timeout to the default, enter this command: SQL>set idletimeout 30 SQL> For more information, see “Setting and Showing the Idle Timeout Value for the Session” (page 63).
SQL> • This command resets the number of displayed rows to all rows: SQL>set list_count 0 SQL>select empnum, first_name, last_name +>from persnl.employee +>order by empnum; EMPNUM FIRST_NAME LAST_NAME ------ ---------------------------------1 ROGER GREEN 23 JERRY HOWARD 29 JANE RAYMOND 32 THOMAS RUDLOFF 39 KLAUS SAFFERT 43 PAUL WINTER 65 RACHEL MCKAY ... 995 Walt Farley --- 62 row(s) selected.
| CUSTNUM | CUSTNAME | ORDERNUM | ORDER_DATE |
| 143 | STEVENS SUPPLY | 700510 | 2003-06-01 |
| 3333 | NATIONAL UTILITIES | 600480 | 2003-05-12 |
| 7777 | SLEEP WELL HOTELS | 100250 | 2003-01-23 |
