simple JD Edwards database security model implementation in oracle

I tend to want the ability to give people a read only account in JD Edwards.  I do this by creating a bunch of roles and then granting database permissions based upon these roles.  I keep my users that connect to the database pretty simple, as JDBC connection pooling works better with simple database connection users.

I never use the JDE account as a database connect account or a “service” account for my kernels, I create a jdeservice user for that, I also create a jdeuser database user that will connect all of my user sessions.  It’ll be assigned the JDE_USER role.  I’ll have a different oracle user account for jdero and also jdedev.  JDEDEV will have special permissions in DEV and will be able to do a bit more in the lower environments – and a bit LESS in the higher environments!

It’s really important to lock down as much as possible, NEVER grant all to public – a very sill thing to do.

create some roles,

JDE_USER
JDE_RO
JDE_DEV
JDE_ADMIN

see them in “select * from DBA_ROLES”

Do some role assignments:

grant JDE_USER to JDE;

Do some grants at a role level:

select 'GRANT SELECT, INSERT, UPDATE, DELETE  on ' || owner || '.' || table_name || ' to JDE_USER;' from all_tables where owner in ('CRPDTA','CRPCTL');

--run the results of the above

Do the same for RO:

select 'GRANT SELECT on ' || owner || '.' || table_name || ' to JDE_RO;' from all_tables where owner in ('CRPDTA','CRPCTL');

Check the roles that a user has been granted, checking the jde user below:

select * from dba_role_privs where grantee = 'JDE';

Check specific user granted privileges:

select privilege
from dba_sys_privs
where grantee='JDE'
order by 1;

Check what permissions a role has:

select * from ROLE_TAB_PRIVS where role = 'JDE_USER';

JDE_USER    CRPDTA    FF34R011        DELETE    NO
JDE_USER    CRPDTA    FF31K30        DELETE    NO
JDE_USER    CRPDTA    FF31K20        DELETE    NO
JDE_USER    CRPDTA    FF31K11        DELETE    NO
JDE_USER    CRPDTA    FF31K10        DELETE    NO
JDE_USER    CRPDTA    FF31113        DELETE    NO
JDE_USER    CRPDTA    FF31011S        DELETE    NO
JDE_USER    CRPDTA    FF30L912        DELETE    NO

naming a managed server vs a managed instance–server manager naming standards

Quick guide:

Managed instance and managed server

Managed Instance	Example	Managed Server	Recommendation
The is the server manager name, used in all SM screens and server manager directories	PY910_9006_WEB02	This is the J2EE container name on your webserver.  Not really used in JDE that much	PY910_9006

Domain name

Call it something to do with the machine and purpose of the domain.  Potentially the release of weblogic also.  Remember that you can have multiple domains on a single server (which is nice).  A domain will always be part of WLS (but you can upgrade a domain, so release might not be too good?).

I like to see the servername and purpose in the domain name:

web02_jdehtml

web02_jdeAIS

web02_jdeovr

Examples and boring stuff

I always have this issue, when creating new JAS instances or enterprise server instances in server manager, what do I name them so that it makes sense.  I think we need a definitive guide!

Above is an example of the server manager screen defining the weblogic domain – in my case e1_apps

So my weblogic domain is called e1_apps.  Should that have a component of the machine name – I think yes – that makes sense. 

The domain name is listed on a number of screens and it’s good to know what machine is belongs to.

Also see when you have a cluster, some other naming options

So you have 2 managed servers, but a single name for the managed instance.

What does this look like in WLS?

You can see that weblogic also defines by domain.

if we look under server manager, we see:

drwxr-xr-x. 7 oracle oinstall 4096 Apr 30  2015 DV910AIS
drwxrwxr-x. 3 oracle oinstall 4096 Apr 30  2015 E1ENT_DNT_Ent
drwxrwxr-x. 3 oracle oinstall 4096 Jul 16  2013 E1ENT-DNT_WLS
drwxr-xr-x. 8 oracle oinstall 4096 May 29  2015 JAS_JPY910_9085
drwxrwxr-x. 8 oracle oinstall 4096 Aug 31  2015 JDE91_PY
drwxrwxr-x. 2 oracle oinstall 4096 Jul 15  2013 ocm
drwxr-xr-x. 7 oracle oinstall 4096 May 29  2015 PY910_9087_AIS
-rwxrwxr-x. 1 oracle oinstall 2235 Aug 31  2015 targets.xml
[root@E1ENT-DNT targets]# pwd
/u01/app/oracle/jde_home_1/SCFHA/targets

 

Note, that these are the

So the server manager name is “Related managed instance” – for example JAS_JPY910_9085

