enter lots of table names into data selection for R98403

Remember to create a vbs script to enter all of your table names into R98403 data selection.  This is an AWESOME time saver:

set objShell = wscript.createobject("WScript.Shell")

Do until success = True 
  Success = objshell.AppActivate("List Of Values") 
  'Success = objshell.AppActivate("something.txt - notepad") 
  wscript.sleep 1000 
Loop

wscript.sleep 100 
wscript.echo "Start data pump" 
wscript.sleep 100 
Success = objshell.AppActivate("List Of Values") 
objshell.sendkeys "+{tab}+{tab}"

objshell.sendkeys "F98840~"
objshell.sendkeys "F98800~"
objshell.sendkeys "F98830~"
objshell.sendkeys "F98810~"
objshell.sendkeys "F594218~"
objshell.sendkeys "F590001~"
objshell.sendkeys "F594213~"
objshell.sendkeys "F590120~"
objshell.sendkeys "F594109~"
objshell.sendkeys "F4096~"
objshell.sendkeys "F0901D~"
objshell.sendkeys "F38111~"
objshell.sendkeys "F594111~"
objshell.sendkeys "F594110~"
objshell.sendkeys "F599312~"
objshell.sendkeys "F62UI13~"
objshell.sendkeys "F56108~"
objshell.sendkeys "F1204~"
objshell.sendkeys "F01131~"
objshell.sendkeys "F56107~"
objshell.sendkeys "F03B16~"
objshell.sendkeys "F3403~"
objshell.sendkeys "F6110~"
objshell.sendkeys "F57150~"
objshell.sendkeys "F470561~"
objshell.sendkeys "F00165~"
objshell.sendkeys "F43199~"

I’ve blogged on this previously, there are small changes that you need to make between tools releases.

Run the script

And in 1 second, you have your many values in data selection!

database link dblink without tnsnames.ora on the server

Wow, another day of learning.  I guess that it all makes sense, but if you create a database link from your client, and you reference a tnsentry that you think is going to exist on the server, then it might not.  Remember database links are going to use the servers tnsnames.ora and sqlnet.ora to try and work out where to go for the data.

create database link "jde_findeva" 
connect to JDE 
identified by myp@ss
using 'findeva’;

The link above needs the server tnsnames to know about findeva, however, this could be written with:

CREATE DATABASE LINK jde_findev
connect to JDE
identified by myp@ss
USING '(DESCRIPTION=(SDU=3000)(ADDRESS=(PROTOCOL=TCP)(HOST=myDBServer01)(PORT=1531))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=findeva.mydomain.com)))' ;

