HP c-tree-SQL ISQL and Tools Manuel d'utilisateur Page 1

ISQL and Tools Reference Guide For use with c-treeSQL ServerThis manual provides reference material for the ISQL interactive SQL utility and other adm

ISQL and Tools 1-2 FairCom Corporation

FairCom Corporation 2-1Chapter 2Quick Tour2.1 INTRODUCTORY TUTORIALiSQL_Tutorial1.sqlThis introductory tutorial will rapidly take you through the basi

ISQL and Tools2-2 FairCom Corporation2.1.2 DefineIn this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement i

Quick TourFairCom Corporation 2-3VALUES ('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main

ISQL and Tools2-4 FairCom Corporation2.2 RELATIONAL MODEL AND INDEXING TUTORIALiSQL_Tutorial2.sqlThis intermediate tutorial will advance the concepts

Quick TourFairCom Corporation 2-5OrderList - A table of records consisting of a list of orders. OrderItem - A table of records consisting of specific

ISQL and Tools2-6 FairCom CorporationISQL> CREATE INDEX itemnum ON itemmast (im_itemnum);ISQL> CREATE TABLE custmast ( cm_custnum VARCHAR(5),

Quick TourFairCom Corporation 2-7INSERT INTO custmast VALUES ('1000', '92867', 'CA', '1', 'Bryan Willia

ISQL and Tools2-8 FairCom Corporation2.2.5 Complete Relational Model and Indexing Tutorial Source CodeComplete source code for the relational model an

Quick TourFairCom Corporation 2-92.3 LOCKING TUTORIALiSQL_Tutorial3.sqlThis tutorial will introduce the concept of locking. The function-ality for th

Copyright © 1992-2004 FairCom Corporation All rights reserved.Portions © 1987-2004 Dharma Systems, Inc. All rights reserved.Eleventh Edition, First pr

ISQL and Tools2-10 FairCom Corporation2.3.2 DefineIn this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement

Quick TourFairCom Corporation 2-11The first process has the record associated with customer number 1003 locked. Meanwhile the second process has atte

ISQL and Tools2-12 FairCom Corporation2.3.5 Complete Locking Tutorial Source CodeComplete source code for the locking tutorial can be found in Appendi

Quick TourFairCom Corporation 2-132.4 TRANSACTION PROCESSING TUTORIALiSQL_Tutorial4.sqlThis tutorial will introduce the concept of transaction process

ISQL and Tools2-14 FairCom CorporationTransactionThese tables consist of a Customer Master table and an Item Master table that support prima-rily stat

Quick TourFairCom Corporation 2-152.4.3 ManageThis step provides data management functionality for the applica-tion. In this example we will add recor

ISQL and Tools2-16 FairCom CorporationSELECT orderlist.ol_custnum, custmast.cm_custnum FROM orderlist, custmast WHERE orderlist.ol_custnum = cus

FairCom Corporation 3-1Chapter 3ISQL Statements3.1 OVERVIEWThis chapter describes only those statements that are specific to ISQL. See the c-treeSQL R

ISQL and Tools3-2 FairCom CorporationThe file name must be enclosed in doubles quotes, such as:isql -s "test script.sql" testdb -u user_name

ISQL StatementsFairCom Corporation 3-33.4 FORMATTING OUTPUT OF ISQL QUERIESFormatting of database query results makes the output of a database query m

FairCom Corporation iTable

ISQL and Tools3-4 FairCom CorporationISQL includes several statements that provide simple formatting of SQL queries. The follow-ing table summarizes t

ISQL StatementsFairCom Corporation 3-5All the examples use the same ISQL query. The query retrieves data on outstanding customer orders. The query joi

ISQL and Tools3-6 FairCom Corporation3.4.1 Formatting Column Display with the COLUMN StatementYou can specify the width of the display for character c

ISQL StatementsFairCom Corporation 3-7Example 3-3: Customizing Format of Numeric Column DisplaysISQL> column order_value format "$99,999,999.

ISQL and Tools3-8 FairCom Corporationwhenever the value in the customer_name column changes. In other words, we need to specify a column break on the

ISQL StatementsFairCom Corporation 3-9The following example also issues two more DISPLAY statements to display the variable val-ues. As before, the DI

ISQL and Tools3-10 FairCom CorporationThe TITLE statement lets you specify text that ISQL displays before (TITLE TOP) or after (TITLE BOTTOM) the quer

ISQL StatementsFairCom Corporation 3-11 End of Orders Summary Report 23 records selectedISQL> 3.5 T

ISQL and Tools3-12 FairCom Corporation<SQL statement> ROLLBACK WORK ;An SQL statement starting immediately after a COMMIT WORK or ROLLBACK WORK

ISQL StatementsFairCom Corporation 3-13insert into stores values (1001,chassis);insert into stores values (1002,chips);select * from stores where item

ii FairCom Corporation3.5 The HELP and TABLE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-113.6 Transa

ISQL and Tools3-14 FairCom CorporationSKIP nThe optional SKIP clause can be used to skip the specified number of lines when the specified break occurs

ISQL StatementsFairCom Corporation 3-15ISQL> select customer_name from customers; CUSTOMER_NAME ---------

ISQL and Tools3-16 FairCom Corporation | BREAK | COLUMN | COMPUTE | DISPLAY | TITLEDescriptionThe CLEAR statement removes set

ISQL StatementsFairCom Corporation 3-17DescriptionThe COLUMN statement controls how ISQL displays a column's values (the FORMAT clause) and speci

ISQL and Tools3-18 FairCom Corporation(a) Format String DetailsTable 3-3: Numeric Format Strings for the COLUMN StatementCharacter Example Descriptio

ISQL StatementsFairCom Corporation 3-19ExamplesThe following examples are based on a table, orders, with columns defined as follows:ISQL> table ord

ISQL and Tools3-20 FairCom Corporationorder_info CHAR 200order_weight IN

ISQL StatementsFairCom Corporation 3-21 Thursday 1 record selectedISQL> column sysdate format "Month"ISQL> select sysdate from sysc

ISQL and Tools3-22 FairCom CorporationCOMPUTE statements have no effect until you issue a BREAK statement with the same break_spec. Issuing the COMPUT

ISQL StatementsFairCom Corporation 3-233.7.6 DEFINESyntaxDEFINE [ variable_name = value ] ;Description The DEFINE statement defines a variable and ass

FairCom Corporation iii5.4

ISQL and Tools3-24 FairCom CorporationIssuing the DISPLAY statement without any arguments displays the currently-set DISPLAY specifications, if any.Ar

ISQL StatementsFairCom Corporation 3-25ISQL> display col 5 "Number of orders placed by", customer_name, "=", n_ord on customer_

ISQL and Tools3-26 FairCom Corporationedited. When you exit the editor, ISQL writes the buffer contents as the last statement in the history buffer.By

ISQL StatementsFairCom Corporation 3-273.7.10 GETSyntaxG[ET] filename;DescriptionThe GET statement reads the first SQL statement stored in the specifi

ISQL and Tools3-28 FairCom Corporation ( orders.order_id = lots.order_id ) and ( ( customers.customer_name = 'Ship Shapers Inc.&a

ISQL StatementsFairCom Corporation 3-29CUSTOMER_NAME ORDER_INFO -------------

ISQL and Tools3-30 FairCom CorporationDescriptionThe HISTORY statement lists the statements in the statement history buffer, along with an identifying

ISQL StatementsFairCom Corporation 3-313.7.13 HOST or SH or !Syntax{ HOST | SH | ! } [host_command];DescriptionThe HOST statement executes a host oper

ISQL and Tools3-32 FairCom Corporation3.7.14 LISTSyntaxL[IST] [ stmt_num ];DescriptionThe LIST statement displays the statement with the specified sta

ISQL StatementsFairCom Corporation 3-333.7.15 QUIT or EXITSyntaxQ[UIT] DescriptionThe QUIT statement terminates the current ISQL session.Related State

iv FairCom Corporation

ISQL and Tools3-34 FairCom CorporationOffice Furniture Inc. 10 records selectedISQL> 3.7.17 SAVESyntaxS[AVE] filename;

ISQL StatementsFairCom Corporation 3-35 | COMMAND LINES number_lines | REPORT { ON | OFF } | ECHO { ON | OFF } | PAUSE {

ISQL and Tools3-36 FairCom CorporationDISPLAY COST ON | OFFSET DISPLAY COST ON displays the values the c-treeSQL optimizer uses to calculate the least

ISQL StatementsFairCom Corporation 3-37sys_chkcol_usagesys_keycol_usageHere's a page break! ...ISQL> SET DISPLAY COST ONISQL> -- Select f

ISQL and Tools3-38 FairCom Corporation ____________________ EDITOR ... : viHISTORY buffer size ... : 50

ISQL StatementsFairCom Corporation 3-39ExampleTo record the displayed output into the file called STK, enter:ISQL> SPOOL STK ON ;ISQL> SELECT *

ISQL and Tools3-40 FairCom CorporationISQL> -- Display a script file with the ! shell statement. The script's SQL ISQL> -- statement uses t

ISQL StatementsFairCom Corporation 3-41has_ccnstrs NOT NULL VARCHAR 1has_ucnstrs NOT NULL V

ISQL and Tools3-42 FairCom Corporationlot_id NOT NULL INT 4purity DO

ISQL StatementsFairCom Corporation 3-43ExamplesThe following example shows the effect of specifying a top title without a bottom title, then both a to

FairCom Corporation vDocumentation Overview PURPOSE OF THIS MANUALThis manual provides reference material for the ISQL interactive SQL utility as wel

ISQL and Tools3-44 FairCom Corporation

FairCom Corporation 4-1Chapter 4Data Load Utility: dbload4.1 INTRODUCTIONThis chapter describes the c-treeSQL database load utility, dbload. This util

ISQL and Tools4-2 FairCom CorporationFigure 4-1: dbload Execution Process4.2 PREREQUISITES FOR DBLOADBefore running dbload, you need:• A valid, reada

Data Load Utility: dbloadFairCom Corporation 4-3-l logfileSpecifies the file into which the error logging is done. stderr is the default. dbload also

ISQL and Tools4-4 FairCom Corporation4.4.1 Variable Length RecordsFor variable length records, the fields in the data file can be of varying length. U

Data Load Utility: dbloadFairCom Corporation 4-54.5.1 The DEFINE RECORD StatementThe DEFINE RECORD statement is used to define the record that is to b

ISQL and Tools4-6 FairCom Corporation• start_position is the position where the field starts. It must be an unsigned integer.• end_position is the pos

Data Load Utility: dbloadFairCom Corporation 4-7statement. The following example shows the list interchanged with respect to the list in the DEFINE RE

ISQL and Tools4-8 FairCom CorporationThe following is the commands file to load records into the orders table. The input data file is orders_in which

Data Load Utility: dbloadFairCom Corporation 4-9Position for SHORT not specified correctly.The size of the field (start position to end position) must

ISQL and Tools vi FairCom Corporation RELATED DOCUMENTATIONRefer to the following documents for more information:lowercase Lowercase type denotes eith

ISQL and Tools4-10 FairCom Corporation

FairCom Corporation 5-1Chapter 5Data Unload Utility: dbdump5.1 INTRODUCTIONThis chapter describes the c-treeSQL database dump utility, dbdump.dbdump

ISQL and Tools5-2 FairCom Corporation• SELECT privileges on the tables named in the commands file5.3 DBDUMP COMMAND LINE SYNTAXThe dbdump command acce

Data Unload Utility: dbdumpFairCom Corporation 5-3The syntax definition for the commands file is as shown:dbdump_commands: define_record_statement

ISQL and Tools5-4 FairCom Corporation• record_length is the length of the fixed length record. This length should include the length of field or recor

Data Unload Utility: dbdumpFairCom Corporation 5-5 ( no, name, loc ) FIELD DELIMITER ' ' ;FOR RECORD dept_rec DUMP INTO deptrecs_out

ISQL and Tools5-6 FairCom Corporation

FairCom Corporation 6-1Chapter 6Schema Export Utility: dbschema6.1 INTRODUCTIONThis chapter describes the c-treeSQL utility, dbschema. This utility re

ISQL and Tools6-2 FairCom Corporationagainst a corresponding user name before it connects to the database. If omitted, the default value depends on th

Schema Export Utility: dbschemaFairCom Corporation 6-3ADMIN@isis% dbschema -t dbp1,test_view rdsdbDBSCHEMAcreate table ADMIN.dbp1 ( c1 int

FairCom Corporation 1-1Chapter 1Introduction1.1 OVERVIEWInteractive SQL (often referred to throughout this manual as ISQL) is a utility supplied with

ISQL and Tools6-4 FairCom Corporation

FairCom Corporation A-1Appendix ATutorial Source CodeA.1 INTRODUCTORY TUTORIALCREATE TABLE CUSTMAST ( cm_custnum VARCHAR(5), cm_zip VARC

ISQL and ToolsA-2 FairCom Corporation oi_itemnum VARCHAR(6) );CREATE INDEX orderitem ON orderitems (oi_ordernum, oi_seqnumber);CREATE TABLE itemmas

Tutorial Source CodeFairCom Corporation A-3COLUMN oi_quantity FORMAT "A10" heading "QTY"COLUMN im_price FORMAT "$99.99"

ISQL and ToolsA-4 FairCom Corporation im_desc VARCHAR(48));CREATE TABLE custmast ( cm_custnum VARCHAR(5), cm_zip VARCHAR(10), cm_st

Tutorial Source CodeFairCom Corporation A-5ROLLBACK WORK;INSERT INTO orderitems VALUES ('3', 1, 2, '3');INSERT INTO orderitems VA

ISQL and ToolsA-6 FairCom Corporation

FairCom Corporation Index-iIndexSymbols@Execute syntax . . . . . . . . . . . . . . . . . . . . .3-12AAdding titles . . . . . . . . . . . . . . . . .

ISQL and ToolsIndex-ii FairCom CorporationFormatting ISQL output . . . . . . . . . . . . . . . . .3-3GGET statement . . . . . . . . . . . . . . . . .

IndexIndex-iii FairCom CorporationCOMPUTE . . . . . . . . . . . . . . 3-4, 3-7, 3-21COMPUTE syntax . . . . . . . . . . . . . . . .3-21DEFINE . . .