EnterpriseOne HTML Server: JAS_JPY910_9085

but, when you create the container in SM, that is what it’s going to be called in WLS

J2EE Server JPY910_9085

See the directory structure below:  under domains –> domain name –> servers, you have your container name.  The J2EE server

/u01/app/oracle/Middleware/user_projects/domains/dnt_domain/servers
[root@E1ENT-DNT servers]# ls -l
total 28
drwxrwxr-x. 7 oracle oinstall 4096 Jul 16  2013 AdminServer
drwxrwxr-x. 3 oracle oinstall 4096 Aug 31  2015 domain_bak
drwxr-x---. 8 oracle oinstall 4096 Feb 26  2015 DV910AIS
drwxrwxr-x. 8 oracle oinstall 4096 Jul 16  2013 JPY910
drwxr-xr-x. 8 oracle oinstall 4096 May 25  2015 JPY910_9085

And so then it get’s a bit fruity, as the final location of your JDE JAS files have both names in the path:

/u01/app/oracle/Middleware/user_projects/domains/dnt_domain/servers/JPY910_9085/stage
[root@E1ENT-DNT stage]# ls -l
total 4
drwxr-x---. 3 oracle oinstall 4096 May 29  2015 JAS_JPY910_9085

/u01/app/oracle/Middleware/user_projects/domains/dnt_domain/servers/JPY910_9085/stage/JAS_JPY910_9085/app/webclient.war/WEB-INF/classes

The final location of jas.ini etc (for runtime) is above, note the combination of the two names managed server (WLS name) and managed instance (JDE NAME)

When you look at the “servers by type”, you get the screen below:

See that this uses the instance name – which is the JDE name.  So what would be handy here?  port, machine, lifecycle – all of this would be good

Also if looking “by group”, it also uses the instance name – what we call in in JDE – not WLS world.

Therefore, when creating a new JAS server (for example),

Which creates:

This is not really known to SM yet – it’s a WLS concept

Now, to create in SM

This is the name that is going to go everywhere in SM – so machine, port and lifecycle is important

simple post about oracle clients–not people-32 bit clients

Any oracle client that is used to send data traffic from your database server to the client (and back), should be at the same release as the database.  You know that if you are on database 11.2.0.4 64 bit linux, then your clients should also be on 11.2.0.4.  There are many reasons for this, but at the end of the day it comes down to security, stability and performance.

Even your deployment server should be on the exact same client patch release.  It’s a 32 bit client too (weird I know).  It’s a 32 bit client on the enterprise servers too.

I’ve recently done a bunch of stress testing and load testing of JD Edwards and have seen a number of sites with an ORA-03114 / ORA-03113 and the associated server side errors for these disconnects.  Sure, quite often these are firewall and network related – but you should be able to make this predictable. I had a situation where the a few connections would just go down after 40 minutes, 15 minutes.  Sometimes with a spectacular fail and sometimes just a retry and things would be back to normal.

Server side looked like this:

Dumping 'Buffer dump info:' addr=0xc6c4760 size=92 bytes
Dump of memory from 0xc6c4760 to 0xc6c47bc
00C6C4760 00000104 01BE1B00 7B000000 00000005  [...........{....]
00C6C4770 0000D300 00000300 D8000000 09000038  [............8...]
00C6C4780 93440000 00000016 00000000 00001D00  [..D.............]
00C6C4790 00000001 00000000 00000000 00000000  [................]
00C6C47A0 4F190000 302D4152 33303431 6F6E203A  [...ORA-01403: no]
00C6C47B0 74616420 6F662061 0A646E75           [ data found.]   
hstflg:  0x40202d81
hstcflg: 0x00000000
hstpro:  6

And in the alert log, a TTC matching EVERY ORA-03114

Mon Apr 11 06:36:15 2016
Archived Log entry 4238 added for thread 1 sequence 4238 ID 0x176a252 dest 1:
Mon Apr 11 06:39:17 2016
Errors in file /rdsdbdata/log/diag/rdbms/vlinest_a/VLINEST/trace/VLINEST_ora_1972.trc  (incident=153033):
ORA-03137: TTC protocol internal error : [12333] [7] [2] [195] [] [] [] []
Incident details in: /rdsdbdata/log/diag/rdbms/vlinest_a/VLINEST/incident/incdir_153033/VLINEST_ora_1972_i153033.trc
Mon Apr 11 06:39:20 2016
Dumping diagnostic data in directory=[cdmp_20160411063920], requested by (instance=1, osid=1972), summary=[incident=153033].
Mon Apr 11 06:39:21 2016
Sweep [inc][153033]: completed
Sweep [inc2][153033]: completed

It seems that there was a very relevant patch listed under bug 18263924, this is a client OCI fix which is supposed to resolve these client and server errors.  Wow, I think we are getting to the point of this entry, it’s about opatch, and determining the exact release of your oracle software.

$ /u01/app/oracleclient32/product/11.2.0/client_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracleclient32/product/11.2.0/client_1
Central Inventory : /home/jde910/oraInventory
   from           : /u01/app/oracleclient32/product/11.2.0/client_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracleclient32/product/11.2.0/client_1/cfgtoollogs/opatch/opatch2016-04-18_10-22-46AM_1.log

Lsinventory Output file location : /u01/app/oracleclient32/product/11.2.0/client_1/cfgtoollogs/opatch/lsinv/lsinventory2016-04-18_10-22-46AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Client 11g                                                    11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  18263924     : applied on Mon Apr 18 10:14:41 EST 2016
Unique Patch ID:  20096960
   Created on 14 Apr 2016, 12:39:07 hrs PST8PDT
   Bugs fixed:
     18263924

 

--------------------------------------------------------------------------------

OPatch succeeded.

You can see from the above that I’ve just executed opatch lsinventory (in my case from the patch directory).  And it’s told me everything that I need to know about the software and patch level. for the oracle home that the executable is in.

You basically enter the same command (I need to qualify opatch with the path), to opatch apply the patch.   Do this from the directory that you “unzipped” the patch into.

Another command that you might want to run is opatch prereq CheckConflictAgainstOHWithDetail -ph ./ to check everything is okay before the apply.

Note that in general you will probably need to stop JD Edwards, as any locks on the files is going to prevent opatch from running properly.

openscript will not start

okay, so I downloaded openscript from here:

http://www.oracle.com/technetwork/oem/downloads/index-084446.html

It’s the proper place, windows 32 and 64 bit

install it (note had to do this as administrator)

C:\OracleATS\openScript\%USERPROFILE%\osworkspace\.metadata

containing:

!ENTRY org.eclipse.osgi 4 0 2016-04-07 10:53:51.908
!MESSAGE An error occurred while automatically activating bundle org.eclipse.core.net (332).
!STACK 0
org.osgi.framework.BundleException: Exception in org.eclipse.core.internal.net.Activator.start() of bundle org.eclipse.core.net.
    at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:1028)
    at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:984)
    at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:346)
    at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:265)
    at org.eclipse.osgi.framework.util.SecureAction.start(SecureAction.java:400)

What!  this is just a base installation, I’ve done nothing.  I’ve not chosen a JRE or a JDK – it just plain does not work.

!SESSION 2016-04-07 10:53:45.455 -----------------------------------------------
eclipse.buildId=unknown
java.version=1.7.0_71
java.vendor=Oracle Corporation
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments:  -os win32 -ws win32 -arch x86

Note thiis is the included JRE version found in C:\OracleATS\openScript\jre

I get the above every time

“An eeror has occurred. See the log file C:\OracleATS\openScript\%USERPROFILE%\osworkspace\.metadata\.log”

I uninstall, reinstall.  Have problems with the helper service, try again and again

Eventually I try a different JRE – actually match it to the one on my machine (my mahine is 64bit BTW)

So Now…

I’m using jre

java version "1.8.0_77"
Java(TM) SE Runtime Environment (build 1.8.0_77-b03)
Java HotSpot(TM) Client VM (build 25.77-b03, mixed mode)

Note that this is 32 bit

Start her up again (as admin) and finally I can log in. What a terribly painful experience.

developer tip to avoid repetition

This is a long an involved problem.

I have a join between F4801 and F1201 and F1217 and F4801T – this is the work order super find.  This enables me to look at any work orders using equipment information and vise versa.   Really nice, but I want to see a unique list of assets, take out the duplicates.

This is easy with a ER to supress grid row, but this is very slow, I want to make it fast.  The use case is if an asset as 10000 work orders, that is a lot of work that the web client needs to do (10000 suppresses)  before it shows you 1 row, you might be waiting.

So I’m going to create a JDE table, then create a oracle view with the table name and put the complex joins into my oracle view – and then I’ll have a fast and efficient solution with a simple application.  Sound good – yes it does!

Problem 1 my table has 386 columns:

There is no way I’m going to create that table, I’ll surely miss one and mess it all up.

So, I’m going to cheat.

I’ve selected out the columns in my existing business view that my existing application is based upon, just the DD id’s of all of them.  Note that this was done by saving the view to a par file, turning that into a zip file and opening the spec.zip file within that par file.

This is a long list.  I moved this into a spreadsheet and got a distinct list of columns and table names and then removed the duplicate column names:

=+"insert into DV910.F98711 values (0,'F55WOSF2',0,'"&C2&"',"&B2&",'SF"&C2&"',0,'E910','','','','');"

Using the formula above I converted the DD item name into an insert statement into central objects.  Sure, people will tell you that you should not do this, but really you also should not look at your phone while you walk.  This is not going to kill you.

Note that I’ve not run this yet!!

I then created a table in OMW called F44WOFS2 and make the column  prefix SF and added 1 column that I knew did exist.  Created my primary key and saved it and checked it in.

Then I deleted the single column from central objects:

delete from dv910.f98711 where tdobnm = 'F55WOSF2';
commit;

And ran all my inserts!

insert into DV910.F98711 values (0,'F55WOSF2',0,'NUMB',1,'SFNUMB',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DOCO',2,'SFDOCO',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DCTO',3,'SFDCTO',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'PARS',4,'SFPARS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'TYPS',5,'SFTYPS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'PRTS',6,'SFPRTS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DL01',7,'SFDL01',0,'E910','','','','');

I then checked out the table and viola!

My table exists with 386 columns.  The exact order and sequence and everything.  This is nice.

The second part of what I need to do is create an oracle view that matches this format so that when I find on this table, I get the results that I intend to get.

Now before you say “This guy is going too far”, this is pretty much what 21CFR11 does:

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 ;

Mine’s just a little more complex…

