Change putty annoying blue with your login script

I love putty and I also hate it.  I love it coz it’s cool, one file, simple, great… I hate the default colour of folders, I know I’m getting old – but only freaks can read royal blue on black.  Colours that are being passed back to putty rely on the following environment variable – LS_COLORS [sic].  I stole most of this from http://sshadmincontrol.com/change-directory-color-from-blue-to-something-readable-on-putty-a-quick-fix, but added some more explanation.

So, you can change this by sed’ing this env variable and setting a new colour! 

Type the command and you get:

export|grep COLO

declare -x LS_COLORS="no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:"

Enter the following command and you’ll see that the number 34 has changed, as so has the colour!

export LS_COLORS=$(echo $LS_COLORS | sed "s/di=\(..\);../di=\1;93/")

declare -x LS_COLORS="no=00:fi=00:di=00;93:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:"

So in your .profile or .bashrc or what ever, you can do the following:

Note that I chose 93 above, but you can choose the colour that you want.

31    Red
32    Green
33    Orange
34    Blue
35    Purple
36    Cyan
37    Grey
90    Dark grey
91    Light red
92    Light green
93    Yellow
94    Light blue
95    Light purple
96    Turquoise
97    White

Now that is totally RAD!

simple java program to test JDBC

ODBC is not an enigma to me, I can write C, VBScript or vba / vb code to use ODBC and interrogate databases.  It’s simple and easy.  what is a bit more of an enigma is java.  I’ve had to write java code (but only simple) when I’m doing load testing and editing scripts in openScript.

But, I quite often want to know what is the exact version of a java driver, or whether it’s actually working or is it right, or does it work, so now that is simple: 

The main purpose of this script was used to ensure that the firewalls between servers are open enough for JDBC – another super frustrating area.

This example is used to test AS/400 ODBC – but you get the picture.

You’ll need jt400.jar too

You’ll need a jdk, as this has the compiler, also required for what we are doing.

"C:\Program Files\Java\jdk1.8.0\bin\javac" dbconnect.java

Note that the above will create u a class file, then use it to run.

H:\scripts\JDBC>"C:\Program Files\Java\jdk1.8.0\bin\java" -cp .;.\jt400.jar dbconnect

Program worked if there are no errors above!

 

dbconnect.java contains:

import java.io.*;
import java.util.*;
import java.sql.*;
   
