DB2 IBM Informix. IBM Informix Guide to SQL: Tutorial. Version 10.0/8.5 G

IBM

Informix

Guide

to

SQL:

Tutorial

Version10.0/8.5

IBM

Informix

Guide

to

SQL:

Tutorial

Version10.0/8.5

Note!

Beforeusingthisinformationandtheproductitsupports,readtheinformationin“Notices”onpageB-1.

FirstEdition(December2004)

ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotectedby copyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany statementsprovidedinthismanualshouldnotbeinterpretedassuch.

WhenyousendinformationtoIBM,yougrantIBManonexclusiverighttouseordistributetheinformationinany wayitbelievesappropriatewithoutincurringanyobligationtoyou.

Contents

Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi

AboutThisManual . . . xi

TypesofUsers . . . xii

SoftwareDependencies . . . xii

AssumptionsAboutYourLocale . . . xii

DemonstrationDatabase . . . xiii

NewFeaturesinDynamicServer,Version10.0 . . . xiii

DocumentationConventions . . . xiv

TypographicalConventions . . . xiv

Feature,Product,andPlatform . . . xv

SyntaxDiagrams . . . xvi

ExampleCodeConventions . . . xx

AdditionalDocumentation. . . xxi

InstallationGuides . . . xxi

OnlineNotes . . . xxi

InformixErrorMessages . . . xxiii

Manuals . . . xxiv

OnlineHelp . . . xxiv

Accessibility . . . xxiv

IBMInformixDynamicServerVersion10.0andCSDKVersion2.90DocumentationSet . . . xxiv

CompliancewithIndustryStandards . . . xxvii

IBMWelcomesYourComments . . . xxviii Chapter1.DatabaseConcepts. . . . . . . . . . . . . . . . . . . . . . . . 1-1 IllustrationofaDataModel . . . 1-2 StoringData . . . 1-3 QueryingData. . . 1-4 ModifyingData . . . 1-5 ConcurrentUseandSecurity . . . 1-6 ControllingDatabaseUse . . . 1-6 CentralizedManagement . . . 1-7 ImportantDatabaseTerms. . . 1-8 TheRelationalDatabaseModel . . . 1-8 Tables. . . 1-9 Columns. . . 1-9 Rows . . . 1-10 Views . . . 1-10 Sequences . . . 1-10 OperationsonTables . . . 1-10 TheObject-RelationalModel(IDS). . . 1-11 StructuredQueryLanguage . . . 1-12 StandardSQL. . . 1-12 InformixSQLandANSISQL . . . 1-13 InteractiveSQL . . . 1-13 GeneralProgramming . . . 1-14

ANSI-CompliantDatabases . . . 1-14 GlobalLanguageSupport(GLS) . . . 1-14 Summary . . . 1-14 Chapter2.ComposingSELECTStatements . . . . . . . . . . . . . . . . . . . 2-1 IntroducingtheSELECTStatement . . . 2-2 OutputfromSELECTStatements . . . 2-3 SomeBasicConcepts . . . 2-4 Single-TableSELECTStatements. . . 2-8 UsingtheAsteriskSymbol(*). . . 2-9 UsingtheORDERBYClausetoSorttheRows. . . 2-10 SelectingSpecificColumns . . . 2-14 UsingtheWHEREClause . . . 2-22 CreatingaComparisonCondition . . . 2-22 UsingaFIRSTClausetoSelectSpecificRows . . . 2-38 ExpressionsandDerivedValues . . . 2-41 UsingRowidValuesInSELECTStatements. . . 2-48 Multiple-TableSELECTStatements . . . 2-49 CreatingaCartesianProduct . . . 2-50 CreatingaJoin . . . 2-52 SomeQueryShortcuts. . . 2-59 Summary . . . 2-62 Chapter3.SelectingDatafromComplexTypes(IDS). . . . . . . . . . . . . . . . 3-1 SelectingRow-TypeData . . . 3-2 SelectingColumnsofaTypedTable . . . 3-3 SelectingColumnsThatContainRow-TypeData . . . 3-4 SelectingfromaCollection. . . 3-8 SelectingNestedCollections . . . 3-9 UsingtheINKeywordtoSearchforElementsinaCollection. . . 3-10 SelectingRowsWithinaTableHierarchy . . . 3-11 SelectingRowsoftheSupertablewithouttheONLYKeyword . . . 3-13 SelectingRowsfromaSupertablewiththeONLYKeyword . . . 3-13 UsinganAliasforaSupertable. . . 3-14 Summary . . . 3-14 Chapter4.UsingFunctionsinSELECTStatements . . . . . . . . . . . . . . . . 4-1 UsingFunctionsinSELECTStatements . . . 4-2 AggregateFunctions. . . 4-2 TimeFunctions . . . 4-8 Date-ConversionFunctions(IDS) . . . 4-13 CardinalityFunction(IDS) . . . 4-17 Smart-Large-ObjectFunctions(IDS) . . . 4-17 String-ManipulationFunctions(IDS) . . . 4-19 OtherFunctions . . . 4-26 UsingSPLRoutinesinSELECTStatements . . . 4-33 UsingDataEncryptionFunctions(IDS) . . . 4-35 Summary . . . 4-36 Chapter5.ComposingAdvancedSELECTStatements . . . . . . . . . . . . . . . 5-1

UsingtheGROUPBYandHAVINGClauses. . . 5-2 UsingtheGROUPBYClause . . . 5-3 UsingtheHAVINGClause . . . 5-6 CreatingAdvancedJoins . . . 5-9 Self-Joins. . . 5-9 OuterJoins . . . 5-13 SubqueriesinSELECTStatements . . . 5-22 CorrelatedSubqueries . . . 5-22 SubqueriesinSELECTStatements . . . 5-23 SubqueriesinaProjectionClause . . . 5-24 SubqueriesinWHEREClauses . . . 5-24 HandlingCollectionsinSELECTStatements(IDS) . . . 5-32 CollectionSubqueries . . . 5-32 Collection-DerivedTables. . . 5-34 SetOperations . . . 5-36 Union . . . 5-36 Intersection . . . 5-43 Difference . . . 5-44 Summary . . . 5-45 Chapter6.ModifyingData . . . . . . . . . . . . . . . . . . . . . . . . . 6-1 ModifyingYourDatabase . . . 6-2 DeletingRows. . . 6-3 DeletingAllRowsofaTable . . . 6-3 DeletingAllRowsusingTRUNCATETABLE . . . 6-4 DeletingSpecifiedRows . . . 6-5 DeletingSelectedRows . . . 6-5 DeletingRowsThatContainRowTypes(IDS) . . . 6-6 DeletingRowsThatContainCollectionTypes(IDS) . . . 6-6 DeletingRowsfromaSupertable(IDS) . . . 6-6 ComplicatedDeleteConditions . . . 6-7 UsingaDeleteJoin(XPS) . . . 6-7 InsertingRows. . . 6-8 SingleRows . . . 6-8 InsertingRowsintoTypedTables(IDS) . . . 6-10 InsertingintoRow-TypeColumns(IDS) . . . 6-11 InsertingRowsintoSupertables(IDS) . . . 6-13 InsertingCollectionValuesintoColumns(IDS) . . . 6-13 InsertingSmartLargeObjects(IDS) . . . 6-15 MultipleRowsandExpressions. . . 6-15 RestrictionsontheInsertSelection. . . 6-16 UpdatingRows . . . 6-17 SelectingRowstoUpdate. . . 6-17 UpdatingwithUniformValues . . . 6-18 RestrictionsonUpdates . . . 6-19 UpdatingwithSelectedValues . . . 6-20 UpdatingRowTypes(IDS) . . . 6-20 UpdatingCollectionTypes(IDS) . . . 6-22 UpdatingRowsofaSupertable(IDS). . . 6-22 UsingaCASEExpressiontoUpdateaColumn . . . 6-23

UsingSQLFunctionstoUpdateSmartLargeObjects(IDS) . . . 6-24 UsingaJointoUpdateaColumn . . . 6-24 PrivilegesonaDatabaseandonitsObjects. . . 6-25 Database-LevelPrivileges. . . 6-25 Table-LevelPrivileges . . . 6-25 DisplayingTablePrivileges . . . 6-26 GrantingPrivilegestoRoles . . . 6-27 DataIntegrity. . . 6-27 EntityIntegrity . . . 6-28 SemanticIntegrity . . . 6-28 ReferentialIntegrity . . . 6-29 ObjectModesandViolationDetection . . . 6-31 InterruptedModifications. . . 6-40 Transactions . . . 6-40 TransactionLogging . . . 6-41 SpecifyingTransactions . . . 6-43 BackupsandLogswithInformixDatabaseServers . . . 6-44 ConcurrencyandLocks . . . 6-45 IBMInformixDataReplication(IDS) . . . 6-46 Summary . . . 6-47 Chapter7.AccessingandModifyingDatainanExternalDatabase . . . . . . . . . . . 7-1 AccessingOtherDatabaseServers . . . 7-1 AccessingANSIDatabases. . . 7-2 CreatingJoinsBetweenExternalDatabaseServers . . . 7-2 AccessingExternalRoutines(IDS) . . . 7-2 RestrictionsforRemoteDatabaseAccess . . . 7-2 SQLStatementsandLoggingModes . . . 7-2 AccessingExternalDatabaseObjects . . . 7-3 Chapter8.ProgrammingwithSQL . . . . . . . . . . . . . . . . . . . . . . 8-1 SQLinPrograms . . . 8-2 SQLinSQLAPIs . . . 8-2 SQLinApplicationLanguages . . . 8-3 StaticEmbedding. . . 8-3 DynamicStatements . . . 8-4 ProgramVariablesandHostVariables . . . 8-4 CallingtheDatabaseServer . . . 8-5 SQLCommunicationsArea . . . 8-5 SQLCODEField . . . 8-6 SQLERRDArray . . . 8-7 SQLWARNArray. . . 8-8 SQLERRMCharacterString . . . 8-10 SQLSTATEValue . . . 8-10 RetrievingSingleRows . . . 8-10 DataTypeConversion . . . 8-11 WorkingwithNULLData . . . 8-12 DealingwithErrors. . . 8-13 RetrievingMultipleRows. . . 8-15 DeclaringaCursor . . . 8-15

