SQL Server Most Utilized Tables

Ever wondered which tables in your databases are being used most heavily?  Below is a script for SQL Server 2005/2008 that will tell you exactly that - which tables are utilized most and what percentage of that use is reads and what percentage is writes.  Heavily utilized tables are prime candidates for SQL Server's Table Partitioning or moving to another filegroup (not necessarily recommended with EnterpriseOne) and besides, it's just gosh-darn fun knowing what's going on inside your databases.

Big Caveat - This script makes use of the sys.dm_db_index_usage_stats SQL Dynamic Management View.  The values returned by this DMV do not persist beyond server restarts.  This means the information it gathers is only valid since the last SQL Server restart or (less likely) database attach.  So, if you just re-started your database server you are not going to get good numbers. Also, this information is kept in cache and is subject to memory pressure flushing, not a likely scenario but possible.

The script will gather index information from which one can infer table access information.  It uses the sp_MSForEachDB stored procedure to run through all databases on the instance, places the information in a temporary table, sums the values for index reads and writes, does a bunch of fancy math, rolls it up to the table level and returns the results to show reads, writes, percent of each and the type of index read.


--SQL Script begin
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(TableName NVARCHAR(255), UserSeeks DEC, UserScans DEC, UserUpdates DEC)
INSERT INTO #Temp
EXEC sp_MSForEachDB 'USE [?]; IF DB_ID(''?'') > 4
BEGIN
SELECT DB_NAME() + ''.'' + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates 
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN [?].sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
WHERE b.object_id > 100 AND a.user_seeks + a.user_scans + a.user_updates > 0
END'

