Database SQL messages and codes

(1)

System i

Database

SQL messages and codes

Version 5 Release 4

(2)

(3)

System i

Database

SQL messages and codes

Version 5 Release 4

(4)

Note

Beforeusingthisinformationandtheproductitsupports,read theinformationin“Notices,”on page189.

FifthEdition(February2006)

Thiseditionappliestoversion5,release4,modification0ofIBMi5/OS(productnumber5722–SS1)andtoall subsequentreleasesandmodificationsuntilotherwiseindicatedinneweditions.Thisversiondoesnotrunonall reducedinstructionsetcomputer(RISC)modelsnordoesitrunonCISCmodels.

©CopyrightInternationalBusinessMachinesCorporation2001,2006.Allrightsreserved.

(5)

SQL messages and codes

. . . . . . . 1

What’snewforV5R4 . . . 1

PrintablePDF . . . 1

SQLCODEandSQLSTATEconcepts . . . 1

ListingofSQLSTATEclasscodes . . . 3

ListingofSQLSTATEvalues . . . 4

SQLmessageconcepts. . . 23

ListingofSQLmessages . . . 24

Appendix. Notices

. . . . . . . . . 189

ProgrammingInterfaceInformation. . . 191

Trademarks . . . 191

Termsandconditions. . . 191

(6)

(7)

SQL messages and codes

Usethistopiccollection tofinddescriptionsofcodesand messagesreturnedwhenusingSQLwithDB2 UniversalDatabase^™foriSeries^™(DB2^® UDBforiSeries).Thistopiccontains listingsof SQLCODEs, SQLSTATEs,class codes,andSQLmessages.

Note: By usingthecodeexamples,youagreetothetermsofthe“Codelicenseand disclaimer information” onpage187.

What’s new for V5R4

Thistopichighlightsthechanges madetothistopiccollectionforV5R4.

New messages and codes added

Severaladditionsweremadetothe“ListingofSQLSTATEclasscodes”onpage3,“ListingofSQLSTATE values”onpage4,andtheListingofSQLmessages.

How to see what’s new or changed

Tohelpyouseewheretechnicalchanges havebeen made,this informationuses:

v The imagetomarkwhereneworchangedinformationbegins.

v The imagetomarkwhereneworchangedinformationends.

Tofindotherinformationaboutwhat’s neworchangedthisrelease,seetheMemotousers.

Printable PDF

Usethistoview andprintaPDFofthis information.

ToviewordownloadthePDFversionofthisdocument,selectSQLmessagesandcodes (about2824KB).

Saving PDF files

Tosavea PDFonyour workstationforviewingorprinting:

1. Right-clickthePDFinyourbrowser(right-click thelinkabove).

2. Click theoption thatsavesthePDFlocally.

3. Navigatetothedirectoryinwhichyouwanttosave thePDF.

4. Click Save.

Downloading Adobe Reader

YouneedAdobe Readerinstalledonyoursystem tovieworprintthesePDFs.Youcandownloada free copyfromtheAdobeWebsite(www.adobe.com/products/acrobat/readstep.html)

.

SQLCODE and SQLSTATE concepts

Thisreference providesalistofSQLCODEs andtheirassociatedSQLSTATEs.Inthis reference,youcan findinstructionsforfindingaSQLCODEinthemessagefilealongwiththetextforthesemessages.

|

(8)

SQLCODEsandSQLSTATEsarereturnedintheSQLCAstructure orthroughtheGETDIAGNOSTICS statement.SQLSTATEisthepreferredstandardreturncode.Itprovidesapplication programswithreturn codesforcommonerrorconditions foundamongtheDB2 UniversalDatabaseproducts.SQLSTATEsare particularlyusefulwhenhandlingerrorsindistributed SQLapplications.

SQLCODE

AnSQLCODEisareturncode.Thereturn codeissent bythedatabasemanagerafter completionofeach SQLstatement.

EachSQLCODEthatisrecognizedbyaDB2UDB foriSeriesserverhasa correspondingmessageinthe messagefileQSQLMSG.Themessageidentifierfor anySQLCODEisconstructedbyappendingthe absolutevalue (5digits) oftheSQLCODEtoSQandchanging thethirdcharactertoLifthefirst characteroftheSQLCODEis0.For example,if theSQLCODEis30070,themessageidentifieris

SQ30070.IftheSQLCODEis-0204,themessageidentifierisSQL0204.Lastly,iftheSQLCODEisa3-digit positivenumber,a zeroisaddedbefore thefirst digit.Forexample,iftheSQLCODEis551, themessage identifierisSQL0551.

SQLSTATE

SQLSTATEprovides applicationprogramswith commonreturncodesforsuccess, warning,and error conditionsfoundamongtheDB2Universal Databaseproducts.SQLSTATEvaluesareparticularlyuseful whenhandlingerrorsindistributedSQLapplications.SQLSTATEvaluesare consistentwiththe

SQLSTATEspecifications containedintheSQL1999standard.

AnSQLSTATEvalueisareturn codethatindicatestheoutcomeofthemostrecentlyexecutedSQL statement.ThemechanismusedtoaccessSQLSTATEvaluesdependsonwhere theSQLstatementis executed.InJava^™,SQLSTATEvaluesarereturnedbyusinggetSQLState() method.InSQLfunctions,SQL procedures,SQLtriggers,andembeddedapplicationsotherthanJava,SQLSTATEvaluesare returnedin thefollowing:

v ThelastfivebytesoftheSQLCA v Astand-aloneSQLSTATEvariable v TheGETDIAGNOSTICSstatement

SQLSTATEvaluesaredesigned sothatapplication programscantestforspecificconditions orclassesof conditions.

SQLSTATEvaluesarecomprisedof atwo-characterclasscode value,followedbya three-character subclasscodevalue.Classcodevaluesrepresentclassesof successfuland unsuccessfulcompletion conditions.IfyouwanttouseSQLSTATEasthebasisofyourapplication’sreturncodes, youcandefine yourown SQLSTATEclassesorsubclassesusingthefollowingguidelines:

v SQLSTATEclassesthatbeginwiththecharacters7 through9 orIthroughZcanbedefined. Within theseclasses, anysubclasscanbedefined.

v

SQLSTATEclassesthatbeginwiththecharacters0 through6 orAthroughH arereservedforthe databasemanager.Withinthese classes,subclassesthatbeginwiththecharacters0 throughH are reservedforthedatabasemanager.SubclassesthatbeginwiththecharactersIthroughZcanbe defined.

TheclasscodeofanSQLSTATEvalueindicates whethertheSQLstatementwasexecutedsuccessfully (classcodes00and 01)orunsuccessfully(allotherclasscodes).

SQLSTATEisrelatedtoSQLCODE.EverySQLSTATEhasoneormoreSQLCODEsassociatedwithit.An SQLSTATEcanrefertomorethanoneSQLCODE.

(9)

SQLSTATEs returned by non-DB2 UDB for iSeries application servers

WhenanSQLSTATEotherthan00000isreturnedfroman applicationserverthatisnotDB2UDB for iSeries,DB2UDBforiSeriesattemptstomaptheSQLSTATEtoa DB2UDBforiSeriesSQLCODEand messagewiththefollowingresults:

v IftheSQLSTATEisnotrecognizedbyDB2UDB foriSeries,thecommonmessagefor theclassis issued.

v

IftheSQLSTATEand SQLCODEcorrespondtoa singleDB2UDB foriSeriesSQLCODE,DB2 UDB attemptstoconvertthereturnedtokensintothereplacementdatathattheSQLmessageexpects.Ifan erroroccurswhile convertingthetokens:

– Thetokensare returnedwithoutconversionintheSQLERRMfieldoftheSQLCAorintheSQL diagnosticsarea.

– Acommon messagefortheclass codeoftheSQLSTATEisissued.

Related reference

“ListingofSQLSTATEclass codes”

Thistableprovidesa listofgeneralSQLSTATEclasses. Eachclasslinkstoalistof themorespecific SQLSTATEcodesthatcomprisethatclass.

“ListingofSQLSTATEvalues” onpage4

ThetablesinthistopicprovidedescriptionsofSQLSTATEcodesthatcanbereturnedtoapplications byDB2UDBforiSeries.ThetablesincludeSQLSTATEvalues,theirmeanings,and their

correspondingSQLCODEvalues.

SQLmessagefinder

Listing of SQLSTATE class codes

Thistableprovides alistofgeneralSQLSTATEclasses.Eachclasslinks toalistofthemorespecific SQLSTATEcodes thatcomprisethatclass.

Classcode Meaning Subcodes

00 UnqualifiedSuccessfulCompletion Table1onpage4

01 Warning Table2onpage4

02 NoData Table3onpage6

07 DynamicSQLError Table4onpage7

08 ConnectionException Table5onpage7

09 TriggeredActionException Table6onpage7

0A FeatureNotSupported Table7onpage7

0E InvalidSchemaNameListSpecification Table8onpage7

0F InvalidToken Table9onpage7

0K ResignalWhenHandlerNotActive Table10onpage8 0W ProhibitedStatementEncounteredDuringTrigger Table11onpage8

0Z DiagnosticsException Table12onpage8

20 CaseNotFoundforCaseStatement Table13onpage8

