ISQL and Tools Reference Guide For use with c-treeSQL Server This manual provides reference material for the ISQL interactive SQL utility and other administrative tools provided in the c-treeSQL environment. It also includes a tutorial describing how to use the ISQL utility.
Copyright © 1992-2004 FairCom Corporation All rights reserved. Portions © 1987-2004 Dharma Systems, Inc. All rights reserved. Eleventh Edition, First printing: September 2003 Information in this document is subject to change without notice. No part of this publication may be stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise without the prior written permission of FairCom Corporation. Printed in the United States of America.
Table of Contents Documentation Overview Purpose of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v Syntax Diagram Conventions . . . . . . . . . . .
3.5 The HELP and TABLE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.6 Transaction Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.7 ISQL Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-12 3.7.1 @ (Execute) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.4 Data File Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 5.5 The Commands File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 5.5.1 The DEFINE RECORD Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3 5.5.2 The FOR RECORD Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4 5.6 Examples. . .
iv FairCom Corporation
Documentation Overview PURPOSE OF THIS MANUAL This manual provides reference material for the ISQL interactive SQL utility as well as the dbload, dbdump, and dbschema administrative tools provided in the c-treeSQL environment. It also includes a tutorial describing how to use the ISQL utility. AUDIENCE The reader of this manual should be familiar with general SQL concepts.
ISQL and Tools lowercase Lowercase type denotes either user-supplied elements or names of other syntax diagrams. User-supplied elements include names of tables, hostlanguage variables, expressions, and literals. Syntax diagrams can refer to each other by name. If a diagram is named, the name appears in lowercase type above and to the left of the diagram, followed by a double-colon (for example, privilege ::). The name of that diagram appears in lowercase in diagrams that refer to it.
Chapter 1 Introduction 1.1 OVERVIEW Interactive SQL (often referred to throughout this manual as ISQL) is a utility supplied with ctreeSQL that lets you issue SQL statements directly from a terminal and see results displayed at the terminal.
ISQL and Tools 1-2 FairCom Corporation
Chapter 2 Quick Tour 2.1 INTRODUCTORY TUTORIAL iSQL_Tutorial1.sql This introductory tutorial will rapidly take you through the basic use of the powerful interactive SQL (iSQL) database interface. iSQL is a full featured command line client side query tool useful for submitting ad hoc SQL statements to a Server. Likewise the tool provides ample output formatting capabilities. By no means does this introduction cover the full scope, detail, or flexibility that iSQL offers.
ISQL and Tools 2.1.2 Define In this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement in which specific fields are defined. Upon successful creation of the table, the changes made to the database by this transaction are made permanent by executing the COMMIT WORK statement. The following SQL syntax provides the functionality for the define phase: • CREATE TABLE — Create a table. • COMMIT WORK — Make changes permanent.
Quick Tour VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'); ISQL> INSERT INTO CUSTMAST VALUES ('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta'); ISQL> INSERT INTO CUSTMAST VALUES ('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia'); ISQL> COMMIT WORK; Display Records ISQL> SELECT * FROM CUSTMAST; Delete Records ISQL> DELETE FROM CUSTMAST; ISQL> COMMIT WORK; 2.1.
ISQL and Tools 2.2 RELATIONAL MODEL AND INDEXING TUTORIAL iSQL_Tutorial2.sql This intermediate tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables and building a relational model. This tutorial will walk you through defining an index for each table, demonstrating the power of indexes in a relational model using a few simple API calls. This tutorial operates on the assumption that the database named 'myDatabase', already exists. Please refer to Section 3.
Quick Tour OrderList - A table of records consisting of a list of orders. OrderItem - A table of records consisting of specific items associated with an order. ItemMaster - A table of records consisting of information about items. CustomerMaster - A table of records consisting of specific info related to each customer. Each order (ordernum) in the orderlist table will contain 1 or more items (itemnum) in the orderitem table.
ISQL and Tools ISQL> CREATE INDEX itemnum ON itemmast (im_itemnum); ISQL> CREATE TABLE custmast ( cm_custnum VARCHAR(5), cm_zip VARCHAR(10), cm_state VARCHAR(3), cm_rating VARCHAR(2), cm_name VARCHAR(48), cm_address VARCHAR(48), cm_city VARCHAR(48)); ISQL> CREATE INDEX custnum ON custmast (cm_custnum); ISQL> COMMIT WORK; 2.2.3 Manage This step provides data management functionality for the application.
Quick Tour INSERT INTO custmast VALUES ('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange'); INSERT INTO custmast VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'); INSERT INTO custmast VALUES ('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta'); INSERT INTO custmast VALUES ('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia'); COMMIT WORK; The following SQL statement performs the query and displays a very small rep
ISQL and Tools 2.2.5 Complete Relational Model and Indexing Tutorial Source Code Complete source code for the relational model and indexing tutorial can be found in Appendix A "Tutorial Source Code".
Quick Tour 2.3 LOCKING TUTORIAL iSQL_Tutorial3.sql This tutorial will introduce the concept of locking. The functionality for this tutorial focuses on adding records, then updating a single record to the customer master table. From the iSQL utility the script will be parsed and passed to the server. The script completes without "committing" the update. This leaves the updated record locked.
ISQL and Tools 2.3.2 Define In this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement in which specific fields are defined. Upon successful creation of the table, the changes made to the database by this transaction are made permanent by executing the COMMIT WORK statement. The following SQL syntax provides the functionality for the define phase: • CREATE TABLE - Create a table. • COMMIT WORK - Make changes permanent.
Quick Tour The first process has the record associated with customer number 1003 locked. Meanwhile the second process has attempted to delete the contents of the customer master table and has been blocked when attempting to lock the table. At this point both processes are effectively blocked. The first process is holding the lock waiting on a keystroke and the second is waiting to grab the lock.
ISQL and Tools 2.3.5 Complete Locking Tutorial Source Code Complete source code for the locking tutorial can be found in Appendix A "Tutorial Source Code".
Quick Tour 2.4 TRANSACTION PROCESSING TUTORIAL iSQL_Tutorial4.sql This tutorial will introduce the concept of transaction processing, based on the relational model of the previous tutorial. Records will be added to tables orderlist and orderitems as a single transaction. Transaction processing in iSQL is handled by two statements: 1. COMMIT WORK that makes the changes done during the transaction permanent and starts a new transaction. 2.
ISQL and Tools Transaction These tables consist of a Customer Master table and an Item Master table that support primarily static information regarding a company's product line and customer demographics. The orderlist and orderitems tables consist of dynamic information pertinent to day to day sales. This dynamic data makes its way into the database as a transaction. If any part of the data is invalid then the transaction is rolled back and none of the data will enter the database.
Quick Tour 2.4.3 Manage This step provides data management functionality for the application. In this example we will add records to the itemmast and custmast tables, intended as static data. Then, sales orders will be processed as a transaction and "commited" or "rolled back" depending on the validity of the data. The final step will be to display the result of the transaction processing by dumping the contents of the orderlist and orderitems tables.
ISQL and Tools SELECT orderlist.ol_custnum, custmast.cm_custnum FROM orderlist, custmast WHERE orderlist.ol_custnum = custmast.cm_custnum; ROLLBACK WORK; 2.4.4 Done When a client application has completed operations with the server, it must release resources by disconnecting from the database. iSQL is an application that provides an interface for interactive SQL. It may not be explicit but a connection is made with the server when the isql tool is launched.
Chapter 3 ISQL Statements 3.1 OVERVIEW This chapter describes only those statements that are specific to ISQL. See the c-treeSQL Reference Guide for detailed reference information on standard SQL statements that can be issued in other environments. 3.2 STARTING INTERACTIVE SQL Start ISQL by issuing the isql command at the shell prompt.
ISQL and Tools The file name must be enclosed in doubles quotes, such as: isql -s "test script.sql" testdb -u user_name The user name c-treeSQL uses to connect to the database specified in the connect_string. ctreeSQL verifies the user name against a corresponding password before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name.
ISQL Statements Table 3-1: ISQL Statements for Statement History Support Statement Summary RUN [ stmt_num ] Displays and executes the current statement or specified statement in the history buffer. See Section 3.7.16 "RUN" on page 333 details. LIST [ stmt_num ] Displays the current statement or specified statement in the history buffer, and makes that statement the current statement by copying it to the end of the history list. See Section 3.7.14 "LIST" on page 3-32 for details.
ISQL and Tools ISQL includes several statements that provide simple formatting of SQL queries. The following table summarizes the ISQL query-formatting statements. Table 3-2: ISQL Statements for Query Formatting Statement Summary DISPLAY Displays text, variable values, and/or column values after the specified set of rows (called a break specification). See Section 3.7.7 "DISPLAY" on page 3-23 for details.
ISQL Statements All the examples use the same ISQL query. The query retrieves data on outstanding customer orders. The query joins two tables, customers and orders. The examples for the TABLE statement on Section 3.7.13 "HOST or SH or !" on page 3-31 show the columns and data types for these sample tables.
ISQL and Tools 3.4.1 Formatting Column Display with the COLUMN Statement You can specify the width of the display for character columns with the COLUMN statement's "An" format string. Specify the format string in the FORMAT clause of the COLUMN statement. You need to issue separate COLUMN statements for each column whose width you want to control in this manner.
ISQL Statements Example 3-3: Customizing Format of Numeric Column Displays ISQL> column order_value format "$99,999,999.99" ISQL> column; -- Show all the COLUMN statements now in effect: column CUSTOMER_NAME format "A19" heading "CUSTOMER_NAME" column CUSTOMER_CITY format "A19" heading "CUSTOMER_CITY" column ORDER_VALUE format "$99,999,999.99" heading "ORDER_VALUE" ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value from customers c, orders o where o.customer_id = c.
ISQL and Tools whenever the value in the customer_name column changes. In other words, we need to specify a column break on the customer_name column. Approach this task in two steps. First, devise a DISPLAY statement to display the customer name and confirm that it is displaying correctly. Then, issue COMPUTE statements to calculate the statistics for each customer (namely, the count and sum of orders), and add DISPLAY statement to include those statistics.
ISQL Statements The following example also issues two more DISPLAY statements to display the variable values. As before, the DISPLAY statements must specify the customer_name break. They also indent their display farther to indicate the relationship with the previously issued DISPLAY. As before, this example uses the COLUMN and DISPLAY statements from previous examples. ISQL processes DISPLAY statements in the order they were issued.
ISQL and Tools The TITLE statement lets you specify text that ISQL displays before (TITLE TOP) or after (TITLE BOTTOM) the query results. The title can also be horizontally positioned by specifying the keywords LEFT, CENTER, or RIGHT; or by specifying the actual column number corresponding to the required positioning of the title. Use the SKIP clause to skip lines after a top title or before a bottom title. The following example uses two TITLE statements to display a query header and footer.
ISQL Statements End of Orders Summary Report 23 records selected ISQL> 3.5 THE HELP AND TABLE STATEMENTS ISQL supports an on-line help facility that can be invoked by using the HELP statement. Typing HELP at the ISQL prompt will display a help file which will list the options accepted by the HELP statement. The various forms of the HELP statement are listed below: • HELP - Displays the options that can be specified for HELP. • HELP COMMANDS - Displays all the statements that ISQL accepts.
ISQL and Tools ROLLBACK WORK ; An SQL statement starting immediately after a COMMIT WORK or ROLLBACK WORK statement starts a new transaction. 3.7 ISQL REFERENCE This section provides reference material for statements specific to ISQL. This section does not include descriptions of standard SQL statements or statements specific to embedded SQL. For details on the syntax and semantics of those other SQL statements, see the c-treeSQL Reference Manual. 3.7.
ISQL Statements insert into stores values (1001,chassis); insert into stores values (1002,chips); select * from stores where item_no > 1001; set echo off ; To execute the above statements stored in a file named cmdfile, enter: ISQL> @cmdfile 3.7.2 BREAK Syntax BREAK [ ON break_spec [ SKIP n ] ] ; break_spec:: { column_name [ , … ] | ROW | PAGE | REPORT } Description The BREAK statement specifies at what point ISQL processes associated DISPLAY and COMPUTE statements.
ISQL and Tools PAGE Causes a break at the end of each page. The end of a page is specified in the SET PAGESIZE statement. See Section 3.7.18 "SET" on page 334 for details on the SET statement. REPORT Causes a break at the end of a report or query. SKIP n The optional SKIP clause can be used to skip the specified number of lines when the specified break occurs and before processing of any associated DISPLAY statements.
ISQL Statements ISQL> select customer_name from CUSTOMER_NAME ------------Sports Cars Inc. Break on every row! Mighty Bulldozer Inc. Break on every row! Ship Shapers Inc. Break on every row! . . . ISQL> break on page ISQL> select customer_name from CUSTOMER_NAME ------------Break on page (page size set to CUSTOMER_NAME ------------Sports Cars Inc. Break on page (page size set to CUSTOMER_NAME ------------Mighty Bulldozer Inc. Break on page (page size set to . . .
ISQL and Tools | | | | | BREAK COLUMN COMPUTE DISPLAY TITLE Description The CLEAR statement removes settings made by the ISQL statement corresponding to option. Argument option Which ISQL statement's settings to clear: • CLEAR HISTORY - Clears the ISQL statement history buffer. • CLEAR BREAK - Clears the break set by the BREAK statement. • CLEAR COLUMN - Clears formatting options set by any COLUMN statements in effect. • CLEAR COMPUTE - Clears all the options set by the COMPUTE statement.
ISQL Statements Description The COLUMN statement controls how ISQL displays a column's values (the FORMAT clause) and specifies alternative column-heading text (the HEADING clause). The COLUMN statement without any arguments displays the current column specifications. Arguments column_name The name of the column affected by the COLUMN statement. If the COLUMN statement includes column_name but omits both the FORMAT and HEADING clauses, ISQL clears any formatting and headings in effect for that column.
ISQL and Tools (a) Format String Details Table 3-3: Numeric Format Strings for the COLUMN Statement Character Example Description 9 99999 Number of 9's specifies width. If the column value is too large to display in the specified format, ISQL displays # characters in place of the value. 0 09999 Display leading zeroes. $ $9999 Prefix the display with '$'. B B9999 Display blanks if the value is zero. , 99,999 Display a comma at position specified by the comma. . 99,999.
ISQL Statements Table 3-4: Date-Time Format Strings for the COLUMN Statement Character Description W The week of month as a 1-digit number (in the range 1-5). DDD The day of year as a 3-digit number (in the range 001-365). DD The day of month as a 2-digit number (in the range 01-31). D The day of week as a 1-digit number (in the range 1-7, 1 for Sunday and 7 for Saturday). DAY The day of week as a 9 character string (in the range 'SUNDAY' to 'SATURDAY '.
ISQL and Tools order_info order_weight order_value order_state CHAR INT INT CHAR 200 4 4 20 ISQL displays the order_info column, at 200 characters, with lots of blank space preceding the values: ISQL> select order_info from orders where order_value < 1000000 ORDER_INFO ---------- Solid Rods 5 in.
ISQL Statements Thursday 1 record selected ISQL> column sysdate ISQL> select sysdate SYSDATE ------May 1 record selected ISQL> column sysdate ISQL> select sysdate SYSDATE ------7th 1 record selected format "Month" from syscalctable format "DDth" from syscalctable Note: If the select-list of a query includes column titles, they override formatting specified in COLUMN statements for those columns. The following example illustrates this behavior.
ISQL and Tools COMPUTE statements have no effect until you issue a BREAK statement with the same break_spec. Issuing the COMPUTE statement without any arguments displays the currently-set COMPUTE specifications, if any. Arguments AVG | MAX | MIN | SUM | COUNT The function to apply to values of column_name. The functions AVG, MAX, MIN, and SUM can be used only when the column is numeric. The function COUNT can be used for any column type. column_name The column whose value is to be computed.
ISQL Statements 3.7.6 DEFINE Syntax DEFINE [ variable_name = value ] ; Description The DEFINE statement defines a variable and assigns an ASCII string value to it. When you refer to the defined variable in DISPLAY statements, ISQL prints the value. The DEFINE statement is useful if you have scripts with many DISPLAY statements. You can change a single DEFINE statement to change the value in all of the DISPLAY statements that refer to the variable.
ISQL and Tools Issuing the DISPLAY statement without any arguments displays the currently-set DISPLAY specifications, if any. Arguments col_position An optional argument that specifies the horizontal positioning of the associated display value. There are two forms for the argument: COL column_number Directly specifies the column position of the display value as an integer(1 specifies column 1, 2 specifies column 2, and so on.). @column_name Names a column in the select list of the SQL query.
ISQL Statements ISQL> display col 5 "Number of orders placed by", customer_name, "=", n_ord on customer_name ISQL> compute count of order_id in n_ord on customer_name; ISQL> select c.customer_name, o.order_id from customers c, orders o where o.customer_id = c.customer_id; CUSTOMER_NAME ------------Sports Cars Inc. Sports Cars Inc. Number of orders placed = 2 Mighty Bulldozer Inc. Mighty Bulldozer Inc. Number of orders placed = 2 Ship Shapers Inc. Ship Shapers Inc. Ship Shapers Inc.
ISQL and Tools edited. When you exit the editor, ISQL writes the buffer contents as the last statement in the history buffer. By default, ISQL invokes the vi editor on UNIX and the MS-DOS editor on NT. You can change the default by setting the EDITOR environment variable: • On UNIX, set the environment variable at the operating system command level: setenv EDITOR /usr/local/bin/gmacs • On NT, set the environment variable in the initialization file DHSQL.
ISQL Statements 3.7.10 GET Syntax G[ET] filename; Description The GET statement reads the first SQL statement stored in the specified script file. Arguments filename The name of the script file. ISQL reads the file until it encounters a semicolon ( ; ) statement terminator. It appends the statement to the history buffer as the most-recent statement. Notes • Execute the statement read by GET using the RUN statement.
ISQL and Tools ( orders.order_id = lots.order_id ) and ( ( customers.customer_name = 'Ship Shapers Inc.' ) AND ( lot_staging.start_date is not NULL ) AND ( lot_staging.end_date is NULL ) ) ISQL> RUN SELECT customers.customer_name, orders.order_info, orders.order_state, lot_staging.lot_location, lot_staging.start_date FROM customers, orders, lots, lot_staging WHERE ( customers.customer_id = orders.customer_id ) and ( lots.lot_id = lot_staging.lot_id ) and ( orders.order_id = lots.
ISQL Statements CUSTOMER_NAME ORDER_INFO ------------- ---------- ORDER_STATE LOT_LOCATION START_DATE ----------- ------------ ---------- Ship Shapers Inc. Processing I Beams Size 10 Hot Rolling 12/26/1994 1 record selected 3.7.11 HELP Syntax HE[LP] {COMMANDS|CLAUSES}; HE[LP] ; Description The HELP statement displays the help information for the specified statement or clause. Notes • HELP COMMANDS displays a list of statements for which help text is available.
ISQL and Tools Description The HISTORY statement lists the statements in the statement history buffer, along with an identifying number. Notes • ISQL maintains a list of statements typed by the user in the statement history buffer. The SET HISTORY statement sets the size of the history buffer. • The statements LIST, EDIT, HISTORY, and RUN are not added to the history buffer. • Use HISTORY to obtain the statement number for a particular statement in the history buffer that you want to execute.
ISQL Statements 3.7.13 HOST or SH or ! Syntax { HOST | SH | ! } [host_command]; Description The HOST statement executes a host operating system command without terminating the current ISQL session. Arguments HOST | SH | ! Synonyms for directing ISQL to execute an operating system command. host_command The operating system command to execute. If host_command is not specified, ISQL spawns a subshell from which you can issue multiple operating system commands.
ISQL and Tools 3.7.14 LIST Syntax L[IST] [ stmt_num ]; Description The LIST statement displays the statement with the specified statement number from the statement history buffer and makes it the current statement by adding it to the end of the history list. If LIST omits stmt_num, it displays the last statement in the history buffer.
ISQL Statements 3.7.15 QUIT or EXIT Syntax Q[UIT] Description The QUIT statement terminates the current ISQL session. Related Statements QUIT and EXIT are synonymous. There is no difference in their effect. 3.7.16 RUN Syntax R[UN] [stmt_num]; Description The RUN statement executes the statement with the specified statement number from the statement history buffer and makes it the current statement by adding it to the end of the history list. If RUN omits stmt_num, it runs the current statement.
ISQL and Tools Office Furniture Inc. 10 records selected ISQL> 3.7.17 SAVE Syntax S[AVE] filename; Description The SAVE statement saves the last statement in the history buffer in filename. The GET and START statements can then be used to read and execute the statement from a file. If filename does not exist, ISQL creates it. If filename does exist, ISQL overwrites it with the contents of the last statement in the history buffer. Example ISQL> ! more test.SQL test.
ISQL Statements | | | | | | | | | COMMAND LINES number_lines REPORT { ON | OFF } ECHO { ON | OFF } PAUSE { ON | OFF } TIME { ON | OFF } DISPLAY COST { ON | OFF } AUTOCOMMIT { ON | OFF } TRANSACTION ISOLATION LEVEL isolation_level CONNECTION { database_name | DEFAULT } Description The SET statement changes various characteristics of an interactive SQL session. Arguments HISTORY Sets the number of statements that ISQL will store in the history buffer.
ISQL and Tools DISPLAY COST ON | OFF SET DISPLAY COST ON displays the values the c-treeSQL optimizer uses to calculate the least-costly query strategy for a particular SQL statement. The UPDATE STATISTICS statement updates the values displayed by SET DISPLAY COST ON. SET DISPLAY COST OFF suppresses the display and is the default. AUTOCOMMIT ON | OFF SET AUTOCOMMIT ON commits changes and starts a new transaction immediately after each SQL statement is executed. SET AUTOCOMMIT OFF is the default.
ISQL Statements sys_chkcol_usage sys_keycol_usage Here's a page break! . . . ISQL> SET DISPLAY COST ON ISQL> -- Select from the one-record SYSCALCTABLE table: ISQL> SELECT * FROM SYSCALCTABLE; Estimated Cost Values : ----------------------COST : 8080 CARDINALITY : 200 TREE SIZE : 3072 FLD --100 3.7.
ISQL and Tools ____________________ EDITOR ..................... : vi HISTORY buffer size ........ : 50 PAUSE ..................... : ON COMMAND LINES .............. : 10 TIMEing command execution.. : OFF SPOOLing ................... : ON LINESIZE .................. : 78 REPORTing Facility ......... : ON PAGESIZE .................. : 72 Spool File ................. : spool_file AUTOCOMMIT ................. : OFF ECHO commands ............. : ON TRANSACTION ISOLATION LEVEL.
ISQL Statements Example To record the displayed output into the file called STK, enter: ISQL> SPOOL STK ON ; ISQL> SELECT * FROM customer ; ISQL> SPOOL OFF ; 3.7.21 START Syntax ST[ART] filename [ argument ] [ ... ] ; Description The START statement executes the first SQL statement stored in the specified script file. Arguments filename The name of the script file. ISQL reads the file until it encounters a semicolon ( ; ) statement terminator.
ISQL and Tools ISQL> -- Display a script file with the ! shell statement. The script's SQL ISQL> -- statement uses the LIKE predicate to retrieve customer names ISQL> -- beginning with the string passed as an argument in a START statement: ISQL> ! more start_ex.sql SELECT customer_name FROM customers WHERE customer_name LIKE '&1%'; ISQL> -- Use the START statement to execute the SQL statement in the script ISQL> -- start_ex.sql. Supply the value 'Ship' as a substitution argument: ISQL> START start_ex.
ISQL Statements has_ccnstrs has_ucnstrs tbl_status rssid NOT NOT NOT NOT NULL NULL NULL NULL VARCHAR VARCHAR VARCHAR INT 1 1 1 4 The following example uses the table command to detail the structure of the tables used in examples throughout this chapter.
ISQL and Tools lot_id purity p_deviation strength s_deviation comments ISQL> table samples COLNAME ------lot_id samples comments ISQL> 3.7.23 NOT NULL INT DOUBLE DOUBLE DOUBLE DOUBLE CHAR NULL ? ------ TYPE ---INT INT CHAR 4 8 8 8 8 200 LENGTH -----4 4 200 TITLE Syntax TITLE [ [ [ [ ] TOP | BOTTOM ] [ LEFT | CENTER | RIGHT | COL n ] " text " ] [ … ] SKIP n ] ; Description The TITLE statement specifies text that ISQL displays either before or after it processes a query.
ISQL Statements Examples The following example shows the effect of specifying a top title without a bottom title, then both a top and bottom title.
ISQL and Tools 3-44 FairCom Corporation
Chapter 4 Data Load Utility: dbload 4.1 INTRODUCTION This chapter describes the c-treeSQL database load utility, dbload. This utility loads records from an input data file into tables of a database. The format of the data file is specified by a record description given in an input commands file to dbload. Both dbload and dbdump commands files use DEFINE RECORD statements with similar syntax to specify the format of loaded or exported data records.
ISQL and Tools Figure 4-1: dbload Execution Process Commands file dbload Data files Log file Bad file c-treeSQL Engine c-tree Plus Database 4.2 PREREQUISITES FOR DBLOAD Before running dbload, you need: 4.3 • A valid, readable commands file • INSERT privileges on the tables named in the commands file DBLOAD COMMAND LINE SYNTAX The dbload command does not directly specify an input file, but instead names a commands file that in turn specifies data input files.
Data Load Utility: dbload -l logfile Specifies the file into which the error logging is done. stderr is the default. dbload also writes statistics to the file: • Number of records read • Number of records skipped • Number of records loaded • Number of records rejected -b badfile The file into which the bad rows that were not loaded, are written. By default badfile is put in the current directory. -c commit_frequency Store the specified number of records before committing the transaction.
ISQL and Tools 4.4.1 Variable Length Records For variable length records, the fields in the data file can be of varying length. Unless the keyword FIXED is used in the commands file, it is assumed that the dbload record processing will be for variable length records. 4.4.2 Fixed Length Records For fixed length records, the fields in the data file must be of fixed length. The length of the record must be the same for all records and is specified in the commands file.
Data Load Utility: dbload 4.5.1 The DEFINE RECORD Statement The DEFINE RECORD statement is used to define the record that is to be loaded into the database. It describes the data found in the data file.
ISQL and Tools • start_position is the position where the field starts. It must be an unsigned integer. • end_position is the position where the field ends. It must be an unsigned integer. The first position of each record is 1 and not 0. If date and time types are to be inserted they can be specified as characters in the data file. If it is a fixed length record then the type specification can be CHAR.
Data Load Utility: dbload statement. The following example shows the list interchanged with respect to the list in the DEFINE RECORD statement. DEFINE RECORD dept_rec AS ( dept_no, dept_name, location ) FIELD DELIMITER ' ' ; FOR EACH RECORD dept_rec FROM dept_in INSERT INTO ADMIN.department (loc, no, name) VALUES (location, dept_no, dept_name) ; NEXT RECORD Here the items no, name, and loc are interchanged in both the table list and the values list when compared with the DEFINE RECORD list.
ISQL and Tools The following is the commands file to load records into the orders table. The input data file is orders_in which is a binary file in the fixed length record format. DEFINE RECORD orders_rec OF FIXED LENGTH 30 AS ( order_no POSITION (1:4) LONG, order_date POSITION (6:16) CHAR, product POSITION (18:25) CHAR, qty POSITION (27:30) LONG ) ; FOR EACH RECORD orders_rec FROM orders_in INSERT INTO ADMIN.orders (no, date, prod, units) VALUES (order_no, order_date, product, qty) ; NEXT RECORD 4.
Data Load Utility: dbload Position for SHORT not specified correctly. The size of the field (start position to end position) must be equal to the size of SHORT. Position for LONG not specified correctly. The size of the field (start position to end position) must be equal to the size of LONG. Position for FLOAT not specified correctly. The size of the field (start position to end position) must be equal to the size of FLOAT. Position for DOUBLE not specified correctly.
ISQL and Tools 4-10 FairCom Corporation
Chapter 5 Data Unload Utility: dbdump 5.1 INTRODUCTION This chapter describes the c-treeSQL database dump utility, dbdump. dbdump writes the data in a database to a file. The format of the exported data is specified by the record description given in an input command file to dbdump. Both dbload and dbdump commands files use DEFINE RECORD statements with similar syntax to specify the format of loaded or exported data records.
ISQL and Tools • 5.3 SELECT privileges on the tables named in the commands file DBDUMP COMMAND LINE SYNTAX The dbdump command accepts the commands file name, the database name and a command option. Syntax dbdump -f commands_file [-n] database_name Options -f commands_file Specifies the file containing dbdump commands. -n Parse the commands file and display errors, if any, without exporting data. If the parsing is successful a message, No errors in the commands file. displays on stdout.
Data Unload Utility: dbdump The syntax definition for the commands file is as shown: dbdump_commands: define_record_statement for_record_statement The following is sample commands file showing dump instructions. DEFINE RECORD ord_rec AS ( ord_no, item_name, date, item_qty ) FIELD DELIMITER ' ' ; FOR RECORD ord_rec dump into ord_dat USING SELECT order_no, product, order_date, qty FROM items; 5.5.1 The DEFINE RECORD Statement The DEFINE RECORD statement is used to define the record of the output file.
ISQL and Tools • record_length is the length of the fixed length record. This length should include the length of field or record delimiters, if any. • field_name is the name used to refer to a field in the data file. • delimiter_char is the field delimiter and is a single character. delimiter_char must be specified as a literal. • delimiter_string is the record delimiter and can be a single character or a string. It must be specified as a literal.
Data Unload Utility: dbdump ( no, name, loc ) FIELD DELIMITER ' ' ; FOR RECORD dept_rec DUMP INTO deptrecs_out USING SELECT dept_no , dept_name , location FROM ADMIN.dept ; The following is the commands file to write records from the customer table. The output data file is cust_out which is a binary file in the fixed length record format.
ISQL and Tools 5-6 FairCom Corporation
Chapter 6 Schema Export Utility: dbschema 6.1 INTRODUCTION This chapter describes the c-treeSQL utility, dbschema. This utility recreates specified database elements and data. Syntax dbschema [ [ [ [ [ -h ] [ -d ] [-u user_name ] [-a password ] [ -o outfile ] -p [ user_name.]procedure_name [ , ... ] ] -t [ user_name.]table_name [ , ... ] ] -T [ user_name.]trigger_name [ , ... ] ] database_name ] Description Generates SQL statements to recreate the specified database elements and data.
ISQL and Tools against a corresponding user name before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_PASSWD environment variable specifies the default password.) -o outfile Redirects the output to the specified file. The default is standard output. -t [ user_name.]table_name [ , ... ] A comma-separated list of tables and views for which definitions should be generated.
Schema Export Utility: dbschema ADMIN@isis% dbschema -t dbp1,test_view rdsdb DBSCHEMA create table ADMIN.dbp1 ( c1 integer ) pctfree 20; create view ADMIN.test_view ( fld ) as select * from test_revoke1 ; The following example uses the -p option with the % wildcard character to generate definitions for all stored procedures whose names begin with the characters foo: ADMIN@isis% dbschema -p foo% rdsdb DBSCHEMA CREATE PROCEDURE ADMIN.
ISQL and Tools 6-4 FairCom Corporation
Appendix A Tutorial Source Code A.
ISQL and Tools oi_itemnum VARCHAR(6) ); CREATE INDEX orderitem ON orderitems (oi_ordernum, oi_seqnumber); CREATE TABLE itemmast ( im_weight INTEGER, im_price MONEY, im_itemnum VARCHAR(6), im_desc VARCHAR(48) ); CREATE INDEX itemnum ON itemmast (im_itemnum); CREATE TABLE custmast ( cm_custnum VARCHAR(5), cm_zip VARCHAR(10), cm_state VARCHAR(3), cm_rating VARCHAR(2), cm_name VARCHAR(48), cm_address VARCHAR(48), cm_city VARCHAR(48)); CREATE INDEX custnum ON custmast (cm_custnum); COMMIT WORK; DELETE DELETE D
Tutorial Source Code COLUMN oi_quantity FORMAT "A10" heading "QTY" COLUMN im_price FORMAT "$99.99" heading "PRICE" SELECT custmast.cm_name, orderitems.oi_quantity, itemmast.im_price FROM custmast, orderitems, itemmast, orderlist WHERE orderlist.ol_custnum = custmast.cm_custnum AND orderlist.ol_ordernum = orderitems.oi_ordernum AND orderitems.oi_itemnum = itemmast.im_itemnum ORDER BY orderlist.ol_custnum; A.
ISQL and Tools im_desc VARCHAR(48)); CREATE TABLE custmast ( cm_custnum VARCHAR(5), cm_zip VARCHAR(10), cm_state VARCHAR(3), cm_rating VARCHAR(2), cm_name VARCHAR(48), cm_address VARCHAR(48), cm_city VARCHAR(48)); COMMIT WORK; DELETE DELETE DELETE DELETE COMMIT FROM orderlist; FROM orderitems; FROM itemmast; FROM custmast; WORK; INSERT INSERT INSERT INSERT INTO INTO INTO INTO itemmast itemmast itemmast itemmast VALUES VALUES VALUES VALUES (10, 19.95, '1', 'Hammer'); (3, 9.99, '2', 'Wrench'); (4, 16.
Tutorial Source Code ROLLBACK WORK; INSERT INTO orderitems VALUES ('3', 1, 2, '3'); INSERT INTO orderitems VALUES ('3', 2, 3, '99'); INSERT INTO orderlist VALUES ('9/22/2002', '9/26/2002', '3', '1002'); SELECT orderitems.oi_itemnum, itemmast.im_itemnum FROM orderitems, itemmast WHERE orderitems.oi_itemnum = itemmast.im_itemnum; SELECT orderlist.ol_custnum, custmast.cm_custnum FROM orderlist, custmast WHERE orderlist.ol_custnum = custmast.
ISQL and Tools A-6 FairCom Corporation
Index Symbols @Execute syntax . . . . . . . . . . . . . . . . . . . . . 3-12 A Adding titles . . . . . . . . . . . . . . . . . . . . . . . . . . 3-9 B Beginning titles . . . . . . . . . . . . . . . . . . . . . . . 3-9 BREAK statement . . . . . . . . . . . . . . . . . . . . . 3-4 BREAK statement syntax . . . . . . . . . . . . . . 3-13 C CLEAR statement . . . . . . . . . . . . . . . . . . . . . 3-4 CLEAR statement syntax . . . . . . . . . . . . . . . 3-15 Column display formatting . . . . . . . . . . . . .
ISQL and Tools Formatting ISQL output . . . . . . . . . . . . . . . . .3-3 sample program source code . . . . . . . . . A-1 G Q GET statement . . . . . . . . . . . . . . . . . . . . . . . .3-3 GET statement syntax . . . . . . . . . . . . . . . . .3-27 Queries, unformatted . . . . . . . . . . . . . . . . . . . 3-5 QUIT statement syntax . . . . . . . . . . . . . . . . 3-26 H R HELP statement syntax . . . . . . . . . . . . . . . .3-29 HISTORY statement . . . . . . . . . . . . . . . . . . .
Index COMPUTE . . . . . . . . . . . . . . 3-4, 3-7, 3-21 COMPUTE syntax . . . . . . . . . . . . . . . . 3-21 DEFINE . . . . . . . . . . . . . . . . . . . . 3-4, 3-23 DEFINE RECORD . . . . . . . . . . . . . 4-5, 5-3 DEFINE syntax . . . . . . . . . . . . . . . . . . . 3-23 DISPLAY . . . . . . . . . . . . . . . 3-4, 3-7, 3-23 DISPLAY syntax . . . . . . . . . . . . . . . . . 3-23 EDIT . . . . . . . . . . . . . . . . . . . . . . . 3-3, 3-25 EDIT syntax . . . . . . . . . . . . . . . . . . . . . 3-25 EXIT . . . .
