Database links for beginners

It cannot be easier.  If you want to run SQL between database instances, why not use a database link.  Yeah, the security does seem a little loose, but the implementation is awesome.  Remember that you cannot run DML via the link, only SQL.  You can do updates, inserts and deletes – but no creates etc.

Say I have E1prod and E1dev databases.

Best thing is to create a link from e1prod to e1dev, as this is the more secure route.

login to e1prod with DBA permissions

create public database link "jde_e1dev.oracle.com"
connect to JDE
identified by password
using 'e1dev';

Then from e1prod you can do:

select count(1) from testdta.F0101@jde_e1dev ;

Note that the user name prefixes the dblink name, this is cool if you user other users.  Also all of your queries in e1dev over the link are going to use the JDE username.

You can check details of the link in select * from all_db_links;

practical advice for large index generation in JDE

E1 or JDE is not that smart when it comes to generating indexes…  In fact, it’s pretty bad.

Developers think that it’s easy, until they realise that the process is going to create all indexes on the F0911 without any parallelism and synchronously.  When you have 21 million records, you might be waiting 48 hours.  And you might be causing some pretty bad locking too.

So how do you get around this conundrum, developers are not too bright and that big “generate indexes” button is pretty damn attractive!!!

Firstly, secure all standard passwords – PRODDTA etc etc.  Developers are also well know for pressing “generate table” – classic.  Oh, how I love flashback for that one!

Secondly don’t let developers generate indexes, sure they can create them…  not generate.  Make it policy.  Secure that data source selection screen (W9866J)!

If you need to generate the indexes, do the following:

• turn client logging on
• Generate the table to an alternate data source
• use findstr to extract the CREATE statements
• find the index you want to generate
• Change owner and tablespace information
• paste query into SQL Plus

Now that is a set of steps that even a developer could understand!

I’m also a fan of having database only indexes, JDE does not need to know about them if you want them for performance only.  The only reason developers love to put them in is because of the poor API’s that are exposed to them in the FDA and RDA for fetch and fetch all… (or one of them)

Connections, listeners and listener notifications

Connections are responsible for authenticating to JDE, to getting the structures of the DSTRs for the events that are coming in and also record information about the transaction server, although when they are started they do not connect to the transaction server. 

Connections have names like “E1_GuaranteedEvents”. 

The guaranteedEvents engine does the following when started:

loads jas.ini

load localBSFNCatalog

loads tnsnames.ora

logs user in

sends  

<?xml version="1.0" encoding="UTF-16"?>
<event-list-request><credentials><username>JDE</username><password>******</password><environment>JPY812</environment><security-token>******</security-token></credentials><environment>JPY812</environment></event-list-request>

receives

<?xml version="1.0" encoding="UTF-16"?><event-list-response><event-type-definition><category>RTE</category><type>NEWSCUST2</type><environment>JPY812</environment><active>true</active><subscribed-to>true</subscribed-to></event-type-definition></event-list-response>

<?xml version="1.0" encoding="UTF-16"?>
<get-subscriptions-request><credentials><username>JDE</username><password>******</password><environment>JPY812</environment><security-token>******</security-token></credentials></get-subscriptions-request>

<?xml version="1.0" encoding="UTF-16"?>
<receive-request><credentials><username>JDE</username><password>******</password><environment>JPY812</environment><security-token>******</security-token></credentials><sessionId>4503880130953618278796531134073486669</sessionId><timeout>2000</timeout></receive-request>