21 CardinalityViolation Table14onpage8

22 DataException Table15onpage8

23 ConstraintViolation Table16onpage9

24 InvalidCursorState Table17onpage10

25 InvalidTransactionState Table18onpage10

(10)

Classcode Meaning Subcodes

26 InvalidSQLStatementIdentifier Table19onpage10 27 TriggeredDataChangeViolation Table20onpage10 28 InvalidAuthorizationSpecification Table21onpage10 2D InvalidTransactionTermination Table22onpage11

2E InvalidConnectionName Table23onpage11

2F SQLFunctionException Table24onpage11

33 InvalidSQLDescriptorName Table25onpage11

34 InvalidCursorName Table26onpage11

35 InvalidConditionNumber Table27onpage11

36 CursorSensitivityException Table28onpage11

38 ExternalFunctionException Table29onpage12

39 ExternalFunctionCallException Table30onpage12

3B InvalidSQLDescriptorName Table31onpage12

3C AmbiguousCursorName Table32onpage12

3F InvalidSchema(Collection)Name Table33onpage12 42 SyntaxErrororAccessRuleViolation Table34onpage12

44 WITHCHECKOPTIONViolation Table35onpage20

46 JavaTMErrors Table36onpage20

51 InvalidApplicationState Table37onpage20

53 InvalidOperandorInconsistentSpecification Table38onpage21 54 SQLorProductLimitExceeded Table39onpage21 55 ObjectNotinPrerequisiteState Table40onpage22 56 MiscellaneousSQLorProductError Table41onpage22 57 ResourceNotAvailableorOperatorIntervention Table42onpage22

58 SystemError Table43onpage23

Listing of SQLSTATE values

Thetablesinthis topicprovidedescriptionsofSQLSTATEcodes thatcanbereturnedtoapplicationsby DB2UDBforiSeries.ThetablesincludeSQLSTATEvalues,theirmeanings,and theircorresponding SQLCODEvalues.

Table1.Classcode00:UnqualifiedSuccessfulCompletion SQLSTATE

Value Meaning SQLCODEValues

00000 Completionoftheoperationwassuccessfulanddidnotresultinanytypeof warningorexceptioncondition.

+000

Table2.Classcode01:Warning SQLSTATE

01002 ADISCONNECTerroroccurred. +596

01003 Nullvalueswereeliminatedfromtheargumentofacolumnfunction. +000

|

(11)

Table2.Classcode01:Warning (continued) SQLSTATE

01004 Thevalueofastringwastruncatedwhenassignedtoanotherstringdata typewithashorterlength.

+000,+445,+802, +20141

01005 InsufficientnumberofentriesinanSQLDA. +239

01006 Aprivilegewasnotrevoked. +569

01007 Aprivilegewasnotgranted. +570

01009 Thesearchconditionistoolongfortheinformationschema. +177 0100A Thequeryexpressionoftheviewistoolongfortheinformationschema. +178 0100C Oneormoreadhocresultsetswerereturnedfromtheprocedure. +466 0100D Thecursorthatwasclosedhasbeenreopenedonthenextresultsetwithin

thechain.

+467

0100E Theprocedurereturnedtoomanyresultsets. +464,+20206

01503 Thenumberofresultcolumnsislargerthanthenumberofhostvariables provided.

+000,+030

01504 TheUPDATEorDELETEstatementdoesnotincludeaWHEREclause. +000,+088 01505 Thestatementwasnotexecutedbecauseitisunacceptableinthis

environment.

+084

01506 AnadjustmentwasmadetoaDATEorTIMESTAMPvaluetocorrectan invaliddateresultingfromanarithmeticoperation.

+000

01515 Thenullvaluehasbeenassignedtoahostvariable,becausethenon-null valueofthecolumnisnotwithintherangeofthehostvariable.

+304

01517 Acharacterthatcannotbeconvertedwasreplacedwithasubstitute character.

+335

01519 Thenullvaluehasbeenassignedtoahostvariable,becauseanumericvalue isoutofrange.

+802

01520 Thenullvaluehasbeenassignedtoahostvariable,becausethecharacters cannotbeconverted.

+331

01522 ThelocaltableorviewnameusedintheCREATEALIASstatementis undefined.

+403

01526 Isolationlevelhasbeenescalated. +595

01527 ASETstatementreferencesaspecialregisterthatdoesnotexistattheAS. +799 01528 WHERENOTNULLisignored,becausetheindexkeycannotcontainnull

values.

+645

01532 Anundefinedobjectnamewasdetected. +204

01534 Thestringrepresentationofadatetimevalueisinvalid. +180,+181 01535 Anarithmeticoperationonadateortimestamphasaresultthatisnot

withinthevalidrangeofdates.

+183

01536 Duringremotebindwhereexistencecheckingisdeferred,theserver-name specifieddoesnotmatchthecurrentserver.

+114

01539 ConnectionissuccessfulbutonlySBCScharactersshouldbeused. +863 01542 AuthorizationIDdoesnothavetheprivilegetoperformtheoperationas

specified.

+552

01544 Thenullvaluehasbeenassignedtoahostvariable,becauseasubstring erroroccurred;forexample,anargumentofSUBSTRisoutofrange.

+138

01545 Anunqualifiedcolumnnamehasbeeninterpretedasacorrelatedreference. +012

|

(12)

Table2.Classcode01:Warning (continued) SQLSTATE

01547 Amixeddatavalueisimproperlyformed. +191,+304,+802

01548 TheauthorizationIDdoesnothavetheprivilegetoperformthespecified operationontheidentifiedobject.

+551

01557 ToomanyhostvariableshavebeenspecifiedonSELECTINTOorFETCH. +326 01564 Thenullvaluehasbeenassignedtoahostvariable,becausedivisionbyzero

occurred.

+802

01565 Thenullvaluehasbeenassignedtoahostvariable,becauseamiscellaneous dataexceptionoccurred;forexample,thecharactervaluefortheCAST, DECIMAL,FLOAT,orINTEGERscalarfunctionisinvalid;afloating-point NAN(notanumber)orinvaliddatainapackeddecimalfieldwasdetected.

+304,+420,+802

01567 Thetablewascreatedbutnotjournaled. +7905

01587 Theunitofworkwascommittedorrolledback,buttheoutcomeisnotfully knownatallsites.

+990

01593 AnALTERTABLEmightcausedatatruncation. +460

01594 InsufficientnumberofentriesinanSQLDAforALLinformation(thatis,not enoughdescriptorstoreturnthedistinctname).

+237

01623 ThevalueofDEGREEisignored. +1530

01627 TheDATALINKvaluemightnotbevalidbecausethetableisinreconcile pendingorreconcileisnotapossiblestate.

+360

01634 Thedistinctdatatypenameistoolongandcannotbereturnedinthe SQLDA.Theshortnameisreturnedinstead.

+7036

01643 AssignmenttoSQLCODEorSQLSTATEvariabledoesnotsignalawarning orerror.

+385

01646 Aresultsetscannotbereturnedbecausethecursorwasclosed. +7050

01647 ADB2SQLBEFOREtriggerchangedtoDB2ROW. +7051

01658 BinarydataisinvalidforDECRYPT_CHARandDECYRYPT_DB. +20224 01660 Theroutinewascreatedbutarestorewillnotupdatethecatalog. +7909

01662 ReleaserecordoptionignoredonCLOSE. +30107

01Hxx ValidwarningSQLSTATEsreturnedbyauser-definedfunctionorexternal procedureCALL.

+462

Table3.Classcode02:NoData SQLSTATE

02000 Oneofthefollowingexceptionsoccurred:

v TheresultoftheSELECTINTOstatementorthesubselectoftheINSERT statementwasanemptytable.

v

ThenumberofrowsidentifiedinthesearchedUPDATEorDELETE statementwaszero.

v ThepositionofthecursorreferencedintheFETCHstatementwasafter thelastrowoftheresulttable.

v Thefetchorientationisinvalid.

+100

02001 Noadditionalresultsetsreturned. +387

02505 TheGETDESCRIPTORVALUEisgreaterthanCOUNT. +20298

|

(13)

Table4.Classcode07:DynamicSQLError SQLSTATE

07001 Thenumberofhostvariablesisnotcorrectforthenumberofparameter markers.

-313

07002 Thecallparameterlistorcontrolblockisinvalid. -804 07003 ThestatementidentifiedintheEXECUTEstatementisaselect-statement,or

isnotinapreparedstate.

-518

07004 TheUSINGclauseorINTOclauseisrequiredfordynamicparameters. -313 07005 Thestatementnameofthecursoridentifiesapreparedstatementthatcannot

beassociatedwithacursor.

-517

07006 Aninputhostvariable,transitionvariable,orparametermarkercannotbe used,becauseofitsdatatype.

-301

07008 Thedescriptorcountisinvalid. -074

07009 Thedescriptorindexisinvalid. -075

Table5.Classcode08:ConnectionException SQLSTATE

08001 Theapplicationrequesterisunabletoestablishtheconnection. -30080,-30082, -30089

08002 Theconnectionalreadyexists. -842

08003 Theconnectiondoesnotexist. -843,-900

08004 Theapplicationserverrejectedestablishmentoftheconnection. -30060,-30061 08501 ADISCONNECTisnotallowedwhentheconnectionusesanLU6.2

