EJB query to SQL syntax

The syntax for performing an EJB query in SQL is database-dependent. Use this information as a reference to discover what is the translated SQL statement from running an EJB query on different database vendor back ends.
This reference topic is helpful for those who:
  • Chooses to deploy against an unsupported database. Use this reference topic to help choose a valid database vendor back-end id that matches closely to your unsupported deployment environment.
  • Chooses to migrate away from the obsolete SQL92 or SQL99 back-end id. Use this reference topic to help determine what back end you should use now that the SQL92 and SQL99 back ends are no longer supported.

Supported database vendor back-end ids

The following is a list of supported database vendor names you can specify to deploy your application against:
Note: Use the back-end id as a value for the dbvendor argument when running the ejbdeploy command. For more information on the ejbdeploy command, see The ejbdeploy command topic.
Table 1. Supported database vendor back-end ids
Database Vendor Back-end ID Description
DB2® DB2UDB_V81 DB2 V8.1 for Linux®, UNIX®, and Windows®
DB2UDB_V82 DB2 V8.2 for Linux, UNIX, and Windows
DB2UDB_V91 DB2 V9.1 for Linux, UNIX, and Windows
DB2UDB_V95 DB2 V9.5 for Linux, UNIX, and Windows
DB2UDBOS390_V8 DB2 V8 for z/OS®
DB2UDBOS390_NEWFN_V8 DB2 V8 for z/OS

Additional to the DB2UDBOS390_V8 option, this option includes the generated data model that has all the new catalog features of DB2 V8 for z/OS specified in the new function mode. Use this option if you plan to work with the generated data model available in the IBM® Rational® Software Development Platform products.

DB2UDBOS390_V9 DB2 V9 for z/OS

This option includes the generated data model that has all the new catalog features of DB2 V9 for z/OS specified in the new function mode. It enables the option to work with the generated data model available in the IBM Rational Software Development Platform products.

DB2UDBISERIES_V53 DB2 V5R3 for iSeries®
DB2UDBISERIES_V54 DB2 V5R4 for iSeries
Oracle ORACLE_V10G Oracle, V10g
ORACLE_V11G Oracle, V11g
Informix® INFORMIX_V100 Informix Dynamic Server, V10
INFORMIX_V110 Informix Dynamic Server, V11
Sybase SYBASE_V1250 Sybase Adaptive Server Enterprise, V12.5
SYBASE_V15 Sybase Adaptive Server Enterprise, V15
SQL Server MSSQLSERVER_2005 Microsoft® SQL Server 2005
Derby DERBY_V10 Apache Derby V10
DERBY_V101 Apache Derby V10.1
The following back-end ids are obsolete and no longer supported:
  • SQL92 (1992 SQL Standard)
  • SQL99 (1999 SQL Standard)

Specifying column name in a SQL statement

The syntax for specifying column names in SQL is database-dependant. For DB2 and Oracle the columns, tables, and correlations names are enclosed in double quotations to hold case sensitive names. The following is an example of a translated SQL statement for DB2:

 SELECT q1."EMPID", q1."NAME",q1."SALARY" FROM Emp q1

However, Informix, SQL Server, and Sybase do not enclose the column names in double quotations. The following is an example of a translated SQL statement for Sybase:

SELECT q1.EMPID, q1.NAME, q1.SALARY  FROM Emp q1

Database Functions

The following sections list database functions you can use in an EJB query and shows the translated SQL syntax for the specified database vendor back end:

Scalar Functions

EJB query contains scalar functions for doing type conversions, string manipulation, and for manipulating date-time values. For more information on the list of scalar functions, see the topic EJB query: Scalar functions.

The left column of the table lists the scalar functions an EJB query might contain. To the right of the EJB query function column, lists the SQL syntax that is pushed-down to the respective back-end database vendors listed on the heading of each column. Empty cells that contain no text means the EJB query function can not be pushed-down to the particular back-end database vendor, and as a result, produce Cannot push down query error condition.

Table 2. Scalar functions in an EJB query and what the SQL syntax that is pushed-down to each back end
EJB query function DB2 z/OS DB2 Oracle Informix Sybase SQL Server SQL92/SQL991 Derby
ABS abs abs abs abs abs abs abs abs
SQRT sqrt sqrt sqrt sqrt sqrt sqrt sqrt sqrt
CONCAT concat concat concat || + + || concat
LENGTH length length length length char_length len char_length length
LOCATE locate locate instr locate charindex charindex locate locate
SUBSTRING substr substr substr substr substring substring substr substr
MOD mod mod mod mod mod % mod mod
ucase upper upper upper upper upper upper upper upper
upper upper upper upper upper upper upper upper upper
lcase lower lower lower lower lower lower lower lower
lower lower lower lower lower lower lower lower lower
char char char to_char   char char   char
bigint   bigint           bigint
date date date           date
decimal decimal decimal            
double   double           double
float float float           float
integer integer integer           integer
real real real           real
smallint   smallint           smallint
time time time           time
timestamp timestamp timestamp           timestamp
digits digits digits           digits
day day day           day
days days days           days
hour hour hour           hour
microsecond microsecond microsecond           microsecond
minute minute minute           minute
month month month           month
second second second           second
year year year           year

Date time arithmetic and comparisons

For general knowledge about date, time arithmetic and comparison see the Date time arithmetic and comparisons topic from the WebSphere® Application Server Information Center.

There is support on the DB2 family to use string representation on DATE, TIME, and TIMESTAMP values; and specify arithmetic and comparison operations in EJB query language. For details on the DATE, TIME and TIMESTAMP values in DB2, see the Datetime values topic.

For databases other than the DB2 family, there is no support to use string representation on DATE, TIME, and TIMESTAMP values; and specify arithmetic and comparison operations in EJB query language. You can instead represent the date, time or timestamp values in milliseconds by using a Java™ long data type. The date, time or timestamp literal should be a numeric literal. To generate a millisecond value, you can use the java.util.Calendar class. Use the java.util.Calendar interface to compare different Calendar objects.

Examples
For each of the database vendors, the following table list an example of the EJB query and its translated SQL query.
Note: The data type for emp_ts is Calendar.
Table 3. Date, time and timestamp representation: EJB query examples and its push-down SQL statements for the different database vendor.
Back-end database vendor Sample EJB query statement The translated SQL query statement
DB2 SELECT e FROM EmpBean e WHERE e.emp_ts=71039082 SELECT q1."no", q1."name", q1."emp_ts" FROM userid.iEmp q1 WHERE q1."emp_ts" = '1970-1-1-11.43.59.082'
Oracle SELECT e FROM EmpBean e WHERE e.emp_ts=71039082 SELECT q1."no", q1."name", q1."emp_ts" FROM userid.iEmp q1 WHERE q1."emp_ts" = TO_DATE ( '1970-1-1-11.43.59','YYYY-MM-DD-HH24.MI.SS')
Informix SELECT e FROM EmpBean e WHERE e.emp_ts>71039082 SELECT q1.no, q1.name, q1.emp_ts FROM userid.iEmp q1 WHERE q1.emp_ts > DATETIME (1970-1-1 11:43:59.082) YEAR TO FRACTION)
Derby SELECT e FROM EmpBean e WHERE e.emp_ts<71039082 SELECT q1."no", q1."name", q1."emp_ts" FROM userid.iEmp q1 WHERE q1."emp_ts" < '1970-1-1-11.43.59.082'
SQL Server SELECT e FROM EmpBean e WHERE e.emp_ts=71039082 SELECT q1.no, q1.name, q1.emp_ts FROM userid.iEmp q1 WHERE q1.emp_ts = 'Jan 1 1970 11:43:59.82 AM'
Sybase SELECT e FROM EmpBean e WHERE e.emp_ts=71039082 SELECT q1.no, q1.name,emp_ts FROM userid.iEmp q1 WHERE q1.emp_ts = 'Jan 1 1970 11:43:59.82 AM'

CONCAT, LOCATE, LENGTH and SUBSTR functions

The concatenation operator (CONCAT) links two string operands to form a string expression. The operands of concatenation must be compatible strings. The locate operator returns the starting position of the first occurrence of a search string within a source string. The length operator returns the length of a value. The substring operator (SUBSTR) returns a substring of a string.