<?xml version="1.0" encoding="UTF-16"?><receive-response><event><event-id>nopmctdev01x_172103107_6014_4300832_0205201011484217</event-id><category>RTE</category><type>NEWSCUST2</type><environment>JPY812</environment><sequence-number>11004339</sequence-number><date-time><milliseconds-since-epoc>1265376404000</milliseconds-since-epoc></date-time><xml-payload>&lt;?xml version = &apos;1.0&apos; encoding = &apos;UTF-8&apos;?&gt;&lt;jdeResponse category=&quot;RTE&quot; environment=&quot;JPY812&quot; pwd=&quot;&quot; responseCreator=&quot;XAPI&quot; role=&quot;*ALL&quot; session=&quot;3007d9d8&quot; token=&quot;&quot; type=&quot;realTimeEvent&quot; user=&quot;PETERSJO&quot;&gt;&lt;event&gt;&lt;header&gt;&lt;eventVersion&gt;1.0&lt;/eventVersion&gt;&lt;type&gt;NEWSCUST2&lt;/type&gt;&lt;user&gt;PETERSJO&lt;/user&gt;&lt;role&gt;*ALL&lt;/role&gt;&lt;application&gt;R55001CM&lt;/application&gt;&lt;version&gt;AALL001&lt;/version&gt;&lt;sessionID&gt;3007d9d8&lt;/sessionID&gt;&lt;environment&gt;JPY812&lt;/environment&gt;&lt;host&gt;nopmctdev01x&lt;/host&gt;&lt;sequenceID&gt;11004339&lt;/sequenceID&gt;&lt;date&gt;02052010&lt;/date&gt;&lt;time&gt;132644&lt;/time&gt;&lt;scope/&gt;&lt;codepage&gt;1252&lt;/codepage&gt;&lt;instanceInfo&gt;&lt;host&gt;nopmctdev01x&lt;/host&gt;&lt;port&gt;6014&lt;/port&gt;&lt;type&gt;JDENET&lt;/type&gt;&lt;/instanceInfo&gt;&lt;/header&gt;&lt;body elementCount=&quot;1&quot;&gt;&lt;detail DSTMPL=&quot;D5503B001B&quot; date=&quot;02052010&quot; executionOrder=&quot;0&quot; name=&quot;NewsCustomerRealTimeWrapperV2&quot; parameterCount=&quot;49&quot; time=&quot;13:26:44&quot; type=&quot;NEWSCUST2&quot;&gt;&lt;szCreditQueueInd type=&quot;String&quot;&gt;03 &lt;/szCreditQueueInd&gt;&lt;szState type=&quot;String&quot;&gt;NSW&lt;/szState&gt;&lt;szAddressLine4 type=&quot;String&quot;&gt;Ultimo                                  &lt;/szAddressLine4&gt;&lt;szAddressLine3 type=&quot;String&quot;&gt;t                       &lt;/szAddressLine3&gt;&lt;szAddressLine2 type=&quot;String&quot;&gt;                    &lt;/szAddressLine2&gt;&lt;szAddressLine1 type=&quot;String&quot;&gt;&lt;/szAddressLine1&gt;&lt;szZipCodePostal type=&quot;String&quot;&gt;2007        &lt;/szZipCodePostal&gt;&lt;szDisputeCode type=&quot;String&quot;&gt;03        &lt;/szDisputeCode&gt;&lt;cClientCommInd type=&quot;Character&quot;&gt; &lt;/cClientCommInd&gt;&lt;szBusinessUnit type=&quot;String&quot;&gt;253&lt;/szBusinessUnit&gt;&lt;szContactFaxNumber type=&quot;String&quot;/&gt;&lt;mnARInternalAccountID type=&quot;Double&quot;&gt;1040888&lt;/mnARInternalAccountID&gt;&lt;jdLastInvoiceDate type=&quot;Date&quot;/&gt;&lt;szCity type=&quot;String&quot;&gt;Sydney                   &lt;/szCity&gt;&lt;szRepCode type=&quot;String&quot;&gt; &lt;/szRepCode&gt;&lt;jdLastPaymentDate type=&quot;Date&quot;&gt;2006/07/10&lt;/jdLastPaymentDate&gt;&lt;mnTotalOutstanding type=&quot;Double&quot;&gt;0&lt;/mnTotalOutstanding&gt;&lt;szDebtorType type=&quot;String&quot;&gt;8&lt;/szDebtorType&gt;&lt;szBusinessIdentifier type=&quot;String&quot;&gt;                    &lt;/szBusinessIdentifier&gt;&lt;szContactName type=&quot;String&quot;&gt;&lt;/szContactName&gt;&lt;mnCurrentBalance type=&quot;Double&quot;&gt;0&lt;/mnCurrentBalance&gt;&lt;szCustomerAccountNumber type=&quot;String&quot;&gt;A0            &lt;/szCustomerAccountNumber&gt;&lt;cClientStopInd type=&quot;Character&quot;&gt; &lt;/cClientStopInd&gt;&lt;mnChargesThisPeriod type=&quot;Double&quot;&gt;0&lt;/mnChargesThisPeriod&gt;&lt;szCreditStatus type=&quot;String&quot;&gt;Refer to Credit               &lt;/szCreditStatus&gt;&lt;mnAmtAgingCategories7 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories7&gt;&lt;mnAmtAgingCategories6 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories6&gt;&lt;mnAmtAgingCategories5 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories5&gt;&lt;mnAmtAgingCategories4 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories4&gt;&lt;mnLastPaymentAmount type=&quot;Double&quot;&gt;32812.38&lt;/mnLastPaymentAmount&gt;&lt;mnCurrentYTDSpend type=&quot;Double&quot;&gt;0.00&lt;/mnCurrentYTDSpend&gt;&lt;mnAmtAgingCategories3 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories3&gt;&lt;mnAmtAgingCategories2 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories2&gt;&lt;mnAmtAgingCategories1 type=&quot;Double&quot;&gt;0&lt;/mnAmtAgingCategories1&gt;&lt;cGSTExemptFlag type=&quot;Character&quot;&gt;N&lt;/cGSTExemptFlag&gt;&lt;szCreditMessage type=&quot;String&quot;&gt;AD&lt;/szCreditMessage&gt;&lt;szAsccountSequenceKey type=&quot;String&quot;&gt&lt;/szAsccountSequenceKey&gt;&lt;szCustomerAccountName type=&quot;String&quot;&gt             &lt;/szCustomerAccountName&gt;&lt;mnTotalOverdue type=&quot;Double&quot;&gt;0&lt;/mnTotalOverdue&gt;&lt;szCreditTerm type=&quot;String&quot;&gt;030&lt;/szCreditTerm&gt;&lt;szSiteID type=&quot;String&quot;&gt;NA&lt;/szSiteID&gt;&lt;mnCreditLimit type=&quot;Double&quot;&gt;&lt;/mnCreditLimit&gt;&lt;szClientAccountNumber type=&quot;String&quot;&gt; &lt;/szClientAccountNumber&gt;&lt;szClientName type=&quot;String&quot;&gt; &lt;/szClientName&gt;&lt;szClientSequenceKey type=&quot;String&quot;&gt; &lt;/szClientSequenceKey&gt;&lt;jdDateChanged type=&quot;Date&quot;&gt;2009/10/09&lt;/jdDateChanged&gt;&lt;szSearchType type=&quot;String&quot;&gt;C  &lt;/szSearchType&gt;&lt;szLedgerType type=&quot;String&quot;&gt;T&lt;/szLedgerType&gt;&lt;szContactPhoneNumber type=&quot;String&quot;&gt;0292811777&lt;/szContactPhoneNumber&gt;&lt;/detail&gt;&lt;/body&gt;&lt;/event&gt;&lt;/jdeResponse&gt;</xml-payload></event></receive-response>

