JDE Bulk change to BATCH QUEUE in versions

I get asked this all of the time, and thought that I'd write a few notes on the topic (before I forget).  Unfortunately the queue of a version is written to central objects, not F983051 in a nice and easy place.

Note that this is going to assist you if you need to find where a batch version queue is actually at.  As it exists in a number of locations that are needed at runtime.  Starts in F98761 central objects, gets copied to the active package build central objects (pending tools release) then gets converted from there to F989999 and finally ends in cache.

The value is stored in the F98761 BLOB in both central objects and the deployed package.  Central objects being the main concern.  Once again, this is the BLOB field

select * from DV920.f98761DVC920A where RSOBNM = 'R42565' and rsvers = 'TEST';

You could tear this down with some JAVA (CLOB / BLOB) and little endian to change it in SQL - but that is going to be a little difficult.

select utl_raw.cast_to_varchar2(rsrdablob), rsobnm from DV920.f98761DVC920A where RSOBNM = 'R42565' and rsvers = 'TEST';

You'll see that the utl_raw functions in SQL don't help you enough because of the platform independent byte order.  Doh.

Anyways, let's get around.  

All we need to do is create a project, insert all of our batch versions into the project and then save a par file.  You know that a par file is a series of zip files, like a Russian Doll (jeez, I hope that is not racist).  Once you get to the bottom of your file structures, you'll find an entry like:

Which has the following contents:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<RDASPEC xmlns:et="http://peoplesoft.com/e1/metadata/v1.0/erptypes" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://peoplesoft.com/e1/metadata/v1.0" Version="TEST" Report="R42565">
-<RDARecord>
-<RDAReport PaperType="LETTER" SubSysWaitTime="0" NextTextID="965" ScaleFactor="0" LimitRowsValue="0" SourceStyleFlags="0" DSName="R42565" POTemplate="T42565" ReportFlags="0" PropertyFlags="1092">
<RDAPrintInfo RDAQueueName="QBATCH" RDAServerString1="*NONE" PageOrientation="2" PageSource="15" PageHeight="15840" PageWidth="12240" PageSize="1"/>
<et:JdeLogFont charSet="1" orientation="0" height="-14"/>
</RDAReport>
</RDARecord>
</RDASPEC>

Easy hey?  All you need to do is update the RDAQueueName above [I just write some basic code to do that] - I'm so old I use bash... and sed and awk...  And then BOOM - restore the par file that you pack back up in your scripts

for file in `ls $expDir/*.par`

  do

    #echo $file

    dir=`echo $file | awk -F. '{print $1}'`

    #echo $dir

    unzip -q $file -d $dir

done

That sort of stuff, just lots more.

So, you get back to your fat client and ensure the project is at the correct status and do a restore of the par file.  You can use UTB (as above) to check that the currently deployed package and central objects have been updated...  Nice.  Then you go and run the job - and the queue is still wrong!  Doh.

Referring to the manual (and me) says to run a quick update package, and all will be good.  But if you cannot wait for that.  Follow these steps

Goto P98770 and find the deployed package

Then write up some SQL like this: - where DVC920A is your active full package.

select * from dv920.f989999DVC920A where wboid like '%R42565%';

delete from dv920.f989999DVC920A where wboid like '%R42565%';

commit;

Then you run it again and it still does not work... WHAT!!!

Oh, delete the cache from the web instance!

BOOM!

Now, you can look at the F989999 and see the NEW QUEUE defined

select wboid,utl_raw.cast_to_varchar2(WBJPO) from dv920.f989999DVC920A where wboid = 'RPTJOBQUEUE-R42565-TEST';

Note how consistently inconsistent JDE is, if you run the command above, you'll see that the job queue can be read (and written by a human).  So if you were a REAL cowboy and had to change the queue on the "superfly" - you might be able to manipulate the F989999 and clear cache only.  I would only attempt that if I was desperate (or in a rush... or wanted a rush)...

I'm still working on the script for the actual bulk queue change, else I might just post it here.  I believe I've provided everything you need to get this done yourself.

MD5 checksum

Not enough blogging...  I know, I know.

Release23 might get a blog or two, I'm pretty excited about some of the enhancements.  I'll say more later, but calling a orchestration from the new workflow modeler is a relief!!

Now, back to this post - which is super basic and we all should now it (and probably do).

Checksum's are used to ensure that the file that had been uploaded has not been tampered with.  So when it's uploaded, quite often there will be a checksum next to the download icon that you can check the download and ensure that no nasty people have changed the download at any point in time.  Nice hey.

You also probably know that a checksum is basically a fingerprint of the file (like a nice and short unique string that is a representation of a much larger piece of data).  So you can MD5 a 10MB file and still only get 32 characters to prove it's identity.  And we know that we actually have [36 = 26 alpha and 10 numeric] 36^32 possibilities.  so that is a lot of uniqueness.

But recently I've needed to validate that some code we have written is making it to various servers (through many proxies and more security than you can poke a stick at), in tact.  So now I'm using checksum.

It's SO simple: 

windoze:

C:\Users\shannon.moir\Downloads>certutil -hashfile myAccess.war MD5

MD5 hash of myAccess.war:

62250ed14d149a7dcb7d35d73519ad06

CertUtil: -hashfile command completed successfully.

C:\Users\shannon.moir\Downloads>where certutil

C:\Windows\System32\certutil.exe

Linux

[ec2-user@F5PLAY1 tmp]$ md5sum myAccess.war

62250ed14d149a7dcb7d35d73519ad06  myAccess.war

Wow - so simple!!!  Therefore I can check git and my customers / servers results and know that it's not a corruption is some transmit

OIC and JDE - the perfect mix (like oil and water)?

I recently did an OIC bootcamp, oh man.  There was so much I liked about the product.  In terms of an integration platform - it seemed to be really well thought out.  I love the fact that you could add human interfaces to integrations to make decisions.  That's right - web forms and human interaction on the fly for an integration!  That is nice.

I also really liked the native connections to VBCS and the fact that if you were running cloud ERP of any of the other oracle cloud products - it seems silly if you are not using Oracle Integration Cloud as part of (or all of) your integration solution.  So much so that we are strategically recruiting in this space and want to own customers cloud ERP integrations.

