Friday, 14 December 2012

Oracle DBA tips for CNC

Sure, you might know it all about databases, but this is not for you.  This is for me, when I forget all of these commands next week...  I've got somewhere to go!

Stay tuned for some banter about CHAR to NCHAR, it's my next port of call!

List your table spaces and space remaining:



SELECT
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"    
FROM
(
SELECT
tablespace_name,
Sum(bytes) AS bytes
FROM
dba_free_space
GROUP BY
tablespace_name
) fs,
(
SELECT
tablespace_name,
SUM(bytes) AS bytes
FROM
dba_data_files
GROUP BY
tablespace_name
) df
WHERE
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc


Tablespace                     Size (GB)              Free (GB)              
------------------------------ ---------------------- ---------------------- 
PRODDTAT                       12.05078125            7                      
UNDOTBS1                       3.7109375              3                      
TESTDTAT                       21.953125              2                      
TESTDTAI                       20.953125              1                      
PRODDTAI                       1.953125               1                      
SYS7333T                       0.029296875            0                    

List your datafiles and amount used:


Now, list your database files and how much of them are being used, whether they are autoextend


SELECT substr(df.file_name,1,45),
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
round((fs.s/df.bytes*100),2) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
--AND df.tablespace_name in ('TESTDTAT', 'TESTDTAI')
ORDER BY t desc;


/u02/jdedwardsppack/ORCL/Tables/proddtat02.db PRODDTAT AVAILABLE 10485.76 60.23 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat03.db TESTDTAT AVAILABLE 10485.76 2.11 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat05.db TESTDTAT AVAILABLE 5242.88 41.99 NO
/u02/oracle/oradata/jdepoc/undotbs01.dbf UNDOTBS1 AVAILABLE 3891.2 91 YES
/u02/jdedwardsppack/ORCL/Tables/dv910t02.dbf DV910T AVAILABLE 3686.4 16.14 YES
/u02/jdedwardsppack/ORCL/Tables/testdtai05.db TESTDTAI AVAILABLE 2097.152 70.75 NO





If you are running out of space, add some more:


ALTER TABLESPACE testdtat ADD DATAFILE '/u02/jdedwardsppack/ORCL/Tables/testdtat04.dbf' SIZE 5G ;

Check that the physical file system can extend of course (df -k /u02)

If you need some space back, you could try a:

alter database datafile '/u02/jdedwardsppack/ORCL/Tables/proddtat03.dbf' RESIZE 100M ;

Note that all of the above is pretty hard.  If you have command line on the DB server, check if emctl is running:

>emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://aubdc00-jdb01t:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/112/aubdc00-jdb01t_jdepoc/sysman/log

The above gives you the URL to go to for the dbconsole.  This is a great graphical way of doing everything easily.  You can chuck the above scripts in the bin and click all your problems away!  (of course, unless you are like me where the console will not start :-(






Refresh your TEST environment with DATAPUMP JDE


Please do not use R98403, if you are - you might need to give up on CNC, or at least read on.  If you are on a 400, data refresh is easy (cept for that damn auditing!!!).  If you are oracle use data pump, if you are SQL, back / restore and sp_changeObjectOwner!

Here is the data pump instructions:

create directory myDpump as '/u02/oracle/dpump' ;

grant READ,WRITE ON DIRECTORY myDpump TO JDE_ROLE ;

then execute the datapump for an export and then an import.

I tend to use parfiles, so I can move them from site to site.

expdp PRODDTA/PRODDTA@JDEPOC parfile=./prod.par


# PD expdp parfile
schemas=PRODCTL,PRODDTA
#content=data_only
content=all
#directory=data_pump_dir 
directory=data_pump_myriad_dir 
dumpfile=prod.dmp
logfile=prod_expdp.log   
job_name=prod_expdp


impdp TESTDTA/TESTDTA@JDEPOC parfile=./prod.par


# DV impdp parfile
schemas=PRODCTL,PRODDTA
remap_schema=PRODCTL:TESTCTL,PRODDTA:TESTDTA
remap_tablespace=PRODCTLI:TESTCTLI,PRODCTLT:TESTCTLT,PRODDTAI:TESTDTAI,PRODDTAT:TESTDTAT
#table_exists_action=truncate
table_exists_action=replace
#content=data_only
content=all
#directory=data_pump_dir 
directory=data_pump_myriad_dir 
dumpfile=prod.dmp 
logfile=test_impdp.log
job_name=test_impdp
#parallel=4

Thanks to Craig VDV for giving me a start on this one!  

If you are enterprise edition of the DB, bang in a parallel=4 at the bottom - this will kick of 4 threads to do your work.



Thursday, 13 December 2012

DB link from Oracle to AS/400 - platform migration integrities - AS/400 to oracle

I must say thanks to SVDS (any CNC in Aus will know who this is).  He showed me this wonderful creation and I'm in debt to him for it.

Now, here is something that has saved me a lot of time and a lot of heart ache!  I've used a db link between oracle and AS/400.  This has been invaluable for running integrities between my upgrade / platform migration.  

Essentially you create a DB link on the oracle side and then use the @DBLINK sytax at the end of the table when referring to AS/400 based tables - awesome!

To see information about links, use SQL like:


COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS

http://docs.oracle.com/cd/B19306_01/gateways.102/b16222/using.htm#i20148 is a good place to start


Create the link



CREATE DATABASE LINK myAS400
        CONNECT TO ONEWORLD IDENTIFIED BY NUMPTY99
        USING 'SHANNON.WORLD';


Where your tns_name_entry is defined something like:


SHANNON.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = xx.77.66.44)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = ORAGATE)
    )
    (HS = OK)
  )

What I hear you say, surely it cannot be that simple...  And yet it is.  For simple SQL this is a blast.  Here are some examples:

See if all the custom objects made it over from XE:


select t1.siobnm, t1.sisy, t2.sistce from ol7333.f9860@myAS400 t1, ol7333.f9861@cms t2 
where t1.siobnm = t2.siobnm and t2.sipathcd = 'DV7333'
and t1.siobnm in (select siobnm from ol7333.f9860@myAS400 t3 where not exists
(select 1 from ol910.f9860 t4
where t3.siobnm = t4.siobnm) 
and t3.siobnm like '%55%');

Data count reconciliation between XE and JDE91 with platform migration from AS/400 to Oracle

create table testdta.rowcounts (table_name varchar (20), AS400_count integer, oracle_count integer) ;
/*  Add your table names from 9.1 data */
insert into testdta.rowcounts (select table_name, 0, 0 from all_Tables where owner = 'PRODDTA') ;
commit ;

/*  Add any tables from the 400 that may not already be in the reconciliation table */
insert into testdta.rowcounts (
select table_name, 0, 0
from qsys2.systables@myAS400  t1
where table_schema = 'TESTDTA'
and not exists (select 1 
  FROM all_tables
  WHERE owner = 'PRODDTA'
  AND table_name = t1.table_name)) ;


/*  update the AS/400 row count data  - you need to run the results of this statement*/
select 'UPDATE TESTDTA.ROWCOUNTS set AS400_count = ( SELECT COUNT(1) FROM ' || 'TESTDTA.' || table_name || '@CMS ' || ') WHERE table_name = ''' || table_name || ''';' 
from qsys2.systables@CMS t1
where table_schema = 'TESTDTA'

/*update the oracle row counts - you need to run the results of this statement*/
select distinct 'UPDATE TESTDTA.ROWCOUNTS set oracle_count = ( SELECT COUNT(1) FROM ' || 'PRODDTA.' || table_name || ' ' || ') WHERE table_name = ''' || table_name || ''';' 
from all_tables t1
where owner = 'PRODDTA'
commit ;

/*  here is your data, NICE! */
select * from TESTDTA.rowcounts where (as400_count <>0 or oracle_count <> 0);



Refresh TASKS from PS910

I was missing fast path and other options on my fat client after an upgrade.  It was quite annoying.  I ran the following scripts to insert all PS records that did not exist:


insert into prodctl.f9000 t1
(select * from ps910ctl.f9000 t2
where not exists (select 1 from prodctl.f9000 t3
where t3.tmtaskid = t2.tmtaskid or t3.tmtasknm = t2.tmtasknm));
commit ;


insert into prodctl.f9001 t1
(select * from ps910ctl.f9001 t2
where not exists (select 1 from prodctl.f9001 t3
where t3.TRRLTYPE = t2.TRRLTYPE
and t3.TRFRMREL =t2.TRFRMREL
and t3.TRTHRREL =t2.TRTHRREL
and t3.TRPARNTTSK =t2.TRPARNTTSK
and t3.TRCHILDTSK =t2.TRCHILDTSK
and t3.TRPRSSEQ =t2.TRPRSSEQ ));
commit;

But, they hardly inserted a row (I'm keeping them here for next time that I need to run them).

I eventually found the following:
https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=403346239050589&id=654116.1&_afrWindowMode=0&_adf.ctrl-state=41yvw5yrg_4

This was all about task view 29 (Which is the tools task view in 812 and above).  this was not secured, it did not EXIST!  I added task view 29 (because I could get to P9000) and then was able to see all of the proper menus.



OneWorld client install DEV client THICK client FAT client

Lots of things with E1 have got easier.  SM makes tools releases easier.  We makes client deploys easier.  9.1 makes viewing graphs easier (OVR).  Why is the fat client SO difficult!!

See the following link for trooublshooting the fat client / dev client install issues

https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=362486124486969&id=1085806.1&_afrWindowMode=0&_adf.ctrl-state=ywdtqyeuj_244

Then also check the guides
http://docs.oracle.com/cd/E24902_01/doc.91/e18843/toc.htm

I'm currently having a problem that I cannot select any packages because of "invalid package".  This is true for all of the packages that I built and the ones that I did not.  It's frustrating with all of the different 

When installing the oracle database piece, I get the following:
"The command failed with error 1."

So, I then found a 9MB file in c:\program files\oracle\inventory\logs\cloneActions..

This said "service already exists...", Arrgghh that's right.  I cleaned up the reg and all files, did not delete the services from the previously aborted install.  Remember to run regedt32 to get rid of the correct 32 bit settings.

sc delete < SERVICE name>
sc delete oracleE1LocalClrAgent
sc delete oraclemtsrecoveryservice


I again delete c:\oracle (not default location without giving you an obvious choice (you can edit settings.ini on the deployment server (under oneworld client install\thirdparty\oracle) and change the default DIR...

Delete registry settings

delete c:\oracle; c:\program files\oracle; c:\program files x86\oracle

restart

try install again, this is using the \\oneworld client install\installManager.exe, as this is the one that installs the DB component.

Steps required

  1. Running Install Manager
  2. Installing a local database
  3. Installing a local database client
  4. Installing the Development Client (this uses the Oracle Universal Installer (OUI)).
  5. Troubleshooting any installation errors.

    Running the Development Client.

So, it's finally working - but... I get
INFO: Command = oracle.sysman.ccr.configCCR.CloneCCRPlugIn 
Command = oracle.sysman.ccr.configCCR.CloneCCRPlugIn has failed  
Configuration assistant "Oracle Configuration Manager Clone" failed 
This is sooo Frustrating.

Find https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=375569752832977&id=1439675.1&_afrWindowMode=0&_adf.ctrl-state=75s8l64k0_34 which tells me that I need the 32 bit client installed first.  Oh man...  I thought that this was for the deployment server only.

Right, follow the delete instructions once again and then install the 32 bit client and then 64bit OEE, CNC is such a joy sometimes.  I did skim read the instructions listed above and I did NOT see this jump out at me...

Okay, all of that is done and we have one more hurdle.  The install logs started to complain that there was no sbf.CAB file to be found (I must have selected to build BSSV - and have done no config).  I removed this from the DV910FC.inf file in the package_inf folder, and BOSH...  install begins.



Wednesday, 5 December 2012

More installation tips

I really do not like coming into an install that is 75% done.  The problem with this is that generally NOTHING works and you tend to doubt everything that you do, but hey…

If you are installing or upgrading JDE on unix, you need to know how to quickly determine if you are locking out user accounts.   It’s a common problem to have separate passwords between old and new systems (in the case of an upgrade) and get some typing wrong.

Remember to use the [DSPWD] in the jde.ini on the deployment server.  JDE expects that the passwords are the same as the OWNERNAME and the password is in CAPITALS (well this is the case for 9.1. 

you can use “SELECT USERNAME, ACCOUNT_STATUS from DBA_USERS’ to see the accounts and their status.  You could also SPECIFY a value like ‘ACCOUNT_STATUS <> ‘OPEN’

I also had a strange situation that the PRODDTA user could not insert into the TESTDTAT tablespace (strange yes), I had to “GRANT UNLIMITED TABLESPACE to PRODDTA’.

Tuesday, 27 November 2012

Running Scripts from EM12C

There is probably a better way of doing this, but I’m going to start with the hard way and then hopefully make it better as I go.

I want a simple script to run a command and email me the results – cool.

I need to ensure that email is going to work from my hosts, so this is editing the /etc/mail/sendmail.mc

dnl define(`SMART_HOST', `smtp.your.provider')dnl

to

define(`SMART_HOST', `smtp.nsw.exemail.com.au’)dnl

some more hacking in this file, this was handy:  http://www.phase2.net/2007/03/sendmail-on-rhel-40-without-dns/

then try and run make from /etc/mail on my OEL 5 machine, get:

[root@E1ENT2 mail]# make
WARNING: 'sendmail.mc' is modified. Please install package sendmail-cf to update your configuration.
WARNING: 'submit.mc' is modified. Please install package sendmail-cf to update your configuration.

need to install some packages

Follow instructions on public yum server:  http://shannonscncjdeblog.blogspot.com.au/2012/06/public-yum-server.html sweet

yum search *send*

[root@E1ENT2 mail]# yum search *send*
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
======================================================= Matched: sendmail.cf ========================================================
sendmail.x86_64 : A widely used Mail Transport Agent (MTA).
sendmail-cf.x86_64 : The files needed to reconfigure Sendmail.

ok, now

make

now restart sendmail

service sendmail restart

Great, so now we can script and get emails that things are being done, nice!

mail -s test name@domain.com < /dev/null 2>&1 >> /dev/null
See from above, we can send files to ourself!
So, a script to restart JDE, ohh – so many ways!
I decided on the following

#!/usr/bin/ksh
USERNAME=`whoami`
if [ $USERNAME = 'jde91' ]
  then
    #we are JDE
    $EVRHOME/system/bin32/EndOneWorld.sh > /tmp/EndOneWorld.out
    sleep 15
    $EVRHOME/system/bin32/RunOneWorld.sh >> /tmp/EndOneWorld.out
    sleep 15
    ps -ef |grep jdenet >> /tmp/EndOneWorld.out
    porttest JDE XXXXX DV910 >> /tmp/EndOneWorld.out
    df -k >> /tmp/EndOneWorld.out
    mail -s 'E1 restart complete'  shannon.moir@myriad-it.com < /tmp/EndOneWorld.out 2>&1 >> /dev/null
else
    echo "File not there"
fi


This was saved, then in created an EM12C job to run it.  This was running at jde91


image


. /home/jde91/.bash_profile; $EVRHOME/system/bin32/restartJDE.ksh


 


 

 
 

AS/400 monitoring and replacement variables

System Monitor Replacement Variables:


Parameter Passed Data
&DATE The Date the monitor triggered or reset
&INTVL Collection interval: How often the monitor collected data (in seconds)
&MON The Monitor name
&RDUR Reset duration: How many intervals does the reset value have to be met before the monitor resets.
&RVAL Reset value: The value that the metric was monitoring for when the monitor reset
&SEQ Sequence number: A unique, incrementing number assigned to each collection interval. Can be used in a
program to compare when triggers happened and in what sequence.
&TDUR Trigger duration: How many intervals does the trigger value have to be met before the monitor triggers
&TIME The time the monitor triggered or reset
&TVAL Trigger value: The value that the metric was monitoring for when the monitor triggered
&VAL Current value: The actual value of the metric when the monitor triggered (2)

Job Monitor Replacement Variables:

Parameter Passed Data
&DATE The Date the monitor triggered or reset
&INTVL Collection interval: How often the monitor collected data (in seconds)
&MON The monitor name
&TIME The time the monitor triggered or reset
&ENDPOINT The endpoint system name
&EVENTTYPE Event type: The type of trigger or reset that is happening, defined as follows:
Triggered Event = 1
Auto Reset Event = 2
Manual Reset Event = 3
&JOBNAME The job name of the job causing the trigger/reset
&JOBNUMBER The job number of the job causing the trigger/reset
&JOBSTATUS The job status causing a trigger/reset
&JOBTYPE The job type of the job causing the trigger/reset
&JOBUSER The job user of the job causing the trigger/rese
&NUMCURRENT Current numeric value
&NUMRESET Threshold value to cause auto-reset of numeric metric
&NUMTRIGGER Threshold value to cause trigger of a numeric metric
&OWNER Monitor owner
&RDUR Reset duration, in intervals, as set in the threshold
&RESETTYPE Reset type and defined as follows:
Manual reset = 1
Automatic reset = 2
&SBS Subsystem of the job causing the trigger/reset
&SERVER Server type of the job causing the trigger/reset.
Note: Not supported for summary metrics.
&TDUR Trigger duration, in intervals, as set in the threshold
&THRESHOLD Threshold number causing the trigger
&MSGID Message ID causing the trigger/reset
&MSGSEV Message severity causing the trigger/reset
&MSGTYPE Message type causing the trigger/reset

Wednesday, 21 November 2012

Moving a site from IIS6 to IIS7

You’ll need to install Web Deployment Tool 1.1 on the source.  You might be able to get it here

Open the IIS6 console and you’ll see the apps installed

 image

Use the Identifier column in the following command to view the dependencies

Start a “Web Deploy Command Line”

image

Make sure you are admin

msdeploy -verb:getDependencies –source :metakey=lm/w3svc/1300211504

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:getDependencies -source
:metakey=lm/w3svc/1300211504
<output>
  <dependencyInfo>
    <dependencies>
      <dependency name="AnonymousAuthentication" />
      <dependency name="WindowsAuthentication" />
    </dependencies>
    <apppoolsInUse>
      <apppoolInUse name="ADAuthDV" definitionIncluded="False" />
    </apppoolsInUse>
  </dependencyInfo>
</output>
C:\Program Files\IIS\Microsoft Web Deploy>

Then you can save it off with

msdeploy -verb:sync -source:metakey=lm/w3svc/1 -dest:package=c:\Site1.zip

If you are unlucky like me, you might need to add a password to the zip

Info: Adding child cert (MY\7D9D8D6756DAEB3B205D5C03250BB95DD4A70C39).
Error: The property 'blob' located at '/metaKey[@path='/lm/w3svc/1300211504']/me
taProperty[@name='SSLCertHash']/cert' is marked as secure. You must specify an e
ncryption password to archive this property.
Error count: 1.

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:sync -source:metakey=lm
/w3svc/1300211504 -dest:package=c:\ADAuthDVLogin.zip,encryptPassword=P@ssw0rd99

Note the comma for the encryptPassword option

Copy them to your destination machine (2008R2 for IIS 7.5 in my situation)

You need to install the

“web platform installer” from http://www.iis.net/downloads

Install it, and then start IIS and you’ll see

image

Double-Click this and then choose products tab

Install the web deploy3 package

image

Restart IIS and you’ll now have import and export functionality

image

You need to import (note that you need to be one your machine name and use the “import server or site pacakge” option

Choose your zip file that you exported (Enter your password if you had one)

I also had to mess around with the application pool settings and edit the web.config files, but eventually it all started working.

Tuesday, 20 November 2012

using WAS on your 400–httpd.conf config for E1

If you are installed JAS on your 400, remember that you need to follow the instructions on the screen.  If it says “follow what is in the readme.txt”, things are not going to work unless you do it.

Please perform the following manual steps.
------------------------------------------

1. Add the following text to the Managed Instance's associated HTTP Server's httpd.conf file.
------------------------------------------------------------------------------------

# The following configuration was added by the EnterpriseOne Server Configuration Framework

Listen 0.0.0.0:9085
<VirtualHost *:9085>
Alias /jde "/QIBM/UserData/WebSphere/AppServer/V7/ND/profiles/default/installedApps/HDRYDOW/WAS7_DV_85.ear/webclient.war"
</VirtualHost>
<Directory "/QIBM/UserData/WebSphere/AppServer/V7/ND/profiles/default/installedApps/HDRYDOW/WAS7_DV_85.ear/webclient.war/WEB_INF">
Order Deny,Allow
Deny from All
</Directory>
<Directory "/QIBM/UserData/WebSphere/AppServer/V7/ND/profiles/default/installedApps/HDRYDOW/WAS7_DV_85.ear/webclient.war">
Order Deny,Allow
Allow from All
</Directory>
------------------------------------------------------------------------------------

2. Restart the Managed Instance's associated HTTP Server to pick up the changes.

ENDTCPSVR SERVER(*HTTP) HTTPSVR(*ALL)

STRTCPSVR SERVER(*HTTP) HTTPSVR(*ALL)

Extra note for AS/400 monitoring

Changes to management central configuration (i.e. when we ask it to automatically restart failed monitors) only take affect once the management central services are restarted:

This can be done with:

  1. In iSeries™ Navigator, expand My connections > System A > Network > Servers > TCP/IP.
  2. Right-click Management Central and select Stop. Wait until the Management Central server has stopped. Press F5 to refresh the screen and view the status in the right pane. The status should display Stopped when the server has stopped.
  3. Right-click Management Central and select Start. This will restart the Management Central servers on the Central System.
  4. Repeat steps 1-3 on the target systems: System B, System C, and System D.

Note that it’s a TCP/IP server, so you can do it with STRTCPSVR *MGTC

Note that this was a helpful article too http://www.mcpressonline.com/system-administration/general/automatically-restart-failed-monitors.html

I did not do this in anger, but assume that it’s not going to affect anything.  I’d probably do it after hours the first time, just to be sure.

This shows that because TCPIP is ended for the backup, this is the reason that the management service is restarted when TCP is:

image

 

Monday, 19 November 2012

proactive AS400 monitoring–out of the box

I have a client that wants to do some more pro-active monitoring of their AS/400.  They want to know if something has failed before their users tell them – innovative!  Different!  Forward thinking!  Let’s be honest, if you read the doco, there are never any problems on an AS400 and this is a waste of time – but maybe not!

I did a little bit a research and I’m amazed what is available “out of the box”.  With some smarts about what to do and how JDE works on the AS/400 – monitoring is fairly easy.

There are 5 main categories of monitoring:

image

These are available from your “Management Central” location within the system I navigator.

What are you going to monitor, here is a start:

  • Disk usage
  • CPU usage
  • Errors in certain job logs (wow, this is GREAT) [tell me if I get
  • Errors in QHST log file, errors in any log file
  • Message in queues

All very possible given the above frame work.  You can just right click and start adding them.  We have our AS400 sending emails when certain disk, CPU thresholds are met.  We have it sending emails when backups are complete and also if there are certain messages in QHST (

SYSTEM

image

Note that I look at what is available (with Graph history), and then add monitors for things that I want to monitor

FILE

You can use a file monitor to notify you whenever a selected file has changed,reached a specified size, or for specified text strings.

QHST for any system problems (CPF1124 – Job start)

JDE log files for specific messages / text strings

JOB

Check jobs for certain messages in job logs

Check existence of jobs, CPU usage of job, count of jobs, thread count etc.

If a UBE is killing the system, you could actually give it lower priority – this is POWERFUL!

You could move a job out of a queue if it was taking too long, this is VERY powerful (with the use of replacement variables)

Message

Monitor for messages in a single message queue.

 

If your monitors are not auto starting

Restarting failed monitoring on the 400

you can manually select the option for “Restart on failed systems” – but this is a pain.  Fix the problem permanently!

clip_image001

Then choose “Connection”

clip_image002

clip_image003

 

In my research found the following helpful:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzahx%2Frzahxmonparent.htm

System iSystems Management Working with Management Central monitors Version 5 Release 4

This was also a VERY good presentation http://www.lisug.org/presentations/Monitoring%20System%20Performance%20Sept2012.pdf

Wednesday, 17 October 2012

Pop quiz

If you are on an AS/400 and want to secure and directory or library from a particular user that has *ALLOBJ authority, can you do it?

….

You have a 50/50 chance on this one.

The correct answer - NO

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzarl/rzarlallobjsa.htm

*ALLOBJ special authority

All-object (*ALLOBJ) special authority allows the user to access any resource on the system whether private authority exists for the user.

Even if the user has *EXCLUDE authority to an object, *ALLOBJ special authority still allows the user to access the object.

Risks: *ALLOBJ special authority gives the user extensive authority over all resources on the system. The user can view, change, or delete any object. The user can also grant to other users the authority to use objects.

A user with *ALLOBJ authority cannot directly perform operations that require another special authority. For example, *ALLOBJ special authority does not allow a user to create another user profile, because creating user profiles requires *SECADM special authority. However, a user with *ALLOBJ special authority can submit a batch job to run using a profile that has the needed special authority. Giving *ALLOBJ special authority essentially gives a user access to all functions on the system.

 

Let me know if you got that right!

iSeries work station definitions–prompting options

Want to default a particular login option for all users on your terminal server for client access and do not know how – well I do.  A great start was found here, but alas the finish was not that great!

http://forums.citrix.com/thread.jspa?threadID=62545

They said to change the user id field in the .cae field, but this is not right.

You will have a .cae file for all of the .ws definitions that you have.  You’ll want to modify the one that your users use. 

[CAE]
UserIDSource=0
UserID=

It’ll look something like the above.

image

Note that the userIdSource is basically an array, with 0 being “Use windows username” and 3 being “User Kerberos”

Change the UserIdSource to be the array value that you want, – 2 in my case for prompt ever time

Seems to have done the trick.

Monday, 15 October 2012

Oracle–the complete solution

The complete oracle solution is a complete reality, and a strategic direction that is very appealing for the JDE centric organisations.  Firstly, what do you need to run JDE?

  • Hardware
  • Storage
  • Virtualisation
  • Operating system
  • Database
  • Web Servers
  • Application
  • Management

How can oracle help with all of these requirements, it’s now fairly easy.

Hardware, as you know is going to be x86 or sprac, there are no limits to the performance and scalability that oracle can offer you in this area.  Exadata and Exalogic will give you the ability to crush ANY processing requirements that you might have.  Throw generic x86 processing at the problem, simple and cheap and able to run oracle standard edition.  If you can afford enterprise edition database, then other engineered solutions, like an ODA, might suite your requirements.

Storage.  Many of the engineered solutions have storage built into them, whether it’s ODA or exalogic or exadata.  Bigdata is going to solve all of your big data problems.  Oracle also have a range of SAN based technology to sort your larger storage requirements – the pillar axiom 600 could help you here.

Virtualisation is simple and Oracle Virtual Machine can create a highly available resilient solution for your enterprise.  You can be fully supported and take advantage of machine templates from Oracle, so save time and money and force you down the path of best practice.

Operating Systems – look no further than Oracle Enterprise Linux.  If you need a supported linux OS for your oracle requirements (or any requirements), look no further.

Database – one word – Oracle.  More words, scalable, performance, reliability.  It’s no longer  a difficult or expensive database.  Conceptually people think that it’s oracle and it’s difficult and expensive, we have clients that do not touch their x85 based oracle database for a year and it just runs…  Enterprise Manager allows you to manage the database through a simple and graphical method.

Web Servers – WebLogic is the best web server available, it’s that simple.  Template are easy.  It’s fast and stable.

Application – JDEdwards

Management – This is like the icing on the cake.  Everything I’ve mentioned can be managed with a single management solution.  Raise cases, alert you if something is wrong.  Manage your entire stack with a single management solution.  The technology is simple, you just need to manage it!

image

Tuesday, 9 October 2012

SOAP connector address in WebSphere & updating webmethods JDE integration server

finding your soap port for websphere (used for connecting SIB explorer)

find serverindex.xml, in a dir like: D:\IBM\WebSphere61\AppServer\profiles\AppSrv01\config\cells\machinenwb01uNode01Cell\nodes\machine01uNode01

“SOAP_CONNECTOR_ADDRESS”

<specialEndpoints xmi:id="NamedEndPoint_1245837904248" endPointName="SOAP_CONNECTOR_ADDRESS">
  <endPoint xmi:id="EndPoint_1245837904248" host="nsgshsjdnwb01u.news.newslimited.local" port="8880"/>
</specialEndpoints>

Installing updates to your integration server for JDE

put the PSFT_E1_Adapter.zip int o the dir below

D:\EnterpriseOne\wsg\IntegrationServer\replicate\inbound

image

deactivate your existing JDE package

Choose install inbound releases

Choose the zip file you just copied into the inbound dir

image

You should get success

Activate it

Then all your connections should still be in the list of adapters:

image

Wednesday, 26 September 2012

JDEdwards and the move from windoze

Good on oracle for making another cool move away from windoze.  Their new server manager console is now available on other platforms.  I noticed in change assistant that you can download the SUN, LINUX or INTEL version of server manager console in 9.1.2.0.  That is nice!

image

I’m trying to think of what is next to kill off the reliance on windows…  Well, it’s going to be that pesky fat client.  Let’s be honest, that is not going to go away in a hurry.  But what if you did not need a fat client.  If you were not creating DLL’s locally or more specifically you were not creating DLL’s that needed to be used on client machines – there is NO reason that you need a wintel deployment server.  I’m quite doubtful that FDA and RDA are going to be written in JAVA, but stranger things have happened.

We need to think that if JDEdwards could have it’s time again, there would be no thick client on windows (knowing what it knows now).  You’d be using JDeveloper as your IDE.  I guess that is one of the nice things about JDEdwards though, it’s got an eclectic pedigree.

R9698711 and bulk table creation and maybe some R9698713 - R98403G

Okay, I’m tired of waiting for enhancements.  I know that this is an area fraught with danger, so please tread carefully.  Run things in proof mode first… etc etc.  Note that there is an enhancement rumour that will make this a 1 step process, but for the time being you might want to follow this.

I’ve been in this situation a number of times and thought I’d let you know what I do with it.  You run R9698711 (bless it’s cotton socks) and you get a large pile of rubbish come out the other end.  You are thinking, hey, E1 works fine – what is all of this rubbish…  It’s generally a result of ESU’s not generating tables, you not following special instructions and a plethora of other reasons.  You might also say that you don’t care much for “SPC Certificate Holding Table - UK – 43”, and I gotta be honest  - neither do I.  The problem is that when your site gets audited by another support organisation, this is the first thing that is picked apart (hey – I do it myself!).

Okay, back to the problem.  You get a huge list of table with problems – what are you going to to?

Remember what this report is doing, it’s comparing the specs to physical tables and making sure they are consistent.  This is for all tables & indexes.  Cool report!

step 1:  run the report to csv and load the csv into excel

step2:  look at all of the tables that have 0 rows and generate them all – how?

step3:  use my previous post about setting up quick data select and use the following formula in the spreadsheet:

image

=+CONCATENATE("objshell.sendkeys """,A2,"   %(a)""")

use the results column and past it into a InsertDSQuickly.vbs file – note that the header looke like below

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

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  wscript.sleep 1000
Loop
wscript.sleep 100

objshell.sendkeys "+{TAB}+{TAB}"

objshell.sendkeys "A0005   %(a)"
objshell.sendkeys "A0006   %(a)"
objshell.sendkeys "A0009   %(a)"
objshell.sendkeys "A0010   %(a)"

This will almost complete your script for adding all of the data selection items for R98403G – this is the golden nugget of information.  You need to use this report, there are a pile of others out there, but they rely on the tables existing somewhere, and if they do not – you are stuck .

It has super simple processing options too:

image

Step 4: call up the data selection windows for R98403G, select object name and select list of values, then run your script.  This will efficiently type ALL missing tables in the data selection list and allow you to claim that you gen’d them all manually and it took you days.

image

The R98403G will then gen all your tables depending on the PO’s that you set.

I was able to clean up 363 entries in one swoop!

R9698713 -  will fix the indexes – not the base tables.  This would be your second sweep.  You fix all of the 0 record tables first (hey this makes total sense, if the specs are wrong it’s gunna be hard to get data into them).  Then run the R9698713 report to fix the indexes.

Tuesday, 25 September 2012

Tools 9.1 update 2–what is new?

9.1.2 is out and our demo site is about to be furnished with all of the new content.  The following is a summary of what oracle have included in the release:

1. Continued enhancement of end user experience

  1. breadcrumbs (also in 9.1.0.4)
  2. task folders in carousel - nice
  3. create favourites from running apps – finally
  4. carousel personalisation
  5. RIA improvements
  6. E1 portlets within an E1Page

2. Additional Platform Choices

  1. Oracle DB 11.2.0.3 cert
  2. windows 2008 R2 cluster support
  3. Firefox 10ESR

3. simplify upgrade and implementation

  1. application configuration analyser
  2. Custom object analyser
  3. optimised index build for TCs
  4. Batch media object conversion from RTF to HTML

4. Productivity

  1. Java API for XML web services
  2. Media object APIs for web services
  3. Dynamic table cache refresh
  4. Form Design Aid improvements
  5. Improvements to E1 page generator

Sunday, 23 September 2012

kerberos and samba for OEL

Actually, what are we trying to achieve.  I have a linux enterprise server and I want my users to be able to put and get interoperability files to this machine.  Right, I want it to be secure too, I want to be able to control it.  So I probably need samba for sharing and then some moire tricks for authentication to MAD.  I need to get samba working for an OEL box.  To get SAMBA working, I want to use domain credentials.  Let’s be honest, doing linux properly involves using LDAP for credential handling.

setting up samba and winbind for windows credential handling in linux.
so u have a share in linux that you want to expose to windoze land, easy...  kerberos, samba and winbind will help you.

First, get kerberos working... how?
vi /etc/krb5.conf


[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log

[libdefaults]
default_realm = MYDOMAIN.LOCAL   <- you define this later
dns_lookup_realm = true
dns_lookup_kdc = true
ticket_lifetime = 24h
forwardable = yes

[realms]
MYDOMAIN.LOCAL = {    <-this is the one that you defined, your LDAP servers
  kdc = ssydads01.MYDOMAIN.local
  admin_server = ssydads01.MYDOMAIN.local
  default_domain = MYDOMAIN.local
  kdc = ssydads01.MYDOMAIN.local
}

[domain_realm]  <-set up some aliasing
.MYDOMAIN.local = MYDOMAIN.LOCAL
MYDOMAIN.local = MYDOMAIN.LOCAL

[appdefaults]
pam = {
   debug = false
   ticket_lifetime = 36000
   renew_lifetime = 36000
   forwardable = true
   krb4_convert = false
}

But what now, you need to ensure that you have the latest packages for winbind etc.
you'll need samba 3.6.7, well at least - I did and so did Geoff!
Geoff has kindly told me which order to install / Update packages in with the handy little addendum:

  • rpm -ivh libwbclient0-3.6.7-44.el5.x86_64.rpm
  • rpm -Uvh libsmbclient0-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh libsmbclient-devel-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh libwbclient-devel-3.6.7-44.el5.x86_64.rpm
  • rpm –ivh –replacepkgs –replacefiles samba3-client-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh –replacepkgs –replacefiles samba3-winbind-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh –replacepkgs –replacefiles samba3-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh –replacepkgs –replacefiles samba3-utils-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh samba3-doc-3.6.7-44.el5.x86_64.rpm
  • rpm -ivh samba3-debuginfo-3.6.7-44.el5.x86_64.rpm

Note that you will not need all of the –replacepkgs if you first uninstall samba

Once this has been done right, you should be able to kinit command.  Note that this is your CaSe sensitive realm name from krb5.conf

kinit user@REALM
kinit shannon@MYDOMAIN.LOCAL

The system will ask you for a password - nice!
So now that you have kerberos functional, time to get winbind and samba working

service smb restart to restart all of your smb related services

using:

> net ads testjoin

you’ll probably get:

ads_connect: No logon servers
Join to domain is not valid: No logon servers

That is because you need to fill out your SMB.conf file.

vi /etc/samba/smb.conf

#Global parameters
[global]
        security = ads
        realm = MYDOMAIN.LOCAL
        max protocol = smb2
        server string = VSYDJDE04
        workgroup = MYDOMAIN
        netbios name = VSYDJDE04
        encrypt passwords = Yes
        auth methods = guest winbind:ntdomain  <--help you use windoze permissions on share
        # guest account = guest
        max xmit = 65535
        name resolve order = wins host
        socket options = TCP_NODELAY SO_RCVBUF=131072 SO_SNDBUF=131072
        os level = 16
        preferred master = No
        domain master = No
        local master = No
        use sendfile = No
        unix charset = UTF8
        winbind use default domain = yes
        create mask = 0700
        directory mask = 0700
        aio read size = 65536
        aio write size = 65536
       #  aio read size = 32768
       #  aio write size = 32768

idmap config * : range = 10000-20000
idmap config * : backend = tdb
idmap config MYDOMAIN : default = yes
idmap config MYDOMAIN : range = 100000-200000
idmap config MYDOMAIN : backend = rid


[test2]
        path = /test
        available = yes
        browseable = yes
        follow symlinks = yes
        read only = no
        public = yes
        max connections = 0
[interop]
        path = /u01/app/jdedwards/interop
        available = yes
        browseable = yes
        follow symlinks = yes
        read only = no
        public = yes
        max connections = 0

 

service smb restart

then:

> net ads join -U domainusername

and enter your password.

net ads join -U smoir
Enter smoir's password:
Using short domain name – MYDOMAIN
Joined 'VSYDJDE03' to realm 'mydomain.local'

if this has all worked, you have all of the net ads commands at your fingertips.  Remember that if you are logged in as root, you’ll need to append –U username to all of your net ads commands – otherwise it’s going to try and authenticate root to the domain.

net ads info            Display details on remote ADS server
net ads join            Join the local machine to ADS realm
net ads testjoin        Validate machine account
net ads leave           Remove the local machine from ADS
net ads status          Display machine account details
net ads user            List/modify users
net ads group           List/modify groups
net ads dns             Issue dynamic DNS update
net ads password        Change user passwords
net ads changetrustpw   Change trust account password
net ads printer         List/modify printer entries
net ads search          Issue LDAP search using filter
net ads dn              Issue LDAP search by DN
net ads sid             Issue LDAP search by SID
net ads workgroup       Display workgroup name
net ads lookup          Perfom CLDAP query on DC
net ads keytab          Manage local keytab file
net ads gpo             Manage group policy objects
net ads kerberos        Manage kerberos keytab

so, i can run net ads user –U smoir, enter my password and get a listing of the domains users!  cool!

you could use "system-config-authentication" for a gui interface, but it's not going to give you all of the options and will write to the afore and after mentioned

As you may have guessed, the final two stanza’s in smb.conf are the dirs that I’m sharing:

[test2]
path = /test
available = yes
browseable = yes
follow symlinks = yes
read only = no
public = yes
max connections = 0
[interop]
path = /u01/app/jdedwards/interop
available = yes
browseable = yes
follow symlinks = yes
read only = no
public = yes
max connections = 0

settings are pretty self explanatory, from windoze – I see

image

Tuesday, 18 September 2012

Index Advisor and JDEdwards performance on the AS/400

This is an easy job that can bear a lot of fruit for the AS/400 – Index rationalisation.

Firstly, run some performance monitors over the 400 and trace activity for an hour or two.  For a busy system this could easily write 4GB an hour of logs to your trace file.

View the summary of these traces and look at your read and write / statement ratios

image

Look at your select to insert and update ratios and get a general feel how you might benefit from more indexes (generally you will).  The above shows a fairly normal read:write ratio of 20:1.

You can go through the details of these files and look for the indexes that the 400 had to make on the fly (fix them) and look at the index recommendations that comes from the period, you can easily generate them.

BUT, here is the best thing that the 400 offers – add your production library into the schemas that you see user database, databasename.

image

This will be recording (depending on certain system settings and OS release levels) ALL recommended indexes for that library since the beginning of time (perhaps not literally, but I think that the 400 has been around for that long, well perhaps not literally – hehe).

image

Right click the library / schema that you want to see the indexes advisements for and choose “Index advisor”.

It shows you the following:

image

Holy moly, that is totally AWESOME for tuning.  Create the indexes that you want with another SIMPLE right click “create index”

image

Don’t stress, create index does not just create the index, you gotta name it and do a few other things.  You get the screen above.  You can then copy the details out (Show SQL)

/* Creating index PRODDTA.F4108_SRM01
Sort Sequence: None (Sort by hexadecimal value) */
CREATE INDEX PRODDTA.F4108_SRM01 ON PRODDTA.F4108 (IOMCU ASC , IOLOTN ASC , IODOCO ASC );

Wow, this has been recommended 60 billion times, I might just add it!

Remember to di this for ALL libraries that might get used.  You’ll be surprised when doing it for SY900, SVM900, PRODCTL, DD900 and OL900 ! 

Brace yourself for a faster system.

Reset all of the stats and do it again a few months later.

Extra for Experts! http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzajq%2Fidxadvisor.htm

The index advisor information can be found in the Database Monitor view 3020 - Index advised (SQE).

The advisor information is stored in columns QQIDXA, QQIDXK and QQIDXD. When the QQIDXA column contains a value of 'Y' the optimizer is advising you to create an index using the key columns shown in column QQIDXD. The intention of creating this index is to improve the performance of the query.

In the list of key columns contained in column QQIDXD the optimizer has listed what it considers the suggested primary and secondary key columns. Primary key columns are columns that should significantly reduce the number of keys selected based on the corresponding query selection. Secondary key columns are columns that may or may not significantly reduce the number of keys selected.

Column QQIDXK contains the number of suggested primary key columns that are listed in column QQIDXD. These are the left-most suggested key columns. The remaining key columns are considered secondary key columns and are listed in order of expected selectivity based on the query. For example, assuming QQIDXK contains the value of 4 and QQIDXD specifies 7 key columns, then the first 4 key columns specified in QQIDXK is the primary key columns. The remaining 3 key columns are the suggested secondary key columns.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzajq%2Fidxadvisor.htm

Create View QQQ3020 as 
(SELECT QQRID as Row_ID,
QQTIME as Time_Created,
QQJFLD as Join_Column,
QQRDBN as Relational_Database_Name,
QQSYS as System_Name,
QQJOB as Job_Name,
QQUSER as Job_User,
QQJNUM as Job_Number,
QQI9 as Thread_ID,
QQUCNT as Unique_Count,
QQUDEF as User_Defined,
QQQDTN as Unique_SubSelect_Number,
QQQDTL as SubSelect_Nested_Level,
QQMATN as Materialized_View_Subselect_Number,
QQMATL as Materialized_View_Nested_Level,
QVP15E as Materialized_View_Union_Level,
QVP15A as Decomposed_Subselect_Number,
QVP15B as Total_Number_Decomposed_SubSelects,
QVP15C as Decomposed_SubSelect_Reason_Code,
QVP15D as Starting_Decomposed_SubSelect,
QQTLN as System_Table_Schema,
QQTFN as System_Table_Name,
QQTMN as Member_Name,
QQPTLN as System_Base_Table_Schema,
QQPTFN as System_Base_Table_Name,
QQPTMN as Base_Member_Name,
QVPLIB as Base_Table_Schema,
QVPTBL as Base_Table_Name,
QQTOTR as Table_Total_Rows,
QQEPT as Estimated_Processing_Time,
QQIDXA as Index_is_Advised,
QQIDXD as Index_Advised_Columns_Short_List,
QQ1000L as Index_Advised_Columns_Long_List,
QQI1 as Number_of_Advised_Columns,
QQI2 as Number_of_Advised_Primary_Columns,
QQRCOD as Reason_Code,
QVRCNT as Unique_Refresh_Counter,
QVC1F as Type_of_Index_Advised,
QQNTNM as NLSS_Table,
QQNLNM as NLSS_Library
FROM UserLib/DBMONTable
WHERE QQRID=3020)
 

OMC OMW promoting F95625 and F95626 records

If you are using some of the more advanced bursting functionality, different email subject lines and or email delivery setup – it’s probably best that you know that you cannot promote this with OMW or define the promotions with OMC.  You’ll need to manually copy the records between your control table environments.  I happened to spend some time trying to solve this for a client today, and then found this reference.

see oracle Bug 11054826 : OMW TRANSFER F95625 F95626 - SAR: 8975106

Friday, 7 September 2012

Have you got big data in JDE… Maybe…

I think big data is measured in TB, especially when it comes to OLTP.  I know that people might disagree, but that’s what I think.  I’m sure that it’s bigger for BI and even bigger for social media feeds, but for me – managing TB’s of OLTP data gives some head aches.

I’ve blogged before on large tables in JDE, and how large their requisite indexes are.  Here is an AS/400 example

  • F0911 90GB
  • F0911_1 6GB
  • F0911_28 6GB

So, 90GB of data and around 140 GB of indexes for F0911.  This is with 610GB of data.  A fairly normal mix I’m here to say!  I feel for the web server if someone wants to “scroll to the end” of the F0911 table in data browser – ouch!  That’s going to crash.

Thursday, 6 September 2012

more detail on BSSV 9.1.0.4 not starting with WLS 10.3.5.0 on unix

I’ve blogged this already, but thought that I’d provide a little more detail.

When you deploy your package to the server, everything goes a little pear shaped.  So I told you to edit the config.xml file for the domain and add a class-path entry.

The file looks like this before:

<server>
  <name>BSSV_PD900</name>
  <ssl>
    <enabled>true</enabled>
    <listen-port>9701</listen-port>
  </ssl>
  <machine>vsydwls02</machine>
  <listen-port>9700</listen-port>
  <server-start>
    <arguments>-Ddefault_path=/u01/app/oracle/e1Agent/SCFHA/targets/BSSV_PD900/config</arguments>
  </server-start>
</server>

And after:L

<server>
<name>BSSV_PD900</name>
<ssl>
<enabled>true</enabled>
<listen-port>9701</listen-port>
</ssl>
<machine>vsydwls02</machine>
<listen-port>9700</listen-port>
<server-start>

<class-path>/u01/app/oracle/Middleware/wlserver_10.3/server/adr/ojdbc6.jar</class-path>
<arguments>-Ddefault_path=/u01/app/oracle/e1Agent/SCFHA/targets/BSSV_PD900/config</arguments>
</server-start>
</server>

save and exit.  restart AdminServer You should be now good to go!