Technology Shift

Simple Inventory and Sales Tracker Spreadsheet

2010-06-15T15:01:00.000-07:00

If you are looking for a simple way to track your inventory and sales, here is a Excel 2007 spreadsheet that could be of help.

This Simple Inventory and Sales Tracker Spreadsheet has a tab that shows your current inventory, sales and re-stocking transactions.

Click image to enlarge Inventory tab

Tracking Inventory
The Inventory tab shows the current state of your inventory. This automatically calculated by considering your beginning inventory, re-stocking and sales made for the product. It also contains the unit sales price used for sales transactions.

Recording Sales
Sales are processed using the Sales tab. To facilitate recording, a drop-down lists are provided with one containing all of the products listed in the Inventory tab and another for the methods of payment accepted. The unit price is also dynamically determined from the Inventory tab and used to automatically calculate the sales price.

Click image to enlarge Sales tab

Re-stocking transactions

Whenever you need to re-stock your inventory, every purchase should be recorded in the Stock Replenishment tab. This tab also contains the same drop-down feature found in the Sales tab. All transactions recorded in this tab will immediately be reflected in the Inventory tab.

Want to purchase this product?
This Simple Inventory and Sales Tracker Spreadsheet is an easy tool to use. If you are interested with this, this product is available for purchase for GBP 4.99 using the button below. Before you buy please ensure that you read the purchase and delivery process and our terms of use.

Credit Card and Consumer Debt Payment Calculator

2010-05-03T15:11:00.000-07:00

Click image to enlarge

Most people have a hard time determining the right payment allocation for servicing debts. In general, there are two prevailing thoughts on this subject. These two debt repayment strategies are snowballing (suggesting that the debt with the smallest balance should paid first) and avalanche method (focusing on the interest rate with the debt with the highest one being serviced first).

This Credit Card and Consumer Debt Payment Calculator spreadsheet is available to support these two debt repayment strategies - showing you how much money should be paid for each outstanding debt for that period. Aside from these two strategies, an third strategy is being introduced called Targeted Avalanche.

The Targeted Avalanche method uses the same principle as the Avalanche method with one minor difference. The Targeted Avalanche strategy uses the estimated interest amount to be incurred in the next period instead of the interest rate. In terms of the efficient use of your money, this is our preferred method of payment.

Using the Credit Card and Consumer Debt Payment Calculator
First, fill in the names of the creditor or lending company, the account balances for each debt and the associated monthly interest rate.Unlike other debt payment calculators, you don't need to sort your entries using any criteria. This automatically done for you.

Second, put in the required minimum monthly payments for each account. This is is the minimum amount you pay to avoid incurring penalty charges. For the credit card debts, your monthly statement will contain the information. For other type of debts, you maybe required to correspond with your lending provider to get the correct figure.

Finally, enter the amount of money available to pay-off your debt. This amount will be easier to determine if you prepare a monthly budget before preparing this from.

Want to purchase this product?
This Credit Card and Consumer Debt Payment Calculator is an easy tool to use. If you are interested with this, this product is available for purchase for GBP 4.99 using the button below. Before you buy please ensure that you read the purchase and delivery process and our terms of use.

ISPF as a training aid

2010-03-30T09:55:00.000-07:00

Recently, I received a query from a Project Manager - could I setup a training tool that could help application developers pick up site-specific commands. This was a major problem as his team is new and had limited experience on the application system being supported.

In the mainframe, this challenge is common with all the third-party and in-house utilities being added to the system. To address this, developers have two choices. First, they can either pick up the application manual but more often than not it is not available and sifting through the information in the manual could be quite a challenge. Second, they can search through existing programs and copy the syntax. The issue with this second approach is that this would not provide the complete view of the command syntax and may even perpertuate issues with mistakes being copied a couple of times better.

At first, my first suggestion was to create a set of ISPF help panels that the developers can invoke. This is a very viable solution with the availability of ISPF Tutorial functionality. But there is one small issue with the approach. Invoking the tool would mean leaving the Edit session and requires the need for Cut and Paste to copy the code syntax.

The solution
I realised that there is an even better solution more suited to this need - the ISPF Model facility. ISPF developers are familiar with this ISPF models as this provides syntax for common ISPF services/commands. I rely on this heavily when I was new to ISPF programming but have not used this recently. I remembered how quickly I was able to learn the command syntax which fits the requirement for this training tool request.

Developming the tool is easy. It involves extending an existing ISPF selection panels and the creation of new ISPF panels and skeletons (which are templates containing the command syntax). With this, the tool was developed in just two days and can easily be maintained by any developer.

The benefit
After introducing the site-specific ISPF models, the onboarding time for new member was drastically reduced. The feedback was that it was easy to use and contains all the information needed by the developer.

There were also unexpected benefits from implementing the tool. Even the experienced developers had been more productive and allow them to focus more on other value-adding activities as there was less time spent on answering queries on command syntax from new members.

The tool also provided a means to introduce coding standards. It was done by extending the command templates to include common error-trapping code stubs.

If you are interested with this project and want to know more, send us your enquiry and we can provide you the details for a minimal fee.

Mainframe FTP download in Excel

2009-11-11T15:48:00.000-08:00

One of the many tasks that IT professionals do when working in mainframe systems is to retrieve data from the mainframe to the desktop. This requirement may range from adhoc downloading of code for review or backup to regular periodic reports.

Although there are processes integrated within the mainframe emulators, most of these involves alot of setup everytime you need to download a file. With this, I thought of creating my own tool that would simplify the process.

The FTP in Excel tool requires far few information to use. Some of the fields like the TSO ID and hostname can be setup just once while others have choices provided through dropdown lists. In other cases, the values have default values just in case you forgot to populate them.

To use the too, you have to provide the following information:

TSO ID which corresponds to the userid you provide in the TSO/E Logon screen
Mainframe host name refers to the name of the mainframe you are trying to connect to. This corresponds to the value associated to the "System ID" field found in the ISPF Primary Menu Panel. Another way to get this is through the ZSYSNODE variable found by choosing the the Dialog Test/Variables option from the ISPF Primary Menu.
Mainframe Dataset name column contains the list of datasets you need to access in the mainframe
Text Filename column contains the list of local files to be used. Please ensure that you include the file extensions. Common extensions are .txt for text and .csv for comma delimited files.
FTP Direction determines whether you are uploading or downloading data from the mainframe.
Type tells the Excel what of data you are downloading, whether it is text-based (for txt and csv files) or binary files (IMG).

Once you have set the required information, you are now ready to proceed with the donwload by pressing the Process FTP Request button. This prompt you for your TSO/E Logon password an then proceed to process your request. This may take a few minutes depending on the number and size of the files but it will prompt you once the process is complete. You can now view the files you created (in the same folder where you save the FTP in Excel tool for downloads and in the mainframe system for uploads). That is how simple it is.

If you are interested with this tool, you can purchase this for GBP 4.99 using the button below. Before you buy please ensure that you read the purchase and delivery process and our terms of use.

Envelope Method Budgetting in Excel 2007

2009-09-04T11:44:00.000-07:00

Most people find tracking where their hard-earned money went hard. There are many solutions available but nothing works as good as the envelope budget method.

The concept of envelope budget is pretty simple. When you get paid, you allocate all of your money into pre-determined outgoings (expenses and savings) - traditionally through marked envelopes. This way you guarantee you don't spend more than your income or beyond what you allocated for each expense category.

But rather than using actual envelopes, we can set this up using Excel. This Envelope budget implemented in Excel 2007 follows a check register format. The top columns represents the common expense categories. funds for long-term goals and savings for short-term goals.

To use the tool, you just need to record all cash flow transactions. Budget planning is done everytime you receive any cash using the "Money in" column. All records with non-zero "Money in" column shows the budget plan for the amount received. To help you out, the periodic average for each expense column is calculated and the remaining unallocated cash is shown in the "Free amount" column.

As for the cash outgoings, the amount incurred will be recorded on the approrpriate category on the day it is payed. The unused allocation for each category is being tracked through the "Balance" row. As the category balnce is being used up, the cell changes color - turning to red when the balance falls to zero.

This Envelope based budget workbook in Excel 2007 is an easy tool to use. If you are interested with this, you can purchase this for GBP 4.99 using the button below. Before you buy please ensure that you read the purchase and delivery process and our terms of use.

Mainframe environment setup for system migration

2009-06-20T16:11:00.001-07:00

A multi-year rationalisation project is being pursued to consolidate the mainframe processes. Currently, there are as many mainframe systems and applications as there are different geographic customer market. This fragmented architecture resulted to a complex environment to support and maintain.

To implement this strategy, it was decided that a common system should be used by all branches representing a geographical market. This is to be done by migrating to the new system one branch at a time.

As part of the conversion, the developers would need to setup branch-specific datasources and jobs/jcls. Prior to our involvement, this task was done manually by two offshore contractors and completed in at least six weeks (depending on the size of the branch). With the number of branches to be converted, this turnaround time was unacceptable.

This issue was addressed by introducing a set of automation utilities to accomplish the task.
These utilities took one person five days to develop and test. The results is a set of customised jobs and VSAM file allocations specific for the job.

With the automation tools, the branch setup process now takes just four hours and requires just one developer. The process also resulted to less errors that is inherent to manual process.

Re-engineering the way we work

2008-11-25T16:17:00.000-08:00

"What if the business is apprehensive to re-engineer the current system? Are there other opportunities for pursuing a re-engineering initiative?"

This is how my conversation with the Development Area Manager started today.

From the initial discussion we had on re-engineering a few weeks back, he went to our business partners and pitch the idea to them. The initial reaction we received was a bit lukewarm. Without the support of the business partners, we are looking for other ways to push the re-engineering agenda.

Innovate how we operate.

Rather than focusing on the client system, we can use the same re-engineering principles on how we work. The approaches used in systems re-engineering can be applied in innovations for application delivery and maintenance.

To do this, we will need to review the processes involved in application development and maintenance. By revisiting each phase on the software development life cycle, we identify the tasks we can re-engineer. To help us in our analysis, I have identified the following categories that we can focus on.

Recurring, repetitive and manual intensive tasks. From experience, most proposals will fall in this category. Not only are these are easy to identify, most developers will be drowning you with proposals as these are tasks we hate doing. It is good to work on these category first (especially since we are just starting to build the skills of our team) as the re-engineering activities required are simpler.

The re-engineering approach for these type of tasks requires a combination of standardisation and automation. First we need to establish the series of steps required to accomplish the task. From these steps we can determine which of them (if not all) we can automate through mainframe facilities like ISPF Edit Macros and ISPF Services.

Tasks with wait times or requires monitoring. Monitoring activities (like those by Production Support staff) are essential to ensure that exceptions are addressed promptly. Although important, the time spent waiting are opportunities better spent on activities that adds more value. With this, it is important that we find other ways of monitoring aside from
having somebody continuously looking at a computer screen until an event happens.

For monitoring non-critical activities, we can avoid manually monitoring them through the use of automated notification - the simplest of which is the email notification through the SMTP (Simple Mail Transfer Protocol) service. We can consider modifying a non-critical job to send the error details to the appropriate group if exceptions are encountered.

SMTP can also be used for automated report distribution. Rather than have someone kick off a process to create and email a report, we can replace this with a mainframe job to be triggered when the input file is created to process the file for us.