Though, this is a JDE blog and I want to comment on the JDE connector that is available with OIC...  I was just about to start writing accelerators for linking Cloud ERP data with JDE data.  I was ready to create synchronisation of popular transactions - potentially starting with financials.  This was going to be the foundation of capability.  Modernising my teams consulting skills from JDE to Cloud ERP.  An accelerator for customers to migrate their data and run things SBS - side by side.

Then, it hit me...

Nice interface...  Love the drag and drop.  Love the fact that the orchestration studio developers MUST be working with the OIC team, because this all looks too cool and too similar!

This is really neat and a well thought out design.  Like I said though, similar to orchestration.  And what do people find frustrating about orchestration (especially hard core developers) - the lack of smashing a keyboard.  We love writing code, finding code, massive changes...  we love regex...  The above does not give you much love for this.

But, I digress.

I want to create a connection and I need to use an adapter (of course, an API is harder to create and the generic rest connector looks way too "RESTY" to me (and if you know JDE's implementation of rest, you will support me here).

Looking at the details of this connect (can't WAIT to paste in my discovery URL or perhaps the swagger or OpenAPI definitions of all the orchestrations I want to call...).

Getting excited...

Nearly there!

Huh??  WSDL??  SOAP...  BSSV... Oh no...  I'm crying all over my plans to take over the world...

I need to get my team to write a connection to REST-ify calls to JDE.  This could be done.

If you need to expose some relative resource URIs and wrap them up into some funky orchestration calls...  Otherwise this is not going to be an amazing integration.

Anyway, I'm going to fight the good fight with the REST connection to see if I can get some orchestration calls working.  I'll be sure to post how I go.

But, OIC - please modernise your connector for JDE to support orchestrations.  PowerApps does it SOOOO nicely.  I know powerApps is not an integration platform, but you know what I mean.

JD Edwards on AWS - with all the fruit

Fusion5  did something amazing about 3 years ago - before we thought it was possible.  We created an elastic JD Edwards in public cloud that used ephemeral pods to run JDE workloads.  This incredible architecture allows the JD Edwards server count to expand and contract with the requisite user demand.  So, as people log in... servers expand...  

Then as demand wains, then the servers also contract - but they contract ONLY when users and batch jobs have finished running on them.  The entire architecture is aware of itself.  The architecture is bought up with all signals being monitored (bespoke ones too - connected users, running UBE's) and these signals are interpreted for expansion and contraction rules.   All of the standard CPU data can be used to trigger any actions with the architecture too.

What does this allow us to do?  Well - once again, let's let the picture do the talking...  We can see that on any given day, how many users logged into JDE and how many unique ephemeral hosts were started to process that load.  We have a cost from AWS in the table [oh yes! this is the public cloud that I'm talking about in this instance], which is the actual costs for the class of machine that is running the pod workload.  We also have the average server response time listed, which is critical for us to determine if the number of servers is appropriate or not.

Finally you will see the mesmerising (oh yes, I think it is!!!) pattern of host growth (bar) vs. connected users (line) which track each other exactly over the month period.

Now, I have the ability to test different hosts, different tools releases, different OS's if I want.  I can release that POD into the farm and measure how it performs.  I can change the server class or anything and know the exact impact that this has on JDE.  I also have all of the logs coming back to a central cloud console (in AWS of course) to do my troubleshooting on.

This is by far the most elegant and easy to manage JD Edwards instance I have ever worked on.  Do you want yours to be this cool?  reach out!

\

JDE oracle licence audit, again and again

There are numerous ways of interpreting oracle licence audits, but my guess is that if oracle wanted to pursue you - you could get in trouble pretty easy.

I invite you  all to pull out your licence agreements and pay special attention to the definition of an application server.  This is someone that is authorized by you to use an application regardless of whether they are using the application.

If you take this as a lawyer would, I would argue that you do not authorise people if you leave security all doors open.  The words and context of this definition seem to imply an active act of authorisation.  This is not active authorisation, you have not performed any actions to authorise.  But - if you lock everything out and then start to authorise back (all doors closed), then you are opening yourself up for issues.  Therefore a solid security model where you are actively allowing people to use JDE programs, you are going to need to ensure that the end users are licenced.

It's strange that this 2010 definition then goes into breaking down particular modules and transactions.

Just testing of the definition has survived the test of time: from https://www.oracle.com/a/ocom/docs/license-definition-rules-v091120.pdf 

Application User: is defined as an individual authorized by You to use the applicable licensed application Programs which are installed on a single server or on multiple servers regardless of whether the individual is actively using the Programs at any given time. If You license the Oracle Self Service Work Request option in conjunction with Oracle Enterprise Asset Management, You are required to maintain licenses for the equivalent number of Application Users licensed and You are granted unlimited access to initiate work requests, view work request status and view scheduled completion dates for Your entire employee population. Application Users licensed for Oracle Order Management are allowed to manually enter orders directly into the Programs but any orders entered electronically from other sources must be licensed separately. For Oracle Sourcing, Oracle Fusion Sourcing, Oracle iSupplier Portal, Oracle Fusion Supplier Portal, Oracle Services Procurement, PeopleSoft eSupplier Connection, PeopleSoft Strategic Sourcing, PeopleSoft Supplier Contract Management and JD Edwards Supplier Self Service Programs, use by Your external suppliers is included with Your application user licenses. For the purposes of the Oracle Financial Services Operational Risk Solution Program, employees who are just contributing information to the Program via the applicable user interface shall not be counted as application users

So yes, seems the same.

Given the above two definitions, it seems that the best thing you can do to avoid any problems is to ensure that you have the appropriate security in place which will enforce your licence metrics.  The old saying that "enterprise licences" prevented audits is not right, as an enterprise licence is generally only for a certain number of modules.

Short addendum - Enterprise Metric:

Enterprise licences can allow customers to have any number of users using JDE and they only need to pay a certain amount of fees based upon revenue of their business.  There are generally additional provisions for additional revenue - i.e. if you increase your revenue over the number listed - you will need to buy additional units.

For example, a customer might have a table like the above.  It means that they can have heaps of users, but will pay oracle based upon the perceived revenue number above (313 $M).  They will pay a fixed fee to oracle based upon this number.  Note that this is only for 19 named modules, not all of the JDe modules.

If the revenue goes above this, then they'll need to "top up" in increments that are generally listed in their licence agreement.  The increments are generally in tranches, therefore if the revenue is above the 313$M and in the next tranche (156$M), then they'll need to pay the additional amounts.

RTE and JDE - what is stored in the BLOB?

I'm chasing down a missing RTE message.

RTE's are good, but confusing and the subject of many of my posts.

This is talking a little about converting the BLOB from and RTE into something slightly readable (or at least searching).

We all know that out of the box, RTE write to a single copy of the F90710 - that is the first thing that it does.

The following statement allowed me to find the owner for F90710 in PY - I remember that we spilt them and I could not remember where.  This is quicker than looking up OCM.

select owner from all_tables where table_name = 'F90710';

I then take a look at some messages, I have history enabled, so there are a heap of messages at status 5.

select * from py920.f90710  order by etevntseq desc;

I then start to rip apart the BLOB field to work out the contents, of course -we know [and are frustrated by] the fact that these are stored as BLOBs in some kinda weird and AS/400 proof format.

Using the UTL_RAW package I can view the contents of the ETEDATA field:

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where etevntseq > 17000 order by etevntseq desc;

So we can see from above that we can make out the characters in the BLOB using cast to varchar2.  This is neat...  We kinda think that because all of those spaces are there, we could use cast_to_nvarchar2, but this leaves us with a bunch of rubbish.  I think I have blogged on this before that JDE is flipping the double bytes into something that the standard NVARCHAR string functions do not like, so we need to do our comparisons (of strings) using S P A C E S between the characters.

There are a bunch of util functions that you can use over the results of the function, as it's basically a VARCHAR.  The other nice thing is that you can also see the DSTR that is storing the values in the BLOB:

D4202310A and D4202310B

So use this to reverse engineer what you are seeing above.

Once would also guess that the 1252 in the header was something related to https://en.wikipedia.org/wiki/Windows-1252

Anyway, back to what we want...  Tracking down events for certain sales orders.

I know that the SO number is 19274775

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where instr(utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)),'19274775') > 1 order by etevntseq desc;

Above shows how I can work through the data.  Note that this does not have spaces because the sales order number is a numeric [in the scheme of things].  Also, important to note that you can return the value of instr to then know the index of the sales order number and make your SQL much more efficient.

We only have a single row - which is a shame, we seem to be missing the status code of ADD, but we have the update.  So I need to go searching.

Hopefully that shows some basics of how you might be able to start debugging some RTE problems in JDE.

Simple and secure Azure SSO for JDE

Who'd like to be able to use all native security options available to them via Azure AD used in JD Edwards - me!

Perhaps take advantage of self enrolling to be able to use the application in the fist place.

Our SSO solution will work side by side with the native capability of JD Edwards to use AD for long username and password authentication - but we give you the additional ability to use Azure and the power of Azure (for things like conditional access / MFA) natively in JDE.

We let Azure do all of the heavy lifting, see https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-protocols-oidc We cleverly use openID connect (an extension of oauth2.0) to allow users to authenticate to Azure for their access to JD Edwards.  

You can see from the above that we do a couple of modifications to JDE to allow this to happen.  Firstly, we allow a redirect (manually with a button or automatically) to send users to Azure:

This button is the second dotted line in the MSFT supplied schematic.

The user then enters credentials with Azure - not with our products - they negotiate.  Said negotiation might include conditional access from various available signals - see below:

The browser then gets a login token from Azure - thanks for that and passes this back to the /auth handler that we have installed on JAS WLS instance.  This handler needs to go out to the internet to validate that the id_token is valid and then generates a PS_TOKEN to all of the user to log into JD Edwards.

The PS_TOKEN is generated for a custom security node, which improves the security posture of the JD Edwards installation.

Note that if the user already has a id_token - then the visit to Azure is ignored and goes directly to myAccess to validate the id_token - see below for a JDE centric view of things.

Once myAccess has generated A PS_TOKEN, this is sent to JDE for verification.  If JDE likes what it's sent, the user is granted login permissions and can continue to use said token for it's defined lifecycle.

So hopefully you can see from the above that all of the heavy lifting is being done by Azure.  We are neatly and securely playing the JDE specific part of logging the user into JD Edwards.

The creation of an enterprise application in Azure is trivial and only takes about 30 minutes.  We are then able to patch your tools release to have an additional "login" button as seen above and add some redirects into a couple of jsp files.  

Finally, the glue in all of this is an enterprise application that sits in WLS and does the heavy lifting in terms of validating the id_token and also generating the PS_TOKEN.  Note that this also rips apart the authentication JWT to extract the long username that is used next to the PS_TOKEN to ensure that the correct user is being logged into JDE.

The design above easily caters for additional Enterprise Applications to be defined in Azure, that will have different id_tokens / cookies - which will also allow for native logging into different JD Edwards environment.

see more here:  https://www.fusion5.com.au/jd-edwards/myaccess/ 

Stored procedure calling from JDE - yes please

I've been involved in a couple of projects that need the ability to call stored procedures from JD Edwards directly.  It does get messy.  Previously I've written an intermediate DLL which loads the OCI DLL's and eventually calls OCIExecuteStmt, which worked well and was secure.  This also did really nice and granular security [kinda] and transaction processing was a breeze.

Despite this being neat, it required constant maintenance, as you needed to repack the tools release with the intermediate DLL each time.  I also had to store the password for the user that needed to run the stored procedures in the JDE.INI.  This was cool and I wrote some encryption algorithms to that would encrypt and decrypt the passwords that I wanted to use.

I've changed tact, I don't know which method I prefer.  I now created a trigger on a standard JD Edwards table that would fire off and run the SP (with up to 3 parameters) before insert into the table.  I chose before insert, as this gave me transaction control.  It's pretty native that if the SP fails when the trigger calls it, then this will also bomb out the insert and return an error to JDE.  This allows for the transaction processing and error handling I need.

I actually do not really like this solution, an insert that calls a trigger that calls a stored procedure - but we are limited in how we are able to execute statements.

I created table F55TRIGX:

Name        Null?    Type          
----------- -------- --------------
TGMATH01    NOT NULL NUMBER        
TGY55SPROCN          NVARCHAR2(256)
TGY55PARM1           NVARCHAR2(512)
TGY55PARM2           NVARCHAR2(512)
TGY55PARM3           NVARCHAR2(512)

It's a little annoying that JDE needs a PK for a table like this, but hey-  I just soaked up a NN.  So, when you insert into the above, my trigger essentially runs:

execute immediate TGY55SPROCN[(TGY55PARM1,TGY55PARM2,TGY55PARM3)]

Note that everything in [] is optional.

I don't really need to provide much more than the trigger text below to get you working at an oracle site.  The trigger writing process is SO terrible for logging and debugging, so thank me later that you can start with a working example.

create or replace trigger TESTDTA.F55TRIGX_GO

before insert on TESTDTA.F55TRIGX

for each row

declare

  results2 varchar2(256);

  szProcedureName varchar(256);

  szParm1 varchar(512);

  szParm2 varchar(512);

  szParm3 varchar(512);

  szCommand varchar2(2048);

begin

  szParm1:=ltrim(rtrim(:new.TGY55PARM1));

  szParm2:=ltrim(rtrim(:new.TGY55PARM2));

  szParm3:=ltrim(rtrim(:new.TGY55PARM3));

  szProcedureName:=ltrim(rtrim(:new.TGY55SPROCN));

  if length(szProcedureName)>0 then

    -- Call this procedure with the parameters

    if(length(szParm1)>0 and length(szParm2)>0 and length(szParm3)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''',''' || szParm3 || ''')' || '; end;';

    elsif (length(szParm1)>0 and length(szParm2)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''')' || '; end;';

    elsif (length(szParm1)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''')' || '; end;';

    else

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '; end;';

    end if;

    execute immediate szCommand ;

    results2 := 'True';

  end if;

end;

This is a nice way for you to automate tasks or perform some "heavy lifting" in the database.  note that you probably need to prefix the SP with the owner or manage that in a consistent way.

JDE and IFTTT - post 1 triggers

 IFTTT is awesome - like really awesome.  I think that PowerAutomate would be awesome if I was an accountant, or an actuary - but IFTTT is a little more fun and seems to be more tactile.

I recently did a presentation at the innovation symposium on using IFTTT and JDE.  Unfortunately my demo did not go so well, so I thought that I would write a little more on the solution that we've put together for JDE and IFTTT.

Firstly, you need to understand some basics about IFTTT.

IF event occurs (see trigger) then perform action(s).  Easy...

So in my JDE examples, it could be the trigger or it could be the action - easy too.

Let's think about JDE being the trigger:

We need to write a standard connection to IFTTT trigger services, we have done this via a lambda based node project hosted in AWS using their API gateway...  Which BTW - is ULTRA cool.  We get to handle all of the security.

What you are quickly going to find is that JDE cannot talk native IFTTT - but you just need a little layer of coercion.

https://ifttt.com/docs/connect_api#triggers - shows what you need to do.

But it's all pretty easy if you are a guru like my friend Max (May or may not be their real name).

So you can create your connection, which essentially calls your API gateway functions that manipulate the payload to talk native IFTTT.  Therefore you can implement your own quasi RTE functionality by overriding all of the OK buttons on JDE forms with form customisations which call orchestrations which get's to IFTTT via connectors and API gateway and lambda...  Simple hey?  What could possibly go wrong?

But it's really nice.

My triggers are pretty simple and have a payload too (or in IFTTT speak, ingredients)

So I can add items to the payload and use them in subsequent IFTTT processes.  So, in the example about.  I can override the WO entry screen to call P1 work order created.  I can ensure that only P1s are trapped, because I can used specific logic in the orchestration to control that.

Therefore my trigger is fired and IFTTT is listening for this with it's magic.  It then looks at it's applets to see what to do when this trigger event receives a payload.

AS you can see from above, I have just automated the fact that when an urgent P1 WO is created in JDE, I get an SMS message.  That is pretty cool.  I could choose many actions, I could email partners or suppliers based on the equipment.  So many nice options are available for me to automate processes out of JDE.

I could illuminate lights and change flapz boards too.  Really easy to get up and running.

I will over off JDE as the "action" creator in my next blog entry.

We will look to publish this capability publicly in time, currently it's all working for our demo system.

 

JDE license information part 2

I'm going to document things to the best of my knowledge today about JDE licenses.  First of all, lets cover of the spelling.  In Australia, using license as a verb means you are going to use an S, but using licence as a noun (a thing - and when we refer to a JDE licence, that is a noun), so I should be using a c.

A good reminder of the above, is that I have a drivers licence in Australia - phew.

Oh, something else important.  Do not rely on this blog for a court case or legal advice.  Oracle will have a point of view that could be different to mine.  I've used 20 years of JDE experience and many oracle official documents and some unofficial communication to formulate my views expressed below.

Now, JDE licence in Australia, JDE license in US.  What constitutes one?

It gets complicated.

I have been told a number of times that if security allows you access to a program, then you need to have a licence for said program.  This is important, you CANNOT have "all doors open" security if you accept this as a valid definition of licence - as all of your users would need licences for all modules in JDE that are not explicitly secured out.

You can check your security using P00950 and make determinations from there.  Quite often if you need a licence audit done by oracle - ALL they can do is check the USER fields on transaction tables.  Oracle will divide transaction tables by there object librarian system code (F9860.SISY) and determine the module that you will need to be able to write records to that table.  ORacle will run some software that will basically select count(distinct(SDUSER)) from F4211 and say - you need X number of licences.  But, things are not that simple (they rarely are)...

What if you are getting new users and disabling users as a part of business as usual - what actually constitutes the need for a licence...  I mean, could I look at each and every day as a unique unit of time and find the number of unique user id's that have transacted against all JDE transnational tables in 42B

You can see from the above report that you are able to use.  You can find all tables in the Sales order management system code and select unique USER from all of these and add them together.  If we were using a timeframe, then you might also need to consider the UPMJ and UPMT fields to further refine the data to a list of users that have transacted.  This is ALL oracle can do.  There is nothing else available to track how many users are using the system.  

Of course, they are going to audit F0092 and F98OWSEC/ F98LPSEC to see how many enabled users there are too.  Note that if you enable LDAP or an SSO product like ours - myaccess (https://www.fusion5.com.au/jd-edwards/myaccess/), then you are going to reduce problems with lingering accounts.

If you have object identification enabled, that could also be used to farm data, but it's not easy.

So, at this stage...  If a user can sign in and is not secured out of a program, they need a valid license.  I'm told that there is a monthly calculation period for this.  But - F9312 for security history would need to be used and deletions would be hard to calculate...  Wowsers, things are getting really difficult, aren't they.  I believe oracle generally do not pursue this model and are more likely to run their audit software and get the transactional user counts, as I have indicated above.  

It's a little confusing and this contributes to confusion.  This allows people to think that only users that write to tables need a license (imagine that).  I don't honestly think that this is the intention of the licence agreement from oracle, I'm sure that if people are on the phones supporting the sales order processing in JDE, then they'd need a license for their inquiries.

If you are lucky enough to have an enterprise agreement - then report your yearly revenue figures and it's "All you can eat".

Remember there is also the concept of "IOT" licences, which can be purchased in groups of 50 at 1/10th of the price of a normal user.  This is designed for the situation that a device might raise a WO for itself based upon temperature excesses or something similar.  Therefore oracle would need to differentiate these in the data - very difficult again.

The Fusion5 approach is different.  We use ACTUAL JDE usage to build a report of what is being used.  We look at all application and report usage in the system and can give you a complete view of what is being used.  While you have an ERPInsights subscription, then this data is available to you at any time.  You can report on any day, week, month or year.  You can also look at your geographic regions to see what license requirements they have.  All of this data is exact.  It uses the actual definitions in JDE to tell you what you are using.

You can see from the above, we've used 2.2 million page views and summarised this by system code and then licensed module.  This is the usage from 1 Jan to 29 April for a single customer.  This is a birds eye view of exactly what is being used at the organisation.   You could slice and dice this by day or location if needed.

Our software costs $1500 a month to use.  It can automatically send you detailed reports on module usage on schedule so that you can be confident that your licencing is in check.  This is a small investment to prevent some large unintended costs.

oracle licensing basics and AWS and oracle technology foundation

When you think you know nearly everything about licences, then you get stumped again...  Here is one that I've forgotten.  This is, in my opinion, a great reason to not use an oracle database on premise.  Oracle are not modernising their licence requirements for their database product on premise, and have seemingly leapfrogged virtual technologies and gone on to support Azure and AWS a little bit better.  (Though what about google and other public clouds - beware!)

Reading this oracle document oracle-software-licensing-basics and subsequently this one on partitioning partitioning-070609.pdf you cannot run oracle DB on any virtual server.  With some exceptions for hardware partitioning, nothing decent listed. Ouch.

At a point in time, I thought that this also means the cloud - which honestly cannot be correct.  The fact is that you can BYOL oracle licences to AWS RDS or have them included in the RDS instance and you are not paying for ALL of the cores of the MONSTER backend machine in AWS.  

Unless explicitly stated elsewhere in this document, soft partitioning (including features/functionality of any technologies listed as examples above) is not permitted as a means to determine or limit the number of software licenses required for any given server or cluster of servers.

And for future deployments, how about this : that host or Kubernetes node must be licensed for the Oracle Programs for the number of processors on that host or Kubernetes node. I

So there is a large discrepancy here...  Then I found what I was looking for: cloud-licensing-070579.pdf

There are exceptions for AWS and Azure - how about that or oracle love!

Amazon EC2 and RDS - count two vCPUs as equivalent to one Oracle Processor license if multi-threading of processor cores is enabled, and one vCPU as equivalent to one Oracle Processor license if multi-threading of processor cores is not enabled. 

Microsoft Azure – count two vCPUs as equivalent to one Oracle Processor license if multithreading of processor cores is enabled, and one vCPU as equivalent to one Oracle Processor license if multi-threading of processor cores is not enabled. 

This is important too:

Under this cloud computing policy, Oracle Database Standard Edition may only be licensed on Authorized Cloud Environment instances up to 16 Amazon vCPUs or 16 Azure vCPUs. Oracle Standard Edition One and Standard Edition 2 may only be licensed on Authorized Cloud Environment instances up to eight Amazon vCPUs or eight Azure vCPUs. If licensing Database Standard Edition 2 by Named User Plus metric, the minimums are 10 NUP licenses per 8 Amazon vCPUs or 8 Azure vCPUs

Therefore, oracle technology foundation oracle-technology-foundation-487257.pdf 

JD Edwards EnterpriseOne customers have long realized the benefits of implementing JD Edwards EnterpriseOne on the industry-leading Oracle Database. Oracle Technology Foundation provides JD Edwards EnterpriseOne customers with the features and functions equivalent to Oracle Database Standard Edition for use within their JD Edwards EnterpriseOne implementation.

So, I think that I have done the due diligence for you.  Oracle technology foundation gives you the ability to use oracle standard edition, the oracle cloud compute policy allows you to have up to 16 Amazon or Azure vCPUs on a host running the database.  Therefore it seems that you can BYOL for RDS for SE up to 16 cores and also stand up EC2 with 16 cores the same.  That is a BIG machine for running JDE.

e1local SQL access via reconfigureDB.exe

I have an interesting problem which requires elevated oracle permissions that I cannot get from my AWS RDS.  Quite specifically I want 

Honestly, why does it paste like that.

Anyways, I can work around it by running things on the local database on a thick client, but again - I need some hefty permissions.

So, goto e920\system\bin32 and run reconfigureDB.exe

You'll get this:

Now choose from enhanced encrtpyion to password and enter a secure password

Immediately you will be able to login locally (e1local) as sys with something like:

sqlplus sys/PassWord99 as sysdba

I run sqlplus from the bin location of e1local, therefore not really worried about tnsnames being configured incorrectly from a client.

All done, I can grant away now.  

as you can see I can also use SQLDeveloper (client app) with basic connection to e1local using the new username and password I created - and we can navigate this database.

Which is also handy if things are going wrong or you want a local database to test things.

AIS troubleshooting - you are not authorized [sic]

This is a pretty common problem that I get involved in a little bit.  Troubleshooting "You are not authorized to run this application." in AIS... What application / form / version?

Hey, JDE engineers - can you have some sort of logging option to show the application that cannot be run, please.  I understand that it might be a security risk [perhaps somehow], if you are showing the applications that people cannot run.  It is SO difficult to work out what people cannot run, especially when there are hidden forms and exits or logic controlled popups... 

Good enhancement idea? 

Back to how you can sort this out.  Remember, the situation is AIS if giving you the error about authorized [sic], all you need to do is enable logging for the user in AIS.  I'm sure you know how to do this, here is a screen shot in case:

In server manager under the AIS instance, goto logging and then hit the button for create new user specific log configuration.

Enter the JDE username, cAsE does not matter

Because I love logging, I choose this option and then apply

The thing is, your job is not done, you then need to synchronise the configuration:

You do that by going back out to the AIS server main page and choose synchronize configuration.  This is going to copy the jdelog.properties into the correct location on the AIS server so that you start logging!

You will then have an entry under the logging section of server manager for that user.  You can then work your magic with AIS to replicate the issue and then suss out some sample logs below:

Search your logs for "{"TITLE":"You are not authorized to run this application.","DESC":"INFORMATION"}

Then look up 6 lines for the section in aqua.  This is DEBUG and shows you the application that was attempted to be run!  Awesome, fix your security and go again!  Also nice to see that this is a R FormServiceAction - Read...  U is for update, see here https://docs.oracle.com/cd/E24705_01/doc.91/e56635/config_ais_calls.htm#EOTMD308

You can also see the data being passed into form and the control ID's being requested.  All very handy when dealing with column security too.

  [DEBUG ] BLOXSOMA - [AIS]             JAS INPUT PARAMETERS: FormServiceDemo=false&LoadBaseFormOnly=false&eventCount=0&OID=P17732_W17732D&FormServiceAction=R&Role=*ALL&ReturnControlIDs=1%5B28%2C31%2C33%2C145%5D&MaxPageSize=100&FormDSData=%7C135758%7C&RENDER_MAFLET=AIS&Environment=JPP920&FormDSTmpl=%7C1%7C&FindOnEntry=TRUE&OutputType= 

15 Feb 2022 10:26:21,055 [Line -1] [DEBUG ] BLOXSOMA - [AIS]             JAS URL: http://Server:9007/jde/FormServiceRequest 

15 Feb 2022 10:26:21,056 [Line -1] [DEBUG ] BLOXSOMA - [AIS]             Request Header Cookies JSESSIONID=T0D6xVrOGJgHpraAoZo8k-tK0mDy0kKOJYH-e4myi7_CEmXxWL1Z!1615883497; 

15 Feb 2022 10:26:21,126 [Line -1] [DEBUG ] BLOXSOMA - [AIS]             Response connection headers {null=[HTTP/1.1 503 Service Unavailable], Content-Length=[176], Date=[Tue, 15 Feb 2022 00:26:21 GMT], Content-Type=[application/json; charset=UTF-8]} 

15 Feb 2022 10:26:21,126 [Line -1] [DEBUG ] BLOXSOMA - [AIS]             NOT Gzip Encoding Read with InputStreamReader connection.getContentEncoding()null connection.getContentLength() 176 

15 Feb 2022 10:26:21,126 [Line -1] [DEBUG ] BLOXSOMA - [AIS]             E1ResUtils inputStreamToString END output {"errors":[{"TITLE":"You are not authorized to run this application.","DESC":"INFORMATION"},{"TITLE":"You are not authorized to run this application.","DESC":"INFORMATION"}]} 

Faster load testing in a modern JDE environment

We have a lot of clients going live at the moment.  This is with new databases, new clouds, new hardware, new tools releases.  Everyone gets a little nervous before the go-live, and I think rightfully so.  The projects are getting leaner and therefore the load testing needs to get leaner to match.

I'm going to cover off, what I think is the leanest and meanest JDE specific load testing that can be done:

• interactive historical comparison
• batch historical comparison
• synthetic load test

Interactive historical comparison

Interactive historical allows you to compare the page load times in your current production environment and compare those with your new environment.  You know that we use ERP Insights to provide us with the baseline and also the results from all of the activity that has been done as part of the UAT / testing.

Generally I look at information like the above, put that into excel and compare the load times over the environments.  Very quickly you'll see if you have problem applications, but also tell you overall how things are going.  This information is constantly updated, so you can react quickly after a go-live.

Data like page load time, server response time and page download time are all critical in forming a complete picture of exactly where the performance challenges might be.  You can see from above that all of these are measured - which can include location of browser!

I'd look at the top 20 applications that are slower and see if there are some themes.

Batch historical comparison

fusion5 have spent a lot of time writing detailed statistical analysis tools over the WSJ tables.   We have an agent that sits on premise and uploads your WSJ history to the cloud.  With this information, we can provide immediate feedback on batch performance too.  Once again, for the purposes of an upgrade - we tend to compare the average performance of all batch jobs in production against the new environments.  Ensure that you have run all of the jobs in the scheduler (or have an active scheduler) so that you get a quick view of average runtime of important jobs.

I like to look at those that are slower and faster, to ensure that the testing is solid (top 20 of each).

Very quickly you can see if yours jobs are selecting the correct data, running enough times and also how long they run on average.

Synthetic load test

This is really important to ensure that all of your configuration files, database connections, load balancers etc are configured correctly for production. It's important to look at your historical usage to know what load testing you should actually run.

Simple synthetic load testing will tell you any thresholds that you've not done correctly.  users per JVM and JDBj connections are a classic problem area!

Getting some speedy results from the above [and successful testing] will make you feel pretty confident walking into your next go live.

Summary

An engagement like this takes less than 1 week and can provide you with some really good insights into how JDE is performing and give you confidence going into your new environment.  you should get a solid baseline for JDE performance which you can continue to measure against moving forward.

If I had any recommendations for customers, tidy up your JDE authentication.  Use standards based (not dodgy) products to protect your ERP. Below is how you can use a JWT to log into JDE securely.  You just need a way of generating the JWT in the correct format - which is pretty easy.  You can see that implementing this method of auth will allow you to embrace a standards based approach to auth.  Something like this stands side by side with our myAccess product.

 Using oauth (JWT) to log into JDE

 

1.1      Introduction

Adopting authenticate standards is critical for system adoption and security compliance.  JD Edwards since 9.2.3.2 allows you to log into AIS or HTML using an access_token, which is a JWT.

 This document covers the end to end process (and some helpful websites) to configure JDE to accept JWT’s for interactive logins.

 

1.2      Steps

Commands are run from a linux command line:  

Note that these steps are done on the web server.  You do not necessarily need to do that.  When you are done, all you need is the single key.pfx file (pkcs12 format) which contains the private key for decrypting the contents of the JWT.  You would use the same file on all of your webservers.

 1.2.1      Key generation:

 Generate a private key

$ openssl genrsa -out private.pem 4096

Extract the public key from the private key

$ openssl rsa -in private.pem -out public.pem -pubout

If you try to insert private and public keys to PKCS12 format without a certificate you get an error - Server manager needs pkcs12

openssl pkcs12 -export -inkey private.pem -in public.pem -out keys.p12

unable to load certificates

Generate self-signed certificate with aforesaid key for 10 years. This certificate isn't used for anything as the counterpart is JWK with just public key, no certificate.  This is, open SSL needs the cert (fancy public key)

$ openssl req -key private.pem -new -x509 -days 3650 -subj "/C=AU/ST=Victoria/O=Fusion5/OU=CNC Gurus/CN=fusion5.com.au" -out cert.pem

Convert the above private key and certificate to PKCS12 format - which is what we need for SM

$ chmod 0400 private.pem

$ openssl pkcs12 -export -inkey private.pem -in cert.pem -out keys.pfx -name "fusion5oauth"

Check the keystore:

$ keytool -list -keystore keys.pfx

 

From <https://ruleoftech.com/2020/generating-jwt-and-jwk-for-information-exchange-between-services>

1.2.2      Test key access

 

Ensure jde user can read the cert

sudo su - jde920

Cat /tmp/shannon/keys.pfx

 

/get a pile of binary rubbish/

 

1.3      Configure SM for the instance you want to login with

Goto a webserver (initially) need to see advanced settings and security section.

Generate a JWT: Need to use RS256, as I found out after I had generated a different JWT… Doh!  There are other options.

 

12 Jan 2022 17:10:18,550[WARN][JAVATOKEN]OAuthJWTAuthenticationManager: getJWTPayload() - JWT token validation failed com.nimbusds.jose.JOSEException: Unsupported JWS algorithm HS256, must be RS256, RS384, RS512, PS256,

 

 

1.3.1      Generate a test JWT

 

This is an awesome site for creating a JWT for testing: 

https://jwt.io/#debugger

 

This will load up an example with the correct HEADER.BODY.CERT JWT 

 https://jwt.io/#debugger-io?token=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJqdGkiOiIxMjM0NTY3ODkwIiwicHJuIjoiU00wMDAwMSIsImFkbWluIjp0cnVlLCJpYXQiOjE2NMTg5MDEsImV4cCI6MTY0MjExODkwMSwiaXNzIjoic2hhbm5vbiIsImF0X3VzZV9uYnIiOjF9.dBhGniUgkDpmuISE05GmsWrmvj0Ip33Ssx6Ud600FsWnWx9ir3mPG_JnVGzTMh4F9SevQ4des35DRryUm21ONzKWPY9KxufK6uVX1UQ1OVLeSIPhHaXZbru7rkxNUvpxm4Q72jhX6UN0_DvjqbQAh7R80jO3WQwBYpAuIXAksR9rHtucDK8Kfb0tFgOP16FK4VpnnqhGx4FxpUFDhUS3LH_0ApbF6xflcevHMys7VMjZX9lpOjqjUH6-nVHfd2wtUpZopz0kY0yFBcnZ_Py150PUrmgZuZyg-ff3rB4-g9sxs7FvULH11DKBZtj8aj3bGrlak9GWc7L7bkTNmC22wP9_KZNGS2FquNG3IZA3qcS7spd80Ix6TEnOYRZgWT2mqdGbj_mQXwlVSaA_lkobXMByO8vaCiGTXB2NSwSDU3VuEXT2vun4FkQSw-Qa5sPnhFpCQUk6CQ8JI0-rFqSQSeB8X_ORn6IX7G-YglN2sbL0_z4zQSV-CENILsngeEP53nTT8Z_m8Rw80Eg5_dNLFSLj1nTCfePKox7ZNhaWWChZkyiiPQty4IfZc-3QgJo6l7AAHMkONAMdo1vH9iwfkkw7P8ztT7ZLkaRvO5Ut9Q4EnI4SwtnJd9Ksq0PM9A08ZOoLolPKfw-QYUEfTaMKk_n6MxQEyRDz06yKkTi4o&publicKey=-----BEGIN%20PUBLIC%20KEY-----%0AMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAqsbe4zhXWdUSppclxsfJ%0AygSoXhAOgEi4OeQAYbuz1JzS2KoBp0QO6CLNKn0nEh8JWPwUkwz4cbtazQnBWjL8%0AmXunzecgyyhWh%2F0nYeLAhrEkVuLcCpAqGagEcHSn0myJsfRAN4D394SQ09gSCAvn%0ACsg1E0ZHSGVRpguAAU8z%2Brkxc%2BjczGide%2Bp1HfoT8EcZxtKZ5qzBnPi1OTPuld9Q%0AJZPm7sMpo03D4BHFuXSb8FJaiOJMdVK%2F8eMUuqbghuK7%2yHvfK6YPF%2Baw9%0A85%2B5aeKmNElriJoCu4uPSFF4XeeujrJOkbYCJ3S8ZBKb8xGO1G64F4Po%2Bf%2F%2Frr6s%0Ab4w0T%2BCbAUwek7UUJ2pI%2BfOtRbD6uwJxDQ%2FZVaAaa4njbJouMLAYD3idm9lo7K%2B0%0AK%2FShM6P9be1RUwoyIB3eVtNiC5Al5%2Bd0FaQaMRyKH%2F%2Bgj9lHqodWEbsm%2BucelZjt%0ADVSbtDat5QpZ2mAJVyI%2BfyDuUGwAXxfi12ZsCpuX0VH5V6O0EpgDxTSTeUQfb1RR%0Aqx1wIuFSxhgQVf6oJ1DcgjyEPtvA9RM7usfez5K1bvUzlhBqeMKVKvNMaNv9eP99%0AVfHXOxH65nj8iKdA%2BT5DFSvEqa2wbXROI9gpXiO9X4AlGx3I9FWSFK2XCEqmAxwx%0A0kkAFjCIolEabw1GBqtOeU0CAwEAAQ%3D%3D%0A-----END%20PUBLIC%20KEY-----

 

Note that the private key is not copied…

 

A couple of things to note:

 

Firstly, remember to paste the entire contents of the private and public keys, with header.

 

{

  "jti": "1234567890",  something unique

  "prn": "SM00001",  your jde username

  "admin": true,

  "iat": 1642118901,   You might need this - https://www.epochconverter.com/

  "exp": 1642118901,

  "iss": "shannon", 

  "at_use_nbr": 1  single use

}

 

Note that you need the private key to generate the JWT.  The private key encrypts the contents so that they cannot be tampered with in transit.

 

This is all about trust.  We are trusting the person with the private key is being VERY good, a good citizen.  Because if you get a hold of the certificate - then you can generate tokens and login as ANYONE!!!  The private key is to be treated like a password.

 

1.3.2      Now, login to JDE:

 

https://f5play.fusion5.cloud/jde/E1Menu.maf?access_token=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJqdGkiOiIxMjM0NTY1Nzg5MCIsInBybiI6IlNNMDAwMDEiLCJhZG1pbiI6dHJ1ZSwiaWF0IjoxNjQyMTE4OTAxLCJleHAiOjE2NDIxMTg5MDEsImlzcyI6InNoYW5ub24iLCJhdFfbmJyIjoxfQ.JjBO6K7-WLCHefpOT3Kw2t2SZTspwcEw7eF334mhk0AIDi3Sw53FPWFXsrGvYengKFnzS9wm8cA1JsWjgsqKFv_OKlqx3hh1613-_1MpLryjaOWVlQpo5X7ZGJ1ae5N-mjPZwqHjCAz2JE1yZWnLUn-BTHa3-DkcANp0DyWe1T1hbFjwgUY_BIKZdOTBCUwNSRUFhir0bGl2ifUj55vQkAQMFskkrGXe371UenfsPyzZfabKNF4_WhTXa2hHeebsmGebmaGW_i-YVR6v0LZYIG8VcksZ9uQj1F55gyRIDS49jtzwzFdxY_gjjLMlTjl3o7zEbMAyv80rHh1Sjv2UJDXkhdXmDolpJvKcTbG3AEeLzMVE4ua_69fNoToa4t_5MktdRw4XqQwzd46JiBPPsGUCg_AaOi4nWmclx8B1HqvApyIQm3TdE7_wJqRt2zLqYRxySbNi0ewFk3vqxa3j3JlhqJiHQxQ7YJRXW4BaUNRMT8FBlp8yKgrQSE0u_uJ7MThq6PeIYqW7gLeNj78Hdu-I1dAnESGQ0KsAB_AckVQLwAEiUDcaZqkZIFBTTAp2smqKFeQ1oG579bcKEnzsNstqzlzpPw46T_VE21oN3VQYcnc8rfgVwk7KDbtd-SflX86fOrt2h4tAs9gFRI3rjjCM0gyjzqrNvzc

 

Okay!

 

This is good reference:

Every Private Key has a corresponding Public Key. The public key is mathematically derived from the private key. These two keys, together called a "key pair", can be used for two purposes: Encryption and Signing. For the purposes of certificates, signing is far more relevant.

A certificate is basically just a public key, which has been signed by someone else's private key. This forms the basis of public key infrastructure (PKI), which is explained in the articles linked in the question.

How do Certificates and Private Keys relate? https://security.stackexchange.com/questions/226747/what-is-the-difference-between-a-certificate-and-a-private-key 

A certificate is just a "fancy" public key, which is related to a private key. You can do the same thing with a certificate as you can do with a public key.

If Bob gets the certificate of Alice, he can encrypt a message for Alice. Likewise, if Alice publishes some data and signs it with her private key, Bob can use Alice's certificate to see if it is really from Alice.

What are all those different file types?

• .pem: A .pem is a de-facto file format called Privacy-Enhanced Mail. A PEM file can contain a lot of different things, such as certificates, private keys, public keys and lots of other things. A file being in PEM format says nothing about the content, just like something being Base64-encoded says nothing about the content.
• .crt, .cer: This is another pseudo-format that is commonly used to store certificates. These can either be in the PEM or in the DER format.
• .p12, .pfx: These are interchangable file extensions for the PKCS#12 format. Technically, PKCS#12 is the successor to Microsoft's PFX format, but they have become interchangable. PKCS#12 files are archives for cryptographic material. Again, what kind of material this contains is completely up to the user.

Wait, what!?

Yes, .crt, .pem, .pfx and .p12 can all be used to store certificates, public keys and private keys. From a purely technical standpoint, you can not tell what the semantic content of any of these files is just by their file extension. If you ever get confused, don't worry - you're not alone.

However, there are some common conventions that are being followed. .p12 and .pfx files are usually used to store a certificate together with the private key that corresponds to this certificate.

Likewise, .crt files usually contain single certificates without any related private key material.

.pem files are wildcards. They can contain anything, and it's not uncommon to see them used for all different kinds of purposes. Luckily, they are all plain text, and are prefixed in a human-readable way, such as

-----BEGIN CERTIFICATE-----
MIICLDCCAdKgAwIBAgIBADAKBggqhkjOPQQDAjB9MQswCQYDVQQGEwJCRTEPMA0G
A1UEChMGR251VExTMSUwIwYDVQQLExxHbnVUTFMgY2VydGlmaWNhdGUgYXV0aG9y
...

Why would an application not handle a .crt file if it wants a client certificate?

A certificate is just a public key, and thus by definition public. A client certificate is no different - just a public key by a person, machine or other "client", that is signed by some authority.

An application that wants a client certificate usually wants to use that certificate for something, such as to authenticate the client to a server. In order to do that, one needs the certificate and the corresponding private key.

So an application should really write "certificate plus private key", because the certificate alone is not enough to prove one's identity. It's actually the private key that does it.

 

To answer vitm's question: As the answer explains, a private key is always associated with a public key, and a certificate contains a public key, as well as other information regarding the individual holding the public key.

If a server program or client program want to use a certificate (e.g. a web server using a server certificate or a web browser using a client certificate), they need both the certificate and the private key.

However, that private key is never sent anywhere. The private key is used mathematically to decrypt messages, which are encrypted with the public key in the certificate - and to sign messages, which are verified using the public key in the certificate.

If I only had a certificate, without a public key, then I would not be able to act as the server or client, to whom the certificate relates to, as I could not sign messages or decrypt messages.