HP (Hewlett-Packard) c-tree-SQL ISQL and Tool reference Guide manual

ISQL and T ools Reference Guide For use with c-treeSQL Server This manual provides refe rence material for the IS QL interactive SQL utility and other administrative tool s provided in th e c-treeSQL enviro nment. It also includes a tutorial describi ng how to use the ISQL utility .[...]

Copyright © 1992-2004 FairCo m Corporation All rights reserved. Portions © 1987-2004 Dharma Systems, Inc. All rights reserved. Eleventh Edition, First printing: September 2003 Information in th is document is subject to change without notice. No part of this publication may be stor ed in a retrieval system, or transmitted in any form or by any me[...]

Fa ir Co m Co rp or at io n i T able of Content s Documentation Overvi ew Purpose of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [...]

ii FairCom Corporation 3.5 The HELP and TABLE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.6 Transaction Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 3.7 ISQL Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . [...]

Fa i r Co m C o rp o ra t i on ii i 5.4 Data File Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5- 2 5.5 The Commands File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 5.5.1 The DEFINE RECORD Statement . . . . . .[...]

iv FairCom Corporation[...]

FairCom Corporation v Document ation Overview PURPOSE OF THIS MANUAL This manual provides reference material for th e ISQL interactive SQL utility as well as the dbload , dbdump , and dbschema administrative tools provided in the c-treeSQ L environment. It also includes a tutorial describing how to us e the ISQL utility . AUDIENCE The reader of thi[...]

ISQL and Tools vi FairCom Corporation RELATED DOCUMENT ATION Refer to the following docum ents fo r more information: lowercase Lowercase type deno tes either user-suppl ied elements or names of othe r syntax diagrams. User-supplied elements include names of tables, host- language variables, e xpressions, and literals. Syntax diagrams can refer to [...]

FairCom Corporation 1-1 Chapter 1 Introduction 1.1 OVERVIEW Interactive SQL (often referred to throughou t this manual as ISQL) is a utility supp lied wi th c- treeSQL that lets you issue SQL statements directly from a term inal and see results displ ayed at the terminal. Y ou can use interactive SQL to: • Learn how SQL statements work • T est [...]

ISQL and Tools 1-2 FairCom Corpora tion[...]

FairCom Corporation 2-1 Chapter 2 Quick T our 2.1 INTRODUCTORY T UTORIAL iSQL_Tutorial1.sql This introductory tutorial will rapidly tak e you throug h the basic use of the powerful interactive SQL (iSQL) database interface. iSQL is a full featured command lin e client side query tool usef ul for submitting ad hoc SQL statemen ts to a Server . Likew[...]

ISQL and Tools 2-2 FairCom Corpora tion 2.1.2 Define In this case define consists of the CREA TE T ABLE statement. This is done in a single iSQL st atement in which specific fields are defined. Upon successfu l cr eation of the table, the changes made to the database by this tr ansaction are made permanent by executing the COMMIT WORK stat ement. T[...]

Quick Tour FairCom Corporation 2-3 VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'); ISQL> INSERT INTO CUSTMAST VALUES ('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'At[...]

ISQL and Tools 2-4 FairCom Corpora tion 2.2 RELATIONAL MODE L AND INDEXING TU T O R I A L iSQL_Tutorial2.sql This intermediate tutorial will ad vance the concepts introduced in the first tutorial by expanding the nu mber of tables and building a relational model. This tutorial wi ll wal k you th rough defining an index for each table, demonstrating[...]

Quick Tour FairCom Corporation 2-5 OrderList - A table of records consisting of a list of orders. OrderItem - A table of records consisting of sp ecific items associated with an order . ItemMaster - A table of records consisting of information about item s. CustomerMaster - A table of records consisting of sp ecific info related to each customer . [...]

ISQL and Tools 2-6 FairCom Corpora tion 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> COM[...]

Quick Tour FairCom Corporation 2-7 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&apos[...]

ISQL and Tools 2-8 FairCom Corpora tion 2.2.5 Complete Relational Model and Indexing T utorial Source Code Complete source code for the relatio nal mo del and indexing tuto- rial can be found in Appendi x A " T utorial Source Code " .[...]

Quick Tour FairCom Corporation 2-9 2.3 LOCKING T UTORIAL iSQL_Tutorial3.sql This tutorial will introduce the co ncept of locking. The fu nction- ality for this tutorial focuses on adding records, then updati ng a single record to th e customer master table. From the iSQL utili ty the script will be parsed and passe d to the server . The script c om[...]

ISQL and Tools 2-10 FairCom Corporation 2.3.2 Define In this case define consists of the CREA TE T ABLE statement. This is done in a single iSQL st atement in which specific fields are defined. Upon successfu l cr eation of the table, the changes made to the database by this tr ansaction are made permanent by executing the COMMIT WORK stat ement. T[...]

Quick Tour FairCom Corporation 2-11 The first process has the record associated with customer number 1003 locked. Meanwhile the second process has attempted to delete the conten ts of the customer master table and has been blocked when attempting to lock the table. At th is point both processes are effectively blocked. The first process is holding [...]

ISQL and Tools 2-12 FairCom Corporation 2.3.5 Complete Locking T utorial Source Code Complete source code for the lock ing tutorial can be found in Appendix A " Tutorial Source Code " .[...]

Quick Tour FairCom Corporation 2-13 2.4 T RANSACTION PROCESSING T U TORIAL iSQL_Tutorial4.sql This tutorial will introduce the concept of trans action processing , based on the relational model of the previous tu torial. Records will be added to tables orderlist and orderitems as a single transac- tion. T ransaction processing in iSQ L is hand led [...]

ISQL and Tools 2-14 FairCom Corporation T ransaction These tables consist of a Customer Master tabl e and an Item Master table that su pport prima- rily static info rmation regarding a com pany's product lin e and cust om er demographics. The orderlist and orderitems tables co nsist of dynam ic information pertinent to day to day sales. This d[...]

Quick Tour FairCom Corporation 2-15 2.4.3 Manage This step provides data managem ent functionality for th e applica- tion. In this exampl e we will ad d records to the itemmast and cust- mast tables, intended as static data. Then, sales ord e rs will be processed as a transaction and "commited" or "rolled back" depending on the [...]

ISQL and Tools 2-16 FairCom Corporation 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 mu st release resources by disconnect ing from the data- base. iSQL is an application th[...]

FairCom Corporation 3-1 Chapter 3 ISQL S t atement s 3.1 OVERVIEW This chapter describes only those statem ents that are specific to ISQL. See the c-tr eeSQL Ref- er ence Guide for detailed reference information on stand a rd SQL statements that can be issued in other environments. 3.2 S T ARTING INTERACTIVE SQL Start ISQL by issuing the isql comma[...]

ISQL and Tools 3-2 FairCom Corpora tion 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 conn ect to the database specified in the connect_string . c- treeSQL verifies the user name against a co rrespo ndi ng password befo re it connects to the databas[...]

ISQL Statements FairCom Corporation 3-3 3.4 FORMATTING OUTPUT OF ISQL QUERIES Formatting of d atabase query results m akes the outp ut of a databa se qu ery m ore presentable and understandable. The formatted output of an ISQL database query can be either displayed on the screen, written to a file, or spooled to a printer to produce a hardcopy of t[...]

ISQL and Tools 3-4 FairCom Corpora tion ISQL includes several statements that provid e simple formatting of SQL queries. The follow- ing table summarizes the ISQL query-fo rmatti ng statements. The rest of this section provides an extended ex ample that illustrates how to use the statements together to impro ve formatting. T able 3-2: ISQL S tateme[...]

ISQL Statements FairCom Corporation 3-5 All the examples use the same ISQL query . The query retrieves data on outstanding customer orders. The query joins two tables, customers and or ders . The example s for the T ABLE state- ment on Section 3.7.13 " H OST or SH or ! " on page 3-31 show the columns and data typ es for these sample tab l[...]

ISQL and Tools 3-6 FairCom Corpora tion 3.4.1 Formatting Column Displ ay with the COLUMN St atement Y ou can specify the width of the display for ch aracter columns with th e COLUMN statement's "An" format string. Specify the form at str ing in the FORMA T clause of the COLUMN state- ment. Y ou need to issue separate COLUMN stat emen[...]

ISQL Statements FairCom Corporation 3-7 Example 3-3: Customizing Format of Nu meric Co lumn 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"[...]

ISQL and Tools 3-8 FairCom Corpora tion whenever the value in the customer_name column changes. In other words, we need to specify a column break on the customer_nam e column. Approach this task in two steps. First, devi se a DISPLA Y statement to display the customer name and confirm that it is displayi ng correct ly . Then, issue COMPUTE statemen[...]

ISQL Statements FairCom Corporation 3-9 The following example also issues two more DI SPLA Y statements to display the variable val- ues. As before, the DISPLA Y statements must specify the customer_name break. They also indent their display farther to indicate the relationship with the previo usly issued DISPLA Y . As before, this example uses the[...]

ISQL and Tools 3-10 FairCom Corporation The TITLE statement lets you sp ecify text that ISQL displays before (TITLE TOP) or after (TITLE BOTT OM) the query results. The title can also be horizontally positioned by specifyin g the keyw ords LEFT , CENTER, or RIGHT ; or by specifying the actual column number corresp onding to the required positioning[...]

ISQL Statements FairCom Corporation 3-11 End of Orders Summary Report 23 records selected ISQL> 3.5 THE HELP AND T A BLE S T ATEMENTS ISQL supports an on-line help facility that can be invoked by usi ng the HELP statem ent . T yp - ing HELP at the ISQL prompt will display a help file which will list the options accepted by the HELP statement. Th[...]

ISQL and Tools 3-12 FairCom Corporation <SQL statement> ROLLBACK WORK ; An SQL statement starting immediately af ter a COMMIT WORK or ROLLBACK WORK statement starts a new transaction. 3.7 ISQL REFERENCE This section provides reference materi al for statements specific to ISQL. This section does not include description s of stan dard SQL state[...]

ISQL Statements FairCom Corporation 3-13 insert into stores values (1001,chassis); insert into stores values (1002,chips); select * from stores where item_no > 1001; set echo off ; T o execute the above statements stored in a file named cmdfile , enter: ISQL> @cmdfile 3.7.2 BREAK Synt ax BREAK [ ON break_spec [ SKIP n ] ] ; break_spec:: { col[...]

ISQL and Tools 3-14 FairCom Corporation SKIP n The optional SKIP clause can be used to skip th e specified number of li nes when the specified break occurs and before processing o f any associated DISPLA Y statements. Examples The following examples illustrate how various break set tin gs and correspond ing DISPLA Y statements affect the disp l a y[...]

ISQL Statements FairCom Corporation 3-15 ISQL> select customer_name from customers; 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 customers; CUSTOMER_NAME ------------- Break on page ([...]

ISQL and Tools 3-16 FairCom Corporation | BREAK | COLUMN | COMPUTE | DISPLAY | TITLE Description The CLEAR statement removes settings made b y the ISQL statement corresp onding to option. Argument option Which ISQL statement' s settings to clear: • CLEAR HISTOR Y - Clears the IS Q L statement history buffer . • CLEAR BREAK - Clears the bre[...]

ISQL Statements FairCom Corporation 3-17 Description The COLUMN statement controls how ISQL displays a column's values (the FORMA T clause) and specifies alternative column-h eading text (the HEADING clause). The COLUMN statement without any arguments displays the current column specifications. Argument s column_nam e The name of the column af[...]

ISQL and Tools 3-18 FairCom Corporation (a) Format S tring Details T able 3-3: Numeric Format Strings for the COLUMN St atement Character Example Description 9 99999 Number of 9's specifies width. If the column val ue is too large to display in the specified format, ISQL displays # characters in place of the value. 0 09999 Display leading zero[...]

ISQL Statements FairCom Corporation 3-19 Examples The following examples are based on a table, or ders , wit h columns defined as follows: ISQL> table orders COLNAME NULL ? TYPE LENGTH ------- ------ ---- ------ order_id NOT NULL INT 4 customer_id INT 4 steel_type CHAR 20 W The week of month as a 1-digit number (in the range 1-5). DDD The day of[...]

ISQL and Tools 3-20 FairCom Corporation order_info CHAR 200 order_weight INT 4 order_value INT 4 order_state CHAR 20 ISQL displays the order_info co lumn, at 200 characters, with lo ts of blank space preceding the values: ISQL> select order_info from orders where order_value < 1000000 ORDER_INFO ---------- Solid Rods 5 in. diameter 1 record s[...]

ISQL Statements FairCom Corporation 3-21 Thursday 1 record selected ISQL> column sysdate format "Month" ISQL> select sysdate from syscalctable SYSDATE ------- May 1 record selected ISQL> column sysdate format "DDth" ISQL> select sysdate from syscalctable SYSDATE ------- 7th 1 record selected Note: If the select-list [...]

ISQL and Tools 3-22 FairCom Corporation COMPUTE statements have no ef fect until you issue a BREAK statement with the same br eak_spec . Issuing the COMPUTE statement without any arguments displays the currently-set COM- PUTE specifications, if any . Argument s A VG | MAX | MIN | SUM | COUNT The function to apply to values of column_name . The func[...]

ISQL Statements FairCom Corporation 3-23 3.7.6 DEFINE Synt ax DEFINE [ variable_name = value ] ; Description The DEFINE statement defines a variable and assigns an ASCII strin g value to it. When you refer to the defined variable in DISPLA Y statements, ISQL prints the value. The DEFINE statement is usefu l if you have scripts wit h many DISPLA Y s[...]

ISQL and Tools 3-24 FairCom Corporation Issuing the DISPLA Y statemen t wit hout any arguments displays the currently-set DISPLA Y specifications, if any . Argument s col_position An optional argument that specifies the horizontal positioning of the associated display value. There are two forms for the ar gument: If the DISPLA Y statement omits co [...]

ISQL Statements FairCom Corporation 3-25 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 ORDER_ID[...]

ISQL and Tools 3-26 FairCom Corporation edited. When you exit the editor , ISQL writes the buf fer 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 . Y ou can change the default by setting the EDITOR environment variable: • On UNIX, set the environment variab le at t[...]

ISQL Statements FairCom Corporation 3-27 3.7.10 GET Synt ax G[ET] filename; Description The GET statement reads the fi rst SQL statement stored in the specified script file. Argument s filename The name of the script file. IS QL reads the file unti l it encounters a semico lon ( ; ) statement terminator . It ap pends the statement to the history bu[...]

ISQL and Tools 3-28 FairCom Corporation ( 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.star[...]

ISQL Statements FairCom Corporation 3-29 CUSTOMER_NAME ORDER_INFO ------------- ---------- ORDER_STATE LOT_LOCATION START_DATE ----------- ------------ ---------- Ship Shapers Inc. I Beams Size 10 Processing Hot Rolling 12/26/1994 1 record selected 3.7.1 1 HELP Synt ax HE[LP] {COMMANDS|CLAUSES}; HE[LP] ; Description The HELP statement displays the [...]

ISQL and Tools 3-30 FairCom Corporation Description The HISTOR Y statement lists the statements in the statement hi story buffer , along with an identifying number . Notes • ISQL maintains a list of statements typed by the user in the statement history buffer . The SET HISTOR Y statement sets the size of the history buf fer . • The statements L[...]

ISQL Statements FairCom Corporation 3-31 3.7.13 HOST or SH or ! Synt ax { HOST | SH | ! } [host_command]; Description The HOST statement executes a host operating system co mmand without terminatin g the cur- rent ISQL session. Argument s HOST | SH | ! Synonyms for directing ISQL to execute an operating system com mand. host_command The operating s[...]

ISQL and Tools 3-32 FairCom Corporation 3.7.14 LIST Synt a x L[IST] [ stmt_num ]; Description The LIST statement displays th e statement with the specified statement num ber from the state- ment history buffer and makes it the current stat ement by adding it to the end of the history list. If LIST omits st mt_num , it displays the last statem ent i[...]

ISQL Statements FairCom Corporation 3-33 3.7.15 QUIT or EXIT Synt ax Q[UIT] Description The QUIT statement terminates the current ISQL session. Related St atement s QUIT and EXIT are synonymous. There is no difference in their effect. 3.7.16 RUN Synt ax R[UN] [stmt_num]; Description The RUN statement executes the st atement with the speci fied stat[...]

ISQL and Tools 3-34 FairCom Corporation Office Furniture Inc. 10 records selected ISQL> 3.7.17 SA VE Synt a x S[AVE] filename; Description The SA VE statement saves the last statement in the history buffer in filename. The GET and ST AR T statements can then be used to r ead and execute the statement from a file. If filename does not exist, ISQL[...]

ISQL Statements FairCom Corporation 3-35 | 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 character [...]

ISQL and Tools 3-36 FairCom Corporation DISPLA Y COST ON | OFF SET DISPLA Y COST ON displays the values th e c-treeSQL optimizer uses to calculate the least-costly query strategy fo r a part icular SQL statement. The UPDA TE ST A TISTICS statem ent updates the values displayed b y SET DISPLA Y COST ON. SET DISPLA Y COST OFF suppresses the display a[...]

ISQL Statements FairCom Corporation 3-37 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.19 SHOW[...]

ISQL and Tools 3-38 FairCom Corporation ____________________ EDITOR ..................... : vi HISTORY buffer size ........ : 50 PAUSE ..................... : ON COMMAND LINES .............. : 10 TIMEing command execution.. : OFF SPOOLing ................... : ON LINESIZE .................. : 78 REPORTing Facility ......... : ON PAGESIZE ..........[...]

ISQL Statements FairCom Corporation 3-39 Example T o record the displayed output into the file called STK, enter: ISQL> SPOOL STK ON ; ISQL> SELECT * FROM customer ; ISQL> SPOOL OFF ; 3.7.21 ST ART Synt ax ST[ART] filename [ argument ] [ ... ] ; Description The ST AR T statement executes the first SQL stat ement stored in the specified scr[...]

ISQL and Tools 3-40 FairCom Corporation 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 [...]

ISQL Statements FairCom Corporation 3-41 has_ccnstrs NOT NULL VARCHAR 1 has_ucnstrs NOT NULL VARCHAR 1 tbl_status NOT NULL VARCHAR 1 rssid NOT NULL INT 4 The following example uses the table command to d etail the struct ure of the tables used in examples throughout this chapter . ISQL> table - List the sample tables TABLENAME --------- customer[...]

ISQL and Tools 3-42 FairCom Corporation lot_id NOT NULL INT 4 purity DOUBLE 8 p_deviation DOUBLE 8 strength DOUBLE 8 s_deviation DOUBLE 8 comments CHAR 200 ISQL> table samples COLNAME NULL ? TYPE LENGTH ------- ------ ---- ------ lot_id INT 4 samples INT 4 comments CHAR 200 ISQL> 3.7.23 TITLE Synt a x TITLE [ [ TOP | BOTTOM ] [ [ LEFT | CENTE[...]

ISQL Statements FairCom Corporation 3-43 Examples The following example shows the effect of specifying a top title without a bottom ti tle, then both a top and bottom title. ISQL> TITLE "fred" ISQL> select * from syscalctable; fred FLD --- 100 1 record selected ISQL> TITLE BOTTOM "flintstone" ISQL> select * from sysc[...]

ISQL and Tools 3-44 FairCom Corporation[...]

FairCom Corporation 4-1 Chapter 4 Dat a Load Utility: dbload 4.1 INTRODUCTION This chapter describes the c-treeSQL database load utility , dbload . This utilit y loads records from an input d ata file into tables o f a databa se. The format of the data file is specified by a record description given in an input com man ds file to dbload . Both dblo[...]

ISQL and Tools 4-2 FairCom Corpora tion Figure 4- 1: dbload Execution Process 4.2 PREREQUISITES FOR DBLOAD Before running dbload , you need: • A valid, readable commands file • INSER T privileges on the tables named in the commands fil e 4.3 DBLOAD COMMAND LINE SYNT AX The dbload command does not directl y sp ecify an input file, but instead na[...]

Data Load Utility: dbload FairCom Corporation 4-3 -l logfile Specifies the file into which the error lo ggi ng is done. stderr is the default. db load 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 th[...]

ISQL and Tools 4-4 FairCom Corpora tion 4.4.1 V ariable Length Records For variable length records, the fields in th e da ta file can be of varying length. Unless the key- word FIXED is used in the commands fi le, it is assumed that the dbload record processing will be for variable length records. 4.4.2 Fixed Length Records For fixed length records[...]

Data Load Utility: dbload FairCom Corporation 4-5 4.5.1 The DEFINE RECORD St atement The DEFINE RECORD statement is us ed to define the record that is to be loaded into the data- base. It describes the data fou nd in the data file . The foll owing are the definition s that are made known by the DEFINE RECORD statemen t: • Names the record to be l[...]

ISQL and Tools 4-6 FairCom Corpora tion • start_position is th e position where t he field starts. It must be an unsigned integer . • end_position is the position where the fiel d en ds. It must be an unsi gned 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 characte[...]

Data Load Utility: dbload FairCom Corporation 4-7 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 ([...]

ISQL and Tools 4-8 FairCom Corpora tion The following is the commands f ile to load records into the or ders table. The input data file is or ders_in which is a binary file in the fixed length recor d fo rmat. 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) CHA[...]

Data Load Utility: dbload FairCom Corporation 4-9 Position for SHORT no t specified corr ectly . The size of the field (start position to end position) must be equa l to th e size of SHOR T . Position for LONG not specified correctly . The size of the field (start position to end position) mu st be equal to the size of LONG . Position for FLOA T no[...]

ISQL and Tools 4-10 FairCom Corporation[...]

FairCom Corporation 5-1 Chapter 5 Dat a 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 comm and file to dbdump . Both dbload and dbdump commands files[...]

ISQL and Tools 5-2 FairCom Corpora tion • SELECT privileges on the tables named in the commands file 5.3 DBDUMP COMMAND LINE SYNT AX The dbdump command accepts the commands file name, the database name and a command option. Synt a x dbdump -f commands_file [-n] database_name Options -f command s_file Specifies the f ile containing dbdump co mmand[...]

Data Unload Utility : dbdump FairCom Corporation 5-3 The syntax definition for the commands file is as shown: dbdump_commands: define_record_statement for_record_statement The following is sample commands file showin g dum p instructions. DEFINE RECORD ord_rec AS ( ord_no, item_name, date, item_qty ) FIELD DELIMITER ' ' ; FOR RECORD ord_r[...]

ISQL and Tools 5-4 FairCom Corpora tion • r ecor d _length is the length of the fixed length record. This length sh ould 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 . delim iter_char must be spec[...]

Data Unload Utility : dbdump FairCom Corporation 5-5 ( 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 tab le. The output data file is cust_out which is a binary file in the f[...]

ISQL and Tools 5-6 FairCom Corpora tion[...]

FairCom Corporation 6-1 Chapter 6 Schema Export Utility: dbschema 6.1 INTRODUCTION This chapter describes the c-treeSQL utility , dbschema . This utility recreates specified data- base elements and data. Synt ax dbschema [ -h ] [ -d ] [-u user_name ] [-a password ] [ -o outfile ] [ -p [ user_name.]procedure_name [ , ... ] ] [ -t [ user_name.]table_[...]

ISQL and Tools 6-2 FairCom Corpora tion against a corresponding user name before it connect s to the database. If omit ted, the default value depends on the environment. (On UNIX , the val ue of the DH_P ASSWD environmen t variable specifies the default password.) -o outfil e Redirects the output to the specified file. The default is standard outpu[...]

Schema Export Utility: dbschema FairCom Corporation 6-3 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 generat e definitions for all stored procedures[...]

ISQL and Tools 6-4 FairCom Corpora tion[...]

FairCom Corporation A-1 Appendix A T utorial Source Code A.1 INTRODUCTORY T U TORIAL 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; INSERT INTO CUSTMAST VALUES ('1000', '92867', 'CA[...]

ISQL and Tools A-2 FairCom Corporation 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), c[...]

Tutorial Source Code FairCom Corporation A-3 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 orde[...]

ISQL and Tools A-4 FairCom Corporation 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 FROM orderlist; DELETE FROM orderitems; DELETE FROM itemmast; DELETE FROM custmast; COMMIT W[...]

Tutorial Source Code FairCom Corporation A-5 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_i[...]

ISQL and Tools A-6 FairCom Corporation[...]

FairCom Corporation Index-i Index Symbols @Execute syntax . . . . . . . . . . . . . . . . . . . . . 3-12 A Adding titles . . . . . . . . . . . . . . . . . . . . . . . . . . 3-9 B Beginning titles . . . . . . . . . . . . . . . . . . . . . . . 3-9 BREAK statement . . . . . . . . . . . . . . . . . . . . . 3-4 BREAK statement syntax . . . . . . . . . .[...]

ISQL and Tools Index-ii FairCom Corporation Formatting ISQL outpu t . . . . . . . . . . . . . . . . . 3-3 G GET statement . . . . . . . . . . . . . . . . . . . . . . . . 3-3 GET statement syntax . . . . . . . . . . . . . . . . . 3-27 H HELP statement syntax . . . . . . . . . . . . . . . . 3-29 HISTORY statement . . . . . . . . . . . . . . . . . . .[...]

Index Index-iii FairCom Corporation 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 , [...]