Home

iSQL User`s Manual - ALTIBASE Customer Support

1. SELECT Indicates an optional element NOT A 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 F DESC iii Preface About This Manual Elements Meaning option ASC DESC CN NU 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 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 E EASTNAME Moon Davenport Kobain 20 rows selected Other Symbols Symbols other than those shown EXEC pl 1 a
2. HOST VARIABLE NAME TYPE VALUE P1 INTEGER 100 P2 CHAR 10 abc V_REAL REAL V_DOUBLE DOUBLE iSQL gt PRINT p2 gt Outputs only variable p2 information NAME TYPE VALUE P2 CHAR 10 abc 49 Examples of iSQL in Use 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 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 tl 3 Execute success iSQL gt PREPARE SELECT eno e firstname
3. INSERT INTO cl VALUES 4 A1234567890123 iSQL gt 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 iSQL User s Manual 36 2 7 Formatting SELECT Query Results iSQL gt 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 iSQL gt SELECT FROM cl 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 set 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 iSQL gt 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 iSQL gt SELECT FROM cl Ii I2 1 456789 2 4 3 45 4 4567890123 4 rows selected 2 7 4 SET FEEDBACK
4. Create success iSQL CREATE SEQUENCE seq2 Create success iSQL CONNECT sys manager Connect success iSQL gt CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30 Create success iSQL CREATE SEQUENCE seq3 CACHE 40 Create success iSQL gt SELECT FROM seq When accessing the database using the SYS account information of all sequences will be displayed USER NAME SEQUENCE NAME CURRENT VALUE INCREMENT BY MIN VALUE MAX VALUE CYCLE iSQL User s Manual 28 2 4 Retrieving Information Related to the Database and Database Objects 30 1 9223372036854775806 NO SEQ3 1 J 9223372036854775806 NO 40 USER1 SEQ1 i 1 100 YES 20 USER1 SEQ2 1 1 9223372036854775806 NO 20 4 rows selected iSQL CONNECT useri userl Connect success iSQL gt SELECT FROM seq Information of all sequences created by User 1 will be displayed SEQUENCE NAME CURRENT VALUE INCREMENT BY MIN VALUE MAX VALUE CYCLE CACHE SIZE SEQ1 1 1 100 YES 20 SEQ2 1 1 9223372036854775806 NO 20 2 rows selected 29 Examples of iSQL in Use 2 5 Controlling Transactions 2 5 Controlling Transactions 2 5 1 Defining Transaction Modes AUTOCOMMIT determines whether to automatically commit the results of acommand at the time of execution iSQL gt AUTOCOMMIT OFF gt Commands are not automatically committed before being manually commit ted by the user Set autocommit off success iSQL gt AUTOCOMMIT ON gt Commands are
5. FROM TAB Displays the list of currently created list tables This command is only available in iSQL Display table 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 avail able only in iSQL Transaction Setting trans AUTOCOMMIT ON Determines whether to commit com control action mode AUTOCOMMIT OFF mands automatically at the time that 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 seq name when EXPLAIN PLAN is ON or ONLY and AUTOCOMMIT is OFF Default OFF Using iSQL 1 4 iSQL Commands Category Type Commands Description File manage Output data SPOOL 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
6. Kobain Foster Ghorbani Momoi iSQL gt 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 elapsed time 966 00 iSQL gt SET TIMESCALE NANSEC E_LASTNAME Moon Davenport Kobain Foster Ghorbani Momoi iSQL gt 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 elapsed time 681000 00 E LASTNAME 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 ITEM ID SMALLINT SMALLINT BIT 1 BIT 1 INTEGER VARCHAR 20 INTEGER 41 IS NULL FIXED FIXED NOT NULL FIXED NOT NULL FIXED FIXED FIXED FIXED NOT NULL Examples of iSQL in Use 2 8 Setting Output Options 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 iSQL gt SET FOREIGNKEYS OFF gt The forei
7. The file is created in the current directory iSQL gt 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 iSOL 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 executed iSQL START schema sql gt 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 sql script which can be found in the SALTIBASE HOME sample APRE schema directory is executed regardless of which directory is the current directory 31 Examples of iSQL in Use 2 6 File Management iSQL gt sample APRE schema schema sql The question mark
8. 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 SEQ will be committed even if AUTOCOMMIT 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 FROM seq When this value is ON iSQL issues a special commit command to commit these commands iSQL User s Manual 30 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
9. f1 SELECT salary INTO f2 FROM employees WHERE eno f1 RETURN f2 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 iSQL User s Manual 56 2 13 Creating Executing and Dropping Functions The following is an example of executing the function emp_func iSQL gt VAR eno INTEGER iSQL gt VAR ret NUMBER iSQL EXEC eno 11 Execute success iSQL gt EXEC ret emp func eno Execute success iSQL gt SELECT eno salary FROM employees WHERE eno 11 ENO SALARY L 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 57 Examples of iSQL in Use 2 14 Convenient User Functions 2 14 C
10. in a script this command executes the file file name in the same directory as the calling script Save SOL SAVE abc sql Saves the last of the commands cur statement to rently in the iSQL buffer to a file file Load SOL LOAD abc sql Loads the first of the commands saved statement in a file at the end of the command buf fer Save DML SET QUERYLOGGING ON This writes executed DML statements statements to file SET QUERYLOGGING OFF such as INSERT UPDATE DELETE and MOVE in SALTIBASE HOME trc isgl query log Edit query statements ED For creating and editing temporary files ED file namel sql 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 result putting the result of a SELECT query column Must be between 10 and 32767 inclu sive Default 80 Format SET LOBSIZE 10 Sets the number of characters to display SELECT result when a CLOB column is output column of Default 80 type CLOB SET LOBOFFSET 3 Sets the number of characters by which to offset the display when a CLOB col umn is output Default 0 Output SET FEED BACK ON Determines whether to output the SELECT result SET FEED BACK OFF number of rows
11. notpad the others bin vi Ex CSH setenv ISQL EDITOR usr bin ed SH ISQL EDITOR usr bin ed export ISQL 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 global script file glogin sql by the DB administrator iSQL executes this script whenever any user executes iSQL or attempts to connect to ALTIBASE HDB for the first time The global file allows the DB administrator to make site specific iSQL 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 sql file
12. success iSQL ALTER SESSION SET EXPLAIN PLAN ON Alter success iSQL gt 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 iSQL gt 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 iSQL User s Manual 44 2 8 Setting Output Options iSQL gt If EXPLAIN PLAN ONLY because only an execution plan is created and the query is not executed values that would be determined after actual execution are indicated using question marks 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
13. the PRE PROCESS PROCESS CON startup and Startup TROL META or SERVICE option to start shutdown ALTIBASE HDB up to the corresponding stage ALTIBASEHDB SHUTDOWN Use one of the NORMAL IMMEDIATE or Shutdown ABORT options to shut down ALTIBASE HDB Database con Access the CONNECT logon nls AS This command allows access to the nection and server as sysdba database as user with password pass 1 disconnection another user where logon has the syn tax user1 pass1 where nls has the syntax NLS character set after having already accessed the data base as another user in iSQL If CON NECT is successful the information related to the previous session is cleared The AS clause allows the SYS user to access the server in sysdba man ager mode Only one user is allowed to connect as sysdba at a time The nls option specifies the character set For detailed information on charac ter sets please refer to the 1 3 iSOL Command Line Options NLS USE option Terminate a connection DISCONNECT Ends the current session and terminates 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 perfor object infor formance mance views provided by the sys mation view list tem This command is available only in inquiry iSQL Display table SELECT
14. values However it is easy to see when output vertically iSQL gt SET VERTICAL ON gt 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 HSE BIRTH 721219 JOIN DATE 18 NOV 2009 STATUS H 1 row selected 45 Examples of iSQL in Use 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 iSQL gt SHOW User SYS iSQL gt SHOW ColSize iSQL SHOW LobOffset iSQL gt SHOW LineSize iSQL SHOW LobSize iSQL SHOW PageSize iSQL SHOW TimeScale iSQL SHOW Heading iSQL SHOW Timing Of iSQL SHOW Vertical iSQL SHOW ForeignKeys iSQL gt SHOW PlanCommit iSQL gt SHOW QueryLogging iSQL SHOW Term On iSQL SHOW Feedback iSQL SHOW User ColSize LobOffset LineSize LobSize PageSize TimeScale Heading Timing Vertical ForeignKeys PlanCommit iSQL User s Manua USER Thisis the current user COLSIZE 0 LOBOFFSET 0 LINESIZE 80 LOBSIZE 80 PAGESIZE 0 TIMESCALE Second HEADING On TIMING f VERTICAL Off FOREIGNKEYS Off PLANCOMMIT Off QUERYLOGGING Off TERM FEEDBACK 1 ALL SYS 0 0 80 80 0 Second On Off Off Off Off 46
15. 2 9 Viewing iSQL Display Settings QueryLogging Off Term On Feedback 1 47 Examples of iSQL in Use 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 NCHAR n NVARCHAR 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 gt VAR p1 INTEGER iSQL gt VAR p2 CHAR 10 iSQL gt VAR v_double DOUBLE iSQL gt 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 Example The following example shows how to assign a value to a variable iSQL EXECUTE pl 100 Execute success iSQL EXEC p2 abc Execute success iSQL User s Manual 48 2 10 Host Variables 2 10 3 Viewing Host Variables 2 10 3 1 Syntax PRINT VAR IABLE gt Shows all declared variables PRINT var_name gt Shows the type and value of the variable var_name 2 10 3 2 Example The following shows the values of all declared variable iSQL gt PRINT VAR
16. 21 222 Shutting Dawn ALTIBASE HDB tte tette ieg diete ARAA RAES 21 2 3 Connecting and Disconinectilig eie ettet eie ee be aret rele rei eee toties eee irt enid 23 2 3 1 Connecting t a Database niione i e sete Erbe tete Ae cole eee A secrete e deae 23 2 3 2 Disconnecting from a Database ssccssssscsssessssesssnsecssssssesssecessseeessnessssnseessnseeesssecesssecssaseessueecssneeessneesesneeessneees 26 2 4 Retrieving Information Related to the Database and Database Objects sscssssscsssssessseecessecssneseessseesesneeesseesesees 27 24 1 Performance VIEWS aims iiini i ree ee EEE Atte DA eet rib ere e cnet 27 2 4 2 Viewing the List of Tables esssssssssssssccsessessseccssescessecssnsesesusecssnsccesssecessusecssuseeesusecsensseccaneeessuseessneeessneeessneessasees 27 2 4 3 Viewing a TableStruct te unre DR REDEEM ENA 28 2 4 4 Viewing Sequence INfOrMmatiOn ssssscccssscsssessccssescesssecssssesccssecssssseccsnecesuecssnseessusecssusecssuseeessueeessueecesueesseneessaeees 28 2 5 Controlling Transactions ectetuer tette teeth ee ss eetoe edes e iege kakaa h e etee tee age TEANA Eoi aeii tastiene 30 2 51 Defining Transaction Modes eE tide ood certe ane aes 30 25 2 PLANCOMMIT m 30 26 File MANAG erent i irit e ertet e iet vrbe ric ibl Po iei E irte AR eoe neeenia 31 PIED 31 2 62 RUNNING Scripts itd aese ceteri ente b eR Mera ree AE
17. 7 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 sq first 28 DEC 2004 gt heading off 1 row selected Set autocommit off success gt 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 It is executable only when Autocommit mode is off 1 row inserted NUM gt heading on 1 2 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 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
18. Altibase Tools amp Utilities isQL User s Manual Release 6 1 1 February 4 2013 Z LTIBASE Altibase Tools amp Utilities iSQL User s Manual Release 6 1 1 Copyright 2001 2012 Altibase Corporation All rights reserved This manual contains proprietary information of Altibase 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 Il 182 13 Guro dong Guro gu Seoul 152 847 South Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 Homepage http www altibase com Contents PHOT ACE PR A AE AAEE IAR AEEA E EEEE AEN EEEE E E EASA TAA OAA OEN EAE i About This Manual well Intended Audience ii Software Envitonmelit usce pcc bett cie c A N a a a aaaea ii OrganizatioH estote imt amc ad M M M NRI ii Documentation CONVENTIONS osineen iari E E iR AN RRR i ii Related Bref V MR a LR ERE V On line Manuals ese eV Altibase Welcomes Your Comments 1 Using iSQL crece eee eese eene tenens tnuae 1 eo PR E RET 2 TIT TiSQE Main F nctloriality tridente ter Set tre de E E RH ERE E 2 1 2 Setting UP ISOL
19. E deett itus to ebbe EAE tpe eoa 31 2 6 3 Savitig SOL Statements eer itte re nieht OEN pe Pene bres eed 32 2 64 l oading SOL Statements ecrit tte e iie EE Te eee Pre Here edet etae EURO 33 2 6 5 Saving DML Statements etienne re ere Ant trei eee eer db eire eee ree nth eripiet 33 2 6 6 Editing Query Staterments s s RE RE RR RR ee EUER TE SER 33 2 7 Formatting SELECT Query Results meserna i an EA EANES 36 PIU MCI SMEs pice ieie INRA A EAEE NRA OER NSR 36 2 7D SBT LOBSIZE epe 36 27 3 SEV EOBORESET ehe t ROREM RSEN ee RD DER ed Es 37 2 4A SET FEEDBACK ase rre t e ee ee eet a e abe ee eiu e ves tore eee REA ED b re etus 37 2 7 5 SET PAGESIZE 27 6 SET HEADINQ oit er eH Nate e ee eee en dr e n tede 2 74 SET COLSIZE 2 ih pet eR DUREE ND reete see 2 8 Setting Output Options 2 8 1 Getting the Elapsed Time sssssesiscosasssssssscssssssasssosoacencasesssosvesesetsssoauasencuacebsndssaessscsbsedtsbedetsossdbscabdacoaiasntavescbasseatosaeZbiaeite 40 2 8 2 Setting Execution Time Units for OUtDUE eessesscsssessccseccsssseccsseccesscecessecessuecssssecessuccsensceessssceesnsecssantecseneees 40 2 8 3 Describing Foreign Key Information ssssscsssssscssseccssesscsssecssssseecssecesssscessuecessncessnscessuccessnsecesneceeeasecssauteessneess 41 2 8 4 Outputting Script Execution Results eesssccsseeccssseccsseesceueccsssceccssecessuccecsnecessuccssssecessucesesnsccssneceesnsecssnutecssaeees 42 2 8 5 Outputting an EXECUTIO
20. 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 gt 2ed or iSQL gt 2 ed SELECT FROM employees iSQL buf Note The command line parameter 2 which is the name of the file to be edited iSQL 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 h gt The history list currently in the isgl buffer 1 SELECT FROM customers 2 SELECT FROM employees iSQL User s Manual 34 2 6 File Management 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 comma
21. L Commands Category Type Commands Description Variable and Prepared SQL Variable dec laration VAR p1 INTEGER Declares the variable p1 as integer type statements VARIABLE p2 CHAR 10 Declares the variable p2 as CHAR type Assign values EXECUTE p1 100 Assigns the value 100 to variable p1 to variables EXEC p2 abc Assigns the text abc to variable p2 Variable dis PRINT VAR IABLE Shows the currently declared variables pay PRINT p1 Shows the type and value of variable p1 Prepared SQL PREPARE SQL statement Separates the processes of query opti statement mization and execution and executes execution the query as a prepared SQL statement In iSQL the default execution method for executing SQL statements is the Direct Execution method in which opti mization and execution are performed at once There is no difference between the two execution methods in iSQL in terms of the results obtained however prepared SQL statements can be used to bind variables to values and execute SQL statements based thereon Functions for Historylist dis HISTORY Shows a list of the commands currently user conve play H saved in the iSQL buffer nience Repeat execu Repeats execution of the command cur tion rently in the iSQL buffer The most recently executed command will be executed again 2 Executes the second command in a list output using the HISTORY
22. M 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 rlD password must be executed in order to connect to the server again DISCONNECT iSQL INSERT INTO systbl VALUES 1 A1 1 row inserted iSQL gt INSERT INTO systbl VALUES 2 A2 1 row inserted iSQL SELECT FROM systbl I1 I2 1 Al 2 A2 2 rows selected iSQL DISCONNECT Disconnect success iSQL gt INSERT INTO systbl VALUES 3 A3 ERR 91020 No Connection State iSQL SELECT FROM systbl ERR 91020 No Connection State iSQL gt CONNECT sys manager Connect success iSQL User s Manual 26 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 ser
23. N Planaren aria 43 2 8 6 Setting Result Output Orientation ou ceecsssscccsecccsssecssssecsssscesseecssnsecesssccesssesesssecsssuceecssecessscessnecsesssecssanteeesneess 45 2 9 Viewing iSQL Display Settings 2 10 Host Variables 2 10 1 Declaring a Host Variable 2 10 2 Assigning a Value to a Host Variable sessssssscsssssscssssecssneccsnsecssseecssusecsssessesnsecssusecssnsecessseessneeeeeasecssnuteessneess 48 2 10 3 Viewing Host Variables niecne i n ERA A En AN 49 2 11 Executing Prepared SQL Statements vsi miniin iini e EE E AEE E E EENT 50 2 11 1 Prepared SQL versus Dynamic SQL Statements esccsssscsssssecssseccesseecesseccssseccsssecessseceesueecssueceesneessnuteessneess 50 2 11 2 Prepared SQL State ments ssscissscssscascsscoseeessseassanictcanvavassnstoscddbecavasanntaasstansseesactebeddbedessanasssstadGiecnvasansesiceshaestbsaneneacidd 50 2 12 Creating Executing and Dropping Stored ProceCures sssscssssscsssssssssesecssseessssesessuecessscecsssecsssusecsssuscessseessnsccessseesssees 51 212A Creating Pracedures isise seiten retete brosse LARES R TREE ESE SEE ESEA E ETEA AEE EEES ahit 51 2 12 2 EXECULING Proc dures nsnsi e ett tete e este EEA ER AA tete 51 2 12 3 Dropping ProcedUr s m ttti CHR EST eee ERRORI eet ete TER ee ni 54 2 13 Creating Executing and Dropping FUNCTIONS sssssssssssccssesscsseccssescecssecsesusecssnsccesnsccssueccssusesesasecssnsccesssecssnseeesaseesenese 56 213 1 Creat
24. NFOS SYS SECURITY SYS SYNONYMS SYS TABLES SYS TABLE PARTITIONS SYS TBS USERS SYS TRIGGERS SYS TRIGGER DML TABLES _ SYS TRIGGER STRINGS SYS TRIGGER UPDATE COLUMNS _ SYS USERS SYS VIEWS SYS VIEW PARSE SYS VIEW RELATED SYS XA HEURISTIC TRANS ALTITBL SYSTBL 25 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 SYSTEM SYSTEM 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 TABLE TABLE Examples of iSQL in Use 2 3 Connecting and Disconnecting 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 SYNONY
25. NONYM 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 CREATE TABLE systbl il INTEGER i2 CHAR 5 Create success iSQL User s Manual 24 iSQL gt SELECT FROM tab USER NAME SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM TABLE NAME 2 3 Connecting and Disconnecting DMIN STO COLUMNS STO DATUMS STO ELLIPSOIDS STO GEOCCS STO GEOGCS _ STO PRIMEMS _ STO PROJCS STO PROJECTIONS STO SRS STO USER COLUMNS _ SYS COLUMNS _ SYS COMMENTS _ SYS CONSTRAINTS 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 I
26. 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 37 Examples of iSQL in Use 2 7 Formatting SELECT Query Results 1 Moon Chan seung CEO 01195662365 3002 M R 2 Davenport Susan designer 0113654540 1500 F 721219 18 NOV 2009 H 2 rows selected 2 7 5 SET PAGESIZE Specifies the number of resultant rows to display at one time iSQL gt SET PAGESIZE 2 gt Showresults in groups comprising two rows each iSQL gt select eno e firstname e lastname from employees ENO E FIRSTNAME E LASTNAME 1 Chan seung Moon 2 Susan Davenport ENO E FIRSTNAME E LASTNAME 3 Ken Kobain 4 Aaron Foster ENO E FIRSTNAME E LASTNAME 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected iSQL gt SET PAGESIZE 0 gt Show allofthe results on one page iSQL gt 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 6 SET HEADING Sets wheth
27. TEGER 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 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 NAME TYPE VALUE V1 INTEGER 3 V2 INTEGER 8 V3 INTEGER 103 2 12 3 Dropping Procedures The DROP command is used to drop delete procedures iSQL User s Manual 54 2 12 Creating Executing and Dropping Stored Procedures In the following example the procedure emp_proc is deleted iSQL gt DROP PROCEDURE emp proc Drop success 55 Examples of iSQL in Use 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 defined END Successful creation of the function can be confirmed by checking the sys procedures meta 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
28. 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 Altibase Client Query utility Release Version 6 1 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved Write Server Name default 127 0 0 1 23 Examples of iSQL in Use 2 3 Connecting and Disconnecting 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 alti1234 Create success iSQL gt CONNECT altiadmin alti1234 Connect success iSQL SHOW USER User ALTIADMIN iSQL CREATE TABLE altitbl il INTEGER i2 CHAR 5 Create success iSQL SELECT FROM tab TABLE NAME TYPE ALTITBL 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 SY
29. ZE 11 39 comment 13 Configuring iSQL 17 CONNECT 23 D DESC 9 28 DISCONNECT 8 26 Display Settings 46 drop procedure 54 E ED 10 33 EDIT 33 Edit query statements 10 Editing the LOGIN file 17 END 51 environment variables 14 EXEC 13 48 EXECUTE 13 48 Execution time 11 EXIT 8 EXPLAIN PLAN 43 F FEEDBACK ON OFF 37 FOREIGNKEYS ON OFF 41 Formatting SELECT result 36 fuction 56 function creation 56 function execution 56 61 G glogin sql 17 H HEADING ON OFF 38 HELP 13 58 HISTORY 13 58 host variable 13 48 l iSQL 2 iSQL Command 8 iSQL Command Line Options 5 ISQOL BUFFER SIZE 15 ISOL CONNECTION 15 ISQL EDITOR 16 L LINESIZE 36 LOAD 33 load 10 LOBOFFSET 11 37 LOBSIZE 11 36 log in 20 login sql 17 N national character process 60 NCHAR 60 NVARCHAR 60 P PAGESIZE 38 performance view 27 PLANCOMMIT ON OFF 30 PREPARE 13 PREPARE SQL statement 50 PRINT 13 49 procedure creation 51 procedure execution 51 Q QUERYLOGGING ON OFF 33 QUIT 8 R re execution 58 Repeat execution 13 S SAVE 10 32 seq 28 sequence 28 Sequence information 9 set foreignkeys 11 set heading 11 set linesize 11 set pagesize 11 set term 11 set timing 11 Setting Up iSQL 3 Shell command 13 shell commands 58 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 31 START 10 startup 8 21 Stop 8 stor
30. bove 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 set by SELECT e lastname FROM acters the user such as table names col employees 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 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 line Manuals Manuals are available at Altibase Customer Support http support 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
31. 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 file name sql file name sqI This indicates the embedded script to be executed If the extension is omitted iSOL assumes the default command file extension sgl 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 The command can be used for the following purposes If a script file that contains the text file_name sql is executed iSQL looks for the file speci fied by file name sql 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 ISQL raises an error If a user inputs file_name sq at the iSQL prompt the result will be the same as when using iSQL to execute file_name sq 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 reference
32. cess iSQL INSERT INTO t1 VALUES 1 1 row inserted iSQL UPDATE t1 SET il 2 1 row updated iSQL SELECT FROM t1 Ii 1 row selected iSQL DELETE FROM t1 1 row deleted iSQL 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 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 iSQL If you execute ed without parameters a temporary file named iSQL buf containing the most recently executed query statements will be created and the following screen will be visible To save 33 Examples of iSQL in Use 2 6 File Management space only a few of the blank lines that would be displayed on the screen are shown here iSQL gt SELECT sysdate FROM dual SYSDATE 01 JAN 2000 1 row selected iSQL gt ED SELECT sysdate FROM dual iSQL 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
33. 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 INDEX help 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 ISQL Ex Windows Server set ALTIBASE HOME C Program Files Altibase Altibase5 Server altibase 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 enviro
34. d 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 sgl iSQL 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 Of the commands currently in the iSQL buffer the SAVE command saves the most recently executed one ina file iSQL User s Manual 32 2 6 File Management This file will be created in the current directory iSQL SELECT FROM book iSQL gt SAVE book sql gt SELECT FROM book is saved in the file book sql Save completed 2 6 4 Loading SQL Statements This function loads the first command in the specified file to the last position in the iSQL buffer iSQL gt 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_query log iSQL CREATE TABLE t1 I1 INTEGER Create suc
35. e esaat i iisage 3 T 33iSQE Command LINE Optio Sirini REE E E E N e RE RR E tay 5 VA ASO COMMANA Sanie EAEN E EE EEEE EEN NENN 8 1 5 iSQbErivironment Variables tei rer Reiten ete arabe ee i E REE 14 1 5 ABIIBASE TIOME ne tetro es aee cen es NAN ee tees gere es 14 1 5 2 ALTIBASE PORTING sinet NEARER ANA Eee Ro EE 14 TSS AETIBASENEPS USE certe deste dite teet doa eben ee doe eret eres 14 1 5 4 ALTIBASE NLS NCHAR LITERAL REPLACE sssessssssssssscsssccsscsseccsscesssccsssscnscssnssenseecusecsscerssecssseecuseesseeesseess 15 15 5 ISQL CONNEGTION T eae 15 IRE Oe MP dad M 15 1 5 Z AETIBASE DATE FORMAT iiber cesccasveabvasscuscdadgovassensscasebovsanivenoaesdadbesssbeansceastuvasbusstodccbaaiors 15 1 5 8 ISOL EDITOR 1 5 9 ALTIBASE IPC FILEPATH 1 6 Personalizing iSQL T6 lgloginsqlusscssuetet nA M NET AE E LM M ACE 1 6 2 loginisql zt re detest lU s n se eee ert beet eese rt He berum 1 6 3 Editing the LOGIN file 2 Examples of ISQL in USE p 2 1 Logging In to iSQL 2 1 1 Login Restrictions sss 2 2 Starting Up and Shutting Down ALTIBASE HDB cssssssssscsssesccssseccsssecccssecessusecssssccesusccesusccssusecesnusecesuccessueeeesueessneeeesaee 21 2 21 Starting Up ALTIBASE HDB innin itea e EAE EAEE
36. e 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 channel 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 ISQL 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 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 iSQL 1 5 iSQL Environment Variables 1 5 8 ISQL_EDITOR This environment variable can be used to change the default editor Windows
37. e lastname sex FROM employees WHERE eno t1 ENO 3 E FIRSTNAME Ken E LASTNAME Kobain SEX M 1 row selected iSQL User s Manual 50 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 Successful creation of the procedures can be confirmed by checking the sys procedures meta table 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 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 51 Examples of iSQL in Use 2 12 Creating Execu
38. ed procedure 51 SYSDBA 23 T tab 9 27 Table list 9 Table structure 9 TERM ON OFF 42 TIMESCALE 40 TIMING ON OFF 40 Transaction mode 9 transaction mode 30 V VAR 48 VARIABLE 48 VERTICAL ON OFF 45 62
39. er to output the header with a SELECT result iSQL gt SET HEADING OFF gt Header is not displayed with the result iSQL gt select eno e firstname e_lastname from employees 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron Foster 5 Farhad Ghorbani 6 Ryu Momoi iSQL User s Manual 38 2 7 Formatting SELECT Query Results 20 rows selected iSQL gt SET HEADING ON gt Outputs header in result iSQL gt 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 iSQL gt CREATE TABLE location ID INTEGER NAME CHAR 20 ADDRESS VARCHAR 500 PHONE CHAR 20 Create success iSQL gt INSERT INTO location VALUES 1 ALTIBASE 10Fl 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
40. er 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 6 1 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 gt Parallel Loading SM Recovery Phase 2 Loading Database SM Recovery Phase 3 Skipping Recovery amp Starting Threads Refining Disk Table xu M Qa idretettnau ee Penna se PSUCCESS SM Rebuilding Indices Total Count 101 prede Vor ee sae one tao esse Sak alee 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 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 Man
41. ermines whether to output an exe put option tionplan tree EXPLAIN PLAN ON cution plan for a SELECT statement ALTER SESSION SET Default OFF EXPLAIN PLAN ONLY ALTER SESSION SET EXPLAIN PLAN OFF SELECT result SET VERTICAL ON Displays SELECT results vertically when output direc SET VERTICAL OFF set to ON tion Default OFF Show value of SHOW LINESIZE Displays the current LINESIZE value iSQL display 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 isgl 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 the 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 SHOW ALL Shows the set values of the display set tings for the current session iSQL User s Manual 12 1 4 iSQ
42. es 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 isq1 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 iSQL 1 4 iSQL Commands 1 4 isQL 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 lowing table Category Type Commands Description iSQL startup Startup isql 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 ALTIBASEHDB ALTIBASEHDB STARTUP Use
43. g Stored Procedures 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 zs pi 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 iSQL CREATE TABLE inoutTbl il INTEGER Create success iSQL INSERT INTO inoutTbl VALUES 1 1 row inserted iSQL gt 1 row inserted iSQL 1 row inserted iSQL SELECT FROM inoutTbl 3 rows selected iSQL CREATE OR REPLACE PROCEDURE inoutProc al IN OUT INTEGER AS 53 Examples of iSQL in Use 2 12 Creating Executing and Dropping Stored Procedures BEGIN SELECT COUNT INTO al FROM inoutTbl WHERE il al END Create success iSQL gt 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 IN
44. gn key information will not be output iSQL gt 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 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 gt select from t1 will still be displayed on the screen the OFF setting only prevents script execution results e g iSQL gt a sql from being displayed iSQL SET TERM OFF iSQL gt SET TIMING ON gt The execution time is not output to the screen iSQL gt schema sql gt The script execution results are not output iSQL gt select eno e firstname e lastname from employees The results of directly input queries will be output ENO E FIRSTNAME E LASTNAME iSQL User s Manual 42 2 8 Setting Output Options 1 Chan seung Moon 2 Susan Davenport 3 Ken Kobain 4 Aaron F
45. he 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 iSQL gt SELECT FROM system sys tables iSQL gt SELECT FROM tab This command is available in iSQL only USER NAME TABLE NAME TYPE 27 Examples of iSQL in Use 2 4 Retrieving Information Related to the Database and Database Objects 2 4 3 Viewing a Table Structure The following command is used to retrieve information on user created tables DESC table_name 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 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 2 4 4 Viewing Sequence Information The following commands are used to obtain information on all sequences that exist in the database SELECT FROM seq iSQL CONNECT sys manager Connect success iSQL CREATE USER userl IDENTIFIED BY userl Create success iSQL CONNECT useri userl Connect success iSQL CREATE SEQUENCE seq1 MAXVALUE 100 CYCLE
46. 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 ISQL 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 places a prompt to enter it will be raised UNIXDOMAIN FILEPATH filepath 5 Using iSQL 1 3 iSQL Command Line Options When a server and client connect using a Unix domain socket in a Unix environment ISQL_CONNECTION UNIX the connection will fail if the server and client have different val ues for ALTIBASE_HOME and also have different Unix domain 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 com
47. in a query result count SET FEED BACK n Format rows SET PAGESIZE 10 Sets how many records of a SELECT of SELECT query result are output at one time result When set to 0 all resultant records are output Default 0 Show hide SET HEADING ON Sets whether to output the header of a SELECT result SET HEADING OFF SELECT result header Default ON Set SELECT SET COLSIZE N Sets the number of characters to output result output when CHAR or VARCHAR type columns 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 for SET TIMESCALE NANSEC output Show hide SET FOREIGNKEYS ON Determines whether to include foreign foreign key SET FOREIGNKEYS OFF key information in the output when dis information 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 iSQL 1 4 iSQL Commands Category Type Commands Description Control out Output execu ALTER SESSION SET Det
48. 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 plan s WHERE clause conditions including FIXED KEY RANGE VARIBALE KEY RANGE and FILTER are classified and displayed in detail Therefore if the WHERE clause is complicated you can check which predicates 43 Examples of iSQL in Use 2 8 Setting Output Options 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 SQL 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 success iSQL ALTER SESSION SET EXPLAIN PLAN ON Alter success iSQL gt 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
49. ing FuhctiOhis 5 sceucecusscnsseanssstasetuccosnnscstescusxeheassseascaasveovsvanseseascbvedhsvaasedecevea avesnsassteadbidisaaust dtadedsitareesnseuataten 56 2 13 2 EXecuting FUretlOns e e ts tuber eere qe et a en e repere estas esee 56 2 13 3 Droppirig FUNCTIONS siet m ret rette sett esie genae o e eade eene eoe ees 57 2 14 Convenient User FUNCHONS sscsisscserescccossersatiececenssscuutessunsocedsisesrseagecuscesnsehovoiscseossscassisensnsccectusssnnuscscisescebsutentbssceensossstbocetenseeseite 58 DZ VAD MISTO e detesdatcsssasseatesduonsesavosebusclvvodusbossedcadicedisvausadsuactocoborensueteceiees 58 pupEdEesunree 58 2 14 3 Getting Help rre teret e ER tie loq eerie rte dul e bbs oret erit inet 58 2 15 Using National Character Sets 1 eritis tetris tere ieteretistret stet ce arte vbi ere eset totes etie deste icta etna ebbeendt deed 60 Preface About This Manual About This Manual This manual describes how to use iSQL to access a database Intended Audience The following ALTIBASE HDB users 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 u
50. ired 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 iSQL User s Manual 58 2 14 Convenient User Functions DROP LOBSIZE VAR IABLE EXECUTE MOVE TERM EXPLAINPLAN PAGESIZE VERTICAL iSQL HELP EXIT exit or quit exit iSQL 59 Examples of iSQL in Use 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 iSQL gt CREATE TABLE t1 c1 NVARCHAR 10 Create success iSQL INSERT INTO tl VALUES N AB 7 t 1 row inserted iSQL gt SELECT FROM t1 C1 AB 7H 1 row selected iSQL User s Manual 60 Index 1 43 10 31 10 32 13 A ALTIBASE_DATE_FORMAT 15 ALTIBASE_HOME 14 ALTIBASE_IPC_FILEPATH 16 ALTIBASE NLS NCHAR LITERAL REPLACE 15 60 ALTIBASE NLS USE 14 ALTIBASE PORT NO 14 AUTOCOMMIT ON OFF 30 C COLSI
51. le which must equal SALTIBASE HOME bin iSQL User s Manual 4 1 3 iSQL 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 0 1 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 1as the IPv4 address or 1 as the IPv6 address 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 in a Unix environment via IPC this option can be omitted If it is specified it is
52. le 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 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 op
53. munication 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 US7ASCII KO16KSC5601 MS949 BIG5 GB231280 UTF8 SHIFTJIS EUC P If omitted the environment variable ALTIBASE NLS USE or altibase properties will be used in descending order of preference and if it is still not specified the basic character set US7ASCII will be used NLS NCHAR LITERAL REPLACE 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 iSQL User s Manual 6 1 3 iSQL Command Line Options prefer_ipv6 This option determin
54. nd 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 30 rows selected 03 JAN 2011 O 35 Examples of iSQL in Use 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 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
55. nderstanding 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 6 is used as the database server Organization This manual is organized as follows Chapter1 Using iSQL This chapter presents an overview of iSQL 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 syntactic 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 itis 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
56. nment 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 BIG5 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 iSQL User s Manual 14 1 5 iSQL Environment Variables the default character set 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 found 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 th
57. of the manual that you are using Any comments that you have about the manual Your name address and phone number When you need an immediate assistance regarding technical issues please contact Altibase Cus tomer Support http support altibase com Thank you We appreciate your feedback and suggestions V Preface About This Manual iSQL User s Manual vi 1 Using iSQL Using iSQL 1 1 iSQL Overview 1 1 iSQL 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 you 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 SQL 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 fi
58. onvenient 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 gt HISTORY gt 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 most shell com mands from within iSQL iSQL ls al total 3417 rw r 1 wlgm1337 section 1198 Nov 1 13 30 aliases fwesesss 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 wlgm1337 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 des
59. oster 5 Farhad Ghorbani 6 Ryu Momoi 20 rows selected elapsed time 0 00 iSQL SET TERM ON gt Script execution results will be output iSQL gt schema sql iSQL 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 In iSQL an execution plan can be output to fine tune SOL 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 SELECT 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
60. 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 6 1 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 gt gt iSQL is 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 iSQL must first be launched with the sysdba option in the same way as when a database is created Note ALTIBASE HDB startup commands 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 ref
61. selected 39 Examples of iSQL in Use 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 SQL statement iSQL gt SET TIMING ON gt Output the execution time in the last line after the command is executed iSQL gt 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 elapsed time 0 01 E LASTNAME Moon Davenport Kobain Foster Ghorbani Momoi iSQL gt 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 iSQL gt SET TIMING ON iSQL gt SET TIMESCALE SEC iSQL gt select eno e firstname ENO E FIRSTNAME 1 Chan seung 2 Susan 3 Ken 4 Aaron 5 Farhad 6 Ryu 20 rows selected elapsed time 0 00 iSQL gt SET TIMESCALE MILSEC iSQL gt select eno e firstname ENO E FIRSTNAME 1 Chan seung iSQL User s Manual e lastname from employees E LASTNAME Moon Davenport Kobain Foster Ghorbani Momoi e lastname from employees E LASTNAME 2 Susan 3 Ken 4 Aaron 5 Farhad 6 Ryu 20 rows selected elapsed time 0 72 iSQL gt SET TIMESCALE MICSEC 2 8 Setting Output Options Davenport
62. ting and Dropping Stored Procedures iSQL VAR eno INTEGER iSQL gt VAR first name CHAR 20 iSQL gt VAR last name CHAR 20 iSQL VAR sex CHAR 1 iSQL EXECUTE eno 21 Execute success iSQL gt EXECUTE first name Joel Execute success iSQL gt EXECUTE last name Johnson Execute success iSQL EXECUTE sex M Execute success iSQL gt EXECUTE emp proc eno firstname lastname sex Execute success iSQL gt 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 gt 1 row inserted iSQL gt 1 row inserted iSQL gt 1 row inserted iSQL SELECT FROM outTbl H 5 rows selected iSQL gt CREATE OR REPLACE PROCEDURE outProc al 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 iSQL EXEC outProc t3 t4 Execute success iSQL PRINT t3 iSQL User s Manual 52 2 12 Creating Executing and Droppin
63. tions 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 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 fi
64. ual 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 2 3 Connecting and Disconnecting Connecting and Disconnecting 2 3 1 Connecting to a Database 2 3 2 3 1 2 3 1 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 1 userlD password The user ID and password with which to establish a connection to ALTIBASE HDB 2 NLS character set The NLS option specifies the character set iSQL CONNECT sys manager NLS US7ASCII Connect success 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 and 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
65. ver 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 V DISKTBL INFO PERFORMANCE VIEW V DISK BTREE HEADER PERFORMANCE VIEW V DISK RTREE HEADER PERFORMANCE VIEW VS EVENT NAME PERFORMANCE VIEW VSFILESTAT PERFORMANCE VIEW VSFLUSHER PERFORMANCE VIEW V FLUSHINFO PERFORMANCE VIEW For the complete list of the performance views provided with ALTIBASE HDB and the meanings of the columns please 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 t
66. which is executed after glogin sql If both the glogin sgl file and the login sq 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 sql file and executes the commands in it The login sql 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 sql AUTOCOMMIT ON SET HEADING OFF SELECT sysdate FROM dual vi login sql AUTOCOMMIT OFF SET HEADING ON DROP TABLE savept CREATE TABLE savept num INTEGER INSERT INTO savept VALUES 1 SAVEPOINT spl INSERT INTO savept VALUES 2 SELECT FROM savept ROLLBACK TO SAVEPOINT sp1 SELECT FROM savept COMMIT Altibase Client Query utility 17 Using iSQL 1 6 Personalizing iSQL Release Version 6 1 1 1 Copyright 2000 Altibase Corporation or its subsidiaries All Rights Reserved Write Server Name default 12

iSQL User`s Manual - ALTIBASE Customer Support

Contents

Download Pdf Manuals

Related Search

Related Contents