SELECT TableName as 'Table Name', sum(UserSeeks + UserScans + UserUpdates) as 'Total Accesses',
sum(UserUpdates) as 'Total Writes',
CONVERT(DEC(25,2),(sum(UserUpdates)/sum(UserSeeks + UserScans + UserUpdates)*100)) as '% Accesses are Writes',
sum(UserSeeks + UserScans) as 'Total Reads', 
CONVERT(DEC(25,2),(sum(UserSeeks + UserScans)/sum(UserSeeks + UserScans + UserUpdates)*100)) as '% Accesses are Reads',
SUM(UserSeeks) as 'Read Seeks', 
CONVERT(DEC(25,2),(SUM(UserSeeks)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Seeks',
SUM(UserScans) as 'Read Scans',
CONVERT(DEC(25,2),(SUM(UserScans)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Scans'
FROM #Temp
GROUP by TableName
ORDER BY 'Total Accesses' DESC
DROP table #Temp
--SQL Script end


The returned results will look like this:

Table Name	Total Accesses	Total Writes	% Accesses are Writes	Total Reads	% Accesses are Reads	Read Seeks	% Reads are Index Seeks	Read Scans	% Reads are Index Scans
JDE_PRODUCTION.F06116	1196887461	15886068	1.33	1181001393	98.67	1181001096	100.00	297	0.00
JDE_PRODUCTION.F0618	934633544	995748	0.11	933637796	99.89	933637770	100.00	26	0.00
JDE_PRODUCTION.F4801	902692704	465333312	51.55	437359392	48.45	418433760	95.67	18925632	4.33
JDE_PRODUCTION.F0911	739173150	49489125	6.70	689684025	93.30	689283925	99.94	400100	0.06

From this we can determine what tables are being utilized heavily and in what manner.   The last four columns deal with methods of index read access and are a bit beyond the scope of this article but if you really want to discuss that just ask in the comments and I'll be glad to expand.

EnterpriseOne Table List With Column Information

I recently saw a request for a complete list of EnterpriseOne tables with column specifications and other information. Getting these details using a SQL script is not simple since JD Edwards seems to violate several standards in SQL-92 and database normalization best practices.

Since the required information is not kept in one table it is necessary to use SQL joins and foreign key relationships to gather all information into one result set. Here's the script:


--SQL Script begin
USE JDE_DEVELOPMENT
SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(40)) as 'Object Name'
, JDE900.OL900.F9860.SIMD as 'Table Description'
, JDE_DV900.DV900.F98711.TDSQLC as 'Column Name'
, JDE900.DD900.F9203.FRDSCA as 'Data Dictionary Descriptions'
, JDE900.DD900.F9210.FRDTAS as 'Column Length'
, JDE900.DD900.F9210.FROWTP as 'Data Type'
, JDE_DEVELOPMENT.TESTCTL.F0005.DRDL01 as 'Data Type Description'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN JDE900.OL900.F9860 on sys.objects.name = JDE900.OL900.F9860.SIOBNM
JOIN JDE_DV900.DV900.F98711 on JDE900.OL900.F9860.SIOBNM = JDE_DV900.DV900.F98711.TDOBNM
JOIN JDE900.DD900.F9203 on JDE_DV900.DV900.F98711.TDOBND = JDE900.DD900.F9203.FRDTAI
JOIN JDE900.DD900.F9210 on JDE_DV900.DV900.F98711.TDOBND = JDE900.DD900.F9210.FRDTAI
JOIN JDE_DEVELOPMENT.TESTCTL.F0005 on JDE900.DD900.F9210.FROWTP = LTRIM(JDE_DEVELOPMENT.TESTCTL.F0005.DRKY)
WHERE JDE_DEVELOPMENT.TESTCTL.F0005.DRSY = 'H98' and JDE_DEVELOPMENT.TESTCTL.F0005.DRRT = 'DT'
and JDE900.DD900.F9203.FRDSCA != '*SAME'
ORDER BY JDE900.OL900.F9860.SIOBNM
--SQL Script end


The results will look something like this:

Object Name	Table Description	Column Name	Data Dictionary Descriptions	Column Length	Data Type	Data Type Description
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNDCT	Document Type	2	2	String
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCTRY	Century	2	9	Numeric
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCTRY	Century	2	9	Numeric
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCNR1	Remaining in Range 1	15	9	Numeric

There will be a row in the results set for each Data Dictionary description of each column in every table in the chosen database.  You will be able to see the complete object name, including schema, the Object Librarian description, the name of the column according to Central Objects specs, the possible descriptions of the column according to Data Dictionary, the column length according to Data Dictionary specifications, the data type according to Data Dictionary specifications, and the data type description according to Data Dictionary specifications.

You may notice that you have duplicate entries in the Column Name and Data Dictionary Descriptions fields.  The EnterpriseOne Data Dictionary often has multiple descriptions for the same data item, determining which description to use in which table or form using code.  In general though, you can be pretty sure that the data returned will give you an idea of what the column is used for when you combine the Data Dictionary description with the table description.

If you wish to analyze a different database you must change the highlighted items. 

If you have any questions about how or why I did something in the script leave a comment and I'll do my best to answer.

SQL Server - Members of db_datareader not in db_denydatawriter

In an earlier series of articles on EnterpriseOne SQL Server default security we discussed the issue of Oracle granting permissions to PUBLIC that created a security exposure when placing users in the SQL Server db_datareader database role.  I advised you to place any user of an E1 database that is in the db_datareader role in the db_denydatawriter role also.  However, I didn't leave you with any quick way to determine what users are in the db_datareader role.  The script below quickly identifies users that are members of db_datareader but not also members of db_denydatawriter.  The script will only return results from databases where this situation actually occurs.

--SQL Script begin
EXEC sp_MSforeachdb 'USE [?]; IF
(SELECT COUNT (*) from (SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393) as a) > 0
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393'
--SQL Script end

Place the users identified by the script in the db_denydatawriter role to address the security exposure.

How Large Are My Tables?

Sometimes I am beating around on a SQL script that I really want to share with others. A couple of scripts I wrote to show the largest tables in a database and the largest tables in all databases on a SQL server is a perfect example.

The scripts make extensive use of the SQL 2005/2008 dynamic management view sys.dm_db_partition_stats. This view displays information about the space used to store data in a database. A concatenation method gives us the three-part name of the table and a simple JOIN brings in the Object Librarian name for the table. The results can be sorted either by row count or size in megabytes by commenting or uncommenting the ORDER BY clause.

The scripts are particularly useful for EnterpriseOne systems to identify tables that are candidates for archiving or tables that need maintenance/purging done. An example is the F98865, a table containing Work Flow Processes that should be purged periodically. With these scripts you can see what the largest tables in your SQL Server databases are.



For E1 Databases - Returns Object Librarian Description

Since we are primarily concerned with EnterpriseOne tables, the first version of the largest tables script is for a single database that is used by JD Edwards. A JOIN is used to bring in the Object Librarian Description from F9860.

--SQL Script begin
USE databasename
GO
IF DB_NAME() LIKE 'JDE%'
BEGIN
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name',
a.SIMD as 'Object Librarian Description', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
END
ELSE
PRINT 'Non-EnterpriseOne Database'
--SQL Script end

As configured this script will return a results set containing a three-part object name, the Object Librarian name, the number of rows and the size taken by each table's data and indexes.

A sample of the results:

SQL Object Name	Object Librarian Description	Rows	Object Size (MB)
JDE_DEVELOPMENT.TESTCTL.F98865	Task Instance	2649997	5065
JDE_DEVELOPMENT.TESTDTA.F0911	Account Ledger	2080214	5289
JDE_DEVELOPMENT.TESTDTA.F00165	Media Objects storage	1183096	2702
JDE_DEVELOPMENT.TESTCTL.F98860	Process Instance	1060162	2290

Again, the results can be sorted either by the row count (default) or object size. Simply change the commenting in the code to change the sort.


For Non-E1 Databases

Since we cannot join non-EnterpriseOne databases on the Object Librarian Object Name we can simply leave that portion out and still get the other information:

--SQL Script begin
USE databasename
GO
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
--SQL Script end

This yields similar results:

SQL Object Name	Rows	Object Size (MB)
ARCDTA.dbo.F42199	15483088	25242
ARCDTA.dbo.F42119	2420972	4345
ARCDTA.dbo.F4074	2184022	553
ARCDTA.dbo.F49219	814320	399

For all E1 Databases on a SQL Server

In some cases we may wish to gain insight into all EnterpriseOne table sizes in every JD Edwards database. For this one we create a temporary table, insert the results from the same query above but wrapped inside SQL Server's undocumented sp_MSforeachdb stored procedure, then read that temporary table.

In this case, since we are only interested in EnterpriseOne databases we specify that we only want database names like JDE%. This admittedly is a little kludgy and will not work on some releases but you are always free to change my code to your liking.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
[Object Librarian Description] varchar (60),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
IF DB_Name() LIKE ''JDE%''
BEGIN
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name ,
a.SIMD, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = ''U'' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
END'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results will look exactly like the above but will contain data from all E1 databases ordered by row count. Uncomment/comment the ORDER BY clauses to sort by Size.


For All Databases on a SQL Server

Finally, we get to the script that gives us table sizes and rows for each and every database on a SQL server. Since the results could contain both E1 and non-E1 objects we leave out the Object Librarian Description.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results from this script will appear a bit odd since they will contain a mix of E1 and non-E1 tables:

SQL Object Name	Rows	Object Size (MB)
Allegro.dbo.dbaudit	41948619	9715
Allegro72_Test.dbo.dbaudit	38409689	8905
Allegro80_BPC_Arch.dbo.physicalquantity	10241635	2273
JDE_DV812.DV812.F980021	4886400	11472

There you are - a couple of scripts to give one some insight into the size of tables in their databases, both EnterpriseOne and non-EnterpriseOne.

Quick SQL Table Backup

Occasionally one needs to perform an action in either EnterpriseOne or SQL Server that places the data in one or more tables at risk. Examples include an index or (obviously) a table generation in OMW, a direct update to the data using a query, the first run of a custom UBE that updates data, etc. Best practices dictate that you should get a backup of the data.

Rather than using the time consuming process of backing up the entire database, and in the absence of table-specific backup/restore tools, one can quickly and easily make a copy of the table using standard SQL T-SQL code.

The command is SELECT INTO, or actually a combination of the SELECT command and the INTO clause. The basic statement looks something like


SELECT *
INTO new_table_name
FROM old_tablename

or

SELECT *
INTO JDE_PRODUCTION.PRODDTA.F0101_BAK
FROM JDE_PRODUCTION.PRODDTA.F0101


While this simple piece of code is sufficient to perform the copy, several problems arise. First, the code above requires you to correctly specify both the source and target table. Also, the SELECT INTO method will create a new table but it will not overwrite a table with the existing name. So, unless you feel like checking for the existence of a table named "JDE_PRODUCTION.PRODDTA.F0101_BAK" every time you perform this action I would suggest using the code below.

In this version we get a uniquely named table as long as you don't run it twice within a minute. The name will consist of the original table name plus characters representing the year, month, day, hour and minute. To do this, we grab the current date and time using GETDATE, convert it to text, replace characters like spaces, commas, etc. that we do not want in the table name, build this into a string, then use EXEC (@SQL) to run the query.

The code looks like this:

--SQL Script begin
DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SET @Tablename = 'JDE_PRODUCTION.PRODDTA.F0101'
SET @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SET @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,'
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL
--Remove dashes on the line below to execute
--EXEC (@SQL)
--SQL Script end

and generates a query that looks like this:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F01012


In the example above we have specified the three part name "JDE_PRODUCTION.PRODDTA.F0101" in the @Tablename variable. You would change this variable to specify your table. Figured I'd mention that 'cause you just never know.

As constructed above, the query runs SELECT @SQL and will simply return the query string we built. It will not execute the copy. Once you are satisfied that the query string built is correct, remove the dashes from in front of EXEC (@SQL) to run the table copy.

You will see something like this when properly executed:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F0101

(1 row(s) affected)

(65436 row(s) affected)


Once you have completed the quick table copy you can now continue with the action that placed the data in danger knowing that you have a copy of the data.

Some caveats apply: the copy only brought over the data. No indexes exist on the backup copy of the table. Also, you now have an easily forgotten extra copy of what might be a very large table. Clean it up as a part of your scheduled maintenance, set a reminder to delete it later or use this method to automatically identify SQL Table Backups for removal.


Related postings:

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html

Determine Objects Owned by a SQL Login in all Databases

I was trying to delete a SQL login and received the following message:

"You cannot drop the selected ID because that login ID owns objects in one or more databases"

I wrote a little script that utilizes the always useful sp_MSForEachDB stored procedure to walk through each database and find the objects owned by a specified user.

SQL 2000

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sysobjects where uid =
(select uid from sysusers where name = ''username'')'

--SQL Script end


SQL 2005

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sys.objects where schema_id =

(select principal_id from sys.database_principals where name = ''username'')'

--SQL Script end

It is probably best to use CTL-T to have the output in text instead of grid format.

Script file for queries used in this article is here:

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

All SQL script files are here:

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

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

View EnterpriseOne Objects & Versions Checked Out

This little script tells you in a single result set:

• Objects Checked Out
  
• Versions Checked Out in DV812, PY812, PD812
  
• Objects created never checked in
  
• Versions created never checked in
  
• Objects with tokens held
  

I always run this just prior to an implementation team leaving the customer site to ensure that they have done the necessary cleanup of objects and projects. I also combine this with xp_sendmail in a SQL Agent to send a report out monthly to developers. More on xp_sendmail at a later date.

This script makes extensive use of the OPENDATASOURCE, a transact-sql command that Microsoft says "Provides ad hoc connection information as part of a four-part object name without using a linked server name." Its use allows you to query multiple SQL servers from a single Query Analyzer/Management Studio connection.

Note: In the OPENDATASOURCE section there is a variable that must be modified to specify your connection information. Change "Data Source" to your SQL Server:

Data Source=sqlserverhostingjde_dv812;integrated security=sspi'

The script is to be run in Query Analyzer or Management Studio with a connection to the SQL Server hosting the JDE812 database. Change the highlighted variables to valid values for your system.

--SQL Script begin
--Objects Checked Out
PRINT 'Objects Checked Out'
PRINT''use JDE812
select siuser as 'User ID', siobnm as 'Object Name ', simkey as 'Machine', sipathcd as 'Path Code'
from OL812.F9861 where SISTCE != '1'
order by siuser

--Versions Checked Out in DV812
PRINT''
PRINT''
PRINT 'Versions Checked Out in DV812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment' from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserverhostingjde_dv812;integrated security=sspi'
).jde_dv812.dv812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Versions Checked Out in PY812
PRINT ''
PRINT ''
PRINT 'Versions Checked Out in PY812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment' from
OPENDATASOURCE('SQLOLEDB','Data Source=sqlserverhostingjde_py812;integrated security=sspi').jde_py812.py812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Versions Checked Out in PD812
PRINT''
PRINT''
PRINT 'Versions Checked Out in PD812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment'
>from OPENDATASOURCE('SQLOLEDB','Data Source=sqlserverhostingjde_pd812;integrated security=sspi').jde_pd812.pd812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Objects created never checked in
PRINT''
PRINT''
PRINT 'Objects created but never checked in'
PRINT''
use JDE812
Select POOMWUSER as 'User', cast(POOMWPRJID as char(20)) as 'Project', cast(POOMWOBJID as char(10)) as 'Object', POOMWMKEY as 'Machine', POPATHCD as 'Path Code '
from SY812.F98222
where POOMWOT !='UBEVER' and POOMWOST=01 and POOMWCHS='1'
order by POOMWUSER, POOMWOBJID, POPATHCD

--Versions created never checked in
PRINT''
PRINT''
use JDE812
PRINT 'Versions created but never checked in'
PRINT''
Select POOMWUSER as 'User', cast(POOMWPRJID as char(10)) as 'Project', cast(POOMWOBJID as char(20)) as 'Object', POOMWMKEY as 'Machine', POPATHCD as 'Path Code'
from SY812.F98222
where POOMWOT ='UBEVER' and POOMWOST=01 and POOMWCHS='1'
order by POOMWUSER, POOMWOBJID, POPATHCD

--Objects with tokens held
PRINT''
PRINT''
PRINT 'Objects with tokens held'
PRINT''
use JDE812
Select POOMWUSER as 'User', cast(POOMWOBJID as char(20)) as 'Object', POOMWPRJID as 'Project'
from SY812.F98222
where POOMWPOS1 != '0'
order by POOMWUSER, POOMWOBJID, POPATHCD
--SQL Script end



Script file for queries used in this article is here:

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

All SQL script files are here:

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

Measuring SQL Server Cumulative Disk IO

At some point you will have to troubleshoot a SQL Server issue that is caused by excessive disk input/output (IO). SQL Server's sensitivity to disk bottlenecks makes it crucial that one know how to identify which specific SQL process (SPID) is consuming IO resources.

To show instantaneous but cumulative measurement by active SPID:


--SQL Script begin
if exists (select * from tempdb.dbo.sysobjects where name = '##who2')
drop table tempdb.dbo.##who2
GO
create table tempdb.dbo.##who2
(
[Spid] int not null,
[Status] nvarchar(130) not null,
[Login] nvarchar(130) not null,
[Host Name] nvarchar(130) null,
[BlkBy] nvarchar(130) null,
[DB Name] nvarchar(130) null,
[Command] nvarchar(130) null,
[CPU] int not null,
[IO] int not null,
[Last Batch] nvarchar(130) null,
[Program Name] nvarchar(130) null,
[Spid2] int not null
)
insert into tempdb.dbo.##who2 exec('sp_who2')
select * from tempdb.dbo.##who2
order by [IO] desc
--SQL Script end


or


--SQL Script begin
if exists (select * from tempdb.dbo.sysobjects where name = '##threads')
drop table tempdb.dbo.##threads
create table tempdb.dbo.##threads
(
[Spid] smallint not null,
[Thread ID] int null,
[Status] nvarchar(10) not null,
[Login Name] nvarchar(10) null,
[IO] bigint not null,
[CPU] int not null,
[MemUsage] int not null
)
insert into tempdb.dbo.##threads exec('dbcc sqlperf(threads) with tableresults, no_infomsgs')
select * from tempdb.dbo.##threads order by [IO] desc
--SQL Script end


The first script shows application name and other information generated by SP_WHO2. The second script is generally quicker and provides more concise IO information. Both will provide you with results that include the amount of IO accumulated by a process. Also, both scripts return results sorted by amount of IO in descending order.

Once you have used the above scripts to identify SPIDs for processes that have used large amounts of IO you can use the SPID in SP_WHO2 or SP_LOCK to gather more information about the process:

Example:

sp_who2 432
sp_lock 432



Script file for queries used in this article is here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts/MeasuringcumulativeIO.sql?attredirects=0

All SQL script files are here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts