BLDJDELF is a utility from Sequel Software (a HelpSystems company) that greatly simplifies the process of building Sequel views and Queries over JD Edwards application database files.
BLDJDELF is also the name of the command you will issue to run the utility.
JDE uses a dictionary that stores the decimal positions of their fields (which have no externally defined decimal positions) as well as column heading and text information. This allows each JDE customer to set up their database to have the optimal number of decimal positions, and useful field text for the data fields in their files based on their particular business. This is a good feature for JDE customers, but it makes it nearly impossible to use an external query product, or to transfer JDE information to other sources. Customers must look up the decimal positions and divide the field by the appropriate factor for the data to be scaled properly.
BLDJDELF (Build JDE Logical File) Command
Sequel Software’s BLDJDELF utility provides an effective solution to this problem. With the BLDJDELF command, you can create a new logical file for any JDE physical file you want. The new logical file incorporates the proper field attributes—including useful field text. The new logicals can be used by Sequel, other query products, and other data transfer tools to simplify the query definition process.
Prompt the BLDJDELF command to access it’s parameters:
The BLDJDELF parameters are as follows:
|JDEFILE (dictionary defined file)|
|This is the name of the JDE physical file that you want to create a new logical file over. JDE physical files are described in the JDE dictionary, hence the term ‘dictionary defined file’. You do not need to know the names of the files that implement the JDE dictionary, but the database library containing the dictionary must be on your library list to run the command successfully.|
|Library||The name of the library containing the physical file over which the new logical file will be built.|
|VIEW||The name of the logical file to be created. This parameter also controls which source file member the SQL statement is written to. (see next parm)|
|Library||The library name for the new logical file.|
|SRCF||The name of the source file where SQL instructions for creating the new logical file will be written.|
|Library||The library containing the source file.|
|UDC||The JDE dictionary offers a very flexible scheme for supplying descriptive text for various user defined codes via the UDC coding feature of the JDE dictionary. BLDJDELF can automatically incorporate UDC code support into the logical files it creates and allows up to 64 UDC code fields in a single logical file. Specific code fields of interest can be listed in this parameter, or BLDJDELF can automatically identify the UDC code fields in the file. UDC descriptions are incorporated by generating appropriate join specifica tions to the F0005 dictionary file where code descriptions are maintained.|
|DATE||Specify date fields to be translated into DB2/400 date fields. Date fields in the “cyyddd” format used in the JDE database can be converted to DB2/400 date data type fields. You can list up to 31 fields or use the special value *ALL to cause all date fields to be translated.|
|ZERODATE||If your system is operating at Version 4 Release 2 or later, you can use this parameter to convert zero values in the numeric date fields. Without this feature, records that have a zero date value will show the “base” value of December 31, 1899. Use a specific date value or one of the special values listed below.|
|ROWSEC||Specify whether or not to include row security filters in the created file. Both JDE World and JDE One World provide mechanisms for controlling access to database records. If you want to build the view logical to include the rules in the JDE dictionary, specify an appropriate value for this parameter. Access to the records in the JDEFILE will then be controlled by the ranges defined for the user’s profile, group name, or *PUBLIC references in the JDE World Business Unit Security file (F0001) or the One World Security Workbench Table (F00950).|
|REPLACE||Specify whether the existing view logical file should be replaced.|
You run BLDJDELF on each library/physical file (or generic* group of files but not *all) you need, and name the resulting logical file using the “VIEW” parameter on the command. BLDJDELF generates SQL/400 (not SEQUEL) statements in the source file you designate in mbr (filename). The replace view parameter deletes an existing logical file of the same name before proceeding with the create step.
The BLDJDELF command then runs the DYNSQLF command to run the SQL/400 statements stored in the source file. DYNSQLF creates the new logical file and then applies the dictionary’s column headings and text to its fields.
UDC Code Considerations
For a number of JDE files, specifying UDC(*ALL) can generate source code for more than the maximum allowed 64 UDC fields. The best way to deal with this is to explicitly list the UDC fields you want translated in a particular logical file. If you do not know which UDC code fields you may want to work with, you can use the UDC(*ALL) option as a tool to generate source for all fields and then use the text and column heading sections of the resulting source code as a tool for documenting the UDC code fields. Once you determine which UDC fields you need, you can then rerun the BLDJDELF process and explicitly list the UDC fields of interest.
Another way to deal with too many UDC fields is to let BLDJDELF generate the source code for all UDC fields, then edit the source and remove the fields you do not need. This approach requires some familiarity with the SQL source used to translate UDC fields into the related text. Editing the source involves changing related parts in different sections of the SQL statement and it is fairly easy to make mistakes. However, it is possible to remove the unneeded UDC field information from each section and then run DYNSQLF to create the logical file after you save your source code changes. If you want to edit the SQL source, the following description will help you get started.
The first section of the CREATE VIEW statement is a listing of all field names to be included in the resulting logical file. Each UDC code field in this section is followed by 2 description fields. The description field names are derived from the code name by appending “D1” for the first and “D2” for the second. On line 3 of the example below, the 3rd field, SDDCTO, is a UDC code field for order type. The next 2 fields, SDDCTOD1 and SDDCTOD2 are the description fields for the order type code field. It is tedious, but you can reliably locate the UDC code fields by searching through this first section looking for this pattern of field names.
The next section, the SELECT clause, defines the fields listed in the first section. The UDC code descriptions are easily identified as consistently named field pairs, such as “UDCxx.drdl01,UDCxx.drdl02”, where xx will be a numeric suffix that increments from 01 through 31 and higher if the file has more than 31 UDC code fields.
The next section, the FROM clause, specifies the file joining relationships for obtaining the UDC descriptions. Each pair of description fields has an associated join relationship like the one on lines 2001– 2003 below. For each UDC code translation you want to remove from the view, you should remove the associated join relation. The correspondence between join relations and UDC code fields is easy to determine since they follow the same UDCxx naming system.
The final two sections provide text and column heading information for all fields. All fields are listed in the same sequence as in the first 2 sections. Any UDC code descriptions you remove from the first sections must also be removed from the LABEL ON sections.
Example Result SQL
1.00 : Create view definition 2.00 CREATE VIEW QTEMP/F4211 3.00 (SDKCOO, SDDOCO, SDDCTO, SDDCTOD1, SDDCTOD2, SDLNID, SDSFXO, 4.00 SDMCU, SDCO, SDOKCO, SDOORN, SDOCTO, SDOCTOD1, SDOCTOD2, | 32.00 SDPID, SDJOBN, SDUPMJ, SDTDAY) AS 1000.00 SELECT 1001.00 SDKCOO, SDDOCO, SDDCTO, UDC01.drdl01, UDC01.drdl02, 1002.00 DECIMAL(SDLNID*0.001,6,3), 1003.00 SDSFXO, SDMCU, SDCO, SDOKCO, SDOORN, SDOCTO, | 1068.00 SDURAB, SDURRF, SDTORG, SDUSER, SDPID, SDJOBN, SDUPMJ, 1069.00 SDTDAY 2000.00 FROM FOCUS/F4211 2001.00 LEFT OUTER JOIN f0005 UDC01 ON 2002.00 UDC01.drsy=’00 ‘ AND UDC01.drrt=’DT’ AND 2003.00 CONCAT(‘ ‘,SDDCTO) = UDC01.drky 2004.00 LEFT OUTER JOIN f0005 UDC02 ON 2005.00 UDC02.drsy=’00 ‘ AND UDC02.drrt=’DT’ AND 2006.00 CONCAT(‘ ‘,SDOCTO) = UDC02.drky | 5000.00 : Set column headings 5001.00 LABEL ON COLUMN QTEMP/F4211 5002.00 (SDKCOO IS ‘Order Co’, 5003.00 SDDOCO IS ‘Order Number’, 5004.00 SDDCTO IS ‘Or Ty’, 5005.00 SDDCTOD1 IS ‘Or Ty Description’, 5006.00 SDDCTOD2 IS ‘Or Ty Description’, | 5329.00 SDTDAY IS ‘Time of Day’); 6000.00 : Set field text 6000.00 : Set field text 6001.00 LABEL ON COLUMN QTEMP/F4211 6002.00 (SDKCOO TEXT IS ‘Order Company (Order Number)’, 6003.00 SDDOCO TEXT IS ‘Document (Order No, Invoice, etc.)’, 6004.00 SDDCTO TEXT IS ‘Order Type’, 6005.00 SDDCTOD1 TEXT IS ‘Order Type Description’, 6006.00 SDDCTOD2 TEXT IS ‘Order Type Description’, | 6224.00SDUPMJTEXTIS‘Date - Updated’, 6225.00SDTDAYTEXTIS‘Time of Day’);
DYNSQLF (Dynamic SQL File) Command
DYNSQLF can be run independently of the BLDJDELF command. This gives users a way to run SQL/400 statements that they manually enter into the source file. This will be useful if you want to create view logical files, but don’t want ALL the underlying physical fields included. It can also be used to create a join logical or grouped result that can be used in SEQUEL’s FROM clause. DYNSQLF does not require that customers have SQL/400 installed in order to run the statements.
Prompt the DYNSQLF command to access it’s parameters:
The DYNSQLF parameters are as follows:
|SRCFILE||This is the name of source file that contains SQL statements to be run by DYNSQLF.|
|Library||The name of the library containing the source file.|
|SRCMBR||The name of the specific source member containing SQL statements for DYNSQLF.|
|IGNERR||The SQL processor issues a return code for each SQL statement processed by DYNSQLF. The code indicates success or failure of the SQL request. IGNERR(*YES) causes DYNSQL to continue processing the remaining SQL statements in the member if an error occurs. With IGNERR(*NO), DYNSQL stops processing whenever a return code indicates that the request failed.|
How it works
The process is driven from the command through a CL program (BLDJDELF) that validates the input. If the source file (SRCF) isn’t found, it is created. An additional “working” source file (QTEMP/@@$SQLSRC) is also created. If a generic name is used as the JDEFILE, a list of files matching the criteria is built and processed.
The CL program attempts to locate the JDE data field specification file on the library list. BLDJDELF checks first for F9210 (OneWorld) and second for F9201 (World). If neither can be found, an escape message is issued and the process terminates. The library list is also checked for JDE dictionary files F9202, F9203, and F0005.
Once the validating steps are passed, the BLDJDELF view is opened to join the appropriate dictionary file (F9201/F9210) to the alpha description file (F9203), the field text file (F9202), and the system cross reference file (QADBILFI). Only records from the file named in the JDEFILE parameter are selected from the system cross reference.
If the ROWSEC value is specified as *JDEDICT, then both the One World Security Workbench table (F00950) and the One World dictionary (F9210) must exist. If the ROWSEC value identifies an MCU field name then the One World dictionary must not be installed.
The view builds a complete field description for each field using the information in the JDE dictionary. The field description contains the “true” data specifications (length, decimal positions), column heading and text, and editing.
The command processing program calls the BLDJDELF1 RPG program to read records from the view data path and write SQL to the source file. The SQL statements in the source file are a CREATE VIEW, and two LABEL ON statements; one for column heading and another for field text. The SQL in the CREATE VIEW statement contains the steps necessary to:
- Convert numerics from the stored Pkd(15,0) form to the “true” format indicated by the dictionary
- Convert date values stored in JDE’s cyyddd format into date data type values and a CASE definition to handle zero date values as indicated by the ZERODATE parameter
- Create a join specification for each description field (UDC) to be resolved to the UDC code definition file (F0005)
- Create a subselect to filter records based on the business unit security field and the rules contained in the Business unit security file (F0001) or Security Workbench Table (F00950) and the user group defined in the Library Lists file (F0092)
The final step in the process copies the SQL from the workfile to the user’s file specified on the SRCF parameter and uses DYNSQLF to run the SQL statements in the source. This will build the view and create the labels.