Home

iSQL User`s Manual - ALTIBASE Customer Support

1. TT TSOL Main Eunctionality iss dettes idee eb ea er edidere endete ee 2 12 Setting Up SO su ie sei es n eR A oma sets SH ae te Deere 3 13 ASOL Command EineOptions acte e DEREN ERO RR LERNEN Fe UR ede gek eae LEUR Ra 5 14 SOLCommands ERR POE 8 1 5 iSOL Environment Variables ctore P rte P i Se EG CU Eee E ER ERE ER Ge 1 5 1 ALTIBASE HOME 1 5 2 ALTIBASE PORT NO 1 5 3 ALTIBASE NLS USE esee oie 1 5 4 ALTIBASE_NLS_NCHAR_LITERAL_REPLACE 0 ccccccsscssesssssessessessesscssesssssessessessessessecssssssscsecsessscsucsncsecencesees 15 1 5 5 ISOL lt GONNEGTION Se ee ees ee Ge ie ee ER een Ee Re ke ee ae ee E ER 15 15 6 ISQL BUFFER SIZE NE AE AE RE Ia tide EE N 15 1 5 7 ALTIBASE DATE FORMAT 5 eeepc ee GE Ge Ee Gede ee ee Ee Ge Ge De Ge ee oe e Ee ee 15 1 5 8 ISOL EDITOR 16 1 5 9 ALTIBASE IPC FILEPATH 1 6 Personalizing iSQL wn l 6 Vglodinitsdl RED C EP C matic AN IM ea con pe DUE 16 2 loginsdl EE EE cata eet ee Pee Aet e eee aca ea te rep ep ite ene ee 1 6 3 Editing the LOGIN file 2 Examples of iSOL in US sesse n 2 1 Logging In to iSQL 2 1 1 Login Restrictions sesse sesse eene see 2 2 Starting Up and Shutting Down ALTIBASE HDB i 22 1 Starting Up ALTIBASE HDB RE OE EE e a i isa 21 2 2 2 Shutting Down ALTIBASE HDDB oett etes ete E
2. SYS CONSTRAINT COLUMNS SYS DATABASE SYS DATABASE LINKS SYS DATA PORTS SYS DIRECTORIES SYS DN USERS SYS DUMMY SYS ENCRYPTED COLUMNS _ SYS GRANT OBJECT SYS GRANT SYSTEM SYS INDEX COLUMNS _ SYS INDEX PARTITIONS SYS INDICES SYS LOBS SYS PART INDICES _ SYS PART KEY COLUMNS SYS PART LOBS SYS PART TABLES SYS PRIVILEGES SYS PROCEDURES SYS PROC PARAS _ SYS PROC PARSE SYS PROC RELATED SYS REPLICATIONS SYS REPL HOSTS SYS REPL ITEMS SYS REPL OFFLINE DIR SYS REPL OLD COLUMNS SYS REPL OLD INDEX COLUMNS SYS REPL OLD INDICES SYS REPL OLD ITEMS SYS REPL RECOVERY INFOS SYS SECURITY SYS SYNONYMS SYS TABLES SYS TABLE PARTITIONS SYS TBS USERS SYS TRIGGERS SYS TRIGGER DMI TABLES SYS TRIGGER STRINGS SYS TRIGGER UPDATE COLUMNS SYS USERS SYS VIEWS SYS VIEW PARSE SYS VIEW RELATED SYS XA HEURISTIC TRANS CLEAR DP DUAL EXPORT PARTITION TO FILE EXPORT TO FILE EXPORT USER TABLES FCLOSE FCLOSE ALL FCOPY 29 SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM
3. NAME TYPE IS NULL DNO SMALLINT FIXED NOT NULL DNAME CHAR 30 FIXED NOT NULL DEP LOCATION CHAR 9 FIXED MGR NO INTEGER FIXED INDEX NAME TYPE IS UNIQUE COLUMN SYS IDX ID 122 BTREE UNIQUE DNO ASC PRIMARY KEY iSQL User s Manual 30 2 4 Retrieving Information Related to the Database and Database Objects 2 4 4 Viewing Sequence Information The following commands are used to obtain information on all sequences that exist in the database SELECT FROM seg iSQL CONNECT sys manager Connect success iSOL CREATE USER userl IDENTIFIED BY userl Create success iSQL CONNECT userl userl Connect success iSOL CREATE SEQUENCE segl MAXVALUE 100 CYCLE Create success iSQL CREATE SEQUENCE seq2 Create success iSQL CONNECT sys manager Connect success iSQL CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30 Create success iSQL CREATE SEQUENCE seq3 CACHE 40 Create success iSQL gt SELECT FROM seg When accessing the database using the SYS account information of all sequences will be displayed USER NAME 30 1 9223372036854775806 NO SEQ3 1 1 9223372036854775806 NO 40 USER1 SEQ1 1 1 100 YES 20 USER1 SEQ2 1 1 9223372036854775806 NO 20 4 rows selected iSQL CONNECT userl userl Connect success iSOL SELECT FROM seq Information of all sequences created by User 1 will be displayed SEQUENCE NAME CURRENT VALUE 31 Examples of iSQL in Use 2 4 Retrievin
4. which one or more items must be selected A delimiter between optional or ENABLE DISABLE COMPILE mandatory arguments ENABLE DISABLE COMPILE Indicates that the previous argu iSQL gt select e lastname from ment is repeated or that sample employees code has been omitted EE EE Moon Davenport Kobain 20 rows selected Other Symbols Symbols other than those shown EXEC pl 1 above are part of the actual code acc NUMBER 11 2 Italics Statement elements in italics indi SELECT FROM table name cate variables and special values CONNECT userID password specified by the user Lower Case Char Indicate program elements setby SELECT e lastname FROM employ acters the user such as table names col ees umn names file names etc iSQL User s Manual About This Manual Rules Meaning Example Upper Case Char Keywords and all elements pro DESC SYSTEM SYS INDICES acters vided by the system appear in upper case Related Documents For more detailed information please refer to the following documents ALTIBASE HDB Installation Guide ALTIBASE HDB Getting Started Guide ALTIBASE HDB Administrators Manual ALTIBASE HDB Replication Manual ALTIBASE HDB Precompiler Users Manual ALTIBASE HDB ODBC Reference ALTIBASE HDB Application Program Interface Users Manual ALTIBASE HDB iSQL Users Manual ALTIBASE HDB Utilities Manual ALTIBASE HDB Error Message Reference On
5. CONNECT sys manager Connect success 27 Examples of iSQL in Use 2 4 Retrieving Information Related to the Database and Database Objects 2 4 Retrieving Information Related to the Database and Database Objects 2 4 1 Performance Views A performance view is a type of data dictionary table capable of inquiring about the server status and database information The following SELECT statement can be used to view the list of perfor mance views provided by ALTIBASE HDB iSQL gt SELECT FROM v tab TABLE NAME TYPE VSALLCOLUMN PERFORMANCE VIEW VSARCHIVE PERFORMANCE VIEW VSBUFFPAGEINFO PERFORMANCE VIEW VSBUFFPOOL STAT PERFORMANCE VIEW VSCATALOG PERFORMANCE VIEW VSDATABASE PERFORMANCE VIEW VSDATAFILES PERFORMANCE VIEW VSDATATYPE PERFORMANCE VIEW V DBA 2PC PENDING PERFORMANCE VIEW VSDBLINK REMOTE STATEMENT INFO PERFORMANCE VIEW VSDBLINK REMOTE TRANSACTION INFO PERFORMANCE VIEW VSDBLINK TRANSACTION INFO PERFORMANCE VIEW V DB FREEPAGELISTS PERFORMANCE VIEW V DB PROTOCOL PERFORMANCE VIEW VSDIRECT PATH INSERT PERFORMANCE VIEW VSDISKTBL INFO PERFORMANCE VIEW V DISK BTREE HEADER PERFORMANCE VIEW V DISK RTREE HEADER PERFORMANCE VIEW VSEVENT NAME PERFORMANCE VIEW VSFILESTAT PERFORMANCE VIEW VSFLUSHER PERFORMANCE VIEW VSFLUSHINFO PERFORMANCE VIEW For the complete list of the performance views provided with ALTIBASE HDB and the meanings of the columns please
6. iSOL CREATE TABLE location ID INTEGER NAME CHAR 20 ADDRESS VARCHAR 500 PHONE CHAR 20 Create success iSOL INSERT INTO location VALUES 1 ALTIBASE 10F1 Daerungpost tower II Guro dong Guro qu Seoul 152 790 Korea 82 2 2082 1000 1 row inserted In the following example the number of characters of a column of type CHAR or VARCHAR is set to 7 iSQL SET COLSIZE 7 iSQL SELECT id name address phone FROM location ID NAME ADDRESS PHONE 1 ALTIBAS 10F1 82 2 20 E Daerung 82 1000 post to wer II Guro d ong Gu ro qu Seoul 1 52 790 Korea 1 row selected iSQL User s Manual 42 2 8 Setting Output Options 2 8 Setting Output Options 2 8 1 Getting the Elapsed Time This function displays the time it took to execute the SOL statement iSOL SET TIMING ON Output the execution time in the last line after the command is executed iSOL select eno e firstname e lastname from employees ENO E FIRSTNAME E LASTNAME 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron Foster 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected elapsed time 0 01 iSOL SET TIMING OFF gt Execution time is not displayed 2 8 2 Setting Execution Time Units for Output This function sets the units with which to output SOL statement execution time Can be set to the following units Seconds Milliseconds Microseconds Nanoseconds iSOL SET TIMING ON iSOL SET TIMESCALE SEC iSO
7. The following users of ALTIBASE HDB will find this manual useful database administrators performance managers database administrators application developers technical support workers It is recommended that those reading this manual possess the following background knowledge basic knowledge in the use of computers operating systems and operating system utilities experience in using relational databases and an understanding of database concepts computer programming experience experience in database server operating system or network administration Software Environment This manual has been prepared assuming that ALTIBASE HDB 5 5 1 is used as the database server Organization This manual is organized as follows Chapter1 Using iSOL This chapter presents an overview of iSOL and explains the commands and how to use iSQL Chapter2 Examples of iSQL in Use This chapter provides in depth examples of each of the commands provided with iSQL Documentation Conventions This section describes the conventions used in this manual Understanding these conventions will make it easier to find information in this manual and other manuals in the series There are two sets of conventions iSQL User s Manual ii About This Manual syntax diagrams sample code conventions This manual describes command syntax using diagrams composed of the following elements Elements Meaning The start of a command If a s
8. e lastname sex VALUES p1 p2 p3 p4 END Create success iSQL gt SELECT FROM system sys procedures order by created desc limit 1 USER ID PROC OID PROC NAME OBJECT TYPE STATUS 2 3208680 EMP PROC 0 0 4 2 192 29 FEB 2012 29 FEB 2012 1 row selected emp proc which was created above is executed iSQL gt VAR eno INTEGER iSOL VAR first name CHAR 20 iSOL VAR last name CHAR 20 iSQL gt VAR sex CHAR 1 iSQL User s Manual 54 2 12 Creating Executing and Dropping Stored Procedures iSQL EXECUTE eno 21 Execute success iSOL EXECUTE first name Joel Execute success iSQL gt EXECUTE last name Johnson Execute success iSQL EXECUTE sex M Execute success iSOL EXECUTE emp proc eno firstname lastname sex Execute success iSOL SELECT eno e firstname e lastname sex FROM employees WHERE eno 21 ENO E FIRSTNAME E LASTNAME SEX 21 Joel Johnson M 1 row selected 2 12 2 2 Example 2 In the following example a procedure called outProc which executes a SELECT statement is cre ated iSQL CREATE TABLE outTbl il INTEGER i2 INTEGER Create success iSQL INSERT INTO outTbl VALUES 1 1 1 row inserted isQL 1 row inserted isQL 1 row inserted iSQL 1 row inserted isQL 1 row inserted iSQL gt SELECT FROM outTbl I1 I2 1 1 1 1 1 al 1 1 T Ba 5 rows selected iSQL gt CREATE OR REPLACE PROCEDURE outProc al
9. iSQL looks for the file speci fied by file name sal and executes its contents in sequence file name sql must be located in the same directory as the script file that called it If no such file exists iSOL raises an error If a user inputs file name sgl at the iSQL prompt the result will be the same as when using iSQL to execute file name sgl The script typically may include SQL statements iSQL commands or stored procedures An EXIT or QUIT command in the script terminates iSQL The following is an example of the execution of a sql in which schema sql is referenced from the SALTIBASE HOME directory In order for this example to be executed without error a sql must exist in the SALTIBASE HOME sample APRE schema directory alongside schema sal iSQL gt sample APRE schema a sql cat a sql schema sql Note The following chapter provides examples of editing the results of a query in an iSQL environment based on the tables created by execution of the above script see appendix Schema 35 Examples of iSQL in Use 2 6 File Management 2 6 3 Saving SQL Statements Of the commands currently in the iSQL buffer the SAVE command saves the most recently executed one in a file This file will be created in the current directory iSQL SELECT FROM book iSOL SAVE book sql SELECT FROM book is saved in the file book sal Save completed 2 6 4 Loading SQL Statements This function loads the first command in the sp
10. lowing table Category Type Commands Description iSQL startup Startup isal option If you execute this command in a shell and shut iSQL will start up For information on the down available options please refer to the 1 3 iSQL Command Line Options section Prompt iSQL gt Type a command at the iSQL prompt and press the ENTER key Shutdown EXIT Used to shut down iSQL QUIT ALTIBASE HDB ALTIBASE STARTUP Use the PRE PROCESS PROCESS CON startup and HDB Startup TROL META or SERVICE option to start shutdown ALTIBASE HDB up to the corresponding stage ALTIBASE SHUTDOWN Use one of the NORMAL IMMEDIATE or HDB Shut ABORT options to shut down ALTIBASE down HDB Database con Access the CONNECT logon nls This command allows access to the data nection and server as AS sysdba base as user with password pass after disconnection another user where logon has the syn having already accessed the database as tax another user in iSQL If CONNECT is suc user1 pass1 cessful the information related to the where nls has the syntax previous session is cleared The AS clause NLS character set allows the SYS user to access the server in sysdba manager mode Only one user is allowed to connect as sysdba at a time The nls option specifies the character set For detailed information on character sets please refer to the 1 3 iSOL Com mand Line Options NLS USE option Terminate a DISCONNECT Ends the current session and terminat
11. recently executed query statements will be created and the following screen will be visible To save space only a few of the blank lines that would be displayed on the screen are shown here iSQL SELECT sysdate FROM dual SYSDATE 01 JAN 2000 1 row selected iSQL gt ED SELECT sysdate FROM dual iSOL buf 1L 26C 2 6 6 2 Editing Existing Files If you want to edit an existing file type the file name in iSQL as a parameter when launching the text editor using the ed command When the screen is initialized blank lines will be displayed as tilde characters iSQL gt ED myquery sql myquery sql INSERT INTO employees ENO E FIRSTNAME E LASTNAME SEX VALUES 21 Shiloh Reynolds F INSERT INTO employees ENO E FIRSTNAME E LASTNAME SEX JOIN DATE VAL UES 22 Joshua Baldwin M TO DATE 2001 11 19 00 00 00 YYYY MM DD HH MI SS myquery sql 2 6 6 3 Editing Query Statements in History Lists You can use the number in the history list to edit previously executed commands In detail the query statements are stored in the temporary file iSQL buf in association with numbers and can be edited with reference to them The edited query will be stored again as the most recent record in the history list and can be executed by entering the re execute character iSQL gt H 1 SELECT FROM customers 2 SELECT FROM employees iSQL 2ed or iSQL gt 2 ed SELECT FROM orde
12. 2 7 Formatting SELECT Query Results 39 2 7 1 SET LINESIZE 39 VIVERA SEO IPAE 39 2 7 3 SELEOBOEESET etre ee ERR A er VER M ER Rio 40 2 7 4 SETFEEDBAGCK erect eo et ede o pm ee e et eee cete e dn 40 2 7 5 SET PAGESIZE 2 4 6 SET HEADING sitit en d a ee ee ee RO Rer e Ge nete eh edt 21 7 SEP COLSIZE c ecd etel e eee edid tate qai qve iet sese t e erede 2 8 Setting Output Options o 2 8 1 Getting the Elapsed Time ios OE EE etin dae EE 43 2 8 2 Setting Execution Time Units for Output 0 ssscsssesccsseeccsneeccsnsccessscccssecesuseessssccecnsecesssccessueeessuseessneceesnseesssees 43 2 8 3 Describing Foreign Key Information ssssccsssssccsssscssssecsssescssnsecssnecesssecssseesesuseessnsecesnsecssssesessueesesnsecssaecessnseessaees 44 2 8 4 Outputting Script Execution RESUItS ssecsssesccsssecsssessecssecssssseccssecessucessssececsnesessecssusecesnsccesneceecuseessnuceessneesssess 45 2 8 5 Outputting an Execution Plan sisssessssassscessssosasssasassedsancsssesetocsnzisconussssadesebsasecosssotaisescadesseasacbenssdeatbsasdeasndadeauanaseseeits 46 2 8 6 Setting Result Output Orientation tees Roe RARR ee R Gee Rea RR Geo eR ee R Roe Roe R Roe ARK ee 48 2 9 Viewing iSQL Display Settings 49 2 10 Host Variables 51 2 10 1 Declaring a Host Variable 51 2 10 2 Assigning a Value to a Host Vari
13. ALTIBASE HDB Use the SHUTDOWN command to shut down a running ALTIBASE HDB server 21 Examples of iSQL in Use 2 2 Starting Up and Shutting Down ALTIBASE HDB The following is an example of the use of iSQL to shut down ALTIBASE HDB For more information on shutting down ALTIBASE HDB please refer to the ALTIBASE HDB Administrators Manual Chapter 4 Startup and Shutdown isQL sysdba shutdown normal Ok Shutdown Proceeding TRANSITION TO PHASE Shutdown Altibase RP Finalization PASS shutdown normal success iSQL User s Manual 22 2 3 Connecting and Disconnecting 2 3 Connecting and Disconnecting 2 3 1 Connecting to a Database The CONNECT command is used to connect to ALTIBASE HDB with a specified user ID If the first con nection attempt fails the CONNECT command does not prompt again for the user ID or password CONNECT logon nis AS SYSDBA where longon has the syntax userlID password and nls has the syntax NLS character set 2 3 1 1 userlD password The user ID and password with which to establish a connection to ALTIBASE HDB 2 3 1 2 NLS character set The NLS option specifies the character set iSQL CONNECT sys manager NLS US7ASCII Connect success 2 3 1 3 AS SYSDBA The AS clause permits the SYS user to access the server in sysdba manager mode If CONNECT is successful the current session is terminated and a connection is established to the server using the specified user ID a
14. EMP TEL DNO SALARY SEX BIRTH JOIN DATE STATUS 1 Moon Chan seung CEO 01195662365 3002 M R 2 Davenport Susan designer 0113654540 1500 F 721219 18 NOV 2009 H 20 rows selected 2 7 2 SET LOBSIZE This specifies the number of characters to display when a CLOB column is queried using a SELECT statement In order to query CLOB column data using a SELECT statement the transaction mode must first be set to AUTOCOMMIT OFF iSQL gt CREATE TABLE cl1 I1 INTEGER I2 CLOB I INSERT INTO cl VALUES 1 A123456789 INSERT INTO cl VALUES 2 A1234 INSERT INTO cl VALUES 3 A12345 INSERT INTO cl VALUES 4 A1234567890123 iSOL AUTOCOMMIT OFF gt This sets the transaction mode to OFF so that a CLOB column can be queried Set autocommit off success iSQL gt SELECT FROM c1 I1 I2 1 A123456789 2 A1234 3 A12345 4 A1234567890123 4 rows selected 39 Examples of iSQL in Use 2 7 Formatting SELECT Query Results iSOL SET LOBSIZE 10 gt This specifies the number of characters to display on the screen when query ing a CLOB column using a SELECT statement iSOL SELECT FROM c1 I1 I2 1 A123456789 2 A1234 3 A12345 4 A123456789 4 rows selected 2 7 3 SET LOBOFFSET This specifies the starting location from which to display CLOB data when a CLOB column is queried using a SELECT statement In order to query CLOB column data using a SELECT statement the transaction mode must first be s
15. Ee EHE I a Lee Ee bees ke 21 2 3 Connecting and Disconnectilhig aier eite retinet ittm ape rte tete bert EAEE EE aSr dk 23 2 3 1 Conhecting to a Database OE EE NENES dd 23 2 3 2 Disconnecting from a Database eee 26 2 4 Retrieving Information Related to the Database and Database Objects 28 2 4 1 Performance Views eese tenent tttnnnnnntetnnnnns 28 2 4 2 Viewing the List of Tables cssssssssccssessecsssccssescesssecssnsssscssecesssecessnccessuessnsecesssecsssseessnsecesuceessuesesuseessnssesenseessnecs 28 ZA 3 VIEWING aTable Structures s eee de dt etd e EU 30 2 4 4 Viewing Sequence Information eee ee RR Geek Gee R AAR ee RR ee AR e Rea ARK ee ee Roe RR ee ARK AR 31 2 5 Controlling Transactions etes SR sesR RR kke SAGE 33 2 5 1 Defining Transaction Modes 33 2 5 2 PLANCOMMIT 33 2 6 File Management 34 26 1 Saving LE 34 2 62 Running Scripts s s EE EE EE ede Re ep RR DET Sa 34 2 6 3 Saving SQL StatemMeMnts sisscassessissscsassarasssssssocersseassessscdcassssannssedsssvnssvnnsasasccasccsasconnsasaassadssovassebsssbasoasdsanassasbis csdaonasisaise 36 2 64 Loading SOL Statermehts te RSEN RR ERR CREARE HE ENCARTA tiet 36 2 6 5 Saving DML Statements e reiten tiir obe iode ERA Ge gs ete rte deo ub ena eere eee tdeo 36 2 6 6 Editing Query Statements eiie ie e dete eite PRAE eR 37
16. For more information about the IPv6 address notation please refer to the ALTIBASE HDB Administrator s Manual U user_id Specifies a user ID registered in the database P password Specifies the password corresponding to the user ID PORT port_no Specifies the port number for connecting via TCP IP or IPC However when connecting ina Unix environment via IPC this option can be omitted If it is specified it is ignored and no error is raised To connect via TCP first set ISQL_CONNECTION TCP on the client and then enter the PORT_NO To connect via IPC in a Windows environment set the environment variable ISQL_CONNECTION IPC and specify the port number using one of the following the PORT option theALTIBASE IPC PORT NO environment variable thelPC PORT NO property in altibase properties If the environment variable ISOL CONNECTION is not set to IPC and the PORT option is omit ted the port number will be checked for first in the environment variable ALTIBASE PORT NO and then in the PORT NO property in altibase properties and if it is not set in either of those 5 Using iSOL 1 3iSQL Command Line Options places a prompt to enter it will be raised UNIXDOMAIN FILEPATH filepath When a server and client connect using a Unix domain socket in a Unix environment ISOL CONNECTION UNIX the connection will fail if the server and client have different val ues for ALTIBASE HOME and also have different Unix do
17. OUT INTEGER a2 IN OUT INTEGER AS BEGIN SELECT COUNT INTO al FROM outTbl WHERE i2 a2 END Create success In the following example outProc is executed iSQL gt VAR t3 INTEGER iSQL gt VAR t4 INTEGER iSQL EXEC t4 1 Execute success 55 Examples of iSQL in Use 2 12 Creating Executing and Dropping Stored Procedures iSQL EXEC outProc t3 t4 Execute success iSQL PRINT t3 NAME TYPE VALUE T3 INTEGER 5 2 12 2 3 Example 3 In the following example the procedure outProc1 is created iSQL gt CREATE OR REPLACE PROCEDURE outProcl pl INTEGER p2 IN OUT INTEGER p3 OUT INTEGER AS BEGIN p2 pl p3 pl 100 END Create success iSQL gt VAR v1 INTEGER iSQL VAR v2 INTEGER iSQL VAR v3 INTEGER iSQL EXEC v1 3 Execute success iSQL EXEC outProcl vl v2 v3 Execute success iSQL PRINT VAR HOST VARIABLE NAME TYPE VALUE V1 INTEGER 3 V2 INTEGER 3 V3 INTEGER 103 2 12 2 4 Example 4 In the following example a procedure called inoutProc1 which executes a SELECT statement is cre ated iSOL CREATE TABLE inoutTbl il INTEGER Create success iSQL INSERT INTO inoutTbl VALUES 1 1 row inserted iSQL 1 row inserted iSOL 1 row inserted iSQL User s Manual 56 2 12 Creating Executing and Dropping Stored Procedures iSQL gt SELECT FROM inoutTbl rows selected iSQL gt CREATE OR REPLACE PROCEDURE inoutProc al IN OUT
18. SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE Examples of iSQL in Use 2 4 Retrieving Information Related to the Database and Database Objects FFLUSH SYNONYM FOPEN SYNONYM FREMOVE SYNONYM FRENAME SYNONYM GET LINE SYNONYM IMPORT FROM FILE SYNONYM IS OPEN SYNONYM NEW LINE SYNONYM PRINT SYNONYM PRINTLN SYNONYM PUT SYNONYM PUT LINE SYNONYM RAISE APPLICATION ERROR SYNONYM REGISTER SYNONYM REMOVE SYNONYM REMOVEALL SYNONYM REMOVE DP SYNONYM REMOVE XID SYNONYM RESUME DP SYNONYM SET DEFAULTS SYNONYM SIGNAL SYNONYM SLEEP SYNONYM WAITANY SYNONYM WAITONE SYNONYM 91 rows selected 2 4 3 Viewing a Table Structure The following command is used to retrieve information on user created tables DESC table name iSOL CREATE TABLE departments DNO SMALLINT PRIMARY KEY DNAME CHAR 30 NOT NULL DEP LOCATION CHAR 9 MGR NO INTEGER iSQL DESC departments table name The name of a table whose information table structure you want to know TABLESPACE SYS TBS MEM DATA ATTRIBUTE
19. a SELECT statement the environment variable ALTIBASE DATE FORMAT can be used to change the default date format which is YYYY MM DD HH MI SS to some other date format Ex For Born Korn or Bash Shell export ALTIBASE DATE FORMAT DD MON YYYY 15 Using iSOL 1 5 iSQL Environment Variables 1 5 8 ISQL EDITOR This environment variable can be used to change the default editor WINDOWS notpad the others bin vi Ex CSH setenv ISQL EDITOR usr bin ed SH ISQL EDITOR usr bin ed export ISOL EDITOR 1 5 9 ALTIBASE IPC FILEPATH In a Unix environment if a client and the server have different values for ALTIBASE HOME they will not be able to connect via IPC if they have different Unix domain socket paths In this case in order to be able to connect via IPC it will be necessary to set the ALTIBASE IPC FILEPATH environment variable or the IPC FILEPATH iSQL option to the SALTIBASE HOME trc cm ipc file used by the server iSQL User s Manual 16 1 6 Personalizing iSQL 1 6 Personalizing iSQL iSQL users can customize their iSQL environment and use the same settings for each session For example using the OS file the user can specify a desired output format so that each query result dis plays the current time whenever query results are output These files can be categorized into the fol lowing two types 1 6 1 glogin sql For initialization tasks that must be conducted when iSQL is started iSOL supports the creation of a
20. file name Starts writing the results shown on the ment to a file screen to the file file name SPOOL OFF Stops writing the results shown on the screen to the file file name SQL script START file name Reads a script file and executes the SOL execution statements in sequence file name Performs a function similar to that of startup when executed via an iSQL prompt file name When used in a script this command exe cutes the file file name in the same direc tory as the calling script Save SOL SAVE abc sql Saves the last of the commands currently statement to in the iSOL buffer to a file file Load SOL LOAD abc sql Loads the first of the commands saved in statement a file at the end of the command buffer Save DML SET QUERYLOGGING ON This writes executed DML statements statements SET QUERYLOGGING OFF such as INSERT UPDATE DELETE and to file MOVE in SALTIBASE HOME trc isal query log Edit query ED For creating and editing temporary files statements ED file namel sal For editing existing files or creating new files 2ED or 2 ED Edits query command number 2 in the history list iSQL User s Manual 10 1 4 iSQL Commands Category Type Commands Description Control out Format SET LINESIZE 100 Sets the length of a display line for out put option SELECT putting the result of a SELECT query result col Must be between 10 and 32767 inclusive umn Default 80 Format SET LOBS
21. num INTEGER INSERT INTO savept VALUES 1 SAVEPOINT spl INSERT INTO savept VALUES 2 SELECT FROM savept ROLLBACK TO SAVEPOINT spl SELECT FROM savept COMMIT Altibase Client Query utility 17 Using iSOL 1 6 Personalizing iSQL Release Version 5 5 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved Write Server Name default 127 0 0 1 Write UserID userl Write Password ISQL CONNECTION TCP SERVER 127 0 0 1 PORT NO 20300 Set autocommit on success gt Executing glogin sal first 01 JAN 2000 gt heading off 1 row selected Set autocommit off success Execute login sql in the current work directory of the user after glogin sql is executed Drop success Create success 1 row inserted Savepoint success gt Itis executable only when Autocommit mode is off 1 row inserted NUM heading on 2 rows selected Rollback success 1 row selected Commit success iSQL User s Manual 18 2 Examples of iSQL in Use This chapter describes several examples of the use of iSQL to manipulate databases 19 Examples of iSQL in Use 2 1 Logging In to iSQL 2 1 Logging In to iSQL To use iSQL users must first be logged in Connection information may be input directly via a com mand line or via the iSQL input prompt isql U userID P password SYSDBA or isql SYSDBA Additional information necessary for connection with the server is the server
22. variables to values and execute SOL statements based thereon Functions for Historylist HISTORY Shows a list of the commands currently user conve display H saved in the iSOL buffer nience Repeatexe Repeats execution of the command cur cution rently in the iSQL buffer The most recently executed command will be exe cuted again 2 Executes the second command in a list output using the HISTORY command Shell com shell command A shell command that follows an excla mand execu mation point will be immediately exe tion cuted from within iSQL Comment comment Indicate a multiple line comment and a comment single line comment respectively Help HELP Provides information on how to use help HELP INDEX outputs a list of commands and HELP EXIT describes e g the EXIT command respectively 13 Using iSQL 1 5 iSQL Environment Variables 1 5 iSQL Environment Variables 1 5 1 ALTIBASE HOME Sets the directory in which the package is installed In the case of MS Windows this is set automatically when the server is installed however when the client is installed this is not automatically set due to the danger of a conflict with the environment variables for the server When installing the client the user must manually set this directory This environmental variable must be set in order to use ISOL Ex Windows Server set ALTIBASE HOME C Program Files Altibase Altibase5 Server altibase
23. Altibase Tools amp Utilities isQL User s Manual Release 5 5 1 January 15 2013 amp ALTIBASE PERFORMANCE SOLUTIONS Altibase Tools amp Utilities iSQL User s Manual Release 5 5 1 Copyright 2001 2010 Altibase Corporation All rights reserved This manual contains proprietary information of Altibase amp Corporation it is provided under a license agreement containing restric tions on use and disclosure and is also protected by copyright patent and other intellectual property law Reverse engineering of the Software is prohibited All trademarks registered or otherwise are the property of their respective owners Altibase Corporation 10F Daerung PostTower II 182 13 Guro dong Guro gu Seoul 152 847 Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 E mail support altibase com www http www altibase com Contents Preface iiid ee EE E AR EIEEE E E P A EE E NE E EEA AE AE i About This Manual Intended Audience a Software Environment ee ese Ge oe ee reed cesa Ge recae ect res clio epi verd obo dte ta ee ii Orgahizatiobi re oce RU PEIPER imas da D M DE ii Documentation CONVENTIONS issie sesde etnies eee eed ene eere eee eon eee ua ee eo reatu uoa qt ii Related DOocuinents ue tcd ve decis Ge A ERGER ro ER eR ooo ee GE IR V OE D e ect V On line Manuals 1 Using iSQL c eeeeeeee eres Ge Ge Sa IM kele I P
24. DUMMY SYSTEM SYS ENCRYPTED COLUMNS _ SYSTEM SYS GRANT OBJECT SYSTEM SYS GRANT SYSTEM SYSTEM SYS INDEX COLUMNS _ SYSTEM SYS INDEX PARTITIONS SYSTEM SYS INDICES SYSTEM SYS LOBS SYSTEM SYS PART INDICES _ SYSTEM SYS PART KEY COLUMNS SYSTEM SYS PART LOBS SYSTEM SYS PART TABLES SYSTEM SYS PRIVILEGES SYSTEM SYS PROCEDURES SYSTEM SYS PROC PARAS SYSTEM SYS PROC PARSE SYSTEM SYS PROC RELATED SYSTEM SYS REPLICATIONS _ SYSTEM SYS REPL HOSTS SYSTEM SYS REPL ITEMS SYSTEM SYS REPL OFFLINE DIR SYSTEM SYS REPL OLD COLUMNS _ SYSTEM SYS REPL OLD INDEX COLUMNS __ SYSTEM SYS REPL OLD INDICES _ SYSTEM SYS REPL OLD ITEMS SYSTEM SYS REPL RECOVERY INFOS SYSTEM SYS SECURITY SYSTEM SYS SYNONYMS SYSTEM SYS TABLES SYSTEM SYS TABLE PARTITIONS SYSTEM SYS TBS USERS SYSTEM SYS TRIGGERS SYSTEM SYS TRIGGER DML TABLES SYSTEM SYS TRIGGER STRINGS SYSTEM SYS TRIGGER UPDATE COLUMNS __ SYSTEM SYS USERS SYSTEM SYS VIEWS SYSTEM SYS VIEW PARSE SYSTEM SYS VIEW RELATED SYSTEM SYS XA HEURISTIC TRANS SYSTEM 25 TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE Examples of iSQL in Use 2 3 Connecting a
25. INTEGER AS BEGIN SELECT COUNT INTO al FROM inoutTbl WHERE il al END Create success iSQL VAR t3 INTEGER iSQL EXEC t3 1 Execute success iSQL EXEC inoutProc t3 Execute success iSQL PRINT t3 NAME TYPE VALUE T3 INTEGER 3 2 12 2 5 Example 5 In the following example the procedure inoutProc1 is created iSQL gt CREATE OR REPLACE PROCEDURE inoutProcl pl INTEGER p2 IN OUT INTEGER p3 OUT INTEGER AS BEGIN p2 pl p2 p3 pl 100 END Create success In the following example the procedure inoutProc1 is executed iSQL gt VAR v1 INTEGER iSQL VAR v2 INTEGER iSQL VAR v3 INTEGER iSQL EXEC v1 3 Execute success iSQL EXEC v2 5 Execute success iSQL EXEC inoutProcl vl v2 v3 Execute success iSQL PRINT VAR HOST VARIABLE 57 Examples of iSQL in Use 2 12 Creating Executing and Dropping Stored Procedures V1 INTEGER 3 V2 INTEGER 8 V3 INTEGER 103 2 12 3 Dropping Procedures The DROP command is used to drop delete procedures In the following example the procedure emp proc is deleted iSOL DROP PROCEDURE emp proc Drop success iSQL User s Manual 58 2 13 Creating Executing and Dropping Functions 2 13 Creating Executing and Dropping Functions 2 13 1 Creating Functions A function is provided to create functions When creating a function you must end with the follow ing syntax and the return type must be defin
26. IZE 10 Sets the number of characters to display SELECT when a CLOB column is output result col Default 80 umn of type CLOB SET LOBOFFSET 3 Sets the number of characters by which to offset the display when a CLOB column is output Default 0 Output SET FEED BACK ON Determines whether to output the num SELECT SET FEED BACK OFF ber of rows in a query result result count SET FEED BACK n Format rows SET PAGESIZE 10 Sets how many records of a SELECT query of SELECT result are output at one time When set to result 0 all resultant records are output Default 0 Show hide SET HEADING ON Sets whether to output the header of a SELECT SET HEADING OFF SELECT result result header Default ON Set SELECT SET COLSIZE N Sets the number of characters to output result out when CHAR or VARCHAR type columns put size are output as a SELECT query result Show SQL SET TIMING ON Sets whether to output the amount of statement SET TIMING OFF time taken to execute a SOL command execution Default OFF time Set the SOL SET TIMESCALE SEC Sets the unit of time for executing SOL statement SET TIMESCALE MILSEC statements as seconds milliseconds execution SET TIMESCALE MICSEC microseconds or nanoseconds time units SET TIMESCALE NANSEC for output Show hide SET FOREIGNKEYS ON Determines whether to include foreign foreign key SET FOREIGNKEYS OFF key information in the output when dis infor
27. L select eno e firstname e lastname from employees ENO E FIRSTNAME E LASTNAME 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron Foster 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected elapsed time 0 00 iSOL SET TIMESCALE MILSEC iSOL select eno e firstname e lastname from employees ENO E FIRSTNAME E LASTNAME 43 Examples of iSQL in Use 2 8 Setting Output Options Chan seung Susan Ken Aaron Farhad Ryu OUI i 0 N IH 20 rows selected elapsed time 0 72 iSOL SET TIMESCALE MICSEC iSQL gt select eno e firstname ENO E FIRSTNAME Moon Davenport Kobain Foster Ghorbani Momoi e lastname from employees E LASTNAME 1 Chan seung 2 Susan 3 Ken 4 Aaron 5 Farhad 6 Ryu 20 rows selected elapsed time 966 00 iSQL gt SET TIMESCALE NANSEC iSOL select eno e_firstname ENO E_FIRSTNAME Moon Davenport Kobain Foster Ghorbani Momoi e_lastname from employees E_LASTNAME 1 Chan seung 2 Susan 3 Ken 4 Aaron 5 Farhad 6 Ryu 20 rows selected elapsed time 681000 00 Moon Davenport Kobain Foster Ghorbani Momoi 2 8 3 Describing Foreign Key Information This function displays information on foreign keys when the DESC command is used to view the table structure iSQL gt SET FOREIGNKEYS ON gt The foreign key information will be output iSQL gt DESC bikes_ive_seen TABLESPACE SYS_TBS_MEM_DATA ATTRIBUTE YEAR USED SOLD KMS SAW_WHERE i
28. L must first be launched with the sysdba option in the same way as when a database is created Startup commands of ALTIBASE HDB can be executed only with the UNIX account with which ALTI BASE HDB including iSQL was installed The following is an example of the use of iSQL to start up ALTIBASE HDB For more information on starting up ALTIBASE HDB please refer to the ALTIBASE HDB Administrators Manual Chapter 4 Startup and Shutdown isql s 127 0 0 1 u sys p manager sysdba Altibase Client Query utility Release Version 5 5 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved ISQL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 ERR 910FB Connected to idle instance iSQL sysdba gt startup service Connecting to the DB server Connected TRANSITION TO PHASE PROCESS TRANSITION TO PHASE CONTROL TRANSITION TO PHASE META SM Recovery Phase 1 Preparing Database Dynamic Memory Version Parallel Loading SM Recovery Phase 2 Loading Database SM Recovery Phase 3 Skipping Recovery amp Starting Threads Refining Disk Table xou ud sha Ra iesu e Re s ececn are un OU C OESS SM Rebuilding Indices Total Count 101 prede EE AE ET AR SUCCESS TRANSITION TO PHASE SERVICE CM Listener started TCP on port 20300 CM Listener started UNIX RP Initialization PASS STARTUP Process SUCCESS Command execute success 2 2 2 Shutting Down
29. LECT statement is executed the execution plan information is displayed along with the resultant records ONLY The SELECT statement is prepared but not executed and only the execution plan infor mation is output This can be used to check the execution plan for a SELECT statement that involves host variable binding or to quickly check the execution plan for queries that take a long time to execute OFF After the SELECT statement is executed only the resultant records are displayed The following command is used to obtain detailed information about how conditions included in WHERE clauses written by the user will be execute ALTER SYSTEM SET TRCLOG DETAIL PREDICATE 1 If this property is set to 1 signifying ON as in the above statement the execution plans WHERE clause conditions including FIXED KEY RANGE VARIBALE KEY RANGE and FILTER are classified and iSQL User s Manual 46 2 8 Setting Output Options displayed in detail Therefore if the WHERE clause is complicated you can check which predicates will be executed by scanning the sorted indexes However this information may not be output if queries are changed to optimize them in some way The following example shows the output when the given SOL statement is executed When TRCLOG DETAIL PREDICATE has been set to 1 on and EXPLAIN PLAN ON the fol lowing is output in addition to the results iSQL ALTER SYSTEM SET TRCLOG DETAIL PREDICATE 1 Alter succe
30. M sequence tblName When this value is ON iSQL issues a special commit command to commit these commands 33 Examples of iSQL in Use 2 6 File Management 2 6 File Management 2 6 1 Saving Results iSQL enables results returned through iSQL to be saved in a designated file In the following exam ple results are stored in the designated file book txt using the SPOOL command To cancel this command use the SPOOL OFF command iSQL SPOOL book txt Spool start book txt Allsubsequently executed commands and their results will be written to book txt The file is created in the current directory iSOL SPOOL OFF Spool Stop gt From this point on no more commands or results will be saved in the file 2 6 2 Running Scripts 2 6 2 1 Command file name sql or START file name sqi file name sgl The script file to be executed If the filename extension is omitted iSQL assumes the default command file extension sql When this command is executed iSQL executes all of the commands in the specified script file in sequence command performs the same function as START An EXIT or QUIT command in the script file terminates iSQL The script file may include general SOL statements iSQL commands references to stored pro cedures etc The following is an example in which the schema sq script which can be found in the SALTIBASE HOME sample APRE schema directory which is the current directory is execute
31. N AB 7 t 1 row inserted iSQL gt SELECT FROM t1 C1 AB 7 4 1 row selected 63 Examples of iSQL in Use 2 15 Using National Character Sets iSQL User s Manual 64 Index 113 10 34 10 35 13 A ALTIBASE DATE FORMAT 15 ALTIBASE HOME 14 ALTIBASE IPC FILEPATH 16 ALTIBASE NLS NCHAR LITERAL REPLACE 15 63 ALTIBASE NLS USE 14 ALTIBASE PORT NO 14 AUTOCOMMIT ON OFF 33 Cc COLSIZE 11 42 comment 13 Configuring iSOL 17 CONNECT 23 D DESC 9 30 DISCONNECT 8 26 Display Settings 49 drop procedure 58 E ED 10 37 EDIT 37 Edit query statements 10 Editing the LOGIN file 17 END 54 environment variables 14 EXEC 13 51 EXECUTE 13 51 Execution time 11 EXIT 8 EXPLAIN PLAN 46 F FEEDBACK ON OFF 40 FOREIGNKEYS ON OFF 44 Formatting SELECT result 39 fuction 59 function creation 59 function execution 59 G glogin sql 17 H HEADING ON OFF 41 HELP 13 61 HISTORY 13 61 host variable 13 51 I iSQL 2 iSQL Command 8 iSQL Command Line Options 5 ISOL BUFFER SIZE 15 ISOL CONNECTION 15 ISOL EDIT 38 ISOL EDITOR 16 L LINESIZE 39 LOAD 36 load 10 LOBOFFSET 11 40 LOBSIZE 11 39 log in 20 login sql 17 N national character process 63 NCHAR 63 NVARCHAR 63 P PAGESIZE 41 performance view 28 PLANCOMMIT ON OFF 33 PREPARE 13 PREPARE SOL statement 53 PRINT 13 52 procedure creation 54 procedure execution 54 Q QUERYLOGGING ON OFF 36 QUIT 8 R re exe
32. NYM EXPORT TO FILE SYNONYM EXPORT USER TABLES SYNONYM FCLOSE SYNONYM FCLOSE ALL SYNONYM FCOPY SYNONYM FFLUSH SYNONYM FOPEN SYNONYM FREMOVE SYNONYM FRENAME SYNONYM GET LINE SYNONYM IMPORT FROM FILE SYNONYM IS OPEN SYNONYM NEW LINE SYNONYM PRINT SYNONYM PRINTLN SYNONYM PUT SYNONYM PUT LINE SYNONYM RAISE APPLICATION ERROR SYNONYM REGISTER SYNONYM REMOVE SYNONYM REMOVEALL SYNONYM REMOVE DP SYNONYM REMOVE XID SYNONYM RESUME DP SYNONYM SET DEFAULTS SYNONYM SIGNAL SYNONYM SLEEP SYNONYM WAITANY SYNONYM WAITONE SYNONYM 33 rows selected iSQL CONNECT sys manager Connect success iSQL SHOW USER User SYS iSQL User s Manual 24 iSQL gt CREATE TABLE systbl il INTEGER Create success iSQL SELECT FROM tab USER NAME SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM 2 3 Connecting and Disconnecting i2 CHAR 5 TABLE NAME TYPE STO COLUMNS _ SYSTEM STO DATUMS SYSTEM STO ELLIPSOIDS SYSTEM STO GEOCCS SYSTEM STO GEOGCS _ SYSTEM STO PRIMEMS SYSTEM STO PROJCS SYSTEM STO PROJECTIONS SYSTEM STO SRS SYSTEM STO USER COLUMNS _ SYSTEM SYS COLUMNS SYSTEM SYS COMMENTS SYSTEM SYS CONSTRAINTS SYSTEM SYS CONSTRAINT COLUMNS SYSTEM SYS DATABASE SYSTEM SYS DATABASE LINKS SYSTEM SYS DATA PORTS SYSTEM SYS DIRECTORIES SYSTEM SYS DN USERS SYSTEM SYS
33. On iSQL SHOW Timing Of iSQL SHOW Vertical iSQL SHOW ForeignKeys iSOL SHOW PlanCommit iSQL gt SHOW TIMING f VERTICAL Off FOREIGNKEYS Off PLANCOMMIT Off QUERYLOGGING QueryLogging Off iSOL SHOW Term On iSOL SHOW Feedback iSOL SHOW User ColSize LobOffset LineSize LobSize PageSize TimeScale Heading Timing Vertical ForeignKeys PlanCommit TERM FEEDBACK 1 ALL SYS 0 0 80 80 0 Second On Off Off Off Off 49 Examples of iSQL in Use 2 9 Viewing iSQL Display Settings QueryLogging Off Term On Feedback 1 iSQL User s Manual 50 2 10 Host Variables 2 10 Host Variables Host variables are first declared and then used Host variables are useful when executing procedures or functions 2 10 1 Declaring a Host Variable 2 10 1 1 Syntax VAR IABLE var name var type 2 10 1 2 Type The following types can be used when declaring variables INTEGER BYTE n NIBBLE n NUMBER NUMBER n NUMBER n m NUMERIC NUMERIC n NUMERIC n m CHAR n VARCHAR n DATE DECIMAL DECIMAL n DECIMAL n m FLOAT FLOAT n DOUBLE REAL BIGINT SMALLINT 2 10 1 3 Example The following examples demonstrate how to declare variables iSQL VAR pl INTEGER iSQL VAR p2 CHAR 10 iSOL VAR v double DOUBLE iSOL VAR v real REAL 2 10 2 Assigning a Value to a Host Variable 2 10 2 1 Syntax EXEC UTE var name value 2 10 2 2 Exampl
34. SQL User s Manual SMALLINT SMALLINT BIT 1 BIT 1 INTEGER VARCHAR 20 44 NOT NULL NOT NULL 2 8 Setting Output Options ITEM ID INTEGER FIXED NOT NULL COMMENT VARCHAR 100 FIXED PRICE INTEGER FIXED NOT NULL DATE SEEN DATE FIXED INDEX NAME TYPE IS UNIQUE COLUMN SYS IDX ID 143 BTREE UNIQUE ITEM ID ASC PRIMARY KEY ITEM ID FOREIGN KEYS MODEL ID SYS IDX ID 142 MID SYS CANDIDATE MODELS MID iSOL SET FOREIGNKEYS OFF gt The foreign key information will not be output iSOL DESC bikes ive seen TABLESPACE SYS TBS MEM DATA ATTRIBUTE NAME TYPE IS NULL MID SMALLINT FIXED YEAR SMALLINT FIXED NOT NULL USED BIT 1 FIXED NOT NULL SOLD BIT 1 FIXED KMS INTEGER FIXED SAW WHERE VARCHAR 20 FIXED ITEM ID INTEGER FIXED NOT NULL COMMENT VARCHAR 100 FIXED PRICE INTEGER FIXED NOT NULL DATE SEEN DATE FIXED INDEX NAME TYPE IS UNIQUE COLUMN SYS IDX ID 143 BTREE UNIQUE ITEM ID ASC PRIMARY KEY ITEM ID iSQL gt 2 8 4 Outputting Script Execution Results Commands can be used to control the output of created results When set to OFF this function prevents the result of a script command executed in iSQL from being displayed on the screen However even if it is set OFF the results of queries that are directly entered e g iSQL select from t1 will still be displayed on the screen the OFF setting only prevents script
35. able ou ssssssssssscsssesscsssssssusecssnsesessccssueecssusecssnsesssnsecsssseeessneesesnseessneeeeenseessaees 51 2 10 3 Viewing Host Variables Re eei e tene a eee ee eke gs bene eg EN 52 2 11 Executing Prepared SQL Statements ssecsssscsssesscsssecssssesccssecsesssecsssesessuecssusesssncessuecssnsecesnscessssecessscesesueccsueeeesueessneeesets 53 2 11 1 Prepared SQL versus Dynamic SQL StateMent sseccssssccsnesccssseccsneeccsnsecssseescsnsecsssseeessneesssueeecsneeseenseessnees 53 2 11 2 Prepared SQL Statements sss 53 2 12 Creating Executing and Dropping Stored Procedures 54 2 12 1 Creating Procedures 54 2 1 2 2 Executind Procedures istae dele NR epe dte ue e GR RARE Ge ee Rees 54 212 3 Dropping PrOCedUres eet ie iac re Re e RE NR R ette EORR re uie TTA t 58 2 13 Creating Executing and Dropping Functions etes Geek OAR Rek Gee Roe RR R Gee Rea RARR ee Rea 59 2 13 T Creating FUNCIONS nieno eiii i t Ree Ree eth RSG Ge ee Re Gee eee ee 59 2 13 2 Executing Functions 59 2 13 3 Dropping Functions 60 2 14 Convenient User Functions 61 2 14 HISTOEV etn es ee OE EA EG ee EO ee eg 61 Ed Ge ET ER EE EE asiti oe dast irane tiesias aaa i iisiaoiaana sasaistot 61 2 14 3 Getting nope E EA AEEA EAEE 61 2 15 Using National Character Sets ER EE ee 63 Preface About This Manual About This Manual This manual describes how to use iSQL to access a database Intended Audience
36. cution 61 Index Repeat execution 13 S SAVE 10 36 seq 31 sequence 31 Sequence information 9 set foreignkeys 11 set heading 11 setlinesize 11 set pagesize 11 set term 11 set timing 11 Setting Up iSOL 3 Shell command 13 shell commands 61 show all 12 show foreignkeys 12 show heading 12 show linesize 12 show pagesize 12 show timing 12 show user 12 shutdown 8 21 SPOOL 10 34 START 10 startup 8 21 Stop 8 stored procedure 54 SYSDBA 23 T tab 9 28 Table list 9 Table structure 9 TERM ON OFF 45 TIMESCALE 43 TIMING ON OFF 43 Transaction mode 9 transaction mode 33 V VAR 51 VARIABLE 51 VERTICAL ON OFF 48 Index 66
37. d iSOL START schema sql The SQL statements in the file are executed or iSQL schema sql When specifying a script file you can use a question mark to indicate the ALTIBASE HDB home directory SALTIBASE HOME of the user account The following is an example in which the schema sq script which can be found in the SALTIBASE HOME sample APRE schema directory is executed regardless of which directory is the iSQL User s Manual 34 2 6 File Management current directory iSOL sample APRE schema schema sql The question mark can also be used with the following iSQL commands EDIT SAVE LOAD SPOOL START The or characters can be used to insert comments in script files means that everything that follows until the end of the line will be handled as a comment whereas comments that span several lines are placed between and m NN 2 2 Command file name sql l Executes the specified script The functionality of the command is similar to that of the com mand This command searches for script files in the same path as the script currently being executed and is thus useful for executing embedded scripts file namel sall This indicates the embedded script to be executed If the extension is omitted iSOL assumes the default command file extension sql The command can be used for the following purposes If a script file that contains the text file name sgl is executed
38. e The following example shows how to assign a value to a variable iSQL EXECUTE pl 100 Execute success iSQL EXEC p2 abc Execute success 51 Examples of iSQL in Use 2 10 Host Variables 2 10 3 Viewing Host Variables 2 10 3 1 Syntax PRINT VAR IABLE Shows all declared variables PRINT var name Shows the type and value ofthe variable var name 2 10 3 2 Example The following shows the values of all declared variable iSQL PRINT VAR HOST VARIABLE NAME TYPE VALUE P1 INTEGER 100 P2 CHAR 10 abc V_REAL REAL V_DOUBLE DOUBLE iSOL PRINT p2 gt Outputs only variable p2 information NAME TYPE VALUE P2 CHAR 10 abc iSQL User s Manual 52 2 11 Executing Prepared SQL Statements 2 11 Executing Prepared SQL Statements 2 11 1 Prepared SQL versus Dynamic SQL Statements SQL statements executed in iSQL are usually executed according to the so called Direct Execution method In Direct Execution syntax analysis validity testing optimization and execution of a query are all performed at once However in Prepared Execution only the syntax analysis validity testing and optimization of the query are performed to set up an execution plan for the query which is then executed when requested by the client When creating an application that uses ODBC the Prepared Execution method is typically used and is more advantageous in terms of speed when an SQL state ment is to be repeatedly
39. ecified file to the last position in the iSQL buffer iSQL LOAD book sql iSQL SELECT FROM book Load completed iSQL gt The results of execution of SELECT FROM book can be seen 2 6 5 Saving DML Statements Executed DML statements such as INSERT UPDATE DELETE and MOVE are saved in SALTIBASE HOME trc isql query log Specify SET QUERYLOGGING ON to use this functionality and OFF to disable it iSQL gt SET QUERYLOGGING ON gt From this point on all executed DML statements will be saved in SALTIBASE HOME trc isql guery log iSQL CREATE TABLE t1 I1 INTEGER Create success iSQL INSERT INTO t1 VALUES 1 1 row inserted iSOL UPDATE t1 SET il 2 1 row updated iSQL gt SELECT FROM t1 I1 1 row selected iSQL gt DELETE FROM t1 1 row deleted iSOL DROP TABLE t1 Drop success iSQL EXIT cat SALTIBASE HOME trc isql query log gt All queries executed since SET QUERYLOGGING ON was executed can be observed 2009 09 16 10 36 14 127 0 0 1 20300 SYS INSERT INTO t1 VALUES 1 2009 09 16 10 36 25 127 0 0 1 20300 SYS UPDATE t1 SET il 2 2009 09 16 10 36 31 127 0 0 1 20300 SYS DELETE FROM t1 iSQL User s Manual 36 2 6 File Management 2 6 6 Editing Query Statements 2 6 6 1 Editing the Most Recent Query Statement The command ed is provided for creating and editing files in iSOL If you execute ed without parameters a temporary file named iSQL buf containing the most
40. ed END Successful creation of the function can be confirmed by checking the sys procedures table In the following example the function emp func which executes an UPDATE statement and a SELECT statement is created iSQL gt CREATE OR REPLACE FUNCTION emp func f1 IN INTEGER RETURN NUMBER AS f2 NUMBER BEGIN UPDATE employees SET salary 1000000 WHERE eno f1 SELECT salary INTO f2 FROM employees WHERE eno f1 RETURN 2 END Create success iSQL gt SELECT FROM system sys procedures USER ID PROC OID PROC NAME OBJECT TYPE STATUS PARA NUM RETURN DATA TYPE RETURN LANG ID RETURN SIZE RETURN PRECISION RETURN SCALE PARSE NO PARSE LEN CREATED LAST DDL TIME 2 3300024 INOUTPROC1 0 0 3 2 132 15 SEP 2010 15 SEP 2010 2 3302344 EMP_FUNC 1 0 1 6 30000 23 38 0 3 209 15 SEP 2010 15 SEP 2010 36 rows selected 2 13 2 Executing Functions Functions can be executed to simultaneously execute multiple queries If the function to be exe cuted has parameters as many variables as there are functions must be declared before the function is executed Additionally a variable for saving the result of the function must also be defined The following is an example of executing the function emp_func 59 Examples of iSQL in Use 2 13 Creating Executing and Dropping Functions iSOL VAR eno INTEGER iSQL VAR ret NUMBER iSQL EXEC eno 11 Execute success iSQL gt EXEC ret emp func eno Execute succes
41. es connection the connection with the server iSQL User s Manual 1 4 iSQL Commands Category Type Commands Description Database Display per SELECT FROM VSTAB Displays the list of all of the performance object infor formance views provided by the system This com mation view list mand is available only in iSQL inquiry Displaytable SELECT FROM TAB Displays the list of currently created list tables This command is only available in isQL Displaytable DESC samp Lists the column definitions for the table structure samp Display SELECT FROM VSSEQ If you accessed the server with the SYS sequence account information on all sequences is Information displayed If you accessed the server as another user only the information on the sequences generated by that user will be displayed This command is available only in iSOL Transaction Setting AUTOCOMMIT ON Determines whether to commit com control transaction AUTOCOMMIT OFF mands automatically at the time that mode they are executed Default ON Other SET SET PLANCOMMIT ON Determines whether to automatically functions SET PLANCOMMIT OFF commit commands such as DESC SELECT FROM TAB or SELECT FROM seg name when EXPLAIN PLAN is ON or ONLY and AUTOCOMMIT is OFF Default OFF 9 Using iSOL 1 4 iSQL Commands Category Type Commands Description File manage Output data SPOOL
42. et to AUTOCOMMIT OFF iSQL gt CREATE TABLE c1 I1 INTEGER I2 CLOB INSERT INTO cl VALUES 1 A123456789 INSERT INTO cl VALUES 2 A1234 INSERT INTO cl VALUES 3 A12345 INSERT INTO cl VALUES 4 A1234567890123 iSQL AUTOCOMMIT OFF Set autocommit off success iSOL SET LOBOFFSET 4 gt This specifies the starting location of data to be shown on the screen num ber of characters to skip when querying a CLOB column using a SELECT statement iSOL SELECT FROM c1 Ii I2 1 456789 2 4 3 45 4 4567890123 4 rows selected 2 7 4 SET FEEDBACK Outputs the number of records found when the results of a SELECT statement are output SET FEEDBACK ON OFF n ON Output the number of resultant records after execution of a SELECT statement OFF Do not output the number of resultant records after execution of a SELECT statement n Output the number of resultant records when the number is n or greater iSQL set feedback on iSQL gt select from employees where eno 3 ENO E LASTNAME E FIRSTNAME EMP JOB EMP TEL DNO SALARY SEX BIRTH JOIN DATE STATUS iSQL User s Manual 40 1 Moon CEO 01195662365 R 2 Davenport designer 0113654540 721219 18 NOV 2009 H 2 rows selected 2 7 5 SET PAGESIZE 2 7 Formatting SELECT Query Results Chan seung 3002 M Susan 1500 F Specifies the number of resultant rows to display at one time iSOL SET PAGESIZE 2 Showresults in groups comprising two ro
43. executed using host variable binding In iSQL the difference between these two methods lies only in whether variables are used or not there is no advantage in terms of speed 2 11 2 Prepared SQL Statements 2 11 2 1 Syntax PREPARE SQL statement 2 11 2 2 Example The following is an example of the use of the PREPARE command to execute an SQL statement iSQL VAR t1 INTEGER iSQL EXEC t1 3 Execute success iSOL PREPARE SELECT eno e firstname e lastname sex FROM employees WHERE eno t1 ENO 2 23 E FIRSTNAME Ken E LASTNAME Kobain SEX M 1 row selected 53 Examples of iSQL in Use 2 12 Creating Executing and Dropping Stored Procedures 2 12 Creating Executing and Dropping Stored Proce dures 2 12 1 Creating Procedures Support is provided for the creation and execution of stored procedures A stored procedure must end with the following END 2 12 2 Executing Procedures Procedures are executed in order to execute multiple queries at one time If the procedure to be exe cuted has parameters as many variables as there are parameters must be declared before the proce dure is executed 2 12 2 1 Example 1 In the following example a procedure named emp proc which executes an INSERT statement using IN parameters is created iSQL gt CREATE OR REPLACE PROCEDURE emp proc pl IN INTEGER p2 IN CHAR 20 p3 IN CHAR 20 p4 IN CHAR 1 AS BEGIN INSERT INTO employees eno e firstname
44. execution results e g iSQL gt a sal from being displayed iSQL SET TERM OFF iSOL SET TIMING ON gt The execution time is not output to the screen iSOL schema sql gt The script execution results are not output iSOL select eno e firstname e lastname from employees The results of directly input queries will be output ENO E FIRSTNAME E LASTNAME 45 Examples of iSQL in Use 2 8 Setting Output Options 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron Foster 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected elapsed time 0 00 iSQL SET TERM ON Script execution results will be output iSQL gt schema sql iSOL ALTER SESSION SET AUTOCOMMIT TRUE gt Start of results Alter success iSQL DROP TABLE ORDERS Drop success elapsed time 0 00 iSQL DROP TABLE EMPLOYEES Drop success elapsed time 0 00 iSQL gt CREATE INDEX ODR IDX3 ON ORDERS GNO ASC Create success elapsed time 0 00 gt Endof results m a EAC cution Plan 5 Outputting an In iSQL an execution plan can be output to fine tune SQL statements Using an execution plan DML statements such as SELECT INSERT UPDATE and DELETE can be checked In order to accomplish this the following command must be executed before a statement such as a SELECT statement is executed ALTER SESSION SET EXPLAIN PLAN option This option can be set to ON OFF or ONLY The default is OFF ON After the SE
45. for the usage environment In order to ensure stable iSQL operation we recommend that the following environment variables 3 Using iSQL 1 2 Setting Up iSQL be set ALTIBASE HOME the path to a server or client installation ALTIBASE PORT NO the port number to use to connect to the server ALTIBASE NLS USE the character set to use to display retrieved data to the user PATH the path containing the executable file which must equal SALTIBASE HOME bin iSQL User s Manual 4 1 3iSQL Command Line Options 1 3 iSQL Command Line Options TheA LTIBASE HDB server must be started before iSQL is executed The following options are case insensitive isql H S server name U user id P password PORT port no UNIXDOMAIN FILEPATH filepath IPC FILEPATH filepath SILENT F infile name O outfile name NLS USE NLS NCHAR LITERAL REPLACE prefer ipv6 S server name Specifies the name or IP address of a computer on which ALTIBASE HDB Server is running When connecting in IPC if this option is specified it is ignored and no error is raised regardless of the operating system It can be a host name an IPv4 address or an IPv6 address An IPv6 address must be enclosed by a left square bracket and a right square bracket For example in the case of localhost meaning this computer Localhost can be specified as the host name 127 0 0 1 as the IPv4 address or 1 as the IPv6 address
46. g Information Related to the Database and Database Objects 1 1 100 YES T d 9223372036854775806 NO 20 2 rows selected iSQL User s Manual 32 2 5 Controlling Transactions 2 5 Controlling Transactions 2 5 1 Defining Transaction Modes AUTOCOMMIT determines whether to automatically commit the results of a command at the time of execution iSOL AUTOCOMMIT OFF Commands are not automatically committed before being manually commit ted by the user Set autocommit off success iSQL AUTOCOMMIT ON Commands are automatically committed at the time of execution Set autocommit on success 2 5 2 PLANCOMMIT SET PLANCOMMIT ON OFF When EXPLAIN PLAN has been set to ON or ONLY there is the possibility that the iSQL commands DESC SELECT FROM TAB or SELECT FROM sequence tblName will be committed even if AUTO COMMIT has been set to OFF This setting determines whether to commit them automatically This setting has been provided to overcome the misunderstanding where the user believes that such a command has not been prepared but the system prepares the command in order to gener ate the execution plan The command would then be committed without the user knowing it when a COMMIT command is executed later When this value is OFF which is the default in a session for which EXPLAIN PLAN is ON or ONLY and AUTOCOMMIIT is OFF ALTIBASE HDB does not autocommit the above commands DESC SELECT FROM tab or SELECT FRO
47. global script file glogin sal by the DB administrator iSQL executes this script whenever any user executes iSOL or attempts to connect to ALTIBASE HDB for the first time The global file allows the DB administrator to make site specific iSOL environment settings for all users The global script file is located in SALTIBASE HOME conf 1 6 2 login sql iSQL also supports the login sal file which is executed after glogin sql If both the glogin sgl file and the login sal file exist login sql is executed after glogin sql during iSQL startup so the commands in login sql will take precedence If several people share one Unix account it will be impossible for them to personalize the glogin sql file In this case individual users may add SQL commands stored procedures or iSQL commands to their respective login sgl files in their personal work directories When a user starts up iSQL iSQL automatically searches the current directory for the login sal file and executes the commands in it The login sal file cannot modify initial iSQL settings or individual session actions 1 6 3 Editing the LOGIN file The user may change the LOGIN file like any other script The following is an example of user1 creat ing a LOGIN file that turns off autocommit mode and executes SQL statements vi glogin sal AUTOCOMMIT ON SET HEADING OFF SELECT sysdate FROM dual vi login sql AUTOCOMMIT OFF SET HEADING ON DROP TABLE savept CREATE TABLE savept
48. he execution of SQL statements SHOW USER Shows the current user SHOW FOREIGNKEYS Shows the current foreign key display setting SHOW VERTICAL Shows whether the results of a SELECT query will be output vertically Control out Show value SHOW ALL Shows the set values of the display set put option of iSQL dis tings for the current session play settings iSQL User s Manual 12 1 4 iSQL Commands Category Type Commands Description Variable and Variable dec VAR p7 INTEGER Declares the variable p1 as integer type Prepared SQL laration statements VARIABLE p2 CHAR 10 Declares the variable p2 as CHAR type Assign val EXECUTE p1 100 Assigns the value 100 to variable p1 ues to vari ables EXEC p2 abc Assigns the text abc to variable p2 Variable dis PRINT VAR IABLE Shows the currently declared variables la pey PRINT p1 Shows the type and value of variable p1 Prepared PREPARE SQL statement Separates the processes of query optimi SQL state zation and execution and executes the ment execu query as a prepared SOL statement In tion iSQL the default execution method for executing SQL statements is the Direct Execution method in which optimization and execution are performed at once There is no difference between the two execution methods in iSQL in terms of the results obtained however pre pared SQL statements can be used to bind
49. home Windows Client set ALTIBASE HOME C Program Files Altibase Altibase5 Client altibase home client 1 5 2 ALTIBASE PORT NO This is the port number of the server to connect to This can be specified either by using the PORT option or in altibase properties If no designated port number can be found in descending order of precedence in the PORT option in the environment variable ALTIBASE PORT NO or in altibase properties a prompt to enter the port number will appear 1 5 3 ALTIBASE NLS USE This is the character set used to display retrieved results to the user US7ASCII KO16KSC5601 MS949 BIGS GB231280 UTF8 SHIFTJIS EUCJP This can be set either using the NLS USE option or in altibase properties If NLS USE is not specified using the NLS USE option the environment variable ALTIBASE NLS USE or altibase properties in descending order of precedence US7ASCII is used as the default character set iSQL User s Manual 14 1 5 iSQL Environment Variables 1 5 4 ALTIBASE NLS NCHAR LITERAL REPLACE By default iSQL converts an entire query string to the database character set before sending the data to the database This behavior can be prevented for a given string literal by setting this prop erty to 1 and placing the N character in front of the string literal A property setting of 1 instructs iSQL to search for the N character in front of every string literal If the N character is fou
50. lgm1337 section 512 Nov 15 10 52 test drwxr xr x 6 wlgm1337 section 512 Nov 11 11 45 work 2 14 3 Getting Help Help is available for the commands provided with iSQL The HELP command without parameters outputs information on how to use help For help on specific commands enter HELP followed by the name of the command for which help is desired iSQL HELP Use help command Enter help index for a list of command iSQL HELP INDEX EDIT QUIT EXIT ROLLBACK ALTER HEADING SAVE AUTOCOMMIT H ISTORY SELECT COMMIT INSERT SPOOL CREATE LINESIZE START DELETE LOAD TIMING DESC LOBOFFSET UPDATE 61 Examples of iSQL in Use 2 14 Convenient User Functions DROP LOBSIZE EXECUTE MOVE EXPLAINPLAN PAGESIZE iSQL HELP EXIT exit or quit exit iSQL iSQL User s Manual VARIABLE TERM VERTICAI 62 2 15 Using National Character Sets 2 15 Using National Character Sets When using NCHAR and NVARCHAR type character constants if the following environment variables settings are made there will be no concerns over possible data loss The ALTIBASE NLS NCHAR LITERAL REPLACE environment variable must be set to1 export ALTIBASE NLS NCHAR LITERAL REPLACE 1 In order to use NCHAR type data that are encoded differently from the database character set enter the character N in front of the string iSOL CREATE TABLE t1 c1 NVARCHAR 10 Create success iSQL gt INSERT INTO tl VALUES
51. line Manuals Manuals are available at Altibase Technical Center http atc altibase com Altibase Welcomes Your Comments Please let us know what you like or dislike about our manuals To help us with future versions of our manuals please tell us about any corrections or classifications that you would find useful Include the following information The name and version of the manual that you are using Any comments that you have about the manual Your name address and phone number Write to us at the following electronic mail address support altibase com When you need an immediate assistance regarding technical issues please contact Altibase Techni cal Center V Preface About This Manual Thank you We appreciate your feedback and suggestions iSQL User s Manual Vi 1 Using iSQL Using iSQL 1 1 iSQL Overview 1 1 iSOL Overview iSQL is a user tool for accessing an Altibase database and retrieving and modifying stored data using SQL statements and a number of additional commands 1 1 1 iSQL Main Functionality ALTIBASE HDB Startup and Shutdown iSQL allows you to perform database management tasks such as starting up and shutting down the server and execute SQL statements using the same command prompt Database Connection amp Disconnection After ALTIBASE HDB starts up you can use various user names to connect to and disconnect from the database Database Object Information Inquiry iSQL allows yo
52. main socket paths In this case if the server and client use corresponding files e g ALTIBASE HOME trc cm unix Unix domain communication is possible IPC FILEPATH filepath When the client and the server are to connect via IPC ISQL_CONNECTION IPC in a Unix envi ronment if ALTIBASE HOME is set differently on them they will not be able to connect if they have different socket paths In this case Unix domain communication can be achived using the ALTIBASE HOME trc cm ipc file and then information about shared memory can be retrieved However this option can be omitted if ALTIBASE IPC FILEPATH is set F infile name Specifies a script file to be executed immediately after iSQL is launched O outfile name Specifies a file in which to store the results of the excuted iSQL commands This file will be cre ated in the current directory If the file already exists it will be overwritten H Outputs help information for iSQL execution SILENT This option turns on silent mode If silent mode is on noncritical messages such as the copy right notice etc will not be displayed NLS USE Specifies the character set with which to display data to the user The following character sets may be specified USZASCII KO16KSC5601 MS949 BIG5 GB231280 UIF8 SHIFTJIS EUC P If omitted the environment variable ALTIBASE NLS USE or altibase properties will be used in descending order of p
53. mation playing the table structure using DESC Default OFF Show hide SET TERM ON Determines whether to display the script execu SET TERM OFF results of execution of a script file on the tion result screen Default ON 11 Using iSOL 1 4 iSQL Commands Category Type Commands Description Control out Output exe ALTER SESSION SET Determines whether to output an execu put option cutionplan EXPLAIN PLAN ON tion plan for a SELECT statement tree ALTER SESSION SET Default OFF EXPLAIN PLAN ONLY ALTER SESSION SET EXPLAIN PLAN OFF SELECT SET VERTICAL ON Displays SELECT results vertically when result out SET VERTICAL OFF set to ON put direction Default OFF Show value SHOW LINESIZE Displays the current LINESIZE value of iSQL dis play settings SHOW COLSIZE Displays the current COLSIZE value SHOW LOBOFFSET Displays the current LOBOFFSET value SHOW LOBSIZE Displays the current LOBSIZE value SHOW PAGESIZE Displays the current PAGESIZE value SHOW PLANCOMMIT Shows whether PLANCOMMIT is ON or OFF SHOW QUERYLOGGING Shows whether DML statements wil be written to ALTIBASE_HOME trc isql_query log when executed SHOW FEEDBACK Shows the current FEEDBACK value SHOW HEADING Shows the current HEADING setting SHOW TERM Shows the current TERM setting SHOW TIMING Shows the current TIMING setting SHOW TIMESCALE This shows the current time units for t
54. must be set manu ally by the user We strongly suggest that you verify that this setting has been properly made as the application may not run properly if this setting is not made port no and NLS USE can be set using the environment variables or the server settings file alti base properties If these settings are made via all three methods they will take priority as follows in descending order 1 command line options 2 environment variables ALTIBASE PORT NO ALTIBASE NLS USE 3 server settings file altibase properties Therefore when it is desired to connect using options other than those that have been previously set the command line options can be used so that it is not necessary to change the settings in the server setting file or the environment variables If any options have not been set when iSQL is executed for the first time the user will be prompted to enter the corresponding variables At this time it is essential to enter values that are valid and fol low the proper format otherwise iSQL may not run properly However if the NLS USE option in particular has not been set no command prompt will appear at the time of execution Instead US7ASCII will be used and a connection attempt will be made In this case if the character set of the database is not US7ASCII the application will not execute properly or some of the user s data may become corrupted Thus it is paramount that NLS USE be set to a suit able value
55. name S user ID U and password P The user ID and password are not case sensitive In order for the SYS user to use iSQL as an administrator the SYSDBA option is used The SYSDBA option can be used for remote access 2 1 1 Login Restrictions Only one user is permitted to connect in SYSDBA mode at one time Two or more users cannot connect in SYSDBA mode at the same time You can access the database remotely in SYSDBA mode but can t start up the database For detailed information on system privileges please refer to the ALTIBASE HDB SQL Reference For detailed information on errors that may arise during iSQL execution please refer to the ALTIBASE HDB Error Message Reference isql S 127 0 0 1 U sys P manager SYSDBA or isql sysdba Altibase Client Query utility Release Version 5 5 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved Write Server Name default 127 0 0 1 Write UserID sys Write Password manager gt The password on the screen is not displayed ISQL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 iSQL sysdba iSOLis connected to the server and SQL iSQL and PSM commands can be input and executed here iSQL User s Manual 20 2 2 Starting Up and Shutting Down ALTIBASE HDB 2 2 Starting Up and Shutting Down ALTIBASE HDB iSQL can be used to start up and shut down ALTIBASE HDB 2 2 1 Starting Up ALTIBASE HDB To start up ALTIBASE HDB iSQ
56. nd iSQL sends the string to the database without converting it to the data base character set This is useful when it is desired to use NCHAR type data that are encoded differ ently from the database character set 0 convert all strings to the database character set without checking for the N character 1 do not convert strings that are preceded by the N character to the database character set Note Setting this variable to 1 can be expensive in terms of usage of client resources 1 5 5 ISQL CONNECTION When ALTIBASE HDB is used in a client server arrangement the user can set environment variables to select the client server protocol that is suitable for the operating environment ALTIBASE HDB supports the TCP IP IPC and Unix domain socket protocols The default protocol for communication with ALTIBASE HDB servers is TCP IP Note that when using the IPC protocol the value of ALTIBASE HDB properties related to the IPC chan nel IPC CHANNEL COUNT must be considered The following example shows how to set the environment variable when using the IPC protocol CSH setenv ISOL CONNECTION IPC SH ISQL CONNECTION IPC export ISOL CONNECTION 1 5 6 ISOL BUFFER SIZE The size of the buffer in which to store queries can be set using this environment variable Ex CSH setenv ISQL BUFFER SIZE 128000 SH ISQL BUFFER SIZE 128000 export ISQL BUFFER SIZE 1 5 7 ALTIBASE DATE FORMAT When retrieving Date type data using
57. nd Disconnecting ALTIADMIN ALTITBL TABLE SYS SYSTBL TABLE CLEAR DP SYNONYM DUAL SYNONYM EXPORT PARTITION TO FILE SYNONYM EXPORT TO FILE SYNONYM EXPORT USER TABLES SYNONYM FCLOSE SYNONYM FCLOSE ALL SYNONYM FCOPY SYNONYM FFLUSH SYNONYM FOPEN SYNONYM FREMOVE SYNONYM FRENAME SYNONYM GET LINE SYNONYM IMPORT FROM FILE SYNONYM IS OPEN SYNONYM NEW LINE SYNONYM PRINT SYNONYM PRINTLN SYNONYM PUT SYNONYM PUT LINE SYNONYM RAISE APPLICATION ERROR SYNONYM REGISTER SYNONYM REMOVE SYNONYM REMOVEALL SYNONYM REMOVE DP SYNONYM REMOVE XID SYNONYM RESUME DP SYNONYM SET DEFAULTS SYNONYM SIGNAL SYNONYM SLEEP SYNONYM WAITANY SYNONYM WAITONE SYNONYM 93 rows selected 2 3 2 Disconnecting from a Database DISCONNECT is used to terminate the current session and disconnect from the server The result of all subsequently executed SQL statements will be a Not connected message and CONNECT use rID password must be executed in order to connect to the server again DISCONNECT iSQL gt INSERT INTO systbl VALUES 1 A1 1 row inserted iSOL INSERT INTO systbl VALUES 2 A2 1 row inserted iSQL gt SELECT FROM systbl II I2 1 Al 2 A2 2 rows selected iSQL gt DISCONNECT Disconnect success iSQL gt INSERT INTO systbl VALUES 3 A3 ERR 91020 No Connection State iSQL User s Manual 26 2 3 Connecting and Disconnecting iSQL SELECT FROM systbl ERR 91020 No Connection State iSQL
58. nd password and the information in altibase properties Accord ingly the session information is cleared before connecting For instance if AUTOCOMMIT mode is set to TRUE in altibase properties and AUTOCOMMIT mode is changed to false in iSQL when the CONNECT statement is executed AUTOCOMMIT mode will be changed to TRUE because of the value in altibase properties If CONNECT fails the previous session is terminated and the connection with the server is closed In other words the result of all SOL statements executed thereafter will be a Not connected message Execute CONNECT userlD password AS SYSDBA to attempt to re establish a connection with the server CONNECT userID password AS SYSDBA Altibase Client Query utility Release Version 5 5 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved 23 Examples of iSQL in Use 2 3 Connecting and Disconnecting Write Server Name default 127 0 0 1 Write UserID SYS Write Password ISQL CONNECTION TCP SERVER 127 0 0 1 PORT NO 20300 iSQL SHOW USER User SYS iSQL CREATE USER altiadmin IDENTIFIED BY altiadmin1234 Create success iSOL CONNECT altiadmin altiadmin1234 Connect success iSQL SHOW USER User ALTIADMIN iSQL gt CREATE TABLE altitbl il INTEGER i2 CHAR 5 Create success iSQL gt SELECT FROM tab TABLE NAME TYPE ALTITBL TABLE CLEAR DP SYNONYM DUAL SYNONYM EXPORT PARTITION TO FILE SYNO
59. query is not executed values that would be determined after actual execution are indicated using question marks 22 like an ACCESS clause 2 8 6 Setting Result Output Orientation When querying data using a SELECT statement in iSQL the results can be displayed either horizon tally or vertically This function is suitable for outputting results that comprise a small number of rows and many col umns If such a result set is output horizontally as is usually the case it is difficult to compare columns and check the values However it is easy to see when output vertically iSQL gt SET VERTICAL ON This sets the print direction vertically iSQL gt SELECT FROM employees WHERE eno 2 ENO 2 E LASTNAME Davenport E_FIRSTNAME Susan EMP_JOB designer EMP_TEL 0113654540 DNO SALARY 1500 SEX aE BIRTH v Z21219 JOIN_DATE 18 NOV 2009 STATUS H 1 row selected iSQL User s Manual 48 2 9 Viewing iSQL Display Settings 2 9 Viewing iSQL Display Settings The following is an example of viewing the values of the iSQL environment variables for the current session iSOL SHOW User SYS iSOL SHOW ColSize iSOL SHOW LobOffset iSOL SHOW LineSize iSQL SHOW LobSize iSQL SHOW PageSize iSOL SHOW TimeScale iSQL gt SHOW USER Thisis the current user COLSIZE 0 LOBOFFSET 0 LINESIZE 80 LOBSIZE 80 PAGESIZE 0 TIMESCALE Second HEADING Heading
60. refer to the ALTIBASE HDB General Reference Chapter 3 Data Dictionary Data in a particular performance view can be queried in the same way as an ordinary table using a SELECT statement and using JOIN etc results can be output in various forms 2 4 2 Viewing the List of Tables Information on all of the tables that exist in the database can be retrieved using the following SELECT statement The SYS TABLES meta table is an internal system table that contains informa tion about the database catalog provided by ALTIBASE HDB iSOL SELECT FROM system sys tables iSOL SELECT FROM tab Thiscommandis available in iSQL only USER NAME TABLE NAME TYPE SYSTEM _ STO COLUMNS _ SYSTEM TABLE SYSTEM _ STO_DATUMS _ SYSTEM TABLE iSQL User s Manual 28 SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM 2 4 Retrieving Information Related to the Database and Database Objects STO ELLIPSOIDS STO GEOCCS STO GEOGCS __ STO PRIMEMS STO PROJCS STO PROJECTIONS STO SRS STO USER COLUMNS SYS COLUMNS SYS COMMENTS SYS CONSTRAINTS
61. reference and if it is still not specified the basic character set US7ASCII will be used NLS NCHAR LITERAL REPLACE iSQL User s Manual 6 1 3 iSOL Command Line Options Q convert all strings to the database character set without checking for the N character 1 do not convert strings that are preceded by the N character to the database charac ter set e prefer_ipv6 This option determines the IP address to be connected first when a host name is given for the s option If this option is specified and a host name is given for the s option this means that resolving the host name to the IPv6 address is prefered If this option is omitted isq1 connects to the IPv4 address by default If it fails to connect to the prefered IP version address an attempt is made to connect using the other IP version address For example when localhost is given for the s option and this option is specified 1sq1 first tries to connect to the 1 IPv6 address If this attempt fails 1sq1 proceeds to connect to the 127 0 0 1 IPv4 address If any of the S U or P options are missing from the above command the user will be prompted to input the option values 7 Using iSOL 1 4 iSQL Commands 1 4 iSOL Commands When iSQL is started an iSQL command prompt will appear and when iSQL commands are entered the results of execution will be displayed The iSQL commands are described individually in the fol
62. rs 37 Examples of iSQL in Use 2 6 File Management iSQL buf Note The command line parameter 2 which is the name of the file to be edited iSQL gt ed 2 must be dis tinguished from the number indicating the line in the file to edit After editing employees was replaced with orders iSQL gt h gt The history list currently in the isal buffer 1 SELECT FROM customers 2 SELECT FROM employees 3 SELECT FROM orders The query statement edited using the 2 ed command will be saved as the last command in the history list iSQL gt gt The most recently executed command will be executed ONO ORDER_DATE ENO CNO GNO OTY ARRIVAL DATE PROCESSING 11290007 29 NOV 2010 12 7111111431202 A111100002 70 02 DEC 2010 C 11290011 29 NOV 2010 12 7610011000001 E111100001 1000 05 DEC 2010 D 11290100 29 NOV 2010 19 7001011001001 E111100001 500 07 DEC 2010 D 12100277 10 DEC 2010 19 7610121220475 12310012 31 DEC 2010 19 7308281201145 C111100001 250 03 JAN 2011 O 30 rows selected iSQL User s Manual 38 2 7 Formatting SELECT Query Results 2 7 Formatting SELECT Query Results The results of a SELECT query can be formatted as desired by the user 2 7 1 SET LINESIZE Sets the size number of characters of one line to be displayed when the results of a SELECT state ment are output It must be between 10 and 32767 iSQL set linesize 70 iSQL select from employees ENO E LASTNAME E FIRSTNAME EMP JOB
63. s iSOL SELECT eno salary FROM employees WHERE eno 11 ENO SALARY 11 1000000 1 row selected 2 13 3 Dropping Functions The DROP FUNCTION statement is used to drop functions In the following example the function emp_func is deleted iSQL DROP FUNCTION emp func Drop success iSQL User s Manual 60 2 14 Convenient User Functions 2 14 Convenient User Functions 2 14 1 History A list of all previously executed commands can be displayed using the HISTORY command The num ber corresponding to a previously executed command can be used to easily execute that command again iSQL HISTORY View history list Or iSQL H 1 SELECT FROM tab 2 SELECT FROM v tab iSQL Re execute the most recent command HISTORY iSQL 2 gt Execute Command number 2 in history list SELECT FROM book 2 14 2 Shell Commands The exclamation point I is a convenient function that allows direct execution of shell commands from within iSOL iSQL ls al total 3417 rw r 1 wlgm1337 section 1198 Nov 1 13 30 aliases TWEE 1 wlgm1337 section 5353 Oct 18 21 17 bash history rw r 1 wlgm1337 section 1436 Nov 2 15 42 bashrc rw r 1 wlgm1337 section 1549 Dec 13 17 36 profile drwxr x 2 wlgml1337 section 512 Nov 2 02 00 TEMP drwxr xr x 2 root root 512 Oct 16 11 29 TT DB rw 1 wlgm1337 section 3446548 Dec 18 13 19 core drwxr x 2 wlgm1337 section 512 Nov 11 16 33 cron drwxr x 2 w
64. ss iSQL ALTER SESSION SET EXPLAIN PLAN ON Alter success iSOL select eno e lastname e firstname from employees where eno 1 ENO E LASTNAME E FIRSTNAME 1 Moon Chan seung 1 row selected PROJECT COLUMN COUNT 3 TUPLE SIZE 48 SCAN TABLE EMPLOYEES INDEX SYS IDX ID 164 ACCESS 1 SELF ID 2 FIXED KEY AND iSQL gt When TRCLOG DETAIL PREDICATE is not set to 1 and EXPLAIN PLAN ON the following is output in addition to the results iSQL ALTER SYSTEM SET TRCLOG DETAIL PREDICATE 0 Alter success iSQL ALTER SESSION SET EXPLAIN PLAN ON Alter success iSOL select eno e lastname e firstname from employees where eno 1 ENO E LASTNAME E FIRSTNAME 1 Moon Chan seung 1 row selected PROJECT COLUMN COUNT 3 TUPLE SIZE 48 SCAN TABLE EMPLOYEES INDEX SYS IDX ID 164 ACCESS 1 SELF ID 2 iSQL gt When TRCLOG DETAIL PREDICATE is not set to 1 and EXPLAIN PLAN ONLY only the follow ing is output iSQL ALTER SYSTEM SET TRCLOG DETAIL PREDICATE 0 Alter success iSQL ALTER SESSION SET EXPLAIN PLAN ONLY Alter success iSOL select eno e lastname e firstname from employees where eno 1 ENO E LASTNAME E FIRSTNAME PROJECT COLUMN COUNT 3 TUPLE SIZE 48 SCAN TABLE EMPLOYEES INDEX SYS IDX ID 164 ACCESS 1 SELF ID 2 47 Examples of iSQL in Use 2 8 Setting Output Options iSQL gt If EXPLAIN PLAN ONLY because only an execution plan is created and the
65. u to use SQL statements to query all database object information and supports con venient commands for inquiring about main objects Database Management via SQL Statements Because iSQL can be used to execute any kind of SOL statement you can control transactions and alter databases quickly and conveniently Functions to Improve User Convenience The above tasks can be easily and conveniently accomplished using the file management and edit ing functions the ability to execute shell commands over iSQL and the HISTORY function iSQL User s Manual 2 1 2 Setting Up iSQL 1 2 Setting Up iSQL In order for iSQL to access a server the following information is necessary ALTIBASE HOME A path to a server or client installation server name The name or IP address of a computer on which ALTIBASE HDB Server is running port no The port number to be used when connecting via TCP or IPC user id A User ID registered in the database password The password corresponding to the User ID NLS USE The character set with which to display retrieved data to the user ALTIBASE HOME can only be set using an environment variable while the other settings may be made using command line options For more information please refer to 1 3 iSOL Command Line Options The ALTIBASE HOME environment variable must be set in order to use iSQL In the case of Windows this is set automatically when the server is installed but in the case ofthe client
66. ws each iSOL select eno e firstname e lastname from employees ENO E FIRSTNAME 1 Chan seung 2 Susan ENO E FIRSTNAME 3 Ken 4 Aaron ENO E FIRSTNAME 5 Farhad 6 Ryu 20 rows selected E LASTNAME Moon Davenport E LASTNAME Kobain Foster E LASTNAME Ghorbani Momoi iSOL SET PAGESIZE 0 Showallofthe results on one page iSOL select eno e firstname e lastname from employees ENO E FIRSTNAME 1 Chan seung 2 Susan 3 Ken 4 Aaron 5 Farhad 6 Ryu 20 rows selected 2 7 6 SET HEADING E LASTNAME Moon Davenport Kobain Foster Ghorbani Momoi Sets whether to output the header with a SELECT result iSQL gt SET HEADING OFF gt Header is not displayed with the result iSOL select eno e firstname e lastname from employees Chan seung Susan Ken Aaron Farhad Ryu GO UI d 0 NH Moon Davenport Kobain Foster Ghorbani Momoi 41 Examples of iSQL in Use 2 7 Formatting SELECT Query Results 20 rows selected iSOL SET HEADING ON Outputs header in result iSOL select eno e firstname e lastname from employees ENO E FIRSTNAME E LASTNAME 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron Foster 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected 2 7 7 SET COLSIZE When the results of a SELECT statement are output sets the number of characters from a column of type CHAR or VARCHAR to display so that columns containing long lines of text can be easily viewed
67. yntactic element starts with an arrow it is not a complete command Reserved word The command continues to the next line If a syntactic ele ment ends with this symbol it is not a complete command The command continues from the previous line If a syn tactic element starts with this symbol it is not a complete gt command The end of a statement Indicates a mandatory element SELECT Indicates an optional element NOT i Indicates a mandatory element comprised of options One and only one option must be specified ADD DROP Indicates an optional element comprised of options ASC DESC iii Preface About This Manual Elements Meaning option ASC DESC CN ed Indicates an optional element in which multiple elements may be specified A comma must precede all but the first The code examples explain SQL stored procedures iSQL and other command line statements The following table describes the printing conventions used in the code examples Rules Meaning Example Indicates an optional item VARCHAR size FIXED VARIABLE Indicates a mandatory field for ENABLE DISABLE COMPILE

iSQL User`s Manual - ALTIBASE Customer Support

Contents

Download Pdf Manuals

Related Search

Related Contents