public class dbconnect
{
    public static void main(String[] args) {
    try {
    Properties props = new Properties();
    props.load(new FileInputStream("connect.properties"));
    String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
    String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
    //Connect to iSeries
    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection(URL, props.getProperty("userId").trim(), props.getProperty("password").trim());
    System.out.println("Creating statement...");
        Statement stmt = conn.createStatement();
    String sql = "SELECT COUNT(1) as RecCount FROM " + props.getProperty("owner").trim() + ".F0101";
        ResultSet rs = stmt.executeQuery(sql);
        //STEP 5: Extract data from result set
        int RecordCount=0;
    while(rs.next())
        {
          //Retrieve by column name
          RecordCount  = rs.getInt("RecCount");
    }
         System.out.print("\nF0101 count: " + RecordCount + "\n");

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

You also need the following in your working dir, called connect.properties

#I-series ip or host name
local_system=10.148.86.181
#I-series UserId, used for login and library list
userId=JDE
#I-series Password
password=goAwayTurk3y
#Schema
owner=PRODDTA

Stress test ODBC

I had a client that was having all sorts of instability from their RFGen machine.  Yes, I don’t generally promote or talk about other vendors, but RFGen essentially acts like a fat conduit for BSFN’s for RF guns – easy.  It treats the fat client as a mechanism for running BSFNs, easy too.

This one client was having a function die every night / two nights / four nights.

We enabled logging and found that the error was occurring at random statements, sometimes update, delete, insert or open connection.  So, we also found that the AS/400 was getting TCP2617 when we were getting the ODBC problems, so the AS/400 did not hear anything from the connection and terminated it, this is when the client also gave up.

Wow… So, we needed to devise a way so that JD Edwards was not to blame, sure we kinda knew that because the errors were all occurring in ODBC statements – but the client wanted proof (of course!!)

This is a vbscript, just cut and paste it into a .vbs extension and run it with the 32bit wscript executable.  This just stresses ODBC a little.  What I found was that this would break after about 50 executions on particular machines.

This all came down to incorrect routing on the AS/400 – but what an adventure it was!

  dateStamp=now
  fileSuffix=Left(FormatDateTime(dateStamp, 1),6) & day(dateStamp) & monthName(month(dateStamp))  & hour(dateStamp)
  Const ForAppending = 8
  logfile="c:\temp\" & fileSuffix & ".txt"
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)

j=2
while j > 1
Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset
set recset = wscript.createobject("ADODB.Recordset")
Dim cmd
set cmd = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "DSN=Business Data - CRP;" & _
                          "User ID=ONEWORLD;" & _
                          "Password=somethingSecret;"

Oracon.Open
Set cmd.ActiveConnection = Oracon
cmd.CommandText = "Select * from crpdta/f0101"
Set recset = cmd.Execute
i=0
while i < 20
  for each field in recset.fields
    tuple=tuple & " " & field.value
  next
  i=i+1
  recset.MoveNext
'  wscript.echo tuple
  tuple=" "
wend

set recset = nothing
oracon.close
set oracon = nothing

j=j+1

  objTextFile.WriteLine(j)

wend

more impdp expdp commands, move huge data from AS/400 to Oracle

I’m copying a massive amount of data for a project.  This is a direct migration of JD Edwards from World A73 to OneWorld 9.1.  This also has a platform migration in the middle.  There are a couple of fortunate things about this, it’s a JD Edwards retirement program.  This means that the data is static and I have time…  But my clients keep telling me (or is it me telling them) that time is money.  So of course I’m doing this in the most efficient way possible.

There is no money for the oracle database gateway for iSeries, that would be nice, but I’m using heterogeneous database gateway to ODBC and perhaps taking a hit on performance but not the back pocket.

So, I’ve completed all of the world conversions and the P9840TC conversions and am ready for the platform migration piece.  I’m saving money on disk and only using single-byte characters because this is going to save me 2TB.  Did I tell you there is 5TB of data being retired / archived?

We are running the retired environment as a cloud based bureau service.  So, I need to pump all of this data into my E1 tables via the oracle HG.  Sure, I could use R98403 – but I cannot wait until 2015 to get the data, so things are getting a little funky.

I’ve generated the table structures with datapump, import export from an exisitng environment.  This is using a tablespace remap & owner remap with METADATA_ONLY.

Sample file for metatdata_only (no data in the export file)

schemas=CRPDTA,CRPCTL
#content=data_only
content=METADATA_ONLY
#directory=data_pump_dir
directory=myDpump
dumpfile=CRPDTACTL.dmp
logfile=CRPDTACTL_expdp.log  
job_name=CRPDTACTL_expdp

This is the imp

schemas=CRPDTA, CRPCTL
remap_schema=CRPCTL:FIACTL,CRPDTA:FIADTA
remap_tablespace=CRPCTLI:FIACTLI,CRPCTLT:FIACTLT,CRPDTAI:FONFIAI,CRPDTAT:FONFIAT
table_exists_action=skip
#content=data_only
content=metadata_only
directory=myDpump
dumpfile=CRPDTACTL.dmp
logfile=FIA_impdp.log
job_name=FIA_impdp2
#parallel=4

I’m then importing all of this and dropping the indexes (yes all of them).  You may think that is a little risky (actually – I’m dropping PK’s also and I won’t be creating them either [that’s another story – don’t stress – it’s totally valid]).  Then I’m using the HG to populate my tables and then I’m going to generate the indexes using an impdp with sqlfile=

Import over the same export dump file with the sqlfile=, note that you need to bin the section on table_exists_action – as it does not like that.

schemas=CRPDTA, CRPCTL
remap_schema=CRPCTL:FIACTL,CRPDTA:FIADTA
remap_tablespace=CRPCTLI:FIACTLI,CRPCTLT:FIACTLT,CRPDTAI:FONFIAI,CRPDTAT:FONFIAT
content=metadata_only
directory=myDpump
dumpfile=CRPDTACTL.dmp
logfile=FIADDL_impdp.log
job_name=FIADDL_impdp3
sqlfile=createFIADDL.sql

Note also that the sqlfile get’s banged into the directory entry that you added earlier in the DB, with:

create directory myDpump as 'f:\\dpump' ;

grant READ,WRITE ON DIRECTORY myDpump TO JDE_ROLE ;

Now for JDE 9.1, this created a 60MB .sql file!  Wow, now there is going to be a considerable amount of work to rip this apart to get only the create index commands!

It’s pretty nice that impdp and expdp actually write the file with a bit of order, so all indexes are done together and all tables are done together.  So it’s easy to rip out the create index statements.

Generate drop constraint statements:

SELECT 'ALTER TABLE' || ' FIADTA.' || table_name || ' drop constraint ' || constraint_name ||';'
from all_constraints
where owner = 'FIADTA' and constraint_name like '%PK' ;

Generate drop index statements:

select 'DROP INDEX FIADTA.' || index_name ||';'
from all_indexes
WHERE owner = 'FIADTA' ;

Then I use 5 separate scripts that are similar to:

spool F:\fia\copyLogs1.log
insert into FIADTA.F4111 select * from FIAPRODDTA.F4111@a01proddta;
commit;
insert into FIADTA.F41051 select * from FIAPRODDTA.F41051@a01proddta;
insert into FIADTA.F3102 select * from FIAPRODDTA.F3102@a01proddta;
commit;

…

JD Edwards oracle create new schemas owners and tablespaces

Just say you want to create a new copy of control tables for some reason, that could be any reason…  Testing some new menus (wait if you still use menus or tasks, you’re not on the right track – ONLY E1pages should be used for JDE navigation).

Anyway, it’s nice to remember how to run your oracle commands for getting this done.  This is an example on 11G with windoze.

CREATE SMALLFILE TABLESPACE "FBACTLI" DATAFILE 'D:\ORACLE\ORADATA\BROWNES\FBACTLI.DBF' SIZE 1000M AUTOEXTEND ON NEXT 1000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "FBACTLT" DATAFILE 'D:\ORACLE\ORADATA\BROWNES\FBACTLT.DBF' SIZE 1000M AUTOEXTEND ON NEXT 1000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

 

-- USER SQL
CREATE USER FBACTL
DEFAULT TABLESPACE FBACTLT
TEMPORARY TABLESPACE TEMP
identified by HelloPass ;

-- ROLES
GRANT “JDE_ROLE” to FBACTL ;

-- SYSTEM PRIVILEGES

-- QUOTAS
ALTER USER FBACTL QUOTA UNLIMITED ON FBACTLT;
ALTER USER FBACTL QUOTA UNLIMITED ON FBACTLI;

You then create a new data source and make sure that you set up the owner to have the correct tablespace defined.  This is from the data source application (gh9011 –> data sources) P986115, choose you data source

Then enter the tablespaces for the indexes and tables (note that I’m creating two at the moment, so the screen shot might be for FIACTL)

You’ll then be good to go to copy things into the new schema.

i want to sql against e1local

this is very handy to have…  Say you’ve created an environment in planner that you want to quickly add to system or vice versa.  Perhaps you added a bunch of data sources that you don’t want to add again for all of the different datasource locations.  Well, this is easier than you think!

Just change the sys password in the e1local database and login with the tnsnames natively.

To change the password, ensure that the sqlnet.ora (C:\oracle\E1Local\NETWORK\ADMIN)  file for e1local has the following:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

then use sqlplus in C:\oracle\E1Local\bin like:  sqlplus / as sysdba

then alter user sys identified by helloWorld ;

Then in SQL Developer, you can connect to this instance.

Do you’re SQL.

Use a dblink to see / alter data in your main instance.

create public database link "e1prod"
connect to JDE
identified by myPass
using 'e1prod';

Then when you want to log into e1 again, remember that you:

The Oracle database SYSTEM and SYS users' passwords MUST be encrypted strings for E1 to run.

So, run reconfigureMSDE from the system/bin32 dir of the dep server.

Note that you type in the oracle password you chose earlier, and the radio buttons I have above.

JDE SQL Packages again

Wow, these things are really painful (sometimes).

They are great when they work, because they seem to expedite query execution, nice… maintain themselves – nice… but become very large – not nice, can become slow – not nice, do not delete themselves – not nice.

So, a rather large client of mine was getting lots of web instability.  All they could come up with is that they did a build recently and promoted some menus..  Menu’s were being particularly troublesome too.  We were getting things like the following in the logs:

18 Jul 2014 07:48:32,984 [SEVERE]  - [BASE]            com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error.. com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error..
18 Jul 2014 07:48:40,237 [SEVERE]  - [JDBJ]            SQLException occured in the SQLPhysicalConnection.select(): | Table or View Name = F983051 - Data Source[0] = Versions - PD910 java.sql.SQLException: [SQL0901] SQL system error.
18 Jul 2014 07:48:40,237 [SEVERE]  - [BASE]            com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error.. java.sql.SQLException: [SQL0901] SQL system error.
18 Jul 2014 07:48:40,237 [SEVERE]  - [BASE]            com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error.. com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error..
18 Jul 2014 07:49:07,444 [SEVERE]  - [JDBJ]            SQLException occured in the SQLPhysicalConnection.select(): | Table or View Name = F983051 - Data Source[0] = Versions - PD910 java.sql.SQLException: [SQL0901] SQL system error.
18 Jul 2014 07:49:07,444 [SEVERE]  - [BASE]            com.jdedwards.database.base.JDBException: [SQL_EXCEPTION_OCCURRED] An SQL exception occurred: [SQL0901] SQL system error.. java.sql.SQLException: [SQL0901] SQL system error.

But see from the above, it’s talking about versions, so we are having lots of menu issues – but the table is the versions table.

An incorrect assumption that we made in the beginning is that there must be a problem with the SQL package in the versions data source.  Alas this is not correct. JDBC is not like ODBC with the placement of SQL Packages for JDE, essentially the SQL package seems to be placed in the library that is first accessed by the connection – this seems to be often control tables to find the menus.  So you’ve got all sorts of access plans being stored in the control tables SQL Package.  Or what it might be is stored in the library where that particular connection went to first – still control tables in my scenario.

Despite the above being in central objects, we could not delete the central objects SQL packages, because they were locked – locked by the 6 production JVM’s no less.  Another reason why we thought that this was the panacea that we were looking for.

Of course we delete these SQL package with an outage and then started to get the errors again after some time of the system being up (you know why now, the new connections [due to JDBJ connection pooling were going to a different library first and therefore starting to use the corrupt SQL package and bombing out!]. 

See the above on the menus being broken.

Remember that this can get really bad, that is when you need to build the AS/400 cross reference.  I’ve been there a number of times.  This is when you cannot query the existence of a able through the catalogs (QSYS2/SYSTABLES), but the file exists.  It means that the AS/400 catalogs are broken and the only way out is a rebuild – yuk!

But remember that you can TEST the database cross reference file by following step 1 below.  At least this means that you’ll know whether you need to organise some downtime.  If you need the downtime, then step 2 is your next step.  I’d delete every NOT Q SQL package and the one that is listed in step 2.

1. Corrupted Database Cross Reference file
   Use command GO LICPGM, then option 5 (prepare for install), then option 1 on Verify System Objects.  The following results can come up:
   • Task to prepare for install successfully completed - - - is a good result
   • Error occurs during prepare for install - - - rebuild DB Cross Reference file or run RCLSTG *DBXREF from a restricted state to rebuild the system-cross reference files.  On an AS400, use commands: ENDSBS *ALL   and  RCLSTG SELECT(*DBXREF)
2. Corrupted Database Host Servers SQL package
   • Delete IBM SQL package QZDAPKG from QGPL library - - - QZDAPKG on an AS400 can become unusable because of corruption, size limitations, or an incompatibility between releases.  All QZDASOINIT, QZDASSINIT, and QZDAINIT jobs hold a lock on QZDAPKG.  To delete QZDAPKG use these steps:
     • ENDHOSTSVR *DATABASE
     • ENDPJ QUSRWRK QZDASOINIT *IMMED
     • ENDPJ QUSRWRK QZDASSINIT *IMMED
     • ENDPJ QSERVER QZDAINIT *IMMED
     • DLTSQLPKG QGPL/QZDAPKG
     • STRHOSTSVR *DATABASE
     • STRPJ QSERVER QZDAINIT
3. Obtain an AS400 Joblog which should provide further diagnostic information.

What’s my wrap – similar to last time.  Unpredictable behaviour and no changes made in the application, reach for your closest SQL Package and remove it (not if it starts with Q – be VERY careful with those!).

Oracle Application Testing Suite (OATS) additional Linux monitoring commands serverstats

I do a lot of performance testing with JD Edwards.  It’s vital to record all of the machine “metrics” along with all of the test results.  Why is this vital?  So that when you run tests next time or when you want to check what the CPU / disk / memory was like under load – you can go back and check.

serverstats is the terminology that OATS has for this. 

Above is the menu items under the server stats configuration menu.

There are many COOL prebuilt templates “metric profiles” for gathering vital performance statistics, but I’ve quite often needed a little more from a unix perspective.

Metrics

A screen of the metrics that make up a metric profile

An option that I love (because I love em12c [how’s that for nerdy passion]) is that OATs is smart enough to be able to hook into EM12C for performance metrics – that is terrific.  Therefore setup enterprise manager as a datasource

But this is all nice to know, but not really meeting the title of my post…  How to augment the pretty basic and INCORRECT list of linux counters that OATS gives you out of the box.

They are a little too basic (but work without the labyrinthine SNMP traps for performance – thank goodness!)

I want io information, I want all of the vmstat command

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0 303860 437712 248572 9278632    0    0     1    11    0    0  4  1 91  5  0
0  0 303860 437712 248572 9278632    0    0     0    26  520 1009  0  0 98  2  0

I want swap information, IO, system and CPU, so I need to create some custom metrics to record the information that I need.

So clone an existing metric

Like the above.  At the end of the day, the combination of the command line, regexp and key value items allow OATS to record a single numeric value that is coming back from the OS and record this at a user define time period.

There are a couple of issues with how Oracle define these:

Firstly their value for memory(free) is wrong, it’s actually mapped to (buff) on OEL.  So they need to fix that.

Secondly the default counters are an average for 1 second taken every 5 seconds…  Perhaps it should be a 5 second average taken every 5 seconds – especially when dealing with io counts and swap io counts…  Does not matter so much for %ages.

So, if you wanted io in (in blocks), then ask for field 8, because the array starts at 0 for the perl regexp.

/(?:\S+\s+){8,8}([0-9]+)/

Everything else can stay the same, but now you have io – great!

Therefore, you can work through your list of columns and iostat options to create the values that you want graphed while you are running your performance tests, that was not hard now was it?

FIELD DESCRIPTION FOR VM MODE

   Procs

   0    r: The number of processes waiting for run time.

    1   b: The number of processes in uninterruptible sleep.

   Memory

    2   swpd: the amount of virtual memory used.

   3    free: the amount of idle memory.

   4    buff: the amount of memory used as buffers.

   5    cache: the amount of memory used as cache.

   Swap

   6    si: Amount of memory swapped in from disk (/s).

   7    so: Amount of memory swapped to disk (/s).

   IO

       bi: Blocks received from a block device (blocks/s).

       bo: Blocks sent to a block device (blocks/s).

   System

       in: The number of interrupts per second, including the clock.

       cs: The number of context switches per second.

   CPU

       These are percentages of total CPU time.

       us: Time spent running non-kernel code. (user time, including nice time)

       sy: Time spent running kernel code. (system time)

       id: Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.

       wa: Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.

       st: Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown.

start E1 instance via service on windows

 

There are a number of ways of doing this, this is just one simple one.

Create a cmd script in your F:\oracle\wls\wlserver\server\bin (equiv dir)

Call it “RegisterE1Service.cmd” – enter some details like the below:

echo off
SETLOCAL
set DOMAIN_NAME=E1_Apps
set USERDOMAIN_HOME=f:\oracle\wls\user_projects\domains\E1_Apps
set SERVER_NAME=JAS_739_88
set PRODUCTION_MODE=false
set JAVA_OPTIONS=-Dweblogic.Stdout="f:\oracle\wls\user_projects\domains\E1_Apps\stdout.txt" -Dweblogic.Stderr="f:\oracle\wls\user_projects\domains\E1_Apps\stderr.txt"
set ADMIN_URL=http://nzaklevfn739:7001
set MEM_ARGS=-Xms1024m -Xmx1024m
call "f:\oracle\wls\wlserver\server\bin\installSvc.cmd"
ENDLOCAL

run it – job done!

Start it!

Do the same for AdminServer

Note, getting more complex…  do this to get your dependencies right

sc config "wlsvc E1_Apps_JAS_739_88" depend= "wlsvc E1_Apps_AdminServer"

and also ensure that the node manager is running

sc config "wlsvc E1_Apps_JAS_739_88" depend= "Oracle Weblog
ic E1_Apps NodeManager (F_oracle_wls_wlserver)"

sc config "wlsvc E1_Apps_Adminserver" depend= "Oracle Weblo
gic E1_Apps NodeManager (F_oracle_wls_wlserver)"

okay, now things should start nicely.

registering the correct node manager service with WLS 12c

Have you had problems starting your JDE containers / JVM’s from SM and need to know what you need to start to get this working?  Don’t know which services to register to get things running?  This post might help you!

There is a nodemanager in wls_home\server\bin, this in not the right one.

There are a series of cms files here which allow you to think that you can register your node manager, but you cannot.  I guess it helps to think about these things at a higher level to understand what you are looking at.  

You need an admin server to log into and also to run commands to your node managers.

You need node managers to look after domains and start and stop the applications within domains, this is sometimes referred to as a machine.

It seems to me that server manager needs both the admin server and the nodegent to start a JDE HTML server, but it does not need the nodeagent to start the admin server.  This is interesting.  As SM must talk with the admin server, which then sends the commands to the nodeManager.

installNodeMgrSvc.cmd
installSvc.cmd
setWLSEnv.cmd
startNodeManager.cmd
uninstallNodeMgrSvc.cmd
uninstallSvc.cmd

They are not the ones that you want to run.

you actually want to be in your domain home, something like:

F:\oracle\wls\user_projects\domains\E1_Apps\bin

You see the following files in this dir:

installNodeMgrSvc.cmd
setDomainEnv.cmd
setStartupEnv.cmd
startComponent.cmd
startManagedWebLogic.cmd
startNodeManager.cmd
startWebLogic.cmd
stopComponent.cmd
stopManagedWebLogic.cmd
stopWebLogic.cmd
uninstallNodeMgrSvc.cmd

You can then setDomainEnv.cmd and then install your service.

See that the service is created with the name or your domain!

So now you can user SM to start and stop servers that have been installed in this domain.

oracle trigger permissions

Wow, this is a frustrating area.  Now, if you are a CNC person and perhaps not an ex DBA, there might be something to learn here.  Oracle triggers are amazing, DBA’s hate them because they run code that they cannot trace and follow.

You can do some really cool things with triggers and they can be triggered on more than the old, before update, after insert.

One thing that you need to understand though, it how permissions work with a trigger.

If you create trigger TESTDTA.MyFirstTrigger, it’ll run using TESTDTA permissions – not the user that fires off the trigger!  How about that.  So if you trigger is doing selects, calling procedures – you need to ensure that the owner / schema has permissions to do all of the stuff that you want the trigger to do.

So, my scenario was that I had a non public database link.  I needed to create the link as TESTDTA, so that I could use the link in the trigger body.

So, a fairly simple post.

I’ve got some cool triggers that tell you when there are too many waiting jobs.  The database will email you all the details of the waiting and processing jobs so that you may be able to perform some maintenance!

Testing BSSV with 910 and WLS JD Edwards web services

This is a simple post that might help you get some well formed XML for testing BSSV with SoapUI so you can ensure that the painful build and deploy process has been successful.

In my example I have an endpoint created, port 9546 on e1web2.

My server manager has

In Soap UI, right click projects and select new project

Choose a name “Testing”

Then enter the URL “https://e1web2.mits.local:9546/DV910/VoucherMatchManager?WSDL” for example

This will create the following:

Note that you are also given a sample request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:orac="http://oracle.e1.bssv.JP43B000/" xmlns:java="java:oracle.e1.bssv.JP43B000.valueobject" xmlns:java1="java:oracle.e1.bssv.util.J0100010.valueobject" xmlns:java2="java:oracle.e1.bssv.util.J4100010.valueobject">
   <soapenv:Header/>
   <soapenv:Body>
      <orac:processVoucherMatchElement>
         <java:Header>
            <java:ProcessingVersion>?</java:ProcessingVersion>
            <java:SupplierInvoiceNumber>?</java:SupplierInvoiceNumber>
            <java:Company>?</java:Company>
            <java:Supplier>
               <java1:EntityId>?</java1:EntityId>
               <java1:EntityLongId>?</java1:EntityLongId>
               <java1:EntityTaxId>?</java1:EntityTaxId>
            </java:Supplier>
            <java:RemitTo>
               <java1:EntityId>?</java1:EntityId>
               <java1:EntityLongId>?</java1:EntityLongId>
               <java1:EntityTaxId>?</java1:EntityTaxId>
            </java:RemitTo>
            <java:PurchaseOrderKey>
               <java:DocumentNumber>?</java:DocumentNumber>
               <java:DocumentTypeCode>?</java:DocumentTypeCode>
               <java:DocumentCompany>?</java:DocumentCompany>
            </java:PurchaseOrderKey>
            <java:Dates>
               <java:DateInvoice>?</java:DateInvoice>
               <java:DateAccounting>?</java:DateAccounting>
            </java:Dates>
            <!--Zero or more repetitions:-->
            <java:Detail>
               <java:CheckRemark>?</java:CheckRemark>
               <java:DatePaymentDue>?</java:DatePaymentDue>
               <java:PurchaseOrderLineKey>
                  <java:DocumentLineNumber>?</java:DocumentLineNumber>
                  <java:DocumentLineNumberSuffix>?</java:DocumentLineNumberSuffix>
               </java:PurchaseOrderLineKey>
               <java:Item>
                  <java2:ItemId>?</java2:ItemId>
                  <java2:ItemProduct>?</java2:ItemProduct>
                  <java2:ItemCatalog>?</java2:ItemCatalog>
                  <java2:ItemFreeForm>?</java2:ItemFreeForm>
                  <java2:ItemSupplier>?</java2:ItemSupplier>
               </java:Item>
               <java:Quantity>
                  <java:UnitOfMeasureCodeTransaction>?</java:UnitOfMeasureCodeTransaction>
                  <java:QuantityToPay>?</java:QuantityToPay>
               </java:Quantity>
               <java:FinancialDetail>
                  <java:CostUnit>?</java:CostUnit>
                  <java:CostExtended>?</java:CostExtended>
                  <java:AmountTax>?</java:AmountTax>
               </java:FinancialDetail>
            </java:Detail>
            <!--Zero or more repetitions:-->
            <java:AdditionalCharge>
               <java:Description1>?</java:Description1>
               <java:AdditionalChargeQty>?</java:AdditionalChargeQty>
               <java:LineTaxableCode>?</java:LineTaxableCode>
               <java:TaxExplanationCode>?</java:TaxExplanationCode>
               <java:TaxRateArea>?</java:TaxRateArea>
               <java:ExpenseAcctNumber>?</java:ExpenseAcctNumber>
               <java:LineTaxAmount>?</java:LineTaxAmount>
               <java:AdditionalChargeAmt>?</java:AdditionalChargeAmt>
            </java:AdditionalCharge>
            <java:FinancialHeader>
               <java:CurrencyCode>?</java:CurrencyCode>
               <java:RateExchangeOverride>?</java:RateExchangeOverride>
               <java:AmountTotalInvoice>?</java:AmountTotalInvoice>
               <java:AmountTax>?</java:AmountTax>
               <java:PaymentTermsCode>?</java:PaymentTermsCode>
            </java:FinancialHeader>
         </java:Header>
      </orac:processVoucherMatchElement>
   </soapenv:Body>
</soapenv:Envelope>

You just need to change the header information with the appropriate security information:

Note that the sample below is for Addressbook, but has the correct header for security.  This is for user JDE with password P@ssW0rd!

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:orac="http://oracle.e1.bssv.JP010000/">
<soapenv:Header>
   <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
     xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
     xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
     soapenv:mustUnderstand="1">
     <wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
       xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
       <wsse:Username>JDE</wsse:Username>  
       <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">P@ssW0rd!</wsse:Password> 
     </wsse:UsernameToken>
   </wsse:Security>
</soapenv:Header>
   <soapenv:Body>
      <orac:getAddressBookElement>
         <entity>
            <entityId>100</entityId>
         </entity>
      </orac:getAddressBookElement>
   </soapenv:Body>
</soapenv:Envelope>