This chapter contains theses topics:
The JD Edwards World JDBC driver can be used by any Java-based reporting tool or software application that needs to access business data using JDBC. A good example for JD Edwards World customers is Oracle Business Intelligence (BI) Publisher.
The World JDBC driver automates most of the work of transforming World data to a usable form that can be used by your Java based business applications, work that would otherwise require additional custom software development to perform similar tasks. This saves the JD Edwards World customer time and effort to create Java based software and reports.
In addition to the security features that were described in chapter 3, the driver handles data transformation of JD Edwards data as it is stored in the World database automatically. However, in order to make proper use of the World JDBC driver, users should understand how SQL is to be used within the World JDBC driver. This chapter illustrates how the World JDBC driver handles the processing of particular aspects of JD Edwards World data and how to use SQL to obtain the correct data from the JDE World database.
World JDBC Driver data-specific features include:
Presumptive joins
Data transformations
Identifying tables, columns, and data dictionary items
Decimal position placement
Julian date conversion
In the JD Edwards World product, Presumptive Joins are used within the World Writer application to quickly allow users to pull in descriptions from parent tables for code and ID values in child tables (foreign key references). The JD Edwards World JDBC driver provides a similar feature that makes it easy to add the descriptions for specific tables.
JDBC Presumptive Join capability is dependent upon the information in the JD Edwards World Data Dictionary tables. It is in effect for any column in the database where the related data dictionary item is defined as:
Data Edit Rules. . UDC (any UDC code)
Data Edit Rules. . FILE F0010 (Company Codes)
Data Edit Rules. . FILE F0013 (Currency Codes)
Data Edit Rules. . FILE F0014 (Payment Terms)
If you use presumptive joins, then the supporting tables indicated above must exist in the user library list.
To use a presumptive join, use any text string that begins with 'VC0' in a separate column immediately following the data column for which you wish to have a text description. For example:
select SDKCOO, 'VC00001', SDDCTO, 'VC00002', SDBCRC, 'VC00003', FROM F4211
will return results that look like those in this table:
| SDKC000 | 00002 | SDDCTO | 00004 | SCBCRC | 00006 |
|---|---|---|---|---|---|
| 10 | Company 10 | SO | Sales Order | BEF | Belgian Francs |
| 43 | Intergalatic Megacorp. Inc. | SD | Direct Ship | UFO | Martian Monetary Unit |
| 43 | Intergalatic Megacorp. Inc | SO | Sales Order | ? | |
| 43 | Intergalatic Megacorp. Inc | SO | Sales Order | USD | U.S. Dollar |
| 63 | Company 00063 | VS | Purchasing Return - Brazil | BRL | Brazilian Real |
| 67 | Feimo's test | SO | Sales Order | ARA | Argentina Peso |
| 100 | Model Finan/Distrib Co (Mktg) | SD | Direct Ship | USD | U.S. Dollar |
| 100 | Model Finan/Distrib Co (Mktg) | SI | Interbranch Sales | USD | U.S. Dollar |
| 100 | Model Finan/Distrib Co (Mktg) | SO | Sales Order | ECU | European Currency Unit |
| 100 | Model Finan/Distrib Co (Mktg) | SO | Sales Order | GBP | Pound Sterling |
If the preceding column does not support presumptive join, then the presumptive join text, (for the above example, 'VC00001' ), will be returned for all values of the trailing column.
If the preceding column does support presumptive join but the code value does not exist in the lookup table, then the presumptive join text will return a null value.
The main purpose of all JDBC data transformations is to present and use all database references to a database column from the end user perspective. The JDBC driver performs these data transformations automatically and the user or the client application should not need to manipulate data values in order for the system to recognize the meaning. That is, dates display as dates, currency displays as currency, and numbers have correct decimal positions. Users should be able to create SQL statements which are easy to read, write, and reflect common business values.
JD Edwards World currently supports three types of data transformations based on JD Edwards World Data Dictionary definitions of the table column:
Decimal position placement
Date translation
Alternate nomenclature
Consider the following SQL example statement:
select shotot , shtotc, shcrr, shdrqj from F4201 where shdrqj between '18/08/2006' and '23/08/2006' and shotot > 1000.00 and SHCRR >0 fetch first 5 rows only
This SQL statement extracts all records in the six day date range between Aug 18, 2006 and Aug 23, 2006 and whose total dollar value exceeds $1000.00.
| shotot | shtotc | shcrr | shdrqj |
|---|---|---|---|
| 1100.00 | 0.00 | 2.0000000 | 18.08.2006 |
| 12500.00 | 0.00 | 0.5000000 | 23.08.2006 |
| 17500.00 | 0.00 | 0.5000000 | 23.08.2006 |
| 17500.00 | 0.00 | 0.5000000 | 23.08.2006 |
| 12500.00 | 0.00 | 0.5000000 | 23.08.2006 |
The JDBC driver converts the inbound date format (DDMMYYYY in this case, where Y represents year, M for month and D for day) to the JD Edwards World Julian date format.
Note:
Your specific date format will depend on your connection definition, the JD Edwards World user preferences and the IBM i system values. In general terms, the date format will be the same as you use within JD Edwards World, unless it is specifically overridden with the "data format" connection property.Finally, users should have the option of referencing objects in the system using an alternate nomenclature. If users are not familiar with the JD Edwards World database structures, an alternate nomenclature allows users to refer to the tables and columns in the JD Edwards World database using descriptive text that is in the driver metadata.
JD Edwards World tables are tables that exist in JD Edwards World Software Versions Repository (SVR), for which an F9801 table entry exists. The driver only revises values for JD Edwards World tables.
The JDBC driver identifies the JD Edwards World Data Dictionary item for a table column by removing the first two characters of the table column name. Therefore, Tables and table columns must follow the standard table definition rules for JDE table columns. Typically, the columns in the table use the following format:
ttdddd
where
"tt" is the table prefix for table name as defined in the table SVR record and
"dddd" is the data dictionary data item JD Edwards World uses to define the table columns.
The exception to this is a JD Edwards World field reference file. The JDBC driver uses columns names in JD Edwards World field reference files (F98FRF* files) as you define them in the file because they represent the data dictionary item names without a prefix.
The JDBC driver considers only data dictionary items in glossary group D or S. The JDBC driver excludes all other data dictionary items.
If the JDBC driver cannot locate a data dictionary item for a column in a JD Edwards World table, then it makes no changes to the JD Edwards World table column data; the data is passed to the client application unaltered.
One of the most difficult issues to address with the use of the JDBC driver with the JD Edwards World database is the placement of decimals for numeric values. The JD Edwards World JDBC driver handles this automatically for the driver users. Additionally, the decimal placement position is based on the data dictionary of the current connection environment. This allows client software that uses the driver to remain independent of the current setup of the environment being used. This is particularly useful for World instances that use different data dictionary information in their environments.
Again, all of this is handled automatically, and it is not necessary for the average user to worry about decimal placement. This topic describes in detail how the JDBC driver task is performed.
In computer terminology, every number is defined by its numeric data type, precision, scale, and numeric value itself.
Numeric data type represents how a numeric data value is represented in memory or saved on disk: On the IBM i, the valid values for numbers are: Packed Decimal data, Zoned Decimal data, Binary data, Floating Point data.
The precision of a number is how many significant digits are in the number. This is the total number of digits that a number can have both left and right of the decimal place.
The scale of a number is the number of digits to the right of the decimal place.
The value is the actual data value such as 100.00
The first three terms are used to define a database column where a number is saved and the last is the actual value itself. Therefore a database column that has a maximum data value of 99999.999 would have the following characteristics:
Datatype: Might be Zoned Decimal in this example
Precision: 8
Scale: 3
A specific data row in the table might have the value of 100.000 in this column.
In the JD Edwards World software, a numeric data value can have two different representations or perspectives:
The user's perspective is the intrinsic business value of the number. It is also called the "Display" value.
The "database" perspective is how the value is stored in the data files.
The benefits of having two perspectives within the JD Edwards World software system is that JDE can provide very flexible software that can easily adapt to many different applications and international requirements. However, having a "Display" perspective that differs from the "database" perspective make it difficult for client applications to use common database tools such as SQL to access the data. The database number values are not necessarily the correct intrinsic business value.
The display and database perspectives for values are defined by the associated data dictionary item for number represented in the JDE software system. The data dictionary identifies data type (FRDTAT), the value precision (FRDTAS), the display decimal (FRCDEC), and the file decimal (FRDTAD) values that represent a number in the JD Edwards World software system.
The numeric value from the user perspective is the display value and is defined as:
numericDataType (precision, displayDecimal)
or
FRDTAT (FRDTAS, FRCDEC)
The numeric value that resides in the data file is the file decimal representation of the number and is:
numericDataType (precision, fileDecimal)
or
FRDTAT (FRDTAS, FRDTAD)
Therefore, whenever the JDBC driver references a number column in the database it must adjust the value by a scale offset. The scale offset is always a power of 10 and the JDBC driver uses it as a multiplier or divisor in the column value calculations. The formula for the scale offset that the JDBC driver must apply is:
scaleoffset = 10 (Display Decimal minus File Decimal)
or
scaleoffset = 10 (FRCDEC - FRDTAD)
The JDBC driver performs decimal position changes by altering the inbound SQL statement to reflect the expectations of the database engine and revises the ResultSet information that you expect in the result set. The JDBC driver performs both of these tasks in the following manner:
Revises the ResultSet values to report the correct scale.
Revises the ResultSetMetaData so that it reports the correct column scale and precision.
Changes outbound SQL statement column references, such as SelectList items, from:
columnName
to
CAST(columnName / ( scaleOffset) AS DECIMAL(FRDTAS, FRCDEC)) AS columnName
Changes inbound SQL statement column references, such as those in the WHERE and JOIN clauses, from:
columnName
to
columnName * scaleOffset
Revisions are not made to the GROUP BY or ORDER BY clause of the SQL statement.
The following example illustrates how the JDBC driver converts a value to the user perspective. The dictionary item OTOT has the following data dictionary information:
| OTOT Type | P |
|---|---|
| OTOT Size | 15 |
| OTOT Data File Decimals | 0 |
| OTOT Display Decimals | 2 |
The scale offset then is:
2-0 = 2
If the original SQL statement is:
SELECT SHOTOT FROM F4201
Then the driver revises the SQL statement to become:
SELECT CAST (SHOTOT/100 AS DECIMAL (15, 2)) AS SHOTOT FROM F4201
The original ResultSet:
| SHOTOT |
|---|
| 20900 |
Is then returned as:
| SHOTOT |
|---|
| 209.00 |
A relational expression is any SQL statement clause which compares two values. You use relational expressions extensively in JOIN and WHERE clauses to match, evaluate, and select data rows. There are performance considerations that you must be aware of when considering changes to SQL statement relational expressions because the DB2 for IBM i evaluates calculations for every record. To minimize these issues, the following rules apply:
When you use a constant in a relationship (SHOTOT > 10.00), the JDBC driver revises the constant value and not the data column in the comparison. For decimal scale conversions, this means that the JDBC driver multiplies the constant value by the scale offset.
For relationships between two columns with the same scale offset, the JDBC driver makes no changes to either side of the relationship. Since both columns have the same relative decimal position, the comparison succeeds without revising the database values.
If the two columns have different scale offsets, then the JDBC driver adjusts the column with the smaller offset by the difference between the two offsets. In this manner, changes are only necessary to one column instead of both columns which minimizes the performance impact of the relationship comparison.
JD Edwards World disabled and does not support some highly complex SQL statement relationship structures. In these situations, the JDBC driver issues an SQL exception error.
JD Edwards World stores most date values in the database as a JD Edwards style Julian date. Some exceptions exist such as the use of text based date columns for Z-files.
Julian date columns have a data dictionary Data Item Class of type "DATEW" (i.e. FRCLAS is set to "DATEW")
JDE Julian date format is not the same as IBM Julian date format:
JDE Julian date = (IBM Julian date - 1900000)
JD Edwards World stores date format as a CYYDDD format where:
C = the century where 0=1900 and 1 = 2000
YY is the year within the century
DDD is the nth day of the year
The CYY value can also be thought of as current year minus 1900, therefore 2010 - 1900 = 110.
When the JDBC driver uses Date values in a ResultSet object, if the database column is based on a data Dictionary item with a DATEW class dictionary item, the JDBC driver reformats the Julian date column to either a numeric representation or a textual user date format. The actual date format value will be presented based on the chosen data format of the connected user.
Beginning with JDBC 3.1, JDBC considers all DATEW database columns to be of database datatype DATE. The value is returned as a java.sql.Date object.
If the JDE date value is 0, then a null date is returned by the JDBC driver.
If the database contains a data value that is not a valid JDE Julian date , then the JDBC driver returns a null data value.
The following example illustrates how the JDBC driver retrieves a value from the database and converts it to reflect the user display perspective. Assume that the date format for the connection is set to "MDY" and the date separator is "-".
Users SQL statement request:
SELECT SHUPMJ FROM F4201 where SHUPMJ = '01-31-2006'
Revised SQL:
SELECT case when SHUPMJ=0 then NULL
when SHUPMJ=999999 then date('9999365')
else date(char(SHUPMJ+1900000)) end AS "SHUPMJ"
FROM F4201
WHERE SHUPMJ = 106031
Original ResultSet returns a:
| SHUPMJ |
|---|
| 106031 |
Revised ResultSet as seen by user. Note, the actual text result will depend on whether the application software requests the ResultSet value as a string representation or as a number:
| SHUPMJ as string |
|---|
| 01/31/2006 |
Constant value Relationships (SHUPMJ > '12-31-1995')
When the SQL statement uses a constant in a relationship, the JDBC driver revises the constant value and not the data column in the comparison. For JD Edwards World date columns, the JDBC driver converts date numbers to Julian date format. If the constant value is not a valid date, the JDBC driver issues an SQL exception error.
When relationships exist between two date columns, the JDBC driver makes no changes to either side of the relationship. Since both columns have the same relative data values, the comparison succeeds without revising the database values.
When relationships exist between a JD Edwards World date and a non-date column, the JDBC driver issues the following SQL exception message:
JDE: Incompatible data types for Date comparison
The JDBC driver manages database object names that contain special characters, for example @1QRYG in F82100. The JDBC driver treats all column and table object references as string object data.
The JDBC driver supports any JD Edwards World table that uses JD Edwards World data dictionary to define the table columns. The JDBC driver only considers tables that use a field reference file which maps to JD Edwards World Data Dictionary for data transformation.
The JDBC driver operates independent of the JD Edwards World release level. The JDBC driver extracts table specifications for all files from the DB2 SYSTABLES and SYSCOLUMNS system tables. This provides release independence for the table structures. All access to data files is via SQL statements and SQL is not dependent on file record formats or format structure. The JDBC driver achieves release independence for all other specification files by extracting specifications from the appropriate JD Edwards World table in the user's connection library list. As the data dictionary files changed between A7.3 and A8.1, the driver searches for the F9210 table in the library list. If it detects F9210 table, it assumes this is an A8.1 or later release and use the F9210 table for data dictionary information.
The JDBC driver dynamically identifies and extracts database specifications (table specs) and identifies JD Edwards World Data Dictionary specifications for a table attribute. The JDBC driver extracts table specifications for all files from the SYSTABLES and SYSCOLUMNS DB2 for i tables. This provides release independence for the table structures.
The JDBC driver is able to handle multiple data item prefixes in the same file, such as with the Z-files, with tables that can contain SHUPMJ, and SDUPMJ columns in the same physical or logical table definition.