Using multiple columns in where clause and set based exception

Jeepers, I have no idea how to define this, but I’m working out records that are in one table and not in another based upon the primary key.  I then want to use this list to feed a insert statement to fix the missing records.

I cannot use the entire tuple, as there is date/time stamps that will say they are all different.

The answer is simple, but I just needed the syntax help.

The “real” use case scenario is synchronising F9862 between two different systems.  We are copying a pathcode and need to make sure all of the OL records are going to exist.  This is easy for F9861 – as it’s pathcode specific.  It’s a little harder when dealing with F9862, F9863 and F9865 – as they are not pathcode specific.

--find the missing records
select * from JDEDBA.F9862 t3
where (siobnm, sifctnm, simd) in (
select SIOBNM,SIFCTNM, SIMD from JDEDBA.F9862 
minus 
select SIOBNM,SIFCTNM, SIMD from OL910.F9862);

--insert them into F9862 - OL one
insert into ol910.f9862
select * from JDEDBA.F9862 t3
where (siobnm, sifctnm, simd) in (
select SIOBNM,SIFCTNM, SIMD from JDEDBA.F9862 
minus 
select SIOBNM,SIFCTNM, SIMD from OL910.F9862);

looking at your JMS messages in WLS JD Edwards RTE version

1.    Install SOAPUI and HermesJMS. Open HermesJMS through SOAPUI -> Tools -> HermesJMS.
2.    In the menu click Options -> Configuration -> Providers. Right click -> Add Classpath Group called weblogic and add weblogic.jar. It will look like this: 
3.    Click Action -> new -> New Session. Change the loader to Weblogic then change the rest of the settings to look like this:

The important changes are the plug in, the class, the session name and the properties for the connection factory. You do not have to define the destinations. The properties defined are:
• binding: is the JNDI name (which in this case is jms/com/peoplesoft/pt/e1/server/enterprise/events/QueueConnectionFactory)
• initialContextFactory: weblogic.jndi.WLInitialContextFactory
• providerURL: t3://<server name>:<server port>
• securityCredentials: weblogic password
• securityPrincipal: weblogic username

4. Once the session is defined right click the session just defined and click Discover. Wait a bit.
5. All your queues!

Also, remember that the actual queue that has the delivered messages is defined in your subscriber definition in JD Edwards.  Used P90701A from a fat client to view the subscribers.  Make sure that the subscriber is defined properly for the event and environment that you want to send to the RTE server.  This is “AS WELL” and activating the event for the environment.

I must shout out to Frank for providing me with this, he’s an awesome java dude.

Oracle application testing suite (OATS) and load balancer

Quite often with using OATS, you don't want to fight the load balancer too much.  Wait, am I starting too soon.

Generally if clients are using a load balancer they are big enough for load testing their ERP before upgrade or before install.  So...  this is where we are at.

My current assignment has me comparing the 812 installation with the 910 installation - this is not as easy as you may think.  There are a lot of variables that need to be considered - especially the read only nature of the existing production environment.

Anyway, back to the post title - load balancers can play hell with load testing, in that they decide where the ERP traffic should go and sometimes (if the URL) is not proxied - then the LB won't like it.  So lets solve this problem.

As you can see from the above, I have a special situation where I need to redirect both the authentication and the ERP URLS.  This is quite unique as we are using a SSO product that allows long usernames and passwords into JDE - nice.

But, the lesson is that we need to rip the ERPURL out of the header and then assign that to the already parameterised URL.

I set my variable "ERPThisWay" to the header information indicated by the string below.  I then display this in the script and finally set my parameterised URL to that value.

http.solve("ERPThisWay", "Location: (.+?)/jde/E1Menu\\.maf",
"", false, Source.ResponseHeader, 0, EncodeOptions.None);
info("{{ERPThisWay}}  This is where we have an auth token");
getVariables().set("szERPURL", "{{ERPThisWay}}",
Variables.Scope.GLOBAL);

so now, my load balancer is working it's chunk off and I'm not forcing a artificial LB using CSV files and databanks.

