How many RedPrairie environments should we have?

This is a common issue that we have to deal with for any project. There are no absolutely right answers but there are some wrong ones:

• Having a single production environment
• Having multiple environments for WMS-online but a single archive environment.
• Having multiple environments for WMS-online but a single parcel environment

So as long as you have separated production environments, that is better than nothing. A good start may be:

• Development
• QA
• Production

And do not take the secondary environments like archive and parcel lightly. You should have a parallel environment for these as well.
There is need for additional environments but before I discuss that I want to clear one myth that a RedPrairie environment is hard to setup! Given that you have an installation, for example dev environment; setting up an additional environment from same code-base is trivial. Exact steps will be:

1. Copy the database to the new environment
If you have kept the database free of environment-specific data then this can be completely scripted.
2. Thats it - because code will already be the same as the other

With that myth busted, now we can consider some additional environments which will come in handy:

Environment	Use code from	Purpose
Test	Development	The development database is generally not a good place for testing the code for even the developers. So this environment will offer them a place where they will have good data to test with. If you have only a few developers you probably do not need this.
Train	QA or Production	Before go-live you probably want this based off QA but after that to train the workforce you want this based on Production source code. This environment will typically have pre-configured exercises and data.
Debug	Depends	The important part is that production database is exported and imported to another place nightly. This will help in diagnosing hard to reproduce issues. Then based on specific needs you may point it from development code, QA code, or production code.

Conclusion

Development, test, and production is a good start but if we understand that if we setup the database free of environment specific entries and understand that new environments can be created very easily from existing environments then these other environments can come in handy. These additional environments do not need to be permanent rather you can bring them online on as-needed basis.

How to approach a multi-phased multi-warehouse upgrade for RedPrairieTM Upgrade?

Introduction

RedPrairie WMS allows for having multiple warehouses in the same instance. This is a powerful feature and I always encourage the customers to consider this over the alternative where you will have a single RedPrairie instance for each warehouse. When you have a single instance the overall footprint improves significantly because for each instance you typically have:

Development, test, production, etc.

Archive environment (Do not go down the path of having a single archive instance for multiple online instances. You shall regret it)

So you can imagine how having fewer instances makes it simpler to support such an environment from IT perspective. You will also have a single code-base and a single environment to patch.

Problem

The initial go-live is done and now it is time for upgrade. At this time if you can afford to upgrade all of the warehouses at once then it is great but if you want to upgrade warehouse at a time, now we have some challenges. At a high level such a project will go as follows:

• Assume old environment is OLDE and has multiple warehouses
• Assume the warehouse to upgrade is WH1
• Install the new version. Lets call this NEWE
• Migrate the warehouse WH1 from OLDE to NEWE
• Setup a temporary environment which has same version has OLDE, lets call it TMPE
• Copy the data for WH1 from OLDE to TMPE
• Upgrade the TMPE environment to the same version as NEWE
• Copy data for WH1 from TMPE to NEWE
Refer to this document to understand that creating these new environments may not be such a big deal
• Go live with warehouse WH1 in NEWE while OLDE is still running the other warehouses
• At some later time do the same for the next warehouse

Even after following this long and arduous process, we will have some basic challenges:

• Can the host send transactions to multiple instances?
This implies that host will send some transactions to OLDE and some to NEWE. This is generally not a huge problem if you are using an ERP like SAP, Oracle, JDE, etc.
• What about the sequence numbers when the second warehouse merges with NEWE
This is the main problem. Some primary keys that are based on sequences, like wrkref, cmbcod, ctnnum, dlytrn_id, trlract_id, ship_id, ship_line_id do not have wh_id as part of the primary key. This implies that when second warehouse will merge we will have an issue with this data. Never fall for "the online data will be cleared" because that will just push the issue to the archive instance.

Some possible solutions may be:

• Do not upgrade history
This option may look good in a conference room but it is really quite ugly. This suggests that the warehouse users will go to the OLDE and Archive for OLDE to look at data before a certain date and go to NEWE for newer data. Just imagine the next upgrade now. You will always need these OLDE environments around because some industries require you to be able to access data for several years.
• Utilize the sequence prefix along with base-36 sequences
Even if you did not use base-36 sequences in old environment, set them up in NEWE. In NEWE use a different prefix from OLDE, for example A0 for wrkref, B0 for cmbcod etc. (I generally recommend not to break the sorting rules so if you are starting fresh then A and B prefixes are ok but if you already have W as the prefix then use X so that old data and new data sorts properly. Never assume that RedPrairie will always sort by date - it may [and should] be sorting by this sequence ) With base-36 you will have plenty of room to expand. Now when the next warehouse is merged the old data will simply load in and not cause any primary key issues. Also there will not be any issue with the archive environment.
Now when we do the next upgrade we can use A1 prefix and so on. You will have a lot of room for future upgrades with this approach.

