timallen
Well Known Member
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.
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.