Tasks where human decisions are made. I am not suggesting that we replace all of the intelligent decision making activities with an automated process as this is not possible. In situations where the decision making is complex, we can help the decision maker by ensuring that all the information he needs are easily accessible. The benefit with this is that we don't change the way people work, we just eliminate the administrative part and let them focus on the parts of their job that adds value.

Tasks requiring system downtime. The activities that fall in this category overlaps with the other previous categories. We need to further categorised them into this type because of their impact to the business. System downtime (whether the downtime is for software migration or data/error recovery during production support) means there are no business activities in our system. Business opportunities are lost on periods when our system is inaccessible. With this, we must give special attention to activities falling in this category.

Pushing re-engineering forward.

There is a good incentive to devote the time it takes to re-engineer our development processes. If done right, these re-engineering successes will be the best marketing tool in selling the re-engineering agenda to our business partners. The tangible results are more powerful than any PowerPoint presentation we can muster. This will open up a wider horizon of opportunities for us in the future.

Succeeding through incremental re-engineering

2008-11-12T10:04:00.001-08:00

If it ain't broke, don't fix it!

It is the most common argument against a re-engineering agenda. It is argued that although the current system maybe slow and inefficient, people were so dependent on them for so long that migrating to an untested system is unthinkable.

Today, I heard this again and then some more.

I was invited by our Development Area Manager to have coffee with him. This gave us the chance to discuss how we can pursue an effective re-engineering initiative. I was asked based on the recommendation of his colleague (another Development Area Manger for another business) who worked with me in the past in pursuing a re-engineering agenda together.

In this conversation, the primary concern raised was on the area of risk. Previous experience shows that re-engineering has a high degree of failure and probability of introducing errors to the system. The larger the system the higher the degree of risk one has to take.

There was also the concern of the perceived high costs related to re-engineering. Most re-engineering pursuits in the past had been expensive - driven by much hyped tools and processes. There is additional associated costs on supporting the new system until it stabilises (which can take longer that what management are willing to accept).

I understand these concerns as I have witnessed these issues manifesting while pursuing re-engineering in the past. But these concerns are driven by one factor - size. With this in mind, these concerns can be managed by implementing re-engineering initiatives in smaller chunks.

The incremental re-engineering framework, also known as evolutionary re-engineering, is the process we have followed in the past. Through incremental opportunistic approach, both the risk and cost concerns can be addressed.

Focusing on a smaller part of the system provides better error recovery. Given the smaller size, errors are easier to investigate and fix. If a major issue is found, the re-engineered component can be backed out with less disruption to the system.

Given the size of some re-engineering projects, development costs can sometimes be absorbed by current projects. We have demonstrated this through the new batch setup utility we created last July. We used the the first week allotted to the branch setup if done manually to create this utility. Not only did we complete the setup before the two week deadline, we now have a utility that drastically cut turnaround time for every new branch we setup.

For managers, incremental re-engineering offers other advantages. The incremental approach provides increased flexibility to address changing priorities and strategy. Fewer resources are committed to re-engineering projects in shorter period of time. This allows managers to re-align resources quickly if required.

There is also a high degree of user acceptance. For some of these re-engineering initiatives, the changes are least disruptive, if not transparent to the user. In other instances, these focused projects provides immediate and tangible benefits (improved response time, simplified process, less errors) that are quickly noticed by users.

With these advantages, incremental re-engineering projects provides a better chance of success. These advantages together with the successful project implementations will make it easier to justify incremental re-engineering projects to management.

Excel Bullet Graphs to Replace Gauges

2008-09-18T14:11:00.000-07:00

Executive dashboards are now common in business. A dashboard provides key data that is concise, easy to read and to interpret. To do this, one of the common tools used are gauges.

Although it is widely used, gauges have one big flaw - huge space is needed for implementation. As a replacement, Stephen Few (Perceptual Edge) suggested a new bar graph variation called bullet graphs. Stephen created a good document discussing the bullet graph specification so I will not be discussing it here.

Instead, I will discuss how to implement the last variation of the bullet graph that Stephen Few discussed in the specification document. This version of the bullet graph will not only tell you how far you are from your target but will also show whether you are on track in meeting this target.

For demonstration, I will be re-creating in Excel the example bullet graph shown below.

Chart Data Preparation

First we need to prepare the data to be used by the chart. To create the graph, the appropriate values are provided in the cell range A3:E3. These values are used in controlling the information in the Chart Data.

Set cells A6, B6, A8 and B8 to zero. Cell A7 contains the Current value so this should point to A3. Cells C6 and C8 contain the Poor limit so these are set to C3. Cell E6 is pointing to the Target value in E3.

For the other cells, specify the following formula:

=MAX(B3-A7,0) in cell B7
=MAX(C6-SUM(A7:B7),0) in cell C7
=D3-C6 in cells D6 and D8
=MAX(SUM(C6:D6)-SUM(A7:C7),0) in cell D7

Creating the initial Stacked bar graph

To create the initial bullet graph, follow these steps.

Highlight the range A6:D8 then select Insert>Chart from the menu.
On the Standard Types tab in the Chart Wizard, select Bar from the Chart type and Stacked Bar in the Chart sub-type. Click Next.
Choose Columns in the Series in option. Click Next.
Uncheck all options in the Gridlines tab, the Show legend option in the Legend tab. Click Next then click Finish to show the stacked bar. It should look something like this.

Updating format of bullet graph

Now, we would need to change the color and presentation of the columns.

Right click any data point in the graph and choose Format Data Series. Click the Options tab and set the Gap width to 0. This will remove the gaps between each stacked bars.
For each data series, update formatting to match the one shown in the example. Remove all borders for each data series and change the color appropriately. Series 1 and 2 are both blue with series 2 set to a shade lighter. Series 3 and 4 are both gray with series 4 a bit lighter.
Remove the border from the Plot area.
Right click the X axis to format it. Click the Font tab and uncheck the Auto scale option. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (0, 300, 50). I advised this step so that you control the scaling.

