How to find a OneWorld table in SQL Server

I use the Knowledge Garden document "Major OneWorld Technical Tables" a lot. But at times it is difficult to know how to contruct a query in SQL Server against a table in the document, since you have to know which catalog and schema the table is stored in.

I use the following cheat sheet to help me out. It has listed each of the data sources and its corresponding SQL Server catalog and schema:

Business Data - ENV: jde_production.proddta
Central objects - PATH CODE: jde_pd7333.pd7333
Control Tables - ENV: jde_production.prodctl
Data Dictionary - B7333: jde7333.dd7333
ENTERPRISE - B7333 Server Map: jde7333.svm7333
System - B7333: jde7333.sys7333
Object Librarian - B7333: jde7333.obj7333
Versions - PATH CODE: jde_pd7333.pd7333

An example of how to use it: you want to query the F9860 Object Librarian Master table to get a list of all tables and their descriptions. It is listed as being in the "Object Librarian" data source. This corresponds to jde7333.obj7333, so I would do this

1) Enter iSQL:
isql -S DATASERVER -U obj7333 -P obj7333 -d jde7333

usage here is:
-S server name
-U user
-P password
-d database
(options are case sensitive)

2) Find out the column names (you could do this in the UTB, or with the stored procedure sp_help, but I am comfortable with this method):

select top 1 * from jde7333.obj7333.f9860

3) Write the query
select siobnm, simd from jde7333.obj7333.f9860 where sifuno ='TBLE'

In this last query, it is not necessary to qualify the table name if you have entered specifying the correct database and user, but it is nice to be able to query tables from different databases without having to exit and reenter iSQL. Also it is mandatory to do this if you are doing a query which joins tables from two different databases.

Hello Tim,
One thing I didn't get; why don't you use P80010 to find OW tables ... within OW?

I use SQL when I want to do something more complex involving more than one environment or path code-- for instance, to find whether objects in a package in PD7333 exist in a package in PY7333. The P80010 would tell me which columns are in a table and their types, but not what catalog and schema they are in to do queries in SQL. OneWorld is powerful, but to do complex queries, it is much simpler to do them in SQL (and I have much more experience with databases than I do with OneWorld).

By the way, I just ran the P80010, and it doesn't seem to work for me-- I put in the F0101, and the application recognizes the table (it tells me its description), but pushing "Find" doesn't find any columns. Do I need to run the Cross-Reference update for this to work?

Thanks.

Hello Tim,

you have to follow this document:0TT-00-0058. It explanis how to configure the tables use by P80010.

Good Luck
Ximo Lozano
Valencia - Spain
Xe, XU7-SP20M1, ES-AS400 V5R1, DEP-W2K

Nice One, Ximo, it works a treat.

Salutacions des de la Ciutat Condal!