Thursday 17 April 2014

Stop disconnecting my session, stop windows sessions timing out, stop locking the work station.

I’ve picked up a little assignment on a new 2008R2 machine.  I’m running TC’s and all sorts of things and the machine is locking me after 5 minutes of inactivity.  Come on!  You turn away for a minute to google something and and the screen is locked.  Also, it gets better, it logs me out after an hour!!!!

I’ve raised a bunch of calls, but you’d think I was working for the CIA, too secure to change these settings for me.  Okay…  No worries.  I’ve created a little vbscript to help me…  Wrong I know, but…

Copy below into “StopLockingMyMachine.vbs – save

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

Do until success = True
  Success = objshell.AppActivate("something.txt - notepad") 
  objShell.sendkeys FormatDateTime(Time,4) & " I'm sleeping for 2 minustes Shhhhhh ~"
  objShell.SendKeys "%{TAB}"
  wscript.sleep 120000 
  success = false
Loop

Then start->run->notepad something.txt

Double click your StopLockingMyMachine.vbs script.

run your script.  Every two minutes it’ll activate the notepad window and type a message into it, then it’ll take you back from whence you came…  Oh, this has saved me…

I’d stop it if you were doing lots of sensitive things, but if you are checking TC’s and things, it’s a belter.

manual unicode coversion for JDE part 2: rebuild indexes–just add parallel!

 

This is used when doing manual unicode conversion index generation.  Note that with a couple of tweeks, some parallel – you’ll be getting them done in nottime!

 

create or replace function jde_unicode_create_index(szOwnerSrc      in  varchar2,
                 szTableNameSrc  in  varchar2,
          szOwnerDest     in  varchar2,
          szTableNameDest in  varchar2,
                                                    errMsg          out varchar2)
       return integer
       authid current_user
is
  OW_SP_SUCCESS   constant integer := 0;
  OW_SP_ERROR     constant integer := -20101;
 
  pkName          varchar2(30) := null;
 
  cursor ind_name_cur is
    select owner, index_name, uniqueness, tablespace_name, ini_trans, max_trans,
           initial_extent, next_extent, min_extents, max_extents, pct_increase,
           freelists, pct_free
    from sys.all_indexes
    where table_owner = szOwnerSrc and table_name = szTableNameSrc
    order by index_name;
  ind_name_rec ind_name_cur%rowtype;
 
  type ColCurTyp is ref cursor;
  ind_col_info    ColCurTyp;
  ind_col_exp     ColCurTyp;
  cons_col_info   ColCurTyp;
 
  sql_stmt1 varchar2(256);
  sql_stmt2 varchar2(256);
  sql_ind_create varchar2(1024);
  sql_ind_rename varchar2(128);
  sql_cons_drop varchar2(128);
 
  ind_col_name varchar2(30);
  ind_col_pos integer;
  ind_col_order varchar2(4);
  cons_col_name varchar2(30);
 
  ind_col_expression varchar2(32);
  isNotFirstColumn boolean := false;
  isNotFirstIndex boolean := false;
  ind_count integer := 0;
 