protectedconversation.

-858

Table6.Classcode09:TriggeredActionException SQLSTATE

09000 AtriggeredSQLstatementfailed. -723

Table7.Classcode0A:FeatureNotSupported SQLSTATE

0A001 TheCONNECTstatementisinvalid,becausetheprocessisnotinthe connectablestate.

-752

Table8.Classcode0E:InvalidSchemaNameListSpecification SQLSTATE

0E000 TheschemanamelistinaSETPATHstatementisnotvalid. -329

Table9.Classcode0F:InvalidToken SQLSTATE

0F001 Thelocatorvaluedoesnotcurrentlyrepresentanyvalue. -423

|

(14)

Table10.Classcode0K:ResignalWhenHandlerNotActive SQLSTATE

0K000 ARESIGNALwasissuedbutahandlerisnotactive. -787

Table11.Classcode0W:ProhibitedStatementEncounteredDuringTrigger SQLSTATE

0W000 Thestatementisnotallowedinatrigger. -751

Table12.Classcode0Z:DiagnosticsException SQLSTATE

0Z001 Maximumnumberofstackeddiagnosticsareasexceeded. -20226 0Z002 Stackeddiagnosticsaccessedwithoutanactivehandler. -20228

Table13.Classcode20:CaseNotFoundforCaseStatement SQLSTATE

20000 ThecasewasnotfoundfortheCASEstatement. -773

Table14.Classcode21:CardinalityViolation SQLSTATE

21000 TheresultofaSELECTINTO,scalarfullselect,orsubqueryofabasic predicateismorethanonevalue.

-811

Table15.Classcode22:DataException SQLSTATE

22001 Characterdata,righttruncationoccurred;forexample,anupdateorinsert valueisastringthatistoolongforthecolumn,oradatetimevaluecannot beassignedtoahostvariable,becauseitistoosmall.

-302,-303,-404,-433, -802

22002 Anullvalue,ortheabsenceofanindicatorparameterwasdetected;for example,thenullvaluecannotbeassignedtoahostvariable,becauseno indicatorvariableisspecified.

-305

22003 Anumericvalueisoutofrange. -302,-304,-406,-446,

-802

22004 Anullvalueisnotallowed. -087,-305

22005 Anerroroccurredonassignment. -076

22006 Thefetchorientationisinvalid. -231

22007 Aninvaliddatetimeformatwasdetected;thatis,aninvalidstring representationorvaluewasspecified.

-180,-181

22008 Datetimefieldoverflowoccurred;forexample,anarithmeticoperationona dateortimestamphasaresultthatisnotwithinthevalidrangeofdates.

-183

|

(15)

Table15.Classcode22:DataException (continued) SQLSTATE

22011 Asubstringerroroccurred;forexample,anargumentofSUBSTRisoutof range.

-138

22012 Divisionbyzeroisinvalid. -802

22018 ThecharactervaluefortheCAST,DECIMAL,FLOAT,orINTEGERscalar functionisinvalid.

-420

22019 TheLIKEpredicatehasaninvalidescapecharacter. -130 22021 Acharacterisnotinthecodedcharactersetortheconversionisnot

supported.

-330,-331

22023 Aparameterorhostvariablevalueisinvalid. -302,-304,-406,-802 22024 ANUL-terminatedinputhostvariableorparameterdidnotcontainaNUL. -302

22025 TheLIKEpredicatestringpatterncontainsaninvalidoccurrenceofan escapecharacter.

-130

22501 Thelengthcontrolfieldofavariablelengthstringisnegativeorgreaterthan themaximum.

-311

22503 Thestringrepresentationofanameisinvalid. -188

22504 Amixeddatavalueisinvalid. -191,-304,-406,-802

22511 ADTlengthexceedsmaximumcolumnlength.ThevalueforaROWIDor referencecolumnisnotvalid.

-399

22522 ACCSIDvalueisnotvalidatall,notvalidforthedatatypeorsubtype,or notvalidfortheencodingscheme.

-189

22524 Characterconversionresultedintruncation -334

22525 Partitioningkeyvalueisnotvalid. -327

22527 Invalidinputdatadetectedforamultiplerowinsert. -30106

Table16.Classcode23:ConstraintViolation SQLSTATE

23001 TheupdateordeleteofaparentkeyispreventedbyaRESTRICTupdateor deleterule.

-531,-532

23502 Aninsertorupdatevalueisnull,butthecolumncannotcontainnullvalues. -407 23503 Theinsertorupdatevalueofaforeignkeyisinvalid. -530 23504 TheupdateordeleteofaparentkeyispreventedbyaNOACTIONupdate

ordeleterule.

-531,-532

23505 Aviolationoftheconstraintimposedbyauniqueindexoraunique constraintoccurred.

-803

23511 Aparentrowcannotbedeleted,becausethecheckconstraintrestrictsthe deletion.

-543

23512 Thecheckconstraintcannotbeadded,becausethetablecontainsrowsthat donotsatisfytheconstraintdefinition.

-544

23513 TheresultingrowoftheINSERTorUPDATEdoesnotconformtothecheck constraintdefinition.

-545

23515 Theuniqueindexcannotbecreatedoruniqueconstraintaddedbecausethe tablecontainsduplicatevaluesofthespecifiedkey.

-603

|

(16)

Table16.Classcode23:ConstraintViolation (continued) SQLSTATE

23520 Theforeignkeycannotbedefined,becauseallofitsvaluesarenotequaltoa parentkeyoftheparenttable.

-667

23522 Therangeofvaluesfortheidentitycolumnorsequenceisexhausted. -359

Table17.Classcode24:InvalidCursorState SQLSTATE

24501 Theidentifiedcursorisnotopen. -501,-507

24502 ThecursoridentifiedinanOPENstatementisalreadyopen. -502,-7055 24504 ThecursoridentifiedintheUPDATE,DELETE,SET,orGETstatementisnot

positionedonarow.

-508

24506 ThestatementidentifiedinthePREPAREisthestatementofanopencursor. -519 24507 FETCHCURRENTwasspecified,butthecurrentrowisdeleted,oravalue

ofanORDERBYcolumnofthecurrentrowhaschanged.

-226

24513 FETCHNEXT,PRIOR,CURRENT,orRELATIVEisnotallowed,becausethe cursorpositionisnotknown.

-227

24514 Apreviouserrorhasdisabledthiscursor. -906

Table18.Classcode25:InvalidTransactionState SQLSTATE

25000 Anupdateoperationisinvalidfortheapplicationexecutionenvironment. -30090 25006 Anupdateoperationisnotvalidbecausethetransactionisread-only. -817 25501 Thestatementisonlyallowedasthefirststatementinaunitofwork. -428

Table19.Classcode26:InvalidSQLStatementIdentifier SQLSTATE

26501 Thestatementidentifieddoesnotexist. -514,-516

26510 ThestatementnamespecifiedinaDECLARECURSORalreadyhasacursor allocatedtoit.

-5023

Table20.Classcode27:TriggeredDataChangeViolation SQLSTATE

27000 AnattemptwasmadetomodifythetargettableoftheMERGEstatementby aconstraintortrigger.

-907

Table21.Classcode28:InvalidAuthorizationSpecification SQLSTATE

28000 Authorizationnameisinvalid. -113,-188,-567

|

(17)

Table22.Classcode2D:InvalidTransactionTermination SQLSTATE

2D522 COMMITandROLLBACKarenotallowedinanATOMICCompound statement.

-774

2D528 DynamicCOMMITorCOMMITONRETURNprocedureisinvalidforthe applicationexecutionenvironment

-426,-30090

2D529 DynamicROLLBACKisinvalidfortheapplicationexecutionenvironment. -427,-30090

Table23.Classcode2E:InvalidConnectionName SQLSTATE

2E000 Connectionnameisinvalid. -113,-188,-251

Table24.Classcode2F:SQLFunctionException SQLSTATE

2F002 TheSQLfunctionattemptedtomodifydata,butthefunctionwasnot definedasMODIFIESSQLDATA.

-577

2F003 Thestatementisnotallowedinafunctionorprocedure. -751 2F004 TheSQLfunctionattemptedtoreaddata,butthefunctionwasnotdefined

asREADSSQLDATA.

-579

2F005 ThefunctiondidnotexecuteaRETURNstatement. -578

Table25.Classcode33:InvalidSQLDescriptorName SQLSTATE

33000 SQLdescriptornameisinvalid. -077

Table26.Classcode34:InvalidCursorName SQLSTATE

34000 Cursornameisinvalid. -504

Table27.Classcode35:InvalidConditionNumber SQLSTATE

35000 Conditionnumberisinvalid. -393

Table28.Classcode36:CursorSensitivityException SQLSTATE

36001 ASENSITIVEcursorcannotbedefinedforthespecifiedselect-statement. -243

||

|

|||

|

(18)

Table29.Classcode38:ExternalFunctionException SQLSTATE

38xxx ValiderrorSQLSTATEsreturnedbyanexternalroutineortrigger. -443 38001 TheexternalroutineisnotallowedtoexecuteSQLstatements. -487 38002 Theexternalroutineattemptedtomodifydata,buttheroutinewasnot

