same, same… but different

These scripts will allow you to build packages from the DEP812 environment.  If you don’t run these, you won’t see the correct enterprise server when you are defining a package from the DEP812 environment.  These will rename the entfries that need to be done.

These must be run against the JDEPlan812 DB.

update dbo.f98611 set omdatp = 'NEWSERVER - Logic'
where omdatp = 'OLDSERVER - Logic' ;

update dbo.f98611 set omdatp = 'NEWSERVER - RTE'
where omdatp = 'OLDSERVER - RTE' ;

update dbo.f98611 set omdatp = 'NEWSERVER - XAPI'
where omdatp = 'OLDSERVER - XAPI' ;

update dbo.f98611 set omdatb = 'NEWSERVER - 812 Server Map '
where omdatb = 'OLDSERVER - 812 Server Map ' ;

update dbo.f98611 set omsrvr = 'NEWSERVER' where omsrvr =
'OLDSERVER' ;

update dbo.f98611 set omdatp = 'NEWSERVER - 812 Server Map'
where omdatp = 'OLDSERVER - 812 Server Map' ;

update dbo.f98611 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

update dbo.f986101 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

update dbo.f986101 set omdatp = 'NEWSERVER - Logic'
where omdatp = 'OLDSERVER - Logic' ;

update dbo.f986101 set omdatp = 'NEWSERVER - RTE'
where omdatp = 'OLDSERVER - RTE' ;

update dbo.f986101 set omdatp = 'NEWSERVER - XAPI'
where omdatp = 'OLDSERVER - XAPI' ;

update dbo.f9650 set mmmkey = 'NEWSERVER' where mmmkey = 'OLDSERVER' ;

update dbo.f9650 set mmdatp = 'NEWSERVER - 812 Server Map'
where mmdatp = 'OLDSERVER - 812 Server Map' ;

update dbo.f9651 set mdmkey = 'NEWSERVER' where mdmkey =
'OLDSERVER' ;

Enterprise server rename, the works burger!

Here is a complete set of SQL for an enterprise server rename, well for me at least.  It takes the form of a PL/SQL block.  It does printers, job queues, OCM’s data sources, LDAP… the works!  Please enjoy (use at your own risk too btw!!!)

spool c:\shannon\sql\MachineRename_SY.txt 

SELECT ora_database_name from dual;

set time on
set echo on 
set flush on  
set feedback on
set timing on

--servermap

update svm812.f98611 set omsrvr = 'NEWSERVER' where omsrvr =
'OLDSERVER' ;

update svm812.f98611 set omdatp = 'NEWSERVER - 812 Server Map'
where omdatp = 'OLDSERVER - 812 Server Map' ;

update svm812.f98611 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

update svm812.f986101 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

--system

update sy812.f98611 set omdatp = 'NEWSERVER - Logic'
where omdatp = 'OLDSERVER - Logic' ;

update sy812.f98611 set omdatp = 'NEWSERVER - RTE'
where omdatp = 'OLDSERVER - RTE' ;

update sy812.f98611 set omdatp = 'NEWSERVER - XAPI'
where omdatp = 'OLDSERVER - XAPI' ;

update sy812.f98611 set omdatb = 'NEWSERVER - 812 Server Map '
where omdatb = 'OLDSERVER - 812 Server Map ' ;

update sy812.f98611 set omsrvr = 'NEWSERVER' where omsrvr =
'OLDSERVER' ;

update sy812.f98611 set omdatp = 'NEWSERVER - 812 Server Map'
where omdatp = 'OLDSERVER - 812 Server Map' ;

update sy812.f98611 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

update sy812.f986101 set omdatp = 'NEWSERVER'
where omdatp = 'OLDSERVER' ;

update sy812.f986101 set omdatp = 'NEWSERVER - Logic'
where omdatp = 'OLDSERVER - Logic' ;

update sy812.f986101 set omdatp = 'NEWSERVER - RTE'
where omdatp = 'OLDSERVER - RTE' ;

update sy812.f986101 set omdatp = 'NEWSERVER - XAPI'
where omdatp = 'OLDSERVER - XAPI' ;

update sy812.f9650 set mmmkey = 'NEWSERVER' where mmmkey = 'OLDSERVER' ;

update sy812.f9650 set mmdatp = 'NEWSERVER - 812 Server Map'
where mmdatp = 'OLDSERVER - 812 Server Map' ;

update sy812.f9651 set mdmkey = 'NEWSERVER' where mdmkey =
'OLDSERVER' ;

update sy812.f96511 set skmkey  = 'NEWSERVER' where skmkey =
'OLDSERVER' ;

--If you have LDAP enabled

update sy812.f00928 set lcentloc = 'NEWSERVER' where lcentloc = 'OLDSERVER' ;

update sy812.f00928 set lcentloc = 'NEWSERVER' where lcentloc = 'OLDSERVER' ;

--Printers

update sy812.f986167 set DPEXEHOST = 'NEWSERVER' where DPEXEHOST = 'OLDSERVER' ;

--Job Queues
update sy812.f986130 set qcexehost = 'NEWSERVER' where qcexehost = 'OLDSERVER' ;

spool off

Running Oracle SQL Scripts

Here are some commands at the top of the script that might make the auditors job much easier.

spool outputFile.txt  --creates a log of what you are doing

SELECT ora_database_name from dual;

set time on – show you the time the statement ran
set echo on  --echo the statement
set flush on   --does nothing
set feedback on – show variable substitutions
set timing on – show you how long the statement took to run

--Body of script goes here

spool off  --Derrrr, turns of the spooling

quit –- logs you out of SQL plus!

UBEOverride vs runtimeCache dirs

UBEOverride has the values of data selection, sequencing and processing options for each job that is submitted to the server.  If the job finishes with ‘D’, the DIR and the ZIP file are deleted.

runtimeCache dir has the results of converting the full package relational database specs to TAMs for each UBE and UBE_VERSION combo.

The dir should only remain for the duration that the jobs runs.  If jobs end in ‘E’ the directory stays (8.98.1.1 AIX)

back to basics – string comparison

so, here are some questions…

is string “ XXXX” > “*    “ ?  NO, “*    “ is bigger

is string “ XXXX” > “*****” ? NO, “*****” is bigger

simple test in oracle is:

select 1 from dual where ' XXXX' > '*    '

if you see 1, it’s true.  If you see “no rows selected”, it’s false.

* has a ascii value of 42

<space> has a value of 32

a has a value of 97

You can tell the ascii value with ‘SELECT ASCII(‘*’) from DUAL ;  (note that those funny quotes will not work).

All of the above will help you with row security.

Is archive logging enabled?

select log_mode from v$database;

That’s it!

Transaction server, 8.98, WAS, JAS, RTE, TLA

A bit more information about the transaction server.

Everything has been running for a couple of months without any problems.  Regular scrutiny of F90710 was revealing 0 records, regular scrutiny of MQQueues via SIBExplorer (oh how I love that program), is revealing 0 records in all of the queues – or at least when the system is very busy there are a couple of records in the various queues.

We’ve had one incident of everything falling over, but it was repaired quickly with a transaction server restart…  until…

We started to get a bunch of messages staying at status 4 in the sy812.f90710

sometimes > 5000 a day.  This is nay good I was thinking.  What has changed?

It finally occurred to me (like when Bart was trying to explain to Homer about Sideshow Bob’s plans to kill aunt Selma) that an additional server must be stealing the messages…  And because the prod txn is the only one that should be getting the messages, this is the problem. I’m still trying to jump through the hoops to understand more about this model.

I think that the txn server is updating the records to 4, but then because the message never gets to the txn server – they are not getting deleted (nice and reliable I guess). So what we do have is a bunch of messages at status 4 that the transaction server does not know about.  This is “sub optimal”.

The transaction server seems to poll the F90710 table for any type 3 records.  When it finds them it updates them to 4.  It then grabs the message and puts the message into the MQ queue and then deletes the message once this has been done.  We are having the problem that another txn server is grabbing the messages (I think) and not able to process them.  Therefore they are staying at status 4.  All I need to do is update all of them to a 3 and they process successfully.

A couple of risks with the above is that the messages in the F90710 might contain old data (or might contain new data??) and therefore the old data might be going to all of the servers?  I can’t be sure of this.

So, pretty amazing really.  If you are getting messages staying in the F90710 at status 4 and you have multiple transaction servers, I’d be checking on them and making sure that your OCMs are right.  Make sure that your other txn servers are polling the correct F90710 tables!

Package deploy and locks and more info

I know that I’ve talked about deploying packages and locks and things before…  I don’t want to cover old ground, but I do want to record more information.  We’ve talked about a scenario where you have PD and PP installed on the one enterprise server.  If there are UBE’s running in PD, you can still deploy to PP without affecting business continuity. 

I believe that being a good technical consultant is balancing these two requirements (system availability and technical productivity).  That is that they can get the maximum amount of work done with minimum disruption to the business.

Anyway, off my soap box.  I had a situation the other day where I was deploying to PP in a similar environment as mentioned above.  The hardware was supporting two pathcodes with a instance of JDE.

