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 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
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.
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 |
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.
ExamplesBack-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' |
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:
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)
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:
SELECT e FROM EmpBean e WHERE SUBSTRING(?1, 1) = 'John Smith' {_varchar}
SELECT q1."EMPID", q1."NAME" FROM Emp q1 WHERE (CAST(SUBSTR ( ?1, 1) AS VARCHAR(255)) = 'John Smith')
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.
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.