After these steps, you should something similar to this.

Adding the Target line.

The target line is added through the steps below.

Right click in any area of the chart and choose Source Data. Click the Series tab then click the Add button to introduce a new data series named Series 5. In the Values field, point it to cell E6. Click OK
Click the new data series added and update the formula from =SERIES(,,Sheet3!$E$6,5) to =SERIES(,Sheet3!$E$6,2,5).
Again, right click Series 5 and choose Chart type. In the Chart type, choose XY(Scatter). Click OK.
Right click the secondary X axis (the one on top) to format it. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (0, 300, 50). This is done to ensure consistency of the scale.
Right click the secondary Y axis (the one on the right) to change the scale. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (1,2, 1). This is needed to position the line in the middle.
Again, right click Series 5 and choose Format Data Series. In the Pattern tab, set the Marker to None. Click the Y Error Bars and Both for the Display, and set the Fixed value to .7. Click OK.
Right Click the Error bar for Series 5 and choose Format Error Bars. In the Patterns tab, change the appropriate color, weight and marker. Click OK.

Some more tidying up.

Right click the Chart area and choose Chart options. In the Axes tab, uncheck the all options except for Value (Y) axis of the Primary axis. Click OK.
Right click the Chart area and choose Format Chart area. Set the Border to none in the Patterns tab. Click OK.
Resize the bullet graph as desired and add the needed text. I recommend that the text should be in a cell instead of using the Title option in the chart.

After all these hard work, you should see something like this.

Bridging the automation gap

2008-09-10T15:00:00.000-07:00

Process re-engineering is back on the management agenda. Pressured by the global economic downturn, big and small outfits alike are pursuing this to cut costs and return to profitability.

For mainframe development teams, process re-engineering is synonymous to automation. The management from the companies I currently and previously work understand that this is the way to go to improve the efficiency of the way deliver software.

To promote this, my previous employer required all developers to implement two productivity tools for the year. My current employer on the other hand has a similar but less direct approach - they pass on the responsibility to the team leads to pursue.

On the surface, both approaches looks feasible. By giving everyone the opportunity to pursue automation, you would expect a greater number of tools to be created. This should be easy as developers offers the same type of service to other non-IT departments. Right?

But the truth is, this is not always the case. In most teams I joined, there is no indication that automation is part of the development process. I ended building the team to be able to pursue this through mentoring and coaching.

How could it be that the IT personnel, the very people whose primary responsibility is closely linked to innovation, does not do the same in their everyday work?

Simple. Most mainframe developers does not have the necessary skills for automation. The facilities needed are different from the development tools used for delivering solutions to the client. Seldom do developers have the opportunity to learn to work with these tools in spite of their availability.

Part of the reason for this is our sole focus to solutions delivery that kept us from looking at our own processes. We are exhausted trying to deliver the next release that we have no time left to take a step back and discover how we can do our own tasks better.

To succeed, company perception needs to changed. Most often, automation is pursued by companies on a brink of disaster. Costs are running high and automation is deemed to be the quick fix.

Automation should be part of a long-term mainframe strategy. Focus should first be on training to enable developers to pursue this. Better results will follow if you have a good team behind it.

Not all mainframe tools are created equal

2008-08-15T17:02:00.001-07:00

Allan (a former colleague who is now working for Accenture Dublin) needed some direction on one of his tasks. He needed to remove extra spaces on fields found in a file. Each 600-byte record is composed of 50 pipe-delimited fields. It is a simple task normally done through ISPF Edit (change) command.

Not this one.

The file involved contains more four hundred thousand records. Given the size, it is not possible to open the file in Edit mode. The only choice he have for the update is through code.

Choosing the right tool.

Most developers will do this in Cobol using indexing techniques on character arrays. The issue with this approach is its inefficiency. given that each character in the 600-byte record needs to be checked (and potentially moved), the amount of CPU processing needed is high. Multiply this by 400,000, then it becomes prohibitive.

There are others who will opt for interpreted languages. commonly used are CLIST, REXX, Easytrieve and SAS. Using some of these may result to less CPU processing but not low enough to be acceptable.

Allan knows about the issues with these approaches. Instead they turn their focus on utilities mainly used for data manipulation. As suggested by his team mate, they considered using Fileaid. Based on experience, I mentioned that Fileaid requires high CPU time that it may not be suited for their need. True enough, their testing confirmed this as it took 1.5 minutes (CPU time) to process just 2000 records.

For this task, DFSORT/Syncsort will be a good tool to use. Allan was somewhat expecting my suggestion as he saw me use them extensively in the past. With five lines of code and a quick test, they found an acceptable solution. Their sample run of 15000 records only took .03 minutes (CPU time) to complete.

This is why being familiar with what your mainframe system has to offer is crucial. In today's world, the accomplishment of the task is not enough. Doing it efficiently should be our goal. With mainframe's long history, a wide range of utilities is available to us. Many of these have overlapping functionalities. This is your key to be able to find the efficient solution suited for your task. There no easy way to do this but but to continuously explore the tools in your system.

How to use variables in SORT Cards?

2008-07-29T14:54:00.000-07:00

Do you know what a sort utility is in the mainframe? DFSORT or Syncsort maybe? How well do you know it?

Most probably, you have used it (or at least seen it being used) in mainframe jobs. It is quite common and simple that you can easily be misled into thinking that you know how the tool works. Most often than not, there are many features that you may not be aware of even when you frequently use this tool.

One common complaint is that you can not pass parameters to a control card within a jobstep. You will be surprised to learn that this facility have been available for quite awhile now. Here's an example on how you pass variables to a sort control card.

//S1 EXEC SORT
//SYMNAMES DD *
CONDVAL,C'MIKE'
//SORTIN DD DSN=YOUR.INPUT.DATASET,DISP=SHR
//SORTOUT DD DSN=YOUR.OUTPUT.DATASET,
// DISP=(NEW,CATLG,CATLG),UNIT=SYSDA,
// DCB=(RECFM=FB,BLKSIZE=0)
//SYSIN DD *
INCLUDE COND=(1,4,CH,EQ,CONDVAL)
SORT FIELDS=COPY

This looks like a typical SORT step found in JCLs except for the added SYMNAMES dataset definition. Sort's variable definition follows the "name,value" syntax with each variable defined occupies one line.

name,value

You can refer to the variables in the SYMNAMES dataset in your control card instead of directly declaring the value. This allows you to have a static control card with the ability to accept dynamic values.

Let me ask again. How well do you know SORT? If not well enough, take time to learn its features.

Payoff from mainframe automation

2008-07-15T14:43:00.000-07:00

Remember the mainframe automation tool that we recently deployed? After a little more than a week, we are now realising the dividends from our little experiment.

From the beginning, we did not incur any additional cost as the tool was developed on the time allotted for the original task. So any time it is used after the initial one produces cost savings for us.

The first opportunity came today.

Because of a miss during analysis and design, we needed to regenerate the files we created for the new branch. The error was caused by incorrect parameters supplied by the designer that was used during the initial run.

Without the tool, the rerun would have taken us back around two days. Now, it only took five minutes to setup the job and another twenty minutes to generate the files. The designer and the project manager were relieved that we were able to address the issue immediately.

The files are crucial for the test phase. No testing can occur without them. A delay in setup greatly impacts the critical path with its cascading effect to the succeeding phases of the development life cycle.

With the quick turnaround because of the tool, the issue was addressed with minimal impact. In the eyes of the user, the fix was transparent as the users were able to begin testing on schedule.

This is just the direct measurable, quantifiable benefits for us. The biggest benefits from automation is flexibility and agility.

Developers are juggling multiple tasks all the time. Without automation, you need to pull them away from their current task to address the issue. This distraction impacts their creativity and their ability to perform on their current task. The longer the distraction is, the greater its impact.

With automation, you limit the disruptions if not totally remove it. By having a simple tool, you have a choice of letting other developers who are free to do the task and allow others to continue with their current tasks.

The tool's simplicity also addresses the issue of single point sensitivity. Instead of having the business intelligence solely on the developers, this is now integrated within the tool.

Developers also benefits from automation. This provides a level of agility that allows us to focus on tasks that adds more value by freeing us from mind-numbing, repetitive and time consuming ones. This provides opportunities for learning and for finding ways to do our job more efficiently and serve our clients better.

These are the reasons why I believe that automation should be pursued. Of course, having the quick payback this week just made argument for it a lot easier to make.

Discovering mainframe automation

2008-07-07T17:09:00.000-07:00

It finally happened - a team member took a bite on my automation bait.

In the first few weeks from joining the company, I created three ISPF Edit macros and presented this to the team. I did a demonstration and shared the code. I was hoping it will influence the team towards the automation route (similar to what I did with the previous teams I work with).

I thought that my strategy did not work as months passed with nothing happening. But everything changed today.

One of the application developers went up to me for some advice. He is currently working on a new branch being created in the system and was assigned to generate all of the datasets needed for all environments.

The entities needed in the file creation process already exists but needed to be customised. This means copying all of the jobs and control cards used in the last branch setup and updating certain strings within each entity to the values associated with the new branch.

It is a simple job that can be achieved by ISPF Edit commands. The only issue with this is that he is dealing with more than five hundred files and at least six development environments. Editing more than three thousand entities is very tedious and prone to errors. Nobody in his right mind should do this.

With this, he remembered my talk about automation back in January and was hoping if I could help him out.

Before I gave my response, I asked him to show me how he would do it manually. First, he open a member to edit it. He then applied some filters using Exclude all ISPF edit command to isolate those lines to be updated. Lastly, he executed a series of Change all Edit commands to apply the updates needed.

Based on what I saw, it is a good candidate for an ISPF Edit macro automation. The steps are defined and can easily be translated to executable calls in REXX.

To create the macro, we started with a basic template in REXX shown below.

000001 /*REXX*/
000002 address isredit "macro"
000003 <insert commands here>
000004 exit 0

Now all we need to do is insert the commands specific for the task. Using the manual steps I was shown previously, we proceeded in translating this commands to REXX calls. This is done by concatenating the address isredit text to the commands entered in the command line. So an exclude command below:

EXCLUDE DSN ALL

will appear as

address isredit "EXCLUDE DSN ALL"

in the Edit macro.

A few more lines (less than ten) and the automation tool is finished. We tried it in one entity and it worked as expected.

He was too eager to use the macro that he started editing the other members in the library. I stopped him from doing so because we can do so much more. From my point of view, the tool is not complete.

I suggested that he does not need to manually go through each member as this step can also be automated. We can to create a REXX executable that lists all members in the library, goes through each member in the list and executes the macro to do the update.

Another thirty minutes to create the additional REXX executable and we are done. We tested it and it ran for three minutes to update all members in the library. By doing this, we have completed a task estimated for two days in less than three hours (less if you only consider the actual development time, taking away the time spent in mentoring). Best of all, this tool is available the next time a new branch is setup.

After that, he is hooked. He came up with the same conclusion as I had before - "This is the way forward". Now, he is taking his new tool a bit further by providing a user interface to make it easier to use. From the looks of it, he will be finished before the week ends.

From experience, I have encountered many similar tasks that can be solve by a simple ISPF Edit macro. You may have one in mind right now and I maybe able to help you out. If you are interested, just contact me.

Mainframe automation for logical screens setup

2008-06-10T16:58:00.000-07:00

Mainframe developers working in the ISPF environment need to have more than one logical screen to work effectively.

