HP Neoview Command Interface (NCI) Guide HP Part Number: 613604-002 Published: September 2010 Edition: HP Neoview Release 2.
© Copyright 2010 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.......................................................................................................17 Intended Audience................................................................................................................................17 Document Organization.......................................................................................................................17 New and Changed Information in This Edition...................................
Showing the Session Attributes.......................................................................................................46 Setting and Showing the Idle Timeout Value for the Session.........................................................47 Customizing the Standard Prompt..................................................................................................47 Setting and Showing the SQL Terminator.......................................................................................
Examples..........................................................................................................................................77 ALLOW Command...............................................................................................................................77 Syntax..............................................................................................................................................77 Considerations.......................................................
Considerations.................................................................................................................................90 Examples..........................................................................................................................................90 LABEL Command.................................................................................................................................91 Syntax..................................................................
Syntax.............................................................................................................................................107 Considerations...............................................................................................................................108 Examples........................................................................................................................................108 SET COLSEP Command..................................................
SET TIMING Command.....................................................................................................................124 Syntax.............................................................................................................................................124 Considerations...............................................................................................................................124 Examples.....................................................................
Syntax.............................................................................................................................................131 Considerations...............................................................................................................................132 Examples........................................................................................................................................132 SHOW MVS Command....................................................
SHOW TABLE Command...................................................................................................................143 Syntax.............................................................................................................................................143 Considerations...............................................................................................................................143 Examples.......................................................................
Syntax.............................................................................................................................................159 Considerations...............................................................................................................................160 Example.........................................................................................................................................160 ALTER DS CPULIST Command............................................
INFO DS Command............................................................................................................................171 Syntax.............................................................................................................................................171 Considerations...............................................................................................................................171 Example..................................................................
Example.........................................................................................................................................184 Index...............................................................................................................................
List of Figures 1-1 14 NCI Within a Neoview Platform Network...................................................................................
List of Tables 3-1 A-1 A-2 A-3 C-1 C-2 C-3 C-4 C-5 C-6 C-7 Launch and Login Parameters......................................................................................................38 NCI Commands That Can Be Run Without a Connection............................................................81 NCI Commands Supported in All Modes.....................................................................................96 PRUN Default Settings................................................................
About This Document This guide describes how to use the Neoview Command Interface (NCI) on a client workstation to manage a database on a Neoview data warehousing platform. NCI enables you to perform daily administrative tasks by running SQL statements or other commands interactively or from script files. Intended Audience This guide is intended for database administrators and support personnel who are maintaining and monitoring a Neoview database.
Chapter or Appendix New or Changed Information Appendix A: NCI Commands This appendix describes these new or changed interface commands: • “CONNECT Command”: You can specify two passwords—one for the user name and the other for the Neoview role—when connecting to the Neoview platform. • “DISCONNECT Command”: There are several NCI commands that you can run after you disconnect from the Neoview platform. • “MODE Command”: A new security mode called SEC is available for running security management commands.
DROP SCHEMA schema [CASCADE] [RESTRICT] DROP SCHEMA schema [ CASCADE | RESTRICT ] { } 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.
If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: myfile.sh 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.
Neoview Performance Analysis Tools User Guide Information about how to use the Neoview Performance Analysis Tools to analyze and troubleshoot query-related issues on the Neoview data warehousing platform. Neoview Repository User Guide Information about using the Repository, including descriptions of Repository views and guidelines for writing Neoview SQL queries against the views.
Publishing History Part Number Product Version Publication Date 613604-002 HP Neoview Release 2.5 September 2010 (613604-002) July 2010 (613604-001) 531835-001 HP Neoview Release 2.4 April 2009 544808-001 HP Neoview Release 2.3 April 2008 544590-001 HP Neoview Release 2.2 August 2007 544530-001 HP Neoview Release 2.1 May 2007 544356-001 HP Neoview Release 2.0 March 2007 HP Encourages Your Comments HP encourages your comments concerning this document.
1 Introduction to NCI Neoview Command Interface (NCI) is a command-line interface that you download and install on a client workstation that has the Neoview JDBC Type 4 Driver installed. Operating systems that support the JDBC driver include Windows, Linux, and UNIX. The JDBC driver connects NCI on a client workstation to a Neoview database on a Neoview data warehousing platform.
2 Installing and Configuring NCI • • • “Installing NCI” (page 25) “Setting the Look and Feel of NCI” (page 25) “Testing the Launch of NCI” (page 29) Installing NCI To install NCI, follow the procedures in the README for the HP Neoview Command Interface (NCI). Setting the Look and Feel of NCI To determine the look and feel of NCI, set the -DhpnciLF property by using the _JAVA_OPTIONS environment variable. This property affects the formatting of status messages.
*** Schema has been set. *** Total elapsed time was 16 second(s). SQL>select * from book; *** Total Query time was 1 second. BOOKID ----------13333 11111 12222 BOOKTITLE BOOKAUTHORID ISCHECKEDOUT ------------------------------ ------------ -----------UML Simplified 93333 0 C++ Internals 91111 0 Object Oriented Design 92222 0 *** Query completed. 3 rows found. 4 columns returned. *** Total elapsed time was 1 second(s).
Setting the Look and Feel in the System Properties on Windows 1. 2. Right-click the My Computer icon on your desktop and then select Properties. In the System Properties dialog box, select the Advanced tab and click Environment Variables. 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 25). For example: -DhpnciLF=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.
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. For example: export _JAVA_OPTIONS=-DhpnciLF=look-and-feel-type look-and-feel-type is one of the “Supported Look-and-Feel Types” (page 25). For example: export _JAVA_OPTIONS=-DhpnciLF=SQLPlus export _JAVA_OPTIONS=-DhpnciLF=BTEQ 3.
3 Launching NCI This chapter describes how to launch NCI from the Windows, Linux, or UNIX environment: • • • • • “Launching NCI on Windows” (page 31) “Launching NCI on Linux or UNIX” (page 34) “Logging In to the Database Platform” (page 35) “Using Optional Launch Parameters” (page 38) “Exiting NCI” For information about launching NCI from Perl or Python, see Chapter 6 (page 65). Launching NCI on Windows 1. Find the Windows launch file, hpnci.cmd, in the NCI bin folder: 2. Double-click the hpnci.
2. Type the location of hpnci.cmd within double quotes (“) or click Browse to locate that file, and then click Next: For the locations of the installed NCI software files, see the README for the HP Neoview Command Interface (NCI). 32 3. Type a name for the shortcut and click Finish: 4.
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.cmd" and add the optional launch parameters: For more information, see “Using Optional Launch Parameters” (page 38). d. Click OK.
5. 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 35). Launching NCI on Linux or UNIX In the terminal window, enter: ./hpnci-installation-directory/nci/bin/hpnci.
Logging In to the Database Platform • • • “Logging In Without Using Login Parameters” (page 35) “Using Login Parameters” (page 36) “Retrying the Login” (page 37) NOTE: You must be authorized to log in to the database platform. To create a user ID to log in to the database, see the Neoview User Management and Security Administration Guide. Logging In Without Using Login Parameters NOTE: If you launch NCI from within HPDM, the NCI session inherits the connection attributes of the HPDM session.
5. After you finish logging in to the database platform, the SQL prompt appears: Welcome to the HP Neoview Command Interface 2.5 (c) Copyright 2006-2010 Hewlett-Packard Development Company, LP. 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 an NCI command. For more information, see Chapter 4 (page 45).
Retrying the Login NCI allows you to reenter the login values, with a maximum of three retries, before it closes the session. NCI applies the retry logic as follows: • If you specify an invalid host name, NCI prompts you to reenter the host name. For example: hpnci –h dd Welcome to the HP Neoview Command Interface 2.5 (c) Copyright 2006-2010 Hewlett-Packard Development Company, LP. Unknown Host: dd Host Name/IP Address: 172.16.1.1 User Name: super.
SQL> • NCI does not prompt you to reenter the login values in these cases: — If the login fails with an error stating the data source was not started — When you include the -q or -version parameter on the command line (The -s parameter permits login retries.) — For a session started using redirected or piped input In these cases, NCI returns an error message and closes the session. You must re-launch the NCI session to connect to the Neoview platform.
Table 3-1 Launch and Login Parameters (continued) Launch or Login Parameter Description {-p | -password} password Specifies the password of the user for logging in to the database platform. password is case-sensitive. Depending on the security policies in force at your site, you might be required to supply two passwords—your own user password and a role password.
Running a Command When Launching NCI To execute an SQL statement or an NCI command when launching the Neoview Command Interface, use the -q or -sql command-line parameter. This parameter enables you to run a single command on the command line without having to enter commands in NCI. NOTE: You cannot specify this parameter at the same time as the -s or -script parameter. When using -q or -sql, you must enclose the command in double quotes.
Running a Script File When Launching NCI To run a script file when launching NCI, use the -s or -script command-line parameter. NOTE: You cannot specify this parameter at the same time as the -q or -sql parameter. After you launch NCI with -s or -script, NCI executes the script file in interactive mode. NCI remains open until you enter the EXIT, QUIT, or DISCONNECT command.
Launching NCI Without Connecting to the Database To start NCI without connecting to the Neoview platform, use the -noconnect option. See Table A-1 (page 81) for a list of NCI commands that can be run without a connection. Example of Launching NCI File With -noconnect • On Windows, in the Command Prompt window, enter: cd hpnci-installation-directory\Neoview Command Interface\bin hpnci.cmd -noconnect • On Linux or UNIX, in the terminal window, enter: cd hpnci-installation-directory/nci/bin ./hpnci.
Exiting NCI To exit NCI, enter one of these commands at a prompt: • • EXIT QUIT For example: SQL>quit These commands are not case-sensitive and do not require a terminator before you press Enter. After you enter one of these commands, NCI immediately quits running and disappears from the screen.
4 Running Commands Interactively in 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 48). 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 81) or “SHOW SESSION Command” (page 138). 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.
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_SALES ODBC_SCHEMA ODBC_TEST PERSNL PUBLIC_ACCESS_SCHEMA ROLEDBA ROLEMGR ROLEUSER SALES SCH SERVIC
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 146).
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 52).
Running SQL Statements In NCI, you can run SQL statements interactively. NCI supports all the SQL statements, SQL utilities, and other SQL-related commands that the Neoview database engine supports. For more information about those SQL statements, see the Neoview SQL Reference Manual. For SQL statements that are available only to HP Support, see the Neoview Database Support Guide.
90000.00 56000.00 --- 11 row(s) selected. SQL> For more information, see the “/ Command” (page 76), “RUN Command” (page 105), 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.
--- SQL command prepared. 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 ?dn 1500 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.
LARRY JIM GEORGE OTTO TIM TED PETER MARK HEIDI ROCKY SUE MARTIN HERBERT JESSICA CLARK HERMAN STRICKER SCHNABL WALKER MCDONALD SMITH FOLEY WEIGL LEWIS CRAMER SCHAEFFER KARAJAN CRINER 1000 3000 3100 3200 3000 2000 3300 4000 3200 2000 1000 3200 3200 3500 --- 35 row(s) selected.
Logging Output To log a NCI session, use the SPOOL or LOG command. The SPOOL and LOG commands record into a log file the commands that you enter in the NCI interface and the output of those commands.
Otherwise, each session writes information to the same log file, making it difficult to determine which information belongs to each session. Stopping the Logging Process To stop logging, enter one of these commands: • • SPOOL OFF LOG OFF 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.
5 Running Scripts in NCI In NCI, you can run script files. • • • • “Creating a Script File” (page 61) “Running a Script File” (page 62) “Logging Output” (page 63) “Running Scripts in Parallel” (page 63) Creating a Script File A script file that you run in NCI must be an ASCII text file that contains only these elements: • • • • “SQL Statements or Other Commands” (page 61) “NCI Commands” (page 61) “Comments” (page 61) “Section Headers” (page 62) For an example, see “Example of a Script File” (page 62).
Section Headers To create sections of commands within a script file, put a section header at the beginning of each section: ?SECTION section-name The section-name cannot begin with a number or an underscore. Each section name in a script file should be unique because NCI executes the first section that it finds that matches the section name in the @ or OBEY command. For more information, see the“@ Command” (page 75) or the “OBEY Command” (page 97).
NOTE: If the script file is outside the directory of the hpnci.cmd or hpnci.sh file (by default, the NCI bin directory), you must specify the full path of the script file in the @ or OBEY command. 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.
statements in the same script file. For more information on running scripts in parallel using the PRUN command, see the “PRUN Command” (page 100).
6 Running NCI From Perl or Python You can execute SQL statements in Perl or Python by invoking the NCI Perl or Python wrapper script. To use the Perl or Python wrapper script, see: • • • “Setting the Login Environment Variables” (page 65) “Perl and Python Wrapper Scripts” (page 68) “Launching NCI From the Perl or Python Command Line” (page 68) These instructions assume that you installed the Neoview Command Interface product. For more information, see Chapter 2 (page 25).
Setting Login Environment Variables in the System Properties 66 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.
Setting Login Environment Variables on the Command Line At each command prompt in any shell except the C shell, enter one of these commands: export HPNCI_PERL_JSERVER=absolute-path-of-JavaServer.jarexport HPNCI_PYTHON_JSERVER=absolute-path-of-Jython.jar export HPNCI_PERL_JSERVER_PORT=portnumber At each command prompt in the C shell, enter one of these commands: setenv HPNCI_PERL_SERVER=absolute-path-of-JavaServer.jar setenv HPNCI_PYTHON_JSERVER=absolute-path-of-Jython.
Example of a Perl Program (example.pl) use lib 'C:\\neo\\nci\\lib\\perl'; use Session; # create a session object $sess = Session->new(); # connect to the database $sess->connect("super.services","password","neo0101 ","18650","TDM_Default_DataSource"); $retval=$sess->execute(" set schema NEO.
A NCI Commands The Neoview Command Interface (NCI) supports these commands in NCI or in script files that you run in NCI. For a list of NCI commands that are available only to HP support, see the Neoview Database Support Guide. Command Description Syntax @ Runs the SQL statements and NCI commands contained in a specified script file. See the “@ Command” (page 75). / Runs the previously executed SQL statement. See the “/ Command” (page 76).
Command Description LABEL Marks a point in the command history See the “LABEL Command” that you can jump to by using the (page 91). GOTO command. LOCALHOST Executes client machine commands. LOG Logs commands and output from NCI See the “LOG Command” (page 93). to a log file. MODE Determines the operating mode of the See the “MODE Command” current session to be either SQL for (page 95). database commands, CS for connectivity service commands, or NS for the Neoview service commands.
Command Description Syntax SET MARKUP Sets the markup format and controls how results are displayed by NCI. See the“SET MARKUP Command” (page 114). SET PARAM Sets a parameter value in the current session. See the “SET PARAM Command” (page 117). SET PROMPT Sets the prompt of the current session See the “SET PROMPT Command” to a specified string or to a session (page 118). variable. SET SQLPROMPT Sets the SQL prompt of the current See the “SET SQLPROMPT session to a specified string.
Command Description SHOW MVS Displays all or a set of the materialized See the “SHOW MVS Command” views in the current schema of the NCI (page 132). session. SHOW PARAM Displays the parameters that are set in See the “SHOW PARAM Command” the current session. (page 134). SHOW PREPARED Displays the prepared statements in the current NCI session. See the “SHOW PREPARED Command” (page 134).
@ Command The @ command executes the SQL statements and NCI commands contained in a specified script file. The @ command is executed the same as the OBEY command. For more information on syntax and considerations, see the “OBEY Command” (page 97). Syntax @{script-file | wild-card-pattern} [(section-name)] script-file is the name of an ASCII text file that contains SQL statements, NCI commands, and comments.
/ Command The / command executes the previously executed SQL statement. This command does not repeat an NCI command. Syntax / Considerations • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. 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.
Considerations • • • • • The ALIAS command can be used in all modes. The ALIAS command requires the SQL terminator. You must enter the ALIAS command on one line. The ALIAS command lasts only for the duration of the session. An alias on an alias is not supported. Examples • This command creates an alias named .OS to perform the LOCALHOST (LH) command: SQL> ALIAS .OS AS LH; • This command executes the new ALIAS with the ls option: SQL>.OS ls hpnci-perl.pl hpnci-python.py hpnci.cmd hpnci.pl hpnci.
Example This ALLOW command gives access to the dumpfile. SQL>ALLOW SERVICES ACCESS TO DUMPFILES --- NS operation complete. CLEAR Command The CLEAR command clears the interface window so that only the prompt appears at the top of the window. CLEAR does not clear the log file or reset the settings of the session. Syntax CLEAR Considerations You must enter the command on one line. The command does not require an SQL terminator.
You might be required to supply two passwords if you are one of these types of users: • Power database user (that is, a locally authenticated database user) associated with ROLE.MGR, ROLE.SECMGR, or ROLE.DBA • Platform user associated with one of the platform roles: SUPER.name, HP.name, or NEOVIEW.name For more information about security policy options, see the Neoview User Management and Security Administration Guide.
Considerations • • • If seconds or minutes are not specified, the default is seconds. The maximum delay limit is 3600 seconds. You can override this value by setting hpnci.maxDelayLimit in _JAVA_OPTIONS. The unit is seconds for hpnci.maxDelayLimit. This command works in all modes and does not require an SQL terminator.
condition is the same as the condition parameter defined for the “IF...THEN Command” (page 90). See “Condition Parameter” (page 90). Considerations • • You must enter the command on one line. The command does not require an SQL terminator.
IDLETIMEOUT Current idle timeout value, which determines when the session expires after a period of inactivity. By default, the idle timeout is 30 minutes. For more information, see “Setting and Showing the Idle Timeout Value for the Session” (page 47) and “SET IDLETIMEOUT Command” (page 112). LIST_COUNT Current list count, which is the maximum number of rows that can be returned by SELECT statements. By default, the list count is all rows. For more information, see “SET LIST_COUNT Command” (page 113).
SCHEMA SERVER SERVICE NAME SQLTERMINATOR STATISTICS TIME TIMING USER • USR neo0101.acme.com:18650 HP_DEFAULT_SERVICE ; OFF OFF OFF role.dba This ENV command shows the effect of setting various session attributes: 4:16:43 PM >env COLSEP " " DATASOURCE TDM_Default_DataSource HISTOPT DEFAULT [No expansion of script files] IDLETIMEOUT 0 min(s) [Never Expires] LIST COUNT 0 [All Rows] LOG c:\mydir\examples.log LOOK AND FEEL NCI MARKUP RAW MODE SQL PROMPT 4:16:49 PM > ROLE DBA SCHEMA PERSNL SERVER neo0101.
Examples • This command disconnects from and exits the NCI, which disappears from the screen: SQL>exit • In a script file, the conditional exit command causes the script file to quit running and disconnect from and exit the NCI when the previously run command returns error code 4082: log c:\errorCode.log select * from employee; exit if errorcode=4082 log off These results are logged when error code 4082 occurs: SQL>select * from employee; *** ERROR[4082] Table, view or stored procedure NEO.USR.
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.
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. • Modify a previously executed statement by replacing a value in the WHERE clause with another value: SQL>select first_name, last_name +>from persnl.employee +>where jobcode=111; --- 0 row(s) selected.
215 216 225 232 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. Disk IOs number of actual disk IOs done by disk process.
--- 11 row(s) selected. SQL> get statistics; Start Time End Time Elapsed Time Compile Time Execution Time Table Name 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 Records Used Disk I/Os Message Count Message Bytes Lock Escl Lock Wait Disk Process Busy Time 2 2 0 4 15232 0 0 363 NEO.TOI.JOB --- SQL operation complete. GOTO Command The GOTO command allows you to jump to a designated point in the command history.
command-name is the name of an NCI command that is supported in the current operating mode. If you do not specify a command, NCI returns a list of all commands that are supported in the current mode. If you specify SET, NCI returns a list of all SET commands that are supported in the current mode. If you specify SHOW, NCI returns a list of all SHOW commands that are supported in the current mode. Considerations You must enter the command on one line. The command does not require an SQL terminator.
IF...THEN Command IF...THEN statements allow for the conditional execution of actions. If the condition is met, the action is executed; otherwise, no action is taken.
SQL> INVOKE Employees SQL> -- ERROR 4082 means the table does not exist SQL> IF ERRORCODE != 4082 THEN GOTO BeginPrepare SQL> CREATE TABLE Employees(SSN INT PRIMARY KEY NOT NULL NOT DROPPABLE, FName VARCHAR(50), LName VARCHAR(50), HireDate DATE DEFAULT CURRENT_DATE); SQL> LABEL BeginPrepare SQL> PREPARE empSelect FROM +> SELECT * FROM +> Employees +> WHERE SSN=?empSSN; SQL> IF USER == “alice” THEN SET PARAM ?empSSN 987654321; SQL> IF %USER == “bob” THEN SET PARAM ?empSSN 123456789; SQL> execute empSelect SQ
LOCALHOST Command The LOCALHOST command allows you to execute client machine commands. Syntax LOCALHOST | LH Considerations • • • • You must enter the command on one line. The command does not require an SQL terminator. The LOCALHOST command has a limitation. When input is entered for the operating system commands (for example, date, time, and cmd), the input is not visible until you hit the enter key. The LOCAL HOST command is supported in all modes.
LOG Command The LOG command logs the entered commands and their output from NCI to a log file. If this is an obey script file, then the command text from the obey script file is shown on the console. Syntax LOG { ON [CLEAR, QUIET, CMDTEXT {ON | OFF}] | log-file [CLEAR, QUIET, CMDTEXT {ON | OFF}] | OFF } ON starts the logging process and records information in the sqlspool.lst file in the NCI bin directory. CLEAR instructs NCI to clear the contents of the sqlspool.
SQL>log c:\log_files\sales_updates.log • This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Linux or UNIX workstation: SQL>log ./log_files/sales_updates.log • 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.
100 450 900 300 500 400 250 420 600 200 MANAGER PROGRAMMER SECRETARY SALESREP ACCOUNTANT SYSTEM ANALYST ASSEMBLER ENGINEER ADMINISTRATOR PRODUCTION SUPV --- 10 row(s) selected This command stops the logging process: SQL>log off For more information, see “Logging Output” (page 58).
WMS specifies Workload Management Services (WMS) mode and supports the use of WMS commands. For a complete description of the WMS commands, see the Neoview Workload Management Services Guide. In addition to the WMS commands, WMS mode supports a subset of the NCI commands. For a list, see Table A-2 (page 96). All other NCI commands are disallowed in WMS mode. SEC specifies security mode (SEC) mode and supports the use of all security management commands.
Examples • This command changes the NCI session to WMS mode operation: SQL>mode wms WMS> • This command returns the NCI session to SQL mode operation: WMS>mode sql SQL> • The .WMS prefix before the WMS command, INFO WMS, allows you to run that WMS command in SQL mode: SQL>.wms info wms; 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 wild-card pattern specified.
• If the (*) is issued in the OBEY command, all files are executed in the current directory. Some of the files in the directory could be binary files. The OBEY command tries to read those binary files and junk or invalid characters are displayed on the console.
SQL>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, --- 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.
PRUN Command The PRUN command runs script files in parallel. Syntax PRUN [ {-d | -defaults} ] | PRUN [ {-sd | -scriptsdir} scripts-directory] [ {-e | -extension} file-extension] [ {-ld | -logsdir} log-directory] [ {-o | -overwrite} {y | n}] [ {-c | -connections} num] -d | -defaults Specify this option to have PRUN use these default settings: Table A-3 PRUN Default Settings Parameter Default Setting -sd | -scriptsdir PRUN searches for the script files in the same directory as the hpnci.sh or hpnci.
• • • • • • you for more input. In the non-interactive mode, if any options are not specified, PRUN uses the default values. The -d or -defaults option cannot be specified with any other option. The PRUN log files also contain the log end time. PRUN does not support the SPOOL or LOG commands. Those commands are ignored in PRUN script files. The environment values from the main session (which are available through the SET commands) are propagated to new sessions started via PRUN.
-overwrite -connections y 5 Status: Complete _____________________________________________ PARALLELRUN(PRUN)SUMMARY _____________________________________________ Total files present ......................99 Total files processed ....................99 Total queries processed .................198 Total errors ..............................0 Total warnings ............................0 Total warnings ............................0 Total connections .........................5 Total connection failures........
log c:\errorCode.log select * from employee; quit if errorcode=4082 log off These results are logged when error code 4082 occurs: SQL>select * from employee; *** ERROR[4082] Table, view or stored procedure NEO.USR.EMPLOYEE does not exist or is inaccessible. SQL>quit if errorcode=4082 RECONNECT Command The RECONNECT command creates a new connection to the Neoview platform using the login credentials of the last successful connection.
Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. To reexecute the immediately preceding command, enter REPEAT without specifying a number. If you enter more than one command on a line, the REPEAT command reexecutes only the last command on the line.
Considerations You must enter the command on one line. The command does not require an SQL terminator. Examples This command resets the last error in the current session: SQL>select * from emp; *** ERROR[4082]Object NEO.SCH.EMP does not exist or is inaccessible. SQL>show lasterror LASTERROR 4082 SQL> reset lasterror SQL>show lasterror LASTERROR 0 RESET PARAM Command The RESET PARAM command clears all parameter values or a specified parameter value in the current session.
Considerations • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. Example This command executes the previously executed SELECT statement: SQL>select count(*) from persnl.employee; (EXPR) -------------------62 --- 1 row(s) selected. SQL>run (EXPR) -------------------62 --- 1 row(s) selected. SQL> SAVEHIST Command The SAVEHIST command saves the session history in a user-specified file.
Examples • This command clears the contents of an existing file named history.txt in the local directory (the same directory where you are running NCI) and saves the session history in the file: SQL>savehist history.txt clear SQL> • This command saves the session history in a file named hist.txt in the specified directory on a Windows workstation: SQL>savehist c:\log_files\hist.txt SQL> • This command saves the session history in a file named hist.
Considerations • • • • In the NCI interface, you must enter the SCHEDULE command on one line. The command does not require an SQL terminator. In NCI, you can execute the command in all modes. Automated REORG operations are first performed on the list of tables specified for automated UPDATE STATISTICS (by the CHG_AUTO_LIST() stored procedure) and then performed on the rest of the database tables.
SET COLSEP Command The SET COLSEP command sets the column separator and allows you to control the formatting of the result displayed for SQL queries. The SET COLSEP command specifies a delimiter value to use for separating columns in each row of the results. The default delimiter is “ ”(white space). Syntax SET COLSEP [separator] Considerations • • You must enter the command on one line. The SET COLSEP command has no effect if the markup is set to HTML, XML, or CSV.
Example The first command changes the role of the NCI session to ROLE.MGR, and the second command logs on the user as that role: SQL>set connectopt role ROLE.MGR SQL>connect johndoe@blip.com, mydsn Password: For more information, see the “CONNECT Command” (page 78). SET FETCHSIZE Command The SET FETCHSIZE command allows you to change the default fetchsize used by JDBC. Setting the value to 0 sets the fetchsize to the default value used in JDBC.
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. If the history option is set to “ALL,” all the commands in the script file are added to the history buffer. If no options are specified, DEFAULT is used. Syntax SET HISTOPT [ALL|DEFAULT] Considerations You must enter the command on one line.
---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.sch; 6> INSERT INTO COURSE1 VALUES ('C11','INTRO TO CS','FOR ROOKIES',3, 100, 'CIS'); 7> INSERT INTO COURSE1 VALUES ('C55','COMPUTER ARCH.','VON NEUMANN''S MACH.',3,100, 'CIS'); SET IDLETIMEOUT Command The SET IDLETIMEOUT command sets the idle timeout value for the current session.
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. Syntax SET LIST_COUNT num-rows num-rows is a positive integer that specifies the maximum number of rows of data to be displayed by SELECT statements that are executed after this command. Zero means that all rows of data are returned.
... 995 Walt Farley --- 62 row(s) selected. SQL> SET MARKUP Command The SET MARKUP command sets the markup format and controls how results are displayed by NCI. 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. Considerations • • • You must enter the command on one line.
| 600480 | 2003-05-12 | | 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.543,FRESNO STATE BANK ,300350,2003-03-03 5635,ROYAL CHEMICALS ,101220,2003-07-21 21,CENTRAL UNIVERSITY,200320,2003-02-17 1234,DATASPEED ,100210,2003-04-10 3210,BESTFOOD MARKETS ,800660,2003-10-09 • This command specifies results be displayed in XML: SQL>set markup xml SQL>select * from author ^ ]]<>/ErrorMsg> • This command displays CSV like output using the COLSEP value as a separator. SQL>set colsep | SQL>set markup colsep SQL>select * from employee; 32|THOMAS |RUDLOFF 39|KLAUS |SAFFERT 89|PETER |SMITH| 29|JANE |RAYMOND 65|RACHEL |MCKAY 75|TIM |WALKER 11|ROGER |GREEN 93|DONALD |TAYLOR |2000|100|138000.40 |3200|100|75000.00 |3300|300|37000.40 |3000|100|136000.00 |4000|100|118000.00 |3000|300|320000.00 |9000|100|175500.00 |3100|300|33000. Considerations • • • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. Use separate SET PARAM commands to name and assign values to each unique parameter in a prepared SQL statement before running the EXECUTE statement. Parameter names are case-sensitive. If you specify a parameter name in lowercase in the SET PARAM command, you must specify it in lowercase in other statements, such as DML statements or EXECUTE. %ROLE displays the current Neoview role 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. %SCHEMA displays the session schema as the prompt. %DATASOURCE displays the session data source as the prompt. Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. Unlike SET SQLPROMPT, you can execute this command in all NCI modes. SQL>set prompt %server> neo0101.mylab.mycorp.net:18650> • This command displays the session schema for the prompt: SQL>set prompt "Schema %schema:" Schema USR: • This command displays the session data source for the prompt: SQL>set prompt "%datasource SQL>" TDM_Default_DataSource SQL> • This command displays multiple session variables: SQL>set prompt %USER@%SCHEMA> super. Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. SET SQLTERMINATOR Command The SET SQLTERMINATOR command sets the SQL statement terminator of the current session. The default is a semicolon (;). 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. Examples This command shows the default output format as PERTABLE: SQL>set statistics on SQL>select * from job; JOBCODE JOBDESC ------- -----------------100 MANAGER 1234 450 PROGRAMMER 900 SECRETARY 300 SALESREP 500 ACCOUNTANT 400 SYSTEM ANALYST 250 ASSEMBLER 420 ENGINEER 600 ADMINISTRATOR 200 PRODUCTION SUPV --- 11 row(s) selected. Start Time End Time Elapsed Time Compile Time Execution Time Table Name 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. 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 47). SHOW ACCESS Command The SHOW ACCESS command displays the current status of access information to memory dump files. Syntax SHOW ACCESS TO DUMPFILES Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. Only ROLE.SECMGR can execute this command. You can execute this command only in SQL mode. Example This SHOW ACCESS command displays the current status of the dumpfile. SQL>show access to dump files ACCESS TO DUMP FILES DENIED -- NS operation complete. double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters. % Use a percent sign (%) to indicate zero or more characters of any type. For example, %art% matches SMART, ARTIFICIAL, and PARTICULAR but not smart or Hearts. "%art%" matches smart and Hearts but not SMART, ARTIFICIAL, or PARTICULAR. * Use an asterisk (*) to indicate zero or more characters of any type. .GOTO AS GOTO USE AS SET SCHEMA SHOW COLSEP Command The SHOW COLSEP command displays the value of the column separator for the current NCI session. Syntax SHOW COLSEP Considerations • • You must enter the command on one line. The command does not require an SQL terminator. If the SET TIMING command is set to ON, the elapsed time information is displayed. Examples • This command displays the column separator. SHOW FETCHSIZE Command The SHOW FETCHSIZE command displays the fetch size value for the current NCI session. Syntax SHOW FETCHSIZE Considerations • • You must enter the command on one line. This command is supported in all modes. SHOW IDLETIMEOUT Command The SHOW IDLETIMEOUT command displays the idle timeout value of the current NCI session. The idle timeout value of a session determines when the session expires after a period of inactivity. The default is 30 minutes. Syntax SHOW IDLETIMEOUT Considerations • • You must enter the command on one line. The command does not require an SQL terminator. If the SET TIMING command is set to ON, the elapsed time information is displayed. Examples This command shows the last error in the current session: SQL>select * from emp; *** ERROR[4082]Object NEO.SCH.EMP does not exist or is inaccessible. SQL>show lasterror LASTERROR 4082 SHOW LIST_COUNT Command The SHOW LIST_COUNT command displays the maximum number of rows to be returned by SELECT statements in the current NCI session. The default is zero, which means that all rows are returned. Syntax SHOW LIST_COUNT Considerations • • • You must enter the command on one line. Examples This command displays the value set for the markup option: SQL>show markup MARKUP RAW Elapsed time:00:00:00:078 SHOW MODE Command The SHOW MODE command displays the operating mode of the current NCI session. The default is SQL. Syntax SHOW MODE Considerations • • You must enter the command on one line. The command does not require an SQL terminator. If the SET TIMING command is set to ON, the elapsed time information is displayed. Considerations • • • • • You must enter the command on one line. The command does not require an SQL terminator. If you do not specify a wild-card pattern in a SHOW MVGROUPS command, NCI displays all the materialized view groups that exist in the current schema. If you specify a wild-card pattern in a SHOW MVGROUPS command, NCI displays only the materialized view group names that match the wild-card pattern. You can execute this command only in SQL mode. it within double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters: % Use a percent sign (%) to indicate zero or more characters of any type. For example, %art% matches SMART, ARTIFICIAL, and PARTICULAR but not smart or Hearts. "%art%" matches smart and Hearts but not SMART, ARTIFICIAL, or PARTICULAR. * Use an asterisk (*) to indicate zero or more characters of any type. SHOW PARAM Command The SHOW PARAM command displays the parameters that are set in the current NCI session. Syntax SHOW PARAM Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. If the SET TIMING command is set to ON, the elapsed time information is displayed. Example • This command shows that parameters that are set for the current session: SQL>show param lastname GREEN dn 1500 sal 40000. SQL> show prepared s% S1 select * from t1 S2 select * from student SQL> show prepared t% T1 select * from test123 SHOW PROCEDURES Command The SHOW PROCEDURES command displays the procedures (Stored Procedures in Java) in the current schema of the NCI session. If a pattern is specified, all procedures matching the pattern are displayed. By default, all procedures in the current schema of the NCI session are displayed. ------------------------------------------------------------------------ADJUSTSALARY DAILYORDERS EMPLOYEEJOB HELLO HELLOSTRING LOWERPRICE MONTHLYORDERS MYPROC N0101 N0122 N0124 N0200 N0202 N0209 N0210 N0211 N0212 N0213 N0216 N0226A N0302 N4210 N4260 N4261 N4264 N4267 SHOW RECCOUNT Command The SHOW RECCOUNT command displays the record count of the previously executed SQL statement. If the previously executed command was an interface command, NCI returns zero. SHOW SCHEMA Command The SHOW SCHEMA command displays the current schema of the NCI session. Syntax SHOW SCHEMA Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. If the SET TIMING command is set to ON, the elapsed time information is displayed. Examples • This command shows all the schemas that exist in the default catalog of the current session: 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 SERVICE LOG Current log file and the directory containing the log file. By default, logging during a session is turned off. For more information, see “Logging Output” (page 58) and “LOG Command” (page 93) or “SPOOL Command” (page 148). LOOK AND FEEL Current look and feel of the NCI. This property affects the formatting of status messages. For more information, see “Setting the Look and Feel of NCI” (page 25). MARKUP Current markup option selected for the session. The default option is RAW. TIMING USER • OFF role.dba This command shows the effect of setting various session attributes: SQL>session 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 ALL 30 min(s) 0 [All Rows] OFF NVS RAW SQL SQL > DBA USR neo0101.acme.com:18650 HP_DEFAULT_SERVICE ; OFF OFF OFF role. Considerations • • • You must enter the command on one line. The command does not require an SQL terminator. You can execute this command only in SQL mode. If the SET TIMING command is set to ON, the elapsed time information is displayed. Example This command shows that the SQL terminator for the current session is a period (.): SQL>show sqlterminator SQLTERMINATOR . For more information, see “Setting and Showing the SQL Terminator” (page 48). within double quotes. To look for similar values, specify only part of the characters of wild-card-pattern combined with these wild-card characters: % Use a percent sign (%) to indicate zero or more characters of any type. For example, %art% matches SMART, ARTIFICIAL, and PARTICULAR but not smart or Hearts. "%art%" matches smart and Hearts but not SMART, ARTIFICIAL, or PARTICULAR. * Use an asterisk (*) to indicate zero or more characters of any type. SHOW TABLE Command The SHOW TABLE command displays information about the indexes, materialized views, or synonyms of a specified table or materialized view. Syntax SHOW TABLE {table-name | materialized-view-name}, { INDEXES | MVS | SYNONYMS | ALL} table-name is: [schema-name.]table-name table-name specifies the name of a table. If you do not fully qualify the table-name, NCI uses the current schema. materialized-view-name specifies the name of a materialized view. ------------------- ----- ---------- ------ ----------- -------Index 1 :EMPLOYEE ----------------EMPNUM ASC Other Yes 0 1 Index 2 :XEMPDEPT ----------------DEPTNUM ASC Other No 0 1 ASC ASC Other Other No No 0 0 1 2 Index 3 :XEMPNAME1 ----------------LAST_NAME FIRST_NAME SQL> • This command shows information about the materialized views of the CUSTOMERS table: SQL>show table customers, mvs MATERIALIZED VIEW NAME ---------------------MYSCH. SHOW TABLES Command The SHOW TABLES command displays all or a set of the tables that exist in the current schema of the NCI session. Syntax SHOW TABLES [wild-card-pattern] wild-card-pattern is a character string used to search for and display tables with names that match the character string. wild-card-pattern matches an uppercase string unless you enclose it within double quotes. SQL> For more information, see “Showing the Tables in a Schema” (page 50). SHOW TIME Command The SHOW TIME command displays whether the setting for the local time in the interface prompt is ON or OFF. Syntax SHOW TIME Considerations • • You must enter the command on one line. The command does not require an SQL terminator. If the SET TIMING command is set to ON, the elapsed time information is displayed. wild-card-pattern is a character string used to search for and display 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. For example, %art% matches SMART, ARTIFICIAL, and PARTICULAR but not smart or Hearts. SPOOL Command The SPOOL command logs the entered commands and their output from the NCI to a log file. Syntax SPOOL { ON [CLEAR, QUIET, CMDTEXT {ON | OFF}] | log-file [CLEAR, QUIET, CMDTEXT {ON | OFF}] | OFF } ON starts the logging process and records information in the sqlspool.lst file in the NCI bin directory. ON CLEAR instructs NCI to clear the contents of the sqlspool.lst file before logging new information to the file. • This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Linux or UNIX workstation: SQL>spool ./log_files/sales_updates.log • This command starts the logging process and clears existing information from the log file before logging new information to the file: SQL>spool persnl_ddl.log clear • This command starts the logging process and records information to the sqlspool. 250 420 600 200 ASSEMBLER ENGINEER ADMINISTRATOR PRODUCTION SUPV --- 10 row(s) selected • This command start the logging process, clears existing information from the log file, and specifies that no output appears on the console window: SQL>log c:\temp\b.txt clear, cmdtext off, quiet SQL>select * +>from neo.toi.job; SQL> log off Output of c:\temp\b. Example • This command shows versions of the Neoview platform, Neoview Connectivity Services, Neoview JDBC Type 4 Driver, and NCI: SQL>version Neoview Neoview Neoview Neoview SQL> • Platform Connectivity Services JDBC Type 4 Driver Command Interface : : : : N02.05.00 T7970N29_18JUN2010_SRV_0706 T1249_N29(R2.5)_18JUN10_HP_JDBCT4_2010_07_06 T0774N29_(R2. B Connectivity Service Commands IMPORTANT: Neoview Release 2.5 introduces a set of connectivity commands that can be run in SQL mode using the CMDOPEN and CMDCLOSE commands. While you can still use existing connectivity service commands, you are strongly encouraged to start using the new connectivity commands. For more information, see Appendix C (page 155). NCI supports connectivity service commands in the connectivity service (CS) mode. Terms used in the INFO DS reports are: 154 DsName Name of the data source this report is about. C Connectivity Commands This appendix describes the connectivity commands that you can run interactively or in script files in NCI. NOTE: NCI supports connectivity commands in SQL mode, provided that you run CMDOPEN beforehand. After you run CMDOPEN, NCI recognizes only connectivity commands at the SQL> prompt and does not recognize SQL statements. To end CMDOPEN's effect on the session and to be able run SQL statements, run CMDCLOSE. SRVRIDLETIMEOUT timeout-value is the number of minutes an NDCS server waits in the available state for a connection before it stops itself when the count of NDCS servers exceeds the AVAILSERVER count for the data source. The default value is SYSTEM_DEFAULT, which is defined by NDCS (10 minutes). Other valid values are "-1" for no timeout and an integer of one to a large number for "num-minutes". Double quotes are required around a value of -1 or num-minutes; double quotes are optional around SYSTEM_DEFAULT. 0 specifies the ENV_SET environment variable, which supports a SET command. 1 specifies the ENV_CONTROL environment variable, which supports a CONTROL command (CONTROL QUERY DEFAULT or CONTROL TABLE). 2 specifies the ENV_DEFINE environment variable, which supports a DEFINE command. DSNAME data-source-name is the name of the data source. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. "attribute-name" is the name of a resource management policy attribute that limits the execution of user queries from a client application, based on cost. Currently, ESTIMATED COST is the only attribute supported. DSNAME data-source-name is the name of the data source. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. LIMITVALUE value is the maximum value for the attribute. Specify any positive number. ACTION is the action to be taken by the NDCS server when the limit is exceeded. PERMISSION OPERATOR assigns OPERATOR privileges to the role. Users associated with roles that have OPERATOR privileges can configure and manage NDCS services, data sources, and servers. Considerations • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. You must have OPERATOR privileges to display and change the NDCS privileges. Initially, only members of SUPER.SUPER, SUPER.SERVICES, ROLE. by MAXSERVER count. The default value is 0. This value cannot exceed the MAXSERVER count. To permit servicing incoming users, the data source goes to the STARTED state as soon as the first server starts. These servers count as idle if they are not allocated to new users immediately. NOTE: If you set the STARTAHEAD value, the NDCS service ignores this value and does not use it. Instead, you should set the INITSERVER value. The NDCS service uses the INITSERVER value instead of STARTAHEAD. ALTER DS CPULIST Command The ALTER DS CPULIST command add or alters the list of processing nodes (CPUs) where the NDCS service can start NDCS servers for the specified data source. The CPULIST attribute is stored as an environment value of type ENV_CPULIST. Syntax ALTER DS data-source-name, CPULIST "cpu-value" cpu-value is: SYSTEM_DEFAULT | num1[, num2]...[, num15] | range(num1-num2) data-source-name is the name of the data source. ALTER DS PRIORITY Command The ALTER DS PRIORITY command adds or alters the process priority of the specified data source. The PRIORITY attribute is stored as an environment value of type ENV_PROCESSPRIORITY. Syntax ALTER DS data-source-name, PRIORITY priority-value priority-value is: SYSTEM_DEFAULT | "value" data-source-name is the name of the data source. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. ALTER DS STATS Command The ALTER DS STATS command adds or alters the statistics flags for the specified data source. The statistics flags determine whether SQL statement statistics are written to the alternate EMS collector when an SQL operation is performed. The statistics attribute is stored as an environment value of type ENV_STATISTICS. • • If the statistics attribute exists, this command modifies it according to the new values. If the statistics attribute does not exist, this command inserts the new values. Example This command turns ALLSTAT on for the DDLTestDS data source: SQL>alter ds DDLTestDS, allstat on; ALTER DS TRACE Command The ALTER DS TRACE command changes the tracing flag for the specified data source running in an NDCS service. ALTER EVAR Command The ALTER EVAR command changes the values of a specific type of environment variable for the specified data source. Syntax ALTER EVAR type, DSNAME data-source-name, VALUE "string" type is: 0 | 1 | 2 0 specifies the ENV_SET environment variable, which supports SET commands. 1 specifies the ENV_CONTROL environment variable, which supports CONTROL commands. 2 specifies the ENV_DEFINE environment variable, which supports DEFINE commands. ALTER RESOURCE Command The ALTER RESOURCE command changes the resource management policy for the specified data source. Syntax ALTER RESOURCE "attribute-name", DSNAME data-source-name, LIMITVALUE value, ACTION "action" action is: LOG | LOG_WITH_INFO | STOP "attribute-name" is the name of a resource management policy attribute that limits the execution of user queries from a client application, based on cost. Currently, ESTIMATED COST is the only attribute supported. ALTER USER Command The ALTER USER command changes the NDCS privileges of a Neoview role. Syntax ALTER USER role-name, PERMISSION type type is: USER | OPERATOR role-name is the name of a Neoview role, such as ROLE.MGR. PERMISSION USER assigns USER privileges to the role. Users associated with roles that have USER privileges do not have NDCS privileges and cannot change the status or configuration of the NDCS services, data sources, or servers. PERMISSION OPERATOR assigns OPERATOR privileges to the role. • • To run the connectivity commands in SQL mode, you must run the CMDOPEN command first. If you do not run CMDOPEN and try to run one of the connectivity commands, NCI returns a syntax error. After you run CMDOPEN, NCI recognizes only connectivity commands. To issue SQL statements, you must run CMDCLOSE. Otherwise, an SQL statement returns an error or does not display results. Example This CMDOPEN command enables you to run connectivity commands in SQL mode: SQL>cmdopen; --- SQL operation complete. DELETE DS Command The DELETE DS command removes a data source from the NDCS configuration. Deleting a data source removes all the environment and resource policies associated with the data source. Syntax DELETE DS data-source-name data-source-name is the name of the data source to be deleted. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. It cannot be the default data source, TDM_Default_DataSource. DSNAME data-source-name is the name of the data source. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. Considerations • • • • • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. Only users associated with roles that have OPERATOR privileges can execute this command. For more information, see the “ADD USER Command” (page 158) or “ALTER USER Command” (page 167). DELETE USER Command The DELETE USER command removes a Neoview role and its privileges from the NDCS configuration. Syntax DELETE USER role-name role-name is the name of a Neoview role, such as ROLE.MGR. Considerations • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. You must have OPERATOR privileges to display and change the NDCS privileges. Initially, only members of SUPER.SUPER, SUPER. Table C-1 INFO DS Output Column Description DSNAME Name of the data source MAXSRVRCNT Maximum number of NDCS servers that the NDCS service can start for this data source AVAILSRVRCNT Number of NDCS servers that are available for client connections when the data source is in the started state INITSRVRCNT Number of NDCS servers that the NDCS service starts for the data source when the data source starts STARTAHEADCNT Not used SRVRIDLETIMEOUT Number of minutes an NDCS server waits in the available DSNAME data-source-name is the name of the data source. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. Considerations • • • • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. The wild card (*) is not supported for the data source name, and the data source name is case-sensitive. Considerations • • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. The wild card (*) is not supported for the data source name, and the data source name is case-sensitive. If you do not specify the name of a data source, this command displays the resource management policies defined for each data source in the NDCS configuration. Table C-4 INFO USER Output Column Description USER_NAME Neoview role to which the privilege was granted PERMISSION Type of privilege: — USER — OPERATOR Roles that have USER privileges do not have NDCS privileges and cannot change the status or configuration of the NDCS services, data sources, or servers. Roles that have OPERATOR privileges can configure and manage NDCS services, data sources, and servers. SQL>list service; START DS Command The START DS command starts a data source under a specified service. New clients can then use this data source. Syntax START DS $service-name, DSNAME data-source-name $service-name is the name of the association server of the NDCS service where the data source should be started. DSNAME data-source-name is the name of the data source to be started. data-source-name is case-sensitive and cannot be the asterisk (*) wild card. Considerations • • • • • To run this command in SQL mode, you must first run the CMDOPEN command. This command does not run in CS mode. The command requires an SQL terminator. Only users associated with roles that have OPERATOR privileges can execute this command. For more information, see the “ADD USER Command” (page 158) or “ALTER USER Command” (page 167). The association server can be in any state but it must exist. Table C-5 STATUS DS Output (continued) Column Description DEFAULTFLAG Flag indicating the default data source: — If this value is 1, the data source is the default data source. — If this value is 0, the data source is not a default data source. • • If you do not specify the name of a data source, the command displays the status of the NDCS servers for all data sources in the NDCS service. If you specify the name of a data source, the command displays the status of the NDCS servers for that data source in the NDCS service. Table C-6 STATUS SERVER Output (continued) Column Description SRVRMINORVERSION For internal use only SRVRBUILDID For internal use only Example This command displays the status of the NDCS servers for the $MXOAS service: SQL>status server $mxoas; STATUS SERVICE Command The STATUS SERVICE command displays the status of the association and configuration servers for an NDCS service. Syntax STATUS SERVICE $service-name $service-name is the name of the association server of the NDCS service. Table C-7 STATUS SERVICE Output (continued) Column Description ASPROCESSNAME Internal name of the system in which the association server is running For example: \WMS0101. STOP DS Command The STOP DS command stops a data source under a specified service. The actual termination occurs at different times, depending on the STOPMODE attribute that you specify. The data source enters the stopped state only after all of its NDCS servers are stopped. Example This command stops the data source DDLTestDS being used by the service $MXOAS and also stops all the NDCS servers that are using this data source: SQL>stop ds $mxoas, dsname DDLTestDS, stopmode immediate, reason 'test_stop'; STOP SERVER Command The STOP SERVER command puts the NDCS server in the stopping state. Syntax STOP SERVER $service-name.port-number, DIAGID dialog-id $service-name is the name of the NDCS service in which the server to be stopped is running. $service-name is the name of the association server of the NDCS service to be stopped. STOPMODE specifies how quickly the command stops the NDCS service, data sources, and servers. Choose one of these values: IMMEDIATE terminates all NDCS servers immediately. A client connected to the NDCS server receives the communication link failure error on the next action that accesses the server. The client is expected to either disconnect and connect to another NDCS server or quit. Index Symbols -DhpnciLF property, 25 -dsn parameter, 39 -h parameter, 38 -help description of, 39 examples of, 42 -host parameter, 38 -noconnect description of, 39 examples of, 42 -p parameter, 39 -password parameter, 39 -q parameter description of, 39 examples of, 40 -r parameter, 38 -role parameter, 38 -s parameter description of, 39 examples of, 41 -script parameter, 39 -sql parameter, 39 -u parameter, 38 -user parameter, 38 -version description of, 39 examples of, 42 / command example of, 53 syntax of, H HELP command, 88 HISTORY command, 89 Host name, 35 HP_ prefix in schema names, 40 hpnci.bat, creating a shortcut to, 31 hpnci.pl, description of, 68 hpnci.py, description of, 68 hpnci.sh, setting the path of, 34 HPNCI_PERL_JSERVER environment variable, 65 HPNCI_PERL_JSERVER_PORT environment variable, 65 HPNCI_PYTHON_JSERVER environment variable, 65 I Idle timeout value, 47 IF... R RECONNECT command, 103 REPEAT command, 103 Reserved schema names, 40 RESET LASTERROR command, 104 RESET PARAM command examples of, 55 syntax of, 105 RUN command, 105 S SAVEHIST command, 106 SCHEDULE command, 107 Schema names (reserved), 40 setting the current schema, 49 showing all schemas in the default catalog, 50 showing the current schema, 49 wild-card search, 137 Script file comments, 61 creating, 61 example of, 62 running multiple files in parallel, 63 running one file at a time, 62 running when la SQL statement breaking across lines, 45 displaying the elapsed time, 48 editing, 84, 87 preparing and executing, 54 repeating, 53, 103 running in the NCI, 53 running when launching NCI, 40 terminating, 46 SQL terminator setting, 48 showing, 48 SQLPlus look and feel, 25 Standard prompt customizing, 47 description of, 45 displaying the current time, 47 START DS command, 176 START SERVICE command, 176 STATUS DS command, 177 STATUS SERVER command, 178 STATUS SERVICE command, 180 STOP DS command, 182 STOP SERVER |