OpeningaCursor . . . 8-16 FetchingRows . . . 8-16 CursorInputModes . . . 8-17 ActiveSetofaCursor . . . 8-18 UsingaCursor:APartsExplosion. . . 8-20 DynamicSQL. . . 8-23 PreparingaStatement . . . 8-23 ExecutingPreparedSQL . . . 8-24 DynamicHostVariables . . . 8-25 FreeingPreparedStatements. . . 8-26 QuickExecution. . . 8-26 EmbeddingData-DefinitionStatements . . . 8-26 GrantingandRevokingPrivilegesinApplications . . . 8-26 AssigningRoles . . . 8-29 Summary . . . 8-29 Chapter9.ModifyingDataThroughSQLPrograms . . . . . . . . . . . . . . . . 9-1 UsingDELETE. . . 9-2 DirectDeletions . . . 9-2 DeletingwithaCursor . . . 9-4 UsingINSERT . . . 9-6 UsinganInsertCursor . . . 9-6 RowsofConstants . . . 9-8 AnInsertExample . . . 9-9 UsingUPDATE . . . 9-11 UsinganUpdateCursor . . . 9-11 CleaningUpaTable . . . 9-12 Summary . . . 9-13 Chapter10.ProgrammingforaMultiuserEnvironment. . . . . . . . . . . . . . . 10-1 ConcurrencyandPerformance . . . 10-2 LockingandIntegrity . . . 10-2 LockingandPerformance. . . 10-2 ConcurrencyIssues. . . 10-3 HowLocksWork . . . 10-4 KindsofLocks . . . 10-4 LockScope . . . 10-5 DurationofaLock . . . 10-11 LocksWhileModifying . . . 10-11 LockingwiththeSELECTStatement. . . 10-12 SettingtheIsolationLevel . . . 10-12 UpdateCursors. . . 10-17 RetainingUpdateLocks . . . 10-17 LocksPlacedwithINSERT,UPDATE,andDELETE. . . 10-18 UnderstandingtheBehavioroftheLockTypes . . . 10-19 ControllingDataModificationwithAccessModes . . . 10-20 SettingtheLockMode . . . 10-20 WaitingforLocks . . . 10-21 NotWaitingforLocks . . . 10-21 WaitingaLimitedTime . . . 10-21

HandlingaDeadlock. . . 10-21 HandlingExternalDeadlock . . . 10-22 SimpleConcurrency . . . 10-22 HoldCursors . . . 10-22 UsingtheSQLStatementCache . . . 10-24 Summary. . . 10-25 Chapter11.CreatingandUsingSPLRoutines. . . . . . . . . . . . . . . . . . 11-1 IntroductiontoSPLRoutines . . . 11-3 WhatYouCanDowithSPLRoutines. . . 11-4 SPLRoutineBehaviorforExtendedParallelServer . . . 11-4 WritingSPLRoutines . . . 11-5 UsingtheCREATEPROCEDUREorCREATEFUNCTIONStatement . . . 11-5 ExampleofaCompleteRoutine . . . 11-16 CreatinganSPLRoutineinaProgram . . . 11-16 RoutinesinDistributedOperation . . . 11-17 DefiningandUsingVariables . . . 11-18 DeclaringLocalVariables . . . 11-19 DeclaringGlobalVariables . . . 11-27 AssigningValuestoVariables . . . 11-28 ExpressionsinSPLRoutines . . . 11-31 WritingtheStatementBlock . . . 11-31 ImplicitandExplicitStatementBlocks . . . 11-31 UsingCursors . . . 11-32 UsingtheFOREACHLooptoDefineCursors . . . 11-33 UsinganIF-ELIF-ELSEStructure . . . 11-35 AddingWHILEandFORLoops . . . 11-38 ExitingaLoop . . . 11-39 ReturningValuesfromanSPLFunction . . . 11-40 ReturningaSingleValue . . . 11-41 ReturningMultipleValues . . . 11-41 HandlingRow-TypeData(IDS) . . . 11-43 PrecedenceofDotNotation. . . 11-44 UpdatingaRow-TypeExpression. . . 11-44 HandlingCollections(IDS) . . . 11-45 UsingCollectionDataTypes . . . 11-45 PreparingforCollectionDataTypes(IDS) . . . 11-46 InsertingElementsintoaCollectionVariable . . . 11-48 SelectingElementsfromaCollection. . . 11-51 DeletingaCollectionElement . . . 11-54 UpdatingaCollectionElement . . . 11-57 UpdatingtheEntireCollection. . . 11-59 InsertingintoaCollection . . . 11-63 ExecutingRoutines . . . 11-67 UsingtheEXECUTEStatements . . . 11-68 UsingtheCALLStatement . . . 11-69 ExecutingRoutinesinExpressions . . . 11-70 ExecutinganExternalFunctionwiththeRETURNStatement . . . 11-71 ExecutingCursorFunctionsfromanSPLRoutine . . . 11-72 DynamicRoutine-NameSpecification . . . 11-72

PrivilegesonRoutines . . . 11-74 PrivilegesforRegisteringaRoutine . . . 11-74 PrivilegesforExecutingaRoutine . . . 11-75 PrivilegesonObjectsAssociatedwithaRoutine . . . 11-76 DBAPrivilegesforExecutingaRoutine. . . 11-77 FindingErrorsinanSPLRoutine. . . 11-79 LookingatCompile-TimeWarnings . . . 11-79 GeneratingtheTextoftheRoutine . . . 11-80 DebugginganSPLRoutine . . . 11-80 ExceptionHandling . . . 11-82 TrappinganErrorandRecovering . . . 11-82 ScopeofControlofanONEXCEPTIONStatement. . . 11-83 User-GeneratedExceptions . . . 11-84 CheckingtheNumberofRowsProcessedinanSPLRoutine . . . 11-86 Summary. . . 11-87 Chapter12.CreatingandUsingTriggers. . . . . . . . . . . . . . . . . . . . 12-1 WhentoUseTriggers . . . 12-2 HowtoCreateaTrigger . . . 12-3 AssigningaTriggerName . . . 12-4 SpecifyingtheTriggerEvent. . . 12-4 DefiningtheTriggeredActions . . . 12-4 ACompleteCREATETRIGGERStatement . . . 12-5 UsingTriggeredActions . . . 12-5 UsingBEFOREandAFTERTriggeredActions . . . 12-5 UsingFOREACHROWTriggeredActions . . . 12-6 UsingSPLRoutinesasTriggeredActions . . . 12-8 TriggersinaTableHierarchy(IDS) . . . 12-10 UsingSelectTriggers(IDS) . . . 12-10 SELECTStatementsThatExecuteTriggeredActions . . . 12-10 RestrictionsonExecutionofSelectTriggers . . . 12-11 SelectTriggersonTablesinaTableHierarchy . . . 12-12 Re-EntrantTriggers . . . 12-12 INSTEADOFTriggersonViews(IDS) . . . 12-13 UsinganINSTEADOFTriggertoUpdateonaView . . . 12-13 TracingTriggeredActions . . . 12-14 ExampleofTRACEStatementsinanSPLRoutine . . . 12-14 ExampleofTRACEOutput. . . 12-14 GeneratingErrorMessages . . . 12-15 ApplyingaFixedErrorMessage . . . 12-15 GeneratingaVariableErrorMessage . . . 12-16 Summary. . . 12-17 Appendix.Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . A-1 Notices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-1 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . X-1

Introduction

AboutThisManual . . . xi

TypesofUsers . . . xii

SoftwareDependencies . . . xii

AssumptionsAboutYourLocale . . . xii

DemonstrationDatabase . . . xiii

NewFeaturesinDynamicServer,Version10.0 . . . xiii

DocumentationConventions . . . xiv

TypographicalConventions . . . xiv

Feature,Product,andPlatform . . . xv

SyntaxDiagrams . . . xvi

HowtoReadaCommand-LineSyntaxDiagram . . . xviii

KeywordsandPunctuation . . . xix

IdentifiersandNames . . . xix

ExampleCodeConventions . . . xx

AdditionalDocumentation. . . xxi

InstallationGuides . . . xxi

OnlineNotes . . . xxi

LocatingOnlineNotes . . . xxii

OnlineNotesFilenames . . . xxiii

InformixErrorMessages . . . xxiii

Manuals . . . xxiv

OnlineManuals . . . xxiv

