HP Neoview Script Guide HP Part Number: 544590-001 Published: August 2007 Edition: HP Neoview Release 2.
© Copyright 2007 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.......................................................................
Showing the Session Attributes.......................................................................................................56 Setting and Showing the Idle Timeout Value for the Session.........................................................57 Customizing the Standard Prompt..................................................................................................57 Setting and Showing the SQL Terminator.......................................................................................
Considerations.................................................................................................................................86 Example...........................................................................................................................................87 CLEAR Command................................................................................................................................87 Syntax...................................................................
Syntax.............................................................................................................................................100 Considerations...............................................................................................................................101 Example.........................................................................................................................................101 QUIT Command........................................................
SET PROMPT Command....................................................................................................................114 Syntax.............................................................................................................................................115 Considerations...............................................................................................................................115 Examples......................................................................
Examples........................................................................................................................................124 SHOW PARAM Command.................................................................................................................125 Syntax.............................................................................................................................................125 Considerations................................................................
Considerations...............................................................................................................................136 Examples........................................................................................................................................136 SPOOL Command...............................................................................................................................137 Syntax.....................................................................
List of Figures 1-1 Neoview Script Within a Neoview Platform Network.................................................................
List of Tables 2-1 Locations of Neoview Script Software Files..................................................................................
About This Document This manual describes how to use the Neoview Script command-line interface on a client workstation to manage a database on a Neoview data warehousing platform. Neoview Script 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.
Chapter 6: Running Neoview Script From Perl or Python Describes how to run Neoview Script from Perl or Python. Appendix A: Neoview Script Interface Commands Provides syntax, considerations, and examples for Neoview Script interface commands. Appendix B: Supported SQL Statements Lists the SQL statements that Neoview Script supports. Appendix C: Connectivity Service Provides syntax, considerations, and examples for connectivity service commands.
INTERVAL { start-field TO end-field | single-field } | Vertical Line A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: {expression | NULL} … Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: ATTRIBUTE[S] attribute [, attribute]... {, sql-expression}...
• • “Neoview Support Library” “Neoview Customer Library” (page 18) Neoview Support Library Manuals in the Neoview support library are for HP service professionals. These manuals are not intended for HP customers. • Hardware Boot Application Online Help Reference information for using the boot software. Neoview Hardware Installation and Support Guide Installation and replacement procedures. Neoview Release 2.
• • Neoview Management Dashboard Client Guide for Database Administrators Information on using the Dashboard Client, including how to install the Client, start and configure the Client Server Gateway (CSG), use the Client windows and property sheets, interpret entity screen information, and use Command and Control to manage queries from the Client. Neoview Owner’s Manual Site-planning information and basic hardware information.
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: pubs.comments@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 Neoview Script Neoview Script 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 Neoview Script on a client workstation to a Neoview database on a Neoview data warehousing platform.
2 Installing Neoview Script NOTE: If you are manually installing Neoview Script on a Linux platform that has the Neoview data loader installed, use the services or sysadmin ID instead of root, which has been frozen for the loader. The best way to install Neoview Script on the loader platform is by using the Neoview Loader Supplemental executive (Installsuppkt). For more information, see the Neoview R2.2 Migration and Upgrade Guide. To install Neoview Script, follow these procedures: 1.
After installing the Java version, proceed with “Verifying the Java Version” (page 24). Verifying the Java Version To display the Java version of the client workstation on the screen, enter: java -version For example: 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.
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. 4. 5. Enter “Neoview Script” in the search box in the upper right corner of the Software Depot home page. Click the HP Neoview Script link that appears in the search results. Follow instructions to download HP Neoview Script, which includes the hpnvsInstaller.jar file, to the directory or folder in Step 1. NOTE: In the File Download dialog box, make sure to select the Save or Save this file to disk option.
installer-directory is the directory where you downloaded the installer file, hpnvsInstaller.jar. 6. Launch the Installer Wizard by entering: java -jar hpnvsInstaller.jar 7. Proceed to “Using the Installer Wizard” (page 29). Using the Installer Wizard When you execute hpnvsInstaller.jar, the Installer Wizard appears: 1. To locate the JDBC driver JAR file, click Browse next to HP Neoview JDBC Type 4 Driver JAR file: 2.
3. Select hpt4jdbc.
4. To find an installation location for Neoview Script, click Browse next to HP Neoview Script installation directory: 5.
The Installer Wizard displays the directory where the Neoview Script will be installed. 6.
The Installation Status dialog box appears on the screen, indicating how many files are installed in the installation directory: The number of files that are extracted and installed is 15. All these files are stored in the nvscript subdirectory within the installation directory. 7. Click OK, and proceed with “Verifying the Installed Software Files” (page 34). Command-Line Installation Steps 1.
4. Enter an existing directory where you would like to install Neoview Script: HP Neoview Script -------------------------------------Enter the installation directory: c:\install The installation status appears, indicating how many files are installed in the installation directory: HP Neoview Script -------------------------------------Enter the installation directory: c:\install Extracted 15 files from the /C:/download/hpnvsInstaller.jar archive into the c:\install directory. Installation Complete.
Table 2-1 Locations of Neoview Script Software Files (continued) Directory 1 Files Description sample-beta.pl Beta version of sample Perl program1 sample-beta.py Beta version of sample Python program1 Neoview Script provides a beta version of enhanced support for Perl and Python programs. This functionality enables multiple SQL statements to run in one database connection from a Perl or Python program. For more information, see the README in the Neoview Script samples directory.
Supported Look-and-Feel Types Currently, Neoview Script supports the SQLPlus and Teradata look-and-feel types, in addition to the default look and feel, Neoview SQL. The default look and feel, Neoview SQL, appears as shown below: Welcome to the HP Neoview Script Interface 2.2 (c) Copyright 2006, 2007 Hewlett-Packard Development Company, LP. Connected to DataSource: TDM_Default_DataSource SQL>create view persnl.salarylist +>as select salary from persnl.employee; --- SQL operation complete.
To set the look and feel, see: • • • “Setting the Look and Feel at a Command-Line Prompt” (page 37) “Setting the Look and Feel in the System Properties on Windows” (page 37) “Setting the Look and Feel in the User Profile on Linux or UNIX” (page 40) If you do not set a look and feel, the default is Neoview SQL.
3. 38 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 -DhpnvsLF property value for the Variable Value, and click OK. The Variable Value must include: -DhpnvsLF=look-and-feel-type look-and-feel-type is one of the “Supported Look-and-Feel Types” (page 36). For example: -DhpnvsLF=SQLPlus 5. 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 -DhpnvsLF property value to nvs. 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.
Testing the Launch of Neoview Script 1. Launch the Neoview Script interface and verify that you can connect to the database. For instructions, see Chapter 3 (page 43). 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. To create a user ID to log in to the database, see the Neoview Database Administrator’s Guide.
3 Launching the Neoview Script Interface This chapter describes how to launch the Neoview Script interface from the Windows, Linux, or UNIX environment: • • • • • • • “Launching the Neoview Script Interface on Windows” (page 43) “Launching the Neoview Script Interface on Linux or UNIX” (page 46) “Logging In to the Database Platform” (page 47) “Using Optional Launch Parameters” (page 50) “Launching Neoview Script Without Connecting to the Database” (page 53) “Running Neoview Script With -version” (page 54)
1. Right-click the desktop and select New > Shortcut: 2. Type the location of hpnvs.cmd within double quotes (“) or click Browse to locate that file, and then click Next: For the location of the Neoview Script software files, see Table 2-1 (page 34). 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 "...\nvscript\bin\hpnvs.
For more information, see “Using Optional Launch Parameters” (page 50). d. 5. Click OK. To launch Neoview Script, double-click the shortcut icon. The Neoview Script interface appears. If you did not set the optional launch parameters, the Neoview Script interface 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 47).
hpnvs-installation-directory is the directory where you installed the Neoview Script software files. For more information, see Table 2-1 (page 34). 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 hpnvs.sh file to launch Neoview Script: hpnvs.
4. Enter the name of a data source that is available and running (that is, started). If you do not enter a data source, Neoview Script connects to Admin_Load_DataSource by default. Admin_Load_DataSource is the recommended data source for Neoview Script connections. If Admin_Load_DataSource (or any data source that you specify) is not started, Neoview Script returns an error and prompts you to close the session.
DataSource Name [Admin_Load_DataSource]: Connected to DataSource: Admin_Load_DataSource SQL> • This example shows a valid host name. Neoview Script prompts only for the user name and password: hpnvs –u super.ser –p serv Welcome to the HP Neoview Script Interface 2.2 (c) Copyright 2006, 2007 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.
Host Name/IP Address: neo0101.mylab.mycorp.net:18650 User Name: dba1 Password: DataSource Name [Admin_Load_DataSource]: Connected to DataSource: Admin_Load_DataSource SQL> At the prompt, you can enter an SQL statement or a Neoview Script interface command. For more information, see Chapter 4 (page 55). 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), and -dsn when launching Neoview Script.
Launch Parameter Description -noconnect Launches a Neoview script session without connecting to the Neoview platform (database).For more information, see “Launching Neoview Script Without Connecting to the Database” (page 53) -version Displays the build version of Neoview Script 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.
Example of Running an SQL Statement With -q or -sql Use -q or -sql with the CREATE SCHEMA statement to create a schema when launching the Neoview Script interface: • On Windows, in the Command Prompt window, enter: cd hpnvs-installation-directory\nvscript\bin hpnvs.cmd -q "create schema persnl" • On Linux or UNIX, in the terminal window, enter: cd hpnvs-installation-directory/nvscript/bin ./hpnvs.
For more information, see “Creating a Script File” (page 71). Example of Running a Script File With -s or -script • On Windows, in the Command Prompt window, enter: cd hpnvs-installation-directory\nvscript\bin hpnvs.cmd -s settings.txt Specify the full path of the script file if it is outside the directory of hpnvs.cmd. • On Linux or UNIX, in the terminal window, enter: cd hpnvs-installation-directory/nvscript/bin ./hpnvs.sh -s settings.
./hpnvs.sh -noconnect Running Neoview Script With -version To display the build version of Neoview Script 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 Neoview Script File With -version • On Windows, in the Command Prompt window, enter: cd hpnvs-installation-directory\nvscript\bin hpnvs.
4 Running Commands Interactively in the Neoview Script Interface After launching the Neoview Script interface, you can run SQL statements and Neoview Script interface commands in the interface.
You can continue any SQL statement over multiple lines, breaking that statement at any point except within a word, a numeric literal, or a multicharacter operator (for example, <=). To break a string literal in a DML statement, use a concatenation operator (||). For more information, see the concatenation operator in the Neoview SQL Reference Manual. To terminate an SQL statement that spans multiple lines, use the SQL terminator for the session.
SCHEMA SERVER SQLTERMINATOR TIME TIMING USER USR neo0101.acme.com:18650 ; OFF OFF role.dba SQL> For more information, see the “ENV Command” (page 89) or “SHOW SESSION Command” (page 128). 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.
20:32:26 SQL ENTER>set time off SQL ENTER> For more information, see the “SET TIME Command” (page 118). 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 TERMINATOR command sets the SQL terminator to a period (.): SQL>set sqlterminator . SQL>insert into sales.custlist +>(select * from invent.supplier +>where suppnum=8).
SQL> For more information, see the “SET TIMING Command” (page 119). 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 Neoview Script 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.
• • “Showing the Views in a Schema” (page 61) “Showing the Synonyms in a Schema” (page 61) Showing the Schemas The SHOW SCHEMAS command displays the schemas that exist in the default catalog: 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_SALE
SQL> For more information, see the “SHOW TABLE Command” (page 132). 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.
For more information, see the “SHOW SYNONYMS Command” (page 131). Displaying Executed Commands To display commands that were recently executed in the Neoview Script 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 62).
Running SQL Statements In the Neoview Script interface, you can run SQL statements interactively. For a list of SQL statements that you can run interactively, see Appendix B (page 139). This subsection shows examples of: • • • • “Executing an SQL Statement” (page 63) “Repeating an SQL Statement” (page 63) “Preparing and Executing SQL Statements” (page 64) “Executing CREATE TRIGGER Statements” (page 67) To run SQL statements from script files in the Neoview Script interface, see Chapter 5 (page 71).
--- 11 row(s) selected. SQL> For more information, see the “/ Command” (page 86), “RUN Command” (page 106), or “REPEAT Command” (page 103). 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 105). 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.
Logging Output To log a Neoview Script session, use the SPOOL or LOG command. The SPOOL and LOG commands record into a log file the commands that you enter in the Neoview Script interface and the output of those commands.
Logging Concurrent Neoview Script Sessions If you plan to run two or more Neoview Script sessions concurrently on the same workstation, use the SPOOL log-file or LOG log-file command and specify a unique name for each log file. Otherwise, each session writes information to the same log file, making it difficult to determine which information belongs to each session.
5 Running Scripts in the Neoview Script Interface In the Neoview Script interface, you can run script files.
the section name in the @ or OBEY command. For more information, see the“@ Command” (page 86) or the “OBEY Command” (page 98). Example of a Script File This script file creates tables in the inventory schema: Running a Script File To run a script file in the Neoview Script interface, use the @ or OBEY command. The @ and OBEY commands run one script file at a time in the Neoview Script interface.
NOTE: If the script file is outside the directory of the hpnvs.cmd or hpnvs.sh file (by default, the Neoview Script bin directory), you must specify the full path of the script file in the @ or OBEY command. For the Neoview Script bin directory, see Table 2-1 (page 34). SQL>@C:\ddl_scripts\sch_invent.sql SQL>-- CREATE SCHEMA SQL>CREATE SCHEMA INVENT; --- SQL operation complete. SQL>-- CREATE TABLES/VIEWS in SCHEMA INVENT SQL>SET SCHEMA INVENT; --- SQL operation complete. SQL>CREATE TABLE INVENT.
NOTE: The PRUN command can be run in non-interactive mode. The PRUN command allows options to be specified on the command line, which enables PRUN to be run in script and/or obey files. The PRUN command is most useful for running sets of data definition language (DDL) statements simultaneously, which speeds up the process of creating large databases. Put all dependent or related DDL statements in the same script file.
6 Running Neoview Script From Perl or Python You can execute an SQL statement in Perl or Python by invoking the Neoview Script Perl or Python wrapper script.
set HPNVS_PASSWORD=password set HPNVS_DATASOURCE=data-source-name Setting Login Environment Variables in the System Properties 76 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.
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.
export export export export HPNVS_SERVER=host-name:port-number HPNVS_USER=user-name HPNVS_PASSWORD=password HPNVS_DATASOURCE=data-source-name At each command prompt in the C shell, enter one of these commands: setenv setenv setenv setenv HPNVS_SERVER=host-name:port-number HPNVS_USER=user-name HPNVS_PASSWORD=password HPNVS_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 .
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 139). See “Perl and Python Commands on Windows” (page 79) and “Perl and Python Commands on Linux or UNIX” (page 79).
For example: >cd /usr/local/hp/nvscript/bin >perl hpnvs.pl "POPULATE INDEX neo.persnl.xempname >ON neo.persnl.employee" • On the Python command line, enter: cd hpnvs-installation-directory/nvscript/bin python hpnvs.py "sql-statement-string" For example: >cd /usr/local/hp/nvscript/bin >python hpnvs.py "SELECT * FROM neo.persnl.employee" The command returns this output: EMPNUM -----1 23 29 32 ...
#-----Reorganize the table -------------------------------------------------$status=`perl /usr/local/hp/nvscript/bin/hpnvs.pl "$reorgtable"`; print "Reorg status :".$status; print "\n"; #----------Update the statistics of the table-------------------------------$status=`perl /usr/local/hp/nvscript/bin/hpnvs.pl "$updatestats"`; print "Update statistics status :".$status; print "\n"; #--------------Fetch results------------------------------------------------@resultrows=`perl /usr/local/hp/nvscript/bin/hpnvs.
hpnvs-installation-directory is the directory where you installed the Neoview Script software files. For more information, see Table 2-1 (page 34). To run a Perl program, enter the perl command at a command prompt, as this example shows: >perl example.pl To run a Python program, enter the python command at a command prompt, as this example shows: >python example.
A Neoview Script Interface Commands Neoview Script supports these commands in the Neoview Script interface or in script files that you run in the Neoview Script interface. For a list of Neoview Script interface commands that are available only to HP support, see the Neoview Database Support Guide. Command Description Syntax @ Runs the SQL statements and Neoview See the “@ Command” (page 86). Script interface commands contained in a specified script file. / Runs the previously executed SQL statement.
Command Description Syntax REPEAT Reexecutes a command. See the “REPEAT Command” (page 103). RESET LASTERROR Resets the last error code to 0. See the “RESET LASTERROR Command” (page 105). RESET PARAM Clears all parameter values or a specified parameter value in the current session. See the “RESET PARAM Command” (page 105). RUN Runs the previously executed SQL statement. See the “RUN Command” (page 106). SAVEHIST Saves the session history in a user-specified file.
Command Description Syntax SHOW LIST_COUNT Displays the maximum number of rows See the “SHOW LIST_COUNT to be returned by SELECT statements Command” (page 121). in the current session. SHOW MARKUP Displays the value that has been set for See the “SHOW MARKUP the markup option for the current Command” (page 122). Neoview Script session. SHOW MODE Displays the operating mode of the current session.
@ Command The @ command executes the SQL statements and Neoview Script interface commands contained in a specified script file. Syntax @script-file [ (section-name )] script-file is the name of an ASCII text file that contains SQL statements, Neoview Script interface commands, and comments. If the script file exists outside the local directory where you launch Neoview Script (by default, the Neoview Script bin directory), specify the full directory path of the script file.
Example This / command executes the previously executed SELECT statement: SQL>select count(*) from persnl.employee; (EXPR) -------------------62 --- 1 row(s) selected. SQL>/ (EXPR) -------------------62 --- 1 row(s) selected. SQL> 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.
hostname specifies the host name or IP address of the database platform to which you want the client to connect. If the hostname is not specified, the value is automatically used from the current Neoview Script session. If Neoview Script was invoked with the -noconnect launch parameter, you are prompted for a hostname value. dsnName specifies the name of a data source. If the dsnName is not specified, the value is automatically used from the current Neoview Script session.
Considerations In the Neoview Script interface, you must enter the command on one line. Examples This command terminates the connection to the Neoview platform. You can connect to the Neoview platform by using the CONNECT and RECONNECT commands: SQL>disconnect Session Disconnected. Please connect to the database by using connect/reconnect command. ENV Command ENV displays attributes of the current Neoview Script session. You can also use the SESSION and SHOW SESSION commands to perform the same function.
SERVER Host name and port number that you entered when logging in to the database platform. For more information, see “Logging In to the Database Platform” (page 47). SQLTERMINATOR Current SQL statement terminator. The default is a semicolon (;). For more information, see “Setting and Showing the SQL Terminator” (page 58) and “SHOW SQLTERMINATOR Command” (page 131). 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.
Syntax EXIT [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, the Neoview Script interface disconnects and exits. Commands that execute successfully in the Neoview Script interface 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.
Without text or number, FC retrieves the most recent command. Considerations • • • In the Neoview Script interface, 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.
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.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.
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. SQL> 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.
number is the number of commands to display. The default number is 10. The maximum number is 100. Considerations • • In the Neoview Script interface, 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 reexecute a command in the history buffer, or use the REPEAT command to reexecute a command without modifying it. See the “FC Command” (page 91) or the “REPEAT Command” (page 103).
6 Files(s) 19,491 bytes 2 Dir (s) 57,686,646,784 bytes free SQL> LH mkdir c:\neo -> Will create a directory c:\neo on your local machine LOG Command The LOG command logs the entered commands and their output from the Neoview Script interface to a log file. Syntax LOG { ON [CLEAR] | log-file [CLEAR] | OFF } ON starts the logging process and records information in the sqlspool.lst file in the Neoview Script bin directory. ON CLEAR instructs Neoview Script to clear the contents of the sqlspool.
• This command starts the logging process and clears existing information from the log file before logging new information to the file: SQL>log persnl_ddl.log clear • This command stops the logging process: SQL>log off For more information, see “Logging Output” (page 68). MODE Command The MODE command determines the operating mode of the current session to be either SQL for database commands or CS for connectivity service commands. The default mode for Neoview Script sessions is SQL.
Examples • This command changes the Neoview Script session to CS mode operation: SQL>mode cs CS# For more information, see Appendix C (page 141). • This command returns the Neoview Script session to SQL mode operation: CS#mode sql SQL> OBEY Command The OBEY command executes the SQL statements and Neoview Script interface commands contained in a specified script file.
?section droptable DROP TABLE COURSE ?section create CREATE TABLE COURSE ( CNO VARCHAR(3) CNAME VARCHAR(22) CDESCP VARCHAR(25) CRED INT, CLABFEE NUMERIC(5,2), CDEPT VARCHAR(4) primary key (cno) ) ; NOT NULL, NOT NULL, NOT NULL, NOT NULL, ?section insert INSERT INTO COURSE VALUES ('C11', 'INTRO TO CS','FOR ROOKIES',3, 100, 'CIS'); INSERT INTO COURSE VALUES ('C22', 'DATA STRUCTURES','VERY USEFUL',3, 50, 'CIS'); INSERT INTO COURSE VALUES ('C33', 'DISCRETE MATHEMATICS', 'ABSOLUTELY NECESSARY',3, 0,'CIS'); ?s
--- 1 row(s) inserted. SQL>INSERT INTO COURSE VALUES +> ('C33', 'DISCRETE MATHEMATICS', 'ABSOLUTELY NECESSARY',3, 0, 'CIS'); --- 1 row(s) inserted. ONLINEDBDUMP Command The ONLINEDBDUMP command alters or displays the configured time to backup or dump the entire Neoview database. Syntax ONLINEDBDUMP [info | AT hh:mm:ss] Considerations • • • In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator.
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. If you do not specify a log file directory, PRUN places the log files in the same directory as the script files. Note: PRUN puts the prun.err.log summary file in the error subdirectory. overwrite If you specify (y), PRUN overwrites the contents of existing log files.
PRUN options are -scriptsdir c:/_nvs/prun -logsdir c:/_nvs/prun/logs -extension sql -overwrite y -connections 5 Status: Complete _____________________________________________ PARALLELRUN(PRUN)SUMMARY _____________________________________________ Total files present ......................99 Total files processed ....................99 Total sqls processed ....................198 Total errors ..............................0 Total warnings ............................0 Total warnings ..........................
Considerations In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator. Examples • This command disconnects from and exits the Neoview Script interface, which disappears from the screen: SQL>quit • In a script file, the conditional exit command causes the script file to quit running and disconnect from and exit the Neoview Script interface when the previously run command returns error code 4082: log c:\errorCode.
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. Neoview Script ignores leading blanks. [-]number is an integer that identifies a command in the history buffer.
DEPT EMPLOYEE JOB PROJECT SQL> RESET LASTERROR Command The RESET LASTERROR command resets the last error code to 0. Syntax RESET LASTERROR Considerations • In the Neoview Script interface, 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.SCH.EMP does not exist or is inaccessible.
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 65). RUN Command The RUN command executes the previously executed SQL statement. This command does not repeat a Neoview Script interface command. Syntax RUN Considerations • • In the Neoview Script interface, you must enter the command on one line.
the Neoview Script bin directory), specify the full directory path of the history file. The specified directory must exist before you execute the SAVEHIST command. CLEAR instructs Neoview Script to clear the contents of the specified file before adding the session history to the file. Considerations • • In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator.
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 HISTOPT Command The SET HISTOPT command sets the history option and controls how commands are added to the history buffer. By default, commands within a script file are not added to history.
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. SQL> history; 1> show histopt 2> obey e:\scripts\nobey\insert2.sql 3> history; 4> set histopt all 5> set schema neo.
SQL>set idletimeout 30 SQL> For more information, see “Setting and Showing the Idle Timeout Value for the Session” (page 57). SET LIST_COUNT Command The SET LIST_COUNT command sets the maximum number of rows to be returned by SELECT statements that are executed after this command. The default is zero, which means that all rows are returned.
1 ROGER 23 JERRY 29 JANE 32 THOMAS 39 KLAUS 43 PAUL 65 RACHEL ... 995 Walt GREEN HOWARD RAYMOND RUDLOFF SAFFERT WINTER MCKAY Farley --- 62 row(s) selected. SQL> SET MARKUP Command The SET MARKUP command sets the markup format and controls how results are displayed by Neoview Script. Syntax SET MARKUP [RAW|HTML|XML|CSV|COLSEP] The supported options enable results to be displayed in XML, HTML, CSV (Comma Separated Values), and COLSEP format. The default format is RAW.
| 7777 | SLEEP WELL HOTELS | 100250 | 2003-01-23 |
SQL>select c.custnum, c.custname,ordernum,order_date, +>from customer c, orders o where c.custnum=o.custnum; | Error Id | Error Code | Error Message |
| 1 | 4082 | Object NEO.NVS.Irwin Shaw 93333 Martin Fowler 92222 Grady Booch 84758345 Judy Blume 89832473 Barbara Kingsolver param-name is the name of the parameter for which a value is specified. 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. param-value is a numeric or character literal that specifies the value for the parameter. If you do not specify a value, Neoview Script returns an error. Syntax SET PROMPT [string] [%USER] [%MODE] [%SERVER] [%SCHEMA] [%DATASOURCE] string is a string value to be displayed as the 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. %USER displays the session user name as the prompt. %MODE displays the operating mode of the session as the prompt. %SERVER displays the session host name and port number as the prompt. • This command displays the operating mode of the session for the prompt: SQL>set prompt %mode: SQL: • This command displays the session host name and port number for the prompt: SQL>set prompt %server> neo0101.mylab.mycorp. Considerations • • • In the Neoview Script interface, 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. Syntax SET SQLTERMINATOR string string is a string value for the SQL terminator. The string may contain any characters except spaces. Spaces are disallowed even if you enclose the string in double quotes. Lowercase and uppercase characters are accepted, but the SQL terminator is always shown in uppercase. Considerations • • • • In the Neoview Script interface, 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. Examples • This command causes the local time to be displayed in the SQL prompt: SQL>set time on 14:17:17 SQL> • This command causes the local time to be displayed in 12–hour AM/PM style in the SQL prompt: SQL>set time on 12h 2:17:17 PM SQL> • This command turns off the local time in the SQL prompt: 2:17:17 PM SQL>set time off SQL> For more information, see “Customizing the Standard Prompt” (page 57). Considerations In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator. Examples • This command displays the column separator. SQL> show colsep COLSEP " " SQL> set colsep * SQL> show colsep COLSEP "*" SHOW HISTOPT Command The SHOW HISTOPT command displays the value that has been set for the history option. Syntax SHOW HISTOPT Considerations In the Neoview Script interface, you must enter the command on one line. SQL>show idletimeout IDLETIMEOUT 30 min(s) • This command shows that the idle timeout value of the session is four hours: SQL>show idletimeout IDLETIMEOUT 240 min(s) • This command shows that the idle timeout value is an infinite amount of time, meaning that the session never expires: SQL>show idletimeout IDLETIMEOUT 0 min(s) [Never Expires] For more information, see “Setting and Showing the Idle Timeout Value for the Session” (page 57). SQL>show list_count LISTCOUNT 0 [All Rows] • This command shows that the maximum number of rows to be displayed by SELECT statements in the session is five: SQL>set list_count 5 SQL>show list_count LISTCOUNT 5 SHOW MARKUP Command The SHOW MARKUP command displays the value set for the markup option. Syntax SHOW MARKUP Considerations In the Neoview Script interface, you must enter the command on one line. wild-card-pattern is a character string used to search for and display materialized view groups with names that match the character string. wild-card-pattern matches an uppercase string unless you enclose it within double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters: % Use a percent sign to indicate zero or more characters of any type. Syntax SHOW MVS [wild-card-pattern] wild-card-pattern is a character string used to search for and display materialized views with names that match the character string. wild-card-pattern matches an uppercase string unless you enclose it within double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters: % Use a percent sign to indicate zero or more characters of any type. SHOW PARAM Command The SHOW PARAM command displays the parameters that are set in the current Neoview Script session. Syntax SHOW PARAM Considerations • • In the Neoview Script interface, 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. Considerations In the Neoview Script interface, you must enter the command on one line. The SHOW PREPARED command can be executed only in SQL mode. Considerations In the Neoview Script interface, you must enter the command on one line. The SHOW PROCEDURES command can be executed only in SQL mode. If you do not specify a wild-card pattern, Neoview Script displays all procedures that exist in the current schema. If you specify a wild-card pattern, Neoview Script displays only the procedure names that match the wild-card pattern. 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 underscore to indicate any single character. For example, boo_ matches BOOK and BOOT but not BOO or BOOTS. Syntax [SHOW] SESSION Considerations • • In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator. SHOW SESSION or SESSION displays these attributes: COLSEP Current column separator, which is used to control how query results are presented. For more information, see “SET COLSEP Command” (page 107). DATASOURCE Name of the data source that you entered when logging in to the database platform. Examples • This command displays the attributes of the current session: SQL>show session COLSEP DATASOURCE HISTOPT IDLETIMEOUT LIST COUNT LOG LOOK AND FEEL MARKUP MODE PROMPT SCHEMA SERVER SQLTERMINATOR TIME TIMING USER • "," TDM_Default_DataSource ALL 30 min(s) 0 [All Rows] OFF NVS XML SQL SQL> USR neo0101.acme.com:18650 ; OFF OFF role. SHOW SQLTERMINATOR Command The SHOW SQLTERMINATOR command displays the SQL statement terminator of the current Neoview Script session. Syntax SHOW SQLTERMINATOR Considerations • • In the Neoview Script interface, 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. Example This command shows that the SQL terminator for the current session is a period (.): SQL>show sqlterminator SQLTERMINATOR . Examples • This command shows all the synonyms in the current schema, SALES: SQL>show synonyms SYNONYM NAMES ------------------------------------------------------------CUST DTLS ORDR PRTS SQL> • This command shows all the synonyms in the current schema, SALES, that have “S” at the end of their names: SQL>show synonyms %s SYNONYM NAMES ------------------------------------------------------------DTLS PRTS SQL> • This command shows all the synonyms in the current schema, SALES, that are named “PRT” follo 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. Considerations • • • • In the Neoview Script interface, 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 you do not specify a wild-card pattern in a SHOW TABLES command, Neoview Script displays all the tables that exist in the current schema. If you specify a wild-card pattern in a SHOW TABLES command, Neoview Script displays only the table names that match the wild-card pattern. Syntax SHOW TIMING Considerations In the Neoview Script interface, you must enter the command on one line. The command does not require an SQL terminator. Example This command shows that the setting for the elapsed time is OFF: SQL>show timing TIMING OFF SHOW VIEWS Command The SHOW VIEWS command displays all or a set of the views that exist in the current schema of the Neoview Script session. VIEW NAMES ------------------------------------------------------------VIEW207 VIEW207N VIEWCS VIEWCUST SQL> • This command shows the views in the current schema, INVENT, that have “VIEW” at the beginning of their names: SQL>show views view% VIEW NAMES ------------------------------------------------------------VIEW207 VIEW207N VIEWCS VIEWCUST SQL> For more information, see “Showing the Views in a Schema” (page 61). SQL>spool 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 Neoview Script): SQL>spool persnl_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 Windows workstation: SQL>spool c:\log_files\sales_updates. B Supported SQL Statements Neoview Script 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 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 Neoview Script supports connectivity service commands in the Neoview Script interface in 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 97). Any user can execute the “INFO DS Command” (page 141). Other connectivity service commands are available only to HP support. 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. Index Symbols -DhpnvsLF property, 35 -dsn parameter, 50 -h parameter, 50 -host parameter, 50 -noconnect examples of, 53 -noconnect parameter, 51 -p parameter, 50 -password parameter, 50 -q parameter description of, 50 examples of, 52 -s parameter description of, 50 examples of, 53 -script parameter, 50 -sql parameter, 50 -u parameter, 50 -user parameter, 50 -version, 51 examples of, 54 / command example of, 63 syntax of, 86 @ command example of, 72 syntax of, 86 _JAVA_OPTIONS environment variable setting at I Idle timeout value, 57 Indexes, showing all indexes of a table, 60 INFO DS command, 141 INSERT statement description of, 140 example of, 58 Installation procedures downloading the Neoview Script installer file, 27 testing the launch of Neoview Script, 41 Installer file, Neoview Script, 27 Interface command (see Neoview Script interface command) IP address, 47 J JDBC driver installation, 26 verifying the version, 26 JDBC Type 4 driver (see JDBC driver) L Launch files, location of, 34 Launch parameters de running, 82 Perl wrapper script description of, 78 location of, 34 POPULATE INDEX utility description of, 140 examples of, 79, 80 Port number, default, 47 PREPARE statement description of, 140 examples of, 64 Procedures wild-card search, 126 Product banner, 55 Prompts continuation, 55 standard, 55 PRUN syntax, 100 Python command line invoking Neoview Script, 78 running an SQL statement, 79, 80 Python program including SQL statements, 80 launching Neoview Script, 80 running, 82 Python wrapper script descript SHOW SQLPROMPT command, 130 SHOW SQLTERMINATOR command, 131 SHOW SYNONYMS command example of, 61 syntax of, 131 SHOW TABLE command example of, 60 syntax of, 132 SHOW TABLES command example of, 60 syntax of, 134 SHOW TIME command, 135 SHOW TIMING command, 135 SHOW VIEWS command example of, 61 syntax of, 136 SPOOL command examples of, 68 syntax of, 137 Spooling (see Logging output) SQL mode, 97 SQL parameters displaying, 65 resetting, 65 setting, 65 SQL statement breaking across lines, 56 displaying the elaps V VERSION command, syntax of, 138 Views showing all views in a schema, 61 wild-card search, 136 W Wild-card characters, 123 Wild-card search materialized views, 124 procedures, 126 schemas, 127 synonyms, 131 tables, 134 views, 136 Windows launch file, location of, 34 147 |
