JD EDWARDS INTERVIEW QUESTIONS

Q1 when should a table be generated?

Generate a table after designing a new table or modifying an existing table by adding or removing columns. Note: Generating a table will drop and recreate the table which will remove all pre-existing data in the table. To ensure this data is not lost, you must export your data, generate the table, and then import it back in.  The changes need to be deployed to the server and client in a package.

When adding or changing indexes for a table there is no need to generate the table.  For an index change you can generate the indexes, and this will not affect the data stored in the table. 

Q2 Where to find the table definition?

• Go into design of the table from Object Management Workbench.  Once in design, choose the File menu and select print. This will provide you with a hard copy. (If you wish to create a soft copy, create a file printer and then print to that.)
• The Table Columns Browse application P80010 is an application that was created to view table definitions and indexes.  

Q3 How to view the data in tables?

The Universal Table Browser (UTB) can be used to view data in tables. This tool lets you verify the existence of data in a table as well as determine the table's structure. UTB uses JDEBASE API's to retrieve data from the database, making it independent of the database you access.

Q 4. Where is information located on table relations within PeopleSoft Enterprise one software?

There is limited information available on table relations within the PeopleSoft Enterpriseone software. You can find some information on Menu/Task GH9073 - Data Model Illustrations.  There are also some documents located in the b7/system/models directory on the system.  

Q5. what are the naming conventions for column prefixes in a table?

In a PeopleSoft EnterpriseOne software table, a column in a table represents a data item. The Table Design Aid tool assigns a table column prefix to each column or data item. If a table is used exclusively in PeopleSoft EnterpriseOne software, the column prefix assigned to the table does not have to be unique because PeopleSoft EnterpriseOne software qualifies the column with the table name. 

For example, table F0101 has a column prefix of AB and Address Number is alias AN8 in that table. The PeopleSoft EnterpriseOne software references AN8 as F0101_ABAN8. If another table F470101 uses the same prefix and AN8, the PeopleSoft EnterpriseOne software references that column as F470101_ABAN8 making it unique as well.

If using Address Number (AN8) in a table that is part of a coexistence environment, the table prefix must begin with "Q" as the first character in order to make it unique. The second column prefix character needs to be unique only if the column will interface with another "Q" prefixed table.

Q6. What type of table format should be used in co-existence? (PeopleSoft World and PeopleSoft EnterpriseOne) 

Note: This only applies to PeopleSoft EnterpriseOne software release Xe.

• PeopleSoft World generates tables on SVR through command JDECRTPF and PeopleSoft EnterpriseOne generates tables using the SQL command CREATE TABLE.
• The record format of a PeopleSoft World table starts with the letter I and does not have a SQL File Type of TABLE. The indexes on the PeopleSoft World table are created as Logical File - FxxxxLA, FxxxxLB, FxxxxLC,... etc. ( use command DSPFD ).
• The record format of a PeopleSoft EnterpriseOne table starts with the letter F, has a SQL File Type of TABLE and is restricted to a MAXMBRS(1). The indexes are created as Logical Files - Fxxxx_1, Fxxxx_2, Fxxxx_3, .. etc. Fxxxx_1 is always the primary unique index of the PeopleSoft EnterpriseOne table. A Primary Key Constraint (i.e. primary keys cannot contain NULL) is also added to the PeopleSoft EnterpriseOne table ( use DSPFD command).
• PeopleSoft EnterpriseOne can access either a PeopleSoft World or PeopleSoft EnterpriseOne table. PeopleSoft World can only access tables created through PeopleSoft World SVR ; otherwise the associated RPG program reading the table will issue a Level Check error on the File Level Identifier.
• To use a PeopleSoft World table in PeopleSoft EnterpriseOne, you need to create the PeopleSoft EnterpriseOne Table Specifications through Object Management Workbench - Table Design Aid - define the table columns and indexes - matching PeopleSoft World Logicals (if you are using this index in PeopleSoft EnterpriseOne) and new PeopleSoft EnterpriseOne ones as needed. After the table specs creation, this creates the .H file specifications for C (Fxxxx.h stored in the b7 folder). Under Table Operations, generate Indexes i.e. Fxxxx_1, Fxxxx_2, Fxxxx_3,...etc. You will have duplicates logicals both on PeopleSoft World and PeopleSoft EnterpriseOne (FxxxxLA = Fxxxx_1, FxxxxLB=Fxxxx_2) depending on how you defined the indexes; you can then remove the duplicate PeopleSoft EnterpriseOne logical because the corresponding PeopleSoft World Logical will work for PeopleSoft EnterpriseOne.
• When generating indexes for existing PeopleSoft World tables, a Primary Key (PK) Constraint does not apply for PeopleSoft World tables; therefore you will get an error that the Create Index failed but in fact all the table indexes have been created except for the PK constraint (use WRKOBJ Fxxxx*).

Q7. why aren't changes to a table showing up?

See Question 1 above about the need to generate a table when changes are made such as adding or moving columns.  Note generating a table does a SQL Table Drop and Create, and any data in the table will be lost.  The client should back up any required data.  

AS/400 only -   DB2 stores historical SQL commands to more efficiently retrieve data. The SQL packages need to be deleted so that DB2 creates new SQL commands for the new table definition.  This applies anytime new indexes are added to a table or columns are added or removed from a table.  

