Affects: Documentation (Ref Guide, User Guide, etc.) Date of First Response: 2009-03-05 04:12:26 project_key: SOA This script: jboss-soa-p-standalone.4.3.0/jboss-esb/tools/schema/db2/template/setup.sql includes these commands: 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; These commands require the DB2 user configured with the SOA-P server to have SYSCTRL or SYSADM authority. It's more likely that customers will want this user defined with connect privileges only: db2 grant connect on database to user <user> The (3) commands above should be removed from the schema tool and be executed by the customer's DBA/sysadmin when the database is created.
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 .