PrintedManuals. . . xxiv

OnlineHelp . . . xxiv

Accessibility . . . xxiv

IBMInformixDynamicServerVersion10.0andCSDKVersion2.90DocumentationSet . . . xxiv

CompliancewithIndustryStandards . . . xxvii

IBMWelcomesYourComments . . . xxviii

In

This

Introduction

Thisintroductionprovidesanoverviewof theinformationinthis manualand describestheconventionsituses.

About

This

Manual

Thismanualshowshow tousebasicand advancedstructuredquerylanguage (SQL)toaccessandmanipulatethedatainyourdatabases. Itdiscussesthe datamanipulationlanguage(DML)statementsaswellastriggersand stored procedurelanguage(SPL)routines,whichDMLstatementsoftenuse.

Thismanualisoneofa seriesofmanualsthatdiscussestheInformix implementationofSQL.TheIBM Informix:GuidetoSQLSyntaxcontainsall

thesyntax descriptionsforSQLandSPL.TheIBM Informix:GuidetoSQL Referenceprovidesreference informationforaspectsof SQLotherthanthe languagestatements.TheIBM Informix:DatabaseDesignand Implementation Guideshowshow touseSQLtoimplementand manageyour databases.

Types

of

Users

Thismanualiswritten forthefollowingusers:

v Databaseusers

v Databaseadministrators

v Database-applicationprogrammers

Thismanualassumesthatyouhavethefollowingbackground:

v Aworkingknowledgeofyourcomputer,youroperatingsystem,and the utilitiesthatyour operatingsystemprovides

v Someexperienceworking withrelationaldatabasesorexposuretodatabase concepts

v Someexperiencewith computerprogramming

Ifyouhavelimitedexperiencewith relationaldatabases, SQL,oryour operatingsystem,refertotheIBM Informix:GettingStartedGuideforyour databaseserverforalistofsupplementarytitles.

Software

Dependencies

Thismanualiswritten withtheassumptionthatyouareusingoneofthe followingdatabaseservers:

v

IBM InformixExtendedParallelServer,Version8.50

v IBM InformixDynamicServer,Version10.0

Assumptions

About

Your

Locale

IBM Informix productscansupport manylanguages,cultures,andcodesets. Alltheinformationrelatedto characterset,collation,and representationof numericdata,currency, date,andtimeisbroughttogetherinasingle environment,calledaGlobalLanguageSupport(GLS)locale.

Theexamplesinthismanual arewrittenwith theassumptionthatyouare usingthedefaultlocale,en_us.8859-1.Thislocale supportsU.S.English formatconventions fordate,time,and currency.Inaddition,thislocale supportstheISO8859-1codeset,whichincludestheASCIIcodeset plus many8-bitcharacters suchasé,è,andñ.

Ifyouplantousenondefaultcharactersinyour dataor yourSQLidentifiers, orifyouwanttoconformtothenondefaultcollationrulesofcharacterdata, youneedtospecifytheappropriatenondefaultlocale.

Forinstructionsonhowtospecifya nondefaultlocale,additionalsyntax, and otherconsiderations relatedtoGLSlocales,seetheIBM Informix:GLS User's Guide.

Demonstration

Database

TheDB–Accessutility,whichisprovided withthedatabaseserverproducts, includesoneormore ofthefollowingdemonstration databases:

v Thestores_demodatabase illustratesarelationalschemawith information aboutafictitiouswholesalesporting-goodsdistributor.Manyexamplesin IBM Informixmanualsare basedonthestores_demodatabase.

ExtendedParallelServer

v

Thesales_demodatabaseillustratesa dimensionalschemafor data-warehousingapplications.Forconceptualinformationaboutdimensional datamodeling,seetheIBM Informix:DatabaseDesignand Implementation Guide.

EndofExtendedParallel Server

DynamicServer

v Thesuperstores_demodatabaseillustratesanobject-relationalschema. The

superstores_demodatabasecontainsexamples ofextendeddatatypes,type andtableinheritance,anduser-definedroutines.

EndofDynamic Server

Forinformationabouthowtocreateandpopulate thedemonstration databases,seetheIBM Informix:DB–AccessUser'sGuide.Fordescriptionsof thedatabasesandtheircontents, seetheIBM Informix:GuidetoSQLReference.

Thescriptsthatyouusetoinstall thedemonstrationdatabasesresideinthe $INFORMIXDIR/bindirectoryonUNIXplatformsandinthe

%INFORMIXDIR%\bindirectory inWindowsenvironments.

New

Features

in

Dynamic

Server,

Version

10.0

Thissectionlists newfeaturesrelevanttothismanual.Inadditionto documentingnewfeatures, thismanualcorrects erratathathavebeen identifiedsincethepreviousedition.

Thismanualdescribes thefollowingnew features:

TheSQLsyntax ofCREATEINDEXand DROPINDEXnowsupportsthe newONLINEkeyword.

v Managingdatabase permissionsthroughdefaultroles

Youcancreatea defaultroleand assignthatroletoindividual usersorto PUBLICona per-databaselevel.

v Restrictingregistration ofexternalroutines

TheDBSA(DatabaseServerAdministrator)canusea newbuilt-inrole, calledEXTEND,tospecifywhichuserscanregisterUDRsthatincludethe

EXTERNALNAMEclause.

v Cross-databasesupport forbuilt-inopaque datatypes

Thereisnocross-database supportforcomplexdatatypes. Theycanonly bemanipulatedinlocaldatabases.

v Column-levelencryption

YoucanusethenewSQLstatement, SETENCRYPTIONPASSWORD,to implementcolumn-levelencryptiontoimprovetheconfidentialityofthe data.Newbuilt-infunctionsprovidemethodsforencryptingand decryptingdata.

Fora comprehensivelistofnew databaseserverfeatures,seethe IBM Informix:GettingStartedGuide.

Documentation

Conventions

Thissectiondescribes theconventionsthatthismanualuses.These

conventionsmakeiteasier togatherinformationfromthisand othervolumes inthedocumentationset.

Thefollowingconventionsarediscussed:

v Typographicalconventions

v Otherconventions

v Syntaxdiagrams

v Command-lineconventions

v Examplecodeconventions

Typographical

Conventions

Thismanualusesthefollowingconventionstointroducenewterms, illustrate screendisplays,describecommandsyntax,andsoforth.

Convention Meaning

KEYWORD Allprimaryelementsinaprogramminglanguagestatement (keywords)appearinuppercaselettersinaseriffont.

Convention Meaning italics

italics italics

Withintext,newtermsandemphasizedwordsappearinitalics. Withinsyntaxandcodeexamples,variablevaluesthatyouareto specifyappearinitalics.

boldface boldface

Namesofprogramentities(suchasclasses,events,andtables), environmentvariables,fileandpathnames,andinterfaceelements (suchasicons,menuitems,andbuttons)appearinboldface. monospace

monospace

Informationthattheproductdisplaysandinformationthatyou enterappearinamonospacetypeface.

KEYSTROKE Keysthatyouaretopressappearinuppercaselettersinasansserif font.

> Thissymbolindicatesamenuitem.Forexample,“ChooseTools> Options”meanschoosetheOptionsitemfromtheToolsmenu.

Tip: Whenyouareinstructedto“enter”charactersorto “execute”a command, immediatelypressRETURN aftertheentry. Whenyouare instructedto “type”thetextorto“press” otherkeys,noRETURNis required.

Feature,

Product,

and

Platform

Feature,product,and platformmarkupidentifiesparagraphsthatcontain feature-specific,product-specific,orplatform-specificinformation.Some

examplesofthis markupfollow:

DynamicServer

Identifiesinformationthatisspecific toIBM InformixDynamicServer EndofDynamic Server

ExtendedParallelServer

Identifiesinformationthatisspecific toIBMInformixExtendedParallel Server EndofExtendedParallel Server

UNIXOnly

Identifiesinformationthatisspecific toUNIXplatforms

End ofUNIXOnly

WindowsOnly

Identifiesinformationthatisspecific totheWindowsenvironment

Endof WindowsOnly

Thismarkupcanapplytooneormoreparagraphswithin asection.Whenan entiresectionappliestoaparticularproductorplatform,this isnotedaspart oftheheadingtext, forexample:

Table Sorting(LinuxOnly)

Syntax

Diagrams

Thisguideusessyntax diagramsbuiltwiththefollowingcomponentsto describethesyntax forstatementsandallcommandsotherthansystem-level commands.

Note: Starting in2004,syntax diagramshavebeenreformattedtoconformto theIBMstandard.

SyntaxdiagramsdepictingSQLandcommand-linestatementshavechanged inthefollowingways:

v Thesymbolsatthebeginningandendofstatementsare nowdoublearrows insteadofa verticallineat theend.

v Thesymbolsatthebeginningandendofsyntax segmentdiagramsarenow verticallinesinsteadofarrows.

v Howmanytimesaloopcanberepeatedisnow explainedinadiagram footnoteinsteadofa numberinagatesymbol.

v Syntaxstatementsthatarelongerthanonelinenowcontinueonthenext lineinsteadof loopingdownwith acontinuousline.

v Productorcondition-specificpathsare nowexplainedindiagramfootnotes insteadoficons.

Thefollowingtabledescribessyntax diagramcomponents. ComponentrepresentedinPDF ComponentrepresentedinHTML Meaning