oats agent install–only install the load testing agent

What does the load testing agent do?  Allows you to simulate load from geographic locations.  Therefore I want to simulate load from Perth, Sydney and Broome and need to install the agent on some remote machines.  Forget dragging the entire install (as there is no “cutdown” available to Broome_, I could take it there by camel quicker.

So, I’m going to lean it out.

• You can remove the WLS and OXE dirs from the expanded zip file which will save you 730 Mb over the network. 
• Then you can remove the OSE, Server, WLS and XE dirs from the stage/Components dir. saving about 500.
• Therefore the agent install can be copies in 581MB uncompressed or 519 compressed.

This is much better than 1.8GB over the network.  I also find this faster than running the installer remotely.

Ever wanted to compare two environments? Row counts…

I have a client with a funny setup, that is they have two SEPARATE systems.  This includes SY, OL, DD – everything that you would expect.  UA910 runs in one and PD910 in the other.  They wanted some sort of reconciliation between the environments to ensure that PD = UA – reasonable in the circumstances.  We ere finding little differences, version here, object there – but then it became harder with things like text translations and items in the system tables.  So I decided to get out the SQL big brush and so some comparisons.  This is based upon some of the scripts that I’ve previously posted on reconciliation of data between upgrades.  This is oracle syntax.

I create a central table.  I use a database link for the jde_ujde stuff – you’ll see that in the code.  I do some mapping of owners too, UADTA to PRODDTA and UACTL to PRODCTL.  Finally I do UA910 to PD910.

You’ll see that I forgot central objects in the first insert and had to catch it up at the end.

Please also note that often I’m generating SQL that you’ll need to run, so paste the results into the run window.

CREATE TABLE "SY910"."DATACOUNTS"
   (    "TABLE_NAME" NVARCHAR2(20),
        "TABLE_OWNER" NVARCHAR2(20),
        "ORACLE_COUNT_DJDE" NUMBER,
        "ORACLE_COUNT_PJDE" NUMBER,
        "TABLE_DESCRIPTION" NVARCHAR2(256)
   )
      TABLESPACE "SY910T" ;

grant all on SY910.DATACOUNTS to public ;

insert into SY910.datacounts select table_name,owner,0,0,simd 
from all_tables, ol910.f9860 
where owner in ('OL910', 'DD910', 'SY910', 'PRODDTA', 'PRODCTL')
and ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name));

select 'update SY910.dataCounts set ORACLE_COUNT_PJDE = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('OL910', 'DD910', 'SY910', 'PRODDTA', 'PRODCTL')
 order by table_name desc ;
 
select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || table_owner || '.' || table_name || '@jde_djde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('OL910', 'DD910', 'SY910')
 order by table_name desc ;

select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UADTA' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PRODDTA' || ''';' from sy910.datacounts 
 where table_owner in ('PRODDTA')
 order by table_name desc ;

select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UACTL' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PRODCTL' || ''';' from sy910.datacounts 
 where table_owner in ('PRODCTL')
 order by table_name desc ;

insert into SY910.datacounts select table_name,owner,0,0,simd 
from all_tables, ol910.f9860 
where owner in ('PD910')
and ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name));