create view testdta.F55WOSF2 (SFNUMB,
SFDOCO,
SFDCTO,
SFPARS,
SFTYPS,
SFPRTS,
SFDL01,
SFSTCM,
SFMCU,
SFLOCN,
SFAN8,
SFESSA,
SFPLSU,
SFPLSA,
SFTOPL,
SFTOEM,
SFBGTC,
SFPLOS,
SFPLLB,
SFPLMR,
SFSEQN,
SFVMRS32,
SFVMRS31,
SFCRR,
SFCRRM,
SFCRDC,
SFCURBALM3,
SFCURBALM2,
SFCURBALM1,
SFENTCKS,
SFSRYN,
SFAN8SRM,
SFAN8AS,
SFVEND,
SFREPTM,
SFREPDT,
SFFAILTM,
SFFAILDT,
SFFAILCD,
SFRLOT,
SFMCUCSL,
SFCSLPRT,
SFPRODF,
SFTIMEZONES,
SFDSAVNAME,
SFSEET,
SFSEST,
SFASN2,
SFASN4,
SFCOVGR,
SFGLCNC,
SFGLCCV,
SFLNGP,
SFEXR1,
SFTXA1,
SFREGION,
SFCTR,
SFRSTM,
SFRYIN,
SFENTCK,
SFMTHPR,
SFTMCO,
SFPHN1,
SFAR1,
SFWHO2,
SFPRODM,
SFISSUE,
SFPMTN,
SFVFWO,
SFJBCD,
SFWR20,
SFWR19,
SFWR18,
SFWR17,
SFWR16,
SFWR15,
SFWR14,
SFWR13,
SFWR12,
SFWR11,
SFUOM2,
SFTRAF,
SFSSOQ,
SFSRNK,
SFSRKF,
SFSQOR,
SFSHPP,
SFPRRP,
SFPRJM,
SFOTAM,
SFOBJ,
SFMPRC,
SFMPCE,
SFDUAL,
SFDRAW,
SFD6J,
SFD5J,
SFCRCF,
SFCRCE,
SFCRCD,
SFCHPR,
SFBSEQ,
SFATST,
SFASID,
SFALSE,
SFAID,
SFCTS8,
SFCTS7,
SFCTS4,
SFLEDG,
SFSRCN,
SFSHFT,
SFSCSP,
SFMWDH,
SFLINE,
SFFA8,
SFFA7,
SFFA6,
SFFA5,
SFFA4,
SFFA3,
SFFA2,
SFFA1,
SFFA0,
SFUPMT,
SFJOBN,
SFUPMJ,
SFSBLI,
SFUN,
SFJBST,
SFAFE,
SFKITL,
SFKIT,
SFUNIT,
SFDADS,
SFDADO,
SFDADC,
SFSFC,
SFACL0,
SFACL9,
SFACL8,
SFACL7,
SFACL6,
SFEXR,
SFEX,
SFMSGA,
SFDER,
SFEFTB,
SFPID,
SFADDS,
SFLOC,
SFLCT,
SFUSER,
SFINSI,
SFAIV,
SFINSA,
SFINSM,
SFINSC,
SFINSP,
SFRMK2,
SFRMK,
SFAMF,
SFDEXJ,
SFJCD,
SFLANO,
SFAPOM,
SFPURP,
SFAPUR,
SFPURO,
SFITCO,
SFFINC,
SFAITP,
SFAITY,
SFTXJS,
SFAROQ,
SFARCQ,
SFRSUB,
SFROBJ,
SFRMCU,
SFXSUB,
SFXOBJ,
SFXMCU,
SFDSUB,
SFDOBJ,
SFDMCU,
SFASUB,
SFAOBJ,
SFAMCU,
SFALRC,
SFARPC,
SFAESV,
SFNORU,
SFEQST,
SFDSP,
SFDAJ,
SFDSCC,
SFDL03,
SFDL02,
SFACL5,
SFACL4,
SFACL3,
SFACL2,
SFACL1,
SFSEQ,
SFAAID,
SFAPID,
SFWARJ,
SFWRFL,
SFCRTL,
SFWOYN,
SFFA23,
SFFA22,
SFFA21,
SFFA9,
SFMTRYN,
SFEQPN,
SFAN8DR,
SFVMRS34,
SFREGSTS,
SFMRRYN,
SFINSDTE,
SFSATYP,
SFTERMYN,
SFAN8DL,
SFEFFF,
SFBREV,
SFWOD,
SFLOTN,
SFCRTU,
SFURRF,
SFURAB,
SFURAT,
SFURDT,
SFURCD,
SFZE10,
SFZE09,
SFZE08,
SFZE07,
SFZE06,
SFZE05,
SFZE04,
SFZE03,
SFZE02,
SFZE01,
SFREFN,
SFVINNU,
SFSYEM,
SFCMOD,
SFPRODC,
SFOGNO,
SFSFXO,
SFOKCO,
SFOCTO,
SFOORN,
SFSTRX,
SFRCTO,
SFRORN,
SFANOB,
SFSLSM,
SFPA8,
SFSHAN,
SFADDJ,
SFSHPJ,
SFORDJ,
SFLNID,
SFKCOO,
SFMMCU,
SFCOOWN,
SFPNS,
SFPMPN,
SFDFMDP,
SFWSCHF,
SFMCULT,
SFXEVT,
SFCOCH,
SFKCO,
SFDCT,
SFDOC,
SFRMTHD,
SFISNO,
SFSCALL,
SFVMRS33,
SFHPLF,
SFRACM,
SFOACM,
SFACSU,
SFACSA,
SFESSU,
SFMTAM,
SFLBAM,
SFAMOT,
SFAMMC,
SFAMLC,
SFAPRT,
SFMPOS,
SFSAID,
SFACDN,
SFESDN,
SFXRTO,
SFNTST,
SFTDAY,
SFBOMC,
SFXDSP,
SFPHSE,
SFREAS,
SFPNRQ,
SFRTCH,
SFDRWC,
SFRREV,
SFRKCO,
SFRAT2,
SFRAT1,
SFLOTG,
SFLOTP,
SFCTS1,
SFLTCM,
SFLTLV,
SFPC,
SFPOU,
SFTDT,
SFMOH,
SFRESC,
SFINDC,
SFUNCD,
SFSPRT,
SFRTG,
SFBM,
SFPPFG,
SFPEC,
SFSHTY,
SFTRT,
SFTBM,
SFPBTM,
SFSHNO,
SFUOM,
SFQTYT,
SFSOQS,
SFSOCN,
SFSOBK,
SFUORG,
SFLITM,
SFAITM,
SFITM,
SFHRSA,
SFAMTA,
SFHRSC,
SFHRSO,
SFAMTC,
SFPAYT,
SFBRT,
SFSETC,
SFAMTO,
SFVR02,
SFVR01,
SFWR10,
SFWR09,
SFWR08,
SFWR07,
SFWR06,
SFWR05,
SFWR04,
SFWR03,
SFWR02,
SFWR01,
SFPPDT,
SFDAT,
SFDAP,
SFDRQJ,
SFSTRT,
SFTRDJ,
SFNAN8,
SFANT,
SFDPL,
SFANP,
SFANPA,
SFANSA,
SFANO,
SFPTWO,
SFCO,
SFAISL,
SFBIN,
SFSRST,
SFDCG,
SFSUB)
AS SELECT F1217.WRNUMB,
F4801.WADOCO,
F4801.WADCTO,
F4801.WAPARS,
F4801.WATYPS,
F4801.WAPRTS,
F4801.WADL01,
F4801.WASTCM,
F4801.WAMCU,
F4801.WALOCN,
F4801.WAAN8,
F4801T.WAESSA,
F4801T.WAPLSU,
F4801T.WAPLSA,
F4801T.WATOPL,
F4801T.WATOEM,
F4801T.WABGTC,
F4801T.WAPLOS,
F4801T.WAPLLB,
F4801T.WAPLMR,
F4801T.WASEQN,
F4801T.WAVMRS32,
F4801T.WAVMRS31,
F4801T.WACRR,
F4801T.WACRRM,
F4801T.WACRDC,
F4801T.WACURBALM3,
F4801T.WACURBALM2,
F4801T.WACURBALM1,
F4801T.WAENTCKS,
F4801T.WASRYN,
F4801T.WAAN8SRM,
F4801T.WAAN8AS,
F4801T.WAVEND,
F4801T.WAREPTM,
F4801T.WAREPDT,
F4801T.WAFAILTM,
F4801T.WAFAILDT,
F4801T.WAFAILCD,
F4801T.WARLOT,
F4801T.WAMCUCSL,
F4801T.WACSLPRT,
F4801T.WAPRODF,
F4801T.WATIMEZONES,
F4801T.WADSAVNAME,
F4801T.WASEET,
F4801T.WASEST,
F4801T.WAASN2,
F4801T.WAASN4,
F4801T.WACOVGR,
F4801T.WAGLCNC,
F4801T.WAGLCCV,
F4801T.WALNGP,
F4801T.WAEXR1,
F4801T.WATXA1,
F4801T.WAREGION,
F4801T.WACTR,
F4801T.WARSTM,
F4801T.WARYIN,
F4801T.WAENTCK,
F4801T.WAMTHPR,
F4801T.WATMCO,
F4801T.WAPHN1,
F4801T.WAAR1,
F4801T.WAWHO2,
F4801T.WAPRODM,
F4801T.WAISSUE,
F4801T.WAPMTN,
F4801T.WAVFWO,
F4801T.WAJBCD,
F4801T.WAWR20,
F4801T.WAWR19,
F4801T.WAWR18,
F4801T.WAWR17,
F4801T.WAWR16,
F4801T.WAWR15,
F4801T.WAWR14,
F4801T.WAWR13,
F4801T.WAWR12,
F4801T.WAWR11,
F4801T.WAUOM2,
F4801T.WATRAF,
F4801T.WASSOQ,
F4801T.WASRNK,
F4801T.WASRKF,
F4801T.WASQOR,
F4801T.WASHPP,
F4801T.WAPRRP,
F4801T.WAPRJM,
F4801T.WAOTAM,
F4801T.WAOBJ,
F4801T.WAMPRC,
F4801T.WAMPCE,
F4801T.WADUAL,
F4801T.WADRAW,
F4801T.WAD6J,
F4801T.WAD5J,
F4801T.WACRCF,
F4801T.WACRCE,
F4801T.WACRCD,
F4801T.WACHPR,
F4801T.WABSEQ,
F4801T.WAATST,
F4801T.WAASID,
F4801T.WAALSE,
F4801T.WAAID,
F4801T.WACTS8,
F4801T.WACTS7,
F4801T.WACTS4,
F4801T.WALEDG,
F4801T.WASRCN,
F4801T.WASHFT,
F4801T.WASCSP,
F4801T.WAMWDH,
F4801T.WALINE,
F1201.FAFA8,
F1201.FAFA7,
F1201.FAFA6,
F1201.FAFA5,
F1201.FAFA4,
F1201.FAFA3,
F1201.FAFA2,
F1201.FAFA1,
F1201.FAFA0,
F1201.FAUPMT,
F1201.FAJOBN,
F1201.FAUPMJ,
F1201.FASBLI,
F1201.FAUN,
F1201.FAJBST,
F1201.FAAFE,
F1201.FAKITL,
F1201.FAKIT,
F1201.FAUNIT,
F1201.FADADS,
F1201.FADADO,
F1201.FADADC,
F1201.FASFC,
F1201.FAACL0,
F1201.FAACL9,
F1201.FAACL8,
F1201.FAACL7,
F1201.FAACL6,
F1201.FAEXR,
F1201.FAEX,
F1201.FAMSGA,
F1201.FADER,
F1201.FAEFTB,
F1201.FAPID,
F1201.FAADDS,
F1201.FALOC,
F1201.FALCT,
F1201.FAUSER,
F1201.FAINSI,
F1201.FAAIV,
F1201.FAINSA,
F1201.FAINSM,
F1201.FAINSC,
F1201.FAINSP,
F1201.FARMK2,
F1201.FARMK,
F1201.FAAMF,
F1201.FADEXJ,
F1201.FAJCD,
F1201.FALANO,
F1201.FAAPOM,
F1201.FAPURP,
F1201.FAAPUR,
F1201.FAPURO,
F1201.FAITCO,
F1201.FAFINC,
F1201.FAAITP,
F1201.FAAITY,
F1201.FATXJS,
F1201.FAAROQ,
F1201.FAARCQ,
F1201.FARSUB,
F1201.FAROBJ,
F1201.FARMCU,
F1201.FAXSUB,
F1201.FAXOBJ,
F1201.FAXMCU,
F1201.FADSUB,
F1201.FADOBJ,
F1201.FADMCU,
F1201.FAASUB,
F1201.FAAOBJ,
F1201.FAAMCU,
F1201.FAALRC,
F1201.FAARPC,
F1201.FAAESV,
F1201.FANORU,
F1201.FAEQST,
F1201.FADSP,
F1201.FADAJ,
F1201.FADSCC,
F1201.FADL03,
F1201.FADL02,
F1201.FAACL5,
F1201.FAACL4,
F1201.FAACL3,
F1201.FAACL2,
F1201.FAACL1,
F1201.FASEQ,
F1201.FAAAID,
F1201.FAAPID,
F1201.FAWARJ,
F1201.FAWRFL,
F1201.FACRTL,
F1201.FAWOYN,
F1201.FAFA23,
F1201.FAFA22,
F1201.FAFA21,
F1201.FAFA9,
F1217.WRMTRYN,
F1217.WREQPN,
F1217.WRAN8DR,
F1217.WRVMRS34,
F1217.WRREGSTS,
F1217.WRMRRYN,
F1217.WRINSDTE,
F1217.WRSATYP,
F1217.WRTERMYN,
F1217.WRAN8DL,
F1217.WREFFF,
F1217.WRBREV,
F1217.WRWOD,
F1217.WRLOTN,
F1217.WRCRTU,
F1217.WRURRF,
F1217.WRURAB,
F1217.WRURAT,
F1217.WRURDT,
F1217.WRURCD,
F1217.WRZE10,
F1217.WRZE09,
F1217.WRZE08,
F1217.WRZE07,
F1217.WRZE06,
F1217.WRZE05,
F1217.WRZE04,
F1217.WRZE03,
F1217.WRZE02,
F1217.WRZE01,
F1217.WRREFN,
F1217.WRVINNU,
F1217.WRSYEM,
F1217.WRCMOD,
F1217.WRPRODC,
F1217.WROGNO,
F1217.WRSFXO,
F1217.WROKCO,
F1217.WROCTO,
F1217.WROORN,
F1217.WRSTRX,
F1217.WRRCTO,
F1217.WRRORN,
F1217.WRANOB,
F1217.WRSLSM,
F1217.WRPA8,
F1217.WRSHAN,
F1217.WRADDJ,
F1217.WRSHPJ,
F1217.WRORDJ,
F1217.WRLNID,
F1217.WRKCOO,
F1217.WRMMCU,
F1217.WRCOOWN,
F4801T.WAPNS,
F4801T.WAPMPN,
F4801T.WADFMDP,
F4801T.WAWSCHF,
F4801T.WAMCULT,
F4801T.WAXEVT,
F4801T.WACOCH,
F4801T.WAKCO,
F4801T.WADCT,
F4801T.WADOC,
F4801T.WARMTHD,
F4801T.WAISNO,
F4801T.WASCALL,
F4801T.WAVMRS33,
F4801T.WAHPLF,
F4801T.WARACM,
F4801T.WAOACM,
F4801T.WAACSU,
F4801T.WAACSA,
F4801T.WAESSU,
F4801.WAMTAM,
F4801.WALBAM,
F4801.WAAMOT,
F4801.WAAMMC,
F4801.WAAMLC,
F4801.WAAPRT,
F4801.WAMPOS,
F4801.WASAID,
F4801.WAACDN,
F4801.WAESDN,
F4801.WAXRTO,
F4801.WANTST,
F4801.WATDAY,
F4801.WABOMC,
F4801.WAXDSP,
F4801.WAPHSE,
F4801.WAREAS,
F4801.WAPNRQ,
F4801.WARTCH,
F4801.WADRWC,
F4801.WARREV,
F4801.WARKCO,
F4801.WARAT2,
F4801.WARAT1,
F4801.WALOTG,
F4801.WALOTP,
F4801.WACTS1,
F4801.WALTCM,
F4801.WALTLV,
F4801.WAPC,
F4801.WAPOU,
F4801.WATDT,
F4801.WAMOH,
F4801.WARESC,
F4801.WAINDC,
F4801.WAUNCD,
F4801.WASPRT,
F4801.WARTG,
F4801.WABM,
F4801.WAPPFG,
F4801.WAPEC,
F4801.WASHTY,
F4801.WATRT,
F4801.WATBM,
F4801.WAPBTM,
F4801.WASHNO,
F4801.WAUOM,
F4801.WAQTYT,
F4801.WASOQS,
F4801.WASOCN,
F4801.WASOBK,
F4801.WAUORG,
F4801.WALITM,
F4801.WAAITM,
F4801.WAITM,
F4801.WAHRSA,
F4801.WAAMTA,
F4801.WAHRSC,
F4801.WAHRSO,
F4801.WAAMTC,
F4801.WAPAYT,
F4801.WABRT,
F4801.WASETC,
F4801.WAAMTO,
F4801.WAVR02,
F4801.WAVR01,
F4801.WAWR10,
F4801.WAWR09,
F4801.WAWR08,
F4801.WAWR07,
F4801.WAWR06,
F4801.WAWR05,
F4801.WAWR04,
F4801.WAWR03,
F4801.WAWR02,
F4801.WAWR01,
F4801.WAPPDT,
F4801.WADAT,
F4801.WADAP,
F4801.WADRQJ,
F4801.WASTRT,
F4801.WATRDJ,
F4801.WANAN8,
F4801.WAANT,
F4801.WADPL,
F4801.WAANP,
F4801.WAANPA,
F4801.WAANSA,
F4801.WAANO,
F4801.WAPTWO,
F4801.WACO,
F4801.WAAISL,
F4801.WABIN,
F4801.WASRST,
F4801.WADCG,
F4801.WASUB
FROM TESTDTA.F4801, TESTDTA.F4801T, TESTDTA.F1201, TESTDTA.F1217  
WHERE (F1217.WRNUMB = F1201.FANUMB AND F4801.WANUMB = F1201.FANUMB AND F4801.WADOCO = F4801T.WADOCO) ORDER BY F1217.WREQPN ASC ;