>>--- Statementbegins.

---> Statementcontinueson nextline.

>--- Statementcontinuesfrom previousline.

--->< Statementends. ---SELECT--- Requireditem. ’---LOCAL---’ Optionalitem. +--DISTINCT---+ ’---UNIQUE---’

Requireditemwithchoice. Oneandonlyoneitem mustbepresent.

+--FOR UPDATE---+ ’--FOR READ ONLY--’

Optionalitemswithchoice areshownbelowthemain line,oneofwhichyou mightspecify.

.---NEXT---.

+---PRIOR---+ ’---PREVIOUS---’

Thevaluesbelowthe mainlineareoptional,one ofwhichyoumight specify.Ifyoudonot specifyanitem,thevalue abovethelinewillbeused asthedefault.

ComponentrepresentedinPDF ComponentrepresentedinHTML Meaning .---,---. V | +---index_name---+ ’---table_name---’

Optionalitems.Several itemsareallowed;a commamustprecedeeach repetition.

>>-| Table Reference |->< _{Referencetoasyntax} segment. Table Reference |--+---view---+--| +---table---+ ’----synonym---’ Syntaxsegment.

HowtoReadaCommand-LineSyntaxDiagram

Thefollowingcommand-linesyntaxdiagramusessomeoftheelementslisted inthetable intheprevious section.

CreatingaNo-ConversionJob onpladm create job job

-p project

-n -d device -D database

-t table

(1) Setting the Run Mode -S server -T target

Notes:

1 Seepage17-4

Thesecondlineinthis diagramhasa segmentnamed“SettingtheRun Mode,”whichaccording tothediagramfootnote,isonpage17-4.This segmentisshown inthefollowingsegmentdiagram(thediagramuses segmentstart andendcomponents).

-f d p a l c u n N

Toconstructacommandcorrectly,startatthetopleftwith thecommand. Followthediagramtotheright,includingtheelementsthatyouwant.The elementsinthediagramare casesensitive.

TheCreatingaNo-ConversionJob diagramillustratesthefollowingsteps: 1. Type onpladmcreatejoband thenthenameofthejob.

2. Optionally, type-pand thenthenameoftheproject. 3. Type thefollowingrequiredelements:

v -n

v -dandthenameofthedevice

v -Dandthenameofthedatabase

v -tand thenameofthetable

4. Optionally, youcanchooseoneormore ofthefollowingelementsand repeat themanarbitrarynumber oftimes:

v -Sandtheservername

v -Tand thetargetservername

v

Therunmode.Toset therunmode, followtheSettingtheRunMode segmentdiagramtotype -f,optionallytyped,p,ora,and then optionallytype loru.

5. Followthediagramtotheterminator.

Yourdiagramiscomplete.

KeywordsandPunctuation

Keywordsarewordsreservedforstatementsandallcommandsexcept system-levelcommands.Whenakeywordappears inasyntax diagram,itis showninuppercase letters.Whenyouuseakeywordina command,youcan writeit inuppercaseorlowercase letters,butyoumustspellthekeyword exactlyasitappears inthesyntax diagram.

Youmust alsouseanypunctuationinyour statementsandcommandsexactly asshown inthesyntaxdiagrams.

IdentifiersandNames

Variablesserveasplaceholders foridentifiersandnamesinthesyntax diagramsandexamples.Youcanreplacea variablewith anarbitraryname,

identifier,orliteral, dependingonthecontext.Variablesarealsousedto representcomplex syntaxelementsthatareexpandedinadditionalsyntax diagrams.Whena variableappearsin asyntaxdiagram,anexample,ortext, itisshowninlowercaseitalic.

Thefollowingsyntaxdiagramusesvariablestoillustratethegeneralform ofa simpleSELECTstatement.

SELECT column_name FROM table_name

Whenyouwritea SELECTstatementofthisform,youreplacethevariables column_nameandtable_name withthenameofa specificcolumnand table.

Example

Code

Conventions

ExamplesofSQLcode occurthroughoutthis manual.Exceptasnoted,the codeisnotspecific toanysingleIBM Informixapplication developmenttool.

IfonlySQLstatementsare listedintheexample,theyarenotdelimitedby semicolons.Forinstance,youmightseethecodeinthefollowingexample: CONNECT TO stores_demo

...

DELETEFROM customer WHERE customer_num = 121 ...

COMMITWORK DISCONNECT CURRENT

TousethisSQLcodeforaspecific product,youmust applythesyntaxrules forthatproduct.Forexample,ifyouareusingDB–Access,youmust delimit multiplestatementswithsemicolons. IfyouareusinganSQLAPI,youmust useEXECSQLat thestart ofeachstatementandasemicolon (orother appropriatedelimiter)at theendofthestatement.

Tip: Ellipsispoints inacodeexample indicatethatmorecode wouldbe addedina fullapplication, butitisnotnecessary toshowitto describe theconceptbeingdiscussed.

FordetaileddirectionsonusingSQLstatementsfora particularapplication developmenttoolorSQLAPI, seethemanualfor yourproduct.

Additional

Documentation

Foradditionalinformation,refertothefollowingtypesofdocumentation:

v Installationguides

v Onlinenotes

v Informixerrormessages

v Manuals

v Onlinehelp

Installation

Guides

Installationguides arelocatedinthe/docdirectoryof theproductCDorin the/docdirectoryoftheproduct‘scompressedfileifyoudownloadedit from theIBM Website.Alternatively,youcanobtaininstallationguidesfromthe IBMInformixOnlineDocumentationsiteat

http://www.ibm.com/software/data/informix/pubs/library/.

Online

Notes

Thefollowingsectionsdescribetheonlinefiles thatsupplementthe

informationinthismanual.Pleaseexaminethese filesbeforeyoubeginusing yourIBMInformixproduct. Theycontainvitalinformationaboutapplication andperformanceissues.

OnlineFile Description Format TOCNotes TheTOC(TableofContents)notesfile

providesacomprehensivedirectoryof hyperlinkstothereleasenotes,thefixedand knowndefectsfile,andallthedocumentation notesfilesforindividualmanualtitles.

HTML

DocumentationNotes Thedocumentationnotesfileforeachmanual containsimportantinformationand

correctionsthatsupplementtheinformation inthemanualorinformationthatwas modifiedsincepublication.

HTML,text

ReleaseNotes Thereleasenotesfiledescribesfeature differencesfromearlierversionsofIBM Informixproductsandhowthesedifferences mightaffectcurrentproducts.Forsome products,thisfilealsocontainsinformation aboutanyknownproblemsandtheir workarounds.

HTML,text

MachineNotes (Non-Windowsplatformsonly)Themachine notesfiledescribesanyplatform-specific actionsthatyoumusttaketoconfigureand useIBM Informixproductsonyour computer.

text

FixedandKnown DefectsFile

Thistextfilelistsissuesthathavebeen identifiedwiththecurrentversion.Italsolists customer-reporteddefectsthathavebeen fixedinboththecurrentversionandin previousversions.

text

LocatingOnlineNotes

Onlinenotesareavailable fromtheIBM InformixOnlineDocumentationsite athttp://www.ibm.com/software/data/informix/pubs/library/.Additionally youcanlocatethesefilesbefore orafterinstallationasdescribedbelow.

BeforeInstallation

Allonlinenotes arelocatedinthe/docdirectoryof theproductCD.The easiestway toaccessthedocumentationnotes,therelease notes,andthefixed andknowndefects fileisthrough thehyperlinks fromtheTOC notesfile.

Themachinenotesfileandthefixed andknowndefects fileare onlyprovided intextformat.

OnUNIXplatforms inthedefaultlocale,thedocumentationnotes,release notes,andmachinenotesfiles appearunderthe

$INFORMIXDIR/release/en_us/0333directory.

DynamicServer

OnWindowsthedocumentationandreleasenotes filesappearinthe Informixfolder. Todisplaythisfolder,chooseStart >Programs>IBM InformixDynamic Serverversion>DocumentationNotesorRelease Notes fromthetaskbar.

MachinenotesdonotapplytoWindows platforms. EndofDynamic Server OnlineNotesFilenames

Onlinenoteshavethefollowingfileformats:

OnlineFile FileFormat Examples

TOCNotes prod_os_tocnotes_version.html ids_win_tocnotes_10.0.html DocumentationNotes prod_bookname_docnotes_version.html/txt ids_hpl_docnotes_10.0.html ReleaseNotes prod_os_relnotes_version.html/txt ids_unix_relnotes_10.0.txt MachineNotes prod_machine_notes_version.txt ids_machine_notes_10.0.txt FixedandKnown

DefectsFile

prod_defects_version.txt

ids_win_fixed_and_known _defects_version.txt ids_defects_10.0.txt client_defects_2.90.txt ids_win_fixed_and_known _defects_10.0.txt

Informix

Error

Messages

Thisfileisacomprehensiveindexoferrormessagesand theircorrective actionsfortheInformixproductsandversionnumbers.

OnUNIXplatforms,usethefinderrcommandtoread theerrormessages and theircorrectiveactions.

DynamicServer

OnWindows,usetheInformix ErrorMessages utilitytoreaderrormessages andtheircorrectiveactions.Todisplaythis utility,chooseStart>Programs> IBMInformixDynamicServerversion >InformixErrorMessagesfromthe taskbar.