You have an edit session for the source code, another for Changeman, maybe another one for the listing (SDSF or maybe IOF). Most environments are setup to allow you to create up to eight screens (although the actual limit is 32).

Probably you have a list of preferred screens that you setup during startup. Most likely, you have a recorded macro that does this for you. This is fine but there is one issue with doing this.

There are many mainframe emulators out in the market - Hummingbird, Eicon Aviva, Extra. A recorded macro will not be portable between these tools.

To address, I recommend a another solution - creating a TSO command to do this setup.
Below is an copy what I currently have in example of

/* REXX */
/* Name : INITSCR */
/* Website : WWW.TECHNOLOGYSHIFT.COM */
ADDRESS "ISPEXEC"
"VGET (ZPANELID)"
"SELECT PGM(ISPSTRT) PARM(SCRNAME ST PERM;SDSF;ST;SWAP) "
"SELECT PGM(ISPSTRT) PARM(SCRNAME SRC PERM;3.4;SWAP) "
"SELECT PGM(ISPSTRT) PARM(SCRNAME CHK PERM;SCRNAME ON;SWAP) "
ZCMD = "SCRNAME TOOLS PERM"
"DISPLAY PANEL("ZPANELID") COMMAND(ZCMD) "
"SELECT PGM(ISRDSLST) PARM(DSL USERID.TOOLS) "
EXIT 0

Copy this code and paste in a new member in one of the concatenated SYSPROC libraries. To invoke it, you should type in the command line TSO followed by the member name. In mine, the command would be TSO INITSCR.

To customised this, you should know the following.

ISPSTRT is the program needed to create a new screen. As you can see, the PARM contains the commands you needed to execute for the screen

ISRDSLST will show you the dataset list (3.4) using the name specified in the PARM

That's it. Regardless of what mainframe emulator you use, this will work in any of them. Try it and see how it goes.

Simplifying Excel formula

2008-06-08T07:30:00.000-07:00

Have you worked with a formula that because it is too long ends up wrapping around a cell when you tried editing it?

Later you realise you made a mistake and updating it is confusing. Worse, this formula is used in several cells so missing one update is easy.

This is a common issue that Excel users encounter. Good thing there is a simple way to address this.

Before you start, you will need to change an option within your Excel session. This is needed to ensure that the formula you are creating will be consistent even if applied in different cells.

Go to the Tools menu and choose Options.

A pop-up window will be shown. In this pop-up, click the General tab and check the R1C1 reference style setting. You should see something similar to the one below.

Now you are ready to for the solution. First, you create and test the long formula in the cell where it is needed. I have included below an actual formula I am using. One thing to note is that the same calculation is needed on the cells below it.

Once you are satisfied with the formula in the cell, you will need to copy the formula to the clipboard.

The next step will be to define a name but instead of specifying a range in the Refers to textbox, paste the formula you previously saved in the clipboard.

The new defined name is now ready for use. This is like any other function that returns a value. So on all cells that needed the formula, type equal sign and then the name you defined.

That's it. If you need to correct some errors, you only need to update it on one place (in the Define Name option) and the changes will be reflected on the cells that calls it.

You can even make this simpler by breaking your formula into segments (with each defined with a unique descriptive name), making your formula easier to manage.

Why don't you try it and see how it goes.

Automated email notification for z/OS

2008-05-19T15:01:00.000-07:00

Despite the tools available in the mainframe, there are still tasks done manually.

Operation support personnel are needed to monitor jobs for abends. They spent their days staring at the screen, refreshing the screen manually by pressing the Return key.

Even mainframe developers cannot escape this. When you are doing testing, you may be required to wait for a long-running job before you can initiate your own tests.

Fortunately there is a simple way to address this using the email service commonly available in the mainframe.

Since most companies are using TN3270 (with TCP/IP) to connect to the mainframe, the Simple Mail Transfer Protocol (SMTP) service is probably already active in your system. If not, you will need the help of your systems support group to enable it.

Getting started.
Setting up a JCL that sends email from the mainframe is easy. You can start with any copy step using mainframe utilities with repro capability. IEBGENER, IDCAMS, SAS, Fileaid, and Eztrieve are some of the many choices available.

My personal preference is SORT and the jobstep will be similar to the one below.

//SORT EXEC PGM=SORT,REGION=0M
//SYSOUT DD SYSOUT=*
//SORTMSG DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=your.email.dsn
//SORTOUT DD SYSOUT=(B,SMTP)
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),DISP=(NEW,PASS)
//SYSIN DD *
SORT FIELDS=COPY

As you can see, it looks like any regular SORT job. The only noticeable difference is with the output (SORTOUT). Instead of a dataset, the output is being routed to the sysout that will be picked up by the SMTP later for processing.

There is another difference that is not apparent. The data found in the input (SORTIN) requires to follow a certain format. The simplest of which is shown below.

helo hostname
mail from: <youremail@address>
rcpt to: <receiver@address>
data
From: youremail@address
To: receiver@address
Subject: your subject

your body text

You need to update the items in italics. Most of these are familiar variables found in any other email except for the hostname. This variable refers to the JES node name for your system.

The items you need to think about are the subject and the body text. For abend monitoring, the common information included in the subject are the jobname, abending step and the return code. Depending upon the nature of the abend, you can include additional details in the body text of the email.

Beyond end-of-job notification.
What if you can do formatting?

How about adding some attachments?

These capabilities are available through Multipurpose Internet Mail Extensions (MIME). MIME is the standard for multi part, multimedia, and binary data in e-mails.

MIME is important as it opens the opportunity to create reports and sending them directly to the users. No more need for printing. Reports are sent on time. There is an added security as no other person has access to the reports except for the intended recipients.

Email standards discussion is another article in itself. If you want to learn more about this, I recommend reading this good article from Planet MVS.

Excel solution for a mainframe task

2008-05-18T12:17:00.000-07:00

People always goes back to what is familiar. IT professionals are no different.

When confronted with an issue, mainframe developers will look for solutions within the legacy system. Most will deal with these using Cobol. Others will lean on using other utilities.

But sometimes you can get solution outside of your development platform.

Previously, I have shown how to create a simple utility that can convert julian date to a gregorian format. Now, I will show you another simple alternative using Excel implemented through the formula shown below.

=DATE(A1/1000),1,MOD(A1,1000))

where A1 is the cell containing a number representing the julian date with ccyyddd format.

Different approaches. Both delivers similar level of simplicity.

So why even bother?

If your focus is on cutting mainframe-related costs, moving processes away from the systems can help. There are many opportunities for this in the area of data analysis and manipulation. How much you can gain will depend on how much of your processes you can migrate.

Non-mainframe solutions can provide a fail safe process. Connection issues, although maybe a rare occurrence, will impact your ability to deliver - especially those high-profile adhoc requests.

For mainframe developers, this breaks the monotony of green texts on a dark screen. This kind of exercise nurtures creativity as it allows you to think differently.

So the next time you are assigned a mainframe task, try coming up with two solutions - one mainframe and another non-mainframe. See how it goes?

Julian Date Conversion : Automation in five lines

2008-05-14T15:26:00.000-07:00

Working with legacy data, it is common to encounter dates in Julian format - a product from an era when a character of data was very precious. This format is meaningless to most unless converted to the more familiar Gregorian format - a task we can easily automate.

You may already have a solution in place in your system. Just in case you don't, I am sharing one with you.

First, create a new member in one of the SYSPROC libraries. Copy the code below and paste to this new member.

/* REXX */
PARSE ARG JDATE
GDATE = DATE('S',JDATE,'J')
SAY GDATE
EXIT 0

That's it. Five lines of code and you are done.

To use the tool, type TSO membername yyddd in the command line. The membername corresponds to the name of the new member created and yyddd is the Julian date to be converted.

This is one of the very first tools I created. The tool's simplicity does not diminish its value as I and the people I shared this with are still using this even today (although the one I have was modified to include a GUI).

Now, if you hear that automation is complex, show them this example.

Mainframe tool for regression testing

2008-05-13T14:14:00.000-07:00

It's just a typical regression testing for a mainframe developer. You submit a JCL and wait until it is completed. You open a new session window to review the output dataset and verify that it matches the expected results. Quite simple.

But when you spend your whole week repeating the same task, this is very mind numbing. You are bound to commit errors. You can't do away with it so you must find ways to address this - by automating some of the steps in the process.

One of the thing you can do is be able to view the output datasets without getting out of the JCL (without starting a new seesion window). An ISPF Edit macro is appropriate for this type of task.

To create one, you will need to edit a new member in one of the libraries associated to SYSPROC. Copy and paste the code below to that member.

/* REXX */
/* Visit WWW.TECHNOLOGYSHIFT.COM */
ADDRESS ISREDIT
"MACRO"
"RESET"
"(CURRLN) = LINE .ZCSR"
CURRLN = TRANSLATE(CURRLN,' ',"=,'")
"(ROW1,COL1) = CURSOR"
CURRLN = LEFT(CURRLN,POS(' ',CURRLN,COL1) - 1)
CURRLN = WORD(CURRLN,WORDS(CURRLN))

IF SYSDSN(CURRLN) = "OK" THEN
ADDRESS ISPEXEC "VIEW DATASET('"CURRLN"')"

EXIT 0

To invoke the macro, you just go and edit any JCL you are testing. Type the macro name (corresponds to the member name) in the command line, position the cursor under the name of the dataset you want to review, then press enter. You should be viewing the dataset now.

Congratulations! With a few lines of code, you created your very first automation. Now you see how simple it is?

Excel's "missing" date function

2008-05-12T15:32:00.000-07:00

While working on one of my personal projects, I needed an Excel date function that I have used before. I typed the formula in a cell and a weird thing happened - the Intellisense did not show me the parameters I need to put in.

So I did what a clever Excel user does, press F1. To my surprise, my query did not retrieve anything from Help. I know the function exists because Excel was able to recognize it and I have used before.

You know what I found out - it is still available but undocumented. Typical Microsoft behavior. Luckily, I was able to find the syntax for it.

(Re)Introducing DATEDIF

DATEDIF returns the difference between two dates. The results is expressed in either years, months or days.

Syntax

=DATEDIF(start_date, end_date, return_type)

start_date is the date that represents the start date

end_date is the date that represents the end date

return_type determines the type of the return value.

Interval Value returned

m Complete calendar months between the dates.

d Number of days between the dates.

y Complete calendar years between the dates.

ym Complete calendar months between the dates as if they were

of the same year (disregards year in evaluation).

yd Complete calendar days between the dates as if they were

of the same year (disregards year in evaluation).

md Complete calendar days between the dates as if they were

of the same month and same year (disregards both year and

month in evaluation).

I have tried the function parameters written here but I can't guarantee its accuracy (MS does not even acknowledge its existence). Feel free to use it and provide me some feedback it what I written here is incorrect.

Excel navigation made easy

2008-05-12T15:26:00.000-07:00

Have you ever wondered what is the best way to move around Excel? You may not believe this but using the keyboard is the best way to do this - quicker than the fastest mouse clicks you could muster.

You don't believe me? Let me show you.

Say you are working with column with 500 contiguous cells and you want to get to the last cell. Easy. Just press page up/down and the arrow keys until you see the last cell.

What if your data is bigger - 100,000 records? You can use your mouse with the scroll bar. Navigating through this is tricky but it is more practical than using the keyboard, right?

Not quite. A quicker keyboard stroke exists. Try doing this. Go to the column you are working on press Ctrl then the down Arrow key. Now you are in the last cell.

What if you want to select all of the non-empty cells in the contiguous list? Simple. You only need to add one key to the previous two. Go to the first cell and press Ctrl, the Shift and the Arrow keys. That's it.

The use of the Ctrl and Shift keys along with the navigation keys allows you to navigate around the worksheet quicker. The way to use them is listed below.

Ctrl+navigation key activates the last cell in a contiguous list for the direction specified.
Ctrl+Shift+nav key selects the cells from the original active cell to the last cell in a contiguous list for the direction specified.
Ctrl+Page up(down) key gets you to the next (previous) worksheet.
Ctrl+Shift+Page up(down) key allows you to select and group worksheets together.
Ctrl+End activates the last cell used in the worksheet.
Ctrl+ShiftEnd select the cells from the original activate cell to the last cell used in the worksheet.

Take time to use these keystrokes until they become intuitive. This is one Excel skill worth mastering.

Easy access to referred ranges

2008-04-12T15:23:00.000-07:00

Using Excel can be cumbersome sometimes. As you gain more confidence, you introduce complexities within your formula and setup. To address this, Excel has many tools available for you and one of these is the Name definition facility .

In Excel, you can define names that refers to frequently used Excel objects - from cells, ranges to complex formulas. This has many uses so it is worthwhile to discuss how to use it.

Let us start with associating a static range to a name.

First you need to highlight the range you want to use.

Access the Name Definition facility by clicking >>Insert>>Name>>Define>> option in the menu.

Then specify the name you have chosen in the Names in Workbook textbox (in this example it is lstName).

Click the Add button to complete the process.

What you have created here is basically list associated to a name. If you want to refer to this range, you can easily do this by using the associated name.

One advantage of this is that it simplifies updates . Say you refer to this range in many formula and you have to update it (maybe extend it to add new values), you need the range associated with name and the formula remains the same.

There are other uses of names that are not included in today's post. Maybe I can discuss them next time. Till then.

A different kind of match (process that is)

2008-03-11T15:51:00.000-07:00

Did you ever need to match data between two datasets in the mainframe? How were you able to do this in the past? Have you thought of finding the best way to do this?

Matching and merging data is the one of the common tasks for data analysis that most legacy developers have a template Cobol program ready for this type of task. This is a common approach that I bet most developers out there have one tucked in, ready to be used when needed.

I have no problem with a Cobol solution as it does the job. It is just that there are a lot of steps needed (tweaks on the keys, layout and the data to return, program compile) before I could use the program. For me it is too much especially if it is for a one off adhoc task.

Is there a quicker way?

Well, there is. In fact, there are many options you can choose from - EZtrieve, SAS or REXX to name a few. These tools provide a simpler approach but there is that provides the simplest solution and it involves a very unlikely tool.

Trust SORT to sort it out.

Yes, you heard it. SORT utilities, Syncsort in particular. The jobstep below is an example of how a typical match and merge SORT step will look like.

//JSTEPA1 EXEC PGM=SORT,REGION=0M
//SYSOUT DD SYSOUT=*
//SYSOUA DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(10,05),RLSE)
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(10,05),RLSE)
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(10,05),RLSE)
//SORTJNF1 DD DISP=SHR,DSN=match.file.one
//SORTJNF2 DD DISP=SHR,DSN=match.file.two
//SORTOUT DD DSN=merge.file.result,
// DISP=(NEW,CATLG,DELETE),DCB=(RECFM=FB,BLKSIZE=0),
// UNIT=SYSDA,SPACE=(CYL,(10,2),RLSE)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(23,9,A,33,4,A,37,2,A,39,2,A)
JOINKEYS FILE=F2, FIELDS=(1,9,A,11,4,A,16,2,A,19,2,A)
REFORMAT FIELDS=(F1:1,68,F2:36,4,41,2,44,2,F1:77,271)
SORT FIELDS=COPY
END

This job has almost the same setup as any SORT step in a JCL. The differences are highlighted below.

Two input files. Unlike your regular SORT step, this has two input files. SORTJNF1 and SORTJNF2 refers to the files to be matched. The output files remains the same.

JOINKEYS FILE=F1,FIELDS/JOINKEYS FILE=F2,FIELDS refers to the keys to be used for matching. As shown, we can specify multiple keys and these keys don't even have to be on the same position. We just have to make sure that the corresponding key for F1 is of the same length as that in F2. The FIELDS parameter follow the following syntax

p,l,o where p is the position, l is the length and o is the sort order.

REFORMAT FIELDS will contain the fields you need to return from both files. This will comprise the new merge record. The parameter after F1:/F2: refers the fields to be returned from the associated file.

That's it. Five (four if you took out the END line) lines of code needed. No code compile needed. Do you know of a shorter way that what I shown?

Note: The example shown is very simple. There are other parameters available to address many needs (filtering records to match, keeping unmatched record from files, etc) so it is worthwhile to explore this functionality.

Keeping unique records in Excel

2008-02-29T15:28:00.000-08:00

In data analysis, one of the most common task is the creations of summaries. To be able to do this, the first step you need to do would be to create a unique list of the field you need to summarized. There are many ways to do this and I am going to share with you the simplest one.

1. Go to the column containing the data to be analyzed. Note: the first top cell in the column will be treated as a header/label.

2. From the menu, choose Data, then Filter, then Advanced Filter.... You should see something similar to the screen below:

3. Once you clicked the Advanced Filter option, a pop up window will show you the Advance Filter options. Tick the Unique records. To determine where the results will be saved, choose between Filter the list, in-place (to keep the results in the same column) or Copy to another location. I usually want my results in another column. If you are like me, you need to specify the target location. See example below which saves the results in column D.

4. Once you have selected your options, click OK. You should see the unique records after.

That's all you need. It can't be any simpler than that.