Decision Analytics. Integrating Lawson with the real world.
The LawsonGuru Letter
The LawsonGuru Letter, brought to you by Decision Analytics

Click here to forward to a colleague

April 2005

The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community. The LawsonGuru Letter is published by-and is solely the opinion of-John Henley of Decision Analytics. Visit Decision Analytics at http://www.danalytics.com. For subscription information, see the bottom of this message.

The LawsonGuru Letter is not affiliated with Lawson Software.
In this issue:
1. Guest Spot: The Power of SSH
2. Reporting, Part 11: Crystal Parameters and Lawson OLE DB Provider
3. Reader Feedback
4. Worthwhile Reading
5. Lawson Tips & Tricks

Alex Tsekhansky of Analysts International (AIC) is one of those people who has a gift for writing.  It seems that each time I publish one of his articles, he's all ready with the next one!  I'm so grateful that he is willing to share his deep knowledge of Lawson and its surrounding technologies with you.  Remember that this is a free newsletter, and the generosity of the Guest Spot authors is one of the primary reasons.  Are you ready to join Alex in the coveted Guest Spot?  If so, email me at mailto:letter-editor@lawsonguru.com.

1. Guest Spot: The Power of SSH
(by Alex Tsekhansky, Analysts International)

In line of my work as a consultant, many power users have heard about SSH, but almost no one actually uses it for any purpose. However, many of those users do not realize the power and convenience that SSH applications provide.  In many cases, SSH provides the only solution that can withstand the scrutiny of IT security as well as management business case review.

What is SSH?

SSH is an acronym that stands for Secure SHell. The most popular SSH application is in Telnet-like programs that allow you to login to the server and get you to the prompt. “What’s the catch?”, - you can ask. “Why would I use some other software instead of my favorite Telnet program, or Lawson LID, or Portal?”

The main power of SSH comes from a field of security. An SSH connection encrypts all traffic between an SSH client and SSH server, and establishes a secure point-to-point “tunnel”. One advantage of SSH is that all information (including names and passwords) are transferred encrypted between SSH client and SSH server. Another advantage is that SSH tunnel can be used to encapsulate sessions and piggy-back other services.

In a “normal” telnet connection traffic is perceived by the server as sent by a remote client.

In an SSH connection a tunnel may be configured in a way that a server “thinks” that a client is physically sitting at the server, i.e. the connection to the server originates from the server itself, and/or responses go to the server.

Why Use SSH?

One useful application of such configuration is related to running X-Windows. More and more software products (e.g. Oracle and Lawson web components) require X-Windows for installation or maintenance. Note that an X-Windows server (such as Exceed or XFree) needs to be running on your PC, and you would start X-Windows client (such as xterm or Java GUI program) from the remote server (e.g. the one that runs Lawson application).

In many cases if you connect to the Lawson server remotely, that server cannot establish connections with other servers outside company’s internal network. Hence, no X-Windows connection can be established in a “normal” way. SSH, however, was designed with X-Windows forwarding in mind. If allowed by the SSH server installed on the remote Lawson server, SSH will take care of forwarding X-Windows packets back to you over SSH tunnel, without the remote server establishing a new connection back to your PC over the internet. This is possible because the Lawson server “thinks” that you’re connected directly to the server somewhere, and your IP address (from X-Windows’ point of view) is the same as one of the server’s IP.

SSH Port Forwarding

The more powerful port-forwarding feature of SSH is a generalization of this setup. If allowed by SSH server, you can forward ports from your PC to ports of a remote server, and remote server ports back to your PC, or any other network device (PC, server etc.) of your choice.

Imagine that you only have the SSH port (22 by default) open between your PC and a Lawson server. SSH connection would allow you to set a rule as following:

HTTP connection to port 80 of your PC (standard HTTP port) will be “magically” forwarded via SSH tunnel to the port 80 of the remote server. Port 80, however, does not need to open on a firewall between you and a Lawson server. All functions will go over the SSH connection.

Add ports 23 (TELNET), 443 (HTTPS), and you can see that you can establish all Lawson related sessions over a single port – 22.

Since the connections are encrypted, and all SSH implementations allow one to specify locations you’re connection from and put limit on users, it is usually easier to convince management to use that feature.

Another interesting application of SSH allowed me to save a client money on long distance. The client allows a dial-up connection to the Lawson UNIX server, but not a VPN connection. But they did allow me to install an SSH client on their server. The server is able to establish SSH connections to other network devices (e.g. my PC) over the internet.

The setup works as follows:

  1. I dial to the client’s server and establish a regular TELNET connection
  2. I run “xterm” in “nohup” back to my home PC. Note that XTERM does not use the dial-up connection; it connects to me over the Internet. Since I ran it via “nohup”, I can exit my TELNET session now and close the dial-up connection.

 There I have a connection as follows:

XTERM window open on my PC provides me with a shell prompt on a client Lawson server.

