E1 Tips

Below is the SQL that can be used to change the database schema of all tables in an SQL Server Database.  We use it when restoring databases from Production to CRP or Development.

USE JDE_CRP_NEW  --TODO: Change this to DB Name

SET NOCOUNT ON
DECLARE @NewSchema nvarchar(256)
DECLARE @OldSchema nvarchar(256)

Set @NewSchema = 'CRPDTA';  --TODO: Change to New Schema 
Set @OldSchema = 'PRODDTA'; --TODO: Change to Old Schema 

--Don’t need to change anything below this
DECLARE @TableName nvarchar(256)
DECLARE TableName CURSOR FOR
SELECT  DISTINCT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @OldSchema

OPEN TableName

FETCH TableName INTO @TableName

WHILE @@Fetch_Status = 0

   BEGIN

   Print 'Modifying ' + @TableName 
   EXEC('ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName)

   FETCH TableName INTO @TableName
   END

CLOSE TableName

DEALLOCATE TableName

I’ve been moving several EnterpriseOne databases to SQL Server lately.  In order to do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables.  Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:

iSeries – SQL query to get a row count on all files in an iSeries library:

 SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS 
FROM QSYS2.SYSTABLESTAT 
WHERE TABLE_SCHEMA = 'PRODDTA' 
AND NUMBER_ROWS > 0 

SQL Server – SQL query to get a row count on all tables in an SQL Server database:
  
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows 
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id 
AND i.indid < 2 
WHERE i.rows > 0 


NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.
Are there any methods that you use to do a quick data integrity check?

I’ve been doing a lot of work with SQL Server lately and have found the following SQL that can be used to drop (delete) all tables in an SQL Server database:

  EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  

Do you have any really handy SQLs?  Post them in the comments.

I really used this page a lot when working with SQL and dates & times.

DB2 Basics: Fun With Dates and Times

We have noticed many orphaned subsystem jobs piling up in the F986113.

In tracking them down we found that the way we stop the subsystem jobs each night for things like backups, nightly processing, etc. may have been causing jobs to just sit there and not be processed. The process that was in place just deleted the subsystem processing job records from the F986113.

I did some research and found that an article on JDEList.com says to essentially use SQL to set the subsystem processing jobs to an “ERROR” state.

That didn’t seem quite right to me either. So, I set out to find out what the application does to end the subsystem jobs and found that it inserts a record into the F986113 that triggers the job to end.

Below is the that can be used to end the subsystem jobs gracefully:

 insert into svm900.f986113 (SSPID,SSVERS,SSSBMDATE,SSSBMTIME,SSJOBSTS,SSOPCR,SSENHV,SSJOBNBR,SSUSER,SSORGHOST,SSJOBPTY,SSEXEHOST)                                                        select SSPID,SSVERS,SSSBMDATE,SSSBMTIME+1,'R','W',SSENHV,SSJOBNBR,'JDEPKG',SSORGHOST,'0',SSEXEHOST FROM SVM900.F986113 WHERE SSPID='R43500' 

For a while now we have had an issue where our subsystem Purchase Order Print job (R43500) will not run in JPD900 but it will in PD900. After going through the debug log I found a something:

--UBE--[0]-- SS0000009:Subsystem Limit is reached. 
--UBE--[0]-- SS:Cannot start new subsystem.

Above this notification I found an SQL was ran looking for subsystem jobs in the F986113:

SELECT COUNT(*) 
FROM SVM900/F986113 
WHERE ( SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 
AND SSEXEHOST = MODENT AND SSJOBSTS = S )

The results of this SQL was 3. Which is the UBE Subsystem Limit specified in the Enterprise Servers JDE.INI [UBE] section (Configurations – Batch Processing from Oracle Server Manager). 3 is also the default value.
However, there is a problem with this SQL querie. It only finds the number of subsystem jobs that have been submitted. It doesnt check to see if they are running. If all subsystem jobs have been stopped properly, there would be zero records returned from this SQL. However, if you have had any issues with subsystem jobs or even if things dont get cleaned up correctly when bouncing services, you can end up with subsystem jobs in an error state.
The only way to clean up these records is to go into the database and remove them:

DELETE FROM SVM900/F986113 
WHERE (SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 
AND SSEXEHOST = MODENT AND SSJOBSTS = S AND SSOPCR=E)

Do you have any tips for SubSystem jobs? If so, please leave them in the comments.

You can use the below SQL to add an EnterpriseOne environment to every role or group in your installation:

INSERT INTO SY910/F0093 (LLUSER,LLLL,LLSEQ,LLMNI)
SELECT ULUSER, 'PD910','1.00','' FROM SY910/F0092

Use the SQL statement below to find all the objects that are checked out:

 select * from ol900/f9861 where sistce=3            

This method can be used to copy any records in a database but the issue that I was trying to resolve was to copy 8 records in the Object Configuration Management (OCM) file, F986101, of one object to another object.

INSERT INTO SY900/F986101( 
OMENHV, OMOBNM, OMDATP, OMUGRP,OMOAPP, OMDATM, OMSY, OMSTSO, 
OMPID, OMFUNO, OMUSER, OMJOBN, OMOCM2, OMUPMJ, OMUPMT) 
(SELECT OMENHV, '<NEW_OBJECTID>', OMDATP, OMUGRP, OMOAPP, 
OMDATM, OMSY, OMSTSO, 'SQL', OMFUNO, '<USERID>', '<USERID>', 
OMOCM2, <6DIGITDATE>, <6DIGITTIME> 
FROM SY900/F986101 WHERE OMOBNM='<ORIGINAL_OBJECTID>') 

When using this SQL you will need to replace the following:

• <NEW_OBJECT> – The target object to which you would like to copy the records.
• <USERID> – (optional) The UserID that you would like to credit making this change.
• <6DIGITDATE> & <6DIGITTIME> – (optional) The JDE Date and Time that you are making the change.
• <ORIGINAL_OBJECTID> – The ObjectID of the OCM record that you wish to copy.

This method can also be used to copy OCM records from one environment to another by setting the environment in the select statement and where clause.

While trying to upgrade from EnterpriseOne 8.11 SP1 to EnterpriseOne 9.0, we also wanted to upgrade our Deployment server to Windows Sever 2008 64bit.  So, that means that in order to runt he upgrade UBEs we needed to get the ODBC Connections onto the new server.  So, I exported the desired ODBC connections from the Windows Registry [HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI].  Then modified the file using a text editor so that when we imported them into our new 64bit server the ODBC Connections would be loaded into the 32bit ODBC Connection Manager.

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INISystem - 811] was changed to [HKEY_LOCAL_MACHINESOFTWAREWow6432NodeODBCODBC.INISystem - 811]

The 32bit ODBC Connections can be managed using the ODBC Connection Manager that is found at C:WindowsSysWOW64odbcad32.exe

I also had to change the Driver specified for each entry:

"Driver"="C:\WINDOWS\system32\cwbodbc.dll" changed to "Driver"="C:\Windows\SysWOW64\cwbodbc.dll"