select 'update SY910.dataCounts set ORACLE_COUNT_PJDE = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('PD910')
 order by table_name desc ;
 
  select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UA910' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PD910' || ''';' from sy910.datacounts 
 where table_owner in ('PD910')
 order by table_name desc ;
 
 commit ;
 
 select * from sy910.datacounts ;

New JDBC test harness for testing oracle connections and SQL

This is my modified connect harness for oracle behind a firewall.  Note that there is a change of the connect string, as I was getting a shed load of ORA-12505 errors not matter how I was formatting the original – so I went hard core.

import java.io.*;
import java.util.*;
import java.sql.*;
    
public class dbconnect
{
    public static void main(String[] args) {
    try { 
    Properties props = new Properties(); 
    props.load(new FileInputStream("dbconnect.properties")); 
    String DRIVER = "oracle.jdbc.driver.OracleDriver"; 
    String URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + props.getProperty("local_system").trim() + ")(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=" + props.getProperty("serviceName").trim() + ")))"; 
    System.out.println("Connect String..." + URL); 
    //jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SCAN-LISTENER-NAME)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=Service-name)))
    //Connect to iSeries 
    Class.forName(DRIVER); 
    Connection conn = DriverManager.getConnection(URL, props.getProperty("userId").trim(), props.getProperty("password").trim()); 
    System.out.println("Creating statement..." + URL);
        Statement stmt = conn.createStatement();
    String sql = "SELECT COUNT(1) as RecCount FROM " + props.getProperty("owner").trim() + ".F0101";
        ResultSet rs = stmt.executeQuery(sql);
        //STEP 5: Extract data from result set
        int RecordCount=0;
    while(rs.next())
        {
          //Retrieve by column name
          RecordCount  = rs.getInt("RecCount");
    }
         System.out.print("\nF0101 count: " + RecordCount + "\n");

       rs.close();
       conn.close(); 
} 
catch (Exception e) { 
System.out.println(e); 
}     
System.out.println("Program worked if there are no errors above!"); // Display the string.
}
}

Love the new interface! Tools 9.1.5.2

I might get a TShirt made with some sort of social commentary about 9.1.5.2 Rocks.

Wow, that gag took a long time for me…

Remember that myriad has a public facing demo environment for 9.1.5.2.  I think the new interface is super crisp and cool.  What do you think?

This is a really cool screen which you need to remember as a CNC.  P98770  It is cool for a number of reasons.  But, one – is when you are like me and your brain is reaching capacity – it’s hard to remember what package is deployed to what machine.  Especially because the lookup for package build and deploy does not really highlight what is the active package when you are selecting a parent package (that is really silly I think).  As generally you want to build against the currently deployed full, or you are going to get a problem when you go to deploy.

Wait, this was supposed to be a quick post and it’s turned into a rant.  So this screen can quickly tell you what is deployed to what server.  Remember that when a JAS server is working out what package to start to use for auto=generation, it looks at the default logic location for the environment that is being accessed and then looks at the package that is deployed to that logic source, then generates from there.

Another enhancement update

Wow, you can tell that I went on leave for too long, I’m only starting to understand the massive changes that have been completed with the new package build and deploy processes.  They seem to be good (although we are having some issues with updating PS910 on the server).

My good friend Shae pointed me in the direction of this document.  This was a great find to explain some of the changes to the package build process.

I’ve added my comments in RED.

Changes to the Enterprise Server Package Build Process

Numerous changes have been made to the Enterprise Server for both full packages and update package.

Full Package Build Changes

The following changes have been made to the enterprise server for full packages:

• R9621S is a new UBE that runs first and initiates the server package build.
• The system copies all of the business functions’ .c and .h files to the enterprise server from the deployment server check-in location. This is done to preserve a snapshot of the .c and .h files at the time of the build.
• The system generates the C code from the NER on the Enterprise Server. These .c and .h files will be compiled with the rest of the business functions on the enterprise server so the generation is now done on the enterprise server.   Enterprise server does NER gen!
• Before the generation of NER(named event rules), the system will lock all processes, delete ddict, ddtext, glbtbl .xdb and ddb. It will unlock the processes and continue with the Generation.   WHAT? Why?
• The system builds the specs directly from Central Objects, putting the results in the package’s spec tables (<tablename>_<packagename>, for example, F98710_DV910FA) in the database. The client package’s local specs are no longer used to build the server spec tables in the database. Building the specs happens on the enterprise server; whereas before this enhancement, it happened on the build (client or deployment server) machine. How are the specs kept in sync?
• The Package Definition application now has an option to compress the server package and/or the client package. The jde.ini setting is no longer valid.
• The system moves the server log, svrpkgbuild.log, from each server to the deployment server. Also, the files located under the text directory on the enterprise server are moved to the deployment server.
• You can now view the server package build logs in the Build History application when using the View Logs option. However, if the client was not built, the client logs do not appear.

Update Package Build Changes

The following changes have been made to the Enterprise server for update packages:

• R9621S is a new UBE that runs first and initiates the server package build.
• The update package business functions’ .c and .h files are transferred to the enterprise server.
• The Generation of NERs’ .c and .h files occurs on the enterprise server.
• Before the generation of NER, if the update package includes NER, the system will lock all processes, delete ddict, ddtext, glbtbl .xdb and ddb. It will unlock the processes and continue with the Generation. This only happens if there are NER in the update package.
• The specs are built from the Central Objects and put in the package’s spec tables (<tablename>_<packagename>, for example, F98710_DV910UP) in the database. Update packages NOW have their own spec tables in CO
• The system moves the server log, svrpkgbuild.log, from each server to the deployment server. Also, the files located under the text directory on the enterprise server are moved to the deployment server.
• You can now view the server package build logs in the Build History application when using the View logs option. However, if the client was not built, the client logs do not appear.

 

Changes to the Client Package Build Process

Numerous changes have also been made to the client build for both full packages and update packages.

Full Package Build Changes

The following changes have been made to the full package client build.

If Building a Server and Client Package

• R9621S is a new UBE that runs first and initiates the server package build.
• R9622C is a new UBE that runs next and initiates the client package build. The client build only occurs if it was selected in the application.
• All of the business functions’ .c and .h files are moved from the enterprise server to the package’s directory on the deployment server.
• There is no generation of NERs’ .c and .h files on the client.
• The system compiles the .c and .h files.
• The specs are built from the package’s spec tables (<tablename>_<packagename>, for example, F98710_DV910FA) in the database and put into the package’s local spec database.  See how this has been reversed!  Clever!  We used to do this the other way, build the DB on the DEP server and the copy this back to CO.

If Building a Client-Only Package

• R9622C is a new UBE that runs and initiates the client package build.
• The build behaves the same as before the enhancement.
• The business functions’ .c and .h files are retrieved from the deployment server.
• The NERs’ .c and .h files are generated on the client.
• Business functions are compiled.
• The specs are built from Central Objects into the package’s local specs.
• If the user adds a server after building a client-only package, the package will build assuming that the client was not built first. This will cause the system to overwrite the package’s business functions’ .c and .h files on the deployment server with those from the enterprise server. Also, all specs will be rebuilt.

R9621 & R9622 replaced!

Wow, these two UBEs have been my package build buddies for a very long time.  We’ve spent many a late night together, although I must admit we are generally joined by R98825D later in the night.  R98825D always steals the show and does all of the glory work that has been set up by the rest.  R98825F is relatively new to the scene and does not always get invited, but when it does – generally outstays their welcome.  Oh yeah, and convincing  R98825F to tip up the first time is pretty difficult.

Their eulogy (R9621 & R9622) could go on for days about how helpful they’ve been and perhaps it’d be quite tearful.

I hope their replacements are up to the task!

R9621S – Server Package Build

R9622C – Client package build

Of course, this is VERY confusing – as the R9621 used to be the client and the R9622 used to be the server.  How did this change get through conceptual change management?

JM17096 is responsible for the introduction of these new players.  This ESU was put on for the new tools, 9.1.5.2

update 1 table from multiple tables

Have you ever wanted to update a table based upon a join.  For example, wanted to update F00165 based upon a join of F00165 and another table?  This is really hard to talk about generically, so lets get specific.

I have a table F00165 with all my media objects.  I have another table F00165SRM that has the location of all of the NEW media objects that exist.  So now I want to update F00165 and set the gdgtfilenm to equal the new value.

Oracle does not support joins in update statements.  I was trying to do a complex where exists type query, but it was taking too long.

I can query the results easy enough, but hard to turn this into an update statement:

select replace(mofilename, 'Z:\','\\jdedatastore\') , substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
    from  proddta.f00165srm, proddta.f00165
    where instr(mofilename,'\',-1,1) > 1
    and instr(gdgtfilenm,'\',-1,1) > 1
    and  length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 5
    and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 5
    and substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)))
    and length(trim(gdgtfilenm)) > 50;

So I stumble on the MERGE statement, and this makes me very happy!

  MERGE
  INTO    proddta.f00165 t3
  USING   (
          SELECT  t1.rowid AS rid, replace(t2.mofilename, 'Z:\','\\jdedatastore\') as newMOLocation
          FROM    proddta.f00165 t1
          JOIN    proddta.f00165srm t2
          ON      substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)))
          WHERE   instr(mofilename,'\',-1,1) > 1
            and instr(gdgtfilenm,'\',-1,1) > 1
            and  length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 5
            and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 5       
            and  gdgtmotype = '5')      
  ON      (t3.rowid = rid)
  WHEN MATCHED THEN
  UPDATE
  SET     t3.gdgtfilenm = newMOLocation;

 

As you can see from the above this seems to be a nice and simple way of doing the update of a single table with the join of multiple tables.  The other nice thing is that you can also do WHEN NOT MATCHED and apply some other logic.  See oracle doco here http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606 

I do however get an ORA-04036 when running it – so perhaps there is a little fine tuning required.  Nice new 12C feature that it hitting me.

vbscript to insert filenames into oracle database table navigating through all children dirs

 

I have my media objects in a flat file structure on the deployment server and need to do some analysis in oracle on my F00165.  I really need to see what physical files still exist and then populate F00165 with the new file locations (yes we are moving).  This is a continuation of a series of emails about media objects and how they can go wrong.

This particular situation is at a client that has about 20 million records in F00165 and reference to over 5 000 000 physical files.  You may or may not know that trying to restore this amount of physical files to a single directory in windoze is going to kill the system.  The file allocations and internal referencing for a folder seems to crap out at about 100 000 files, and the copy then takes longer than me doing it on an abacus and arranging the bits and bytes with a magnet.

So I’ve created a oracle table with the command:

create table proddta.f00165 (mofilename varchar(256)) ;

Then ran the script

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset 
set recset = wscript.createobject("ADODB.Recordset")
set Insertresults = wscript.createobject("ADODB.Recordset")
set f98moquerecset = wscript.createobject("ADODB.Recordset")
Dim cmdInsert 
set cmd = wscript.createobject("ADODB.Command")
set cmdInsert = wscript.createobject("ADODB.Command")
set cmdf98moque = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "DSN=pjde;" & _
"User ID=jde;" & _
"Password=jde;"

Oracon.Open
Set cmdInsert.ActiveConnection = Oracon

ShowSubfolders FSO.GetFolder("Z:\MediaObjectsData\HTMLUploads"), 3 

Sub ShowSubFolders(Folder, Depth)
    If Depth > 0 then
        For Each Subfolder in Folder.SubFolders
            if(instr(subfolder,"RECYCLE")=0) then
              'Wscript.Echo Subfolder.Path
              ShowSubFolders Subfolder, Depth -1 
		Set colFiles = subFolder.Files
		For Each file In colFiles
    		  absPath = FSO.GetAbsolutePathName(file)
    		  'wscript.echo absPath
    		  cmdInsert.CommandText = "INSERT INTO PRODDTA.F00165SRM VALUES ('" & absPath & "')"
    		  Set recset = cmdInsert.Execute
		Next
            end if
        Next
    End if
End Sub

Note that 

Note that "Z:\MediaObjectsData\HTMLUploads" is the root of the copy of the media objects.  We had to use a series of sub folders because of the windoze problems with performance.

So then I had my oracle table with a FULL path to all of the media objects.

I followed the above with some SQL against the F00165 to update the location of many of the physical file references that are wrong.

update proddta.f00165
set gdgtfilenm = (select replace(mofilename, 'Z:\','\\jdedatastore\') 
    from  proddta.f00165srm 
    where substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1))) )
where  gdgtmotype = '5'
and gdgtfilenm like '\\%'
and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 1
and exists
(select 1 from
proddta.f00165srm
where substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1))) 
and length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 1);