PI65593: THE DATABASE SCHEMA NAME CANNOT BE CONFIGURED WITH OPENJPA.JDBC.SCHEMAFACTORY

Fixes are available

APAR status

Closed as program error.

Error description

Using JPA in an application running on WAS causes issues
regarding effort and consistency in the development and
operations workflow, because the database schema name cannot be
configured in WAS.

That means, the JPA application has to be created specifically
for every WAS environment that is connected to a different DB
schema.

What makes things more confusing, the datasource custom
property "currentSchema" is used by by WAS/JPA in some cases,
however, it is not used when the property
"openjpa.jdbc.SchemaFactory" is specified in the
persistence.xml:

<property name="openjpa.jdbc.SchemaFactory" value="native
(ForeignKeys=true)"/>

Once the openjpa.jdbc.SchemaFactory is specified, the
openjpa.jdbc. Schema has to be set as well in the
persistence.xml, so it seems - currentSchema is then not used
anymore
The problem is that having a different value for
openjpa.jdbc.Schema means having a different persistence.xml
which again means having a different EAR for each stage
(development, integration, prod, ...) just to include the
schema name.

The customer asks, what is the WebSphere Application Server
approach to operate an JPA application in different stages that
may be connected to different schemas?

Local fix

Problem summary

****************************************************************
* USERS AFFECTED:  All users of IBM WebSphere Application      *
*                  Server V8.5.0, V8.5.5, and V9.0.0 who make  *
*                  use of the OpenJPA property SchemaFactory   *
*                  and property useSchemaName.                 *
****************************************************************
* PROBLEM DESCRIPTION: When SchemaFactory and                  *
*                      useSchemaName=false is set, a schema    *
*                      name is incorrectly used.               *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************
When "openjpa.jdbc.SchemaFactory=native(ForeignKeys=true)" is
set, this tells OpenJPA to do up-front schema validation, and
allows OpenJPA to be able to learn about any schemas it can
"see".  When this happens, OpenJPA will use the schema when
generating SQL statements.  For example, if OpenJPA finds
table A under schema Y, when generating SQL on table A, schema
Y will be appended to the generated SQL (e.g. SELECT a.id from
Y.A a....).  Without this property, OpenJPA doesn't otherwise
use a schema name if it is not specified (i.e. as per the
number of ways defined by the JPA spec, or the OpenJPA
proprietary options, e.g. openjpa.jdbc.Schema).  OpenJPA
offers a way to tell it to not use a schema name by using this
property:
"openjpa.jdbc.DBDictionary=useSchemaName=false"
When this property is set, OpenJPA will not append the schema
to generated SQL (e.g. SELECT a.id from A a....).  However, I
have found a case where OpenJPA incorrectly applies the schema
name.  To explain, take this Entity, note the TableGenerator:
@Entity
public class MyEntityTable implements Serializable {
@Id
@TableGenerator(name = "TABLE_GENERATOR", table =
"MY_ID_TABLE",
pkColumnName = "MY_PK_COLUMN",
pkColumnValue="MY_PK_NAME",
valueColumnName = "MY_VALUE_COLUMN")
@GeneratedValue(strategy = GenerationType.TABLE, generator
= "TABLE_GENERATOR")
......
With this Entity and TableGenerator, the SQL to select and
update the generated value should be:
SELECT MY_VALUE_COLUMN FROM MY_ID_TABLE WHERE MY_PK_COLUMN = ?
UPDATE MY_ID_TABLE SET MY_VALUE_COLUMN = ? WHERE MY_PK_COLUMN
= ? AND MY_VALUE_COLUMN = ?
However, with the above SchemaFactory, and
'useSchemaName=false' settings, the table 'MY_ID_TABLE' would
have the schema name appended to it (e.g. if the schema name
is Y, the SQL would contain "Y.MY_ID_TABLE").  For SQL
statements on 'MyEntityTable' itself, the schema name is not
added to the SQL.  The issue is limited to SQL statements
against the table 'MY_ID_TABLE'.  This is do to a hole in the
OpenJPA code that generates SQL for the 'MY_ID_TABLE'.  That
is, the particular area of code does not take into
consideration the value of 'useSchemaName'.

Problem conclusion

With this fix, code has been added to OpenJPA to ensure that
the value of property 'useSchemaName' is used when generating
SQL for an @TableGenerator.

The fix for this APAR is currently targeted for
inclusion in Service Levels (Fix Packs) 8.5.5.11 and 9.0.0.2
of WebSphere Application Server versions 8.5.5 and 9.0.0,
and fix pack 16.0.0.4 for WebSphere Application Server Liberty.

Please refer to the recommended updates page for delivery
information:
http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980

Temporary fix

Comments

APAR Information

APAR number
PI65593
Reported component name
WEBS APP SERV N
Reported component ID
5724H8800
Reported release
850
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-07-11
Closed date
2016-08-25
Last modified date
2016-11-10

APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:

Fix information

Fixed component name
WEBS APP SERV N
Fixed component ID
5724H8800

Applicable component levels

R850 PSY
UP

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.5","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
03 May 2022

Tips

PI65593: THE DATABASE SCHEMA NAME CANNOT BE CONFIGURED WITH OPENJPA.JDBC.SCHEMAFACTORY

Fixes are available

Subscribe

APAR status

Closed as program error.

Error description

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

APAR Information

APAR number

Reported component name

Reported component ID

Reported release

Status

PE

HIPER

Special Attention

Submitted date

Closed date

Last modified date

APAR is sysrouted FROM one or more of the following:

APAR is sysrouted TO one or more of the following:

Fix information

Fixed component name

Fixed component ID

Applicable component levels

R850 PSY

Document Information

Share your feedback

Need support?