DB2 - Profile tools cannot load user data

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.edu.person.SakaiPersonImpl]
SystemOut O ERROR: 2010-04-20 17:06:21,689 Can't instantiate page using constructor public org.sakaiproject.profile2.tool.Dispatcher() [WebContainer : 1]
org.apache.wicket.WicketRuntimeException: Can't instantiate page using constructor public org.sakaiproject.profile2.tool.Dispatcher()
at org.apache.wicket.session.DefaultPageFactory.newPage(DefaultPageFactory.java:168)
at org.apache.wicket.session.DefaultPageFactory.newPage(DefaultPageFactory.java:58)
...

• - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

AddableSessio W org.apache.commons.logging.impl.Jdk14Logger warn Unsuccessful schema statement: create table SAKAI_PERSON_T (ID bigint generated by default as identity, PE
RSON_TYPE varchar(3) not null, VERSION integer not null, UUID varchar(36) not null unique, LAST_MODIFIED_BY varchar(36) not null, LAST_MODIFIED_DATE timestamp not null, CREATED_BY varchar(36) not null, CREATED_DATE timestamp not null, AGENT_UUID varchar(99) not null, TYPE_UUID varchar(36) not null, COMMON_NAME varchar(255), DESCRIPTION varchar(255), SEE_ALSO varchar(255), STREET varchar(255), SURNAME varchar(255), TELEPHONE_NUMBER varchar(255), FAX_NUMBER varchar(255), LOCALITY_NAME varchar(255), OU varchar(255), PHYSICAL_DELIVERY_OFFICE_NAME varchar(255), POSTAL_ADDRESS varchar(255), POSTAL_CODE varchar(255), POST_OFFICE_BOX varchar(255), STATE_PROVINCE_NAME varchar(255), STREET_ADDRESS varchar(255), TITLE varchar(255), BUSINESS_CATEGORY varchar(255), CAR_LICENSE varchar(255), DEPARTMENT_NUMBER varchar(255), DISPLAY_NAME varchar(255), EMPLOYEE_NUMBER varchar(255), EMPLOYEE_TYPE varchar(255), GIVEN_NAME varchar(255), HOME_PHONE varchar(255), HOME_POSTAL_ADDRESS varchar(255), INITIALS varchar(255), JPEG_PHOTO blob(255),
LABELED_URI varchar(255), MAIL varchar(255), MANAGER varchar(255), MOBILE varchar(255), ORGANIZATION varchar(255), PAGER varchar(255), PREFERRED_LANGUAGE varchar(255), ROOM_NUMBER varchar(255), SECRETARY varchar(255), UID_C varchar(255), USER_CERTIFICATE LONG VARCHAR FOR BIT DATA, USER_PKCS12 LONG VARCHAR FOR BIT DATA, USER_SMIME_CERTIFICATE LONG VARCHAR FOR BIT DATA, X500_UNIQUE_ID varchar(255), AFFILIATION varchar(255), ENTITLEMENT varchar(255), NICKNAME varchar(255), ORG_DN varchar(255), ORG_UNIT_DN varchar(255), PRIMARY_AFFILIATION varchar(255), PRIMARY_ORG_UNIT_DN varchar(255), PRINCIPAL_NAME varchar(255), CAMPUS varchar(255), HIDE_PRIVATE_INFO smallint, HIDE_PUBLIC_INFO smallint, NOTES varchar(4000), PICTURE_URL varchar(255), SYSTEM_PICTURE_PREFERRED smallint, ferpaEnabled smallint, dateOfBirth date, locked sma
llint, FAVOURITE_BOOKS varchar(4000), FAVOURITE_TV_SHOWS varchar(4000), FAVOURITE_MOVIES varchar(4000), FAVOURITE_QUOTES varchar(4000), EDUCATION_COURSE varchar(4000), EDUCATION_SUBJECTS varchar(4000), NORMALIZEDMOBILE varchar(255), primary key (ID), unique (AGENT_UUID, TYPE_UUID))
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -670, SQLSTATE: 54010, SQLERRMC: 32677;
at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
at com.ibm.db2.jcc.c.fg.b(fg.java:1160)
...

• - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

-204

name IS AN UNDEFINED NAME
Explanation