Another question you may be asking is if the approach described above has to be that painful for bringing one warehouse at a time. Notice the concept of a duplicate environment, copying the data there, then removing all warehouses except yours, then upgrading it etc.
Considering the current solution-set where the "dbupgrade" process upgrades the whole database that is being referenced by the current environment that is the only option. The upgrade scripts merge the concept of schema changes and data migration.These scripts also change the whole database, i.e. do not selectively run for certain warehouses. When we are bringing in the second warehouse into NEWE ideally:

• the process could have ignored the schema changes
• Only bring data from OLDE to NEWE and at the same time change the data definition
• When bringing in the data, bring in selected rows

I have not seen the new RedPrairie solutions in this regard (like environment manager) so I cannot comment on them but if the solution works by pointing to an environment that may be a different version and load subset of data to an environment with a newer version and performs well then it would be a good solution otherwise you may have to invest in developing it yourself. Contact me if you want to consider developing such a solution.

Common Tips and Tricks for RedPrairie(tm) WMS (Technical)

Index

Autonomous Nested Transactions in RedPrairie

Do not store environment specific data in the database

Getting SQLServer deadlocks?

An integrator query keeps showing up as bad query for oracle?

Always use base-36 sequences

Shortcut to run RedPrairie^tm command line tools on Windows

Create multiple environments from same installation

How to turn off Windows Popup when MOCA crashes?

---

How to turn off Windows Popup when MOCA crashes?

This is important because this is a server; we should not get popups on the server when an error occurs. Please refer to MSDN Article for details.
• Change registry key HKLM\Software\Microsoft\Windows\WindowsErrorReporting to add a DWORD key “DontShowUI” with value of 1.
• If visual studio is installed on the server, disable just in time debugging. Go to "Tools" then "Options". Then go to "Debugging" and then "Just in Time". Disable all checkboxes
Verify that popups will not be displayed on crash.
• Open a command prompt on the server and run env.bat
• Run msql -S

dump core
/

• Make sure it simply returns 204 and does not show a popup
Top

---

Autonomous Nested Transactions in RedPrairie

The concept of an autonomous transaction is that the parent transaction commits and rolls back but within that transaction we want to do another transaction which will commit/rollback independently. For example:

update row in table1
Now start a new transaction 
   update row in table table2
   commit
End nested transaction

Now in parent transaction, even if it rolls back, table2 update is still committed.

Most common use of this is for logging messages. For example RedPrairie integrator sl_msg_log or sl_async_evt_queue can get data even if parent transaction rolls back.
In Oracle, this is a native feature; for example you can do following within MOCA if backend is Oracle.

   [
   declare
      pragma autonomous_transaction;
   begin
      some pl/sql code which updates the database
      ;
      commit;
   end;
   ]

There is no straightforward way of doing this in SQLServer. Some options were:
• Send a message to another daemon process which interprets the message and commits to the database. In previous versions integrator daemons provided this functionality
• Do a remote command to yourself as 127.0.0.1 and do a commit in that transaction
Now as of 2011, MOCA provides this natively for all databases. A MOCA Command can have a new tag called "transaction" If that is set to "new" then the work within the command will be done in its own commit context, e.g.

<command>
<name>change ossi job log aut</name>
<description>Change an entry into the job log in own commit context</description>
<type>Local Syntax</type>
<transaction>new</transaction>
   ...

To test it, you can do something like:
• Open two msql sessions
• In session 1, turn off auto-commit
• Now in session 1, execute an update statement, then execute a MOCA command that has its own transaction, e.g.

[update sl_sys_def set sys_descr = 'Transactions to the ASI and Mapics system!!' where sys_id = 'ASI-MAPICS']
;
/* Following is a command with New transaction */
change ossi job log aut
where uc_ossi_job_seq = 1
and uc_ossi_module_seq = 0
and uc_ossi_action_seq = 0
and uc_ossi_err_code = 12
and uc_ossi_err_descr = 'T1'
         

• In session 2, query sl_sys_def for ASI-MAPICS system; you will not see the changed description because auto-commit was off
• In session 2, query for effect of the autonomous MOCA command, you will see the change
• In session 1, rollback or commit will affect the update statement but not the autonomous MOCA Command
This is a very useful feature. You do not need to log to a file; instead you can use database tables for logging which is much more efficient. For example we always put a general purpose log table to keep track of long running jobs and tasks. It may come in handy to log daily transactions in case of certain failures. I have not really compared the oracle autonomous transaction vs. MOCA autonomous transaction to see which is more efficient. If you are on oracle, you could stick with PL/SQL autonomous transaction but this is the only option for SQLServer. Top

---

Do not store environment specific data in the database

Any given RedPrairie environment typically needs to maintain links to several other environments for example:
• Integrator settings for host/port, queue names, folders
• Parcel Server connection information
• Archive instance connection information
Whenever I am engaged in a project, I always setup an absolute goal that none of such data elements be stored in the database. Instead these should be environment variables. If we are able to achieve that then the task of copying database from one environment to another becomes a simple task which greatly simplifies the overall footprint of RedPrairie. Such settings should instead be turned to environment variables that would be stored in RedPrairie registry. Make sure all environment variables are prefixed by "UC_". You can easily achieve this goal:

Integrator Data	Do not store host:port at the system level - there is really no absolute reason for it. Then when setting up connection information, use SL_GET_ENV algorithm and point to an environment variable.
Parcel Connection	Override the component that gets the settings to read environment variable rather than the table
Archive Connection	Override "archive production data" to read the environment varaible.

Top

---

Getting SQLServer deadlocks?

First it is very important to understand what is a deadlock. Many people wrongly consider a deadlock as a typical locking condition. Read this MSDN article to get a better understanding SQLServer has a concept of lock escalation where a lock that starts with a row-level lock can escalate to a page level lock. So even if you have architected an application properly the exact sequence of locking becomes un-predictable because you do not know what you are locking because data within a page is unpredictable. In later versions of SQLServer, it is possible to disable this lock escalation. In all frequently used tables I always disable the lock escalation. You can use following script for a given table:

   [sp_indexoption @table_name, 'AllowRowLocks', true]
   ;
   [sp_indexoption @table_name, 'AllowPageLocks', false]
   ;
   [alter table @table_name:raw set (lock_escalation = disable) ]

Thanks to the power of MOCA piping, you can easily run this for the tables you want to run it for, for example:

publish data where table_list = 'coma-separated-list-of-tables'
|
{
   convert list where string = @table_list and type = 'L'
   |
   {
      [commit] catch(@?)
      ;
      [sp_indexoption @retstr, 'AllowRowLocks', true]
      ;
      [sp_indexoption @retstr, 'AllowPageLocks', false]
      ;
      [alter table @retstr:raw set (lock_escalation = disable) ]
      ;
      publish data where table_name = @retstr
   }
}

Just a word of caution that SQLServer maintains a list of locks so after this change you will get a lot of locks (this is different from Oracle - but that is a different discussion, just know that Oracle does not have an issue with so many locks). But you pretty much have to do it. Top

---

An integrator query keeps showing up as bad query for oracle?

Those tasked with monitoring SQLs on Oracle often complain about one specific query from integrator:

select ...
FROM sl_evt_data ed, sl_ifd_def id, sl_sys_comm ssc, sl_ifd_sys_map_data ismd, sl_ifd_data_hdr sidh
WHERE id.ifd_id = sidh.ifd_id 
and id.ifd_ver = sidh.ifd_ver 
and ssc.sys_id = ismd.sys_id 
and ssc.comm_mode_cd = :1 
and ssc.comm_dir_cd = :2 
and ismd.sys_id = :3 
and ismd.ena_flg = 'T' 
and ismd.blkd_flg = 'F' 
and ismd.comm_mode_cd in ( :99, 'SYNCE' ) 
and ismd.ifd_id = sidh.ifd_id 
and ismd.ifd_ver = sidh.ifd_ver 
and ismd.evt_data_seq = sidh.evt_data_seq 
and nvl(ismd.comm_mthd_id, ssc.comm_mthd_id) like :5 
and sidh.sys_id = :99 
and sidh.snd_dt is null 
and nvl(sidh.proc_err_flg,'F') = 'F' 
and sidh.evt_data_seq = ed.evt_data_seq 
and ed.evt_stat_cd = 'IC' 
ORDER BY 
...

This query is looking for events in a specific status (IC) but Oracle ends up not favoring sl_evt_data as starting table because of the number of rows in this table. This query itself is not a bad query All we need to do is use the proper optimization tools In this case Oracle sees a large table and wants to go by a smaller table because it does not know that even though sl_evt_data is a large table but there are only a few rows in the status in question. To help oracle make the right choice, create histogram on the evt_stat_cd column. Top

---

Always use base-36 sequences