Q8. When does the .h file get created?

The .h file gets created as soon as the user saves the table. 

Q9. Why not to use data items with item occurrences?

The purpose of creating a data item with item occurrences is that the associated data dictionary items are created automatically.  For example if a data item of DL is created with ten item occurrences, the system will create data items DL01 - DL10.  These associated data items can be used within the table, but the actual data item with the specified number of occurrences (DL) should not be used within the design of a table. It will result in an incorrect table column length.

For example, if data item DL, which is 30 long and has item occurrences of 10, is used in a table, then this will create a table column of 300 (30 x 10).

Q10. What is the limit to the number of tables for complex join business views?

Three tables is the limit to the number of tables for a complex join business view such as a left outer or right outer join business view.

Q11.  When you create a business view or modify an existing one, do you have to generate it? 

No. One of the biggest myths about business views is that they need to be generated. All that generating a business view does is allow that business view to be used in a business function to access data. Within the PeopleSoft EnterpriseOne software, we rarely access data in a business function through a business view instead the data is accessed based on the table. If a business view will be used within a business function to access data, then the business view needs to be generated.  Otherwise, this is not a requirement when creating a business view.

Question: How do I copy versions from one environment to another?

Before the OneWorld Xe release, versions were transferred using R9830512, but this UBE was disabled in Xe. The recommended method is to use Object Management Workbench (OMW) to transfer versions. To copy versions with OMW:

      1.     Create activity rules for copying versions. 

      2.     Add the versions to the project (there is an “add all” feature in OMW).

      3.     Add a project.

      4.     Advance the project to the correct status.

If you prefer to use R9830512, add the following lines to the client JDE.INI:

[OMW]

R9830512_UBE_VERSION_COPY=W

Question: After applying an ESU, the update package build fails with the following error message:

/* CER ERROR #3026: Unable to retrieve Business Function Header */

When a business function is built, any header files not found in the update package are taken from the parent package, not the parent path code. Check the parent package for missing or out-of-date header files; if they are not present do a full build of the parent package. Building update packages adds objects to the parent package, but if an ESU containing a new or changed header file was not built, these types of errors occur. Data structure errors in builds could also be caused by this type of problem.

Question: I have applied JD1234 to DV7333 and PD7333. I want to create an update package for both environments but there is only one JD1234 package defined.

Go to the “Work with Packages” form and use the “copy” option to create a duplicate package with a unique name. As with any ESU package, be sure to update the package properties (path code and based-on package) before building the package.

 Re-Creating Large Amounts of OneWorld Indexes

Summary

When OneWorld indexes are missing for any part of the OneWorld database, this process addresses a relatively fast and effortless way to re-create all of the missing indexes. The recommended way to re-create any OneWorld index is through the Object Librarian program using the index generate option when a table is drilled down to. If 10's or 100's of indexes are missing this method can become quite tedious and time consuming. An alternative to this approach is to follow the steps outlined below for re-creating large amounts of missing or corrupted indexes. The skill set needed to be successful in this process is someone that is comfortable and knowledgeable with the native SQL command utility to the OneWorld RDBMS (i.e. SQLPlus for Oracle, Query Analyzer for SQL Server, and STRSQL and CL for the AS400).

Steps

      1.     Ensure all OneWorld users are logged off the system. 

      2.     Create a backup of the System Data Source owned F9843 table as follows:

Log in as the OneWorld system account sysb733 or the appropriate account on the AS/400.

create table F9843_orig as select * from F9843 ..... (for Oracle)

select * into f9843_orig from f9843 ..... (for SQL Server)

crtdupobj obj(F9843) fromlib(sysb733) objtype(*FILE) tolib(sysb733) newobj(F9843_orig data(*YES) ..... (For AS/400)

      3.     Delete all of the records in the F9843 table by issuing: delete from sysb733.F9843

     4.     Create an insert script to populate the F9843 with records of tables that need their indexes re-created. The script should have repeated use of the following command:

insert into sysb733.F9843 values ('F0101',' ',' ',4,0,' ',' ',' ',0,' ',' ',' ',' ',' ',0,0,' ',0)

Include in the script one of these commands for each table you want to re-create indexes for.

      5.     Run the script in interactive SQL to populate the F9843 with records.

      6.     Log into OneWorld from a Client Workstation.

      7.     Go to Batch Versions by typing BV in the fast path.

      8.     Type in R98404, then hit find.

      9.     Choose version XJDE0001 as the version to be executed then click Select.

     10.  On the advanced form exit, Over ride the execution location to run locally.

     11.  On the Processing Options Window-Environment Tab, type in the correct environment that you want indexes built for. On the Update Tab, the first option should have a value of 2; the second option should have a value of A.

      12.  After the UBE is finished, you should have new indexes for all of the tables you inserted into the F9843 table.

      13.  Clean up the temporary copy of the F9843 table by the following commands:

drop table sysb733.F9843;

commit;

create table sysb733.F9843 as select * from sysb733.F9843_orig ..... (For Oracle)

select * into f9843 from f9843_orig ..... (For SQL Server)

crtdupobj obj(F9843_orig) fromlib(sysb733) objtype(*FILE) tolib(sysb733) newobj(F9843) data(*YES) ..... (For AS/400)

      14.  Perform the appropriate selects against your database to be sure all of the desired indexes were created.