definedasMODIFIESSQLDATA.

-577

38003 Thestatementisnotallowedinaroutine. -751

38004 Theexternalroutineattemptedtoreaddata,buttheroutinewasnotdefined asREADSSQLDATA.

-579

38501 Erroroccurredwhilecallingauser-definedfunction,externalprocedure,or trigger(usingtheSIMPLECALLorSIMPLECALLWITHNULLScalling convention).

-443,-4302

Table30.Classcode39:ExternalFunctionCallException SQLSTATE

39004 AnullvalueisnotallowedforanINorINOUTargumentwhenusing PARAMETERSTYLEGENERALoranargumentthatisaJavaprimitive type.

-470,-20205

Table31.Classcode3B:SavepointException SQLSTATE

3B001 Thesavepointisnotvalid. -880

3B002 Themaximumnumberofsavepointshasbeenreached. -20112

3B501 Aduplicatesavepointnamewasdetected. -881

3B502 ARELEASEorROLLBACKTOSAVEPOINTwasspecified,butasavepoint doesnotexist.

-882

Table32.Classcode3C:AmbiguousCursorName SQLSTATE

3C000 Thecursornameisambiguous. -051

Table33.Classcode3F:InvalidSchema(Collection)Name SQLSTATE

3F000 Theschema(collection)nameisinvalid. -713

Table34.Classcode42:SyntaxErrororAccessRuleViolation SQLSTATE

42501 TheauthorizationIDdoesnothavetheprivilegetoperformthespecified operationontheidentifiedobject.

-551

42502 TheauthorizationIDdoesnothavetheprivilegetoperformtheoperationas specified.

-552

(19)

Table34.Classcode42:SyntaxErrororAccessRuleViolation (continued) SQLSTATE

42506 Ownerauthorizationfailureoccurred. -30053

42601 Acharacter,token,orclauseisinvalidormissing. -007,-011,-029,-097, -104,-109,-115,-128, -199,-441,-491 42602 Acharacterthatisinvalidinanamehasbeendetected. -113,-251 42603 Anunterminatedstringconstanthasbeendetected. -010 42604 Aninvalidnumericorstringconstanthasbeendetected. -103,-105 42605 Thenumberofargumentsspecifiedforascalarfunctionisinvalid. -170 42606 Aninvalidhexadecimalconstanthasbeendetected. -110 42607 AnoperandofacolumnfunctionorCONCAToperatorisinvalid. -112 42609 Alloperandsofanoperatororpredicateareparametermarkers. -417

42610 Aparametermarkerisnotallowed. -184,-418

42611 Thecolumnorargumentdefinitionisinvalid. -106,-604

42612 ThestatementstringisanSQLstatementthatisnotacceptableinthecontext inwhichitispresented.

-084,-142

42613 Clausesaremutuallyexclusive. -628

42614 Aduplicatekeywordorclauseisinvalid. -637

42615 Aninvalidalternativewasdetected. -644

42616 Invalidoptionsarespecified. -5047

42617 Thestatementstringisblankorempty. -198

42618 Ahostvariableisnotallowed. -090,-312,-5012,

-5024 42620 Read-onlySCROLLwasspecifiedwiththeUPDATEclause. -228 42621 Thecheckconstraintgeneratedcolumnexpressionisinvalid. -546

42622 Anameorlabelistoolong. -107

42623 ADEFAULTclausecannotbespecified. -373

42625 ACASEexpressionisinvalid. -580

42629 ParameternamesmustbespecifiedforSQLroutines. -078 42631 AnexpressionmustbespecifiedonaRETURNstatementinanSQL

function.

-057

42633 AnASclauseisrequiredforanargumentofXMLATTRIBUTESor XMLFOREST.

-20227

42634 TheXMLnameisnotvalid. -20275

42635 TheXMLnamespaceprefixisnotvalid. -20276

42701 AduplicatecolumnnameinanINSERTorUPDATEoperationortheSET transition-variablewasdetected.

-121

42702 Acolumnreferenceisambiguous,becauseofduplicatenames. -203

42703 Anundefinedcolumnorparameternamewasdetected. -205,-206,-213, -5001

42704 Anundefinedobjectorconstraintnamewasdetected. -204

42705 Anundefinedserver-namewasdetected. -950

42707 AcolumnnameinORDERBYdoesnotidentifyacolumnoftheresulttable. -208

|

(20)

42709 Aduplicatecolumnnamewasspecifiedinakeycolumnlist. -537 42710 Aduplicateobjectorconstraintnamewasdetected. -456,-601 42711 AduplicatecolumnnamewasdetectedintheobjectdefinitionorALTER

TABLEstatement.

-612

42712 AduplicatetabledesignatorwasdetectedintheFROMclauseor REFERENCINGclauseofaCREATETRIGGERstatement.

-212

42713 Aduplicateobjectwasdetectedinalistoristhesameasanexistingobject. -242

42718 Thelocalservernameisnotdefined. -250

42723 Afunctionwiththesamesignaturealreadyexistsintheschema. -454 42724 Unabletoaccessanexternalprogramusedforauser-definedfunctionora

procedure.

-444,-4300,-4303, -4304,-4306 42725 Aroutineormethodwasreferenceddirectly(notbyeithersignatureorby

specificinstancename),butthereismorethanonespecificinstanceofthat routineormethod.

-476

42726 Duplicatenamesforcommontableexpressionsweredetected. -340 42732 AduplicateschemanameintheSETCURRENTPATHstatementwas

detected.

-585

42733 Aprocedurewiththespecifiednamecannotbeaddedtotheschemabecause theprocedureoverloadingisnotallowedinthisdatabaseandthereis alreadyaprocedurewiththesamenameintheschema.

-484

42734 Aduplicateparameter-name,SQLvariablename,label,orcondition-name wasdetected.

-590

42736 ThelabelspecifiedontheGOTO,ITERATE,orLEAVEstatementisnot foundornotvalid.

-779

42737 Theconditionspecifiedisnotdefined. -781

42738 AduplicatecolumnnameorunnamedcolumnwasspecifiedinaDECLARE CURSORstatementofaFORstatement.

-783

42747 ThesamedescriptoritemwasspecifiedmorethanonceinthesameSET DESCRIPTORstatement.

-20299

42802 Thenumberofinsertorupdatevaluesisnotthesameasthenumberof columns.

-117

42803 AcolumnreferenceintheSELECTorHAVINGclauseisinvalid,becauseitis notagroupingcolumn;oracolumnreferenceintheGROUPBYclauseis invalid.

-119,-122

42804 TheresultexpressionsinaCASEexpressionarenotcompatible. -581 42805 AnintegerintheORDERBYclausedoesnotidentifyacolumnoftheresult

table.

-125

42806 Avaluecannotbeassignedtoahostvariable,becausethedatatypesarenot compatible.

-303

42807 TheINSERT,UPDATE,orDELETEisnotpermittedonthisobject. -150,-155 42808 AcolumnidentifiedintheINSERTorUPDATEoperationcannotbe

updated.

-151

42809 Theidentifiedobjectisnotthetypeofobjecttowhichthestatementapplies. -152,-156,-159 42810 AbasetableisnotidentifiedinaFOREIGNKEYclause. -157

|

(21)

42811 Thenumberofcolumnsspecifiedisnotthesameasthenumberofcolumns intheSELECTclause.

-158

42813 WITHCHECKOPTIONcannotbeusedforthespecifiedview. -160 42814 Thecolumncannotbedropped,becauseitistheonlycolumninthetable. -195

42815 Thedatatype,length,scale,value,orCCSIDisinvalid. -060,-171,-451,-713, -846,-5005

42816 Adatetimevalueordurationinanexpressionisinvalid. -182,-187 42817 Thecolumncannotbedropped,becauseRESTRICTwasspecifiedandaview

orconstraintisdependentonthecolumnorthecolumnispartofa partitioningkey.

-196

42818 Theoperandsofanoperatororfunctionarenotcompatible. -131,-401 42819 Anoperandofanarithmeticoperationoranoperandofafunctionthat

requiresanumberisnotanumber.

-402

42820 Anumericconstantistoolong,orithasavaluethatisnotwithintherange ofitsdatatype.

-405,-410

42821 Adatatypeforanassignmenttoacolumnorvariableisnotcompatiblewith thedatatype.

-408

42822 AnexpressionintheORDERBYclauseorGROUPBYclauseisnotvalid. -214 42823 Multiplecolumnsarereturnedfromasubquerythatonlyallowsonecolumn. -412 42824 AnoperandofLIKEisnotastring,orthefirstoperandisnotacolumn. -132,-414 42825 TherowsofUNION,INTERSECT,EXCEPT,orVALUESdonothave

compatiblecolumns.

-415

42826 TherowsofUNION,INTERSECT,EXCEPT,orVALUESdonothavethe samenumberofcolumns.

-421

42827 ThetableidentifiedintheUPDATEorDELETEisnotthesametable designatedbythecursor.

-509

42828 ThetabledesignatedbythecursoroftheUPDATEorDELETEstatement cannotbemodified,orthecursorisreadonly.

-510,-520

42829 FORUPDATEOFisinvalid,becausetheresulttabledesignatedbythe cursorcannotbemodified.

-511