RedPrairie^tm sequences are defined in the sysctl table. They have a flag which can turn them from base 10 to base 36. I always put most of the heavily used sequences to base-36 when a project starts.
Some sequences where this is especially useful are:
• wrkref (pckwrk table)
• cmbcod (pckmov table)
• ship_id (shipment table)
• ship_line_id (shipment_line table)
• dtlnum (invdtl table)
• ctnnum (pckwrk table)
• Strongly consider for subnum (invsub table) and lodnum (invlod table)
• Sequences for transaction history tables are already base-36, for example dlytrn_id, etc.
• Integrator tables do not have string columns as PK so cannot turn them to base-36 but these are pretty long (12 digits)
Never under-estimate the frequency at which these sequences can be consumed and never ever let someone tell you that "these will be in archive by the time they roll around". Archive instance has the same restrictions so moving the problem to archive does not really address the core issue. Following are some sequences where I have actually seen this issue:

Sequence	Length	Default Prefix	Maximum	Max with single character prefix and base-36	Likely Reason for heavy use
cmbcod	10	CMB	9,999,999	101,559,956,668,416	Frequent failed allocations from replen manager
ship_id	10	SID	9,999,999	101,559,956,668,416	Automatically creating shipments and allocating them [and failure on allocation]
ship_line_id	10	SLN	9,999,999	101,559,956,668,416	Automatically creating shipments and allocating them [and failure on allocation]

Just to put it in perspective, lets say you consume 10,000 sequence numbers a day, time till you run out of numbers for 7 characters is as follows:

Base 10 with 7 characters	Base 36 with 7 characters
999 days	7,836,416 days or 21,469 years [I think it should be a safe bet]

If you are already live and now want to consider this change then you should find the base-36 equivalent number for the current sequence value and increase the value to that. Then change sequence to base-36. You will have a huge gap in sequence numbers but you will most likely still have much more room to grow. If you are very close to using up the numbers, then change your prefix to a higher valued prefix for example SLN can become SLP (or take the opportunity to go from 3 character prefix to a single character) then start over as a new base-36 sequence. Top

---

Shortcut to run RedPrairie^tm command line tools on Windows

Several RedPrairie tools, like mbuild, mgendoc, rollout process require a command prompt.  Rather than running "env.bat" every time, I place a shortcut on my desktop for each environment I am supporting.  This way I can get an environment right away.  In order to create such a shortcut, follow following steps:
1. Open windows "cmd" window.
2. "cd" to the installation directory, i.e. parent directory of your LES folder.
3. Run "moca\bin\servicemgr -e environment name -dump".  This generates a file called "env.bat" in this directory.
4. Now right click on your desktop and create a shortcut to "cmd /k c:\redprairie\<env>\env.bat"
5. Set the size of the window and scroll properties
6. Set a name for the window
Top

---

Create multiple environments from same installation

A redprairie environment is defined as code + data. It is possible to create multiple environments from same code or have different code bases point to the same data. Both of these concepts come in handy. Before I discuss this, I want to explain the concept of "rptab" file which is part of Moca NG framework. On Windows this file is in c:\programdata\RedPrairie\server and contains all environments on this server. The text file has multiple lines where each line has 2 or 3 fields (3rd is optional) delimited by ";".
1. Name of the environment
2. LES folder
3. Optional: Path to registry file. If not provided it is assumed to be in data subfolder under LES and the name is assumed to be "registry"
Registry file has following entries that setup the environment:
• URL
• RMI-PORT:
• port
• classic-port
• Database section
With these two concepts, we can use the same set of source code and create multiple environments from it, i,e. dev environment and test environment could share the software but would have different databases. You can follow following steps to, for example, install software once and create different environments, e.g. dev and test:
1. Install the software (e.g. c:\redprairie\WM201214)
2. Modify the rptab file to create a new entry for your new environment.
• If you want a separate LES folder, then specify that otherwise specify the same LES folder as WM201214
• Specify the path to registry file. If you are sharing the LES folder, then this is important
3. Optional: If you want a different LES folder in test then create a new folder for your environment ( e.g. c:\redprairie\test\LES ) and copy c:\redprairie\WM201214\LES to this new location
4. Change the registry file for your new environment for URL, RMI-PORT, classic-port, and database section
5. Use c:\redprairie\WM201214\moca\bin\servicemgr.exe to create a new service and a new shortcut
Another variation may be to test the same data from two different code bases(what I described above is same code for different data). If you do this, make sure only one environment is running at one time
1. Point the registry in both environments to the same database
Top

Upgrading a RedPrairie(tm) Environment - The right way

How should we approach a RedPrairie^tm WMS (DCS) upgrade - from a technical perspective?  Should I put the CD in and let the installer's upgrade do the magic?  In this post I will explain my perspective on this.  My approach is as follows:

1. Never use the RedPrairie^tm installer's upgrade option
2. Always do a new install into an empty database
3. Then use the RedPrairie provided upgrade script.

Why not use the installer's upgrade option?

This is a general suggestion for all products that when we are approaching an upgrade, at that time we must keep in mind the possibility that the upgrade may fail.  If and when it fails, we want to be able to quickly fall back to the previous version.  Upgrades also take some time and during that time we do not want the environment to be unavailable.  When troubleshooting an upgrade, it is important to understand if the issue is with the upgrade or with the install itself.

How are RedPrairie^tm upgrade scripts organized?

Every sub-product in RedPrairie^tm; for example MOCA, MCS, SAL, DCS, etc. has a "db" sub-folder and within that we have following sub-folders:

Folder	Description
ddl	DDL scripts for creating tables, indexes, sequences
data	mload and slexp files
Upgrade	This folder has multiple sub-folders based on versions that contain scripts to upgrade the database

When we are doing a new install the scripts from "ddl" folders are used to create the objects.  When we are doing an upgrade the scripts from the "upgrade" folder are used.  So we definitely have two separate code paths.

If you look at this folder, you will also notice that by definition you should be able to upgrade from any version to the latest version.  So if someone is trying to go from 2005 to 2012.1.4 - that should work.  You should not have to go through intermediate versions.  Unfortunately sometimes RedPrairie^tm does not keep them straight so we run into issues.  For example what could happen is that say 2008 to 2009.1.4 upgrade scripts had an issue - but understand that RedPrairie^tm product group is working on 2010 version at that time as well.  So what should have happened is that the fix to the upgrade scripts should have been merged to with the active development branch - but it is possible that it was not done.  In such cases you may have to go through intermediate versions - but from an architecture standpoint that was not needed.

What is the "RedPrairie^tm provided upgrade script"?

If you go to the RedPrairie environment folder (parent directory of %MOCADIR%), it has a folder called "install" and then a subfolder "database".  In version prior to 2010, there was a "dupgrade.pl".  Now we have a "dbupgrade.jar".  This script is responsible for going through all the sub-products and running all the upgrade scripts.  Those upgrade scripts will create new tables, modify existing tables, and change data as needed.  The installer also runs this same script to do the upgrade.

How does RedPrairie^tm determine the current version?

Every sub-product has a special table called [product]_dbversion; for example moca_dversion, dcs_dbversion, etc.  This table has a single column and single row that has the exact version that this database was create with or upgraded to.  The upgrade scripts look at this version and compare to the current version (as reported by, for example, "list library versions") and figures out which upgrade directories to process.  When it is done with the upgrade process it updates this table.

Why install to an empty database first?

As we can see that upgrade is a different code path, installation will most likely have a better chance of success than an upgrade.  Then we will have a clean database to compare an upgraded environment to.  For example in one case when I upgraded from 2008 to 2012.1.4 there were over 1000 differences between that database and a clean database.  If I did not have a clean database to compare to, I would be constantly fighting battles to figure out what is going on.  Before any testing starts we should compare the upgraded schema to this clean schema and make sure it is ok.

I recommend that this pristine environment is kept permanently.  This provides a place where we can check if the issue is due to a customization or is in standard code.  If we are able to reproduce an issue in the standard code it will be much easier to obtain support from RedPrairie^tm.

So the exact steps are:

1. Create an empty database.  I always call them with embedded version#, e.g. PRSWM201214.  "PRS" stands for pristine.
2. Run the installer and choose "new" install.  Name it differently from the other environment on this server.
3. Install this into the newly created database.
4. Load the base data as well
5. Check to make sure the instance looks good
6. Now change the "registry" and point the "database" settings to the database that you want to upgrade.
7. Run the dbuprade from the command line
1. If version is less than 2010, run "perl -S dbupgrade.pl"
2. If version is after 2010, run "java -jar dbupgrade.jar"
8. Change the port in the registry if you want the existing clients to not change their connection.

You can choose some other variants to this approach as well, e.g.

• In step #6, do not point to exactly the same database but a copy of it.  This way you could keep both the old environment and the new environment running in parallel.  In this case in step #8 if port in the  registry for the new environment is changed, also change it in the old environment.
• You could install it twice one to keep the pristine environment running and one to upgrade.  This may not be necessary because it is very easy to create new environment from the same install - I will explain that in a separate blog post.

Conclusion:

RedPrairie^tm solutions are very intuitive once we understand the concepts and details behind them.  By taking this simple approach the task of upgrading will become simpler and more importantly if when the first attempts to upgrade fail we would not have created a mess for existing environments.