JD Edwards "Julian Date"

What we (you, me, everyone in the JD Edwards universe) have always referred to as "Julian Date" is actual an Ordinal Date.

http://en.wikipedia.org/wiki/Ordinal_date


It is not, however, an ISO 8601 compliant Ordinal Date as ISO 8601 requires the use of 4 digit years.

http://en.wikipedia.org/wiki/ISO_8601#Ordinal_dates


The Julian Date is actually "...the interval of time in days and fractions of a day, since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."

http://en.wikipedia.org/wiki/Julian_date


Do you suppose that 2454992.14583 ago, some guy was standing there with a sundial and said "Now!" to start the whole thing ticking?



Now you know...


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

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. To do so, 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:

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 below to execute
--EXEC (@SQL)

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 or set a reminder to delete it later.


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

Using Alternate EnterpriseOne Serialized Objects Tables

No downtime, middle of the day full serialized objects generation.

When CNC admins have time to sleep they dream of getting more sleep. They imagine that there is the possibility of performing administrative tasks at some time of the day other than during The Maintenance Window.

Generating a full set of serialized objects is not as critical as it was prior to Dynamic Web Generation and Auto Package Discovery, both introduced in the 8.96/8.12 release, but if you are on a Tools Release prior to 8.96 or simply wish to generate a full set of serialized objects without disrupting your users or possibly your social life, try using two sets of the serialized objects tables (F989998/F989999).

The concept is that *PUBLIC will be using one set of serialized objects tables while an EGEN user will be using another set during the full generation.

Setup

Create an E1 user EGEN with associated security record, role membership, etc. Ensure the EGEN user has access to the J* environments.

Using OMW, generate the F989998 and F989999 serialized objects tables in the Business Data datasource for each environment.

Create copies of System OCM's for F989998 and F989999 for all J* environments so that you have OCM's for both tables for both Central Objects and Business Data for both *PUBLIC and EGEN, one active and one inactive.

The easiest way to get the OCM's copied correctly is to copy the existing F989998 and F989999 OCM for Central Objects, changing the Data Source to Business Data and the System Role to EGEN during the copy and then make those OCM's active. Then copy all J* environment F989998/F989999 OCM's, changing only the System Role from *PUBLIC to EGEN or from EGEN to *PUBLIC during the copy. This step will create the proper inactive OCM's.

You should end up with something that looks like this:

Environment	Object Name	Object Type	Primary Data Source	System Role	Object Status
JDV812	F989998	TBLE	Central Objects - DV812	EGEN	NA
JDV812	F989998	TBLE	Business Data - TEST	*PUBLIC	NA
JDV812	F989998	TBLE	Business Data - TEST	EGEN	AV
JDV812	F989998	TBLE	Central Objects - DV812	*PUBLIC	AV
JDV812	F989999	TBLE	Business Data - TEST	EGEN	AV
JDV812	F989999	TBLE	Central Objects - DV812	*PUBLIC	AV
JDV812	F989999	TBLE	Central Objects - DV812	EGEN	NA
JDV812	F989999	TBLE	Business Data - TEST	*PUBLIC	NA
JPD812	F989998	TBLE	Central Objects - PD812	EGEN	NA
JPD812	F989998	TBLE	Central Objects - PD812	*PUBLIC	AV
JPD812	F989998	TBLE	Business Data - PROD	EGEN	AV
JPD812	F989998	TBLE	Business Data - PROD	*PUBLIC	NA
JPD812	F989999	TBLE	Business Data - PROD	EGEN	AV
JPD812	F989999	TBLE	Central Objects - PD812	*PUBLIC	AV
JPD812	F989999	TBLE	Business Data - PROD	*PUBLIC	NA
JPD812	F989999	TBLE	Central Objects - PD812	EGEN	NA
JPY812	F989998	TBLE	Central Objects - PY812	EGEN	NA
JPY812	F989998	TBLE	Central Objects - PY812	*PUBLIC	AV
JPY812	F989998	TBLE	Business Data - CRP	*PUBLIC	NA
JPY812	F989998	TBLE	Business Data - CRP	EGEN	AV
JPY812	F989999	TBLE	Business Data - CRP	EGEN	AV
JPY812	F989999	TBLE	Central Objects - PY812	*PUBLIC	AV
JPY812	F989999	TBLE	Central Objects - PY812	EGEN	NA
JPY812	F989999	TBLE	Business Data - CRP	*PUBLIC	NA

Again, the concept is that *PUBLIC will be using one set of serialized objects tables while EGEN will be using another set during the generation. When the full gen is done, you will have a completely populated set of serialized objects tables waiting to be used while the other set continued to be used during the full generation. An OCM swap and a cache flush will put the newly generated tables into use without interrupting users.

Execution

Log into egenerator using the EGEN E1 user, comment out the [JDBJ-SPEC DATA SOURCE] stanza in jdbj.ini to use the WebDev machine for egeneration (Thanks to John Bassett for this tip.), generate core objects, perform a full gen, reindex the F989998 and F989999 tables, then perform an OCM swap and flush the OCM and serialized objects caches.

The OCM swap will make the "alternate" serialized objects tables active for *PUBLIC and inactive for EGEN and will make the "original" serialized objects tables inactive for *PUBLIC and active for EGEN, ready for the next full gen and swap.

Example

Before:

Environment	Object Name	Object Type	Primary Data Source	System Role	Object Status
JDV812	F989998	TBLE	Central Objects - DV812	EGEN	NA
JDV812	F989998	TBLE	Business Data - TEST	*PUBLIC	NA
JDV812	F989998	TBLE	Business Data - TEST	EGEN	AV
JDV812	F989998	TBLE	Central Objects - DV812	*PUBLIC	AV
JDV812	F989999	TBLE	Business Data - TEST	EGEN	AV
JDV812	F989999	TBLE	Central Objects - DV812	*PUBLIC	AV
JDV812	F989999	TBLE	Central Objects - DV812	EGEN	NA
JDV812	F989999	TBLE	Business Data - TEST	*PUBLIC	NA

After:

Environment	Object Name	Object Type	Primary Data Source	System Role	Object Status
JDV812	F989998	TBLE	Central Objects - DV812	EGEN	AV
JDV812	F989998	TBLE	Business Data - TEST	*PUBLIC	AV
JDV812	F989998	TBLE	Business Data - TEST	EGEN	NA
JDV812	F989998	TBLE	Central Objects - DV812	*PUBLIC	NA
JDV812	F989999	TBLE	Business Data - TEST	EGEN	NA
JDV812	F989999	TBLE	Central Objects - DV812	*PUBLIC	NA
JDV812	F989999	TBLE	Central Objects - DV812	EGEN	AV
JDV812	F989999	TBLE	Business Data - TEST	*PUBLIC	AV

Now go dream about getting more sleep.

Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

Change Integrated Solutions (WebSphere) Console Timeout

In IBM's Integrated Solutions Console (formerly known as WebSphere Console), the administrative interface for WebSphere 6.1, the default console user inactivity timeout is 30 minutes. I happen to think this is a bit short, particularly since most anyone using the console is a highly trusted user, generally an IT administrator who is well-versed in computer and network security practices.

For this reason, and since I find it such a hassle to come back to the console after a short time and be told that "Your session has become invalid", I change the timeout to something I think is more reasonable, like 720 minutes.

Please note that I am referring to the timeout for the admin console, not session timeouts.

If you are using WebSphere Network Deployment (and you should be) edit this attribute in the following file on the Network Deployment machine:

C:\Program Files\IBM\WebSphere\AppServer\profiles\Dmgr01\config\cells\NetworkDeploymentservernameCell01\applications\isclite.ear\deployments\isclite\deployment.xml

Set the attribute invalidationTimeout to the desired value, in minutes, where the maximum value is -1 (do not time out)


Restart the WebSphere service on the Network Deployment machine.


If you are not using Network Deployment.....you should be, so go implement ND and follow the directions above.


If you are still on WebSphere 6 (and maybe 5):

Edit the ${WAS_HOME}/systemApps/adminconsole.ear/deployment.xml file to change the invalidationTimeout attribute value to the desired session timeout. The default is 30.

Restart the application service.


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

Auto Restart WebSphere Application Servers

So you've installed the EnterpriseOne HTML application on your fancy WebSphere Application Server (uppercase) and you are doing some restart/reboot testing. If you are using WebSphere Network Deployment, as you absolutely should be, you may notice that if you reboot the entire box, the previously running application servers (lowercase) do not restart even though the node agent service is set to automatically start.

This is because the WebSphere Integrated Solutions console (fka Network Deployment console) and not the node agent controls the state of the application servers.

The restart state behavior of the application servers is determined by what IBM calls the Monitoring Policy settings. These settings tell the node agent what it should do with the java processes after both an internal (to WebSphere) abnormal shutdown and a complete node restart.

The settings we are concerned with are Automatic restart and Node restart state. The first controls how WebSphere will react to an internal application server failure and the default is set to True. The second is how WebSphere will handle a complete node start (reboot) and the default is set to Stopped. This means that if you restart the physical box (or it reboots itself) you will have to manually start the application servers or WebSphere cluster of application servers in the WebSphere Integrated Solutions console.

My philosophy on automatic starts is that it should be disallowed only in situations where automatically restarting will either undoubtedly fail (ex: failing back an E1 enterprise server on a W2K3 cluster) or could cause corruption. Since WebSphere is mostly at the presentation layer and since Oracle is making extensive progress with Failed Transaction Recovery, I prefer that the application servers start automatically on node restart.

To have the application servers automatically restart, set the Node restart state to RUNNING in the Servers> Application Servers> server_name > Server Infrastructure > Java and Process Management > Process Definition > Java and Process Management > Monitoring Policy section of the WebSphere Integrated Solutions console. click OK, save your changes, and test.

Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

Simplified E1 URL

Wanna see an ugly URL?

http://thisismyservername:82/jde/owhtml


Can we please stop doing this to our EnterpriseOne users? Not only is it tedious and non-descriptive, it looks amateurish.

The use of aliasing and Virtual Hosting techniques in DNS, IBM HTTP Server (Apache), and WebSphere allows E1 JAS instances to be referenced by name instead of by TCP Port Number. DNS mappings, Apache VirtualHosts, and WAS Host Aliases are created to enable this solution. This gets rids of the port numbers.

Paper is here: http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/whitepapers/E1%2CEliminatePortNumbersJAS.pdf?attredirects=0


In addition, we can get rid of the "/jde/owhtml" portion of the URL by using the Apache DocumentRoot directive.

Paper for this part is here: http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/whitepapers/E1%2CSimplifiedURLusingDocumentRootv1.00.pdf?attredirects=0

These white papers are a bit dated in the WebSphere area but the methods remain the same.

When you get done you will have proper URL's that look something like this:

http://e1development
http://e1prototype

or

http://e1dv.domain.com
http://e1py.domain.com

or

http://whatevernameyourlittleheartdesires


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

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 of 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

Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear