Installing Oracle XE and SQL Plus on CentOS

Dependencies

rpm --install libaio-0.3.107-10.el6.x86_64.rpm

rpm --install bc-1.06.95-1.el6.x86_64.rpm

Oracle XE Install and post config (note, you need to have populated xe.rsp)

rpm --install oracle-xe-11.2.0-1.0.x86_64.rpm

/etc/init.d/oracle-xe configure responseFile=xe.rsp

Instant Client and SQLPlus install

rpm --install oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

rpm --install oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

Environment setup

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

export ORACLE_SID=XE

Test with

/usr/lib/oracle/11.2/client64/bin/sqlplus system/oracle@XE

Resources

http://pkgs.org

http://www.oracle.com

Some basic Oracle-xe and Sqlplus notes

Query

----------

sqlplus <user>/<password>@<SID> <<EDF

select * from dba_users;

EOF

Setting the editor

-------------------------------

SQL> define_editor=’vi’

Oracle XE Status / stop / start

-----------------------

  /etc/init.d/oracle-xe status (as root)

   lsnrctl status (as oracle user)

   lsnrctl start (as oracle user)

Oracle XE Install on Ubuntu
---------------------------------
Download oracle-xe-universal_10.2.0.1-1.1_i386.deb
/opt/oracle/src/oracle-xe-universal_10.2.0.1-1.1_i386.deb
edit /etc/apt/sources.list as follows...
deb file:/opt/oracle/src raring main
Create a Packages.gz file as follows...
cd /opt/oracle/src
dpkg-scanpackages . | gzip > /opt/oracle/src/dists/raring/main/binary-i386/Packages.gz

Update apt cache...
apt-get update

Install ...
apt-get install oracle-xe-universal
ureadahead will be reprofiled on next reboot
Setting up oracle-xe-universal (10.2.0.1-1.1) ...
update-rc.d: warning: /etc/init.d/oracle-xe missing LSB information
update-rc.d: see <http://wiki.debian.org/LSBInitScripts>
Executing Post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database
HTTP Admin port = 40000
DB Port = 40521
password for SYS and SYSTEM = oracle123
started on boot = no
The DB is now started .. and it's running as user 'oracle'. not sure how it knew to run as this user, I don't recall

providing the installer that info.

Now it's installed we need to run it
------------------------------------
Already running as user 'oracle'. not sure how it knew to run as this user, I don't recall providing the installer that
info.

Install and Configure SQL Plus on *NIX

1./ Download SQL Plus and InstantClient from the Oracle site
Open a browswer and go to http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
The following packages are required

• SQLPlus: Additional libraries and executable for running SQL*Plus with Instant Client (e.g. instantclient-sqlplus-solaris.sparc64-12.1.0.1.0.zip)
• Basic Lite: Smaller version of the Basic, with only English error messages  (e.g. instantclient-basiclite-solaris.sparc64-12.1.0.1.0.zip)

2./ Copy these archves to the target machines, create a directory (e.g. /opt/bpm/scripts/database) and unpack the above packages in this directory 

3./ Set and export the following enviroment variables to point at the instantclient_<version> directory. For example...

• export LD_LIBRARY_PATH=/opt/bpm/scripts/database/instantclient_12_1:${LD_LIBRARY_PATH}
• export SQLPATH=/opt/bpm/scripts/database/instantclient_12_1:${SQLPATH}
• export TNS_ADMIN=/opt/bpm/scripts/database/instantclient_12_1

4./ Test basic operation 

• cd /opt/bpm/scripts/database*
• ./sqlplus

If the runtime is working correctly this message will be displayed

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 1 10:20:04 2013

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

Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

5./ Set up tnsnames.ora

• Create the file /opt/bpm/scripts/database/instantclient<version>/tnsnames.ora - e.g. /opt/bpm/scripts/database/instantclient_12_1/tnanames.ora
• Add an entry for each database. For example ... 

DATABASE_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jupiter.stack1.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BPM_UAT)
    )
  )

DATABASE_2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = saturn.stack1.com)(PORT = 1725))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = BPM_DEV)
    )
  )

6./ Test connectivity

• cd /opt/bpm/scripts/database
• ./sqlplus <user>/<password>@<database name>

For example

• ./sqlplus BPMN_USER/********#@DATABASE_1

You should now be logged in to the desired database as the specified user and be sitting at an SQL> prompt.

7./ Compete the test by querying the database... 

• select USERNAME from ALL_USERS where username like '%BPM' order by username; 

How to monitor tablespace usage in Oracle

You need a user with access to the following tables

    dba_free_space
    dba_data_files
    dba_tablespaces

If you get your DBA to create this user for you, here's the SQL...

SQL> create user monitor identified by BPM123bpm;
SQL> grant create session, select any table, select any dictionary to monitor;

Now you can run queries like this...
- To show the size of each tablespace...

select tablespace_name,sum(bytes) from dba_data_files group by tablespace_name;

- To show the actual usage of each tablespace...

select tablespace_name,sum(bytes) from dba_segments group by tablespace_name;

- Or a nice script that somebody gave me is below...

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
/
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_temp_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ; 

Oracle SID vs SERVICE url

I don't proclaim to know any details about this, but someone mentioned it and I know I'm going to need this info one day, so I'm writing it down

This is a Service
jdbc:oracle:thin:@//10.7.12.3:1521/BPM

This is a SID
jdbc:oracle:thin:@10.7.12.3:1521:BPM

Execute an SQL script file in Oracle SQLPlus

From within SQLPlus...
SQL >  @myScript.sql

Invoking SQLPlus with SQL input
sqlplus <user>/<password>@<SID> @myScript.sql

Creating Oracle roles, users and tablespaces for BPM 75 Advanced

Example...


CREATE ROLE BPMN_GOLD_OWNER_ROLE;
GRANT CREATE CLUSTER to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE INDEXTYPE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE MATERIALIZED VIEW to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE PROCEDURE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SEQUENCE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SESSION to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SYNONYM to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TABLE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TRIGGER to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TYPE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE VIEW to BPMN_GOLD_OWNER_ROLE;
GRANT JAVAUSERPRIV to BPMN_GOLD_OWNER_ROLE;

--===============================================================

CREATE ROLE BPMN_GOLD_PDWOWNER_ROLE;
GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLD_PDWOWNER_ROLE;
GRANT EXECUTE ON SYS.DBMS_LOCK TO BPMN_GOLD_PDWOWNER_ROLE;

--===============================================================

CREATE ROLE BPMN_GOLD_XAOWNER_ROLE;
GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON SYS.PENDING_TRANS$ to BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON DBA_2PC_PENDING to BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON DBA_PENDING_TRANSACTIONS to BPMN_GOLD_XAOWNER_ROLE;
GRANT EXECUTE ON DBMS_XA to BPMN_GOLD_XAOWNER_ROLE;

--===============================================================
-- Common DB user / role / tablespace
--===============================================================

CREATE TABLESPACE "BPMN_GOLDCOM_DATA01"
DATAFILE '+DATA/BPM/BPMN_GOLDCOM_DATA01.dbf'
SIZE 2000M reuse
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- Creating schema for CommonDB
CREATE USER BPMN_GOLDCOM_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDCOM_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDCOM_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDCOM_OWNER;


--===============================================================
-- ProcessChoreograpther DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPCH_2_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPCH_2_DATA01.dbf'
SIZE 16000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDPCH_2_OWNER IDENTIFIED BY <some password>
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE BPMN_GOLDPCH_2_DATA01
QUOTA UNLIMITED ON BPMN_GOLDPCH_2_DATA01 ;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPCH_2_OWNER;

--===============================================================
-- ProcessServer DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPSS_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPSS_DATA01.dbf'
SIZE 15000M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

-- Create schema owner if the user does not already exist --
CREATE USER BPMN_GOLDPSS_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDPSS_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDPSS_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPSS_OWNER;

--===============================================================
-- PerformanceDW DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPDW_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPDW_DATA01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDPDW_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDPDW_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDPDW_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPDW_OWNER;
GRANT BPMN_GOLD_PDWOWNER_ROLE TO BPMN_GOLDPDW_OWNER;

--===============================================================

-- BusinessSpace DB user / role / tablespace

--===============================================================

CREATE TABLESPACE BPMN_GOLDBSP_DATA01

DATAFILE '+DATA/BPM/BPMN_GOLDBSP_DATA01.dbf'

SIZE 2000M reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDBSP_OWNER IDENTIFIED BY <some password>

DEFAULT TABLESPACE BPMN_GOLDBSP_DATA01

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON BPMN_GOLDBSP_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDBSP_OWNER;

--===============================================================

-- Messaging Enginer DB users / roles / tablespaces

--===============================================================

-- Creating Tablespace for Messaging

CREATE TABLESPACE "BPMN_GOLDAPPM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDAPPM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDAPPM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDAPPM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDAPPM_2_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDBPCM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDBPCM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDBPCM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDBPCM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDBPCM_2_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDCEIM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDCEIM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDCEIM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDCEIM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDCEIM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDPDWM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDPDWM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDPDWM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDPDWM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDPDWM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDPSSM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDPSSM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDPSSM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDPSSM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDPSSM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDSYSM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDSYSM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDSYSM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDSYSM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDSYSM_2_DATA01 ;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDAPPM_2_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDBPCM_2_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDCEIM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPDWM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPSSM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDSYSM_2_OWNER;

--===============================================================

-- XA Recovery DB user / role / tablespace

--===============================================================

CREATE TABLESPACE "BPMN_GOLDXAR_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDXAR_DATA01.dbf'

SIZE 400M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

-- Creating schema for CommonDB XA

CREATE USER BPMN_GOLDXAR_OWNER IDENTIFIED BY <some password>

DEFAULT TABLESPACE BPMN_GOLDXAR_DATA01

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON BPMN_GOLDXAR_DATA01;

--Gran Role BPM_COMMON

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDXAR_OWNER;

GRANT BPMN_GOLD_XAOWNER_ROLE TO BPMN_GOLDXAR_OWNER;

Connect to Oracle DB using SQLPLus without a tnsnames.ora

Run ....

sqlplus  /nolog

This will place you in the SQL prompt without logging on.

From here you can run the conn command

For example...

SQL> conn clarkeb@'(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)
RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=jupiter1)(PORT=1621)))(CONNECT_DATA=(SERVICE_NAME=BPM_DB)))(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=jupiter2)(PORT=1621)))(CONNECT_DATA=(SERVICE_NAME=BPM_DB))))'


Lombardi BPD not starting task

I had a Simulate User Task activity which I tried to assign to a List of Users - i.e. under Routing, the drop down was set to List of Users which contained 2 LDAP users and 1 user from the local repository (in this case tw_admin). When I ran this BPD, the user task simply didn't run. No errors in the SystemOut or SystemErr logs, nothing.  However, if I took the local repository user our of the List of Users it all worked fine. The key difference between the two users was their OU. Needs further investigation

Nudging stuck task in BPM 7.5 Event Manager

If you have stuck tasks in the EM async queue, setting the EM instance to null and the task status to 1 followed by a pause and resume of all EM instances will cause the other EM instance/instances to pick up the tasks. It still means the EM instance in question won't pick up further jobs as at this stage it's broken. Maybe i'll be able to work out why it breaks and how to fix it at some point, but for now this workaround will get you out of trouble.

Here's the database query you need to do this

UPDATE lsw_em_task SET task_status = 1, task_owner = null WHERE task_status = 3 AND task_owner = <the id of your EM instance>


Note1: In SQL Developer the date columns in the Lombardi tables only display the date. To display the time as well you need to run something like select to_char(scheduled_time, 'hh24:mi:ss') from lsw_em_task. (or you could always change the NLS settings in SQL Developer to ensure DATE columns display timestamps Tools > preferences > database > NLS - in the Date format field specify DD-MON-RR  HH24.MI.SS
Note2: The ID of your EM instance(s) can be found in the LSW_EM_INSTANCE table

Drop all tables for a specified user in Oracle

Begin
for a in (select table_name from user_tables) loop
execute immediate ('drop table '||a.table_name||' cascade constraints');
end loop;
End;

Checking table privileges in Oracle 11g

SELECT distinct grantee, table_name, select_priv FROM table_privileges

BPM Version 8 Standard Edition Databases

Version 8 Standard Edition uses 4 Databases

1. BPMDB - The Process Centre / Process Server databases
2. PDWDB - The Performance Data Warehouse Database
3. BSPDB - The Business Space Database (New in version 8, Business Space used to only be part of Advanced in v 7.x)
4. MEDB - The Messaging Engine Database (In v7.x the ME tables were schema's under PDWDB and BPMDB

For some reason the documentation states that the Common Database is needed, but as far as I can tell it's not. There are no datasources configured for it during any part of the installation.

Example DB2 commands for creating the databases are as follows:

db2 create database <Database Name> automatic storage yes  using codeset UTF-8 territory GB pagesize 32768;

db2 connect to <Database Name>;

db2 grant dbadm on database to user <user name>;

db2 UPDATE DB CFG FOR <Database Name> USING LOGFILSIZ 4096 DEFERRED;

db2 UPDATE DB CFG FOR <Database Name> USING LOGSECOND 64 DEFERRED;

db2 connect reset;

Once the databases have been created, you can create the tables and stored procedures within by using the scripts generated when you imported and generated the deployment environment:
(Note - create database scripts are provided for all but MEDB, however in most cases your DBA's will want to create the databases, apply any table space settings specific their standards (as above) and set you up with a user with rights for you to create the tables etc. (Not sure why none were provided for MEDB by the way. I guess somebody just forgot ;) )

BPMDB <dmgr profile>/dbScripts/PerformanceDW/DB2/BPMDB 
 

• db2 connect to BPMDB 
• db2 -tvf createTable_ProcessServer.sql
• db2 -tvf createProcedure_ProcessServer.sql
• db2 terminate  

PDWDB <dmgr profile>/dbScripts/PerformanceDW/DB2/PDWDB

• db2 connect to PDWDB 
• db2 -tvf createTable_PerformanceDW.sql
• db2 terminate  

BSPDB <dmgr profile>/dbScripts/PerformanceDW/DB2/BSPDB

A wrapper script is provided, however I've forgotten it's name...
Will follow


MEDB

The MEDB DDL needs to be generated by the SIBDDLGenerator.sh script as follows

sibDDLGenerator [ -systemDBMS name ][ -versionDBMS version ][ -platformDBMS platform ][ -schemaschema name ][ -useruser name ][{ -create -drop }][ -databasedatabase ][ -storagegroupstorage group ][ -cataloghigh level qualifier ][ -bufferpoolbuffer pool ][ -statementendstatement end ][ -nolinebreaks ][ -noblanklines ][ -firstlinefirst line ][ -lastlinelast line ][ -permanentnumber ][ -temporarynumber ]

You'll need 2 sets of DDL, one for the Process Server ME's and one for the PDW ME's. 
I usually create a directory alongside the ones generated for the other databases (e.g. <dmgr profile>/dbScripts/PerformanceDW/DB2/MEDB) and then run the above command twice substituting the schema names each time to match the ones you specified in your Deployment Environment, and redirecting to a suitably named file each time

Configure JDBC connection pools with wsadmin

import re

for dataSource in AdminConfig.list('DataSource').splitlines():

        jndi= AdminConfig.showAttribute(dataSource, 'jndiName')

        if(re.search("PerformanceDB", jndi)):

                connPool=AdminConfig.showAttribute(dataSource, 'connectionPool')

                AdminConfig.modify(connPool, '[[maxConnections "200"]]')

AdminConfig.save() 

Setting prepared statement cache size on JDBC datasources wsadmin

import re 

for dsName in 'BPEDB PerformanceDB PDWDB TwqlDB TeamWorksDB ProcessDB WPSDB'.split(): 

        for dataSource in AdminConfig.list('DataSource').splitlines(): 

                jndi= AdminConfig.showAttribute(dataSource, 'jndiName') 

                if(re.search(dsName, jndi)): 

                        print "Setting prepared statement cache "+jndi 

                        AdminConfig.modify(dataSource, '[[statementCacheSize "100"]]') 

AdminConfig.save()