Now, the big test – UTB

Yay!  I’ve done it… 

Now I need to create my complex join to only get the distinct list of assets, but I think that this is going to be the easy part.

I guess I should show that also.

create view testdta.F55WOSF2 (SFNUMB,
SFDOCO,
SFDCTO,
SFPARS,
SFTYPS,
SFPRTS,
SFDL01,
SFSTCM,
…
SFSUB)
AS SELECT F1217.WRNUMB,
F4801.WADOCO,
F4801.WADCTO,
F4801.WAPARS,
F4801.WATYPS,
F4801.WAPRTS,
F4801.WADL01,
F4801.WASTCM,
…

F4801.WASUB
FROM TESTDTA.F4801, TESTDTA.F4801T, TESTDTA.F1201, TESTDTA.F1217  
WHERE F1217.WRNUMB = F1201.FANUMB AND F4801.WANUMB = F1201.FANUMB AND F4801.WADOCO = F4801T.WADOCO
AND F4801.WADOCO= (SELECT MAX(WADOCO) from TESTDTA.F4801 innerF4801 where innerF4801.WANUMB=F4801.WANUMB )
ORDER BY F1217.WREQPN ASC ;

As I don’t really care about the WO that I’m choosing, I’m just getting the newest one, as I just want a list of assets at the end of the day.