I deployed the package and waited… and waited…  sweated… waited…

I got very nervous and looked at the deployment logs and saw that the package was trying to get locks on the server.  I looked at task manager and saw that one of the kernels was “spinning it’s wheels”, it has 100000’s of seconds on it’s runsheet, obviously a runaway.  I killed the kernel in question and almost immediately the system took a HUGE gulp of air and things were back to normal.

So, what does this mean…  We’ll it means that you definitely lock the entire instance of JDE when you deploy a package, not just the pathcode that you are using.  I guess the small justification is that if UBEs were running in PD, the package would still go through.

So, my recommendation is changing.  If you are deployed for a pathcode that shares the PD instance, don’t deploy until the system is quiet.  Check your UBE’s before hitting deploy, check for any kernels that are “spinning their wheels”.

<rant mode>

• I personally think that the new 812 deployments are a huge step backwards.  I took in the marketing hype and thought that it was going to be magical, but in the hard light of day, the process is quite cumbersome and prone to errors. 
• It takes way too long to deploy (although this is a configurable option). 
• I get seemingly random “network errors”, “internal server” errors etc. 
• I like the generation option, but let’s be honest…  Why is not on the deployment server and an option with the build / deploy?  Generate java objects should be another tick box when you are defining the build.  They should generate the objects to a couple of temp F98999% files and then copy then in when deploying.  Job done!  Generation server??  What a waste of resources and a burden on the environment!

</rant mode>

Moving your media objects

This is not as easy as you think.  The path to some of the objects is written into the F00165.  So, you might move all of the files and they just might not work anymore…  Arrgghhh – what are you going to do…

You put your thinking cap on, and decide that you could rename the new location (via cname) the same as the old location.  ie.

\\oldMediaObjLocation

and you want to whack them on a new server, say \\newserver

and you still want to reference your chares with \\oldMediaObjLocation

So, you’re smart – you use a cname or an alias which basically (via DNS) points oldMediaObjLocation  to newserver – but it does not work.

I get prompted for a username and password or something about the server already exists, which it does not.

So, now you need to do this for 2003:

1. Create the CNAME record for the file server on the appropriate DNS server, if the CNAME record is not already present.
2. Apply the following registry change to the file server. To do so, follow these steps:
      1. Start Registry Editor (Regedt32.exe).
      2. Locate and click the following key in the registry:
         HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
      3. On the Edit menu, click Add Value, and then add the following registry value:
         Value name: DisableStrictNameChecking
         Data type: REG_DWORD
         Radix: Decimal
         Value: 1
      4. Quit Registry Editor.
3. Restart your computer.

Then you’ll be able to reference unc shares that are named via a cname.

So, oyu can move your media objects and they will all still work!  Woo hoo!

P98613 – low level cache awesomeness

What a cracker of a table controlling a cracker of a concept.  How many rows of each table is going to be cached by JDE.  It’s often a problem that a customer changes a period for a customer and it takes hours for this to propagate through the JDE infrastructure, not matter how often you clear the web cache.

Remember that we deal with two very distinct caches in JDE. 

1.  JDBj based cache (clear cache from SM or SAW)

2.  JDE middleware cache  (wsj – row exit for clear cache)*

*I’ve seen this one crash servers so be careful

If you have constant problems with particular tables (F0010 company master for example), fire up P98613.  Take F0010 out of the table.  Not more caching of company information.

Remember that this is a system table, not a data table..  So if you change it in DV, it’s going to change it EVERYWHERE!

Oracle Dates confusement

I know that confusement (sp??) is not a real word, but I have heard it used by a lot of people, so I think that it should be a real word.

Anyway, because my experiences of oracle have been mainly with JDE, I don’t understand the oracle date type very well.  Say I want to group data by an oracle date day, how do I do that???  Because the oracle date secretly contains time…

With the adoption of UTIME, I need to update my skills to be able to take this on…  So google search here I come.

My query is simple, I want to fine all of the records in the F90710 that seem to be sticking.  Ie. the trigger message is not making it to the transaction server for these messages and they are stuck!!!

so I want something like:

select count(1), ETEVNTST, ETEVNTNAME, ETEVNTTIME from sy812.f90710 group by ETEVNTNAME,ETEVNTST, ETEVNTTIME

And I get many separate lines for every day, so I need to convert the date to a day I guess.

select count(1), ETEVNTST, ETEVNTNAME, trunc(ETEVNTTIME, 'MONTH')
from sy812.f90710
group by ETEVNTNAME,ETEVNTST, trunc(ETEVNTTIME, 'MONTH')

This did nothing for me, I think it was grouped by month.  A little handy I guess.  I tried using ‘DAY’ in the TRUNC function, was not what I needed.

So, am going to try:

select count(1), ETEVNTST, ETEVNTNAME, to_char(ETEVNTTIME, 'DDMMYY')
from sy812.f90710
group by ETEVNTNAME,ETEVNTST, to_char(ETEVNTTIME, 'DDMMYY')

That’s the ticket.  The use of tochar is awesomely simple. HH:MM:SS are all available.

Collaborative portal for authentication

I need to support long usernames for a 8.98.1.1 site via MAD LDAP.  I’m thinking that I can get the collaborative portal to do the full length username authentication, and then map a field in the LDAP to be the E1user id.  This will then be passed into the authentication token.  Job done.  I know that a portal install is not going to be the most efficient mechanism for this, but it might just be okay.

Should I look into GSSAPI?  Can it do username translations?

“Alternatively you can choose to enable Single Sign-On and NOT configure LDAP to work with the JDE security kernel. This is key and is the point I have been trying to make. You can do this and enable GSS-API to work with Microsoft's Kerberos implementation (turn on Integrated Windows Authentication in the Internet Explorer browser) and experience SharePoint like Single Sign-On with JDE. I do this for my employer and wouldn't have it any other way for them. Others may choose another method and I wouldn't have any problem with that.“

from http://www.jdelist.com/ubb/showthreaded.php?Cat=&Number=140716&page=&view=&sb=5&o=&vc=1

Anyway, watch this space. A solution will be on it’s way.

long running queries in oracle, it’s simple

If you know the session you are looking for (say using a query like the previous post), then with the SID, use the below to get all of the info about it’s progress.

select sofar, time_remaining/60 as time_rem_mins from v$session_longops where sid = 1630

Awesome!

session hogs

stolen from http://www.dba-oracle.com/oracle_tips_top_session.htm

select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;

Big tables in SQLServer

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

 

That’ll give you the ones with lots of rows, then use the following SP, to get the sizes:

sp_spaceused 'PRODDTA.F4211'
sp_spaceused 'PRODDTA.F49211'
sp_spaceused 'PRODDTA.F42199'
sp_spaceused 'PRODDTA.F4111'
sp_spaceused 'PRODDTA.F41051'

How big is that oracle table??

Use this query first if you want the size of 1 table and it’s indexes:  Note that you need to escape the _, because that is usually a wildcard for any character.

select sum(bytes)/(1024*1024) from dba_segments where segment_name = 'F0911' or segment_name like 'F0911!_%' escape '!'

spool a .sql file with the command below, and that will generate all of the statements for you. 

set pagesize 0

set echo off

select 'select sum(bytes)/(1024*1024)' || ', ''' || table_name || '''' || ' from dba_segments where segment_name = ' || '''' || table_name || '''' || ' or segment_name like ' || '''' || table_name || '!_%' || '''' || 'escape ' || '''' || '!' || ''';'  from all_tables where table_name like 'F%' 

This will generate a bunch of statements like:

select sum(bytes)/(1024*1024), 'F0040Z1' from dba_segments where segment_name = 'F0040Z1' or segment_name like 'F0040Z1!_%'escape '!';     

Above is tables and indexes