begin
  errMsg := null;
 
  
  sql_stmt1 := 'SELECT COLUMN_NAME, COLUMN_POSITION, DESCEND FROM SYS.ALL_IND_COLUMNS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 ORDER BY COLUMN_POSITION';
  sql_stmt2 := 'SELECT COLUMN_EXPRESSION FROM SYS.ALL_IND_EXPRESSIONS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 AND COLUMN_POSITION = :3';
 
  isNotFirstColumn := false;
 
  open ind_name_cur;
  loop
    if (isNotFirstIndex) then
      isNotFirstColumn := false;
    end if;
 
    sql_ind_create := 'CREATE ';
 
    fetch ind_name_cur into ind_name_rec;
    exit when ind_name_cur%notfound;
    
 
    sql_ind_rename := 'ALTER INDEX ' || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' RENAME TO ' || szTableNameSrc || '_' || ind_count;
    ind_count := ind_count + 1;
 
    if (ind_name_rec.uniqueness = 'UNIQUE') then
      sql_ind_create := sql_ind_create || 'UNIQUE INDEX ';
    else
      sql_ind_create := sql_ind_create || 'INDEX ';
    end if;
    
    sql_ind_create := sql_ind_create || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' ON ' || szOwnerDest || '.' || szTableNameDest || '(';
 
    open ind_col_info for sql_stmt1 using ind_name_rec.owner, ind_name_rec.index_name;
    loop
      fetch ind_col_info into ind_col_name, ind_col_pos, ind_col_order;
      exit when ind_col_info%notfound;
      
      if (isNotFirstColumn) then
        sql_ind_create := sql_ind_create || ', ';
      end if;
 
      
 
      if (ind_col_order = 'DESC') then
        open ind_col_exp for sql_stmt2 using ind_name_rec.owner, ind_name_rec.index_name, ind_col_pos;
        fetch ind_col_exp into ind_col_expression;
        ind_col_name := substr(ind_col_expression, 2, length(ind_col_expression)-2);
        
        close ind_col_exp;
      end if;
 
      sql_ind_create := sql_ind_create || ind_col_name || ' ' || ind_col_order;
      isNotFirstColumn := true;
    end loop;
    close ind_col_info;
 
    isNotFirstIndex := true;
 
    sql_ind_create := sql_ind_create || ')';
    if (ind_name_rec.pct_free is not null) then
      sql_ind_create := sql_ind_create || ' PCTFREE ' || ind_name_rec.pct_free;
    end if;
    if (ind_name_rec.ini_trans is not null) then
      sql_ind_create := sql_ind_create || ' INITRANS ' || ind_name_rec.ini_trans;
    end if;
    if (ind_name_rec.max_trans is not null) then
      sql_ind_create := sql_ind_create || ' MAXTRANS ' || ind_name_rec.max_trans;
    end if;
    if (ind_name_rec.initial_extent is not null) or
       (ind_name_rec.next_extent is not null) or
       (ind_name_rec.min_extents is not null) or
       (ind_name_rec.max_extents is not null) or
       (ind_name_rec.pct_increase is not null) or
       (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' STORAGE (';
    end if;
 
    if (ind_name_rec.initial_extent is not null) then
      sql_ind_create := sql_ind_create || ' INITIAL ' || ind_name_rec.initial_extent;
    end if;
    if (ind_name_rec.next_extent is not null) then
      sql_ind_create := sql_ind_create || ' NEXT ' || ind_name_rec.next_extent;
    end if;
    if (ind_name_rec.min_extents is not null) then
      sql_ind_create := sql_ind_create || ' MINEXTENTS ' || ind_name_rec.min_extents;
    end if;
    if (ind_name_rec.max_extents is not null) then
      sql_ind_create := sql_ind_create || ' MAXEXTENTS ' || ind_name_rec.max_extents;
    end if;
    if (ind_name_rec.pct_increase is not null) then
      sql_ind_create := sql_ind_create || ' PCTINCREASE ' || ind_name_rec.pct_increase;
    end if;
    if (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' FREELISTS ' || ind_name_rec.freelists;
    end if;
 
    if (ind_name_rec.initial_extent is not null) or
       (ind_name_rec.next_extent is not null) or
       (ind_name_rec.min_extents is not null) or
       (ind_name_rec.max_extents is not null) or
       (ind_name_rec.pct_increase is not null) or
       (ind_name_rec.freelists is not null) then
      sql_ind_create := sql_ind_create || ' )';
    end if;
 
    if (ind_name_rec.tablespace_name is not null) then
      sql_ind_create := sql_ind_create || ' TABLESPACE '|| ind_name_rec.tablespace_name;
    end if;
 
    
    execute immediate sql_ind_rename;
 
    
    execute immediate sql_ind_create;
 
  end loop;
 
  close ind_name_cur;
 
  
  begin
    select constraint_name into pkName from sys.all_constraints where owner = szOwnerSrc and table_name = szTableNameSrc and constraint_type = 'P';
  exception
    when no_data_found then
      
      errMsg := 'Warning: no primary key in table ' || szOwnerDest || '.' || szTableNameDest;
    when others then
      
      errMsg := 'Error - ' || sqlerrm;
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
      return (OW_SP_ERROR);
  end;
 
 
 
  if (pkName > ' ') then
    sql_stmt1 := 'ALTER TABLE ' || szOwnerDest || '.' || szTableNameDest || ' ADD CONSTRAINT ' || pkName || ' PRIMARY KEY (';
    sql_stmt2 := 'SELECT COLUMN_NAME FROM SYS.ALL_CONS_COLUMNS WHERE OWNER = :1 AND CONSTRAINT_NAME = :2';
    sql_cons_drop := 'ALTER TABLE ' || szOwnerSrc || '.' || szTableNameSrc || ' DROP CONSTRAINT ' || pkName;
 
    open cons_col_info for sql_stmt2 using szOwnerSrc, pkName;
    isNotFirstColumn := false;
    loop
      fetch cons_col_info into cons_col_name;
      exit when cons_col_info%notfound;
 
      if (isNotFirstColumn) then
        sql_stmt1 := sql_stmt1 || ', ';
      end if;
 
      sql_stmt1 := sql_stmt1 || cons_col_name;
      isNotFirstColumn := true;
    end loop;
    close cons_col_info;
 
    
    execute immediate sql_cons_drop;
 
    sql_stmt1 := sql_stmt1 || ')';
    
    execute immediate sql_stmt1;
  end if;
  
  return (OW_SP_SUCCESS);
exception
  when others then
    
    errMsg := 'Error - ' || sqlerrm;
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
    return (OW_SP_ERROR);
end jde_unicode_create_index;

Saturday 12 April 2014

Quantifying employee / user productivity with ERP analytics

What am I talking about?  The ability to see who is your busiest ERP user, what screens they are going to and how often.  Working out your end user interactive productivity.  Sure, anyone can count the UBE’s that they run, or a quick query to see how machine sales orders they process or how many batches they post – but are those a true reflection of user productivity?  No.

We’ve created ERP analytics, where we plug in the google analytic engine to your ERP.  This gives us the ability to slice and dice your ERP usage – so you can work out your most productive users or your least productive users – this could be good information.

Add to this the average interactive performance metrics that can come out as well.  Add to this alerts if your system starts to slow down.  You are being told that the average interactive performance of your ERP is lower than expected – asking you to check things out!  All of this is possible with Myriad’s ERP analytics.

This is a “no cost” service, where we apply our proprietary changes to your ERP installation which will being to populate your profile in google analytics.  Within days and weeks you can understand what is being run, and how much it’s being used.  Imagine upgrade time knowing EXACTLY what applications are being used and how often.  Hone in your retrofit efforts!

image

So you can see from the above that you know how many users are logging in.  Where they are logging in from, their browser, their OS everything.

We can tell you real-time – who is logged in – where they are logging in from:

image

We can tell you what app was used on what day

image

The slice and dice capabilities are endless.

We can also compare your performance (and usage patterns to other companies (anonymously).

All of this for free?  Too good to be true?  Well it’s not.  Installation and configuration is all free.  After two weeks we do a session with you to show you the data and you can see how you’d like to access the data.

Access to the live data only costs a small subscription fee for the number of users that need access to the analytics data.

UBESaveLogFiles=1 none of my UBE’s are creating ERROR log files

I want logs, I need them.  Job status of D means nothing to me…  I need to know if there were errors.  you need to go to the [UBE] section of the JDE.INI and ensure that the following is set to 1.

[UBE]

UBESaveLogFiles=1

Note that the above should ALWAYS be 1

I don’t see how you could be confident with your system if this was not enabled.

Friday 11 April 2014

interesting oracle JD Edwards database size and export size comparison

So exports don’t contain indexes, so they are going to be much smaller than the entire database.

I have a site with 15GB of indexes, 12GB of data and the export file is only 6.2GB.  if I use compress on the export file, it comes down to 515MB!!!

So, a 27GB enterprise class database when exported and compressed comes down to 515MB! 

You can’t tell me that the future of computing power won’t have a database like this running in memory off a computer the size of a thumb drive!

 

image

Thursday 10 April 2014

imp and exp and parfile and indexfile and global search and replace with vi

I know that this is old skool – but I’ve not had time to re-learn dpump, so I’m putting on the glomesh top and nike high tops (and some oakley frogskins) and going old skool on my data refresh knowledge.

You might bang on about a few extra things that dpump might do, but imp and exp is pretty rad (word rad is from the same era as imp and exp).

I’m also making a big thing about global search and replace, because I can never remember the command.

EXP

exp PD910/PASSWORD@instance PARFILE=expPD910.par

This file contains the following:

TABLES=('F980021', 'F95622', 'F95623', 'F95624', 'F95620', 'F95621', 'F952420', 'F95600', 'F98710', 'F982400', 'F98305V', 'F98306', 'F95625', 'F95626', 'F95627', 'F95628', 'F969861', 'F9698710', 'F9698712', 'F980011', 'F983051', 'F983052', 'F952400', 'F952411', 'F98711', 'F98712', 'F98713', 'F98720', 'F98740', 'F98741', 'F98743', 'F98745', 'F98750', 'F98751', 'F98752', 'F98753', 'F98760', 'F98761', 'F98762', 'F98770', 'F98950', 'F98950BK', 'F98950D', 'F989998', 'F989999') LOG=exp910.log FILE=export910_b4golive.dmp

This creates the dumpfile with the data and structures (FILE=) and also a log file (LOG=)

cool, now I want to import this data into PP910 – yes I’m refreshing PP central objects with PD central objects.

So I drop all of the indexes in PP910 and truncate all of the tables –simple.

IMP

Then I IMP the data with the following (note that I don’t want any of the full packages)

imp PP910/PASSWORD@instance

TABLES=('F980021', 'F95622', 'F95623', 'F95624', 'F95620', 'F95621', 'F952420', 'F95600', 'F98710', 'F982400', 'F98305V', 'F98306', 'F95625', 'F95626', 'F95627', 'F95628', 'F969861', 'F9698710', 'F9698712', 'F980011', 'F983051', 'F983052', 'F952400', 'F952411', 'F98711', 'F98712', 'F98713', 'F98720', 'F98740', 'F98741', 'F98743', 'F98745', 'F98750', 'F98751', 'F98752', 'F98753', 'F98760', 'F98761', 'F98762', 'F98770', 'F98950', 'F98950BK', 'F98950D', 'F989998', 'F989999') LOG=impPP910.log FILE=export910_b4golive.dmp ROWS=Y FEEDBACK=10000 IGNORE=Y INDEXES=N

Coolio – see how I did not need to change the tablespace owners or anything (yet!!!).  This will insert all of the data.  But now I have to generate the indexes.  Note that I get a . every time 10000 rows are imported to a table – nice and nerdy oracle.  You need IGNORE=Y to avoid it failing because the tables already exist (I truncated, did not drop).

DDL

So, I use an imdexfile

TABLES=('F980021', 'F95622', 'F95623', 'F95624', 'F95620', 'F95621', 'F952420', 'F95600', 'F98710', 'F982400', 'F98305V', 'F98306', 'F95625', 'F95626', 'F95627', 'F95628', 'F969861', 'F9698710', 'F9698712', 'F980011', 'F983051', 'F983052', 'F952400', 'F952411', 'F98711', 'F98712', 'F98713', 'F98720', 'F98740', 'F98741', 'F98743', 'F98745', 'F98750', 'F98751', 'F98752', 'F98753', 'F98760', 'F98761', 'F98762', 'F98770', 'F98950', 'F98950BK', 'F98950D', 'F989998', 'F989999') LOG=impPP910DDL.log FILE=export910_b4golive.dmp ROWS=N INDEXES=Y INDEXFILE=createIndexes.sql

Note that an index file will prevent things hitting the database, but will write it to a file.  The above example with write the “CREATE INDEX” DDL to a file called createIndexes.sql

I then use vi to change the owner and the tablespaces with:

vi global search and replace (a command I always forget)

:%s/PD910/PP910/g

And then I wang in some parallel action:

:%s/NOLOGGING/PARALLEL 4 NOLOGGING/g

Woot, these indexes are going to fly!

I then execute the script and I have my indexes in the correct tablespace in the correct schema.

startup.properties and customising timeout and much more

What I found is that when I use nmstart, the values that I put into web.xml are not being picked up. 

background:

So, in web.xml I have added:

<session-config>

<session-timeout>60</session-timeout>

</session-config>

This is great, timeout working when I start from either weblogic console or E1!  Wow, things are working so nicely.  But people are telling me (replace t with Y yelling)

So, what is needed

D:\Oracle\Middleware\user_projects\domains\E1_Apps\servers\JWB01P_PDCMSAU_94\data\nodemanager

#Server startup properties

#Fri Apr 04 16:30:07 EST 2014

Arguments=-XX\:MaxPermSize\=256m -Djavax.xml.rpc.ServiceFactory\=oracle.j2ee.ws.client.ServiceFactoryImpl -Xms32m -Xmx1536m

SSLArguments=-Dweblogic.security.SSL.ignoreHostnameVerification\=false -Dweblogic.ReverseDNSAllowed\=false

RestartMax=2

RestartDelaySeconds=0

RestartInterval=3600

ClassPath=D\:\\Oracle\\Middleware\\wlserver_10.3\\server\\lib\\weblogic.jar;D\:\\jde_home\\SCFHA\\targets\\JWB01P_WLS\\config\\jdbc\\E1_Apps\\JWB01P_PDCMSAU_94\\jt400.jar

AdminURL=http\://10.38.16.10\:7001

AutoRestart=true

AutoKillIfFailed=false

weblogic.httpd.session.timeoutSecs=3600

So now, when nmstart is being used, my new timeout and new JVM sizes are being used.

Tuesday 8 April 2014

A very sad day for a great operating system

What a great OS, shame to see it go.  It hosts all my VM’s that I use for connecting to clients – lean and mean.

I might have to start running linux guests.

image

Thursday 3 April 2014

Is midnight AM or PM?

If your life depended on it, do you really and truly know the answer?

I know this is a pretty dumb question, but…  I was asked to schedule a job for 12 midnight and did not really know if I should be selecting AM or PM in the “AM or PM indicator”. 

Google has informed me that midnight is AM and midday is PM – thanks Google, no more midday reboots!

Starting JD Edwards weblogic processes automatically–best practice

Best practice for starting weblogic processes:

Intro

You are going to use weblogic.WLST scripting. This is available in interactive or batch mode. If you are having issues, I recommend using interactive mode for debugging.

You can access the console by setting your domainEnv (as per below):

Testing

D:\Oracle\Middleware\user_projects\domains\E1_Apps\bin\setDomainEnv.cmd

I like to check my java version

java –version

Then start the command interface

java weblogic.WLST

(you can exit with exit() )

Change nodemanager username and password:

Generate some secure connection files, but remember to firstly change the default username and password for logging into the node manager:

This was very helpful:

http://oraclemiddlewareblog.com/2012/01/15/cannot-connect-to-node-manager-access-to-domain-for-user-weblogic-denied/

Make sure you log into: http://localhost:7001/console/login/LoginForm.jsp (this is the default URL)

clip_image002

Lock and edit

clip_image004

Activate changes, they should work immediately. See that I’ve set them the same as weblogic

Generate userconfigfile & userkeyfile

Then you’ll be able to issue the connect statement from weblogic.WLST

So, generate the userconfigfile and the userkeyfile – so passwords just are not hanging about!

java weblogic.Admin -adminurl t3://adminserverl:port -username <adminusername> -password <adminpassword> -userconfigfile userconfig -userkeyfile userkey –STOREUSERCONFIG

d:\Oracle\Middleware\user_projects\domains\E1_Apps>java weblogic.Admin –adminurl t3://localhost:7003 -username weblogic -password xxxx -userconfigfile user config -userkeyfile userkey –STOREUSERCONFIG

clip_image005

Copy these files to a dir of choice. I then use 2 files, a bat file and a command file to pass into weblogic.WLST

clip_image007

Create your scripts:

Note that I have my security files and also the cmd and py commands.

startJDE.bat

@rem Script to start

call D:\Oracle\Middleware\user_projects\domains\E1_Apps\bin\setDomainEnv.cmd

java weblogic.WLST D:\StartWeb\StartJDE.py > d:\startweb\StartJDE.log

@exit

StartJDE.py

print 'starting the script .... '

redirect('d:\StartWeb\WLSTLogs.txt')

startNodeManager(verbose='true',NodeManagerHome='D:/Oracle/Middleware/wlserver_10.3/common/nodemanager',ListenPort='5556', ListenAddress='' );

nmConnect(userConfigFile='d:\StartWeb\userconfigNM.secure',userKeyFile='d:\StartWeb\userkeyNM.secure',domainName='E1_Apps', port='5556', nmType='ssl')

nmStart('AdminServer');

nmStart('JWB02P_PDCMSAU_94');

nmStart('JWB02P_PDCMSNZ_95');

nmStart('JWB02P_PDFIN_96');

nmStart('JWB02P_PDFIN_97');

exit()

Then schedule the script to run as a domain account (same as server manager) in the task scheduler.  Therefore all of your processes will start as the correct user and SM will be able to communicate with them.

Wednesday 2 April 2014

Simple post–simple consultant!

I could not for the life of my synchronise any jas.ini or jdbj.ini files to one of my production web clusters via server manager.  Everything else was working fine.  This is for WLS 10.3.5 and 9.1.3.3

I got the following in my server_manager agent logs:

WARNING: Stdout: weblogic.Deployer invoked with options:  -adminurl t3://AUBDC00-JWB01P.au.ad.westfield.com:7001 -userconfigfile D:\\jde_home\\SCFHA\\targets\\JWB01P_WLS\\config\\owlConfig.secure -userkeyfile D:\\jde_home\\SCFHA\\targets\\JWB01P_WLS\\config\\owlKey.secure -name JWB01P_PDCMSAU_94 -redeploy webclient.war/WEB-INF/classes<02/04/2014 10:55:17 AM EST> <Info> <J2EE Deployment SPI> <BEA-260121> <Initiating redeploy operation for application, JWB01P_PDCMSAU_94 [archive: null], to configured targets.> [Deployer:149163]The domain edit lock is owned by another session in non-exclusive mode - this deployment operation requires exclusive access to the edit lock and hence cannot proceed. If you are using "Automatically Aquire Lock and Activate Changes" in the console, then the lock will expire shortly so retry this operation. Exitcode: 1
02/04/2014 10:55:22 AM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer updateApplicationFiles
WARNING: Stderr:
02/04/2014 10:55:22 AM com.jdedwards.mgmt.targets.owl.OWLJ2EEServer updateApplicationFiles
WARNING: Update application files.  Exception: UPDATE FAILED: Redeploy of application 'JWB01P_PDCMSAU_94' using location 'webclient.war/WEB-INF/classes'.
02/04/2014 10:55:22 AM com.jdedwards.mgmt.targets.webserver.WebServer synchronizeConfiguration
WARNING: Could not update remote configuration files for instance JWB01P_PDCMSAU_94
This is usually the result when one of the application servers of a cluster not running.
Make sure all servers are running and issue the sychronization again. Check logs for root cause.
Exception invoking method updateApplicationFiles
02/04/2014 10:55:22 AM com.jdedwards.mgmt.targets.ManagedTarget recordHistoricalEvent
FINE: Recording historical event 'stateChanged' for instance 'JWB01P_PDCMSAU_94'.

I got the following through SM – classic

image

Could not update remote configuration files for instance JWB01P_PDCMSAU_94 This is usually the result when one of the application servers of a cluster not running. Make sure all servers are running and issue the sychronization again. Check logs for root cause. Exception invoking method updateApplicationFiles
Failure during invoke:invoke(, , synchronizeConfiguration) javax.management.MBeanException : Could not update remote configuration files for instance JWB01P_PDCMSAU_94 This is usually the result when one of the application servers of a cluster not running. Make sure all servers are running and issue the sychronization again. Check logs for root cause. Exception invoking method updateApplicationFiles

This is an example of an error message being too helpful, as it’s making assumptions about the problem, I have no cluster.  Also this is NOT helping, because the problem is more obvious!!!  How about the message says “you’ve locked the weblogic config you dill, how about you unlock it via your weblogic console so that I can update my files”.

 

image

 

Wow, sometimes blogging reveals some embarrassing situations. 

Once released – update worked as it should.