When installing an enterprise application with Microsoft SQL Server (Microsoft SQL), it is important to be careful with the database user, SQL Authentication, and the default schema. The SQL scripts provided in this document would apply to any scenario where you need to change the owner of tables, views, or stored procedures in any Microsoft SQL database.
Note: For this article's reference, I reference Lombardi Teamworks (v7.0, v6.x and earlier), WebSphere Lombardi Edition (v7.1, 7.2), and IBM Business Process Manager (v7.5 and later).
When you install these products with Microsoft SQL Server as the database and choose SQL authentication, the default schema needs to be the same as the user name.
Example: The database user is 'teamworks', then all tables after installation will be similar to the following name: teamworks.LSW_tableName. If your tables read dbo.LSW_tableName then you will need to correct the schema name. When installing with Microsoft SQL Authentication, there are various built-in SQL queries, which look for dbUserName.LSW_table. You might see error messages that are similar to the following text in log files:
Error Example 1 - The table LSW_SYSTEM exists, but it cannot be reached because it is owned by dbo.
2011-06-10 15:18:52,593 [SIBJMSRAThreadPool : 7] ERROR com.lombardisoftware.server.ejb.persistence.PersistenceServicesBean -
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into LSW_SYSTEM (SYSTEM_ID,NAME,DESCRIPTION,ARCHIVED,VERSION,LAST_MODIFIED,LAST_MODIFIED_BY_USER_ID) values (?,?,?,?,?,?,?)]; SQL state ; error code ; Cannot insert duplicate key row
in object 'teamworks.LSW_SYSTEM
Error Example 2 - The auto creation scripts for the performance server wants to create tables with the schema teamworks. In this case, the user teamworks is not the default schema and does not have db_owner rights.
2011-06-13 09:01:48,205 [SIBJMSRAThreadPool : 6] ERROR com.lombardisoftware.server.ejb.tracking.RepresentationManagerServicesBean -
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ CREATE TABLE teamworks.TG_SLATHRESHOLDTRAVERSALS (
Complete the following steps to correct the schema name:
- Shutdown the application (Teamworks, WebSphere Lombardi Edition, or IBM Business Process Manager)
- Log in to the database with a high level user, system administrator or equivalent
- Back up the database in the event you need to roll it back.
- Check to see if the database user has db_owner rights and that the default schema is the same as the user. By default, when creating new users in Microsoft SQL, the user's default schema is dbo. Make changes as necessary.
- Drop all Service Integration Bus (SIB) tables in the database. New SIB tables with the proper schema name are created when the servers start. There is no harm in dropping SIB tables on the process database. There is no in-flight instance or task data that is stored in SIB records.
- Run the SQL scripts to change tables, stored procedures, and views.
- Refresh the view database to confirm that all schema names are changed.
- Restart servers and confirm that the log files do not have any errors.
Change Stored Procedure
Change Table Owner
This script changes the schema owner for stored procedures.
SET @OldOwner = 'dbo'
SET @NewOwner = 'lswbpm'
DECLARE CURS CURSOR FOR
type = 'p'
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
NOT name LIKE 'dt%' FOR READ ONLY
DECLARE @ProcName sysname
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
IF @@VERSION >= 'Microsoft SQL Server 2005'
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')
FETCH CURS INTO @ProcName
This script changes the schema owner for all tables in a database.
use bpmProcessDB /*which database to perfrom operation on.*/
DECLARE tabcurs CURSOR
SELECT 'dbo.' + [name] /*dbo here is the OLD schema name*/
WHERE xtype = 'u'
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname
WHILE @@fetch_status = 0
EXEC sp_changeobjectowner @tname, 'lswbpm' /*lswbpm here is the NEW schema and owner*/
FETCH NEXT FROM tabcurs INTO @tname
Example for single changes, in VIEWS, performance DB. These are the default views for the perfomance server. If other views exist from tracking tables, those will also need to be changed.
EXEC sp_changeobjectowner 'oldschema.VIEW_NAME', 'newSchema'
/*default ones for bpmPeformanceDB*/
EXEC sp_changeobjectowner 'dbo.PROCESSFLOWS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SLASTATUS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SLATHRESHOLDTRAVERSALS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SNAPSHOTS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TASKS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TIMINGINTERVALS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TIMINGINTERVALVALUE', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKEDFIELDS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKEDFIELDUSE', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGGROUPS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGPOINTS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGPOINTVALUE', 'lswbpm'
Modified on by SteveWebb
Whenever you are promoting a newer version of your process application snapshot you have few options to choose from on how you would like to handle in-flight (running) instances on the target runtime server.
In this blog post, I would like to concentrate on the Migrate option for instances because Leave and Delete are straight-forward; there is no need to explain them in more detail. However, the Migrate option might cause unpredictable results in some cases. So, the purpose of this article is to talk about the best practices when you use the Migrate option.
You can find general information on migrating instances in the Migrating instances topic within the IBM Business Process Manager Information Center.
What does migrate running instances actually mean?
The Migrate option means that currently running instances are migrated to the new snapshot that you are deploying. Wherever the running instances are in the flow of the process, the new version is implemented for the next item or step. VERY important note on this - if you migrate currently running instances to a new version of the snapshot, problems can occur if the new version removes steps or other components from the business process definition (BPD). When running instances have tokens on business process definition (BPD) or service-level components that have been removed in the new snapshot, migration can fail. Tokens indicate where a runtime instance is currently executing and can be present on an activity, a conditional sequence line, a coach, a service call, and numerous other components.
If you believe that the Migrate option is the right option for you, then almost all of the best practices from the Lombardi Teamworks 6 product are applicable in this case. The reason is because Teamworks 6 did not have any deployment options; you just used import/export. This action always overrode the previous version with new code and all the in-flight instances were impacted. In other words, you were always using a migrate option. The IBM Business Process Manager rules for migration when you deploy a snapshot and migrate are not 100% the same as it was in Teamworks 6. However, 90% of those best practices that were available for Lombardi Teamworks 6 are applicable when you deploy a snapshot and migrate in IBM Business Process Manager. So, they are not applicable to IBM Business Process Manager "as such" because it has been completely re-written and redesigned in this area (there were not any snapshots in Teamworks 6), but the case where you do deploy a snapshot and migrate is close to how it used to be.
My suggestion is to read the following articles for an overview of the best practices from Teamworks 6 and, based on that information, develop your own procedure for migrating live instances to new to avoid any unpredictable results:
Sergii Malynovskyi, who is based Kyiv, Ukraine, is a Team Lead for the WebSphere Lombardi Edition and IBM Business Process Manager Level 2 Support Team.
When you install WebSphere Lombardi Edition Version 7.2 or the IBM Business Process Manager Version 7.5 products, you find that the Process Center, Process Admin Console, the authoring environment, and the Process Portal all use English as the default language. What do you do if English is not your primary language and you are more proficient in, for example, Spanish? Starting with WebSphere Lombardi Edition Version 7.2 and continuing with the IBM Business Process Manager V7.5 products, support exists for a variety of additional languages through custom localized language packs. These product versions support the following localized language packs:
- Portuguese (Brazil)
- Simplified Chinese
Although these products support the additional languages, you might need to implement a custom language localization. Information on the process is available in the following articles, which are available in the IBM BPM Community:
Note: Free registration is required to access the IBM BPM Community, which provides access to many additional resources including the sample exchange and helpful information provided by IBM and fellow users.
Thanks to Daniel Schoonmaker in IBM Business Process Manager Level 2 Support for recommending this blog article.
We welcome your feedback on this blog article. Did the information help you? Do you have any suggestions? Leave us a comment below and tell us how we did!
Modified on by Bill Wentworth
There are times that under the direction from a database administrator that you might need to manually change a database. This change might be an index to improve performance. You need to consider the following information in regards to indexes. Indexes are useful when:
You want to access a small percentage of the rows in a table, such as less than 5% for a small table and less than 15% for a larger table. Some of the stock IBM Business Process Manager tables can be accessed with a rather large amount of data being retrieved. So, that is why it is important to do it on a per custom index basis.
The index itself can be used to answer the query. For example, it is advantageous to use the primary key index to answer the "select count(*) from T" query using a fast full index scan. It is advantageous because the index is generally many times smaller than the table itself.
Indexes can be a bother when you have over-indexed the table. For example, you have an index on (a,b,c) and (a,b) and (a) or indexes that are never used. Extraneous indexes slow down the database manipulation language (DML) operations of an insert, update, or delete operation unnecessarily.
Indexes are only used to speed up the search for a matching field within the records. It stands to reason that using indexing fields only for output are a waste of disk space and processing time when doing an insert or delete operation. Thus, they should be avoided. Also, given the nature of a binary search, the cardinality or uniqueness of the data is important. Indexing on a field with a cardinality of 2 splits the data in half. Whereas, a cardinality of 1,000 returns approximately 1,000 records. With such a low cardinality, the effectiveness is reduced to a linear sort, and the query optimizer avoids using the index if the cardinality is less than 30% of the record number, which effectively makes the index a waste of space.
Consider the following information when you are determining whether to use database indexes with IBM Business Process Manager:
All applications are different. This fact is why we tend to be conservative at installation time. We only create indexes that we think will benefit all applications. It is not abnormal for a bit of index customization to help an application. If the database design tools recommend creating new indexes to improve query response time, I would recommend that you take that advice. However, I suggest verifying that the indexes provide a benefit using measurements in your lower regions before moving them up to production.
There are costs at run time to maintaining the indexes. Usually, the savings in query response time justify the costs at insert or update operation time. However, I occasionally see the Advisor tools recommend new indexes that provide only marginal benefit when I actually measure them against my application. In these cases, I tend to remove the index from regular use. On general principle, I run with the smallest number of indexes I need to get the best results.
Your database administrator would be the best person to advise you on what indexes he or she believes your IBM Business Process Manager environment would benefit most from because, in most of the cases, it's very application or solution dependent.
Make sure to keep a good list of those custom indexes and temporarily remove them before applying any upgrade. This approach is recommended so that any database modification phase in the upgrade process does not fail because it wants to put an index on something that already has an existing custom index.
Keep in mind that if you report a product problem, IBM Support might ask you to remove any custom indexes (temporarily, at least) as part of troubleshooting or fault isolation processes.
Make sure to keep your database in a good shape by performing regular cleanups of completed instances and tasks. For more information, see the Data querying takes a long time and process server database tables are using too much disk space with WebSphere Lombardi Edition (WLE) and the IBM Business Process Manager (BPM) products technote. It explains the technique that can be used "as is" or you can use it to create your own custom stored procedure based on your cleanup requirements. It is important to perform this task on regular basis; otherwise, the stock IBM Business Process Manager queries response times will increase and additional custom indexes can only make it worse.
Sergii Malynovskyi, who is based Kyiv, Ukraine, is a Team Lead for the WebSphere Lombardi Edition and IBM Business Process Manager Level 2 Support Team.
We encourage you to leave feedback on this article below.
The IBM Knowledge Center Open Beta is now live on ibm.com! The Beta will run until the end of February 2014.
You can access the latest IBM Knowledge Center at http://www.ibm.com/support/knowledgecenter/
IBM is improving your technical content experience
IBM Knowledge Center is our new technology designed to bring IBM's technical publications together in a single location, and will replace our individual IBM information centers.
In this version of the Knowledge Center, IBM simplified the user experience, improved search, and refined the overall experience with many other enhancements. You can get help for IBM Knowledge Center by clicking the question mark (Help icon) in the upper right corner of any page of the Knowledge Center. The Help icon will take you to http://www.ibm.com/support/knowledgecenter/doc/kc_help.html
Send us your feedback!
IBM would like you to provide your feedback after you have had some time to use the IBM Knowledge Center, by signing in with your IBM ID and taking a few moments to complete a survey at https://www.ibm.com/software/support/trial/cst/forms/survey.wss?id=5323
Alternatively, you can click on the Feedback button at the bottom of most pages within the IBM Knowledge center to provide IBM with your input.
Known Beta limitations
We are still:
Fine tuning IBM Knowledge Center. So you might experience some minor functional problems.
Configuring and adding content to IBM Knowledge Center. So the content you see might not be exactly what you expect.
Configuring and indexing content for search. So search results might not be exactly what you expect, or might not be in all the languages you expect.
IBM would like as many IBM clients as possible to participate. The IBM Knowledge Center team would like to thank you very much for your time and for helping us make IBM Knowledge Center a better information experience!
Today, we are announcing an upcoming change to the Twitter accounts for the IBM Business Process Management (BPM) product family. For your convenience, and to better align with our product family strategy, we are consolidating the IBM_Lombardi, IBM_Modeler, IBM_Monitor, and IBM_ProcessServ Twitter accounts into one Twitter account. Effective Wednesday, February 1, 2012, we will consolidate these four Twitter accounts into one Twitter account called IBM_BPM. To denote product-specific tweets, we will use the following #hashtags:
- IBM Business Process Manager Standard / IBM Business Process Manager Express / IBM Business Process Manager Advanced: #bpm
- IBM Integration Designer: #iid
- WebSphere Lombardi Edition: #lombardi
- WebSphere Business Modeler: #modeler
- WebSphere Business Monitor: #monitor
- WebSphere Enterprise Service Bus: #wesb
- WebSphere Integration Developer: #wid
- WebSphere Process Server: #wps
So, what does this change mean for you?
Current IBM_ProcessServ followers
If you are currently following the IBM_ProcessServ Twitter account, an action is not necessary. The account name will change and you will follow the new account automatically.
Current IBM_Lombardi, IBM_Modeler, and IBM_Monitor followers
If you are following the IBM_Lombardi, IBM_Modeler, or IBM_Monitor Twitter accounts, new tweets will cease on Wednesday, February 1, 2012. To avoid any interruption, follow the IBM_ProcessServ account before February 1, 2012 in advance of the change or the IBM_BPM account beginning on February 1st. If you follow the IBM_ProcessServ account before February 1st, you will automatically migrate to the IBM_BPM account. Beginning February 1st, tweets that previously were sent from the IBM_Lombardi, IBM_Modeler, and IBM_Monitor Twitter accounts will originate from the IBM_BPM account.
Note: The IBM_Adapters Twitter account will remain active.
Do you follow us on Twitter? Our presence on Twitter is extensive! We provide links to technical support-related information that exists in many different formats such as problem and solution documents, developerWorks articles, IBM Red Books, IBM Red Papers, YouTube videos, blogs, and so on. We also provide links to educational opportunities and other related information to help you get the most out of your investment in our products.
There are two Business Process Management products that do not have a Twitter account named for them. However, we are currently providing information about those products through related Twitter accounts.
- IBM Business Process Manager Version 7.5 products were released earlier this year. To obtain information on IBM Business Process Manager Standard, IBM Business Process Manager Express, and IBM Business Process Manager Advanced, you can follow either the IBM_ProcessServ or the IBM_Lombardi Twitter account. Information pertaining to those products are provided through both of those accounts at different times; you won't miss any Business Process Manager-related tweets if you are following one account and not the other.
- WebSphere Enterprise Service Bus Version 7.5 was released earlier this year. To obtain information about this release and previous releases, follow the IBM_ProcessServ account.
We provide Business Process Management-related information through the following Twitter accounts:
- Twitter Account: IBM_Adapters
Products covered: WebSphere Adapters
- Twitter Account: IBM_Lombardi
Products covered: IBM Business Process Manager Standard, IBM Business Process Manager Express, IBM Business Process Manager Advanced, WebSphere Lombardi Edition
- Twitter Account: IBM_Modeler
Products covered: WebSphere Business Modeler and WebSphere Business Compass
- Twitter Account: IBM_Monitor
Products covered: WebSphere Business Monitor
- Twitter Account: IBM_ProcessServ
Products covered: IBM Business Process Manager Standard, IBM Business
Process Manager Express, IBM Business Process Manager Advanced,
WebSphere Enterprise Service Bus, and WebSphere Process Server
If you have any suggestions for content that you would like to see through our Twitter accounts, you can leave us comments here or send us a direct message through Twitter.
(content by Holly_Fitzgerald)
The Accelerated Value Program is pleased to announce that Accelerated Value Central (AVC) will again be part of the upcoming IMPACT 2012 Conference to bring you exclusive events and opportunities that will enhance your IMPACT 2012 experience.
Over the coming months we will be providing additional information on our AVC events, designed exclusively for you - our AVP clients(*). This information will be provided directly to you by your Accelerated Value Leader and / or Specialist as well as published in our News item entitled Accelerated Value Central at Impact 2012 (April 29 - May 04)
. Be sure to bookmark the News item to stay on top of these exclusive AVC events.
Additional information about the conference can be found on the main IMPACT 2012 page
Please visit the IMPACT 2012 registration site
for more information including Early Bird Registration.
We look forward to seeing you in Las Vegas!
* If you're not an AVP client, see what AVP has to offer: WebSphere Software Accelerated Value Program
Are you a WebSphere Lombardi Edition Version 7.2 customer? If so, read on...
Fix Packs, by their very nature, link together various changes to the product code like the various pieces of your favorite jigsaw puzzle. Each change from a customer Problem Management Report (PMR) is a puzzle piece that is assembled together with other puzzle pieces, tested, and assembled together to form a fix pack. Changes in one fix pack typically depend on the changes from previous fix packs to work correctly. For changes to WebSphere Lombardi Edition Version 7.2, we previously required that you install each of the fix packs individually and in sequential order due to dependencies between them. However, on Friday, July 6th, 2012, the fix pack strategy for Version 7.2 changed when we released Version 7.2.0 Fix Pack 4. This fix pack is very significant as it is the first fix pack for this release that includes all of the changes from all of the previous fix packs and additional fixes. It removes the requirement to install all previous fix packs prior to installing this fix pack. In fact, fix packs 1, 2, and 3 are no longer available. The following graphic illustrates the fix pack strategy change:
To see a list of the changes that are addressed in this cumulative fix pack and to download it, see the Fix List for WebSphere Lombardi Edition 7.2 and 7.1.