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 backends.
This reference topic can be helpful for those who:
  • Chooses to deploy against an unsupported database. Use this reference topic to help choose a valid database vendor backend id that matches closely to your unsupported deployment environment.
  • Chooses to use the deprecated SQL92 or SQL99 backend id. Use this reference topic to help determine what backend you should use, in the near future, when the deprecated SQL92 and SQL99 backends are no longer available.

Supported database vendor backend ids

The following is a list of supported database vendor names you can specify to deploy your application against:
Note: Use the backend 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 backend ids
Database Vendor Backend ID Description
DB2® DB2UDB_V81 DB2 Universal database V8.1 for Linux®, UNIX®, and Windows®
DB2UDB_V82 DB2 Universal database V8.2 for Linux, UNIX, and Windows
DB2UDB_V91 DB2 Universal database V9.1 for Linux, UNIX, and Windows
DB2UDBOS390_V7 DB2 Universal Database™ for z/OS®, V7
DB2UDBOS390_V8 DB2 Universal Database for z/OS, V8
DB2UDBOS390_NEWFN_V8 DB2 Universal Database for z/OS, V8

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

DB2UDBOS390_V9 DB2 Universal Database for z/OS, V9

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

DB2UDBISERIES_V53 DB2 Universal Database for iSeries™, V5R3
DB2UDBISERIES_V54 DB2 Universal Database for iSeries, V5R4
Oracle ORACLE_V9I Oracle, V9i
ORACLE_V10G Oracle, V10g
Informix® INFORMIX_V93 Informix Dynamic Server, V9.3
INFORMIX_V94 Informix Dynamic Server, V9.4
INFORMIX_V100 Informix Dynamic Server, V10.0
Sybase SYBASE_V1250 Sybase Adaptive Server Enterprise, V12.5
SYBASE_V15 Sybase Adaptive Server Enterprise, V15.0
SQL Server MSSQLSERVER_2000 Microsoft® SQL Server 2000
MSSQLSERVER_2005 Microsoft SQL Server 2005
Cloudscape™ DERBY_V10 IBM Cloudscape, V10.1
The following backend ids are deprecated:
  • SQL92 (1992 SQL Standard)
  • SQL99 (1999 SQL Standard)
Although SQL92 and SQL99 are deprecated, the SQL92 and SQL99 options remain available.

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 backend:

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 backend 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 backend 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 backend
EJB query function DB2 z/OS DB2 Oracle Informix Sybase SQL Server SQL92/SQL99 Cloudscape
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 InfoCenter.

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.
Backend 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)
Cloudscape 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 Universal Database on 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 Universal database for Windows and UNIX

For DB2 for iSeries and Universal database 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 Universal Database 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 backends, except DB2 for iSeries

For more details on SQLJ, refer to the Using embedded Structured Query Language in Java (SQLJ) support 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 backend 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.


Feedback