Youcanalso accessthesefiles fromtheIBM InformixOnlineDocumentation siteat http://www.ibm.com/software/data/informix/pubs/library/.

Manuals

OnlineManuals

ACD thatcontainsyourmanualsinelectronicformatisprovidedwithyour IBMInformixproducts.Youcaninstallthedocumentationoraccessit directly fromtheCD.For informationabouthow toinstall,read,and printonline manuals,seetheinstallationinsert thataccompaniesyourCD.Youcanalso obtainthesameonline manualsfromtheIBM InformixOnlineDocumentation siteat http://www.ibm.com/software/data/informix/pubs/library/.

PrintedManuals

Toorderhardcopymanuals,contactyour salesrepresentativeorvisittheIBM PublicationsCenter Websiteat

http://www.ibm.com/software/howtobuy/data.html.

Online

Help

IBMInformixonline help,provided witheachgraphicaluser interface(GUI), displaysinformationaboutthoseinterfacesandthefunctionsthatthey perform.UsethehelpfacilitiesthateachGUIprovidestodisplaytheonline help.

Accessibility

IBMiscommittedtomaking ourdocumentationaccessibletopersonswith disabilities.Ourbooks areavailableinHTMLformatsothattheycanbe accessedwithassistivetechnologysuchasscreenreadersoftware.Thesyntax diagramsinourmanualsareavailableindotted decimalformat,whichisan accessibleformatthatisavailableonlyifyouare usinga screenreader.For moreinformationaboutthedotted decimalformat,seetheAccessibility appendix.

IBM

Informix

Dynamic

Server

Version

10.0

and

CSDK

Version

2.90

Documentation

Set

Thefollowingtableslistthemanualsthatare partoftheIBMInformix DynamicServer,Version10.0andtheCSDKVersion2.90,documentationset. PDFandHTMLversions ofthesemanualsareavailableat

http://www.ibm.com/software/data/informix/pubs/library/.Youcanorder hardcopyversionsofthese manualsfromtheIBMPublicationsCenterat http://www.ibm.com/software/howtobuy/data.html.

Table1.DatabaseServerManuals

Manual Subject

Administrator’sGuide Understanding,configuring,andadministeringyourdatabaseserver. Administrator’sReference ReferencematerialforInformixDynamicServer,suchasthesyntaxof

databaseserverutilitiesonmodeandonstat,anddescriptionsof configurationparameters,thesysmasterstables,andlogical-logrecords. BackupandRestoreGuide Theconceptsandmethodsyouneedtounderstandwhenyouusethe

ON-Barandontapeutilitiestobackupandrestoredata.

DB-AccessUser’sGuide UsingtheDB-Accessutilitytoaccess,modify,andretrievedatafrom Informixdatabases.

DataBladeAPI FunctionReference

TheDataBladeAPIfunctionsandthesubsetofESQL/Cfunctionsthat theDataBladeAPIsupports.YoucanusetheDataBladeAPItodevelop clientLIBMIapplicationsandCuser-definedroutinesthataccessdatain Informixdatabases.

DataBladeAPI Programmer’sGuide

TheDataBladeAPI,whichistheC-languageapplication-programming interfaceprovidedwithDynamicServer.YouusetheDataBladeAPIto developclientandserverapplicationsthataccessdatastoredinInformix databases.

DatabaseDesignand ImplementationGuide

Designing,implementing,andmanagingyourInformixdatabases. EnterpriseReplication

Guide

Howtodesign,implement,andmanageanEnterpriseReplicationsystem toreplicatedatabetweenmultipledatabaseservers.

ErrorMessagesfile Causesandsolutionsfornumberederrormessagesyoumightreceive whenyouworkwithIBMInformixproducts.

GettingStartedGuide DescribestheproductsbundledwithIBMInformixDynamicServerand interoperabilitywithotherIBMproducts.Summarizesimportantfeatures ofDynamicServerandthenewfeaturesforeachversion.

GuidetoSQL:Reference InformationaboutInformixdatabases,datatypes,systemcatalogtables, environmentvariables,andthestores_demodemonstrationdatabase. GuidetoSQL:Syntax DetaileddescriptionsofthesyntaxforallInformixSQLandSPL

statements.

GuidetoSQL:Tutorial AtutorialonSQL,asimplementedbyInformixproducts,thatdescribes thebasicideasandtermsthatareusedwhenyouworkwitharelational database.

High-PerformanceLoader User’sGuide

AccessingandusingtheHigh-PerformanceLoader(HPL),toloadand unloadlargequantitiesofdatatoandfromInformixdatabases. InstallationGuidefor

MicrosoftWindows

InstructionsforinstallingIBMInformixDynamicServeronWindows. InstallationGuidefor

UNIXandLinux

InstructionsforinstallingIBMInformixDynamicServeronUNIXand Linux.

Table1.DatabaseServerManuals (continued)

Manual Subject

J/FoundationDeveloper’s Guide

Writinguser-definedroutines(UDRs)intheJavaprogramminglanguage forInformixDynamicServerwithJ/Foundation.

LargeObjectLocator DataBladeModuleUser’s Guide

UsingtheLargeObjectLocator,afoundationDataBlademodulethatcan beusedbyothermodulesthatcreateorstorelarge-objectdata.TheLarge ObjectLocatorenablesyoutocreateasingleconsistentinterfacetolarge objectsandextendstheconceptoflargeobjectstoincludedatastored outsidethedatabase.

MigrationGuide ConversiontoandreversionfromthelatestversionsofInformix databaseservers.MigrationbetweendifferentInformixdatabaseservers. OpticalSubsystemGuide TheOpticalSubsystem,autilitythatsupportsthestorageofBYTEand

TEXTdataonopticaldisk.

PerformanceGuide ConfiguringandoperatingIBMInformixDynamicServertoachieve optimumperformance.

R-TreeIndexUser’sGuide CreatingR-treeindexesonappropriatedatatypes,creatingnewoperator classesthatusetheR-treeaccessmethod,andmanagingdatabasesthat usetheR-treesecondaryaccessmethod.

SNMPSubagentGuide TheIBMInformixsubagentthatallowsaSimpleNetworkManagement Protocol(SNMP)networkmanagertomonitorthestatusofInformix servers.

StorageManager Administrator’sGuide

InformixStorageManager(ISM),whichmanagesstoragedevicesand mediaforyourInformixdatabaseserver.

TrustedFacilityGuide Thesecure-auditingcapabilitiesofDynamicServer,includingthecreation andmaintenanceofauditlogs.

User-DefinedRoutinesand DataTypesDeveloper’s Guide

Howtodefinenewdatatypesandenableuser-definedroutines(UDRs) toextendIBMInformixDynamicServer.

Virtual-IndexInterface Programmer’sGuide

Creatingasecondaryaccessmethod(index)withtheVirtual-Index Interface(VII)toextendthebuilt-inindexingschemesofIBMInformix DynamicServer.TypicallyusedwithaDataBlademodule.

Virtual-TableInterface Programmer’sGuide

CreatingaprimaryaccessmethodwiththeVirtual-TableInterface(VTI) sothatusershaveasingleSQLinterfacetoInformixtablesandtodata thatdoesnotconformtothestorageschemeofInformixDynamicServer.

Table2.Client/ConnectivityManuals

Manual Subject

ClientProductsInstallation Guide

InstallingIBMInformixClientSoftwareDeveloper’sKit(ClientSDK)and IBMInformixConnectoncomputersthatuseUNIX,Linux,and

Windows. EmbeddedSQLJUser’s

Guide

UsingIBMInformixEmbeddedSQLJtoembedSQLstatementsinJava programs.

Table2.Client/ConnectivityManuals (continued)

Manual Subject

ESQL/CProgrammer’s Manual

TheIBMInformiximplementationofembeddedSQLforC.

GLSUser’sGuide TheGlobalLanguageSupport(GLS)feature,whichallowsIBMInformix APIsanddatabaseserverstohandledifferentlanguages,cultural conventions,andcodesets.

JDBCDriverProgrammer’s Guide

InstallingandusingInformixJDBCDrivertoconnecttoanInformix databasefromwithinaJavaapplicationorapplet.

.NETProviderReference Guide

UsingInformix.NETProvidertoenable.NETclientapplicationsto accessandmanipulatedatainInformixdatabases.

ODBCDriverProgrammer’s Manual

UsingtheInformixODBCDriverAPItoaccessanInformixdatabaseand interactwiththeInformixdatabaseserver.

OLEDBProvider Programmer’sGuide

InstallingandconfiguringInformixOLEDBProvidertoenableclient applications,suchasActiveXDataObject(ADO)applicationsandWeb pages,toaccessdataonanInformixserver.

ObjectInterfaceforC++ Programmer’sGuide

ThearchitectureoftheC++objectinterfaceandacompleteclass reference.

Table3.DataBladeDeveloper’sKitManuals

Manual Subject

DataBladeDeveloper’sKit User’sGuide

DevelopingandpackagingDataBlademodulesusingBladeSmithand BladePack.

DataBladeModule DevelopmentOverview

BasicorientationfordevelopingDataBlademodules.Includesan exampleillustratingthedevelopmentofaDataBlademodule. DataBladeModule

InstallationandRegistration Guide