Basically, you’ve done a tnsping on a machine that does know about the database and ripped out everything from (DESCRIPTION onwards.  Removed the spaces and used this to create your database link.  Notice that this is also not a public link, make it private.

Mental note–syntax for simple left outer join on oracle

I’m too old school, I use where exists and union, but not enough left outer joins.

select table_name, simd
from all_tables LEFT OUTER JOIN ol910.f9860 ON ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where table_name like 'F98%' and owner = 'PP910';

The example above is great for CNC people (and JDE DBA’s) as it gives you a description of the extremely helpful table names in JDE.  Note that the left outer join helps when there is package build files in the mix – things that are not in object librarian.  Note that this is also fairly timely with another recent post about finding table names or counts – or something.

TABLE_NAME                     SIMD                                                         
------------------------------ ------------------------------------------------------------ 
F98745                         Smart Field Named Mappings                                   
F98750                         Forms Design Aid Text Information                            
F98751                         Forms Design Aid Specification Information                   
F98752                         Forms Design Aid/Software Versions Repository Header Info.   
F98753                         Forms Design Aid/Software Versions Repository Detail Info.   
F98760                         Report Design Aid Text Information                           
F98761                         Report Design Aid Specification Info                         
F98762                         JDEBLC - Behavior Information                                
F98950                         User Overrides Table                                         
F98950D                        User Overrides Language Table                                
F989998                        Java Persistent Object Cross Reference Table                 

This post is really for me to use next time I need to do this.

Work submitted job (WSJ) history for new enterprise server

AS you know WSJ job information is stored in F986110 (not in the system data source) and the next numbers for jobs are controlled by F986111 in the same data source.

When you build a new enterprise server for a client (as a replacement for an old one), generally in the new virtual world – you just build a new machine.  This is an easy way to ensure that production is not affected.  It’s easy to introduce to an existing architecture and then easy to swap out the old.  Quite often your users will want access to their historical UBE’s.

So, you can use the following SQL carefully to do this for you.  Note that this will do the jobs and their execution details.

You’ll need to ensure that the PDF / CSV / log files are copied (physically) to the new server if you want the users to be able to view the output.

TABLE_NAME                     SIMD                                                       
------------------------------ ------------------------------------------------------------                         
F986110                        Job Control Status Master                                    
F986111                        Job Number Master File                                       
F986113                        Subsystem Job Master                                         
F986114A                       Audit Repository Detail                                      
F986114                        Auditing Information Data Repository     

The tables and descriptions are above, this is hand SQL for generating this information.  The only thing that is handy is the ltrim and rtrim to get a match between oracle catalogs and object librarian.

select table_name, simd
from all_tables, ol910.f9860
where ltrim(rtrim(upper(table_name))) = ltrim(rtrim(upper(siobnm))) 
and owner = 'SVMAP01';

Change the execution details with the following.  Note that I’m moving from AUBDC-JAP01P and AUBDC-JAP02P to AUPDC-JAP01P and AUPDC-JAP01P.

select * from SVMAP01.F986114;
update svmap01.f986114 set JCEXEHOST = 'AUPDC00-JAP01P' where JCEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986114 set JCEXEHOST = 'AUPDC00-JAP02P' where JCEXEHOST = 'AUBDC00-JAP02P' ;
commit;

select * from SVMAP01.F986114A;
update svmap01.f986114A set JDEXEHOST = 'AUPDC00-JAP01P' where JDEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986114A set JDEXEHOST = 'AUPDC00-JAP02P' where JDEXEHOST = 'AUBDC00-JAP02P' ;
commit;

select * from SVMAP01.F986113;
update svmap01.f986113 set SSEXEHOST = 'AUPDC00-JAP01P' where SSEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986113 set SSEXEHOST = 'AUPDC00-JAP02P' where SSEXEHOST = 'AUBDC00-JAP02P' ;
commit;

Now the actual WSJ information

update svmap01.f986111 set jcjobnbr = (select jcjobnbr + 1 from svmap01.f986111 where jcexehost = 'AUBDC00-JAP01P') where jcexehost = 'AUPDC00-JAP01P';
commit;

update svmap02.f986111 set jcjobnbr = (select jcjobnbr + 1 from svmap02.f986111 where jcexehost = 'AUBDC00-JAP02P') where jcexehost = 'AUPDC00-JAP02P';
commit;

select count(1), jcexehost from svmap01.f986110 group by jcexehost ;
update svmap01.f986110 set JCEXEHOST = 'AUPDC00-JAP01P' where JCEXEHOST = 'AUBDC00-JAP01P' ;
commit;

select count(1), jcexehost from svmap02.f986110 group by jcexehost ;
update svmap02.f986110 set JCEXEHOST = 'AUPDC00-JAP02P' where JCEXEHOST = 'AUBDC00-JAP02P' ;
commit;

Now you can goto WSJ and see all of the history.  New jobs will have a continued number too.

quick lesson with WLS 12C 12.1.3.0 WLS and losing your customisations

I’ve done a few posts about this, but thought I’d reiterate some of those posts.

When you start an instance the actual runtime location for the JAS files are read from a directory like the following.  Notice that it’s in the middleware\oracle_home

D:\oracle\Middleware\Oracle_Home\user_projects\domains\E1_Apps\servers\JWB01P_PPFIN_93\stage\JWB01P_PPFIN_93\app\webclient.war

When the application is started, these are replaced by the contents of

D:\jde_home\SCFHA\targets\JWB01P_PPFIN_93\owl_deployment\webclient.ear\app\webclient.war after every restart.

This is because the application has been deployed with the following staging mode:

Deployment Staging Mode	Behavior	When to Use
stage	The Administration Server first copies the deployment unit source files to the staging directories of target servers specified by theStaging Directory Name attribute. The target servers then deploy using their local copy of the deployment files.	§ Deploying small or moderate-sized applications to multiple WebLogic Server instances. § Deploying small or moderate-sized applications to a cluster.

Which can been seen here – when looking at the application definition in weblogic console

So if you are doing cool things in jsp files or skinning and changing colours – of course – that is TOTALLY unsupported.  You need to make these changes in the staging directory, so that when things are copied from staging, everything works.

Note that if you want to implement the “grizzly bear” approach, you actually patch the par file for the tools release and give it a different id.  You can then distribute the par file to your web servers and deploy.

This is what we do when we install google analytics for a client’s web environment.  We take their tools release and patch the par file.  They get a totally different listing in server manager, which they can deploy or regress at their own will.  How nice is that!

Heterogeneous Services ODBC based data source and CCSID 13488

Could I have a more complicated scenario.

I’m moving over 4TB of data from an AS/400 to oracle on linux.  We’ve decided to us the linux based ODBC driver and oracle ODBC based gateway & listener combo to get the data from AS/400 to Oracle.  The AS/400 based data is unicode, using CCSID 13488 and GRAPHIC type fields.

This is all good, and is how a JDE database should look when using UNICODE for AS/400.

Problem was, that out of the box, I was getting spaces (or data being read essentially as bytes not a string).

Ouch, this is not how things worked last time.  Last time all of the data was cool.  I tested to see if this was a problem when using generic iSeries windows based ODBC driver, and it was not:

We downloaded and applied the latest V7R1 linux based ODBC drivers and still had the same issue…  Crykie, things are not looking good for my 4TB.

GRAPHIC

This property affects the handling of the graphic (DBCS) data types of GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB that have a CCSID other than Unicode (13488). This property affects two different behaviors: 1. Whether graphic fields have their lengths reported as a character count or byte count through the SQLDescribeCol API and SQLColAttribute API with the SQL_COLUMN_LENGTH option. 2. Whether graphic fields are reported as a supported type in the SQLGetTypeInfo result set

0 = Report character count, report as not supported 1 = Report character count, report as supported 2 = Report byte count, report as not supported 3 = Report byte count, report as supported

You can see that from above, that if the ODBC driver is compliant with ODBC standards 3 and above, it should be okay (use NLS_CHAR settings) from the client…  Oh man…

Then, my DBA guru fixed the issue (with help from this slow witted CNC consultant).

Made following changes to initELDORA.ora

HS_NLS_NCHAR=UCS2 ----- this is for graphic datatype

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252 ------ this is for our database

Made following change to odbc.ini

ForceTranslation = 1

Not too sure of the second option is needed, as this is only supposed to be for 65531 (or what ever it is, not for 13488).

Anyway, after the changes above, we had liftoff:

This made me very happy and allowed me to create some crazy scripts to move all this data from the 400 to oracle.

synchronise configuration of WLS JAS node while down

Are you as frustrated as me with this.

You have made some JAS.INI file changes in SM that you want to synchronise with your installed JAS APP.

Cool, I get it – now I want to synchronise the config – so basically

copy from:

D:\jde_home\SCFHA\targets\JWB01P_PPCMSNZ_92\config

to

D:\oracle\Middleware\Oracle_Home\user_projects\domains\E1_Apps\servers\JWB01P_PPCMSNZ_92\stage\JWB01P_PPCMSNZ_92\app\webclient.war\WEB-INF\classes

But it STARTs the instance.  I do not want it started, I just want the parameters to be right for next time I start it.  It’s really frustrating.  I can see in the Server Manager logs, that it does the changes  to the file and then requests a restart:

FINER: Setting the attribute 'OracleAccessSSOSignOffURL' to value '' in INI file 'D:\jde_home\SCFHA\targets\JWB01P_PPCMSAU_91\config\jas.ini'.
23/09/2015 2:13:48 PM com.jdedwards.mgmt.targets.IniDynamicMBean setAttributeWithUserName
FINER: Setting the attribute 'frameBustingForE1Menu' to value 'differentDomain' in INI file 'D:\jde_home\SCFHA\targets\JWB01P_PPCMSAU_91\config\jas.ini'.
23/09/2015 2:14:06 PM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer start
FINER: Recieved request to start server 'JWB01P_PPCMSAU_91'

So this is not cool, does anyone else have this?  I understand that if this is the FIRST time the application is started – it’s not actually been expanded to the stage directory by WLS, so this might explain why it cannot be sync’d until it’s at least started for the first time.

It’s also important to remember that it’s the nodemanager that does all of the stop and start commands that are executed through SM, therefore you need to ensure that this is up and running for the synchronisations to work.

23/09/2015 2:09:54 PM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer updateApplicationFiles

INFO: Update succeeded for application 'JWB01P_PPCMSAU_91', from location 'webclient.war/WEB-INF/classes'

23/09/2015 2:09:54 PM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer updateApplicationFiles

INFO: Update succeeded for application 'JWB01P_PPCMSAU_91', from location 'webclient.war/WEB-INF/classes'

23/09/2015 2:15:00 PM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer updateApplicationFiles

You can see from the above that it’s got no problems updating the files.

Quick deployment server tip – What is “System Local”

When you log into PLANNER in a modern installation, where do your tables exist?  For example, if I log into JDEPLAN and then look at data sources.

And choose Planner – which table is this going to select from?

<P>select table_name, owner from all_tables where table_name = 'F98611' ;</P>
<P>TABLE_NAME  OWNER 
<BR>------------------------------ ------------------------------ 
<BR>F98611
JDESY910
<BR>F98611
JDEPLAN910 </P>

This is coming from jdeplan910 of course, so when is jdesy910 used?

That is fairly easy to answer too, as we know a few things about bootstrapping from JDE.INI, which leads us to the truth.

Remember that before the fat client on the deployment server knows ANYTHING, it looks at JDE.INI (c:\windows)…

[DB SYSTEM SETTINGS]<BR>Version=43<BR>Default User=JDE<BR>Default 
Role=*ALL<BR>Default Env=JDEPLAN<BR>Default PathCode=PLANNER<BR>Base 
Datasource=Planner - 910<BR>Server=127.0.0.1<BR>Database=E1Local<BR>Load 
Library=<BR>Decimal Shift =Y<BR>Julian Dates=Y<BR>Use 
Owner=N<BR>Secured=Y<BR>Type=E<BR>Library 
List=<BR>Library=<BR>DatabaseName2=JDEPlan910<BR>ServerPort=1521<BR>JDBNETUse=N<BR>UnicodeFlag=Y<BR>LOBFlag=Y

So we can see from here that the client uses this datasource to load OCM’s, so if you want to see where things are located, look at the OCM’s that are pointed to in the JDE.INI (same for any client!)

Therefore, we can see the data sources that are mapped to JDESY910

So we can then go to OCM, to find out how these are used:

Ahh, so now we know, and it all seems related to ESU’s and product packaging.  Another place to put configuration that you do not want to reference, but perhaps build from. 

So if you need to SQL any data sources for planner (say you are changing something low level – tnsnames.ora alias for a lot of data sources), then you must ensure that you update the correct F98611 and F986101:

update jdeplan910.f98611 set omdatb = 'E1SYS2' where upper(omdatb) = 'E1SYS';
commit;

best practice for swapping out a deployment server

Things are complicated.  New machine names, new operating systems, I want all of the EST

so install a new copy of JDE on your new deployment server, goto edelivery to get the media if you cannot locate it on your old deployment server.

Then, detach the planner and metadata databases on your OLD deployment server.  Make sure that activeConsole is not being used.  this is done from the planner\data directory and calling detach_planner.bat and detach_metadata.bat scripts.  These can take 30 minutes to run.

Copy the files from the old dep server planner\data and planner\spec dir to a new location

attach the original files on your original deployment server again by calling the attach_planner and attach_metadata scripts from the planner\data directory

Now you can synchronise your entire E910 dir from your old deployment server to the new one (I tend to create E910_vanilla and E910_client under the JDEdwards directory.

Now you need to detach the planner and metadata databases on your new deployment server before you do any folder renames.

Now you can rename the E910 dir’s above, replacing the vanilla with the one from your other server

Now you can attach the database files with the scripts in the new DIR structure.

Note also that attach is WAY quicker than detach!

Errors I had:

C:\Oracle\E1Local\database\oradim.log contained the following

Tue Sep 22 16:00:41 2015<BR>c:\Oracle\E1Local\bin\oradim.exe -startup -sid 
e1local -usrpwd *&nbsp; -log oradim.log -nocheck 0 <BR>Tue Sep 22 16:00:46 
2015<BR>ORA-01122: database file 12 failed verification check<BR>ORA-01110: data 
file 12: 'D:\JDEDWARDS\E910\PLANNER\SPEC\SPEC_E910.DBF'<BR>ORA-19734: wrong 
creation SCN - control file expects converted plugged-in datafile

C:\Oracle\diag\rdbms\e1local\e1local\trace

Read of datafile 'D:\JDEDWARDS\E910\PLANNER\SPEC\SPEC_E910.DBF' (fno 12) header 
failed with ORA-19734<BR>Rereading datafile 12 header failed with 
ORA-19734<BR>Errors in file 
c:\oracle\diag\rdbms\e1local\e1local\trace\e1local_ora_2068.trc:<BR>ORA-01122: 
database file 12 failed verification check<BR>ORA-01110: data file 12: 
'D:\JDEDWARDS\E910\PLANNER\SPEC\SPEC_E910.DBF'<BR>ORA-19734: wrong creation SCN 
- control file expects converted plugged-in datafile<BR>ORA-1122 signalled 
during: alter database open...

Note that the detach script for metadata at least probably will not work – yes this is a pain.  Reasons are simple.  You’ve just installed vanilla JDE and you want to attach your other dep server data files.  Of course you’ve not applied special instructions to the new dep server, as it’s new.  Therefore the fixes in the latest planner are not on your new dep server install – you have a problem.

Grab the script from your planner\data dir of your old deployment server (that has had planner ESU’s applied and also has had special instructions applied) – you’ll then be able to detach and attach.

How do you know this worked: login to planner and goto gh9612 – if you see ESU’s you know you are golden!

 

I’ll tell you more along the way, but above is good for getting your ESU history and architecture onto the new dep server.

Join the team at Myriad

We are flat out with work at the moment and are looking for a capable resource to augment our merry team.   We have a heap of really interesting work on and would love to hand some over to the right resource.  Please reach out to me if you are interested or know someone, immediate start!