Now I start SSH server on my PC and connect to it via SSH client that I start in the XTERM window. Anything that I run in XTERM window runs on the Lawson server. With remote port forwarding I can forward any ports of my PC to the ports of the Lawson server – including the ones I use for LID and web services. Now I can do all Lawson operations on the client server by connecting to “localhost” (my PC).

Using SSH to Transfer Files

SSH may also be used to copy files between a client and a server while preserving permissions, ownership (if UIDs are the same on both servers) and subdirectory structure, and not using any extra disk space. 

Since most FTP implementations cannot transfer whole directories and only individual files (especially the command-line clients that you normally see on UNIX), usually you would use TAR to create an archive of a subdirectory structure, then FTP it to the destination server, and then unTAR the archive. That, however, is hardly suitable for large copies, when you need to transfer gigabytes of information as the TAR archive will take as much space as the content of the directories you’re trying to transfer – on both source and destination server. The command:

$ tar cf – directory1 | ssh -e none server1 tar xf –

accomplishes the same result without creating intermediate TAR archives on either of the servers. The above command will transfer “directory1” directory with subdirectories from current server to “server1” while preserving permissions, ownership and symbolic links. Using similar method one can transfer directories and files from server1 to server2 while being logged in to server3.

Some SSH implementations come with a utility called SCP that does exactly the same, and relieves you from remembering the right parameters of SSH file copy.

Finding SSH for your Platform

SHH works on both UNIX and Windows. On UNIX many popular platforms come with SSH client and server already installed (though not necessarily started by default). On Windows, I use Open SSH, which comes as part of CYGWIN suite of free software downloadable from www.cygwin.com.

2. Reporting, Part 11: Crystal Parameters and Lawson OLE DB Provider

Over the past months, we've been looking at various aspects of using the Lawson's OLE DB Provider with Crystal Reports (see the article list at http://www.danalytics.com/guru/letter/). This month, I want to target a specific feature, which seems to be a hurdle for many users, yet is often the key to enhancing the performance of reports which use the Lawson OLE DB Provider.

How the Lawson OLE DB Provider integrates with Crystal

Recall that the Lawson OLE DB Provider connects to the Lawson application data via DME. The DME program returns what ever data is desired back to Crystal and Crystal applies any record selection you specify for the report:



When you use the OLE DB Provider's Query Builder, you can specify which indexes are used to access the Lawson data. What's more, you can also specify which records are returned, by populating the index values you want to be returned (e.g., all employees in Company 1). You can do this either at the time you build the query (which means you need to revise the query any time your selection criteria changes. Or you can "link" the index values up to your report's parameters.

This is an important-yet under-utilized and not very well-known-feature of the integration of the Lawson OLE DB Provider with Crystal Reports. When you specify parameters for a Crystal report, you want those parameters to be used, if possible, to force the data provider (the Lawson OLE DB Provider in this case) to return only the desired data. If you don't do this, the data provider returns all the data, and Crystal itself has to perform the selection.

How do I pass Crystal Parameters to the OLE DB Provider?

So, the big question is, "how do I pass parameters into the Lawson OLE DB Provider?" Well, the answer is simple, but it works from the opposite direction! You have to "tell" the Lawson OLE DB Provider that a given value is a parameter, and then Crystal will "create" a new parameter. Here are the steps:

1. Create your index criteria in the OLE DB Query Builder using the '?' question mark:



2. When you close the indexes dialog, you will see that the "&INDEX" parameter has been added to the DME query string:




3. Next, when you exit the Query Builder and return to Crystal Reports, you will see that a Crystal parameter field, Param1, is created:



4. Un-check "Set to Null" if it is set.

5. Note that the parameter will be named Param1 (and canonically, Param2, Param3, etc. for additional parameters). You should not rename this parameter, as it gets automatically re-created each time you return from the Query Builder back into Crystal Reports.

6. However you can change the "Prompt Text" for the parameter, as well as create a parameter list:

7. When you refresh the report, you will then be prompted for the Company, and the Lawson OLE DB Provider will return only the data for the selected Company:



Parameters can also be used for Filter

You can specify "?" parameters for Indexes, but you can also use them for Filters (non-index criteria). Although this won't speed up the Lawson part of the query (since it's not taking advantage of an index), it will reduce the amount of data that is returned from the OLE DB Provider to Crystal, therefore making the overall report run faster:

Specifying Multiple Values for a Given Index Field

More than one parameter "?" can be entered into an Index field criteria, creating multiple parameters (e.g., Param2, Param3, etc.). This means you can create range-driven parameters, as in "? -> ?" , which will create Param1 and Param2, where Param1 is the starting string and Param2 is the ending string. You can also separate values with semi-colons to provide a simple "list" parameter.

By the way, you can also use this ";" list and "->" range technique when specifying any index selection, not just those for parameter-driven values. For example, if you have companies 1, 2, and 3, and want to report on just company 1 and 3, specify the index as a "1;3", and the DME query will return just those records. Enter "1->2", and DME will return company 1 and 2.

Some Final Notes
The parameters created by OLE DB Provider are always "string" parameters. So, if the parameter needs to be used in a formula, you may need to use a Crystal string conversion function (e.g., ToNumber) to compare it to a number field.

You cannot pass parameters for date fields and conditional (derived) fields.

Those of you with sharp eyes may have noticed that I'm using the newest version of Crystal: XI. Stay tuned, because next month, I'll show you some of its slick new features, including the long-awaited dynamic pick lists for parameters!

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- QUOTE OF THE ISSUE –

“I can't give you a sure-fire formula for success, but I can give you a formula for
failure: try to please everybody all the time.”

- Herbert Bayard Swope
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3. Reader Feedback
Send your comments to mailto:letter-comments@lawsonguru.com.
 
Some comments on the March 2005 issue regarding the exclusion of non-partner consultants from CUE (see http://www.danalytics.com/guru/letter/archive/2005-03.htm):
 
“We wanted to go to CUE as well.  In fact they even let us register and took our money.  Early this year they refunded everything and told us we couldn’t go.  This not only came as a shock to us but our customers as well.  Never in my computer career spanning over 25 years have I seen such behavior from a manufacturer who wants to exclude people who work on their product at customers requests.  You are right in saying they may be a takeover target.  They are attractively priced today at $5.75 and still dropping.  We’ll be watching what happens in the coming months.  The writing may be on the wall.”
 
“Well, look at it this way, next year it will be called something else (whatever the new owners decide).”
4. Worthwhile Reading

A New Blueprint For The Enterprise
Enterprise architecture is not just about mapping and standardizing hardware and software anymore. Now it's about services, events and-get this-good old ROI.
CIO Magazine, March 1, 2005
 http://www.cio.com/archive/030105/blueprint.html

RFID Tagging for Hospital Patients
At New York's Jacobi Medical Center, more than 200 patients admitted last summer were tagged with radio frequency identification (RFID) chips around their wrists instead of the standard-issue plastic wristbands.
CIO Magazine, March 1, 2005
 http://www.cio.com/archive/030105/tl_tracking.html

Adventures in Babysitting
Backup day care can cut absenteeism, decrease turnover, and save employees from the stress of lining up a last-minute caregiver.
CFO Magazine, Human Capital Special Issue 2005
 http://www.cfo.com/article.cfm/3664763/c_3686543

Speed and Simplify Data Movement
Use Oracle Database 10g Data Pump for fast, flexible export and import.
Oracle Magazine, March/April 2005
 http://www.oracle.com/technology/oramag/oracle/05-mar/o25tuning.html

A New Way To Manage Vendors
CIOs grappling with an increasingly complex marketplace are finding relief-and better relationships with vendors-by forming VMOs within their organizations. Here's how.
CIO Magazine, February 1, 2005
 http://www.cio.com/archive/020105/vendor.html
5. Lawson Tips & Tricks
Share your tips. Send them to mailto:letter-tips@lawsonguru.com.

Using suspendpdl.sh (suspendpdl.bat) & resumepdl.sh (resumepdl.bat)

There are two very useful IOS commands--new in the 8.0.3 IOS/Environment:

suspendpdl.sh turns off RMI connections for a particular product line;
resumepdl.sh turns RMI back on.

This means that you don't have to shut down RMI for the entire environment, just for a desired product line. 

An example of this would be if you needed to run dbreorg on a TEST product line.  You can't run dbreorg with any open database connections.  Prior to 8.0.3, this required that you shut down the entire RMI pool (and therefore, no one could use Portal!).  Now you can use suspendpdl.sh and resumepdl.sh to take the TEST product line "off-line" while running dbreorg:

$ cd $CGIDIR/rmi
$ suspendpdl.sh TEST
$ dbreorg TEST
$ resumepdl.sh TEST

However, that you still can't use suspendpdl.sh and resumepdl.sh on the LOGAN or GEN product lines.  That requires a full stop of the RMI server.

These scripts apply to the UNIX and iSeries platforms.  On the Windows platform, use suspendpdl.bat and resumepdl.bat rather than suspendpdl.sh and resumepdl.sh.

The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community. The LawsonGuru Letter is published by--and is solely the opinion of--John Henley of Decision Analytics. Visit Decision Analytics at http://www.danalytics.com.
The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community. The LawsonGuru Letter is published by--and is solely the opinion of--John Henley of Decision Analytics. Visit Decision Analytics at http://www.danalytics.com.

To subscribe, visit http://www.danalytics.com/guru/letter/
Copyright © 2005, Decision Analytics. All rights reserved.
Please share The LawsonGuru Letter in whole or in part as long as copyright and attribution are always included.
Decision Analytics is an independent consultancy, focusing on Lawson technical projects, and specializing in customization/modification, data conversion, and integration/interfaces. Please visit http://www.danalytics.com for more information.
Decision Analytics. Integrating Lawson with the Real World.