Db2 Sql Error Sqlcode=-670
Contents |
creating new Controller database Technote (troubleshooting) Problem(Abstract) User launches "Controller Configuration" and selects a database connection. User starts the standard (Java-based) "Database Conversion Utility", and chooses "Controller DB". User clicks db2 sql error sqlcode=-670 sqlstate=54010 sqlerrmc=32677 "Connect" and chooses "Create DB". After a while, an error appears. Symptom ....Running script:
Db2 Sql Error Sqlcode=-670 Sqlstate=54010
script/db2/table_crdsubmission.sql Running script: script/db2/table_crdperiodlockingbycompany.sql Running script: script/db2/table_xconsolstat.sql Running script: script/db2/table_xstageacc.sql ** ERROR: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-670, SQLSTATE=54010, SQLERRMC=16293;CONTROLLER_TABLE, DRIVER=3.59.81
Sqlcode=-670 Sqlstate=54010
** ERROR: Invalid datbase, XFRANGO is empty Cause There are several potential causes for the generic "Invalid datbase, XFRANGO is empty" error. TIP: For example, see separate Technote #1503319. This Technote specifically relates
Sql Error Code 668
to the scenario where the problem is caused by the database settings being too restrictive. Specifically, in the above error message, the table space "Controller_table" had been configured to be too small (16K). Details: In one example, the cause was that the page size of "CONTROLLER_TABLE" was not 32k. In other words: BAD database-creation script: CREATE REGULAR TABLESPACE CONTROLLER_TABLE PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 db2 sql 668 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL CCR_16KBP DROPPED TABLE RECOVERY ON; GOOD script: CREATE REGULAR TABLESPACE CONTROLLER_TABLE PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL CCR_32KBP DROPPED TABLE RECOVERY ON; Environment Controller database hosted on DB2. Diagnosing the problem Notice how the error message has the following codes: SQLCODE=-670 SQLSTATE=54010 The above two codes refer to the problem where the row length of the table exceeded a limit of length bytes. TIP: For more information, see 'DB2 Error Codes' link below. SQLERRMC=16293 This is warning you that the row length of a table in the database manager cannot exceed 16293 bytes in a table space with an 16K page size TIP: This value would be 4005 bytes in a table space with a 4K page size, 8101 bytes in a table space with an 8K page size and 32677 bytes in a table space with an 32K page size. CONTROLLER_TABLE this is the name of the tablespace that the problem applies to. Resolving the problem Either: Modify the existing database, by increasing the page size for the relevant table space or delete the existing database and re-create blank database with corr
Events Forums DB2-LDB2-L ArchivesNA ConferenceEMEA Conference Resources BlogsIDUG eBulletinIDUG NewsroomCommunitiesRegional User Groups Articles&Content IDUG Content ArticlesTech ChannelBeginner's BlogDB2 Hot Topics BlogContent LibraryIDUG YouTubePast Conference ContentCodeplace Library GetInvolved Volunteering with IDUGVolunteer PositionsJoin a Regional User GroupBecome http://www.idug.org/p/fo/et/thread=13941 an IDUG MentorSponsorship OpportunitiesJoin a CommunitySpeaker Community -670 SQL Code Trying To https://jira.sakaiproject.org/browse/SAK-18407 Insert To 32K Table - Why? James D. Hannis -670 SQL Code Trying To Insert To 32K Table - Why? November 8, 2002 10:16 AM The following QMF query is giving a -670 SQL Code on an OS/390 DB2 V7 test subsystem: DSNT408I SQLCODE = -670, db2 sql ERROR: THE RECORD LENGTH OF THE TABLE EXCEEDS THE PAGE SIZE LIMIT DSNT418I SQLSTATE = 54010 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXOJW SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -100 0 0 1078268169 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'40451109' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION INSERT INTO BES100ZL.PSHOMEPAGEMAP SELECT * FROM DBABESS.PSHOMEPAGEMAP db2 sql error A WHERE NOT EXISTS (SELECT 'X' FROM BES100ZL.PSHOMEPAGEMAP B WHERE A.HOMEPAGENAME = B.HOMEPAGENAME AND A.SEQNO = B.SEQNO ) Both of these tables reside in tablespaces defined with Pagesize 32K & using 32K bufferpools. The query will run successfully when the INSERT INTO portion is removed. We did a work around by unloading the rows via DSNTIAUL & doing a LOAD RESUME YES. Any ideas on why the original query is failing? The -670 is also returned when I tried to EXPLAIN the query. TABLESPACE: PSIM100S CREATOR : BES100ZL CREATEDBY: U075270 DATABASE : BESZIM1D STATUS : A N.TABLES : 15 BUFFERPOOL: BP32K PAGESIZE: 32K N.ACTIVE : 551 TABLESPACE: PSIM100S CREATOR : DBABESS CREATEDBY: U075270 DATABASE : BESSIM1D STATUS : A N.TABLES : 15 BUFFERPOOL: BP32K6 PAGESIZE: 32K N.ACTIVE : 1,892 DB2 Object ===> BP Option ===> DS Buffer Pool ===> BP32% Creator ===> * Where => N Qualifier ===> * Grantor ===> * Loc: LOCAL ---------- SSID: DBT2 ----------U273996 - LINE 1 OF 2 > CMD NAME ID USE VPSIZE HPSIZE VPSEQT VPPSEQT HPSEQ
ExportXMLWordPrintable Details Type: Bug Status: Closed Priority: Major Resolution: Won't Fix Affects Version/s: 2.7.0 Fix Version/s: None Component/s: zz Profile [DO NOT USE] Labels: None Environment: WAS/DB2 on blades Description The Profile and Profile 2 tools are inoperable in 2.7b07 when using a DB2 database due to a sql error. To test: 1) Log in as admin, go to Users, create a new user (all that is needed is a username and password) 2) Log in as the created user, go into the Profile tool under their My Workspace The Profile tool (actually Profile 2) displays this: "Internal Error: An error has occurred". The deprecated version of Profile gives an error page, however you must add the tool through the Sites tool as an admin to replicate (though not necessary since both appear to be the same error). This seems to stem from a DB2 exception at application initialization (when loading the db for the first time). Below is the stack trace from Profile, the sql exception at init time, and relevant DB2 lookup info. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - JDBCException E org.apache.commons.logging.impl.Jdk14Logger error DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2INST1.SAKAI_PERSON_T ... JDBCException E org.apache.commons.logging.impl.Jdk14Logger error DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;DB2INST1.SAKAI_PERSON_T ... SystemOut O 2010-04-20 17:06:20,677 ERROR WebContainer : 1 org.sakaiproject.profile2.logic.SakaiProxyImpl - SakaiProxy.getSakaiPerson(): Couldn't get SakaiPerson for: 543fa3de-f8de-400a-8460-f91dbdfac7ee : class org.springframework.dao.InvalidDataAccessResourceUsageException : could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query SystemOut O WARN: 2010-04-20 17:06:20,677 No SakaiPerson for 543fa3de-f8de-400a-8460-f91dbdfac7ee. Creating one. [WebContainer : 1] ... SystemOut O 2010-04-20 17:06:21,688 ERROR WebContainer : 1 org.sakaiproject.profile2.logic.SakaiProxyImpl - SakaiProxy.createSakaiPerson(): Couldn't create SakaiPerson : class org.springframework.dao.InvalidDataAccessResourceUsageException : could not insert: [org.sakaiproject.component.common.edu.person.SakaiPersonImpl]; nested exception is org.hibernate.exception.SQLGrammarException: could not insert: [org.sakaiproject.component.common.ed