42830 Theforeignkeydoesnotconformtothedescriptionoftheparentkey. -538

42832 Theoperationisnotallowedonsystemobjects. -607

42833 Thequalifiedobjectnameisinconsistentwiththenamingoption. -5016 42834 SETNULLcannotbespecified,becausenocolumnoftheforeignkeycanbe

assignedthenullvalue.

-629

42835 Cyclicreferencescannotbespecifiedbetweennamedderivedtables. -341 42836 Thespecificationofarecursive,namedderivedtableisinvalid. -345,-346 42837 Thecolumncannotbealtered,becauseitsattributesarenotcompatiblewith

thecurrentcolumnattributes.

-190

42841 Aparametermarkercannotbeauser-definedtypeorreferencetype. -432 42842 Acolumnorparameterdefinitionisinvalid,becauseaspecifiedoptionis

inconsistentwiththecolumndescription.

-683

42845 AninvaliduseofaNOTDETERMINISTICorEXTERNALACTIONfunction wasdetected.

-583

|

(22)

42846 Castfromsourcetypetotargettypeisnotsupported. -461 42847 AnOVRDBFcommandwasissuedforoneofthereferencedfiles,butoneof

theparametersisnotvalidforSQL.

-7002

42848 IsolationlevelCSWITHKEEPLOCKSisnotallowed. -194

42850 AlogicalfileisinvalidinCREATEVIEW. -7010

42851 Areferencedfileisnotatable,view,orphysicalfile. -7011 42852 TheprivilegesspecifiedinGRANTorREVOKEareinvalidorinconsistent.

(Forexample,GRANTALTERonaview.)

-557

42854 Aresultcolumndatatypeintheselectlistisnotcompatiblewiththedefined typeinatypedviewormaterializedquerytabledefinition.

-20055

42855 TheassignmentoftheLOBtothishostvariableisnotallowed.Thetarget hostvariableforallfetchesofthisLOBvalueforThiscursormustbea locatororLOBvariable.

-392

42857 Areferencedfilehasmorethanoneformat. -7003

42858 Operationcannotbeappliedtothespecifiedobject. -7001 42860 Theconstraintcannotbedroppedbecauseitisenforcingaprimarykeyor

ROWID.

-784

42862 Anextendeddynamicstatementcannotbeexecutedagainstanon-extended dynamicpackage.

-827

42863 AnundefinedhostvariableinREXXhasbeendetected. -306 42866 ThedatatypeineithertheRETURNSclauseortheCASTFROMclausein

theCREATEFUNCTIONstatementisnotappropriateforthedatatype returnedfromthesourcedfunctionorRETURNstatementinthefunction body.

-475

42872 FETCHstatementclausesareincompatiblewiththecursordefinition. -225 42873 Aninvalidnumberofrowswasspecifiedinamultiple-rowFETCHor

multiple-rowINSERT.

-221

42874 ALWCPYDTA(*NO)wasspecified,butacopyisnecessarytoimplementthe select-statement.

-527

42875 Theschema-nameportionofaqualifiednamemustbethesamenameasthe schemaname.

-5051

42876 DifferentCCSIDsforkeysinCREATEINDEXareonlyallowedwitha*HEX sortsequence.

-7024

42877 Thecolumnnamecannotbequalified. -197

42878 AninvalidfunctionorprocedurenamewasusedwiththeEXTERNAL keyword.

-449

42879 ThedatatypeofoneormoreinputparametersintheCREATEFUNCTION statementisnotappropriateforthecorrespondingdatatypeinthesource function.

-492

42880 TheCASTTOandCASTFROMdatatypesareincompatible,orwould alwaysresultintruncationofafixedstring.

-453

42881 Invaliduseofafunction. -391

42882 Thespecificinstancenamequalifierisnotequaltothefunctionname qualifier.

-455

42883 Nofunctionormethodwasfoundwithamatchingsignature. -458

(23)

42884 Noroutinewasfoundwiththespecifiednameandcompatiblearguments. -440 42885 ThenumberofinputparametersspecifiedonaCREATEFUNCTION

statementdoesnotmatchthenumberprovidedbythefunctionnamedinthe SOURCEclause.

-483

42886 TheIN,OUT,orINOUTparameterattributesdonotmatch. -469 42887 Thefunctionisnotvalidinthecontextwhereitoccurs. -390

42888 Thetabledoesnothaveaprimarykey. -539

42889 Thetablealreadyhasaprimarykey. -624

42890 Acolumnlistwasspecifiedinthereferencesclause,buttheidentifiedparent tabledoesnothaveauniqueconstraintwiththespecifiedcolumnnames.

-573

42891 AduplicateUNIQUEconstraintalreadyexists. -541

42892 Thereferentialconstraintandtriggerarenotallowed,becausetheDELETE ruleandtriggereventarenotcompatible.

-675

42893 Theobjectorconstraintcannotbedroppedorauthoritiescannotberevoked fromtheobject,becauseotherobjectsaredependentonit.

-478,-616

42894 Thevalueofacolumnorsequenceattributeisinvalid. -574 42895 ForstaticSQL,aninputhostvariablecannotbeused,becauseitsdatatypeis

notcompatiblewiththeparameterofaprocedureoruser-definedfunction.

-301

42896 TheASPnumberisinvalid. -7026

42898 Aninvalidcorrelatedreferenceortransitiontablewasdetectedinatrigger. -696 42899 Correlatedreferencesandcolumnnamesarenotallowedfortriggered

actionswiththeFOREACHSTATEMENTclause.

-697

428A1 Unabletoaccessafilereferencedbyafilereferencevariable. -452

428B3 AninvalidSQLSTATEwasspecified. -435

428B7 AnumberspecifiedinanSQLstatementisoutofthevalidrange. -490

428B8 Thenamespecifiedonarenameisnotvalid. -7029

428BA WITHOUTRETURNcursorsmustnotbespecifiedinSETRESULTSETS. -20236 428C1 OnlyoneROWIDorIDENTITYcolumncanbespecifiedforatable. -372 428C4 Thenumberofelementsoneachsideofthepredicateoperatorisnotthe

same.

-216

428C7 AROWIDorreferencecolumnspecificationisnotvalid. -771 428C9 AROWIDorIDENTITYcolumncannotbespecifiedasthetargetcolumnof

anINSERTorUPDATE.

-798

428D1 UnabletoaccessafilereferencedbyaDATALINKvalue. -358 428D2 ASLOCATORcannotbespecifiedforanon-LOBparameter. -398 428D4 AcursorspecifiedinaFORstatementcannotbereferencedinanOPEN,

CLOSE,orFETCHstatement.

-776

428D5 Theendinglabeldoesnotmatchthebeginninglabel. -778 428D6 UNDOisnotallowedforNOTATOMICcompoundstatements. -780

428D7 Theconditionvalueisnotallowed. -782

428D8 Thesqlcodeorsqlstatevariabledeclarationisnotvalid. -785

428DE ThePAGESIZEvalueisnotsupported. -1583

|

(24)

428EC Thefullselectspecifiedforthematerializedquerytableisnotvalid. -20058 428EK Thequalifierforadeclaredglobaltemporarytablenameoranindexona

declaredglobaltemporarytablemustbeSESSION.

-079

428EW Thetablecannotbeconvertedtoorfromamaterializedquerytable. -20093 428F1 AnSQLTABLEfunctionmustreturnatableresult. -20120 428F2 AnintegerexpressionmustbespecifiedonaRETURNstatementinanSQL

procedure.

-058

428F9 Asequenceexpressioncannotbespecifiedinthiscontext. -348

428FA Thescaleofthedecimalnumbermustbezero. -336

428FC Thelengthoftheencryptionpasswordisnotvalid. -20144 428FD Thepasswordusedfordecryptiondoesnotmatchthepasswordusedto

encryptthedata.

-20145

428FE ThedataisnotaresultoftheENCRYPTfunction. -20146 428FI TheORDEROFclausewasspecified,butthereferencedtabledesignatoris

notordered.

-20214

428FP OnlyoneINSTEADOFtriggerisallowedforeachkindofoperationona view.

-20178

428FQ AnINSTEADOFtriggermustnotspecifyaviewthatisdefinedusingWITH CHECKOPTION,aviewthatisdefinedonanotherviewthatisdefined WITHCHECKOPTION,oraviewthatisnestedinaviewthatisdefined withtheWITHROWMOVEMENTclause.

-20179

428FR Acolumncannotbealteredasspecified. -20180

428FT Thetableisnotcompatiblewiththespecifieddatapartitioningoperation. -20183 428FY Acolumncannotbeadded,dropped,oralteredinamaterializedquery

table.

-20235

428G0 Alogicalfilepreventsthealterofthepartitionattributes. -20246 428G2 Thelastdatapartitioncannotbedroppedfromthetable. -20251 42903 InvaliduseofanaggregatefunctionorOLAPfunction. -120 42904 TheSQLprocedurewasnotcreatedbecauseofacompilationerror. -7032 42906 AcolumnfunctioninasubqueryofaHAVINGclauseincludesan

expressionthatappliesanoperatortoacorrelatedreference.

-133

42907 Thestringistoolong. -134