The object identified by name is not defined in the DB2® subsystem. This SQLCODE can be generated for any type of DB2 object.

If the error was encountered for a particular version of a native SQL procedure, the name token may return the following information:

name-of-procedure VERSION version-id

For COMMENT or DROP, if the specified object is a package, the implicitly or explicitly specified version of the package is not defined in the DB2 subsystem. If the version-ID was not explicitly specified, the statement attempted to process the null version for the identified package, but the null version does not exist. The VERSION clause can be used to specify a specific version to be processed.
Attention: A function cannot be sourced on the COALESCE, NULLIF, RAISE_ERROR, RID, or VALUE built-in functions. Additionally, there are restrictions on the way that you can source on the COUNT, COUNT_BIG, CHAR, and STRIP built-in functions because of some of the keywords that they accept.
System action

The statement cannot be executed.
Programmer response

Verify that the object name was correctly specified in the SQL statement, including any required qualifiers. If it is correct, ensure that the object exists in the system before resubmitting the statement.

If the specified object is a routine, issue the CREATE PROCEDURE or CREATE FUNCTION statement to define the routine to DB2. Issue the -START PROCEDURE command to activate the new definition.

If the specified object is a package, issue the COMMENT or DROP statement with the VERSION clause to identify the specific version to be processed.
SQLSTATE

42704

46002

• - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

-727

SQL0727N
An error occurred during implicit system action type action-type . Information returned for the error includes SQLCODE sqlcode , SQLSTATE sqlstate and message tokens token-list .
Explanation:

The processing of a statement or command has caused the database manager to implicitly perform additional processing. During this processing an error was encountered. The action attempted is shown by the action-type :

1
implicit rebind of a package
2
implicit prepare of a cached dynamic SQL statement
3
implicit regeneration of a view
4
This return code is reserved for use by DB2.
5
incremental bind of a static SQL statement, not bound during package bind time
6
implicit prepare of a reoptimizable statement containing host-variables, special registers, or parameter markers

The sqlcode, sqlstate and message token list (each token is separated by the vertical bar character) are provided. The message tokens may be truncated. See the corresponding message for the sqlcode for further explanation of the error.

The original SQL statement or command that caused the action-type cannot be processed and the implicit system action was not successful.

Federated system users: You may have received this message because you dynamically prepared an SQL statement in a pass-through session and then tried to execute the statement after the session was closed.
User Response:

Check the message associated with the SQLCODE of the SQL statement that failed. Follow the action suggested by that message.

For an invalid package, the REBIND command can be used to recreate the error or to explicitly validate the package once the cause of the error has been resolved.

For a failure while regenerating a view, the name of the view that failed is recorded in the administration notification log. The failing view can be dropped or a change made to the statement or command that caused the view regeneration.

Federated system users: If the statement that failed was dynamically prepared in a pass-through session, open another pass-through session, write and prepare the statement again, and execute it while the session is still open.

sqlcode : -727

sqlstate : 56098

• - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

-670

SQL0670N
The row length of the table exceeded a limit of length bytes. (Table space tablespace-name .)
Explanation:

The row length of a table in the database manager cannot exceed:

• 4005 bytes in a table space with a 4K page size

• 8101 bytes in a table space with an 8K page size

• 16293 bytes in a table space with an 16K page size

• 32677 bytes in a table space with an 32K page size

The length is calculated by adding the internal lengths of the columns. Details of internal column lengths can be found under CREATE TABLE in the SQL Reference.

One of the following conditions can occur:

• The row length for the table defined in the CREATE TABLE or ALTER TABLE statement exceeds the limit for the page size of the table space. The regular table space name tablespace-name identifies the table space from which the page size was used to determine the limit on the row length.

• The row length for the table defined in the DECLARE GLOBAL TEMPORARY TABLE statement exceeds the limit for the page size of the table space. The user temporary table space name tablespace-name identifies the table space from which the page size was used to determine the limit on the row length.

The statement cannot be processed.
User Response:

Depending on the cause, do one of the following.

• In the case of CREATE TABLE, ALTER TABLE, or DECLARE GLOBAL TEMPORARY TABLE, specify a table space with a larger pagesize, if possible.

• Otherwise, reduce the row length by eliminating one or more columns or reducing the lengths of one or more columns.

sqlcode : -670

sqlstate : 54010