Home

Getting Started Guide - ALTIBASE Customer Support

1. n printf Scalar Host Variables n printf n check sqlca sqlcode if sqlca sqlcode SQL SUCCESS sqlca sqlerrd 2 holds the rows processed deleted count printf d rows deleted n n sqlca sqlerrd 2 else printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc disconnect EXEC SQL DISCONNECT check sqlca sqlcode if sqlca sqlcode SQL SUCCESS 9 printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc Getting Started Guide 70 9 5 Applications Written Using the C C Precompiler 9 5 4 2 Execution Result make delete delete DELETE 7 rows deleted 71 Developing ALTIBASE HDB Applications 9 5 Applications Written Using the C C Precompiler Getting Started Guide 72 Index A abort 9 ALTER REPLICATION RESET 38 ALTER REPLICATION STOP 38 ALTIBASE NLS NCHAR LITERAL REPLACE 29 ALTIBASE NLS USE 29 B backup database 48 C C C Precompiler 68 Character Set 22 Character Set Classification 25 Checking Whether Fail Over Has Succeeded 44 classpath 59 Client Character Set 26 constraints 16 CTF 42 D Database Character Set 25 Database Link 18 Database Trigger 18 H How to replicate 37 l Index 17 Introduction of Altibase Replication 36 J JDBC driver 59 L LOB 17 M makefile 53 Microsoft ODBC Manager 62 Multilingual Features 22 N
2. User password Port Number Memory allocation for the environment if SOLAllocEnv amp henv SQL ERROR printf AllocEnv error n exit 1 Memory allocation for the connection if SQLAllocConnect henv amp hdbc SQL ERROR printf AllocDbe error n SQLINTEGER errNo SQLSMALLINT msgLength SOLTCHAR errMsg MSG LEN L SUCCESS SQLError henv NULL NULL NULL amp errNo errMsg if MSG LEN SQI printf Error exit 1 amp msgLength 11d s n errNo errMsg Establish a connection sprintf char constr DSN s UID s PWD s CONNTYPE 1 PORT NO s DSN 65 Developing ALTIBASE HDB Applications 9 4 Applications Using ODBC with MS Windows USERNAME PASSWD PORTNO if SQLDriverConnect hdbc NULL constr SQL NTS NULL 0 NULL SQL DRIVER COMPLETE printf DBNAME s n DBNAME printf USERNAME s n USERNAME printf Connection error n SQLINTEGER errNo SQLSMALLINT msgLength SOLTCHAR errMsg MSG LEN if SQL SUCCESS SQLError henv hdbc NULL NULL amp errNo errMsg MSG LEN amp msgLength printf Error 11d s n errNo errMsg SQLFreeConnect hdbc SQLFreeEnv henv exit 1 printf connected n Memory allocation for the statement if SQLAllocStmt hdbc amp hstmt SQL ERROR printf AllocStmt erro
3. National Character Set 25 NLS 22 Oo object previliges 20 ODBC API 53 P Partitioned Index 19 Partitioned table 19 Q queue 16 R recovery 49 replication 18 S Sequences 17 Shutdown 8 Immediate 8 Normal 8 Startup Altibase 6 STF 42 Stored Procedures and Functions 17 Structure for Multilingual Support 22 system privileges 20 T table 16 tablespace 18 Type Sets 18 U Unicode 27 Unicode Database 27 Unicode Datatype 28 user 19 V views 17 Index
4. rc SOLPrepare bstmt SOLCHAR query SQL NTS if f s SQL_ERROR if SOL SUCCESS SQLError env con bstmt NULL amp errNo SOLCHAR errMsg MSG LEN amp msgLength fprintf stderr d s n errNo errMsg Executing a prepared SQL statement void execute rc SQLExecute bstmt it rc SOL ERROR if SOL SUCCESS SQLError env con bstmt NULL amp errNo SOLCHAR errMsg MSG LEN amp msgLength fprintf stderr d s n errNo errMsg Getting Started Guide 56 void sbigint bigint int cnt a int 1 long long i1 char tmp 100 int len SOL NTS prepare Binding parameters SOLBindParameter bstmt void amp il 0 amp len for i 0 i cnt i sprintf tmp Sd i il atol tmp execute Process COMMIT transaction re SQLTransact NULL con SQL COMMIT void slong integer int cnt int i int il char tmp 100 int len SOL NTS prepare SQLBindParameter bstmt void amp il 0 amp len for i 0 i cnt i sprintf tmp d il atoi tmp execute Process COMMIT transaction SQLTransact NULL con SQL COMMIT 1 SQL PARAM INPUT i void char char int cnt int i char i1 100 char tmp 100 1 SQL PARAM INPUT SQL C SLONG SQL INTEGER 9 2 Using ALTIBASE HDB ODBC APIs SOL C SBIGINT SQL BIGINT O0 O 0 0 int len SQL NTS prepare SQ
5. 6 3 1 Creating Replication Objects ssssccsssecssssssccsseccssssscsssesesssesessueeessnsecessseessuescesuseessuscsecsuecesusecssuecesueessneceesnseessaees 38 xs Starting Replicatinsscstiss ssssescsisescsassacecasasdsssazosasevacaseedcavestiesoiacsezincoaussssadesebeizasousssotadatecovisennseasacbisnsouistascebiazenxeasscalonds 38 6 3 3 Stopping Replication 55 2 d Ree TH i ie e re puts e RE edad 38 6 3 4 Resetting Replication 38 6 3 5 Dropping Tables 39 6 3 6 Adding Tables 39 6 3 7 Dropping a Replication Object 0 ssssssssssssccssessccssecssssssecssecsssseeccsnsseesuseccsusecssusecesssecssnsecesusecsssseesusecssneceseaseeseaees 39 6 4 Executing DDL Statements in a Replication Environment sssscsssssscsssescsssseccssescssseecsssccssnsecesnsecssnseesessneecesneecssaeeesees 40 VA Fail Over eene TIS 41 pA srnacze qe T 42 7 1 1 The Fail Over Concept 42 7 2 How to Use Fail Over eene 44 7 2 1 Setting the Fail Over Connection Property 44 7 2 2 Checking Whether Fail Over Has Succeeded eesssssssssssccsssecesssecesssecssnesesusecssnsececnsecessusecesuesesuseersneeeesnseessaees 44 7 2 3 How to Write a Fail Over Callback Function sessssssssscsssecssssecccssesessseccsnseceesscessssccessseceesssecesnseeccnseessnueeeesneesesese 44 CEET QIOETDIINI CITTA MM 47 8 T ALTIBASE HDB B
6. EAA E CHR ERR NAR 27 5 3 2 Unicode Encoding tart E EAE A A RRN 27 5 3 3 Storing Unicode CharactelS eniron tie ERR EEE e ETE Ee E USE Perd 27 5 3 4 A Unicode Databasen rere pt OR e eite AEREN ENE REEN 27 5 3 5 Uhicode Datatypes 5 espe netten iet e EU PRAE 28 5 4 Making Environment Settings for a Multilingual Database uu essssscsssessssseeccsssecesssesesnseesssueccesnscessusecssneecesnsecssneeeseee 29 5 4 1 Setting Environment Variables csssscsssssscssescccssecssssssccssecsssssecsssecsesuseccsseccesuseessueccssuseesesusecssneessnseessnecescnseessaees 29 ET AS Ce EES IEA A ETENE P 30 5 5 Considerations when Choosing a Database Character Set ssssscssssssccssssssssecssssssccssecsesuseessnscccsssecessueessuecesnseessneeesees 32 5 5 1 Scope of Usage eee e eet ie ce boe e eb o ite edet 32 5 5 2 ONE RestriCtlOht irruit eri eb e RR RESO SDR DARIB CENE ERREUR ERE ed PR regere etat e donee 32 5 5 3 Effects of Character Set Conversion 33 6 Database Replication 35 6 1 Introduction to Replication eee 36 6 2 How Databases Are Replicated in ALTIBASE HDB u cssssssssssssssscssssssssecssssssssssssssssccssscssnccsssesessccsusccsssccsnssessecssscesseesssessees 37 6 2 1 Establishing a Replication EnvironmMent ssscsssssscsssssssssescssseecsssscessuecesssscesssecsssuccessueseesusecssnsesesusecssneessnseessaees 37 6 3 How to Replicate a Database asunen aE T ieia E GRETE 38
7. Fail Over due to its ability to connect to another DBMS server enables service to be continuously provided without client applications ever being aware that a fault occurred One of the following two kinds of Fail Over is performed depending on the time point at which the fault is discovered CTF Connection Time Fail Over STF Service Time Fail Over With CTF the fault is discovered at the time of connection to the DBMS and connection is made to another DBMS at an available node instead of the DBMS in which the fault occurred so that service can be continuously provided With STF because a fault occurs while service is being provided after successful connection to the DBMS reconnection is made to a DBMS on another available node and session properties are restored so that the business logic of the user s application can continue to be used That is to say tasks currently being executed on the DBMS in which the fault occurred may need to be executed again When this kind of Fail Over is conducted in order to be confident in the results of a task the data bases on the DBMS in which the fault occurred and the DBMS that is available for service must be guaranteed to be in exactly the same state and contain exactly the same data In order to guarantee that the databases match ALTIBASE HDB copies the database using Off Line Replication In Off Line Replication the stand by server reads the logs from the active server so that it
8. LITERAL REPLACE If this is set to 1 TRUE the client does not convert strings that are preceded by the N character to the database character set Rather it sends them to the server without change and the server con verts them to the national character set The default is 0 FALSE Queries used by client applications are usually converted to the database character set and then sent to the server Under this scheme for a database that uses the US7ASCII character set data that fall out of the range of the US7ASCII character set can t be inserted into that database even if an NCHAR column is created for that purpose 29 Multilingual Features 5 4 Making Environment Settings for a Multilingual Database For example if the client character set is KO16KSC5601 and the database character set is US7ASCII data are converted from the client character set to the database character set when an INSERT state ment is executed In this case as can be seen in the following example because can t be converted to US7ASCII the replacement character is stored in the table iSQL gt CREATE TABLE t1 il NVARCHAR 10 Create success iSQL gt INSERT INTO t1 VALUES ok 1 row inserted iSQL gt SELECT FROM t1 Therefore a method of saving data that does not fall within the range of the database character set in an NCHAR column is needed In one such method seen below an environment variable setting is made and data are inserted using
9. Query try stmt execute DROP TABLE TESTOOL catch SQLException se try stmt execute CREATE TABLE TESTOO1 name varchar 20 age number 3 pstmt con prepareStatement INSERT INTO TESTOO1 VALUES pstmt setString 1 Hong Gil dong pstmt setInt 2 25 pstmt execute res stmt executeQuery SELECT FROM TESTOOL Output the received results on screen while res next System out println Name res getString 1 Age res getInt 2 Disconnected stmt close pstmt close con close Getting Started Guide 60 9 3 Applications Using JDBC catch Exception e e printStackTrace 9 3 3 2 Execution Results javac JdbcTest java JdbcTest 20300 lt port Name Hong Gil dong Age 25 61 Developing ALTIBASE HDB Applications 9 4 Applications Using ODBC with MS Windows 9 4 Applications Using ODBC with MS Windows This section describes how to use the Microsoft ODBC manager and the ODBC driver of ALTIBASE HDB to develop applications for Windows For more detailed information please refer to the ALTI BASE HDB API User s Manual 9 4 1 Installing the ODBC Driver 1 Toinstall the ODBC driver of ALTIBASE HDB in Windows visit the Altibase Technical Center at http atc altibase com and download and execute the ODBC driver file to install the ODBC driver dll file in the system folder 2 Under Administrative Too
10. Set Getting Started Guide 34 6 Database Replication Replication is the operation of copying and maintaining database objects in multiple databases that make up a distributed database system ALTIBASE HDB provides transaction log based replication so that when a database server experiences an unexpected outage service can continue to be pro vided without any interruption This chapter gives an overview of how to perform replication and broadly explains the related concepts This chapter contains the following sections 6 1 Introduction to Replication 6 2 How Databases Are Replicated in ALTIBASE HDB 6 3 How to Replicate a Database 6 4 Executing DDL Statements in a Replication Environment 35 Database Replication 6 1 Introduction to Replication 6 1 Introduction to Replication The Altibase database replication function maintains an up to date backup of the database on an active server and in the event that the server is unexpectedly terminated immediately resumes ser vice again from an identical database on an alternative server so as to realize an operating environ ment in which uninterruptible service is provided In this chapter an explanation will first be given of how databases are replicated in ALTIBASE HDB followed by instructions to help you replicate your databases properly Please refer to the ALTIBASE HDB Replication Manual for more detailed informa tion Getting Started Guide 36 6 2 How Databases Are Replicate
11. multithreaded application this option must be specified I include path Specify the directory paths for files included using APRE C C default current directory parse lt none partial full gt Control which non SQL code is parsed D lt define name gt Use to define a preprocessor symbol vV Output the version of APRE n Specify when CHAR variables are not null padded unsafe_null Specify to suppress errors when NULL values are fetched and indicator variables are not used align Specify when using alignment in AIX spill lt values gt Specify the register allocation spill area size keyword Display all reserved keywords debug macro symbol Use for debugging macro Display macro table symbol Display symbol table nchar_var lt variable_name_list gt Process the specified variables using the Altibase national character set nchar utf16 Set client nchar encoding to UTF 16 filename The name of a source file containing embedded SQL statements Its filename extension must be sc 9 5 3 Multi threaded Programming The ALTIBASE HDB C C precompiler supports multi threaded programming The following are some considerations to keep in mind when developing multi threaded applications that contain embedded SQL statements The user must indicate to the precompiler that the program is a multithreaded program Each thread must have its own connection The name of each c
12. must be stopped before this is done ALTER REPLICATION replication name STOP ALTER REPLICATION replication name ADD TABLE FROM user name table name TO user name table name 6 3 7 Dropping a Replication Object This is how a replication object is dropped If replication has been started it must first be stopped before the replication object can be dropped ALTER REPLICATION replication name STOP DROP REPLICATION replication name 39 Database Replication 6 4 Executing DDL Statements in a Replication Environment 6 4 Executing DDL Statements in a Replication Environ ment If the REPLICATION DDL ENABLE property is set to 1 on a replication server the following DDL statements can be executed ALTER TABLE table name ADD COLUMN ALTER TABLE table name DROP COLUMN ALTER TABLE table name ALTER COLUMN column name SET DEFAULT ALTER TABLE table name ALTER COLUMN column name DROP DEFAULT ALTER TABLE table name TRUNCATE PARTITIONTRUNCATE TABLE CREATE INDEX DROP INDEX Please refer to the Replication Manual for a complete list of the DDL statements that may be exe cuted as well as more information about restrictions pertaining to replication However depending on the task at hand DDL statements that are not normally permitted in a repli cation environment may be executed as long as replication is first paused or replication definitions are first dropped Additionally DDL statements cannot be executed on table objects th
13. printf SQLBindCol error n exit 1 if SQL ERROR SOLBindCol hstmt 2 SOL C SLONG amp age 0 amp len printf SQLBindCol error n exit 1 LN SOLFetch hstmt SOL SUCCESS Display the result while the result is available printf Name 5s Age 51d n name age Release all handles and terminate the connection SQLFreeStmt hstmt SQL DROP SQLDisconnect hdbc SQLFreeConnect hdbc SOLFreeEnv henv 9 4 2 2 Execution Result After an executable exe file is compiled using Visual C and executed the result is as follows test odbc exe Connected Name Hong Gil dong Age 25 Press any key to continue 67 Developing ALTIBASE HDB Applications 9 5 Applications Written Using the C C Precompiler 9 5 Applications Written Using the C C Precompiler ALTIBASE HDB C C precompiler converts source code that contains embedded SQL statements to run time library calls and creates a new source program that can be compiled in the host language This chapter describes how to develop applications using the ALTIBASE HDB C C precompiler For more information about the ALTIBASE HDB C C precompiler please refer to the ALTIBASE HDB Pre compiler User s Manual 9 5 1 Environment Settings The following environment settings must be made in order to compile and link files precompiled using the C C precompiler 9 5 1 1 HeaderFile The necessary header file
14. process of authoring client applications that access ALTIBASE HDB This chapter contains the following sections 9 1 Writing Client Application Programs 9 2 Using ALTIBASE HDB ODBC APIs 9 3 Applications Using JDBC 9 4 Applications Using ODBC with MS Windows 9 5 Applications Written Using the C C Precompiler 51 Developing ALTIBASE HDB Applications 9 1 Writing Client Application Programs 9 1 Writing Client Application Programs Developing ALTIBASE HDB applications using the ALTIBASE HDB application program interface API for JDBC ODBC the C C precompiler and the like is not much different than developing applica tions for use with other database products This chapter will briefly introduce the process of author ing client applications for use with ALTIBASE HDB For more detailed information about writing client applications please refer to the ODBC Reference the Precompiler User s Manual and the API User s Manual Getting Started Guide 52 9 2 Using ALTIBASE HDB ODBC APIs 9 2 Using ALTIBASE HDB ODBC APIs This chapter explains how to write client applications that use the ODBC driver of ALTIBASE HDB ODBC driver of ALTIBASE HDB can be used when using ALTIBASE HDB in a client server environment For more detailed information please refer to the ALTIBASE HDB ODBC Reference and API User s Man ual 9 2 1 Header Files and Libraries To develop a program using ODBC the following files which can be found in the inc
15. schema objects which belong to certain schema and non schema objects which do not have any relationship with particular schema In this chapter schema objects and non schema objects will be classified and the database objects in each category will be explained 4 1 1 Schema Objects 4 1 1 4 1 E 4 1 1 Schemas are logical collections of data and objects Relational schemas are grouped by database user ID and include tables views and other objects A user owns a schema and manages it using SQL statements The objects included in schemas are called schema objects ALTIBASE HDB supports the following schema objects 1 Tables Atable is the basic unit for storing data and is a set of records consisting of columns ALTIBASE HDB tables are divided into memory tables and disk tables depending on how the data are stored and are also divided based on who creates them into system tables which are created and managed by the system and user tables which are created by general users System tables are also called the data dictionary For detailed information about the data diction ary provided with ALTIBASE HDB as well as data dictionary management issues please refer to the Data Dictionary in Chapter 2 of the General Reference Additionally replication target tables and large volume tables also have special issues related to their management For more detailed information than can be found here the portion of the Administr
16. sets If the character set used by the database is different from the character set used by the client char acter set conversion occurs This can sometimes lead to data loss as shown in Figure 6 2 Figure 5 2 A Database and a Client with Different Character Sets American Database a asa US7ASCII Windows Client MS949 To prevent data loss caused by character set conversion it is recommended that the character set used on the database be a superset of the character set used by the client Thus to prevent data loss when character conversion is performed as seen in the picture above the character set used by the database should be MS949 or UTF8 which is a superset of MS949 5 1 3 3 The database and multiple clients use different character sets If multiple client applications use different character sets specifying that the server use a character set that encompasses all of the character sets used by the clients can prevent data loss attributable to character set conversion 23 Multilingual Features 5 1 Multilingual Support Overview Figure 5 3 A Database and Multiple Clients with Different Character Sets Character Set V Conversion Unix Client Unix Client SHIFT JIS GB231280 Character Set C_D m i LJ m Conversion Windows Client MS949 Windows Client UTF16 Figure 6 3 illustrates a system in which clients that are connected to the same database u
17. the NCHAR literal export ALTIBASE NLS NCHAR LITERAL REPLACE 1 iSQL CREATE TABLE t1 il NVARCHAR 10 Create success iSQL INSERT INTO ti VALUES NO 1 row inserted iSQL gt SELECT FROM t1 As can be seen above If ALTIBASE NLS NCHAR LITERAL REPLACE is set to 1 TRUE and data are inserted the client does not convert strings that are preceded by the N character to the database character set Instead these strings are sent to the server without change where they are converted to the national character set 5 4 2 Example The following explains the process of setting up an environment in which the default database char acter set is KSC5601 and UTF16 is used as the national character set 5 4 2 1 Database Creation iSQL sysdba CREATE DATABASE mydb INITSIZE 10m NOARCHIVELOG CHARACTER SET KSC5601 NATIONAL CHARACTER SET UTF16 DB Info Page Size 32768 Page Count 257 Total DB Size 8421376 DB File Size 1073741824 Creating MMDB FILES SUCCESS Creating Catalog Tables SUCCESS Creating DRDB FILES SUCCESS SM Rebuilding Indices Total Count 0 SUCCESS DB Writing Completed All Done Getting Started Guide 30 5 4 Making Environment Settings for a Multilingual Database Create success 5 4 2 2 Making Environment Settings on the Client To use KSC5601 on the client set the environment variable as follows export ALTIBASE NLS USE KSC5601 To use ASCII on the client set the environment v
18. to the following documents ALTIBASE HDB Installation Guide ALTIBASE HDB Administrator s Manual ALTIBASE HDB Replication Manual ALTIBASE HDB Precompiler User s Manual ALTIBASE HDB ODBC Reference ALTIBASE HDB Application Program Interface User s Manual ALTIBASE HDB iSQL User s Manual ALTIBASE HDB Utilities Manual ALTIBASE HDB Error Message Reference nr On line Manuals are available at Altibase Technical Center http atc altibase com V Preface About This Manual 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 Thank you We appreciate your feedback and suggestions Getting Started Guide vi 1 Installing ALTIBASE HDB This chapter provides only a brief overview of the installation procedure For complete instructions on how to install ALTIBASE HDB server it is strongly recommended that you consult the ALTIBASE HDB Installation Guide This chapter contains the follow
19. tools runtime environment APIs etc has been installed under the usr directory in a UNIX environment export JAVA HOME usr javal 2 export CLASSPATH SALTIBASE HOME lib Altibase jar CLASSPATH 9 3 3 Writing Applications This simple program code shows you how to connect to and disconnect from a database using JDBC APIs of ALTIBASE HDB 9 3 3 1 JDBC Code Sample JdbcTest java import java util Properties import java sql class JdbcTest 59 Developing ALTIBASE HDB Applications 9 3 Applications Using JDBC public static void main String args Properties props new Properties Connection con null Statement stmt null PreparedStatement pstmt null ResultSet res if args length 0 System err println Usage java JdbcTest port noWMn System exit 1 String port args 0 String url jdbc Altibase 127 0 0 1 port mydb String user SYS String passwd MANager String enc US7ASCII props put user user props put password passwd props put encoding enc Register Altibase JDBC driver try Class forName Altibase jdbc driver AltibaseDriver catch Exceptione System err println Can t register Altibase Driver return Allocate statement after connection try con DriverManager getConnection url props stmt con createStatement catch Exception e e printStackTrace
20. unistd h gt include lt sqlcli h gt void sbigint bigint int cnt void slong integer int cnt void char char int cnt void char number int cnt void double double int cnt void prepare void execute void usage long logMsec const char astr void conn char port char conntype define MSG LEN 1024 SQLHENV env Handle for the environment SQLHDBC con Handle for the connection SQLHSTMT hstmt Handle for a statement SQLHSTMT bstmt Handle for a statement int errNo short msgLength char errMsg MSG LEN SQLRETURN rc Main program int main int ac char av if ac lt 5 usage conn av 2 av 31 switch atoi av 1 case 1 logMsec BIGINT START TIME sbigint bigint atoi av 4 logMsec BIGINT END TIME break case 2 logMsec INTEGER START TIME slong integer atoi av 41 logMsec INTEGER END TIME break case 3 logMsec CHAR START TIME char char atoi av 41 logMsec CHAR END TIME break case 4 logMsec NUMBER START TIME char number atoi av 4 logMsec NUMBER END TIME break case 5 logMsec DOUBLE START TIME Getting Started Guide 54 9 2 Using ALTIBASE HDB ODBC APIs double double atoi av 41 logMsec DOUBLE END TIME break print the usage of the program void usage pr
21. 2 VARCHAR 10 2 go Msg 0 Level 16 State HYCOO Optional feature not implemented 0 rows affected 9 4 2 Writing Applications This code sample shows you how to connect to and disconnect from a database using the Windows ODBC driver of ALTIBASE HDB 9 4 2 1 Windows ODBC Code Sample test odbc cpp include include include include include lt windows h gt lt sql h gt lt sqlext h gt lt stdio h gt lt stdlib h gt Getting Started Guide 64 define SQL LEN 1000 define MSG LEN 1024 SOLHENV henv SOLHDBC hdbc SOLHSTMT hstmt SOLRETURN retcode 9 4 Applications Using ODBC with MS Windows void execute err SOLHSTMT stat char q printf Error sNn q SOLINTEGER errNo SOLSMALLINT msgLength SOLTCHAR errMsg MSG LEN if MSG LEN SQI L SUCCESS printf Error SQLFreeStmt stat if SOLError henv hdbc stat NULL amp errNo errMsg amp msgLength 11d s n errNo errMsg SQL DROP SQL ERROR SQLDisconnect hdbc printf disconnect error n SQLFreeConnect hdbc SOLFreeEnv henv exit 1 void main char DSN char query SQL LEN int age SQLCHAR constr 100 SQLINTEGER len DSN ALTIBASE DBNAME mydb USERNAME SYS PASSWD MANager PORTNO 20300 DBNAME USERNAME PASSWD PORTNO name 21 Domain Server Name Database name User name
22. Altibase Administration Getting Started Guide Release 5 5 1 January 17 2013 a ALTIBASE PERFORMANCE SOLUTIONS Altibase Administration Getting Started Guide Release 5 5 1 Copyright 2001 2009 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 edi esci E EI E EEE EEA E EE e dun eet ae Ua sees ese E e SEIE tees iro cut ese ov E E acea na Se c EUR QE i About This Marn al tee aec c d eee RE cas e Se OR UE RR e e De T purs ii PUGIGNCO AERE T T ms ii Software Environment setti treo e aei pede eee inesset ted ec ares alise epe N ee nae ii Organization 5e EON PEU E eiae eade dax DD UM E DEDE ii DOCUMENTATION CONVENTIONS erit raihi eee AR toes eee o eee EAREN iii Related Docuinents etel ve deciso A RETE N E Ce DIR OE D e RR V Onslin Mantals tbe atte tete lee codon sut Cobb od top p Eee ode V Altibase Welcomes Your Co
23. C3 B6 00 F6 F6 00 NLS National Language Support This allows the database to be used in a particular language environment If NLS is appropri ately set the user can read and write data to and from the database using the character set specified by the user s application 5 1 3 Multilingual Support Multi language support consists of performing conversion between the character sets used by the database and the client application respectively From the aspect of multilingual support the server and client can have one of four relationships therebetween which are explained individually below The database and the client use the same character set The database and the client use different character sets The database and multiple clients use different character sets Unicode data types are supported by both the database and the client 5 1 3 1 The database and the client use the same character set The character set used by the database is the same as the character set used by the client Getting Started Guide 22 5 1 Multilingual Support Overview Figure 5 1 A Database and a Client with the Same Character Set Korean Database KO16KSC5601 Unix Client Windows Client KO16KSC5601 KO16KSC5601 If both the database and the client use KSC5601 as the character set as shown in Figure 6 1 charac ter set conversion need not be performed 5 1 3 2 The database and the client use different character
24. LBindParameter bstmt 1 SOL PARAM INPUT SQL C CHAR SQL CHAR sizeof il 1 0 void il sizeof il amp len for i 0 i lt cnt i sprintf tmp Sd i strcpy il tmp execute COMMIT a transaction SOLTransact NULL con SQL COMMIT void char number int cnt int i 57 Developing ALTIBASE HDB Applications 9 2 Using ALTIBASE HDB ODBC APIs char i1 100 char tmp 100 int len SQL NTS prepare SQLBindParameter bstmt sizeof il 1 0 void for i 0 i lt cnt i sprintf tmp Sd i strcpy il tmp execute COMMIT a transaction SQLTransact NULL con 1 SQL PARAM INPUT SOL C CHAR SQL NUMERIC il sizeof i1 amp len SOL COMMIT void double double int cnt a int 1 double i1 char tmp 100 int len SQL NTS prepare SQLBindParameter bstmt void amp il 0 amp len for i 0 i lt cnt i sprintf tmp d il atof tmp execute COMMIT a transaction SQLTransact NULL con X 9 2 4 2 Execution Results test 1 20300 1 100 START TIME END TIME make test BIGINT BIGINT 16 Getting Started Guide 1 SQL PARAM INPUT SQL C DOUBLE SQL COMMIT 16 43 48 43 49 58 SOL DOUBLE 0 0 9 3 Applications Using JDBC 9 3 Applications Using JDBC The following describes how to create a client application that uses the JDBC dr
25. N Name Altibase hast name or IP 127 0 0 1 Port defaul 20300 20300 User Sys Password res Database mydb NLS USE US7ASCI omen Once ALTIBASE HDB server settings have been made clicking on the system DSN tab and then on the ALTIBASE HDB ODBC driver as shown below allows you to change the name of the data base file to Altibase or some other name as desired 63 Developing ALTIBASE HDB Applications 9 4 Applications Using ODBC with MS Windows User Data Sources ee D ne Add Altibase Altibase_ODBC_cm330 ume cmos An ODBC User date source stores information about how to connect to the indicated data provider A User data source is only visible to you and can only be used on the current machine 6 After making ODBC settings as shown above the iodbc exe program which can be found in the ODBC installation folder e g C Program Files Altibase Altibase5_ODBC can be used to check whether ODBC settings of ALTIBASE HDB were made correctly after which a SOL state ment can be executed as a test to confirm once more that the ODBC Data Source settings of ALTIBASE HDB are complete and correct Ex C InstallDir gt IODBC exe S altibase H 127 0 0 1 U SYS P MANager iodbc Ver 0 11 Beta 2 Copyright 1995 FFE Software Inc This is free software and you are welcome to redistribute it under cer tain conditions type help for details 1 CREATE TABLE t1 il INTEGER PRIMARY KEY i
26. acktp Policy 2 trente Herde RP eee abet pee deer eed 48 8 2 ALTIBASE HDB Recovery Policy 49 9 Developing ALTIBASE HDB Applications 9 1 Writing Client Application Programs 52 9 2 Using ALTIBASE HDB ODBC APS a eiA EOR EON AORE 53 9 2 1 Header Files and Libraries osessssssssssssessssessescsssesssssssseesessonsesssssonsessesssssessessnsessesssnsessrssnsessesonsesseetonsessessensesearsenees 53 9 2 2 Ma kefil M 53 9 2 3 Multi threaded Programming ee teeseeeen teen tttentttttnntttnt ttt tttta ttt tttsostttt attt tosta sttoosss 53 9 2 4 Writing Applications 53 9 3 Applications Using JDBC 59 9 3 1 JDBC Driver 59 973 2D GLASSPAT EH oS 59 9 323 Writihd Applications os RISO e RR SEU E te t cco dele ees 59 9 4 Applications Using ODBC with MS Windows sssssssssccsssssccssecsssssscsssecessutecssnsecssusecssuecssusccessscessueeecesucsssusecssneeeesnseeseneeeeses 62 9 4 T Installing the ODBC DriVer cte RE REER 62 94 2 Writing Applications 2 ete tete e AERA e te ETRAS 64 9 5 Applications Written Using the C C Precompiler sssscssssssccssessesssecsssescesnseccsusecssnesessseessnscecssnsccesuteccsueesesnsecseneeesees 68 9 5 1 Environment Settings um 9 52 PLE CONTIG ERR 9 5 3 Multi threaded Programmming ssssscsssssccssssccssessesssecessseccssecssssesccssecessuecssnsecessseesssus
27. ariable as follows export ALTIBASE NLS USE ASCII 5 4 2 3 Setting Other Environment Variables and Properties Set the following environment variable and property appropriately for the usage environment Environment Variable ALTIBASE NLS NCHAR LITERAL REPLACE Property NLS COMP or NLS NCHAR CONV EXCP 31 Multilingual Features 5 5 Considerations when Choosing a Database Character Set 5 5 Considerations when Choosing a Database Charac ter Set When choosing a database character set please give careful consideration to any issues that could arise including those associated with identifiers as well as data loss and inadvertent conversion that may occur when data are converted 5 5 1 Scope of Usage 5 5 1 1 Identifiers Column names schema objects and comments are saved in the database using the database char acter set however other identifiers can only be stored using the US7ASCII character set The following table shows which character sets can be used for each kind of identifier Table 5 1 Character Sets that Can Be Used for each Identifier Identifier Name Available Character Set Column Name Database Character Set Schema Object Database Character Set Annotation Database Character Set Database Link Name Database Character Set Database Name US7ASCII File Name Such as Data and Log Files US7ASCII Directory Name US7ASCII Keyword US7ASCII Tablespace Name US7ASCII 5 5 1 2 S
28. at are replica tion targets To execute such a DDL statement replication must first be stopped on both servers and the table in question must be dropped from the replication definition after which DDL statements can be exe cuted on each server Finally once the DDL statements have been executed successfully the rele vant tables are re registered in the replication definitions and replication is resumed Getting Started Guide 40 7 Fail Over Fail Over functionality is provided so that when a fault occurs in a database system that is actively providing service it can be overcome and service can continue to be provided as though no fault had occurred This chapter will explain how to use the Fail Over functionality that is provided with ALTIBASE HDB This chapter contains the following sections 7 1 About Fail Over 7 2 How to Use Fail Over 41 Fail Over 7 1 About Fail Over 7 1 About Fail Over Fail Over means the ability to overcome a fault that occurs in a database system that is actively providing service and continue to provide service despite the fault The kinds of faults that can occur include the case in which the hardware on which the DBMS is operating experiences a fault the case in which the network via which the server is connected experiences an outage and the case in which the DBMS software encounters an error and shuts down abnormally When one of these kinds of fault occurs regardless of which kind it is
29. at the level of the entire database are called non schema objects ALTIBASE HDB provides the following non schema objects 4 1 2 1 Replication Replication can be thought of as a kind of object that allows information to be automatically sent from a local server to a remote server so that the data in tables on multiple servers can be kept con sistent For more details on replication please refer to the Replication Manual 4 1 2 2 Tablespaces The tablespace is the largest logical data storage unit A database comprises and manages multiple tablespaces ALTIBASE HDB automatically creates a system tablespace when a database is created and the user creates user defined tablespaces as desired ALTIBASE HDB supports 3 types of tablespaces disk tablespace which resides on disk memory tablespace which resides in memory and volatile tablespace which also resides in memory but dif fers from memory tablespace in that logging is not performed For more information on tablespace management please refer to the portion of the Administrator s Manual entitled Managing Tablespace Getting Started Guide 18 4 1 Database Objects An Overview When a table is partitioned the table is called a partitioned table A partitioned table is a large table that has been divided into several partitions based on the partitioning conditions range list and hash For more information please refer to the portion of the Administrators Manual pertain
30. ate a database If you created a database cre ation script using the package installer you can execute this script and a database will be created for you automatically Getting Started Guide 2 1 2 Special Considerations 1 2 Special Considerations 1 2 1 Considerations when Installing ALTIBASE HDB in UNIX 1 2 1 1 Configuring Kernel Parameters If system kernel parameters were not set during installation set the parameters manually 1 2 2 Considerations when Installing ALTIBASE HDB in Windows 1 2 2 1 You Must Be Logged On as an Administrator The ALTIBASE HDB Package Installer registers the ODBC driver of ALTIBASE HBD registers the ALTI BASE HDB server as a Windows service and starts the service Only administrators can register the ODBC driver register the process as a Windows service and start the service 3 Installing ALTIBASE HDB 1 2 Special Considerations Getting Started Guide 2 Startup and Shutdown This chapter explains how to start up and shut down ALTIBASE HDB after it has been properly installed This chapter contains the following sections 2 1 Startup 2 2 Shutting Down ALTIBASE HDB 5 Startup and Shutdown 2 1 Startup 2 1 Startup The ALTIBASE HDB server can be started up in one of two ways either using a server script or when a DBMS administrator logs in using the sys account accesses the DBMS in sysdba administrator mode and explicitly starts ALTIBASE HDB To explain the ALTIBASE HDB server
31. ators Manual pertaining to database objects describes in great detail how to manage them 2 Queue Tables ALTIBASE HDB supports asynchronous data communication between user applications and the database using message queuing functionality Queue tables are manipulated using DML and DDL statements just like other database tables For more information on the concepts and functionality of queue tables please refer to the portion of the Administrator s Manual pertaining to database objects 3 Constraints Constraints serve to restrict data manipulation in order to maintain data consistency when data are inserted into tables or when existing data in tables are changed Depending on the target of the constraints constraints are divided into column constraints and table constraints ALTIBASE HDB supports the following kinds of constraints NULL NOT NULL Constraints Unique Key Constraints Getting Started Guide 16 4 1 Database Objects An Overview Primary Key Constraints Foreign Key Constraints TIMESTAMP Constraints For more detailed information than can be found here please refer to the portion of the Administra tor s Manual pertaining to constraints LOB Large Object is a data type that is intended for storing large data such as documents or image files The maximum size of a LOB data object that can be saved is 2 GB minus one byte A table can have more than one LOB type data column The LOB data type can be divided in
32. bout This Manual Chapter7 Fail Over Chapter8 Backup and Recovery Chapter9 Developing ALTIBASE HDB Applications This chapter introduces each API Application Programming Interface ODBC JDBC C C Precompiler etc ntions 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 syntax diagrams sample code conventions This manual describes command syntax using diagrams composed of the following elements Elements semantics The command starts The syntax element which is not a com plete command starts with an arrow Reserved word The command continues to the next line The syntax element which is not a complete command terminates with this sym bol The command continues from the previous line The syntax element which is a complete command starts with this sym bol End of the statement Mandatory SELECT iii Preface About This Manual Elements semantics Optional NOT Mandatory field with optional items Only one field must be provided ADD DROP Optional field with optional item ASC DESC Optional
33. can harmonize its database with that on the active server Because one of the characteristics of replication is that the databases might not be in exactly the same state we recommend that the Fail Over Callback function be used to confirm that the data bases match Fail Over Callback is explained in detail in the next chapter ALTIBASE HDB5 Fail Over settings include a Fail Over property which is set to true to specify that Fail Over is to be executed Additionally the Fail Over Callback function can be used to check whether the databases match before Fail Over is executed The three kinds of Fail Over related tasks that must be executed by the client application are sum marized as follows the Fail Over connection property must be set to true the Fail Over Callback function must be registered additional tasks may be necessary depending on the result of callback Getting Started Guide 42 7 1 About Fail Over For more detailed information please refer to the Replication Manual 43 Fail Over 7 2 How to Use Fail Over 7 2 How to Use Fail Over 7 2 1 Setting the Fail Over Connection Property If the Fail Over connection property has been set when ALTIBASE HDB senses the occurrence of a fault it conducts internal Fail Over tasks as specified by the connection property There are two ways to show the property values by viewing the Connection Property string used by the API s Connect function by viewing the altibase pro
34. command which is the command that is used to shut down the server has three mutually exclusive options The way in which the server is shut down is different for each option The shutdown command of ALTIBASE HDB can only be given from the account used to install ALTIBASE HDB normal In order for the server to shut down normally the server must first wait until all clients have discon nected If server shutdown is initiated while some tasks are still underway the server waits for pro cesses to terminate in the following order first threads that sense client server communication are shut down followed by service threads the Data Storage Manager and finally the ALTIBASE HDB server process At this time the ALTIBASE HDB server has been completely shut down When the server is shut down in this way the following message is output iSQL sysdba shutdown normal Ok Shutdown Proceeding TRANSITION TO PHASE Shutdown Altibase RP Finalization PASS shutdown normal success 2 2 1 immediate When the immediate shutdown option is used before the server is shut down connected sessions are forcibly disconnected and then current transactions are forcibly rolled back The output for immediate shutdown is as follows iSQL sysdba shutdown immediate Ok Shutdown Proceeding TRANSITION TO PHASE Shutdown Altibase RP Finalization PASS shutdown immediate success The server can also be forcibly shut down using a server
35. conducted during periods of high use excessive logs can be generated The online backup procedure is as follows 1 Back up memory tablespaces and log anchor files 2 Back up disk tablespaces 3 Back up archive log files Getting Started Guide 48 8 2 ALTIBASE HDB Recovery Policy 8 2 ALTIBASE HDB Recovery Policy ALTIBASE HDB provides the following recovery methods logical backup recovery restart recovery media recovery Logical backup recovery means recovery from backup text files using the iLoader utility Restart recovery is a simple recovery method that is automatically initiated when an ALTIBASE HDB server is restarted Restart recovery is conducted after a database server has been abnormally terminated Media recovery uses database files log anchor files and archive log files that were created based on the backup policy to recover database files either to the most recent backup or to a specific moment in time point in time recovery Depending on the kind of media error and the recovery proce dure the database may be recovered using either complete recovery or incomplete recovery as appropriate For more information about backup and recovery please refer to the portion of the Administrator s Manual pertaining to backup and recovery 49 Backup and Recovery 8 2 ALTIBASE HDB Recovery Policy Getting Started Guide 50 9 Developing ALTIBASE HDB Applications This chapter will provide an overview of the
36. d in ALTIBASE HDB 6 2 How Databases Are Replicated in ALTIBASE HDB 6 2 1 Establishing a Replication Environment In order to make use of the replication functionality first the tables containing the data to be repli cated are defined and a schema comprising the remote server to be replicated the replication name the primary key the port number etc is set and a replication connection is established between the local server and the remote server Then replication of the data on the remote server can begin Bidirectional replication in which replication is also initiated on the remote server is also possible 37 Database Replication 6 3 How to Replicate a Database 6 3 How to Replicate a Database When database replication is performed in ALTIBASE HDB the local server sends database changes that have occurred in the system to the remote server and the remote server makes corresponding changes in its own database The local server and the remote server start threads dedicated to the task of replication These threads are distinct from the database service threads The replication Sender thread on the local server transmits the database changes and the replication Receiver thread on the remote server receives the information about the data changes and implements them in its database Additionally the replication Sender and Receiver threads automatically detect whether the corre sponding servers were shut down normally or abnormal
37. dure using its name For more information please refer to the portions of the Administrators Manual pertaining to stored procedures and stored functions Additionally for detailed information about the special features of stored procedures as provided 17 DB Objects and Privileges 4 1 Database Objects An Overview with ALTIBASE HDB as well as how to manage them please refer to the Stored Procedures Manual 4 1 1 9 Type Sets A type set is a database object which allows a number of user defined data types that are used by stored procedures and stored functions to be grouped together in one place for convenient man agement For more about type sets please refer to the ALTIBASE HDB Stored Procedures Manual 4 1 1 10 Database Triggers Atrigger is a kind of stored procedure that is called automatically by the system when data in a table are inserted deleted or updated thus allowing a specific set of tasks to be automatically performed By defining constraints and triggers for tables the user can maintain data consistency For more information please refer to the portion of the Administrator s Manual pertaining to trig gers 4 1 1 11 Database Link Database Link allows database servers that are geographically distributed but connected via a net work to be linked together to combine the data thereon and output a single result 4 1 2 Non schema Objects Objects that do not belong to any particular schema and are thus managed
38. e and know how to start it up and shut it down you will of course want to execute some SQL statements starting with DDL statements for creating other users and database objects such as tables and then DML statements to populate the tables with data and perform similar actions For a description of the various kinds of available database objects please refer to Chapter4 DB Objects and Privileges ALTIBASE HDB Server supports the complete ANSI 92 SQL standard and additionally provides some extended functionality For a complete description of all SOL statements that are supported by ALTI BASE HDB Server please refer to the SOL Reference Getting Started Guide 12 3 2 How to Execute SQL Statements 3 2 How to Execute SQL Statements 3 2 1 Executing SQL Statements using the iSQL Utility The most straightforward way to execute SQL statements is to use the iSQL utility which is a com mand line interface that ships with ALTIBASE HDB For complete information on how to use the iSQL utility please refer to the iSQL User s Manual 3 2 2 Executing SQL Statements using the AdminCenter Utility SQL statements can also be executed using the AdminCenter utility which can be downloaded from altibase com For complete information on the functionality offered by the AdminCenter utility please refer to the AdminCenter User s Manual 3 2 3 Executing SQL Statements using Custom Authored Client Applica tions Additionally SOL statements can also be ex
39. ecssueeeeenseessasesesuseessnseeesnseessnees 69 9 5 4 Writing Applications sacre retirees ter te FEE Ha Ue T i 69 Preface About This Manual About This Manual This manual explains the concepts components and basic use of ALTIBASE HDB Audience This manual has been prepared for the following users of ALTIBASE HDB database managers performance managers database users 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 will be used as the database server Organization This manual is organized as follows Chapter1 Installing ALTIBASE HDB Chapter2 Startup and Shutdown Chapter3 Working with ALTIBASE HDB Chapter4 DB Objects and Privileges Chapter5 Multilingual Features This chapter describes the structure of the multilingual features and related environment set tings and notes some relevant considerations Chapter6 Database Replication This chapter describes the replication environment and how to perform replication Getting Started Guide ii A
40. ecuted using custom authored client applications The point of entry for complete information on authoring applications for use with ALTIBASE HDB is Chapter 9 of this manual XREF 13 Working with ALTIBASE HDB 3 3 The Sample Schema 3 3 The Sample Schema The ALTIBASE HDB server package includes a script which when executed creates a sample schema that includes a series of database tables and other objects and populates the tables with sample data Simply execute the script which is located at ALTIBASE HOME sample APRE schema schema sql and the sample schema is created for you Many of the examples in the product documentation are based on this sample schema If you wish to follow along with the examples or simply need some sample data on which to execute SQL state ments for practice it is recommended that you execute the above script to create the sample schema For a complete description of all of the objects and data in the sample schema please refer to the ALTIBASE HDB General Reference Getting Started Guide 14 4 DB Objects and Privileges In this chapter schema objects and non schema objects will be classified and the database objects in each category will be explained This chapter contains the following sections 4 1 Database Objects An Overview 4 2 Privileges 15 DB Objects and Privileges 4 1 Database Objects An Overview 4 1 Database Objects An Overview Database objects can be divided into
41. en a user is created using the CREATE USER statement can be revoked if necessary However the privileges of the SYSTEM and SYS users cannot be revoked Getting Started Guide 20 9 Multilingual Features This chapter describes the multilingual structure of ALTIBASE HDB as well as environment settings and other points to consider when using ALTIBASE HDB in a multilingual environment This chapter contains the following sections 5 1 Multilingual Support Overview 5 2 Character Set Classification for Multilingual Support 5 3 Using Unicode 5 4 Making Environment Settings for a Multilingual Database 5 5 Considerations when Choosing a Database Character Set 21 Multilingual Features 5 1 Multilingual Support Overview 5 1 Multilingual Support Overview 5 1 1 Concept Multilingual support means that the database is capable of storing and processing character sets used in different countries In other words a single database can handle clients that use different languages such as for example Korean Chinese and Japanese 5 1 2 Related Terminology Character Set A character set is a particular group of characters that are associated with respective numeric values The following table shows how an individual character is associated with a different numeric value depending on whether it is encoded using the UTF 8 UTF 16 BE or UTF 16 LE character set Character UTF 8 UTF 16 BE UTF 16 LE 41 0041 41 00
42. g for such incidents This chapter contains the following sections 8 1 ALTIBASE HDB Backup Policy 8 2 ALTIBASE HDB Recovery Policy 47 Backup and Recovery 8 1 ALTIBASE HDB Backup Policy 8 1 ALTIBASE HDB Backup Policy ALTIBASE HDB normally supports both logical backup and physical backup Logical backup is for cre ating copies of database objects and saving data in text file format The ALTIBASE HDB utilities aex port and iLoader can be used to perform logical backup Logical backup does not support recovery up until the point in time at which the error occurred The logical backup procedure is as follows 1 Exportthe database objects to text files using aexport or iLoader while ALTIBASE HDB is run ning 2 Store the backup text files to disk or tape Physical backup means copying physical data files to disk or tape ALTIBASE HDB supports both offline cold backup and online hot backup Performing offline backup comprises shutting down the database normally and backing up all files required by the database The offline backup procedure is as follows 1 Shutdown the database normally 2 Back up the log anchor file online log files and database files Online backup can be used when the database is in archive log mode Online backup can be con ducted while the database is providing service but it is recommended that online backup be per formed during periods when low usage is anticipated If online backup is
43. haracter set is used to store NCHAR and NVARCHAR data types and can be used to store text in Unicode 5 2 2 1 How to Specify the National Character Set The national character set of the database is specified using the CREATE DATABASE statement when a database is created 25 Multilingual Features 5 2 Character Set Classification for Multilingual Support 5 2 2 2 Supported National Character Sets ALTIBASE HDB supports the following two national character sets UTF8 UTF16 Big Endian 5 2 3 Client Character Set The client character set is the character set used to display data to the client Data sent from the server are converted to and displayed in the character set specified by respec tive clients 5 2 3 1 How to Specify the Client Character Set The client character set can be specified using ALTIBASE NLS USE on the client 5 2 3 2 Supported Client Character Sets US7ASCII Default KO16KSC5601 MS949 BIG5 GB231280 UTF8 UTF16 Big Endian SHIFTJIS EUCJP Getting Started Guide 26 5 3 Using Unicode 5 3 Using Unicode in a Multilingual Database 5 3 1 The Unicode Concept Unicode is an internationally encoded character set that enables information to be stored in any lan guage using a single character set Unicode provides a unique code value for every character regardless of the platform program or language Therefore Unicode is useful when it is necessary to save data in
44. ing sections 1 1 The Installation Process 1 2 Special Considerations 1 Installing ALTIBASE HDB 1 1 The Installation Process 1 1 The Installation Process 1 1 1 Download the Package Installer Visit altibase com and download the installer that is suitable for your operating system 1 1 2 Run the Package Installer When the package installer is executed the following tasks are performed automatically 1 1 2 1 Create the ALTIBASE HOME Directory This is the directory that contains the executable files configuration files and by default the data and log files The package installer gives you the opportunity to specify this directory 1 1 2 2 Set the ALTIBASE HDB Property Values The package installer suggests default property settings and gives you the opportunity to change them as desired These properties can be changed after installation is complete by modifying the altibase properties file which is located at ALTIBASE HOME conf altibase properties 1 1 2 3 Create a Database Creation Script The package installer can be used to create a script that you can later execute to create a database automatically which greatly simplifies the database creation task 1 1 3 Provide a License Key There are two ways to provide a license key either by entering the license key directly or by select ing a file containing license key information 1 1 4 Create a Database After installation is complete it will be necessary to cre
45. ing the NCHAR or NVARCHAR data type 5 3 5 1 Supported Character Sets UTF8 UTF16 5 3 5 2 When are Unicode data types necessary When columns for storing multilingual data are needed in a non Unicode database When most of the data to be saved in a given column are in the same language but some of the data to be saved in that column are in some other language s When saving data from a wchar_t 16 bit buffer of a Windows client application Getting Started Guide 28 5 4 Making Environment Settings for a Multilingual Database 5 4 Making Environment Settings for a Multilingual Database In order to establish a database that supports multiple languages settings must be made as follows 1 When creating a database consider which character set is the most widely used by clients and specify that character set for the server 2 SetNLS appropriately for the client character set 3 Setother environment variables and properties 5 4 1 Setting Environment Variables Set the following environment variables on the clients ALTIBASE NLS USE ALTIBASE NLS NCHAR LITERAL REPLACE 5 4 1 1 ALTIBASE NLS USE Any of the following character sets may be used on the clients Data sent from the server are con verted to and displayed in the character set specified by each of the clients US7ASCII Default KO16KSC5601 MS949 BIG5 GB231280 UTF8 SHIFTJIS EUCJP 5 4 1 2 ALTIBASE NLS NCHAR
46. ing to parti tioned tables Indexes are categorized as partitioned indexes or non partitioned indexes based on whether or not they are partitioned Non partitioned indexes are indexes that have not been partitioned while par titioned indexes like partitioned tables are large indexes that have been divided into several indexes based on some partitioning conditions For more information please refer to the portion of the Administrators Manual pertaining to parti tioned indexes A user is the owner of a schema and is associated with a user account that is required in order to access a database Users are created by the system and are categorized as either system users who manage the entire system or general users Users must have been granted appropriate privileges in order to access and manage the database For more information please refer to Chapter 5 Objects and Privileges in the Administrator s Man ual 19 DB Objects and Privileges 4 2 Privileges 4 2 Privileges Users must have appropriate privileges in order to access database objects and data This chapter describes the privileges pertaining to users and objects and how to manage them 4 2 1 Managing Privileges ALTIBASE HDB supports the management of user privileges but does not yet support so called roles which are aggregations of privileges The types of privileges that ALTIBASE HDB supports are as follows 4 2 1 1 System Privileges System access pri
47. intf Usage test program no port no conntype lt cnt gt n printf tprogram no 1 gt Nt SBIGINT BIGINT An printf tprogram_no 2 gt Nt SLONG INTEGER n printf tprogram_no gt Nt CHAR CHAR n printf tprogram_no gt t CHAR NUMERIC n printf tprogram_no gt Nt DOUBLE DOUBLE n exit 1 Check the starting time and the ending time of the program long logMsec const char astr Ul B w struct timeval tv struct tm ctm gettimeofday amp tv NULL ctm localtime amp tv tv sec fprintf stderr s 02d 02d ctm gt tm_sec return tv tv_usec Altibase connection statement void conn char port char conntype char connStr 200 char query 200 oo 02d n astr ctm tm hour ctm tm min if SOL ERROR SQLAllocEnv amp env fprintf stderr SQLAllocEnv error n Memory allocation for the envi ronment return if SQL ERROR SQLAllocConnect env amp con Memory allocation for a con nection fprintf stderr SQLAllocConnect error n SQLFreeEnv env return sprintf char connStr DSN 127 0 0 1 PORT_NO s UID SYS PWD MANager CONN TYPE s port conntype Connection creation if SOL ERROR SQLDriverConnect con NULL SOLCHAR connStr SQL NTS NULL 0 NULL SQL DRIVER NOPROMPT if SOL SUCCESS SQLError env con NULL NULL amp errNo SOLCHAR e
48. is ulpLibInterface h and is located in SALTIBASE HOME include To compile precompiled programs the following compiler option must be used I SALTIBASE HOME include 9 5 1 2 Library The necessary library files are libapre a and libodbccli a which are located in the SALTIBASE HOME lib directory To link the precompiled application program the following options must be used L SALTIBASE HOME lib lapre lodbccli lpthread 9 5 2 Precompiling The C C precompiler converts code that was written in C or C and contains embedded SQL statements to a C or C application The input file which contains the code written in C or C has the sc filename extension and the output file has the c or cpp filename extension While the default filename extension of the output file is c the user can set this freely as desired 9 5 2 1 Precompiling embedded C C programs The following example shows the use of various options when precompiling APRE Altibase Precompiler C C Precompiler HELP Screen Usage apre lt options gt filename h Display this help information t lt c cpp gt Specify the file extension for the output file c File extension is c default Getting Started Guide 68 9 5 Applications Written Using the C C Precompiler cpp File extension is cpp o output path Specify the directory path for the output file default current directory mt When precompiling a
49. iver of ALTIBASE HDB For more information about the JDBC driver of ALTIBASE HDB please refer to the ALTIBASE HDB API User s Manual 9 3 1 JDBC Driver By default ALTIBASE HDB provides the JDBC driver file altibase jar in the SALTIBASE_HOME lib directory To connect to an ALTIBASE HDB server the driver is first loaded and then an attempt is made to access the URL The form of URL that is supported by the JDBC driver of ALTIBASE HDB is as follows jdbc Altibase hostname portnum databasename Step 1 In order to load the JDBC driver it must be registered in the program using code like the fol lowing Class forName Altibase jdbc driver AltibaseDriver Step 2 Usually the URL is provided and an attempt to connect to the URL is made as follows In this example the ID used to log in to ALTIBASE HDB is SYS and the password is manager String url jdbc Altibase 127 0 0 1 20300 mydb Connection con DriverManager getConnection url SYS manager At present the JDBC 2 0 API and a portion of the Standard Extension API are supported 9 3 2 CLASSPATH To run an java application of ALTIBASE HDB the Altibase jar file must be included in CLASSPATH As an example to use Altibase jar CLASSPATH is set as follows in the default login shell files e g bashrc profile etc which can be found in the user s home directory This example uses a Bourne shell environment and assumes that java 1 2 which provides a compiler
50. lback Function The way to write a Fail Over Callback function differs depending on the form of the client applica tion but the basic structure is usually the same and comprises the following defining Fail Over related data structures writing the body of Fail Over Callback functions that will be called when Fail Over related events occur checking whether Fail Over has succeeded The Fail Over event is either defined in the data structure definition or included in a defined inter face header file The callback function body must include Fail Over events that is tasks that must be conducted when Fail Over starts or finishes for example code that checks whether databases match If Fail Over completes successfully and the callback function also executes successfully with Getting Started Guide 44 7 2 How to Use Fail Over the result that the service that was suspended by the fault can be used again then Fail Over is con sidered to have been successful For specific information on how to write such functions in various client application environments please refer to the Replication Manual 45 Fail Over 7 2 How to Use Fail Over Getting Started Guide 46 8 Backup and Recovery ALTIBASE HDB data can be lost due to unforeseen circumstances such as in the event of system fail ure or the loss of or damage to a disk or data file This chapter describes ALTIBASE HDB s backup and recovery features for use in preparin
51. ls in the Control Panel click on Data Sources ODBC A window like the following should appear To add the system DSN click on the User DSN or System DSN tab and then click on the Add button on the right ODBC Data Source Administrator User DSN System DSN Fie DSN Drivers Tracing Connection Pooling About User Data Sources amen ete S Remove An ODBC User data source stores information about how to connect to the indicated data provider A User data source is only visible to you and can only be used on the current machine ECTERETNITNI 3 In the list of drivers select the ALTIBASE HDB driver altibase ODBC CM330 and then click the Finish button Getting Started Guide 62 9 4 Applications Using ODBC with MS Windows Create New Data Source Altibase ODBC cm33u Sitibases_cm Driver da Microsoft para arquivos texto txt csv Driver do Microsoft Access m Driver do Microsoft dBase dbf Driver do Microsoft Exceli xls Driver do Microsoft Paradox db Driver para o Microsoft Visual FoxPro ccess Driver mdb ha Teathae 6 edb After adding the ODBC data source of ALTIBASE HDB the dialog box shown below should open If it does not click the Configure button in the ODBC Data Source Administrator win dow and then specify the connection information host port user id and password to config ure the data source Altibase3 Config Windows DS
52. lude and lib subdirectories of the ALTIBASE HDB home directory are needed SALTIBASE HOME include sqlcli h SALTIBASE_HOME lib libodbccli a 9 2 2 Makefile In order to compile the ODBC source code in your program the following must be included in the Makefile include ALTIBASE HOME install altibase env mk This file includes links to library paths and libraries that are needed at compile time as well as instructions for making object files Please refer to the sample Makefile in SALTIBASE HOME sam ple SQLCLI Makefile Sample Code include ALTIBASE HOME install altibase env mk SRCS OBJS SRCS cpp OBJEXT BINS demo_ex1 all BINS demo exi demo exl OBJEXT LD LFLAGS LDOUT demo_ex1 BINEXT demo exi OBJEXT S LIBOPT odbccli LIBAFT LIBOPT alticoreS LIBAFT S LIBS 9 2 3 Multi threaded Programming When developing a multi threaded program please keep the following in mind Each thread must have an environment handle a connection handle etc separately allocated thereto 9 2 4 Writing Applications The following code shows how to connect to and disconnect from an ALTIBASE HDB server in a pro gram that uses ODBC 53 Developing ALTIBASE HDB Applications 9 2 Using ALTIBASE HDB ODBC APIs 9 2 4 1 ODBC Code Sample test cpp include lt stdio h gt include lt string h gt include lt stdlib h gt include lt time h gt include lt sys time h gt include lt
53. ly and take appropriate action 6 3 1 Creating Replication Objects Replication to synchronize a local server with a remote server is defined as follows CREATE LAZY EAGER REPLICATION replication name AS MASTER AS SLAVE WITH remote host ip remote host port no FROM user name table name TO user name table name FROM user name table name TO user name table name FROM user name table name TO user name table name 6 3 2 Starting Replication Replication is started in one of these ways ALTER REPLICATION replication name SYNC PARALLEL parallel factor ALTER REPLICATION replication name SYNC ONLY PARALLEL parallel factor ALTER REPLICATION replication name START ALTER REPLICATION replication name QUICKSTART 6 3 3 Stopping Replication Replication is stopped in this way ALTER REPLICATION replication name STOP 6 3 4 Resetting Replication This is how replication information is reset Replication must be stopped before this is done Getting Started Guide 38 6 3 How to Replicate a Database ALTER REPLICATION replication name RESET 6 3 5 Dropping Tables This is how tables are dropped deregistered from a replication object Replication must be stopped before this is done ALTER REPLICATION replication name STOP ALTER REPLICATION replication name DROP TABLE FROM user name table name TO user name table name 6 3 6 Adding Tables This is how tables are added to registered with a replication object Replication
54. mments vi 1 Installing ALTIBASE HDB 1 1 1 The Installation Process ss 2 1 1 1 Download the Package Installer n2 1 1 2 Run the Package Installer sad 1 1 3 Provide a License Key 2 1 1 4 Create a Database 2 1 2 Speclal Gonsiderations zo tepore de e EB ere RENTE RAS EPI ERR Pe DRE NO 3 1 2 1 Considerations when Installing ALTIBASE HDB in UNIX ssesssssscssssscccsseccssseecssusscesssecssssecessuecessueessneecesneees 3 1 2 2 Considerations when Installing ALTIBASE HDB in WindOWs eese teen tients ntttnnns 3 2 Startup Entente T 5 Pn Ec M an esas 6 2 2 Shutting Down ALTIBASE HDD ione ttl b aerei etr ER Ee peteret beendet patate ENA a 8 peABunii cc 8 DZ Qi DAD ONE E 9 3 Working with ALTIBASE HDB ccscsssssscsssssccscsssscsscssscesssescscssescscssccssccsecssessecssscssssseccssssssseccsssseccscssescssssseoese 11 3 1 Supported SOL Statements sesscssssarsacccsscrvsrseseoresscedequsatsrevacosscrsansabvonesccd esevaiavanncouisesuvasbasescdcheschdnavonscbadsdbvanvo eosasedaperdbavnnnecsdedbons 12 3 2 OW to Exec te SOL Statements 2 eee retient tti ne ito cei e Rede ree ede eee et debeo edt cente 13 3 2 1 Executing SQL Statements using the iSQL Utility eese teen ttnntttnnncttnntttttnnct
55. multiple fields are allowed The comma must be in front of every repetition ASC il DESC a ae The code examples explain SQL stored procedures iSQL and other command line syntax The following table describes the printing conventions used in the code examples Getting Started Guide Rules Semantics Example Indicates optional fields VARCHAR size FIXED VARIABLE Indicates mandatory fields Indi ENABLE DISABLE COMPILE cates to make sure to select at least one Argument indicating optional or ENABLE DISABLE COMPILE mandatory fields ENABLE DISABLE COMPILE iv About This Manual Rules Semantics Example Repetition of the previous argu ment Omit the example codes iSQL select e lastname from employees E LASTNAME Moon Davenport Kobain 20 rows selected Other symbols Other symbols EXEC pl 1 acc NUMBER 11 2 Italicized words Indicates variable or value that must be provided by user SELECT FROM table name CONNECT userID password tax Lower case Program elements provided bythe SELECT e lastname FROM employ words user such as table names column ees names file names etc Upper case Elements provided by the system DESC SYSTEM SYS INDICES words or keyword appeared in the syn D yocuments For more detailed information please refer
56. on port 20300 CM Listener started UNIX RP Initialization PASS STARTUP Process SUCCESS Command execute success The following shows how to start up a database using a server script server start Altibase Client Query utility Release Version 6 1 1 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved Getting Started Guide 6 2 1 Startup ISQOL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 ERR 910FB Connected to idle instance 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 SM Refine Memory Table SM Rebuilding Indices Total Count 100 2 22 cm er RU be ees 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 7 Startup and Shutdown 2 2 Shutting Down ALTIBASE HDB 2 2 Shutting Down ALTIBASE HDB The ALTIBASE HDB server can be shut down either using a server script or when a user with DBMS administrator privileges uses iSQL to shut down ALTIBASE HDB as the sys user with the sysdba parameter The shutdown
57. onnection must be unique in the program Each connection name within a program must be unique Embedded SQL statements must also indicate the name of the connection they will use 9 5 4 Writing Applications 9 5 4 1 Apre C C Code Sample BORK RRR KR RR RR RK KR Sk kk ke e RR RR KR Sk kk ke RRR RK k RR RR RR RR KR RRR eek SAMPLE DELETE CODE delete sc 1 Using scalar host variables 2 Reference array host variables arraysl sc HR KK RK RR RR RR KR RR k k k k k RR k k k RR k k k k RR k k k kk k k x 69 Developing ALTIBASE HDB Applications 9 5 Applications Written Using the C C Precompiler int main declare host variables EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 char conn opt 1024 scalar type int s eno short s dno EXEC SQL END DECLARE SECTION printf lt DELETE gt n name password options strepy usr SYS strcpy pwd MANAGER strcpy conn opt DSN 127 0 0 1 CONNTYPE 1 PORT NO 20300 Altibase server connection EXEC SQL CONNECT usr IDENTIFIED BY pwd USING conn_opt check sqlca sqlcode if sqlca sqlcode SQL SUCCESS printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc exit 1 use scalar host variables S eno 5 S dno 1000 EXEC SQL DELETE FROM EMPLOYEES WHERE ENO gt s eno AND DNO gt s_dno AND EMP JOB LIKE P printf
58. perties files the altibase cli ini filethe odbc ini file WinODBC For more details about how to set this property please refer to the Replication Manual 7 2 2 Checking Whether Fail Over Has Succeeded In the case of CTF Connection Time Fail Over attempting to connect to the database makes it immediately obvious whether Fail Over was successful In contrast in the case of STF Service Time Fail Over whether Fail Over was successful is determined by checking for exceptions and errors For example in the case of JDBC when a SQLException is caught the value of SQLStates status is checked using the SOLException s getSOLState method and if this value is found to be ES 08FO01 then it is known that Fail Over succeeded In the case of CLI and ODBC if the result of a SOLPrepare SOLExecute or SQLFetch statement or the like is an error rather than SOL SUCCESS a statement handle is returned in response to SQLGetDi agRec and if the result of the call to SOLGetDiagRec is ALTIBASE FAILOVER SUCCESS then it is con firmed that STF Service Time Fail Over succeeded When using embedded SQL after executing an EXEC SQL statement the value of the return code sqica sqlcode is checked and if it is ALTIBASE FAILOVER SUCCESS rather than SOL SUCCESS then it is confirmed that STF Service Time Fail Over succeeded For more detailed information on these settings please refer to the Replication Manual 7 2 3 How to Write a Fail Over Cal
59. r n SQLDisconnect hdbc SQLFreeConnect hdbc SQLFreeEnv henv exit 1 Query execution sprintf query DROP TABLE T1 SOLExecDirect hstmt SQLTCHAR query SQL NTS sprintf query CREATE TABLE T1 name varchar 20 age number 3 if SOL ERROR SQLExecDirect hstmt SQLTCHAR query SOL NTS execute err hstmt query Prepare for the statement and bind the variable sprintf query INSERT INTO T1 VALUES if SOL ERROR SQLPrepare hstmt SOLTCHAR query SOL NTS execute err hstmt query if SOL ERROR SQLBindParameter hstmt 1 SOL PARAM INPUT SQL C CHAR SOL CHAR 0 O name 19 amp len printf SQLBindParameter error gt 9 WMn query exit 1 if SOL ERROR SQLBindParameter hstmt 2 SOL PARAM INPUT SQL C SLONG SOL NUMERIC 0 0 amp age 0 amp len printf SQLBindParameter error gt 9 WMn query exit 1 Execute the prepared statement sprintf name Hong Gil dong age 25 if SQL ERROR SQLExecute hstmt execute err hstmt query Getting Started Guide 66 9 4 Applications Using ODBC with MS Windows sprintf query SELECT FROM T1 if SQL ERROR SQLExecDirect hstmt SQLTCHAR query SQL NTS execute err hstmt query Save the result of Select in the variable if SQL ERROR SOLBindCol hstmt 1 SQL C CHAR name 21 amp len
60. rrMsg MSG LEN amp msgLength fprintf stderr rCM d s n errNo errMsg SQLFreeConnect con SQLFreeEnv env return 55 Developing ALTIBASE HDB Applications 9 2 Using ALTIBASE HDB ODBC APIs Not automatically reflected upon execution of each SQL statement SOLSetConnectAttr con SOL ATTR AUTOCOMMIT void SQL AUTOCOMMIT OFF 0 if r s SQL_ERROR if SOL SUCCESS SQLError env con NULL NULL amp errNo SQLCHAR errMsg MSG LEN amp msgLength fprintf stderr d s n errNo errMsg hstmt bstmt SQL NULL HSTMT SQLAllocStmt con amp hstmt SQLAllocStmt con amp bstmt Executing the DDL statement directly and output the message in the defined format into a file strcpy query drop table t1 rc SQLExecDirect hstmt SOLCHAR query SOL NTS If Gre SOL ERROR if SOL SUCCESS SQLError env con hstmt NULL amp errNo SOLCHAR errMsg MSG LEN amp msgLength fprintf stderr d s n errNo errMsg strcpy query create table t1 il number 6 rc SQLExecDirect hstmt SOLCHAR query SOL NTS if rc SQL ERROR if SOL SUCCESS SQLError env con hstmt NULL amp errNo SOLCHAR errMsg MSG LEN amp msgLength fprintf stderr d s n errNo errMsg Preparing an SQL statement void prepare char query 100 strcpy query insert into t1 values
61. s 13 3 2 2 Executing SQL Statements using the AdminCenter Utility seen ttenntttnnnnes 13 3 2 3 Executing SQL Statements using Custom Authored Client Applications sss 13 3 3 The Sample Schema ttti tette teet eee diede ai deu 14 4 DB Objects and Privileges 15 4 1 Database Objects An Overview 16 4 1 1 Schema Objects 16 4 1 2 Non schema Objects 18 4 2 Privileges ss 20 4 2 1 Managing Privileges we 20 4 2 2 Granting Privileges 20 4 2 3 Revoking Privileges 1 20 ERIPUIT SU M IN 21 5 1 Multilingual Support OVetrvieW xcii tt RARE ERN HERRERA SEEN EIER ESTE ERRANT REA EE 22 ER Edad ecc 22 5 1 2 Related Terminology e RU ied eU 22 5 1 3 M ltilinig al Support iet e eee ee A eU eet e R ean 22 5 2 Character Set Classification for Multilingual Support eesssessccssescesssscsssescecuseccsuscecsseceesusecssseccecssecessuecssueeessasecssneeesees 25 5 2 1 Database Character Set eitis etii dels ee steel Coi te ep ete 25 5 2 2 National Character Set sssssissassesssssccssssasssescsnascesssesasessscissssssersiassssdesniseenssnassctbcvnisesntsnassdiestonnsaedscsaieeassssenaassetavadasantonisc 25 5 2 3 Client Character Setiana aan a i EEr EE ESRA EE EEE Sa 26 GB USING Uni I Saeni SE NEe aaoi seeds 27 5 341 TNE Vnicod CONCEAL annei
62. script command server stop Altibase Client Query utility Release Version 6 1 1 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISQOL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 Ok Shutdown Proceeding TRANSITION TO PHASE Shutdown Altibase RP Finalization PASS shutdown immediate success Getting Started Guide 8 2 2 Shutting Down ALTIBASE HDB 2 abort This option forces termination of an ALTIBASE HDB server with the system command kill 9 When ALTIBASE HDB is shut down in this way the database may not be closed properly and thus database recovery will need to be performed when ALTIBASE HDB is restarted When the abort option is used the following is output isQL sysdba shutdown abort iSQL sysdba The server can also be forcibly shut down using a server script command server kill Altibase Client Query utility Release Version 6 1 1 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISOL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 9 Startup and Shutdown 2 2 Shutting Down ALTIBASE HDB Getting Started Guide 10 3 Working with ALTIBASE HDB This chapter contains the following sections 3 1 Supported SQL Statements 3 2 How to Execute SQL Statements 3 3 The Sample Schema 11 Working with ALTIBASE HDB 3 1 Supported SQL Statements 3 1 Supported SQL Statements Now that you have created a databas
63. se Japa nese Chinese and Korean In order to prevent data loss caused by character set conversion the data base character set should be set to UTF8 which supports the languages in use on all of these clients 5 1 3 4 Unicode data types are supported by both the database and the client If the database and client application use NCHAR or NVARCHAR both of which support Unicode data multiple languages are supported regardless of which character set each of them is using Getting Started Guide 24 5 2 Character Set Classification for Multilingual Support 5 2 Character Set Classification for Multilingual Sup port 5 2 1 Database Character Set The database character set is the character set with which data are saved in the database Any character set that encompasses completely includes the ASCII character set as per the SOL standard can be used as the database character set Thus UTF 16 can t be used as the database char acter set because UTF 16 doesn t encompass the ASCII character set 5 2 1 1 How to Specify the Database Character Set The database character set can be specified using the CREATE DATABASE statement when a data base is created 5 2 1 2 Supported Database Character Sets ALTIBASE HDB supports the use of the following 8 database character sets all of which support ASCII US7ASCII KO16KSC5601 MS949 BIG5 GB231280 UTF8 SHIFTJIS EUCJP 5 2 2 National Character Set The national c
64. several languages simultaneously 5 3 2 Unicode Encoding Unicode encoding is the way Unicode data are represented so that they can be stored on a com puter Unicode which means a system or collection of characters requires an encoding system such as UTF 8 or UTF 16 in order to encode data 5 3 3 Storing Unicode Characters Unicode characters can be stored in a database in two ways When the database is created it can be designated as one in which character data are stored as Unicode data NCHAR or NVARCHAR columns can be used to store Unicode characters Please note that if the database character set is UTF8 and the national character set is UTF16 Uni code characters may be stored in two different ways in the same database 5 3 4 A Unicode Database If the database character set is set to UTF8 at the time that the database is created to thus create a Unicode database then Unicode data can be saved in CHAR and VARCHAR type columns 5 3 4 1 Supported Character Set UTF8 5 3 4 2 When is a Unicode database needed When SQL statements or stored procedures include Unicode data When you are not sure whether multilingual data will be inserted into the database or what column they will be inserted into 27 Multilingual Features 5 3 Using Unicode 5 3 5 Unicode Datatypes Even if a character set other than UTF8 was specified at the time a database was created it is still possible to store Unicode characters us
65. startup process first the properties are read and system mem ory is checked and then the system environment of ALTIBASE HBD is initialized system data are ini tialized signal handling is initialized the memory used for database space is initialized the Query Processor is initialized and finally the threads are initialized This completes ALTIBASE HDB server startup The command to start up ALTIBASE HDB can only be given using the Unix account with which ALTIBASE HDB was installed The following shows how to start up a database using the iSQL utility which ships with ALTIBASE HDB For more information on the iSQL utility please refer to the ALTIBASE HDB iSQL User s Manual isgl 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 iSQL sysdba gt iSQL sysdba startup 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 SM Refine Memory Table SUCCESS SM Rebuilding Indices Total Count 100 SUCCESS TRANSITION TO PHASE SERVICE CM Listener started TCP
66. to the Binary Large Object BLOB which can hold binary data such as images and video files and the Character Large Object CLOB which can hold string data For more information on LOB management please refer to the LOB data type portion of the General Reference Indexes are elements that allow records within tables to be accessed more quickly Indexes are cre ated within tables and increase the performance with which DML statements are processed For more information please refer to the portion of the Administrator s Manual dealing with indexes A view does not contain actual data but is a logical table created on the basis of one or more tables or views At this time updatable views and materialized views are not supported by ALTIBASE HDB For more information please refer to the portion of the ALTIBASE HDB Administrator s Manual per taining to views ALTIBASE HDB provides sequences for generating unique keys For more information please refer to the portion of the ALTIBASE HDB Administrator s Manual pertaining to sequences es and Functi A stored procedure is a kind of database object in which all kinds of operations such as SQL state ments stream control statements assignment statements and error handling routines are pro grammatically combined into a single module that is permanently stored in the database after which all of the operations stored therein can be executed merely by calling the stored proce
67. tored SQL Statements SQL statements that are stored in meta tables such as those belonging to triggers and stored proce dures are stored using the database character set 5 5 2 One Restriction 5 5 2 1 Replication Replication cannot be performed between two databases that use different character sets Getting Started Guide 32 5 5 Considerations when Choosing a Database Character Set 5 5 3 Effects of Character Set Conversion If the database character set is different from the character set used by the client character set con version will occur The possibility of data loss is not the only negative consequence performance may also suffer 5 5 3 1 Data Loss When data are converted from a character set that can represent a wide range of characters to another with a narrower range data loss can result Any characters that cannot be represented using the destination character set will be converted to a replacement character In US7ASCII the replacement character is the question mark 5 5 3 2 Conversion Overhead If all clients use the same character set and the same character set is specified when a database is created no character conversion will occur However if different character sets are in use on each client and the database character set is a superset of the character sets used by the clients character conversion will occur 33 Multilingual Features 5 5 Considerations when Choosing a Database Character
68. vileges are usually managed by the DBA Database Administrator Users with sys tem privileges can execute individual tasks and manage all objects in all schemas 4 2 1 2 Object Privileges The object owner manages object privileges which are the right to access and manipulate objects For a complete list of the privileges supported in ALTIBASE HDB please refer to the portion of the ALTIBASE HDB Administrator s Manual dealing with privilege management and for more detailed information about statements for granting and revoking privileges please refer to the SQL Reference 4 2 2 Granting Privileges When a database is in an initialized state immediately after it has been created the SYSTEM and SYS users already exist have all DBA privileges and can grant privileges to normal users When a normal user is created using the CREATE USER statement the system automatically grants the user the minimum privileges necessary to access the database such as the authority to execute CREATE SESSION and CREATE TABLE statements Other privileges must be explicitly granted by the DBA For more detailed information on how to grant and manage privileges please refer to the relevant portion of the Administrators Manual and to the SQL Reference 4 2 3 Revoking Privileges Privileges granted to users other than the SYSTEM and SYS users can be explicitly revoked using the REVOKE statement Even the privileges that are automatically granted by the system wh

Getting Started Guide - ALTIBASE Customer Support

Contents

Download Pdf Manuals

Related Search

Related Contents