InstallingDataBlademodulesandusingBladeManagertomanage DataBlademodulesinInformixdatabases.

Compliance

with

Industry

Standards

TheAmericanNationalStandardsInstitute(ANSI)andtheInternational OrganizationofStandardization(ISO)havejointly establishedaset of industrystandardsfor theStructuredQueryLanguage(SQL). IBMInformix SQL-basedproductsarefullycompliantwithSQL-92EntryLevel(published asANSIX3.135-1992),whichisidenticaltoISO9075:1992. Inaddition,many featuresofIBM Informixdatabaseserverscomplywith theSQL-92

IntermediateandFullLeveland X/OpenSQLCommonApplications Environment(CAE)standards.

IBM

Welcomes

Your

Comments

Wewanttoknowaboutanycorrectionsorclarificationsthatyouwouldfind usefulinourmanuals,whichwillhelp usimprovefutureversions.Include thefollowinginformation:

v Thenameand versionofthemanualthatyouare using

v Sectionandpagenumber

v Yoursuggestionsaboutthemanual

Sendyourcommentstous atthefollowingemailaddress:

[email protected]

Thisemailaddressisreservedforreportingerrorsandomissionsinour documentation.Forimmediatehelp withatechnicalproblem,contactIBM TechnicalSupport.

Chapter

1.

Database

Concepts

IllustrationofaDataModel . . . 1-2 StoringData . . . 1-3 QueryingData. . . 1-4 ModifyingData . . . 1-5 ConcurrentUseandSecurity . . . 1-6 ControllingDatabaseUse . . . 1-6 Access-ManagementStrategies . . . 1-6 CentralizedManagement . . . 1-7 ImportantDatabaseTerms. . . 1-8 TheRelationalDatabaseModel . . . 1-8 Tables. . . 1-9 Columns. . . 1-9 Rows . . . 1-10 Views . . . 1-10 Sequences . . . 1-10 OperationsonTables . . . 1-10 TheObject-RelationalModel(IDS). . . 1-11 StructuredQueryLanguage . . . 1-12 StandardSQL. . . 1-12 InformixSQLandANSISQL . . . 1-13 InteractiveSQL . . . 1-13 GeneralProgramming . . . 1-14 ANSI-CompliantDatabases . . . 1-14 GlobalLanguageSupport(GLS) . . . 1-14 Summary . . . 1-14

In

This

Chapter

Thischapterdescribesfundamentaldatabase conceptsand focusesonthe followingtopics:

v Datamodels

v Multipleusers

v Databaseterminology

v

SQL(StructuredQueryLanguage)

Yourrealuseof adatabasebegins withtheSELECTstatement,which Chapter2,“Composing SELECTStatements,”onpage2-1,describes.

Illustration

of

a

Data

Model

Theprincipaldifferencebetweeninformationcollectedin adatabaseversus informationcollectedina fileisthewaythedataisorganized.Aflatfileis organizedphysically;certainitemsprecedeorfollowotheritems.Butthe contentsofa databaseareorganized accordingtoadatamodel.Adatamodel isa plan,ormap, thatdefinestheunitsof dataandspecifieshow eachunit relatestotheothers.

Forexample,anumber canappearin eitherafileora database.Ina file,it is simplya numberthatoccursatacertain pointinthefile.Anumber ina database,however,hasarole thatthedatamodelassignstoit.Therole might bea pricethatisassociatedwithaproductthatwassoldasoneitemofanorder thatacustomerplaced.Eachofthesecomponents,price,product,item,order, andcustomer,alsohasarole thatthedatamodelspecifies.For anillustration ofadatamodel,seeFigure1-1.

Youdesignthedatamodelwhenyoucreatethedatabase.Youtheninsert unitsofdataaccordingtotheplanthatthemodellaysout.Some booksuse theterm schemainsteadofdatamodel.

Storing

Data

Anotherdifferencebetweena databaseanda fileisthattheorganizationof thedatabase isstored withthedatabase.

Afilecanhaveacomplex innerstructure, butthedefinitionofthatstructure isnotwithin thefile;itisintheprogramsthatcreateorusethefile.For example,a documentfilethataword-processingprogramstoresmight containdetailedstructuresthatdescribetheformatofthedocument.

However,onlytheword-processingprogramcandecipherthecontentsofthe file,becausethestructureisdefinedwithintheprogram,notwithin thefile.

1015 06/27/98 1 case baseball gloves $450.00 1014 06/25/98 1 case footballs $960.00

1013 06/22/98 1 each tennis racquet $19.80 1012 06/18/98 1 case volleyballs $840.00 1011 06/18/98 5 each tennis racquet $99.00 1010 06/17/98 1 case tennis balls $36.00

ORDERS

Adatamodel,however,iscontainedinthedatabaseitdescribes.Ittravels withthedatabaseandisavailableto anyprogramthatusesthedatabase.The modeldefinesnotonlythenamesof thedataitemsbutalso theirdatatypes, soa programcanadaptitselftothedatabase.Forexample,a programcan findoutthat,inthecurrentdatabase,a priceitemisa decimalnumber with eightdigits,twototheright ofthedecimalpoint;thenitcanallocatestorage foranumberof thattype.Howprogramsworkwithdatabasesisthesubject ofChapter8, “Programmingwith SQL,”onpage8-1,and Chapter9,

“ModifyingDataThroughSQLPrograms,”onpage9-1.

Querying

Data

Anotherdifferencebetweena databaseanda fileisthewayyoucanaccess them.Youcansearchafilesequentially,lookingforparticularvaluesat particularphysicallocationsineachlineorrecord.Thatis,youmightask, “Whatrecordshavethenumber 1013inthefirst field?”Figure1-2showsthis typeofsearch.

Incontrast,whenyouquerya database,youusethetermsthatthemodel defines.Youcanquerythedatabasewith questionssuchas,“Whatordershave beenplacedforproductsmadebytheShimaraCorporation, bycustomersin NewJersey,withshipdatesinthethirdquarter?”Figure1-3showsthistypeof query.

ORDERS

1015 06/27/98 1 case baseball gloves $450.00 1013 06/22/98 1 each tennis racquet $19.80

06/22/98 1 case tennis balls $36.00 06/22/98 1 case tennis balls $48.00 1012 06/18/98 1 case volleyballs $840.00 1011 06/18/98 5 each tennis racquet $99.00 1010 06/17/98 1 case tennis balls $36.00

Inotherwords,whenyouaccessdatathatisstored inafile,youmust state yourquestionintermsofthephysicallayoutofthefile.Whenyouquerya database,youcanignorethearcanedetailsofcomputerstorageand state yourqueryintermsthatreflecttherealworld,at leasttotheextentthatthe datamodelreflectstherealworld.

Chapter2,“Composing SELECTStatements,”onpage2-1,andChapter5, “ComposingAdvanced SELECTStatements,”onpage5-1,discussthe languageyouusetomakequeries.

Forinformationabouthowtobuildand implementyourdatamodel,seethe IBM Informix:DatabaseDesign andImplementationGuide.

Modifying

Data

Thedatamodelalsomakesitpossibletomodifythecontentsofthedatabase withlesschance forerror.Youcanquerythedatabasewith statementssuch as,“Findeverystockitemwitha manufacturerofPrestaorSchraeder,and increaseitspriceby13percent.”Youstatechanges intermsthatreflectthe meaningofthedata.Youdonothavetowastetimeandeffortthinkingabout detailsoffields withinrecordsina file,sothechancesforerrorarefewer.

order 1016 06/29/98 order 1023 07/24/98 manufacturer Shimara

Run: Next Restart Exit

Display the next page of query results

---stores---Press CTRL-W for Help---1019 Bob Shorter SHM swim cap 07/16/98 order 1019 07/16/98 customer Cathy O’Brian state New Jersey customer Bob Shorter

Thestatementsyouusetomodifystoreddataare coveredinChapter6, “ModifyingData,”onpage6-1.

Concurrent

Use

and

Security

Adatabase canbeacommon resourceformanyusers.Multipleuserscan queryand modifyadatabase simultaneously.The databaseserver(the programthatmanagesthecontentsofall databases)ensures thatthequeries andmodificationsaredoneinsequenceandwithoutconflict.

Havingconcurrentusersonadatabaseprovides greatadvantagesbut also introducesnewproblemsofsecurityandprivacy. Somedatabasesareprivate; individualsset themupfortheirownuse.Other databasescontain

confidentialmaterialthatmust beshared,but onlyamonga selectgroup;still otherdatabasesprovidepublicaccess.

Controlling

Database

Use

Informixdatabasesoftwareprovidesthemeanstocontroldatabaseuse.When youdesigna database,youcanperformanyofthefollowingfunctions:

v Keepthedatabasecompletelyprivate

v Openitsentirecontentstoall usersor toselectedusers

v Restricttheselectionofdatathatsomeuserscanview (differentselections ofdatatodifferentgroupsofusers)

v Allowspecifieduserstoviewcertainitems,butnotmodifythem

v Allowspecifieduserstoaddnewdata,butnotmodifyolddata

v

Allowspecifieduserstomodifyall,orspecifieditemsof,existingdata

v Ensurethataddedormodifieddataconforms tothedatamodel

Access-ManagementStrategies

