Bug 778747 (SOA-1204)

Link: Added: This issue related SOA-1205

Link: Added: This issue related SOA-630

Hi Len , Could you please explain why the (3) command should be removed ?  It actually creates a system managed temporary tablespace .  Should we remove the default system managed temporary tablespace  first before execute (3) to avoid there is two system managed temporary tablespace exists ? 

The problem is with the privilege level needed to execute these commands. The customer will have to create a DB2 user with SYSCTRL or SYSADM authority for the SOA-P server to use to connect to the DB.

I think there are two options for fixing this problem :

1.  schema tool check if the 32k page size tablespace and temporary tablespace are created . If not , it prompts this message :
"The 32k page size tablespace and temporary tablespace are needed for SOA platform , please contact your DBA to execute the following db2 statements :
create bufferpool jbpmbp immediate size 1000 pagesize 32k;
create tablespace jbpmts pagesize 32k managed by AUTOMATIC STORAGE BUFFERPOOL jbpmbp;
create temporary tablespace jbpmtempts pagesize 32k managed by AUTOMATIC STORAGE BUFFERPOOL jbpmbp

2. schema tool does not check anything and just add switch to db2 instructions to  the document. 

Where will we go ?

Link: Added: This issue related SOA-1060

Link: Added: This issue is related to SOA-1357

Fixed in rev 3117:

Index: resource/schema/tool/esb/db2/template/antsql.xml
===================================================================
--- resource/schema/tool/esb/db2/template/antsql.xml    (revision 3117)
+++ resource/schema/tool/esb/db2/template/antsql.xml    (working copy)
@@ -1,14 +1,3 @@
 <?xml version="1.0"?>
 <project name="setup db2" default="setup" basedir=".">
-        <!--create 32k pagesize tablespace for jbpm-->
-         <target name="setup">
-               <sql driver="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://@HOSTNAME@:@PORT@/@DATABASE_NAME@"
-                       userid="@USERNAME@" password="@PASSWORD@" src="setup.sql" print="yes">
-                       <classpath>
-                               <fileset dir="../driver">
-                                       <include name="*.jar"/>
-                               </fileset>
-                       </classpath>
-               </sql>
-       </target>
 </project>

The script is unchanged in the 4.3 CP02 CR2 build:


[ldimaggi@ldimaggi template]$ pwd
/opt/local/43_CP02_CR2/jboss-soa-p-standalone.4.3.0/jboss-esb/tools/schema/db2/template

[ldimaggi@ldimaggi template]$ cat setup.sql 

create bufferpool jbpmbp immediate size 1000 pagesize 32k;
create tablespace jbpmts pagesize 32k managed by AUTOMATIC STORAGE BUFFERPOOL jbpmbp;
create temporary tablespace jbpmtempts pagesize 32k managed by AUTOMATIC STORAGE BUFFERPOOL jbpmbp;

Hi Len , 

This script will NOT be executed when user runs schema tool for DB2 :

[jimma@localhost]cat ./jboss-soa-p-standalone.4.3.0/jboss-esb/tools/schema/db2/template/antsql.xml
<?xml version="1.0"?>
<project name="setup db2" basedir=".">
</project>

I do not delete the content in setup.sql for user manually execute it. We can add this instruction in 
SOA-P documentation. 

Julian just told me  this db2 large table space issue has been fixed : https://jira.jboss.org/jira/browse/SOA-1437. 
I will double check it and remove the db2 setup.sql.

JBPM already fixed this issue . But JUDDI needs at least 8k  page size table space to create tables.This is the error message when I use juudi sql to  create talbes:

CREATE TABLE IBINDING_TEMPLATE ( SERVICE_KEY VARCHAR(41) NOT NULL, BINDING_KEY VARCHAR(41) NOT NULL, ACCESS_POINT_TYPE VARCHAR(20) , ACCESS_POINT_URL VARCHAR(4000) , HOSTING_REDIRECTOR VARCHAR(255) , LAST_UPDATE TIMESTAMP NOT NULL, PRIMARY KEY (BINDING_KEY), FOREIGN KEY (SERVICE_KEY) REFERENCES IBUSINESS_SERVICE (SERVICE_KEY) )
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0286N  A default table space could not be found with a page size of at 
least "8192" that authorization ID "JIMMA" is authorized to use.  
SQLSTATE=42727


Create another document issue for this : https://jira.jboss.org/jira/browse/SOA-1450

Link: Added: This issue is a dependency of SOA-1450

Link: Removed: This issue is a dependency of SOA-1450 

Link: Added: This issue is related to SOA-1450

Removed the content for creating large page size for JBPM (Committed revision 3595), jBPM already fixed this and does not need large page size table space.

added to 4.3.CP02 release notes as resolved:


SOA-1204 - When using DB2, the creation of the jBPM database required a user with SYSCTRL or SYSADM  authority.
This is no longer required for jBPM.

Closing - the setup.sql file is now empty for DB2 in 4.3 CP02 CR3:

[ldimaggi@ldimaggi template]$ pwd
/opt/local/43_CP02_CR3/jboss-soa-p.4.3.0/jboss-as/tools/schema/db2/template

[ldimaggi@ldimaggi template]$ od -ha setup.sql 
0000000 000a
         nl nul
0000001

The schema tool is able to run with the file deleted - why leave a 1 byte file there? I'll look into this and may log a (minor) JIRA to remove the file.

This file is kept for adding the drop and create new database statement like the other database Oracle, sybase , postgresql does.
I think we can remove this file now and add these statements after CP02 release .