And now I only get 33 records, which is the list of unique assets that match my criteria.

Finding when the last time your object was stuck into a package

‘said the vicar to the nun!

Bit of comedy gold there!  No, this is a bit serious.  So you’re in the situation where you’ve done some modifications – awesome!

But your modifications are not on the web server – not awesome.

There are literally 1 million things to look at to determine what has gone wrong, but here is a method that I used fairly effectively.

Firstly use OMW to work out when was the last time the object was checked in and promoted.  This history is easy to read and can be done with row exit on the object.  Remember to remove the project name from the above screen if you want to see everything.

This tells you a little bit, it tells you that a package needed to have been built after 11/02/16 for this object to be on the web (and the package had to be deployed)

I use UTB to find the package detail tables in the system, these are F9622 and F9631 (which you can see the results below)

These show me that the object P55WOSF was only put into a package on the 29/01/16 – so this is the problem.  Beauty.

F96225 is also handy if you need to see whether things were successful

 

I don’t need to know in this situation, but really you should link to the package deployed table to know if it’s been deployed.

 

F98826 is going to assist you there.

Match this with the actual screen

30 is deployed in the UPINPKST field.  So you can see the latest package has not been deployed.

Please also remember, that you can do some other analysis with P98770

Which also lists out all of the updates to the current deployed FULL

Hopefully the combination of the above information helps you track down your modification that suffering from AWOL-ification.