Adatabase administrator(DBA)cansetrolestostandardizeandchangethe privilegesofmanyusersbytreating themasmembers ofaclass.Whenthe DBAassignsprivileges tothatrole, everyuserofthatrolehasthose privileges.Inordertoenabletheseroles,a usermust issueaSET ROLE statement.TheSQLstatementsusedfordefiningandmanipulatingroles include:CREATEROLE,DROPROLE,GRANT,REVOKE, andSETROLE.

Tocreateandgrantarole:

1. Use theCREATEROLEstatementtocreateanewrole inthecurrent database.

2. Use theGRANTstatementtograntprivilegestothatrole

3. Use theGRANTROLEstatementtogranttheroletoa userortoPUBLIC (all users).

FormoreinformationontheSQLsyntaxstatementsfor definingand manipulatingroles,seetheIBM Informix:GuidetoSQLSyntax.

TheDBAcandefineadefaultroletoassigna roletoindividual usersortothe PUBLICgroupfora particulardatabase.Theroleisautomaticallyactivated whentheuser establishesa connectionwiththedatabase,withouttheuser needingtoissuea SETROLEstatement. Eachuserhaswhateverprivilegesare grantedtotheuserindividually, aswell astheprivileges ofthedefaultrole.

Note: Ifdifferentdefaultrolesareassignedtotheuser andtoPUBLIC, the defaultroleof theuser takesprecedence.

Todefineandgrantprivilegesforadefaultrole:

1. UsetheCREATEROLEstatementtocreateanewrole inthecurrent database.

2. UsetheGRANTstatementtograntprivilegestotherole.

3. Grant therole toauser andsettheroleasthedefaultuser orPUBLICrole usingthefollowingsyntax:

GRANT DEFAULT ROLE rolename TO username or

GRANT DEFAULT ROLE rolename TO PUBLIC

4. UsetheREVOKEDEFAULTROLEstatementtodisassociateadefaultrole froma user.

OnlytheDBAorthedatabaseownercanremovethedefaultrole. 5. UsetheSETROLEDEFAULT statementtoresetthecurrentrolebackto

thedefaultrole.

Forsecurityreasons,theDBAcancreaterolesthathavelimitedaccess. For instance,onlytheDatabaseSystemAdministrator(DBSA)oruserstowhom theDBSAhasgrantedthebuilt-inEXTENDrolecancreateordropUDRsthat aredefinedwiththeEXTERNALkeyword.Formoreinformationonthe ExternalRoutineReferencesegmentorSQLstatementsfordefiningand manipulatingroles,seetheIBM Informix:GuidetoSQLSyntax.

Formoreinformationondefaultroles,seetheIBM Informix:Administrator's Guide.

Formoreinformationabouthowtogrant andlimitaccesstoyourdatabase, seetheIBM Informix:DatabaseDesignand ImplementationGuide.

Centralized

Management

Databasesthatmanypeopleusearevaluableandmustbe protectedas importantbusinessassets.Youcreateasignificantproblemwhenyoucompile astoreofvaluabledataandsimultaneouslyallowmanyemployeestoaccess

it.Youhandlethisproblembyprotectingdatawhilemaintaining performance.Thedatabaseserverletsyoucentralizethese tasks.

Databasesmustbe guardedagainstlossordamage.Thehazardsaremany: failuresinsoftwareand hardware,andtherisksoffire,flood,andother naturaldisasters.Losinganimportantdatabasecreates ahugepotentialfor damage.Thedamagecouldinclude notonlytheexpenseanddifficultyof re-creatingthelostdata,butalsothelossofproductive timebythedatabase usersaswell asthelossofbusinessandgoodwill whileuserscannotwork.A planforregularbackupshelpsavoidormitigatethesepotentialdisasters.

Alargedatabase thatmanypeopleusemustbe maintainedandtuned.

Someonemust monitoritsuseofsystemresources,chartitsgrowth,anticipate bottlenecks,and planforexpansion. Userswillreportproblemsin the

applicationprograms;someonemustdiagnosetheseproblemsand correct them.Ifrapidresponseisimportant, someonemustanalyzetheperformance ofthesystemandfindthecausesofslow responses.

Important

Database

Terms

Youshouldknowanumber oftermsbefore youbeginthenextchapter. Dependingonthedatabase serveryouuse,a differentsetoftermscan describethedatabase andthedatamodelthatapply.

The

Relational

Database

Model

ThedatabasesyoucreatewithanInformixdatabase serverareobject-relational databases.Inpracticaltermsthismeansthatalldataispresentedintheform oftableswith rowsand columnswherethefollowingsimple corresponding relationshipsapply.

Relationship Description

table=entity Atablerepresentsallthatthedatabaseknows

aboutonesubjectorkindof thing.

column=attribute Acolumnrepresentsonefeature,

characteristic,orfactthatistrueofthetable subject.

row=instance Arowrepresentsoneindividual instanceof

thetablesubject.

Somerulesapplyabouthowyouchooseentitiesandattributes,buttheyare importantonlywhenyouare designinga newdatabase.(Formore

informationaboutdatabase design,seetheIBM Informix:DatabaseDesignand ImplementationGuide.)Thedatamodelinanexistingdatabaseisalreadyset. Tousethedatabase,youneed toknowonlythenamesof thetablesand columnsandhow theycorrespondtotherealworld.

Tables

Adatabase isa collectionofinformationthatisgroupedinto oneormore tables.Atableisanarrayofdataitems organizedintorowsandcolumns.A demonstrationdatabaseisdistributedwith everyInformixdatabaseserver product.Apartial tablefromthedemonstration databasefollows.

stock_num manu_code description unit_price unit unit_descr . . . . . . . . . . . . . . . . . .

1 HRO baseballgloves 250.00 case 10gloves/case 1 HSK baseballgloves 800.00 case 10gloves/case 1 SMT baseballgloves 450.00 case 10gloves/case

2 HRO baseball 126.00 case 24/case

3 HSK baseballbat 240.00 case 12/case

4 HSK football 960.00 case 24/case

4 HRO football 480.00 case 24/case

5 NRG tennisracquet 28.00 each each

. .

. ... ... ... ... ...

313 ANZ swimcap 60.00 case 12/box

Atable representsallthatthedatabase administrator(DBA)wantstostore aboutoneentity,onetypeofthingthatthedatabasedescribes.The example table,stock,representsallthattheDBAwantstostore aboutthemerchandise thatasportinggoodsstorestocks.Other tablesinthedemonstrationdatabase representsuchentitiesascustomerandorders.

Thinkofadatabase asacollectionof tables.Tocreateadatabaseistocreatea setoftables.Theright toqueryormodify tablescanbecontrolled ona table-by-tablebasis,sothatsomeuserscanviewormodify sometablesbut notothers.

Columns

Eachcolumnofatablecontains oneattribute,whichisonecharacteristic, feature,orfactthatdescribesthesubjectofthetable.Thestocktablehas columnsforthefollowingfactsaboutitemsofmerchandise:stock numbers, manufacturercodes,descriptions,prices,andunits ofmeasure.

Rows

Eachrowofa tableisoneinstance ofthesubjectofthetable,whichisone particularexampleofthatentity.Eachrowof thestocktablestandsforone itemofmerchandisethatthesportinggoodsstore sells.

Views

Aview isavirtual tablebasedonaspecified SELECTstatement.Aview isa dynamicallycontrolledpictureofthecontentsina databaseandallows a programmertodeterminewhatinformationtheuserseesandmanipulates. Differentuserscanbe givendifferentviewsof thecontentsofadatabase,and theiraccesstothose contentscanberestrictedin severalways.

Sequences

Asequenceisadatabaseobjectthatgeneratesa sequenceofwholenumbers withina definedrange. Thesequenceofnumberscanrunineither ascending ordescendingorder,andismonotonic.Formoreinformationabout

sequences,seetheIBM Informix:GuidetoSQLSyntax.

Operations

on

Tables

Becauseadatabaseisreallya collectionoftables, databaseoperationsare operationsontables.Theobject-relationalmodelsupportsthreefundamental operations:selection,projection,andjoining.Figure1-4showstheselection andprojectionoperations.(Allthreeoperationsaredefinedindetail,with manyexamples, inthefollowingchapters.)

SELECTION

stocktable

P R O J E C T I O N stock_num manu_code description unit_price unit unit_descr

1 HRO baseball gloves 250.00 case 10 gloves/case 1 HSK baseball gloves 800.00 case 10 gloves/case 1 SMT baseball gloves 450.00 case 10 gloves/case

2 HRO baseball 126.00 case 24/case

3 HSK baseball bat 240.00 case 12/case

4 HSK football 960.00 case 24/case

4 HRO football 480.00 case 24/case

5 NRG tennis racquet 28.00 each each

313 ANZ swim cap 60.00 case 12/box

Whenyouselectdatafroma table,youarechoosing certainrowsandignoring others.Forexample,youcanquerythestocktable byaskingthedatabase managementsystemto,“Selectallrowsinwhichthemanufacturercodeis HSKandtheunitpriceisbetween200.00 and300.00.”

Whenyouprojectfroma table,youare choosingcertaincolumns andignoring others.Forexample,youcanquerythestocktable byaskingthedatabase managementsystemto“project thestock_num,unit_descr,and unit_price columns.”

Atable containsinformationaboutonlyoneentity;whenyouwant