Event Acknowledged

<?xml version="1.0" encoding="UTF-16"?>
<acknowledge-request><credentials><username>JDE</username><password>******</password><environment>JPY812</environment><security-token>******</security-token></credentials><session-id>4503880130953618278796531134073486669</session-id></acknowledge-request>

Above  is the actual Event that has been received.  So the guaranteed event connection is going to JDE and via XML requests to the appropriate jdenet kernel, it’s getting all of the data.

Note that the connection sends a confirmation which will trigger the removal of the event from the F90710. 

This is then passed to the listener and then the listener notifications.  The listener is the one that listens for the events and passes them to the transaction server.

When they are started, they write ini files in the equivalent dir to “D:\EnterpriseOne\wsg\IntegrationServer\ini_files\E1_GuaranteedEvents_DV812”.  Note that changing these files makes no difference to runtime, you gotta change the config in the administration control panel.

A JDE listener has a connection to listen to, ie. like the “E1_GuarantedEvents” above.  It also has an environment associated with it.

Then you have listener notifications, which you need to create in the webMethods development tool.

Business Object Reservations P00095

Ever had your web server crash or app server crash and then you have lots of users ring up and say “I cannot get into batch XXX coz it’s locked by another user”?  …  No…  Well, don’t bother reading anymore.

If you do, you can look at P00095 (in fast path) to see the business object reservations.

8.97 WSG Web Services Gateway observations

My first real deep dive into WSG was with 8.98 – where a transaction server is used.  The was on WebSphere too, so all of the MQ queues were also being used to store the messages and assist in the reliability of everything. The TXN server chatted to the enterprise server and used MQQueues to store messages and await the integration server to come and get them.  Once the messages were in a queue, the F90710 message was deleted.  Cool, so reliability ends when the message is put into a Queue.  Nice.

The next step is for anything to come and pop that message out of the txn servers queues.  This is where you have an integration server and the JDE adapters.  The guaranteed events adapter has the ability to read the metadata for the XML doc that is in the MQSeries Queue.  So you just tell that piece where to get some JDE information (so that it can determine DSTR’s for messages etc) and also where to find a txn server.  Then the integration server gets the messages from the txn server, and all is good.

A picture is worth 1000 words, so I think that the above basically covers things off.

This is totally different in 8.97 (so it seems).

I’m new to the 8.97 WSG implementation, but it seems that there is no txn server.  All messages flow directly to and from the adapters, without the reliable delivery / queueing mechanism that the transaction server caters for. 

We have a client that is using this for a big web based front end, this calls logic via WSG.  The adapter in WSG calls the JDE business functions which return data eventually back to the website.  This is nice, but this is SINGLE THREADED.  Therefore there might be 20 users on the external website, but when it comes to calling some JDE logic they all connect to a WSG instance which runs all requests in a single thread, which has one session to one call object kernel which is also single threaded.

So if you’re going to build an enterprise solution, don’t use this technology.  I believe things are getting better.  I remember the good old days when a single COM interface would distribute across all your logic kernels and flog the mutithreaded crap out of E1.  Take me back to the good old days.  I’m not an advocate of flat file transfers, but do like scalable solutions.

The simple things in life…

So I want to look at the last 400000 lines of a debug file…  it’s a unix site, so it’s easy.

tail –n400000 filename.log > shannon.log

shannon.log now contains the last 400000 lines of the filename.log file.

Now I want the next 400000 lines, easy again.

tail –n800000 filename.log |head –n400000 > shannon.log

shannon.log now has the penultimate 400000 line chunck of the file.

Nice, and I can keep chopping it up like that!

P98770 - simple

Shows you active deployed packages and the updates that have been applied to them.