DB2 versus other database vendors

For DB2, the functions that accept the input arguments as an input parameters such as SUBSTR, CONCAT, LOCATE, and LENGTH functions require the use of the CAST syntax, but other database vendors do not require CAST for their functions.

Example:

Consider the following sample EJB query statement with the input argument that has an input parameter of type java.lang.String:
SELECT e FROM EmpBean e WHERE SUBSTRING(?1, 1) = 'John Smith' {_varchar}
The following is the translated SQL statement for DB2 for z/OS:
SELECT  q1."EMPID",  q1."NAME" FROM Emp q1  WHERE  (CAST(SUBSTR ( ?, 1) AS VARCHAR(255)) = 
'John Smith')
The following is the translated SQL statement for Informix:
SELECT  q1.EMPID,  q1.NAME FROM Emp q1  WHERE  SUBSTR(?,1)='John Smith'
You can see the other database vendors (in this example, Informix) does not require the CAST syntax for the SUBSTR function.

DB2 for iSeries and for Windows and UNIX

For DB2 for iSeries and for Windows and UNIX, the CONCAT function CAST the combined length attribute to either 4000 or 32672 as shown below. Please refer to the DB2 Information Center for details.

Examples:

Example #1: CAST the combined length attributes to VARCHAR(4000)

Sample EJB query statement:
 SELECT e  FROM EmpBean e WHERE concat(?1, 'ahmad') = 'deptahmad'  {_varchar}
The translated SQL statement:
SELECT  q2."no",  q2."name" FROM userid.Emp q1 WHERE  (CAST(concat ( ?, 'ahmad') 
AS VARCHAR(4000)) = 'deptahmad')  
Example #2: Using parameter markers of data type VARCHAR
SELECT  d.name FROM DeptBean d WHERE CONCAT(?1,?2) = 'Firstname1' {_varchar,_varchar}
SELECT  q1."name" FROM userid.Deptc q1 WHERE  (CAST(concat (CAST(? AS VARCHAR(32672)), 
CAST(? AS VARCHAR(32672))) AS VARCHAR(4000)) = 'Firstname1')

DB2 for z/OS

If there is a parameter marker, the functions that accept the input argument as an input parameter such as CONCAT, SUBSTR, LOCATE, LENGTH functions require to CAST the length attribute of the parameter marker as defined in the DB2 reference manual. For details, see the topics:

Example:

Sample EJB query statement:
SELECT e FROM EmpBean e WHERE SUBSTRING(?1, 1) = 'John Smith'  {_varchar}
The translated SQL statement:
SELECT  q1."EMPID",  q1."NAME" FROM Emp q1  WHERE  (CAST(SUBSTR ( ?1, 1) AS VARCHAR(255)) = 
'John Smith') 

SQLJ

SQLJ enables you to embed SQL statements into Java™ programs.

SQLJ support is available for all DB2 family back ends, except DB2 for iSeries

For more details on SQLJ, refer to the Deploying Structured Query Language in Java (SQLJ) applications topic.

Optimistic predicate clause

The objective of optimistic concurrency control is to minimize the time over which a given resource would be unavailable for use by other transaction. When optimistic access intent is setup, you need to specify the attributes that you want to include in the optimistic predicate. The attribute is included in the WHERE clause of the UPDATE SQL statement:
UPDATE SET X = ? WHERE ID = ? AND X = ?

where ID is the primary key column.

For the DB2UDBOS390_V8 and DB2UDBOS390_V9 back-end ID, if the optimistic predicate column is nullable, the resulting UPDATE query uses the "IS NOT DISTINCT FROM" construct. For example, if ID is the primary key column and NAME is nullable, then the resulting UPDATE query is:

UPDATE SET NAME = ? WHERE ID = ? AND NAME IS NOT DISTINCT FROM ?

The syntax for getting an update lock using the SELECT statement is database-dependent. For details on the translated SQL statement for the UPDATE clause, refer to the topic, Access intent -- isolation levels and update locks.

1 SQL92 and SQL99 are no longer supported back-end ids and are referenced in this topic for informational purpose only.