List Submitted Jobs From All Servers

There are times when CNC administrators need to view a list of all submitted jobs in a single result set. E1 tools or standard SQL scripts do not satisfy this requirement but the script below will allow one to view all submitted UBE's from all servers in one list.

The script makes use of the OPENDATASOURCE method, Integrated Security authentication, and the Union operator to allow the script to be run on any SQL server, without the need to hard code password information, and combines the results into a single grid.

Notes: In the OPENDATASOURCE section there are two variables that must be modified to specify your connection information.

1- Change "Data Source" to your SQL Server:

Data Source=sqlserver_hosting_batch_server's_server_map

and

2- Change schema name or object owner in the fully qualified object name section:

(jde812.svm812.f986110)


Add or remove Union and Select sections in the script to suit your needs. Every batch server that has a separate server map requires its own section.


--SQL Script begin
--Batch Server 1
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server1_server_map;integrated security=sspi'
).jde812.svm812.f986110

union all

--Batch Server 2
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server2_server_map;integrated security=sspi'
).jde812.svm812a.f986110

union all

--Batch Server 3
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server3_server_map;integrated security=sspi'
).jde812.svm812b.f986110

union all

--Batch Server 4
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server4_server_map;integrated security=sspi'
).jde812.svm812c.f986110

union all

--Batch Server 5
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server5_server_map;integrated security=sspi'
).jde812.svm812d.f986110

order by cast(jcactdate as float(6)) desc, cast(jcacttime as float(6)) desc
--SQL Script end


The result set will appear as a single list of all jobs submitted to all E1 servers, in descending order by date of last activity.



Functional Possibilities of the Script

In addition to being used to view submitted jobs from all servers in one place, the concept of the script could be used to correct what I consider to be a significant shortcoming in EnterpriseOne.

A major design goal of E1 (fka OneWorld) was the separation of the technology from the user. The realization of this goal is obvious in many places including database independent data sources, OCM's and multi-platform code. Most end users have no idea what "platform" the "system" runs on and care only that it runs......all day, every day.

I would say that the goal of isolating the user from the configuration was wonderfully accomplished in most cases but EnterpriseOne, in some applications still (as of 8.12/8.97) makes an end user choose a server from a list in order to view their submitted job.

Imagine yourself as an accounting clerk, skilled primarily in the art of numbers and financial concepts submitting a report and being faced with the following screen in order to find the results:



Figure 1 View Job Status - Work With Servers (P986116|ZJDE0001 W986116A)


A list of servers, data sources and Server Map data sources being presented to an end user is contrary to the concept of separation of function and technology and clearly takes us well away from this design goal. Why in the world would it ever be necessary for an E1 user to see or know about this information?

Utilizing the concept of abstracting the servers when users are trying to find their submitted job would bring the View Job Status - Work With Servers application in line with the original design goal of separating technology from functionality and would greatly simplify the end user task of finding their report output.

Script file for queries used in this article is here:

http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/WSJfromallservers.sql?attredirects=0

All SQL script files are here:

http://blogfiles.karamazovgroup.com/Home/sql-server-scripts