informationaboutmultiple entities,youmustjoin theirtables.Youcanjoin tablesinmanyways.For moreinformationaboutjoinoperations,referto Chapter5,“ComposingAdvanced SELECTStatements,”onpage5-1.

The

Object-Relational

Model

(IDS)

DynamicServerallows youtobuildobject-relationaldatabases.Inadditionto supportingalphanumericdatasuchascharacterstrings,integers,date,and decimal,anobject-relationaldatabaseextends thefeaturesofa relational modelwiththefollowingobject-orientedcapabilities:

v Extensibility.Youcanextendthecapabilityofthedatabaseserverby definingnewdatatypes(andtheaccessmethodsandfunctionstosupport them)anduser-definedroutines(UDRs)thatallowyoutostoreand manageimages,audio,video,largetextdocuments,and soforth.

IBM,aswellasthird-partyvendors,packagessomedatatypesandaccess methodsinto DataBlademodules,orshared classlibraries,thatyoucanadd ontothedatabase server,if itsuitsyourneeds.DataBlademodules enable youtostore non-traditionaldatatypessuchastwo-dimensionalspatial objects(lines,polygons, ellipses,and circles)andtoaccessthemthrough R-treeindexes.ADataBlademodulemightalso providenewtypesofaccess tolargetextdocuments,includingphrasematching,fuzzysearches,and synonymmatching.

Youcanalso extendthedatabase serveronyour ownbyusingthefeatures ofDynamicServer thatenableyoutoadddatatypesandaccessmethods. Formoreinformation,seeIBM Informix:User-DefinedRoutinesandData TypesDeveloper'sGuide.

YoucancreateUDRsinSPLand theCprogramminglanguageto

encapsulateapplicationlogicortoenhancethefunctionalityoftheDynamic Server.Formoreinformation,seeChapter11,“CreatingandUsing SPL Routines,”onpage11-1.

v ComplexTypes.Youcandefinenew datatypesthatcombineoneormore existingdatatypes.Complextypesenablegreaterflexibilityinorganizing dataatthelevelof columnsandtables. Forexample,withcomplextypes, youcandefinecolumnsthatcontaincollectionsofvaluesofasingle type andcolumns thatcontainmultiple componenttypes.

v Inheritance.Youcandefineobjects(typesand tables)thatacquirethe propertiesof otherobjectsand addnew propertiesthatare specifictothe objectthatyoudefine.

DynamicServerprovides object-orientedcapabilitiesbeyondthose ofthe relationalmodelbutrepresents alldataintheform oftableswith rowsand columns.Althoughtheobject-relational modelextendsthecapabilitiesofthe relationalmodel,youcanimplementyour datamodelasatraditional relationaldatabaseif youchoose.

Somerulesapplyabouthowyouchooseentitiesandattributes,buttheyare importantonlywhenyouare designinga newdatabase.Formoreinformation aboutobject-relational databasedesign,seetheIBM Informix:DatabaseDesign andImplementationGuide.

Structured

Query

Language

Mostcomputersoftwarehasnotyetreachedapointwhere youcanliterally askadatabase,“WhatordershavebeenplacedbycustomersinNewJersey withshipdatesin thethirdquarter?”Youmust stillphrasequestionsina restrictedsyntaxthatthesoftwarecaneasilyparse.Youcanposethesame questiontothedemonstration databaseinthefollowingterms:

SELECT * FROM customer, orders

WHERE customer.customer_num = orders.customer_num AND customer.state = ’NJ’

AND orders.ship_date

BETWEEN DATE(’7/1/98’) AND DATE(’9/30/98’)

ThisquestionisasampleofStructuredQueryLanguage(SQL).Itisthe languagethatyouuseto directall operationsonthedatabase.SQLis

composedof statements,eachof whichbegins withoneortwokeywordsthat specifya function.TheInformiximplementationofSQLincludesalarge numberofSQLstatements,fromALLOCATEDESCRIPTORtoWHENEVER.

Youwillusemostofthestatementsonlywhenyouset uportuneyour database.Youwillusethreeorfourstatementsregularlytoqueryorupdate yourdatabase.FordetailsonSQLstatements,seetheIBM Informix:Guideto SQLSyntax.

Onestatement, SELECT,isinalmost constantuse. SELECTistheonly statementthatyoucanusetoretrievedatafromthedatabase.Itisalso the mostcomplicatedstatement, andthenexttwochaptersofthisbookexplore itsmanyuses.

Standard

SQL

SQLandtherelationalmodelwereinventedanddevelopedatIBMinthe earlyandmiddle 1970s.OnceIBMprovedthatitwaspossibletoimplement

practicalrelationaldatabasesandthatSQLwasa usablelanguagefor manipulatingthem,otherimplementationsofSQLwere developed.

Forreasonsofperformanceorcompetitive advantage,ortotakeadvantageof localhardwareorsoftware features,eachSQLimplementationdifferedin smallwaysfromtheothersandfromtheIBMversionof thelanguage.To ensurethatthedifferencesremainedsmall, astandardscommitteewasformed intheearly 1980s.

CommitteeX3H2,sponsoredbytheAmericanNationalStandardsInstitute (ANSI),issued theSQL1 standardin 1986.Thisstandarddefinesa coresetof SQLfeaturesandthesyntaxofstatementssuchasSELECT.

Informix

SQL

and

ANSI

SQL

TheInformiximplementationof SQLiscompatiblewithstandardSQL. InformixSQLisalsocompatiblewith theIBMversionofthelanguage. However,InformixSQLcontains extensionstothestandard;thatis, extra optionsorfeaturesforcertainstatements,andlooserrulesforothers.Mostof thedifferencesoccur inthestatementsthatarenotineverydayuse.For example,fewdifferencesoccur intheSELECTstatement, whichaccountsfor 90percentofSQLuse.

However,theInformixSQLextensionsdoexistand createconflicts.

Thousandsofcustomershaveembedded Informix-styleSQLinprogramsand storedroutines.NowthatInformixproductsarepartof theIBM Data

Managementproductsuite,usersrelyonIBMtokeeptheInformix-styleSQL languagethesame.Other customersrequire theabilitytousedatabasesina waythatconformsexactlytotheANSIstandard,and theyrelyonIBMto changeitslanguagetoconform.

IBMresolvestheconflictwiththefollowingcompromise:

v TheInformix-styleversionofSQL,withitsextensionstothestandard,is availablebydefault.

v Youcanask anyInformix-styleSQLlanguageprocessor tocheckyour use ofSQLandpost awarningflagwheneveryouuseanInformix-style extension.

Whena differenceexistsbetweenInformixandANSIstandard,the IBM Informix:GuidetoSQLSyntaxidentifiestheInformixsyntaxasan extensiontotheANSIstandardforSQL.

Interactive

SQL

Tocarryouttheexamplesinthisbookand toexperimentwithSQLand databasedesign,youneeda programthatletsyouexecuteSQLstatements interactively.DB–Accessissuchaprogram. IthelpsyoucomposeSQL

statementsandthenpassesyour SQLstatementstothedatabaseserverfor executionand displaystheresultstoyou.

General

Programming

YoucanwriteprogramsthatincorporateSQLstatementsandexchangedata withthedatabaseserver.Thatis,youcanwritea programtoretrievedata fromthedatabase andformatithoweveryouchoose.Youcanalsowrite programsthattakedatafromanysourceinanyformat,prepareit,andinsert itintothedatabase.

Youcanalso writeprogramscalled storedroutinestoworkwith database dataandobjects.Thestoredroutinesthatyouwritearestoreddirectlyina databaseintables.Youcanthenexecuteastored routinefromDB–Accessor anSQLApplicationProgrammingInterface(API)suchasIBM Informix ESQL/C.

Chapter8,“Programmingwith SQL,”onpage8-1,andChapter9,“Modifying DataThroughSQLPrograms,”onpage9-1,presentanoverviewofhow SQL isusedinprograms.

ANSI-Compliant

Databases

UsetheMODEANSIkeywordswhenyoucreateadatabasetodesignateitas ANSIcompliant.Withinsuchadatabase,certain characteristicsof the

ANSI/ISOstandardapply.For example,allactionsthatmodifydatatake placewithin atransactionautomatically,whichmeansthatthechangesare madeintheirentiretyornotatall.Differencesinthebehaviorof

ANSI-compliantdatabasesarenoted,whereappropriate,inthestatement descriptionsintheIBM Informix:GuidetoSQLSyntax.For adetailed

discussionofANSI-compliantdatabases, seetheIBM Informix:DatabaseDesign andImplementationGuide.

Global

Language

Support

(GLS)

InformixdatabaseserverproductsprovidetheGlobal LanguageSupport (GLS)feature.InadditiontoU.S.ASCIIEnglish,GLS allowsyouto workin otherlocalesandusenon-ASCIIcharactersinSQLdataandidentifiers.You canusetheGLSfeatureto conformtothecustomsofaspecific locale.The localefilescontainculture-specificinformation,suchasmoneyanddate formatsandcollationorders.For moreGLSinformation,seetheIBM Informix: GLSUser'sGuide.

Summary

Adatabase containsacollection ofrelatedinformationbutdiffersina fundamentalwayfromothermethodsofstoringdata.Thedatabasecontains notonlythedata,but alsoadatamodelthatdefineseachdataitemand specifiesitsmeaningwithrespecttotheotheritemsand totherealworld.