Wednesday, 18 July 2018

Learning something new every day .... Where's my WAS Admin Console stuff for BPM ?

So today I'm working with a client to deploy IBM BPM Standard 8.6 alongside IBM Master Data Management 11.6, to leverage the Data Stewardship capabilities that the BPM/MDM tie-up offers.

In brief, this means that I'm installing the BPM binaries into an existing installation of WebSphere Application Server Network Deployment 8.5.5.13, which is hosting the MDM environment ( Deployment Manager, Node Agent and Application Server/Cluster ).

However, when I created the BPM Deployment Environment, using BPMConfig.sh, I'm specifying to use DIFFERENT WAS profiles, leaving the MDM stuff well alone.

So, for no obvious reason, I hit a small glitch with my Deployment Manager, having built the BPM Deployment Environment.

Whilst I can start/stop things, run up the Process Center/Portal/Admin UIs etc., I'm unable to see  the BPM-related capabilities within the Deployment Manager ( Integrated Solutions Console ), such as: -




etc. even though the ISC *DID* show BPM as being installed : -


( ignore the version in this example; I've got 8.5.6 on my VM, but 8.6.0 on the customer's environment )

I also checked that the profile was properly augmented: -

cat /opt/ibm/WebSphere/AppServer/properties/profileRegistry.xml
 
<?xml version="1.0" encoding="UTF-8"?><profiles>
    <profile isAReservationTicket="false" isDefault="true" name="Dmgr01" path="/opt/ibm/WebSphereProfiles/Dmgr01" template="/opt/ibm/WebSphere/AppServer/profileTemplates/management">
        <augmentor template="/opt/ibm/WebSphere/AppServer/profileTemplates/BPM/BpmDmgr"/>
    </profile>
    <profile isAReservationTicket="false" isDefault="false" name="AppSrv01" path="/opt/ibm/WebSphereProfiles/AppSrv01" template="/opt/ibm/WebSphere/AppServer/profileTemplates/managed">
        <augmentor template="/opt/ibm/WebSphere/AppServer/profileTemplates/BPM/BpmNode"/>
    </profile>


but to no avail.

More weird, I received something similar to this: -

ServletWrappe E com.ibm.ws.webcontainer.servlet.ServletWrapper service SRVE0014E: Uncaught service() exception root cause /com.ibm.ws.console.servermanagement/addPropLayout.jsp: com.ibm.websphere.servlet.error.ServletErrorReport: javax.servlet.jsp.JspException: Missing message for key "addprops.category.label.businessintegration"

when I attempted to navigate here: -


and the right-hand side of the page merely contained that exception.

Thankfully this post had the answer: -


specifically this bit: -

3) run the command to restore admin console application: [Dmgr profile]/bin/iscdeploy.sh -restore
...

So I did the needful: -

/opt/ibm/WebSphereProfiles/Dmgr01/bin/iscdeploy.sh 

having shut down ALL the JVMs, and magically it fixed the problem.

I'm not sure how I got here, but glad I found a fix.

Thanks, Internet, you rock !



Friday, 13 July 2018

IBM Cloudant - Another useful source

I've referenced Glynn Bird here before, as the author of the most excellent couchimport, couchbackup and couchrestore tools.

Here's Glynn's personal site: -


including even more lovely CouchDB ( Cloudant ) goodness.

Wednesday, 11 July 2018

Microsoft Excel - Generating test data

We had a requirement to generate some test data within a Microsoft Excel spreadsheet.

similar to this: -


This was the magic invocation: -

="Subscriber Reference #"&ROW()

We merely needed to copy that to the clipboard, and paste it into a nice chunk 'o cells ….

So it's a piece of text concatenated with the row() index, using the ampersand ( & ) symbol.


IBM BPM and Oracle - Bootstrap challenges

So, whilst running the bootstrap command: -

/opt/ibm/WebSphereProfiles/Dmgr01/bin/bootstrapProcessServerData.sh -clusterName AppCluster

I saw this: -

java.lang.Exception: java.lang.reflect.InvocationTargetException
Caused by: java.lang.reflect.InvocationTargetException
Caused by: java.lang.IllegalStateException: Failed to initialize registry
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'handlersMap': Cannot create inner bean 'com.lombardisoftware.server.ejb.persistence.PSDefaultHandler#f4e9076' of type [com.lombardisoftware.server.ejb.persistence.PSDefaultHandler] while setting bean property 'sourceMap' with key [TypedStringValue: value [Branch], target type [null]]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.lombardisoftware.server.ejb.persistence.PSDefaultHandler#f4e9076' defined in class path resource [registry.persistence.xml]: Cannot resolve reference to bean 'dao.branch' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dao.branch' defined in class path resource [registry.persistence.xml]: Instantiation of bean failed; nested exception is java.lang.ExceptionInInitializerError

etc.

So I checked the log: -

view /opt/ibm/WebSphereProfiles/Dmgr01/logs/bootstrapProcesServerData.AppCluster.log

and saw this: -

[11/07/18 12:20:27:801 BST] 00000001 ProviderTrack I com.ibm.ffdc.osgi.ProviderTracker AddingService FFDC1007I: FFDC Provider Installed: com.ibm.ffdc.util.provider.FfdcOnDirProvider@cfa421b1
[11/07/18 12:20:28:470 BST] 00000001 LocalCache    I   CWLLG2155I:  Cache settings read have been from file file:////opt/ibm/WebSphere/AppServer/BPM/Lombardi/process-server/twinit/lib/basic_resources.jar!/LombardiTeamWorksCache.xml.
[11/07/18 12:20:28:768 BST] 00000001 XmlBeanDefini I org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
[11/07/18 12:20:28:784 BST] 00000001 SQLErrorCodes I org.springframework.jdbc.support.SQLErrorCodesFactory <init> SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
[11/07/18 12:20:28:787 BST] 00000001 SQLErrorCodes W org.springframework.jdbc.support.SQLErrorCodesFactory getErrorCodes Error while extracting database product name - falling back to empty error codes
                                 org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory, cause: IO Error: The Network Adapter could not establish the connection


I checked that the Oracle 12c listener appeared to be running: -

netstat -aon|grep LISTEN

tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp6       0      0 :::36875                :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::22                   :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 ::1:25                  :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::5500                 :::*                    LISTEN      off (0.00/0/0)


lsnrctl status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-JUL-2018 12:42:50

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                10-JUL-2018 20:22:19
Uptime                    0 days 16 hr. 20 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/bpm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=bpm.uk.ibm.com)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


Notice the problem ?

Both the netstat and the lsnrctl commands show that the listener is "bound" to 127.0.0.1 rather than to the server's hostname.




lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-JUL-2018 12:44:58

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bpm.uk.ibm.com)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully


 lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-JUL-2018 12:45:09

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/bpm/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bpm.uk.ibm.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bpm.uk.ibm.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                11-JUL-2018 12:45:09
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/bpm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bpm.uk.ibm.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


I then saw this: -

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

which I solved via a previous blog post: -

So, the conclusion is that the Listener needs to know / care about the host name of the box: -

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bpm.uk.ibm.com)(PORT=1521)))

whereas the BPM -> Oracle connectivity needs to know / care about the Oracle Service Name: -

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 11 15:06:23 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------
orcl.uk.ibm.com

SQL>
exit

so the JDBC data sources need to look like this: -


This needs to be reflected in the BPM Deployment Environment properties file: -

bpm.de.db.1.databaseName=orcl.uk.ibm.com
bpm.de.db.2.databaseName=orcl.uk.ibm.com
bpm.de.db.3.databaseName=orcl.uk.ibm.com

So now we have this: -

/opt/ibm/WebSphereProfiles/Dmgr01/bin/bootstrapProcessServerData.sh -clusterName AppCluster

Bootstraping data into cluster AppCluster and logging into /opt/ibm/WebSphereProfiles/Dmgr01/logs/bootstrapProcesServerData.AppCluster.log

WASX7357I: By request, this scripting client is not connected to any server process. Certain configuration and application operations will be available in local mode.
'BootstrapProcessServerData admin command completed successfully.....'

Saturday, 7 July 2018

IBM AppConnect and DB2 and SQL0530N

I'm fiddling about with IBM AppConnect Professional ( formerly known as CastIron ), looking at the integration between a flow running on AppConnect, hosted on the IBM Cloud ( nee Bluemix ) and a DB2 database running on a VM on my Mac.

I'll be writing another blog post about the actual integration, including the Secure Gateway later.

Meantime, I wanted to test my flow, which should be monitoring a table for changes.

I did this by inserting a new row into the EMPLOYEE table of the SAMPLE database ( which has been around since I worked on DB2/400 in the mid-90s ).

This is what that table looks like: -

db2 describe "select * from DB2INST1.EMPLOYEE"

 Column Information

 Number of columns: 14

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 452   CHARACTER                 6  EMPNO                                     5
 448   VARCHAR                  12  FIRSTNME                                  8
 453   CHARACTER                 1  MIDINIT                                   7
 448   VARCHAR                  15  LASTNAME                                  8
 453   CHARACTER                 3  WORKDEPT                                  8
 453   CHARACTER                 4  PHONENO                                   7
 385   DATE                     10  HIREDATE                                  8
 453   CHARACTER                 8  JOB                                       3
 500   SMALLINT                  2  EDLEVEL                                   7
 453   CHARACTER                 1  SEX                                       3
 385   DATE                     10  BIRTHDATE                                 9
 485   DECIMAL                9, 2  SALARY                                    6
 485   DECIMAL                9, 2  BONUS                                     5
 485   DECIMAL                9, 2  COMM                                      4


This is what I ran: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','ABC','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

which returned: -

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0530N  The insert or update value of the FOREIGN KEY 
"DB2INST1.EMPLOYEE.RED" is not equal to any value of the parent key of the 
parent table.  SQLSTATE=23503

which baffled me somewhat.

I dug into the database further: -

db2look -d sample -e -t db2inst1.employee

CREATE TABLE "DB2INST1"."EMPLOYEE"  (
  "EMPNO" CHAR(6 OCTETS) NOT NULL , 
  "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , 
  "MIDINIT" CHAR(1 OCTETS) , 
  "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , 
  "WORKDEPT" CHAR(3 OCTETS) , 
  "PHONENO" CHAR(4 OCTETS) , 
  "HIREDATE" DATE , 
  "JOB" CHAR(8 OCTETS) , 
  "EDLEVEL" SMALLINT NOT NULL , 
  "SEX" CHAR(1 OCTETS) , 
  "BIRTHDATE" DATE , 
  "SALARY" DECIMAL(9,2) , 
  "BONUS" DECIMAL(9,2) , 
  "COMM" DECIMAL(9,2) )   
 IN "USERSPACE1"  
 ORGANIZE BY ROW; 


-- DDL Statements for Primary Key on Table "DB2INST1"."EMPLOYEE"

ALTER TABLE "DB2INST1"."EMPLOYEE" 
ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY
("EMPNO");



-- DDL Statements for Indexes on Table "DB2INST1"."EMPLOYEE"

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "DB2INST1"."XEMP2" ON "DB2INST1"."EMPLOYEE" 
("WORKDEPT" ASC)

COMPRESS NO 
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Aliases based on Table "DB2INST1"."EMPLOYEE"

CREATE ALIAS "DB2INST1"."EMP" FOR TABLE "DB2INST1"."EMPLOYEE";


-- DDL Statements for Foreign Keys on Table "DB2INST1"."EMPLOYEE"

ALTER TABLE "DB2INST1"."EMPLOYEE" 
ADD CONSTRAINT "RED" FOREIGN KEY
("WORKDEPT")
REFERENCES "DB2INST1"."DEPARTMENT"
("DEPTNO")
ON DELETE SET NULL
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
...

which showed me the error of my way.

In essence, the WORKDEPT column is actually keyed against a different table: -

db2 describe "select * from DB2INST1.DEPARTMENT"

 Column Information

 Number of columns: 5

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 452   CHARACTER                 3  DEPTNO                                    6
 448   VARCHAR                  36  DEPTNAME                                  8
 453   CHARACTER                 6  MGRNO                                     5
 452   CHARACTER                 3  ADMRDEPT                                  8
 453   CHARACTER                16  LOCATION                                  8

db2 "select * from DB2INST1.DEPARTMENT"

DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION        
------ ------------------------------------ ------ -------- ----------------
A00    SPIFFY COMPUTER SERVICE DIV.         000010 A00      -               
B01    PLANNING                             000020 A00      -               
C01    INFORMATION CENTER                   000030 A00      -               
D01    DEVELOPMENT CENTER                   -      A00      -               
D11    MANUFACTURING SYSTEMS                000060 D01      -               
D21    ADMINISTRATION SYSTEMS               000070 D01      -               
E01    SUPPORT SERVICES                     000050 A00      -               
E11    OPERATIONS                           000090 E01      -               
E21    SOFTWARE SUPPORT                     000100 E01      -               
F22    BRANCH OFFICE F2                     -      E01      -               
G22    BRANCH OFFICE G2                     -      E01      -               
H22    BRANCH OFFICE H2                     -      E01      -               
I22    BRANCH OFFICE I2                     -      E01      -               
J22    BRANCH OFFICE J2                     -      E01      -               

  14 record(s) selected.


My insert: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','ABC','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

is using a DIFFERENT and NON-EXISTENT code ( ABC ) for WORKDEPT.

I changed my insert to: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','A00','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

and it all worked: _

DB20000I  The SQL command completed successfully.

Yay !

IBM API Connect V2018.3.1 is available

This just in: -

IBM API Connect V2018.3.1 is now available. This update includes important internal development fixes and support for the API Designer as part of the toolkit.

Content

IBM API Connect 2018.x delivers enhanced capabilities for the market-leading IBM API management solution. In addition to the ability to deploy in complex, multi-cloud topologies, this version provides enhanced experiences for developers and cloud administrators at organizations.

The API Connect 2018.3.1 update includes important internal development fixes. In addition, this release includes the API Designer within the toolkit. API developers use the API management functions in the API Designer or the CLI to create draft API definitions for REST and SOAP APIs, or for OAuth provider endpoints that are used for OAuth 2.0 authentication. The API definitions can be configured to add the API to a Product, add a policy assembly flow (to manipulate requests/responses), and to define security options and other settings. APIs can then be tested locally prior to publishing, to ensure they are defined and implemented correctly.

Upgrading to 2018.3.1 makes changes to the underlying data structure of API Connect.  It is highly recommended to have automatic backups configured in your environment and at least one successful backup complete prior to performing this upgrade.

We advise all users of IBM API Connect 2018.1.x and earlier versions of IBM API Connect 2018.2.x to install this update to take advantage of the fixes.

Friday, 6 July 2018

Doofus Alert - Using Cloudant queries via cURL

I'm continuing to tinker with Cloudant, and am looking at how I can use indexes ( indices ? ) via the command-line using cURL.

This is what I'm sending: -

curl -X POST -H 'Content-type: application/json' -g $COUCH_URL/$COUCH_DATABASE/_find -d query.json

and this is what I'm seeing: -

{"error":"bad_request","reason":"invalid UTF-8 JSON"}

I checked my query: -

cat query.json 

{
   "selector": {
      "$or": [
         {
            "givenName": "Maggie"
         },
         {
            "givenName": "Lisa"
         }
      ]
   },
   "fields": [
      "givenName",
      "familyName"
   ],
   "sort": [
      {
         "givenName": "asc"
      }
   ]
}

and it looks OK.

Thankfully, this chap had already hit the same: -



Yep, that's exactly where I went wrong ….

I changed my query: -

curl -X POST -H 'Content-type: application/json' -g $COUCH_URL/$COUCH_DATABASE/_find -d @query.json

and … guess what ….

IT WORKED

{"docs":[
{"givenName":"Lisa","familyName":"Simpson"},
{"givenName":"Maggie","familyName":"Simpson"}
],
"bookmark": "g1AAAAA9eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYozGoIkOGASOSAhkDibb2J6emZqVhYA5ooQDg"}

Can you say "Doofus" ? I bet you can ….