select 'select sum(bytes)/(1024*1024)' || ', ''' || table_name || '''' || ',''' || owner || '''' || ' from dba_segments where segment_name = ' || '''' || table_name || '''' || ';'  from all_tables where table_name like 'F%'

Above is tables only

i hate vista so much it hurts (sometimes)

Generally if I’m using find, I hate it more…  I hate it like I want to punch it… 

But this has allowed my hate to recede (somewhat)

http://www.microsoft.com/windows/products/winfamily/desktopsearch/technicalresources/advquery.mspx

Actually wait, I still hate it.  The above is GREAT for finding files…  But cr@p for finding things in files…

I hate statements like “windows does it automatically” – my kingdom for grep!!!

http://support.microsoft.com/kb/932989

That lists about 1 million things that could be wrong.

That is it, I’ve had enough.  I’m going to use grep for win32.

View csv from wsj not working

Problem

In HTML client you launch WSJ (Work With Submitted Jobs), select your Enterprise/Batch Server, select your finished report  and click on View CSV. Nothing occurs.  On a FAT client, this works correctly.

Solution

To fix the issue, you will need to do the following:

1. Add the following parameter in your JAS.INI
        [OWWEB]
   UBEContentDisposition=true

Using ServerManager, click on WEB Runtime configuration and Check 'UBEContentDisposition'

    2.  Add the following Windows Registry key to the machine your using to View CSV:
              [HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/csv]
              extension=.csv
If issue persists, check your Internet Explorer settings:

• Tools
• Internet Options
• On Security tab, click on Custom level, scroll to Downloads section and select: Automatic prompting for file download
• Enable and apply
• Close Internet Explorer and restart

You should now be able to open your CSV file.

This was copied from oracle support, ID 781462.1

tracing tricky connect issues in SQLServer 2008

 

SQL Server 2008 contains a new feature designed to help troubleshoot particularly tricky connectivity problems. This new feature, the Connectivity Ring Buffer, captures a record for each server-initiated connection closure, including each kill of a session or Login Failed event. To facilitate efficient troubleshooting, the ring buffer attempts to provide information to correlate between the client failure and the server’s closing action. Up to 1k records in the ring buffer are persisted for as long as the server is online, and after 1000 records, the buffer wraps around and begins replacing the oldest records. The Connectivity Ring Buffer’s records are exposed by a DMV which can be queried:

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

from http://blogs.msdn.com/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

But…  I’m on 2005 and am getting a lot of

“SQL Native Client TCP Provider An existing connection was forcibly closed by the remote host” in my APP server and BAT server jde logs.

I think that it might be network related, but I’ll keep you posted.

Right, so we’ve come a long way with this error.

It seems that this is related to either:

EnableTCPChimney = 0  Follow http://support.microsoft.com/kb/945977 to disable

Another registry change DisableTaskOffload=1  http://blogs.technet.com/brad_rutkowski/archive/2007/08/10/how-to-know-if-tcp-offload-is-working.aspx

There is a lot of documentation on some bad NICs http://support.microsoft.com/kb/942861

Typically, this problem occurs when the network adapter uses the Broadcom 5708 chipset. For example, the following network adapters use the Broadcom 5708 chipset:

• Broadcom NetXtreme II
• Hewlett-Packard NC373i Multifunction Gigabit Server Adapter

So, disable Chimney, disableTaskOffload, get the latest NIC drivers & bios

Movember donations, bring it on!

Sponsor me for Movember, it’s for a good cause:

http://au.movember.com/mospace/316906

That is it!

JDESPECRESULT_DUPLICATEKEY is metadata kernel logs

We’ve been getting UBE’s failing randomly.

In the UBE Log we have:

Failed to load job spec cache for job 103247 report R09801_ZJDE0002. Cannot load specs for UBE. Please remove the cache directories for this report under the /PD812/spec/runtimeCache directory and rerun the report. If errors persist, check the metadata kernel logs for more information (object could be missing from package, failed to access package database, etc.)

Hmm, not really that helpful.  But the metadata kernel logs are much better:

79420/6683 WRK:Metadata job 804 Thu Nov 5 00:17:40.634859 ServerDispatch.cpp1419
MD_INFO: Previous UBE Job Failed (R03B551_SMNG051S_103114, Pathcode= isTemplate=<1>)
479420/515 WRK:Metadata job 791 Thu Nov 5 00:17:45.898359 specmisc.c2448
CreateJobCache() - Failed to find version record for R03B551_+ in RDASpec source repository for job 0.
479420/515 WRK:Metadata job 791 Thu Nov 5 00:17:45.899167 ServerDispatch.cpp728
Job Cache Template creation for report_version = R03B551_NNWN050S Failed, job = 103118
479420/6426 WRK:Metadata job 803 Thu Nov 5 00:17:50.790448 specmisc.c3386
copySpecs() - jdeSpecInsert() failed with error code, JDESPECRESULT_DUPLICATEKEY. Record was selected from another repository using the k
ey, R03B551.+.

So, we see the duplicate key problem.  The duplicate is converting F98761 to TAM in the runtimeCache dir. 

I had a sniff around with TAM Browse, thinking that there might be a corruption in the base object, but nothing much could be found.  It all looked good.  There were all of the correct constraints on the tables too, so no duplicates could have hit the database..

Then oracle support came up with the goods.

You CANNOT run the same REPORT|VERSION combo at the same time!!!!  WTF?  what happened to concurrent applications, concurrent UBEs?  Their magical solution is to ensure that jobs are not run at the same time (for the first time) HUH????  This is madness. So you deploy a full package, take JDE down…  Wait 10 mins…  Scheduler is ready to fire off a bunch of jobs… There might be some waiting jobs already…  They all want to run at the same time down multi-threaded queues and corrupt each other.  so the jobs you run more often are more prone to the corruption!  nice work!

SAR is 8933751

NEWS FLASH

It gets worse.  You cannot run parallel reports until a runtimeCache has been established for the report.  So just say you want to launch 4 versions of a report at the same time from the scheduler (after a full package deploy), you cannot.  They will fight each other to write the runtimeCache specs for the base report /PD812/spec/runtimeCache/R0010P/rdaspec.ddb etc and will write corrupt specs or fail or both! 

Remember that a full package deploy will / should delete all runtimeCache specs.

So all UBEs need to be single threaded until at least one has been run before.  Nice business rules!

JDE julian date to date in Excel

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+MID(A1,3,1),1,RIGHT(A1,3))

whack 109023 into A1 and watch that bad boy go!

21CFR11 and v$session

Okay, so I guess this is probably another big lesson in “reading the documentation” – it bores me even typing that line!

We’ve got a problem enabling 21CRF11 in prod, it seems that it’s permissions to v$session for the data owner – which is the guy that runs the trigger.  So proddta user needs permissions to V$SESSION.  Cannot find much doco anywhere on this, but hey – this is what we needed last time.  Note that the JDE user also needs CREATE PROCEDURE and CREATE ANY TRIGGER.  Good article on how to grant access to v$session http://oraclespin.wordpress.com/2008/12/18/how-to-grant-access-to-vsession-to-other-users/

I’m pretty sure that this is going to sort things out, but because I don’t have SYS permissions, I cannot do this grant, even as JDEDBA.

One of the final triggers has the following errors:

ORA-00942 table or view does not exist line 15, same again for 31.

Of course these line numbers don’t mean anything either…

current time as JDETIME (UPMT) for oracle

jdetime is HHMMSS

so in oracle

select to_number(to_char(sysdate, 'HH24MISS')) from dual ;

Handy!!

DML / SQL when enabling auditing

I’ve enabled auditing many times before.  Everytime has been a nightmare. You need lots of space for big tables, this could be done MUCH better with a rename statement!  Anyway, I need to stop complaining…  So, back to my story, auditing enabled fine in DV, fine in PY – go for PROD – ERROR…  Or more accurately – warning…

For the last statement below, I get:

892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.351001    dbperfrq.c462
    OCI0000179 - Error - ORA-24344: success with compilation error
892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.366001    Jdb_drvm.c1116
    JDB9900401 - Failed to execute db request

Of course – NO MORE INFO.  SO I have to run all of this manually and try and determine why it’s not working. show err at the command line after the final trigger create statement will show the problems in SQLPLUS.

 

CREATE TABLE PRODDTA.F0011_ADT (ICICUT CHAR(2), ICICU NUMBER, ICIST CHAR(1), ICIAPP CHAR(1), ICAICU NUMBER, ICUSER CHAR(10), ICDICJ NUMBER(6), ICNDO NUMBER, ICBAL CHAR(1), ICBALJ CHAR(1), ICAME NUMBER, ICDOCN NUMBER, ICAUSR CHAR(10), ICPOB CHAR(1), ICIBOI CHAR(1), ICAIPT NUMBER, ICOFFP CHAR(1), ICPID CHAR(10), ICJOBN CHAR(10), ICUPMJ NUMBER(6), ICUPMT NUMBER, ICDRSP CHAR(1), IC52PP CHAR(1), ICCBP CHAR(1)) tablespace proddtat                                           storage ( pctincrease 0 ) ;

GRANT ALL ON PRODDTA.F0011_ADT TO PUBLIC ;

CREATE  UNIQUE INDEX PRODDTA.F0011_ADT_0 ON PRODDTA.F0011_ADT (ICICUT ASC, ICICU ASC) tablespace proddtai                                           storage ( pctincrease 0 ) ;

ALTER TABLE PRODDTA.F0011_ADT ADD CONSTRAINT F0011_ADT_PK PRIMARY KEY (ICICUT, ICICU) ;

INSERT INTO PRODDTA.F0011_ADT (ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP) SELECT ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP FROM PRODDTA.F0011 ;

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS  WHERE  ( OBJECT_NAME = 'F0011' AND OBJECT_TYPE = 'VIEW' AND OWNER = 'PRODDTA' ) ;

DROP TABLE PRODDTA.F0011 ;

ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRGUID CHAR(36) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRMKEY CHAR(15) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRPID CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRUSER CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICISDELETE CHAR(1) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRSEQN NUMBER DEFAULT 0 ) ;

CREATE VIEW PRODDTA.F0011 AS SELECT ICICUT , ICICU , ICIST , ICIAPP , ICAICU , ICUSER , ICDICJ , ICNDO , ICBAL , ICBALJ , ICAME , ICDOCN , ICAUSR , ICPOB , ICIBOI , ICAIPT , ICOFFP , ICPID , ICJOBN , ICUPMJ , ICUPMT , ICDRSP , IC52PP , ICCBP  FROM PRODDTA.F0011_ADT ;

GRANT ALL ON PRODDTA.F0011 TO PUBLIC ;

DROP PACKAGE PRODDTA.F0011_CFRA_PKG ;

create or replace package PRODDTA.F0011_ADT_CFRA_PKG
as
  type ridArray is table of rowid index by binary_integer;
  ids ridArray;
  empty ridArray;
end ;

DROP TRIGGER PRODDTA.F0011_CFRA_RIA

-- ORACLE INSERT ROW BEFORE

create or replace trigger PRODDTA.F0011_CFRA_RIA
before insert on PRODDTA.F0011_ADT
for each row
declare
  guid char(36);
  user char(10);
  workstation char(15);
  programid varchar2(48);
  audittype char(1) := '1';
begin
  if (:new.ICCFRSEQN = 1) then
    -- JDB insert
    guid := :new.ICCFRGUID;
    if (guid = 'OW Without GUID') then
      audittype := '2';
      user := substr(ora_login_user, 1, 10);
      workstation := substr(sys_context('userenv', 'host'), 1, 15);
      select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
      :new.ICCFRUSER := user;
      :new.ICCFRMKEY := workstation;
      :new.ICCFRPID  := substr(programid, 1, 10);
    else
      audittype := '1';
      user := :new.ICCFRUSER;
      workstation := :new.ICCFRMKEY;
      programid := :new.ICCFRPID;
    end if;
  else
    -- 3rd party insert
    audittype := '3';
    guid := 'Third Party';
    user := substr(ora_login_user, 1, 10);
    workstation := substr(sys_context('userenv', 'host'), 1, 15);
    select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
    :new.ICCFRGUID := guid;
    :new.ICCFRUSER := user;
    :new.ICCFRMKEY := workstation;
    :new.ICCFRPID  := substr(programid, 1, 10);
  end if;

  -- write audit record for insert
  insert into PRODDTA.A0011 (ICCFRGUID, ICICUT ,ICICU , ICB4ORAFTR, ICCFRACTION,
    ICIST, ICUSER, ICDICJ,
    ICAUDITTYPE, ICCFRUSER, ICCFRMKEY, ICDATETIME, ICCFRPID) values
    (guid, :new.ICICUT ,:new.ICICU , 'A', '1',
    :new.ICIST, :new.ICUSER, :new.ICDICJ,
    audittype, user, workstation,
    to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end;

metadata kernel restarts

We just had a problem where the metadata kernel stopped performing requests for UBEs.  The UBE would be ‘P’ing (hehe), yet nothing was occurring.  We could not see any “introspective” data either.  After running a job with logging on, we saw the message:

Oct 20 13:44:29.323150    specopen.c2949     - 585730/1 MAIN_THREAD                           Spec Encapsulation UBE Job Number set.  Job Number = 97900
Oct 20 13:44:29.323195    specmisc.c1669     - 585730/1 MAIN_THREAD                           Waiting for metadata kernel to finish cache loading for job 97900.

And that is where it would stay.  I found the metatdata kernel pid and kill –9’d the sucker on the enterprise server.  Another one started immediately. (I did actually test the process on the DEV machine before blazing away at prod – unlike me I know).

I also made a change to the JDE.INI on the ent server to increase the number of metaData kernels to 2.  This was from a metalink article.  This will not take affect until the next restart.

This actually occurred because the JDE account for out PP812 environment (shares prod end server) locked out.  The metadata kernel seemed to get into a spin.  The only jobs that were running where those that already had a “runtimeCache” directory.

Note that you can do the same to queue kernels, go into JDE and P986130 and choose “refresh queue” for any queue on the ent server that you smashed the queue kernel.  This will restart it immediately and the business will not be the wiser!  (note that this is E812 queues, where they are a single kernel).

What I know about the transaction server

So I thought I’d write down some things about the transaction server that you might or might not know.

The JDE transaction server (for WAS) uses internal MQ queues for guaranteed message delivery.  All of my experience at this stage has been publishing messages out of JDE with the use of RTEs.

The RTE messages are published out of JDE with the use of jdeIEO_EventAdd() and other functions.  There are some tests in the code to see if the event is active, and if it is – start firing off the messages.

The RTE code sends a trigger message to the transaction server.  The transaction server then does the SELECTs and INSERTs on the F90710 based upon the trigger messages that it receives.  The Enterprise Server OCM’s (server maps) for service configuration define the port and host that the RTEs are sent to.  This is the incoming jdenet port in the SM configuration, not the port that the txn server is installed on.

The outbound messages are defined per environment per machine, so there is some good flexibility there.  Out of the box, the message queues are only 50000 deep – so you might want to increase them if your volumes are high.

Once the transaction server gets the trigger message, it puts the message into an event queue.  I believe that the once there, the code is executed to retrieve the F90710 record.  once this record is received and processed and deleted, the message is moved to a WQ_SubQueue.  You can see all of this in action if you download, install and configure the SIB explorer.

Transaction server installs are easy BUT WATCH YOUR PORTS.  Make sure that the incoming jdenet port is unique.  Don’t use ports like 9080 –> 9090, these are all used by internal WAS processing.  Ensure that you run netstat on the transaction server machine to ensure that the port is free.

WM will then connect to the install port of the txn server to get messages off the txn server.

Enhancement for JDE tools idea

For Tables within OMW, have a button for generate DML for tables or indexes.  This would launch notepad or copy the DML into the clipboard so that the indexes could be generated with parallelism.  Now that would be a cool thing!

I hate it when I have to turn logging on, generate a table in an alternate data source, just so I can extract the CREATE INDEX / CREATE TABLE statements from the logs…

This is also important, because there is some sort of assumption that I can just press “generate indexes” in OMW on a table with 40 million rows.

Another STUPID thing is that the index name assigned to the index in the database is nothing to do with what you see in TDA. Just plain stupid.  There should be an identifier in TDA which tells us what name is going to be assigned to the index in the DB for manual reconciliation.

8.98.2.0 Update 2 announcement

• WebLogic is in – WOW
• Choosa mandatory fields before you can hit find - WOW

For information on the JD Edward EnterpriseOne Tools releases including the tools release process, please refer to Document ID 653448.1 on My Oracle Support.

JD Edwards EnterpriseOne Tools 8.98.2.0 contains new product enhancements and additional platform certifications.

The new product enhancements in 8.98.2.0 are:

· Resource Management in Server Manager
A set of enhancements to the Server Manager tool which provides greater insight and diagnostics into the memory and CPU usage of E1 call object and UBE kernel processes.  This enables system administrators to quickly identify processes with high resource consumption and reclaim those system resources to ensure system stability.  It also decreases the time required for developers to identify the root cause of resource consumption issues.

· Application Query Security
A modification to the Security Workbench application and runtime platform that enables administrators to configure required field input when users are performing queries in interactive applications and data browser. The administrator can also configure this security by application and also choose to issue an error or warning when the user has not entered in the required fields.

· Automatic Generation of Tables and Indices in ESUs
A modification to the process for applying Electronic Software Updates (ESUs) to automatically generate new tables and indices that  previously required manual special instructions.

The additional platform certifications consist of:

· Support for Oracle WebLogic Server 10.3.1 (Linux 5 64-bit)
Note:  This is the initial certification of the Oracle WebLogic server with JD Edwards EnterpriseOne

· Support for Oracle Database 11g R2 (Linux 5 64-bit only)

· Support for Oracle Web Services Manager 10.1.3.3

· Support for Oracle Service Bus 10.3

· Support for IBM WebSphere Portal 6.1

· Support for Microsoft Visual Studio 2008 for development and package build processes

· Support for Microsoft Windows 7 for JDE E1 web client (Internet Explorer 8 only)

The 8.98.2.0 release also includes general maintenance, including fixes included in maintenance packs 8.98.1.1 through 8.98.1.5. 

Refer to the document ID 747323.1 on My Oracle Support for specific details about supported platforms.

Server manager keeps crashing… why…

I looked and looked through metalink3, could not find a thing.

Searched for server manager crash, server manager stable, SM console – everything I could think of and came up with nothing.

Talked to colleague A (let’s call them Shae") who mentioned something from colleague B (let’s call them Brendan) that there is a blog entry from associate C (Let’s call them Clayton).

http://servermanager.blogspot.com/2008/01/sm-automatically-shutsdown-on-signout.html

Looks like this is the likely candidate.  Now, can I reverse engineer a search string to get this out of metalink3 ??

Aaarrrrgggghhhh, first time.  Search for “installManagementConsoleService.bat” and get 3 relevant articles.  That REALLY sucks.  I did a lot of searches and came up with nothing.  If I could find my search history, you’d have a laugh.

Anyways, this looks like the link https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=659575.1

Not wanting to breach any copyright, the following is a suggested solution:

There are two methods to correct this issue.  The first method is recommended since it does not involve editing the registry manually and it also ensures that the Server Manager install script gets modified so if it is rerun in the future, it will add the service correctly.  Method 2, however, is likely the quickest work around.

Method 1:
1) Make the following change in the installManagementConsoleService.bat which is located in your JDE_HOME\bin directory of the Server Manager machine:

Change:
"--StartParams=-Xmx512m;-Djde.home=%JDE_HOME%;-jar;oc4j.jar"

to:
"--StartParams=-Xmx512m;-Xrs;-Djde.home=%JDE_HOME%;-jar;oc4j.jar"

(note the addition of -Xrs)

This change REQUIRES that -Xrs come just after -Xmx512m.

2) Ensure that the Server Manager service is currently stopped.

3) Open a command prompt, and go to your JDE_HOME\bin directory
Run:
uninstallManagementConsoleService.bat

4) After the service uninstalls successfully, run:
installManagementConsoleService.bat PASSWORD

where PASSWORD is your original jde_admin password.

5) Start the service.  It should now remain running after you log out.

Method 2:
1) Open the registry editor

2) Locate the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Apache Software Foundation\Procrun 2.0\SCFMngmtConsole1\Parameters\Start 

where SCFMngmtConsole1 is the last part of the display name of the service

Set the "Params" to:
 
-Xmx512m
-Xrs
-Djde.home=C:\jde_home
-jar
oc4j.jar
 
(note the addition of -Xrs)

This change REQUIRES that -Xrs come just after -Xmx512m.

3) Start the service.  It should now remain running after you log out.

oracle to jde date conversion

CREATE OR REPLACE function PRODDTA.jde_date_to_char

(           jde_date            number,

            format_str          varchar2 := 'DD/MM/YYYY'

)

return varchar2

is

begin

/* Function to convert dates in JDE to format specified */

    return

            to_char( jde_date_to_date(jde_date), format_str    );

end jde_date_to_char;

 

CREATE OR REPLACE function PRODDTA.jde_date_to_date

(     jde_date    number

)

return date

is

begin

/* Function to convert dates in JDE to oracle dates

   JDE stores it dates in a 6 digit number field

   1st 3 characters is the number of years since 1900

   next 3 characters is the day # in the year */

if nvl(jde_date,0) = 0

then

return null;

else

return

to_date(

to_char(

to_number(

substr(to_char(jde_date,'000000'),2,3)

)+1900

)||

substr(to_char(jde_date,'000000'),5,3),

'YYYYDDD'

);

end if;

end jde_date_to_date;

CNC101 - Check specs against physical tables

Run R9698711 to determine which tables are out of synch with what is in the database.  Ripper of a report.  It does report a bunch of crap, because many of the Brazilian tax code tables are not required for Aus, but hey – it’s a start.

Change location of temp PDF files

SERVER JDE.INI

[JDENET] section, please add:

netTemporaryDir=C:\TEMP   (standard windows directory structure)
or
netTemporaryDir=/u03/alternative/path/to/tmp   (Unix based directory structure)

see this

Reg file to stop browser freeze (not brain freeze)

Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"MaxConnectionsPerServer"=dword:0000000a
"MaxConnectionsPer1_0Server"=dword:0000000a

Paste the above into a file with a .reg extension and then double click it, it’ll put the correct setting in the users browser.

I’ve made a link to a reg file on my machine above, not too sure what is going to happen on the big bad internet…  Hmm, that answers that…  did not work.

working [LOCALWEB] section of WAS 6.1 JDE.INI

[LOCALWEB]

# Installation flag, if it is 0, no HTML testing setup, disable all HTML testing

AppServerInstalled=1

# Datasource containing serialized specs for web runtime

# Used for synchonization of specs through JDB

Spec Datasource=OneWorld Local - DV812

# Name of local web server, localhost is default but may not be valid always.

webhostname=localhost

# web server port , Websphere Express default is 7080

webport=9080

# executable for starting web server

webserverstart=C:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin\startServer.bat

# executable for stopping web server

webserverstop=C:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin\stopServer.bat

# arguments for starting web server

webserverstartarg=server1

# arguments for stopping web server

webserverstoparg=server1

# start web server on demand, or immediately

# valid values : ONDEMAND (web server will be started on the first HTTP request) ,

                 MANUAL (web server has to be started manually by user on port specified),

                 IMME (web server starts as soon as ActivConsole starts)

StartAppServer=IMME

# delay time between starting web server and launching browser window

# default value is 60 (60 secs)

WebDelay=60

What version of WAS are you running

You’ll find a program called “genVersionReport.bat”, this will generate an HTML file with all of the version information that you could ever dream of “IBM WebSphere Application Server Product Installation Status Report”.  You can use this on the express version and the full version.  By default, the report is generated in the directory that you ran the program in.  The file is called versionReport.html

Dodging up a package

No time to build a package, well try this on for size…

All you need to to is copy all of the package name files from central objects, copy the serialized objects and generate a manifest – job done!

So in my little example below, I want to get all CRP code (PY812) that is in package PY812CF into PD812CF.  Now, I’m not that much of a cowboy, I’m actually in a sandpit environment which is a exact copy of PROD – honest…  No breach of change control here…  Noooo sirrrreeee.

Remember the goal…  The goal is to leave all of the metadata tables thinking that the same old package is deployed – you are just changing all of the code beneath…  The perfect crime!

The @ syntax at the end of the table name is a reference to a database link.

I truncate all of the existing PD812CF package central objects.  Don’t truncate the F98770 table, it’s the package manifest.

select 'INSERT INTO PD812.' || table_name ||' SELECT * FROM PY812.' || substr(table_name,1,6) || 'PY812CF@jde_d2jde ;'
from all_tables
where owner = 'PD812'
and table_name like '%CF' and table_name not like '%F98770%'

Leave the F98770, this is the manifest.

I then copy the F989999 and F989998 over, but you could let them JITI.

Log into the generation machine with WebDevelopment=TRUE (see one of my older posts) and generate the manifest.

Job done.  You might want to copy the enterprise server binaries over too, just to be sure.

If you are lucky enough to have oracle enterprise edition

There are a couple of simple words that you can type at the end of many SQL queries and DML statements that can imrpove performance dramatically…  parrallel #number.

My example is:

CREATE INDEX &&DATAOWNER..F03B21_CO ON &&DATAOWNER..F03B21
(ZSCO)
LOGGING
TABLESPACE &&DATAOWNER.I
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL 3 COMPUTE STATISTICS;

This will create 3 additional worker threads that will beaver away at generating the index.

If you have large tables in JDE, you do NOT want to generate indexes through the OMW option “generate indexes”.  This will take too long.  You want to generate the table against a datasource that you do not use (Data Dict for example) with logging on.

From your fat boy command line, run something like:

findstr “CREATE “ jde.log > CREATESTATEMENTS.sql

Then massage all of the CREATE INDEX statements to include the parallel option.

Remember that generating indexes from JDE for large tables is bad… It will generate all of them as use all of the defaults for size, therefore you’ll end up with a pile of badly fragmented indexes that took WAY TOO LONG to generate.

Note that you need to have enterprise edition to use this (well, actually I think that you might be able to specify the parameter, but it’ll be recorded in the DB as a infringement).

Operations That Can Be Parallelized

Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:

• SQL queries requiring at least one full table scan or queries involving an index range scan spanning multiple partitions.

• Operations such as creating or rebuilding an index or rebuilding one or more partitions of an index.

• Partition operations such as moving or splitting partitions

• CREATE TABLE AS SELECT operations, if the SELECT involves
  a full table or partition scan.

• INSERT INTO . . . SELECT operations, if the SELECT involves a full table or partition scan.

• Update and delete operations on partitioned tables

 

Some of this was taken from http://www.akadia.com/services/ora_parallel_processing.html

Recompress specs

sign into dep server DEP812 environment goto GH9083

package build history.

Find your package (oh err!)

Open latest date, client, compression options and choose spec (note that is all I want to compress)

Choose “Reset status”

Change to 01|01 and reset

Highlight word client and then resubmit build

Choose cancel on the “Generate NER” popup.

And OK

**Note that I had this problem a couple of times.  I could not delete the spec.cab file, so did a NET SESSION command on the deployment server and then deleted any of the SESSIONS that came up in the list.

I then submitted the recompress again:

I’m my situation, the following log (CompressErrors.txt) in the package main directory

31/08/2009 01:03:58 1 FCIAddFile() failed: code 4 [Could not create a temporary file]

31/08/2009 01:03:58 1 System error code (errno): 13, Permission denied

31/08/2009 01:03:58 1 The file that cannot be added is: <\\XX\E812\DV812\package\DV812GF\spec\SPEC_DV812GF.mdf>

Locking in Oracle and JDE

So you see that there is locking and you want to know what is going on in the database…

The following SQL is going to help with identifying what is being locked and blocked.

select session_id "sid",SERIAL#  "Serial",
substr(object_name,1,20) "Object",
  substr(os_user_name,1,10) "Terminal",
  substr(oracle_username,1,10) "Locker",
  nvl(lockwait,'active') "Wait",
  decode(locked_mode,
    2, 'row share',
    3, 'row exclusive',
    4, 'share',
    5, 'share row exclusive',
    6, 'exclusive',  'unknown') "Lockmode",
  OBJECT_TYPE "Type"
FROM
  SYS.V_$LOCKED_OBJECT A,
  SYS.ALL_OBJECTS B,
  SYS.V_$SESSION c
WHERE
  A.OBJECT_ID = B.OBJECT_ID AND
  C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Details of the process, get pid’s from above SQL:

select process as "PID:THREAD", server, ltrim(rtrim(program)), ltrim(rtrim(machine)), lockwait

from v$session where sid = 1504 or sid = 1176

This will give you the PID and thread ID of the E1 job that is holding the locks.  You can the use the following to kill the offending pids (or jde kernels).

Database level:

alter system kill session 'session-id,session-serial'

This command kills a session. The session-id and session-serialparameters are found in the v$session view (columns sid and serial#).

Operating System Level:

a) UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX .

ps –ef |grep SPID [to see the proc]

kill –9 SPID [to kill the proc]

b) The Windows command to kill this session would be as follows. This uses SID and thread as the parameters:

C:\oracle9i\bin>orakill ORCL92 768

I’m guessing that you might want to be careful with connection pooling if this is the case.

Strange oracle SQL results, using NULL and <>

here are a couple of SQL examples, test your skills.

back ground:

Select count(1) from CRPDTA.F03B14;

>20467464 rows

select count(1) from CRPDTA.F03B14 WHERE rzpost <> ‘D’

>0 rows

select count(1) from CRPDTA.F03B14 WHERE RZPOST = ‘D’

>2767142 rows

So, do you see the problem?  2.7 million rows are ‘D’, 0 are not ‘D’, yet there are 18 millions rows not accounted for!

These rows ARE NULL!

So, what would I get with:

select count(1) from CRPDTA.F03B14 where RZPOST = NULL ?

>0 rows

WTF???  0, I thought I’d get 18000000.  Well, here is another idiosyncrasy of oracle SQL:

select count(1) from CRPDTA.F03B14 where RZPOST IS NULL;

>17709320 rows

server manager and WAS cluster… not happy with debugging

You think that server manager is the answer to all of your problems…  You use WAS ND to serve up the web…  You go live…  Everything is good…

Until…

Lets say…

You want to turn on logging for a user…  And you can get the the logging section for one of the cluster nodes…  Actually you can’t get to any of them…  that is not cool…

Or maybe you want to use SM to report of failed logins or other metrics for the web servers, sorry – you can’t do that either.

Hmmm, so not the perfect solution you thought.

The better way is to contact www.myriad-it.com for their virtual load balancer and some advice on what to do with your architecture… I’d avoid ND and use apache load balance directives or the myLB.

What update package was that object in

I get asked this all of the time…

SELECT BDPKGNAME FROM SY812.F9622 WHERE BDPKGOPTION = ‘R0010”’ ;

It’s that simple.  BDVERS contains the version information too.  BDUPMJ is also a good field.

find big PDF files

find $EVRHOME/PrintQueue –name “*_PDF” –size +1000k –exec ls –l \; |wc

above will give you a list of PDF files that are > 1 Mb.

If you wanted to delete or mv them, then change ls –l {} \; to rm {} \;

Note also that this is a handy way of getting around the old “The parameter list is too long” – WTF!!!  120K entries causes this…  Come on unix, I think that windoz has got you on that one.

No seriously, use of the find command gets around that little nugget easily

transaction server, pile of crap

So I’ve been working with the 8.98.1.1 txn server and server manager of the same release.  I gotta tell you that this thing is a pile of poo.  We are hitting it hard (250000 messages at a time), but it breaks with different error messages all of the time.

I’ve been getting terrible responses from oracle too, untimely and inaccurate.

But, one thing they did provide was an IBM product called the service integration bus explorer – it’s awesome!

http://www.alphaworks.ibm.com/tech/sibexplorer

You can see all your queues with accurate numbers.  Take it from me – the queue count in SM is an approximation at best…

The purge functionality works and you don’t have to wait hours to see the queued message count.  It’s been a very positive step seeing this in action. 

rownum and between – oracle result sets

Limiting your resultset with rownum is a powerful feature in oracle sqlplus.  Because the rownum is applied to the result set, a between statement is not possible…  So here is a sneaky and cheeky way of doing a between.

insert into testdta.F55001CM
(select aian8, 109170, 141300, 'SQL', 'A', 'MOIRS'
  from ( select a.*, rownum rnum
           from (
  select aian8, 109170, 141300, 'SQL', 'A', 'MOIRS'
  from testdta.f03012,testdta.F0101
  where AIAN8 = ABAN8
  and substr(ABALKY, 1, 2) in ('AY','LM','NY','NA','EA')) a
          where rownum <= 236000)
where rnum >= 235000 )

o-dear… ODIR…

I hate outlook and nokia software and synch and EVERYTHING related to these topics.  I’m sick of duplicate entries everywhere, it’s a nightmate…

But I discovered a decent freeware tool for de-duplication.  http://techie-buzz.com/utilites/free-outlook-email-contacts-and-calendar-duplicate-remover.html worked first time, no license keys, no secret downloads, no limitations…  Woo Hoo!

Got rid of all the dups, placed them in a “duplicates” folder.  I now see people names and not random numbers (my use of random is not factual, because of course the numbers are not actually random…) when people call me.

Package deploy on different pathcode

ever been up late at night… Waiting for that damn UBE to finish so you could deploy a package… Maybe I’ve got good news for you… Maybe after reading this, you’ll get more sleep at night, just maybe…

Did you know that if the UBE is running in a pathcode that you are not deploying the package for, it will not prevent the package build from completing properly? Did you? Seriously? Did you know that for 100% certainty… Nah, I thought so… Well now you do.

Enjoy you deployments…

***News flash, this is not the case for AS/400's. It seems that they need full access.***

Installing 8.98 on existing unix server

I’m installing 8.98.1.1 via SM to a unix server that has 8.96 on it.

It’s happened twice now, so I know that it’s not just me… 

the JDE.INI file will be written with a dodgy [JDEIPC]

startIPCKeyValue=

setting.  It’s been set to blank twice for me (AIX).  You need to change this to 4000 or something.  Note that you’ll also need to run ipcs (at unix command line as jde user that runs services) to see if jde812 still owns and share resources.  You might then have to delete any dodgy ones with ipcrm –m XXXXXX

I find that if you try to start JDE without first locating this section, it will create shared memory that will prevent services starting.  You need to get rid of this with ipcrm

You also remember to run the following is you are installing one of the newest tools releases.

$SYSTEM/bin32/E812ToolsEnvSetup.sh

WAS61 dmgr won’t start

Lets say that maybe I put the wrong value into the Java process definition for the dmgr profile…  and now I can’t do anything because the CellManager won’t start – awesome!

never fear…  Time to edit the server.xml file that is written to

D:\IBM\WebSphere61\AppServer\profiles\Dmgr01\config\cells\nsgshsjdnwb03Cell01\nodes\nsgshsjdnwb03CellManager01\servers\dmgr

or equivilent dir…

There will be a setting in the <processDefinitions and <jvmEntries for the amount of memory that you configured.  Delete the variable name and the = and the data “512” from the text, save the file and start the service again…  Woo hoo, back in business!

Pop quiz

Web users are getting heaps of:

“An exception has been caught by the Web client. Please contact your system administrator. See log for details. Exception id =Exception_2009-07-20 13:18:20.465_WARRENG   There was a problem with the sever while running the business function F06116EndDoc.\nThe server may still be available, but because of state information, the entire unit of work must be resubmitted.\nPlease exit the application and restart it.\nPost Button Clicked Line number 0\nP051121_W051121C com.jdedwards.runtime.base.SystemException: There was a problem with the sever while running the business function F06116EndDoc.\nThe server may still be available, but because of state information, the entire unit of work must be resubmitted.\nPlease exit the application and restart it.\nPost Button Clicked Line number 0\nP051121_W051121C”

Application server is saying nothing…

What is it going to be…  Obvious, timeouts…  Remember that a call object kernel is not going to complain that it takes too long to perform an action, it’ll keep trying…  Meanwhile, the web will have given up a long time ago.

Note that if there are not ERRORs causing the timeout, the log server logs will reveal nothing!

In the above situation, enabling logging revealed the following:

Jul 20 13:30:00.521003 - 1008/6140 WRK:JDE_08090008_P051121              SELECT  *  FROM JDE_PROD.PRODCTL.F0002  (UPDLOCK)  WHERE  ( NNSY = '06' )  ORDER BY NNSY ASC FOR UPDATE OF NNN001, NNN002, NNN003, NNN004, NNN005, NNN006, NNN007, NNN008, NNN009, NNN010
Jul 20 13:30:00.521004 - 1008/6140 WRK:JDE_08090008_P051121              Entering DBPerformRequest
Jul 20 13:30:00.521005 - 1008/6140 WRK:JDE_08090008_P051121              ODBC:S DBPerformRequest req=08FC3B40 con=07F0A5E8 env=00A614E8 dbc=00A74640 spid=718 JDEDATA A (JDE@Control Tables - Prod)
Jul 20 13:30:00.521006 - 1008/6140 WRK:JDE_08090008_P051121              Exiting DBPerformRequest
Jul 20 13:30:14.897000 - 1008/4600 SYS:Dispatch                          About to call dispatch function, flags=0x0, Type=916
Jul 20 13:30:14.897001 - 1008/4600 SYS:Dispatch                          ADD TO POOL type: 916, 172.19.1.65_5796_m283302
Jul 20 13:30:14.897002 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: Entering psthread_pool_job_createEx()
Jul 20 13:30:14.897003 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: psthread_pool_job_createEx(): Exiting
Jul 20 13:30:14.897004 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: Entering: psthread_pool_add_job()
Jul 20 13:30:14.897005 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: threads: total (5) idle (3) waiting (3) jobs: queued (0)
Jul 20 13:30:14.897006 - 1008/4600 SYS:Dispatch                          PSTHREAD_BLQ: ps_blocking_queue_enqueue(WRK:Queue): Entering
Jul 20 13:30:14.897007 - 1008/4600 SYS:Dispatch                          PSTHREAD_BLQ: ps_blocking_queue_enqueue(WRK:Queue): Exiting
Jul 20 13:30:14.897008 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: psthread_pool_add_job(): Exiting
Jul 20 13:30:14.897009 - 1008/5956 WRK:Idle Worker                       PSTHREAD_BLQ: ps_blocking_queue_dequeue(WRK:Queue): Exiting
Jul 20 13:30:14.897010 - 1008/5956 WRK:Active Worker                     START SYSTEM JOB type 916, 172.19.1.65_5796_m283302
Jul 20 13:30:14.897011 - 1008/5956 WRK:Get User List                     END SYSTEM JOB   type 916, 172.19.1.65_5796_m283302
Jul 20 13:30:14.897012 - 1008/5956 WRK:Idle Worker                       PSTHREAD_BLQ: ps_blocking_queue_dequeue(WRK:Queue): Entering
Jul 20 13:30:29.899000 - 1008/4600 SYS:Dispatch                          ADD TO POOL type: 10, 0.0.0.0_0_m0
Jul 20 13:30:29.899001 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: Entering psthread_pool_job_createEx()
Jul 20 13:30:29.899002 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: psthread_pool_job_createEx(): Exiting
Jul 20 13:30:29.899003 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: Entering: psthread_pool_add_job()
Jul 20 13:30:29.899004 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: threads: total (5) idle (3) waiting (3) jobs: queued (0)
Jul 20 13:30:29.899005 - 1008/4600 SYS:Dispatch                          PSTHREAD_BLQ: ps_blocking_queue_enqueue(WRK:Queue): Entering
Jul 20 13:30:29.899006 - 1008/4600 SYS:Dispatch                          PSTHREAD_BLQ: ps_blocking_queue_enqueue(WRK:Queue): Exiting
Jul 20 13:30:29.899007 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: psthread_pool_add_job(): Exiting
Jul 20 13:30:29.899008 - 1008/3068 WRK:Idle Worker                       PSTHREAD_BLQ: ps_blocking_queue_dequeue(WRK:Queue): Exiting
Jul 20 13:30:29.899009 - 1008/3068 WRK:Active Worker                     START SYSTEM JOB type 10, 0.0.0.0_0_m0
Jul 20 13:30:29.899010 - 1008/3068 WRK:eNetKernelIdle                    END SYSTEM JOB   type 10, 0.0.0.0_0_m0
Jul 20 13:30:29.899011 - 1008/3068 WRK:Idle Worker                       PSTHREAD_BLQ: ps_blocking_queue_dequeue(WRK:Queue): Entering
Jul 20 13:30:35.977000 - 1008/4600 SYS:Dispatch                          About to call dispatch function, flags=0x0, Type=916
Jul 20 13:30:35.977001 - 1008/4600 SYS:Dispatch                          ADD TO POOL type: 916, 172.19.1.65_5796_m283571
Jul 20 13:30:35.977002 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: Entering psthread_pool_job_createEx()
Jul 20 13:30:35.977003 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPJ: psthread_pool_job_createEx(): Exiting
Jul 20 13:30:35.977004 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: Entering: psthread_pool_add_job()
Jul 20 13:30:35.977005 - 1008/4600 SYS:Dispatch                          PSTHREAD_TPL: threads: total (5) idle (3) waiting (3) jobs: queued (0)
Jul 20 13:30:35.977006 - 1008/4600 SYS:Dispatch                          PSTHREAD_BLQ: ps_blocking_queue_enqueue(WRK:Queue): Entering

Note that there is a bunch of timeouts for the SQL operation…  Hmm, me thinks that when there is a problem with the F002 – it’s locking.   A hunting we will go.

Run the following queries to find the locks:  Note that the database is SQLServer 2005

sp_lock ;
--Gives a very terse list of locks.

--This was the winning query below:

SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address;
GO

-- Query for specific lock types
SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode
FROM sys.dm_tran_locks
WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID');
GO

I then opened the instance activity monitor and saw the blocked process and killed it!

Job done!

Server manager viewing large files

Did you know that the server manager uses the operating system TEMP / TMP dir to write temp files to when you choose to view a large log file…  It’s been reported that C: drives are being filled by users trying to view 6GB log files…

I’m not advocating the viewing of 6GB log files, but good to know where to look if the C: of your server manager console machine is filling up!

Thanks to Nigel for that one!

Simple, disable oracle trigger

ALTER TRIGGER orders_before_insert DISABLE;

That’s it really…

Copying a pathcode? OL made easy

--  Copy pathcode 1 records to pathcode 2

accept PATHCODE_TO_REPLACE TEXT PROMPT 'Enter the pathcode you are replacing -> '
accept PATHCODE_TO_DUPLICATE TEXT PROMPT 'Enter the pathcode you are duplicating –> '

accept OLOWNER TEXT PROMPT 'Enter the pathcode you are duplicating -> '

create table f9861_temp as select * from &&OLOWNER..f9861 where sipathcd = '&PATHCODE_TO_DUPLICATE';

delete from f9861_temp where SISTCE != '1'

commit;

update f9861_temp set sipathcd = '&PATHCODE_TO_REPLACE';

commit;

delete from &&OLOWNER..f9861 where sipathcd = '&PATHCODE_TO_REPLACE';

commit;

insert into &&OLOWNER..f9861 select * from f9861_temp;

commit;

drop table f9861_temp;

Compare column count with central objects for tables

Note that this also show correct usage of PROMPT and ACCEPT in SQL scripts.

--  Note that this script will create a temp table to
--  make the execution quicker

-- drop this table
PROMPT
PROMPT You're about to determine the differences in column counts between
PROMPT central objects and you're data data source
PROMPT
ACCEPT data_owner CHAR PROMPT 'Enter the OneWorld Data owner name - in caps -> '
ACCEPT pathcode_owner CHAR PROMPT 'Enter the OneWorld pathcode owner  -> '

set pagesize 50

drop table temp_table_count;

create table temp_table_count
(table_name varchar(50) not null,
spec_column_count integer not null,
relational_column_count integer not null)
;

-- insert the relational column counts
--
insert into temp_table_count
select table_name, 0, count(1)
from all_tab_columns
where owner = '&data_owner'
group by table_name
order by table_name ;

commit;

-- get the spec table counts from the F98711
--
update temp_table_count
set spec_column_count = (
select count(1)
from &pathcode_owner..f98711
where  ltrim(rtrim(tdobnm)) = table_name);

commit;

-- Compare and display the differences
--
select table_name || ' ' || spec_column_count || ' ' || relational_column_count
from temp_table_count
where spec_column_count != relational_column_count;

Add a web server to WAS post install

http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/topic/com.ibm.websphere.nd.doc/info/ae/ae/cins_webserver.html

Note that you cannot add a webserver with the admin web interface, you need to use the command line.

Essentially this task is performed from the plugins install dir. There is a facilitating script there called configurewebserver1.bat.

The webserver might not have been installed properly because of pathing. If you install WAS and then reboot and then do HTTP server and plugins it might work.

This script calls a couple of files that might need to have a path modified. Please see my edits below:

D:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin\wsadmin.bat -f D:\IBM\WebSphere\AppServer\bin\configureWebserverDefinition.jacl webserver1 IHS "D:\\IBM\\IBMHTTPServer" "D:\\IBM\\IBMHTTPServer\\conf\\httpd.conf" 80 MAP_ALL "D:\\IBM\\WebSphere\\Plugins" unmanaged nsgshsjdnsp02u.XXXX.local nsgshsjdnsp02u.XXXX.local windows

From above, I had to path the wsadmin.bat file and the configureWebServerDefinition.jacl file.
Note that this is a 25K file, you don't wanna create one of these yourself!!

Output of the script looks like the following:

Start computing the cell Type.
StandAlone profile found.

Input parameters:

Web server name - webserver1
Web server type - IHS
Web server install location - D:\IBM\IBMHTTPServer
Web server config location - D:\IBM\IBMHTTPServer\conf\httpd.conf
Web server port - 80
Map Applications - MAP_ALL
Plugin install location - D:\IBM\WebSphere\Plugins
Web server node type - unmanaged
Web server node name - webserver1_node
Web server host name - nsgshsjdnsp02u.XXX.local
Web server operating system - windows

Creating the unmanaged node webserver1_node .
Unmanged node webserver1_node is created.

Creating the web server definition for webserver1.
Parameters for administering IHS web server should be updated using wsadmin scri
pt or admin console.
Web server definition for webserver1 is created.

Start computing the plugin properties ID.
Plugin properties ID is computed.

Start updating the plugin install location.
Plugin install location is updated.

Start updating the plugin log file location.
Plugin log file location is updated.

Start saving the configuration.

Configuration save is complete.

Computed the list of installed applications.

Processing the application DefaultApplication.
Get the current target mapping for the applictaion DefaultApplication.


Computed the current target mapping for the application DefaultApplication.
Start updating the target mappings for the application DefaultApplication.
Target mapping is updated for the application DefaultApplication.

Processing the application ivtApp.
Get the current target mapping for the applictaion ivtApp.


Computed the current target mapping for the application ivtApp.
Start updating the target mappings for the application ivtApp.
Target mapping is updated for the application ivtApp.

Processing the application query.
Get the current target mapping for the applictaion query.
Computed the current target mapping for the application query.
Start updating the target mappings for the application query.
Target mapping is updated for the application query.

Start saving the configuration.

Configuration save is complete.

D:\IBM\WebSphere\Plugins\bin>

More speed captain

Want some more system speed?  I’ve got a plan, it’s sneeky and cheeky. 

Do what many people do, let users generate all of the objects for the PD environment with web JITI.  This will populate the F989999 with all of the WBOID’s.  Extract a unique list of APPL and UBE objects and paste them into a GeneratorList.txt file (just like the ones that are in the work dir of the update package build on the deployment server.

Contents of the GeneratorList.txt

• MAGIC_NUMBER=0x00C0FFEE
• #Do Not modify the Magic Number or its position in any case!
• #This file contains the objects that will be generated  when
• #this file is chosen and menu Item BulkGen is clicked
• #Add the desired objects in the proper section
• #Invalid object names or objects in incorrect section can
• #not be generated.
• #Applications
• APP:P00950
• #Reports
• REPORT:R009505
• #Ners (NERs are case-sensitive)
• NER:IsDataSelectionSecurityPresent
• #BSFN (BSFNs are case-sensitive)
• BSFN:BuildPackage
• #DSTR
• DSTR:D00950N
• #DSTR
• DSTR:T009505

So basically, you need to use the template above and generate the contents with SQL.

This is oracle syntax…

select distinct 'APP:' || substr(wboid,5,instr(wboid,'-',4,2)-5) from pd812.f989999 where wboid like 'APP-%'

select distinct 'REPORT:' || substr(wboid,12, length(ltrim(rtrim(wboid)))-11) from pd812.f989999 where wboid like ‘REPORTINF%’

Note that in oracle you should put “set pagesize 0” you could also spool the output to a file.

Appending these two statements will create a GeneratorList.txt file that will generate all of the APPS and UBEs that have been used since the last full deployment.

This is much quicker than a full build and much more efficient!

transaction server in 8.98.1.1

The install was nice once I have a stand-alone WAS61 installation.  Not problems.  Almost worked out of the box – amazed yes.

I did try and copy my old 8.96 config files over the top of the existing ones, that WAS a mistake.  Just fix the ones that are shipped.

It probably worked this way in the old release, but the e1transvr makes extensive use of the internal WebSphere queueing mechanisms for reliable delivery.

The messages are stored in the F98710 table until a transaction server is available to receive all of the messages.  Once the transaction server is started, it lists the subscribers that are subscribing to it for messages. (This is really nice and can be seen in server manager).  This is also really nice because it shows you how many messages are queues to each of the subscribers.

You need to count the rows in the F90710 to see how many messages are yet to make it to the transaction server.

This is the view from SM of some subscribers.

If you had a situation like me with 16500 queued messages, you might need to clear the cache – but OF COURSE this does not work in the current 8.98.1.1 tools release.  So, a sneaky way I found was the following:

Find the dir that has the filestore for the logs and pending transactions and delete the files.

Viola, you no longer have 16500 messages queued to be delivered.

Override the need for FTP

in the [OWWEB] section of the JAS.INI there is a setting:

UseOMEinNTShare=true

I think that 8.98.1.1 the default might be false.

8.97 & above need FTP for media objects

To tell you the truth, I did not know that…

 

Tools Releases 8.97 and higher - Server Manager

With tools release 8.97 and higher it is no longer necessary to use the MO QUEUE folder path in the jas.ini, which was required in previous releases. The configuration now comes from the web.xml file and is automatically configured. As for the port and share settings, they are now incorporated in Server Manager. To access these settings:

1. After logging into Server Manager, locate the EnterpriseOne HTML Server link under Managed Instances and select it.
2. On the left navigation bar, locate Web Runtime under the Configuration header and select it.
3. Under Web Runtime, you will see the same Media Object entries which were found in the jas.ini in previous releases.

• Media Object FTP Port (This is the port and user being used for FTP. More on this below.)
• Media Object FTP User ((Replace 'anonymous' with the appropriate username and password if you set up any security on your FTP server.)
• Media Object FTP Password
• Use Windows File Sharing (This setting defines whether the system will use FTP or Windows File Sharing for transferring media objects to the path in the P98MOQUE. To use FTP, do not check the box..)

Services

1. Make sure the admin user assigned to start the web services has privileges to the
   - moqueue folder on the JAS server
   - MEDIAOBJ\... folders on the deployment server
   MO Server needs to have permission for the WSAD ADMIN. There is a login User to the physical machine and there is WSAD Admin that starts the services. It is the WSAD Admin that requires rights on the Media Object Server. If the admin user who starts the web services does not have the permissions to these directories, then when the end users log on to web clients, they will not be able to add media objects even though they are able to do this on fat clients.
2. Check the HTTP, Websphere or Oracle Application Server (OAS), and Server Manager services on the jas server. Ensure these services are all being started with a network account user. If using a domain for your network, then this can be a network id which has been given the proper permissions. However, if using Workgroups, the user being used to start the services should be in the "Local Admin" group account on each server; not in the "Local System" account. Then make sure the other servers are being started with this account as well.

Note: After making any permission changes, be sure to stop/start services for server manager and then restart the jas instance using server manager.

Configuring FTP

By default, media objects are stored on the deployment server running a Windows operating system. Unless the Unix, Linux, or AS400 server hosting the HTML Web Server is able to map a network drive to the deployment server with read/write authority, FTP protocol should be used to store and retrieve media objects.

To prepare an FTP Server for Media Objects:
Follow the instructions below to add the FTP Server component to the deployment server. These instructions are based on the Windows operating system but may change as Microsoft has new releases. However, the steps will be similar.

1. Open Add/Remove Programs from Control Panel
2. On the left hand side, click Add/Remove Windows Components
3. Select Internet Information Services (IIS)
4. Click Details button
5. Select File Transfer Protocol (FTP) Server
6. Click OK
7. Click Next and finish the component installation.

Follow the instruction below to set up a Media Object directory on the FTP server

1. Open Internet Services Manager from Administrative Tools in Control Panel
2. Start the Default FTP Site
3. Right click at Default FTP Site
4. Select New -> Virtual Directory
5. On Virtual Directory Alias screen, enter B7334 for ERP 8, B7333 for Xe, B9 for EnterpriseOne 8.9, E810 for 8.10, E811 for 8.11, E812 for 8.12, and E900 for 9.0.
6. On FTP Site Content Directory screen, browse to appropriate B7334, B7333, B9, E810, E811, E812, or E900 directory
7. On Access Permissions screen, select both Read and Write
8. Click Finish to finish the configuration

Note: When setting up the FTP, make sure the path goes to the installed E1 directory such as c:\900 and not directly to the media object queue folder.

To configure the E1 HTML Web Server to use FTP for Media Object storage and retrieval, go back to the jas.ini or server manager and configure the [OWWEB] or Web Runtime section with the correct Port and login information.
To test your FTP site:
On your HTML Web Server, use an FTP client and manually connect to your deployment server using the information specified in the jas.ini. Change the current directory to B7333, B7334, B9, E810, E811, E812, or E900. Test putting and getting a file to and from the server.

Note: As mentioned previously, use the UPN naming convention for the Queue Path in P98MOQUE. For instance, if your deployment server for E900 is DEPSRV, your queue path should begin with \\DEPSRV\E900. To use this queue path, the HTML Web Server connects to DEPSRV using FTP on the port number specified in the jas.ini, and logs on using username/password also specified in the jas.ini. Then the HTML Web Server tries to change the current directory to E900. The E900 virtual directory must be available under the Default FTP Site.