HP c-tree-SQL ISQL and Tool 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-20 04 FairCo m Corporation All rights reserved. Portions © 1987-20 04 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 [...]

Fa ir C om Co rp o ra ti o n i T able of Content s Documentation Overv iew Purpose of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .[...]

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

F ai r C o m Co r p o r a ti o n i ii 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 materi al 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 t o use the ISQL ut ility . AUDIENCE The reader of t[...]

ISQL and Tools vi FairCom Corporation RELATED DOCUMENT ATION Refer to the following docum ents for more infor mati on: lowercase Lowercase type denotes either u ser-supplied elements or n ames of other syntax diagrams. User-supplied elements include names of tables, host- language variables, e xpressions, and l iterals. Syntax diagrams can refe r t[...]

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

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 rapi dly tak e you throug h the basic use of the powerful interactive SQL (iSQL) databas e interface. iSQL is a full featured command lin e client side query tool useful for submitting ad hoc SQL statemen ts to a Server . Like[...]

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 successful 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. Th[...]

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 wil l ad vance the concepts introduced in the first tutorial by expanding th e number of tables and buildin g a relational model. This tutorial wi ll walk you through definin g an index for each table, demonstratin[...]

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 abou t 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 model and indexing tut o- rial can b e found in App endix A " Tutorial Source Code " .[...]

Quick Tour FairCom Corporation 2-9 2.3 LOCKING T UTORIAL iSQL_Tutorial3.sql This tutorial w ill introduce th e concept of locki ng. The functio n- ality for this tutorial focuses on adding records, then updati ng a single record to th e customer master table. From the iSQL utility the script will be parsed and passe d to the server . The script c o[...]

ISQL and Tools 2-10 FairCom Corporati on 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 successful 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 attemp ting to lock the tab le. At th is point both processes are effectively blocked. The first process is hold i[...]

ISQL and Tools 2-12 FairCom Corporati on 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 UTORIAL iSQL_Tutorial4.sql This tutorial w ill introduce th e concept of trans action processing, based on the relational model of the previou s tu torial. Records will be added to tables orderlist and orderitems as a single transac- tion. T ransacti on processing in iSQ L is hand le[...]

ISQL and Tools 2-14 FairCom Corporati on T ransaction These tables consist of a Customer Master tabl e and an Item Master table th at support prima- rily static in formation regarding a company's product line and custom er dem ographics. The orderlist and orderitems tables co nsist of dy nam ic information pertinent to day to day sales. This d[...]

Quick Tour FairCom Corporation 2-15 2.4.3 Manage This step provides data ma nagem ent functionality fo r the 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 ers will be processed as a transaction and "commited" or "rolled back" depending on the [...]

ISQL and Tools 2-16 FairCom Corporati on 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 d isconnecting 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 thos e statem ents that are specific to ISQL. See the c-tr eeSQL Ref- er ence Guide for detailed reference informatio n on standa rd SQL statements that can be issued in other environments. 3.2 S T ARTING INTERACTIVE SQL Start ISQL by issuing the isql comm[...]

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_str ing . c- treeSQL verifies the user name against a co rresponding password before it connects to the database.[...]

ISQL Statements FairCom Corporation 3-3 3.4 FORMATTING OUTPUT OF ISQL QUERIES Formatting of d atabase query results makes the output of a database query m ore presentable and understandable. The formatted output of an ISQL database query can be eith er displayed on the screen, written to a file, or spooled to a printer to produce a hardcopy of the [...]

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 -formatti 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 t atem[...]

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 types for these sample tables[...]

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 th e form at str ing in the FORMA T clause of the COLUMN state- ment. Y ou need to issue separate COLUMN stat eme[...]

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

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 custo mer_name column. Approach this task in two steps. First, devi se a DISPLA Y statement to display the customer name and confirm that it is disp layi ng correct ly . Then, issue COMPUTE stateme[...]

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 rel ationship with the previo usly issued DISPLA Y . As before, this example uses th[...]

ISQL and Tools 3-10 FairCom Corporati on 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 positione d by specifyin g the keyw ords LEFT , CENTER, or RIGHT ; or by specifying the actual column num ber corresp onding to the required position[...]

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. The [...]

ISQL and Tools 3-12 FairCom Corporati on <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 descript ions of stan dard SQL stat[...]

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 Corporati on 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 tings and corresponding DISPLA Y statements affect the disp lay of[...]

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 Corporati on | BREAK | COLUMN | COMPUTE | DISPLAY | TITLE Description The CLEAR statement removes settings m ade by the ISQL statemen t corresponding to option. Argument option Which ISQL statement' s settings to clear: • CLEAR HISTOR Y - Clears the IS QL 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_n ame The name of the column a[...]

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

ISQL Statements FairCom Corporation 3-19 Examples The following examples are based on a table, or ders , with 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 Corporati on 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 [...]

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 Corporati on 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 fun[...]

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 st rin g value to it. When you refer to the defined variable in DISPLA Y statements, ISQL prints the value. The DEFINE statement is useful if you have scripts with many DISPLA Y st[...]

ISQL and Tools 3-24 FairCom Corporati on Issuing the DISPLA Y statemen t without 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 Corporati on 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 [...]

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 fi le until it encounters a semicolon ( ; ) statement terminator . It ap pends the statement to the history buf[...]

ISQL and Tools 3-28 FairCom Corporati on ( 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.sta[...]

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 Corporati on Description The HISTOR Y statement lis ts 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[...]

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 operat ing system co mmand without termi natin g the cur- rent ISQL session. Argument s HOST | SH | ! Synonyms for directing ISQL to execute an operati ng system com mand. host_command The operatin[...]

ISQL and Tools 3-32 FairCom Corporati on 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 di sp lays the last statem en[...]

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 ef fect. 3.7.16 RUN Synt ax R[UN] [stmt_num]; Description The RUN statement executes the st atement with the speci fied sta[...]

ISQL and Tools 3-34 FairCom Corporati on 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 hist ory 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, IS[...]

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 Corporati on DISPLA Y COST ON | OFF SET DISPLA Y COST ON displays the valu es th e c-treeSQL optimizer uses to calculate the least-costly query strategy fo r a particular 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 [...]

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 Corporati on ____________________ 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 Corporati on 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 detail the struct ure of the tables used in examples throughout this chapter . ISQL> table - List the sample tables TABLENAME --------- customers[...]

ISQL and Tools 3-42 FairCom Corporati on 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 | CENT[...]

ISQL Statements FairCom Corporation 3-43 Examples The following example shows the effect of specifying a top title without a bott om 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 sys[...]

ISQL and Tools 3-44 FairCom Corporati on[...]

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 uti lit y loads records from an input d ata file into tables of a databa se. The format of the data file is specified by a record description given in an input com mands file to dblo ad . Both dblo[...]

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

Data Load Utility: dbload FairCom Corporation 4-3 -l logfile Specifies the file into which the error lo gging is done. stderr is the de fault. 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 tha[...]

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 comm ands 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[...]

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 found in the data fi le . The following are the definitions that are made known by the DEFINE RECORD statemen t: • Names the record to be loa[...]

ISQL and Tools 4-6 FairCom Corpora tion • start_position is the positio n where the field starts. It must be an u nsigned integer . • end_position is the positio n 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 charact[...]

Data Load Utility: dbload FairCom Corporation 4-7 statement. The following example shows the li st 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 t able. The input data file is or ders_in which is a binary file in the fixed leng th 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) 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 corr ectly . The size of the field (start position to end position ) mu st be equal to the size of LONG . Position for FLOA T [...]

ISQL and Tools 4-10 FairCom Corporati on[...]

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 com m and file to dbdump . Both dbload and dbdump commands file[...]

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 c ommand option. Synt a x dbdump -f commands_file [-n] database_name Options -f command s_file Specifies the f ile containing dbdump command[...]

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 fil e showin g dum p instructions. DEFINE RECORD ord_rec AS ( ord_no, item_name, date, item_qty ) FIELD DELIMITER ' ' ; FOR RECORD ord_[...]

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, i f 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 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 d ata file is cust _out which is a binary file in the[...]

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 , dbschem a . 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 i t connects to the database. If omitted, the default value depends on the environment. (On UNIX , the value of the DH_P A SSWD env iro nmen t variable specifies the default password.) -o outfil e Redirects the output to the specified file. The default is standard out [...]

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 g enerate 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 Corporatio n Formatting ISQL ou tput . . . . . . . . . . . . . . . . . 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 ,[...]