42908 Thestatementdoesnotincludearequiredcolumnlist. -153,-343 42910 ThestatementisnotallowedinaCompoundstatement. -775 42911 Adecimaldivideoperationisinvalid,becausetheresultwouldhavea

negativescale.

-419

42912 Acolumncannotbeupdated,becauseitisnotidentifiedintheUPDATE clauseoftheselect-statementofthecursor.

-503

42914 TheDELETEisinvalid,becauseatablereferencedinasubquerycanbe affectedbytheoperation.

-536

42917 Theobjectcannotbeexplicitlydroppedoraltered. -658 42918 Auser-defineddatatypecannotbecreatedwithasystem-defineddatatype

name(forexample,INTEGER).

-473

|

(25)

42919 Nestedcompoundstatementsarenotallowed. -777

42922 DROPSCHEMAcannotbeexecutedundercommitmentcontrol. -5003 42923 Programorpackagemustbere-createdtoreferenceanalias-name. -7033 42924 Analiasresolvedtoanotheraliasratherthanatableorviewattheremote

location.

-513

42925 RecursivenamedderivedtablescannotspecifySELECTDISTINCTandmust specifyUNIONALL.

-342

42926 LocatorsarenotallowedwithCOMMIT(*NONE). -7034

42928 WITHEMPTYTABLEcannotbespecified. -1596

42929 FORALLPARTITIONSisnotallowedforanencodedvectorindex. -20243 42930 ThesamecolumnwasidentifiedinFORUPDATEOFandORDERBY. -5021 42932 Theprogrampreparationassumptionsareincorrect. -30052

42937 Theparametermustnothaveasubtypeofmixed. -192

42939 Thenamecannotbeused,becausethespecifiedidentifierisreservedfor systemuse.

-457,-707

42944 TheauthorizationIDcannotbebothanownerandprimarygroupowner. -7028 42961 Theservernamespecifieddoesnotmatchthecurrentserver. -114 42962 Alongcolumn,LOBcolumn,structuredtypecolumnordatalinkcolumn

cannotbeusedinanindex,akey,oraconstraint.

-350

42969 Thepackagewasnotcreatedandthecurrentunitofworkwasrolledback, becauseofinternallimitationsoraninvalidsectionnumber.

-7020

42970 COMMITHOLDorROLLBACKHOLDisonlyallowedtoaDB2UDBfor iSeriesapplicationserver.

-7018

42971 SQLstatementscannotbeexecutedundercommitmentcontrol,because commitmentcontrolisalreadyactivetoanotherrelationaldatabase.

-7017

42972 Anexpressioninajoin-conditionorONclauseofaMERGEstatement referencescolumnsinmorethanoneoftheoperandtables.

-338

42977 TheauthorizationIDcannotbechangedwhenconnectingtothelocalserver. -7022

42978 Anindicatorvariableisnotasmallinteger. -080

42981 CREATESCHEMAisnotallowedifchangesarependingintheunitofwork. -7941 42984 Theprivilegecannotbegrantedtotheview,because*OBJOPRor*OBJMGT

authorityexistsonadependentviewortable,andthegranteedoesnothave

*ALLOBJorthespecifiedprivilegeonthedependenttableorview.

-7027

42985 Thestatementisnotallowedinaroutine. -577,-579,-751

42987 Thestatementisnotallowedinatrigger. -751

42990 Auniqueindexoruniqueconstraintisnotallowedbecausethekeycolumns arenotasupersetofthepartitionedkeycolumns.

-270

42995 Therequestedfunctiondoesnotapplytoglobaltemporarytables. -526 42996 Thepartitionkeycannotbeadatetimeorfloating-pointcolumn. -328 42998 Areferentialconstraintisnotallowedbecausetheforeignkeycolumnsare

notasupersetofthepartitionedkeycolumnsorthenodegroupisnotthe sameastheparenttable.

-256

42999 Thequeryisnotallowedonadistributedtableorview. -255

|

(26)

429B6 Rowsfromadistributedtablecannotberedistributedbecausethetable containsadatalinkcolumnwithFILELINKCONTROL.

-7037

429B7 AreferentialconstraintwithadeleteruleofCASCADEisnotallowedona tablewithaDataLinkcolumnwithFILELINKCONTROL.

-7038

429BD RETURNmustbethelastSQLstatementoftheatomiccompoundstatement withinanSQLrowortablefunction.

-20148

429BH Apartitionedtablecannotcontainanidentitycolumn. -20247 429BM TheICUsortsequencecannotbeusedinthiscontext. -20268

Table35.Classcode44:WITHCHECKOPTIONViolation SQLSTATE

44000 TheINSERTorUPDATEisnotallowed,becausearesultingrowdoesnot satisfytheviewdefinition.

-161

Table36.Classcode46:JavaErrors SQLSTATE

46001 TheURLspecifiedonaninstallorreplaceofajarproceduredidnotidentify avalidjarfile.

-20200

46002 Thejarnamespecifiedontheinstall,replace,orremoveofaJavaprocedure isnotvalid.

-20201

46003 Thejarfilecannotberemoved,aclassisinusebyaprocedure. -20202 46007 AJavafunctionhasaJavamethodwithaninvalidsignature. -20203 46008 AJavafunctioncannotmaptoasingleJavamethod. -20204 46501 Theinstallorremovejarprocedurefor″<jar-id>″specifiedtheuseofa

deploymentdescriptor.

-20207

Table37.Classcode51:InvalidApplicationState SQLSTATE

51002 ThepackagecorrespondingtoanSQLstatementexecutionrequestwasnot found.

-805

51003 Consistencytokensdonotmatch. -818

51004 AnaddressintheSQLDAisinvalid. -822

51009 COMMITorROLLBACKisnotallowed,becausecommitmentcontrolhas notbeenstarted.

-7007

51015 Anattemptwasmadetoexecuteasectionthatwasfoundtobeinerrorat bindtime.

-525

51021 SQLstatementscannotbeexecuteduntiltheapplicationprocessexecutesa rollbackoperation.

-918

51035 APREVVALexpressioncannotbeusedbecauseavaluehasnotbeen generatedforthesequenceyetinthissession.

-845

51036 Animplicitconnecttoaremoteserverisnotallowedbecauseasavepointis outstanding.

-20110

(27)

Table37.Classcode51:InvalidApplicationState (continued) SQLSTATE

51037 Theoperationisnotallowedbecauseatriggerhasbeenmarkedinoperative. -7048

51039 TheENCRYPTIONPASSWORDvalueisnotset. -20143

Table38.Classcode53:InvalidOperandorInconsistentSpecification SQLSTATE

53038 Thenumberofkeylimitvaluesiszeroorgreaterthanthenumberof columnsinthekey.

-663

53039 ThePARTclauseoftheALTERstatementisomittedorinvalid. -665 53045 Thedatatypeofthekeylimitconstantisnotthesameasthedatatypeof

thecolumn.

-678

Table39.Classcode54:SQLorProductLimitExceeded SQLSTATE

54001 Thestatementistoolongortoocomplex. -101

54002 Astringconstantistoolong. -102

54004 ThestatementhastoomanytablenamesortoomanyitemsinaSELECTor INSERTlist.

-129,-840

54005 Thesortkeyistoolong,orhastoomanycolumns. -136

54006 Theresultofconcatenationistoolong. -137

54008 Thekeyistoolong,acolumnofthekeyistoolong,orthekeyhastoo manycolumns.

-602,-613,-614,-631

54009 ToomanyuserswerespecifiedinGRANTorREVOKE. -5017

54010 Therecordlengthofthetableistoolong. -101

54011 Toomanycolumnswerespecifiedforatable,view,ortablefunction. -101,-680

54018 Therowistoolong. -809

54019 Themaximumnumberoflatedescriptorshasbeenexceeded,probably becausetoomanydifferentCCSIDswereused.

-871

54021 Toomanyconstraints,orthesizeoftheconstraintistoolarge. -642 54023 Thelimitforthenumberofparametersorargumentsforafunctionora

procedurehasbeenexceeded.

-442,-448

54028 ThemaximumnumberofconcurrentLOBhandleshasbeenreached. -429

54035 Aninternalobjectlimitwasexceeded. -7049

54038 Themaximumdepthofnestedroutinesortriggerswasexceeded. -724 54044 Amultiple-byte(UCS-2)sortsequencetablecannotbesupportedinDRDA^®

becauseitistoolarge.

-7031

54054 Thecombinationofthenumberoftablespacepartitionsandthe correspondinglengthofthepartitioninglimitkeyittoolarge.

-4701

54057 AnXMLelementname,attributename,namespaceprefixorURListoo long.

-20326

|

(28)

Table40.Classcode55:ObjectNotinPrerequisiteState SQLSTATE

55005 RecursionisonlysupportedtoaDB2UDBforiSeriesapplicationserver. -145 55006 Theobjectcannotbedropped,becauseitiscurrentlyinusebythesame

applicationprocess.

-615,-950

55007 Theobjectcannotbealtered,becauseitiscurrentlyinusebythesame applicationprocess.

-951

55018 Theschemacannotbedropped,becauseitisinthelibrarylist. -7006 55019 Theobjectisinaninvalidstatefortheoperation. -7008,-20054 55029 Localprogramattemptedtoconnecttoaremotedatabase. -862

55042 Thealiasisnotallowedbecauseitidentifiesasinglememberofamultiple memberfile.

-7030

55048 Encrypteddatacannotbeencrypted. -20147

55050 Anobjectcannotbecreatedintoaprotectedschema. -7052 55058 TheDEBUGMODEcannotbechangedforaroutinethatwascreatedwith

DISABLEDEBUGMODE.

-20313

Table41.Classcode56:MiscellaneousSQLorProductError SQLSTATE

56016 Therangesspecifiedfordatapartitionsarenotvalid. -636 56084 AnunsupportedSQLTYPEwasencounteredinaselectlistorinputlist. -351,-352

56095 Abindoptionisinvalid. -30104

560BF Theencryptionanddecryptionfacilityhasnotbeeninstalled. -20223 560C4 TheoptionisnotvalidfortheARDinterface. -5027,-5028

Table42.Classcode57:ResourceNotAvailableorOperatorIntervention SQLSTATE

57005 Thestatementcannotbeexecuted,becauseautilityoragovernortimelimit wasexceeded.

-666

57006 Theobjectcannotbecreated,becauseaDROPorCREATEispending. -679 57007 Theobjectcannotbeused,becauseanoperationispending. -910

57011 Virtualstorageordatabaseresourceisnotavailable. -904,-971,-7053 57012 Anon-databaseresourceisnotavailable.Thiswillnotaffectthesuccessful

executionofsubsequentstatements.

-30040

57013 Anon-databaseresourceisnotavailable.Thiswillaffectthesuccessful executionofsubsequentstatements.

-30041

57014 Processingwascanceledasrequested. -952

57017 Characterconversionisnotdefined. -332

57033 Deadlockortimeoutoccurredwithoutautomaticrollback. -913

57042 DDMrecursionhasoccurred. -30001

57043 AlocalSQLapplicationprogramcannotbeexecutedonanapplication server.

-7021

|

(29)

Table42.Classcode57:ResourceNotAvailableorOperatorIntervention (continued) SQLSTATE

57050 Thefileserverisnotcurrentlyavailable. -357

Table43.Classcode58:SystemError SQLSTATE

58002 Anexithasreturnedanerrororinvaliddata. -7054

58003 Aninvalidsectionnumberwasdetected. -144

58004 Asystemerror(thatdoesnotnecessarilyprecludethesuccessfulexecutionof subsequentSQLstatements)occurred.

-901,-4301

58008 Executionfailedduetoadistributionprotocolerrorthatwillnotaffectthe successfulexecutionofsubsequentDDMcommandsorSQLstatements.

-30000

58009 Executionfailedduetoadistributionprotocolerrorthatcauseddeallocation oftheconversation.

-30020

58010 Executionfailedduetoadistributionprotocolerrorthatwillaffectthe successfulexecutionofsubsequentDDMcommandsorSQLstatements.

-30021

58011 TheDDMcommandisinvalidwhilethebindprocessisinprogress. -30050 58012 Thebindprocesswiththespecifiedpackagenameandconsistencytokenis

notactive.

-30051

58014 TheDDMcommandisnotsupported. -30070

58015 TheDDMobjectisnotsupported. -30071

58016 TheDDMparameterisnotsupported. -30072

58017 TheDDMparametervalueisnotsupported. -30073

58018 TheDDMreplymessageisnotsupported. -30074

58028 Thecommitoperationfailed,becausearesourceintheunitofworkwasnot abletocommititsresources.

-175

58033 Anunexpectederroroccurredwhileattemptingtoaccessaclientdriver. -969

SQL message concepts

SQLmessagesare displayedwhenaDB2 UniversalDatabaseforiSeriesreturnsanerrororwarningcode totheapplicationthatusesit.

Tofindaspecific message,SQLCODE,orSQLSTATE, trytheSQLmessagefinder.

UseDisplayMessageDescription(DSPMSGD)todisplayamessage description

DetaileddescriptionsofallDB2UDB foriSeriesmessages,includingSQLCODEs,areavailablein messagefileQSQLMSG.YoucandisplayandprintthemfromtheDisplayMessageDescriptiondisplay.

TheCLcommandtodisplaythemessagedescriptionisDSPMSGD.Thiscommandshowsyouboththe firstlevelandthesecondleveltextforthemessage.Thefirstlevelisa short,singlesentenceversionof themessage.Thesecondlevelgenerallydescribesthereasoninmore detailandprovides suggested solutions.

ToshowthemessagethatcorrespondstoSQLCODE0204,typethefollowingcommand:

DSPMSGD RANGE(SQL0204) MSGF(QSYS/QSQLMSG)

|

(30)

“ListingofSQLmessages”

ThesetableslistSQLmessages. Usethesetablestofind messagetext,causetext,recovery text,and corresponding SQLCODEsandSQLSTATEs.

Listing of SQL messages

ThesetableslistSQLmessages.Usethesetablestofindmessagetext, causetext, recoverytext, and correspondingSQLCODEsand SQLSTATEs.

Youcanusethefollowingtableswithboth positiveand negativeSQLCODEs.Taketheabsolutevalue of theSQLCODE,then appendittothelettersSQL(forSQLCODEslessthan10000) orthelettersSQ(for SQLCODEsgreaterthanorequal to10000)todeterminethemessageidentifier.EachSQLCODE correspondstooneormoreSQLSTATEs.

SQ20054

MessageText: File&1in&2hasDataLinksinlinkpendingmode.

CauseText: Theaccessedfile&1hasDataLinksinlinkpendingmodeforreasoncode&3.Thereason codesareasfollows:21-ThestatusoftheDataLinkscannotbeverified.Afilecannotbe usedforINSERTandUPDATEstatementswhileinlinkpendingmode.

RecoveryText: UsetheWRKPFDL(WorkwithPhysicalFileDataLinks)commandtodeterminewhichfiles haveDataLinksinlinkpendingmode.Resolvethependinglinksandtrytherequestagain.

SQLCODEor SQLCODEs:

-20054

SQLSTATEor SQLSTATEs:

55019

SQ20055

MessageText: Resultcolumnnotcompatiblewithcolumn&1inmaterializedquerytable.

CauseText: Thedatatypeofaresultcolumnintheselectlistisnotcompatiblewithcolumn&1inthe materializedquerytable.

RecoveryText: EnsurethatthedatatypesofallSELECTresultcolumnsarecompatiblewiththe correspondingexistingcolumnsofthematerializedquerytable.

SQLCODEor SQLCODEs:

-20055

42854

SQ20058

MessageText: Subselectformaterializedquerytable&1notvalidforreasoncode&3.

(31)

SQ20058

CauseText: &1in&2cannotbecreatedoralteredbecausetheSELECTstatementcontainsareferenceor expressionthatisnotvalid.Reasoncodesandtheirmeaningsare:

v 1-Referstoanothermaterializedquerytableortoaviewthatreferstoamaterialized querytable.

v 2-Referstoadeclaredtemporarytable,atableinQTEMP,aprogram-describedfile,ora non-SQLlogicalfileintheFROMclause.

v 3-ReferstoaVIEWthatcontainsaninvaliditemforamaterializedquerytable.

v 4-ContainsanexpressionwithaDataLinkoradistincttypebasedonaDataLinkwhere theDataLinkisFILELINKCONTROL.

v

6-Referstoafunctionthathasanexternalactionorthatisnotdeterministic.

v

8-Referstoaspecialregister.

v 9-ReferstoresultcolumnthatisanotanSQLdatatype,suchasbinarywithprecision, DBCS-ONLY,orDBCS-EITHER.

RecoveryText: Basedonthereasoncode,correcttheSELECTstatementforthematerializedquerytable.

Trytherequestagain.

SQLCODEor SQLCODEs:

-20058

428EC

SQ20093

MessageText: AttempttoALTERtable&1in&2failed.Reasoncode&3.

CauseText: TheALTERrequestinvolvingamaterializedquerytablecannotbecompleted.Reason codesandtheirmeaningsare:

v 2-TheALTERspecifiedDROPMATERIALIZEDQUERYorALTERMATERIALIZED QUERY,butthespecifiedtableisnotamaterializedquerytable.

v 4-Thetablehasoneormoretriggersdefined.

v 8-Thetableisreferencedinthedefinitionofanothermaterializedquerytable.

v 9-Thetableisreferenceddirectlyorindirectly(suchasthroughaview)intheSELECT statement.

v 10-TheALTERspecifiedADDMATERIALIZEDQUERY,butthespecifiedtableis alreadyamaterializedquerytable.

v

11-Thenumberofcolumnsintheexistingtabledoesnotmatchthenumberofcolumns fortheresultoftheSELECTstatement.

v 12-Columndatatypesintheexistingtabledonotmatchthecolumndatatypesforthe resultoftheSELECTstatement.

v 16-TheALTERspecifiedADDMATERIALIZEDQUERY,butthespecifiedtablehas morethanonemember.

RecoveryText: ChangetheALTERstatement,specifyadifferenttable,orchangetheSELECTstatementto specifydifferentresultcolumns.Trytherequestagain.

SQLCODEor SQLCODEs:

-20093

428EW

SQ20110

MessageText: Cannotimplicitlyconnecttoaremotesitewithasavepointoutstanding.

CauseText: ThestatementreferencedanobjectataremoteDBMSwhenanactivesavepointexists.

(32)

SQ20110

RecoveryText: Animplicitconnecttoaremoteserverisnotallowedbecauseasavepointisoutstanding.

IssueaRELEASETOSAVEPOINTorROLLBACKTOSAVEPOINTbeforeretryingthe statement.Committingorrollingbackthetransactionwillalsoreleaseexistingsavepoints.

SQLCODEor SQLCODEs:

-20110

51036

SQ20112

MessageText: Themaximumnumberofsavepointshasbeenreached.

CauseText: Aresourcelimithasbeenexceeded.

RecoveryText: IssueaRELEASETOSAVEPOINTorROLLBACKTOSAVEPOINTbeforecreating additionalsavepoints.YoumayissueaCOMMITorROLLBACKtoreleaseexisting savepoints.

SQLCODEor SQLCODEs:

-20112

3B002

SQ20120

MessageText: SQLTABLEfunctionmustreturnatableresult.

CauseText: TheRETURNstatementinanSQLTABLEfunctionmustreturnatableresult.

RecoveryText: SpecifyaqueryintheRETURNstatementofatablefunction.Trytherequestagain.

SQLCODEor SQLCODEs:

-20120

428F1

SQ20141

MessageText: Truncationofvaluewithlength&1occurredfor&2.

CauseText: Onassignmentofavaluetoahostvariableorparameter,truncationoccurredandthe lengthofthevaluethatwastruncatedistoolargetobereturnedintheindicatorvariable.

Thissituationcanoccurwhentruncationoccursonassignmentof:

v Avaluetoaparameterofaremotestoredprocedureandthevaluebeingtruncatedis greaterthan127bytes.Inthiscase,theindicatorvariablewillcontainavalueof127.

v ALOBvaluetoahostvariableandthevaluebeingtruncatedisgreaterthan32767bytes.

Inthiscase,theindicatorvariablewillcontainavalueof32767.

Inthesecasestheactuallengthofthetruncatedvaluecannotbereturnedtotheapplication usingtheindicatorvariable.Theactuallengthofthevalueisreturnedasmessagetoken length.

RecoveryText: Changethelengthofthehostvariableorparameterandtrytherequestagain.

SQLCODEor SQLCODEs:

+20141

01004

(33)

SQ20143

MessageText: Encryptionordecryptionfunctionfailed.

CauseText: Anencryptionordecryptionfunctionfailedbecausetheencryptionpasswordvaluewas notset.

RecoveryText: UsetheSETENCRYPTIONPASSWORDstatementtosetthepasswordtobeusedfor encryptionanddecryptionfunctions.Thepasswordcanalsobespecifiedasanargumentof theencryptionanddecryptionfunctions.

SQLCODEor SQLCODEs:

-20143

51039

SQ20144

MessageText: Encryptionpasswordlengthnotvalid.

CauseText: Thelengthoftheencryptionpasswordmustbefrom6to127characters.

RecoveryText: Correctthelengthofthepasswordvalue.Submittherequestagain.

SQLCODEor SQLCODEs:

-20144

428FC

SQ20145

MessageText: Thedecryptionfunctionfailed.

CauseText: Thepasswordusedfordecryptiondoesnotmatchthepasswordusedtoencryptthedata.

Thedatamustbedecryptedusingthesamepasswordthatwasusedtoencryptthedata.

RecoveryText: Ensurethatthesamepasswordisusedtoencryptanddecryptthedata.

SQLCODEor SQLCODEs:

-20145

428FD

SQ20146

MessageText: Thedecryptionfunctionfailed.Thedataisnotencrypted.

CauseText: ThedatamustbearesultoftheENCRYPT,ENCRYPT_RC2,orENCRYPT_TDESfunction.

RecoveryText: EnsurethatthedataisaresultoftheENCRYPT,ENCRYPT_RC2,orENCRYPT_TDES function.Datapassedtoadecryptionfunctionmustbeencrypteddata.

SQLCODEor SQLCODEs:

-20146

428FE

SQ20147

MessageText: TheENCRYPTfunctionfailed.Dataisalreadyencrypted.

CauseText: Anattemptwasmadetoencryptdatathatisalreadyencrypted.

RecoveryText: Ensurethatthedatahasnotalreadybeenencrypted.

(34)

SQ20147 SQLCODEor SQLCODEs:

-20147

55048

SQ20148

MessageText: RETURNstatementmustbespecifiedinfunction&1in&2.

CauseText: ARETURNstatementmustbespecifiedinanSQLfunction.

RecoveryText: SpecifyaRETURNstatement.Trytherequestagain.

SQLCODEor SQLCODEs:

-20148

429BD

SQ20178

MessageText: INSTEADOFtriggernotcreatedforview&1.

CauseText: View&1in&2alreadyhasanINSTEADOFtriggerdefinedforthespecifiedoperation.A viewcanhaveonlyoneINSTEADOFtriggerdefinedforeachoftheINSERT,UPDATE, andDELETEoperations.

RecoveryText: Droptheexistingtrigger.Combinethefunctionfortheexistingtriggerandthenewtrigger intoasingletrigger.Createthetriggeragain.

SQLCODEor SQLCODEs:

-20178

428FP

SQ20179

MessageText: Triggercannotbecreatedforview&1thatusesaWITHCHECKOPTION.

CauseText: View&1in&2specifiesaWITHCHECKOPTIONorisdependentonaviewwhichhasa WITHCHECKOPTION.AnINSTEADOFtriggercannotbedefinedforaviewthathasa WITHCHECKOPTION.

RecoveryText: RemovetheWITHCHECKOPTIONfromtheview.

SQLCODEor SQLCODEs:

-20179

428FQ

SQ20180

MessageText: Column&1cannotbealtered.

CauseText: Column&1isapartitioningkeycolumnfortable&2in&3andcannotbealtered.

RecoveryText: Changethetabletoanon-partitionedtablebeforeattemptingtoalterthecolumnorremove theALTERCOLUMNclausefromthestatement.Trytherequestagain.

SQLCODEor SQLCODEs:

-20180

428FR

||

|

||

|

||

|

||

|

(35)

SQ20183

MessageText: ALTERTABLEpartitioningclausenotvalidfor&1.

CauseText: Table&1in&2cannotbealtered.Oneofthefollowingerrorshasoccurred:

v ADDPARTITIONBYwasspecifiedonanALTERTABLEstatement,butthetableis alreadyapartitionedtableorisaphysicalfile.

v ADDPARTITION,ALTERPARTITION,orDROPPARTITIONwasspecifiedonan ALTERTABLEstatement,butthetableisnotapartitionedtable.

v DROPPARTITIONofaRANGEpartitionwasspecifiedontheALTERTABLEstatement, butthePRESERVEROWSclausewasused.ThePRESERVEROWSclauseisonlyvalid forpartitionedtablesthathaveHASHpartitioning.

RecoveryText: Changethestatementtoonethatisvalidorspecifyadifferenttable.Trytherequestagain.

SQLCODEor SQLCODEs:

-20183

428FT

SQ20200

MessageText: Theinstallorreplaceof&1in&2failedbecause&3couldnotbelocated.

CauseText: TheURLspecifiedontheinstallorreplacejarproceduredidnotidentifyavalidjarfile.

RecoveryText: ReissuetheinstallorreplacejarprocedurewithaURLthatidentifiesavalidjarfile.

SQLCODEor SQLCODEs:

-20200

46001

SQ20201

MessageText: Theinstall,replace,orremoveof&1in&2failedbecausethejarnameisnotvalid.

CauseText: Thejarnamespecifiedontheinstall,replace,orremovejarprocedureisnotvalid.For example,thejaridmaybeoftheimproperformat,maynotexisttobereplacedor removed,orcannotbeinstalledasitalreadyexists.

RecoveryText: Ensurethejarnameisofthecorrectformat.Ifthejaridexists,itmayneedtoberemoved beforeitcanbeinstalled.Fortheremoveorreplaceprocedures,ensurethejaridexists.

SQLCODEor SQLCODEs:

-20201

46002

SQ20202

MessageText: Thereplaceorremoveof&1in&2failedbecause&3isinuse.

CauseText: Thespecifiedclassinthejarfileiscurrentlyinusebyadefinedprocedure,orthe replacementjarfiledoesnotcontainthespecifiedclassforwhichaprocedureisdefined.

RecoveryText: Ensureallproceduresreferencingtheclassesbeingremovedaredroppedandresubmitthe replaceorremoveprocedure.

SQLCODEor SQLCODEs:

-20202

Database SQL messages and codes

System i

Database

SQL messages and codes

Version 5 Release 4

System i

Database

SQL messages and codes

Version 5 Release 4

Contents

SQL messages and codes

. . . . . . . 1

Appendix. Notices

. . . . . . . . . 189

SQL messages and codes

What’s new for V5R4

New messages and codes added

How to see what’s new or changed

Printable PDF

Saving PDF files

Downloading Adobe Reader

SQLCODE and SQLSTATE concepts

SQLCODE

SQLSTATE

SQLSTATEs returned by non-DB2 UDB for iSeries application servers

Listing of SQLSTATE class codes

Listing of SQLSTATE values

SQL message concepts

Listing of SQL messages