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 . . .
Kommentare zu diesen Handbüchern